Single Insertion scripts for AR Transactions In Oracle Applications in 11i

 

Single Insertion scripts for AR Transactions

 

Instructions for Transactions

Overview

Receivables: Receivables is all about receiving money. A department which keeps track of the money to be received from the customers for Goods/ Services provided to them.

In most business entities this is typically done by generating an invoice and mailing or electronically delivering it to the customer, which in turn must pay it with an established time frame called credit or payment terms. Actually based on transactions we use to know the due amount of the customer. This documents shows how to create a Regular Invoice is nothing but a transaction and creation of Credit memos and Debit memos.

Transaction: A transaction is an invoice which is sent by the customers to the Organization. Based on which the customers will get paid.

This Document provides the guidance to the user with the necessary information for creating Transactions.

The Interface table used here is:

1. RA_INTERFACE_LINES_ALL

Insertion steps

Following are the steps that must be followed, in order, to perform the Single insertions for Regular

Transactions, Credit memos and Debit memos:

<><>

Execution Step	Description
Step 1	Select * from ra_interface_lines_all where trunc(creation_date)=trunc(sysdate) To check whether is there any other records exists in the interface table.
Step 2	INSERT INTO Apps.ra_interface_lines_all (batch_source_name, line_type, description, currency_code, interface_line_context, org_id, orig_system_bill_address_id, orig_system_ship_address_id, orig_system_bill_customer_id, orig_system_ship_customer_id, quantity, unit_selling_price, amount, inventory_item_id, conversion_type, creation_date, created_by, uom_code, uom_name, tax_code, gl_date, conversion_rate, term_id, term_name, cust_trx_type_id, cust_trx_type_name, interface_line_attribute1, interface_line_attribute2, interface_line_attribute3, interface_line_attribute4 ) VALUES ('LEGACY', 'LINE', 'Sample Source', 'USD', 'CONVERSION', 204, 9123, 9123, 96084, 96084, NULL, NULL, 100, 109868, 'User', SYSDATE, 1008480, 'Ea', 'Each','Exempt', SYSDATE, 1, 1062, 'N30', 1, 'Invoice', 'LEG_TRX_000', 1, 1, 'REF_000' );
Step 3	COMMIT;
Step 4	Run the Auto invoice Master Program by using Receivables Manager, Vision Operations (USA) responsibility. Navigation: ControlRequests  Run Parameters which we need to pass: Number of Instances  1 Invoice Source  LEGECY Default Date  17-SEP-2009 Transaction type  Invoice (Low) Bill To Customer Number  (High) Bill To Customer Number  (Low) Bill To Customer Name  (High) Bill To Customer Name  (Low) GL Date  (High) GL Date  (Low) Ship Date  (High) Ship Date  (Low)Transaction Number  (High)Transaction Number  (Low) Sales Order Number  (High) Sales Order Number  (Low) Invoice Date  (High) Invoice Date  (Low) Ship To Customer Number  (High) Ship To Customer Number  (Low) Ship To Customer Name  (High) Ship To Customer Name  Base Due Date on Trx Date  Yes Due Date Adjustment Days  Then Submit the request.
Step 5	After submitting auto Invoice master program it automatically submits auto invoice import program.
Step 6	After completion of the concurrent programs check the errors tables if there is any errors. Select * from ra_interface_errors_all where interface_line_id=46819
Step 7	Then Check the base tables. Select * from ra_customer_trx_all where created_by=1008480 Select * from ra_customer_trx_lines_all where customer_trx_id=219300
Step 8	INSERT INTO ra_interface_lines_all ( batch_source_name, line_type, description, currency_code, conversion_type,conversion_rate, created_by, creation_date, last_updated_by, last_update_date, org_id, amount, orig_system_bill_address_id, orig_system_ship_address_id, orig_system_bill_customer_id, orig_system_ship_customer_id, trx_date, cust_trx_type_name, cust_trx_type_id, gl_date, interface_line_context, interface_line_attribute1, interface_line_attribute2, interface_line_attribute3, interface_line_attribute4,primary_salesrep_id,header_attribute1 ) VALUES ('LEGACY', 'LINE', 'Sample Source', 'USD', 'User',1, 1008480, SYSDATE, 1008480, SYSDATE, 204, -100, 9123, 9123, 96084, 96084, SYSDATE, 'Credit Memo', 2, SYSDATE, 'CONVERSION','REF_003', 1, 1, 'REF_003',-3,'REF_003' );
Step 9	COMMIT;
Step 10	Check the Interface table whether the got populated exactly or not. Select * from ra_interface_lines_all where created_by=1008480;
Step 11	Run the Auto invoice Master Program by using Receivables Manager, Vision Operations (USA) responsibility. Navigation: ControlRequests  Run Parameters which we need to pass: Number of Instances  1 Invoice Source  LEGECY Default Date  11-NOV-2009 Transaction type  Credit memo (Low) Bill To Customer Number  (High) Bill To Customer Number  (Low) Bill To Customer Name  (High) Bill To Customer Name  (Low) GL Date  (High) GL Date  (Low) Ship Date  (High) Ship Date  (Low)Transaction Number  (High)Transaction Number  (Low) Sales Order Number  (High) Sales Order Number  (Low) Invoice Date  (High) Invoice Date  (Low) Ship To Customer Number  (High) Ship To Customer Number  (Low) Ship To Customer Name  (High) Ship To Customer Name  Base Due Date on Trx Date  Yes Due Date Adjustment Days  Then Submit the request.
Step 12	After submitting auto Invoice master program it automatically submits auto invoice import program.
Step 13	After completion of the concurrent programs check the errors tables if there is any errors. Select * from ra_interface_errors_all where interface_line_id=46819
Step 14	Then Check the base tables. Select * from ra_customer_trx_all where created_by=1008480 Select * from ra_customer_trx_lines_all where trx_number=502552;
Step 15	INSERT INTO ra_interface_lines_all (batch_source_name, set_of_books_id, line_type, term_name, term_id, description, currency_code, conversion_type, created_by, creation_date, last_updated_by, last_update_date, org_id, amount, orig_system_bill_address_id, orig_system_ship_address_id, trx_date, primary_salesrep_id, cust_trx_type_name, cust_trx_type_id, orig_system_bill_customer_id, orig_system_ship_customer_id, conversion_rate, gl_date, header_attribute_category, header_attribute1, header_attribute2, interface_line_context, interface_line_attribute1, interface_line_attribute2, interface_line_attribute3, interface_line_attribute4 ) VALUES ('LEGACY', 1, 'LINE', '30 Net', 4, 'Sample Source', 'USD', 'User', 1008480, SYSDATE, 1008480, SYSDATE, 204, 100, 9123, 9123, SYSDATE, NULL, 'Debit Memo', 1004, 96084, 96084, 1, SYSDATE, 'LEGACY', 'REF_005', SYSDATE, 'CONVERSION', 'REF_005', SYSDATE, 1, 1 );
Step 16	COMMIT;
Step 17	Run the Auto invoice Master Program by using Receivables Manager, Vision Operations (USA) responsibility. Navigation: ControlRequests  Run Parameters which we need to pass: Number of Instances  1 Invoice Source  LEGECY Default Date  17-SEP-2009 Transaction type  Invoice (Low) Bill To Customer Number  (High) Bill To Customer Number  (Low) Bill To Customer Name  (High) Bill To Customer Name  (Low) GL Date  (High) GL Date  (Low) Ship Date  (High) Ship Date  (Low)Transaction Number  (High)Transaction Number  (Low) Sales Order Number  (High) Sales Order Number  (Low) Invoice Date  (High) Invoice Date  (Low) Ship To Customer Number  (High) Ship To Customer Number  (Low) Ship To Customer Name  (High) Ship To Customer Name  Base Due Date on Trx Date  Yes Due Date Adjustment Days  Then Submit the request.
Step 18	After submitting auto Invoice master program it automatically submits auto invoice import program.
	Check whether is there is any errors. Select * from ra_interface_errors_all where interface_line_id=46819;
	Then Check the base tables. Select * from ra_customer_trx_all where created_by=1008480; Select * from ra_customer_trx_lines_all where trx_number=502552;

Pre-Requisitions

<><>

Execution Step	Description
Step 1	Customer should be defined in Oracle. Navigation: Set up Customers  Standard Select * from ra_customers where customer_name like 'Apps CUST%'
Step 2	Currency must be defined in Oracle. Navigation: Setup  Financials  Currencies  Currencies Select * from fnd_currencies where currency_code like 'USD'
Step 3	Item must be defined on Oracle. Navigation: Set up  Transactions  Items  Define Items. Select * form mtl_system_items_b where segment1 like 'JK_TEST_ITEM%'
Step 4	Unit of measure must be defined in Oracle. Navigation: Set up  System  Unit of Measure  Classes. Select * from mtl_unit _of_measures where uom_code like 'Ea%'
Step 5	Transaction typed must be defined in Oracle. Navigation: Set up Transactions  Transaction types Select * from ra_cust_trx_types_all where name like 'invoice%'
Step 6	Source must be defined in Oracle. Navigation: Set up  Transactions  Sources select * from ra_batch_sources_all where name like ’LEGECY%'
Step 7	Payment terms must be defined in Oracle. Navigation: Set up  Transactions  Payment Terms. select * from ra_terms where name like 'N30%'

Code Attachments

single_insert_transactions.sql

The above sql file has the code to perform single insert into Interface tables for transactions.

INSERT INTO Apps.ra_interface_lines_all
(batch_source_name, line_type, description, currency_code,
interface_line_context, org_id, orig_system_bill_address_id,
orig_system_ship_address_id, orig_system_bill_customer_id,
orig_system_ship_customer_id, quantity, unit_selling_price,
amount, inventory_item_id, conversion_type, creation_date,
created_by, uom_code, uom_name, tax_code, gl_date,
conversion_rate, term_id, term_name, cust_trx_type_id,
cust_trx_type_name, interface_line_attribute1,
interface_line_attribute2, interface_line_attribute3,
interface_line_attribute4
)
VALUES ('LEGACY', 'LINE', 'Sample Source', 'USD',
'CONVERSION', 204, 9123,
9123, 96084,
96084, NULL, NULL,
100, 109868, 'User', SYSDATE,
1008480, 'Ea', 'Each','Exempt',
SYSDATE,
1, 1062, 'N30', 1,
'Invoice', 'LEG_TRX_000',
1, 1,
'REF_000'
);

COMMIT;

Script for Credit memos Single Insert:

INSERT INTO ra_interface_lines_all

( batch_source_name, line_type, description,
currency_code, conversion_type,conversion_rate, created_by, creation_date,
last_updated_by, last_update_date, org_id, amount,
orig_system_bill_address_id, orig_system_ship_address_id,
orig_system_bill_customer_id, orig_system_ship_customer_id,
trx_date,
cust_trx_type_name, cust_trx_type_id, gl_date,
interface_line_context, interface_line_attribute1,
interface_line_attribute2, interface_line_attribute3,
interface_line_attribute4,primary_salesrep_id,header_attribute1
)
VALUES ('LEGACY', 'LINE', 'Sample Source',
'USD', 'User',1, 1008480, SYSDATE,
1008480, SYSDATE, 204, -100,
9123, 9123,
96084, 96084,
SYSDATE, 'Credit Memo',
2, SYSDATE,
'CONVERSION','REF_003',
1, 1,
'REF_003',-3,'REF_003'
);
COMMIT;

Script for Debit memos:

INSERT INTO ra_interface_lines_all

(batch_source_name, set_of_books_id, line_type, term_name,
term_id, description, currency_code, conversion_type,
created_by, creation_date, last_updated_by, last_update_date,
org_id, amount, orig_system_bill_address_id,
orig_system_ship_address_id, trx_date, primary_salesrep_id,
cust_trx_type_name, cust_trx_type_id,
orig_system_bill_customer_id, orig_system_ship_customer_id,
conversion_rate, gl_date, header_attribute_category,
header_attribute1, header_attribute2, interface_line_context,
interface_line_attribute1, interface_line_attribute2,
interface_line_attribute3, interface_line_attribute4
)
VALUES ('LEGACY', 1, 'LINE', '30 Net',
4, 'Sample Source', 'USD', 'User',
1008480, SYSDATE, 1008480, SYSDATE,
204, 100, 9123,
9123, SYSDATE, NULL,
'Debit Memo', 1004,
96084, 96084,
1, SYSDATE, 'LEGACY',
'REF_005', SYSDATE, 'CONVERSION',
'REF_005', SYSDATE,
1, 1

);

COMMIT;

Single Insert Script for AP Invoices in 11.5.10 Oracle Applications

Single Insertion scripts for AP Invoices

This Document provides the guidance to the user with the necessary information for creating invoices through Single Insert in accurate way.

The Interface table used here is:

AP_INVOICES_INTERFACE.

AP_INVOICE_DISTRIBUTIONS_INTERFACE

 Insertion steps

Following are the steps that must be followed, in order, to perform the Single insertions for Invoices:

Execution Step	Description
Step 1	select * from AP_INVOICES_ALL where invoice_num='XX_INV_102’; --No rows selected. Hence we can create an Item with ‘XX_INV_002’
Step 2	select DISTINCT master_organization_id from MTL_PARAMETERS; --select one record from the above query For Example choose MASTER_ORGANIZATION_ID=204
Step 3	INSERT INTO ap_invoices_interface (invoice_id, invoice_num, invoice_amount, invoice_date, invoice_type_lookup_code, payment_method_lookup_code, gl_date, vendor_id, vendor_site_id, source, invoice_currency_code, payment_currency_code, terms_id, org_id, terms_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES (ap_invoices_interface_s.NEXTVAL, 'XX_INV_007', 2000, SYSDATE, 'STANDARD', 'CHECK', SYSDATE, 29925, 5064, 'External', 'USD', 'USD', 10003, 204, SYSDATE, SYSDATE, 1008480, SYSDATE, 1008480 );
Step 4	INSERT INTO ap_invoice_lines_interface (invoice_id, invoice_line_id, dist_code_combination_id, line_number, line_type_lookup_code, amount, accounting_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES (ap_invoices_interface_s.CURRVAL, ap_invoice_lines_interface_s.NEXTVAL, 17347, 1, 'ITEM', 2000, SYSDATE, SYSDATE, 1008480, SYSDATE, 1008480 );
Step 5	COMMIT;
Step 6	Select * from ap_invoices_interface where trunc(creation_date)=trunc (sysdate);
Step 7	Select * from ap_invoice_lines_interface where trunc(creation_date) =trunc(sysdate);
Step 8	Then Login to Applications and switch to Payables, Vision Operations (USA) Responsibility: Payables, Vision Operations (USA) Navigation: Others -->   Requests -->  Run Run Payables Open Import Program Parameters: Source → External Group → Null Batch Name → N/A Hold Name → Null Hold Reason → Null GL Date → Null Purge → No Summarize Report → No Then submit the request. After successful completion of the request please follow the next step.
Step 9	Select * from ap_invoices_all where invoice_num like ‘XX_INV_102’
Step 10	Select * from ap_invoices_distributions_all where invoice_id=64210

Pre-Requisitions

Execution Step	Description
Step 1	Organization should already exist.
Step 2	Template should already exist.
Step 3	Item to be created should not exist before.
Step 4	Accounting period must be in Open status.

Code Attachments

single_insert_invoices.sql

The above sql file has the code to perform single insert into Interface tables for Invoices.

INSERT INTO ap_invoices_interface

(invoice_id, invoice_num, invoice_amount, invoice_date,

invoice_type_lookup_code, payment_method_lookup_code, gl_date,

vendor_id, vendor_site_id, source, invoice_currency_code,

payment_currency_code, terms_id, org_id, terms_date,

creation_date, created_by, last_update_date, last_updated_by

)

VALUES (ap_invoices_interface_s.NEXTVAL, 'XX_INV_007', 2000, SYSDATE,

'STANDARD', 'CHECK', SYSDATE,

29925, 5064, 'External', 'USD',

'USD', 10003, 204, SYSDATE,

SYSDATE, 1008480, SYSDATE, 1008480

);

INSERT INTO ap_invoice_lines_interface

(invoice_id,

invoice_line_id, dist_code_combination_id, line_number,

line_type_lookup_code, amount, accounting_date, creation_date,

created_by, last_update_date, last_updated_by

)

VALUES (ap_invoices_interface_s.CURRVAL,

ap_invoice_lines_interface_s.NEXTVAL, 17347, 1,

'ITEM', 2000, SYSDATE, SYSDATE,

1008480, SYSDATE, 1008480

);

COMMIT;

Forms

R12 Tables and View changes

http://www.ziddu.com/download/15525756/R12_TableView_Changes.pdf.html

To submit concurrent program from BackEnd.

PROCEDURE call_concurrent

/*=======================================================================

OBJECT NAME: CALL_CONCURRENT

OBJECT TYPE: Public Procedure

DESCRIPTION: This procedure calls the standard import program 'Receiving Transactions Interface' to load the Receipts

PARAMETERS:

<<Name>> << Type >> <<Data Type>> <<Description>>

=====================================================================*/

IS

l_request_id NUMBER;

l_errbuf VARCHAR2 (3000);

l_phase VARCHAR2 (20);

l_status VARCHAR2 (20);

l_dev_phase VARCHAR2 (20);

l_dev_status VARCHAR2 (20);

l_message VARCHAR2 (2000);

l_flag BOOLEAN;

BEGIN

g_object_name := 'om';

g_procedure_name := 'CALL_CONCURRENT';

g_package_name := 'jk_package';

DBMS_OUTPUT.put_line ('IN call_concurrent');

BEGIN

fnd_global.apps_initialize (fnd_global.user_id,

fnd_global.resp_id,

fnd_global.resp_appl_id

);

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

--fnd_global.apps_initialize (8038, 50175, 700);

l_request_id :=

fnd_request.submit_request ('om', 'rvctp', NULL,

--'Transaction processing module for Integrated Receiving',

NULL,

--TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI'),

FALSE, 'BATCH', 0);

COMMIT;

DBMS_OUTPUT.put_line ('l_request_id IS ' || l_request_id);

IF l_request_id = 0

THEN

g_progress := '6002';

l_errbuf := fnd_message.get;

g_log :=

' Unexpected error occurred In Call concurrent Procedure ->'

|| SQLERRM

|| ' '

|| l_errbuf;

DBMS_OUTPUT.put_line (g_log);

g_error_message := g_log;

error_log_write;

ELSE

g_progress := '6004';

COMMIT;

l_flag :=

fnd_concurrent.wait_for_request (l_request_id,

30,

86400,

l_phase,

l_status,

l_dev_phase,

l_dev_status,

l_message

);

COMMIT;

END IF;

END call_concurrent;