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