Purchase Analysis with Landed cost details.

[Former Member]

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.

[NEMOTO]

hi you may not here anymore but this may solve it. the reason why you got duplicate data, IPF1 should assign basedocnum and baseline. Also i am not using india localization so didn't check joint of OCPH table.

SELECT 
  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 OPDN T0 
INNER JOIN PDN1 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 T0.[DocEntry] = T4.[BaseEntry] AND T1.[ItemCode] = T4.[ItemCode] AND T1.[LineNum] = T4.[OrigLine] 
WHERE T0.[DocType]='I' and T0.[DocDate] between [%0] and [%1]