Interfaces

INTERFACE:

Interface : It is a medium for transferring the data from third party system to Oracle.

1.We need to insert the  one record manually from application end and notify mandatory columns

2.We need to perform one single insert statement from sql

3. Get the flat file from client

4.Get MD050 from functional consultant and based on that we need to create Md070

5.Load the data from flat file to user table by using loader program

6. Validate data and transfer it into staging table by using procedure

7. Move the data from staging to interface table by using procedure

8.Run the concurrent program from the application end then records are successfully inserted.

Item interface:

Base Tables:

mtl_system_items_b

mtl_items_revision

mtl_item_categories

Interface Tables:

Mtl_system_items_interface

mtl_item_categories_interface

Mtl_item_revisions_interface

Concurrent program:

Import items

Api Package:

Ego_item_pub.Process_items

Parameters (Import Items):

All Organizations : yes/No

Validate Items:  yes/No

Process items : yes/No

Delete processed Rows: yes/No

Process set null for all :

Create or update items : 1 – create new items

Parameters (API):

EGO_ITEM_PUB.process_items

( p_api_version => '1.0',

p_init_msg_list =>fnd_api.g_true,

p_commit =>fnd_api.g_true,

p_item_tbl =>v_item_tbl,

x_item_tbl=> x_item_tbl,

 x_return_status=> v_return_status,

 x_msg_count=>v_msg_count);

fnd_msg_pub.get

(p_msg_index => l_index ,

p_encoded => 'F' ,

p_data => lx_msg_data ,

p_msg_index_out => l_msg_index_out );

Single insert statement:

INSERT INTO mtl_system_items_interface( segment1
,description
,organization_code
,template_name
,primary_uom_code
,primary_unit_of_measure
--,revision
,process_flag
,set_process_id
,transaction_type
,attribute15
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login )
VALUES ( x.item_number
,x.item_description
,x.org_code
,x.template_name
,UPPER( x.uom )
,derive_uom( UPPER( x.uom ) )
--,x.revision
,1
,g_set_process_id
,'CREATE'
,x.record_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id );

Q1.Why we used staging table

A: Because of reference data.

Validation Columns:

Organization id

Make or buy

Puchasable Flag

Item Status

Item code

Primary Uom

Pricing uom

Planner code

Cogs account

Sales account

Bom_allowed_flag

Item type

Q: What tables are used for validation

1.       Organization id: mtl-parameters

2.       Item code : mtl_system_items_b

3.       Fnd_lookups

4.       Fnd_lookup_values

5.       Primary UOM:Mtl_units_of_measure_tl

6.       Planner code :mtl_planners

Purchase order  Interface:

Base Tables:

Po_headers_all

Po_lines_all

Po_distributions_all

Po_line_locations_all

Interface Tables:

Po_headers_interface

Po_lines_interface

Po_distributions_interface

Validations:

Header information

Operating unit name is valid

Supplier name is valid

Supplier site valid

Buyer is valid

Payment term is valid

Purchase order type is valid

Ship-to  and bill-to location is valid

Line information

Line Type is valid

Item name is valid

Item category  is valid

UOM is valid

Ship to location is valid

Quantity greater than zero

Concurrent Program name:

Purchase order detail report

Single insert statement:

INSERT INTO po_headers_interface

(interface_header_id,

batch_id,

process_code,

action,

org_id,

document_type_code,

currency_code,

agent_id,

vendor_name,

vendor_site_code,

ship_to_location,

bill_to_location,

reference_num)

VALUES

(po_headers_interface_s.NEXTVAL,

18,

'PENDING', --process_code

'ORIGINAL',  --  Indicates this is a new document

204, -- Org id for operating unit Vision Operations

'STANDARD',  -- Indicates a standard PO is being imported

'USD', -- The currency to be used in the PO

57, -- The ID of the buyer

'Office Supplies, Inc.',  -- Supplier name

'OFFICESUPPLIES',  --  Supplier Site

'V1- New York City', -- Ship to location name

'V1- New York City', -- Bill to location name

'TestPO'      -- Reference that can be tied to the imported PO

);

Lines Interface:

insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
item,
line_num,
shipment_num,
shipment_type,
quantity,
unit_price,
unit_of_measure,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
'AS54888',
1,
2,
'PRICE BREAK',
500,
11,
'Each',
207,
207,
'01-JUL-2006',
'01-JAN-2007');

Parameters:

Title

Buyer name

Items From

Item TO

Categories From

categories to

Vendors from

vendors to

status

Dynamic Precision

Supplier Interface:

Base Tables:

Po_vendors

Po_vendor_sites_all

po_vendor_contacts

Interface Table:

Ap_suppplers_int

Ap_suppliers_site_int

Ap_sup_site_contact_int

API

ap_vendor_pub_pkg.import_vendors

ap_vendor_pub_pkg.import_vendor_sites

ap_vendor_pub_pkg.import_vendor_contacts

p_api_version         

p_source              

       p_what_to_import   

        p_commit_size        

        x_return_status      

        x_msg_count            

        x_msg_data             

Concurrent Program name :

Supplier Open Interface Import

Supplier Sites Open Interface Import

Supplier Site Contacts Open Interface Import.

Validations:

Vendor_id

Supplier type

Invoice_match_option

Employee_name

Terms

Invoice currency

Payment currency

Terms basis

Payment basis

Ship to location id

Bill to location id

Supplier Site:

Operating unit

Supplier name is valid

Site code

Liablity account

Country code

Prepayment account

Future payment account

Terms

Invoice currency

Payment currency

Terms basis

Payment basis

Ship to location id

Bill to location id

Pay basis

Invoice match option

Supplier Contacts:

Site id is valid

Vendor is is valid

Organanization id is valiod

Single insert statements:

INSERT INTO AP_SUPPLIERS_INT
( VENDOR_INTERFACE_ID ,
SEGMENT1,
TERMS_NAME,
PAYMENT_PRIORITY ,
PAY_GROUP_LOOKUP_CODE,
VENDOR_NAME,
VENDOR_TYPE_LOOKUP_CODE,
STATUS )
values(AP_SUPPLIERS_INT_S.NEXTVAL, --VENDOR_INTERFACE_ID
'545456', --SEGMENT1,
-- 566, --EMPLOYEE_ID,
'IMMEDIATE', --TERMS_NAME,
1 , --PAYMENT_PRIORITY ,
'AAAA', --PAY_GROUP_LOOKUP_CODE,
'DUMMYCOMPANY', --VENDOR_NAME,
'COMPANY', --VENDOR_TYPE_LOOKUP_CODE,
'NEW' -- STATUS
);

COMMIT;

INSERT INTO AP_SUPPLIER_SITES_INT(
STATUS,
VENDOR_INTERFACE_ID, -- VENDOR_INTERFACE_ID,
VENDOR_SITE_INTERFACE_ID,
VENDOR_SITE_CODE,
ADDRESS_STYLE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
ZIP,
COUNTRY,
EMAIL_ADDRESS,
PAY_SITE_FLAG,
PURCHASING_SITE_FLAG,
--OPERATING_UNIT_NAME
ORG_ID)
values
(
'NEW',--STATUS
AP_SUPPLIERS_INT_S.CURRVAL ,--VENDOR_INTERFACE_ID
AP_SUPPLIER_SITES_INT_S.NEXTVAL,--VENDOR_SITE_INTERFACE_ID
'DUMMY SITE',--VENDOR_SITE_CODE
'NE',--ADDRESS_STYLE
'ADDRESS1',--ADDRESS_LINE1
'ADDRESS2',--ADDRESS_LINE2
'ADDRESS3',--ADDRESS_LINE3
'Anywhere',--CITY
'00100',--ZIP
'IT', ---COUNTRY
'AT@DUMMYCOMPANY.COM',--EMAIL_ADDRESS
'Y',--PAY_SITE
'Y', -- PURCHASING_SITE_FLAG
121 )--OPERATING_UNIT_ID
COMMIT;

 

INSERT INTO AP_SUP_SITE_CONTACT_INT (
VENDOR_INTERFACE_ID,
VENDOR_CONTACT_INTERFACE_ID,
VENDOR_SITE_CODE,
ORG_ID,
-- OPERATING_UNIT_NAME,
FIRST_NAME,
LAST_NAME,
PHONE,
EMAIL_ADDRESS)
VALUES (
AP_SUPPLIERS_INT_S.CURRVAL ,--VENDOR_INTERFACE_ID
AP_SUP_SITE_CONTACT_INT_S.NEXTVAL,
'DUMMY SITE',
121,
-- 'DUMMY OPERATING UNIT', --OPERATING_UNIT_NAME,
'JOE',
'DOE',
'555d-54',
'j.wood@dummycompany.com')

Parameters:

Supplier open interface import

import options:all,new,reject

batch size:1000

print exceptions only:yes/no

debug switch:yes/no

trace switch:No