Skip to Content

Relationship Map Query/SQL Script

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.

Add comment
10|10000 characters needed characters exceeded

5 Answers

  • Best Answer
    Posted on Aug 03, 2013 at 07:29 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 03, 2013 at 01:28 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2013 at 11:37 PM

    Hi Richard,

    You can use Union All for this purpose

    Regards,

    JP

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 03, 2013 at 07:01 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 03, 2013 at 07:38 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Richard Gabaldon

      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