Scribd
Upload
41 views

I need to write a pl_sql package to import supplie

To import supplier information into Oracle E-Business Suite R12.2.9, use the Supplier Open Interface Import concurrent program and populate the relevant interface tables: AP_SUPPLIERS_INT, AP_SUPPLIER_SITES_INT, AP_SUP_SITE_CONTACT_INT, and AP_SUPPLIER_BANK_ACCTS_INT. Perform validations on mandatory fields, unique constraints, and lookup values before loading data, ensuring all necessary information is accurate and complete. After data preparation and validation, insert the data into the interface tables, run the import program, handle any errors, and verify successful imports.

Uploaded by

o.orahi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
41 views

I need to write a pl_sql package to import supplie

To import supplier information into Oracle E-Business Suite R12.2.9, use the Supplier Open Interface Import concurrent program and populate the relevant interface tables: AP_SUPPLIERS_INT, AP_SUPPLIER_SITES_INT, AP_SUP_SITE_CONTACT_INT, and AP_SUPPLIER_BANK_ACCTS_INT. Perform validations on mandatory fields, unique constraints, and lookup values before loading data, ensuring all necessary information is accurate and complete. After data preparation and validation, insert the data into the interface tables, run the import program, handle any errors, and verify successful imports.

Uploaded by

o.orahi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

I need to write a pl/sql package to import supplier

information into oracle ebs r12.2.9 base tables.


which public apis do I have to use, what are the
different validations do I have to perform before
loading the incoming data into the respective
interface tables
To create a PL/SQL package for importing supplier information into Oracle E-Business Suite
(EBS) R12.2.9 base tables, you need to follow these steps:

Public APIs and Interface Tables


1. Public APIs:
Use the Supplier Open Interface Import concurrent program to load data from interface
tables into base tables.
This program internally uses APIs to validate and transfer data.
2. Interface Tables:
AP_SUPPLIERS_INT: For supplier header data.
AP_SUPPLIER_SITES_INT: For supplier site data.
AP_SUP_SITE_CONTACT_INT: For supplier site contact data.
AP_SUPPLIER_BANK_ACCTS_INT: For supplier bank account information.
3. Base Tables:
AP_SUPPLIERS: Stores supplier header details.
AP_SUPPLIER_SITES_ALL: Stores supplier site details.
AP_SUPPLIER_CONTACTS: Stores contact details.
AP_SUPPLIER_BANK_ACCOUNTS: Stores bank account details.

Validations Before Loading Data


Before loading data into the interface tables, ensure the following validations are performed:
Header-Level Validations (AP_SUPPLIERS_INT)
Mandatory Fields:
VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, and ORG_ID must be provided.
Unique Constraints:
Ensure that the combination of VENDOR_NAME and ORG_ID is unique.
Lookup Validation:
Validate values like VENDOR_TYPE_LOOKUP_CODE against Oracle EBS lookup values (e.g.,
STANDARD, EMPLOYEE).

Site-Level Validations (AP_SUPPLIER_SITES_INT)


Mandatory Fields:
Fields like VENDOR_SITE_CODE, ORG_ID, and address fields (ADDRESS_LINE1, CITY, etc.) are
required.
Parent Linkage:
Ensure that the corresponding supplier exists in the interface table or base table
(VENDOR_ID must match).
Country Code Validation:
Validate country codes against Oracle EBS standards.

Contact-Level Validations (AP_SUP_SITE_CONTACT_INT)


Mandatory Fields:
Fields like FIRST_NAME, LAST_NAME, and associated site information (VENDOR_SITE_ID) must
be provided.
Email Validation:
Check for valid email format, if provided.

Bank Account-Level Validations (AP_SUPPLIER_BANK_ACCTS_INT)


Mandatory Fields:
Fields such as BANK_ACCOUNT_NUM, BANK_ACCOUNT_NAME, and associated supplier/site IDs
must be provided.
Bank Details Validation:
Validate bank account number format and ensure the bank exists in Oracle EBS.
Steps for Data Loading
1. Data Preparation:
Extract and format incoming data to match the structure of interface tables.
2. Data Validation:
Perform all above validations programmatically or through staging tables.
3. Insert Data into Interface Tables:
INSERT INTO AP_SUPPLIERS_INT
(VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, ORG_ID, CREATED_BY, CREATION_DATE)
VALUES
('ABC Corp', 'STANDARD', 204, USER_ID, SYSDATE);

INSERT INTO AP_SUPPLIER_SITES_INT


(VENDOR_SITE_CODE, VENDOR_ID, ORG_ID, ADDRESS_LINE1, CITY, COUNTRY)
VALUES
('MAIN_SITE', SUPPLIER_ID, 204, '123 Main St', 'Los Angeles', 'US');

4. Run Supplier Open Interface Import Program:


Submit the "Supplier Open Interface Import" concurrent program to process data from
interface tables into base tables.
5. Error Handling:
Query the error table (AP_INTERFACE_ERRORS) for any issues during import.
SELECT INTERFACE_ID, ERROR_MESSAGE FROM AP_INTERFACE_ERRORS;

6. Verification:
Verify successful imports by querying base tables or using Oracle EBS forms.
SELECT VENDOR_NAME FROM AP_SUPPLIERS WHERE VENDOR_NAME = 'ABC Corp';

7. Cleanup:
Remove processed records from interface tables to avoid duplicate imports.

By following these steps and validations, you can ensure a smooth and error-free import process
for supplier data into Oracle EBS R12.2.9.
⁂

You might also like