Introduction:
In this guide we’ll explain how to create AR invoice in oracle apps r12 using API, along with standard API parameters explanation.
Goal:
Create AR Invoice using “ar_invoice_api_pub.create_single_invoice” API
Forms Or Pages:
Example:
API IN/OUT Parameters :
— IN p_api_version NUMBER.
— IN P_BATCH_SOURCE_REC PL/SQL record type.
— IN P_TRX_HEADER_TBL PL/SQL table type TRX_HEADER_REC_TYPE.
— IN P_TRX_LINES_TBL PL/SQL table type TRX_LINE_REC_TYPE.
— IN P_TRX_DIST_TBL PL/SQL table type TRX_DIST_REC_TYPE.
— IN P_TRX_SALESCREDITS_TBL PL/SQL table type TRX_SALESCREDITS_REC_TYPE.
— OUT x_customer_trx_id NUMBER.
— OUT x_return_status VARCHAR2.
— OUT x_msg_count NUMBER.
— OUT x_msg_data VARCHAR2.
API Parameters clarifications:
- param p_api_version : Compare version numbers of incoming calls to its current versions Default (1.0)
- param l_trx_header_tbl .trx_header_id : Identifier for the Invoice header record. This must be unique for each record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table.
- param l_trx_header_tbl .trx_number : This is the transaction number for the invoice. This field should not be populated if the batch source has Copy Document Sequence Number to Transaction Number checked or if Automatic Transaction Numbering is enabled.
- param l_trx_header_tbl .bill_to_customer_id : This must exist in hz_cust_accounts table. The customer must be an active (‘A’) customer. Validated against hz_cust_accounts.cust_account_id.
- param l_trx_header_tbl .cust_trx_type_id : Transaction Type Identifier. This can have any one of the following three values: ‘INV’, ‘DM’, or ‘CM’. Validated against ra_cust_trx_types. If not populated, then it is retrieved from the batch source.
- param l_batch_source_rec.batch_source_id : If batch_source_id is null then value will be derived from AR_RA_BATCH_SOURCE profile option. In case the value is passed then it will be validated against ra_batch_sources. Only ‘Manual’ batch sources are allowed.
- param l_trx_lines_tbl .trx_header_id : Identifier for the Invoice header record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table. This column ties back with P_TRX_HEADER_TBL.
- param l_trx_lines_tbl .trx_line_id : Identifier for the Invoice lines record. This column can be generated based on a sequence or any number value. The value does not get recorded into any table.
- param l_trx_lines_tbl .line_number : Line number of the invoice
- param l_trx_lines_tbl .description : Line description. Required if inventory_item_id or memo_line_id is not provided.
- param l_trx_lines_tbl .memo_line_id : Memo line description identifier. Mutually exclusive with the column INVENTORY_ITEM_ID. Not required for ‘TAX’ and ‘FREIGHT’ lines.
- param l_trx_lines_tbl .quantity_invoiced : Quantity of invoice line. Required for Invoices.
- param l_trx_lines_tbl .unit_selling_price : Selling price per unit for a transaction line. Required for Invoices.
- param l_trx_lines_tbl .line_type : Receivables lookup code for STD_LINE_TYPE.
- param x_customer_trx_id : Returns customer_trx_id in case it is called for creating a single invoice. This parameter works only with CREATE_SINGLE_INVOICE procedure.
- param x_return_status : Represent the API status.
- param x_msg_count : Number of messages in the PI message list (not used by this API).
- param x_msg_data : Message in case API encounters any unexpected error.
API Calling example:
DECLARE
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_trx_number NUMBER;
l_customer_trx_id NUMBER;
l_trx_header_id NUMBER;
o_return_status VARCHAR2 (1);
o_msg_count NUMBER;
o_msg_data VARCHAR2 (2000);
l_err_msg VARCHAR2 (1000);
l_cnt NUMBER := 0;
l_msg_index_out NUMBER;
BEGIN
/* Setting the oracle applications context for the particular session */
fnd_global.apps_initialize (user_id => 1318, resp_id => 50559, resp_appl_id => 222);
/* Setting the org context for the particular session */
mo_global.set_policy_context ('S', 204);
BEGIN
SELECT ra_customer_trx_s.nextval INTO l_trx_header_id FROM dual;
END;
l_trx_header_tbl (1).trx_header_id := l_trx_header_id;
l_trx_header_tbl (1).trx_number := NULL;
l_trx_header_tbl (1).bill_to_customer_id := 1006;
l_trx_header_tbl (1).cust_trx_type_id := 1;
l_trx_header_tbl (1).comments := 'Oraask.com - Header Test description to create new AR Invoice through API';
l_batch_source_rec.batch_source_id := 1528;
l_trx_lines_tbl (1).trx_header_id := l_trx_header_id;
l_trx_lines_tbl (1).trx_line_id := ra_customer_trx_lines_s.nextval;
l_trx_lines_tbl (1).line_number := 1;
l_trx_lines_tbl (1).description := 'Oraask.com - Line Test description to create new AR Invoice through API';
l_trx_lines_tbl (1).memo_line_id := NULL;
l_trx_lines_tbl (1).quantity_invoiced := 10;
l_trx_lines_tbl (1).unit_selling_price := 12;
l_trx_lines_tbl (1).line_type := 'LINE';
ar_invoice_api_pub.create_single_invoice ( -- std parameters
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_true
-- api parameters
,p_batch_source_rec => l_batch_source_rec
,p_trx_header_tbl => l_trx_header_tbl
,p_trx_lines_tbl => l_trx_lines_tbl
,p_trx_dist_tbl => l_trx_dist_tbl
,p_trx_salescredits_tbl => l_trx_salescredits_tbl
-- Out parameters
,x_customer_trx_id => l_customer_trx_id
,x_return_status => o_return_status
,x_msg_count => o_msg_count
,x_msg_data => o_msg_data);
dbms_output.put_line ('********************************');
IF o_return_status = fnd_api.g_ret_sts_error
OR o_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line ('O_RETURN_STATUS = ' || o_return_status);
dbms_output.put_line ('O_MSG_COUNT = ' || o_msg_count);
IF o_msg_count > 0 THEN
FOR v_index IN 1 .. o_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index
,p_encoded => 'F'
,p_data => o_msg_data
,p_msg_index_out => l_msg_index_out);
o_msg_data := substr (o_msg_data, 1, 3950);
END LOOP;
dbms_output.put_line ('O_MSG_DATA = ' || o_msg_data);
END IF;
ELSE
SELECT count (*) INTO l_cnt FROM ar_trx_errors_gt;
IF l_cnt = 0 THEN
BEGIN
SELECT trx_number
INTO l_trx_number
FROM ra_customer_trx
WHERE customer_trx_id = l_customer_trx_id;
END;
dbms_output.put_line ('Transaction Number: ' || l_trx_number);
dbms_output.put_line ('Customer Trx id: ' || l_customer_trx_id);
dbms_output.put_line ('Return Status: ' || o_return_status);
ELSE
dbms_output.put_line ('Transaction not Created, Please check ar_trx_errors_gt table');
END IF;
END IF;
dbms_output.put_line ('********************************');
EXCEPTION
WHEN OTHERS THEN
l_err_msg := substr (sqlerrm, 0, 1000);
dbms_output.put_line ('***************************');
dbms_output.put_line ('There is an exception has been raised from API with error message: ' || l_err_msg);
dbms_output.put_line ('***************************');
END;Special Notes:
- In the above examples, we did not pass distribution, sales credits, or contingencies . Note, however, that you can create an invoice passing distributions, sales credits, and contingencies
- Please don’t use this code directly on production environment, instead test it on test environment first to make sure that API working correctly as per your requirements.
- We are only giving one example of how to create AR Invoice from backend with constant parameter values, so it’s your responsibility to change the parameter values as per your requirement.
Final Result:
In this article, we have demonstrate how to create AR Invoice using API in oracle apps r12 .
We are very happy to hear from you in comment section below in case you face any issue that we can asset you with.
Hopefully, it was clear and concise.
[convertkit form=1415728]
Thanks, sir, It is easily understood. I’m new to the Oracle app, If don’t mind, please provide any one basic invoice documentation for AR_Credit and AR_Adusted Oriented, sir.
Well done Hassan
Thank you Ramy Fahmy