[Home] [Help]
494: IF PG_DEBUG in ('Y', 'C') THEN
495: msc_sch_wb.atp_debug('Match item exists');
496: END IF;
497: --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
498: -- else go through msc_item_id_lid
499: IF(p_organization_id is not null) THEN
500:
501: SELECT inventory_item_id, inventory_item_id
502: bulk collect into
511:
512: select sr_inventory_item_id, inventory_item_id
513: bulk collect into
514: l_sr_inv_item_id, l_inv_item_ids
515: from msc_item_id_lid
516: where sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
517: AND sr_instance_id = p_instance_id;
518:
519: END IF;
541: EXIT WHEN l_sr_inv_item_id(l_item_count) = p_match_item_id;
542: END LOOP;
543: ELSE
544: --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
545: -- else go through msc_item_id_lid
546: IF(p_organization_id is not null) THEN
547:
548: SELECT inventory_item_id
549: INTO l_inv_item_id
556: ELSE
557:
558: SELECT inventory_item_id
559: INTO l_inv_item_id
560: FROM msc_item_id_lid
561: WHERE sr_inventory_item_id = p_inventory_item_id
562: AND sr_instance_id = p_instance_id;
563:
564: END IF;
572: return l_inv_item_id;
573: EXCEPTION
574: WHEN NO_DATA_FOUND THEN
575: /*
576: 4091487 Added the if condition to see if no items are present in msc_item_id_lid
577: raise TRY ATP Later other wise raise collections error.
578: */
579: IF(p_organization_id is null) THEN
580: Select count(*) into l_count from msc_item_id_lid;
576: 4091487 Added the if condition to see if no items are present in msc_item_id_lid
577: raise TRY ATP Later other wise raise collections error.
578: */
579: IF(p_organization_id is null) THEN
580: Select count(*) into l_count from msc_item_id_lid;
581: IF l_count > 0 THEN
582: return null;
583: ELSE
584: return -1;