• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Best way to pull accurate received date for Requisition line item.

 2 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
Normajean
New Member Send Private Message
Posts: 3
New Member
2/12/2013 9:49 PM
Hello Lawson folks;
I am a new member of the Enterprise data warehouse team and am working on the Lawson Supply Chain project.
Prior to my coming on board logic had been written to pull the received date for requisition line items by using the following process:
1. join to polinesrc to get the ponumber\poline number for the requisition line
(Join is polinesrc.source_doc_n = reqline.req_number and polinesrc.src_line_nbr = reqline.line_nbr)
2. join to porecline to get the received date for the po_number\po_line we found in #1 above (join is poreceive.po_number and poreceive.po_line_nbr.)

Comment: This logic is faulty as join #1 can produce multiples rows (there can be multiple polinesrc records associated with the same req_number\reqline_line_nbr.) AND join #2 can produce mulitple rows because the same po_number\po_line can be received multiple times.

So, now I need to find a clean, accurate way to find the receive date for each requisition line. I looked around and saw that there is a table called RQTRANS that looks like it might fill the bill.
The description of this table says "This file is the history of all deliveries for all requisitions. This file contains all requisition types: inventory, nonstock, special order, and services."
We don't currently bring this table into our enterprise data warehouse - and it will involve quite a bit of work to do so. We would for sure do this if it would fill the need but I thought I'd post something here to see if I could get some feedback prior to starting the project to let us know that we are on the right track.
Many thanks in advance for any feedback, tips or input you might be able to provide about this approach (or perhaps an even better one.)
Kat V
Veteran Member Send Private Message
Posts: 1020
Veteran Member
2/18/2013 8:48 PM
Why is that logic faulty? If you receive the same line twice wouldn't you want to see BOTH dates? We prefer the PO Line Source table tied to PO and Receipt for the very reasons you do not.

But as to your question - yes, RQTRANS does give you the receipt date for the line. Doc Type shows as PO as receipts are PO30. Req Number is Req Number and Doc Number is Receiver. Seq is Req Line number and Line Number is Receipt line number (PO line number). Trans Date is the actual date of receipt.

It doesn't give you PO number for the req line and would be null on any inner joins in SQL. The quantities and costs breakdown to stock uom - so if they order 1 BX @$25 on the req it shows as 25 EA @$1 on RQTRANS when it's received.
Normajean
New Member Send Private Message
Posts: 3
New Member
2/19/2013 5:44 PM
Hi Kat;
Thanks so much for your reply and the information about the RQTRANS table.
So why I said that using the PO schema to pull requlisition receipt dates is faulty is that there is no way to tie a porecline item to a particular requsition line.
For example, let's say I have 5 different requisition numbers associated with the same POnumber\line...and then say I have 5 poreclines (let's say one a month over the span of 5 months) that are all associated with that same POnumber\line.
There is no way to distinguish the receipt date for one of those singular requisition numbers...because the lowest level of granularity in porecline is the poNumber\line.
Because porecline does not store an association with a particular requisition (only with the PONumber\line) I can't know which one of the requisition items was received in each of the five months...there is no way to tie the right porecline with a particular requisition,
I'm sure that's why Lawson created the RQTRANS table - because the PO schema doesn't support auditing the requsition life cycle (it's PO-centric not requisition-centric.)
In our supply chain we're looking at the turn around time for each requisition so we'll need to move to the RQTRANS table for that.
Thanks again for your help,
Normajean