ITEM CONVERSION IN ORACLE INVENTORY MODULE
ITEM CONVERSION IN ORACLE INVENTORY MODULE WITH CODE OR CONVERT LEGACY ITEMS DATA INTO ORACLE APPS
/****************************************************************
file_name : xx_item_con_create_objects.sql
description : conversion staging table and error table
object names : creating conversion table,synonyms,sequences,indexes and triggers
for loading the object data from the flat files to the staging tables.
*****************************************************************/
creation of staging table
create table xxaa_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)
);
create sequence xxaa_item_conv_s
start with 1
increment by 1;
create or replace trigger xxaa_items_conv_trg
before insert
on item_con_stg
for each row
begin
if :new.transaction_id_stg is null
then
select xxaa_item_conv_s.nextval, 'N'
into :new.transaction_id_stg, :new.status_stg
from dual;
end if;
end;
/
/****************************************************************
description : conversion error table
*****************************************************************/
create unique index xxaa_items_conv_cu01 on xxaa_items_conv_stg(transaction_id_stg);
create index xxaa_items_conv_cn01 on xxaa_items_conv_stg(status_stg);
create index xxaa_items_conv_cn02 on xxaa_items_conv_stg(ls_item_number);
create index xxaa_items_conv_cn03 on xxaa_items_conv_stg(ls_item_number,ls_organization_code);
create sequence xxaa_items_conv_s
start with 1
increment by 1;
desc xxaa_items_conv_stg
create or replace trigger xxaa_items_conv_trg
before insert
on xxaa_items_conv_stg
for each row
begin
if :new.transaction_id_stg is null
then
select xxaa_items_conv_s.nextval,'N',
sysdate
into :new.transaction_id_stg, :new.status_stg,
:new.creation_date
from dual;
end if;
end;
create table xxaa_items_err_log
(application varchar (80),
file_name varchar (80),
object_name varchar (80),
package_name varchar (50),
procedure_name varchar (50),
table_name varchar (50),
column_name varchar (50),
column_value varchar (2000),
error_date date,
error_message varchar (2000),
transaction_id_stg number
);
drop synonym xxaa_items_err_log;
create synonym xxaa_items_err_logs for xxaa_items_err_log;
commit ;
staging table data for item conversion
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
creation of error tables data
/****************************************************************
file_name : xxaa_err_codes_script.sql
description : error codes script file
object names : items,qoh,boms ,customers,open ar,orders, service contracts,install base,service request
,knowledge base,projects.
*****************************************************************/
insert into xxaa_error_codes
values ('CUS100', 'Customer Number Is Null.');
insert into xxaa_error_codes
values ('CUS101', 'Customer Name Is Null.');
insert into xxaa_error_codes
values ('CUS102', 'Party Type Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS103', 'Profile Class Code Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS104', 'Primary Salesrep Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS105', 'Freight Terms Code Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS106', 'Order Type Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS107', 'Ship Method Code Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS108', 'Customer Site Cannot Be Without Customer Header.');
insert into xxaa_error_codes
values ('CUS109', 'Customer Address Line 1 Cannot be Null.');
insert into xxaa_error_codes
values ('CUS110', 'Customer Address City Cannot be Null.');
insert into xxaa_error_codes
values ('CUS111', 'Customer Address State Cannot be Null.');
insert into xxaa_error_codes
values ('CUS112', 'Customer Address County Cannot be Null.');
insert into xxaa_error_codes
values ('CUS113', 'Customer Address Postal Code Cannot be Null.');
insert into xxaa_error_codes
values ('CUS114', 'Customer Address Bill To Flag Cannot be Null.');
insert into xxaa_error_codes
values ('CUS115', 'Customer Address Ship To Flag Cannot be Null.');
insert into xxaa_error_codes
values ('CUS116', 'Customer Address Primary Flag Cannot be Null.');
insert into xxaa_error_codes
values ('CUS117', 'Customer Name Does not exist in Oracle.');
insert into xxaa_error_codes
values ('CUS118', 'Customer Number Does not exist in Oracle.');
insert into xxaa_error_codes
values ('CUS119', 'Customer Number Does not exist in Sites Legacy file.');
insert into xxaa_error_codes
values ('CUS120', 'Customer Class Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS121', 'FOB Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS122', 'Customer Address Country cannot be Null.');
insert into xxaa_error_codes
values ('CUS123', 'Customer Type Does not Exists in Oracle.');
insert into xxaa_error_codes
values ('CUS124', 'Party Xref Id Cannot Be Null');
insert into xxaa_error_codes
values ('CUS125', 'Site Xref Id Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS126', 'Customer Already Exists In Oracle');
insert into xxaa_error_codes
values ('CUS127', 'Secondary Party Xref Id Cannot be Null');
insert into xxaa_error_codes
values ('CUS128', 'Relationship Type Doesnot Exists in Oracle.');
insert into xxaa_error_codes
values ('CUS129', 'Party Xref Id Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS130', 'Secondary Party Xref Id Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS131', 'Duplicate Party Xref Id Present in Data File.');
insert into xxaa_error_codes
values ('CUS132', 'Duplicate Site Xref Id Present in Data File.');
insert into xxaa_error_codes
values ('CUS134', 'Contact Points Without Contact Record.');
insert into xxaa_error_codes
values ('CUS135',
'Duplicate Combination of Party Site Xref Id and Contact Id .');
insert into xxaa_error_codes
values ('CUS136',
'Duplicate Combination of Contact Id and Contact Detail Id.');
insert into xxaa_error_codes
values ('CUS137', 'Contact Id Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS138',
'Both Contact First Name and Contact Last Name Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS141', 'Contact Detail Id Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS142', 'Phone Number Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS143', 'Phone Line Type Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS146', 'Phone Line Type Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS148',
'No Party Site Id Exists in Oracle with Given Party Site Xref Id.');
insert into xxaa_error_codes
values ('CUS149',
'No Cust Acct Site Id and Cust Account Id Exists with the Party Site Id.');
insert into xxaa_error_codes
values ('CUS150',
'Revenue Code Combination Segments Deos Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS151', 'Email Address Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS152', 'Party Header Without Party Site Record.');
insert into xxaa_error_codes
values ('CUS153', 'Party Site Without Party Header Record.');
insert into xxaa_error_codes
values ('CUS154', 'Corresponding Party Site Record Failed Validation.');
insert into xxaa_error_codes
values ('CUS155', 'Corresponding Party Record Failed Validation.');
insert into xxaa_error_codes
values ('CUS156',
'Duplicate Combination of Party Xref Id and Secondaty Party Xref Id.');
insert into xxaa_error_codes
values ('CUS157', 'Party Site Xref Id Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS158',
'Corresponding Contact Point Record Failed Validation.');
insert into xxaa_error_codes
values ('CUS159', 'Corresponding Contact Record Failed Validation.');
--
commit ;
insert into xxaa_error_codes
values ('ITM102', 'Primary Unit Of Measure Code Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM103', 'Primary Unit of Measure does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM105',
'Weight unit of Measure code does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM106',
'Volumne unit of Measure code does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM109', 'Cost Of Sales Account Segments Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM110',
'Combination of Cost Sales Account Segments is Invalid.');
insert into xxaa_error_codes
values ('ITM111', 'Sales Account Segments Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM112', 'Combination of Sales Account Segments is Invalid.');
insert into xxaa_error_codes
values ('ITM113', 'Expense Account Segments Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM114', 'Combination of Expense Account Segments is Invalid.');
insert into xxaa_error_codes
values ('ITM115', 'Item Catalog Group does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM116', 'Item Number does not exist in the Org.');
insert into xxaa_error_codes
values ('ITM117', 'Item Description Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM121', 'Duplicate Combination of Item and Organization');
insert into xxaa_error_codes
values ('ITM122', 'Cost Type Cannot Be Null');
insert into xxaa_error_codes
values ('ITM123', 'Cost Type Does not Exists in Oracle');
insert into xxaa_error_codes
values ('ITM100', 'Item Number Cannot Be NULL.');
insert into xxaa_error_codes
values ('ITM101', 'Item Number Does Not Exists In Master Org.');
insert into xxaa_error_codes
values ('ITM104',
'Subinventory code is either NULL OR Invalid for the given organization.');
insert into xxaa_error_codes
values ('ITM107', 'Template cannot be NULL.');
insert into xxaa_error_codes
values ('ITM108', 'Template does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM118', 'Planner Code Does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM119', 'Buyer Id Does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM120', 'Transaction UOM Does not Exist in Oracle.');
insert into xxaa_error_codes
values ('ITM124', 'Item Category Cannot Be Null');
insert into xxaa_error_codes
values ('ITM125', 'Item Category Does not Exists in Oracle');
insert into xxaa_error_codes
values ('ITM126',
'Combination of Locator Segments Does Not Exists In Oracle');
insert into xxaa_error_codes
values ('ITM127', 'Transaction Quantity Cannot be Zero or Negetive');
insert into xxaa_error_codes
values ('ITM128',
'Serial Number is not required as the Item is not under Serial Control');
insert into xxaa_error_codes
values ('ITM129',
'Serial Number is required as the Item is under Serial Control');
insert into xxaa_error_codes
values ('ITM131', 'COS Account Does not Exists In Oracle');
insert into xxaa_error_codes
values ('ITM132', 'Sales Account Does not Exists In Oracle');
commit ;
insert into xxaa_error_codes
values ('ORG100', 'Organization Code Cannot Be NULL.');
insert into xxaa_error_codes
values ('ORG101', 'Organization Code does not exist in Oracle.');
insert into xxaa_error_codes
values ('OPU100', 'Operating Unit cannot be NULL.');
insert into xxaa_error_codes
values ('OPU101', 'Operating Unit is invalid.');
commit ;
commit ;
creation of package specification.
create or replace package xxaa_item_con_pkg
as
/*
***************************************************************************
**business objects :items
**file name :xxaa_items_conv_pkg.pks
**file type :package specification
**version :1
**purpose :convert legacy items data into oracle
****************************************************************************
*/
procedure map(p_transaction_id_from number,
p_transaction_id_to number);
procedure validate(p_transaction_id_from number,
p_transaction_id_to number);
procedure load(p_transaction_id_from number,
p_transaction_id_to number);
procedure error_log_write;
end xxaa_items_conv_pkg;
implementation of pacakge body.
create or replace package body xxaa_items_conv_pkg
as
/*
*************************************************************************
** business object: oracle items for conversion
** file name: xxaa_items_conv_pkg.pkb
** file type: package body
** version: 1
** purpose: to convert the items data into oracle
*************************************************************************
*/
g_application_name varchar2 (80);
g_file_name varchar2 (80);
g_object_name varchar2 (80);
g_package_name varchar2 (80);
g_procedure_name varchar2 (80);
g_table_name varchar2 (80);
g_column_name varchar2 (80);
g_column_value varchar2 (1000);
g_error_date date default sysdate;
g_error_message varchar2 (1000);
g_sql_code varchar2 (100);
g_sql_message varchar2 (2000);
g_global_print_mode number;
g_transaction_id number;
/*
**------------------------------------------------------------------------
** procedure name: map
** purpose :procedure to map the staging table fields.
**------------------------------------------------------------------------
*/
procedure map (p_transaction_id_from number, p_transaction_id_to number)
is
begin
/*===================================================
default organization code to v1
====================================================*/
update xxaa_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 xxaa_items_conv_stg a
set status_stg = 'ME',
error_code = error_code || 'ITM121;;'
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')))
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
commit;
/*===============================================
default cost of sales account
================================================*/
update xxaa_items_conv_stg
set ls_cos_account = '01-600-5110-0000-000';
commit;
/*===============================================
default expense account
==============================================*/
update xxaa_items_conv_stg
set ls_expense_account = '01-000-2440-0000-000';
commit;
/*================================================
default sales account
==================================================*/
update xxaa_items_conv_stg
set ls_sales_account = '01-110-9110-0000-000';
commit;
/*=================================================
default template name to 'finish good' if it is null
==================================================*/
update xxaa_items_conv_stg
set ls_template_name = 'Finished Good'
where ls_template_name is null;
commit;
/*===============================================
default primary unit of measure
================================================*/
update xxaa_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 xxaa_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 (p_transaction_id_from number, p_transaction_id_to number)
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 xxaa_items_conv_stg
where ls_organization_code is not null
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_uom_code
is
select distinct ls_pr_unit_of_measure
from xxaa_items_conv_stg
where transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_template
is
select distinct ls_template_name
from xxaa_items_conv_stg
where ls_template_name is not null
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_cos_account
is
select distinct ls_cos_account
from xxaa_items_conv_stg
where 1 = 1
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_expense_account
is
select distinct ls_expense_account
from xxaa_items_conv_stg
where 1 = 1
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_sales_account
is
select distinct ls_sales_account
from xxaa_items_conv_stg
where 1 = 1
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
begin
g_application_name := 'INVENTORY';
g_object_name := 'ITEMS';
g_package_name := 'XXAA_ITEMS_CONV_PKG';
g_procedure_name := 'VALIDATE';
g_table_name := 'XXAA_ITEMS_CONV_STG';
g_file_name := 'Items.csv';
/*======================================
validation for organization if it is null
======================================*/
begin
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ORG100;;'
where ls_organization_code is null
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when others
then
g_column_name := 'LS ORGANIZATION CODE';
g_error_message := 'Organization code is null' || sqlerrm;
error_log_write;
end;
commit;
/*==================================================
validation for organization id
==================================================*/
for org_code_rec in cur_org_code
loop
begin
g_column_name := 'LS ORGANIZATION CODE';
g_column_value := org_code_rec.ls_organization_code;
select organization_id
into l_organization_id
from mtl_parameters
where organization_code = org_code_rec.ls_organization_code;
update xxaa_items_conv_stg
set organization_id = l_organization_id
where ls_organization_code = org_code_rec.ls_organization_code
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ORG101;;'
where ls_organization_code = org_code_rec.ls_organization_code
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_organization_code = org_code_rec.ls_organization_code
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :='Unexpected error occurred in organization validation ->'|| sqlerrm;
error_log_write;
end;
end loop;
commit;
/*==========================================
validation for primary uom code
=======================================*/
for uom_code_rec in cur_uom_code
loop
begin
g_column_name := 'LS_PR_UNIT_OF_MEASURE';
g_column_value := uom_code_rec.ls_pr_unit_of_measure;
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 xxaa_items_conv_stg
set pr_uom_code = l_primary_uom
where ls_pr_unit_of_measure = uom_code_rec.ls_pr_unit_of_measure
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM130;;'
where ls_pr_unit_of_measure =
uom_code_rec.ls_pr_unit_of_measure
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_pr_unit_of_measure =
uom_code_rec.ls_pr_unit_of_measure
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Primary UOM validation ->'
|| sqlerrm;
error_log_write;
end;
end loop;
commit;
/*==========================================
validation for template id
============================================*/
for item_template_rec in cur_item_template
loop
begin
g_column_name := 'LS TEMPLATE NAME';
g_column_value := item_template_rec.ls_template_name;
select template_id
into l_template_id
from mtl_item_templates
where template_name = item_template_rec.ls_template_name;
update xxaa_items_conv_stg
set template_id = l_template_id
where ls_template_name = item_template_rec.ls_template_name
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM108;;'
where ls_template_name = item_template_rec.ls_template_name
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_template_name = item_template_rec.ls_template_name
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Template Name validation ->'
|| sqlerrm;
error_log_write;
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
g_column_name := 'LS COS ACCOUNT';
g_column_value := item_cos_account_rec.ls_cos_account;
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';
update xxaa_items_conv_stg
set cos_account_id = l_cos_account_id
where ls_cos_account = item_cos_account_rec.ls_cos_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM102;;'
where ls_cos_account = item_cos_account_rec.ls_cos_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_cos_account = item_cos_account_rec.ls_cos_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in COS Account validation ->'
|| sqlerrm;
error_log_write;
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
g_column_name := 'LS EXPENSE ACCOUNT';
g_column_value := item_expense_account_rec.ls_expense_account;
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 xxaa_items_conv_stg
set expense_account_id = l_expense_account_id
where ls_expense_account =
item_expense_account_rec.ls_expense_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM132;;'
where ls_expense_account =
item_expense_account_rec.ls_expense_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_expense_account =
item_expense_account_rec.ls_expense_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Expense Account validation ->'
|| sqlerrm;
error_log_write;
end;
end if;
end loop;
commit;
/*=====================================================
validation for sales account
======================================================*/
for item_sales_account_rec in cur_item_sales_account
loop
begin
g_column_name := 'LS SALES ACCOUNT';
g_column_value := item_sales_account_rec.ls_sales_account;
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 xxaa_items_conv_stg
set sales_account_id = l_sales_account_id
where ls_sales_account = item_sales_account_rec.ls_sales_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM112;;'
where ls_sales_account =
item_sales_account_rec.ls_sales_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_sales_account =
item_sales_account_rec.ls_sales_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Sales Account validation ->'
|| sqlerrm;
error_log_write;
end;
end loop;
commit;
update xxaa_items_conv_stg
set status_stg = 'V'
where status_stg = 'M'
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
commit;
end validate;
/*
**------------------------------------------------------------------------
** procedure name: load
** purpose :procedure to write the errored data into the errors table.
**------------------------------------------------------------------------
*/
procedure load (p_transaction_id_from number, p_transaction_id_to number)
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 xxaa_items_conv_stg
where status_stg = 'V'
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
begin
g_application_name := 'INVENTORY';
g_object_name := 'ITEMS';
g_package_name := 'XXAA_ITEMS_CONV_PKG';
g_procedure_name := 'LOAD';
g_table_name := 'XXAA_ITEMS_CONV_STG';
g_file_name := 'Items.csv';
begin
select user_id
into l_user_id
from fnd_user
where user_name = 'TESTING';
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,
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 xxaa_items_conv_stg
set status_stg = 'LE'
where status_stg = 'V'
and transaction_id_stg = items_rec.transaction_id_stg;
commit;
g_error_message :='Unexpected error occurred in load procedure ->' || sqlerrm;
dbms_output.put_line (sqlerrm);
error_log_write;
end;
end loop;
update xxaa_items_conv_stg
set status_stg = 'L'
where status_stg = 'V'
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
commit;
exception
when others
then
g_error_message :='Unexpected error occurred in load procedure ->' || sqlerrm;
error_log_write;
end load;
/*
**------------------------------------------------------------------------
** procedure name: error_log_write
** purpose :procedure to write the errored data into the errors table.
**------------------------------------------------------------------------
*/
procedure error_log_write
is
begin
insert into xxaa_items_err_log
(application, file_name, object_name,
package_name, procedure_name, table_name,
column_name, column_value, error_date,
error_message, transaction_id_stg
)
values (g_application_name, g_file_name, g_object_name,
g_package_name, g_procedure_name, g_table_name,
g_column_name, g_column_value, g_error_date,
g_error_message, g_transaction_id
);
commit;
exception
when others
then
fnd_file.put_line (fnd_file.log, 'ERROR_LOG_WRITE#' || sqlerrm);
dbms_output.put_line ('ERROR_LOG_WRITE#' || sqlerrm);
end error_log_write;
/* procedure val_error_rep is
begin
null;
end val_error_rep;
procedure load_error_rep is
begin
null;
end load_error_rep;*/
end xxaa_items_conv_pkg;
ITEM CONVERSION IN ORACLE INVENTORY MODULE WITH CODE OR CONVERT LEGACY ITEMS DATA INTO ORACLE APPS
/****************************************************************
file_name : xx_item_con_create_objects.sql
description : conversion staging table and error table
object names : creating conversion table,synonyms,sequences,indexes and triggers
for loading the object data from the flat files to the staging tables.
*****************************************************************/
creation of staging table
create table xxaa_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)
);
create sequence xxaa_item_conv_s
start with 1
increment by 1;
create or replace trigger xxaa_items_conv_trg
before insert
on item_con_stg
for each row
begin
if :new.transaction_id_stg is null
then
select xxaa_item_conv_s.nextval, 'N'
into :new.transaction_id_stg, :new.status_stg
from dual;
end if;
end;
/
/****************************************************************
description : conversion error table
*****************************************************************/
create unique index xxaa_items_conv_cu01 on xxaa_items_conv_stg(transaction_id_stg);
create index xxaa_items_conv_cn01 on xxaa_items_conv_stg(status_stg);
create index xxaa_items_conv_cn02 on xxaa_items_conv_stg(ls_item_number);
create index xxaa_items_conv_cn03 on xxaa_items_conv_stg(ls_item_number,ls_organization_code);
create sequence xxaa_items_conv_s
start with 1
increment by 1;
desc xxaa_items_conv_stg
create or replace trigger xxaa_items_conv_trg
before insert
on xxaa_items_conv_stg
for each row
begin
if :new.transaction_id_stg is null
then
select xxaa_items_conv_s.nextval,'N',
sysdate
into :new.transaction_id_stg, :new.status_stg,
:new.creation_date
from dual;
end if;
end;
create table xxaa_items_err_log
(application varchar (80),
file_name varchar (80),
object_name varchar (80),
package_name varchar (50),
procedure_name varchar (50),
table_name varchar (50),
column_name varchar (50),
column_value varchar (2000),
error_date date,
error_message varchar (2000),
transaction_id_stg number
);
drop synonym xxaa_items_err_log;
create synonym xxaa_items_err_logs for xxaa_items_err_log;
commit ;
staging table data for item conversion
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
creation of error tables data
/****************************************************************
file_name : xxaa_err_codes_script.sql
description : error codes script file
object names : items,qoh,boms ,customers,open ar,orders, service contracts,install base,service request
,knowledge base,projects.
*****************************************************************/
insert into xxaa_error_codes
values ('CUS100', 'Customer Number Is Null.');
insert into xxaa_error_codes
values ('CUS101', 'Customer Name Is Null.');
insert into xxaa_error_codes
values ('CUS102', 'Party Type Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS103', 'Profile Class Code Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS104', 'Primary Salesrep Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS105', 'Freight Terms Code Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS106', 'Order Type Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS107', 'Ship Method Code Does Not Exist In Oracle.');
insert into xxaa_error_codes
values ('CUS108', 'Customer Site Cannot Be Without Customer Header.');
insert into xxaa_error_codes
values ('CUS109', 'Customer Address Line 1 Cannot be Null.');
insert into xxaa_error_codes
values ('CUS110', 'Customer Address City Cannot be Null.');
insert into xxaa_error_codes
values ('CUS111', 'Customer Address State Cannot be Null.');
insert into xxaa_error_codes
values ('CUS112', 'Customer Address County Cannot be Null.');
insert into xxaa_error_codes
values ('CUS113', 'Customer Address Postal Code Cannot be Null.');
insert into xxaa_error_codes
values ('CUS114', 'Customer Address Bill To Flag Cannot be Null.');
insert into xxaa_error_codes
values ('CUS115', 'Customer Address Ship To Flag Cannot be Null.');
insert into xxaa_error_codes
values ('CUS116', 'Customer Address Primary Flag Cannot be Null.');
insert into xxaa_error_codes
values ('CUS117', 'Customer Name Does not exist in Oracle.');
insert into xxaa_error_codes
values ('CUS118', 'Customer Number Does not exist in Oracle.');
insert into xxaa_error_codes
values ('CUS119', 'Customer Number Does not exist in Sites Legacy file.');
insert into xxaa_error_codes
values ('CUS120', 'Customer Class Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS121', 'FOB Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS122', 'Customer Address Country cannot be Null.');
insert into xxaa_error_codes
values ('CUS123', 'Customer Type Does not Exists in Oracle.');
insert into xxaa_error_codes
values ('CUS124', 'Party Xref Id Cannot Be Null');
insert into xxaa_error_codes
values ('CUS125', 'Site Xref Id Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS126', 'Customer Already Exists In Oracle');
insert into xxaa_error_codes
values ('CUS127', 'Secondary Party Xref Id Cannot be Null');
insert into xxaa_error_codes
values ('CUS128', 'Relationship Type Doesnot Exists in Oracle.');
insert into xxaa_error_codes
values ('CUS129', 'Party Xref Id Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS130', 'Secondary Party Xref Id Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS131', 'Duplicate Party Xref Id Present in Data File.');
insert into xxaa_error_codes
values ('CUS132', 'Duplicate Site Xref Id Present in Data File.');
insert into xxaa_error_codes
values ('CUS134', 'Contact Points Without Contact Record.');
insert into xxaa_error_codes
values ('CUS135',
'Duplicate Combination of Party Site Xref Id and Contact Id .');
insert into xxaa_error_codes
values ('CUS136',
'Duplicate Combination of Contact Id and Contact Detail Id.');
insert into xxaa_error_codes
values ('CUS137', 'Contact Id Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS138',
'Both Contact First Name and Contact Last Name Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS141', 'Contact Detail Id Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS142', 'Phone Number Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS143', 'Phone Line Type Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS146', 'Phone Line Type Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS148',
'No Party Site Id Exists in Oracle with Given Party Site Xref Id.');
insert into xxaa_error_codes
values ('CUS149',
'No Cust Acct Site Id and Cust Account Id Exists with the Party Site Id.');
insert into xxaa_error_codes
values ('CUS150',
'Revenue Code Combination Segments Deos Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS151', 'Email Address Cannot Be Null.');
insert into xxaa_error_codes
values ('CUS152', 'Party Header Without Party Site Record.');
insert into xxaa_error_codes
values ('CUS153', 'Party Site Without Party Header Record.');
insert into xxaa_error_codes
values ('CUS154', 'Corresponding Party Site Record Failed Validation.');
insert into xxaa_error_codes
values ('CUS155', 'Corresponding Party Record Failed Validation.');
insert into xxaa_error_codes
values ('CUS156',
'Duplicate Combination of Party Xref Id and Secondaty Party Xref Id.');
insert into xxaa_error_codes
values ('CUS157', 'Party Site Xref Id Does Not Exist in Oracle.');
insert into xxaa_error_codes
values ('CUS158',
'Corresponding Contact Point Record Failed Validation.');
insert into xxaa_error_codes
values ('CUS159', 'Corresponding Contact Record Failed Validation.');
--
commit ;
insert into xxaa_error_codes
values ('ITM102', 'Primary Unit Of Measure Code Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM103', 'Primary Unit of Measure does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM105',
'Weight unit of Measure code does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM106',
'Volumne unit of Measure code does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM109', 'Cost Of Sales Account Segments Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM110',
'Combination of Cost Sales Account Segments is Invalid.');
insert into xxaa_error_codes
values ('ITM111', 'Sales Account Segments Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM112', 'Combination of Sales Account Segments is Invalid.');
insert into xxaa_error_codes
values ('ITM113', 'Expense Account Segments Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM114', 'Combination of Expense Account Segments is Invalid.');
insert into xxaa_error_codes
values ('ITM115', 'Item Catalog Group does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM116', 'Item Number does not exist in the Org.');
insert into xxaa_error_codes
values ('ITM117', 'Item Description Cannot Be Null.');
insert into xxaa_error_codes
values ('ITM121', 'Duplicate Combination of Item and Organization');
insert into xxaa_error_codes
values ('ITM122', 'Cost Type Cannot Be Null');
insert into xxaa_error_codes
values ('ITM123', 'Cost Type Does not Exists in Oracle');
insert into xxaa_error_codes
values ('ITM100', 'Item Number Cannot Be NULL.');
insert into xxaa_error_codes
values ('ITM101', 'Item Number Does Not Exists In Master Org.');
insert into xxaa_error_codes
values ('ITM104',
'Subinventory code is either NULL OR Invalid for the given organization.');
insert into xxaa_error_codes
values ('ITM107', 'Template cannot be NULL.');
insert into xxaa_error_codes
values ('ITM108', 'Template does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM118', 'Planner Code Does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM119', 'Buyer Id Does not exist in Oracle.');
insert into xxaa_error_codes
values ('ITM120', 'Transaction UOM Does not Exist in Oracle.');
insert into xxaa_error_codes
values ('ITM124', 'Item Category Cannot Be Null');
insert into xxaa_error_codes
values ('ITM125', 'Item Category Does not Exists in Oracle');
insert into xxaa_error_codes
values ('ITM126',
'Combination of Locator Segments Does Not Exists In Oracle');
insert into xxaa_error_codes
values ('ITM127', 'Transaction Quantity Cannot be Zero or Negetive');
insert into xxaa_error_codes
values ('ITM128',
'Serial Number is not required as the Item is not under Serial Control');
insert into xxaa_error_codes
values ('ITM129',
'Serial Number is required as the Item is under Serial Control');
insert into xxaa_error_codes
values ('ITM131', 'COS Account Does not Exists In Oracle');
insert into xxaa_error_codes
values ('ITM132', 'Sales Account Does not Exists In Oracle');
commit ;
insert into xxaa_error_codes
values ('ORG100', 'Organization Code Cannot Be NULL.');
insert into xxaa_error_codes
values ('ORG101', 'Organization Code does not exist in Oracle.');
insert into xxaa_error_codes
values ('OPU100', 'Operating Unit cannot be NULL.');
insert into xxaa_error_codes
values ('OPU101', 'Operating Unit is invalid.');
commit ;
commit ;
creation of package specification.
create or replace package xxaa_item_con_pkg
as
/*
***************************************************************************
**business objects :items
**file name :xxaa_items_conv_pkg.pks
**file type :package specification
**version :1
**purpose :convert legacy items data into oracle
****************************************************************************
*/
procedure map(p_transaction_id_from number,
p_transaction_id_to number);
procedure validate(p_transaction_id_from number,
p_transaction_id_to number);
procedure load(p_transaction_id_from number,
p_transaction_id_to number);
procedure error_log_write;
end xxaa_items_conv_pkg;
implementation of pacakge body.
create or replace package body xxaa_items_conv_pkg
as
/*
*************************************************************************
** business object: oracle items for conversion
** file name: xxaa_items_conv_pkg.pkb
** file type: package body
** version: 1
** purpose: to convert the items data into oracle
*************************************************************************
*/
g_application_name varchar2 (80);
g_file_name varchar2 (80);
g_object_name varchar2 (80);
g_package_name varchar2 (80);
g_procedure_name varchar2 (80);
g_table_name varchar2 (80);
g_column_name varchar2 (80);
g_column_value varchar2 (1000);
g_error_date date default sysdate;
g_error_message varchar2 (1000);
g_sql_code varchar2 (100);
g_sql_message varchar2 (2000);
g_global_print_mode number;
g_transaction_id number;
/*
**------------------------------------------------------------------------
** procedure name: map
** purpose :procedure to map the staging table fields.
**------------------------------------------------------------------------
*/
procedure map (p_transaction_id_from number, p_transaction_id_to number)
is
begin
/*===================================================
default organization code to v1
====================================================*/
update xxaa_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 xxaa_items_conv_stg a
set status_stg = 'ME',
error_code = error_code || 'ITM121;;'
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')))
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
commit;
/*===============================================
default cost of sales account
================================================*/
update xxaa_items_conv_stg
set ls_cos_account = '01-600-5110-0000-000';
commit;
/*===============================================
default expense account
==============================================*/
update xxaa_items_conv_stg
set ls_expense_account = '01-000-2440-0000-000';
commit;
/*================================================
default sales account
==================================================*/
update xxaa_items_conv_stg
set ls_sales_account = '01-110-9110-0000-000';
commit;
/*=================================================
default template name to 'finish good' if it is null
==================================================*/
update xxaa_items_conv_stg
set ls_template_name = 'Finished Good'
where ls_template_name is null;
commit;
/*===============================================
default primary unit of measure
================================================*/
update xxaa_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 xxaa_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 (p_transaction_id_from number, p_transaction_id_to number)
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 xxaa_items_conv_stg
where ls_organization_code is not null
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_uom_code
is
select distinct ls_pr_unit_of_measure
from xxaa_items_conv_stg
where transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_template
is
select distinct ls_template_name
from xxaa_items_conv_stg
where ls_template_name is not null
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_cos_account
is
select distinct ls_cos_account
from xxaa_items_conv_stg
where 1 = 1
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_expense_account
is
select distinct ls_expense_account
from xxaa_items_conv_stg
where 1 = 1
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
cursor cur_item_sales_account
is
select distinct ls_sales_account
from xxaa_items_conv_stg
where 1 = 1
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
begin
g_application_name := 'INVENTORY';
g_object_name := 'ITEMS';
g_package_name := 'XXAA_ITEMS_CONV_PKG';
g_procedure_name := 'VALIDATE';
g_table_name := 'XXAA_ITEMS_CONV_STG';
g_file_name := 'Items.csv';
/*======================================
validation for organization if it is null
======================================*/
begin
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ORG100;;'
where ls_organization_code is null
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when others
then
g_column_name := 'LS ORGANIZATION CODE';
g_error_message := 'Organization code is null' || sqlerrm;
error_log_write;
end;
commit;
/*==================================================
validation for organization id
==================================================*/
for org_code_rec in cur_org_code
loop
begin
g_column_name := 'LS ORGANIZATION CODE';
g_column_value := org_code_rec.ls_organization_code;
select organization_id
into l_organization_id
from mtl_parameters
where organization_code = org_code_rec.ls_organization_code;
update xxaa_items_conv_stg
set organization_id = l_organization_id
where ls_organization_code = org_code_rec.ls_organization_code
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ORG101;;'
where ls_organization_code = org_code_rec.ls_organization_code
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_organization_code = org_code_rec.ls_organization_code
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :='Unexpected error occurred in organization validation ->'|| sqlerrm;
error_log_write;
end;
end loop;
commit;
/*==========================================
validation for primary uom code
=======================================*/
for uom_code_rec in cur_uom_code
loop
begin
g_column_name := 'LS_PR_UNIT_OF_MEASURE';
g_column_value := uom_code_rec.ls_pr_unit_of_measure;
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 xxaa_items_conv_stg
set pr_uom_code = l_primary_uom
where ls_pr_unit_of_measure = uom_code_rec.ls_pr_unit_of_measure
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM130;;'
where ls_pr_unit_of_measure =
uom_code_rec.ls_pr_unit_of_measure
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_pr_unit_of_measure =
uom_code_rec.ls_pr_unit_of_measure
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Primary UOM validation ->'
|| sqlerrm;
error_log_write;
end;
end loop;
commit;
/*==========================================
validation for template id
============================================*/
for item_template_rec in cur_item_template
loop
begin
g_column_name := 'LS TEMPLATE NAME';
g_column_value := item_template_rec.ls_template_name;
select template_id
into l_template_id
from mtl_item_templates
where template_name = item_template_rec.ls_template_name;
update xxaa_items_conv_stg
set template_id = l_template_id
where ls_template_name = item_template_rec.ls_template_name
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM108;;'
where ls_template_name = item_template_rec.ls_template_name
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_template_name = item_template_rec.ls_template_name
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Template Name validation ->'
|| sqlerrm;
error_log_write;
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
g_column_name := 'LS COS ACCOUNT';
g_column_value := item_cos_account_rec.ls_cos_account;
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';
update xxaa_items_conv_stg
set cos_account_id = l_cos_account_id
where ls_cos_account = item_cos_account_rec.ls_cos_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM102;;'
where ls_cos_account = item_cos_account_rec.ls_cos_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_cos_account = item_cos_account_rec.ls_cos_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in COS Account validation ->'
|| sqlerrm;
error_log_write;
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
g_column_name := 'LS EXPENSE ACCOUNT';
g_column_value := item_expense_account_rec.ls_expense_account;
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 xxaa_items_conv_stg
set expense_account_id = l_expense_account_id
where ls_expense_account =
item_expense_account_rec.ls_expense_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM132;;'
where ls_expense_account =
item_expense_account_rec.ls_expense_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_expense_account =
item_expense_account_rec.ls_expense_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Expense Account validation ->'
|| sqlerrm;
error_log_write;
end;
end if;
end loop;
commit;
/*=====================================================
validation for sales account
======================================================*/
for item_sales_account_rec in cur_item_sales_account
loop
begin
g_column_name := 'LS SALES ACCOUNT';
g_column_value := item_sales_account_rec.ls_sales_account;
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 xxaa_items_conv_stg
set sales_account_id = l_sales_account_id
where ls_sales_account = item_sales_account_rec.ls_sales_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
exception
when no_data_found
then
update xxaa_items_conv_stg
set status_stg = 'VE',
error_code = error_code || 'ITM112;;'
where ls_sales_account =
item_sales_account_rec.ls_sales_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
when others
then
update xxaa_items_conv_stg
set status_stg = 'VE'
where ls_sales_account =
item_sales_account_rec.ls_sales_account
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
g_error_message :=
'Unexpected error occurred in Sales Account validation ->'
|| sqlerrm;
error_log_write;
end;
end loop;
commit;
update xxaa_items_conv_stg
set status_stg = 'V'
where status_stg = 'M'
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
commit;
end validate;
/*
**------------------------------------------------------------------------
** procedure name: load
** purpose :procedure to write the errored data into the errors table.
**------------------------------------------------------------------------
*/
procedure load (p_transaction_id_from number, p_transaction_id_to number)
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 xxaa_items_conv_stg
where status_stg = 'V'
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
begin
g_application_name := 'INVENTORY';
g_object_name := 'ITEMS';
g_package_name := 'XXAA_ITEMS_CONV_PKG';
g_procedure_name := 'LOAD';
g_table_name := 'XXAA_ITEMS_CONV_STG';
g_file_name := 'Items.csv';
begin
select user_id
into l_user_id
from fnd_user
where user_name = 'TESTING';
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,
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 xxaa_items_conv_stg
set status_stg = 'LE'
where status_stg = 'V'
and transaction_id_stg = items_rec.transaction_id_stg;
commit;
g_error_message :='Unexpected error occurred in load procedure ->' || sqlerrm;
dbms_output.put_line (sqlerrm);
error_log_write;
end;
end loop;
update xxaa_items_conv_stg
set status_stg = 'L'
where status_stg = 'V'
and transaction_id_stg between p_transaction_id_from
and p_transaction_id_to;
commit;
exception
when others
then
g_error_message :='Unexpected error occurred in load procedure ->' || sqlerrm;
error_log_write;
end load;
/*
**------------------------------------------------------------------------
** procedure name: error_log_write
** purpose :procedure to write the errored data into the errors table.
**------------------------------------------------------------------------
*/
procedure error_log_write
is
begin
insert into xxaa_items_err_log
(application, file_name, object_name,
package_name, procedure_name, table_name,
column_name, column_value, error_date,
error_message, transaction_id_stg
)
values (g_application_name, g_file_name, g_object_name,
g_package_name, g_procedure_name, g_table_name,
g_column_name, g_column_value, g_error_date,
g_error_message, g_transaction_id
);
commit;
exception
when others
then
fnd_file.put_line (fnd_file.log, 'ERROR_LOG_WRITE#' || sqlerrm);
dbms_output.put_line ('ERROR_LOG_WRITE#' || sqlerrm);
end error_log_write;
/* procedure val_error_rep is
begin
null;
end val_error_rep;
procedure load_error_rep is
begin
null;
end load_error_rep;*/
end xxaa_items_conv_pkg;
No comments:
Post a Comment