Manual Purchase Orders

Hi Friends,

Here is my third and last post, on the sequence I initiated under requisition and purchase order topic. And we will here talking about the Manual Purchase Orders i.e., without requisitions.

We will start with a sample PO that has been entered manually. Let’s assume our sample PO has PO Number ‘PO-1234’ and PO_HEADER_ID as 123456.

So, to get to its header level information we will simply restrict a record from PO_HEADERS_ALL against the PO_HEADER_ID. The query will be

SELECT *

FROM PO_HEADERS_ALL

WHERE PO_HEADER_ID = 123456;

And this will result our sample PO’s record.

Now after adding the PO Lines and Distributions tables, the query becomes

SELECT *

FROM PO_HEADERS_ALL PHA

, PO_LINES_ALL PLA

, PO_DISTRIBUTIONS_ALL PDA

WHERE PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

AND PDA.PO_LINE_ID = PLA.PO_LINE_ID

AND PHA.PO_HEADER_ID = 123456;

Adding the check to make sure we are only getting the records on Standard Purchase Orders.

SELECT *

FROM PO_HEADERS_ALL PHA

, PO_LINES_ALL PLA

, PO_DISTRIBUTIONS_ALL PDA

WHERE PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

AND PDA.PO_LINE_ID = PLA.PO_LINE_ID

AND PHA.PO_HEADER_ID = 123456

AND PHA.TYPE_LOOKUP_CODE = ‘STANDARD’;

One more thing here is a catch to make sure that we are targeting only manual POs, which is the field REQ_DISTRIBUTION_ID in PO_DISTRIBUTIONS_ALL should be null. So, our query will after incorporating this check will become

SELECT *

FROM PO_HEADERS_ALL PHA

, PO_LINES_ALL PLA

, PO_DISTRIBUTIONS_ALL PDA

WHERE PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

AND PDA.PO_LINE_ID = PLA.PO_LINE_ID

AND PHA.PO_HEADER_ID = 123456

AND PHA.TYPE_LOOKUP_CODE = ‘STANDARD’

AND PDA. REQ_DISTRIBUTION_ID is NULL;

And that the last step is adding the required fields to get information against these Manual POs.

SELECT PLA.LINE_NUM LINE_NUMBER

, PLA.ITEM_DESCRIPTION MATERIAL_DESCRIPTION

       , PLA.UNIT_MEAS_LOOKUP_CODE UOM

                   , PLA.QUANTITY PO_QUANTITY

                   , PDA.QUANTITY_DELIVERED QUANTITY_RECEIVED

                   , PDA.QUANTITY_DELIVERED

                   , PDA.QUANTITY_BILLED QUANTITY_BILLED

                   , PHA.SEGMENT1 PO_NUMBER

       , DECODE (PHA.AUTHORIZATION_STATUS,

               NULL, 'INCOMPLETE',

               UPPER(PHA.AUTHORIZATION_STATUS)

              ) PO_STATUS

              , CASE

  WHEN PLA.QUANTITY = NVL(PDA.QUANTITY_DELIVERED,0) THEN 'DELIVERED'

  WHEN PLA.QUANTITY < NVL(PDA.QUANTITY_DELIVERED,0) THEN 'EXTRA DELIVERED'

  WHEN PLA.QUANTITY > NVL(PDA.QUANTITY_DELIVERED,0) THEN 'NOT DELIVERED'

  ELSE NULL

END MATERIAL_STATUS

                , PLA.ITEM_ID

                , PLA.ATTRIBUTE2

                , PHA.VENDOR_ID

FROM PO_HEADERS_ALL PHA

, PO_LINES_ALL PLA

, PO_DISTRIBUTIONS_ALL PDA

WHERE PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

AND PDA.PO_LINE_ID = PLA.PO_LINE_ID

AND PHA.PO_HEADER_ID = 123456

AND PHA.TYPE_LOOKUP_CODE = ‘STANDARD’

AND PDA. REQ_DISTRIBUTION_ID is NULL;

So, this query gives us the information about manual PO ‘PO-1234’ and if we want to get all Manual POs records, we simply need to remove the check AND PHA.PO_HEADER_ID = 123456.

I hope this post be of some help for you. Feel free to catch me for any related queries. Stay Blessed :)

Thank You,