The following lines contain the word 'select', 'insert', 'update' or 'delete':
update bom_bill_of_mtls_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update bom_inventory_comps_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update bom_ref_desgs_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update bom_sub_comps_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update mtl_item_revisions_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
inserts record into MTL_INTERFACE_ERRORS.
REQUIRES
err_text out buffer to return error message
MODIFIES
MTL_ITEM_REVISIONS_INTERFACE
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
-----------------------------------------------------------------------------*/
FUNCTION bmasrev_assign_revision (
org_id NUMBER,
all_org NUMBER,
user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text IN OUT VARCHAR2
)
return INTEGER
IS
stmt_num NUMBER := 0;
select organization_code OC, organization_id OI,
revision R,
inventory_item_id III, item_number IIN,
transaction_id TI,
implementation_date ID, effectivity_date ED
from mtl_item_revisions_interface
where process_flag = 1
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
update mtl_item_revisions_interface
set transaction_id = mtl_system_items_interface_s.nextval
where transaction_id is null
and process_flag = 1
and rownum < 500;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update mtl_item_revisions_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update mtl_item_revisions_interface set
organization_id = nvl(organization_id, c1rec.OI),
inventory_item_id = nvl(inventory_item_id, c1rec.III),
revision = UPPER(c1rec.R),
process_flag = 2,
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
effectivity_date = nvl(effectivity_date, sysdate),
IMPLEMENTATION_DATE = nvl(effectivity_date, sysdate)
where transaction_id = c1rec.TI;
cursor c1 is select
organization_id OI, organization_code OC,
assembly_item_id AII, item_number AIN,
common_assembly_item_id CAII, common_item_number CAIN,
common_organization_id COI, common_org_code COC,
alternate_bom_designator ABD, transaction_id TI,
bill_sequence_id BSI, common_bill_sequence_id CBSI,
revision R, last_update_date LUD, last_updated_by LUB,
creation_date CD, created_by CB, last_update_login LUL
from bom_bill_of_mtls_interface
where process_flag = 1
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
cursor c2 is select
transaction_id TI, common_bill_sequence_id CBSI,
assembly_item_id AII, common_assembly_item_id CAAI,
common_assembly_item_id CAID, organization_id OI,
alternate_bom_designator ABD, common_organization_id COI,
bill_Sequence_id BSI
from bom_bill_of_mtls_interface
where process_flag = 99
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
cursor c3 is select 1
from dual where exists ( select 1
from MTL_ITEM_REVISIONS_INTERFACE
where inventory_item_id = assembly_id
and organization_id = assembly_org_id
and revision = item_rev);
update bom_bill_of_mtls_interface ori
set transaction_id = mtl_system_items_interface_s.nextval,
bill_sequence_id = nvl(bill_sequence_id,
bom_inventory_components_s.nextval)
where transaction_id is null
and process_flag = 1
and rownum < 500;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = 3
where transaction_id = c1rec.TI;
** Insert revision record
*/
stmt_num := 6;
insert into mtl_item_revisions_interface
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
TRANSACTION_ID,
PROCESS_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
) values
(c1rec.AII, c1rec.OI, UPPER(c1rec.R),
nvl(c1rec.LUD, sysdate),
nvl(c1rec.LUB, user_id),
nvl(c1rec.CD, sysdate),
nvl(c1rec.CB, user_id),
nvl(c1rec.LUL, user_id),
sysdate,
sysdate,
mtl_system_items_interface_s.nextval,
2,
req_id,
prog_appid,
prog_id,
sysdate
);
update bom_bill_of_mtls_interface
set organization_id = nvl(organization_id, c1rec.OI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
common_organization_id = nvl(common_organization_id, c1rec.COI),
common_assembly_item_id = nvl(common_assembly_item_id, c1rec.CAII),
assembly_type = nvl(assembly_type, 1),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 99
where transaction_id = c1rec.TI;
update bom_bill_of_mtls_interface set
process_flag = proc_flag,
common_bill_sequence_id = c2rec.CBSI,
common_organization_id = c_org_id,
common_assembly_item_id = assy_id
where transaction_id = c2rec.TI;
select organization_code OC, organization_id OI,
assembly_item_id AII, assembly_item_number AIN,
alternate_bom_designator ABD, bill_sequence_id BSI,
component_sequence_id CSI, transaction_id TI,
component_item_id CII, component_item_number CIN,
location_name LN, supply_locator_id SLI,
operation_seq_num OSN,
to_char(effectivity_date, 'YYYY/MM/DD HH24:MI') ED,
bom_item_type BIT
from bom_inventory_comps_interface
where process_flag = 1
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
update bom_inventory_comps_interface
set transaction_id = mtl_system_items_interface_s.nextval,
component_sequence_id = nvl(component_sequence_id,
bom_inventory_components_s.nextval)
where transaction_id is null
and process_flag = 1
and rownum < 500;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select bom_item_type
into c1rec.BIT
from mtl_system_items
where organization_id = c1rec.OI
and inventory_item_id = c1rec.CII;
update bom_inventory_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_inventory_comps_interface set
component_item_id = nvl(component_item_id, c1rec.CII),
item_num = nvl(item_num, 1),
component_quantity = nvl(component_quantity, 1),
component_yield_factor = nvl(component_yield_factor, 1),
implementation_date = effectivity_date,
planning_factor = nvl(planning_factor, 100),
quantity_related = nvl(quantity_related, 2),
so_basis = nvl(so_basis, 2),
optional = nvl(optional, 2),
mutually_exclusive_options = nvl(mutually_exclusive_options, 2),
include_in_cost_rollup = nvl(include_in_cost_rollup, 1),
check_atp = nvl(check_atp, 2),
required_to_ship = nvl(required_to_ship, 2),
required_for_revenue = nvl(required_for_Revenue, 2),
include_on_ship_docs = nvl(include_on_ship_docs, 2),
include_on_bill_docs = nvl(include_on_bill_docs, 2),
low_quantity = nvl(low_quantity, nvl(high_quantity,null)),
high_quantity = nvl(high_quantity,nvl(low_quantity,null)),
bill_sequence_id = nvl(bill_Sequence_id, c1rec.BSI),
pick_components = nvl(pick_components, 2),
supply_locator_id = nvl(supply_locator_id, c1rec.SLI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
alternate_bom_designator = nvl(alternate_bom_designator,c1rec.ABD),
organization_id = nvl(organization_id, c1rec.OI),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 2,
bom_item_type = c1rec.BIT
where transaction_id = c1rec.TI;
** update component_sequence_id for ref desgs and sub comps
*/
ret_code := bmascmpid_assign_cmp_seq_id(
org_id => c1rec.OI,
assy_id => c1rec.AII,
alt_desg => c1rec.ABD,
op_seq => c1rec.OSN,
cmp_seq_id => c1rec.CSI,
cmp_id => c1rec.CII,
eff_date => c1rec.ED,
err_text => err_text);
select bill_sequence_id
into bill_seq_id
from bom_bill_of_materials
where organization_id = org_id
and assembly_item_id = item_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE');
select bill_sequence_id
into bill_seq_id
from bom_bill_of_mtls_interface
where organization_id = org_id
and assembly_item_id = item_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and process_flag <> 3 and process_flag <> 7
and rownum = 1;
select component_sequence_id
into cmp_seq_id
from bom_inventory_components
where bill_sequence_id = bill_seq_id
and component_item_id = cmp_id
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date;
select component_sequence_id
into cmp_seq_id
from bom_inventory_comps_interface
where bill_sequence_id = bill_seq_id
and component_item_id = cmp_id
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and process_flag <>3 and process_flag <> 7
and rownum = 1;
select assembly_item_id, organization_id, alternate_bom_designator
into item_id, org_id, alt_desg
from bom_bill_of_materials
where bill_sequence_id = bill_seq_id;
select assembly_item_id, organization_id, alternate_bom_designator
into item_id, org_id, alt_desg
from bom_bill_of_mtls_interface
where bill_sequence_id = bill_seq_id
and process_flag <> 3 and process_flag <> 7
and rownum = 1;
Select bici.rowid row_id
From bom_inventory_comps_interface bici
where bici.process_flag = 1
and bici.assembly_item_id is null
and bici.organization_id = org_id
and bici.assembly_item_number = item_number
and bici.bill_sequence_id is null;
Select brd.rowid row_id
From bom_ref_desgs_interface brd
where brd.process_flag = 1
and brd.assembly_item_id is null
and brd.organization_id = org_id
and brd.assembly_item_number = item_number
and brd.bill_sequence_id is null;
Select bsc.rowid row_id
From bom_sub_comps_interface bsc
where bsc.process_flag = 1
and bsc.assembly_item_id is null
and bsc.organization_id = org_id
and bsc.assembly_item_number = item_number
and bsc.bill_sequence_id is null;
update bom_inventory_comps_interface
set assembly_item_id = item_id
where rowid = X_Component.row_id;
update bom_ref_desgs_interface ori
set ori.assembly_item_id = item_id
where ori.rowid = X_Designator.row_id;
update bom_sub_comps_interface ori
set ori.assembly_item_id = item_id
where ori.rowid = X_Substitute.row_id;
update bom_inventory_comps_interface set
bill_sequence_id = bom_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and bill_sequence_id is null
and rownum < 500;
update bom_ref_desgs_interface set
bill_sequence_id = bom_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and bill_sequence_id is null
and rownum < 500;
update bom_sub_comps_interface set
bill_sequence_id = bom_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and bill_sequence_id is null
and rownum < 500;
update bom_ref_desgs_interface set
component_sequence_id = cmp_seq_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and component_item_id = cmp_id
and component_sequence_id is null
and rownum < 500;
update bom_sub_comps_interface set
component_sequence_id = cmp_seq_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and component_item_id = cmp_id
and component_sequence_id is null
and rownum < 500;
select component_quantity
into comp_qty
from bom_inventory_components
where component_sequence_id = comp_seq_id;
select component_quantity
into comp_qty
from bom_inventory_comps_interface
where component_sequence_id = comp_seq_id
and process_flag <> 3 and process_flag <> 7
and rownum = 1;
select component_sequence_id CSI,
transaction_id TI, organization_id OI,
bill_sequence_id BSI, assembly_item_id AII,
assembly_item_number AIN, alternate_bom_designator ABD,
component_item_id CII, component_item_number CIN,
operation_seq_num OSN,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED
from bom_ref_desgs_interface
where process_flag = 1
and component_sequence_id is null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select transaction_id TI, organization_id OI
from bom_ref_desgs_interface
where process_flag = 1
and component_sequence_id is not null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select component_sequence_id CSI
from bom_ref_desgs_interface
where process_flag = 99
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500
group by component_sequence_id;
insert into bom_ref_desgs_interface (
COMPONENT_REFERENCE_DESIGNATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COMPONENT_SEQUENCE_ID,
PROCESS_FLAG) select
REFERENCE_DESIGNATOR,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, user_id),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, user_id),
NVL(LAST_UPDATE_LOGIN, user_id),
NVL(REQUEST_ID, req_id),
NVL(PROGRAM_APPLICATION_ID, prog_appid),
NVL(PROGRAM_ID, prog_id),
NVL(PROGRAM_UPDATE_DATE, sysdate),
COMPONENT_SEQUENCE_ID,
1
from bom_inventory_comps_interface
where process_flag = 2
and reference_designator is not null;
update bom_ref_desgs_interface
set transaction_id = mtl_system_items_interface_s.nextval
where transaction_id is null
and process_flag = 1
and rownum < 500;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface
set component_sequence_id = c1rec.CSI,
assembly_item_id = c1rec.AII,
component_item_id = c1rec.CII,
bill_sequence_id = c1rec.BSI,
organization_id = c1rec.OI
where transaction_id = c1rec.TI;
update bom_ref_desgs_interface
set process_flag = 99,
last_update_date = nvl(last_update_date,sysdate),
last_updated_by = nvl(last_updated_by,user_id),
creation_date = nvl(creation_date,sysdate),
created_by = nvl(created_by,user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate)
where transaction_id = c2rec.TI;
select count(distinct component_sequence_id)
into total_recs
from bom_ref_desgs_interface
where process_flag = 99;
select mtl_system_items_interface_s.nextval
into dummy_txn
from sys.dual;
update bom_ref_desgs_interface
set transaction_id = dummy_txn,
process_flag = 2
where component_sequence_id = c3rec.CSI
and process_flag = 99;
select organization_id OI, substitute_comp_number SCN,
substitute_component_id SCI, transaction_id TI
from bom_sub_comps_interface
where process_flag = 1
and substitute_component_id is null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select component_sequence_id CSI,
transaction_id TI, organization_id OI,
bill_sequence_id BSI, assembly_item_id AII,
assembly_item_number AIN, alternate_bom_designator ABD,
component_item_id CII, component_item_number CIN,
operation_seq_num OSN,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED
from bom_sub_comps_interface
where process_flag = 1
and component_sequence_id is null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select transaction_id TI, organization_id OI,
component_sequence_id CSI, substitute_item_quantity SIQ
from bom_sub_comps_interface
where process_flag = 1
and component_sequence_id is not null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select component_sequence_id CSI
from bom_sub_comps_interface
where process_flag = 99
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500
group by component_sequence_id;
insert into bom_sub_comps_interface (
SUBSTITUTE_COMPONENT_ID,
SUBSTITUTE_COMP_NUMBER,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COMPONENT_SEQUENCE_ID,
PROCESS_FLAG,
SUBSTITUTE_ITEM_QUANTITY)
select
SUBSTITUTE_COMP_ID,
SUBSTITUTE_COMP_NUMBER,
ORGANIZATION_ID,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, user_id),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, user_id),
NVL(LAST_UPDATE_LOGIN, user_id),
NVL(REQUEST_ID, req_id),
NVL(PROGRAM_APPLICATION_ID, prog_appid),
NVL(PROGRAM_ID, prog_id),
NVL(PROGRAM_UPDATE_DATE, sysdate),
COMPONENT_SEQUENCE_ID,
1,
COMPONENT_QUANTITY
from bom_inventory_comps_interface
where process_flag = 2
and (substitute_comp_id is not null
or
substitute_comp_number is not null);
update bom_sub_comps_interface
set transaction_id = mtl_system_items_interface_s.nextval
where transaction_id is null
and process_flag = 1
and rownum < 500;
** update substitute component id if null
*/
while continue_loop loop
commit_cnt := 0;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c0rec.TI;
update bom_sub_comps_interface
set substitute_component_id = c0rec.SCI
where transaction_id = c0rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_sub_comps_interface
set component_sequence_id = c1rec.CSI,
assembly_item_id = c1rec.AII,
component_item_id = c1rec.CII,
bill_sequence_id = c1rec.BSI
where transaction_id = c1rec.TI;
update bom_sub_comps_interface set
process_flag = 3
where transaction_id = c2rec.TI;
update bom_sub_comps_interface
set process_flag = 99,
substitute_item_quantity = nvl(c2rec.SIQ,substitute_item_quantity),
last_update_date = nvl(last_update_date,sysdate),
last_updated_by = nvl(last_updated_by,user_id),
creation_date = nvl(creation_date,sysdate),
created_by = nvl(created_by,user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate)
where transaction_id = c2rec.TI;
select count(distinct component_sequence_id)
into total_recs
from bom_sub_comps_interface
where process_flag = 99;
select mtl_system_items_interface_s.nextval
into dummy_txn
from sys.dual;
update bom_sub_comps_interface
set transaction_id = dummy_txn,
process_flag = 2
where component_sequence_id = c3rec.CSI
and process_flag = 99;