Inbound Interface :
Create control file and call sql loader using HOST as
executable
/* -----------------------
|| Call SQL Loader
Program
||
-----------------------
|| This should be
done using the below command.
|| If 0 is returned
then it has not worked. Otherwise the
concurrent
|| request ID is
returned
*/
FND_FILE.PUT_LINE(FND_FILE.LOG,'Load PO Data : Submitting SQL*Loader');
l_request_id :=
FND_REQUEST.submit_request ( application => 'EQXX'
,program =>
'E_PO_SQL_LOADER'
,description => Data Load script for PO Generic Interface'
,argument1 => p_data_file
,argument2 => l_control_file
);
IF l_request_id = 0
THEN
-- Request could
not be started => Inform Integration Module and Raise Exception
RAISE
e_submit_loader_error;
ELSE
-- Request
initialised succesfully => Wait for completion and Check status
COMMIT; -- This
will start the request
FND_FILE.PUT_LINE(FND_FILE.LOG,'Load Data : Waiting for SQL*Loader');
l_dummy :=
FND_CONCURRENT.wait_for_request ( request_id => l_request_id
, phase =>
l_conc_phase
, status => l_conc_status
, dev_phase =>
l_conc_dev_phase
, dev_status => l_conc_dev_status
, message => l_conc_message
);
IF l_dummy =
TRUE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Concurrent Request has returned
TRUE');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Concurrent Request has returned
FALSE');
END IF;
IF
UPPER(l_conc_status) IN ('ERROR','WARNING') THEN
RAISE
e_load_error;
END IF;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Load PO Data : Source Systems : ' ||
p_source);
2/ E_PO_SQL_LOADER is concurrent program and acting as
executable , and executable type is HOST
3/ HOST program is calling .prog extension program which
contains unix script to call sql loader script
4/ first 4 parameters are reserve for unix shell scripting
(from 0 to 4)
5/ Code in prog should be like as below:
# submit
SQL*Loader
sqlldr
$v_id_pswd control=$EQU_TOP/bin/$v_sqlldr_ctl \
data=$v_filename \
bad=$EQU_TOP/bin/POSQL_$v_request_id.bad \
log=$EQU_TOP/bin/POSQL_$v_request_id.log
where control and data file are coming from concurrent
program parameter
eval
v_filename=$5
v_sqlldr_ctl=$6
6/ control file should
be present in custom top / bin folder
7/ Call concurrent
program by fnd_request.submit_Request
l_request_id :=
FND_REQUEST.submit_request ( application => 'EXX'
,program =>
'E_PO_SQL_LOADER'
,description => ' Data Load script for PO Generic Interface'
,argument1 => p_data_file
,argument2 => l_control_file
);
8/ After successful execution of loader , data is present in
staging tables
9/ Now run the concurrent program that will take data from
staging table, put validations and insert into mtl_system_items_interface , mtl_item_categories_interface,
PO _headers_interface,po_lines_interface
10 / Then call standard concurrent program to submit from
interface to base table
11/ program name : Import Standard Purchase Orders
12/ Import Items
13/Item Organization Assignment
2/ For supplier
import :
First create control
file and check data file format and then create host program to call .prog file
, which will call sql loader and data insert into staging tables
Pre-requisites setup’s
are: Payment terms, Pay
Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees
have to set up as vendors).
The Interface Tables
are:
§ AP_SUPPLIERS_INT
§ AP_SUPPLIER_SITES_INT
§ AP_SUP_SITE_CONTACT_INT
Mandatory Columns:
§ VENDOR_SITE_INTERFACE_ID
(ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique
identifier
§ VENDOR_SITE_CODE – Supplier Site name
Interface programs:
1.
Supplier Open Interface
Import
2.
Supplier Sites Open
Interface Import
3.
Supplier Site Contacts
Open Interface Import
The data inserted via these interfaces
are automatically populated into TCA tables.
Note:
AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections
information.
3/ For Customer Import
First create control
file and check data file format and then create host program to call .prog file
, which will call sql loader and data insert into staging tables
Interface Tables
·
RA_CONTACT_PHONES_INTERFACE
·
RA_CUSTOMER_PROFILES_INTERFACE
·
RA_CUSTOMER_INTERFACE
·
RA_CUSTOMER_BANKS_INTERFACE
·
RA_CUST_PAY_METHOD_INTERFACE
Mandatory columns:
·
ORG_ID
·
ORIG_SYSTEM_CUSTOMER_REF
·
INSERT_UPDATE_FLAG
·
CUSTOMER_NAME
·
CUSTOMER_NUMBER (if you are not using
Automatic Customer Numbering)
·
CUSTOMER_STATUS
·
LAST_UPDATED_BY
·
LAST_UPDATE_DATE
·
CREATED_BY
·
CREATION_DATE
Then run the Customer
Interface program to validate your imported data and transfer the data to the
Customer tables within your system
Customer Interface transfers customer data from the interface
tables into the following tables:
·
HZ_CONTACT_POINTS
·
HZ_CUST_ACCT_RELATE_ALL
·
HZ_CUST_ACCT_ROLES
·
HZ_CUST_ACCT_SITES_ALL
·
HZ_CUST_ACCOUNTS
·
HZ_CUST_PROFILE_AMTS
·
RA_CUST_RECEIPT_METHODS
·
HZ_CUST_SITE_USES_ALL
·
HZ_CUSTOMER_PROFILES
·
HZ_LOCATIONS
·
HZ_ORG_CONTACTS
·
HZ_PARTIES
·
HZ_PARTY_SITES
·
HZ_PERSON_PROFILES
·
AP_BANK_ACCOUNT_USES
·
AP_BANK_ACCOUNTS
·
AP_BANK_BRANCHES
The Customer Interface program will not allow updates to the
following tables:
·
HZ_CUST_ACCT_RELATE_ALL
·
HZ_CUST_SITE_USES_ALL
·
RA_CUST_RECEIPT_METHODS
·
AP_BANK_ACCOUNT_USES
·
AP_BANK_ACCOUNTS
·
AP_BANK_BRANCHES
