[Home] [Help]
282: WO.MASTER_WORKORDER_FLAG MASTER_WORKORDER_FLAG,
283: VST.PROJECT_ID PROJECT_ID,
284: NVL2( WO.VISIT_TASK_ID,VTS.PROJECT_TASK_ID,TO_NUMBER(NULL)) PROJECT_TASK_ID,
285: NVL2( WO.VISIT_TASK_ID,VTS.SERVICE_REQUEST_ID,TO_NUMBER(NULL)) INCIDENT_ID
286: FROM AHL_WORKORDERS WO,
287: AHL_VISITS_B VST,
288: AHL_VISIT_TASKS_B VTS,
289: WIP_DISCRETE_JOBS WIP
290: WHERE WIP.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
585: FND_LOOKUP_VALUES LKUP4,
586: AHL_VWP_STAGES_VL ASTG,
587: MTL_SYSTEM_ITEMS_B_KFV MTSB,
588: AHL_ROUTES_B ARV,
589: AHL_WORKORDERS AWO,
590: PA_LOOKUPS PA
591: WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
592: AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
593: AND ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID (+)
710: FND_LOOKUP_VALUES LKUP1,
711: FND_LOOKUP_VALUES LKUP2,
712: FND_LOOKUP_VALUES LKUP3,
713: MTL_SYSTEM_ITEMS_B_KFV MTSB,
714: AHL_WORKORDERS AWO,
715: PA_LOOKUPS PA
716: WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
717: AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
718: AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
826: FND_LOOKUP_VALUES_VL LKUP1,
827: FND_LOOKUP_VALUES_VL LKUP2,
828: FND_LOOKUP_VALUES_VL LKUP3,
829: MTL_SYSTEM_ITEMS_B_KFV MTSB,
830: AHL_WORKORDERS AWO,
831: PA_LOOKUPS PA
832: WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
833: AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
834: AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
929: SELECT
930: /* scheduled_start_date,
931: scheduled_end_date
932: FROM
933: ahl_workorders_v
934: WHERE visit_id = p_visit_id
935: AND visit_task_id = p_visit_task_id;
936: */
937: -- AnRaj: Changed query for perf issue#2, Bug 4919465
937: -- AnRaj: Changed query for perf issue#2, Bug 4919465
938: wdj.scheduled_start_date scheduled_start_date,
939: wdj.scheduled_completion_date scheduled_end_date
940: FROM wip_discrete_jobs wdj,
941: ahl_workorders wo
942: WHERE wdj.wip_entity_id = wo.wip_entity_id
943: AND visit_id = p_visit_id
944: AND visit_task_id = p_visit_task_id;
945:
3773: l_old_Task_rec c_visit_task_det%rowtype;
3774:
3775: -- To find if WIP job is created for the Visit
3776: CURSOR c_job(x_id IN NUMBER) IS
3777: SELECT COUNT(*) FROM AHL_WORKORDERS
3778: WHERE VISIT_ID = x_id
3779: AND MASTER_WORKORDER_FLAG = 'Y'
3780: AND STATUS_CODE = 17;
3781:
3779: AND MASTER_WORKORDER_FLAG = 'Y'
3780: AND STATUS_CODE = 17;
3781:
3782: CURSOR c_Task_WO(x_task_id IN NUMBER) IS
3783: SELECT * FROM AHL_WORKORDERS
3784: WHERE VISIT_TASK_ID = x_task_id
3785: AND STATUS_CODE = 17;
3786: l_task_workrec c_Task_WO%ROWTYPE;
3787:
5895: FROM wip_discrete_jobs WHERE wip_entity_id =
5896: (
5897: SELECT
5898: wip_entity_id
5899: FROM ahl_workorders
5900: WHERE
5901: master_workorder_flag = 'Y' AND
5902: visit_task_id IS null AND
5903: status_code not in (22,7) and
6444:
6445: -- To find if WIP job is created for the Visit
6446: CURSOR c_wo_exist(x_task_id IN NUMBER)
6447: IS
6448: select 'X' from ahl_workorders
6449: where VISIT_TASK_ID=x_task_id;
6450:
6451: -- To find if WIP job is created for the Visit
6452: CURSOR c_workorders(x_task_id IN NUMBER)
6450:
6451: -- To find if WIP job is created for the Visit
6452: CURSOR c_workorders(x_task_id IN NUMBER)
6453: IS
6454: SELECT * FROM AHL_WORKORDERS
6455: WHERE VISIT_TASK_ID=x_task_id
6456: AND STATUS_CODE<>'22' AND STATUS_CODE<> '7';
6457:
6458: l_workrec c_workorders%ROWTYPE;
6882: AND T.VISIT_TASK_ID = x_id;
6883:
6884: -- To find if WIP job is created for the Visit
6885: CURSOR c_workorders(x_task_id IN NUMBER) IS
6886: SELECT * FROM AHL_WORKORDERS
6887: WHERE VISIT_TASK_ID=x_task_id
6888: AND STATUS_CODE<>'22' AND STATUS_CODE<> '7';
6889: c_workrec c_workorders%ROWTYPE;
6890:
7397: AND T.VISIT_TASK_ID = x_id;
7398:
7399: -- To find if WIP job is created for the Visit
7400: CURSOR c_workorders(x_task_id IN NUMBER) IS
7401: SELECT * FROM AHL_WORKORDERS
7402: WHERE VISIT_TASK_ID=x_task_id
7403: AND STATUS_CODE <> '22' AND STATUS_CODE <> '7';
7404: c_workrec c_workorders%ROWTYPE;
7405:
8860: WHERE visit_task_id = p_task_id;
8861:
8862: -- Cursor to get the RTS workorder for a repair batch
8863: CURSOR c_get_rts_wo_id(p_rpr_batch_task_id NUMBER, p_instance_id NUMBER) IS
8864: SELECT wip_entity_id FROM ahl_workorders
8865: WHERE status_code IN ('1', '3', '6', '19', '20', '17')
8866: --unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
8867: AND visit_task_id IN
8868: (SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt
10325:
10326: -- To find if WIP job is created for the Visit
10327: CURSOR c_wo_exist(x_task_id IN NUMBER)
10328: IS
10329: select 'X' from ahl_workorders
10330: where VISIT_TASK_ID=x_task_id;
10331:
10332: -- To find if WIP job is created for the Visit
10333: CURSOR c_workorders(x_task_id IN NUMBER)
10331:
10332: -- To find if WIP job is created for the Visit
10333: CURSOR c_workorders(x_task_id IN NUMBER)
10334: IS
10335: SELECT * FROM AHL_WORKORDERS
10336: WHERE VISIT_TASK_ID=x_task_id
10337: AND STATUS_CODE<>'22' AND STATUS_CODE<> '7';
10338:
10339: -- SKPATHAK :: 12-DEC-2010 :: ER 13416865