Target Type

[Former Member]

Hi Experts,

I am trying to create a query where i have to filter which of the Purchase Orders are already processed with AP Downpayment Invoice. Unfortunately, I cannot locate the target entry or any field that would identify the said Purchase Orders.

Thanks in advance.

Arlene

[Former Member]

Hi Arlene,

Try:

SELECT

T0.[DocNum] 'PO Number', T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T3.[DocNum] 'AP Downpayment Invoice'

FROM OPOR T0

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN DPO1 T2 ON T2.BaseEntry = T0.DocEntry and T1.LineNum = T2.BaseLine and T2.BaseType='22'

INNER JOIN ODPO T3 ON T2.DocEntry = T3.DocEntry

Thanks,

Gordon

[Former Member]

Hi Arlene,

Hope this helps you. This are PO's with corresponding AP Downpayment Invoice No.

SELECT

T0.[DocNum] 'PO Number', T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T3.[DocNum] 'AP Downpayment Invoice'

FROM OPOR T0 

INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN DPO1 T2 ON T1.TrgetEntry = T2.DocEntry and T1.LineNum = T2.BaseLine

INNER JOIN ODPO T3 ON T2.DocEntry = T3.DocEntry

Regards,

[former_member212181]

Hi Arlene,

In Purchase order Target Type will be GRPO reference.

You will get PO reference in AP down payment invoice Line level as Base reference.

Please try below query

SELECT T0.DocNum[PONum], T0.DocDate[PO Date], T0.CardCode, T0.CardName, T1.ItemCode, T4.ItemName, T1.Quantity, T1.Price, T3.DocNum[AP DPM Invoice Num], T3.DocDate[AP DPM Invoice Dt]

FROM OPOR T0 

  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

  Left Outer Join DPO1 T2 on T2.BaseType = 22 and T2.BaseEntry = T1.DocEntry and T2.BaseLine = T1.LineNum

  LEFT OUTER JOIN ODPO T3 ON T2.DocEntry = T3.DocEntry

  INNER JOIN OITM T4 ON T1.[ItemCode] = T4.[ItemCode]

WHERE T0.DocDate >=[%0] and  T0.DocDate <=[%1]

Thanks

Unnikrishnan