cancel
Showing results for 
Search instead for 
Did you mean: 

I need Purchase register report item wise with item group name please help...

former_member835712
Participant
0 Kudos
267

I need Purchase register report item wise with item group name please help...

Accepted Solutions (1)

Accepted Solutions (1)

dineshsakthivadivel
Participant
0 Kudos

Hi Gyan, This is a base query, you can enhance it based on the required fields. This supports HANA Database, if you have SQL Database you can modify the same.

SELECT T1."DocEntry" "DocEntry", CASE T0."ObjType" WHEN '18' THEN 'A/P Invoice' WHEN '19' THEN 'A/P Credit Memo' END AS "Document Type", CASE T0."DocStatus" WHEN 'O' THEN 'Open' WHEN 'C' THEN 'Closed' END AS "Document Current Status", T0."DocNum",T0."DocDate",T0."CardCode",T0."CardName",T1."ItemCode",T1."Dscription",T3."ItmsGrpNam",T1."Quantity" "Quantity",T1."Price" "Unit Price",T1."LineTotal" "Line Total" FROM OPCH T0 INNER JOIN PCH1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode"=T2."ItemCode" INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" WHERE T0."CANCELED" = 'N' UNION ALL SELECT T1."DocEntry" "DocEntry", CASE T0."ObjType" WHEN '18' THEN 'A/P Invoice' WHEN '19' THEN 'A/P Credit Memo' END AS "Document Type", CASE T0."DocStatus" WHEN 'O' THEN 'Open' WHEN 'C' THEN 'Closed' END AS "Document Current Status", T0."DocNum",T0."DocDate",T0."CardCode",T0."CardName",T1."ItemCode",T1."Dscription",T3."ItmsGrpNam",T1."Quantity"*-1 "Quantity",T1."Price"*-1 "Unit Price", T1."LineTotal"*-1 "Line Total" FROM ORPC T0 INNER JOIN RPC1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode"=T2."ItemCode" INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" WHERE T0."CANCELED" = 'N'

former_member835712
Participant

Thanks Sir...

Answers (2)

Answers (2)

dineshsakthivadivel
Participant
0 Kudos

Hi Gyan,

You need to open a new question thread for the other related subject.

dineshsakthivadivel
Participant
0 Kudos

Hi Gyan,

kindly close the thread if your query was answered

former_member835712
Participant
0 Kudos

Sir i Need Bill of Materials report with average price and Last purchase price..kinly share the query please