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.

ITPub博客

Single Insertion scripts for Approved Supplier List (ASL).

Insertion steps

Pre-Requisitions

最新文章