SAP Business One 9.2: 系统存储过程之四
SAP
SAP Bysiness one 9.2在 示例 系统里 SQL Server 数据库名称： sap560 ,
以下是部分存储过程代码：
CREATE PROCEDURE TmSp_IVIFillUXILM
AS
BEGIN
declare @Result int
declare @ILMbaseTable as nvarchar(4)
declare @ILMbaseTableID as nvarchar(8)
set @Result = 0
TRUNCATE TABLE UXILM
set @ILMbaseTableID = (SELECT MsgSource FROM CIVI)
/*
UILM 10000162L
OILM 10000048L
*/
IF (@ILMbaseTableID=10000048 OR @ILMbaseTableID IS NULL)
set @ILMbaseTable = 'OILM'
ELSE IF (@ILMbaseTableID=10000162)
set @ILMbaseTable = 'UILM'
execute ('INSERT INTO UXILM
SELECT T0.MessageID, -1, -1, T0.DocEntry, T0.DocLineNum, T0.TransType
FROM ' + @ILMbaseTable + ' T0
WHERE T0.Transtype=202 AND T0.DocLineNum>-1
AND NOT EXISTS(SELECT U0.LineNum FROM WOR1 U0 WHERE U0.LineNum = T0.DocLineNum AND U0.DocEntry = T0.DocEntry )
AND NOT EXISTS (SELECT U0.BaseLine FROM IGE1 U0 WHERE U0.BaseType = (202) AND U0.BaseEntry = T0.DocEntry AND U0.BaseLine = T0.DocLineNum )
AND NOT EXISTS (SELECT U0.BaseLine FROM IGN1 U0 WHERE U0.BaseType = (202) AND U0.BaseEntry = T0.DocEntry AND U0.BaseLine = T0.DocLineNum )')
execute ('UPDATE T0
SET T0.MinMsgID=(SELECT MIN(T1.MessageID) FROM '+ @ILMbaseTable + ' T1 WHERE T1.DocLineNum=T0.DocLineNum AND T1.TransType=202 AND T1.DocEntry=T0.DocEntry) FROM UXILM T0')
execute ('UPDATE T0
SET T0.MaxMsgID=(SELECT MAX(T1.MessageID) FROM '+ @ILMbaseTable + ' T1 WHERE T1.DocLineNum=T0.DocLineNum AND T1.TransType=202 AND T1.DocEntry=T0.DocEntry) FROM UXILM T0')
execute ('DELETE T0 FROM UXILM T0
WHERE EXISTS(SELECT T1.MessageID
FROM ' + @ILMbaseTable + ' T1
WHERE T1.TransType=59 AND T1.BaseType=202 AND T1.BaseAbsEnt=T0.DocEntry AND
T1.MessageID>=T0.MinMsgID AND T1.MessageID<=T0.MaxMsgID)')
return 0;
end
GO
Create procedure TmSp_IVIInventoryValueDiffsFIFOItems
/*Inventory Value Differences - FIFO items_1.2 */
/* qty > 0 then UITM/UITW.stockvalue = sum(UIVL1.transvalue)= sum(UIVQ.openvalue)*/
/* qty = 0 then UITM/UITW.stockvalue = 0, sum(UIVL1.transvalue) = 0, sum(UIVQ.openvalue) = 0 */
as
begin
set nocount on
Declare @Perpetual_Inventory char(1)
Declare @isPricePerWH Char(1),@PriceDec TinyInt
declare @roundmthd char(1)
declare @maincurrency char(3)
declare @sumdec int
declare @deciamls int
declare @impact int
set @impact = 0
set @Perpetual_Inventory = (select continvnt from oadm)
--- Detect amount rounding
set @roundmthd = (select roundmthd from oadm)
set @maincurrency = (select maincurncy from oadm)
set @sumdec = (select sumdec from oadm)
set @deciamls = (select decimals from ocrn where currcode = @maincurrency)
Select @isPricePerWH = T1.PriceSys From OADM T1
if @roundmthd = 'Y' and @deciamls <> -1
begin
set @sumdec = @deciamls
end
If @Perpetual_Inventory = 'Y' and @isPricePerWH = 'Y'
/* Item cost is managed per warehouse */
Begin
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_Loccode_IVI_verification')
drop index UIVL.UIVL_itemcode_Loccode_IVI_verification
-- create index on UIVL
CREATE NONCLUSTERED INDEX UIVL_itemcode_Loccode_IVI_verification
ON UIVL (itemcode, loccode);
if object_id('tempdb..#UIVQ_Aggregation') is not null
drop table #UIVQ_Aggregation
select Itemcode,Loccode,sum(OpenValue) OpenValue
into #UIVQ_Aggregation
from UIVQ group by Itemcode,Loccode
--create index on aggragation table
CREATE NONCLUSTERED INDEX #UIVQ_Aggregation_itemcode_Loccode
ON #UIVQ_Aggregation (itemcode, loccode);
if object_id('tempdb..#UIVL_Aggregation') is not null
drop table #UIVL_Aggregation
select T0.ItemCode,T0.LocCode,SUM(T1.TransValue) as TransValue,SUM (isnull(T0.InQty,0) - isnull(T0.OutQty,0)) AS 'QtyInUIVL'
into #UIVL_Aggregation
from UIVL T0
Join UIVL1 T1
on T0.TransSeq = T1.TransSeq
group by T0.ItemCode,T0.LocCode
CREATE NONCLUSTERED INDEX #UIVL_Aggregation_itemcode_Loccode
ON #UIVL_Aggregation (itemcode, loccode);
if exists (
Select top 1 M.itemcode
From UITW W INNER join UITM M on W.itemcode = M.itemcode
INNER join #UIVL_Aggregation V on W.itemcode = V.Itemcode and W.Whscode = V.Loccode
INNER join #UIVQ_Aggregation Q on Q.Itemcode = V.Itemcode and Q.Loccode = V.Loccode
Where
M.evalsystem = 'F' AND W.Stockvalue <> V.Transvalue
OR M.evalsystem <> 'B' AND abs(
Case
when W.OnHand = 0 Then '0'
when W.OnHand < 0 Then W.StockValue
Else Q.OpenValue
end
- W.StockValue) > 1
OR M.evalsystem = 'B' AND Q.Openvalue <> 0
)
begin
set @impact = 1
end
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_Loccode_IVI_verification')
drop index UIVL.UIVL_itemcode_Loccode_IVI_verification
--drop index #UIVQ_Aggregation.#UIVQ_Aggregation_itemcode_Loccode
--drop index #UIVL_Aggregation.#UIVL_Aggregation_itemcode_Loccode
if object_id('tempdb..#UIVQ_Aggregation') is not null
drop table #UIVQ_Aggregation
if object_id('tempdb..#UIVL_Aggregation') is not null
drop table #UIVL_Aggregation
--end of if @Perpetual_Inventory = 'Y' and @isPricePerWH = 'Y'
end
else
If @Perpetual_Inventory = 'Y' and @isPricePerWH = 'N'
/* Item cost is managed per company */
Begin
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_IVI_verification')
drop index UIVL.UIVL_itemcode_IVI_verification
-- create index on UIVL
CREATE NONCLUSTERED INDEX UIVL_itemcode_IVI_veri
fication
ON UIVL (itemcode);
if object_id('tempdb..#UIVQ_Aggregation_Item') is not null
drop table #UIVQ_Aggregation_Item
select Itemcode,sum(OpenValue) OpenValue
into #UIVQ_Aggregation_Item
from UIVQ group by Itemcode
--create index on aggragation table
CREATE NONCLUSTERED INDEX #UIVQ_Aggregation_itemcode
ON #UIVQ_Aggregation_Item (itemcode);
if object_id('tempdb..#UIVL_Aggregation_Item') is not null
drop table #UIVL_Aggregation_Item
select T0.ItemCode,SUM(T1.TransValue) as TransValue,SUM (isnull(T0.InQty,0) - isnull(T0.OutQty,0)) AS 'QtyInUIVL'
into #UIVL_Aggregation_Item
from UIVL T0
Join UIVL1 T1
on T0.TransSeq = T1.TransSeq
group by T0.ItemCode
CREATE NONCLUSTERED INDEX #UIVL_Aggregation_itemcode
ON #UIVL_Aggregation_Item (itemcode);
if exists (
Select top 1 M.itemcode
From
UITM M INNER join #UIVL_Aggregation_Item V on M.itemcode = V.Itemcode
INNER join #UIVQ_Aggregation_Item Q on Q.Itemcode = V.Itemcode
Where
M.evalsystem = 'F' AND M.Stockvalue <> V.Transvalue
OR M.evalsystem <> 'B' AND abs((
Case
when M.OnHand = 0 Then '0'
else case
when M.OnHand < 0 then M.StockValue
Else Q.OpenValue
end
end) - M.StockValue) > 1
OR M.evalsystem = 'B' AND Q.Openvalue <> 0
)
begin
set @impact = 1
end
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_IVI_verification')
drop index UIVL.UIVL_itemcode_IVI_verification
--drop index #UIVQ_Aggregation.#UIVQ_Aggregation_itemcode
--drop index #UIVL_Aggregation.#UIVL_Aggregation_itemcode
if object_id('tempdb..#UIVQ_Aggregation_Item') is not null
drop table #UIVQ_Aggregation_Item
if object_id('tempdb..#UIVL_Aggregation_Item') is not null
drop table #UIVL_Aggregation_Item
--end of if @Perpetual_Inventory = 'Y' and @isPricePerWH = 'N'
end
if(@impact > 0)
/*Inventory Value recalculation for FIFO items failed*/
Return -1
else
/*Inventory Value recalculation for FIFO items is succesfull*/
Return 0
End
GO
/*Inventory Value Differences - MAP & STD items_1.2 */
/*This query verfies that: */
/*When in positive zone: sum(UIVL1.transvalue) = UITM/UITW.StockValue */
/*When qty is zero: sum(UIVL1.transvalue) = UITM/UITW.StockValue = 0*/
Create procedure TmSp_IVIInventoryValueDiffsMAPnSTDItems
as
begin
set nocount on
Declare @isPricePerWH Char(1),@PriceDec TinyInt
declare @roundmthd char(1)
declare @maincurrency char(3)
declare @sumdec int
declare @deciamls int
declare @impact int
set @impact = 0
--- Detect amount rounding
set @roundmthd = (select roundmthd from oadm)
set @maincurrency = (select maincurncy from oadm)
set @sumdec = (select sumdec from oadm)
set @deciamls = (select decimals from ocrn where currcode = @maincurrency)
Select @isPricePerWH = T1.PriceSys From OADM T1
if @roundmthd = 'Y' and @deciamls <> -1
begin
set @sumdec = @deciamls
end
If @isPricePerWH = 'Y'
/* Item cost is managed per warehouse */
Begin
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_Loccode_IVI_verification')
drop index UIVL.UIVL_itemcode_Loccode_IVI_verification
-- create index on UIVL
CREATE NONCLUSTERED INDEX UIVL_itemcode_Loccode_IVI_verification
ON UIVL (itemcode, loccode);
if object_id('tempdb..#UIVL_Aggregation') is not null
drop table #UIVL_Aggregation
select T0.ItemCode,T0.LocCode,SUM(T1.TransValue) as TransValue,SUM (isnull(T0.InQty,0) - isnull(T0.OutQty,0)) AS 'QtyInUIVL'
into #UIVL_Aggregation
from UIVL T0
Join UIVL1 T1
on T0.TransSeq = T1.TransSeq
group by T0.ItemCode,T0.LocCode
CREATE NONCLUSTERED INDEX #UIVL_Aggregation_itemcode_Loccode
ON #UIVL_Aggregation (itemcode, loccode);
if exists (
Select top 1 M.itemcode
From
UITW W INNER join UITM M on W.itemcode = M.itemcode INNER join #UIVL_Aggregation V on W.itemcode = V.Itemcode and W.Whscode = V.Loccode
where
M.evalsystem in ('A','S') and
(W.Stockvalue <> V.Transvalue or
W.OnHand = 0 and (W.StockValue > 1 or W.StockValue < -1))
)
begin
set @impact = 1
end
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_Loccode_IVI_verification')
drop index UIVL.UIVL_itemcode_Loccode_IVI_verification
--drop index #UIVL_Aggregation.#UIVL_Aggregation_itemcode_Loccode
if object_id('tempdb..#UIVL_Aggregation') is not null
drop table #UIVL_Aggregation
--end of if @Perpetual_Inventory = 'Y' and @isPricePerWH = 'Y'
end
else
If @isPricePerWH = 'N'
/* Item cost is managed per company */
Begin
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_IVI_verification')
drop index UIVL.UIVL_itemcode_IVI_verification
-- create index on UIVL
CREATE NONCLUSTERED INDEX UIVL_itemcode_IVI_verification
ON UIVL (itemcode);
if object_id('tempdb..#UIVL_Aggregation_Item') is not null
drop table #UIVL_Aggregation_Item
select T0.ItemCode,SUM(T1.TransValue) as TransValue,SUM (isnull(T0.InQty,0) - isnull(T0.OutQty,0)) AS 'QtyInUIVL'
into #UIVL_Aggregation_Item
from UIVL T0
Join UIVL1 T1
on T0.TransSeq = T1.TransSeq
group by T0.ItemCode
CREATE NONCLUSTERED INDEX #UIVL_Aggregation_itemcode
ON #UIVL_Aggregation_Item (itemcode);
if exists (
Select top 1 M.itemcode
From UITM M INNER join #UIVL_Aggregation_Item V on M.itemcode = V.Itemcode
Where
M.evalsystem in ('A','S') and
(M.Stockvalue <> V.Transvalue or
M.OnHand = 0 and (M.StockValue > 1 or M.StockValue < -1))
)
begin
set @impact = 1
end
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UIVL]') AND name = N'UIVL_itemcode_IVI_verification')
drop index UIVL.UIVL_itemcode_IVI_verification
--drop index #UIVL_Aggregation.#UIVL_Aggregation_itemcode
if object_id('tempdb..#UIVL_Aggregation_Item') is not null
drop table #UIVL_Aggregation_Item
--end of if @Perpetual_Inventory = 'Y' and @isPricePerWH = 'N'
end
if(@impact > 0)
/*Inventory Value recalculation for fifo items failed*/
Return -1
else
/*Inventory Value recalculation for fifo items is succesfull*/
Return 0
End
GO
CREATE PROCEDURE TmSp_IVIInventoryValueDiffsSNBItems
/*Inventory Value Differences - SnB items_1.0 */
as
BEGIN
set nocount on
Declare @Perpetual_Inventory char(1)
Declare @isPricePerWH Char(1)
-- Declare @PriceDec TinyInt
-- declare @roundmthd char(1)
-- declare @maincurrency char(3)
-- declare @sumdec int
-- declare @deciamls int
declare @impact int
set @impact = 0
set @Perpetual_Inventory = (SELECT ContInvnt FROM OADM)
set @isPricePerWH = (SELECT PriceSys From OADM)
--- Detect amount rounding
-- set @roundmthd = (SELECT RoundMthd FROM OADM)
-- set @maincurrency = (SELECT MainCurncy FROM OADM)
-- set @sumdec = (SELECT SumDec FROM OADM)
-- set @deciamls = (SELECT Decimals FROM OCRN WHERE CurrCode = @maincurrency)
-- if @roundmthd = 'Y' and @deciamls <> -1
-- begin
-- set @sumdec = @deciamls
-- end
If @Perpetual_Inventory = 'Y'
Begin
-- TEMP TABLES
if object_id('tempdb..#UBVL_Aggregation_Base') is not null
DROP TABLE #UBVL_Aggregation_Base
SELECT T0.ItemCode,T0.MDAbsEntry, T0.ManagedBy, SUM(T0.InvValue) InvValue, MAX(T0.AbsEntry) maxEntry
INTO #UBVL_Aggregation_Base
FROM UBVL T0
WHERE MdAbsEntry is not NULL
GROUP BY T0.ItemCode,T0.MDAbsEntry, T0.ManagedBy
if object_id('tempdb..#UBVL_Aggregation') is not null
DROP TABLE #UBVL_Aggregation
SELECT T0.*, T1.AccQty-t1.AccNegQ BVLQty
INTO #UBVL_Aggregation
FROM #UBVL_Aggregation_Base T0,UBVL T1
WHERE T1.AbsEntry=maxEntry
-- UITM.StockValue <=> UBVL.InvValue
If @isPricePerWH = 'Y'
Begin
if object_id('tempdb..#UITW_Aggregation') is not null
DROP TABLE #UITW_Aggregation
SELECT T0.ItemCode, SUM(T0.StockValue) ITW_StockValue
INTO #UITW_Aggregation
FROM UITW T0
GROUP BY T0.ItemCode
if exists(
SELECT #UITW_Aggregation.ItemCode, SUM(InvValue)
FROM #UITW_Aggregation, #UBVL_Aggregation
WHERE #UITW_Aggregation.ItemCode=#UBVL_Aggregation.ItemCode
GROUP BY #UITW_Aggregation.ItemCode, ITW_StockValue
HAVING ITW_StockValue <> SUM(InvValue)
)
begin
set @impact = 1
end
End -- If @isPricePerWH = 'Y'
Else
Begin
if exists(
SELECT UITM.ItemCode, SUM(InvValue)
FROM UITM, #UBVL_Aggregation
WHERE UITM.ItemCode=#UBVL_Aggregation.ItemCode
GROUP BY UITM.ItemCode, StockValue
HAVING StockValue <> SUM(InvValue)
)
begin
set @impact = 1
end
End
--UITM.OnHand <=> UBVL.calculatedOnHand
if (@impact = 0 AND exists(
SELECT UITM.ItemCode, SUM(bvlQty)
FROM UITM, #UBVL_Aggregation
WHERE UITM.ItemCode=#UBVL_Aggregation.ItemCode
GROUP BY UITM.ItemCode, OnHand
HAVING OnHand<>SUM(bvlQty)
))
begin
set @impact = 2
end
-- UBTN quantity pre batch <=> UBVL quantity per batch
if (@impact = 0 AND exists(
SELECT UBTN.ItemCode , BVLQty, (UBTN.Quantity - UBTN.QuantOut) as BTNQty
FROM #UBVL_Aggregation, UBTN
WHERE UBTN.ItemCode=#UBVL_Aggregation.ItemCode AND UBTN.AbsEntry=#UBVL_Aggregation.MdAbsEntry and #UBVL_Aggregation.ManagedBy = UBTN.ObjType
and BVLQty<>(UBTN.Quantity - UBTN.QuantOut)
))
begin
set @impact = 3
end
-- USRN quantity pre batch <=> UBVL quantity per batch
if (@impact = 0 AND exists(
SELECT USRN.ItemCode , BVLQty, (USRN.Quantity - USRN.QuantOut) as SRNQty
FROM #UBVL_Aggregation, USRN
WHERE USRN.ItemCode=#UBVL_Aggregation.ItemCode AND USRN.AbsEntry=#UBVL_Aggregation.MdAbsEntry and #UBVL_Aggregation.ManagedBy = USRN.ObjType
and BVLQty<>(USRN.Quantity - USRN.QuantOut)
))
begin
set @impact = 4
end
-- UIVL Qties and TransValues <=> UBVL Qties and TransValues
if (@impact = 0)
begin
-- TEMP table for UIVL
if object_id('tempdb..#UIVL_Aggregation_Item') is not null
drop table #UIVL_Aggregation_Item
SELECT T0.ItemCode,SUM(T1.TransValue) as TransValue,SUM (isnull(T0.InQty,0) - isnull(T0.OutQty,0)) AS 'QtyInUIVL'
into #UIVL_Aggregation_Item
FROM UIVL T0
Join UIVL1 T1
on T0.TransSeq = T1.TransSeq
WHERE T0.CostMethod='B'
GROUP BY T0.ItemCode
-- Stock Values
if (@impact = 0) AND exists(
SELECT T0.ItemCode, SUM(T1.InvValue) FROM #UIVL_Aggregation_Item T0, #UBVL_Aggregation T1
WHERE T0.ItemCode=T1.ItemCode
GROUP BY T0.ItemCode, T0.TransValue
HAVING T0.TransValue<>SUM(T1.InvValue))
begin
set @impact = 5
end
-- Stock Qties
if (@impact = 0) AND exists(
SELECT T0.ItemCode,(T0.QtyInUIVL) ,SUM(T1.BVLQty) FROM #UIVL_Aggregation_Item T0, #UBVL_Aggregation T1
WHERE T0.ItemCode=T1.ItemCode
GROUP BY T0.ItemCode,T0.QtyInUIVL
HAVING (T0.QtyInUIVL)<>SUM(T1.BVLQty))
begin
set @impact = 6
end
end -- UIVL <=> UBVL check
End
if(@impact > 0)
/*Inventory Value recalculation for SnB items failed*/
Return -1
else
/*Inventory Value recalculation for SnB items is succesfull*/
Return 0
END
GO
Create procedure TmSp_IVIVerifyInventoryRecalculation
As
Begin
Declare @Result int
set @Result = 0
If exists (SELECT TOP 1 itemcode from UITM where InvntItem ='Y' and evalsystem in ('S','A'))
Begin
EXEC @Result = TmSp_IVIInventoryValueDiffsMAPnSTDItems
End
If @Result <> -1
If exists (SELECT TOP 1 itemcode from UITM where InvntItem ='Y' and evalsystem = 'F')
Begin
EXEC @Result = TmSp_IVIInventoryValueDiffsFIFOItems
End
If @Result <> -1
If exists (SELECT TOP 1 itemcode from UITM where InvntItem ='Y' and evalsystem = 'B')
Begin
EXEC @Result = TmSp_IVIInventoryValueDiffsSNBItems
End
If @Result <> -1
return 0
/* Recalculation is succesfull */
else
return -1
/*Recalculation failed */
end
GO
Create procedure TmSp_IVIYearTransfer
AS
Declare @Count int
set @Count = 0
set @Count = (select count(1)
where
exists(select Docentry from odln where Instance >0)
or exists(select Docentry from ordn where Instance >0 )
or exists(select Docentry from oinv where Instance >0 )
or exists(select Docentry from orin where Instance >0 )
or exists(select Docentry from ocsi where Instance >0 )
or exists(select Docentry from ocsv where Instance >0 )
or exists(select Docentry from opdn where Instance >0 )
or exists(select Docentry from orpd where Instance >0 )
or exists(select Docentry from opch where Instance >0 )
or exists(select Docentry from orpc where Instance >0 )
or exists(select Docentry from ocpi where Instance >0 )
or exists(select Docentry from ocpv where Instance >0 ))
return @Count
GO
/********************************************************************************************************************/
/* TmSp_PaymentsAndDuePayments - Select data for Payments and Due Payments feature.
*/
/********************************************************************************************************************/
CREATE PROCEDURE [dbo].[TmSp_PaymentsAndDuePayments]
@CurrentYear SMALLINT = NULL,
@FromBPCode NVARCHAR(15) = NULL,
@ToBPCode NVARCHAR(15) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create temporary table to store data to
CREATE TABLE #tblPDPTempData
(
DocType nvarchar(20),
DocAbs int,
DocInstalmntID smallint,
CardCode nvarchar(15),
CardName nvarchar(100),
DocumentDate datetime,
DueDate datetime,
PaymentDate datetime,
DocTotal numeric(19,6),
OpenAmount numeric(19,6),
BoEStatus char(1),
BoEAbs int,
DaysOverdue int,
YearFlag char(1),
DueDateOverThreshold char(1),
ReconciliationTotal numeric(19,6),
SubDocType nvarchar(20),
SubDocAbs int,
SubDocInstalmntID smallint,
ReconcAbs int
)
-- Create cursor to loop through all vendors
-- from given range
DECLARE vendorCursor CURSOR LOCAL FOR
SELECT
CardCode
FROM
OCRD
WHERE
CardType = 'S'
AND CardCode >= @FromBPCode
AND CardCode <= @ToBPCode
ORDER BY
CardCode
OPEN vendorCursor
DECLARE @PreviousYear SMALLINT
DECLARE @PreviousYearStart DATETIME
DECLARE @CurrentYearEnd DATETIME
DECLARE @vendorCode NVARCHAR(15)
-- Initialize local variables
SET @PreviousYear = @CurrentYear - 1
SET @PreviousYearStart = CONVERT (DATETIME, CONVERT (VARCHAR, @PreviousYear) + CONVERT (VARCHAR, '0101'), 112)
SET @CurrentYearEnd = CONVERT (DATETIME, CONVERT (VARCHAR, @CurrentYear) + CONVERT (VARCHAR, '1231'), 112)
-- Initialize cursor variable
FETCH NEXT FROM vendorCursor
INTO @vendorCode
-- Loop
WHILE @@FETCH_STATUS = 0
BEGIN
-- Select PCH records
INSERT INTO #tblPDPTempData
SELECT
T0.[ObjType],
T1.[DocEntry],
T1.[InstlmntID],
T0.[CardCode],
T0.[CardName],
T0.[TaxDate],
T1.[DueDate],
NULL,
T1.[InsTotal],
(T1.[InsTotal] - T1.[PaidToDate]),
NULL,
NULL,
0,
N'N',
N'N',
(
SELECT
-ABS (Sum (Ix.ReconSum))
FROM
(
SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'18'
AND I1.SrcObjAbs = T0.DocEntry
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'46'
INNER JOIN OVPM I3 ON I3.DocNum = I2.SrcObjAbs
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
UNION SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'18'
AND I1.SrcObjAbs = T0.DocEntry
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'19'
INNER JOIN ORPC I3 ON I3.DocEntry = I2.SrcObjAbs
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
UNION SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'18'
AND I1.SrcObjAbs = T0.DocEntry
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'30'
INNER JOIN OJDT I3 ON I3.TransId = I2.SrcObjAbs
INNER JOIN JDT1 I4 ON I4.TransId = I3.TransId
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
AND I4.Debit <> 0
) Ix
),
NULL,
NULL,
NULL,
NULL
FROM
[dbo].[OPCH] T0
INNER JOIN [dbo].[PCH6] T1 ON T1.[DocEntry] = T0.[DocEntry]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T0.[CANCELED] = 'N'
AND T1.[InsTotal] > 0
-- Select payments for PCH records
UNION SELECT
T3.[ObjType],
T2.[DocEntry],
T2.[InstlmntID],
T3.[CardCode],
T3.[CardName],
T1.[TaxDate],
T1.[DocDueDate],
T1.[TaxDate],
T0.[SumApplied],
T0.[SumApplied],
T4.[BoeStatus],
T4.[BoeKey],
0,
N'N',
N'N',
NULL,
T1.[ObjType],
T1.[DocEntry],
-1,
NULL
FROM
[dbo].[VPM2] T0
INNER JOIN [dbo].[OVPM] T1 ON T1.[DocNum] = T0.[DocNum]
INNER JOIN [dbo].[PCH6] T2 ON T2.[DocEntry] = T0.[DocEntry]
AND T2.[InstlmntID] = T0.[InstId]
AND T0.[InvType] = N'18'
INNER JOIN [dbo].[OPCH] T3 ON T3.[DocEntry] = T2.[DocEntry]
LEFT OUTER JOIN [dbo].[OBOE] T4 ON T4.[BoeKey] = T1.[BoeAbs]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T1.[TaxDate] <= @CurrentYearEnd
)
AND T3.[CardCode] = @vendorCode
AND T3.[CANCELED] = 'N'
AND T2.[InsTotal] > 0
AND T1.[Canceled] = 'N'
-- Select credit memos for PCH records
UNION SELECT
T0.[ObjType],
T1.[DocEntry],
T1.[InstlmntID],
T5.[CardCode],
T5.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T6.[InsTotal],
(T6.[InsTotal] - T6.[PaidToDate]),
NULL,
NULL,
-10,
N'N',
N'N',
NULL,
T6.[ObjType],
T6.[DocEntry],
T6.[InstlmntID],
NULL
FROM
[dbo].[OPCH] T0
INNER JOIN [dbo].[PCH6] T1 ON T1.[DocEntry] = T0.[DocEntry]
INNER JOIN [dbo].[OITR] T2 ON T2.[ReconType] = N'4'
INNER JOIN [dbo].[ITR1] T3 ON T3.[ReconNum] = T2.[ReconNum]
AND T3.[SrcObjAbs] = T0.[DocEntry]
AND T3.[SrcObjTyp] = N'18'
INNER JOIN [dbo].[ITR1] T4 ON T4.[ReconNum] = T3.[ReconNum]
AND T4.[SrcObjTyp] = N'19'
INNER JOIN [dbo].[ORPC] T5 ON T5.[DocEntry] = T4.[SrcObjAbs]
INNER JOIN [dbo].[RPC6] T6 ON T6.[DocEntry] = T5.[DocEntry]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T0.[CANCELED] = 'N'
AND T1.[InsTotal] > 0
AND T2.[CancelAbs] = 0
-- Select reconciled payments for PCH records
UNION SELECT
T3.[ObjType],
T3.[DocEntry],
T4.[InstlmntID],
T3.[CardCode],
T3.[CardName],
T5.[TaxDate],
T5.[DocDueDate],
T5.[TaxDate],
T1.[ReconSum],
T1.[ReconSum],
T6.[BoeStatus],
T6.[BoeKey],
0,
N'N',
N'N',
T2.[ReconSum],
T5.[ObjType],
T5.[DocEntry],
-1,
T0.[ReconNum]
FROM
[dbo].[OITR] T0
INNER JOIN [dbo].[ITR1] T1 ON T1.[ReconNum] = T0.[ReconNum]
AND T1.[SrcObjTyp] = N'18'
INNER JOIN [dbo].[ITR1] T2 ON T2.[ReconNum] = T0.[ReconNum]
AND T2.[SrcObjTyp] = N'46'
INNER JOIN [dbo].[OPCH] T3 ON T3.[DocEntry] = T1.[SrcObjAbs]
INNER JOIN [dbo].[PCH6] T4 ON T4.[DocEntry] = T3.[DocEntry]
AND T1.[TransRowId] = T4.[InstlmntID] - 1
INNER JOIN [dbo].[OVPM] T5 ON T5.[DocEntry] = T2.[SrcObjAbs]
LEFT OUTER JOIN [dbo].[OBOE] T6 ON T6.[BoeKey] = T5.[BoeAbs]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T3.[CardCode] = @vendorCode
AND T5.[Canceled] = 'N'
AND T0.[CancelAbs] = 0
AND T0.[ReconType] = N'0'
-- Select reconciled credit memos for PCH records
UNION SELECT
T3.[ObjType],
T3.[DocEntry],
T4.[InstlmntID],
T3.[CardCode],
T3.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T1.[ReconSum],
T1.[ReconSum],
NULL,
NULL,
-10,
N'N',
N'N',
T2.[Reconsum],
T6.[ObjType],
T6.[DocEntry],
T6.[InstlmntID],
T0.[ReconNum]
FROM
[dbo].[OITR] T0
INNER JOIN [dbo].[ITR1] T1 ON T1.[ReconNum] = T0.[ReconNum]
AND T1.[SrcObjTyp] = N'18'
INNER JOIN [dbo].[ITR1] T2 ON T2.[ReconNum] = T0.[ReconNum]
AND T2.[SrcObjTyp] = N'19'
INNER JOIN [dbo].[OPCH] T3 ON T3.[DocEntry] = T1.[SrcObjAbs]
INNER JOIN [dbo].[PCH6] T4 ON T4.[DocEntry] = T3.[DocEntry]
INNER JOIN [dbo].[ORPC] T5 ON T5.[DocEntry] = T2.[SrcObjAbs]
INNER JOIN [dbo].[RPC6] T6 ON T6.[DocEntry] = T5.[DocEntry]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T3.[CardCode] = @vendorCode
AND T5.[CANCELED] = 'N'
AND T0.[CancelAbs] = 0
AND T0.[ReconType] = N'0'
-- Select reconciled journal entries (CM type) for PCH records
UNION SELECT
T0.[ObjType],
T1.[DocEntry],
T1.[InstlmntID],
T7.[CardCode],
T7.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T3.[ReconSum],
T3.[ReconSum],
NULL,
NULL,
-10,
N'N',
N'N',
T4.[ReconSum],
T6.[ObjType],
T6.[TransId],
T6.[Line_ID] + 1,
T2.[ReconNum]
FROM
[dbo].[OPCH] T0
INNER JOIN [dbo].[PCH6] T1 ON T1.[DocEntry] = T0.[DocEntry]
INNER JOIN [dbo].[OITR] T2 ON T2.[ReconType] = N'0'
INNER JOIN [dbo].[ITR1] T3 ON T3.[ReconNum] = T2.[ReconNum]
AND T3.[SrcObjAbs] = T0.[DocEntry]
AND T3.[SrcObjTyp] = N'18'
INNER JOIN [dbo].[ITR1] T4 ON T4.[ReconNum] = T3.[ReconNum]
AND T4.[SrcObjTyp] = N'30'
INNER JOIN [dbo].[OJDT] T5 ON T5.[TransId] = T4.[SrcObjAbs]
INNER JOIN [dbo].[JDT1] T6 ON T6.[TransId] = T5.[TransId]
INNER JOIN [dbo].[OCRD] T7 ON T7.[CardCode] = T6.[ShortName]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T0.[CANCELED] = 'N'
AND T1.[InsTotal] > 0
AND T6.[Debit] <> 0
AND T2.[CancelAbs] = 0
-- Select DPO records
UNION SELECT
T0.[ObjType],
T1.[DocEntry],
T1.[InstlmntID],
T0.[CardCode],
T0.[CardName],
T0.[TaxDate],
T1.[DueDate],
NULL,
T1.[InsTotal],
(T1.[InsTotal] - T1.[PaidToDate]),
NULL,
NULL,
0,
N'N',
N'N',
(
SELECT
-ABS (Sum (Ix.ReconSum))
FROM
(
SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'204'
AND I1.SrcObjAbs = T0.DocEntry
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'46'
INNER JOIN OVPM I3 ON I3.DocNum = I2.SrcObjAbs
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
UNION SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'204'
AND I1.SrcObjAbs = T0.DocEntry
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'19'
INNER JOIN ORPC I3 ON I3.DocEntry = I2.SrcObjAbs
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
UNION SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'204'
AND I1.SrcObjAbs = T0.DocEntry
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'30'
INNER JOIN OJDT I3 ON I3.TransId = I2.SrcObjAbs
INNER JOIN JDT1 I4 ON I4.TransId = I3.TransId
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
AND I4.Debit <> 0
) Ix
),
NULL,
NULL,
NULL,
NULL
FROM
[dbo].[ODPO] T0
INNER JOIN [dbo].[DPO6] T1 ON T1.[DocEntry] = T0.[DocEntry]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T0.[CANCELED] = 'N'
AND T1.[InsTotal] > 0
-- Select payments for DPO records
UNION SELECT
T3.[ObjType],
T2.[DocEntry],
T2.[InstlmntID],
T3.[CardCode],
T3.[CardName],
T3.[TaxDate],
T2.[DueDate],
T1.[TaxDate],
T0.[SumApplied],
T0.[SumApplied],
T4.[BoeStatus],
T4.[BoeKey],
0,
N'N',
N'N',
NULL,
T1.[ObjType],
T1.[DocEntry],
-1,
NULL
FROM
[dbo].[VPM2] T0
INNER JOIN [dbo].[OVPM] T1 ON T1.[DocNum] = T0.[DocNum]
INNER JOIN [dbo].[DPO6] T2 ON T2.[DocEntry] = T0.[DocEntry]
AND T2.[InstlmntID] = T0.[InstId]
AND T0.[InvType] = N'204'
INNER JOIN [dbo].[ODPO] T3 ON T3.[DocEntry] = T2.[DocEntry]
LEFT OUTER JOIN [dbo].[OBOE] T4 ON T4.[BoeKey] = T1.[BoeAbs]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T1.[TaxDate] <= @CurrentYearEnd
)
AND T3.[CardCode] = @vendorCode
AND T3.[CANCELED] = 'N'
AND T2.[InsTotal] > 0
AND T1.[Canceled] = 'N'
-- Select credit memos for DPO records
UNION SELECT
T0.[ObjType],
T1.[DocEntry],
T1.[InstlmntID],
T5.[CardCode],
T5.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T6.[InsTotal],
(T6.[InsTotal] - T6.[PaidToDate]),
NULL,
NULL,
-10,
N'N',
N'N',
NULL,
T6.[ObjType],
T6.[DocEntry],
T6.[InstlmntID],
NULL
FROM
[dbo].[ODPO] T0
INNER JOIN [dbo].[DPO6] T1 ON T1.[DocEntry] = T0.[DocEntry]
INNER JOIN [dbo].[OITR] T2 ON T2.[ReconType] = N'4'
INNER JOIN [dbo].[ITR1] T3 ON T3.[ReconNum] = T2.[ReconNum]
AND T3.[SrcObjAbs] = T0.[DocEntry]
AND T3.[SrcObjTyp] = N'204'
INNER JOIN [dbo].[ITR1] T4 ON T4.[ReconNum] = T3.[ReconNum]
AND T4.[SrcObjTyp] = N'19'
INNER JOIN [dbo].[ORPC] T5 ON T5.[DocEntry] = T4.[SrcObjAbs]
INNER JOIN [dbo].[RPC6] T6 ON T6.[DocEntry] = T5.[DocEntry]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T0.[CANCELED] = 'N'
AND T1.[InsTotal] > 0
AND T2.[CancelAbs] = 0
-- Select reconciled payments for DPO records
UNION SELECT
T3.[ObjType],
T3.[DocEntry],
T4.[InstlmntID],
T3.[CardCode],
T3.[CardName],
T5.[TaxDate],
T5.[DocDueDate],
T5.[TaxDate],
T1.[ReconSum],
T1.[ReconSum],
T6.[BoeStatus],
T6.[BoeKey],
0,
N'N',
N'N',
T2.[ReconSum],
T5.[ObjType],
T5.[DocEntry],
-1,
T0.[ReconNum]
FROM
[dbo].[OITR] T0
INNER JOIN [dbo].[ITR1] T1 ON T1.[ReconNum] = T0.[ReconNum]
AND T1.[SrcObjTyp] = N'204'
INNER JOIN [dbo].[ITR1] T2 ON T2.[ReconNum] = T0.[ReconNum]
AND T2.[SrcObjTyp] = N'46'
INNER JOIN [dbo].[ODPO] T3 ON T3.[DocEntry] = T1.[SrcObjAbs]
INNER JOIN [dbo].[DPO6] T4 ON T4.[DocEntry] = T3.[DocEntry]
AND T1.[TransRowId] = T4.[InstlmntID] - 1
INNER JOIN [dbo].[OVPM] T5 ON T5.[DocEntry] = T2.[SrcObjAbs]
LEFT OUTER JOIN [dbo].[OBOE] T6 ON T6.[BoeKey] = T5.[BoeAbs]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T3.[CardCode] = @vendorCode
AND T5.[Canceled] = 'N'
AND T0.[CancelAbs] = 0
AND T0.[ReconType] = N'0'
-- Select reconciled credit memos for DPO records
UNION SELECT
T3.[ObjType],
T3.[DocEntry],
T4.[InstlmntID],
T3.[CardCode],
T3.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T1.[ReconSum],
T1.[ReconSum],
NULL,
NULL,
-10,
N'N',
N'N',
T2.[ReconSum],
T6.[ObjType],
T6.[DocEntry],
T6.[InstlmntID],
T0.[ReconNum]
FROM
[dbo].[OITR] T0
INNER JOIN [dbo].[ITR1] T1 ON T1.[ReconNum] = T0.[ReconNum]
AND T1.[SrcObjTyp] = N'204'
INNER JOIN [dbo].[ITR1] T2 ON T2.[ReconNum] = T0.[ReconNum]
AND T2.[SrcObjTyp] = N'19'
INNER JOIN [dbo].[ODPO] T3 ON T3.[DocEntry] = T1.[SrcObjAbs]
INNER JOIN [dbo].[DPO6] T4 ON T4.[DocEntry] = T3.[DocEntry]
INNER JOIN [dbo].[ORPC] T5 ON T5.[DocEntry] = T2.[SrcObjAbs]
INNER JOIN [dbo].[RPC6] T6 ON T6.[DocEntry] = T5.[DocEntry]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T3.[CardCode] = @vendorCode
AND T5.[CANCELED] = 'N'
AND T0.[CancelAbs] = 0
AND T0.[ReconType] = N'0'
-- Select reconciled journal entries (CM type) for DPO records
UNION SELECT
T0.[ObjType],
T1.[DocEntry],
T1.[InstlmntID],
T7.[CardCode],
T7.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T3.[ReconSum],
T3.[ReconSum],
NULL,
NULL,
-10,
N'N',
N'N',
T4.[ReconSum],
T6.[ObjType],
T6.[TransId],
T6.[Line_ID] + 1,
T2.[ReconNum]
FROM
[dbo].[ODPO] T0
INNER JOIN [dbo].[DPO6] T1 ON T1.[DocEntry] = T0.[DocEntry]
INNER JOIN [dbo].[OITR] T2 ON T2.[ReconType] = N'0'
INNER JOIN [dbo].[ITR1] T3 ON T3.[ReconNum] = T2.[ReconNum]
AND T3.[SrcObjAbs] = T0.[DocEntry]
AND T3.[SrcObjTyp] = N'204'
INNER JOIN [dbo].[ITR1] T4 ON T4.[ReconNum] = T3.[ReconNum]
AND T4.[SrcObjTyp] = N'30'
INNER JOIN [dbo].[OJDT] T5 ON T5.[TransId] = T4.[SrcObjAbs]
INNER JOIN [dbo].[JDT1] T6 ON T6.[TransId] = T5.[TransId]
INNER JOIN [dbo].[OCRD] T7 ON T7.[CardCode] = T6.[ShortName]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T0.[CANCELED] = 'N'
AND T1.[InsTotal] > 0
AND T6.[Debit] <> 0
AND T2.[CancelAbs] = 0
-- Select JE records (invoice type)
UNION SELECT
T0.[ObjType],
T1.[TransId],
T1.[Line_ID] + 1,
T2.[CardCode],
T2.[CardName],
T0.[TaxDate],
T1.[DueDate],
NULL,
T1.[Credit],
T1.[BalDueCred],
NULL,
NULL,
0,
N'N',
N'N',
(
SELECT
-ABS (Sum (Ix.ReconSum))
FROM
(
SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'30'
AND I1.SrcObjAbs = T0.TransId
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'46'
INNER JOIN OVPM I3 ON I3.DocNum = I2.SrcObjAbs
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
UNION SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'30'
AND I1.SrcObjAbs = T0.TransId
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'19'
INNER JOIN ORPC I3 ON I3.DocEntry = I2.SrcObjAbs
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
UNION SELECT
I2.ReconSum
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
AND I1.SrcObjTyp = N'30'
AND I1.SrcObjAbs = T0.TransId
INNER JOIN ITR1 I2 ON I2.ReconNum = I0.ReconNum
AND I2.SrcObjTyp = N'30'
AND I2.SrcObjAbs <> T0.TransId
INNER JOIN OJDT I3 ON I3.TransId = I2.SrcObjAbs
INNER JOIN JDT1 I4 ON I4.TransId = I3.TransId
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I3.TaxDate > @CurrentYearEnd
AND I4.Debit <> 0
) Ix
),
NULL,
NULL,
NULL,
NULL
FROM
[dbo].[OJDT] T0
INNER JOIN [dbo].[JDT1] T1 ON T1.[TransId] = T0.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T2.[CardCode] = T1.[ShortName]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
)
AND T1.[ShortName] = @vendorCode
AND T0.[TransType] IN (N'30',N'-2',N'-3')
AND T1.[Credit] <> 0
AND T0.[StornoToTr] IS NULL
AND NOT EXISTS (
SELECT
T0.[TransId]
FROM
[dbo].[OJDT] U0
WHERE
U0.[StornoToTr] = T0.[TransId]
)
-- Select payments for JE records
UNION SELECT
T3.[ObjType],
T2.[TransId],
T2.[Line_ID] + 1,
T4.[CardCode],
T4.[CardName],
T1.[TaxDate],
T1.[DocDueDate],
T1.[TaxDate],
T0.[SumApplied],
T0.[SumApplied],
T5.[BoeStatus],
T5.[BoeKey],
0,
N'N',
N'N',
NULL,
T1.[ObjType],
T1.[DocEntry],
-1,
NULL
FROM
[dbo].[VPM2] T0
INNER JOIN [dbo].[OVPM] T1 ON T1.[DocNum] = T0.[DocNum]
INNER JOIN [dbo].[JDT1] T2 ON T2.[TransId] = T0.[DocEntry]
AND T0.[InvType] = N'30'
INNER JOIN [dbo].[OJDT] T3 ON T3.[TransId] = T2.[TransId]
INNER JOIN [dbo].[OCRD] T4 ON T4.[CardCode] = T2.[ShortName]
LEFT OUTER JOIN [dbo].[OBOE] T5 ON T5.[BoeKey] = T1.[BoeAbs]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T1.[TaxDate] <= @CurrentYearEnd
)
AND T2.[ShortName] = @vendorCode
AND T3.[TransType] IN (N'30',N'-2',N'-3')
AND T2.[Credit] <> 0
AND T3.[StornoToTr] IS NULL
AND NOT EXISTS (
SELECT
T0.[DocEntry]
FROM
[dbo].[OJDT] U0
WHERE
U0.[StornoToTr] = T3.[TransId]
)
AND T1.[Canceled] = 'N'
-- Select reconciled payments for JE records
UNION SELECT
T3.[ObjType],
T4.[TransId],
T4.[Line_ID] + 1,
T7.[CardCode],
T7.[CardName],
T5.[TaxDate],
T5.[DocDueDate],
T5.[TaxDate],
T1.[ReconSum],
T1.[ReconSum],
T6.[BoeStatus],
T6.[BoeKey],
0,
N'N',
N'N',
T2.[ReconSum],
T5.[ObjType],
T5.[DocEntry],
-1,
T0.[ReconNum]
FROM
[dbo].[OITR] T0
INNER JOIN [dbo].[ITR1] T1 ON T1.[ReconNum] = T0.[ReconNum]
AND T1.[SrcObjTyp] = N'30'
INNER JOIN [dbo].[ITR1] T2 ON T2.[ReconNum] = T0.[ReconNum]
AND T2.[SrcObjTyp] = N'46'
INNER JOIN [dbo].[OJDT] T3 ON T3.[TransId] = T1.[SrcObjAbs]
INNER JOIN [dbo].[JDT1] T4 ON T4.[TransId] = T3.[TransId]
AND T1.[TransRowId] = T4.[Line_ID]
INNER JOIN [dbo].[OVPM] T5 ON T5.[DocEntry] = T2.[SrcObjAbs]
LEFT OUTER JOIN [dbo].[OBOE] T6 ON T6.[BoeKey] = T5.[BoeAbs]
INNER JOIN [dbo].[OCRD] T7 ON T7.[CardCode] = T1.[ShortName]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T4.[ShortName] = @vendorCode
AND T4.[Credit] <> 0
AND T3.[StornoToTr] IS NULL
AND NOT EXISTS (
SELECT
T0.ReconNum
FROM
[dbo].[OJDT] U0
WHERE
U0.[StornoToTr] = T3.[TransId]
)
AND T5.[Canceled] = 'N'
AND T0.[CancelAbs] = 0
AND T0.[ReconType] = N'0'
-- Select reconciled credit memos for JE records
UNION SELECT
T3.[ObjType],
T4.[TransId],
T4.[Line_ID] + 1,
T7.[CardCode],
T7.[CardName],
T5.[TaxDate],
T5.[DocDueDate],
T5.[TaxDate],
T1.[ReconSum],
T1.[ReconSum],
NULL,
NULL,
-10,
N'N',
N'N',
T2.[ReconSum],
T6.[ObjType],
T6.[DocEntry],
T6.[InstlmntID],
T0.[ReconNum]
FROM
[dbo].[OITR] T0
INNER JOIN [dbo].[ITR1] T1 ON T1.[ReconNum] = T0.[ReconNum]
AND T1.[SrcObjTyp] = N'30'
INNER JOIN [dbo].[ITR1] T2 ON T2.[ReconNum] = T0.[ReconNum]
AND T2.[SrcObjTyp] = N'19'
INNER JOIN [dbo].[OJDT] T3 ON T3.[TransId] = T1.[SrcObjAbs]
INNER JOIN [dbo].[JDT1] T4 ON T4.[TransId] = T3.[TransId]
INNER JOIN [dbo].[ORPC] T5 ON T5.[DocEntry] = T2.[SrcObjAbs]
INNER JOIN [dbo].[RPC6] T6 ON T6.[DocEntry] = T6.[DocEntry]
INNER JOIN [dbo].[OCRD] T7 ON T7.[CardCode] = T1.[ShortName]
WHERE
(
T3.[TaxDate] >= @PreviousYearStart
AND T3.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T4.[ShortName] = @vendorCode
AND T4.[Credit] <> 0
AND T3.[StornoToTr] IS NULL
AND NOT EXISTS (
SELECT
T0.[ReconNum]
FROM
[dbo].[OJDT] U0
WHERE
U0.[StornoToTr] = T3.[TransId]
)
AND T5.[CANCELED] = 'N'
AND T0.[CancelAbs] = 0
AND T0.[ReconType] = N'0'
-- Select reconciled journal entries (CM type) for JE (invoice type) records
UNION SELECT
T0.[ObjType],
T1.[TransId],
T1.[Line_ID] + 1,
T7.[CardCode],
T7.[CardName],
T5.[TaxDate],
T6.[DueDate],
T5.[TaxDate],
T3.[ReconSum],
T3.[ReconSum],
NULL,
NULL,
-10,
N'N',
N'N',
T4.[ReconSum],
T6.[ObjType],
T6.[TransId],
T6.[Line_ID] + 1,
T2.[ReconNum]
FROM
[dbo].[OJDT] T0
INNER JOIN [dbo].[JDT1] T1 ON T1.[TransId] = T0.[TransId]
AND T1.[Credit] <> 0
INNER JOIN [dbo].[OITR] T2 ON T2.[ReconType] = N'0'
INNER JOIN [dbo].[ITR1] T3 ON T3.[ReconNum] = T2.[ReconNum]
AND T3.[SrcObjAbs] = T0.[TransId]
AND T3.[SrcObjTyp] = N'30'
INNER JOIN [dbo].[ITR1] T4 ON T4.[ReconNum] = T3.[ReconNum]
AND T4.[SrcObjTyp] = N'30'
INNER JOIN [dbo].[OJDT] T5 ON T5.[TransId] = T4.[SrcObjAbs]
INNER JOIN [dbo].[JDT1] T6 ON T6.[TransId] = T5.[TransId]
INNER JOIN [dbo].[OCRD] T7 ON T7.[CardCode] = T6.[ShortName]
AND T7.[CardCode] = T1.[ShortName]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
AND T5.[TaxDate] <= @CurrentYearEnd
)
AND T7.[CardCode] = @vendorCode
AND T6.[Debit] <> 0
AND T2.[CancelAbs] = 0
-- Select non-based payments (they may be paid by BoE
-- and may be partially reconciled)
UNION SELECT
T0.ObjType,
T0.DocEntry,
-1,
T0.CardCode,
T0.CardName,
T0.TaxDate,
T0.DocDueDate,
T0.TaxDate,
T0.NoDocSum,
T0.NoDocSum,
(
SELECT
BoeStatus
FROM
OBOE B0
WHERE
B0.BoeKey = T0.BoeAbs
),
T0.BoeAbs,
0,
'N',
'N',
(
SELECT
Sum (I1.ReconSum)
FROM
OITR I0
INNER JOIN ITR1 I1 ON I1.ReconNum = I0.ReconNum
WHERE
I0.CancelAbs = 0
AND I0.ReconType IN (0, 3)
AND I1.SrcObjTyp = 46
AND I1.SrcObjAbs = T0.DocEntry
),
T0.ObjType,
T0.DocEntry,
-1,
NULL
FROM
OVPM T0
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
)
AND T0.CardCode = @vendorCode
AND T0.NoDocSum > 0
-- Select non-based credit memos
UNION SELECT
T0.[ObjType],
T1.[DocEntry],
-1,
T0.[CardCode],
T0.[CardName],
T0.[TaxDate],
T1.[DueDate],
NULL,
T1.[InsTotal],
(T1.[InsTotal] - T1.[PaidToDate]),
NULL,
NULL,
-10,
N'N',
N'N',
NULL,
NULL,
NULL,
NULL,
NULL
FROM
[dbo].[ORPC] T0
INNER JOIN [dbo].[RPC6] T1 ON T1.[DocEntry] = T0.[DocEntry]
LEFT OUTER JOIN [dbo].[ITR1] T2 ON T2.[SrcObjAbs] = T0.[DocEntry]
AND T2.[SrcObjTyp] = N'19'
LEFT OUTER JOIN [dbo].[OITR] T3 ON T3.[ReconNum] = T2.[ReconNum]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
)
AND T0.[CardCode] = @vendorCode
AND T1.[InsTotal] > 0
AND T1.[InsTotal] > T1.[PaidToDate]
AND
(
T3.[ReconType] IS NULL
OR T3.[ReconType] <> N'4'
)
-- Select JE records (credit memo type)
UNION SELECT
T0.[ObjType],
T1.[TransId],
T1.[Line_ID] + 1,
T2.[CardCode],
T2.[CardName],
T0.[TaxDate],
T1.[DueDate],
NULL,
T1.[Debit],
T1.[BalDueDeb],
NULL,
NULL,
-10,
N'N',
N'N',
NULL,
NULL,
NULL,
NULL,
NULL
FROM
[dbo].[OJDT] T0
INNER JOIN [dbo].[JDT1] T1 ON T1.[TransId] = T0.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T2.[CardCode] = T1.[ShortName]
WHERE
(
T0.[TaxDate] >= @PreviousYearStart
AND T0.[TaxDate] <= @CurrentYearEnd
)
AND T1.[ShortName] = @vendorCode
AND T0.[TransType] IN (N'30',N'-2',N'-3')
AND T1.[Debit] <> 0
AND T1.[BalDueDeb] > 0
AND T0.[StornoToTr] IS NULL
AND NOT EXISTS (
SELECT
T0.[TransId]
FROM
[dbo].[OJDT] U0
WHERE
U0.[StornoToTr] = T0.[TransId]
)
FETCH NEXT FROM vendorCursor
INTO @vendorCode
END
-- Close cursor
CLOSE vendorCursor
DEALLOCATE vendorCursor
-- Present collected data
SELECT
DocType AS 'Doc. No. (Type)', -- 1
DocAbs AS 'Doc. No. (Doc. number)', -- 2
DocInstalmntID AS 'Doc. No. (Installment ID)', -- 3
CardCode AS 'Card Code', -- 4
CardName AS 'Card Name', -- 5
DocumentDate AS 'Document Date', -- 6
DueDate AS 'Due Date', -- 7
PaymentDate AS 'Payment Date', -- 8
DocTotal AS 'Document Total', -- 9
OpenAmount AS 'Open Amount', -- 10
BoEStatus AS 'BoE Status', -- 11
BoEAbs AS 'BoE Key', -- 12
DaysOverdue AS 'Days Overdue', -- 13
YearFlag AS 'Year Flag', -- 14
DueDateOverThreshold AS 'Due Date is Over Threshold', -- 15
ReconciliationTotal AS 'Reconciliation Total', -- 16
SubDocType AS 'Sub Document Type', -- 17
SubDocAbs AS 'Sub Document Number', -- 18
SubDocInstalmntID AS 'Sub Document Installment', --19
ReconcAbs AS 'Reconciliation Number' --20
FROM
#tblPDPTempData
ORDER BY
1, 2, 3, 17, 18, 19, 20
-- Close table
DROP TABLE #tblPDPTempData
END
GO
CREATE PROCEDURE TmSp_PicknPackCreateTempTable
@docTable varchar(64)
AS
declare @tempTable sysname
declare @count int
declare @i int
declare @tablePrefix sysname
-- generate name for temp table >>>
set @tablePrefix = '##TMP_DOC1_'
set @count = 1
set @tempTable = ''
set @i = 0
while (@count <> 0)
begin
declare @numStr sysname
set @numStr = cast(@i as sysname)
set @tempTable = @tablePrefix + @numStr
select @count = count(*) from tempdb.dbo.sysobjects with(nolock) where xtype = 'u' and name = @tempTable
set @i = @i + 1
end
-- <<< generate name for docentry temp table
if (@docTable = 'ORDR')
begin
exec('
select
t0.docentry, t1.itemcode, t1.whscode, t1.dropship, t1.FromWhsCod, t1.linestatus, t1.treetype, t1.shipdate, t1.dscription,
t1.quantity, t1.pickoty, t1.OpenCreQty, t1.DelivrdQty, t1.pickstatus, t1.pickidno, t1.backordr, t1.releasqtty, t1.linenum,
t1.shiptocode, t1.trnscode, t1.shiptodesc, t1.price, t1. currency, t1.linetotal, t1.freetxt, t1.objtype, t1.usebaseun,
t1.unitMsr, t1.NumPerMsr, t1.unitMsr2, t1.NumPerMsr2, t1.VisOrder, t1.SlpCode, t1.Project, t1.OcrCode, t1.OcrCode2, t1.OcrCode3, t1.OcrCode4, t1.OcrCode5, t1.SubCatNum, t1.CodeBars, t1.Length1, t1.Length2, t1.Len1Unit, t1.Len2Unit, t1.Width1, t1.Width2, t1.Wdth1Unit, t1.Wdth2Unit, t1.Height1, t1.Height2, t1.Hght1Unit,
t1.Hght2Unit, t1.Weight1, t1.Weight2, t1.Wght1Unit, t1.Wght2Unit, t1.Volume, t1.VolUnit, t1.SWW, t1.freetxt as Length, t1.freetxt as Width, t1.freetxt as Height, t1.freetxt as Weight, t1.ChgAsmBoMW,
t1.uomEntry, t1.uomEntry2, t1.uomCode, t1.uomCode2, CAST(4 AS int) ItemType, CAST(NULL as char(1)) IssueType
into ' + @tempTable + '
from [dbo].[ORDR] t0
inner join [dbo].[RDR1] t1 on t0.docentry = t1.docentry
inner join [dbo].[oitm] t2 on t1.itemcode = t2.itemcode
where T0.DOCSTATUS = N''O'' AND t1.linestatus = N''O'' and t2.treetype <> N''S''
')
end
else if (@docTable = 'OINV')
begin
exec('
select
t0.docentry, t1.itemcode, t1.whscode, t1.dropship, t1.FromWhsCod, t1.linestatus, t1.treetype, t1.shipdate, t1.dscription,
t1.quantity, t1.pickoty, t1.OpenCreQty, t1.DelivrdQty, t1.pickstatus, t1.pickidno, t1.backordr, t1.releasqtty, t1.linenum,
t1.shiptocode, t1.trnscode, t1.shiptodesc, t1.price, t1. currency, t1.linetotal, t1.freetxt, t1.objtype, t1.usebaseun,
t1.unitMsr, t1.NumPerMsr, t1.unitMsr2, t1.NumPerMsr2, t1.VisOrder, t1.SlpCode, t1.Project, t1.OcrCode, t1.OcrCode2, t1.OcrCode3, t1.OcrCode4, t1.OcrCode5, t1.SubCatNum, t1.CodeBars, t1.Length1, t1.Length2, t1.Len1Unit, t1.Len2Unit, t1.Width1, t1.Width2, t1.Wdth1Unit, t1.Wdth2Unit, t1.Height1, t1.Height2, t1.Hght1Unit,
t1.Hght2Unit, t1.Weight1, t1.Weight2, t1.Wght1Unit, t1.Wght2Unit, t1.Volume, t1.VolUnit, t1.SWW, t1.freetxt as Length, t1.freetxt as Width, t1.freetxt as Height, t1.freetxt as Weight, t1.ChgAsmBoMW,
t1.uomEntry, t1.uomEntry2, t1.uomCode, t1.uomCode2, CAST(4 AS int) ItemType, CAST(NULL as char(1)) IssueType
into ' + @tempTable + '
from [dbo].[OINV] t0
inner join [dbo].[INV1] t1 on t0.docentry = t1.docentry
inner join [dbo].[oitm] t2 on t1.itemcode = t2.itemcode
where T0.INVNTSTTUS = N''O'' AND T0.ISINS = N''Y'' AND t1.linestatus = N''O'' and t2.treetype <> N''S''
')
end
else if (@docTable = 'OWOR')
begin
exec('
select
t0.docentry, t1.itemcode, t1.whscode, t1.dropship, t1.FromWhsCod, t1.linestatus, t1.treetype, t1.shipdate, t1.dscription,
t1.quantity, t1.pickoty, t1.OpenCreQty, t1.DelivrdQty, t1.pickstatus, t1.pickidno, t1.backordr, t1.releasqtty, t1.linenum,
t1.shiptocode, t1.trnscode, t1.shiptodesc, t1.price, t1. currency, t1.linetotal, t1.freetxt, t1.objtype, t1.usebaseun,
t1.unitMsr, t1.NumPerMsr, t1.unitMsr2, t1.NumPerMsr2, t1.VisOrder, t1.SlpCode, t1.Project, t1.OcrCode, t1.OcrCode2, t1.OcrCode3, t1.OcrCode4, t1.OcrCode5, t1.SubCatNum, t1.CodeBars, t1.Length1, t1.Length2, t1.Len1Unit, t1.Len2Unit, t1.Width1, t1.Width2, t1.Wdth1Unit, t1.Wdth2Unit, t1.Height1, t1.Height
2, t1.Hght1Unit,
t1.Hght2Unit, t1.Weight1, t1.Weight2, t1.Wght1Unit, t1.Wght2Unit, t1.Volume, t1.VolUnit, t1.SWW, t1.freetxt as Length, t1.freetxt as Width, t1.freetxt as Height, t1.freetxt as Weight, t1.ChgAsmBoMW,
t1.uomEntry, t1.uomEntry2, t1.uomCode, t1.uomCode2, CAST(4 AS int) ItemType, CAST(NULL as char(1)) IssueType
into ' + @tempTable + '
from [dbo].[OINV] t0
inner join [dbo].[INV1] t1 on t0.docentry = t1.docentry
inner join [dbo].[oitm] t2 on t1.itemcode = t2.itemcode
where 1<>1
insert into ' + @tempTable + '
select
t0.docentry, t1.itemcode, t1.wareHouse, N''N'', N'''', N''O'', N''N'', t0.DueDate, t2.ItemName,
t1.PlannedQty, t1.pickQty, t1.PlannedQty - t1.IssuedQty, t1.IssuedQty, t1.pickstatus, t1.pickidno, N''Y'', t1.releaseqty, t1.linenum,
null, -1, N'''', 0, N'''', 0, N'''', 202, N''Y'',
t2.InvntryUom, 1, t2.InvntryUom, 1, t1.VisOrder, -1, t1.Project, t1.OcrCode, t1.OcrCode2, t1.OcrCode3, t1.OcrCode4, t1.OcrCode5, N'''', null, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, t2.SWW, N'''', N'''', N'''', N'''', N''N'',
t1.uomEntry, t1.uomEntry, t1.uomCode, t1.uomCode, t1.ItemType, t1.IssueType
from [dbo].[OWOR] t0
inner join [dbo].[WOR1] t1 on t0.docentry = t1.docentry
inner join [dbo].[oitm] t2 on t1.itemcode = t2.itemcode
where (T0.Status = N''P'' OR T0.Status = N''R'') AND (T0.Type = N''S'' OR T0.Type = N''P'') and t1.ItemType = 4 and t2.treetype <> N''S''
insert into ' + @tempTable + '
select
t0.docentry, t1.itemcode, t1.wareHouse, N''N'', N'''', N''O'', N''N'', t0.DueDate, t2.ResName,
t1.PlannedQty, t1.pickQty, t1.PlannedQty - t1.IssuedQty, t1.IssuedQty, t1.pickstatus, t1.pickidno, N''Y'', t1.releaseqty, t1.linenum,
null, -1, N'''', 0, N'''', 0, N'''', 202, N''Y'',
null, 1, null, 1, t1.VisOrder, -1, t1.Project, t1.OcrCode, t1.OcrCode2, t1.OcrCode3, t1.OcrCode4, t1.OcrCode5, N'''', null, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, null, N'''', N'''', N'''', N'''', N''N'',
t1.uomEntry, t1.uomEntry, t1.uomCode, t1.uomCode, t1.ItemType, t1.IssueType
from [dbo].[OWOR] t0
inner join [dbo].[WOR1] t1 on t0.docentry = t1.docentry
inner join [dbo].[ORSC] t2 on t1.itemcode = t2.rescode
where (T0.Status = N''P'' OR T0.Status = N''R'') AND (T0.Type = N''S'' OR T0.Type = N''P'') and t1.ItemType = 290
')
end
else if (@docTable = 'OWTQ')
begin
exec('
select
t0.docentry, t1.itemcode, t1.FromWhsCod as WhsCode, t1.dropship, t1.WhsCode as FromWhsCod, t1.linestatus, t1.treetype, t1.shipdate, t1.dscription,
t1.quantity, t1.pickoty, t1.OpenCreQty, t1.quantity - t1.OpenCreQty as DelivrdQty, t1.pickstatus, t1.pickidno, t1.backordr, t1.releasqtty, t1.linenum,
t1.shiptocode, t1.trnscode, t1.shiptodesc, t1.price, t1. currency, t1.linetotal, t1.freetxt, t1.objtype, t1.usebaseun,
t1.unitMsr, t1.NumPerMsr, t1.unitMsr2, t1.NumPerMsr2, t1.VisOrder, t0.SlpCode, t1.Project, t1.OcrCode, t1.OcrCode2, t1.OcrCode3, t1.OcrCode4, t1.OcrCode5, t1.SubCatNum, t1.CodeBars, t1.Length1, t1.Length2, t1.Len1Unit, t1.Len2Unit, t1.Width1, t1.Width2, t1.Wdth1Unit, t1.Wdth2Unit, t1.Height1, t1.Height2, t1.Hght1Unit,
t1.Hght2Unit, t1.Weight1, t1.Weight2, t1.Wght1Unit, t1.Wght2Unit, t1.Volume, t1.VolUnit, t1.SWW, t1.freetxt as Length, t1.freetxt as Width, t1.freetxt as Height, t1.freetxt as Weight, t1.ChgAsmBoMW,
t1.uomEntry, t1.uomEntry2, t1.uomCode, t1.uomCode2, CAST(4 AS int) ItemType, CAST(NULL as char(1)) IssueType
into ' + @tempTable + '
from [dbo].[OWTQ] t0
inner join [dbo].[WTQ1] t1 on t0.docentry = t1.docentry
inner join [dbo].[oitm] t2 on t1.itemcode = t2.itemcode
where T0.DOCSTATUS = N''O'' AND t1.linestatus = N''O'' and t2.treetype <> N''S''
')
end
select @tempTable
GO
CREATE PROCEDURE TmSp_PicknPackCreateTmp4Qtty
AS
declare @tableName sysname
declare @count int
declare @i int
declare @tablePrefix sysname
set @tablePrefix = '##TMP4QTY_' + cast(@@SPID as varchar(6)) + '_'
set @count = 1
set @tableName = ''
set @i = 0
while (@count <> 0)
begin
declare @numStr sysname
declare @timeStampStr as varchar(20)
set @numStr = cast(@i as sysname)
set @timeStampStr = convert(varchar(20), getdate(), 20)
set @timeStampStr = replace(@timeStampStr, '-', '')
set @timeStampStr = replace(@timeStampStr, ':', '')
set @timeStampStr = replace(@timeStampStr, ' ', '')
set @timeStampStr = right(@timeStampStr, 12)
-- set @tableName = @tablePrefix + @numStr
set @tableName = @tablePrefix + @numStr + '_' + @timeStampStr
-- Remark: table name length should be less (or equal) to 32 chars
select @count = count(*) from tempdb.dbo.sysobjects with(nolock) where xtype = 'u' and name = @tableName
set @i = @i + 1
end
exec('create table '+@tableName+'
(
[ItemCode] nvarchar(50),
[WhsCode]nvarchar(8),
[ReleasQtty]numeric(19,6),
[PickOty]numeric(19,6),
[ChgAsmBoMW]nvarchar(1)
)')
exec('create clustered index IX_'+@tableName+' on '+@tableName+' ([ItemCode],[WhsCode])')
/*
* RDR1 covering indexes:
create index RDR1_COVERING_RELEASED_QTTY on RDR1
(LineStatus, ReleasQtty)
include (ItemCode, WhsCode, NumPerMsr, ChgAsmBoMW)
where LineStatus <> 'C' and ReleasQtty <> 0.0
-- drop index RDR1_COVERING_RELEASED_QTTY on RDR1
create index RDR1_COVERING_PICK_QTY on RDR1
(LineStatus, PickOty)
include (ItemCode, WhsCode, NumPerMsr, ChgAsmBoMW)
where LineStatus <> 'C' and PickOty <> 0.0
-- drop index RDR1_COVERING_PICK_QTY on RDR1
-- Remark: there is 'O' as 'Oscar' (not 'Q' as 'Quebec') in PickOty
*/
exec('
insert into '+@tableName+'
select U0.ItemCode,U0.WhsCode,SUM(U0.ReleasQtty) ReleasQtty,SUM(u0.[PickOty]) PickOty,
min (U0.[ChgAsmBoMW])
from
(
/* locking optimization for RDR1 query using covering indexes */
select T1.[ItemCode], T1.[WhsCode], T1.[ReleasQtty] * T1.[NumPerMsr] ReleasQtty, 0.0 PickOty, T1.[ChgAsmBoMW]
from [RDR1] T1 inner join [ORDR] T0 on T0.[DocEntry] = T1.[DocEntry]
where T0.DocType = ''I'' and T1.[LineStatus] <> ''C'' and T1.[ReleasQtty] <> 0.0
union all
select T1.[ItemCode], T1.[WhsCode], 0.0 ReleasQtty, T1.[PickOty] * T1.[NumPerMsr] PickOty, T1.[ChgAsmBoMW]
from [RDR1] T1 inner join [ORDR] T0 on T0.[DocEntry] = T1.[DocEntry]
where T0.DocType = ''I'' and T1.[LineStatus] <> ''C'' and T1.[PickOty] <> 0.0
union all
select T1.[ItemCode], T1.[WhsCode], T1.[ReleasQtty] * T1.[NumPerMsr] ReleasQtty, T1.[PickOty] * T1.[NumPerMsr] PickOty, T1.[ChgAsmBoMW]
from [INV1] T1 inner join [OINV] T0 on T0.[DocEntry] = T1.[DocEntry]
where T0.[isIns] = ''Y'' and T0.DocType = ''I'' and T1.[LineStatus]<>''C''
union all
select T1.[ItemCode], T1.[FromWhsCod] WhsCode, T1.[ReleasQtty] * T1.[NumPerMsr] ReleasQtty, T1.[PickOty] * T1.[NumPerMsr] PickOty, T1.[ChgAsmBoMW]
from [WTQ1] T1 inner join [OWTQ] T0 on T0.[DocEntry] = T1.[DocEntry]
where T1.[LineStatus]<>''C''
union all
select T1.[ItemCode], T1.[wareHouse] WhsCode, T1.[ReleaseQty] ReleasQtty, T1.[PickQty] PickOty, ''N'' ChgAsmBoMW
from [WOR1] T1 inner join [OWOR] T0 on T0.[DocEntry] = T1.[DocEntry]
where (T0.Status = ''P'' OR T0.Status = ''R'') AND (T0.Type = ''S'' OR T0.Type = ''P'') and T1.ItemType = 4
) U0
group by U0.[ItemCode], U0.[WhsCode]')
select @tableName
GO
CREATE proc TmSp_RebuildAcctMatch
@actCode nvarchar (15),
@actCurr nvarchar (3),
@useLocal nchar (1),
@isCard nvarchar (1),
@external nvarchar(1)
as
begin
declare @maxMatch int
declare @count int
declare @maxMatchBnk int
if (@external = N'N')
begin
if (@useLocal = N'Y')
begin
insert into OMTH
select ShortName, N'I', IntrnMatch, sum (Debit),
@isCard, N'0', -1, GETDATE(), @actCurr, NULL, N'1' ,GETDATE()
from JDT1 where ShortName = @actCode and IntrnMatch > 0
group by ShortName, IntrnMatch
end
else
begin
insert into OMTH
select ShortName, N'I', IntrnMatch, sum (FCDebit),
@isCard, N'0', -1, GETDATE(), @actCurr, NULL, N'1' ,GETDATE()
from JDT1 where ShortName = @actCode and IntrnMatch > 0
group by ShortName, IntrnMatch
end
end
if (@external = N'Y')
begin
if (@useLocal = N'Y')
begin
insert into OMTH
select ShortName, N'E', ExtrMatch, (sum(Debit)-sum(Credit)),
@isCard, N'0', -1, GETDATE(), @actCurr, NULL, N'1' ,GETDATE()
from JDT1 where ShortName = @actCode and ExtrMatch >0
group by ShortName, ExtrMatch
end
else
begin
insert into OMTH
select ShortName, N'E', ExtrMatch, (sum(FCDebit)-sum(FCCredit)),
@isCard, N'0', -1, GETDATE(), @actCurr, NULL, N'1' ,GETDATE()
from JDT1 where ShortName = @actCode and ExtrMatch >0
group by ShortName, ExtrMatch
end
end
end
GO
CREATE proc TmSp_RebuildMatchHistory
As
begin
declare @errCode int
declare @maxMatchNum int
declare @mainCurr nvarchar (3)
declare @actCode nvarchar (15)
declare @actCurr nvarchar (3)
declare @useLocal nchar (1)
declare @external nchar (1)
declare @isCard nchar (1)
select @mainCurr = MainCurncy from oadm
declare dbAccounts cursor for
SELECT JDT1.Account, max(OACT.ActCurr), N'A'
FROM OACT INNER JOIN
JDT1 ON OACT.AcctCode = JDT1.Account
WHERE (JDT1.IntrnMatch > 0 AND JDT1.Shortname = JDT1.Account)
GROUP by JDT1.Account
union
SELECT JDT1.Shortname, max(OCRD.Currency), N'C'
FROM OCRD INNER JOIN
JDT1 ON OCRD.CardCode = JDT1.Shortname
WHERE (JDT1.IntrnMatch > 0 AND JDT1.Shortname <> JDT1.Account)
GROUP by JDT1.Shortname
delete from omth
open dbAccounts
Fetch next from dbAccounts into @actCode, @actCurr, @isCard
while @@fetch_status = 0
begin
if (@actCurr = N'' or @actCurr = N'##')
Set @actCurr = @mainCurr
if (@actCurr = @mainCurr)
set @useLocal = N'Y'
else
set @useLocal = N'N'
update OACT set OACT.IntrMatch = 0
where AcctCode = @actCode
update OCRD set OCRD.InMatchNum = 0
where CardCode = @actCode
set @external = N'N'
exec TmSp_RebuildAcctMatch @actCode, @actCurr, @useLocal, @isCard, @external
select @maxMatchNum = max(MatchNum) from OMTH
where MthAcctCod = @actCode AND IsInternal = N'I'
update OACT set OACT.IntrMatch = @maxMatchNum
where AcctCode = @actCode
update OCRD set OCRD.InMatchNum = @maxMatchNum
where CardCode = @actCode
Fetch next from dbAccounts into @actCode, @actCurr, @isCard
end
close dbAccounts
deallocate dbAccounts
--external
declare dbAccounts cursor for
SELECT JDT1.Account, max(OACT.ActCurr), N'A'
FROM OACT INNER JOIN
JDT1 ON OACT.AcctCode = JDT1.Account
WHERE (JDT1.ExtrMatch > 0 AND JDT1.Shortname = JDT1.Account)
GROUP by JDT1.Account
union
SELECT JDT1.Shortname, max(OCRD.Currency), N'C'
FROM OCRD INNER JOIN
JDT1 ON OCRD.CardCode = JDT1.Shortname
WHERE (JDT1.ExtrMatch > 0 AND JDT1.Shortname <> JDT1.Account)
GROUP by JDT1.Shortname
open dbAccounts
Fetch next from dbAccounts into @actCode, @actCurr,@isCard
while @@fetch_status = 0
begin
if (@actCurr = N'' or @actCurr = N'##')
Set @actCurr = @mainCurr
if (@actCurr = @mainCurr)
set @useLocal = N'Y'
else
set @useLocal = N'N'
update OACT set OACT.ExtrMatch = 0
where AcctCode = @actCode
update OCRD set OCRD.ExMatchNum = 0
where CardCode = @actCode
set @external = N'Y'
exec TmSp_RebuildAcctMatch @actCode, @actCurr, @useLocal, @isCard, @external
select @maxMatchNum = max(MatchNum) from OMTH
where MthAcctCod = @actCode AND IsInternal = N'E'
update OACT set OACT.ExtrMatch = @maxMatchNum
where AcctCode = @actCode
update OCRD set OCRD.ExMatchNum = @maxMatchNum
where CardCode = @actCode
Fetch next from dbAccounts into @actCode, @actCurr, @isCard
end
close dbAccounts
deallocate dbAccounts
end
GO
CREATE proc TmSp_Replace_Table
@SourceDB nvarchar(128) = NULL ,
@SourceTable nvarchar(128) = NULL,
@UtilityDB nvarchar(128) = NULL,
@UtilityTable nvarchar(128) = NULL
as
begin
exec ('Delete from ['+ @SourceDB +'].[dbo].[' + @SourceTable + ']')
exec ('Insert into ['+ @SourceDB +'].[dbo].['+ @SourceTable + ']
Select *
From ['+ @UtilityDB +'].[dbo].[' + @UtilityTable +']')
end
GO
CREATE proc TmSp_SetGlStamp
@FldType int
as
begin
if (@FldType = 1) UPDATE CINF SET ADMStamp = ADMStamp + 1
if (@FldType = 2) UPDATE CINF SET ACTStamp = ACTStamp + 1
if (@FldType = 3) UPDATE CINF SET RTTStamp = RTTStamp + 1
end
GO
CREATE PROCEDURE [dbo].[TmSp_TransferRecordsToUILM]
@MsgFromCopy INT, @MsgToCopy INT
AS
BEGIN
DECLARE @MaxMIDfromUILM INT
DECLARE @MessageIDDelta INT
SELECT @MaxMIDfromUILM = ISNULL(MAX(MessageID),0) FROM UILM
SET @MessageIDDelta = @MaxMIDfromUILM - @MsgFromCopy
declare @col varchar(max)
declare @sql varchar(max)
SELECT SC.NAME, SC.colid
INTO #TMP
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
WHERE SO.xtype = 'U' and SO.NAME = 'OILM' and SC.colid <> 1
ORDER BY SC.colid
set @sql = 'INSERT INTO UILM SELECT MessageID + '
+ cast (@MessageIDDelta as varchar(max))
while (select count(*) from #TMP) > 0
BEGIN
set @col =(select top 1 name from #TMP order by colid)
delete from #TMP where name = @col
set @sql = @sql + ',' + @col
END
set @sql = @sql + ' FROM [OILM] WHERE MessageID > '
+ cast (@MsgFromCopy as varchar(max)) + ' AND MessageID <= '
+ cast (@MsgToCopy as varchar(max))
exec (@sql)
INSERT INTO UILM1
SELECT [MessageID] + @MessageIDDelta
,[ItemCode]
,[SysNumber]
,[Quantity]
,[MdAbsEntry]
FROM [ILM1]
WHERE MessageID > @MsgFromCopy AND MessageID <= @MsgToCopy
INSERT INTO UILM2
SELECT [MessageID] + @MessageIDDelta
,[AccountId]
,[AcctCode]
,[DebitCredi]
FROM [ILM2]
WHERE MessageID >@MsgFromCopy AND MessageID <= @MsgToCopy
INSERT INTO UILM3
SELECT [MessageID] + @MessageIDDelta
,[LineID]
,[POLine]
,[ItemType]
,[ItemCode]
,[LocType]
,[LocCode]
,[Quantity]
,[TotalLC]
,[BaseAbsEnt]
,[BaseType]
,[BaseLine]
FROM [ILM3]
WHERE MessageID >@MsgFromCopy AND MessageID <= @MsgToCopy
END
GO
CREATE PROCEDURE TmSP_UpdateESEnabled @Enabled CHAR AS
BEGIN
DECLARE @OldStatus CHAR;
SELECT @OldStatus = "ESEnabled" FROM "CINF";
IF @OldStatus <> @Enabled
BEGIN
UPDATE "CINF" SET "ESEnabled"=@Enabled;
--CACHE_OBJECT_CINF 124
DECLARE @c INT = 0;
SELECT @c=count("ID") FROM "CHEN" WHERE "ID"=124 AND "Component"='C';
IF @c = 0
INSERT INTO "CHEN"("Component", "ID", "Counter") VALUES('C', 124, 1);
ELSE
UPDATE "CHEN" SET "Counter"="Counter" + 1 WHERE "ID"=124 AND "Component"='C';
END;
END;
GO
CREATE proc TmSp_UpdatingOITM_OITW
@SourceDB nvarchar(128) = NULL ,
@UtilityDB nvarchar(128) = NULL,
@isCompanyPerWh char (1) = NULL
as
begin
if (@isCompanyPerWh = 'Y')
begin
exec ('UPDATE t1
SET t1.avgPrice = t2.avgPrice, t1.stockValue = t2.stockValue
FROM ['+ @SourceDB +'].[dbo].[OITW] t1, ['+ @UtilityDB +'].[dbo].[OITW] t2
where t1.itemCode = t2.itemCode and t1.whsCode = t2.whsCode')
end
else
begin
exec ('UPDATE t1
SET t1.avgPrice = t2.avgPrice, t1.stockValue = t2.stockValue
FROM ['+ @SourceDB +'].[dbo].[OITM] t1, ['+ @UtilityDB +'].[dbo].[OITM] t2
where t1.itemCode = t2.itemCode')
end
exec ('UPDATE t1
SET t1.onHand = t2.onHand
FROM ['+ @SourceDB +'].[dbo].[OITW] t1, ['+ @UtilityDB +'].[dbo].[OITW] t2
where t1.itemCode = t2.itemCode and t1.whsCode = t2.whsCode
UPDATE t1
SET t1.onHand = t2.onHand
FROM ['+ @SourceDB +'].[dbo].[OITM] t1, ['+ @UtilityDB +'].[dbo].[OITM] t2
where t1.itemCode = t2.itemCode')
end
GO
Create Procedure TmSp_UpgradeElectronicInvoiceMX
--With Encryption
AS
BEGIN
DECLARE @table_name varchar(100)
DECLARE @object_abbev varchar(100)
DECLARE @action_type varchar(100)
DECLARE @counter int
DECLARE @max_count int
DECLARE @nextAbsEntry int
DECLARE @sqlStr nvarchar(MAX)
DECLARE @sqlUDF_src nvarchar(100)
DECLARE @sqlUDF_tgt nvarchar(100)
SET @counter = 0
SET @max_count = 3
IF ((select count(*) from CUFD where TableID = 'ECM2' and AliasID IN ('B1SYS_SignMsg', 'B1SYS_SignDigest')) = 2)
BEGIN
SET @sqlUDF_tgt = ', U_B1SYS_SignMsg, U_B1SYS_SignDigest'
SET @sqlUDF_src = ', t0.SignMsg, t0.SignDigest'
END
ELSE
BEGIN
SET @sqlUDF_tgt = ''
SET @sqlUDF_src = ''
END
-- get autoKey for ECM2
-- - we do not need check ECM2.AbsEntry, it was not used in MX until now
SET @nextAbsEntry = IsNull((select AutoKey from ONNM where ObjectCode = '1730000000'), 1) -- for ECM Electronic Communication
SET @nextAbsEntry = @nextAbsEntry - 1
WHILE @counter < @max_count
BEGIN
IF @counter = 0
BEGIN
SET @table_name = 'OINV'
SET @object_abbev = 'IN'
SET @action_type = 'D' -- LAT_DOCUMENT_AR
END
ELSE IF @counter = 1
BEGIN
SET @table_name = 'ORIN'
SET @object_abbev = 'CN'
SET @action_type = 'D' -- LAT_DOCUMENT_AR
END
ELSE IF @counter = 2
BEGIN
SET @table_name = 'ODPI'
SET @object_abbev = 'DT'
SET @action_type = 'D' -- LAT_DOCUMENT_AR
END
/*
OINV.EDocStatus
'N' New
'P' Pending
'S' Sent
'E' Error
'C' OK
ECM2.ActStatus
'N' New
'P' Pending
'E' Error
'O' OK
'S' Sent
'R' Document Error
'W' Waiting
'A' Authorized
'I' In Process
'J' Rejected
'D' Denied
'C' Canceled
'B' Aborted
*/
SET @sqlStr = '
insert into ECM2
(AbsEntry,
Code, ActType, ActStatus,
ActEnv, BPLId, ObjectID, ReportID,
SrcObjType, SrcObjAbs, Cancel, GenType,
UserSign, CreateDate, CreateTS, UserSign2, UpdateDate, UpdateTS' + @sqlUDF_tgt +')
select
ROW_NUMBER() OVER (Order by t0.DocEntry) + ' + CAST(@nextAbsEntry as nvarchar) + ',
''M'', ''' + @action_type + ''', (CASE WHEN t0.CANCELED = ''Y'' THEN ''C'' WHEN t0.EDocStatus = ''C'' THEN ''A'' ELSE t0.EDocStatus END),
(CASE WHEN t0.EDocTest = ''Y'' THEN 0 ELSE 1 END), t0.BPLId, '''+ @object_abbev + ' '' + CAST(t0.DocNum as nvarchar), t0.EDocNum,
t0.ObjType, t0.DocEntry, t0.CANCELED, t0.EDocGenTyp,
t0.UserSign, t0.CreateDate, t0.CreateTS, t0.UserSign2, t0.UpdateDate, t0.UpdateTS' + @sqlUDF_src + '
from ' + @table_name + ' t0
inner join NNM1 t1 on t0.Series = t1.Series
left join ecm2 t5 on t5.SrcObjType = t0.ObjType and t5.SrcObjAbs = t0.DocEntry
where t1.IsDigSerie = ''Y''
and t0.CANCELED IN (''Y'', ''N'')
and t0.EDocGenTyp IN (''G'', ''L'')
and t5.AbsEntry is NULL' -- dont add data to ECM2 in case document already exists
EXEC(@sqlStr)
IF @@ERROR <> 0
BEGIN
GOTO EXIT_LABEL
END
SET @sqlStr = '
insert into ECM3
(AbsEntry,
LogNum, LogType, ExportFmt,
LogMessage, LogData,
UserSign, CreateDate, CreateTS, UserSign2, UpdateDate, UpdateTS)
select
t5.AbsEntry,
1, ''S'', t0.EDocExpFrm,
(CASE when EDocErrCod IS NULL then '''' ELSE EDocErrCod + '': '' + SUBSTRING(EDocErrMsg, 1, 245) END), t0.EDocCntnt,
t0.UserSign, t0.CreateDate, t0.CreateTS, t0.UserSign2, t0.UpdateDate, t0.UpdateTS
from ' + @table_name + ' t0
inner join NNM1 t1 on t0.Series = t1.Series
left join ecm2 t5 on t5.SrcObjType = t0.ObjType and t5.SrcObjAbs = t0.DocEntry
left join ecm3 t6 on t6.AbsEntry = t5.AbsEntry
where t1.IsDigSerie = ''Y''
and t0.CANCELED IN (''Y'', ''N'')
and t0.EDocGenTyp IN (''G'', ''L'')
and t6.AbsEntry is NULL' -- dont add data to ECM3 in case there are already records
EXEC(@sqlStr)
IF @@ERROR <> 0
BEGIN
GOTO EXIT_LABEL
END
SET @counter = @counter + 1
select @nextAbsEntry = IsNull(Max(AbsEntry), 0) from ECM2
END
-- update AutoKey in ONNM table
select @nextAbsEntry = IsNull(Max(AbsEntry), 0) + 1 from ECM2
update ONNM SET AutoKey = @nextAbsEntry where ObjectCode = '1730000000' -- for ECM Electronic Communication
EXIT_LABEL:
END
GO
CREATE PROCEDURE [TmSp_VMF_TransformExpenseXML]
@XML nvarchar(max),
@SystemID int,
@AutoFill smallint = 0,
@IgnoreError smallint = 0
AS
BEGIN
declare @BomXML nvarchar(max)
declare @BomResult nvarchar(max)
declare @Error nvarchar(300)
set @BomXML = ISNULL(@XML, '')
set @BomResult = ''
declare @bchar nvarchar(5)
declare @echar nvarchar(5)
declare @blen smallint
declare @elen smallint
set @bchar = '<'
set @echar = '>'
set @blen = LEN(@bchar)
set @elen = LEN(@echar)
declare @item nvarchar(1000)
declare @b1item nvarchar(100)
declare @count int
declare @tagName nvarchar(200)
declare @ctagName nvarchar(200)
declare @position int
declare @position2 int
declare @position3 int
declare @position4 int
declare @objList TABLE (TagName nvarchar(200), ObjectID int, ObjAbbr nvarchar(10))
insert into @objList (TagName, ObjectID, ObjAbbr) values ('ExpenseType', 242000007, 'EXT')
insert into @objList (TagName, ObjectID, ObjAbbr) values ('VatGroup', 5, 'VTG')
insert into @objList (TagName, ObjectID, ObjAbbr) values ('Currency', 37, 'CRN')
insert into @objList (TagName, ObjectID, ObjAbbr) values ('CardCode', 171, 'HEM')
insert into @objList (TagName, ObjectID, ObjAbbr) values ('ProjectCode', 63, 'PRJ')
insert into @objList (TagName, ObjectID, ObjAbbr) values ('CostingCode', 61, 'PRC')
declare @ObjectID int
declare @ObjAbbr nvarchar(10)
declare @fullLen int
set @fullLen = LEN(@BomXML)
set @position = CHARINDEX(@bchar, @BomXML, 1)
while @position > 0
BEGIN
set @position2 = CHARINDEX(@echar, @BomXML, @position +@blen) -- (+1) - position of the > for begin tag
if (SUBSTRING(@BomXML, @position2 -1, 1) <> '/' AND SUBSTRING(@BomXML, @position + @blen, 1) <> '/') -- empty tag or closing tag
BEGIN
set @tagName = SUBSTRING(@BomXML, @position +@blen, @position2 - @position -@blen)
set @position3 = CHARINDEX(@bchar, @BomXML, @position2 + @elen)
if (SUBSTRING(@BomXML, @position3 +@blen, 1) = '/')
BEGIN
set @position4 = CHARINDEX(@echar, @BomXML, @position3 +@blen)
set @ctagName = SUBSTRING(@BomXML, @position3 +@blen+1, @position4 - @position3 -@blen -1)
select @count = count(*) from @objList where TagName = @tagName
if (@tagName = @ctagName AND @count = 1) -- TODO: namespaces can be in begin tag
BEGIN
set @item = SUBSTRING(@BomXML, @position2 + @elen, @position3 - @position2 - @elen)
if (LTRIM(@item) <> '')
BEGIN
select @ObjectID = ObjectID, @ObjAbbr = ObjAbbr from @objList where TagName = @tagName
select @count = count(*) from OMPO
inner join MPO1 on OMPO.AbsEntry = MPO1.AbsEntry
where ThirdPVal = @item and ThirdPID = @SystemID and ObjectId = @ObjectID
if @count = 1
BEGIN
select @b1item = ObAbsEntry from OMPO
inner join MPO1 on OMPO.AbsEntry = MPO1.AbsEntry
where ThirdPVal = @item and ThirdPID = @SystemID and ObjectId = @ObjectID
--emp to crd
IF @ObjAbbr = 'HEM'
BEGIN
DECLARE @b1emp nvarchar(100)
set @b1emp = @b1item
select @b1item = BPLink from OHEM where empID = @b1emp
END
set @BomResult = @BomResult + SUBSTRING(@BomXML, 1, @position2 + @elen - 1) + @b1item
set @BomXML = SUBSTRING(@BomXML, @position2 + @elen + LEN(@item), @fullLen)
set @position2 = 0
END
ELSE
BEGIN
if (@count > 1)
BEGIN
set @Error = '<error>Inconsistent Mapping!</error>'
END
ELSE
BEGIN
set @Error = '<error>Missing Mapping!</error>'
if (@AutoFill = 1)
BEGIN
DECLARE @sql nvarchar(1000)
DECLARE @tableName nvarchar(100)
DECLARE @fieldname nvarchar(100)
set @tableName = '@BE_VM_' + CAST(@SystemID as nvarchar(10)) + '_' + @ObjAbbr
select @fieldname = 'U_' + ColAlias from UKD1 where TableName = @tableName
set @sql = N'select @1 = count(*) from [' +@tableName+ '] where [' +@fieldname+ '] = @2'
EXEC sp_executesql @sql, N'@1 INT OUTPUT, @2 NVARCHAR(100)', @1 = @count OUTPUT, @2 = @item
if (@count = 0)
BEGIN
set @sql = N'insert into [' +@tableName+ '] ([' +@fieldname+ ']) values (@1)'
EXEC sp_executesql @sql, N'@1 NVARCHAR(100)', @item
END
END
END
END
END
END
END
END
set @BomResult = @BomResult + SUBSTRING(@BomXML, 1, @position2 + @elen - 1)
set @BomXML = SUBSTRING(@BomXML, @position2 + @elen, @fullLen)
set @position = CHARINDEX(@bchar, @BomXML, 1)
END
if (@IgnoreError = 2)
begin
SELECT @BomResult
end
else
begin
if (@IgnoreError = 1)
begin
SELECT @BomResult + ISNULL(CHAR(13) + @Error, '')
end
else
begin
if (ISNULL(@Error, '') = '')
begin
SELECT @BomResult
end
else
begin
SELECT @Error
end
end
end
END
GO
优质生活从拆开始
浙公网安备 33010602011771号