[Home] [Help]
5601: FROM ahl_pc_nodes_b B
5602: WHERE B.pc_node_id = EFF.pc_node_id
5603: START WITH B.pc_node_id
5604: IN (select pc_node_id
5605: from ahl_pc_associations itm, csi_item_instances csi
5606: where itm.association_type_flag = 'I'
5607: and itm.unit_item_id = csi.inventory_item_id
5608: and csi.instance_id = UCH.csi_item_instance_id)
5609: CONNECT BY B.pc_node_id = PRIOR B.parent_node_id),
5649: l_base_query VARCHAR2(8000) :=
5650: ' INSERT INTO AHL_APPLICABLE_INSTANCES(CSI_ITEM_INSTANCE_ID, POSITION_ID)' ||
5651: ' SELECT UNIQUE INST.instance_id, -1' ||
5652: ' FROM mtl_system_items_b ITEMS,' ||
5653: ' csi_item_instances INST,' ||
5654: ' mtl_serial_numbers SERIALS' ||
5655: ' WHERE ITEMS.inventory_item_id = INST.inventory_item_id' ||
5656: ' AND ITEMS.organization_id = INST.inv_master_organization_id' ||
5657: ' AND INST.serial_number = SERIALS.serial_number' ||
5920: || ' WO.WIP_ENTITY_ID'
5921: || ' FROM AHL_WORKORDERS WO,'
5922: || ' AHL_VISIT_TASKS_B ATSK,'
5923: || ' AHL_VISITS_B AVST,'
5924: || ' CSI_ITEM_INSTANCES CSIII'
5925: || ' WHERE WO.visit_task_id = ATSK.visit_task_id'
5926: || ' AND WO.STATUS_CODE NOT IN (7, 22, 17, 12, 18, 21, 4, 5)'
5927: || ' AND ATSK.return_to_supply_flag = ''Y'''
5928: || ' AND ATSK.visit_id = AVST.visit_id'
5930: || ' (SELECT CSIII.SERIAL_NUMBER, CSIII.INVENTORY_ITEM_ID, PRHA.REQUISITION_HEADER_ID, AMTI.RESERVATION_ID'
5931: || ' FROM PO_REQUISITION_HEADERS_ALL PRHA,'
5932: || ' OE_ORDER_HEADERS_ALL OOHA,'
5933: || ' MTL_SERIAL_NUMBERS MSN,'
5934: || ' CSI_ITEM_INSTANCES CSIII,'
5935: || ' MTL_SERIAL_NUMBERS_TEMP MSNT,'
5936: || ' WSH_DELIVERY_DETAILS WDD,'
5937: || ' WSH_SERIAL_NUMBERS WSN,'
5938: || ' AHL_MM_TXN_INTERFACE AMTI'
6010: --Reservations filter
6011: IF(p_demand_Record.Filter_Reservations = 'Y') THEN
6012: l_base_query := l_base_query || ' AND EXISTS '
6013: || ' (SELECT 1'
6014: || ' FROM MTL_RESERVATIONS RESERV, CSI_ITEM_INSTANCES CSIII'
6015: || ' WHERE CSIII.instance_id = NVL(TSK.instance_id, VST.item_instance_id)'
6016: || ' AND RESERV.SUPPLY_SOURCE_HEADER_ID = WO.WIP_ENTITY_ID'
6017: || ' AND RESERV.SUPPLY_SOURCE_TYPE_ID = 5)';
6018: END IF;
6074:
6075: --End of base criteria for Cannibalization Supply
6076: l_base_query := l_base_query || ' )'
6077: || ' SELECT cis.instance_id'
6078: || ' FROM csi_item_instances cis, csi_ii_relationships cir, base_inst'
6079: || ' WHERE cis.instance_id = cir.subject_id'
6080: || ' AND cir.relationship_type_code = ''COMPONENT-OF'''
6081: || ' AND NVL(cir.active_start_date,sysdate) <= sysdate'
6082: || ' AND NVL(cir.active_end_date,sysdate) >= sysdate'
6083: || ' START WITH cir.object_id = base_inst.item_instance_id'
6084: || ' CONNECT BY cir.object_id = prior cir.subject_id'
6085: || ' UNION'
6086: || ' SELECT cis.instance_id'
6087: || ' FROM csi_item_instances cis, base_inst'
6088: || ' WHERE cis.instance_id = base_inst.item_instance_id)';
6089:
6090: END IF;
6091: