Skip to Content
0
Aug 25, 2023 at 09:19 AM

please help to corrects this query for crystal report in this report carton count not is sequence

40 Views

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;