[Home] [Help]
486: IF PG_DEBUG in ('Y', 'C') THEN
487: msc_sch_wb.atp_debug('Match item exists');
488: END IF;
489: --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
490: -- else go through msc_item_id_lid
491: IF(p_organization_id is not null) THEN
492:
493: SELECT inventory_item_id, inventory_item_id
494: bulk collect into
503:
504: select sr_inventory_item_id, inventory_item_id
505: bulk collect into
506: l_sr_inv_item_id, l_inv_item_ids
507: from msc_item_id_lid
508: where sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
509: AND sr_instance_id = p_instance_id;
510:
511: END IF;
533: EXIT WHEN l_sr_inv_item_id(l_item_count) = p_match_item_id;
534: END LOOP;
535: ELSE
536: --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
537: -- else go through msc_item_id_lid
538: IF(p_organization_id is not null) THEN
539:
540: SELECT inventory_item_id
541: INTO l_inv_item_id
548: ELSE
549:
550: SELECT inventory_item_id
551: INTO l_inv_item_id
552: FROM msc_item_id_lid
553: WHERE sr_inventory_item_id = p_inventory_item_id
554: AND sr_instance_id = p_instance_id;
555:
556: END IF;
564: return l_inv_item_id;
565: EXCEPTION
566: WHEN NO_DATA_FOUND THEN
567: /*
568: 4091487 Added the if condition to see if no items are present in msc_item_id_lid
569: raise TRY ATP Later other wise raise collections error.
570: */
571: IF(p_organization_id is null) THEN
572: Select count(*) into l_count from msc_item_id_lid;
568: 4091487 Added the if condition to see if no items are present in msc_item_id_lid
569: raise TRY ATP Later other wise raise collections error.
570: */
571: IF(p_organization_id is null) THEN
572: Select count(*) into l_count from msc_item_id_lid;
573: IF l_count > 0 THEN
574: return null;
575: ELSE
576: return -1;