ITEM CONVERSION IN ORACLE INVENTORY MODULE
ITEM CONVERSION IN ORACLE INVENTORY MODULE WITH CODE OR CONVERT LEGACY ITEMS DATA INTO ORACLE APPS
staging table creation:
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;
/
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:
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
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
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 map (p_transaction_id_from number, p_transaction_id_to number)
is
begin
update xxaa_items_conv_stg set ls_organization_code = 'V1';
commit;
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 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 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 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