The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqltxt VARCHAR2(9999); -- SQL select statement
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
select 1 into l_org_exists
from mtl_parameters
where organization_id=l_org_id;
sqltxt := ' select mif1.padded_item_number "Assembly Item Number", '||
' mp.organization_code "Organization Code", '||
' bsb.alternate_bom_designator "Alternate Bom Designator", '||
' bcb.operation_seq_num "Operation Seq Num", '||
' bcb.item_num "Item Seq Num", '||
' mif2.padded_item_number "Component Item", '||
' decode(bcb.from_end_item_unit_number,null,''*MISSING'', '||
' bcb.from_end_item_unit_number||'' (Invalid)'') "From End Item Unit Number", '||
' bcb.to_end_item_unit_number||'' (Invalid)'' "To End Item Unit Number", '||
' bcb.component_quantity "Component Quantity", '||
' to_char(bcb.effectivity_date,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date", '||
' to_char(bcb.disable_date,''DD-MON-YYYY HH24:MI:SS'') "Disable Date", '||
' bcb.change_notice "Change Notice", '||
' bsb.assembly_item_id "Assembly Item Id", '||
' bsb.organization_id "Organization Id", '||
' bsb.bill_sequence_id "Bill Sequence Id", '||
' bcb.component_sequence_id "Component Sequence Id" '||
' from bom_components_b bcb, bom_structures_b bsb, '||
' mtl_item_flexfields mif1, mtl_item_flexfields mif2, '||
' mtl_parameters mp '||
' where bsb.bill_sequence_id = bcb.bill_sequence_id '||
' and bsb.assembly_item_id = mif1.inventory_item_id '||
' and bsb.organization_id = mif1.organization_id '||
' and bcb.component_item_id = mif2.inventory_item_id '||
' and bsb.organization_id = mif2.organization_id '||
' and mif1.organization_id = mp.organization_id '||
' and mif1.effectivity_control = 2 '||
' and ( ( bcb.from_end_item_unit_number is null '||
' or '||
' ( bcb.from_end_item_unit_number is not null '||
' and not exists '||
' (select 1 from pjm_unit_numbers '||
' where unit_number = bcb.from_end_item_unit_number) '||
' ) '||
' ) '||
' and ( bcb.to_end_item_unit_number is not null '||
' and not exists '||
' (select 1 from pjm_unit_numbers '||
' where unit_number = bcb.to_end_item_unit_number) '||
' ) '||
' ) ';
' select mif1.padded_item_number "Assembly Item Number", '||
' mp.organization_code "Organization Code", '||
' bsb.alternate_bom_designator "Alternate Bom Designator", '||
' bcb.operation_seq_num "Operation Seq Num", '||
' bcb.item_num "Item Seq Num", '||
' mif2.padded_item_number "Component Item", '||
' decode(bcb.from_end_item_unit_number,null,''*MISSING'', '||
' bcb.from_end_item_unit_number||'' (Invalid)'') "From End Item Unit Number", '||
' bcb.to_end_item_unit_number "To End Item Unit Number", '||
' bcb.component_quantity "Component Quantity", '||
' to_char(bcb.effectivity_date,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date", '||
' to_char(bcb.disable_date,''DD-MON-YYYY HH24:MI:SS'') "Disable Date", '||
' bcb.change_notice "Change Notice", '||
' bsb.assembly_item_id "Assembly Item Id", '||
' bsb.organization_id "Organization Id", '||
' bsb.bill_sequence_id "Bill Sequence Id", '||
' bcb.component_sequence_id "Component Sequence Id" '||
' from bom_components_b bcb, bom_structures_b bsb, '||
' mtl_item_flexfields mif1, mtl_item_flexfields mif2, '||
' mtl_parameters mp '||
' where bsb.bill_sequence_id = bcb.bill_sequence_id '||
' and bsb.assembly_item_id = mif1.inventory_item_id '||
' and bsb.organization_id = mif1.organization_id '||
' and bcb.component_item_id = mif2.inventory_item_id '||
' and bsb.organization_id = mif2.organization_id '||
' and mif1.organization_id = mp.organization_id '||
' and mif1.effectivity_control = 2 '||
' and ( ( bcb.from_end_item_unit_number is null '||
' or ( bcb.from_end_item_unit_number is not null '||
' and not exists '||
' (select 1 from pjm_unit_numbers '||
' where unit_number = bcb.from_end_item_unit_number) '||
' ) '||
' ) '||
' and ( bcb.to_end_item_unit_number is null '||
' or '||
' ( bcb.to_end_item_unit_number is not null '||
' and exists '||
' (select 1 from pjm_unit_numbers '||
' where unit_number = bcb.to_end_item_unit_number) '||
' ) '||
' ) '||
' ) ';
' select mif1.padded_item_number "Assembly Item Number", '||
' mp.organization_code "Organization Code", '||
' bsb.alternate_bom_designator "Alternate Bom Designator", '||
' bcb.operation_seq_num "Operation Seq Num", '||
' bcb.item_num "Item Seq Num", '||
' mif2.padded_item_number "Component Item", '||
' bcb.from_end_item_unit_number "From End Item Unit Number", '||
' bcb.to_end_item_unit_number||'' (Invalid)'' "To End Item Unit Number", '||
' bcb.component_quantity "Component Quantity", '||
' to_char(bcb.effectivity_date,''DD-MON-YYYY HH24:MI:SS'')"Effectivity Date", '||
' to_char(bcb.disable_date,''DD-MON-YYYY HH24:MI:SS'') "Disable Date", '||
' bcb.change_notice "Change Notice", '||
' bsb.assembly_item_id "Assembly Item Id", '||
' bsb.organization_id "Organization Id", '||
' bsb.bill_sequence_id "Bill Sequence Id", '||
' bcb.component_sequence_id "Component Sequence Id" '||
' from bom_components_b bcb, bom_structures_b bsb, '||
' mtl_item_flexfields mif1, mtl_item_flexfields mif2, '||
' mtl_parameters mp '||
' where bsb.bill_sequence_id = bcb.bill_sequence_id '||
' and bsb.assembly_item_id = mif1.inventory_item_id '||
' and bsb.organization_id = mif1.organization_id '||
' and bcb.component_item_id = mif2.inventory_item_id '||
' and bsb.organization_id = mif2.organization_id '||
' and mif1.organization_id = mp.organization_id '||
' and mif1.effectivity_control = 2 '||
' and ( ( bcb.to_end_item_unit_number is not null '||
' and not exists '||
' (select 1 from pjm_unit_numbers '||
' where unit_number = bcb.to_end_item_unit_number) '||
' ) '||
' and ( bcb.from_end_item_unit_number is not null '||
' and exists '||
' (select 1 from pjm_unit_numbers '||
' where unit_number = bcb.from_end_item_unit_number) '||
' ) '||
' ) ';
sqltxt:= ' select mif.padded_item_number "Assembly Item", '||
' mp.organization_code "Organization Code", '||
' bsb.alternate_bom_designator "Alternate Designator", '||
' decode(bsb.assembly_type,1,''Manufacturing Bill'',2,''Engineering Bill'') "Assembly Type",'||
' to_char(bsb.implementation_date,''DD-MON-YYYY HH24:MI:SS'') "Implementation Date", '||
' bsb.pending_from_ecn "Pending From ECN", '||
' bsb.assembly_item_id "Assembly Item Id" , '||
' bsb.organization_id "Organization Id", '||
' bsb.bill_sequence_id "Bill Sequence Id", '||
' bsb.common_bill_sequence_id "Common Bill Sequence Id" '||
' from bom_structures_b bsb, mtl_item_flexfields mif, mtl_parameters mp '||
' where mif.inventory_item_id = bsb.assembly_item_id '||
' and mif.organization_id = bsb.organization_id '||
' and bsb.organization_id = mp.organization_id '||
' and bsb.implementation_date is null ';
(2) Use the below update statement to correct these bills.
update bom_structures_b
set implementation_date = creation_date
where implementation_date is null;
Line: 357
(3) Make sure that the total number of records updated
are same as the number of records fetched above.
(4) Commit the transaction.
';