Supplier , Supplier site open interface

Defaults:
The intent of the Supplier Open Interface Import is to emulate the entry of Suppliers, Sites, and Contacts through the Supplier Entry form as closely as possible. As such, there are many values that will be automatically defaulted if not
provided by the user.

These defaults for Suppliers can come from AP_SYSTEM_PARAMETERS_ALL, FINANCIALS_SYSTEM_PARAMS_ALL, PO_SYSTEM_PARAMETERS.ALL, RCV_PARAMETERS, or hard-coded values. As these source tables are organized by operating unit and the supplier data is not, the Suppliers Open Interface Import program assigns a default organization to each run based on the responsibility of the user initiating the concurrent request. This is necessary in order to determine which default values to
assign to supplier records.

The defaults for Supplier Sites generally come from the Supplier record. However, in certain cases due to multiple organizations considerations, it is necessary to default values for Supplier Sites from AP_SYSTEM_PARAMETERS_ALL or FINANCIALS_SYSTEM_PARAMS_ALL.

Validations:

As with defaults, the validations performed by the Open Interface programs are intended to emulate the processing done by the Supplier Entry form. In addition to checks done at the database level for correct data types, these validations include
checks performed against existing lookup values or values derived from other tables. When these types of validations are done, a check is performed to make sure that the value supplied is that of an active record, a record that has not been end-dated or disabled.
Details of the validations performed can be found in the descriptions for the AP_SUPPLIERS_INT, AP_SUPPLIER_SITES_INT, and AP_SUP_SITE_CONTACT_INT tables.

Rejections:

If an invalid value is encountered when the concurrent process is evaluating each row, the ’REJECT_CODE’ column is populated with the relevant rejection message and the ’STATUS’ is updated to ’REJECTED’. Processing on that row then stops and the
program moves on to the next row.



1. Fisrt Create suppliers with minimum information as mentioned below:

Insert into AP_SUPPLIERS_INT(VENDOR_INTERFACE_ID,VENDOR_NAME)

2. Create Supplier site with minimum information as mentioned below:

Insert into AP_SUPPLIER_SITES_INT(VENDOR_ID,VENDOR_SITE_CODE,ORG_ID)

If using in multi org environment enter value for SHIP_TO_LOCATION_CODE, BILL_TO_LOCATION_CODE.

In our case we have used below info:

Note: I have selected many columns but these are not required. After payment_currency_code no coulmn is required except Org_id, supplier_notif_method, email_address


insert into AP_SUPPLIER_SITES_INT
(vendor_id,VENDOR_SITE_CODE,ATTENTION_AR_FLAG,ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,CITY, STATE, ZIP,PROVINCE,COUNTRY,AREA_CODE, PHONE,FAX,FAX_AREA_CODE, PAYMENT_METHOD_LOOKUP_CODE,INVOICE_CURRENCY_CODE, PAYMENT_CURRENCY_CODE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,VAT_REGISTRATION_NUM,ORG_ID,ADDRESS_LINE4,COUNTY, SUPPLIER_NOTIF_METHOD,EMAIL_ADDRESS)
select * from XX_BCS_SITE


create table XX_BCS_SITE (
vendor_id VARCHAR2(200),
vendor_site_code VARCHAR2(200),
attention_ar_flag VARCHAR2(200),
address_line1 VARCHAR2(200),
address_line2 VARCHAR2(200),
address_line3 VARCHAR2(200),
city VARCHAR2(200),
state VARCHAR2(200),
zip VARCHAR2(200),
province VARCHAR2(200),
country VARCHAR2(200),
area_code VARCHAR2(200),
phone VARCHAR2(200),
fax VARCHAR2(200),
fax_area_code VARCHAR2(200),
payment_method_lookup_code VARCHAR2(200),
invoice_currency_code VARCHAR2(200),
payment_currency_code VARCHAR2(200),
attribute1 VARCHAR2(200),
attribute2 VARCHAR2(200),
attribute3 VARCHAR2(200),
attribute4 VARCHAR2(200),
vat_registration_num VARCHAR2(200),
org_id VARCHAR2(200),
address_line4 VARCHAR2(200),
county VARCHAR2(200),
supplier_notif_method VARCHAR2(200),
email_address VARCHAR2(200))

Insert into XX_BCS_SITE values
('1351','DAMMAM','N','PO BOX 1053, DAMMAM 31431','','',
'DAMMAM','','31431','','SA','+966 3','8472466','8471812',
'8471293','CHECK','SAR','SAR','8472466 8471812 8471293',
'','','TEL :8472466 8471812FAX 8471293',
'DA0108','126','','','','accounts@cmdc.com.sa');

3.Enter supplier contact information as below:


create table XX_BCS_SUP_CONTACT (
vendor_site_id VARCHAR2(200),
FIRST_NAME VARCHAR2(200),
MIDDLE_NAME VARCHAR2(200),
LAST_NAME VARCHAR2(200),
TITLE VARCHAR2(200),
area_code VARCHAR2(200),
phone VARCHAR2(200),
org_id VARCHAR2(200))



Insert into XX_BCS_SUP_CONTACT values ('517','','','MS. PAMELA VENGLAR','','','','292');
Insert into XX_BCS_SUP_CONTACT values ('1403','','','MR. OSAMA SAMI','','','','292');



INSERT INTO AP_SUP_SITE_CONTACT_INT VENDOR_SITE_ID,FIRST_NAME,MIDDLE_NAME,LAST_NAME, TITLE,AREA_CODE,PHONE,ORG_ID) SELECT * FROM XX_BCS_SUP_CONTACT

Update AP_SUP_SITE_CONTACT_INT
set STATUS = 'NEW'

Note: For all the above three tables STATUS must be NEW. While submitting the request Supplier Open Interface Import,Supplier

Sites Open Interface Import,Supplier Site Contacts Open Interface Import the Import Option in the parameter must be NEW.