Open interfaces in Oracle E-Business Suite

Abstract: In the Oracle ERP system, the interface of the main program for automatic invoicing is provided to facilitate the import of user data and form the handling of receivables. In this article, we provide an automatic invoicing import instance, so that you can learn more about the automatic invoicing interface import program in the AR module.

You can use the submit request window to submit the automatic invoicing import program, main program, and clearing program. However, you can only use the "run automatic invoicing" window to submit the automatic invoicing main program and clearing program. The main program allows you to run multiple automatic invoicing instances to improve system performance and quickly import transactions.

You can run the automatic invoicing purge program to delete interface lines that are processed by the automatic invoicing import program and successfully transferred to the receivables management system. If the "clear interface table" option in the "System Options" window is set to "yes", you do not have to run this program. In this case, the receivables management system will automatically delete the interface line after you run the automatic invoicing.

The receivables management system uses the following table to temporarily store data transmitted by other systems:
Ra_interface_lines_all
Ra_interface_salescredits_all
Ra_interface_distributions_all
"Automatic invoicing" uses the fourth table ra_interface_errors_all to store the interface data that has not passed the verification. For more information about these tables, see the Oracle user manual.
1. Introduction to import an instance through the automatic invoicing Interface

2. perform related settings in the system
Step 1: Define an external business source)
If you want to use the manual serial number, do not select the automatic serial number option. Modify the following items in the 'customer information': purchaser, recipient, and recipient address, set to 'value' instead of 'identifi '.

Step 2: Define a descriptive elastic domain in the receivables system-'line transaction processing elastic Region' (Line Transaction flexfield) and define a new elastic domain segment, define each segment and segment value set.
Corresponding Control file:
Interface_line_context --- elastic domain segment
Interface_line_attribute1 ----- first segment of the elastic domain
Interface_line_attribute2 ----- second paragraph of the elastic domain
Interface_line_attribute3 ----- third paragraph of the elastic domain

Step 3: Define automatic invoicing line sorting rules
You can define invoice line sorting rules for transaction lines imported into the receivables management system using "automatic invoicing. Automatic invoicing uses these rules to sort transactions when you group the created transactions as invoices, debit tickets, and credit tickets. You can assign row sorting rules to each group rule.

Step 4: Define the automatic invoicing grouping rules
Define the grouping rules for automatic invoicing to group revenue and loan transaction processing into invoices, debit notices, and loan notices. The attribute specified by the grouping rule must be consistent with the row displayed in the same transaction.
"Automatic invoicing" requires that the "currency code" and "Settlement customer" transaction processing attributes be forcibly grouped (each invoice only allows one of these attributes ). The receivables management system automatically applies the grouping rules for these requirements to any additional transaction processing attributes that you assign to the grouping rules. All attributes of the elastic domain for transaction processing are optional in the grouping rules. You can assign these attributes as optional grouping features in the grouping rules window.

Figure 2 Detailed description of grouping rules
3. Define a data source file template (data.csv)
To allow the SQL * loader program to accurately upload data in the external file, we need to match the data in the external file with the fields in the SQL * loader file one by one.

4. Develop Host concurrent programs (arimport. Prog)
The host concurrent program is written using the script program of the operating system. The main purpose is to call the "data import program ". The purpose of the "data import program" is to automatically import external data sources (such as CSV and TXT files) to the interface table (ra_interface_lines_all.

Figure 4 Host Program in Oracle

 

// Host program code ################################### ######################################## ######## filename: arimport. prog ####################################### ######################################## #### program_name = 'basename $ 0' program_name = 'basename $ program_name. prog 'start_time = 'date + % d-% B-% Y "" % H: % m' echo $ program_name "begin .... "$ start_time datenow = 'date + % Y % m % d 'user_passwd = $1 var_user_id = $2 var_user_name = $3 var_request_id = $4 out_file = 'echo $ applcsf/$ applout /o $ var_request_id.out 'local_path = $ ar_top/Data ctl_file_name = 'echo $ local_path/"arcontrol. CTL "'data_file_name = 'echo $ local_path/" data.csv "'ctl_file_log = 'echo $ local_path"/"data" $ datenow ". log "'# echo $ ctl_file_name # echo $ data_file_name # echo $ ctl_file_log ########### sqlldr ######## CD $ local_path # $ ORACLE_HOME/bin/sqlldr $ user_passwd control = $ ctl_file_name log = $ ctl_file_log $ ORACLE_HOME/bin/sqlldr $ user_passwd control = arcontrol. CTL log = $ ctl_file_log cat $ ctl_file_log> $ out_file ########### update interface data ######## sqlplus-S $ user_passwd @ $ ar_top/SQL/arimport_upd. SQL {printf ("hello, world/N ");}

5. Develop the SQL * loader program (arcontrol. CTL)
The purpose of developing a data import program is to automatically import external data (such as TXT and CSV files) to the interface table of the Oracle ar module by calling the Host Program.

// SQL * loader program load data infile 'data.csv 'replace into Table ar. ra_interface_lines_all fields terminated by ', 'optionally enclosed by' "'(trx_number char (20)" Trim (: trx_number) ", trx_date 'yyyy-MM-DD', currency_code char (15) "Trim (: currency_code)", cust_trx_type_name char (20) "Trim (: cust_trx_type_name)", gl_date date 'yyyy-MM-DD ', role char (240) "Trim (: Role) ", term_name char (15)" Trim (: term_name) ", line_number, description char (240), quantity, unit_selling_price, batch_source_name char (50), set_of_books_id, line_type char (20) "Trim (: line_type)", conversion_type char (30) "Trim (: conversion_type )")

6. Develop an SQL script (arimport_upd. SQL)
To make it easier for users to use template files, we embed an SQL script in the Host Program. This SQL script aims to use some information entered by the user, automatically find the desired information from the Oracle database. For example, in the module file, you need to enter the "customer name", and in the interface table of the Oracle system, enter the customer ID (orig_system_bill_customer_ref ). For users, the customer ID is transparent. Therefore, this program is developed to make the user template more friendly.

// SQL script declare begin update ra_interface_lines_all iall set iall. conversion_type = substr (iall. conversion_type, 1, 9); Commit; update ra_interface_lines_all iall set iall. orig_system_bill_customer_ref = (select. customer_id from ar_customers_v A where. customer_name = iall. orig_system_bill_customer_ref), iall. orig_system_bill_address_ref = (select B. address_id from ar_customers_v A, ar_addresses_v B where. customer_name = iall. orig_system_bill_customer_ref and. customer_id = B. customer_id); Update ra_interface_lines_all t set T. interface_line_context = 'external import', T. interface_line_attribute1 = 'interface', T. interface_line_attribute2 = T. trx_number, T. interface_line_attribute3 = T. line_number, T. amount = T. quantity * t. unit_selling_price, T. memo_line_name = T. description, T. conversion_date = gl_date, t.org _ id = 81; update ra_interface_lines_all uila set uila. conversion_type = 'user', uila. conversion_date = uila. gl_date, uila. conversion_rate = 1 where uila. currency_code = 'cny '; commit; Exception when others then rollback; end;/exit;