Hello Experts,
Is there any possibilility to find the last purchase details of raw materials based on the posted purchase invoice.
I am trying with SQL server query but query is not 100% correct please give me some idea to resolve it.
My query is below.
select max(PIH.[Posting Date]),PIH.[Currency Code],[Direct Unit Cost]-([Line Discount Amount]/nullif(Quantity,0)) "Last Purchase Price",[Unit Cost (LCY)],PIL.No_ "Item No.",Description,[Document No_],
case PIH.[Incom Terms] when 0 then ' ' when 1 then 'Ex-Works' when 2 then 'FCA' when 3 then 'CPT' when 4 then 'CIP' when 5 then 'DAT' when 6 then 'DAP' when 7 then 'DDP'
when 8 then 'FAS' when 9 then 'FOB' when 10 then 'CFR' when 11 then 'CIF' when 12 then 'DAF'
when 13 then 'DES' when 14 then 'DEQ' when 15 then 'DDU' when 16 then 'FOR' end as [Incom Terms]
from [India Private Limited$Purch_ Inv_ Line] PIL
left outer join [India Private Limited$Purch_ Inv_ Header] PIH on PIH.No_ = PIL.[Document No_]
where PIL.No_<>'' and PIL.Type=2
group by PIH.[Currency Code],[Direct Unit Cost]-([Line Discount Amount]/nullif(Quantity,0)),[Unit Cost (LCY)],PIL.No_ ,Description,[Document No_],
PIH.[Incom Terms]
*This post is locked for comments
I have the same question (0)
