[Home] [Help]
170: scheduled_start_date,
171: job_status_code,
172: job_number,
173: organization_id
174: FROM ahl_workorders_v
175: WHERE workorder_id = p_workorder_id;
176: */
177: -- Modified by SURRKUMA for Service Bulletin on 17-Jun-11
178: -- AnRaj: Changed query, Perf Bug#4908609,Issue#1
182: WO.status_code job_status_code,
183: WO.workorder_name job_number,
184: VST.ORGANIZATION_ID organization_id,
185: VST.VISIT_ID visit_id
186: from AHL_WORKORDERS WO,
187: WIP_DISCRETE_JOBS WDJ,
188: AHL_VISITS_VL VST,
189: AHL_VISIT_TASKS_VL VTS
190: where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID and
989: -- Cursor to check whether removed instance is in job or not.
990: /**
991: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
992: SELECT 'Y'
993: FROM ahl_workorders awo, csi_item_instances csi
994: WHERE awo.wip_entity_id = csi.wip_job_id
995: AND awo.workorder_id = p_workorder_id
996: AND csi.instance_id = p_instance_id;
997: **/
999: -- Changed by jaramana on 31-MAR-2011 for bug 11856373 to compare using
1000: -- the item and quantity of the disposition rather than the actual instance id; sorao for backporting
1001: CURSOR chk_disp_inst_job_csr (p_disposition_id NUMBER, p_workorder_id NUMBER) IS
1002: SELECT 'Y'
1003: FROM ahl_workorders awo, csi_item_instances csi, ahl_prd_dispositions_b disp
1004: WHERE awo.workorder_id = p_workorder_id
1005: AND csi.wip_job_id = awo.wip_entity_id
1006: AND csi.INVENTORY_ITEM_ID = disp.INVENTORY_ITEM_ID
1007: AND csi.QUANTITY = disp.QUANTITY;
1008:
1009: -- Cursor to get the first released non-master workorder id for a given NR summary workorder.
1010: CURSOR get_rel_nonmaster_wo_id_csr(c_nr_wo_id NUMBER) IS
1011: SELECT workorder_id
1012: FROM ahl_workorders
1013: WHERE master_workorder_flag = 'N'
1014: AND wip_entity_id IN
1015: (SELECT child_object_id
1016: FROM wip_sched_relationships
1013: WHERE master_workorder_flag = 'N'
1014: AND wip_entity_id IN
1015: (SELECT child_object_id
1016: FROM wip_sched_relationships
1017: START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
1018: CONNECT BY parent_object_id = PRIOR child_object_id
1019: AND parent_object_type_id = PRIOR child_object_type_id
1020: AND relationship_type = 1
1021: )
1511: WHERE disposition_id = p_disposition_id ;
1512:
1513:
1514: CURSOR get_organization_csr(p_workorder_id IN NUMBER) IS
1515: SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
1516: WHERE wo.workorder_id = p_workorder_id
1517: AND wo.visit_id = vi.visit_id;
1518:
1519: CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
1542:
1543: -- Added by jaramana on October 8, 2007 for ER 5903256
1544: CURSOR check_nr_wo_status_csr(p_nr_workorder_id IN NUMBER) IS
1545: SELECT 'Y'
1546: FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_UNIT_EFFECTIVITIES_B UE
1547: WHERE WO.workorder_id = NVL(p_nr_workorder_id, -1)
1548: AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
1549: AND UE.UNIT_EFFECTIVITY_ID = TSK.UNIT_EFFECTIVITY_ID
1550: AND UE.STATUS_CODE IS NULL;
1570: -- SATHAPLI::Bug 7111116, 21-May-2008, fix start
1571: -- Cursor to get the first released non-master workorder id for a given NR summary workorder.
1572: CURSOR get_rel_nonmaster_wo_id_csr(c_nr_wo_id NUMBER) IS
1573: SELECT workorder_id
1574: FROM ahl_workorders
1575: WHERE master_workorder_flag = 'N'
1576: AND wip_entity_id IN
1577: (SELECT child_object_id
1578: FROM wip_sched_relationships
1575: WHERE master_workorder_flag = 'N'
1576: AND wip_entity_id IN
1577: (SELECT child_object_id
1578: FROM wip_sched_relationships
1579: START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
1580: CONNECT BY parent_object_id = PRIOR child_object_id
1581: AND parent_object_type_id = PRIOR child_object_type_id
1582: AND relationship_type = 1
1583: )
1595: -- Cursor to check whether removed instance is in the given workorder or not.
1596: /**
1597: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
1598: SELECT 'Y'
1599: FROM ahl_workorders awo, csi_item_instances csi
1600: WHERE awo.wip_entity_id = csi.wip_job_id
1601: AND awo.workorder_id = p_workorder_id
1602: AND csi.instance_id = p_instance_id;
1603: **/
1605: -- Changed by jaramana on 31-MAR-2011 for bug 11856373 to compare using
1606: -- the item and quantity of the disposition rather than the actual instance id
1607: CURSOR chk_disp_inst_job_csr (p_disposition_id NUMBER, p_workorder_id NUMBER) IS
1608: SELECT 'Y'
1609: FROM ahl_workorders awo, csi_item_instances csi, ahl_prd_dispositions_b disp
1610: WHERE awo.workorder_id = p_workorder_id
1611: AND csi.wip_job_id = awo.wip_entity_id
1612: AND csi.INVENTORY_ITEM_ID = disp.INVENTORY_ITEM_ID
1613: AND csi.QUANTITY = disp.QUANTITY;
2609:
2610: -- Cursor to check whether removed instance is in job or not.
2611: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
2612: SELECT 'Y'
2613: FROM ahl_workorders awo, csi_item_instances csi
2614: WHERE awo.wip_entity_id = csi.wip_job_id
2615: AND awo.workorder_id = p_workorder_id
2616: AND csi.instance_id = p_instance_id;
2617:
2617:
2618: -- Added by changes by by jaramana on 25-APR-2011 for bug 11870333
2619: CURSOR get_rel_nonmaster_wo_id_csr(c_nr_wo_id NUMBER) IS
2620: SELECT workorder_id
2621: FROM ahl_workorders
2622: WHERE master_workorder_flag = 'N'
2623: AND wip_entity_id IN
2624: (SELECT child_object_id
2625: FROM wip_sched_relationships
2622: WHERE master_workorder_flag = 'N'
2623: AND wip_entity_id IN
2624: (SELECT child_object_id
2625: FROM wip_sched_relationships
2626: START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
2627: CONNECT BY parent_object_id = PRIOR child_object_id
2628: AND parent_object_type_id = PRIOR child_object_type_id
2629: AND relationship_type = 1
2630: )
2634: --sareepar Added for Bug #13372980 on 25/04/2012
2635: -- Cursor to get originating workorder details
2636: CURSOR get_orig_workorder_det_csr(p_workorder_id NUMBER) IS
2637: SELECT scheduled_start_date
2638: FROM ahl_workorders_v
2639: WHERE workorder_id = p_workorder_id;
2640:
2641:
2642: l_rel_nm_wo_id NUMBER;
2700: -- AVIKUKUM::PIE::FP::19-OCT-2010
2701: l_sr_task_tbl(0).service_type_code := p_disposition_rec.service_type_code;
2702: l_sr_task_tbl(0).service_type := p_disposition_rec.service_type;
2703:
2704: SELECT visit_id INTO l_visit_id FROM AHL_WORKORDERS WHERE workorder_id = p_disposition_rec.workorder_id;
2705:
2706: l_sr_task_tbl(0).Visit_id:= l_visit_id;
2707: l_sr_task_tbl(0).Originating_wo_id:= p_disposition_rec.workorder_id;
2708: l_sr_task_tbl(0).Operation_type := 'CREATE' ;
2952: PROCEDURE derive_columns(p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type)
2953: IS
2954:
2955: CURSOR get_organization_csr(p_workorder_id IN NUMBER) IS
2956: SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
2957: WHERE wo.workorder_id = p_workorder_id
2958: AND wo.visit_id = vi.visit_id;
2959:
2960: CURSOR get_instance_from_serial(p_item_id IN NUMBER, p_serial_num IN VARCHAR2) IS
3248:
3249: --Begin Performance Tuning
3250: CURSOR get_wo_instance_id(p_workorder_id IN NUMBER) IS
3251: SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
3252: FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
3253: WHERE workorder_id = p_workorder_id
3254: and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
3255: AND VST.VISIT_ID=VTS.VISIT_ID;
3256: --End performance Tunning
4151:
4152: --Validate_workorder----------------------------------------------------
4153: PROCEDURE validate_workorder(p_workorder_id IN NUMBER) IS
4154: CURSOR workorder_csr(p_workorder_id IN NUMBER) IS
4155: SELECT 'x' FROM AHL_WORKORDERS
4156: WHERE workorder_id = p_workorder_id;
4157:
4158: l_exist VARCHAR(1);
4159:
4198: --Validate_item---------------------------------------------
4199: PROCEDURE validate_item(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER, p_workorder_id IN NUMBER) IS
4200:
4201: CURSOR get_wo_organization_csr(p_workorder_id NUMBER) IS
4202: SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
4203: WHERE wo.workorder_id = p_workorder_id
4204: AND wo.visit_id = vi.visit_id;
4205:
4206: CURSOR val_item_csr(p_item_id NUMBER, p_organization_id NUMBER) IS
4321: SELECT 'x' from csi_item_instances
4322: where instance_id = p_instance_id;
4323:
4324: CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
4325: SELECT 'x' from csi_item_instances csi, ahl_workorders wo
4326: WHERE instance_id = p_instance_id
4327: and wo.wip_entity_id = csi.wip_job_id
4328: and csi.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
4329: and trunc(sysdate) between trunc(nvl(csi.active_start_date, sysdate)) and trunc(nvl(csi.active_end_date, sysdate));
4336:
4337: --Begin Performance Tuning
4338: CURSOR get_wo_instance_id(p_workorder_id IN NUMBER) IS
4339: SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
4340: FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
4341: WHERE workorder_id = p_workorder_id
4342: and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
4343: AND VST.VISIT_ID=VTS.VISIT_ID;
4344: --End Performance Tuning
5152: FUNCTION workorder_Editable(p_workorder_id IN NUMBER) RETURN BOOLEAN
5153: IS
5154:
5155: CURSOR workorder_status_csr(p_workorder_id IN NUMBER) IS
5156: SELECT status_code from ahl_workorders where workorder_id = p_workorder_id;
5157:
5158: l_status_code VARCHAR(30);
5159: BEGIN
5160: OPEN workorder_status_csr(p_workorder_id);
5180: FUNCTION get_unit_instance_id(p_workorder_id IN NUMBER) RETURN NUMBER
5181: IS
5182: CURSOR task_instance_csr IS
5183: SELECT VTS.INSTANCE_ID, VTS.VISIT_ID
5184: FROM AHL_VISIT_TASKS_B VTS, AHL_WORKORDERS WO
5185: WHERE WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
5186: WO.WORKORDER_ID = p_workorder_id;
5187:
5188: CURSOR visit_instance_csr (c_visit_id IN NUMBER) IS
5429: IS
5430: Cursor get_child_entities IS
5431: SELECT CHILD_OBJECT_ID
5432: FROM EAM_WO_RELATIONSHIPS
5433: WHERE PARENT_OBJECT_ID = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_workorder_id)
5434: AND PARENT_RELATIONSHIP_TYPE = 1
5435: ORDER BY CHILD_OBJECT_ID;
5436:
5437: Cursor get_matching_wo_dtls(c_wip_entity_id IN NUMBER) IS
5435: ORDER BY CHILD_OBJECT_ID;
5436:
5437: Cursor get_matching_wo_dtls(c_wip_entity_id IN NUMBER) IS
5438: SELECT WO.WORKORDER_ID, WO.WIP_ENTITY_ID, WO.VISIT_TASK_ID
5439: FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
5440: WHERE WIP_ENTITY_ID = c_wip_entity_id AND
5441: WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID AND
5442: TSK.TASK_TYPE_CODE <> 'SUMMARY';
5443:
5442: TSK.TASK_TYPE_CODE <> 'SUMMARY';
5443:
5444: Cursor get_non_summary_entity(c_wip_entity_id IN NUMBER) IS
5445: SELECT EAM.CHILD_OBJECT_ID
5446: FROM EAM_WO_RELATIONSHIPS EAM, AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
5447: WHERE EAM.CHILD_OBJECT_ID = WO.WIP_ENTITY_ID AND
5448: TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID AND
5449: TSK.TASK_TYPE_CODE <> 'SUMMARY'
5450: START WITH EAM.CHILD_OBJECT_ID = c_wip_entity_id
5530:
5531: l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||'Update_item_location';
5532: l_transaction_type_id NUMBER;
5533: l_return_val BOOLEAN;
5534: l_wo_status_code AHL_WORKORDERS.STATUS_CODE%TYPE;
5535: l_temp_wo_id NUMBER;
5536:
5537: -- For getting the status of the workorder from the wip_entity_id
5538: CURSOR ahl_wo_status_csr(c_wip_entity_id IN NUMBER) IS
5536:
5537: -- For getting the status of the workorder from the wip_entity_id
5538: CURSOR ahl_wo_status_csr(c_wip_entity_id IN NUMBER) IS
5539: select workorder_id, status_code
5540: FROM ahl_workorders
5541: WHERE wip_entity_id = c_wip_entity_id;
5542:
5543: -- For getting the the updated object_version number from csi_item_isntances
5544: CURSOR ahl_obj_ver_csr IS