Inbound Interface
Process Steps:
) Stage Table Creation
) Control file development
) PL/SQL Program
) Submit Standard Program
· Inbound interface will be used to upload the data from legacy
system in to Oracle applications base tables.
· We will receive the flat file from the client then we will
create staging table upload the data from flat file in to stage table.
· Develop the PL/SQL program to validate the data weather it is
valid or not, if it is valid we will insert in to interface table, if it is not
valid we will insert in to error table.
· Once the data is available in interface table submit the
standard program from SRS window, and we will transfer the data from interface
table to base tables.
· GL_INTERFACE_TABLE
STATUS: This column will accept any string but we will always insert
standards string called new it indicates that we are brining new data in to
General Ledge Applications.
SET_OF_BOOKS_ID: We have to enter the appropriate set of books ID,
it should be valid set of books id is available in GL_SETS_BOOKS table it is
valid, otherwise i8t is invalid.
USER_JE_SOURCES_NAME: We have to enter the journal sources name for the transaction
we can find all the valid source names in the tabled called GL_JE_SOURCES.
USER_JE_CATEGORY_NAME: WE have to find out weather journal category is available in
the GL_JE_CATEGORIES table. It is is available then we will insert,
otherwise we will reject.
CURRENCY_CODE: We have to enter the valid currency code in
FND_CURREINCES table we can find out weather it is valid or not.
ACCOUNTING_DATE and CREATION_DATE: Both columns will
accept valid date but that date should be less than or equal to System date.
CREATED_BY: WE have to enter valid user_id from FND_USER table we can
identify weather it is valid user_id or not.
PERIOD_NAME: We have to enter valid period name and period should be in the
open status from GL_PERIODS table we can find out weather it is valid period or
not, from GL_PERIOD_STATUS table we can find out period is in the
open status or not.
ENTERED_DR and ENTERED_CR: Both columns
will accept positive number Debit and credit amount, both debit and credit
should be equal otherwise account will be imported as suspense account.
GROUP_ID: WE will enter unique group number while importing from
interface table to base table it will be used as parameter.
ACTUAL_FLAG: This column will accept single character either ‘A’
or ‘B’ or ‘E’ a- Actual amounts, B- Budget Amounts, E- Encumbrance Amounts
Reference
1 - Batch
Name
Reference
2 - Batch
Description
Reference
3 - Don’t
Enter any value
Reference
4 - Journal
Entry Name
Reference
5 - Journal
Entry Description
Reference 11 to
20 - Don’t
enter any values.
Chart_of_accounts_id - Don’t
enter any values.
Transationc_date - Don’t
enter any values.
Je-Batch_id - Don’t
enter any values.
Je_header_id - Don’t
enter any values.
Pre requisites for GL
Interface:
1) Set of books should be defined (Currency, Calendar, Chart of
Accounts)
2) 2) Currency Conversion Rates needs to be defined.
3) Accounting Period should be defined and also opened
4) Source name and as well as category name should be defined.
Process Steps:
1) We have received flat file from client
2) We have created Staging table as per flat file structure
3) Developed Control file and uploaded data
4) Developed PL/SQL Program to upload the data from stage into
interface table
i. declare
Cursor
ii. open cursor
iii. Validate each record
iv. If no invalid record then insert into interface table.
Temporary Table
Creation
Create Table
GL_INTERF_TEMP (
STATUS Varchar2(10),
SET_OF_BOOKS_ID Number(8),
ACCOUNTING_DATE, Date,
CURRENCY Number(8),
DATE_CREATED Date,
CREATED_BY Number(8),
ACTUAL_FLAG Varchar2(1),
CATEGORY Varchar2(10),
SOURCE Varchar2(10),
CURR_CONVERSION Number(8),
SEGMENT1 Varchar2(100),
SEGMENT2 Varchar2(100),
SEGMENT3 Varchar2(100),
SEGMENT4 Varchar2(100),
SEGMENT5 Varchar2(100),
ENTERED_DR Number(8),
ENTERED_CR Number(8),
ACCOUNTED_DR Number(8),
ACCOUNTED_CR Number(8),
GROUP_ID Number(8)
);
Control File Creation
OPTIONS (SKIP = 1 )
LOAD DATA
INFILE '&1'
INSERT into TABLE GL_INTERF_TEMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
CATEGORY,
SOURCE,
CURR_CONVERSION,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
GROUP_ID)
CREATE A CONCURRENT PROGRM FROM SRS WINDOW THE EXCECUTABLE NAME MUST BE SQL LOADER .
ADD A PARAMETER TO DYNAMICALLY LOAD THE DATA USING CSV FILE OR .txt FILE.
AFTER LOADING THE DATA IN TO STAGING TABLE i.e in GL_INTERF_TEMP
Creation of PL/SQL procedure – to transfer the data from staging table
to interface table after validation
CREATE OR REPLACE
PROCEDURE GL_IN_PRO( Errbuf OUT
VARCHAR2,
Retcode
OUT VARCHAR2)
IS
-- cursor
declaration
CURSOR
gl_cur IS
SELECT
status ,
set_of_books_id ,
accounting_date ,
currency ,
date_created ,
created_by ,
actual_flag ,
category ,
source ,
curr_conversion ,
segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
entered_dr ,
entered_cr ,
accounted_dr ,
accounted_cr ,
group_id
FROM
GL_INT_TEMP;
l_currencycode VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag VARCHAR2(2);
l_error_msg VARCHAR2(100);
l_err_flag VARCHAR2(10);
l_category VARCHAR2(100);
L_USERID NUMBER(10);
BEGIN
DELETE
FROM gl_interface;
COMMIT;
FOR rec_cur IN gl_cur
LOOP
l_flag:='A';
l_err_flag:='A';
--This PL/SQL
Block will do the currency validation
--end of the
currency validation
--Category Column
Validation
BEGIN
SELECT
USER_JE_CATEGORY_NAME
INTO l_CATEGORY
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME
= REC_CUR.Category;
EXCEPTION
WHEN
OTHERS THEN
l_category:=NULL;
l_flag:='E';
l_error_msg:='Category
does not exist ';
END;
Fnd_File.put_line
(Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End Category
Column Validation
--User ID column
validation
BEGIN
SELECT USER_ID
INTO L_USERID
FROM FND_USER
WHERE USER_ID
= REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
L_userid:=NULL;
l_flag:='E';
l_error_msg:='User
ID does not exist ';
END;
Fnd_File.put_line
(Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of
Created_by OR UserID column Validation
--Set
of books Validation
BEGIN
SELECT
set_of_books_id
INTO l_set_of_books_id
FROM GL_SETS_OF_BOOKS
WHERE
set_of_books_id=rec_cur.set_of_books_id;
EXCEPTION
WHEN
OTHERS THEN
l_set_of_books_id:=NULL;
l_flag:='E';
l_error_msg:='set
of Books ID does not exist ';
END;
Fnd_File.put_line
(Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
-- End Set
of books Validation
--Status Column
validation
/* BEGIN
IF
rec_cur.status = 'NEW' THEN
l_flag:= 'A';
ELSE
l_flag:= 'E';
Fnd_File.put_line
(Fnd_File.LOG,'Status column has got invalid data');
END IF;
END;*/
BEGIN
SELECT
currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE
currency_code=rec_cur.currency
AND
currency_code='USD';
EXCEPTION
WHEN
OTHERS THEN
l_currencycode:=NULL;
l_flag:='E';
l_error_msg:='currency
code does not exists';
END;
Fnd_File.put_line
(Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
Fnd_File.put_line
(Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of Actual Flag
Column validation
IF l_flag!='E'
THEN
Fnd_File.put_line
(Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO
gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
user_currency_conversion_type,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
group_id)
VALUES
(rec_cur.status ,
rec_cur.set_of_books_id ,
rec_cur.accounting_date ,
rec_cur.currency ,
rec_cur.date_created ,
rec_cur.created_by ,
rec_cur.actual_flag ,
rec_cur.category ,
rec_cur.source ,
rec_cur.curr_conversion ,
rec_cur.segment1 ,
rec_cur.segment2 ,
rec_cur.segment3 ,
rec_cur.segment4 ,
rec_cur.segment5 ,
rec_cur.entered_dr ,
rec_cur.entered_cr ,
rec_cur.accounted_dr ,
rec_cur.accounted_cr ,
rec_cur.group_id);
END IF;
l_flag:=NULL;
l_error_msg:=NULL;
END LOOP;
COMMIT;
END GL_IN_PRO;
Purchase Order Inbound
Interface
Pre Requisites
1) Supplier sites contact details should be defined
2) Organization structure should be defined
3) Ship to bill to locations needs to be defined
4) Set of books needs to be defined
5) Items, item categories, UOM needs to be defined
6) Employee creation, buyer setup should be defined.
Process Steps
1) Create the Staging tables
2) Develop the Control files and register as concurrent program
3) Develop the PL/SQL Program and write the validations and insert
into interface table
4) Run the standard program called Import Standard Purchase orders
from PO Responsibility
Parameter
: Default
Buyer :
Null
Create
or update
items : No
PO
Status :
APPROVED
Batch
ID :
13
(We
can get from headers interface table)
5) Take the Request ID execute following query we can get the PO
numbers
select
segment1 Ponumber
from po_headers_all
where request
_id = 145233;
6) Go to the PO Application and Query the PO from as per the PO
number.
Control File: -
PO_HEADERS_INTERFACE
load data
infile *
insert into table
XX_HEADERS
fields terminated by
"," optionally enclosed by '"'
TRAILING NULLCOLS
( INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,APPROVAL_STATUS
,FREIGHT_CARRIER
,FOB
,FREIGHT_TERMS
)
Control File: -
PO_LINES_INTERFACE
load data
infile *
insert into
table XX_LINES
fields terminated by
"," optionally enclosed by '"'
TRAILING NULLCOLS
( interface_header_id
,interface_line_id
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,list_price_per_unit)
Procedure for
Insertions of data from Interface tables to Base tables after validating.
CREATE OR REPLACE
PROCEDURE PO_Int1(Errbuf OUT VARCHAR2,
Retcode
OUT VARCHAR2) AS
CURSOR c1 IS SELECT *
FROM PO_HEADER_TABLE;
CURSOR c2 IS SELECT *
FROM PO_LINE_TABLE;
l_vendor_id
number(10);
l_item varchar2(150);
l_flag varchar2(4)
default 'A';
l_msg varchar2(200);
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);
BEGIN
DELETE FROM
PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
SELECT
vendor_id
INTO l_vendor_id
FROM
po_vendors
WHERE
vendor_name = x1.VENDOR_NAME;
-- AND ORG_ID
= Fnd_Profile.Value('ORG_ID');
EXCEPTION
WHEN
OTHERS THEN
l_flag :=
'E';
l_vendor_id
:= 0;
l_msg :=
'Vendor id is Not in SYSTEM';
END;
--Vendor Site
code Validation
/* begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code
= x1.vendor_site_code;
EXCEPTION
WHEN
OTHERS THEN
l_flag :=
'E';
l_site_code :=
0;
l_msg :=
'Vendor Site Code is Not in SYSTEM';
END;*/
--End of Site Code
Validation
--Currency Code
Validation
Begin
select
currency_code
into l_curr_code
from fnd_currencies
where currency_code
= x1.CURRENCY_CODE;
EXCEPTION
WHEN
OTHERS THEN
l_flag :=
'E';
l_curr_code :=
0;
l_msg :=
'Currency Code is Invalid';
END;
--End of te Currency
Validation
--Operating Unit ID
Validation
Begin
select
organization_id
into l_org_id
from hr_operating_units
where organization_id
= x1.org_id;
EXCEPTION
WHEN
OTHERS THEN
l_flag :=
'E';
l_org_id :=
0;
l_msg :=
'Invalid Organization ID';
END;
--End of the ORG ID
Validation
IF l_flag
!= 'E' THEN
INSERT
INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id
,x1.document_type_code
,x1.CURRENCY_CODE
,x1.AGENT_NAME
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10
,x1.APPROVAL_STATUS
,SYSDATE
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN
c2 LOOP
l_flag := 'A';
--Item Validation
begin
select
segment1
into l_item
from mtl_system_items_b
where segment1 =
x2.item
AND ORGANIZATION_ID
= fnd_profile.value('ORG_ID');
exception
when others then
l_flag :=
'E';
l_vendor_id
:= 0;
l_msg :=
'Item is not valid Item';
END;
--End of the Item
Validation
if l_flag
!= 'E' then
INSERT INTO
PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.ITEM
,x2.ITEM_DESCRIPTION
,x2.item_id
,x2.UOM_CODE
,x2.QUANTITY,
X2.UNIT_PRICE,
X2.SHIP_TO_ORGANIZATION_CODE,
X2.SHIP_TO_LOCATION,
X2.NEED_BY_DATE,
X2.PROMISED_DATE,
X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END PO_INT1;
No comments:
Post a Comment