How to link OPCH, OJDT and JDT1 in A/P Invoice

[former_member1088267]

i want to create a new template for A/P Invoice, which can show journal entry details in the document. But when i make the link for OPCH, OJDT and JDT1, there will show duplicate rows for AP Invoice, and there is blank for journa entry field. Below is the query:

SELECT "OPCH"."DocNum", "OPCH"."CardCode", "OPCH"."LicTradNum", "OPCH"."U_BP_Name", "OPCH"."TaxDate", "OPCH"."DocDueDate", "OPCH"."DocCur", "OPCH"."NumAtCard", "OPCH"."Address", "NNM1"."BeginStr", "OCRD"."Phone1", "OCTG"."PymntGroup", "PCH1"."TreeType", "PCH1"."ItemCode", "PCH1"."Dscription", "PCH1"."Quantity", "PCH1"."Price", "PCH1"."VatPrcnt", "PCH1"."GTotal", "PCH1"."GTotalFC", "OPCH"."Comments", "JDT1"."ShortName" FROM ((((( "SAP_FANS_Production"."dbo"."OPCH" "OPCH" LEFT OUTER JOIN "SAP_FANS_Production"."dbo"."NNM1" "NNM1" ON "OPCH"."Series" = "NNM1"."Series" ) LEFT OUTER JOIN "SAP_FANS_Production"."dbo"."OCRD" "OCRD" ON "OPCH"."CardCode" = "OCRD"."CardCode" ) LEFT OUTER JOIN "SAP_FANS_Production"."dbo"."OCTG" "OCTG" ON "OPCH"."GroupNum" = "OCTG"."GroupNum" ) INNER JOIN "SAP_FANS_Production"."dbo"."PCH1" "PCH1" ON "OPCH"."DocEntry" = "PCH1"."DocEntry" ) INNER JOIN "SAP_FANS_Production"."dbo"."OJDT" "OJDT" ON "OPCH"."TransId" = "OJDT"."TransId" ) LEFT OUTER JOIN "SAP_FANS_Production"."dbo"."JDT1" "JDT1" ON (((( "OJDT"."TransId" = "JDT1"."TransId" ) AND ( "OJDT"."DueDate" = "JDT1"."DueDate" )) AND ( "OJDT"."TransType" = "JDT1"."TransType" )) AND ( "OJDT"."CreatedBy" = "JDT1"."CreatedBy" )) AND ( "OJDT"."RefDate" = "JDT1"."RefDate" ) WHERE "OPCH"."DocNum" = 1910000054 ORDER BY "OPCH"."DocNum"

[msundararaja_perumal]

Hello,

If my memory serves right you can use the TransType and BaseRef of OJDT to link in the subreport.

Thanks.

[former_member1088267]

Hi, thank you. I add subreport and use TransId to link, it works.