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 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.
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
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
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.
|