DO BEGIN
Declare DocEntry INT = {?@Dockey};
Declare ObjectID INT = {?@ObjectID};
CREATE TABLE TEMP AS
(
select DISTINCT
aa."CompnyName",
P0."DocNum" as "PackingSlipNo",
TO_NVARCHAR(P0."DocDueDate", 'DD.MM.YYYY') as "Date",
P5."U_BRAND" as "Buyer",
(select STRING_AGG("RefDocNum",',') from {?@Schema}.RDR21 A where A."DocEntry" = P0."DocEntry") as "W.O",
P4."NumAtCard" as "BuyersOrder",
WEEK(P0."DocDueDate"),
P0."CardName",
SUBSTRING(P0."CardCode",1,3) AS "PFX",
(Case when P3."U_OLDERPCOD" is not null then P3."U_OLDERPCOD" else P3."ItemCode" end) as "CRP ID",
P3."U_CUSTITMCOD" as "BuyerCode",
P3."ItemName" as "ItemDescription",
P1."Quantity" as "PackedQuantity",
P3."SalUnitMsr" as "PackedQuantityUOM",
P3."U_SEMICTN" ||'/'||P3."U_MSTRCTN" as "(S/M)",
Round((P1."Quantity" / P3."U_MSTRCTN"),3) as "TL.CRTN",
P3."SLength1" as "DIM.CM_L",
P3."SWidth1" as "DIM.CM_W",
P3."SHeight1" as "DIM.CM_H",
((P3."SVolume")) as "CBM",
((P3."SVolume" * (P1."Quantity" / P3."U_MSTRCTN"))) as "TI.CBM",
(P3."U_ITMWT") as "WT.PC/SET",
Round((P3."U_ITMWT" * P3."U_MSTRCTN"),3) as "TL.NET.WT",
Round(((P3."U_ITMWT" * P3."U_MSTRCTN") * (P1."Quantity" / P3."U_MSTRCTN"))) as "PER_CRTN1net",
Round(((P3."U_ITMWT" * P3."U_MSTRCTN")+P3."U_MCWT"),3) as "TLGRSWT",
Round(((P1."Quantity" / P3."U_MSTRCTN")*((P3."U_ITMWT" * P3."U_MSTRCTN")+P3."U_MCWT")),3) as "PER_CRTN1grs",
(Select t16."U_NAME" from {?@Schema}.ORDR S0
LEFT OUTER JOIN {?@Schema}."OWDD" T14 ON T14."DocEntry"=P0."DocEntry" and t14."ObjType"=P0."ObjType"
LEFT OUTER JOIN {?@Schema}."WDD1" T15 ON T15."WddCode"=T14."WddCode" and t15."Status"='Y'
LEFT OUTER JOIN {?@Schema}."OUSR" T16 ON T16."USERID"=T15."UserID"
Where S0."DocEntry"=P0."DocEntry" /*and T15.UserID = 57*/) as "Approver1",
(select "U_NAME" from {?@Schema}.OUSR where P0."UserSign"="USERID") as "PreparedBy",
Row_Number() over (order by P0."DocNum")As "RowNumber"
from {?@Schema}.ORDR P0
cross join {?@Schema}.OADM aa
Inner Join {?@Schema}.RDR1 P1 on P0."DocEntry" = P1."DocEntry"
--Inner Join {?@Schema}.RDR21 P2 on P2."DocEntry" = P0."DocEntry"
Left Join {?@Schema}.OITM P3 on P3."ItemCode" = P1."ItemCode"
Left Join {?@Schema}.ORDR P4 on P4."CardCode" = P0."CardCode" and P4."DocEntry" = P1."BaseEntry"
Left Join {?@Schema}.OCRD P5 on P5."CardCode" = P0."CardCode"
where P0."DocEntry" = '0');
IF ObjectID = 17
THEN
INSERT INTO TEMP(
select DISTINCT
aa."CompnyName",
P0."DocNum" as "PackingSlipNo",
TO_NVARCHAR(P0."DocDueDate", 'DD.MM.YYYY') as "Date",
P5."U_BRAND" as "Buyer",
(select STRING_AGG("RefDocNum",',') from {?@Schema}.RDR21 A where A."DocEntry" = P0."DocEntry") as "W.O",
P4."NumAtCard" as "BuyersOrder",
WEEK(P0."DocDueDate"),
P0."CardName",
SUBSTRING(P0."CardCode",1,3) AS "PFX",
(Case when P3."U_OLDERPCOD" is not null then P3."U_OLDERPCOD" else P3."ItemCode" end) as "CRP ID",
P3."U_CUSTITMCOD" as "BuyerCode",
P3."ItemName" as "ItemDescription",
P1."Quantity" as "PackedQuantity",
P3."SalUnitMsr" as "PackedQuantityUOM",
P3."U_SEMICTN" ||'/'||P3."U_MSTRCTN" as "(S/M)",
Round((P1."Quantity" / P3."U_MSTRCTN"),3) as "TL.CRTN",
P3."SLength1" as "DIM.CM_L",
P3."SWidth1" as "DIM.CM_W",
P3."SHeight1" as "DIM.CM_H",
((P3."SVolume")) as "CBM",
((P3."SVolume" * (P1."Quantity" / P3."U_MSTRCTN"))) as "TI.CBM",
(P3."U_ITMWT") as "WT.PC/SET",
Round((P3."U_ITMWT" * P3."U_MSTRCTN"),3) as "TL.NET.WT",
Round(((P3."U_ITMWT" * P3."U_MSTRCTN") * (P1."Quantity" / P3."U_MSTRCTN"))) as "PER_CRTN1net",
Round(((P3."U_ITMWT" * P3."U_MSTRCTN")+P3."U_MCWT"),3) as "TLGRSWT",
Round(((P1."Quantity" / P3."U_MSTRCTN")*((P3."U_ITMWT" * P3."U_MSTRCTN")+P3."U_MCWT")),3) as "PER_CRTN1grs",
(Select t16."U_NAME" from {?@Schema}.ORDR S0
LEFT OUTER JOIN {?@Schema}."OWDD" T14 ON T14."DocEntry"=P0."DocEntry" and t14."ObjType"=P0."ObjType"
LEFT OUTER JOIN {?@Schema}."WDD1" T15 ON T15."WddCode"=T14."WddCode" and t15."Status"='Y'
LEFT OUTER JOIN {?@Schema}."OUSR" T16 ON T16."USERID"=T15."UserID"
Where S0."DocEntry"=P0."DocEntry" /*and T15.UserID = 57*/) as "Approver1",
(select "U_NAME" from {?@Schema}.OUSR where P0."UserSign"="USERID") as "PreparedBy",
Row_Number() over (order by P0."DocNum")As "RowNumber"
from {?@Schema}.ORDR P0
cross join {?@Schema}.OADM aa
Inner Join {?@Schema}.RDR1 P1 on P0."DocEntry" = P1."DocEntry"
--Inner Join {?@Schema}.RDR21 P2 on P2."DocEntry" = P0."DocEntry"
Left Join {?@Schema}.OITM P3 on P3."ItemCode" = P1."ItemCode"
Left Join {?@Schema}.ORDR P4 on P4."CardCode" = P0."CardCode" and P4."DocEntry" = P1."BaseEntry"
Left Join {?@Schema}.OCRD P5 on P5."CardCode" = P0."CardCode"
where P0."DocEntry" = DocEntry);
SELECT AA.*,
Case When AA."RowNumber"-1=0 Then '1-'||round("TL.CRTN",3)
ELSE
IFNULL((Select Round("TL.CRTN",3)+1 FROM TEMP Where "RowNumber"=AA."RowNumber"-1),0)||'-'||
(IFNULL((Select Round("TL.CRTN",3) FROM TEMP Where "RowNumber"=AA."RowNumber"-1),0)+ round("TL.CRTN",3))
END AS "Package Nos"
--Round((IFNULL((Select "Total Carton" FROM TEMP Where "RowNumber"=AA."RowNumber"-1),0)+"TL.CRTN"),3) || ' CARTONS ' || "Header" as "CSHEADER"
FROM TEMP AS AA;
ELSEIF ObjectID = 112
THEN
INSERT INTO TEMP (
select DISTINCT
aa."CompnyName",
P0."DocNum" as "PackingSlipNo",
TO_NVARCHAR(P0."DocDueDate", 'DD.MM.YYYY') as "Date",
P5."U_BRAND" as "Buyer",
(select STRING_AGG("RefDocNum",',') from {?@Schema}.RDR21 A where A."DocEntry" = P0."DocEntry") as "W.O",
P4."NumAtCard" as "BuyersOrder",
WEEK(P0."DocDueDate"),
P0."CardName",
SUBSTRING(P0."CardCode",1,3) AS "PFX",
(Case when P3."U_OLDERPCOD" is not null then P3."U_OLDERPCOD" else P3."ItemCode" end) as "CRP ID",
P3."U_CUSTITMCOD" as "BuyerCode",
P3."ItemName" as "ItemDescription",
P1."Quantity" as "PackedQuantity",
P3."SalUnitMsr" as "PackedQuantityUOM",
P3."U_SEMICTN" ||'/'||P3."U_MSTRCTN" as "(S/M)",
Round((P1."Quantity" / P3."U_MSTRCTN"),3) as "TL.CRTN",
P3."SLength1" as "DIM.CM_L",
P3."SWidth1" as "DIM.CM_W",
P3."SHeight1" as "DIM.CM_H",
((P3."SVolume")) as "CBM",
((P3."SVolume" * (P1."Quantity" / P3."U_MSTRCTN"))) as "TI.CBM",
(P3."U_ITMWT") as "WT.PC/SET",
Round((P3."U_ITMWT" * P3."U_MSTRCTN"),3) as "TL.NET.WT",
Round(((P3."U_ITMWT" * P3."U_MSTRCTN") * (P1."Quantity" / P3."U_MSTRCTN"))) as "PER_CRTN1net",
Round(((P3."U_ITMWT" * P3."U_MSTRCTN")+P3."U_MCWT"),3) as "TLGRSWT",
Round(((P1."Quantity" / P3."U_MSTRCTN")*((P3."U_ITMWT" * P3."U_MSTRCTN")+P3."U_MCWT")),3) as "PER_CRTN1grs",
(Select t16."U_NAME" from {?@Schema}.ORDR S0
LEFT OUTER JOIN {?@Schema}."OWDD" T14 ON T14."DocEntry"=P0."DocEntry" and t14."ObjType"=P0."ObjType"
LEFT OUTER JOIN {?@Schema}."WDD1" T15 ON T15."WddCode"=T14."WddCode" and t15."Status"='Y'
LEFT OUTER JOIN {?@Schema}."OUSR" T16 ON T16."USERID"=T15."UserID"
Where S0."DocEntry"=P0."DocEntry" /*and T15.UserID = 57*/) as "Approver1",
(select "U_NAME" from {?@Schema}.OUSR where P0."UserSign"="USERID") as "PreparedBy",
Row_Number() over (order by P0."DocNum")As "RowNumber"
from {?@Schema}.ODRF P0
cross join {?@Schema}.OADM aa
Inner Join {?@Schema}.DRF1 P1 on P0."DocEntry" = P1."DocEntry"
--Inner Join {?@Schema}.DRF21 P2 on P2."DocEntry" = P0."DocEntry"
Left Join {?@Schema}.OITM P3 on P3."ItemCode" = P1."ItemCode"
Left Join {?@Schema}.ORDR P4 on P4."CardCode" = P0."CardCode" and P4."DocEntry" = P1."BaseEntry"
Left Join {?@Schema}.OCRD P5 on P5."CardCode" = P0."CardCode"
where P0."DocEntry" = DocEntry);
SELECT AA.*,
Case When AA."RowNumber"-1=0 Then '1-'||round("TL.CRTN",3)
ELSE
IFNULL((Select Round("TL.CRTN",3)+1 FROM TEMP Where "RowNumber"=AA."RowNumber"-1),0)||'-'||
(IFNULL((Select Round("TL.CRTN",3) FROM TEMP Where "RowNumber"=AA."RowNumber"-1),0)+ round("TL.CRTN",3))
END AS "Package Nos"
--Round((IFNULL((Select "Total Carton" FROM TEMP Where "RowNumber"=AA."RowNumber"-1),0)+"TL.CRTN"),3) || ' CARTONS ' || "Header" as "CSHEADER"
FROM TEMP AS AA;
END IF;
drop table TEMP;
END;