Relationship Map Query/SQL Script

[Former Member]

Hi all,

Can anyone help me to do this? A query to show Sales order,Purchase Order, A/R Invoice, CMs, Delivery, Returns, Incoming payment, Deposit, GRPO, A/P Invoice, Outgoing Payment. All in one query, seems like the relationship map

Any help will be highly appreciated. Thanks.

[Former Member · Accepted solution]

i did this query but seems something is wrong versus validation in relationship map in SBO

SELECT distinct

    CASE WHEN a.CardCode IS NULL  THEN 'IMDS TX' ELSE a.CardCode END as 'CARDCODE'

    ,CASE WHEN a.CardName IS NULL  THEN 'IMDS TX' ELSE a.CardName END as 'CARDNAME'

    ,t1.so# as 'SO #'

    ,t1.dlvry# as 'DR #'

    ,t1.Inv# as 'A/R INVOICE'

    ,t1.ORCT# as 'INCOMING PAYMENT'

    ,t1.DEPO# as 'DEPOSIT #'

    ,t2.po# as 'PO #'

    ,t2.grpo# as 'GRPO #'

    ,t2.APInv# as 'A/P INVOICE'

    ,t2.OVPM# as 'OUTGOING PAYMENT'

FROM        

    ocrd a

          right join

    -- Get Sales Module

          (select distinct

                    --a.CardName

                    a.cardcode

                    ,a.docnum as so#

                    ,CASE when b.TargetType = '15' then          c.DocNum else 0 end as dlvry#

                    ,CASE when b.TargetType = '13'

                                                  then (select distinct a.DocNum from OINV a where b.TrgetEntry = a.docnum)

                                when d.TargetType = '13' then e.docnum

                              end as Inv#

                    ,h.DocNum as ORCT#

                    ,k.DeposId as DEPO#

                                                             from ORDR a

                                                             left outer join RDR1 b on a.DocEntry = b.DocEntry

                                                             left outer join ODLN c on b.TrgetEntry = c.DocNum

                                                             left outer join DLN1 d on c.DocEntry = d.DocEntry

                                                             left outer join OINV e  on d.TrgetEntry = e.DocNum

                                                             left outer join INV1 f on e.DocEntry = f.DocEntry

                                                             left outer join RCT2 g on g.DocEntry = e.DocEntry

                                                             left outer join ORCT h on g.DocNum = h.DocEntry

                                                             left outer join RCT1 i on h.DocEntry = i.docnum

                                                             left outer join OCHH j on i.CheckNum = j.CheckNum

                                                             left outer join ODPS k on j.transNum = k.TransAbs

                                                             where a.CANCELED = 'N'

                                                             and a.DocNum is not null                                                  

                                                             ) t1 on t1.CardCode = a.CardCode

          right join

                                                            (select distinct a.NumAtCard,a.DocNum as po#,a.DocDate,a.DocDueDate,a.doctotal as doctotal,a.CardName,

                                                                                                     b.BaseCard,b.BaseEntry,c.POtotal,d.DocNum as GRPO#,d.DocTotal as OPDNTotal,

                                                                                                     d.TaxDate as OPDNTaxDate,e.SVolume,e.SWeight,j.Inv#  as APInv#, j.OVPM#

                                                            from OPOR a

                                                            left outer join POR1 b on a.DocEntry = b.DocEntry

                                                            left outer join

                                                                                          (select b.BaseCard,SUM(a.doctotal) as POtotal from OPOR a

                                                                                           left outer join por1 b on a.docentry = b.docentry

                                                                                           group by b.BaseCard ) c on c.BaseCard = b.BaseCard

                                                            left outer join OPDN d on b.trgetentry = d.docnum

                                                            left outer join

                                                                                          (select a.docentry,a.TrgetEntry,SUM(SVolume) as Svolume,SUM(SWeight1) as Sweight

                                                                                           from PDN1 a

                                                                                           left outer join OITM b on a.ItemCode = b.ItemCode

                                                                                           group by a.DocEntry,a.TrgetEntry

                                                                                          )e on e.DocEntry = d.DocEntry

                                                      --left outer join OPCH f on e.TrgetEntry = f.DocNum

                                                      left outer join

                                                                                (select i.DocNum as Inv#, h.DocNum as OVPM#  from OVPM g

                                                                                left outer join VPM2 h on g.DocNum = h.DocEntry

                                                                                left outer join OPCH i on h.DocEntry = i.DocEntry )j on e.TrgetEntry = j.Inv#

                                                            where a.CANCELED = 'N'

                                                            )t2 on t1.so# = t2.BaseEntry

[kothandaraman_nagarajan · Accepted solution]

Hi,

The correct link between sales and delivery order should be in this way and same way you can try for delivery to Invoice....

ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry left join DLN1 T2 on T2.[BaseEntry]  = t0.docentry and  T2.[BaseLine]  = T1.[LineNum] INNER JOIN ODLN T3 ON T2.DocEntry = T3.DocEntry

Hope helpful.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

Thanks, though i'm still looking at linking tables ORDR-ORCT & OPOR-OVPM through ORDR Docnum & POR1 BaseEntry.

Thanks,

Richard

[KennedyT21 · Accepted solution]

Hi Richard

Try This

FROM POR1 T1

LEFT OUTER JOIN PDN1  T3 on T3.baseentry =T1.docentry AND T3.BaseLine=t1.LineNum  AND T3.BaseType='22'

LEFT OUTER JOIN PCH1  T4 on T4.BaseEntry = T3.DocEntry AND T4.BaseLine=t3.LineNum AND T4.BaseType='20'

LEFT OUTER JOIN RPD1  T5 on T5.BaseEntry = T3.DocEntry AND T5.BaseLine=t3.LineNum AND T5.BaseType='20'

LEFT OUTER JOIN RPC1  T6 on T6.BaseEntry = T4.DocEntry AND T6.BaseLine=t4.LineNum AND T6.BaseType='18'

LEFT OUTER JOIN OPOR P1 ON T1.DocEntry = P1.DocEntry

LEFT OUTER JOIN OPDN P2 ON T3.DocEntry = P2.DocEntry

LEFT OUTER JOIN OPCH P3 on T4.DocEntry = P3.DocEntry

LEFT OUTER JOIN ORPD P5 on T5.DocEntry = P5.DocEntry

LEFT OUTER JOIN ORPC P6 on T6.DocEntry = P6.DocEntry 

INNER JOIN vpm2 S1 ON P3.DocEntry = S1.DocEntry and s1.InvType='18'

Inner JOIN ovpm s2 ON s1.DocNum = s2.DocNum

[KennedyT21 · Accepted solution]

Have you checked does the issue solved or not if not ask your questions

if solved pls close the thread with correct or helpful answers

Regards

Kennedy

[Former Member]

Hi,

I am not sure why you have such request. AP & AR has very few direct link from those tables. How you can link them? Give a concrete business example.

Thanks,

Gordon

[Former Member]

I'm doing a customized report that will show the full transaction path from ORDR to OVPM. That is SO-ORCT linked to PO-OVPM. The process flow starts in SO (Drop ship setup). Here's what i want to obtain in one row, i'd like to see this trail in a query.

ORDR#>ODLN#>OINV#>ORCT#>ODPS#>OPOR#>OPDN#>OPCH#>OVPM#

link will be coming from ORDR & PO. I posted the query i made initially (still in moderators review)

Thanks,

Richard

[Former Member]

Hi Richard

This type of Query is extremely useful especially if you have a customer that does back to back orders.

Please can you let me know what you final query is as I would like to test the process from start to finish

Best Regards

Andrew

[Former Member]

Hi Kennedy T. the goal is to see the entire relationship (transaction flow) row. i.e given that a purchase order uses procurement document wizard. That is Sales order to Deposit linked to Purchase order to Outgoing payment.

[KennedyT21]

Hi Richard Gabaldon...

You can pull the information from OJDT & JDT1 for all the financial transaction.

Link for Sales order to CN

OINV II inner join INV1 I On I.DocEntry=II.DocEntry

   left outer join DLN1 D on I.BaseEntry=D.DocEntry and I.BaseType=15 and I.BaseLine=D.LineNum

left outer join ODLN DD on DD.DocEntry=D.DocEntry

   left outer join RDR1 O on (I.BaseEntry=O.DocEntry and I.BaseType=17 and I.BaseLine=O.LineNum)

     or (D.BaseEntry=O.DocEntry and D.BaseType=17 and D.BaseLine=O.LineNum)

left outer join ORDR OO on OO.DocEntry=O.DocEntry

Same way using the Union all function you have to link the purchase

Regards

Kennedy

[MukeshSingh]

Kennedy T,

Thanks for nice query but this query same problem.

i required Purchase Module full relationship map query

Please suggest.

Thanks

Regards,

Mukesh Singh

[Former Member]

Hi Richard,

You can use Union All for this purpose

Regards,

JP