Requisitions and Purchase Orders

Hi Friends,

Today I am here with some helpful information on requisition and purchase order links and joins and important columns. This post will be divided into three posts

• Number One will be where purchase orders are entered against requisitions.

• Number Two will be where Purchase orders are not entered as yet against requisitions.

• Number Three will be where Purchase orders are entered manuall without requisitions being entered.

Let's start with the first case today. Our base here is requisition so we will start with a sample MR# 'AB1234' which is SEGMENT1 and 666765 be the REQUISITION_HEADER_ID of PO_REQUISITION_HEADERS_ALL so our query will be like

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

WHERE PRHA.SEGMENT1 = 'AB1234';

And now we will add the next table required i.e., PO_REQUISITION_LINES_ALL. Here you should first simply select this table with this requisition header id and note count and make sure that the record count should match with the header table joined.

SELECT COUNT(*)

FROM PO_REQUISITION_LINES_ALL PRLA

WHERE PRLA.REQUISITION_HEADER_ID = 666765;

This count should match with the number of records the query below returns

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

                , PO_REQUISITION_LINES_ALL PRLA

WHERE PRHA.SEGMENT1 = 'AB1234'

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID;

And now we will add the next table required i.e., PO_REQ_DISTRIBUTIONS_ALL

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

                , PO_REQUISITION_LINES_ALL PRLA

                , PO_REQ_DISTRIBUTIONS_ALL PRDA

WHERE 1=1

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

AND PRHA.SEGMENT1 = 'AB1234';

And now we are going to add the next table required i.e., PO_DISTRIBUTIONS_ALL

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

                , PO_REQUISITION_LINES_ALL PRLA

                , PO_REQ_DISTRIBUTIONS_ALL PRDA

                , PO_DISTRIBUTIONS_ALL PDA

WHERE 1=1

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

AND PRHA.SEGMENT1 = 'AB1234';

And then we will add the next table required i.e., PO_LINES_ALL

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

                , PO_REQUISITION_LINES_ALL PRLA

                , PO_REQ_DISTRIBUTIONS_ALL PRDA

                , PO_DISTRIBUTIONS_ALL PDA

                , PO_LINES_ALL PLA

WHERE 1=1

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

AND PRHA.SEGMENT1 = 'AB1234';

And here is the last join for link requisition to purchase order. i.e., with PO_HEADERS_ALL

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

                , PO_REQUISITION_LINES_ALL PRLA

                , PO_REQ_DISTRIBUTIONS_ALL PRDA

                , PO_DISTRIBUTIONS_ALL PDA

                , PO_LINES_ALL PLA

                , PO_HEADERS_ALL PHA

WHERE 1=1

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PRHA.SEGMENT1 = 'AB1234';

Now some checks are to be added to make sure data integrity

SELECT *

FROM PO_REQUISITION_HEADERS_ALL PRHA

                , PO_REQUISITION_LINES_ALL PRLA

                , PO_REQ_DISTRIBUTIONS_ALL PRDA

                , PO_DISTRIBUTIONS_ALL PDA

                , PO_LINES_ALL PLA

                , PO_HEADERS_ALL PHA

WHERE 1=1

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PRHA.SEGMENT1 = 'AB1234'

AND DECODE(PHA.CANCEL_FLAG, NULL, 'N', 'Y','Y','N') = 'N'

AND PHA.TYPE_LOOKUP_CODE = 'STANDARD';

Now we are going to add the important columns from these tables.

SELECT NVL (PRHA.SEGMENT1, -1) PR_NUM

    , PRLA.LINE_NUM PR_LINE_NUM

    , PRLA.ITEM_DESCRIPTION PR_MATERIAL_DESC

    , PRLA.UNIT_MEAS_LOOKUP_CODE PR_UOM

    , PRLA.QUANTITY PR_QTY

    , PLA.QUANTITY PO_QTY

    , PRLA.QUANTITY_RECEIVED PR_QTY_RCVD

    , PRLA.QUANTITY_DELIVERED PR_QTY_DLRVD

    , PDA.QUANTITY_BILLED PO_QTY_BILLED

    , UPPER(PRHA.AUTHORIZATION_STATUS) PR_STATUS,

    (SELECT MAX (DISTINCT PH.SEGMENT1)

       FROM PO_LINES_ALL PL, PO_HEADERS_ALL PH

        WHERE PL.ATTRIBUTE2 = PRHA.SEGMENT1

           AND PL.PO_HEADER_ID = PH.PO_HEADER_ID

           AND PH.TYPE_LOOKUP_CODE = 'RFQ') RFQ_NUM,

       (SELECT MAX (DISTINCT PH.SEGMENT1)

          FROM PO_LINES_ALL PL, PO_HEADERS_ALL PH

         WHERE PL.ATTRIBUTE2 = PRHA.SEGMENT1

           AND PL.PO_HEADER_ID = PH.PO_HEADER_ID

           AND PH.TYPE_LOOKUP_CODE = 'QUOTATION'

           AND QUOTE_WARNING_DELAY = 1) QUOATATION_NUM,

       PHA.SEGMENT1 PO_NUMBER,

       DECODE (PHA.AUTHORIZATION_STATUS,

               NULL, 'INCOMPLETE',

               UPPER(PHA.AUTHORIZATION_STATUS)

              ) PO_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

FROM PO_REQUISITION_HEADERS_ALL PRHA

    , PO_REQUISITION_LINES_ALL PRLA

    , PO_REQ_DISTRIBUTIONS_ALL PRDA

    , PO_DISTRIBUTIONS_ALL PDA

    , PO_LINES_ALL PLA

    , PO_HEADERS_ALL PHA

WHERE 1=1

AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PRHA.SEGMENT1 = 'AB1234'

AND DECODE(PHA.CANCEL_FLAG, NULL, 'N', 'Y','Y','N') = 'N'

AND PHA.TYPE_LOOKUP_CODE = 'STANDARD';

It was a little effort about today's blog. hope to catch you soon. feel free to write to me for any related query. Stay happy :)