Purchase Analysis with Landed cost details.

243 Views

Dear Experts,

I am creating a query to have a report for Purchase analysis based on AP/Invoice and also adding the Landed cost details with it. the query which I am using is as below:

SELECT Distinct T0.[DocDate]'Posting Date',T0.[DocNum], T0.[NumAtCard]'Vendor Ref. no.',T0.[CardCode]'Vendor Code', T0.[CardName]'Vendor Name',T1.[ItemCode]'Item No.',T1.[Dscription]'Item Description',T1.[Quantity],T1.[Price], T1.[Rate]'Exchange Rate',T1.[TaxCode], ( T1.[Price] * T1.[Quantity])'Total LC',T1.[WhsCode]'warehouse',T1.[AssblValue],T3.[ChapterID]'HSN',( T0.[DocTotal] - T0.[DiscSum] ) 'Total Before Discount',T0.[PaidToDate]'Total Amount Due',T4.[Weight1], T4.[TtlCustLC]'Custom Value',T4.[TtlExpndLC]'Allocation Cost', T4.[PriceAtWH]'Whse Price', T4.[LineTotal]'Total' FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OCHP T3 ON T2.[ChapterID] = T3.[AbsEntry] INNER JOIN IPF1 T4 ON T2.[ItemCode] = T4.[ItemCode] WHERE T0.[DocType]='I' and T0.[DocDate] between [%0] and [%1]

but when I run this query, the output is not as required, the data in the item code gets duplicated, like the first item in the invoice will only be shown. requesting you to kindly help.

do let me know if you need more information.

thanks.