SAP Business One 9.2: 系统存储过程之二
SAP Bysiness one 9.2在 示例 系统里 SQL Server 数据库名称： sap560 ,
以下是部分存储过程代码：
use sap560
go
CREATE PROCEDURE _TmSp_BinLocationListCRPrint
@type int,
@binSublevels nvarchar(500),-- (sublevel1From|sublevel1To|sublevel2From|sublevel2To|...)
@binAttributes nvarchar(500),-- (attr1From|attr1To|attr2From|attr2To|...)
@binCodeFromTo nvarchar(500),-- (binLocationFrom|binLocationTo|)
@itemCodeFromTo nvarchar(110),
@itemGroups nvarchar(max),
@batchFromTo nvarchar(300),
@serialFromTo nvarchar(300),
@binAbsSet nvarchar(max),
@batchAbs nvarchar(100),
@serialAbs nvarchar(100),
@whsCodeFromTo nvarchar(100), -- (warehouseIncludingFrom|warehouseIncludingTo|warehouseExcludingFrom|warehouseExcludingTo|)
@flag int
AS
DECLARE
@displayInactiveBin nvarchar(1),
@T_SQL nvarchar(max),
@ADD_RANGE_COND_STR nvarchar(max) =
N'IF @from_value <> ''''
SET @T_SQL = @T_SQL + '' AND '' + @table_alias + ''.'' + @table_field + ''>=(N'''''' + @from_value + '''''')''
IF @to_value <> ''''
SET @T_SQL = @T_SQL + '' AND '' + @table_alias + ''.'' + @table_field + ''<=(N'''''' + @to_value + '''''')''',
@ADD_BIN_SUBLEVEL_COND nvarchar(max) =
N'SET @table_alias = @T_OBIN
SET @table_field = ''SL1Code''
SET @from_value = @binSbl1From
SET @to_value = @binSbl1To
$ADD_RANGE_COND_STR
SET @table_field = ''SL2Code''
SET @from_value = @binSbl2From
SET @to_value = @binSbl2To
$ADD_RANGE_COND_STR
SET @table_field = ''SL3Code''
SET @from_value = @binSbl3From
SET @to_value = @binSbl3To
$ADD_RANGE_COND_STR
SET @table_field = ''SL4Code''
SET @from_value = @binSbl4From
SET @to_value = @binSbl4To
$ADD_RANGE_COND_STR',
@ADD_BIN_ATTR_COND nvarchar(max) =
N'SET @table_alias = @T_OBIN
SET @table_field = ''Attr1Val''
SET @from_value = @binAttr1From
SET @to_value = @binAttr1To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr2Val''
SET @from_value = @binAttr2From
SET @to_value = @binAttr2To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr3Val''
SET @from_value = @binAttr3From
SET @to_value = @binAttr3To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr4Val''
SET @from_value = @binAttr4From
SET @to_value = @binAttr4To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr5Val''
SET @from_value = @binAttr5From
SET @to_value = @binAttr5To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr6Val''
SET @from_value = @binAttr6From
SET @to_value = @binAttr6To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr7Val''
SET @from_value = @binAttr7From
SET @to_value = @binAttr7To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr8Val''
SET @from_value = @binAttr8From
SET @to_value = @binAttr8To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr9Val''
SET @from_value = @binAttr9From
SET @to_value = @binAttr9To
$ADD_RANGE_COND_STR
SET @table_field = ''Attr10Val''
SET @from_value = @binAttr10From
SET @to_value = @binAttr10To
$ADD_RANGE_COND_STR',
@ADD_BIN_CODE_COND nvarchar(max) =
N'SET @table_alias = @T_OBIN
SET @table_field = ''BinCode''
SET @from_value = @binLocCodeFrom
SET @to_value = @binLocCodeTo
$ADD_RANGE_COND_STR',
@ADD_WHS_INCLUD_COND nvarchar(max) =
N'SET @table_alias = @T_OBIN
SET @table_field = ''WhsCode''
SET @from_value = @whsIncludingFrom
SET @to_value = @whsIncludingTo
$ADD_RANGE_COND_STR',
@ADD_WHS_EXCLUD_COND nvarchar(max) =
N'SET @table_alias = @T_OBIN
SET @table_field = ''WhsCode''
SET @from_value = @whsExcludingFrom
SET @to_value = @whsExcludingTo
IF @from_value <> '''' OR @to_value <> ''''
BEGIN
SET @T_SQL = @T_SQL + '' AND (''
IF @from_value <> ''''
SET @T_SQL = @T_SQL + @table_alias + ''.'' + @table_field + ''<(N'''''' + @from_value + '''''')''
IF @to_value <> ''''
BEGIN
IF @from_value <> ''''
SET @T_SQL = @T_SQL + '' OR '' + @table_alias + ''.'' + @table_field + ''>(N'''''' + @to_value + '''''')''
ELSE
SET @T_SQL = @T_SQL + @table_alias + ''.'' + @table_field + ''>(N'''''' + @to_value + '''''')''
END
SET @T_SQL = @T_SQL + '') ''
END',
@ADD_INACTIVE_BIN_COND nvarchar(max) =
N'IF @displayInactiveBin = ''N''
SET @T_SQL = @T_SQL + '' AND '' + @T_OBIN + ''.Disabled = ''''N'''' ''',
@ParmDefinition nvarchar(max) =
N'
@binSublevels nvarchar(500),
@binAttributes nvarchar(500),
@binCodeFromTo nvarchar(500),
@itemCodeFromTo nvarchar(110),
@itemGroups nvarchar(max),
@batchFromTo nvarchar(300),
@serialFromTo nvarchar(300),
@binAbsSet nvarchar(max),
@batchAbs nvarchar(100),
@serialAbs nvarchar(100),
@whsCodeFromTo nvarchar(100),
@displayInactiveBin nvarchar(1),
@T_SQL nvarchar(max) = N'''',
@BIN_SUBLEVEL_LENGTH int = 50,
@BIN_ATTR_LENGTH int = 20,
@BIN_LOC_CODE_LENGTH int = 228,
@ITEM_CODE_LENGTH int = 50,
@SNB_NUMBER_LENGTH int = 36,
@SNB_ATTR_LENGTH int = 32,
@WHS_CODE_LENGTH int = 8,
@BIN_SUBLEVEL_COUNT int = 4,
@BIN_ATTR_COUNT int = 10,
@T_OBIN nvarchar(4) = N''OBIN'',
@T_OIBQ nvarchar(4) = N''OIBQ'',
@T_OBBQ nvarchar(4) = N''OBBQ'',
@T_OSBQ nvarchar(4) = N''OSBQ'',
@T_OITM nvarchar(4) = N''OITM'',
@T_OBTN nvarchar(4) = N''OBTN'',
@T_OSRN nvarchar(4) = N''OSRN'',
@T_OITB nvarchar(4) = N''OITB'',
@T_ITM_RTRICT nvarchar(10) = N''ITM_RTRICT'',
@T_BATCH_RTRICT nvarchar(12) = N''BATCH_RTRICT'',
@T_REPLENISH_QTY nvarchar(20) = N''REPLENISH_QTY'',
@ITM_RTRICT_SPC_ITM nvarchar(4) = N''1'',
@ITM_RTRICT_SNG_ITM nvarchar(4) = N''2'',
@ITM_RTRICT_SPC_ITM_GROUP nvarchar(4) = N''3'',
@ITM_RTRICT_SNG_ITM_GROUP nvarchar(4) = N''4'',
@binSbl1From nvarchar(50) = N'''',
@binSbl1To nvarchar(50) = N'''',
@binSbl2From nvarchar(50) = N'''',
@binSbl2To nvarchar(50) = N'''',
@binSbl3From nvarchar(50) = N'''',
@binSbl3To nvarchar(50) = N'''',
@binSbl4From nvarchar(50) = N'''',
@binSbl4To nvarchar(50) = N'''',
@binAttr1From nvarchar(20) = N'''',
@binAttr1To nvarchar(20) = N'''',
@binAttr2From nvarchar(20) = N'''',
@binAttr2To nvarchar(20) = N'''',
@binAttr3From nvarchar(20) = N'''',
@binAttr3To nvarchar(20) = N'''',
@binAttr4From nvarchar(20) = N'''',
@binAttr4To nvarchar(20) = N'''',
@binAttr5From nvarchar(20) = N'''',
@binAttr5To nvarchar(20) = N'''',
@binAttr6From nvarchar(20) = N'''',
@binAttr6To nvarchar(20) = N'''',
@binAttr7From nvarchar(20) = N'''',
@binAttr7To nvarchar(20) = N'''',
@binAttr8From nvarchar(20) = N'''',
@binAttr8To nvarchar(20) = N'''',
@binAttr9From nvarchar(20) = N'''',
@binAttr9To nvarchar(20) = N'''',
@binAttr10From nvarchar(20) = N'''',
@binAttr10To nvarchar(20) = N'''',
@binLocCodeFrom nvarchar(228) = N'''',
@binLocCodeTo nvarchar(228) = N'''',
@itemCodeFrom nvarchar(50) = N'''',
@itemCodeTo nvarchar(50) = N'''',
@batchNumberFrom nvarchar(36) = N'''',
@batchNumberTo nvarchar(36) = N'''',
@batchAttr1From nvarchar(32) = N'''',
@batchAttr1To nvarchar(32) = N'''',
@batchAttr2From nvarchar(32) = N'''',
@batchAttr2To nvarchar(32) = N'''',
@serialNumberFrom nvarchar(36) = N'''',
@serialNumberTo nvarchar(36) = N'''',
@mfrSerialNumberFrom nvarchar(32) = N'''',
@mfrSerialNumberTo nvarchar(32) = N'''',
@lotNumberFrom nvarchar(32) = N'''',
@lotNumberTo nvarchar(32) = N'''',
@whsIncludingFrom nvarchar(8) = N'''',
@whsIncludingTo nvarchar(8) = N'''',
@whsExcludingFrom nvarchar(8) = N'''',
@whsExcludingTo nvarchar(8) = N'''',
@displayInactiveItem nvarchar(1) = N'''',
@T_SQL_ITEM_RTRICT nvarchar(max) = N'''',
@T_SQL_BATCH_RTRICT nvarchar(max) = N'''',
@T_SQL_REPLENISH_QTY nvarchar(max) = N'''',
@V_INT int = 0,
@table_alias nvarchar(10) = N'''',
@table_field nvarchar(20) = N'''',
@from_value nvarchar(250) = N'''',
@to_value nvarchar(250) = N'''',
@COMMA nvarchar(1) = N'','',
@BIN_LOC_TABLE_BASE_FIELD nvarchar(max) = N'''' ',
@PREPARE_SQL nvarchar(max) =
N'IF @binSublevels <> ''''
BEGIN
SET @V_INT = 1
SET @binSbl1From = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl1To = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl2From = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl2To = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl3From = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl3To = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl4From = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
SET @V_INT = @V_INT + @BIN_SUBLEVEL_LENGTH
SET @binSbl4To = RTRIM(SUBSTRING(@binSublevels, @V_INT, @BIN_SUBLEVEL_LENGTH))
END
IF @binAttributes <> ''''
BEGIN
SET @V_INT = 1
SET @binAttr1From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr1To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr2From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr2To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr3From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr3To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr4From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr4To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr5From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr5To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr6From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr6To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr7From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr7To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr8From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr8To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr9From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr9To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr10From = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
SET @V_INT = @V_INT + @BIN_ATTR_LENGTH
SET @binAttr10To = RTRIM(SUBSTRING(@binAttributes, @V_INT, @BIN_ATTR_LENGTH))
END
IF @binCodeFromTo <> ''''
BEGIN
SET @V_INT = 1
SET @binLocCodeFrom = RTRIM(SUBSTRING(@binCodeFromTo, @V_INT, @BIN_LOC_CODE_LENGTH))
SET @V_INT = @V_INT + @BIN_LOC_CODE_LENGTH
SET @binLocCodeTo = RTRIM(SUBSTRING(@binCodeFromTo, @V_INT, @BIN_LOC_CODE_LENGTH))
END
IF @itemCodeFromTo <> ''''
BEGIN
SET @V_INT = 1
SET @itemCodeFrom = RTRIM(SUBSTRING(@itemCodeFromTo, @V_INT, @ITEM_CODE_LENGTH))
SET @V_INT = @V_INT + @BIN_LOC_CODE_LENGTH
SET @itemCodeTo = RTRIM(SUBSTRING(@itemCodeFromTo, @V_INT, @ITEM_CODE_LENGTH))
END
IF @batchFromTo <> ''''
BEGIN
SET @V_INT = 1
SET @batchNumberFrom = RTRIM(SUBSTRING(@batchFromTo, @V_INT, @SNB_NUMBER_LENGTH))
SET @V_INT = @V_INT + @SNB_NUMBER_LENGTH
SET @batchNumberTo = RTRIM(SUBSTRING(@batchFromTo, @V_INT, @SNB_NUMBER_LENGTH))
SET @V_INT = @V_INT + @SNB_NUMBER_LENGTH
SET @batchAttr1From = RTRIM(SUBSTRING(@batchFromTo, @V_INT, @SNB_ATTR_LENGTH))
SET @V_INT = @V_INT + @SNB_ATTR_LENGTH
SET @batchAttr1To = RTRIM(SUBSTRING(@batchFromTo, @V_INT, @SNB_ATTR_LENGTH))
SET @V_INT = @V_INT + @SNB_ATTR_LENGTH
SET @batchAttr2From = RTRIM(SUBSTRING(@batchFromTo, @V_INT, @SNB_ATTR_LENGTH))
SET @V_INT = @V_INT + @SNB_ATTR_LENGTH
SET @batchAttr2To = RTRIM(SUBSTRING(@batchFromTo, @V_INT, @SNB_ATTR_LENGTH))
END
IF @serialFromTo <> ''''
BEGIN
SET @V_INT = 1
SET @serialNumberFrom = RTRIM(SUBSTRING(@serialFromTo, @V_INT, @SNB_NUMBER_LENGTH))
SET @V_INT = @V_INT + @SNB_NUMBER_LENGTH
SET @serialNumberTo = RTRIM(SUBSTRING(@serialFromTo, @V_INT, @SNB_NUMBER_LENGTH))
SET @V_INT = @V_INT + @SNB_NUMBER_LENGTH
SET @mfrSerialNumberFrom = RTRIM(SUBSTRING(@serialFromTo, @V_INT, @SNB_ATTR_LENGTH))
SET @V_INT = @V_INT + @SNB_ATTR_LENGTH
SET @mfrSerialNumberTo = RTRIM(SUBSTRING(@serialFromTo, @V_INT, @SNB_ATTR_LENGTH))
SET @V_INT = @V_INT + @SNB_ATTR_LENGTH
SET @lotNumberFrom = RTRIM(SUBSTRING(@serialFromTo, @V_INT, @SNB_ATTR_LENGTH))
SET @V_INT = @V_INT + @SNB_ATTR_LENGTH
SET @lotNumberTo = RTRIM(SUBSTRING(@serialFromTo, @V_INT, @SNB_ATTR_LENGTH))
END
IF @whsCodeFromTo <> ''''
BEGIN
SET @V_INT = 1
SET @whsIncludingFrom = RTRIM(SUBSTRING(@whsCodeFromTo, @V_INT, @WHS_CODE_LENGTH))
SET @V_INT = @V_INT + @WHS_CODE_LENGTH
SET @whsIncludingTo = RTRIM(SUBSTRING(@whsCodeFromTo, @V_INT, @WHS_CODE_LENGTH))
SET @V_INT = @V_INT + @WHS_CODE_LENGTH
SET @whsExcludingFrom = RTRIM(SUBSTRING(@whsCodeFromTo, @V_INT, @WHS_CODE_LENGTH))
SET @V_INT = @V_INT + @WHS_CODE_LENGTH
SET @whsExcludingTo = RTRIM(SUBSTRING(@whsCodeFromTo, @V_INT, @WHS_CODE_LENGTH))
END
SET @binAbsSet = LTRIM(RTRIM(@binAbsSet))
IF @binAbsSet <> ''''
BEGIN
DECLARE
@start int = 1,
@length int = 1,
@BinAbs nvarchar(10)
CREATE TABLE #TMP_TABLE_BIN_ABS (BinAbs INTEGER)
SET @V_INT = CHARINDEX(@COMMA, @binAbsSet)
WHILE @V_INT <> 0
BEGIN
SET @length = @V_INT - @start
SET @BinAbs = SUBSTRING(@binAbsSet, @start, @length)
INSERT INTO #TMP_TABLE_BIN_ABS VALUES(@BinAbs)
SET @start = @V_INT + 1
SET @V_INT = CHARINDEX(@COMMA, @binAbsSet, @start)
END
IF @V_INT = 0
BEGIN
SET @length = LEN(@binAbsSet) + 1 - @start
SET @BinAbs = SUBSTRING(@binAbsSet, @start, @length)
INSERT INTO #TMP_TABLE_BIN_ABS VALUES(@BinAbs)
END
END
SET @BIN_LOC_TABLE_BASE_FIELD =
@T_OBIN + ''.BinCode,''
+ @T_OBIN + ''.WhsCode,''
+ @T_OBIN + ''.SL1Code,''
+ @T_OBIN + ''.SL2Code,''
+ @T_OBIN + ''.SL3Code,''
+ @T_OBIN + ''.SL4Code,''
+ @T_OBIN + ''.Disabled,''
+ @T_OBIN + ''.ReceiveBin,''
+ @T_OBIN + ''.Descr,''
+ @T_OBIN + ''.AltSortCod,''
+ @T_OBIN + ''.BarCode,''
+ ''CASE '' + @T_OBIN + ''.MinLevel WHEN 0 THEN NULL ELSE '' + @T_OBIN + ''.MinLevel END AS MinLevel,''
+ ''CASE '' + @T_OBIN + ''.MaxLevel WHEN 0 THEN NULL ELSE '' + @T_OBIN + ''.MaxLevel END AS MaxLevel,''
+ @T_OBIN + ''.Attr1Val,''
+ @T_OBIN + ''.Attr2Val,''
+ @T_OBIN + ''.Attr3Val,''
+ @T_OBIN + ''.Attr4Val,''
+ @T_OBIN + ''.Attr5Val,''
+ @T_OBIN + ''.Attr6Val,''
+ @T_OBIN + ''.Attr7Val,''
+ @T_OBIN + ''.Attr8Val,''
+ @T_OBIN + ''.Attr9Val,''
+ @T_OBIN + ''.Attr10Val,''
+ @T_OBIN + ''.RtrictType,''
+ @T_OBIN + ''.RtrictResn,''
+ @T_OBIN + ''.RtrictDate,''
SET @T_SQL_ITEM_RTRICT =
N''(SELECT '' + @T_OBIN + ''.AbsEntry AS ''''BinAbs'''',''
+ @T_OBIN + ''.SpcItmCode AS ''''RestrictedTo'''' FROM ''
+ @T_OBIN + '' WHERE '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SPC_ITM
+ '' UNION ALL ''
+ '' SELECT '' + @T_OIBQ + ''.BinAbs AS ''''BinAbs'''', '' + @T_OIBQ + ''.ItemCode AS ''''RestrictedTo'''' FROM ''
+ @T_OIBQ + '' WHERE '' + @T_OIBQ + ''.BinAbs IN (SELECT AbsEntry FROM '' + @T_OBIN + '' WHERE ''
+ @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SNG_ITM + '') AND '' + @T_OIBQ + ''.OnHandQty > 0 GROUP BY ''
+ @T_OIBQ + ''.BinAbs, '' + @T_OIBQ + ''.ItemCode HAVING COUNT('' + @T_OIBQ + ''.BinAbs) < 2 ''
+ '' UNION ALL ''
+ '' SELECT '' + @T_OBIN + ''.AbsEntry AS ''''BinAbs'''', '' + @T_OITB + ''.ItmsGrpNam AS ''''RestrictedTo'''' FROM ''
+ @T_OBIN + '' INNER JOIN '' + @T_OITB + '' ON '' + @T_OBIN + ''.SpcItmGrpC = '' + @T_OITB + ''.ItmsGrpCod''
+ '' WHERE '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SPC_ITM_GROUP +
'' UNION ALL '' +
'' SELECT '' + @T_OIBQ + ''.BinAbs AS ''''BinAbs'''', '' + '' MAX('' + @T_OITB + ''.ItmsGrpNam) ''
+ '' AS ''''RestrictedTo'''' FROM '' + @T_OIBQ + '' INNER JOIN '' + @T_OITM + '' ON ''
+ @T_OIBQ + ''.ItemCode = '' + @T_OITM + ''.ItemCode '' + '' INNER JOIN '' + @T_OITB + '' ON ''
+ @T_OITM + ''.ItmsGrpCod = '' + @T_OITB + ''.ItmsGrpCod '' + '' WHERE ''
+ @T_OIBQ + ''.BinAbs IN (SELECT AbsEntry FROM '' + @T_OBIN + '' WHERE '' + @T_OBIN + ''.ItmRtrictT = ''
+ @ITM_RTRICT_SNG_ITM_GROUP + '') AND '' + @T_OIBQ + ''.OnHandQty > 0 GROUP BY '' + @T_OIBQ
+ ''.BinAbs, '' + @T_OITM + ''.ItmsGrpCod HAVING COUNT('' + @T_OIBQ + ''.BinAbs) < 2) AS '' + @T_ITM_RTRICT
SET @T_SQL_BATCH_RTRICT =
N''(SELECT '' + @T_OBBQ + ''.BinAbs AS ''''BinAbs'''', '' + ''Min('' + @T_OBTN + ''.DistNumber) AS ''''Batch'''' FROM ''
+ @T_OBBQ + '' INNER JOIN '' + @T_OBTN + '' ON '' + @T_OBBQ + ''.SnBMDAbs = '' + @T_OBTN + ''.AbsEntry WHERE ''
+ @T_OBBQ + ''.BinAbs IN (SELECT AbsEntry FROM '' + @T_OBIN + '' WHERE '' + @T_OBIN + ''.SngBatch = ''''Y'''') AND ''
+ @T_OBBQ + ''.OnHandQty <> 0 GROUP BY '' + @T_OBBQ + ''.BinAbs, '' + @T_OBBQ
+ ''.SnBMDAbs HAVING COUNT(*) < 2) AS '' + @T_BATCH_RTRICT
SET @T_SQL_REPLENISH_QTY =
N'' (SELECT '' + @T_OIBQ + ''.BinAbs, SUM('' + @T_OIBQ + ''.OnHandQty) AS ReplenishOnHandQty FROM '' + @T_OIBQ
+ '' INNER JOIN '' + @T_OBIN + '' ON '' + @T_OIBQ + ''.BinAbs = '' + @T_OBIN + ''.AbsEntry AND '' + @T_OIBQ
+ ''.ItemCode = '' + @T_OBIN + ''.SpcItmCode AND '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SPC_ITM
+ '' WHERE '' + @T_OIBQ + ''.OnHandQty <> 0 GROUP BY '' + @T_OIBQ + ''.BinAbs ''
+ '' UNION ALL ''
+ '' SELECT '' + @T_OIBQ + ''.BinAbs, SUM('' + @T_OIBQ + ''.OnHandQty) AS ReplenishOnHandQty FROM '' + @T_OIBQ
+ '' WHERE '' + @T_OIBQ + ''.OnHandQty > 0 AND '' + @T_OIBQ + ''.BinAbs IN (SELECT AbsEntry FROM '' + @T_OBIN
+ '' WHERE '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SNG_ITM + '' ) GROUP BY '' + @T_OIBQ + ''.BinAbs ) AS '' + @T_REPLENISH_QTY
'
BEGIN
SET NOCOUNT ON;
IF @flag & 4 = 4
SET @displayInactiveBin = N'Y'
ELSE
SET @displayInactiveBin = N'N'
SET @ADD_BIN_SUBLEVEL_COND = REPLACE(@ADD_BIN_SUBLEVEL_COND, N'$ADD_RANGE_COND_STR', @ADD_RANGE_COND_STR)
SET @ADD_BIN_ATTR_COND = REPLACE(@ADD_BIN_ATTR_COND, N'$ADD_RANGE_COND_STR', @ADD_RANGE_COND_STR)
SET @ADD_BIN_CODE_COND = REPLACE(@ADD_BIN_CODE_COND, N'$ADD_RANGE_COND_STR', @ADD_RANGE_COND_STR)
SET @ADD_WHS_INCLUD_COND = REPLACE(@ADD_WHS_INCLUD_COND, N'$ADD_RANGE_COND_STR', @ADD_RANGE_COND_STR)
SET @T_SQL =
N'
BEGIN
$PREPARE_SQL
SET @T_SQL =
N''SELECT '' + @BIN_LOC_TABLE_BASE_FIELD
+ @T_OBIN + ''.ItmRtrictT, ''
+ @T_ITM_RTRICT + ''.RestrictedTo, ''
+ @T_OBIN + ''.SngBatch, ''
+ @T_BATCH_RTRICT + ''.Batch,''
+ '' CASE B.ItemCount WHEN 0 THEN NULL ELSE B.ItemCount END AS ItemCount, ''
+ '' CASE B.OnHandQty WHEN 0 THEN NULL ELSE B.OnHandQty END AS OnHandQty, ''
+ '' CASE B.SnBCount WHEN 0 THEN NULL ELSE B.SnBCount END AS SnBCount, ''
+ '' CASE ''
+ '' WHEN B.ItmCountNeg = 1 AND '' + @T_OBIN + ''.ItmRtrictT <> '' + @ITM_RTRICT_SPC_ITM + '' THEN ''
+ '' CASE WHEN '' + @T_OBIN + ''.MinLevel <> ISNULL(B.OnHandQty, 0) THEN '' + @T_OBIN + ''.MinLevel - ISNULL(B.OnHandQty, 0) ELSE NULL END ''
+ '' WHEN '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SPC_ITM + '' AND '' + @T_OBIN + ''.Disabled = N''''N'''' THEN ''
+ '' CASE WHEN '' + @T_OBIN + ''.MinLevel <> '' + ''ISNULL('' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty, 0) ''
+ '' THEN '' + + @T_OBIN + ''.MinLevel - '' + ''ISNULL('' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty, 0) ELSE NULL END ''
+ '' WHEN B.ItmCountNeg <> 1 AND '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SNG_ITM + '' THEN ''
+ '' CASE WHEN '' + @T_OBIN + ''.MinLevel <> '' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty THEN ''
+ @T_OBIN + ''.MinLevel - '' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty ELSE NULL END ''
+ '' ELSE NULL ''
+ '' END AS QtyBelowMin, ''
+ '' CASE ''
+ '' WHEN B.ItmCountNeg = 1 AND '' + @T_OBIN + ''.ItmRtrictT <> '' + @ITM_RTRICT_SPC_ITM
+ '' AND '' + @T_OBIN + ''.MaxLevel <> 0 AND '' + @T_OBIN + ''.MaxLevel > ISNULL(B.OnHandQty, 0) ''
+ '' THEN ('' + @T_OBIN + ''.MaxLevel - ISNULL(B.OnHandQty, 0)) ''
+ '' WHEN '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SPC_ITM + '' AND '' + @T_OBIN + ''.Disabled = N''''N'''' ''
+ '' AND '' + @T_OBIN + ''.MaxLevel <> 0 AND '' + @T_OBIN + ''.MaxLevel > '' + ''ISNULL( '' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty, 0) ''
+ '' THEN '' + @T_OBIN + ''.MaxLevel - '' + ''ISNULL('' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty, 0) ''
+ '' WHEN B.ItmCountNeg <> 1 AND '' + @T_OBIN + ''.ItmRtrictT = '' + @ITM_RTRICT_SNG_ITM
+ '' AND '' + @T_OBIN + ''.MaxLevel <> 0 AND '' + @T_OBIN + ''.MaxLevel > '' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty ''
+ '' THEN '' + @T_OBIN + ''.MaxLevel - '' + @T_REPLENISH_QTY + ''.ReplenishOnHandQty ''
+ '' ELSE NULL ''
+ '' END AS ReplenishmentQty ''
+ '' FROM '' + @T_OBIN + '' LEFT OUTER JOIN ''
+ '' (SELECT MAX(A.BinAbs) AS ''''BinAbs'''',''
+ ''SUM(A.ItemCount) AS ''''ItemCount'''',''
+ ''SUM(A.OnHandQty) AS ''''OnHandQty'''',''
+ ''SUM(A.SnBCount) AS ''''SnBCount'''',''
+ ''SUM(A.ItmCountNeg) AS ''''ItmCountNeg'''' ''
+ '' FROM ''
+ '' (SELECT '' + @T_OIBQ + ''.BinAbs AS ''''BinAbs'''',''
+ ''Count(DISTINCT '' + @T_OIBQ + ''.ItemCode) AS ''''ItemCount'''',''
+ ''Max('' + @T_OIBQ + ''.OnHandQty) AS ''''OnHandQty'''',''
+ ''Count(DISTINCT '' + @T_OBBQ + ''.SnBMDAbs) ''
+ '' + Count(DISTINCT '' + @T_OSBQ + ''.SnBMDAbs) AS ''''SnBCount'''',''
+ ''Count(DISTINCT '' + @T_OIBQ + ''.ItemCode) AS ''''ItmCountNeg''''''
+ '' FROM '' + @T_OIBQ
+ '' LEFT OUTER JOIN '' + @T_OBBQ + '' ON ''
+ @T_OIBQ + ''.BinAbs = '' + @T_OBBQ + ''.BinAbs''
+ '' AND '' + @T_OIBQ + ''.ItemCode = '' + @T_OBBQ + ''.ItemCode''
+ '' AND '' + @T_OBBQ + ''.OnHandQty <> 0''
+ '' LEFT OUTER JOIN '' + @T_OSBQ
+ '' ON '' + @T_OIBQ + ''.BinAbs = '' + @T_OSBQ + ''.BinAbs''
+ '' AND '' + @T_OIBQ + ''.ItemCode = '' + @T_OSBQ + ''.ItemCode''
+ '' AND '' + @T_OSBQ + ''.OnHandQty <> 0''
+ '' WHERE '' + @T_OIBQ + ''.OnHandQty > 0 ''
+ '' GROUP BY '' + @T_OIBQ + ''.BinAbs,'' + @T_OIBQ + ''.AbsEntry''
+ '' UNION ALL ''
+ '' SELECT '' + @T_OIBQ + ''.BinAbs AS ''''BinAbs'''',''
+ ''0 AS ''''ItemCount'''',''
+ ''Max('' + @T_OIBQ + ''.OnHandQty) AS ''''OnHandQty'''',''
+ ''Count(DISTINCT '' + @T_OBBQ + ''.SnBMDAbs) ''
+ '' + Count(DISTINCT '' + @T_OSBQ + ''.SnBMDAbs) AS ''''SnBCount'''',''
+ ''Count(DISTINCT '' + @T_OIBQ + ''.ItemCode) AS ''''ItmContNeg''''''
+ '' FROM '' + @T_OIBQ
+ '' LEFT OUTER JOIN '' + @T_OBBQ + '' ON ''
+ @T_OIBQ + ''.BinAbs = '' + @T_OBBQ + ''.BinAbs''
+ '' AND '' + @T_OIBQ + ''.ItemCode = '' + @T_OBBQ + ''.ItemCode''
+ '' AND '' + @T_OBBQ + ''.OnHandQty <> 0''
+ '' LEFT OUTER JOIN '' + @T_OSBQ
+ '' ON '' + @T_OIBQ + ''.BinAbs = '' + @T_OSBQ + ''.BinAbs''
+ '' AND '' + @T_OIBQ + ''.ItemCode = '' + @T_OSBQ + ''.ItemCode''
+ '' AND '' + @T_OSBQ + ''.OnHandQty <> 0''
+ '' WHERE '' + @T_OIBQ + ''.OnHandQty < 0''
+ '' GROUP BY '' + @T_OIBQ + ''.BinAbs,'' + @T_OIBQ + ''.AbsEntry) AS A''
+ '' GROUP BY A.BinAbs) AS B''
+ '' ON '' + @T_OBIN + ''.AbsEntry = B.BinAbs''
+ '' LEFT OUTER JOIN '' +
@T_SQL_ITEM_RTRICT + '' ON '' + @T_OBIN + ''.AbsEntry = '' + @T_ITM_RTRICT + ''.BinAbs''
+ '' LEFT OUTER JOIN '' +
@T_SQL_BATCH_RTRICT + '' ON '' + @T_OBIN + ''.AbsEntry = '' + @T_BATCH_RTRICT + ''.BinAbs''
+ '' LEFT OUTER JOIN '' +
@T_SQL_REPLENISH_QTY + '' ON '' + @T_OBIN + ''.AbsEntry = '' + @T_REPLENISH_QTY + ''.BinAbs''
+ '' WHERE '' + @T_OBIN + ''.AbsEntry > 0 ''
SET @T_SQL = @T_SQL + '' AND '' + @T_OBIN + ''.WhsCode IN ( SELECT WhsCode FROM OWHS WHERE Inactive = N''''N'''') ''
IF @binAbsSet <> ''''
SET @T_SQL = @T_SQL + '' AND '' + @T_OBIN + ''.AbsEntry IN (SELECT BinAbs FROM #TMP_TABLE_BIN_ABS) ''
$ADD_WHS_INCLUD_COND
$ADD_WHS_EXCLUD_COND
$ADD_BIN_SUBLEVEL_COND
$ADD_BIN_ATTR_COND
$ADD_BIN_CODE_COND
$ADD_INACTIVE_BIN_COND
SET @T_SQL = @T_SQL + '' ORDER BY '' + @T_OBIN + ''.BinCode''
--PRINT(@T_SQL)
EXEC(@T_SQL)
END'
SET @T_SQL = REPLACE(@T_SQL, N'$PREPARE_SQL', @PREPARE_SQL)
SET @T_SQL = REPLACE(@T_SQL, N'$ADD_WHS_INCLUD_COND', @ADD_WHS_INCLUD_COND)
SET @T_SQL = REPLACE(@T_SQL, N'$ADD_WHS_EXCLUD_COND', @ADD_WHS_EXCLUD_COND)
SET @T_SQL = REPLACE(@T_SQL, N'$ADD_BIN_SUBLEVEL_COND', @ADD_BIN_SUBLEVEL_COND)
SET @T_SQL = REPLACE(@T_SQL, N'$ADD_BIN_ATTR_COND', @ADD_BIN_ATTR_COND)
SET @T_SQL = REPLACE(@T_SQL, N'$ADD_BIN_CODE_COND', @ADD_BIN_CODE_COND)
SET @T_SQL = REPLACE(@T_SQL, N'$ADD_INACTIVE_BIN_COND', @ADD_INACTIVE_BIN_COND)
EXEC sp_executesql
@T_SQL,
@ParmDefinition,
@binSublevels = @binSublevels,
@binAttributes = @binAttributes,
@binCodeFromTo = @binCodeFromTo,
@itemCodeFromTo = @itemCodeFromTo,
@itemGroups = @itemGroups,
@batchFromTo = @batchFromTo,
@serialFromTo = @serialFromTo,
@binAbsSet = @binAbsSet,
@batchAbs = @batchAbs,
@serialAbs = @serialAbs,
@whsCodeFromTo = @whsCodeFromTo,
@displayInactiveBin = @displayInactiveBin
END
GO
CREATE PROC _TmSp_CreateGetCompanyTimeFunction
as
Begin
SET NOCOUNT ON
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCompanyTime]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetCompanyTime]
Declare @sql nvarchar (2000)
set @sql = 'create FUNCTION GetCompanyTime() RETURNS datetime AS BEGin DECLARE @OutDate datetime DECLARE @offset1 numeric(9,4) DECLARE @dst varchar(1) select top 1 @offset1 = offset, @dst = isnull(ActiveDst,0) from OTIZ order by id desc if @offset1 is NULL RETURN(getdate()) if (@dst = ''Y'') set @offset1 = @offset1 + 60 set @offset1 = @offset1 /1440 RETURN(getUTCdate() + @offset1) END'
exec (@sql )
SET NOCOUNT OFF
End
GO
CREATE proc _TmSp_ReconUpgFillInTempJDT1
@tempJDT1Table nvarchar(128) = NULL,
@tempBPTable nvarchar(128) = NULL
as
begin
exec ('insert into ' + @tempJDT1Table + ' select T0.* from JDT1 T0 inner join ' + @tempBPTable + ' T1 on T0.ShortName = T1.ShortName and T0.Account = T1.Account' )
end
GO
CREATE PROCEDURE [dbo].[_TmSp_RetrieveDocLineBatchAndSerialNumbers]
@docType int,
@docEntry int
AS
BEGIN
create table #temp_Numbers
(
DocRow int,
ManagedBy int,
BatchNumber nvarchar(4000),
SerialNumber nvarchar(4000),
Quantity numeric(19, 6),
Price numeric(19,6),
Rate numeric(19, 6)
)
declare @docTable as char(4)
if @docType=15
set @docTable='DLN1'
else if @docType=16
set @docTable='RDN1'
else if @docType=20
set @docTable='PDN1'
else if @docType=21
set @docTable='RPD1'
else if @docType=59
set @docTable='IGN1'
else if @docType=60
set @docTable='IGE1'
else if @docType=67
set @docTable='WTR1'
else if @docType=112
set @docTable='DRF1'
create table #temp_Numbers_Return
(
DocumentEntry int,
DocRow int,
ManagedBy int,
BatchNumber nvarchar(4000),
SerialNumber nvarchar(4000),
Price numeric(19,6)
)
--select t0.LineNum, t5.ManagedBy,t5.BatchNumber,t5.SerialNumber,t5.Quantity from PDN1 t0
--left join
--(
-- select t1.DocEntry,t1.DocLine, t1.ManagedBy,t3.DistNumber as 'BatchNumber', t4.DistNumber as 'SerialNumber', t2.Quantity
-- from OITL t1
-- join ITL1 t2 on t1.LogEntry=t2.LogEntry and t1.DocType= 20 and t1.DocEntry=1
-- left outer join OBTN t3 on t2.SysNumber=t3.SysNumber and t2.ItemCode=t3.ItemCode and t1.ManagedBy=10000044
-- left outer join OSRN t4 on t2.SysNumber=t4.SysNumber and t2.ItemCode=t4.ItemCode and t1.ManagedBy=10000045
--) t5
--on t0.DocEntry=t5.DocEntry and t0.LineNum=t5.DocLine
--where t0.DocEntry=1
--order by t0.LineNum
declare @sqlStatement nvarchar(4000)
set @sqlStatement=N'insert into #temp_Numbers select t0.LineNum, t5.ManagedBy, t5.BatchNumber, t5.SerialNumber, t5.Quantity,t0.Price,t0.Rate from '+@docTable+' t0
left join
(
select t1.DocEntry,t1.DocLine, t1.ManagedBy,t3.DistNumber as ''BatchNumber'', t4.DistNumber as ''SerialNumber'', t2.Quantity
from OITL t1
join ITL1 t2 on t1.LogEntry=t2.LogEntry and t1.DocType= '+convert(nvarchar(20),@docType)+' and t1.DocEntry='+ convert(nvarchar(20),@docEntry)+
N' left outer join OBTN t3 on t2.SysNumber=t3.SysNumber and t2.ItemCode=t3.ItemCode and t1.ManagedBy=10000044
left outer join OSRN t4 on t2.SysNumber=t4.SysNumber and t2.ItemCode=t4.ItemCode and t1.ManagedBy=10000045
) t5
on t0.DocEntry=t5.DocEntry and t0.LineNum=t5.DocLine
where t0.DocEntry= '+convert(nvarchar(20),@docEntry)+N' order by t0.LineNum'
exec sp_executesql @sqlStatement
declare @currentRow int,@number nvarchar(4000), @managedByOfLastLine int,@lcPrice numeric(19, 6)
declare @rowID int, @managedBy int, @batch_number nvarchar(4000), @serial_number nvarchar(4000), @qty numeric(19, 6),@price numeric(19, 6),@rate numeric(19, 6)
set @number=''
set @lcPrice=0
declare rec_Cursor cursor for(select t0.* from #temp_Numbers t0) for read only
open rec_Cursor
FETCH NEXT FROM rec_Cursor into @rowID,@managedBy,@batch_number,@serial_number,@qty,@price,@rate
if @@FETCH_STATUS=0
set @currentRow=@rowID
while @@FETCH_STATUS = 0
begin
if @managedBy=10000044
set @number=@number+@batch_number+'('+Convert(nvarchar,Convert(Integer,abs(@qty)))+')'+'; '
else
set @number=@number+@serial_number+'; '
if not @rate is null and @rate <> 0
set @lcPrice=@price*@rate
else
set @lcPrice=@price
set @managedByOfLastLine=@managedBy
FETCH NEXT FROM rec_Cursor into @rowID,@managedBy,@batch_number,@serial_number,@qty,@price,@rate
if @@FETCH_STATUS<>0 or @currentRow<>@rowID
begin
if @managedByOfLastLine=10000044
insert into #temp_Numbers_Return values(@docEntry,@currentRow,@managedByOfLastLine,substring(@number,0,len(@number)),NULL,@lcPrice)
else if @managedByOfLastLine=10000045
insert into #temp_Numbers_Return values(@docEntry,@currentRow,@managedByOfLastLine,NULL,substring(@number,0,len(@number)),@lcPrice)
else
insert into #temp_Numbers_Return values(@docEntry,@currentRow,@managedByOfLastLine,NULL,NULL,@lcPrice)
if @@FETCH_STATUS=0
begin
set @currentRow=@rowID
set @number=''
end
end
end
select * from #temp_Numbers_Return order by DocRow
close rec_Cursor
deallocate rec_Cursor
drop table #temp_Numbers
drop table #temp_Numbers_Return
END
GO
CREATE PROCEDURE _TmSp_UpdateCreateDateOnOINM
--WITH ENCRYPTION
AS
--OINV
UPDATE OINM
SET CreateDate = (SELECT T1.CreateDate FROM OINV T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 13
--ORIN
UPDATE OINM
SET CreateDate = (SELECT T1.CreateDate FROM ORIN T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 14
--ODLN
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM ODLN T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 15
--OPDN
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OPDN T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 20
--ORPD
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM ORPD T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 21
--OPCH
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OPCH T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 18
--ORPC
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM ORPC T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 19
--ORDN
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM ORDN T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 16
--OIPF
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OIPF T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 69
--OIGN
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OIGN T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 59
--OIGE
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OIGE T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 60
--OWTR
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OWTR T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 67
--OMRV
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OMRV T1 WHERE T1.DocEntry = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 162
--OWKO
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OWKO T1 WHERE T1.OrderNum = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND OINM.TransType = 68
--OINM (for stock posting and initial quantity)
UPDATE OINM
SET OINM.CreateDate = (SELECT T1.CreateDate FROM OJDT T1 WHERE T1.TransId = OINM.CreatedBy)
WHERE OINM.CreateDate is NULL AND (OINM.TransType = -2 OR OINM.TransType = 58)
GO
CREATE PROCEDURE _TmSp_UpgradeEDF
@ECM2tableName nvarchar(100),
@ECM3tableName nvarchar(100)
--With Encryption
AS
BEGIN
declare @ecm2CopyName nvarchar(100)
declare @ecm2TempName nvarchar(100)
declare @ecm3TempName nvarchar(100)
declare @execStr nvarchar(MAX)
set @ecm2CopyName = @ECM2tableName + '_COPY'
set @ecm2TempName = 'TEMP_' + @ECM2tableName
set @ecm3TempName = 'TEMP_' + @ECM3tableName
-- create copy of ECM2 table
if object_id(@ecm2TempName) is not null and object_id(@ecm2CopyName) is null
begin
set @execStr = 'SELECT * INTO ' + @ecm2CopyName + ' FROM ' + @ecm2TempName
exec(@execStr)
-- do not continue to upgrade (just copy table)
return
end
-- necessary checks before upgrade
-- ecm2CopyName (ECM2_COPY/AEC2_COPY) must exist. Copied from TEMP table
if object_id(@ecm2CopyName) is null
begin
-- do not continue to upgrade, TEMP table does not exist for "non fast upgrade" cases (if main table was empty or same structure, ...)
return
end
-- test for upgraded ECM2/ECM3 table structure (9.1 -> 9.2)
-- ECM2: removed field ActData
IF EXISTS(SELECT * FROM sys.columns WHERE Name = 'ActData' AND Object_ID = Object_ID(@ECM2tableName))
BEGIN
return 0
END
-- ECM3: new field LogData
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'LogData' AND Object_ID = Object_ID(@ECM3tableName))
BEGIN
return 0
END
-- if TEMP_ECM3 table does not exist then use ECM3
if object_id(@ecm3TempName) is null
begin
set @ecm3TempName = @ECM3tableName
end
-- upgrade steps
-- mark old ecm3 rows as 'Note'
set @execStr = 'update ' + @ECM3tableName +' set LogType = ''N'''
exec(@execStr)
-- copy ActData to ECM3 as 'Send'
set @execStr = 'insert into ' + @ECM3tableName + '(AbsEntry, LogNum, LogMessage, UserSign, CreateDate, UserSign2, UpdateDate, LogInstanc, CreateTS, UpdateTS, LogType, LogData, LogOpDate, LogOpTS)
select t0.AbsEntry, 1 + isNull(t3.nextLogNum,0),
ActMessage, UserSign, CreateDate, UserSign2, UpdateDate, LogInstanc, CreateTS, UpdateTS, ''S'', ActData, SendDate, SendTS
from ' + @ecm2CopyName + ' t0
left outer join
(
select absEntry, max (nextLogNum) as nextLogNum from
(select absEntry, isNull(max(logNum),0) as nextLogNum from ' + @ECM3tableName + ' group by AbsEntry
union all
select absEntry, isNull(max(logNum),0) as nextLogNum from ' + @ecm3TempName + ' group by AbsEntry
)as subQuery
group by AbsEntry
)
t3 on t3.AbsEntry = t0.AbsEntry
where t0.ActData is not null or t0.ActData2 is not null'
exec(@execStr)
-- copy ActData2 to ECM3 as 'Receive'
set @execStr = 'insert into ' + @ECM3tableName + '(AbsEntry, LogNum, LogMessage, UserSign, CreateDate, UserSign2, UpdateDate, LogInstanc, CreateTS, UpdateTS, LogType, LogData, LogOpDate, LogOpTS)
select t0.AbsEntry, 1 + isNull(t3.nextLogNum,0),
ActMessage, UserSign, CreateDate, UserSign2, UpdateDate, LogInstanc, CreateTS, UpdateTS, ''R'', ActData2, ReceivDate, NULL
from ' + @ecm2CopyName + ' t0
left outer join
(
select absEntry, max (nextLogNum) as nextLogNum from
(select absEntry, isNull(max(logNum),0) as nextLogNum from ' + @ECM3tableName + ' group by AbsEntry
union all
select absEntry, isNull(max(logNum),0) as nextLogNum from ' + @ecm3TempName + ' group by AbsEntry
)as subQuery
group by AbsEntry
)
t3 on t3.AbsEntry = t0.AbsEntry
where t0.ActData is not null or t0.ActData2 is not null'
exec(@execStr)
-- drop table
set @execStr = 'drop table ' + @ecm2CopyName
exec(@execStr)
END
GO
Create Procedure [dbo].[CRSP_GET_ITEM_GROUPS]
@Items nvarchar(4000)
As
Begin
create table #CR_TEMP_ITME_GOUPS(ItmsGrpNam nvarchar(40),ItemCode nvarchar(50));
create table #CR_TEMP_ITME_CODES_GROUPS(ItemCode nvarchar(50),ItmsGrpCod nvarchar(40));
exec ('insert into #CR_TEMP_ITME_CODES_GROUPS select t0.ItemCode,t0.ItmsGrpCod from OITM T0 where '+ @Items)
insert into #CR_TEMP_ITME_GOUPS
select t2.ItmsGrpNam,t3.ItemCode
from OITB t2 join
(select t1.* from (select t0.*, row_number() over (partition by t0.ItmsGrpCod order by t0.ItemCode) as rowno from #CR_TEMP_ITME_CODES_GROUPS t0) as t1 where t1.rowno=1) as t3
on t2.ItmsGrpCod=t3.ItmsGrpCod
Select * from #CR_TEMP_ITME_GOUPS;
drop table #CR_TEMP_ITME_CODES_GROUPS
drop table #CR_TEMP_ITME_GOUPS
End
GO
create Procedure [dbo].[CRSP_INVENTORY_TURNOVER_RATE]
@FromDate datetime,
@ToDate datetime,
@Items nvarchar(4000)
As
BEGIN
if object_id(N'tempdb..#CRSP_INVENTORY_ITEMS_LOCAL',N'U') is not null
DROP TABLE #CRSP_INVENTORY_ITEMS_LOCAL
CREATE TABLE #CRSP_INVENTORY_ITEMS_LOCAL (ItemCode nvarchar(50));
-- Insert selected result to temp table created above
declare @sqlQuery nvarchar(4000)
set @sqlQuery = N'INSERT INTO #CRSP_INVENTORY_ITEMS_LOCAL SELECT T0."ItemCode" FROM OITM T0 Where ' + @Items
exec sp_executesql @sqlQuery;
-- Fetch IssueQty from OILM instead of OIVL for scenario like "Return" based on "Delivery"
with cte1
as
(
select MessageID, DocEntry, TransType, DocLineNum, ActionType, T0.ItemCode, LocCode, BaseType, BaseAbsEnt, BaseLine, LocType,
case when ActionType = 1 then EffectQty
when ActionType = 2 then -EffectQty end as EffectQty
from oilm T0
Join #CRSP_INVENTORY_ITEMS_LOCAL T1 On T0."ItemCode" collate chinese_prc_ci_as = T1."ItemCode"
where AccumType=1 and ActionType in (1,2) and "DocDate" Between @FromDate And @ToDate
),
cte2
as
(
select
case when t1.TransType is null then t0.TransType else t0.BaseType end as TransType,
case when t1.DocEntry is null then t0.DocEntry else t0.BaseAbsEnt end as DocEntry,
case when t1.DocLineNum is null then t0.DocLineNum else t0.BaseLine end as DocLineNum,
t0.ItemCode, t0.LocCode, t0.LocType, t0.EffectQty
from cte1 t0 left join cte1 t1
on t0.BaseType = t1.TransType and t0.BaseAbsEnt = t1.DocEntry and t0.BaseLine = t1.DocLineNum
)
select
T1."LocType" as LocType,
T1."LocCode" as LocCode,
T1."ItemCode" as ItemCode,
T1."CloseStock" as CloseStock,
T4."OpenStock"as OpenStock,
T5."IssueQty" as IssueQty,
T6."LastDate" as LastDate,
T2."OnHand" as OnHandTotal,
T6."OnHand" as OnHand,
T6."MinStock" as MinStock,
T6."WhsCode" as WhsCode,
T3."WhsName" as WhsName,
T2."ItmsGrpCod" as ItmsGrpCod,
T2."ItemName" as ItemName,
T2."MinLevel" as MinLevel,
T2."ByWh" as ByWh,
T2."InvntryUom" as InvntryUom,
T2."LeadTime" as LeadTime,
T2."ItmsGrpNam" as ItmsGrpNam
from
(
Select "LocType", "LocCode", "ItemCode", Sum("InQty" - "OutQty") as "CloseStock"
From "OIVL"
Where "DocDate" <= @ToDate
Group by "LocType", "LocCode", "ItemCode"
) T1 /*CloseStock*/
Join
(
SELECT T0."ItemCode",
T0."ItmsGrpCod",
T0."ItemName",
T0."OnHand",
T0."MinLevel",
T0."ByWh",
T0."InvntryUom",
T0."LeadTime",
T1."ItmsGrpNam"
From "OITM" T0
Join "OITB" T1 On T0."ItmsGrpCod" = T1."ItmsGrpCod"
Join #CRSP_INVENTORY_ITEMS_LOCAL T2 On T0."ItemCode" collate chinese_prc_ci_as = T2."ItemCode"
) T2 /*OITM & OITB*/
on T1."ItemCode" = T2."ItemCode"
Join
(
SELECT T0."WhsCode", T0."WhsName"
From "OWHS" T0
) T3 /*OWHS*/ on T1."LocCode" = T3."WhsCode"
left join
(
Select "LocType", "LocCode", "ItemCode", Sum("InQty" - "OutQty") as "OpenStock"
From "OIVL"
Where "DocDate" < @FromDate
Group by "LocType", "LocCode", "ItemCode"
) T4 /*OpenStock*/
on T1."LocType" = T4."LocType"
and T1."LocCode" = T4."LocCode"
and T1."ItemCode" = T4."ItemCode"
left join
(
select t0.ItemCode, t0.LocCode, t0.LocType, sum(t0.EffectQty) as "IssueQty" from
(
select ItemCode, LocCode, LocType, sum(EffectQty) as EffectQty from cte2
group by TransType, DocEntry, DocLineNum,ItemCode, LocCode, LocType
having sum(EffectQty) < 0
) as t0
group by t0.ItemCode, t0.LocCode, t0.LocType
) T5 /*IssueQty*/
on T1."LocType" = T5."LocType"
and T1."LocCode" = T5."LocCode"
and T1."ItemCode" = T5."ItemCode"
left join
(
select T1."LocType", T1."LocCode", T1."ItemCode", T1."LastDate",
T2."WhsCode", T2."OnHand", T2."MinStock" from
(
Select "LocType", "LocCode", "ItemCode", max("DocDate") as "LastDate"
From "OIVL"
Where "InQty" > 0
Group By "LocType", "LocCode", "ItemCode"
) T1 /*LastDate*/
join
(
SELECT "ItemCode", "WhsCode", "OnHand", "MinStock" From "OITW"
) T2 /*OITW*/
on T1."ItemCode" = T2."ItemCode" and T1."LocCode" = T2."WhsCode"
) T6
on T1."LocType" = T6."LocType"
and T1."LocCode" = T6."LocCode"
and T1."ItemCode" = T6."ItemCode";
--DROP TABLE #CRSP_INVENTORY_ITEMS_LOCAL; /* cause client of rev48 disconnected */
END
GO
CREATE proc SBO_SP_PostTransactionNotice
@object_type nvarchar(30), -- SBO Object Type
@transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)
AS
begin
-- Return values
declare @error int -- Result (0 for no error)
declare @error_message nvarchar (200) -- Error string to be displayed
select @error = 0
select @error_message = N'Ok'
--------------------------------------------------------------------------------------------------------------------------------
-- ADD YOUR CODE HERE
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message
end
GO
CREATE proc SBO_SP_TransactionNotification
@object_type nvarchar(30), -- SBO Object Type
@transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)
AS
begin
-- Return values
declare @error int -- Result (0 for no error)
declare @error_message nvarchar (200) -- Error string to be displayed
select @error = 0
select @error_message = N'Ok'
--------------------------------------------------------------------------------------------------------------------------------
-- ADD YOUR CODE HERE
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message
end
GO
CREATE proc TmSp_adding_To_OITW
@SourceDB nvarchar(128) = NULL ,
@UtilityDB nvarchar(128) = NULL
as
begin
exec (' INSERT
INTO ['+ @SourceDB +'].[dbo].[OITW]
SELECT T1.*
FROM ['+ @UtilityDB +'].[dbo].[OITW] T1
LEFT OUTER JOIN ['+ @SourceDB +'].[dbo].[OITW] T0
ON T0.ItemCode = T1.ItemCode and T0.WhsCode = T1.WhsCode
WHERE T0.itemcode is NULL and T1.OnHand!=0 ')
end
GO
/********************************************************************************************************************/
/* TmSp_ChooseCIN4Correction- Retreives open corrections for correction invoice
*/
/********************************************************************************************************************/
CREATE PROCEDURE TmSp_ChooseCIN4Correction
@card nvarchar(100),
@Type nvarchar(2),
@curr nvarchar(4)
AS
if (@Type=N'I')
begin
if ( DATALENGTH(ltrim(rtrim(@curr)))>0 )
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OCIN
WHERE (NOT EXISTS
(select distinct(DocEntry) from CIN1
where DocEntry=OCIN.DocEntry AND
(Quantity<=0 OR
LineStatus=N'C' OR
OpenCreQty<>Quantity) ))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'I'
AND DocCur=@curr
ORDER BY DocNum
end
else
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OCIN
WHERE (NOT EXISTS
(select distinct(DocEntry) from CIN1
where DocEntry=OCIN.DocEntry AND
(Quantity<=0 OR
LineStatus=N'C' OR
OpenCreQty<>Quantity) ))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'I'
ORDER BY DocNum
end
end
else
begin
if ( DATALENGTH(ltrim(rtrim(@curr)))>0 )
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OCIN
WHERE (NOT EXISTS
(select distinct(DocEntry) from CIN1
where DocEntry=OCIN.DocEntry AND
LineStatus<>N'O'))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'S'
AND DocCur=@curr
ORDER BY DocNum
end
else
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OCIN
WHERE (NOT EXISTS
(select distinct(DocEntry) from CIN1
where DocEntry=OCIN.DocEntry AND
LineStatus<>N'O'))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'S'
ORDER BY DocNum
end
end
GO
/********************************************************************************************************************/
/* TmSp_ChooseINV4Correction - Retreives open invoices for correction invoice
*/
/********************************************************************************************************************/
CREATE PROCEDURE TmSp_ChooseINV4Correction
@card nvarchar(100),
@Type nvarchar(2),
@curr nvarchar(4)
AS
if (@Type=N'I')
begin
if ( DATALENGTH(ltrim(rtrim(@curr)))>0 )
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OINV
WHERE (NOT EXISTS
(select distinct(DocEntry) from INV1
where DocEntry=OINV.DocEntry AND
(Quantity<=0 OR
LineStatus=N'C' OR
OpenCreQty<>Quantity) ))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'I'
AND DocCur=@curr
ORDER BY DocNum
end
else
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OINV
WHERE (NOT EXISTS
(select distinct(DocEntry) from INV1
where DocEntry=OINV.DocEntry AND
(Quantity<=0 OR
LineStatus=N'C' OR
OpenCreQty<>Quantity) ))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'I'
ORDER BY DocNum
end
end
else
begin
if ( DATALENGTH(ltrim(rtrim(@curr)))>0 )
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OINV
WHERE (NOT EXISTS
(select distinct(DocEntry) from INV1
where DocEntry=OINV.DocEntry AND
LineStatus<>N'O'))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'S'
AND DocCur=@curr
ORDER BY DocNum
end
else
begin
select DocEntry, DocNum, DocDate, CardCode, Comments, docDueDate from OINV
WHERE (NOT EXISTS
(select distinct(DocEntry) from INV1
where DocEntry=OINV.DocEntry AND
LineStatus<>N'O'))
AND CANCELED<>N'Y'
AND cardCode = @card
AND DocType=N'S'
ORDER BY DocNum
end
end
GO
CREATE PROCEDURE TmSp_CorrActReport
@account AS NVARCHAR(255),
@dateFrom AS NVARCHAR(8),
@dateTo AS NVARCHAR(8)
AS
BEGIN
SELECT Account AS Acct,
SUM(Debit) AS DebitLC,
SUM(Credit) AS CreditLC,
SUM(SysDeb) AS DebitSC,
SUM(SysCred) AS CreditSC,
0 AS Sort
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Account = @account
AND Header.RefDate < CONVERT(DATETIME, @dateFrom, 112)
GROUP BY Account
UNION ALL
SELECT @account, 0, 0, 0, 0, 0
WHERE NOT EXISTS
(
SELECT 1
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Account = @account
AND Header.RefDate < CONVERT(DATETIME, @dateFrom, 112)
)
UNION ALL
SELECT Acct,
SUM(CreditLC) AS DebitLC,
SUM(DebitLC) AS CreditLC,
SUM(CreditSC) AS DebitSC,
SUM(DebitSC) AS CreditSC,
1 AS Sort
FROM
(
SELECT Lines.Account AS Acct,
SUM(Debit) AS DebitLC,
SUM(Credit) AS CreditLC,
SUM(SysDeb) AS DebitSC,
SUM(SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE (
ContraAct IN
(
SELECT CardCode
FROM OCRD
WHERE DebPayAcct = @account
UNION ALL
SELECT CardCode
FROM CRD3
WHERE AcctCode = @account
)
OR ContraAct = @account
) AND @account IN
(
SELECT Account
FROM JDT1
WHERE JDT1.TransId = Header.TransId
)
AND Account <> @account
AND Lines.TransId = Header.TransId
GROUP BY Account, Header.RefDate
HAVING Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
UNION ALL
-- Debit and credit are swapped intentionally
SELECT Lines1.Account AS Acct,
SUM(Lines2.Credit) AS DebitLC,
SUM(Lines2.Debit) AS CreditLC,
SUM(Lines2.SysCred) AS DebitSC,
SUM(Lines2.SysDeb) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, OJDT AS Header
WHERE Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines2.Account = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit < Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit > Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit < Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit > Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.Account
UNION ALL
SELECT Lines1.Account AS Acct,
SUM(Lines1.Debit) AS DebitLC,
SUM(Lines1.Credit) AS CreditLC,
SUM(Lines1.SysDeb) AS DebitSC,
SUM(Lines1.SysCred) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, OJDT AS Header
WHERE Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines2.Account = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit > Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit < Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit > Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit < Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.Account
UNION ALL
-- Debit and credit are swapped intentionally
SELECT Lines1.Account AS Acct,
SUM(-Lines3.Credit) AS DebitLC,
SUM(-Lines3.Debit) AS CreditLC,
SUM(-Lines3.SysCred) AS DebitSC,
SUM(-Lines3.SysDeb) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, JDT1 AS Lines3, OJDT AS Header
WHERE Lines2.TransId = Lines3.TransId
AND Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines3.ContraAct = Lines1.ShortName
AND Lines3.Account <> @account
AND Lines2.Account = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit > Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit < Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit > Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit < Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.Account
UNION ALL
SELECT 'Wrong correspondence 1' AS Acct,
SUM(Lines.Debit) AS DebitLC,
SUM(Lines.Credit) AS CreditLC,
SUM(Lines.SysDeb) AS DebitSC,
SUM(Lines.SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Lines.Account = @account
AND Lines.ContraAct IS NULL
AND NOT EXISTS
(
SELECT 1 FROM JDT1
WHERE Lines.TransId = JDT1.TransId
AND JDT1.ContraAct = Lines.ShortName
)
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Account
UNION ALL
SELECT 'Wrong correspondence 2' AS Acct,
SUM(Lines.Debit) AS DebitLC,
SUM(Lines.Credit) AS CreditLC,
SUM(Lines.SysDeb) AS DebitSC,
SUM(Lines.SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Lines.Account = @account
AND Lines.ContraAct IS NOT NULL
AND Lines.ContraAct <> ''
AND NOT EXISTS
(
SELECT 1 FROM JDT1
WHERE Lines.TransId = JDT1.TransId
AND JDT1.ShortName = Lines.ContraAct
)
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Account
) DUMMY
GROUP BY Acct
ORDER BY Sort, Acct
END
GO
CREATE PROCEDURE TmSp_CorrActReport_BP
@account AS NVARCHAR(255),
@dateFrom AS NVARCHAR(8),
@dateTo AS NVARCHAR(8)
AS
BEGIN
SELECT ShortName AS Acct,
SUM(Debit) AS DebitLC,
SUM(Credit) AS CreditLC,
SUM(SysDeb) AS DebitSC,
SUM(SysCred) AS CreditSC,
0 AS Sort
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND ShortName = @account
AND Header.RefDate < CONVERT(DATETIME, @dateFrom, 112)
GROUP BY ShortName
UNION ALL
SELECT @account, 0, 0, 0, 0, 0
WHERE NOT EXISTS
(
SELECT 1
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND ShortName = @account
AND Header.RefDate < CONVERT(DATETIME, @dateFrom, 112)
)
UNION ALL
SELECT Acct,
SUM(CreditLC) AS DebitLC,
SUM(DebitLC) AS CreditLC,
SUM(CreditSC) AS DebitSC,
SUM(DebitSC) AS CreditSC,
1 AS Sort
FROM
(
SELECT Account AS Acct,
SUM(Debit) AS DebitLC,
SUM(Credit) AS CreditLC,
SUM(SysDeb) AS DebitSC,
SUM(SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE ContraAct = @account
AND Lines.TransId = Header.TransId
GROUP BY Account, Header.RefDate
HAVING Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
UNION ALL
-- Debit and credit are swapped intentionally
SELECT Lines1.Account AS Acct,
SUM(Lines2.Credit) AS DebitLC,
SUM(Lines2.Debit) AS CreditLC,
SUM(Lines2.SysCred) AS DebitSC,
SUM(Lines2.SysDeb) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, OJDT AS Header
WHERE Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines2.ShortName = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit < Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit > Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit < Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit > Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.Account
UNION ALL
SELECT Lines1.Account AS Acct,
SUM(Lines1.Debit) AS DebitLC,
SUM(Lines1.Credit) AS CreditLC,
SUM(Lines1.SysDeb) AS DebitSC,
SUM(Lines1.SysCred) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, OJDT AS Header
WHERE Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines2.ShortName = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit > Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit < Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit > Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit < Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.Account
UNION ALL
-- Debit and credit are swapped intentionally
SELECT Lines1.Account AS Acct,
SUM(-Lines3.Credit) AS DebitLC,
SUM(-Lines3.Debit) AS CreditLC,
SUM(-Lines3.SysCred) AS DebitSC,
SUM(-Lines3.SysDeb) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, JDT1 AS Lines3, OJDT AS Header
WHERE Lines2.TransId = Lines3.TransId
AND Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines3.ContraAct = Lines1.ShortName
AND Lines3.ShortName <> @account
AND Lines2.ShortName = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit > Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit < Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit > Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit < Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.Account
UNION ALL
SELECT 'Wrong correspondence 1' AS Acct,
SUM(Lines.Debit) AS DebitLC,
SUM(Lines.Credit) AS CreditLC,
SUM(Lines.SysDeb) AS DebitSC,
SUM(Lines.SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Lines.ShortName = @account
AND (Lines.ContraAct IS NULL OR Lines.ContraAct = '')
AND NOT EXISTS
(
SELECT 1 FROM JDT1
WHERE Lines.TransId = JDT1.TransId
AND JDT1.ContraAct = Lines.ShortName
)
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Account
UNION ALL
SELECT 'Wrong correspondence 2' AS Acct,
SUM(Lines.Debit) AS DebitLC,
SUM(Lines.Credit) AS CreditLC,
SUM(Lines.SysDeb) AS DebitSC,
SUM(Lines.SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Lines.ShortName = @account
AND Lines.ContraAct IS NOT NULL
AND Lines.ContraAct <> ''
AND NOT EXISTS
(
SELECT 1 FROM JDT1
WHERE Lines.TransId = JDT1.TransId
AND JDT1.ShortName = Lines.ContraAct
)
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Account
) DUMMY
GROUP BY Acct
ORDER BY Sort, Acct
END
GO
CREATE PROCEDURE TmSp_CorrActReport_BP_Split
@account AS NVARCHAR(255),
@dateFrom AS NVARCHAR(8),
@dateTo AS NVARCHAR(8)
AS
BEGIN
SELECT ShortName AS Acct,
SUM(Debit) AS DebitLC,
SUM(Credit) AS CreditLC,
SUM(SysDeb) AS DebitSC,
SUM(SysCred) AS CreditSC,
0 AS Sort
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND ShortName = @account
AND Header.RefDate < CONVERT(DATETIME, @dateFrom, 112)
GROUP BY ShortName
UNION ALL
SELECT @account, 0, 0, 0, 0, 0
WHERE NOT EXISTS
(
SELECT 1
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND ShortName = @account
AND Header.RefDate < CONVERT(DATETIME, @dateFrom, 112)
)
UNION ALL
SELECT Acct,
SUM(CreditLC) AS DebitLC,
SUM(DebitLC) AS CreditLC,
SUM(CreditSC) AS DebitSC,
SUM(DebitSC) AS CreditSC,
1 AS Sort
FROM
(
SELECT ShortName AS Acct,
SUM(Debit) AS DebitLC,
SUM(Credit) AS CreditLC,
SUM(SysDeb) AS DebitSC,
SUM(SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE ContraAct = @account
AND Lines.TransId = Header.TransId
GROUP BY ShortName, Header.RefDate
HAVING Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
UNION ALL
-- Debit and credit are swapped intentionally
SELECT Lines1.ShortName AS Acct,
SUM(Lines2.Credit) AS DebitLC,
SUM(Lines2.Debit) AS CreditLC,
SUM(Lines2.SysCred) AS DebitSC,
SUM(Lines2.SysDeb) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, OJDT AS Header
WHERE Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines2.ShortName = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit < Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit > Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit < Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit > Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.ShortName
UNION ALL
SELECT Lines1.ShortName AS Acct,
SUM(Lines1.Debit) AS DebitLC,
SUM(Lines1.Credit) AS CreditLC,
SUM(Lines1.SysDeb) AS DebitSC,
SUM(Lines1.SysCred) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, OJDT AS Header
WHERE Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines2.ShortName = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit > Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit < Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit > Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit < Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.ShortName
UNION ALL
-- Debit and credit are swapped intentionally
SELECT Lines1.ShortName AS Acct,
SUM(-Lines3.Credit) AS DebitLC,
SUM(-Lines3.Debit) AS CreditLC,
SUM(-Lines3.SysCred) AS DebitSC,
SUM(-Lines3.SysDeb) AS CreditSC
FROM JDT1 AS Lines1, JDT1 AS Lines2, JDT1 AS Lines3, OJDT AS Header
WHERE Lines2.TransId = Lines3.TransId
AND Lines1.TransId = Lines2.TransId
AND Lines1.TransId = Header.TransId
AND Lines3.ContraAct = Lines1.ShortName
AND Lines3.ShortName <> @account
AND Lines2.ShortName = @account
AND Lines2.ContraAct = Lines1.ShortName
AND (Lines1.ContraAct IS NULL or Lines1.ContraAct = '')
AND ((Lines2.Credit > Lines1.Debit AND Lines2.Credit > 0) OR (Lines2.Credit < Lines1.Debit AND Lines2.Credit < 0) OR
(Lines2.Debit > Lines1.Credit AND Lines2.Debit > 0) OR (Lines2.Debit < Lines1.Credit AND Lines2.Debit < 0))
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Lines1.ShortName
UNION ALL
SELECT 'Wrong correspondence 1' AS Acct,
SUM(Lines.Debit) AS DebitLC,
SUM(Lines.Credit) AS CreditLC,
SUM(Lines.SysDeb) AS DebitSC,
SUM(Lines.SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Lines.ShortName = @account
AND (Lines.ContraAct IS NULL OR Lines.ContraAct = '')
AND NOT EXISTS
(
SELECT 1 FROM JDT1
WHERE Lines.TransId = JDT1.TransId
AND JDT1.ContraAct = Lines.ShortName
)
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Account
UNION ALL
SELECT 'Wrong correspondence 2' AS Acct,
SUM(Lines.Debit) AS DebitLC,
SUM(Lines.Credit) AS CreditLC,
SUM(Lines.SysDeb) AS DebitSC,
SUM(Lines.SysCred) AS CreditSC
FROM JDT1 AS Lines, OJDT AS Header
WHERE Header.TransId = Lines.TransId
AND Lines.ShortName = @account
AND Lines.ContraAct IS NOT NULL
AND Lines.ContraAct <> ''
AND NOT EXISTS
(
SELECT 1 FROM JDT1
WHERE Lines.TransId = JDT1.TransId
AND JDT1.ShortName = Lines.ContraAct
)
AND Header.RefDate
BETWEEN CONVERT(DATETIME, @dateFrom, 112)
AND CONVERT(DATETIME, @dateTo, 112)
GROUP BY Account
) DUMMY
GROUP BY Acct
ORDER BY Sort, Acct
END
优质生活从拆开始
浙公网安备 33010602011771号