Single Insertion scripts for Approved Supplier List (ASL).

This Document provides the guidance to the user with the necessary information for creating the approved supplier lists through API approach.

The API's used here are:

1.po_asl_ths.insert_row

 

2.po_asl_attributes_ths.insert_row

3. po_asl_documents_ths.insert_row

Mandatory Columns

The following tables describes only the most commonly used fields used in the API. There are other

fields available in the API/PUB.

po_asl_ths.insert_row:

<><>

COLUMN_NAME	DATATYPE	REQUIRED	COMMENTS
inventory_item_id	NUMBER	Y	Item
Vendor_id	NUMBER	Y	Supplier
Vendor_site_id	NUMBER	Y	Supplier Site
using_organization_id	NUMBER	Y	Ship-to organization using this record
owning_organization_id	NUMBER	Y	Organization that created the record initially
vendor_business_type	VARCHAR2	Y	Business type of Distributor, Direct, or Manufacturer. Default to 'DIRECT'
asl_status_id	NUMBER	Y	Approval/certification status. Default to ‘Approved’ (2)
primary_vendor_item	VARCHAR2	Y	Supplier item
last_update_date	DATE	Y	Default to 'SYSDATE'
last_updated_by	NUMBER	Y	Standard Who Column
creation_date	DATE	Y	Default to 'SYSDATE'
created_by	NUMBER	Y	Standard Who Column

po_asl_attributes_ths.insert_row:

<><>

COLUMN_NAME	DATATYPE	REQUIRED	COMMENTS
asl_id	NUMBER	Y	ASL unique identifier
inventory_item_id	NUMBER	Y	Item
Vendor_id	NUMBER	Y	Supplier
Vendor_site_id	NUMBER	Y	Supplier Site
using_organization_id	NUMBER	Y	Ship-to organization using this record
Document_sourcing_method	VARCHAR2	Y	Default to 'ASL'
Release_generation_method	VARCHAR2	Y	Automatic release method Default to 'CREATE_AND_APPROVE'
attribute_category	VARCHAR2	Y	Descriptive flex field segment
attribute1(state)	VARCHAR2	Y	Descriptive flex field segment
country_of_origin_code	VARCHAR2	Y	Code for the item's country of manufacture
last_update_date	DATE	Y	Default to 'SYSDATE'
last_updated_by	NUMBER	Y	Standard Who Column
creation_date	DATE	Y	Default to 'SYSDATE'
created_by	NUMBER	Y	Standard Who Column

po_asl_documents_ths.insert_row:

<><>

COLUMN_NAME	DATATYPE	REQUIRED	COMMENTS
asl_id	NUMBER	Y	ASL unique identifier
using_organization_id	NUMBER	Y	Ship-to organization using this record
sequence_number	NUMBER	Y	Document ranking Default to '1'
document_type_code	VARCHAR2	Y	Document type Default to 'QUOTATION'
document_header_id	NUMBER	Y	Document header unique identifier
document_line_id	NUMBER	Y	Document line unique identifier
last_update_date	DATE	Y	Default to 'SYSDATE'
last_updated_by	NUMBER	Y	Standard Who Column
creation_date	DATE	Y	Default to 'SYSDATE'
created_by	NUMBER	Y	Standard Who Column

Insertion steps

Following are the steps that must be followed, in order, to perform the Single insertions for approved supplier lists in 11.5.10

<><>

Execution Step	Description
Step 1	Default the using organization id and owning organization id to the value of organization that created the record initially
Step 2	Identify the vendor id and his corresponding vendor site id. SELECT vendor_id FROM po_vendors WHERE vendor_name = 'Dispomedica GmbH'; With the above vendor_id get the vendor_site_id . SELECT vendor_site_id FROM po_vendor_sites_all WHERE vendor_id = 313557;
Step 3	Choose one of the values for the vendor_business_type column. Business type of Distributor : Company sells products made by manufacturers. Direct : Company sells their products directly to you. Manufacturer : Company manufactures and sells through distributors Default the status_id to '2'.
Step 4	Since, it is an API approach we directly insert the date into the base tables using the standard oracle API procedures. Creating a record by inserting the data into the base table po_approved_supplier_list through apps.po_asl_ths.insert_row procedure. DECLARE x_row_id VARCHAR2(20); x_asl_id NUMBER; BEGIN apps.po_asl_ths.insert_row (x_row_id, x_asl_id, 2489, --using_organization_id 2489, --owning_organization_id, 'DIRECT', --vendor_business_type, 2, --status_id, SYSDATE, --last_updated_date 10907, --last_updated_by, SYSDATE, --creation_date, 10907, --created_by, NULL, 313557, --vendor_id, 589535, --inventory_item_id, NULL, 24180, --vendor_site_id, '01.3307', --primary_vendor_item, NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL); COMMIT; END;
Step 5	Choose the code for the item's country of manufacture and the state.
Step 6	Default the asl_id to '75310' derived from the step 4. Default the document_sourcing_method to 'ASL'. Default the release_generation_method to 'Automatic_release' I .e, 'create_and_approve' . Default the attribute_category to '2476'
Step 7	Creating a record by inserting the data into the base table po_asl_attributes through apps.po_asl_attributes_ths .insert_row procedure. DECLARE x_row_id VARCHAR2(20); BEGIN apps.po_asl_attributes_ths.insert_row (x_row_id, 75310, --asl_id 2489, --using_organization_id, SYSDATE, --last_updated_date 10907, --last_updated_by, SYSDATE, --creation_date 10907, --created_by, 'ASL', --document_sourcing_method 'CREATE_AND_APPROVE', --release_generation_method NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL, 313557, --vendor_id, 24180, --vendor_site_id, 589535, --inventory_item_id, NULL, '2476', --attribute_category '09', --state(attribute1), NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL, 'DE', --country_of_origin_code, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL); COMMIT; END;
Step 8	Identify the document_header_ id and document_line_id of the type_look_up code 'QUOTATION'. SELECT po_header_id FROM apps.po_headers_all WHERE segment1 = 70253 AND type_lookup_code = 'QUOTATION' AND org_id = 2476; Similarly deriving document_line_id: SELECT po_line_id FROM apps.po_lines_all WHERE po_header_id = 106717 AND item_id = 589535 AND ROWNUM = 1;
Step 9	Choose the following values for the document_type_code column STANDARD → One time purchase orders BLANKET → Cover a specific date range and used when not sure of the exact quantity is not known prior. QUOTATION → Quotation in response to the RFQ generated by the organization. Default the asl_id to '75310' derived from the step 4. Default the sequence_number to '1'. Sequence_number is the document ranking.
Step 10	Creating a record by inserting the data into the base table po_asl_documents through apps.po_asl_documents_ths .insert_row procedure. DECLARE x_row_id VARCHAR2(20); BEGIN apps.po_asl_documents_ths.insert_row (x_row_id, 75310, --asl_id 2489, --using_organization_id, 1, --sequence_number, 'QUOTATION', --document_type_code 106717, --document_header_id, 343765, --document_line_id, SYSDATE, --last_updated_date 10907, --last_updated_by, SYSDATE, --creation_date 10907, --created_by, NULL,NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL, NULL,NULL); COMMIT; END;
Step 11	Check the Base tables and their respective columns effected. Below listed are the important columns in each table. PO_APPROVED_SUPPLIER_LIST USING_ORGANIZATION_ID OWNING_ORGANIZATION_ID VENDOR_BUSINESS_TYPE ASL_STATUS_ID LAST_UPDATE_DATE LAST_UPDATE_BY CREATION_DATE CREATION_BY VENDOR_ID ITEM_ID VENDOR_SITE_ID PRIMARY_VENDOR_ITEM PO_ASL_ATTRIBUTES: ASL_ID USING_ORGANIZATION_ID LAST_UPDATE_DATE LAST_UPDATE_BY CREATION_DATE CREATION_BY DOCUMENT_SORCING_METHOD RELEASE_GENERATION_METHOD VENDOR_ID VENDOR_SITE_ID ITEM_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 COUNTRY_OF_ORIGIN_CODE PO_ASL_DOCUMENTS: ASL_ID USING_ORGANIZATION_ID SEQUENCE_NUM DOCUMENT_TYPE_CODE DOCUMENT_HEADER_ID DOCUMENT_LINE_ID LAST_UPDATE_DATE LAST_UPDATE_BY CREATION_DATE CREATION_BY

Pre-Requisitions

Below mentioned are the list of tasks which need to be setup,before creating a record for approved supplier list.

<><>

Execution Step	Description
Step 1	The item being used must exist in MTL_SYSTEM_ITEMS_B.
Step 2	The supplier being used must exist in PO_VENDORS.
Step 3	The supplier site being used must exist in PO_VENDOR_SITES_ALL.
Step 4	The quotation number being used must exist in PO_HEADERS_ALL.

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;