RE: [sap-business-one] Common keys of Purchase Order, Goods Receipt PO and A/P Invoice
Posted by
Admin at
|
Share this post:
|
|
1 Comments
 | Posted by ilun82 (System Analyst) on Apr 7 at 9:52 PM |  Mark as helpful |
Thanks.
For 2nd query:
FIND DELIVERIES AND AR INVOICES LINKED TO A SPECIFIC SALES ORDER:
SELECT distinct D1.BaseDocNum AS 'Sales Order No', 'DN', D0.DocNum as 'Delivery', D0.DocDate, D0.DocDueDate, D0.DocTotal,
'IN', I0.DocNum as 'Invoice', I0.DocDate, I0.DocDueDate as 'Due Date', I0.DocTotal, I0.PaidToDate
FROM ((ODLN D0 inner Join DLN1 D1 on D0.DocEntry = D1.DocEntry) full outer join (OINV I0 inner join INV1 I1 on I0.DocEntry I1.DocEntry) on (I1.BaseType AND D1.DocEntry = I1.BaseEntry AND D1.LineNum=I1.BaseLine))
WHERE (D1.BaseType AND D1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseEntry IN (SELECT Distinct DocEntry FROM DLN1 WHERE BaseType AND BaseRef='[%0]'))
At my browser it preview some symbol . Does does this symbol for?
From: sap-business-one@Groups.ITtoolbox.com
To: leeailoon@hotmail.com
Date: Wed, 7 Apr 2010 18:35:12 -0400
Subject: RE:[sap-business-one] Common keys of Purchase Order, Goods Receipt PO and A/P Invoice
Posted by csicard (Sr. Consultant)
on Apr 7 at 6:36 PM Mark as helpful
Here are two working queries that may be helpful in getting you started:
FIND ALL GOODS RECEIPTS LINKED TO A SPECIFIC PO:
SELECT t0.docnum as 'Goods Receipt #', T0.DocDate as 'Received', T1.ItemCode, T1.BaseRef as 'PO #', T1.DocDate as 'PO Date',T1.Quantity as 'Qty Recd', T1.Dscription,T1.BaseQty as 'PO Qty', T1.OpenQty as 'Qty Open', T1.Price as 'Unit Price', T1.LineTotal, T1.WhsCode, T1.INMPrice, T1.LineStatus, T0.CardCode FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry and t1.baseref= [%]
FIND DELIVERIES AND AR INVOICES LINKED TO A SPECIFIC SALES ORDER:
SELECT distinct D1.BaseDocNum AS 'Sales Order No', 'DN', D0.DocNum as 'Delivery', D0.DocDate, D0.DocDueDate, D0.DocTotal,
'IN', I0.DocNum as 'Invoice', I0.DocDate, I0.DocDueDate as 'Due Date', I0.DocTotal, I0.PaidToDate
FROM ((ODLN D0 inner Join DLN1 D1 on D0.DocEntry = D1.DocEntry) full outer join (OINV I0 inner join INV1 I1 on I0.DocEntry = I1.DocEntry) on (I1.BaseType AND D1.DocEntry = I1.BaseEntry AND D1.LineNum=I1.BaseLine))
WHERE (D1.BaseType AND D1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseEntry IN (SELECT Distinct DocEntry FROM DLN1 WHERE BaseType AND BaseRef='[%0]'))
---------------Original Message---------------
From: ilun82
Sent: Wednesday, April 07, 2010 6:33 AM
Subject: Common keys of Purchase Order, Goods Receipt PO and A/P Invoice
> I need to do a query, to list all Purchase Order, with respective Purchase Order's target document (i.e. Goods Receipt PO) and respective Goods Receipt PO's target document (i.e. A/P Invoice). Please advise the common key for these 3 documents. Or is there any other way to workout the list?
>
>
>
> Hotmail: Powerful Free email with security by Microsoft. Get it now.
__.____._ For 2nd query:
FIND DELIVERIES AND AR INVOICES LINKED TO A SPECIFIC SALES ORDER:
SELECT distinct D1.BaseDocNum AS 'Sales Order No', 'DN', D0.DocNum as 'Delivery', D0.DocDate, D0.DocDueDate, D0.DocTotal,
'IN', I0.DocNum as 'Invoice', I0.DocDate, I0.DocDueDate as 'Due Date', I0.DocTotal, I0.PaidToDate
FROM ((ODLN D0 inner Join DLN1 D1 on D0.DocEntry = D1.DocEntry) full outer join (OINV I0 inner join INV1 I1 on I0.DocEntry I1.DocEntry) on (I1.BaseType AND D1.DocEntry = I1.BaseEntry AND D1.LineNum=I1.BaseLine))
WHERE (D1.BaseType AND D1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseEntry IN (SELECT Distinct DocEntry FROM DLN1 WHERE BaseType AND BaseRef='[%0]'))
At my browser it preview some symbol . Does does this symbol for?
From: sap-business-one@Groups.ITtoolbox.com
To: leeailoon@hotmail.com
Date: Wed, 7 Apr 2010 18:35:12 -0400
Subject: RE:[sap-business-one] Common keys of Purchase Order, Goods Receipt PO and A/P Invoice
Posted by csicard (Sr. Consultant)
on Apr 7 at 6:36 PM Mark as helpful
Here are two working queries that may be helpful in getting you started:
FIND ALL GOODS RECEIPTS LINKED TO A SPECIFIC PO:
SELECT t0.docnum as 'Goods Receipt #', T0.DocDate as 'Received', T1.ItemCode, T1.BaseRef as 'PO #', T1.DocDate as 'PO Date',T1.Quantity as 'Qty Recd', T1.Dscription,T1.BaseQty as 'PO Qty', T1.OpenQty as 'Qty Open', T1.Price as 'Unit Price', T1.LineTotal, T1.WhsCode, T1.INMPrice, T1.LineStatus, T0.CardCode FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry and t1.baseref= [%]
FIND DELIVERIES AND AR INVOICES LINKED TO A SPECIFIC SALES ORDER:
SELECT distinct D1.BaseDocNum AS 'Sales Order No', 'DN', D0.DocNum as 'Delivery', D0.DocDate, D0.DocDueDate, D0.DocTotal,
'IN', I0.DocNum as 'Invoice', I0.DocDate, I0.DocDueDate as 'Due Date', I0.DocTotal, I0.PaidToDate
FROM ((ODLN D0 inner Join DLN1 D1 on D0.DocEntry = D1.DocEntry) full outer join (OINV I0 inner join INV1 I1 on I0.DocEntry = I1.DocEntry) on (I1.BaseType AND D1.DocEntry = I1.BaseEntry AND D1.LineNum=I1.BaseLine))
WHERE (D1.BaseType AND D1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseRef='[%0]') OR (I1.BaseType AND I1.BaseEntry IN (SELECT Distinct DocEntry FROM DLN1 WHERE BaseType AND BaseRef='[%0]'))
---------------Original Message---------------
From: ilun82
Sent: Wednesday, April 07, 2010 6:33 AM
Subject: Common keys of Purchase Order, Goods Receipt PO and A/P Invoice
> I need to do a query, to list all Purchase Order, with respective Purchase Order's target document (i.e. Goods Receipt PO) and respective Goods Receipt PO's target document (i.e. A/P Invoice). Please advise the common key for these 3 documents. Or is there any other way to workout the list?
>
>
>
> Hotmail: Powerful Free email with security by Microsoft. Get it now.
Copyright © 2010 Toolbox.com and message author.
Toolbox.com 4343 N. Scottsdale Road Suite 280, Scottsdale, AZ 85251
Toolbox.com 4343 N. Scottsdale Road Suite 280, Scottsdale, AZ 85251
Related Content
In the Spotlight
White Papers
In the Spotlight
Earn Recognition for Your Contributions at Toolbox for IT. Gain Points for Community Achievements
View this thread online
Manage group e-mails
Create an FAQ on this topic
Tell us what you think
Unsubscribe from discussion
Manage group e-mails
Create an FAQ on this topic
Tell us what you think
Unsubscribe from discussion
_.____.__ 
