[Home] [Help]
138: -- SATHAPLI::Component Maintenance Planning Project, 14-Nov-2011
139: -- cursor to check whether the workorder visit is a Component Visit
140: CURSOR chk_comp_visit_csr (c_workorder_id NUMBER) IS
141: SELECT 'X'
142: FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
143: WHERE WO.workorder_id = c_workorder_id
144: AND TSK.visit_task_id = WO.visit_task_id
145: AND AHL_CMP_UTIL_PKG.Is_Comp_Visit(TSK.visit_id) = 'Y';
146:
595: -- Get workorder details.
596: -- and get organization id
597: CURSOR ahl_job_csr(p_wo_id in number) IS
598: select job_status_code, organization_id , wip_entity_id
599: --from ahl_workorders_v
600: from ahl_workorder_tasks_v
601: where workorder_id = p_wo_id;
602:
603: /*
1355:
1356: SELECT
1357: NVL(VST.ITEM_INSTANCE_ID, VTS.INSTANCE_ID)
1358: FROM
1359: AHL_WORKORDERS AWOS,
1360: AHL_VISITS_B VST,
1361: AHL_VISIT_TASKS_B VTS
1362: WHERE
1363: AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
1510: from csi_item_instances CII
1511: where CII.instance_id = p_instance_id;
1512:
1513: CURSOR get_wip_job_csr(p_workorder_id IN NUMBER) IS
1514: select wip_entity_id from ahl_workorders
1515: where workorder_id = p_workorder_id;
1516: l_wip_job_id NUMBER;
1517:
1518: CURSOR removal_instance_id(p_instance_id IN NUMBER, p_wip_job_id IN NUMBER) IS
2157: from csi_item_instances CII
2158: where CII.instance_id = p_instance_id;
2159:
2160: CURSOR get_wip_job_csr(p_workorder_id IN NUMBER) IS
2161: select wip_entity_id from ahl_workorders
2162: where workorder_id = p_workorder_id;
2163: l_wip_job_id NUMBER;
2164:
2165: CURSOR removal_instance_id(p_instance_id IN NUMBER, p_wip_job_id IN NUMBER) IS
2693:
2694: -- For wip_entity_id for the workorder
2695: CURSOR ahl_wip_entity_csr(p_wo_id in Number) IS
2696: Select AHL.wip_entity_id, WIP.entity_type
2697: --FROM ahl_workorders_v AHL, wip_entities WIP
2698: FROM ahl_search_workorders_v AHL, wip_entities WIP
2699: WHERE AHL.workorder_id = p_wo_id
2700: and WIP.wip_entity_id = AHL.wip_entity_id;
2701:
2796: CURSOR ahl_mtl_txn_param_csr (p_wo_id in number, p_instance_id number) is
2797: SELECT CSI.INVENTORY_ITEM_ID, AHL.organization_id
2798: , CSI.inventory_revision revision, CSI.quantity, CSI.unit_of_measure,
2799: CSI.serial_number, CSI.lot_number
2800: --FROM ahl_workorders_v AHL, csi_item_instances CSI
2801: FROM ahl_workorder_tasks_v AHL, csi_item_instances CSI
2802: Where CSI.instance_id = p_instance_id
2803: --and csi.inv_organization_id = ahl.organization_id
2804: And AHL.workorder_id = p_wo_id;
3143: disp.serial_number, disp.lot_number,
3144: csi.inventory_item_id, vst.organization_id, vst.inv_locator_id,
3145: loc.subinventory_code, awo.operation_sequence_num
3146: FROM ahl_prd_dispositions_b disp, csi_item_instances csi,
3147: ahl_workorders wo, ahl_visits_b vst,
3148: mtl_item_locations_kfv loc, ahl_workorder_operations awo
3149: WHERE disp.instance_id = csi.instance_id
3150: AND disp.part_change_id = p_part_change_id
3151: AND disp.workorder_id = wo.workorder_id
3676:
3677: l_junk VARCHAR2(1);
3678:
3679: CURSOR check_org_csr(p_from_wip_entity NUMBER,p_to_wip_entity NUMBER)IS
3680: SELECT 'x' FROM ahl_workorders A,ahl_workorders B WHERE
3681: A.wip_entity_id = p_from_wip_entity
3682: AND B.wip_entity_id = p_to_wip_entity
3683: AND A.visit_id = B.visit_id;
3684:
4182: l_inactive_item_found BOOLEAN;
4183: l_junk VARCHAR2(1);
4184:
4185: CURSOR is_open_job_instance_csr(p_instance_id IN NUMBER) IS
4186: SELECT 'x' from ahl_visit_tasks_b VST, ahl_workorders WO where
4187: VST.instance_id = p_instance_id
4188: AND VST.visit_task_id = WO.visit_task_id
4189: AND VST.visit_id = WO.visit_id
4190: AND WO.status_code NOT IN ('4','5','7','12','17','22');
4772:
4773: SELECT
4774: NVL(VST.ITEM_INSTANCE_ID, VTS.INSTANCE_ID)
4775: FROM
4776: AHL_WORKORDERS AWOS,
4777: AHL_VISITS_B VST,
4778: AHL_VISIT_TASKS_B VTS
4779: WHERE
4780: AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
5029: CURSOR GET_SRC_RES_ID_CSR(P_ITEM_INSTANCE_ID IN NUMBER)
5030: IS
5031: SELECT MTL.RESERVATION_ID RESERVATION_ID
5032: FROM MTL_RESERVATIONS MTL,
5033: AHL_WORKORDERS AWO,
5034: AHL_VISIT_TASKS_B AVT,
5035: AHL_WORKORDER_TASKS_V AWT
5036: WHERE AWO.WORKORDER_ID = AWT.WORKORDER_ID
5037: AND AWO.WIP_ENTITY_ID = AWT.WIP_ENTITY_ID