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.

[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