In the following code I am retreiving a value (finished good item) and check to see if a Bill Of Material has the finished good assigned to it. I then want to print that finished good item number to the screen (ultimately insert it into the interface table).
However when it hits an item that isn't in a BOM the query fails with 'no data found'. How do I get around this so it doesn't break on the error but instead drops into the IF statement and prints the record?
OPEN get_fg (bom_v);
FETCH get_fg INTO fg_v;
EXIT WHEN get_fg%NOTFOUND;
-- check to see if fg item exists in BOM
select distinct segment1 into comp_exist_v
from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+)
and mtl.organization_id = 402
and comp.bill_sequence_id = bom.bill_sequence_id (+)
and mtl.segment1 = fg_v;
-- if item is missing from BOM, print to screen
if comp_exist_v != fg_v
DBMS_OUTPUT.PUT_LINE(bom_v||' '||'MISSING: '||fg_v);