balakumar.viswanathan2 you remember you give the below code based on the below workflow, it worked fine but now the customer reported that there are 2 AP invoices not appearing, so after examiniation i discovered that there are 2 flows:
the below flow which is valid, and another flow (the purchase order is reading from the sales order, can you ammend it please so the query can enable to read the purchase order from the sales quotation or sales order, thanks.
SELECT T7."DocNum" AS "AP Invoice No.", T7."DocDate" AS "AP Invoice Date", T7."DocCur" AS "AP Invoice Cur", T6."Price" AS "AP Invoice Unit Price",
T6."Quantity" AS "AP Invoice Qty", T6."Price" * T6."Quantity" AS "AP Invoice Amount", (T6."Price" * T6."Quantity" * T7."DocRate")/1507.5 AS "AP Invoice Amount USD",
CASE
WHEN T7."DocTotal" - T7."PaidToDate" = 0 THEN 'Y'
ELSE 'N'
END AS "Paid Y/N", T7."Project" AS "Project #", T3."DocDate" AS "Date of PO", T3."CardCode" AS "BP Supplier Code", T3."CardName" AS "BP Supplier Name",
T2."Quantity" AS "PO Quantity", T2."Price" * T2."Quantity" AS "PO Amount", (T2."Price" * T2."Quantity" * T3."DocRate") / 1507.5 AS "PO Amount USD",
T11."ItmsGrpNam" AS "Item Group", T10."U_SubCat1" AS "Item Sub-Category",
T10."ItemCode",
T10."ItemName" AS "Product Name", T5."CardCode" AS "BP Client Code", T5."CardName" AS "BP Client Name", T5."DocNum" AS "SO #", T5."DocDate" AS "SO Date",
T4."Price" * T4."Quantity" AS "SO Amount", T9."DocNum" AS "AR Invoice No.", T9."DocDate" AS "AR Invoice Date", T9."DocCur" AS "AR Invoice Cur", T8."Price" AS "AR Invoice Unit Price", T8."Quantity" AS "AR Invoice Qty", T8."Price" * T8."Quantity" AS "AR Invoice Amount", (T8."Price" * T8."Quantity" * T9."DocRate")/1507.5 AS "AR Invoice Amount USD", T12."SlpName" AS "Sales Employee Name" FROM "OQUT" T0
INNER JOIN "QUT1" T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN "POR1" T2 ON T2."BaseEntry" = T1."DocEntry" AND T2."BaseLine" = T1."LineNum" AND T2."BaseType" = T0."ObjType"
INNER JOIN "OPOR" T3 ON T2."DocEntry" = T3."DocEntry"
INNER JOIN "RDR1" T4 ON T4."BaseEntry" = T1."DocEntry" AND T4."BaseLine" = T1."LineNum" AND T4."BaseType" = T0."ObjType"
INNER JOIN "ORDR" T5 ON T4."DocEntry" = T5."DocEntry"
INNER JOIN "PCH1" T6 ON T6."BaseEntry" = T2."DocEntry" AND T6."BaseLine" = T2."LineNum" AND T6."BaseType" = T2."ObjType"
INNER JOIN "OPCH" T7 ON T7."DocEntry" = T6."DocEntry"
INNER JOIN "INV1" T8 ON T8."BaseEntry" = T4."DocEntry" AND T8."BaseLine" = T4."LineNum" AND T8."BaseType" = T4."ObjType"
INNER JOIN "OINV" T9 ON T9."DocEntry" = T8."DocEntry"
INNER JOIN "OITM" T10 ON T10."ItemCode" = T1."ItemCode"
INNER JOIN "OITB" T11 ON T10."ItmsGrpCod" = T11."ItmsGrpCod"
INNER JOIN "OSLP" T12 ON T9."SlpCode" = T12."SlpCode
- SAP Managed Tags:
Accepted Solutions (1)
Answers (12)
balakumar.viswanathan2 i changed the JOIN from INNER JOIN to LEFT OUTER JOIN, when i replaced the joins am doing filtering on the sales orders it's giving 2 duplicated records, note the scenario is covered as mentioned but it's showing duplicated records in the other scenarios, results down for your help.
balakumar.viswanathan2
there's an additonal workflow to be added in the query i also discovered (below is workflow to be added to the below query), can you help me please, thanks.
ELECT T7."DocNum" AS "AP Invoice No.", T7."DocDate" AS "AP Invoice Date", T7."DocCur" AS "AP Invoice Cur", T6."Price" AS "AP Invoice Unit Price",
T6."Quantity" AS "AP Invoice Qty", T6."Price" * T6."Quantity" AS "AP Invoice Amount", (T6."Price" * T6."Quantity" * T7."DocRate")/1507.5 AS "AP Invoice Amount USD",
CASE
WHEN T7."DocTotal" - T7."PaidToDate" = 0 THEN 'Y'
ELSE 'N'
END AS "Paid Y/N", T7."Project" AS "Project #", T3."DocDate" AS "Date of PO", T3."CardCode" AS "BP Supplier Code", T3."CardName" AS "BP Supplier Name",
T2."Quantity" AS "PO Quantity", T2."Price" * T2."Quantity" AS "PO Amount", (T2."Price" * T2."Quantity" * T3."DocRate") / 1507.5 AS "PO Amount USD",
T11."ItmsGrpNam" AS "Item Group", T10."U_SubCat1" AS "Item Sub-Category",
T10."ItemCode",
T10."ItemName" AS "Product Name", T5."CardCode" AS "BP Client Code", T5."CardName" AS "BP Client Name", T5."DocNum" AS "SO #", T5."DocDate" AS "SO Date",
T4."Price" * T4."Quantity" AS "SO Amount", T9."DocNum" AS "AR Invoice No.", T9."DocDate" AS "AR Invoice Date", T9."DocCur" AS "AR Invoice Cur", T8."Price" AS "AR Invoice Unit Price", T8."Quantity" AS "AR Invoice Qty", T8."Price" * T8."Quantity" AS "AR Invoice Amount", (T8."Price" * T8."Quantity" * T9."DocRate")/1507.5 AS "AR Invoice Amount USD", T12."SlpName" AS "Sales Employee Name" FROM "OQUT" T0
INNER JOIN "QUT1" T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN "POR1" T2 ON T2."BaseEntry" = T1."DocEntry" AND T2."BaseLine" = T1."LineNum" AND T2."BaseType" = T0."ObjType"
INNER JOIN "OPOR" T3 ON T2."DocEntry" = T3."DocEntry"
INNER JOIN "RDR1" T4 ON T4."BaseEntry" = T1."DocEntry" AND T4."BaseLine" = T1."LineNum" AND T4."BaseType" = T0."ObjType"
INNER JOIN "ORDR" T5 ON T4."DocEntry" = T5."DocEntry"
INNER JOIN "PCH1" T6 ON T6."BaseEntry" = T2."DocEntry" AND T6."BaseLine" = T2."LineNum" AND T6."BaseType" = T2."ObjType"
INNER JOIN "OPCH" T7 ON T7."DocEntry" = T6."DocEntry"
INNER JOIN "INV1" T8 ON T8."BaseEntry" = T4."DocEntry" AND T8."BaseLine" = T4."LineNum" AND T8."BaseType" = T4."ObjType"
INNER JOIN "OINV" T9 ON T9."DocEntry" = T8."DocEntry"
INNER JOIN "OITM" T10 ON T10."ItemCode" = T1."ItemCode"
INNER JOIN "OITB" T11 ON T10."ItmsGrpCod" = T11."ItmsGrpCod"
INNER JOIN "OSLP" T12 ON T9."SlpCode" = T12."SlpCode
UNION ALL
SELECT T7."DocNum" AS "AP Invoice No.", T7."DocDate" AS "AP Invoice Date", T7."DocCur" AS "AP Invoice Cur", T6."Price" AS "AP Invoice Unit Price",
T6."Quantity" AS "AP Invoice Qty", T6."Price" * T6."Quantity" AS "AP Invoice Amount", (T6."Price" * T6."Quantity" * T7."DocRate")/1507.5 AS "AP Invoice Amount USD",
CASE
WHEN T7."DocTotal" - T7."PaidToDate" = 0 THEN 'Y'
ELSE 'N'
END AS "Paid Y/N", T7."Project" AS "Project #", T3."DocDate" AS "Date of PO", T3."CardCode" AS "BP Supplier Code", T3."CardName" AS "BP Supplier Name",
T2."Quantity" AS "PO Quantity", T2."Price" * T2."Quantity" AS "PO Amount", (T2."Price" * T2."Quantity" * T3."DocRate") / 1507.5 AS "PO Amount USD",
T11."ItmsGrpNam" AS "Item Group", T10."U_SubCat1" AS "Item Sub-Category",
T10."ItemCode",
T10."ItemName" AS "Product Name", T5."CardCode" AS "BP Client Code", T5."CardName" AS "BP Client Name", T5."DocNum" AS "SO #", T5."DocDate" AS "SO Date",
T4."Price" * T4."Quantity" AS "SO Amount", T9."DocNum" AS "AR Invoice No.", T9."DocDate" AS "AR Invoice Date", T9."DocCur" AS "AR Invoice Cur", T8."Price" AS "AR Invoice Unit Price", T8."Quantity" AS "AR Invoice Qty", T8."Price" * T8."Quantity" AS "AR Invoice Amount", (T8."Price" * T8."Quantity" * T9."DocRate")/1507.5 AS "AR Invoice Amount USD", T12."SlpName" AS "Sales Employee Name"
FROM "OQUT" T0
INNER JOIN "QUT1" T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN "RDR1" T4 ON T4."BaseEntry" = T1."DocEntry" AND T4."BaseLine" = T1."LineNum" AND T4."BaseType" = T0."ObjType"
INNER JOIN "ORDR" T5 ON T4."DocEntry" = T5."DocEntry"
INNER JOIN "POR1" T2 ON T2."BaseEntry" = T4."DocEntry" AND T2."BaseLine" = T4."LineNum" AND T2."BaseType" = T5."ObjType"
INNER JOIN "OPOR" T3 ON T2."DocEntry" = T3."DocEntry"
INNER JOIN "PCH1" T6 ON T6."BaseEntry" = T2."DocEntry" AND T6."BaseLine" = T2."LineNum" AND T6."BaseType" = T2."ObjType"
INNER JOIN "OPCH" T7 ON T7."DocEntry" = T6."DocEntry"
INNER JOIN "INV1" T8 ON T8."BaseEntry" = T4."DocEntry" AND T8."BaseLine" = T4."LineNum" AND T8."BaseType" = T4."ObjType"
INNER JOIN "OINV" T9 ON T9."DocEntry" = T8."DocEntry"
INNER JOIN "OITM" T10 ON T10."ItemCode" = T1."ItemCode"
INNER JOIN "OITB" T11 ON T10."ItmsGrpCod" = T11."ItmsGrpCod"
INNER JOIN "OSLP" T12 ON T9."SlpCode" = T12."SlpCode
balakumar.viswanathan2 the flow on the second condition image below for your reference.
| User | Count |
|---|---|
| 99 | |
| 11 | |
| 11 | |
| 6 | |
| 6 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.