ITEM CONVERSION INBOUND


ITEM CONVERSION INBOUND,AskHareesh Blog for OracleApps

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