How To Create Customer In Oracle Apps R12

On: August 4, 2020

API to create AR invoice in Oracle Apps R12

Hassan AbdElrahman

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:

API to create AR invoice in Oracle Apps R12

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:

API to create AR invoice in Oracle Apps R12 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.

Hassan AbdElrahman

Oracle ACE | Oracle Senior Technical Consultant @ Cloud Solutions Expert

- An Oracle ACE Awarded. - Around 7+ years of experience in Oracle Applications Technical experience in Oracle E-Business Suite R12.

I have written 75+ articles and answered 100+ questions in a related field.
Proficient in Workflows, Data Conversions, Interfaces, Client Extensions, Reports (XML, Bi Publishers, and RDF), and Forms (10g), Alerts, OAF extension.
hands-on experience with oracle mobile supply chain framework JAVA framework.
Hands-on experience with Oracle integration cloud OIC
Excellent knowledge of DATABASE Index's. Views, Triggers, Procedures, Functions, Database security, Privileges and Optimization.
Played various roles such as designing and development of application Documents and Direct interaction with client to gather requirements.