This document outlines the specifications and the methodology for using the Prepayments API.
Use the Prepayments API to:
Generate a unique payment grouping identifier (payment_set_id)
Create a prepayment receipt flagged with this payment_set_id
Apply the prepayment receipt to a receivable activity of type Prepayment
You can access this API:
As standard PL/SQL server-side routine calls
Through forms, utilizing the capability of Forms6 to have a procedure as its underlying base table
The Prepayments API addresses the following business needs:
Enables the creation of a receipt in advance of the invoicing event
Provides a mechanism of matching a prepayment receipt to a prepaid invoice
The Prepayments API lets you model down payments, deposits, or prepayments as receipts created in Oracle Receivables in advance of the invoice creation event.
It is not intended for the purpose of creating receipts for existing invoices, simply before the invoices.
This section describes how to use the Prepayments API to:
Create a prepayment receipt
Apply the prepayment receipt to the prepayment activity
Calculate the amount of all the installments of a particular payment term
To create, apply, and refund a prepayment receipt, you can call the following PL/SQL routine:
AR_PREPAYMENTS_PUB.Create_Prepayment: Use this routine to create a prepayment receipt.
AR_PREPAYMENTS_PUB.Get_Installment: Use this routine to calculate the amount of all installments of a given payment term.
This routine is called to create a prepayment receipt.
This API routine has 5 output, 8 input-output, and 56 input parameters. Of the output parameters, the API returns 5.
Standard API parameters: 4
Prepayment parameters: 48 + 8 (INOUT) parameters
4 (global descriptive flexfield parameters)
Standard API parameters: 3
Prepayment parameters: 2 + 8 (INOUT) parameters
The input descriptive flexfield parameter is a record of type attribute_rec_type.
TYPE attribute_rec_type IS RECORD(
attribute_category VARCHAR2(30) DEFAULT NULL,
attribute1 VARCHAR2(150) DEFAULT NULL,
attribute2 VARCHAR2(150) DEFAULT NULL,
attribute3 VARCHAR2(150) DEFAULT NULL,
attribute4 VARCHAR2(150) DEFAULT NULL,
attribute5 VARCHAR2(150) DEFAULT NULL,
attribute6 VARCHAR2(150) DEFAULT NULL,
attribute7 VARCHAR2(150) DEFAULT NULL,
attribute8 VARCHAR2(150) DEFAULT NULL,
attribute9 VARCHAR2(150) DEFAULT NULL,
attribute10 VARCHAR2(150) DEFAULT NULL,
attribute11 VARCHAR2(150) DEFAULT NULL,
attribute12 VARCHAR2(150) DEFAULT NULL,
attribute13 VARCHAR2(150) DEFAULT NULL,
attribute14 VARCHAR2(150) DEFAULT NULL,
attribute15 VARCHAR2(150) DEFAULT NULL);
The input global descriptive flexfield parameter is a record of type global_attr_rec_type.
TYPE global_attribute_rec_type IS RECORD(
global_attribute_category VARCHAR2(30) default null,
global_attribute1 VARCHAR2(150) default NULL,
global_attribute2 VARCHAR2(150) DEFAULT NULL,
global_attribute3 VARCHAR2(150) DEFAULT NULL,
global_attribute4 VARCHAR2(150) DEFAULT NULL,
global_attribute5 VARCHAR2(150) DEFAULT NULL,
global_attribute6 VARCHAR2(150) DEFAULT NULL,
global_attribute7 VARCHAR2(150) DEFAULT NULL,
global_attribute8 VARCHAR2(150) DEFAULT NULL,
global_attribute9 VARCHAR2(150) DEFAULT NULL,
global_attribute10 VARCHAR2(150) DEFAULT NULL,
global_attribute11 VARCHAR2(150) DEFAULT NULL,
global_attribute12 VARCHAR2(150) DEFAULT NULL,
global_attribute13 VARCHAR2(150) DEFAULT NULL,
global_attribute14 VARCHAR2(150) DEFAULT NULL,
global_attribute15 VARCHAR2(150) DEFAULT NULL,
global_attribute16 VARCHAR2(150) DEFAULT NULL,
global_attribute17 VARCHAR2(150) DEFAULT NULL,
global_attribute18 VARCHAR2(150) DEFAULT NULL,
global_attribute19 VARCHAR2(150) DEFAULT NULL,
global_attribute20 VARCHAR2(150) DEFAULT NULL);
The following table lists the parameters that pertain specifically to the Create Prepayment routine:
| Parameter | Type | Mandatory/Optional | Data-type | Default Value | Description |
|---|---|---|---|---|---|
| p_api_version | IN | M | NUMBER | Constant 1.0 | |
| p_init_msg_list | IN | O | VARCHAR2 | Default FND_API.G_FALSE | |
| p_commit | IN | O | VARCHAR2 | Default FND_API.G_FALSE | |
| p_validation_level | IN | O | NUMBER | Default FND_API.G_VALID_LEVEL_FULL | |
| x_return_status | OUT | M | VARCHAR2 | Return status of the prepayment call | |
| x_msg_count | OUT | M | NUMBER | Message counts in message stack | |
| x_msg_data | OUT | M | VARCHAR2 | Message text in message stack. | |
| p_usr_currency_code | IN | O | VARCHAR2 | Translated currency code | |
| p_currency_code | IN | M | VARCHAR2 | Currency of the receipt | |
| p_usr_exchange_rate_type | IN | O | VARCHAR2 | User exchange rate type | |
| p_exchange_rate_type | IN | O | VARCHAR2 | Exchange rate type, if other than functional currency (if functional currency is different than receipt) | |
| p_exchange_rate_date | IN | O | DATE | Exchange rate date | |
| p_exchange_rate | IN | O | NUMBER | Exchange rate | |
| p_amount | IN | M | NUMBER | Receipt amount | |
| p_factor_discount_amount | IN | O | NUMBER | Factor discount amount | |
| p_receipt_number | INOUT | O | VARCHAR2 | Receipt number, need to pass if doc sequence is not enabled | |
| p_receipt_date | IN | O | DATE | Receipt creation Date | |
| p_gl_date | IN | O | DATE | GL date of the receipt | |
| p_maturity_date | IN | O | DATE | Maturity date of the receipt | |
| p_postmark_date | IN | O | DATE | Postmark date of receipt | |
| p_customer_id | IN | M | NUMBER | Customer ID of the receipt | |
| p_customer_name | IN | O | VARCHAR2 | Customer Name | |
| p_customer_number | IN | O | NUMBER | Customer Number | |
| p_customer_bank_account_id | IN | M | NUMBER | Customer bank account ID | |
| p_customer_bank_account_num | IN | O | VARCHAR2 | Customer bank account number | |
| p_customer_bank_account_name | IN | O | VARCHAR2 | Customer bank account name | |
| p_location | IN | O | VARCHAR2 | Location | |
| p_customer_site_use_id | IN | M | NUMBER | Site use ID | |
| p_customer_receipt_reference | IN | O | VARCHAR2 | Reference information on receipt header | |
| p_override_remit_account_flag | IN | O | VARCHAR2 | Remittance account override flag | |
| p_remittance_bank_account_id | IN | M | VARCHAR2 | Remittance bank account ID | |
| p_remittance_bank_account_num | IN | O | VARCHAR2 | Remittance bank account number | |
| p_remittance_bank_account_name | IN | O | VARCHAR2 | Remittance bank account name | |
| p_deposit_date | IN | O | DATE | Deposit date | |
| p_receipt_method_id | IN | M | NUMBER | Remittance method ID (receipt method) | |
| p_receipt_method_name | IN | O | VARCHAR2 | Receipt method name | |
| p_doc_sequence_value | IN | O | NUMBER | Doc sequence value, if doc sequence is enabled (mandatory if doc sequence is enabled) | |
| p_ussgl_transaction_code | IN | O | NUMBER | USSGL transaction code, if exists, on receipt header | |
| p_anticipated_clearing_date | IN | O | DATE | Anticipated receipt clearing date | |
| p_called_from | IN | M | NUMBER | Which program called this routine? | |
| p_attribute_rec | IN | O | RECORD TYPE | Receipt Header attributes | |
| p_global_attribute_rec | IN | O | RECORD TYPE | Global attributes on receipt header (GDF) | |
| p_receipt_comments | IN | O | VARCHAR2 | Receipt header comments | |
| p_issuer_name | IN | O | VARCHAR2 | AR Notes Issuer name | |
| p_issue_date | IN | O | DATE | AR Notes Issue Date | |
| p_issuer_bank_branch_id | IN | O | NUMBER | AR Notes Issuer bank branch ID | |
| p_cr_id | OUT | M | NUMBER | Cash receipt ID | |
| p_applied_payment_schedule_id | IN | M | NUMBER | For prepayment, it will be -7 | |
| p_amount_applied | IN | O | NUMBER | Specify amount which needs to be put in prepayment out of the receipt amount | |
| p_application_ref_type | IN | O | VARCHAR2 | Prepayment application reference from a lookup code for lookup type AR_PREPAYMENT_TYPE to indicate where it is created from. For example, OM. | |
| p_application_ref_id | IN OUT | M | NUMBER | Application reference ID. For example, order ID. | |
| p_application_ref_num | IN OUT | M | VARCHAR2 | Reference number. For example, order number. | |
| p_secondary_application_ref_id | IN OUT | O | NUMBER | Additional reference, if exists | |
| p_receivable_trx_id | IN | O | NUMBER | Receivable activity ID, default if not passed for prepayment. | |
| p_amount_applied_from | IN | O | NUMBER | Amount applied in functional currency | |
| p_apply_date | IN | O | DATE | If null, takes sysdate | |
| p_apply_gl_date | IN | O | DATE | Application GL date | |
| app_ussgl_transaction_code | IN | O | VARCHAR2 | USSGL transaction type code on application | |
| p_show_closed_invoices | IN | O | VARCHAR2 | Default FALSE | |
| p_move_deferred_tax | IN | O | VARCHAR2 | Default Y | |
| app_attribute_rec | IN | O | RECORD TYPE | Application attributes | |
| app_global_attribute_rec | IN | O | RECORD TYPE | Global application attributes (GDF) | |
| app_comments | IN | O | VARCHAR2 | comments on application | |
| p_payment_server_order_num | IN OUT | M | VARCHAR2 | Payment server order number | |
| p_call_payment_processor | IN | O | VARCHAR2 | Decides whether to call Oracle Payments. DEFAULT FND_API.G_FALSE | |
| p_payment_response_error_code | IN OUT | M | VARCHAR2 | Oracle Payments return error code | |
| p_approval_code | IN OUT | M | VARCHAR2 | Credit Card Approval code | |
| p_receivable_application_id | OUT | M | NUMBER | Receivable applications ID of the application | |
| p_payment_set_id | IN OUT | M | NUMBER | If passed, it will take the passed payment_set_id while creating prepayment application. Otherwise, generate a new number and pass it back. |
The following is a test case for creating a prepayment.
Objective:
To create a prepayment, passing the minimum number of parameters.
Entered parameters:
p_api_version
p_currency_code
p_amount
p_customer_id
p_customer_bank_account_id
p_customer_site_use_id
p_remittance_bank_account_id
p_receipt_method_id
p_called_from
p_applied_payment_schedule_id
p_application_ref_id
p_application_ref_num
The API call in this case would be:
AR_PREPAYMENTS_PUB.create_prepayment(
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_init_msg_list => FND_API.G_TRUE,
p_receipt_number => l_receipt_number,
p_currency_code => l_currency_code,
p_amount => p_payment_amount,
p_receipt_method_id => l_receipt_method_id,
p_customer_id => p_customer_id,
p_customer_site_use_id => l_site_use_id,
p_customer_bank_account_id => p_bank_account_id,
p_currency_code => l_receipt_currency_code,
p_exchange_rate => l_receipt_exchange_rate,
p_exchange_rate_type => l_receipt_exchange_rate_type,
p_exchange_rate_date => l_receipt_exchange_rate_date,
p_applied_payment_schedule_id => p_payment_schedule_id,
p_application_ref_type => l_application_ref_type , --Order type
p_application_ref_num => l_application_ref_num, --Order Number
p_application_ref_id => l_application_ref_id, --Order Id
p_cr_id => l_cr_id --OUT,
p_receivable_application_id => l_receivable_application_id --OUT
p_call_payment_processor => l_call_payment_processor
p_payment_response_error_code => l_payment_response_error_code
p_payment_set_id => l_payment_set_id -If not passed generate a new number
);
This routine is called to calculate the amount of all installments of a given payment term.
This API routine has 4 output and 5 input parameters. Of the output parameters, the API returns 5.
Standard API parameters: 0
Prepayment parameters: 5
Standard API parameters: 3
Prepayment parameters: 1
The following table lists the parameters that pertain specifically to the Get Installment routine:
| Parameter | Type | Mandatory/Optional | Data-Type | Default Value | Details |
|---|---|---|---|---|---|
| p_term_id | IN | M | NUMBER | Payment term ID | |
| p_amount | IN | M | NUMBER | Line amount and additional charges (if any) | |
| p_tax | IN | O | NUMBER | Tax amount | |
| p_freight | IN | O | NUMBER | Freight charges | |
| p_currency_code | IN | M | VARCHAR2 | Currency code for calculating the installment amount | |
| p_installment_tbl | OUT | O | NUMBER | A table consisting of installment number and installment amount | |
| x_return_status | OUT | M | VARCHAR2 | Return status of the API call | |
| x_msg_count | OUT | M | NUMBER | Message counts in message stack | |
| x_msg_data | OUT | M | VARCHAR2 | Message text in message stack. |
The following is a test case for get_installment.
Objective:
To get the installment amount given an amount, payment term and currency code.
Entered parameters:
p_term_id
p_amount
p_currency_code
AR_PREPAYMENTS_PUB.get_installment(
p_term_id => l_term_id ,
p_amount => l_amount,
p_currency_code => l_currency_code,
p_installment_tbl => l_installment_tbl , --OUT
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Messages play an important role in the effectiveness of your API calls. The right message is raised at the right point to convey to you the exact error that has occurred or any warnings that have been raised.
In the Prepayments API, all error messages and warnings raised during the execution are put on the message stack and can be retrieved by the user as described in Robust Validation.
The following is the list of all error messages raised by the Prepayments API.
| Message Number | Message Name | Message Description |
|---|---|---|
| 96735 | AR_RAPI_CUS_BK_AC_2_INVALID | Invalid combination of customer bank account name and number. |
| 294347 | AR_RAPI_PREPAY_SEQ_FAILED | The prepayment sequence generation has failed. Please contact your system administrator. |
| AR_PPAY_PAY_TERM_INVALID | Payment term ID is invalid. | |
| AR_PPAY_BASE_AMOUNT_INVALID | The amount can not be null, 0, or negative. | |
| 96734 | AR_RAPI_CURR_CODE_INVALID | Currency code is invalid. |
Since this API also calls the Receipt API AR_RECEIPT_API_PUB, it could also throw messages raised by the Receipt API.
Please refer to messages listed in Receipt API Messages.