SAP Business One 9.2: 系统存储过程之三
SAP
SAP Bysiness one 9.2在 示例 系统里 SQL Server 数据库名称： sap560 ,
以下是部分存储过程代码：
CREATE PROCEDURE TmSp_CorrActReport_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 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 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.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.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.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.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.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.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.Account = @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.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 [dbo].[TmSp_DocLineTypeLayout]
@DocKey@ INT,
@ObjectId@ INT
AS
------------------------------------------
DECLARE @object NVARCHAR(4000)
IF (@ObjectId@=13) SET @object = 'INV'
ELSE IF (@ObjectId@=14) SET @object = 'RIN'
ELSE IF (@ObjectId@=15) SET @object = 'DLN'
ELSE IF (@ObjectId@=16) SET @object = 'RDN'
ELSE IF (@ObjectId@=17) SET @object = 'RDR'
ELSE IF (@ObjectId@=18) SET @object = 'PCH'
ELSE IF (@ObjectId@=19) SET @object = 'RPC'
ELSE IF (@ObjectId@=20) SET @object = 'PDN'
ELSE IF (@ObjectId@=21) SET @object = 'RPD'
ELSE IF (@ObjectId@=22) SET @object = 'POR'
ELSE IF (@ObjectId@=23) SET @object = 'QUT'
ELSE IF (@ObjectId@=24) SET @object = 'RCT'
ELSE IF (@ObjectId@=25) SET @object = 'DPS'
ELSE IF (@ObjectId@=46) SET @object = 'VPM'
ELSE IF (@ObjectId@=58) SET @object = 'INM'
ELSE IF (@ObjectId@=59) SET @object = 'IGN'
ELSE IF (@ObjectId@=60) SET @object = 'IGE'
ELSE IF (@ObjectId@=67) SET @object = 'WTR'
ELSE IF (@ObjectId@=68) SET @object = 'WKO'
ELSE IF (@ObjectId@=69) SET @object = 'IPF'
ELSE IF (@ObjectId@=112) SET @object = 'DRF'
ELSE IF (@ObjectId@=132) SET @object = 'CIN'
ELSE IF (@ObjectId@=140) SET @object = 'PDF'
ELSE IF (@ObjectId@=162) SET @object = 'MRV'
ELSE IF (@ObjectId@=163) SET @object = 'CPI'
ELSE IF (@ObjectId@=164) SET @object = 'CPV'
ELSE IF (@ObjectId@=165) SET @object = 'CSI'
ELSE IF (@ObjectId@=166) SET @object = 'CSV'
ELSE IF (@ObjectId@=191) SET @object = 'SCL'
ELSE IF (@ObjectId@=198) SET @object = 'FCT'
ELSE IF (@ObjectId@=199) SET @object = 'MSN'
ELSE IF (@ObjectId@=202) SET @object = 'WOR'
ELSE IF (@ObjectId@=203) SET @object = 'DPI'
ELSE IF (@ObjectId@=204) SET @object = 'DPO'
ELSE IF (@ObjectId@=321) SET @object = 'ITR'
ELSE IF (@ObjectId@=140000009) SET @object = 'OEI'
ELSE IF (@ObjectId@=140000010) SET @object = 'IEI'
ELSE IF (@ObjectId@=540000006) SET @object = 'PQT'
ELSE IF (@ObjectId@=1470000113) SET @object = 'PRQ'
------------------------------------------
SELECT * INTO #DOC1 FROM RIN1 WHERE 1=2
SELECT * INTO #DOC10 FROM RIN10 WHERE 1=2
INSERT INTO #DOC1 EXEC ('SELECT * FROM '+@object+'1 WHERE DocEntry = '+@DocKey@)
INSERT INTO #DOC10 EXEC ('SELECT * FROM '+@object+'10 WHERE DocEntry = '+@DocKey@)
------------------------------------------
DECLARE @LineSeq INT
DECLARE @_LineNum INT
DECLARE @_LineSeq INT
DECLARE @LineType NCHAR
DECLARE @TotalLevel INT
CREATE TABLE #TempTab
( DocEntry INT,
LineNum INT,
_LineNum INT,
_LineSeq INT,
LineType NCHAR(1),
TotalLevel INT,
SubTotal NUMERIC(19, 6),
TotalFrgn NUMERIC(19, 6),
TotalSumSy NUMERIC(19, 6),
GTotal NUMERIC(19, 6),
GTotalFC NUMERIC(19, 6),
GTotalSC NUMERIC(19, 6),
VatSum NUMERIC(19, 6),
VatSumFrgn NUMERIC(19, 6),
VatSumSy NUMERIC(19, 6),
LineText NTEXT
)
INSERT INTO #TempTab(DocEntry, LineNum, _LineNum, _LineSeq, LineType, LineText) SELECT DocEntry, VisOrder, VisOrder, -1, LineType, '' FROM #DOC1
INSERT INTO #TempTab(DocEntry, LineNum, _LineNum, _LineSeq, LineType, LineText) SELECT DocEntry, -1, aftLinenum, LineSeq, LineType, LineText FROM #DOC10
-- UPDATE #TempTab SET LineText = '' WHERE LineType = 'S'
-- Update Total Level
UPDATE #TempTab SET TotalLevel = -1
DECLARE curTemp CURSOR FOR SELECT LineType,_LineNum, _LineSeq FROM #TempTab ORDER BY DocEntry, _LineNum, _LineSeq
SET @TotalLevel = 0
OPEN curTemp
FETCH curTemp INTO @LineType, @_LineNum, @_LineSeq
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@LineType = 'S')
BEGIN
UPDATE #TempTab SET TotalLevel = @TotalLevel WHERE _LineNum = @_LineNum AND _LineSeq = @_LineSeq
SET @TotalLevel = @TotalLevel + 1
END
ELSE
IF (@LineType = 'R')
BEGIN
SET @TotalLevel = 0
END
FETCH curTemp INTO @LineType, @_LineNum, @_LineSeq
END
CLOSE curTemp
DEALLOCATE curTemp
-- Update Total
DECLARE @d1 NUMERIC(19, 6)
DECLARE @d2 NUMERIC(19, 6)
DECLARE @d3 NUMERIC(19, 6)
DECLARE @d4 NUMERIC(19, 6)
DECLARE @d5 NU
MERIC(19, 6)
DECLARE @d6 NUMERIC(19, 6)
DECLARE @d7 NUMERIC(19, 6)
DECLARE @d8 NUMERIC(19, 6)
DECLARE @d9 NUMERIC(19, 6)
DECLARE @fromLineNum INT
DECLARE @level INT
SET @level = 0
WHILE @level < 10
BEGIN
SET @fromLineNum = 0
DECLARE cur CURSOR FOR SELECT LineType,_LineNum, _LineSeq FROM #TempTab WHERE TotalLevel = @level ORDER BY DocEntry, _LineNum, _LineSeq
OPEN cur
FETCH cur INTO @LineType, @_LineNum, @_LineSeq
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@LineType = 'S')
BEGIN
SELECT
@d1 = SUM(LineTotal),
@d2 = SUM(TotalFrgn),
@d3 = SUM(TotalSumSy),
@d4 = SUM(GTotal),
@d5 = SUM(GTotalFC),
@d6 = SUM(GTotalSC),
@d7 = SUM(VatSum),
@d8 = SUM(VatSumFrgn),
@d9 = SUM(VatSumSy)
FROM #DOC1
WHERE (@fromLineNum<=VisOrder) AND (VisOrder<=@_LineNum) AND (LineType='R')
UPDATE #TempTab
SET SubTotal = @d1,
TotalFrgn = @d2,
TotalSumSy =@d3,
GTotal = @d4,
GTotalFC = @d5,
GTotalSC = @d6,
VatSum = @d7,
VatSumFrgn =@d8,
VatSumSy = @d9
WHERE _LineNum = @_LineNum AND _LineSeq = @_LineSeq
SET @fromLineNum = @_LineNum + 1
END
FETCH cur INTO @LineType, @_LineNum, @_LineSeq
END
CLOSE cur
DEALLOCATE cur
SET @level = @level + 1
END
-- Output Result
SELECT * FROM #TempTab ORDER BY DocEntry, _LineNum, _LineSeq
DROP TABLE #TempTab
DROP TABLE #DOC1
DROP TABLE #DOC10
GO
CREATE procedure TmSp_GetDocVatTotals
@array nvarchar(4),
@absEntry int,
@totalField int,
@object nvarchar(4)
as
if (@totalField=0)
begin
exec ('SELECT
T1.Code,
T1.Name,
T0.VatPrcnt,
T0.EquVatPer,
0, 0, 0, 0, 0, 0 ,0, 0, 0, 0, 0, 0, 0
FROM [dbo].['+@array+'] T0 INNER JOIN [dbo].[OVTG] T1 ON T0.VatGroup = T1.Code
INNER JOIN [dbo].['+@object+'] T2 ON T0.DocEntry = T2.DocEntry
WHERE T0.DocEntry = '+@absEntry+'
GROUP BY T1.[Code], T1.[Name], T0.[VatPrcnt], T0.[EquVatPer]
ORDER BY T1.Code')
end
else if (@totalField=1)
begin
exec ('SELECT
T1.Code,
T1.Name,
T0.VatPrcnt,
T0.EquVatPer,
0, 0, 0, 0, 0, 0 ,0, 0, 0, 0, 0, 0, 0
FROM [dbo].['+@array+'] T0 INNER JOIN [dbo].[OVTG] T1 ON T0.VatGroup = T1.Code
INNER JOIN [dbo].['+@object+'] T2 ON T0.DocEntry = T2.DocEntry
WHERE T0.DocEntry = '+@absEntry+'
GROUP BY T1.[Code], T1.[Name], T0.[VatPrcnt], T0.[EquVatPer]
ORDER BY T1.Code')
end
else
begin
exec ('SELECT
T1.Code,
T1.Name,
T0.VatPrcnt,
T0.EquVatPer,
0, 0, 0, 0, 0, 0 ,0, 0, 0, 0, 0, 0, 0
FROM [dbo].['+@array+'] T0 INNER JOIN [dbo].[OVTG] T1 ON T0.VatGroup = T1.Code
INNER JOIN [dbo].['+@object+'] T2 ON T0.DocEntry = T2.DocEntry
WHERE T0.DocEntry = '+@absEntry+'
GROUP BY T1.[Code], T1.[Name], T0.[VatPrcnt], T0.[EquVatPer]
ORDER BY T1.Code')
end
GO
----------------------------------------------------------------------------------------------------------------------
-- This stored procedure is compatible with SAP Business One 2007.
----------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE TmSp_HU_PrepareTaxReportData @PeriodStartStr NVARCHAR(50), @PeriodEndStr NVARCHAR(50), @VatLimit INTEGER = 100000
AS
BEGIN
----------------------------------------
DECLARE @getReportedDocSumErase NVARCHAR(2000)
SET @getReportedDocSumErase = 'IF EXISTS (SELECT * FROM sys.objects WHERE Type = ''FN'' AND Name LIKE ''GetReportedDocSum'')
DROP FUNCTION GetReportedDocSum'
Exec(@getReportedDocSumErase)
----------------------------------------
DECLARE @getReportedDocSumCreate NVARCHAR(2000)
SET @getReportedDocSumCreate = 'CREATE FUNCTION dbo.GetReportedDocSum(@ObjType INTEGER, @DocSum NUMERIC(21, 6))
RETURNS NUMERIC(21, 6)
-- WITH SchemaBinding
AS
BEGIN
DECLARE @result NUMERIC(21, 6)
SET @result = (CASE WHEN @ObjType IN (13, 14, 15, 16, 165, 166, 203) THEN @DocSum
WHEN @ObjType IN (18, 19, 20, 21, 163, 164, 204) THEN -@DocSum
WHEN @ObjType = 30 THEN -@DocSum
WHEN @ObjType = 24 THEN @DocSum
WHEN @ObjType = 46 THEN @DocSum
ELSE @DocSum
END);
RETURN @result;
END'
Exec(@getReportedDocSumCreate)
------------------------------------------------------------------------------------------------------------------------
DECLARE @GetDocTypeNameErase NVARCHAR(2000)
SET @GetDocTypeNameErase = 'IF EXISTS (SELECT * FROM sys.objects WHERE Type = ''FN'' AND Name LIKE ''GetDocTypeName'')
DROP FUNCTION GetDocTypeName'
Exec(@GetDocTypeNameErase)
----------------------------------------
DECLARE @GetDocTypeNameCreate NVARCHAR(4000)
SET @GetDocTypeNameCreate = N'CREATE FUNCTION dbo.GetDocTypeName(@ObjType INTEGER)
RETURNS NVARCHAR(50)
-- WITH SchemaBinding
AS
BEGIN
DECLARE @name NVARCHAR(50)
SET @name = (CASE WHEN @ObjType = 13 THEN N''Kimen' + NCHAR(0x0151) + N' sz' + NCHAR(0x00E1) + N'mla'' -- Kimeno szamla
WHEN @ObjType = 14 THEN N''Kimen' + NCHAR(0x0151) + N' j' + NCHAR(0x00F3) + N'v' + NCHAR(0x00E1) + NCHAR(0x00ED) + N'r' + NCHAR(0x00E1) + N's'' -- Kimeno jovairas
WHEN @ObjType = 15 THEN N''Sz' + NCHAR(0x00E1) + N'll' + NCHAR(0x00ED) + N't' + NCHAR(0x00E1) + N's'' -- Szallitas
WHEN @ObjType = 16 THEN N''Vev' + NCHAR(0x0151) + N'i vissz' + NCHAR(0x00E1) + N'ru'' -- Vevoi visszaru
WHEN @ObjType = 165 THEN N''Kimen' + NCHAR(0x0151) + N' helyesb' + NCHAR(0x00ED) + N't' + NCHAR(0x0151) + N' sz' + NCHAR(0x00E1) + N'mla'' -- Kimeno helyesbito szamla
WHEN @ObjType = 166 THEN N''Kimen' + NCHAR(0x0151) + N' helyesb' + NCHAR(0x00ED) + N't' + NCHAR(0x0151) + N' sz' + NCHAR(0x00E1) + N'mla storn' + NCHAR(0x00F3) + N'ja'' -- Kimeno helyesbito szamla stornoja
WHEN @ObjType = 203 THEN N''Kimen' + NCHAR(0x0151) + N' el' + NCHAR(0x0151) + N'legsz' + NCHAR(0x00E1) + N'mla'' -- Kimeno elolegszamla
WHEN @ObjType = 18 THEN N''Bej' + NCHAR(0x00F6) + N'v' + NCHAR(0x0151) + N' sz' + NCHAR(0x00E1) + N'mla'' -- Bejovo szamla
WHEN @ObjType = 19 THEN N''Bej' + NCHAR(0x00F6) + N'v' + NCHAR(0x0151) + N' j' + NCHAR(0x00F3) + N'v' + NCHAR(0x00E1) + NCHAR(0x00ED) + N'r' + NCHAR(0x00E1) + N's'' -- Bejovo jovairas
WHEN @ObjType = 20 THEN N''' + NCHAR(0x00C1) + N'rube' + NCHAR(0x00E9) + N'rkez' + NCHAR(0x00E9) + N's'' -- Arubeerkezes
WHEN @ObjType = 21 THEN N''Sz' + NCHAR(0x00E1) + N'll' + NCHAR(0x00ED) + N't' + NCHAR(0x00F3) + N'i vissz' + NCHAR(0x00E1) + N'ru'' -- Szallitoi visszaru
WHEN @ObjType = 163 THEN N''Bej' + NCHAR(0x00F6) + N'v' + NCHAR(0x0151) + N' helyesb' + NCHAR(0x00ED) + N't' + NCHAR(0x0151) + N' sz' + NCHAR(0x00E1) + N'mla'' -- Bejovo helyesbito szamla
WHEN @ObjType = 164 THEN N''Bej' + NCHAR(0x00F6) + N'v' + NCHAR(0x0151) + N' helyesb' + NCHAR(0x00ED) + N't' + NCHAR(0x0151) + N' sz' + NCHAR(0x00E1) + N'mla storn' + NCHAR(0x00F3) + N'ja'' -- Bejovo helyesbito szamla stornoja
WHEN @ObjType = 204 THEN N''Bej' + NCHAR(0x00F6) + N'v' + NCHAR(0x0151) + N' el' + NCHAR(0x0151) + N'legsz' + NCHAR(0x00E1) + N'mla'' -- Bejovo elolegszamla
WHEN @ObjType = 30 THEN N''Napl' + NCHAR(0x00F3) + N'k' + NCHAR(0x00F6) + N'nyvel' + NCHAR(0x00E9) + N's'' -- Naplokonyveles
WHEN @ObjType = 24 THEN N''Bej' + NCHAR(0x00F6) + N'v' + NCHAR(0x0151) + N' fizet' + NCHAR(0x00E9) + N'sek'' -- Bejovo fizetesek
WHEN @ObjType = 46 THEN N''Kimen' + NCHAR(0x0151) + N' fizet' + NCHAR(0x00E9) + N'sek'' -- Kimeno fizetesek
ELSE NULL
END);
RETURN @name;
END'
Exec(@GetDocTypeNameCreate)
----------------------------------------
SET NOCOUNT ON
--------------------------------------------------------------------------------
-- 1. setup all parameters
DECLARE @PeriodStart DATETIME
DECLARE @PeriodEnd DATETIME
SET @PeriodStart = CONVERT(DATETIME, @PeriodStartStr, 120)
SET @PeriodEnd = CONVERT(DATETIME, @PeriodEndStr, 120)
IF (@PeriodStart < '2017-07
-01')
SET @VatLimit = 1000000
ELSE
SET @VatLimit = 100000
--------------------------------------------------------------------------------
-- 2. create temporary table where all calculated data are stored
IF EXISTS (SELECT * FROM sys.objects WHERE Type = 'U' AND Name LIKE 'ReportedDocLines')
DROP TABLE ReportedDocLines
CREATE TABLE dbo.ReportedDocLines (
DocType NVARCHAR(20) NOT NULL,
CommonTaxEntry INTEGER NULL,
TaxEntry INTEGER NOT NULL,
LineSeq INTEGER NOT NULL,
FlagOriginal NCHAR(2) NOT NULL,
BP NVARCHAR(20) NOT NULL,
BPName NVARCHAR(100) NULL,
BPLicTradNum NVARCHAR(32) NULL,
BPAdditionalID NVARCHAR(64) NULL,
BPUnifiedTaxID NVARCHAR(32) NULL,
ConnectedBP NVARCHAR(20) NULL,
ConnectedBPName NVARCHAR(100) NULL,
ConnectedBPLicTradNum NVARCHAR(32) NULL,
ConnectedBPAdditionalID NVARCHAR(64) NULL,
ConnectedBPUnifiedTaxID NVARCHAR(32) NULL,
NumAtCard NVARCHAR(100) NULL,
ObjType NVARCHAR(20) NOT NULL,
AbsEntry INTEGER NOT NULL,
DocNum INTEGER NOT NULL,
OrdinalNum INTEGER NOT NULL,
LineNum INTEGER NOT NULL,
ArrType INTEGER NOT NULL,
GrpNum INTEGER NOT NULL,
BaseObjType NVARCHAR(20) NOT NULL,
BaseAbsEntry INTEGER NOT NULL,
BaseDocNum INTEGER NULL,
BaseOrdinalNum INTEGER NOT NULL,
BaseLineNum INTEGER NOT NULL,
BaseArrType INTEGER NOT NULL,
BaseGrpNum INTEGER NOT NULL,
DocsInChain INTEGER NOT NULL DEFAULT(1),
TaxCode NVARCHAR(8) NOT NULL,
TaxType NCHAR(1) NOT NULL, -- Y = regular, A = acquisition, R = reverse
BaseSum NUMERIC(19,6) NOT NULL,
VatSum NUMERIC(19,6) NOT NULL,
DocBaseSum NUMERIC(19,6) NOT NULL,
DocVatSum NUMERIC(19,6) NOT NULL,
DocDate DATETIME NOT NULL,
VatDate DATETIME NOT NULL,
TaxDate DATETIME NOT NULL
--CONSTRAINT ReportedDocLines_PK_TaxEntry PRIMARY KEY(TaxEntry, LineSeq)
)
CREATE INDEX ReportedDocLines_IDX_BaseDoc
ON ReportedDocLines (BaseObjType, BaseAbsEntry, BaseLineNum, BaseArrType, BaseGrpNum)
INCLUDE (CommonTaxEntry, TaxEntry, LineSeq)
--------------------------------------------------------------------------------
-- 3. complete doc chains (same CommonTaxEntry) with all needed data
IF EXISTS (SELECT * FROM sys.objects WHERE Type = 'U' AND Name LIKE 'DocLinesInChain')
DROP TABLE DocLinesInChain
CREATE TABLE dbo.DocLinesInChain (
CommonTaxEntry INTEGER NULL,
TaxEntry INTEGER NOT NULL,
LineSeq INTEGER NOT NULL,
ObjType INTEGER NOT NULL,
AbsEntry INTEGER NOT NULL,
OrdinalNum INTEGER NOT NULL,
LineNum INTEGER NOT NULL,
ArrType INTEGER NOT NULL,
GrpNum INTEGER NOT NULL,
BaseObjType INTEGER NOT NULL,
BaseAbsEntry INTEGER NOT NULL,
BaseOrdinalNum INTEGER NOT NULL,
BaseLineNum INTEGER NOT NULL,
BaseArrType INTEGER NOT NULL,
BaseGrpNum INTEGER NOT NULL,
IsBased INTEGER NULL,
IsInPeriod INTEGER NULL DEFAULT(-1),
IsAboveVatLimit INTEGER NULL DEFAULT(-1)
CONSTRAINT DocLinesInChain_PK_TaxEntry PRIMARY KEY(TaxEntry, LineSeq)
)
--------------------------------------------------------------------------------
-- fix TAX1 issue with cancelling invoices including linked downpayments (SMS incident 47230)
IF EXISTS (SELECT * FROM sys.objects WHERE Type = 'U' AND Name LIKE 'copyTAX1')
DROP TABLE copyTAX1;
SELECT * INTO dbo.copyTAX1 FROM TAX1 WHERE AbsEntry = -1;
ALTER TABLE copyTAX1 ADD CONSTRAINT copyTAX1_PRIMARY PRIMARY KEY CLUSTERED (AbsEntry ASC, LineSeq ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE NONCLUSTERED INDEX copyTAX1_Base ON copyTAX1(BaseObjTyp, BaseAbs, BaseArrTyp, BaseLinNum, BaseGrpNum);
INSERT INTO copyTAX1 SELECT * FROM TAX1;
UPDATE tblLines
SET BaseObjTyp = tblBaseDocLine.BaseObjTyp, BaseAbs = tblBaseDocLine.BaseAbs, BaseArrTyp = 22, BaseLinNum = SrcLineNum
FROM
copyTAX1 tblLines
INNER JOIN
OTAX tblMain
ON tblMain.AbsEntry = tblLines.AbsEntry
INNER JOIN -- select only cancelling docs
B1_MarketingDocumentsView tblDocs
ON tblDocs.ObjType = tblMain.SrcObjType AND
tblDocs.DocEntry = tblMain.SrcObjAbs AND
tblDocs.CANCELED = 'C'
INNER JOIN -- cancelling docs are based only on 1 base doc, therefore following aggregation is OK
(SELECT AbsEntry, MAX(BaseObjTyp) BaseObjTyp, MAX(BaseAbs) BaseAbs
FROM copyTAX1
WHERE SrcArrType = 12
GROUP BY AbsEntry
) tblBaseDocLine
ON tblBaseDocLine.AbsEntry = tblMain.AbsEntry
WHERE tblLines.SrcArrType = 22
--------------------------------------------------------------------------------
-- 4. prepare standalone docs, which are not part of any doc chain
INSERT INTO ReportedDocLines
SELECT
(CASE WHEN tblMain.SrcObjType IN (13, 15, 203, 18, 20, 204) THEN 'standalone'
WHEN tblMain.SrcObjType IN (14, 16, 165, 166, 19, 21, 163, 164) THEN 'chain'
WHEN tblMain.SrcObjType IN (30, 24, 46) THEN 'tax booking'
ELSE '' END) AS DocType,
NULL, tblMain.AbsEntry, tblLine.LineSeq,
(CASE WHEN tblMain.SrcObjType IN (13, 15, 203, 18, 20, 204) THEN 'E'
WHEN tblMain.SrcObjType IN (14, 16, 165, 166, 19, 21, 163, 164) THEN 'KT'
ELSE '' END) AS FlagOriginal,
tblDocs.CardCode, tblDocs.CardName, tblDocs.LicTradNum, tblTheBP.AddID, tblTheBP.VatIdUnCmp,
tblSameBP.CardCode, tblSameBP.CardName, tblSameBP.LicTradNum, tblSameBP.AddID, tblSameBP.VatIdUnCmp, tblDocs.NumAtCard,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblDocs.DocNum, tblMain.OrdinLNum, tblLine.SrcLineNum, tblLine.SrcArrType, tblLine.SrcGrpNum,
-1, -1, NULL, -1, -1, -1, -1,
1,
tblLine.TaxCode, (CASE WHEN tblLine.IsAcq = 'Y' THEN 'A' ELSE tblLine.TaxType END),
(CASE WHEN tblLine.CrditDebit = 'C' THEN tblLine.BaseSum ELSE -tblLine.BaseSum END) AS BaseSum,
(CASE WHEN tblLine.CrditDebit = 'C' THEN tblLine.VatSum ELSE -tblLine.VatSum END) AS VatSum,
tblBigVat.DocBaseSum, tblBigVat.DocVatSum,
tblDocs.DocDate, tblDocs.VatDate, tblDocs.TaxDate
FROM
OTAX tblMain
INNER JOIN -- document lines, freights, ...
copyTAX1 tblLine
ON tblLine.AbsEntry = tblMain.AbsEntry AND
tblLine.IsAcq <> 'Y' AND -- exclude lines with acquisition tax
tblLine.TaxType <> 'R' AND -- exclude lines with reverse tax
tblLine.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
LEFT JOIN
OTAX tblCheckBase
ON tblCheckBase.SrcObjType = tblLine.BaseObjTyp AND
tblCheckBase.SrcObjAbs = tblLine.BaseAbs
INNER JOIN -- get doc. dates and only docs within reported period
B1_MarketingDocumentsView tblDocs
ON tblDocs.ObjType = tblMain.SrcObjType AND
tblDocs.DocEntry = tblMain.SrcObjAbs AND
tblDocs.VatDate >= @PeriodStart AND
tblDocs.VatDate <= @PeriodEnd
INNER JOIN -- only docs where doc total VAT >= 1000000 (@VatLimit)
(SELECT AbsEntry,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(BaseSum, 0) ELSE -IsNULL(BaseSum, 0) END) AS DocBaseSum,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS DocVatSum
FROM
copyTAX1
WHERE IsAcq <> 'Y' -- exclude lines with acquisition tax
AND TaxType <> 'R' -- exclude lines with reverse tax
AND TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY AbsEntry
HAVING ABS(SUM(CASE WHEN CrditDebit = 'C' THEN VatSum ELSE -VatSum END)) >= @VatLimit
) tblBigVat
ON tblBigVat.AbsEntry = tblMain.AbsEntry
LEFT JOIN -- only doc lines which are not a base for another doc line (see WHERE condition)
copyTAX1 tblChildLine
ON tblChildLine.BaseObjTyp = tblMain.SrcObjType AND
tblChildLine.BaseAbs = tblMain.SrcObjAbs AND
tblChildLine.BaseLinNum = tblLine.SrcLineNum AND
tblChildLine.BaseArrTyp = tblLine.SrcArrType AND
tblChildLine.BaseGrpNum = tblLine.SrcGrpNum
LEFT OUTER JOIN -- main BP, to access additional data
OCRD tblTheBP
ON tblTheBP.CardCode = tblDocs.CardCode
LEFT OUTER JOIN -- connected BP, so it is possible to group data per the same partner (who is customer and vendor at the same time)
OCRD tblSameBP
ON tblSameBP.ConnBP = tblDocs.CardCode
WHERE (tblChildLine.AbsEntry IS NULL
AND (tblLine.BaseObjTyp <= 0 OR tblLine.BaseAbs <= 0 OR tblLine.BaseLinNum < 0 OR -- there is no base doc or
tblCheckBase.AbsEntry IS NULL)) -- it is not VAT relevant (not in OTAX, TAX1 tables)
--PRINT ' 4. Standalone docs prepared';
--------------------------------------------------------------------------------
-- 4.5 prepare standalone docs, which are part a doc chain
INSERT INTO ReportedDocLines
SELECT
'standalone' AS DocType, NULL, tblMain.AbsEntry, tblLine.LineSeq, '',
tblDocs.CardCode, tblDocs.CardName, tblDocs.LicTradNum, tblTheBP.AddID, tblTheBP.VatIdUnCmp,
tblSameBP.CardCode, tblSameBP.CardName, tblSameBP.LicTradNum, tblSameBP.AddID, tblSameBP.VatIdUnCmp, tblDocs.NumAtCard,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblDocs.DocNum, tblMain.OrdinLNum, tblLine.SrcLineNum, tblLine.SrcArrType, tblLine.SrcGrpNum,
-1, -1, NULL, -1, -1, -1, -1,
1,
tblLine.TaxCode, (CASE WHEN tblLine.IsAcq = 'Y' THEN 'A' ELSE tblLine.TaxType END) AS TaxType,
(CASE WHEN tblLine.CrditDebit = 'C' THEN tblLine.BaseSum ELSE -tblLine.BaseSum END) AS BaseSum,
(CASE WHEN tblLine.CrditDebit = 'C' THEN tblLine.VatSum ELSE -tblLine.VatSum END) AS VatSum,
tblBigVat.DocBaseSum, tblBigVat.DocVatSum,
tblDocs.DocDate, tblDocs.VatDate, tblDocs.TaxDate
FROM
OTAX tblMain
INNER JOIN -- document lines, freights, ...
copyTAX1 tblLine
ON tblLine.AbsEntry = tblMain.AbsEntry AND
tblLine.IsAcq <> 'Y' AND -- exclude lines with acquisition tax
tblLine.TaxType <> 'R' AND -- exclude lines with reverse tax
tblLine.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
LEFT JOIN
ReportedDocLines tblStandaloneDocs
ON tblStandaloneDocs.TaxEntry = tblLine.AbsEntry AND
tblStandaloneDocs.LineSeq = tblLine.LineSeq
INNER JOIN -- get doc. dates and only docs within reported period
B1_MarketingDocumentsView tblDocs
ON tblDocs.ObjType = tblMain.SrcObjType AND
tblDocs.DocEntry = tblMain.SrcObjAbs AND
tblDocs.VatDate >= @PeriodStart AND
tblDocs.VatDate <= @PeriodEnd
INNER JOIN -- only docs where doc total VAT >= 1000000 (@VatLimit)
(SELECT AbsEntry,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(BaseSum, 0) ELSE -IsNULL(BaseSum, 0) END) AS DocBaseSum,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS DocVatSum
FROM
copyTAX1
WHERE IsAcq <> 'Y' -- exclude lines with acquisition tax
AND TaxType <> 'R' -- exclude lines with reverse tax
AND TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY AbsEntry
HAVING ABS(SUM(CASE WHEN CrditDebit = 'C' THEN VatSum ELSE -VatSum END)) >= @VatLimit
) tblBigVat
ON tblBigVat.AbsEntry = tblMain.AbsEntry
LEFT OUTER JOIN -- main BP, to access additional data
OCRD tblTheBP
ON tblTheBP.CardCode = tblDocs.CardCode
LEFT OUTER JOIN -- connected BP, so it is possible to group data per the same partner (who is customer and vendor at the same time)
OCRD tblSameBP
ON tblSameBP.ConnBP = tblDocs.CardCode
WHERE tblStandaloneDocs.AbsEntry IS NULL -- exclude pure standalone docs
AND tblMain.SrcObjType IN (13, 15, 203, 18, 20, 204); -- only main docs (not reversals) should be included:
-- <-- "in spite of a document which belongs to the report period and corrected or canceled with CM or CRI should show in this area"
--PRINT ' 4.5 Standalone docs from chains prepared';
--------------------------------------------------------------------------------
-- 5. prepare docs in chain
WITH
DocsInChain(CommonTaxEntry, TaxEntry, LineSeq,
ObjType, AbsEntry, OrdinalNum, LineNum, ArrType, GrpNum,
BaseObjType, BaseAbsEntry, BaseOrdinalNum, BaseLineNum, BaseArrType, BaseGrpNum, IsBased)
AS
(SELECT tblMain.AbsEntry, tblMain.AbsEntry, tblDocLines.LineSeq,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblMain.OrdinLNum, tblDocLines.SrcLineNum, tblDocLines.SrcArrType, tblDocLines.SrcGrpNum,
tblDocLines.BaseObjTyp, tblDocLines.BaseAbs, -1, tblDocLines.BaseLinNum, tblDocLines.BaseArrTyp, tblDocLines.BaseGrpNum, 0
FROM
OTAX tblMain
INNER JOIN -- doc lines which are not based on anything
copyTAX1 tblDocLines
ON tblDocLines.AbsEntry = tblMain.AbsEntry AND
tblDocLines.IsAcq <> 'Y' AND -- exclude lines with acquisition tax
tblDocLines.TaxType <> 'R' AND -- exclude lines with reverse tax
tblDocLines.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
LEFT JOIN -- for downpayments, BaseAbs is set to an existing number, but that doc is not in OTAX/TAX1 (it is not tax relevant)
OTAX tblCheckBase
ON tblCheckBase.SrcObjType = tblDocLines.BaseObjTyp AND
tblCheckBase.SrcObjAbs = tblDocLines.BaseAbs
WHERE (tblDocLines.BaseObjTyp <= 0 OR --tblDocLines.BaseAbs <= 0 OR tblDocLines.BaseLinNum < 0 OR -- there is no base doc or
tblCheckBase.AbsEntry IS NULL) -- it is not VAT relevant (not in OTAX, TAX1 tables)
UNION ALL -- recursive part follows
SELECT tblBaseDoc.CommonTaxEntry, tblDocLines.AbsEntry, tblDocLines.LineSeq,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblMain.OrdinLNum, tblDocLines.SrcLineNum, tblDocLines.SrcArrType, tblDocLines.SrcGrpNum,
tblBaseDoc.ObjType, tblBaseDoc.AbsEntry, tblBaseDoc.OrdinalNum, tblBaseDoc.LineNum, tblBaseDoc.ArrType, tblBaseDoc.GrpNum, 1
FROM
DocsInChain tblBaseDoc -- recursion
INNER JOIN -- add/append target doc lines for all found doc lines (currently found, in current iteration), this is a recursion hook
copyTAX1 tblDocLines
ON tblDocLines.BaseObjTyp = tblBaseDoc.ObjType AND
tblDocLines.BaseAbs = tblBaseDoc.AbsEntry AND
tblDocLines.BaseLinNum = tblBaseDoc.LineNum AND
tblDocLines.BaseArrTyp = tblBaseDoc.ArrType AND
tblDocLines.BaseGrpNum = tblBaseDoc.GrpNum AND
tblDocLines.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
INNER JOIN -- join needed doc data
OTAX tblMain
ON tblMain.AbsEntry = tblDocLines.AbsEntry
)
INSERT INTO DocLinesInChain(CommonTaxEntry, TaxEntry, LineSeq, ObjType, AbsEntry, OrdinalNum, LineNum, ArrType, GrpNum,
BaseObjType, BaseAbsEntry, BaseOrdinalNum, BaseLineNum, BaseArrType, BaseGrpNum, IsBased)
SELECT * FROM DocsInChain
--PRINT ' 5. Docs in chains prepared';
--------------------------------------------------------------------------------
-- 6. update "IsInPeriod", "IsAboveVatLimit" flag for docs in chain
UPDATE tblMain
SET IsInPeriod = (CASE WHEN tblDocs.VatDate >= @PeriodStart AND tblDocs.VatDate <= @PeriodEnd THEN 1 ELSE 0 END),
IsAboveVatLimit = (CASE WHEN ABS(VatSum) >= @VatLimit THEN 1 ELSE 0 END)
FROM
DocLinesInChain tblMain
INNER JOIN
B1_MarketingDocumentsView tblDocs
ON tblDocs.ObjType = tblMain.ObjType AND
tblDocs.DocEntry = tblMain.AbsEntry
INNER JOIN
(SELECT AbsEntry, SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS VatSum
FROM copyTAX1
WHERE IsAcq <> 'Y' -- exclude lines with acquisition tax
AND TaxType <> 'R' -- exclude lines with reverse tax
AND TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY AbsEntry
) tblDocVatSums
ON tblDocVatSums.AbsEntry = tblMain.TaxEntry
--PRINT ' 6. Docs in chains updated';
--------------------------------------------------------------------------------
-- 6.5 update "IsAboveVatLimit" flag for docs in chain, when chain total VAT >= 1000000, but each particular doc VAT <= 1000000
UPDATE tblMain
SET IsAboveVatLimit = 1
FROM
DocLinesInChain tblMain
INNER JOIN
(SELECT tblMainChains.CommonTaxEntry
FROM
(SELECT DISTINCT CommonTaxEntry, TaxEntry
FROM
DocLinesInChain
WHERE ObjType IN (13, 165, 166, 203, 19, 163, 164, 204)
) tblMainChains
INNER JOIN
(SELECT AbsEntry, SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS VatSum
FROM copyTAX1
WHERE IsAcq <> 'Y' -- exclude lines with acquisition tax
AND TaxType <> 'R' -- exclude lines with reverse tax
AND TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY AbsEntry
) tblDocVatSums
ON tblDocVatSums.AbsEntry = tblMainChains.TaxEntry
GROUP BY tblMainChains.CommonTaxEntry
HAVING ABS(SUM(tblDocVatSums.VatSum)) >= @VatLimit
) tblChainDocs
ON tblChainDocs.CommonTaxEntry = tblMain.CommonTaxEntry
--PRINT ' 6.5 Docs in chains updated (chain total)';
--------------------------------------------------------------------------------
-- 7. calc docs in chain relevant for reported period
INSERT INTO ReportedDocLines
SELECT 'chain' AS DocType, tblMain.CommonTaxEntry, tblMain.TaxEntry, tblMain.LineSeq,
(CASE WHEN tblMain.IsBased = 0 THEN 'E' WHEN tblMain.IsInPeriod = 1 THEN 'KT' ELSE 'K' END) AS FlagOriginal,
tblDocs.CardCode, tblDocs.CardName, tblDocs.LicTradNum, tblTheBP.AddID, tblTheBP.VatIdUnCmp,
tblSameBP.CardCode, tblSameBP.CardName, tblSameBP.LicTradNum, tblSameBP.AddID, tblSameBP.VatIdUnCmp, tblDocs.NumAtCard,
tblMain.ObjType, tblMain.AbsEntry, tblDocs.DocNum, tblMain.OrdinalNum, tblMain.LineNum, tblMain.ArrType, tblMain.GrpNum,
tblMain.BaseObjType, tblMain.BaseAbsEntry, tblBaseDocs.DocNum, tblMain.BaseOrdinalNum, tblMain.BaseLineNum, tblMain.BaseArrType, tblMain.BaseGrpNum,
tblChains.CountDocsInChain,
tblDocLine.TaxCode, (CASE WHEN tblDocLine.IsAcq = 'Y' THEN 'A' ELSE tblDocLine.TaxType END) AS TaxType,
(CASE WHEN tblDocLine.CrditDebit = 'C' THEN tblDocLine.BaseSum ELSE -tblDocLine.BaseSum END) AS BaseSum,
(CASE WHEN tblDocLine.CrditDebit = 'C' THEN tblDocLine.VatSum ELSE -tblDocLine.VatSum END) AS VatSum,
tblDocSum.DocBaseSum, tblDocSum.DocVatSum,
tblDocs.DocDate, tblDocs.VatDate, tblDocs.TaxDate
FROM
DocLinesInChain tblMain
INNER JOIN -- at least 1 doc in chain must be within reported perdiod, and at least 1 doc in chain must have VAT >= 1000000 (@VatLimit)
(SELECT CommonTaxEntry, COUNT(DISTINCT TaxEntry) CountDocsInChain
FROM
DocLinesInChain
GROUP BY CommonTaxEntry
HAVING MAX(IsBased) > 0
AND MAX(IsInPeriod) > 0
AND MAX(IsAboveVatLimit) > 0
) tblChains
ON tblChains.CommonTaxEntry = tblMain.CommonTaxEntry
INNER JOIN
copyTAX1 tblDocLine
ON tblDocLine.AbsEntry = tblMain.TaxEntry AND
tblDocLine.LineSeq = tblMain.LineSeq AND
tblDocLine.IsAcq <> 'Y' AND -- exclude lines with acquisition tax
tblDocLine.TaxType <> 'R' AND -- exclude lines with reverse tax
tblDocLine.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
INNER JOIN -- calculate total doc VAT
(SELECT AbsEntry,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(BaseSum, 0) ELSE -IsNULL(BaseSum, 0) END) AS DocBaseSum,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS DocVatSum
FROM
copyTAX1
WHERE IsAcq <> 'Y' -- exclude lines with acquisition tax
AND TaxType <> 'R' -- exclude lines with reverse tax
AND TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY AbsEntry
) tblDocSum
ON tblDocSum.AbsEntry = tblMain.TaxEntry
LEFT JOIN -- get doc. data
B1_MarketingDocumentsView tblDocs
ON tblDocs.ObjType = tblMain.ObjType AND
tblDocs.DocEntry = tblMain.AbsEntry
LEFT JOIN -- get base doc. data
B1_MarketingDocumentsView tblBaseDocs
ON tblBaseDocs.ObjType = tblMain.BaseObjType AND
tblBaseDocs.DocEntry = tblMain.BaseAbsEntry
LEFT OUTER JOIN -- main BP, to access additional data
OCRD tblTheBP
ON tblTheBP.CardCode = tblDocs.CardCode
LEFT JOIN -- connected BP, so it is possible to group data per the same partner (who is customer and vendor at the same time)
OCRD tblSameBP
ON tblSameBP.ConnBP = tblDocs.CardCode
--PRINT ' 7. Docs in chains transferred to result';
--------------------------------------------------------------------------------
-- 8. standalone AP docs which were skipped because of low doc VAT sum (but total BP VAT sum >= 1000000)
INSERT INTO ReportedDocLines
SELECT
'rest AP' AS DocType, NULL, tblMain.AbsEntry, tblLine.LineSeq,
(CASE WHEN tblMain.SrcObjType IN (13, 15, 203, 18, 20, 204) THEN 'E'
WHEN tblMain.SrcObjType IN (14, 16, 165, 166, 19, 21, 163, 164) THEN 'KT'
ELSE '' END) AS FlagOriginal,
tblDocs.CardCode, tblDocs.CardName, tblDocs.LicTradNum, tblTheBP.AddID, tblTheBP.VatIdUnCmp,
tblSameBP.CardCode, tblSameBP.CardName, tblSameBP.LicTradNum, tblSameBP.AddID, tblSameBP.VatIdUnCmp, tblDocs.NumAtCard,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblDocs.DocNum, tblMain.OrdinLNum, tblLine.SrcLineNum, tblLine.SrcArrType, tblLine.SrcGrpNum,
-1, -1, NULL, -1, -1, -1, -1,
1,
tblLine.TaxCode, (CASE WHEN tblLine.IsAcq = 'Y' THEN 'A' ELSE tblLine.TaxType END) AS TaxType,
(CASE WHEN tblLine.CrditDebit = 'C' THEN tblLine.BaseSum ELSE -tblLine.BaseSum END) AS BaseSum,
(CASE WHEN tblLine.CrditDebit = 'C' THEN tblLine.VatSum ELSE -tblLine.VatSum END) AS VatSum,
tblBigVat.DocBaseSum, tblBigVat.DocVatSum,
tblDocs.DocDate, tblDocs.VatDate, tblDocs.TaxDate
FROM
OTAX tblMain
INNER JOIN -- document lines, freights, ...
copyTAX1 tblLine
ON tblLine.AbsEntry = tblMain.AbsEntry AND
tblLine.IsAcq <> 'Y' AND -- exclude lines with acquisition tax
tblLine.TaxType <> 'R' AND -- exclude lines with reverse tax
tblLine.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
LEFT JOIN -- exclude already reported docs/chains from this calculation (see WHERE condition)
ReportedDocLines tblAlreadyReported
ON tblAlreadyReported.TaxEntry = tblLine.AbsEntry AND
tblAlreadyReported.LineSeq = tblLine.LineSeq
INNER JOIN -- get doc. dates and only docs within reported period
B1_MarketingDocumentsView tblDocs
ON tblDocs.ObjType = tblMain.SrcObjType AND
tblDocs.DocEntry = tblMain.SrcObjAbs AND
tblDocs.VatDate >= @PeriodStart AND
tblDocs.VatDate <= @PeriodEnd
INNER JOIN -- BP total VAT per reported period >= 1000000 (@VatLimit)
(SELECT tblDocsBigBP.LicTradNum AS LicTradNum,
SUM(CASE WHEN tblLineBigBP.CrditDebit = 'C' THEN tblLineBigBP.VatSum ELSE -tblLineBigBP.VatSum END) AS LicTradNumVat
FROM
copyTAX1 tblLineBigBP
INNER JOIN
OTAX tblMainBigBP
ON tblMainBigBP.AbsEntry = tblLineBigBP.AbsEntry
INNER JOIN
B1_MarketingDocumentsView tblDocsBigBP
ON tblDocsBigBP.ObjType = tblMainBigBP.SrcObjType AND
tblDocsBigBP.DocEntry = tblMainBigBP.SrcObjAbs AND
tblDocsBigBP.VatDate >= @PeriodStart AND
tblDocsBigBP.VatDate <= @PeriodEnd
WHERE tblLineBigBP.IsAcq <> 'Y' -- exclude lines with acquisition tax
AND tblLineBigBP.TaxType <> 'R' -- exclude lines with reverse tax
AND tblMainBigBP.SrcObjType IN (18, 19, 20, 21, 163, 164, 204) -- only AP side
GROUP BY tblDocsBigBP.LicTradNum
HAVING ABS(SUM(CASE WHEN tblLineBigBP.CrditDebit = 'C' THEN tblLineBigBP.VatSum ELSE -tblLineBigBP.VatSum END)) >= @VatLimit
) tblBigBP
ON tblBigBP.LicTradNum = tblDocs.LicTradNum
INNER JOIN -- total doc VAT
(SELECT AbsEntry,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(BaseSum, 0) ELSE -IsNULL(BaseSum, 0) END) AS DocBaseSum,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS DocVatSum
FROM
copyTAX1
WHERE IsAcq <> 'Y' -- exclude lines with acquisition tax
AND TaxType <> 'R' -- exclude lines with reverse tax
AND TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY AbsEntry
) tblBigVat
ON tblBigVat.AbsEntry = tblMain.AbsEntry
LEFT OUTER JOIN -- main BP, to access additional data
OCRD tblTheBP
ON tblTheBP.CardCode = tblDocs.CardCode
LEFT OUTER JOIN -- connected BP, so it is possible to group data per the same partner (who is customer and vendor at the same time)
OCRD tblSameBP
ON tblSameBP.ConnBP = tblDocs.CardCode
WHERE tblAlreadyReported.TaxEntry IS NULL
AND tblMain.SrcObjType IN (18, 19, 20, 21, 163, 164, 204) -- 204 = Downpayment (outgoing)
--PRINT ' 8. Remaining standalone AP docs prepared';
--------------------------------------------------------------------------------
-- 10. prepare Journal Entries
INSERT INTO ReportedDocLines
SELECT 'tax booking' AS DocType, NULL, tblMain.AbsEntry, tblLine.LineSeq, '',
IsNULL(tblDocs.CardCode, ''), tblDocs.CardName, tblDocs.LicTradNum, tblTheBP.AddID, tblTheBP.VatIdUnCmp,
tblSameBP.CardCode, tblSameBP.CardName, tblSameBP.LicTradNum, tblSameBP.AddID, tblSameBP.VatIdUnCmp, tblDocs.NumAtCard,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblDocs.DocNum, tblMain.OrdinLNum, tblLine.SrcLineNum, tblLine.SrcArrType, tblLine.SrcGrpNum,
-1, -1, NULL, -1, -1, -1, -1,
1,
tblLine.TaxCode, (CASE WHEN tblLine.IsAcq = 'Y' THEN 'A' ELSE tblLine.TaxType END) AS TaxType,
(CASE WHEN tblLine.CrditDebit = 'C' THEN IsNULL(tblLine.BaseSum, 0) ELSE -IsNULL(tblLine.BaseSum, 0) END) AS BaseSum,
(CASE WHEN tblLine.CrditDebit = 'C' THEN IsNULL(tblLine.VatSum, 0) ELSE -IsNULL(tblLine.VatSum, 0) END
) AS VatSum,
tblBigVat.DocBaseSum,
tblBigVat.DocVatSum,
tblDocs.DocDate, tblDocs.VatDate, tblDocs.TaxDate
FROM
copyTAX1 tblLine
INNER JOIN
OTAX tblMain
ON tblMain.AbsEntry = tblLine.AbsEntry AND
tblMain.SrcObjType = 30 -- only Journal Ent. (30)
INNER JOIN -- get doc. dates and only docs within reported period
B1_VatView tblDocs
ON tblDocs.SrcObjType = tblMain.SrcObjType AND
tblDocs.SrcObjAbs = tblMain.SrcObjAbs AND
tblDocs.VatDate >= @PeriodStart AND
tblDocs.VatDate <= @PeriodEnd
INNER JOIN -- only docs where doc total VAT >= 1000000 (@VatLimit)
(SELECT tblLine.AbsEntry,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(BaseSum, 0) ELSE -IsNULL(BaseSum, 0) END) AS DocBaseSum,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS DocVatSum
FROM
copyTAX1 tblLine
WHERE tblLine.IsAcq <> 'Y' -- exclude lines with acquisition tax
AND tblLine.TaxType <> 'R' -- exclude lines with reverse tax
AND tblLine.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY tblLine.AbsEntry
) tblBigVat
ON tblBigVat.AbsEntry = tblMain.AbsEntry
LEFT OUTER JOIN -- main BP, to access additional data
OCRD tblTheBP
ON tblTheBP.CardCode = tblDocs.CardCode
LEFT OUTER JOIN -- connected BP, so it is possible to group data per the same partner (who is customer and vendor at the same time)
OCRD tblSameBP
ON tblSameBP.ConnBP = tblDocs.CardCode
WHERE tblLine.IsAcq <> 'Y' -- exclude lines with acquisition tax
AND tblLine.TaxType <> 'R' -- exclude lines with reverse tax
--PRINT ' 10. Journal Entries prepared';
--------------------------------------------------------------------------------
-- 11. prepare Payments
INSERT INTO ReportedDocLines
SELECT 'tax booking' AS DocType, NULL, tblMain.AbsEntry, tblLine.LineSeq, '',
'', NULL, NULL, NULL,
tblDocs.LicTradNum,
NULL, NULL, NULL, NULL, NULL, NULL,
tblMain.SrcObjType, tblMain.SrcObjAbs, tblDocs.DocEntry, tblMain.OrdinLNum, tblLine.SrcLineNum, tblLine.SrcArrType, tblLine.SrcGrpNum,
-1, -1, NULL, -1, -1, -1, -1,
1,
tblLine.TaxCode, (CASE WHEN tblLine.IsAcq = 'Y' THEN 'A' ELSE tblLine.TaxType END) AS TaxType,
(CASE WHEN tblLine.CrditDebit = 'C' THEN IsNULL(tblLine.BaseSum, 0) ELSE -IsNULL(tblLine.BaseSum, 0) END) AS BaseSum,
(CASE WHEN tblLine.CrditDebit = 'C' THEN IsNULL(tblLine.VatSum, 0) ELSE -IsNULL(tblLine.VatSum, 0) END) AS VatSum,
tblBigVat.DocBaseSum,
tblBigVat.DocVatSum,
tblDocs.DocDate, tblDocs.VatDate, tblDocs.TaxDate
FROM
copyTAX1 tblLine
INNER JOIN
OTAX tblMain
ON tblMain.AbsEntry = tblLine.AbsEntry AND
tblMain.SrcObjType IN (24, 46) -- only Payments (24 = incoming, 46 = outgoing)
INNER JOIN -- payments data and selection
(SELECT ObjType, DocEntry, DocDate, VatDate, TaxDate,
NULL AS LicTradNum
FROM OVPM
WHERE DocType = 'A'
AND ApplyVAT = 'Y'
AND VatDate >= @PeriodStart
AND VatDate <= @PeriodEnd
UNION ALL
SELECT ObjType, DocEntry, DocDate, VatDate, TaxDate,
NULL AS LicTradNum
FROM ORCT
WHERE DocType = 'A'
AND ApplyVAT = 'Y'
AND VatDate >= @PeriodStart
AND VatDate <= @PeriodEnd
) tblDocs
ON tblDocs.ObjType = tblMain.SrcObjType AND
tblDocs.DocEntry = tblMain.SrcObjAbs
INNER JOIN -- only docs where doc total VAT >= 1000000 (@VatLimit)
(SELECT tblLine.AbsEntry,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(BaseSum, 0) ELSE -IsNULL(BaseSum, 0) END) AS DocBaseSum,
SUM(CASE WHEN CrditDebit = 'C' THEN IsNULL(VatSum, 0) ELSE -IsNULL(VatSum, 0) END) AS DocVatSum
FROM
copyTAX1 tblLine
WHERE tblLine.IsAcq <> 'Y' -- exclude lines with acquisition tax
AND tblLine.TaxType <> 'R' -- exclude lines with reverse tax
AND tblLine.TaxType <> 'D' -- exclude lines with deferred tax (temporary fix, util 2017-07-01 legal change)
GROUP BY tblLine.AbsEntry
) tblBigVat
ON tblBigVat.AbsEntry = tblMain.AbsEntry
WHERE tblLine.IsAcq <> 'Y' -- exclude lines with acquisition tax
AND tblLine.TaxType <> 'R' -- exclude lines with reverse tax
--PRINT ' 11. Payments prepared';
--------------------------------------------------------------------------------
-- release resources
DROP TABLE copyTAX1;
SET NOCOUNT OFF;
--------------------------------------------------------------------------------
-- 12. print result data (EN and HU versions of headers are included)
SELECT
--ConnectedBPLicTradNum AS "Vendor Tax ID", ConnectedBP AS "Vendor Code", ConnectedBPName AS "Vendor Name",
--ConnectedBPAdditionalID AS "Vendor Add. ID", ConnectedBPUnifiedTaxID AS "Vendor Unified Tax ID",
--BPLicTradNum AS "Customer Tax ID", BP AS "Customer Code", BPName AS "Customer Name",
--BPAdditionalID AS "Customer Add. ID", BPUnifiedTaxID AS "Customer Unified Tax ID",
--RptType AS "Report Type", CAST(PageType AS INTEGER) AS "Page Type", DocChain, DocType AS "Doc. Type",
--VendorRefNum AS "Vendor Ref. Num.", DocNum AS "Doc Number", CorrType AS "Correction Type",
--BaseDocNum AS "Base Doc Number",
--CONVERT(NVARCHAR(10), DocDate, 111) AS PostingDate, CONVERT(NVARCHAR(10), VatDate, 111) AS VatDate, CONVERT(NVARCHAR(10), TaxDate, 111) AS DocDate,
--IsNULL(dbo.GetReportedDocSum(ObjType, BaseSum), 0) AS DocBaseSum,
--IsNULL(dbo.GetReportedDocSum(ObjType, VatSum), 0) AS DocVatSum
ConnectedBPLicTradNum AS "Szallitoi Adoszam", ConnectedBP AS "Szallito Kodja", ConnectedBPName AS "Szallito Neve",
ConnectedBPAdditionalID AS "Szallito 2. ID-szam", ConnectedBPUnifiedTaxID AS "Szallito Egyseges Adoszam",
BPLicTradNum AS "Vevoi Adoszam", BP AS "Vevo Kodja", BPName AS "Vevo Neve",
BPAdditionalID AS "Vevo 2. ID-szam", BPUnifiedTaxID AS "Vevo Egyseges Adoszam",
RptType AS "Riport Tipus", CAST(PageType AS INTEGER) AS "Oldaltipus", DocChain AS "Dokument Lanc", DocType AS "Dokumentum Tipus",
VendorRefNum AS "Szallitoi/Vevoi referencia szam", DocNum AS "Bizonylatszam", CorrType AS "Szamla Tipus",
BaseDocNum AS "Elozmeny szamla sorszama",
CONVERT(NVARCHAR(10), DocDate, 111) AS "Konyvelesi Datum", CONVERT(NVARCHAR(10), VatDate, 111) AS "AFA Datum", CONVERT(NVARCHAR(10), TaxDate, 111) AS "Bizonylat Datuma",
IsNULL(dbo.GetReportedDocSum(ObjType, BaseSum), 0) AS "Bizonylat erteke",
IsNULL(dbo.GetReportedDocSum(ObjType, VatSum), 0) AS "Adoertek"
FROM
(SELECT -- standalone AR docs
BPLicTradNum, BP, MIN(BPName) AS BPName, MIN(BPAdditionalID) AS BPAdditionalID, MIN(BPUnifiedTaxID) AS BPUnifiedTaxID,
MIN(ConnectedBPLicTradNum) AS ConnectedBPLicTradNum, MIN(ConnectedBP) AS ConnectedBP, MIN(ConnectedBPName) AS ConnectedBPName,
MIN(ConnectedBPAdditionalID) AS ConnectedBPAdditionalID, MIN(ConnectedBPUnifiedTaxID) AS ConnectedBPUnifiedTaxID,
--'AR standalone - 01.lap' AS RptType,
NCHAR(0x00C9) + N'rt' + NCHAR(0x00E9) + N'kes' + NCHAR(0x00ED) + N't' + NCHAR(0x00E9) + N's - 01.lap' AS RptType,
1 AS PageType,
(CASE WHEN CommonTaxEntry <= 0 THEN NULL ELSE CommonTaxEntry END) AS DocChain, MIN(TaxEntry) AS TaxEntry,
ObjType, dbo.GetDocTypeName(ObjType) AS DocType,
MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, (CASE WHEN ObjType IS NULL THEN NULL ELSE MAX(FlagOriginal) END) AS CorrType,
(CASE WHEN ObjType IN (14, 16, 165, 166, 19, 21, 163, 164) AND (MIN(BaseAbsEntry) IS NULL OR MIN(BaseAbsEntry) <= 0) THEN N'!'
ELSE CAST(MIN(BaseDocNum) AS NVARCHAR(20)) END) AS BaseDocNum,
MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
SUM(BaseSum) AS BaseSum,
SUM(VatSum) AS VatSum
FROM ReportedDocLines
WHERE DocType = 'standalone'
AND ObjType IN (13, 14, 15, 16, 165, 166, 203)
GROUP BY BPLicTradNum, BP, CommonTaxEntry, ObjType, AbsEntry
UNION ALL
SELECT -- AR docs in chain
BPLicTradNum, BP, MIN(BPName) AS BPName, MIN(BPAdditionalID) AS BPAdditionalID, MIN(BPUnifiedTaxID) AS BPUnifiedTaxID,
MIN(ConnectedBPLicTradNum) AS ConnectedBPLicTradNum, MIN(ConnectedBP) AS ConnectedBP, MIN(ConnectedBPName) AS ConnectedBPName,
MIN(ConnectedBPAdditionalID) AS ConnectedBPAdditionalID, MIN(ConnectedBPUnifiedTaxID) AS ConnectedBPUnifiedTaxID,
--'AR chain - 01-K lap' AS RptType,
NCHAR(0x00C9) + N'rt' + NCHAR(0x00E9) + N'kes' + NCHAR(0x00ED) + N't' + NCHAR(0x00E9) + N's korrekci' + NCHAR(0x00F3) + N'ja - 01-K lap' AS RptType,
2 AS PageType,
(CASE WHEN CommonTaxEntry <= 0 THEN NULL ELSE CommonTaxEntry END) AS DocChain, MIN(TaxEntry) AS TaxEntry,
ObjType, dbo.GetDocTypeName(ObjType) AS DocType,
MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, (CASE WHEN ObjType IS NULL THEN NULL ELSE MAX(FlagOriginal) END) AS CorrType,
(CASE WHEN ObjType IN (14, 16, 165, 166, 19, 21, 163, 164) AND (MIN(BaseAbsEntry) IS NULL OR MIN(BaseAbsEntry) <= 0) THEN N'!'
ELSE CAST(MIN(BaseDocNum) AS NVARCHAR(20)) END) AS BaseDocNum,
MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
SUM(BaseSum) AS BaseSum,
SUM(VatSum) AS VatSum
FROM ReportedDocLines
WHERE DocType = 'chain'
AND ObjType IN (13, 14, 15, 16, 165, 166, 203)
GROUP BY BPLicTradNum, BP, CommonTaxEntry, ObjType, AbsEntry
UNION ALL
SELECT -- standalone AP docs
MIN(ConnectedBPLicTradNum), MIN(ConnectedBP), MIN(ConnectedBPName) AS BPName,
MIN(ConnectedBPAdditionalID) AS ConnectedBPAdditionalID, MIN(ConnectedBPUnifiedTaxID) AS ConnectedBPUnifiedTaxID,
BPLicTradNum AS ConnectedBPLicTradNum, BP AS ConnectedBP, MIN(BPName) AS ConnectedBPName,
MIN(BPAdditionalID) AS BPAdditionalID, MIN(BPUnifiedTaxID) AS BPUnifiedTaxID,
--'AP standalone - 02 lap' AS RptType,
N'Beszerz' + NCHAR(0x00E9) + N's - 02.lap' AS RptType,
3 AS PageType,
(CASE WHEN CommonTaxEntry <= 0 THEN NULL ELSE CommonTaxEntry END) AS DocChain, MIN(TaxEntry) AS TaxEntry,
ObjType, dbo.GetDocTypeName(ObjType) AS DocType,
MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, (CASE WHEN ObjType IS NULL THEN NULL ELSE MAX(FlagOriginal) END) AS CorrType,
(CASE WHEN ObjType IN (14, 16, 165, 166, 19, 21, 163, 164) AND (MIN(BaseAbsEntry) IS NULL OR MIN(BaseAbsEntry) <= 0) THEN N'!'
ELSE CAST(MIN(BaseDocNum) AS NVARCHAR(20)) END) AS BaseDocNum,
MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
SUM(BaseSum) AS BaseSum,
SUM(VatSum) AS VatSum
FROM ReportedDocLines
WHERE DocType = 'standalone'
AND ObjType IN (18, 19, 20, 21, 163, 164, 204)
GROUP BY BPLicTradNum, BP, CommonTaxEntry, ObjType, AbsEntry
UNION ALL
SELECT -- AP docs in chain
MIN(ConnectedBPLicTradNum), MIN(ConnectedBP), MIN(ConnectedBPName) AS BPName,
MIN(ConnectedBPAdditionalID) AS ConnectedBPAdditionalID, MIN(ConnectedBPUnifiedTaxID) AS ConnectedBPUnifiedTaxID,
BPLicTradNum AS ConnectedBPLicTradNum, BP AS ConnectedBP, MIN(BPName) AS ConnectedBPName,
MIN(BPAdditionalID) AS BPAdditionalID, MIN(BPUnifiedTaxID) AS BPUnifiedTaxID,
--'AP chain - 02-K lap' AS RptType,
N'Beszerz' + NCHAR(0x00E9) + N's korrekci' + NCHAR(0x00F3) + N'ja - 02-K lap' AS RptType,
4 AS PageType,
(CASE WHEN CommonTaxEntry <= 0 THEN NULL ELSE CommonTaxEntry END) AS DocChain, MIN(TaxEntry) AS TaxEntry,
ObjType, dbo.GetDocTypeName(ObjType) AS DocType,
MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, (CASE WHEN ObjType IS NULL THEN NULL ELSE MAX(FlagOriginal) END) AS CorrType,
(CASE WHEN ObjType IN (14, 16, 165, 166, 19, 21, 163, 164) AND (MIN(BaseAbsEntry) IS NULL OR MIN(BaseAbsEntry) <= 0) THEN N'!'
ELSE CAST(MIN(BaseDocNum) AS NVARCHAR(20)) END) AS BaseDocNum,
MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
SUM(BaseSum) AS BaseSum,
SUM(VatSum) AS VatSum
FROM ReportedDocLines
WHERE DocType = 'chain'
AND ObjType IN (18, 19, 20, 21, 163, 164, 204)
GROUP BY BPLicTradNum, BP, CommonTaxEntry, ObjType, AbsEntry
UNION ALL
-- OPTION 1 for section 6/D - use/uncomment either Option 1 (separated docs in 6/D) or Option 2 (sum of all docs in 6/D)
--
--SELECT -- remaining AP docs, where sum per business partner within the selected period > 1000000, but each of these docs is < 1000000
-- MIN(ConnectedBPLicTradNum), MIN(ConnectedBP), MIN(ConnectedBPName) AS BPName,
-- MIN(ConnectedBPAdditionalID) AS ConnectedBPAdditionalID, MIN(ConnectedBPUnifiedTaxID) AS ConnectedBPUnifiedTaxID,
-- BPLicTradNum AS ConnectedBPLicTradNum, BP AS ConnectedBP, MIN(BPName) AS ConnectedBPName,
-- MIN(BPAdditionalID) AS BPAdditionalID, MIN(BPUnifiedTaxID) AS BPUnifiedTaxID,
-- --'AP rest - 6/D' AS RptType,
-- N'Egy' + NCHAR(0x00E9) + N'b beszerz' + NCHAR(0x00E9) + N's f' + NCHAR(0x0151) + N'lap - 6/D mez' + NCHAR(0x0151) AS RptType,
-- (CASE WHEN SUM(BaseSum) < 0 THEN 3
-- WHEN SUM(BaseSum) >= 0 THEN 4
-- --ELSE 5
-- END) as PageType,
-- (CASE WHEN CommonTaxEntry <= 0 THEN NULL ELSE CommonTaxEntry END) AS DocChain, MIN(TaxEntry) AS TaxEntry,
-- ObjType, dbo.GetDocTypeName(ObjType) AS DocType,
-- MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, (CASE WHEN ObjType IS NULL THEN NULL ELSE MAX(FlagOriginal) END) AS CorrType,
-- (CASE WHEN ObjType IN (14, 16, 165, 166, 19, 21, 163, 164) AND (MIN(BaseAbsEntry) IS NULL OR MIN(BaseAbsEntry) <= 0) THEN N'!'
-- ELSE CAST(MIN(BaseDocNum) AS NVARCHAR(20)) END) AS BaseDocNum,
-- MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
-- SUM(BaseSum) AS BaseSum,
-- SUM(VatSum) AS VatSum
--FROM ReportedDocLines
--WHERE DocType = 'rest AP'
--GROUP BY BPLicTradNum, BP, CommonTaxEntry, ObjType, AbsEntry
----------------------------------------
-- OPTION 2 for section 6/D - use/uncomment either Option 1 (separated docs in 6/D) or Option 2 (sum of all docs in 6/D)
--
SELECT -- remaining AP docs, where sum per business partner within the selected period > 1000000, but each of these docs is < 1000000
MIN(ConnectedBPLicTradNum), MIN(ConnectedBP), MIN(ConnectedBPName) AS BPName,
MIN(ConnectedBPAdditionalID) AS ConnectedBPAdditionalID, MIN(ConnectedBPUnifiedTaxID) AS ConnectedBPUnifiedTaxID,
BPLicTradNum AS ConnectedBPLicTradNum, BP AS ConnectedBP, MIN(BPName) AS ConnectedBPName,
MIN(BPAdditionalID) AS BPAdditionalID, MIN(BPUnifiedTaxID) AS BPUnifiedTaxID,
--'AP rest - 6/D' AS RptType,
N'Egy' + NCHAR(0x00E9) + N'b beszerz' + NCHAR(0x00E9) + N's f' + NCHAR(0x0151) + N'lap - 6/D mez' + NCHAR(0x0151) AS RptType,
(CASE WHEN SUM(BaseSum) < 0 THEN 3
WHEN SUM(BaseSum) >= 0 THEN 4
--ELSE 5
END) as PageType,
(CASE WHEN CommonTaxEntry <= 0 THEN NULL ELSE CommonTaxEntry END) AS DocChain, MIN(TaxEntry) AS TaxEntry,
MIN(ObjType) AS ObjType, dbo.GetDocTypeName(MIN(ObjType)) AS DocType,
MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, NULL AS CorrType,
'' AS BaseDocNum,
MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
SUM(BaseSum) AS BaseSum,
SUM(VatSum) AS VatSum
FROM ReportedDocLines
WHERE DocType = 'rest AP'
GROUP BY BPLicTradNum, BP, CommonTaxEntry
----------------------------------------
UNION ALL
SELECT -- journal entries and payments (on account)
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(BPLicTradNum) ELSE MIN(ConnectedBPLicTradNum) END) AS BPLicTradNum,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(BP) ELSE MIN(ConnectedBP) END) AS BP,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(BPName) ELSE MIN(ConnectedBPName) END) AS BPName,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(BPAdditionalID) ELSE MIN(ConnectedBPAdditionalID) END) AS BPAdditionalID,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(BPUnifiedTaxID) ELSE MIN(ConnectedBPUnifiedTaxID) END) AS BPUnifiedTaxID,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(ConnectedBPLicTradNum) ELSE MIN(BPLicTradNum) END) AS ConnectedBPLicTradNum,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(ConnectedBP) ELSE MIN(BP) END) AS ConnectedBP,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(ConnectedBPName) ELSE MIN(BPName) END) AS ConnectedBPName,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(ConnectedBPAdditionalID) ELSE MIN(BPAdditionalID) END) AS ConnectedBPAdditionalID,
(CASE WHEN tblTaxCodes.Category = 'O' THEN MIN(ConnectedBPUnifiedTaxID) ELSE MIN(BPUnifiedTaxID) END) AS ConnectedBPUnifiedTaxID,
--'JE and Payments' AS RptType,
N'Egy' + NCHAR(0x00E9) + N'b t' + NCHAR(0x00E9) + N'telek' AS RptType,
(CASE WHEN tblTaxCodes.Category = 'O' AND SUM(BaseSum) >= 0 THEN 1
WHEN tblTaxCodes.Category = 'O' AND SUM(BaseSum) < 0 THEN 2
WHEN tblTaxCodes.Category = 'I' AND SUM(BaseSum) < 0 THEN 3
WHEN tblTaxCodes.Category = 'I' AND SUM(BaseSum) >= 0 THEN 4
ELSE 6
END) as PageType,
NULL AS DocChain, MIN(TaxEntry) AS TaxEntry,
ObjType, dbo.GetDocTypeName(ObjType) AS DocType,
MIN(NumAtCard) AS VendorRefNum, MIN(DocNum) AS DocNum, (CASE WHEN ObjType IS NULL THEN NULL ELSE MAX(FlagOriginal) END) AS CorrType,
NULL AS BaseDocNum,
MIN(DocDate) AS DocDate, MIN(VatDate) AS VatDate, MIN(TaxDate) AS TaxDate,
SUM(BaseSum) AS BaseSum,
SUM(VatSum) AS VatSum
FROM
ReportedDocLines tblDocs
INNER JOIN
OVTG tblTaxCodes
ON tblTaxCodes.Code = tblDocs.TaxCode
WHERE DocType = 'tax booking'
AND ObjType IN (30, 24, 46)
GROUP BY ObjType, AbsEntry, LineNum, TaxCode, tblTaxCodes.Category
) tblData
ORDER BY BPLicTradNum, BP, ConnectedBPLicTradNum, ConnectedBP, PageType, RptType, DocChain, DocDate, TaxEntry
End_Task:
-- SET NOCOUNT OFF
END
GO
CREATE PROCEDURE TmSp_Inventory_Counting_GetCounterName
@DocKey@ INT,
@ObjectId@ nvarchar(20),
@MultipleCounterType nvarchar(1) = 'I',
@MultipleCounterOrder nvarchar(12)
AS
BEGIN
Declare @selectStr nvarchar(256)
Declare @fromStr nvarchar(256)
Declare @whereStr nvarchar(64)
Declare @CountingType nvarchar(1)
Declare @taker1Type int
Declare @typeUSR int = 12
Declare @typeHEM int = 171
Declare @CountTypeSingle nvarchar(1) = '1'
Declare @CountTypeMultiple nvarchar(1) = '2'
Declare @CounterTypeTeam nvarchar(1) = 'T'
Declare @CounterTypeIndividual nvarchar(1) = 'I'
Declare @MainTable nvarchar(16)
Declare @Counter nvarchar(64)
Declare @joinTbl nvarchar(16)
Declare @joinCol nvarchar(16)
Declare @ResultCounterName nvarchar(16) = 'CounterName'
Declare @DocEntry nvarchar(16) = @DocKey@
Declare @CounterOrder nvarchar(16) = @MultipleCounterOrder
Set @whereStr = N' where T0.DocEntry=' + @DocEntry
IF (@ObjectId@ = 1470000065)
Begin
select @CountingType = "T0"."CountType" from "OINC" "T0" where "T0"."DocEntry"=@DocKey@
select @Taker1Type= "Taker1Type" from "OINC" where "DocEntry"=@DocKey@
if (@CountingType = @CountTypeSingle )
Begin
select @Taker1Type= "Taker1Type" from "OINC" where "DocEntry"=@DocKey@
If(@taker1Type = @typeUSR)
Begin
Set @Counter = ' "T1"."U_NAME" '
Set @joinTbl = ' "OUSR" '
Set @joinCol = ' "USERID" '
End
Else
Begin
Set @Counter = ' "T1"."lastName"+'' ''+"T1"."firstName" '
Set @joinTbl = ' "OHEM" '
Set @joinCol = ' "empID" '
End
Set @selectStr = 'select ' + @Counter + ' as ' + @ResultCounterName + ' from "OINC" "T0" ' + ' join ' + @joinTbl + ' T1 on T0.Taker1Id = T1.' + @joinCol + @whereStr
End
Else
Begin
If(@MultipleCounterType = @CounterTypeTeam)
Begin
Set @MainTable =' "INC4" "T0" '
End
Else
Begin
Set @MainTable =' "INC8" "T0" '
End
Set @selectStr = 'select ' + ' "T0"."CounteName" '+ ' as ' + @ResultCounterName + ' from '+ @MainTable + @whereStr + ' and "T0"."VisOrder" = ' + @CounterOrder
End
End
Else
Begin
select @CountingType = "T0"."CountType" from "OICD" "T0" where "T0"."DocEntry"=@DocEntry
select @Taker1Type= "Taker1Type" from "OICD" where "DocEntry"=@DocEntry
if (@CountingType = @CountTypeSingle )
Begin
select @Taker1Type= "Taker1Type" from "OICD" where "DocEntry"=@DocEntry
If(@taker1Type = @typeUSR)
Begin
Set @Counter = ' "T1"."U_NAME" '
Set @joinTbl = ' "OUSR" '
Set @joinCol = ' "USERID" '
End
Else
Begin
Set @Counter = ' "T1"."lastName"+'' ''+"T1"."firstName" '
Set @joinTbl = ' "OHEM" '
Set @joinCol = ' "empID" '
End
Set @selectStr = 'select ' + @Counter + ' as ' + @ResultCounterName + ' from "OICD" "T0" ' + ' join ' + @joinTbl + ' T1 on T0.Taker1Id = T1.' + @joinCol + @whereStr
End
Else
Begin
If(@MultipleCounterType = @CounterTypeTeam)
Begin
Set @MainTable =' "ICD4" "T0" '
End
Else
Begin
Set @MainTable =' "ICD8" "T0" '
End
Set @selectStr = 'select ' + ' "T0"."CounteName" '+ ' as ' + @ResultCounterName + ' from '+ @MainTable + @whereStr + ' and "T0"."VisOrder" = ' + @CounterOrder
End
End
exec (@selectStr)
END
GO
CREATE PROCEDURE TmSp_Inventory_Counting_IndividualCounter
@DocKey@ INT,
@ObjectId@ nvarchar(20)
AS
BEGIN
Declare @DocEntry INT = @DocKey@
IF (@ObjectId@ = '1470000065')
BEGIN
select "OINC"."DocEntry", "OINC"."DocNum", "OINC"."CountDate",
"OINC"."Time", "OINC"."CountType", "OINC"."IndvCount",
"INC1"."ItemCode", "INC1"."ItemDesc",
"INC1"."WhsCode", "INC1"."InWhsQty",
"INC1"."CountQty", "INC1"."BinEntry",
"INC1"."Counted", "OBIN"."BinCode",
"INC81"."CounteName" as "IndvCounter1Name",
"INC82"."CounteName" as "IndvCounter2Name",
"INC83"."CounteName" as "IndvCounter3Name",
"INC84"."CounteName" as "IndvCounter4Name",
"INC85"."CounteName" as "IndvCounter5Name",
"INC91"."TotalQty" as "IndvCounter1Qty",
"INC92"."TotalQty" as "IndvCounter2Qty",
"INC93"."TotalQty" as "IndvCounter3Qty",
"INC94"."TotalQty" as "IndvCounter4Qty",
"INC95"."TotalQty" as "IndvCounter5Qty" from "INC1"
left join "OINC" on "OINC"."DocEntry" = "INC1"."DocEntry"
left join "OBIN" on "INC1"."BinEntry" = "OBIN"."AbsEntry"
left join "INC8" as "INC81" on ("INC1"."DocEntry" = "INC81"."DocEntry" and "INC81"."VisOrder" = 1)
left join "INC8" as "INC82" on ("INC1"."DocEntry" = "INC82"."DocEntry" and "INC82"."VisOrder" = 2)
left join "INC8" as "INC83" on ("INC1"."DocEntry" = "INC83"."DocEntry" and "INC83"."VisOrder" = 3)
left join "INC8" as "INC84" on ("INC1"."DocEntry" = "INC84"."DocEntry" and "INC84"."VisOrder" = 4)
left join "INC8" as "INC85" on ("INC1"."DocEntry" = "INC85"."DocEntry" and "INC85"."VisOrder" = 5)
left join "INC9" as "INC91" on ("INC1"."DocEntry" = "INC91"."DocEntry" and "INC1"."LineNum" = "INC91"."LineNum" and "INC81"."CounterNum" = "INC91"."CounterNum" and "INC81"."VisOrder" = 1)
left join "INC9" as "INC92" on ("INC1"."DocEntry" = "INC92"."DocEntry" and "INC1"."LineNum" = "INC92"."LineNum" and "INC82"."CounterNum" = "INC92"."CounterNum" and "INC82"."VisOrder" = 2)
left join "INC9" as "INC93" on ("INC1"."DocEntry" = "INC93"."DocEntry" and "INC1"."LineNum" = "INC93"."LineNum" and "INC83"."CounterNum" = "INC93"."CounterNum" and "INC83"."VisOrder" = 3)
left join "INC9" as "INC94" on ("INC1"."DocEntry" = "INC94"."DocEntry" and "INC1"."LineNum" = "INC94"."LineNum" and "INC84"."CounterNum" = "INC94"."CounterNum" and "INC84"."VisOrder" = 4)
left join "INC9" as "INC95" on ("INC1"."DocEntry" = "INC95"."DocEntry" and "INC1"."LineNum" = "INC95"."LineNum" and "INC85"."CounterNum" = "INC95"."CounterNum" and "INC85"."VisOrder" = 5)
where "OINC"."DocEntry" = @DocEntry order by "INC1"."VisOrder"
END
Else
BEGIN
select "OICD"."DocEntry", "OICD"."DocNum", "OICD"."CountDate",
"OICD"."Time", "OICD"."CountType", "OICD"."IndvCount",
"ICD1"."ItemCode", "ICD1"."ItemDesc",
"ICD1"."WhsCode", "ICD1"."InWhsQty",
"ICD1"."CountQty", "ICD1"."BinEntry",
"ICD1"."Counted", "OBIN"."BinCode",
"ICD81"."CounteName" as "IndvCounter1Name",
"ICD82"."CounteName" as "IndvCounter2Name",
"ICD83"."CounteName" as "IndvCounter3Name",
"ICD84"."CounteName" as "IndvCounter4Name",
"ICD85"."CounteName" as "IndvCounter5Name",
"ICD91"."TotalQty" as "IndvCounter1Qty",
"ICD92"."TotalQty" as "IndvCounter2Qty",
"ICD93"."TotalQty" as "IndvCounter3Qty",
"ICD94"."TotalQty" as "IndvCounter4Qty",
"ICD95"."TotalQty" as "IndvCounter5Qty" from "ICD1"
left join "OICD" on "OICD"."DocEntry" = "ICD1"."DocEntry"
left join "OBIN" on "ICD1"."BinEntry" = "OBIN"."AbsEntry"
left join "ICD8" as "ICD81" on ("ICD1"."DocEntry" = "ICD81"."DocEntry" and "ICD81"."VisOrder" = 1)
left join "ICD8" as "ICD82" on ("ICD1"."DocEntry" = "ICD82"."DocEntry" and "ICD82"."VisOrder" = 2)
left join "ICD8" as "ICD83" on ("ICD1"."DocEntry" = "ICD83"."DocEntry" and "ICD83"."VisOrder" = 3)
left join "ICD8" as "ICD84" on ("ICD1"."DocEntry" = "ICD84"."DocEntry" and "ICD84"."VisOrder" = 4)
left join "ICD8" as "ICD85" on ("ICD1"."DocEntry" = "ICD85"."DocEntry" and "ICD85"."VisOrder" = 5)
left join "ICD9" as "ICD91" on ("ICD1"."DocEntry" = "ICD91"."DocEntry" and "ICD1"."LineNum" = "ICD91"."LineNum" and "ICD81"."CounterNum" = "ICD91"."CounterNum" and "ICD81"."VisOrder" = 1)
left join "ICD9" as "ICD92" on ("ICD1"."DocEntry" = "ICD92"."DocEntry" and "ICD1"."LineNum" = "ICD92"."LineNum" and "ICD82"."CounterNum" = "ICD92"."CounterNum" and "ICD82"."VisOrder" = 2)
left join "ICD9" as "ICD93" on ("ICD1"."DocEntry" = "ICD93"."DocEntry" and "ICD1"."LineNum" = "ICD93"."LineNum" and "ICD83"."CounterNum" = "ICD93"."CounterNum" and "ICD83"."VisOrder" = 3)
left join "ICD9" as "ICD94" on ("ICD1"."DocEntry" = "ICD94"."DocEntry" and "ICD1"."LineNum" = "ICD94"."LineNum" and "ICD84"."CounterNum" = "ICD94"."CounterNum" and "ICD84"."VisOrder" = 4)
left join "ICD9" as "ICD95" on ("ICD1"."DocEntry" = "ICD95"."DocEntry" and "ICD1"."LineNum" = "ICD95"."LineNum" and "ICD85"."CounterNum" = "ICD95"."CounterNum" and "ICD85"."VisOrder" = 5)
where "OICD"."DocEntry" = @DocEntry order by "ICD1"."VisOrder"
END
END
GO
CREATE PROCEDURE [dbo].[TmSp_Inventory_Counting_PerCounter]
@DocKey@ INT ,
@ObjectId@ NCHAR(20),
@MultipleCounterType nvarchar(1) = 'I',
@MultipleCounterOrder nvarchar(12)
AS
BEGIN
DECLARE @CountType nvarchar(1)
DECLARE @CountNumber int
Declare @DocEntry INT = @DocKey@
IF (@ObjectId@ = '1470000065')
BEGIN
SELECT @CountType = "OINC"."CountType" FROM "OINC" WHERE "OINC"."DocEntry" = @DocEntry;
IF (@CountType = '1') --single counter
BEGIN
SELECT "T1"."DocEntry", "T1"."ItemCode", "T1"."ItemDesc", "T1"."WhsCode", "T1"."BinEntry", "T2"."BinCode", "T1"."InWhsQty", "T1"."Counted", "T1"."CountQty"
FROM "INC1" "T1" LEFT JOIN "OBIN" "T2" ON "T1"."BinEntry"="T2"."AbsEntry" WHERE "T1"."DocEntry"=@DocEntry ORDER BY "T1"."VisOrder"
END
ELSE --multiple counters
BEGIN
IF (@MultipleCounterType = 'I') --individual counter
BEGIN
SELECT @CountNumber = "T1"."CounterNum" FROM "INC8" "T1" WHERE "T1"."DocEntry" = @DocEntry AND "T1"."VisOrder" = @MultipleCounterOrder
SELECT "T1"."DocEntry", "T1"."ItemCode", "T1"."ItemDesc", "T1"."WhsCode", "T1"."BinEntry", "T2"."BinCode", "T1"."InWhsQty", "T1"."Counted", "T3"."TotalQty" AS "CountQty"
FROM "INC1" "T1"
LEFT JOIN "OBIN" "T2" ON "T1"."BinEntry"="T2"."AbsEntry"
LEFT JOIN "INC9" "T3" ON "T1"."DocEntry"="T3"."DocEntry" AND "T1"."LineNum"="T3"."LineNum" AND "T3".CounterNum = @CountNumber
WHERE "T1"."DocEntry"=@DocEntry ORDER BY "T1"."VisOrder"
END
ELSE --Team counters
BEGIN
SELECT @CountNumber = "T1"."CounterNum" FROM "INC4" "T1" WHERE "T1"."DocEntry" = @DocEntry AND "T1"."VisOrder" = @MultipleCounterOrder
SELECT "T1"."DocEntry", "T1"."ItemCode", "T1"."ItemDesc", "T1"."WhsCode", "T1"."BinEntry", "T2"."BinCode", "T1"."InWhsQty", "T1"."Counted", "T3"."TotalQty" AS "CountQty"
FROM "INC1" "T1"
LEFT JOIN "OBIN" "T2" ON "T1"."BinEntry"="T2"."AbsEntry"
LEFT JOIN "INC5" "T3" ON "T1"."DocEntry"="T3"."DocEntry" AND "T1"."LineNum"="T3"."LineNum" AND "T3".CounterNum = @CountNumber
WHERE "T1"."DocEntry"=@DocEntry ORDER BY "T1"."VisOrder"
END
END
END
ELSE
BEGIN
SELECT @CountType = "OICD"."CountType" FROM "OICD" WHERE "OICD"."DocEntry" = @DocEntry;
IF (@CountType = '1') --single counter
BEGIN
SELECT "T1"."DocEntry", "T1"."ItemCode", "T1"."ItemDesc", "T1"."WhsCode", "T1"."BinEntry", "T2"."BinCode", "T1"."InWhsQty", "T1"."Counted", "T1"."CountQty"
FROM "ICD1" "T1" LEFT JOIN "OBIN" "T2" ON "T1"."BinEntry"="T2"."AbsEntry" WHERE "T1"."DocEntry"=@DocEntry ORDER BY "T1"."VisOrder"
END
ELSE --multiple counters
BEGIN
IF (@MultipleCounterType = 'I') --individual counter
BEGIN
SELECT @CountNumber = "T1"."CounterNum" FROM "ICD8" "T1" WHERE "T1"."DocEntry" = @DocEntry AND "T1"."VisOrder" = @MultipleCounterOrder
SELECT "T1"."DocEntry", "T1"."ItemCode", "T1"."ItemDesc", "T1"."WhsCode", "T1"."BinEntry", "T2"."BinCode", "T1"."InWhsQty", "T1"."Counted", "T3"."TotalQty" AS "CountQty"
FROM "ICD1" "T1"
LEFT JOIN "OBIN" "T2" ON "T1"."BinEntry"="T2"."AbsEntry"
LEFT JOIN "ICD9" "T3" ON "T1"."DocEntry"="T3"."DocEntry" AND "T1"."LineNum"="T3"."LineNum" AND "T3".CounterNum = @CountNumber
WHERE "T1"."DocEntry"=@DocEntry ORDER BY "T1"."VisOrder"
END
ELSE --Team counters
BEGIN
SELECT @CountNumber = "T1"."CounterNum" FROM "ICD4" "T1" WHERE "T1"."DocEntry" = @DocEntry AND "T1"."VisOrder" = @MultipleCounterOrder
SELECT "T1"."DocEntry", "T1"."ItemCode", "T1"."ItemDesc", "T1"."WhsCode", "T1"."BinEntry", "T2"."BinCode", "T1"."InWhsQty", "T1"."Counted", "T3"."TotalQty" AS "CountQty"
FROM "ICD1" "T1"
LEFT JOIN "OBIN" "T2" ON "T1"."BinEntry"="T2"."AbsEntry"
LEFT JOIN "ICD5" "T3" ON "T1"."DocEntry"="T3"."DocEntry" AND "T1"."LineNum"="T3"."LineNum" AND "T3".CounterNum = @CountNumber
WHERE "T1"."DocEntry"=@DocEntry ORDER BY "T1"."VisOrder"
END
END
END
END
GO
CREATE PROCEDURE TmSp_Inventory_Counting_TeamCounter
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
BEGIN
IF (@ObjectId@ = '1470000065')
BEGIN
select "OINC"."DocEntry", "OINC"."DocNum", "OINC"."CountDate", "OINC"."Time", "OINC"."CountType", "OINC"."TeamCount",
"INC1"."ItemCode", "INC1"."ItemDesc", "INC1"."WhsCode", "INC1"."InWhsQty", "INC1"."CountQty", "INC1"."BinEntry", "INC1"."Counted", OBIN."BinCode",
"INC41"."CounteName" as "TeamCounter1Name", "INC42"."CounteName" as "TeamCounter2Name", INC43."CounteName" as "TeamCounter3Name", INC44."CounteName" as "TeamCounter4Name", INC45."CounteName" as "TeamCounter5Name",
INC46."CounteName" as "TeamCounter6Name", INC47."CounteName" as "TeamCounter7Name", INC48."CounteName" as "TeamCounter8Name", INC49."CounteName" as "TeamCounter9Name", INC410."CounteName" as "TeamCounter10Name",
INC51."TotalQty" as "TeamCounter1Qty", INC52."TotalQty" as "TeamCounter2Qty", INC53."TotalQty" as "TeamCounter3Qty", INC54."TotalQty" as "TeamCounter4Qty", INC55."TotalQty" as "TeamCounter5Qty",
INC56."TotalQty" as "TeamCounter6Qty", INC57."TotalQty" as "TeamCounter7Qty", INC58."TotalQty" as "TeamCounter8Qty", INC59."TotalQty" as "TeamCounter9Qty", INC510."TotalQty" as "TeamCounter10Qty" from "INC1"
left join "OINC" on "OINC"."DocEntry" = "INC1"."DocEntry"
left join OBIN on "INC1".BinEntry = OBIN.AbsEntry
left join "INC4" as "INC41" on ("INC1"."DocEntry" = "INC41"."DocEntry" and "INC41"."VisOrder" = 1)
left join "INC4" as "INC42" on ("INC1"."DocEntry" = "INC42"."DocEntry" and "INC42"."VisOrder" = 2)
left join "INC4" as "INC43" on ("INC1"."DocEntry" = "INC43"."DocEntry" and "INC43"."VisOrder" = 3)
left join "INC4" as "INC44" on ("INC1"."DocEntry" = "INC44"."DocEntry" and "INC44"."VisOrder" = 4)
left join "INC4" as "INC45" on ("INC1"."DocEntry" = "INC45"."DocEntry" and "INC45"."VisOrder" = 5)
left join "INC4" as "INC46" on ("INC1"."DocEntry" = "INC46"."DocEntry" and "INC46"."VisOrder" = 6)
left join "INC4" as "INC47" on ("INC1"."DocEntry" = "INC47"."DocEntry" and "INC47"."VisOrder" = 7)
left join "INC4" as "INC48" on ("INC1"."DocEntry" = "INC48"."DocEntry" and "INC48"."VisOrder" = 8)
left join "INC4" as "INC49" on ("INC1"."DocEntry" = "INC49"."DocEntry" and "INC49"."VisOrder" = 9)
left join "INC4" as "INC410" on ("INC1"."DocEntry" = "INC410"."DocEntry" and "INC410"."VisOrder" = 10)
left join "INC5" as "INC51" on ("INC1"."DocEntry" = "INC51"."DocEntry" and "INC1"."LineNum" = "INC51"."LineNum" and "INC41"."CounterNum" = "INC51"."CounterNum" and "INC41"."VisOrder" = 1)
left join "INC5" as "INC52" on ("INC1"."DocEntry" = "INC52"."DocEntry" and "INC1"."LineNum" = "INC52"."LineNum" and "INC42"."CounterNum" = "INC52"."CounterNum" and "INC42"."VisOrder" = 2)
left join "INC5" as "INC53" on ("INC1"."DocEntry" = "INC53"."DocEntry" and "INC1"."LineNum" = "INC53"."LineNum" and "INC43"."CounterNum" = "INC53"."CounterNum" and "INC43"."VisOrder" = 3)
left join "INC5" as "INC54" on ("INC1"."DocEntry" = "INC54"."DocEntry" and "INC1"."LineNum" = "INC54"."LineNum" and "INC44"."CounterNum" = "INC54"."CounterNum" and "INC44"."VisOrder" = 4)
left join "INC5" as "INC55" on ("INC1"."DocEntry" = "INC55"."DocEntry" and "INC1"."LineNum" = "INC55"."LineNum" and "INC45"."CounterNum" = "INC55"."CounterNum" and "INC45"."VisOrder" = 5)
left join "INC5" as "INC56" on ("INC1"."DocEntry" = "INC56"."DocEntry" and "INC1"."LineNum" = "INC56"."LineNum" and "INC46"."CounterNum" = "INC56"."CounterNum" and "INC46"."VisOrder" = 6)
left join "INC5" as "INC57" on ("INC1"."DocEntry" = "INC57"."DocEntry" and "INC1"."LineNum" = "INC57"."LineNum" and "INC47"."CounterNum" = "INC57"."CounterNum" and "INC47"."VisOrder" = 7)
left join "INC5" as "INC58" on ("INC1"."DocEntry" = "INC58"."DocEntry" and "INC1"."LineNum" = "INC58"."LineNum" and "INC48"."CounterNum" = "INC58"."CounterNum" and "INC48"."VisOrder" = 8)
left join "INC5" as "INC59" on ("INC1"."DocEntry" = "INC59"."DocEntry" and "INC1"."LineNum" = "INC59"."LineNum" and "INC49"."CounterNum" = "INC59"."CounterNum" and "INC49"."VisOrder" = 9)
left join "INC5" as "INC510" on ("INC1"."DocEntry" = "INC510"."DocEntry" and "INC1"."LineNum" = "INC510"."LineNum" and "INC410"."CounterNum" = "INC510"."CounterNum" and "INC410"."VisOrder" = 10)
where "OINC"."DocEntry" = @DocKey@ order by "INC1"."VisOrder"
END
Else
BEGIN
select "OICD"."DocEntry", "OICD"."DocNum", "OICD"."CountDate", "OICD"."Time", "OICD"."CountType", "OICD"."TeamCount",
"ICD1"."ItemCode", "ICD1"."ItemDesc", "ICD1"."WhsCode", "ICD1"."InWhsQty", "ICD1"."CountQty", "ICD1"."BinEntry", "ICD1"."Counted", OBIN."BinCode",
"ICD41"."CounteName" as "TeamCounter1Name", "ICD42"."CounteName" as "TeamCounter2Name", ICD43."CounteName" as "TeamCounter3Name", ICD44."CounteName" as "TeamCounter4Name", ICD45."CounteName" as "TeamCounter5Name",
ICD46."CounteName" as "TeamCounter6Name", ICD47."CounteName" as "TeamCounter7Name", ICD48."CounteName" as "TeamCounter8Name", ICD49."CounteName" as "TeamCounter9Name", ICD410."CounteName" as "TeamCounter10Name",
ICD51."TotalQty" as "TeamCounter1Qty", ICD52."TotalQty" as "TeamCounter2Qty", ICD53."TotalQty" as "TeamCounter3Qty", ICD54."TotalQty" as "TeamCounter4Qty", ICD55."TotalQty" as "TeamCounter5Qty",
ICD56."TotalQty" as "TeamCounter6Qty", ICD57."TotalQty" as "TeamCounter7Qty", ICD58."TotalQty" as "TeamCounter8Qty", ICD59."TotalQty" as "TeamCounter9Qty", ICD510."TotalQty" as "TeamCounter10Qty" from "ICD1"
left join "OICD" on "OICD"."DocEntry" = "ICD1"."DocEntry"
left join OBIN on "ICD1".BinEntry = OBIN.AbsEntry
left join "ICD4" as "ICD41" on ("ICD1"."DocEntry" = "ICD41"."DocEntry" and "ICD41"."VisOrder" = 1)
left join "ICD4" as "ICD42" on ("ICD1"."DocEntry" = "ICD42"."DocEntry" and "ICD42"."VisOrder" = 2)
left join "ICD4" as "ICD43" on ("ICD1"."DocEntry" = "ICD43"."DocEntry" and "ICD43"."VisOrder" = 3)
left join "ICD4" as "ICD44" on ("ICD1"."DocEntry" = "ICD44"."DocEntry" and "ICD44"."VisOrder" = 4)
left join "ICD4" as "ICD45" on ("ICD1"."DocEntry" = "ICD45"."DocEntry" and "ICD45"."VisOrder" = 5)
left join "ICD4" as "ICD46" on ("ICD1"."DocEntry" = "ICD46"."DocEntry" and "ICD46"."VisOrder" = 6)
left join "ICD4" as "ICD47" on ("ICD1"."DocEntry" = "ICD47"."DocEntry" and "ICD47"."VisOrder" = 7)
left join "ICD4" as "ICD48" on ("ICD1"."DocEntry" = "ICD48"."DocEntry" and "ICD48"."VisOrder" = 8)
left join "ICD4" as "ICD49" on ("ICD1"."DocEntry" = "ICD49"."DocEntry" and "ICD49"."VisOrder" = 9)
left join "ICD4" as "ICD410" on ("ICD1"."DocEntry" = "ICD410"."DocEntry" and "ICD410"."VisOrder" = 10)
left join "ICD5" as "ICD51" on ("ICD1"."DocEntry" = "ICD51"."DocEntry" and "ICD1"."LineNum" = "ICD51"."LineNum" and "ICD41"."CounterNum" = "ICD51"."CounterNum" and "ICD41"."VisOrder" = 1)
left join "ICD5" as "ICD52" on ("ICD1"."DocEntry" = "ICD52"."DocEntry" and "ICD1"."LineNum" = "ICD52"."LineNum" and "ICD42"."CounterNum" = "ICD52"."CounterNum" and "ICD42"."VisOrder" = 2)
left join "ICD5" as "ICD53" on ("ICD1"."DocEntry" = "ICD53"."DocEntry" and "ICD1"."LineNum" = "ICD53"."LineNum" and "ICD43"."CounterNum" = "ICD53"."CounterNum" and "ICD43"."VisOrder" = 3)
left join "ICD5" as "ICD54" on ("ICD1"."DocEntry" = "ICD54"."DocEntry" and "ICD1"."LineNum" = "ICD54"."LineNum" and "ICD44"."CounterNum" = "ICD54"."CounterNum" and "ICD44"."VisOrder" = 4)
left join "ICD5" as "ICD55" on ("ICD1"."DocEntry" = "ICD55"."DocEntry" and "ICD1"."LineNum" = "ICD55"."LineNum" and "ICD45"."CounterNum" = "ICD55"."CounterNum" and "ICD45"."VisOrder" = 5)
left join "ICD5" as "ICD56" on ("ICD1"."DocEntry" = "ICD56"."DocEntry" and "ICD1"."LineNum" = "ICD56"."LineNum" and "ICD46"."CounterNum" = "ICD56"."CounterNum" and "ICD46"."VisOrder" = 6)
left join "ICD5" as "ICD57" on ("ICD1"."DocEntry" = "ICD57"."DocEntry" and "ICD1"."LineNum" = "ICD57"."LineNum" and "ICD47"."CounterNum" = "ICD57"."CounterNum" and "ICD47"."VisOrder" = 7)
left join "ICD5" as "ICD58" on ("ICD1"."DocEntry" = "ICD58"."DocEntry" and "ICD1"."LineNum" = "ICD58"."LineNum" and "ICD48"."CounterNum" = "ICD58"."CounterNum" and "ICD48"."VisOrder" = 8)
left join "ICD5" as "ICD59" on ("ICD1"."DocEntry" = "ICD59"."DocEntry" and "ICD1"."LineNum" = "ICD59"."LineNum" and "ICD49"."CounterNum" = "ICD59"."CounterNum" and "ICD49"."VisOrder" = 9)
left join "ICD5" as "ICD510" on ("ICD1"."DocEntry" = "ICD510"."DocEntry" and "ICD1"."LineNum" = "ICD510"."LineNum" and "ICD410"."CounterNum" = "ICD510"."CounterNum" and "ICD410"."VisOrder" = 10)
where "OICD"."DocEntry" = @DocKey@ order by "ICD1"."VisOrder"
END
END
GO
CREATE PROCEDURE TmSp_Inventory_Counting_VirtualBin
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = 1470000065)
BEGIN
select count(*) as Count from "INC1" where "DocEntry"= @DocKey@ and "BinEntry" is not null
END
Else
BEGIN
select count(*) as Count from "ICD1" where "DocEntry"=@DocKey@ and "BinEntry" is not null
END
GO
CREATE PROCEDURE TmSp_Inventory_OpenBalance_VirtualBin
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = '310000001')
BEGIN
select count(*) as Count from IQI1 where DocEntry = @DocKey@ and BinEntry is not null
END
Else
BEGIN
select count(*) as Count from IOD1 where DocEntry = @DocKey@ and BinEntry is not null
END
GO
CREATE PROCEDURE TmSp_Inventory_OpenBalance_VirtualIQI1
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = '310000001')
BEGIN
select * from IQI1 T1 left outer join OBIN T2 on T1.BinEntry=T2.AbsEntry where T1.DocEntry = @DocKey@
END
Else
BEGIN
select * from IOD1 T1 left outer join OBIN T2 on T1.BinEntry=T2.AbsEntry where T1.DocEntry = @DocKey@
END
GO
CREATE PROCEDURE TmSp_Inventory_OpenBalance_VirtualOIQI
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = '310000001')
BEGIN
select * from OIQI T1 left outer join OPLN T2 on T1.PriceSrc=1 and T1.PriceList<>-1 and T1.PriceList=T2.ListNum where DocEntry = @DocKey@
END
Else
BEGIN
select * from OIOD T1 left outer join OPLN T2 on T1.PriceSrc=1 and T1.PriceList<>-1 and T1.PriceList=T2.ListNum where DocEntry = @DocKey@
END
GO
CREATE PROCEDURE TmSp_Inventory_Posting_VirtualBin
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = '10000071')
BEGIN
select count(*) as Count from IQR1 where DocEntry = @DocKey@ and BinEntry is not null
END
Else
BEGIN
select count(*) as Count from IPD1 where DocEntry = @DocKey@ and BinEntry is not null
END
GO
CREATE PROCEDURE TmSp_Inventory_Posting_VirtualIQR1
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = '10000071')
BEGIN
select * from "IQR1" "T1" left outer join "OBIN" "T2" on "T1"."BinEntry"="T2"."AbsEntry" where "T1"."DocEntry"=@DocKey@
END
Else
BEGIN
select * from "IPD1" "T1" left outer join "OBIN" "T2" on "T1"."BinEntry"="T2"."AbsEntry" where "T1"."DocEntry"=@DocKey@
END
GO
CREATE PROCEDURE TmSp_Inventory_Posting_VirtualOIQR
@DocKey@ INT ,
@ObjectId@ NCHAR(20)
AS
IF (@ObjectId@ = '10000071')
BEGIN
select * from OIQR T1 left outer join "OPLN" "T2" on "T1"."PriceSrc"=1 and "T1"."PriceList"<>-1 and "T1"."PriceList"="T2"."ListNum" where "T1"."DocEntry"=@DocKey@
END
Else
BEGIN
select * from OIPD T1 left outer join "OPLN" "T2" on "T1"."PriceSrc"=1 and "T1"."PriceList"<>-1 and "T1"."PriceList"="T2"."ListNum" where "T1"."DocEntry"=@DocKey@
END
GO
Create Proc TmSp_InventoryCountingRiskAssessmentReport
@priceListName char(32),
@riskcode char,
@ItemPara1Min nvarchar(32),
@ItemPara1Max nvarchar(32),
@ItemPara2Min nvarchar(32),
@ItemPara2Max nvarchar(32),
@ItemPara3Min nvarchar(32),
@ItemPara3Max nvarchar(32),
@WhsParaMin nvarchar(32),
@WhsParaMax nvarchar(32)
AS BEGIN
DECLARE @localcurrency nvarchar(3)
SET @localcurrency = (SELECT MainCurncy FROM OADM)
DECLARE @IsPricePerWH char(1)
SET @IsPricePerWH = (SELECT priceSys FROM OADM)
-- Create temporary table to store data to
CREATE TABLE #tblRiskTempData
(
ItemCode nvarchar(50),
ItemName nvarchar(200),
WhsCode nvarchar(8),
DaysSinceLastCount INT,
Risk1 nvarchar(10),
TransVolume numeric(19,6),
Risk2 nvarchar(10),
ValueOfItem numeric(19,6),
Risk3 nvarchar(10),
CalcRisk nvarchar(10)
)
DECLARE @high char
SET @high = cast('H' AS CHAR)
DECLARE @medium char
SET @medium = cast('M' AS CHAR)
DECLARE @low char
SET @low = cast('L' AS CHAR)
DECLARE @header nvarchar(2000)
SET @header =
'SELECT T2.ItemCode, T3.ItemName, T2.WhsCode,
DaysSinceLast = isnull(datediff(DAY, LastDate, getdate()),0),
isnull((select sum(InQty + OutQty) from OIVL T4 WHERE T4.LocCode = T2.WhsCode AND T4.ItemCode = T2.ItemCode
AND (T4.CreateDate > T2.LastDate OR (T4.CreateDate = T2.LastDate AND T4.CreateTime > T2.LastTime))), 0) AS TransVolume,'
DECLARE @tail nvarchar(3000)
SET @tail =
' FROM
(SELECT T1.ItemCode, T1.WhsCode, T1.AvgPrice,
LastDate = CASE WHEN datediff(DAY, T1.LastCountDate, T1.LastPostDate) < 0 THEN T1.LastCountDate
WHEN datediff(DAY, T1.LastCountDate, T1.LastPostDate) > 0 THEN T1.LastPostDate
WHEN datediff(DAY, T1.LastCountDate, T1.LastPostDate) IS NULL THEN
isnull(isnull(LastCountDate, LastPostDate), T1.CreateDate) END,
LastTime = CASE WHEN datediff(DAY, T1.LastCountDate, T1.LastPostDate) < 0 THEN T1.LastCountTime
WHEN datediff(DAY, T1.LastCountDate, T1.LastPostDate) > 0 THEN T1.LastPostTime
WHEN datediff(DAY, T1.LastCountDate, T1.LastPostDate) IS NULL THEN
isnull(isnull(LastCountTime, LastPostTime), 0) END
FROM (SELECT T0.ItemCode AS ItemCode, T0.WhsCode AS WhsCode, T0.CreateDate AS CreateDate, T0.AvgPrice AS AvgPrice,
(SELECT TOP 1 CountDate FROM INC1 WHERE INC1.WhsCode = T0.WhsCode AND INC1.ItemCode = T0.ItemCode ORDER BY INC1.CountDate DESC) AS LastCountDate,
(SELECT TOP 1 CountTime FROM INC1 WHERE INC1.WhsCode = T0.WhsCode AND INC1.ItemCode = T0.ItemCode ORDER BY INC1.CountDate DESC) AS LastCountTime,
(SELECT TOP 1 CountDate FROM IQR1 WHERE IQR1.WhsCode = T0.WhsCode AND IQR1.ItemCode = T0.ItemCode ORDER BY IQR1.CountDate DESC) AS LastPostDate,
(SELECT TOP 1 CountTime FROM IQR1 WHERE IQR1.WhsCode = T0.WhsCode AND IQR1.ItemCode = T0.ItemCode ORDER BY IQR1.CountDate DESC) AS LastPostTime
FROM OITW T0) T1) AS T2
INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode'
DECLARE @price nvarchar(2000)
IF @priceListName = 'Last Evaluated Price' BEGIN
SET @price = 'ValueOfItem = T3.LstEvlPric'
END
ELSE IF @priceListName = 'Last Purchase Price' BEGIN
SET @price =
' ValueOfItem = CASE WHEN LastPurCur = ''' + @localcurrency +''' THEN LastPurPrc
ELSE LastPurPrc * (
isnull(
CASE WHEN ORTT.RateDate = cast(convert(VARCHAR, getdate(), 102) AS DATETIME) THEN ORTT.Rate
ELSE NULL END, 0))
END '
SET @tail = @tail + ' LEFT JOIN ORTT ON T3.LastPurCur = ORTT.Currency'
END
ELSE IF @priceListName = 'Item Cost' BEGIN
SET @price =
' ValueOfItem = CASE WHEN EvalSystem = ''F'' AND
( CASE WHEN (SELECT sum(T5.OpenQty) FROM OIVQ T5 WHERE T2.WhsCOde = T5.LocCode AND T2.ItemCode = T5.ItemCode) = 0 THEN 0
ELSE (SELECT sum(T6.OpenValue)/sum(T6.OpenQty) FROM OIVQ T6 WHERE T2.WhsCOde = T6.LocCode AND T2.ItemCode = T6.ItemCode) END) > 0
THEN (SELECT sum(T7.OpenValue)/sum(T7.OpenQty) FROM OIVQ T7 WHERE T2.WhsCOde = T7.LocCode AND T2.ItemCode = T7.ItemCode)
ELSE (CASE WHEN ''' + @IsPricePerWH + '''=''Y'' THEN T2.AvgPrice
ELSE T3.AvgPrice
END)
END '
END
ELSE BEGIN
SET @price =
' ValueOfItem =
CASE WHEN ITM1.Currency = ''' + @localcurrency +''' THEN isnull(ITM1.Price , 0)
ELSE ITM1.Price * (
isnull(
CASE WHEN ORTT.RateDate = cast(convert(VARCHAR, getdate(), 102) AS DATETIME) THEN ORTT.Rate
ELSE NULL END, 0))
END '
SET @tail = @tail +
' INNER JOIN ITM1 ON T3.ItemCode = ITM1.ItemCode
INNER JOIN OPLN ON ITM1.PriceList = OPLN.ListNum AND OPLN.ListName = ''' + @priceListName + '''
LEFT JOIN ORTT ON ORTT.Currency = ITM1.Currency'
END
EXEC( 'insert into #tblRiskTempData(ItemCode, ItemName, WhsCode, DaysSinceLastCount, TransVolume, ValueOfItem) '
+ @header + @price + @tail + ' WHERE EXISTS(SELECT ItemCode FROM OIVL WHERE OIVL.ItemCode=T2.ItemCode AND OIVL.LocCode=T2.WhsCode)')
DECLARE @strsql as nvarchar(3000)
SET @strsql = 'UPDATE #tblRiskTempData SET
Risk1 = CASE WHEN DaysSinceLastCount > ' + @ItemPara1Max + ' THEN ''' + @high +
''' WHEN DaysSinceLastCount < ' + @ItemPara1Min + ' THEN ''' + @low +
''' ELSE ''' + @medium +''' END,
Risk2 = CASE WHEN TransVolume > ' + @ItemPara2Max + ' THEN ''' + @high +
''' WHEN TransVolume < ' + @ItemPara2Min + ' THEN ''' + @low +
''' ELSE ''' + @medium +''' END,
Risk3 = CASE WHEN ValueOfItem > ' + @ItemPara3Max + ' THEN ''' + @high +
''' WHEN ValueOfItem < ' + @ItemPara3Min + ' THEN ''' + @low +
''' ELSE ''' + @medium +''' END,
CalcRisk =
CASE WHEN (DaysSinceLastCount > ' + @ItemPara1Max + ') AND ((TransVolume > ' + @ItemPara2Max + ') OR (ValueOfItem > ' + @ItemPara3Max + ')) THEN ''' + @high +
''' WHEN DaysSinceLastCount < ' + @ItemPara1Min + ' AND TransVolume < ' + @ItemPara2Min + ' AND ValueOfItem < ' + @ItemPara3Min + ' THEN ''' + @low +
''' ELSE ''' + @medium +''' END'
EXEC(@strsql)
DECLARE @strwhssql as nvarchar(3000)
SET @strwhssql =
'SELECT #tblRiskTempData.ItemCode, #tblRiskTempData.ItemName,
#tblRiskTempData.WhsCode, #tblRiskTempData.DaysSinceLastCount,#tblRiskTempData.Risk1,
#tblRiskTempData.TransVolume, #tblRiskTempData.Risk2, #tblRiskTempData.ValueOfItem,
#tblRiskTempData.Risk3, #tblRiskTempData.CalcRisk, OBPL.BPLName, OBPL.BPLId FROM #tblRiskTempData
INNER JOIN OWHS ON #tblRiskTempData.WhsCode = OWHS.WhsCode COLLATE database_default
LEFT JOIN OBPL ON OWHS.BplId = OBPL.BPLId
WHERE #tblRiskTempData.WhsCode IN(
SELECT T0.WhsCode FROM (
(SELECT WhsCode, count(ItemCode) AS TOTAL FROM #tblRiskTempData GROUP BY WhsCode) T0
LEFT JOIN
(SELECT WhsCode, CalcRisk, isnull(count(ItemCode), 0)AS TotalByRisk FROM #tblRiskTempData
WHERE CalcRisk = '''+@high + '''
GROUP BY WhsCode, CalcRisk) T1
ON T0.WhsCode = T1.WhsCode )'
IF @riskcode = @high BEGIN
SET @strwhssql = @strwhssql +
'WHERE TOTAL <> 0 AND Cast(isnull(TotalByRisk, 0) AS FLOAT)/ TOTAL > ' + @WhsParaMax
END
ELSE IF @riskcode = @low BEGIN
SET @strwhssql = @strwhssql +
'WHERE (TOTAL <> 0 AND Cast(isnull(TotalByRisk, 0) AS FLOAT)/ TOTAL < ' + @WhsParaMin + ') OR TOTAL = 0'
END
ELSE BEGIN
SET @strwhssql = @strwhssql +
'WHERE TOTAL <> 0 AND Cast(isnull(TotalByRisk, 0) AS FLOAT)/ TOTAL >= ' + @WhsParaMin + ' AND
Cast(isnull(TotalByRisk, 0) AS FLOAT)/ TOTAL <= ' + @WhsParaMax
END
SET @strwhssql = @strwhssql + ') ORDER BY #tblRiskTempData.WhsCode, #tblRiskTempData.ItemCode'
EXEC (@strwhssql)
DROP TABLE #tblRiskTempData
END
优质生活从拆开始
浙公网安备 33010602011771号