What is the data item link between the Purch_ Rcpt_ Line, and the Purchase Line tables?
I am trying to link in the Purch_ Rcpt_ Line into a report that has the Purchase Line table currently returning an output.
One way I can think of now is…get the
1.Purchase Header from Purchase Line
2.Get Purchase Receipt Header using Order No. field
- Get purch. receipt Line from Purchase Receipt Header…
When i run this query i am returning 157 results, but when i join the Purch_ Rcpt_ Line table i return 221… am i missing something…
select *
from
[Company$Purchase Line] a
where [Qty_ to Invoice]<>0
and a.[Type]<>2
select *
from
[Company$Purchase Line] a
join [Company$Purch_ Rcpt_ Line] b on b.[Order No_] = a.[Document No_]
and b.[Order Line No_] = a.[Line No_]
where [Qty_ to Invoice]<>0
and a.[Type]<>2
My Goal in joining the Purch_ Rcpt_ Line table is to return the Posting Date, So a user can run a report and see what was received but not invoiced and run the report by a date range…
If you want to show user Qty. Rcd. Not Invoiced then there is one field in Purch.Rcpt Line from where you can show that
Field :- Qty. Rcd. Not Invoiced
Just done similar reports.
It should based on Item Ledger Entry.
Find Type = Purchase Receipt , then you find your PO and posting dates.
This is way better than just based on purchase receipt line.
I agree with Rics… It should be based on item Ledger entry and not TAB121.
Oh OK.
My Goal of the report is to see what PO do not have invoices so the user can search by Posting Date.
Or do a report on the Accrued Purchase Payable GL to return
-posting date
-document #
-vendor
-value
-and purchase order #
Let me dig into the ILE table, Thanks
exactly the report i’ve done.
ILE > Vendor .
Done. (wouldn’t need Purchase Header / Line either… ) [:D][:D]
I guess i do not fully understand,
What is the difference between these 2 queries one from the ILE and one from PRL…? IS there anny documentation i can read to further understand Invoicing
SELECT *
FROM [Company$Item Ledger Entry] a
where [Document Type]=5
and [Source Type]=2
and [Remaining Quantity]<>0
order by [Source No_], [Posting Date]
select * from [Company$Purch_ Rcpt_ Line] a
where a.[Qty_ Rcd_ Not Invoiced]<>0
I’ve thought of it.
Simple . Partial Receipt.
Purchase Receipt Line to Purchase Line is not one to one relationship.
More commonly , it’s N Purchase Receipt Line to 1 Purchase Line
Example,10 ePhones got shipped several times 4+2+3 = 10 to complete the order.
so 3 Receipt Lines to 1 PO line.
Cos next time user will ask… How about Sales Return but not Credit Memo-ed ? you know where you will find the answers [:P][:P]
besides… ILE actual contains better Cost Amount, Expected Cost, Actual Cost in it , where Receipt Lines wouldn’t have .
(eg late item charge cost into the purchase line , adjusted cost for FIFO , Average under posted expected cost)
Hi Sean,
If i understand you correctly… the sole requirement of yours is just to identify those POs which are yet to be invoiced…If that is the case then you can directly use Purchase Line
CASE-I (You are using the PO interface itself to invoice the PO. )
In this case you can use Purchase Line table directly (use the field “Qty. Rcd. Not Invoiced” to get the quantity and “Amt. Rcd. Not Invoiced” to get the corresponding value).
Filter Purchase Line on Document Type as ‘Order’
CASE-II (You are using Get Receipt Lines feature of Purchase Invoice interface)
Even In this case you can use Purchase Line table directly (use the field “Qty. Rcd. Not Invoiced” to get the quantity and “Amt. Rcd. Not Invoiced” to get the corresponding value).
Filter Purchase Line on Document Type as ‘Order’
Advantage of using Purchase line would be that the number of records in this table would be very less as compared to ILE(TAB32) or TAB121.
Case-I --The PO lines would automatically get deleted on complete invoicing of all the lines.
CASE-II-- The PO shoud be deleted periodically from… Administration->IT Administration->data Deletion->Purchase Documents->Delete Invoiced Purchase Orders.
In both the cases the volumn of Purchase line would reduce and hence you would get better prformance for your report.
Hope this solves your problem
/Anup
and if i wanted to filter by Posting date, if i linked the Purchase Line and Receipt line table I can get the posting date from the Receipt line table?
Hi Sean,
Yes, That way you can get your desired result. But instead of Purch. Rcpt Line I would suggest to link with Purch. Rcpt Header (Filter on Order No. and Posting Date). The benifit of filtering on Purch. Rcpt Header is that the number of records in the header would be far less than in the Purch Rcpt Line. So the Perfoamnce would be better.
/Anup