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