How can I find related multi-target documents

[Former Member]

Hi.

SBO version : SAP Business One 8.81 (8.81.311) PL: 03

How can I find related multi-target documents?

For example

  #1 purchase order  100(ea)    ->#2 Goods receipt  50(ea),

                                                     #3 Goods receipt 50(ea)

Except way to find DB, any ohter way? 

I want to know see all target documents.

Thanks.

EJ

[KennedyT21 · Accepted solution]

Hi jjo jjo..

Relationship maps are available in SAP Business One 8.81. Relationship mapping shows the relationship between various SAP Business One documents. On both the sales and accounts payable side of the transaction we can generate various documents which are interrelated. For example we can generate a sales quote, copy it to an AR order, copy the AR order to three separate delivery notes and then create an AR invoice. Relationship mapping keeps a visual representation of these documents. We can produce a relationship map by simply choosing a relevant document (for example, PO, goods receipt etc.), right click the mouse and then click on “relationship map”.

Hope helpful

Regards

Kennedy

[Former Member · Accepted solution]

Hi Kennedy.

I  did right click . But no relationship map.

Reference below sceen.

Thanks

EJ

[Former Member · Accepted solution]

Hi......

Please try this.........

SELECT T0.DocEntry, T0.[DocNum] As 'PO No.', T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.FreeTxt,T1.[Quantity], T1.Price as 'PO Price', T4.[PymntGroup] 'Payment terms', T3.DocNum As 'GRN No.', T2.Quantity  As 'Total Rcvd', T1.[OpenQty], T1.ShipDate FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry LEFT Join PDN1 t2 On T1.DocEntry=T2.BaseEntry and T1.LineNum=T2.BaseLine LEFT Join OPDN T3 On T2.DocEntry=T3.DocEntry Left Join OCTG T4 On T0.GroupNum = T4.GroupNum

Where (T0.Docstatus='[%0]' or '[%0]'='') And (T1.LineStatus='[%1]' or '[%1]'='')

Group By T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T3.DocNum, T2.Quantity, T1.Price, T4.[PymntGroup], T1.FreeTxt, T0.DocEntry, T1.ShipDate

Order By T0.DocEntry

Regards,

Rahul

[Former Member · Accepted solution]

Hi.

Thank you for your answer.

I want to know like check function exist or not.  It doesn't mean  using query.

I can't find relationship map function on 8.81.

someone said exsit realationship map on 8.81. 

Relationship  map or any other function, Both it's ok.

check function exist on 8.81?

(reference my verion:

SAP Business One 8.81 (8.81.311) PL: 03)

Thanks.

EJ

[kothandaraman_nagarajan · Accepted solution]

Hi,

Relationship map only applicable to release 8.81 PL05 and higher.

Thanks & Regards,

Nagarajan

[azeemhafeez]

Hi JJO,

Try this query. This would lead you to the Goods Receipt PO, any Goods Return and AP invoice based on the selected PO. The required parameters are: Purchase Order Number and its Posting Date (which you can change as per your requirements)

SELECT T1.[DocEntry] as 'Doc#', T1.[DocNum] as 'PO#', T1.[DocDate] as 'Dt.', T1.[CardName] as 'Vendor', T0.[ItemCode] as 'Code', T0.[Dscription] as 'Item Name', ROUND(T0.[Quantity],1) as 'Qty.', T0.[unitMsr] as 'U', T0.[Price] as 'Rate', T3.[DocEntry] as 'Doc#', T3.[DocNum] as 'GRN#', T3.[DocDate] as 'Dt.', ROUND(T2.[Quantity],1) as 'Qty.', T2.[WhsCode] as 'Whs', ROUND((T2.[BaseOpnQty]-T2.[Quantity]),2) as 'RPO', T0.[LineStatus] as 'S', T5.[DocEntry] as 'Doc#', T5.[DocNum] as 'RT#', T5.[DocDate] as 'Dt.', ROUND(T4.[Quantity],1) as 'Qty.', T7.[DocEntry] as 'Doc#', T7.[DocNum] as 'Inv#', T7.[DocDate] as 'Dt.', ROUND(T6.[Quantity],1) as 'Qty.'

FROM POR1 T0  FULL JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry FULL JOIN PDN1 T2 ON T1.DocEntry = T2.BaseEntry AND T0.ItemCode = T2.ItemCode FULL JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry FULL JOIN RPD1 T4 ON T3.DocEntry = T4.BaseEntry AND T0.ItemCode = T4.ItemCode FULL JOIN ORPD T5 ON T4.DocEntry = T5.DocEntry FULL JOIN PCH1 T6 ON T3.DocEntry = T6.BaseEntry AND T0.ItemCode = T6.ItemCode FULL JOIN OPCH T7 ON T6.DocEntry = T7.DocEntry

WHERE T1.[DocNum] ='[%0]' AND T1.[DocDate] = '[%1]'

ORDER BY T1.DocEntry,T0.ItemCode, T3.DocEntry, T5.DocEntry, T7.DocEntry

Hope it would help you.

Regards,

Azeem

[Former Member]

Try This

Declare @Category as nvarchar(100)

Declare @POnum as nvarchar(500)

Set @Category = (Select Max(S0.Category) from OFPR S0 where S0.Category='[%0]')

Set @POnum = (Select max(S1.Docnum) From OPOR S1 Where S1.Docnum='[%1]')

Select P0.Docnum as [PO Doc Num],P0.Docdate [PO Doc Date] ,

G0.Docnum [GRPO Doc Num],G0.Docdate [GRPO Doc Date],

I0.Docnum [Invoice Doc Num],I0.Docdate [Invoice Doc Date]

from (OPOR P0 inner join POR1 P1 on P0.Docentry=P1.Docentry) full outer join (OPDN G0 inner join PDN1 G1 on G0.Docentry=G1.Docentry) on

P0.Docentry=G1.BaseEntry full outer join (OPCH I0 inner Join PCH1 I1 on I0.Docentry=I1.Docentry) on G1.Docentry=I1.BaseEntry

Where

P0.Docnum=@POnum and P0.FinncPriod in (Select F0.AbsEntry from OFPR F0 where F0.Category = @Category)

Group By

P0.Docnum,P0.Docdate,G0.Docnum,G0.Docdate,I0.Docnum,I0.Docdate

Order By

P0.Docnum,P0.Docdate

[Former Member]

Hi jjo jjo.......

If you are carrying SAP 8.81 then I suggest you to create a customized query report where you can easily get the details of Base and Target Docs.......

Regards,

Rahul

[kothandaraman_nagarajan]

Hi,

Use relationship map to find target documents.

Thanks & Regards,

Nagarajan

[Former Member]

Hi. Naga Rajan.

Thanks your answer.

But relation map can use since 8.82.

Isn't it?

Thanks

EJ

[kothandaraman_nagarajan]

Hi,

From 8.81 onwards you can use it.

Please close the thread if answered.

Thanks & Regards,

Nagarajan