[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: --AnRaj: Changed query, Perf Bug#4908609,Issue#1
178: select WO.route_id route_id,
180: WDJ.SCHEDULED_START_DATE scheduled_start_date,
181: WO.status_code job_status_code,
182: WO.workorder_name job_number,
183: VST.ORGANIZATION_ID organization_id
184: from AHL_WORKORDERS WO,
185: WIP_DISCRETE_JOBS WDJ,
186: AHL_VISITS_VL VST,
187: AHL_VISIT_TASKS_VL VTS
188: where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID and
1351: WHERE disposition_id = p_disposition_id ;
1352:
1353:
1354: CURSOR get_organization_csr(p_workorder_id IN NUMBER) IS
1355: SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
1356: WHERE wo.workorder_id = p_workorder_id
1357: AND wo.visit_id = vi.visit_id;
1358:
1359: CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
1382:
1383: -- Added by jaramana on October 8, 2007 for ER 5903256
1384: CURSOR check_nr_wo_status_csr(p_nr_workorder_id IN NUMBER) IS
1385: SELECT 'Y'
1386: FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_UNIT_EFFECTIVITIES_B UE
1387: WHERE WO.workorder_id = NVL(p_nr_workorder_id, -1)
1388: AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
1389: AND UE.UNIT_EFFECTIVITY_ID = TSK.UNIT_EFFECTIVITY_ID
1390: AND UE.STATUS_CODE IS NULL;
1410: -- SATHAPLI::Bug 7111116, 21-May-2008, fix start
1411: -- Cursor to get the first released non-master workorder id for a given NR summary workorder.
1412: CURSOR get_rel_nonmaster_wo_id_csr(c_nr_wo_id NUMBER) IS
1413: SELECT workorder_id
1414: FROM ahl_workorders
1415: WHERE master_workorder_flag = 'N'
1416: AND wip_entity_id IN
1417: (SELECT child_object_id
1418: FROM wip_sched_relationships
1415: WHERE master_workorder_flag = 'N'
1416: AND wip_entity_id IN
1417: (SELECT child_object_id
1418: FROM wip_sched_relationships
1419: START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
1420: CONNECT BY parent_object_id = PRIOR child_object_id
1421: AND parent_object_type_id = PRIOR child_object_type_id
1422: AND relationship_type = 1
1423: )
2307:
2308: -- Cursor to check whether removed instance is in job or not.
2309: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
2310: SELECT 'Y'
2311: FROM ahl_workorders awo, csi_item_instances csi
2312: WHERE awo.wip_entity_id = csi.wip_job_id
2313: AND awo.workorder_id = p_workorder_id
2314: AND csi.instance_id = p_instance_id;
2315:
2357:
2358: l_sr_task_tbl(0).Problem_code := p_disposition_rec.problem_code;
2359: l_sr_task_tbl(0).duration := p_disposition_rec.duration;
2360:
2361: SELECT visit_id INTO l_visit_id FROM AHL_WORKORDERS WHERE workorder_id = p_disposition_rec.workorder_id;
2362:
2363: l_sr_task_tbl(0).Visit_id:= l_visit_id;
2364: l_sr_task_tbl(0).Originating_wo_id:= p_disposition_rec.workorder_id;
2365: l_sr_task_tbl(0).Operation_type := 'CREATE' ;
2529: PROCEDURE derive_columns(p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type)
2530: IS
2531:
2532: CURSOR get_organization_csr(p_workorder_id IN NUMBER) IS
2533: SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
2534: WHERE wo.workorder_id = p_workorder_id
2535: AND wo.visit_id = vi.visit_id;
2536:
2537: CURSOR get_instance_from_serial(p_item_id IN NUMBER, p_serial_num IN VARCHAR2) IS
2825:
2826: --Begin Performance Tuning
2827: CURSOR get_wo_instance_id(p_workorder_id IN NUMBER) IS
2828: SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
2829: FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
2830: WHERE workorder_id = p_workorder_id
2831: and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
2832: AND VST.VISIT_ID=VTS.VISIT_ID;
2833: --End performance Tunning
3691:
3692: --Validate_workorder----------------------------------------------------
3693: PROCEDURE validate_workorder(p_workorder_id IN NUMBER) IS
3694: CURSOR workorder_csr(p_workorder_id IN NUMBER) IS
3695: SELECT 'x' FROM AHL_WORKORDERS
3696: WHERE workorder_id = p_workorder_id;
3697:
3698: l_exist VARCHAR(1);
3699:
3738: --Validate_item---------------------------------------------
3739: PROCEDURE validate_item(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER, p_workorder_id IN NUMBER) IS
3740:
3741: CURSOR get_wo_organization_csr(p_workorder_id NUMBER) IS
3742: SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
3743: WHERE wo.workorder_id = p_workorder_id
3744: AND wo.visit_id = vi.visit_id;
3745:
3746: CURSOR val_item_csr(p_item_id NUMBER, p_organization_id NUMBER) IS
3861: SELECT 'x' from csi_item_instances
3862: where instance_id = p_instance_id;
3863:
3864: CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
3865: SELECT 'x' from csi_item_instances csi, ahl_workorders wo
3866: WHERE instance_id = p_instance_id
3867: and wo.wip_entity_id = csi.wip_job_id
3868: and csi.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
3869: and trunc(sysdate) between trunc(nvl(csi.active_start_date, sysdate)) and trunc(nvl(csi.active_end_date, sysdate));
3876:
3877: --Begin Performance Tuning
3878: CURSOR get_wo_instance_id(p_workorder_id IN NUMBER) IS
3879: SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
3880: FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
3881: WHERE workorder_id = p_workorder_id
3882: and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
3883: AND VST.VISIT_ID=VTS.VISIT_ID;
3884: --End Performance Tuning
4692: FUNCTION workorder_Editable(p_workorder_id IN NUMBER) RETURN BOOLEAN
4693: IS
4694:
4695: CURSOR workorder_status_csr(p_workorder_id IN NUMBER) IS
4696: SELECT status_code from ahl_workorders where workorder_id = p_workorder_id;
4697:
4698: l_status_code VARCHAR(30);
4699: BEGIN
4700: OPEN workorder_status_csr(p_workorder_id);
4720: FUNCTION get_unit_instance_id(p_workorder_id IN NUMBER) RETURN NUMBER
4721: IS
4722: CURSOR task_instance_csr IS
4723: SELECT VTS.INSTANCE_ID, VTS.VISIT_ID
4724: FROM AHL_VISIT_TASKS_B VTS, AHL_WORKORDERS WO
4725: WHERE WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
4726: WO.WORKORDER_ID = p_workorder_id;
4727:
4728: CURSOR visit_instance_csr (c_visit_id IN NUMBER) IS
4969: IS
4970: Cursor get_child_entities IS
4971: SELECT CHILD_OBJECT_ID
4972: FROM EAM_WO_RELATIONSHIPS
4973: WHERE PARENT_OBJECT_ID = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_workorder_id)
4974: AND PARENT_RELATIONSHIP_TYPE = 1
4975: ORDER BY CHILD_OBJECT_ID;
4976:
4977: Cursor get_matching_wo_dtls(c_wip_entity_id IN NUMBER) IS
4975: ORDER BY CHILD_OBJECT_ID;
4976:
4977: Cursor get_matching_wo_dtls(c_wip_entity_id IN NUMBER) IS
4978: SELECT WO.WORKORDER_ID, WO.WIP_ENTITY_ID, WO.VISIT_TASK_ID
4979: FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
4980: WHERE WIP_ENTITY_ID = c_wip_entity_id AND
4981: WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID AND
4982: TSK.TASK_TYPE_CODE <> 'SUMMARY';
4983:
4982: TSK.TASK_TYPE_CODE <> 'SUMMARY';
4983:
4984: Cursor get_non_summary_entity(c_wip_entity_id IN NUMBER) IS
4985: SELECT EAM.CHILD_OBJECT_ID
4986: FROM EAM_WO_RELATIONSHIPS EAM, AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
4987: WHERE EAM.CHILD_OBJECT_ID = WO.WIP_ENTITY_ID AND
4988: TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID AND
4989: TSK.TASK_TYPE_CODE <> 'SUMMARY'
4990: START WITH EAM.CHILD_OBJECT_ID = c_wip_entity_id
5066:
5067: l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||'Update_item_location';
5068: l_transaction_type_id NUMBER;
5069: l_return_val BOOLEAN;
5070: l_wo_status_code AHL_WORKORDERS.STATUS_CODE%TYPE;
5071: l_temp_wo_id NUMBER;
5072:
5073: -- For getting the status of the workorder from the wip_entity_id
5074: CURSOR ahl_wo_status_csr(c_wip_entity_id IN NUMBER) IS
5072:
5073: -- For getting the status of the workorder from the wip_entity_id
5074: CURSOR ahl_wo_status_csr(c_wip_entity_id IN NUMBER) IS
5075: select workorder_id, status_code
5076: FROM ahl_workorders
5077: WHERE wip_entity_id = c_wip_entity_id;
5078:
5079: -- For getting the the updated object_version number from csi_item_isntances
5080: CURSOR ahl_obj_ver_csr IS