Hello Experts,
When I run the Query Generator with parameters item group and Customer Code, I am facing a problem
. As I got an error "Internal error (-1) occurred [Message 131-183].
Store Procedure:
CREATE PROCEDURE "ATO_SP_DailySaleAnalysis_Operation_V1.2"(IN Fdate date,
IN Tdate date,
IN SSFrom Nvarchar(50),
IN SSTo Nvarchar(50),
IN IGroupFrom NVARCHAR(50),
IN CardFrom nVarchar(50),
IN CardTo nVarchar(50)
) AS
BEGIN
SELECT
T2."SeriesName",
T0."DocNum" ,
T0."DocDate",
T0."DocDueDate",
CASE WHEN T0."isIns" = 'N'
THEN 'A/R Invoice'
ELSE 'A/R Reserve Invoice'
END AS "Invoice",
CASE WHEN T0.CANCELED = 'N'
THEN CASE WHEN t0."DocStatus" = 'O'
THEN CASE WHEN t0."InvntSttus" = 'C'
AND t0."isIns" = 'Y'
THEN 'Delivered'
ELSE 'Open'
END
ELSE CASE WHEN t0."DocStatus" = 'C'
THEN CASE WHEN t0."InvntSttus" = 'O'
AND t0."isIns" = 'Y'
THEN 'Paid'
ELSE 'Close'
END
END
END
ELSE CASE WHEN t0.CANCELED = 'Y'
THEN 'CANCEL'
ELSE 'Closed Cancellation'
END
END AS "Status",
T3."CardCode" as "Customer Code",
T0."CardName" ,
T4."GroupName" AS "Customer Group",
T8."GroupName" AS "BP Properties",
T9."ListName" AS "Selling Price",
T3."U_Township" AS "Township",
T0."NumAtCard" as "BP Reference No",
ifnull(T0."U_SSType",'') as "Sales Type",
T1."ItemCode" as "Item Code",
T1."Dscription" as "Description",
CASE WHEN T0.CANCELED = 'Y'
THEN -T1."Quantity"
ELSE T1."Quantity"
END AS "Quantity",
T1."OpenQty",
CASE WHEN T0.CANCELED = 'Y'
THEN -T1."Price"
ELSE T1."Price"
END AS "Price",
CASE WHEN T0.CANCELED = 'Y'
THEN -T1."LineTotal"
ELSE T1."LineTotal"
END AS "Amount",
T5."SlpName" AS "Sales Employee",
T1."WhsCode",
T1."OcrCode3" AS "Brand",
T1."OcrCode" AS "Branch-Location",
T1."OcrCode2" AS "Department",
T1."Project" as "Project Code",
T0."Comments",
T0."U_HPBank" as "[SO] HP Bank" ,
T0."DocEntry",
T1."LineNum",
T10."WhsName",
T1."VatPrcnt" as "Tax",
T1."VatGroup" as "TaxG",
T1."VatSum" as "LTax",
T0."VatSum" as "DocTax",
T0."U_AgentCode" as "AgentCode",
T0."U_Agent" as "AgentName",
T1."U_CommFee" as "AgentCommissionAmount",
T3."Phone2" as "Tel2",
T3."Phone1" as "Tel1",
T3."U_Region" as "Region",
T3."U_State" as "State",
T3."U_VillageTract" as "Village",
T0."DiscPrcnt" as "DocDiscount",
T0."DiscSum" as "DiscountSum",
T1."DiscPrcnt" as "RowDiscountPercentage",
T1."U_Discount_Amt" as "RowDiscountAmount",
T1."LineTotal" as "Total (LC)",
T0."DocTotal" as "DocTotal",
T1."Currency" as "Currency",
T3."U_Branch" as "BPBranch" ,
T16."ItmsGrpNam" as "ItmsGrpName"
FROM OINV T0
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN NNM1 T2 ON T0."Series" = T2."Series"
INNER JOIN OCRD T3 ON T0."CardCode" = T3."CardCode"
INNER JOIN OCRG T4 ON T3."GroupCode" = T4."GroupCode"
LEFT OUTER JOIN OSLP T5 ON T0."SlpCode" = T5."SlpCode"
INNER JOIN OITM T6 ON T1."ItemCode" = T6."ItemCode"
LEFT OUTER JOIN PROPERTIES T8 ON T8."CardCode" = T3."CardCode"
LEFT JOIN ITR1 T11 ON T11."TransId"=T0."TransId" and T0."ReceiptNum"=0
LEFT JOIN OITR T12 ON T12."ReconNum"=T11."ReconNum"
LEFT JOIN ORCT T13 ON T0."ReceiptNum"=T13."DocNum"
LEFT JOIN DLN1 T14 ON T14."DocEntry"=T1."TrgetEntry" and T14."LineNum"=T1."BaseLine"
LEFT JOIN ODLN T15 ON T15."DocEntry"=T14."DocEntry"
LEFT OUTER JOIN OPLN T9 ON T9."ListNum" = T3."ListNum"
LEFT OUTER JOIN OWHS T10 ON T1."WhsCode"=T10."WhsCode"
INNER JOIN OITB T16 ON T16."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE
T0."DocDate" >=:Fdate
AND T0."DocDate" <=:Tdate
AND T0."U_SSType" >= :SSFrom
AND T0."U_SSType" <= :SSTo
AND
(T16."ItmsGrpNam"=:IGroupFrom or IFNULL(:IGroupFrom,'')='')
AND
(T0."CardCode" >=:CardFrom OR IFNULL(:CardFrom,'')='')
AND (T0."CardCode" <=:CardTo or IFNULL(:CardTo,'')='')
;
END
;
SAP Passed Parameters:
CALL "ATO_SP_DailySaleAnalysis_Operation_V1.2"
(
IFNULL((SELECT TOP 1 "RefDate" FROM OJDT T0 WHERE T0."RefDate" ='[%0]'),'[%0]'),
IFNULL((SELECT TOP 1 "RefDate" FROM OJDT T0 WHERE T0."RefDate" ='[%1]'),'[%1]'),
(SELECT TOP 1 CAST("U_SSType" AS NVARCHAR(50)) FROM "OINV" T1 WHERE T1."U_SSType"='[%2]'),
(SELECT TOP 1 CAST("U_SSType" AS NVARCHAR(50)) FROM "OINV" T1 WHERE T1."U_SSType"='[%3]'),
(SELECT TOP 1 "ItmsGrpNam" FROM "OITB" TT WHERE TT."ItmsGrpNam"='[%4]'),
(SELECT TOP 1 "CardCode" FROM "OINV" T2 WHERE T2."CardCode"='[%5]'),
(SELECT TOP 1 "CardCode" FROM "OINV" T2 WHERE T2."CardCode"='[%6]')
);