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

Payments in R12

Hey friends,

I recently had a query to work on payment details. I had to find out the batch payments with corresponding supplier and bank details. While working on it, I planned to share the journey to develop this query.

What I did, I went to payments screen, inquire a batch and from there I identified a batch with the name ‘Sample Batch Nov-14’.

When I had to find its corresponding entry in database, I used the query

SELECT *

FROM AP_CHECKS_ALL

WHERE CHECKRUN_NAME = ‘Sample Batch Nov-14’;

Here, I got the database record. I was interested in CHECK_ID here, to get all of its payments. So, once again I referred to the same query, but this time I copied its CHECK_ID into a variable V_CHECK_ID

SELECT CHECK_ID

INTO V_CHECK_ID

FROM AP_CHECKS_ALL

WHERE CHECKRUN_NAME = ‘Sample Batch Nov-14’;

Now, the next step was to get to the payments against this check/batch. I moved to AP_INVOICE_PAYMENTS_ALL and used the query

SELECT *

FROM AP_INVOICE_PAYMENTS_ALL

WHERE CHECK_ID = V_CHECK_ID;        

This query brought all the payments against the identified batch.

In addition to getting the payments, I was supposed to display supplier information corresponding to payment. I got all the supplier related information from the batch level, AP_CHECKS_ALL through the fields VENDOR_NAME, BANK_ACCOUNT_NUM, IBAN_NUMBER, BANK_NUM, BANK_ACCOUNT_NAME.

So my query turned something like

SELECT ACA.CHECKRUN_NAME

                , ACA.CHECK_DATE

                , AIPA.AMOUNT PAYMENT_AMOUNT

                , ACA.CURRENCY_CODE

                , ACA.PAYMENT_METHOD_CODE

                , ACA.VENDOR_NAME

                , ACA.BANK_ACCOUNT_NUM VENDOR_BANK_ACC_NO

                , ACA.IBAN_NUMBER VENDOR_BANK_IBAN

                , ACA.BANK_ACCOUNT_NAME VENDOR_BANK_NAME

                , ACA.BANK_NUM SYS_DEF_NUM_AGAINST_VENDOR_BANK

FROM AP_CHECKS_ALL ACA

, AP_INVOICE_PAYMENTS_ALL AIPA

WHERE AIPA.CHECK_ID = ACA.CHECK_ID

AND ACA.CHECK_ID = V_CHECK_ID;

Now, I was missing only the company’s bank part, from which the payment was to be made. For that I had to involve cash management module tables CE_BANK_ACCT_USES_ALL and CE_BANK_ACCOUNTS. We have this field CE_BANK_ACCT_USE_ID in batch table that links to CE_BANK_ACCT_USES_ALL.

So, this time after addition of CE_BANK_ACCT_USES_ALL table, my query turns into

SELECT ACA.CHECKRUN_NAME

                , ACA.CHECK_DATE

                , AIPA.AMOUNT PAYMENT_AMOUNT

                , ACA.CURRENCY_CODE

                , ACA.PAYMENT_METHOD_CODE

                , ACA.VENDOR_NAME

                , ACA.BANK_ACCOUNT_NUM VENDOR_BANK_ACC_NO

                , ACA.IBAN_NUMBER VENDOR_BANK_IBAN

                , ACA.BANK_ACCOUNT_NAME VENDOR_BANK_NAME

                , ACA.BANK_NUM SYS_DEF_NUM_AGAINST_VENDOR_BANK

FROM AP_CHECKS_ALL ACA

, AP_INVOICE_PAYMENTS_ALL AIPA

, CE_BANK_ACCT_USES_ALL CBAUA

WHERE AIPA.CHECK_ID = ACA.CHECK_ID

AND CBAUA.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID

AND ACA.CHECK_ID = V_CHECK_ID;

The table CE_BANK_ACCT_USES_ALL is an intermediate table here to let us join to CE_BANK_ACCOUNTS, from where we will get internal bank account’s information through the columns LIKE BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, IBAN_NUMBER, and CURRENCY_CODE. And my final query will turn into something like below

SELECT ACA.CHECKRUN_NAME

                , ACA.CHECK_DATE

                , AIPA.AMOUNT PAYMENT_AMOUNT

                , ACA.CURRENCY_CODE

                , ACA.PAYMENT_METHOD_CODE

                , ACA.VENDOR_NAME

                , ACA.BANK_ACCOUNT_NUM VENDOR_BANK_ACC_NO

                , ACA.IBAN_NUMBER VENDOR_BANK_IBAN

                , ACA.BANK_ACCOUNT_NAME VENDOR_BANK_NAME

                , ACA.BANK_NUM SYS_DEF_NUM_AGAINST_VENDOR_BANK

                , CBA.BANK_ACCOUNT_NAME INTERNAL_BANK_NAME

                , CBA.BANK_ACCOUNT_NUM INTERNAL_BANK_ACCT_NO

                , CBA.IBAN_NUMBER INTERNAL_BANK_IBAN

                , CBA.CURRENCY_CODE INTERNAL_BANK_ACC_CURRENCY

FROM AP_CHECKS_ALL ACA

, AP_INVOICE_PAYMENTS_ALL AIPA

, CE_BANK_ACCT_USES_ALL CBAUA

, CE_BANK_ACCOUNTS CBA

WHERE AIPA.CHECK_ID = ACA.CHECK_ID

AND CBAUA.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID

AND CBA.BANK_ACCOUNT_ID = CBAUA.BANK_ACCOUNT_ID

AND ACA.CHECK_ID = V_CHECK_ID;

So this was my little journey to find out payments with bank details. Thank you for accompanying me :)

Happy Learning :)