[Home] [Help]
93: ROLV.BILLEDBYORGID,
94: ROLV.BILLEDBYCONTACTID,
95: ROLV.GROUP_ID
96: FROM RCV_OPSM_LINE_V ROLV,
97: MTL_CROSS_REFERENCES_VL MCRV,
98: MTL_PARAMETERS MP
99: WHERE ROLV.SHIPMENT_HEADER_ID =p_shipmentheaderid
100: AND ROLV.GROUP_ID =p_groupid
101: AND ROLV.SOURCE_DOCUMENT_CODE ='RMA RECEIPT'
105: AND MP.MASTER_ORGANIZATION_ID = MCRV.ORGANIZATION_ID
106: AND ROLV.ITEM_ID = MCRV.INVENTORY_ITEM_ID
107: AND MCRV.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
108: AND MCRV.CROSS_REFERENCE ='YES'
109: AND NOT EXISTS ( SELECT 'Y' FROM mtl_cross_references_vl mcr
110: WHERE mcr.inventory_item_id = ROLV.ITEM_ID
111: AND mcr.organization_id = ROLV.ORGANIZATION_ID
112: AND mcr.cross_reference_type ='OPSM INTEGRATED'
113: AND upper(mcr.cross_reference)= 'NO');
608: BEGIN
609: SELECT 1
610: INTO l_count
611: FROM MTL_PARAMETERS MP,
612: MTL_CROSS_REFERENCES_VL MCRV,
613: MTL_SYSTEM_ITEMS_B MSIB
614: WHERE MSIB.ORGANIZATION_ID = v_opsm_items.ORGANIZATION_ID
615: AND MSIB.INVENTORY_ITEM_ID = v_opsm_items.ITEM_ID
616: AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
619: AND MP.MASTER_ORGANIZATION_ID =MCRV.ORGANIZATION_ID
620: AND MCRV.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
621: AND MCRV.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
622: AND MCRV.CROSS_REFERENCE = 'YES'
623: AND NOT EXISTS ( SELECT 'Y' FROM mtl_cross_references_vl mcr
624: WHERE mcr.inventory_item_id = v_opsm_items.ITEM_ID
625: AND mcr.organization_id = v_opsm_items.ORGANIZATION_ID
626: AND mcr.cross_reference_type ='OPSM INTEGRATED'
627: AND upper(mcr.cross_reference)= 'NO');