Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Tricky query

Dear Expert,

Can you pls explain this below query because i want to use this query by doing some changes. It is tricky query.

For me, the tricky part is from "Recent Vendor" .

SELECT 'Delivery Note' as 'Doc Type',T0.docdate as 'Srat date', T0.[DocDate] as'Postingdate', T2.[Name] as'Posting period', T0.[DocNum] as'Doc No.', T0.[CardCode]as 'Carcode',

T0.[CardName] as'Card Name',T0.numatcard as 'Contract number', T1.[ItemCode] as'Item code', T1.[Dscription]as'Item Description',

(case when t1.currency='RMB' then T1.[price] else (T1.[Price]* t0.docrate)end)*  T1.[Quantity]  as 'Sles TO',

T1.[StockPrice] *  T1.[Quantity]  as 'COGS',

T1.[Quantity] as'Quantity', T0.[Comments], T3.[SlpName] 

,t6.ItmsGrpNam,T1.WHSCODE, isnull(a5.cc,t5.cardcode)as 'Vendor',

t8.Name

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OFPR T2 ON T0.FinncPriod = T2.AbsEntry

INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode

left join OITM t5 on t5.ItemCode=t1.itemcode

LEFT JOIN OITB T6 ON T6.ItmsGrpCod=t5.ItmsGrpCod

LEFT JOIN Ohem T7 ON T7.middleName=t3.Memo

LEFT JOIN Oubr T8 ON T8.Code=t7.branch

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% recent vendor


left join (

              

select MAX(t4.cardcode)as 'CC',t4.ItemCode

from (

select max(t3.DocDate) as 'sd',

(t1.itemcode),

t3.cardcode

from POR1 t1

inner join OPOR t3 on t1.DocEntry=t3.DocEntry and t3.CANCELED='N'

inner join (

select max(c1.DocDate) as 'md',b1.itemcode from POR1 b1 inner join OPOR c1 on c1.DocEntry=b1.DocEntry where c1.CANCELED='N' and (b1.ItemCode is not null or b1.ItemCode<>'')

group by b1.ItemCode

) t2 on t2.md=t3.docDate and t2.ItemCode=t1.ItemCode

where t1.ItemCode is not null or t1.ItemCode<>''

group by t1.ItemCode,t3.cardcode) t4

group by t4.ItemCode) a5 on a5.ItemCode=t1.ItemCode

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

where t0.doctype='I' and T0.[docdate]>='[%0]' and T0.[docdate]<='[%1]'  and T0.[CardCode] not like 'sc%' and T0.[CardCode] not like 'sc%'/*DN infor*/






Thanks in advance,

Regards,

Dwaraka




Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question