Prepare
the data file
Item
Number,Description,Organization Code,Primary UOM,Template Name,COS
Account,Expense Account,Sales Account
Apps_Item_1,Apps_Item_Test1,V1,Each,Finished
Good,01-120-MAD3-MAD3-000,01-120-MAD2-MAD2- 000,01-600-5110-0000-000
Apps_Item_2,Apps_Item_Test2,V1,Each,Finished
Good,01-510-4110-0000-000,01-540-7530-0000- 000,01-510-5110-0000-000
Apps_Item_3,Apps_Item_Test3,V1,Each,Finished
Good,01-530-4110-0000-000,01-540-7530-0000- 000,01-520-5110-0000-000
Apps_Item_4,Apps_Item_Test4,V1,Each,Finished
Good,01-600-4110-0000-000,01-510-7620-0000- 000,01-600-5110-0000-000
Apps_Item_5,Apps_Item_Test5,V1,Each,Finished
Good,01-120-MAD3-MAD3-000,01-000-1570-0000- 000,01-600-5110-0000-000
Apps_Item_6,Apps_Item_Test6,V1,Each,Finished
Good,01-530-4110-0000-000,01-120-MAD2-MAD2- 000,01-510-5110-0000-000
Apps_Item_7,Apps_Item_Test7,V1,KGs,Finished
Good,01-120-MAD3-MAD3-000,01-510-7620-0000- 000,01-600-5110-0000-000
Apps_Item_8,,V1,Each,Finished
Good,01-530-4110-0000-001,01-540-7530-0000-000,01-510-5110- 0000-000
Apps_Item_9,Apps_Item_Test9,V1,Each,Finished
Good,01-120-MAD3-MAD3-000,01-120-12D2-MAD3- 000,01-600-5110-0000-000
Apps_Item_10,Apps_Item_Test10,V1,Each,Finished
Good,01-530-4110-0000-000,01-120-12I2-0000- 000,01-522-5110-0000-000
Step1:
Create the staging table as per the data file
CREATE TABLE apps.xxcust_items_conv_stg
(
status_stg
VARCHAR2
(7),
transaction_id_stg
NUMBER,
ls_item_number
VARCHAR2
(40),
ls_description
VARCHAR2
(240),
ls_organization_code
VARCHAR2
(3),
ls_template_name
VARCHAR2
(40),
ls_Pr_Unit_of_Measure
VARCHAR2
(25),
ls_item_type
VARCHAR2
(30),
ls_cos_account
VARCHAR2
(160),
ls_expense_account
VARCHAR2
(240),
ls_sales_account
VARCHAR2
(160),
organization_id
NUMBER,
template_id
NUMBER,
inventory_item_id
NUMBER,
pr_uom_code
VARCHAR2
(30),
coS_account_id
NUMBER,
expense_account_ID
NUMBER,
sales_account_ID
NUMBER,
created_by
NUMBER,
creation_date
DATE,
ERROR_CODE
VARCHAR2
(200)
);
Step2:
Create the Sequence and Trigger for above staging table to insert the sequence
and status as N CREATE SEQUENCE apps.xxcust_items_conv_s
START WITH 1
INCREMENT BY 1;
CREATE OR REPLACE
TRIGGER xxcust_items_conv_trg
BEFORE
INSERT
ON apps.xxcust_items_conv_stg
FOR
EACH ROW
BEGIN
IF :NEW.transaction_id_stg
IS
NULL
THEN
SELECT xxcust_items_conv_s.NEXTVAL,'N',
SYSDATE
INTO :NEW.transaction_id_stg,
:NEW.status_stg,
:NEW.creation_date
FROM DUAL;
END IF;
END;
Step3:
Create the control file with extension as “.ctl” to load the data into staging
table
OPTIONS (SKIP=1)
LOAD DATA
Infile 'Items.csv'
Truncate
into table xxcust_items_conv_stg
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
(
LS_ITEM_NUMBER "ltrim(rtrim(:LS_ITEM_NUMBER))"
,LS_DESCRIPTION "ltrim(rtrim(:LS_DESCRIPTION))"
,LS_ORGANIZATION_CODE "ltrim(rtrim(:LS_ORGANIZATION_CODE))"
,LS_PR_UNIT_OF_MEASURE "ltrim(rtrim(:LS_PR_UNIT_OF_MEASURE))"
,LS_TEMPLATE_NAME "ltrim(rtrim(:LS_TEMPLATE_NAME))"
,LS_COS_ACCOUNT "ltrim(rtrim(:LS_COS_ACCOUNT))"
,LS_EXPENSE_ACCOUNT "ltrim(rtrim(:LS_EXPENSE_ACCOUNT))"
,LS_SALES_ACCOUNT "ltrim(rtrim(:LS_SALES_ACCOUNT))"
)
Step4:
Upload the Control file and data file into server $XXCUST_TOP/bin location
(/u02/E
BIZ/apps/apps_st/appl/XXCUST/12.0.0/bin)
Step5:
Execute the below command to load the data into staging table
sqlldr
control=XXCUST_ITEMS_CONV.ctl
Enter
the DB user name and Password : apps/apps
Step6:
Verify the staging table data is inserted or not (Make sure to insert the all
record in data file if any records are not inserted verify and insert
again)
Step7: Create the Specification and package body
with all the validations
CREATE OR REPLACE PACKAGE xxcust_items_conv_pkg
AS
/*
*************************************************************************
** Business Object: ITEMS,
** File Name:
xxcust_items_conv_PKG.PKS
** File Type: PACKAGE
SPECIFICATION
** Purpose: CONVERT LEGACY
ITEMS DATA INTO ORACLE
*************************************************************************
*/
PROCEDURE
MAIN (p_retmsg OUT VARCHAR2, p_retcode
OUT NUMBER);
PROCEDURE MAP;
PROCEDURE
VALIDATE;
PROCEDURE LOAD;
END xxcust_items_conv_pkg;
/
CREATE OR REPLACE
PACKAGE BODY xxcust_items_conv_pkg
AS
PROCEDURE MAIN (p_retmsg
OUT VARCHAR2, p_retcode
OUT NUMBER)
AS
BEGIN
fnd_file.put_line
(fnd_file.LOG,'+-----------------------------------------------------------+');
fnd_file.put_line
(fnd_file.LOG, 'Starting
MAP Procedure ');
MAP;
fnd_file.put_line
(fnd_file.LOG,'+-----------------------------------------------------------+');
fnd_file.put_line
(fnd_file.LOG, 'Starting
VALIDATE Procedure ');
VALIDATE;
fnd_file.put_line
(fnd_file.LOG,'+-----------------------------------------------------------+');
fnd_file.put_line
(fnd_file.LOG, 'Starting
LOAD Procedure ');
LOAD;
END;
/*
** Business Object:
ORACLE ITEMS FOR CONVERSION
** File Name:
xxcust_items_conv_PKG.PKB
** File Type:
PACKAGE BODY
** Purpose: TO
CONVERT THE ITEMS DATA INTO ORACLE
*************************************************************************
*/
/*
**------------------------------------------------------------------------
** Procedure
Name: MAP
** Purpose
:Procedure to map the staging table fields.
**------------------------------------------------------------------------
*/
PROCEDURE
MAP
IS
BEGIN
/*==========================================================================
=================
Default
Organization Code to V1
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
SET ls_organization_code
= 'V1';
COMMIT;
/*==========================================================================
=================
Update Status of
the Duplicate Records (Records given in data file and already present in
Oracle)
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
a
SET status_stg
= 'ME',
ERROR_CODE
=
ERROR_CODE
|| 'Duplicate
Combination of Item and Organization;'
WHERE
ls_item_number
IN
(SELECT segment1
FROM mtl_system_items_b
m
WHERE m.segment1
= a.ls_item_number
AND
m.organization_id IN
(SELECT organization_id
FROM mtl_parameters
p
WHERE
p.organization_code
IN ('V1')));
COMMIT;
/*==========================================================================
=================
Default Cost of
Sales Account
============================================================================ ==============*/
UPDATE xxcust_items_conv_stg
SET ls_cos_account
= '01-600-5110-0000-000';
COMMIT;
/*==========================================================================
=================
Default Expense Account
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
SET ls_expense_account
= '01-000-2440-0000-000';
COMMIT;
/*==========================================================================
=================
Default
Sales Account
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
SET ls_sales_account
= '01-110-9110-0000-000';
COMMIT;
/*==========================================================================
=================
Default
Template Name to 'Finish Good' if it is NULL
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
SET ls_template_name
= 'Finished
Good'
WHERE
ls_template_name
IS
NULL;
COMMIT;
/*==========================================================================
=================
Default Primary Unit Of
Measure
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
SET ls_pr_unit_of_measure
= 'Each'
WHERE
ls_pr_unit_of_measure
IS
NULL;
COMMIT;
/*==========================================================================
=================
Update Status of
Records that are successfully mapped
============================================================================ ==============*/
UPDATE
xxcust_items_conv_stg
SET status_stg
= 'M'
WHERE
status_stg
= 'N';
COMMIT;
END MAP;
/*
**------------------------------------------------------------------------
** Procedure Name: VALIDATE
** Purpose :Procedure to
validate the staging table fields.
**------------------------------------------------------------------------
*/
PROCEDURE
VALIDATE
IS
l_organization_id
NUMBER;
l_template_id
NUMBER;
l_planner_code
VARCHAR2
(50);
l_buyer_id
NUMBER;
l_subinv_name
VARCHAR2
(100);
l_sa_id
NUMBER;
l_cogs_id
NUMBER;
l_primary_uom
mtl_units_of_measure.uom_code%TYPE;
l_buyer_name
po_agents_name_v.full_name%TYPE;
l_buyer_id
po_agents_name_v.buyer_id%TYPE;
l_planner_code
mtl_planners.planner_code%TYPE;
l_cos_account_id
NUMBER;
l_expense_account_id
NUMBER;
l_sales_account_id
NUMBER;
CURSOR cur_org_code
IS
SELECT DISTINCT ls_organization_code
FROM xxcust_items_conv_stg
WHERE
ls_organization_code
IS
NOT NULL;
CURSOR cur_uom_code
IS
SELECT DISTINCT ls_pr_unit_of_measure
FROM xxcust_items_conv_stg;
CURSOR cur_item_template
IS
SELECT DISTINCT ls_template_name
FROM xxcust_items_conv_stg
WHERE
ls_template_name
IS
NOT NULL;
CURSOR cur_item_cos_account
IS
SELECT DISTINCT ls_cos_account
FROM xxcust_items_conv_stg
WHERE
1 = 1;
CURSOR
cur_item_expense_account
Oracle Conversion Process With Example is
SELECT DISTINCT ls_expense_account
FROM xxcust_items_conv_stg
WHERE
1 = 1;
CURSOR cur_item_sales_account
IS
SELECT DISTINCT ls_sales_account
FROM xxcust_items_conv_stg
WHERE
1 = 1;
BEGIN
/*==========================================================================
=================
Validation
for Organization if it is null
============================================================================ ==============*/
BEGIN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
=
ERROR_CODE
|| 'Organization
Code Cannot Be NULL;;'
WHERE
ls_organization_code
IS
NULL;
EXCEPTION
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
=
ERROR_CODE
|| 'Organization
Code Cannot Be NULL;;'
WHERE
ls_organization_code
IS
NULL;
END;
COMMIT;
/*==========================================================================
=================
Validation for Organization
id
============================================================================ ==============*/
FOR org_code_rec
IN cur_org_code
LOOP
BEGIN
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE
organization_code
= org_code_rec.ls_organization_code;
UPDATE xxcust_items_conv_stg
SET organization_id
= l_organization_id
WHERE
ls_organization_code
= org_code_rec.ls_organization_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
SET status_stg
= 'VE', UPDATE
xxcust_items_conv_stg
Oracle Conversion Process
ERROR_CODE =
ERROR_CODE
|| 'Organization Code does
not exist in Oracle;;'
WHERE
ls_organization_code
=
org_code_rec.ls_organization_code;
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
=
ERROR_CODE
|| 'Organization Code does
not exist in Oracle;;'
WHERE
ls_organization_code
=
org_code_rec.ls_organization_code;
END;
END LOOP;
COMMIT;
/*==========================================================================
=================
Validation for Primary UOM
Code
============================================================================ ==============*/
FOR uom_code_rec
IN cur_uom_code
LOOP
BEGIN
SELECT uom_code
INTO l_primary_uom
FROM mtl_units_of_measure
WHERE
NVL (disable_date, SYSDATE + 1)
> SYSDATE
AND unit_of_measure
=
LTRIM (RTRIM (uom_code_rec.ls_pr_unit_of_measure));
UPDATE xxcust_items_conv_stg
SET pr_uom_code
= l_primary_uom
WHERE
ls_pr_unit_of_measure
= uom_code_rec.ls_pr_unit_of_measure;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
Primary UOM Code;;'
WHERE
ls_pr_unit_of_measure
=
uom_code_rec.ls_pr_unit_of_measure;
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
Primary UOM Code;;'
WHERE
ls_pr_unit_of_measure
=
uom_code_rec.ls_pr_unit_of_measure;
END;
END LOOP;
COMMIT;
/*==========================================================================
=================
Validation
for Template id
============================================================================ ==============*/
FOR item_template_rec
IN cur_item_template
LOOP
BEGIN
SELECT template_id
INTO l_template_id
FROM mtl_item_templates
WHERE
template_name
= item_template_rec.ls_template_name;
UPDATE xxcust_items_conv_stg
SET template_id
= l_template_id
WHERE
ls_template_name
= item_template_rec.ls_template_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
Template id;;'
WHERE
ls_template_name
= item_template_rec.ls_template_name;
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
Template id;;'
WHERE
ls_template_name
= item_template_rec.ls_template_name;
END;
END LOOP;
COMMIT;
/*==========================================================================
=================
Validation for COS Account
============================================================================ ==============*/
FOR item_cos_account_rec
IN cur_item_cos_account
LOOP
IF item_cos_account_rec.ls_cos_account
IS
NOT NULL
THEN
BEGIN
SELECT code_combination_id
INTO l_cos_account_id
FROM gl_code_combinations_kfv
WHERE
1 = 1 AND chart_of_accounts_id
= 101
AND concatenated_segments
=
item_cos_account_rec.ls_cos_account
AND enabled_flag
= 'Y';
Oracle Conversion Process
UPDATE xxcust_items_conv_stg
SET cos_account_id
= l_cos_account_id
WHERE
ls_cos_account
= item_cos_account_rec.ls_cos_account;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
COS Account;;'
WHERE
ls_cos_account
= item_cos_account_rec.ls_cos_account;
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
COS Account;;'
WHERE
ls_cos_account
= item_cos_account_rec.ls_cos_account;
END;
END IF;
END LOOP;
COMMIT;
/*==========================================================================
=================
Validation
for Expense Account
============================================================================ ==============*/
FOR item_expense_account_rec
IN cur_item_expense_account
LOOP
IF item_expense_account_rec.ls_expense_account
IS
NOT NULL
THEN
BEGIN
SELECT code_combination_id
INTO l_expense_account_id
FROM gl_code_combinations_kfv
WHERE
1 = 1 AND chart_of_accounts_id
= 101
AND concatenated_segments
=
item_expense_account_rec.ls_expense_account
AND enabled_flag
= 'Y';
UPDATE xxcust_items_conv_stg
SET expense_account_id
= l_expense_account_id
WHERE
ls_expense_account
=
item_expense_account_rec.ls_expense_account;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
=
ERROR_CODE
|| 'Invalid
Expense Account;;'
WHERE
ls_expense_account
=
item_expense_account_rec.ls_expense_account;
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
=
ERROR_CODE
|| 'Invalid
Expense Account;;'
WHERE
ls_expense_account
=
item_expense_account_rec.ls_expense_account;
END;
END IF;
END LOOP;
COMMIT;
/*==========================================================================
=================
Validation
for Sales Account
============================================================================ ==============*/
FOR item_sales_account_rec
IN cur_item_sales_account
LOOP
BEGIN
SELECT code_combination_id
INTO l_sales_account_id
FROM gl_code_combinations_kfv
WHERE
enabled_flag
= 'Y'
AND concatenated_segments
=
item_sales_account_rec.ls_sales_account;
--AND
organization_id = item_subinv_rec.organization_id;
UPDATE
xxcust_items_conv_stg
SET sales_account_id
= l_sales_account_id
WHERE
ls_sales_account
= item_sales_account_rec.ls_sales_account;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
Sales Account;;'
WHERE
ls_sales_account
=
item_sales_account_rec.ls_sales_account;
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'VE',
ERROR_CODE
= ERROR_CODE
|| 'Invalid
Sales Account;;'
WHERE
ls_sales_account
=
item_sales_account_rec.ls_sales_account;
END;
END LOOP;
COMMIT;
UPDATE xxcust_items_conv_stg
SET status_stg
= 'V'
WHERE
status_stg
= 'M';
COMMIT;
END VALIDATE;
/*
**------------------------------------------------------------------------
** Procedure Name: LOAD
** Purpose :Procedure to
write the errored data into the errors table.
**------------------------------------------------------------------------
*/
PROCEDURE
LOAD
IS
l_transaction_type
VARCHAR2
(10) := 'CREATE';
l_process_flag
NUMBER
:= 1;
l_user_id
NUMBER;
l_creation_date
DATE :=
SYSDATE;
l_last_update_date
DATE :=
SYSDATE;
l_organization_id
NUMBER;
CURSOR cur_items
IS
SELECT *
FROM xxcust_items_conv_stg
WHERE
status_stg
= 'V';
BEGIN
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE
user_name
= 'OPERATIONS';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_user_id
:= 0;
END;
FOR items_rec
IN cur_items
LOOP
BEGIN
INSERT INTO mtl_system_items_interface
(creation_date,
created_by,
segment1,
description,
organization_id,
primary_uom_code,
template_id,
cost_of_sales_account,
expense_account,
sales_account,
last_update_date,
last_updated_by,
process_flag,
set_process_id,
transaction_type,
attribute14)
VALUES (SYSDATE,
l_user_id,
items_rec.ls_item_number,
items_rec.ls_description,
Oracle
Conversion
items_rec.organization_id,
items_rec.pr_uom_code,
items_rec.template_id,
items_rec.cos_account_id,
items_rec.expense_account_id,
items_rec.sales_account_id,
l_last_update_date,
l_user_id,
l_process_flag,
112,
--MOD
(items_rec.transaction_id_stg, 5),
l_transaction_type,
items_rec.transaction_id_stg);
EXCEPTION
WHEN OTHERS
THEN
UPDATE xxcust_items_conv_stg
SET status_stg
= 'LE'
WHERE
status_stg
= 'V'
AND transaction_id_stg
= items_rec.transaction_id_stg;
COMMIT;
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
UPDATE xxcust_items_conv_stg
SET status_stg
= 'L'
WHERE
status_stg
= 'V';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line
(fnd_file.LOG,
'Unexpected
error occurred in load procedure ->');
END LOAD;
END xxcust_items_conv_pkg;
/
Step8:
Compile the package in APPS Schema
Step9:
Create the Executable with above package
Nav
: Application Developer 🡪Concurrent 🡪Executable
Enter the below values:
|
Field |
Value |
|
Executable |
XXCUST_ITEMS_CONV |
|
Short
Name |
XXCUST_ITEMS_CONV |
|
Application |
Custom
Development |
|
Execution
Method |
PL/SQL
Stored Procedure |
|
Execution
File Name |
XXCUST_ITEMS_CONV_PKG.MAIN |
Step10: Create the Concurrent Program with above
Executable
Nav
: Application Developer 🡪Concurrent 🡪Program
Enter the below values:
|
Field |
Value |
|
Program |
XXCUST
Item Conversion Program |
|
Short
Name |
XXCUST_ITEMS_CONV |
|
Application |
Custom
Development |
|
Executable
Name |
XXCUST_ITEMS_CONV |
|
Parameters |
We
are not passing any parameters |
Step11:
Assign the concurrent program to Request group
Nav
: System Administrator 🡪Security 🡪Responsibility
🡪Request
Query
the request group : All Inclusive GUI and add the concurrent
program
|
Field |
Value |
|
Group |
All
Inclusive GUI |
|
Application |
Inventory |
|
Program |
XXCUST
Item Conversion Program |
Step12: Goto the Inventory responsibility and run
the submit the concurrent program to validate the data and insert into
interface table
Nav
: Inventory, Vision Operations (USA) 🡪 View 🡪Requests
🡪Submit New Request 🡪Single
Request
Oracle Conversion Proces verify the staging table and interface table
Step14: Once data loaded into interface table
submit item interface program insert the data into staging table Nav : Inventory, Vision Operations (USA) 🡪 Items 🡪Import
🡪Import Items
Select the Organization and submit the
program
completed check the base table and application to
all the fields are populated or not
select * from mtl_system_items_b
where 1 = 1
and creation_date >
sysdate-1
order by segment1
desc
Query the loaded item :
Nav
: Inventory, Vision Operations (USA) 🡪 Items 🡪Master
Items
0 Comments