R12 – Payments – (Funds Disbursement) Step by Step
Payments is a separate module in R12 and has new features, new look and feel (it is now Java page vs an Oracle form in 11i). The module is called as iPayments and the Application Short name is IBY. All the payments table starts with IBY. In this Article, we will be seeing how to do Payments in R12 in a step by step manner. In the example below, we will see how to do a Check Payment Process.
Responsibility – Payables Manager
Navigation – Entry – Payments Manager
Click on the Payments Manager as shown above. Clicking this will open an OAF page as shown below
Click on Submit Single Payment Process Request as shown above. This opens a Payments process window as shown below.
Enter the mandatory columns in the page above.
Payment Process Request Name – Enter a valid payment batch name
Click on the Payment Attributes Tab.
Enter the Mandatory columns under this tab as shown below
Click on Processing tab.
Check the check box – Stop Process for Review After Scheduled Payment Selection. Checking this check box pauses the Payment Process , so as to help reviewing the Selected Invoices for Payments.
Click on Submit.
Once the Submit button is clicked, it brings to the window as shown above.
Here enter the Payment batch name in Payment process Request column and click Go to find the status.
In the below screenshot, please fiind the Payment Process Request Status as New . Click on the Refresh Status button to view the Status changing as it progress.
The next status is Invoices Pending Review
In the Main window – click on View – Requests and click on Find Request to view the Request just submitted.
Coming back to the OAF page…
Click on the Start Action Icon as shown below to review the Selected Invoices for Payment.
Here its lists out all the Invoices selected for payment
Under the Select menu, there is a check box against each Invoice.
In this stage, we can choose which Invoices needs to be paid and also we can edit the Payment Amount.
After the review is done, check the Check box under Select for the Invoices which needs to be paid as shown below.
In the screenshot below, we can see the choices of Action which can be done..
Click on Submit button as shown below.
On the screenshot above, see the status as Formatting.
Click on the Show button to expand.
The window expands as shown below
The status shows as Formatted – Ready for Printing.
Click on the Take Action Icon as shown below
Choose a printer by clicking on the Find button or Enter a valid printer name
Click on Print as shown below
Clicking on Print , submits the Format Payment Instructsions process as shown below
And pops up the below screen.
On the below screen click on the number under Need Action as shown below
See the status of the Payment as Submitted for Printing. Click on Take Action Icon as shown above
Review the payment details in the screenshot above. Click on Record Print Status as shown above
Click on Continue button as shown above.
There will be a warning as shown below..
|
Are you sure you want to confirm the print status as final? This action cannot be reversed. You should not proceed with this action unless you are sure that all documents with the status of Printed were successfully printed. The payments will be marked as complete and the payment documents will be recorded as issued. If you complete this action and discover printing problems, you will need to void the payment and select the document to be paid in a new payment process.
Click on Apply as shown below
Click on the Refresh button to see the status
Refreshing the window, shows the Status as Confirmed Payment
Refer to the article Payment Process Request – Status / Stages with Description, for different stages/status messages which we face during the Payment process and to the article R12 – Queries to Troubleshoot Payments , for troubleshooting the Payment from backend or to follow-up with the tables underlying in the Payment Process.
========================================================================
========================================================================
--Internal Bank Accounts
Payment Process Request - FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE
Technical details Payment Process Request (PPR) processing in Payments in R12
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER"
FROM HZ_PARTIES HZP
, AP_SUPPLIERS APS
, HZ_PARTY_SITES SITE_SUPP
, AP_SUPPLIER_SITES_ALL ASS
, IBY_EXTERNAL_PAYEES_ALL IEP
, IBY_PMT_INSTR_USES_ALL IPI
, IBY_EXT_BANK_ACCOUNTS IEB
, HZ_PARTIES HZPBANK
, HZ_PARTIES HZPBRANCH
, HZ_ORGANIZATION_PROFILES HOPBANK
, HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3
========================================================================
========================================================================
This Doc Describes the PPR Setup Step by Step table changes, Functional Steps, PPR Status Changes and Packages used to add/update PPR Instruction and Extract XML Data.
Table Changes:
--Internal Bank Accounts
SELECT * FROM CE_BANK_ACCOUNTS;
--Payment Documents to be used for Printed type Payments
SELECT * FROM CE_PAYMENT_DOCUMENTS;
SELECT * FROM AP_CHECK_FORMATS;
-- Payable Formats
SELECT * FROM IBY_FORMATS_B;
--Payable Profiles
SELECT * FROM IBY_SYS_PMT_PROFILES_B
SELECT * FROM IBY_EXTRACTS_TL;
-- Payment Methods
SELECT * FROM IBY_PAYMENT_METHODS_B;
-- stores the criteria that a payment batch uses to select invoices for payment
SELECT * FROM AP_INV_SELECTION_CRITERIA_ALL;
/*AP_SELECTED_INVOICES_ALL is a temporary table that stores
information about invoices selected for payment in a
payment batch. Your Oracle Payables application
inserts into this table after you initiate a payment batch.
There will be one row for each invoice that Payables selects for
payment in the current payment batch. When you build payments
in a payment batch, your Oracle Payables
application uses information in this table to create
rows in AP_SELECTED_INVOICE_CHECKS.*/
SELECT * FROM AP_SELECTED_INVOICES_ALL
SELECT * FROM AP_SELECTED_INVOICE_CHECKS_ALL
SELECT * FROM AP_UNSELECTED_INVOICES_ALL
-- This table containes locked invoices details to prevent other check runs from selecting the same invoices
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL
--This table contains transaction details, document details, payer, payee, etc
SELECT * FROM IBY_DOCS_PAYABLE_ALL;
SELECT * FROM IBY_PAYMENTS_ALL;
SELECT * FROM AP_INVOICE_PAYMENTS_ALL;
-- XML documents associated with a particular transaction generated by FORMAT PAYMENT INSTRUCTION Program
SELECT * FROM IBY_TRXN_DOCUMENTS;
-- Stores PPR instructions
SELECT * FROM IBY_PAYMENT_INSTRUCTIONS_ALL
--Payables Payments Data
SELECT * FROM AP_CHECKS_ALL;
Functional Steps:
Following steps are needed to create a New Payment Format in Oracle Payables R12 and associate it with Payment Document.
Step A. Create XML Template
Payables Manager > Setup > Payment > Payment Administrator > Formats > XML Publisher Format Templates
Click on 'Create Template'
Enter the Following values:
Name:
Code:
Application: Payments
Data Definition: Oracle Payments Funds Disbursement Payment Process Request Extract 1.0
Type: RTF
Default Output Type: PDF
Description:
File: Click on Browse and then upload the rtf file from your local machine.
Language: English
Territory: United States
Click on Apply.
Step B. Create Payment Format
Payables Manager > Setup > Payment > Payment Administrator > Formats > Formats
Select Type: Disbursement Payment Instruction
Click on 'Create'
Enter the Following values:
Code:
Name:
Data Extract: Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
XML Publisher Template: XML Template Name created in Step A
Click on Apply
Step C. Create Payment Document
Payables Manager > Setup > Payment > Bank Accounts
Query up your Bank Name. It is assumed that your Bank has already been setup in AP.
Click on 'GO'
Select the Bank Account and click on Manage Payment Documents.
Click on 'Create'
Enter the Following Values:
Name:
Paper Stock Type: Blank Stock
Number of Lines per Remittance Stub:
Format: Payment format Name created in Step B
Payment Document Category: CHECK PAY
First Available Document Number:
Last Available Document Number:
Enter any other information that you may need.
Click on Apply
Step D. Create Payment Process Profile
Payables Manager > Setup > Payment > Payment Administrator > Payment Process Profiles
Click on 'Create'
Enter the Following Values:
Code:
Name:
Payment Instruction Format: Payment format Name created in Step B
Processing Type:
Payment Completion Point:
Default Payment Document: Payment Document Name created in Step C
Payment File:
Default Printer:
Enter any other information that you may need.
Click Apply
Step E. Payment Process Request Templates
Payables Manager > Payments > Entry > Payments Manager
Click on Templates
Click on Create
Enter all valid information in the template that you have created in the Installation steps.
PPR Status Changes:
NEW
This status indicates that the PPR has been successfully submitted for processing, and the AutoSelect program is digesting the criteria provided by the user on the header of the PPR in preparation of the automatic selection the invoices and memos related to that criteria.
SELECTING INVOICES
This status indicates that the AutoSelect program is selecting the eligible invoices/memos for the payment batch based on Due Date, Discount Date, Pay Group, and other criteria provided by the user on the header of the PPR
CANCELLED - NO INVOICES SELECTED
If no invoices or memos met the selection criteria provided by the user on the header of the PPR, the PPR is automatically terminated and the status changes to this status.
MISSING..." STATUSES
Other statuses may appear at this point in the process if the user failed to included required information on the PPR header, such as "Missing Exchange Rates", etc.
INVOICES SELECTED
After selecting the documents (invoices/memos), they are locked to prevent other checkruns from selecting the same documents
INVOICES PENDING REVIEW
This status will only appear if you selected the "Stop Process for Review After Scheduled Payment Selection" option on the Processing tab of the PPR header. This status means that the PPR process has stopped, and is waiting for you to review the invoices and memos that were selected for payment (and make any changes to the batch, as needed). Click on the Take Action icon to be taken to the Review Proposed Payments window
CALCULATING SPECIAL AMOUNTS
This status will only appear if you selected the "Calculate Payment Withholding and Interest During the Scheduled Payment Selection" option on the Processing tab of the PPR header. This status means that interest and withholding tax are being calculated and applied, as necessary, to the invoices and memos selected for this payment batch
ASSEMBLING/ASSEMBLED PAYMENTS
An "interim" status, it appears after the calculation for interest and withholding has been completed, and the Build Payments program is starting. It may appear again later after the user provides any required bank account and PPP information for the invoices/memos ("documents") selected
INFORMATION REQUIRED - PENDING ACTION
This status appears if you did not provide a default Internal (Disbursement) Bank Account and/or PPP on the header of the PPR. In that case, you need to click on the Take Action icon to be taken to a form where you can decide which internal bank account and PPP should be used for each invoice and memo selected for payment
PENDING PROPOSED PAYMENT REVIEW
This status will only appear if you selected the "Stop Process for Review After Creation of Proposed Payments" option on the Processing tab of the PPR header. In this case, the system is waiting for you to review (and modify, if needed) the proposed payments for this batch. Click on the Take Action icon to be taken to the "Review Proposed Payments" window
FORMATTING
This status indicates that the proposed payments have been turned into payment instruction files. At this point, you will want to click on the Show link to view the new associated payment instruction file(s). Each payment instruction file with have their own PI Reference Number. If you have both electronic and paper ("check") payments involved in this payment batch, you will see a payment instruction file for each type of payment method
CONFIRMED PAYMENT
Once the payment instructions have been transmitted/printed and confirmed, the Status of the PPR changes to this status to indicate a successfully completed payment batch (PPR)
TERMINATED
If the user terminates a PPR anytime prior to confirmation of the payments (using the Terminate icon), the status will change to "Terminated", and the PPR is permanently closed
Packages used to add/update PPR Instruction:
Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments.
The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively.
These files are located in the $IBY_TOP/patch/115/sql directory.
The package allows custom elements to be created at five levels within the payment XML extract.
You cannot customize the package specification, but the package body contains stubbed functions that you can customize.
The five functions are as follows:
Instruction - FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at instruction level and run only once for the instruction.
<OutboundPaymentInstruction> Tag
Payment - FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at payment level and run once for each payment in the instruction.
<OutboundPayment> Tag
Document Payable - FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.
<DocumentPayable> Tag
Document Payable Line - FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.
<DocumentPayableLine> Tag.
Payment Process Request - FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each payment process request.
<PaymentProcessProfile> Tag
Extract XML Data of Format Payment Instructions:
R12: Understanding XML Payment Templates and Formats (Doc ID 562806.1)
For getting the XML Data of Payment Format Instruction
1. Go into the responsibility: Oracle Payment Setup
2. Navigate to: Formats > click on the Go To Task icon > query the format payment you are using
3. Click on the Update icon
4. Assign the XML Publisher Template "Extract Identity", and save your change and return to the Oracle Payment Setups menu.
5. Click on the Go To Task icon on the XML Publisher Format Templates row > query up the Extract Identity template.
6. Verify that the template has the file "IBY_IDENTITY_en.xsl" assigned. If not,
please contact support in order to get this file (see Note: 457411.1).
7. Run a Payment Process Request (PPR) with a Payment Process Profile (PPP) assigned that uses the Format from #1 through #4 above. The output should now be XML format, and can be viewed using a browser.
8. Migrate the solution as appropriate to other environments.
The Extract Identity Template Has No File Attached (Doc ID 457411.1)
Load the Extract Identity Template with the following command
java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME IBY \
-LOB_CODE IBY_IDENTITY \
-XDO_FILE_TYPE XSL-XML \
-LANGUAGE en \
-TERRITORY 00 \
-FILE_NAME $IBY_TOP/patch/115/publisher/templates/IBY_IDENTITY_en.xsl
R12 Oracle Payments Processing 'How To' documents (Doc ID 579132.1)
=====================================================================
=====================================================================
Technical details Payment Process Request (PPR) processing in Payments in R12
Technical details Payment Process Request (PPR) processing in Payments in R12
Overview
Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.
There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments
Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.
Submitting a Single Payment Process Request
Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.
Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.
Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).
Click on Submit to submit the Payment process request.
Document Selection – Payables
Code: AP_AUTOSELECT_PKG
When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.
Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.
Selection:
Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product
The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.
Locking:
After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents.
Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”
If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.
Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates
If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.
Build Payments - Payments
Code: IBY_DISBURSE_SUBMIT_PUB_PKG
Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
Primary Key: PAYMENT_SEVICE_REQUEST_ID
Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG
Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.
Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED
The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.
Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number
a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.
If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.
b) Document Validation
Code: IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..
b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.
b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.
The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
REQUEST - Reject all documents in this PPR
DOCUMENT - Reject only the document in error
PAYEE - Reject all the documents related to the supplier
NONE - Stop the request for review
b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c) Create Payments
Code: IBY_PAYGROUP_PUB
The validated documents are then grouped into proposed payments based on the grouping rules, both user defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.
Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.
The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
IBY_PAYMENTS_ALL links through payment_service_request_id.
Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date
The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.
Format Payments - Payments
Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB
When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id
Key Columns in IBY_PAYMENT_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments - Payables
Code: AP_PMT_CALLOUT_PKG
Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL
Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.
There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments
Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.
Submitting a Single Payment Process Request
Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.
Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.
Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).
Click on Submit to submit the Payment process request.
Document Selection – Payables
Code: AP_AUTOSELECT_PKG
When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.
Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.
Selection:
Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product
The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.
Locking:
After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents.
Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”
If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.
Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates
If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.
Build Payments - Payments
Code: IBY_DISBURSE_SUBMIT_PUB_PKG
Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
Primary Key: PAYMENT_SEVICE_REQUEST_ID
Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG
Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.
Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED
The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.
Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number
a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.
If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.
b) Document Validation
Code: IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..
b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.
b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.
The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
REQUEST - Reject all documents in this PPR
DOCUMENT - Reject only the document in error
PAYEE - Reject all the documents related to the supplier
NONE - Stop the request for review
b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c) Create Payments
Code: IBY_PAYGROUP_PUB
The validated documents are then grouped into proposed payments based on the grouping rules, both user defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.
Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.
The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
IBY_PAYMENTS_ALL links through payment_service_request_id.
Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date
The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.
Format Payments - Payments
Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB
When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id
Key Columns in IBY_PAYMENT_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments - Payables
Code: AP_PMT_CALLOUT_PKG
Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL
===================================================
===================================================
PAYMENT PROCESSING REQUEST FUNCTIONALITY-
In 11i we used Payment batches to pay for multiple invoices same time. In R12, PPR is the replacement of Payment batches. R12 PPR process enables payment Administrator to select multiple invoices for payment by selection criteria and he can pause the invoice selection and payment build process. During the invoice selection review, payment manager can review the invoice selected; if the invoices were validated or approved and hence did not get included in the payment process request. He can add or remove the invoices in the Payment process and also can check the cash requirements for the full payment. Payment manager can also dismiss the individual documents or payments if necessary, and restart the payment build process.
Steps in Pay run Process-
Managing a Pay run involves 3 main processes
- Selection of the invoices for payment
- Grouping the invoices into payments
- Building the payment instruction files to either print checks or send instructions to bank.
There are four steps in the processing of PPR:-
- Document selection – Handled by Payables(AP)
- Build Payments – Handled by Payments(IBY)
- Format Payments – Handled by Payments(IBY)
- Confirm Payments – Handled by Payables(AP)
Submitting a Single Payment Process Request
Mandatory fields – Payment Process Request name, pay through date, Payment date, and Exchange rate type.
Under Processing tab, options are available to stop the process after document selection/payment and also how to create the payment instructions:
- Maximize Credits.
- Stop Process for review after scheduled payment selection.
- Calculate payment withholding and interest during scheduled payment selection.
- Stop process for review after creation of proposed payments.
Click on submit to submit the Payment process request.
Document Selection – Payables
This process calls AP_AUTOSELECT_PKG.
When a payment process request is submitted, a record is inserted in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name i.e payment process request name. Invoices are then selected based on the due date, discount date, paygroup, and other criteria provided by the user while submitting the PPR.
The AP_SELECTED_INVOICES_ALL table is populated with the selected invoices and AP_UNSELECTED_INVOICES_ALL table by the unselected invoices.
Note: After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
If the PPR has been setup to ‘Stop Process for Review after Scheduled Payment Selection’, the process stops for user review.
Then the status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review after Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled – No Invoices Selected”. Then void all invoices
For others, the actions available are
a) Terminate the PPR
For others, the actions available are
a) Terminate the PPR
b) Modify / proceed to submit the PPR and start the build process.
Build Payments – Payments
Call IBY_DISBURSE_SUBMIT_PUB_PKG
Build payment creates records in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
A payment process request is a group of documents payable that a source product submits to Oracle Payments for payment service processing. This table contains the parameters like Calling application identifier, Internal bank account, Allow zero payments flag, etc. selected in the Payment Process Request.
| PAYMENT_SERVICE_REQUEST_ID | NUMBER | System generated primary key |
| CALLING_APP_ID | NUMBER | Source product Identifier |
| CALL_APP_PAY_SERVICE_REQ_CODE | VARCHAR2 | Source product’s payment process request Identifier. Since the source product’s Identifiers may be alphanumeric, even numeric document Identifiers are stored as VARCHAR2. |
| PAYMENT_SERVICE_REQUEST_STATUS | VARCHAR2 | Payment process request status. Values from the lookup IBY_REQUEST_STATUSES include PAYMENTS_CREATED. |
| PROCESS_TYPE | VARCHAR2 | Specifies the process by which documents payable are built into payments and payments into payment instructions. Values from the lookup IBY_PROCESS_TYPES include STANDARD, IMMEDIATE, and MANUAL. |
| ALLOW_ZERO_PAYMENTS_FLAG | VARCHAR2 | Y or N flag that indicates whether zero payments are allowed for this payment request. If set to N, any zero value payments created for this payment request is failed. |
| INTERNAL_BANK_ACCOUNT_ID | NUMBER | Internal bank account identifier |
| MAXIMUM_PAYMENT_AMOUNT | NUMBER | Maximum payment amount used to override default maximum payment amount |
| MINIMUM_PAYMENT_AMOUNT | NUMBER | Minimum payment amount used to override default minimum payment amount |
Note: The displayed status of the PPR is generated by ibyvutlb.pls
Following are the possible values of PAYMENT_SERVICE_REQUEST_STATUS column-
- DOCUMENTS_VALIDATED
- INFORMATION_REQUIRED
- INSERTED
- PAYMENTS_CREATED
- PENDING_REVIEW
- TERMINATED
- VALIDATION_FAILED
- COMPLETED
In 11i AP_SELECTED_INVOICE_CHECKS_ALL table is populated by the Build Payment process.
The Build Program also populates IBY_DOCS_PAYABLE_ALL table
IBY_DOCS_PAYABLE_ALL- This table contains the documents payable which are updated by system while processing “Build Payments” program. A document payable is a supplier invoice or similar document that needs to be paid. In addition, this table contains whatever document information is necessary for payment processing.
This table contains transaction details, document details, payer, payee, etc.”
This table contains transaction details, document details, payer, payee, etc.”
| Name | Datatype | Comments |
| PAY_PROC_TRXN_TYPE_CODE | VARCHAR2 | Type of payment processing transaction or document |
| CALLING_APP_ID | NUMBER | Calling product Identifier |
| CALLING_APP_DOC_REF_NUMBER | VARCHAR2 | Reference number entered by user of the source product. Need not be unique |
| DOCUMENT_PAYABLE_ID | NUMBER | Oracle Payments’ unique internal document payable Identifier |
| PAYMENT_FUNCTION | VARCHAR2 | Function or purpose of the payment. Values from the lookup IBY_PAYMENT_FUNCTIONS include SUPPLIER_PAYMENT, CUSTOMER_REFUNDS, and others. |
| PAYMENT_DATE | DATE | Payment date |
| DOCUMENT_DATE | DATE | Date of document |
| DOCUMENT_TYPE | VARCHAR2 | Type of document payable. Values from the IBY_DOCUMENT_TYPES lookup include INVOICE. |
| DOCUMENT_STATUS | VARCHAR2 | Document status. Values from the lookup IBY_DOCS_PAYABLE_STATUSES include PAYMENT CREATED. |
| DOCUMENT_CURRENCY_CODE | VARCHAR2 | Document currency code |
| DOCUMENT_AMOUNT | NUMBER | Total amount in document currency |
| PAYMENT_CURRENCY_CODE | VARCHAR2 | Payment currency code |
| PAYMENT_AMOUNT | NUMBER | Amount to be paid in payment currency |
| PAYMENT_SERVICE_REQUEST_ID | NUMBER | Identifier of the payment process request in which this document was submitted |
| PAYMENT_METHOD_CODE | VARCHAR2 | Payment method Identifier |
| EXCLUSIVE_PAYMENT_FLAG | VARCHAR2 | Y or N flag indicating whether this document payable should not be grouped with any other documents payable. |
| CALLING_APP_DOC_UNIQUE_REF1 | VARCHAR2 | Source product’s first unique document payable Identifier |
| CALLING_APP_DOC_UNIQUE_REF2 | VARCHAR2 | Source product’s second unique document payable Identifier (Invoice_id) |
| CALLING_APP_DOC_UNIQUE_REF3 | VARCHAR2 | Source product’s third unique document payable Identifier(Payment_number) |
| CALLING_APP_DOC_UNIQUE_REF4 | VARCHAR2 | Source product’s fourth unique document payable Identifier |
| CALLING_APP_DOC_UNIQUE_REF5 | VARCHAR2 | Source product’s fifth unique document payable Identifier |
A. Internal Bank Account/Payment Process Profile Assignment:
Call IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required – Pending Action”
User should complete the missing information and Run Payment Process to continue.
User should complete the missing information and Run Payment Process to continue.
B. Document Validation
Call IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations.b.1 – If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.TheDOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failureb.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
Call IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations.b.1 – If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.TheDOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failureb.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c. Create Payments
Call IBY_PAYGROUP_PUBThe validated documents are then grouped into proposed payments based on the grouping rules, both users defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, it is paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.The build program then updates theIBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
Call IBY_PAYGROUP_PUBThe validated documents are then grouped into proposed payments based on the grouping rules, both users defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, it is paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.The build program then updates theIBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
IBY_PAYMENTS_ALL
This table contains all the payments created by system while processing “Build Payments”. A Payment can be single check or an electronic fund transfer between first party payer and third party payee. A row in this table corresponds to one or more documents payable. Payments are built by grouping documents payable according to Oracle Payments’ grouping rules.
This table also stores information of payments at grouping level. The groups can be Single, Mixed and grouped as defined in Payment Process Profile for the purpose of SEPA.
The payment details are displayed on the Payments tab of the Funds Disbursement Process Home page.
This table also stores information of payments at grouping level. The groups can be Single, Mixed and grouped as defined in Payment Process Profile for the purpose of SEPA.
The payment details are displayed on the Payments tab of the Funds Disbursement Process Home page.
| Name | Datatype | Comments |
| PAYMENT_ID | NUMBER | Unique internal Identifier for this record. Generated using a database sequence. |
| PAYMENT_METHOD_CODE | VARCHAR2 | Payment method used for making the payments. |
| PAYMENT_SERVICE_REQUEST_ID | NUMBER | Payment service request Id and it is the foreign key to the table iby_pay_service_requests. |
| PROCESS_TYPE | VARCHAR2 | Specifies the process by which the payment is built into a payment instruction. Values, from the lookup IBY_PROCESS_TYPES, include STANDARD, IMMEDIATE, and MANUAL. |
| PAYMENT_STATUS | VARCHAR2 | The status of the Payment. Values are derived from the lookup IBY_PAYMENT_STATUSES. The possible values are CREATED, FORMATTED, TRANSMITTED, VOID_BY_OVERFLOW, REJECTED, FORMATTED, VOID, etc. |
| PAYMENTS_COMPLETE_FLAG | VARCHAR2 | Y or N flag that indicates if the payment is complete |
| PAYMENT_FUNCTION | VARCHAR2 | Function or purpose of the payment. Values from the lookup IBY_PAYMENT_FUNCTIONS include SUPPLIER_PAYMENT, CUSTOMER_REFUNDS, and others. |
| PAYMENT_AMOUNT | NUMBER | Amount of the payment |
| PAYMENT_CURRENCY_CODE | VARCHAR2 | Currency of the payment |
| BILL_PAYABLE_FLAG | VARCHAR2 | Y or N flag indicating whether a payment is a bill payable, that is, a future dated payment |
| EXCLUSIVE_PAYMENT_FLAG | VARCHAR2 | Y or N flag indicating whether this payment is made up of a single document payable that was meant to be paid alone |
| SEPARATE_REMIT_ADVICE_REQ_FLAG | VARCHAR2 | Y or N flag indicating whether a separate remittance advice needs to be generated for a payment. |
| INTERNAL_BANK_ACCOUNT_ID | NUMBER | Internal bank account id used for making the payment. |
| ORG_ID | NUMBER | Unique internal identifier of the Operating Unit. Validated against HR_OPERATING_UNITS.ORGANIZATION_ID. |
| ORG_TYPE | VARCHAR2 | Organization type. Values, from the lookup IBY_ORGANIZATION_TYPES Include Operating Unit, Business Group, and Legal Entity |
| LEGAL_ENTITY_ID | NUMBER | Legal entity identifier |
The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.
Format Payments – Payments
Call IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB
When a PPR is submitted, there are two options
When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
The table IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id
The link in this case will be through iby_payments_all.payment_instruction_id
Key Columns of IBY_PAYMENT_INSTRUCTIONS_ALL table
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments – Payables
Call AP_PMT_CALLOUT_PKG
Record Print Status of the checks to confirm the payments. Oracle Payments callsap_pmt_callout_pkg.payment_completed to confirm the payments.
Record Print Status of the checks to confirm the payments. Oracle Payments callsap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is updated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL
Also http://oracleapps88.blogspot.in/2013/01/oracle-account-payables-list.html
Also http://oracleapps88.blogspot.in/2013/01/oracle-account-payables-list.html
====================================================
Supplier===Bank-Bank Branch-Account Details
====================================================
SELECT HZP.PARTY_NAME "VENDOR NAME", APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER"
FROM HZ_PARTIES HZP
, AP_SUPPLIERS APS
, HZ_PARTY_SITES SITE_SUPP
, AP_SUPPLIER_SITES_ALL ASS
, IBY_EXTERNAL_PAYEES_ALL IEP
, IBY_PMT_INSTR_USES_ALL IPI
, IBY_EXT_BANK_ACCOUNTS IEB
, HZ_PARTIES HZPBANK
, HZ_PARTIES HZPBRANCH
, HZ_ORGANIZATION_PROFILES HOPBANK
, HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3
No comments:
Post a Comment