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,

Requisitions without PO

Hi Friends,

I had mentioned in my last published post that my next post would be about requisitions, against which no Purchase Orders are not entered. So here it is,

Our base here is requisition so we will start with a sample record with MR# 'AB1234' which is SEGMENT1 and 666765 be the REQUISITION_HEADER_ID of PO_REQUISITION_HEADERS_ALL.

We will make sure that there is no PO entered against this requisition by checking through the following query where we check entry in PO_DISTRIBUTIONS_ALL against the said requisition’s distribution records

The only catch here in this case is making sure that we don’t have any entry against the said requisition in Purchase order’s tables. We, will make it sure through the following query

SELECT COUNT(REQ_DISTRIBUTION_ID)

FROM PO_DISTRIBUTIONS_ALL PDA

WHERE REQ_DISTRIBUTION_ID IN (SELECT DISTINCT PRDA.DISTRIBUTION_ID

FROM PO_REQ_DISTRIBUTIONS_ALL PRDA

                                                , PO_REQUISITION_LINES_ALL PRLA

                                      WHERE PRLA.REQUISITION_HEADER_ID = 666765

                                      AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID)

We must get the result of above query as 0 in order to get to the correct sample record.

 Now, to get the requisition details of the sample record, we join all three headers, lines and distribution level tables in the query. Our query will be

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

, PO_ REQUISITION_LINES_ALL PRLA

, PO_REQ_DISTRIBUTIONS_ALL PRDA

WHERE PRHA. REQUISITION_HEADER_ID = 666765

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID

AND PRDA. DISTRIBUTION_ID NOT IN SELECT COUNT(REQ_DISTRIBUTION_ID)

FROM PO_DISTRIBUTIONS_ALL PDA

WHERE REQ_DISTRIBUTION_ID IN (SELECT DISTINCT REQ_DISTRIBUTION_ID

FROM PO_DISTRIBUTIONS_ALL PDA

WHERE REQ_DISTRIBUTION_ID IS NOT NULL);

Now the last step is adding the columns to get the information against requisition without PO.

SELECT PRHA.SEGMENT1 REQUISITION_NUMBER

, PRLA.LINE_NUM,

       PRLA.ITEM_DESCRIPTION MATERIAL_DESCRIPTION,

       PRLA.UNIT_MEAS_LOOKUP_CODE UOM

, PRLA.QUANTITY PR_QUANTITY,

       , PRLA.QUANTITY_RECEIVED QUANTITY_RECEIVED,

       PRLA.QUANTITY_DELIVERED QUANTITY_DELIVERED

, UPPER(PRHA.AUTHORIZATION_STATUS) MR_STATUS

             , CASE

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

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

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

  ELSE NULL

END MATERIAL_STATUS

       , PRLA.DESTINATION_ORGANIZATION_ID

, PRLA.ITEM_ID

       , PRHA.REQUISITION_HEADER_ID

, (SELECT GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7

FROM GL_CODE_COMBINATIONS GCC

WHERE GCC.CODE_COMBINATION_ID = PRDA.CODE_COMBINATION_ID

) BUDGET_NO,

(

SELECT VENDOR_NAME

FROM AP_SUPPLIERS

WHERE VENDOR_ID = PRLA.VENDOR_ID

) SUPPLIER_NAME

FROM PO_REQUISITION_HEADERS_ALL PRHA

, PO_ REQUISITION_LINES_ALL PRLA

, PO_REQ_DISTRIBUTIONS_ALL PRDA

WHERE PRHA. REQUISITION_HEADER_ID = 666765

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID

AND PRDA. DISTRIBUTION_ID NOT IN SELECT COUNT(REQ_DISTRIBUTION_ID)

FROM PO_DISTRIBUTIONS_ALL PDA

WHERE REQ_DISTRIBUTION_ID IN (SELECT DISTINCT REQ_DISTRIBUTION_ID

FROM PO_DISTRIBUTIONS_ALL PDA

WHERE REQ_DISTRIBUTION_ID IS NOT NULL);

So this is all about getting to requisitions without PO, we can get all requisition without PO by removing the check PRHA. REQUISITION_HEADER_ID = 666765.

Feel free to catch me for any related queries. Thank You,

Stay blessed J