[Home] [Help]
464: --CHANGES by jrotich bug #13028686 begin
465: CURSOR c_stages_without_types(c_visit_id IN NUMBER ) IS
466: SELECT DISTINCT STAGES.STAGE_ID ,
467: STAGES.STAGE_NAME
468: FROM AHL_VISIT_TASKS_B TASKS,
469: AHL_VWP_STAGES_VL STAGES
470: WHERE
471: TASKS.VISIT_ID= c_visit_id AND
472: STAGES.STAGE_ID=TASKS.STAGE_ID AND
1304: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1305: END IF;
1306:
1307: IF l_return_status = 'S' THEN
1308: UPDATE AHL_VISIT_TASKS_B
1309: SET PROJECT_TASK_ID = l_task_id,
1310: OBJECT_VERSION_NUMBER = object_version_number + 1,
1311: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
1312: LAST_UPDATE_DATE = SYSDATE,
2278: l_task_out(z).pa_task_id);
2279: END IF;
2280:
2281: --PRAKKUM :: PIE :: 13-OCT-2010 ::START
2282: -- Update AHL_VISIT_TASKS_B only for non-rollup tasks
2283: IF (INSTR(l_task_in(z).PA_TASK_NUMBER, '-') = 0) THEN
2284: OPEN c_Task_OVN(p_visit_id,l_task_in(z).PA_TASK_NUMBER);
2285: FETCH c_Task_OVN INTO l_obj_version;
2286: CLOSE c_Task_OVN;
2284: OPEN c_Task_OVN(p_visit_id,l_task_in(z).PA_TASK_NUMBER);
2285: FETCH c_Task_OVN INTO l_obj_version;
2286: CLOSE c_Task_OVN;
2287:
2288: UPDATE AHL_VISIT_TASKS_B
2289: SET PROJECT_TASK_ID = l_task_out(z).pa_task_id,
2290: OBJECT_VERSION_NUMBER = l_obj_version + 1,
2291: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
2292: LAST_UPDATE_DATE = SYSDATE,
2638: WHERE
2639: visit_id = c_visit_id AND
2640: EXISTS (
2641: SELECT 1
2642: FROM ahl_visit_tasks_b avt,
2643: pa_tasks pt
2644: WHERE avt.visit_id = vst.visit_id
2645: AND avt.visit_id = c_visit_id
2646: AND avt.repair_batch_name IS NOT NULL
3715: OPEN c_task_proj(c_visit_rec.PROJECT_ID);
3716: LOOP
3717: FETCH c_task_proj INTO c_task_proj_rec;
3718: EXIT WHEN c_task_proj%NOTFOUND;
3719: -- Update AHL_VISIT_TASKS_B only for non-rollup tasks
3720: IF (c_visit_rec.TOP_PROJECT_TASK_ID IS NULL
3721: AND l_top_task_id IS NULL) THEN
3722: IF (INSTR(c_task_proj_rec.task_number, '-') = 0) THEN -- tasks not under a visit level top task
3723: UPDATE AHL_VISIT_TASKS_B SET
3719: -- Update AHL_VISIT_TASKS_B only for non-rollup tasks
3720: IF (c_visit_rec.TOP_PROJECT_TASK_ID IS NULL
3721: AND l_top_task_id IS NULL) THEN
3722: IF (INSTR(c_task_proj_rec.task_number, '-') = 0) THEN -- tasks not under a visit level top task
3723: UPDATE AHL_VISIT_TASKS_B SET
3724: PROJECT_TASK_ID = c_task_proj_rec.task_id,
3725: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3726: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
3727: LAST_UPDATE_DATE = SYSDATE,
3734: AND PROJECT_TASK_ID is NULL;
3735: END IF;
3736: ELSE
3737: IF (INSTR(c_task_proj_rec.task_number,'-',1,2) = 0) THEN -- tasks under a visit top task
3738: UPDATE AHL_VISIT_TASKS_B SET
3739: PROJECT_TASK_ID = c_task_proj_rec.task_id,
3740: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3741: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
3742: LAST_UPDATE_DATE = SYSDATE,
4568: * in cursor query to prevent fetching tasks that are Closed due to Repair Batch closure.
4569: */
4570: CURSOR c_route_chk (x_id IN NUMBER) IS
4571: SELECT VT.MR_ROUTE_ID
4572: FROM AHL_VISIT_TASKS_B VT
4573: WHERE VT.VISIT_ID = x_id
4574: AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
4575: AND VT.MR_Route_ID IS NOT NULL
4576: AND NOT EXISTS (
4587: /* MANESING::Component Maintenance Planning Project, 07-Aug-2011, added visit task CLOSED status
4588: * in cursor query to prevent fetching tasks that are Closed due to Repair Batch closure.
4589: */
4590: CURSOR c_route_tsk (x_id IN NUMBER) IS
4591: SELECT VISIT_TASK_ID, VISIT_TASK_NUMBER FROM AHL_VISIT_TASKS_B
4592: WHERE VISIT_ID = x_id
4593: AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
4594: AND MR_Route_ID IS NULL
4595: AND TASK_TYPE_CODE <> 'SUMMARY';
4609: /* MANESING::Component Maintenance Planning Project, 07-Aug-2011, added visit task CLOSED status
4610: * in cursor query to prevent fetching tasks that are Closed due to Repair Batch closure.
4611: */
4612: CURSOR c_dept_task (x_id IN NUMBER) IS
4613: SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B
4614: WHERE VISIT_ID = x_id
4615: AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
4616: AND DEPARTMENT_ID IS NOT NULL;
4617: c_dept_task_rec c_dept_task%ROWTYPE;
4621: /* MANESING::Component Maintenance Planning Project, 07-Aug-2011, added visit task CLOSED status
4622: * in cursor query to prevent fetching tasks that are Closed due to Repair Batch closure.
4623: */
4624: CURSOR c_dept_tsk (x_id IN NUMBER) IS
4625: SELECT DEPARTMENT_ID, VISIT_TASK_NUMBER FROM AHL_VISIT_TASKS_B
4626: WHERE VISIT_ID = x_id
4627: AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
4628: AND DEPARTMENT_ID IS NOT NULL;
4629: c_dept_tsk_rec c_dept_tsk%ROWTYPE;
4661:
4662: c_uc_status_rec c_uc_status%ROWTYPE;
4663: -- SKPATHAK :: ER:12730539 :: 01-AUG-2011
4664: CURSOR get_task_status (c_task_id IN NUMBER) IS
4665: SELECT status_code FROM ahl_visit_tasks_b
4666: WHERE visit_task_id = c_task_id;
4667: l_task_status VARCHAR2(30);
4668:
4669: BEGIN
5524: AND MASTER_WORKORDER_FLAG = 'Y';
5525:
5526: CURSOR get_task_dtls(c_visit_task_id IN NUMBER) IS
5527: SELECT visit_task_number, task_type_code, SERVICE_REQUEST_ID, MR_ID, originating_task_id
5528: FROM AHL_VISIT_TASKS_B
5529: WHERE visit_task_id = c_visit_task_id;
5530:
5531: -- Get all the Parent Task Records For the Visit Task
5532: CURSOR get_parent_task_dtls(c_visit_task_id NUMBER) IS
5530:
5531: -- Get all the Parent Task Records For the Visit Task
5532: CURSOR get_parent_task_dtls(c_visit_task_id NUMBER) IS
5533: SELECT PARENT.visit_task_number, PARENT.visit_task_id
5534: FROM AHL_VISIT_TASKS_B PARENT, AHL_VISIT_TASKS_B CHILD
5535: WHERE PARENT.visit_task_id = CHILD.originating_task_id
5536: AND CHILD.visit_task_id = c_visit_task_id;
5537:
5538: CURSOR does_wo_exist_csr(c_visit_task_id NUMBER) IS
5670: IS
5671: SELECT visit_task_id,
5672: visit_task_number,
5673: NVL(originating_task_id, -1)
5674: FROM AHL_VISIT_TASKS_B
5675: WHERE visit_id = c_visit_id
5676: AND task_type_code='SUMMARY'
5677: AND mr_id IS NOT NULL
5678: ORDER BY 3;*/
5683: IS
5684: SELECT visit_task_id,
5685: visit_task_number,
5686: NVL(originating_task_id, -1)
5687: FROM AHL_VISIT_TASKS_B
5688: WHERE visit_id = c_visit_id
5689: AND task_type_code='SUMMARY'
5690: AND (mr_id IS NOT NULL OR unit_effectivity_id IS NOT NULL)
5691: AND NVL(STATUS_CODE, 'X') <> 'DELETED'
5712: -- Get all the Tasks associated to a MR.
5713: CURSOR get_tasks_for_mr(c_visit_id NUMBER, c_mr_task_id NUMBER)
5714: IS
5715: SELECT visit_task_number, visit_task_id
5716: FROM AHL_VISIT_TASKS_B
5717: WHERE visit_id = c_visit_id
5718: AND originating_task_id = c_mr_task_id
5719: AND task_type_code <> 'SUMMARY'
5720: AND NVL(STATUS_CODE, 'X') = 'PLANNING' --Srini Bug #4075702
5724: -- Get all the Unassociated Tasks and Stage Tasks.
5725: CURSOR get_unassoc_stage_tasks(c_visit_id NUMBER)
5726: IS
5727: SELECT visit_task_number, visit_task_id
5728: FROM AHL_VISIT_TASKS_B
5729: WHERE visit_id = c_visit_id
5730: AND NVL(STATUS_CODE, 'X') = 'PLANNING'
5731: AND task_type_code IN ('UNASSOCIATED','STAGE');
5732:
5913: CURSOR get_tech_dependencies(c_visit_task_id NUMBER)
5914: IS
5915: SELECT PARENT.visit_task_number parent_task_number,
5916: CHILD.visit_task_number child_task_number
5917: FROM AHL_VISIT_TASKS_B PARENT,
5918: AHL_VISIT_TASKS_B CHILD,
5919: AHL_TASK_LINKS LINK
5920: WHERE PARENT.visit_task_id = LINK.parent_task_id
5921: AND CHILD.visit_task_id = LINK.visit_task_id
5914: IS
5915: SELECT PARENT.visit_task_number parent_task_number,
5916: CHILD.visit_task_number child_task_number
5917: FROM AHL_VISIT_TASKS_B PARENT,
5918: AHL_VISIT_TASKS_B CHILD,
5919: AHL_TASK_LINKS LINK
5920: WHERE PARENT.visit_task_id = LINK.parent_task_id
5921: AND CHILD.visit_task_id = LINK.visit_task_id
5922: AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING'
6012: CURSOR get_tech_dependencies(c_visit_id NUMBER)
6013: IS
6014: SELECT PARENT.visit_task_number parent_task_number,
6015: CHILD.visit_task_number child_task_number
6016: FROM AHL_VISIT_TASKS_B PARENT,
6017: AHL_VISIT_TASKS_B CHILD,
6018: AHL_TASK_LINKS LINK
6019: WHERE PARENT.visit_task_id = LINK.parent_task_id
6020: AND CHILD.visit_task_id = LINK.visit_task_id
6013: IS
6014: SELECT PARENT.visit_task_number parent_task_number,
6015: CHILD.visit_task_number child_task_number
6016: FROM AHL_VISIT_TASKS_B PARENT,
6017: AHL_VISIT_TASKS_B CHILD,
6018: AHL_TASK_LINKS LINK
6019: WHERE PARENT.visit_task_id = LINK.parent_task_id
6020: AND CHILD.visit_task_id = LINK.visit_task_id
6021: AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING' --Srini Bug #4075702
6195: -- Get workorder status for the coresponding job in originating visit
6196: CURSOR c_wo_status (x_orig_visit_id IN NUMBER, x_visit_task_id IN NUMBER) IS
6197: SELECT wo.status_code
6198: FROM AHL_WORKORDERS WO,
6199: AHL_VISIT_TASKS_B t
6200: WHERE wo.VISIT_ID = x_orig_visit_id
6201: AND wo.visit_task_id = t.visit_task_id
6202: AND t.visit_task_number = (Select visit_task_number
6203: from ahl_visit_tasks_b
6199: AHL_VISIT_TASKS_B t
6200: WHERE wo.VISIT_ID = x_orig_visit_id
6201: AND wo.visit_task_id = t.visit_task_id
6202: AND t.visit_task_number = (Select visit_task_number
6203: from ahl_visit_tasks_b
6204: where visit_task_id = x_visit_task_id)
6205: AND wo.STATUS_CODE not in ('22','7'); --(22-Deleted, 7-Cancelled)
6206: -- yazhou end
6207:
6211: CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
6212: SELECT min(start_date_time), max(end_date_time)
6213: --TCHIMIRA::19-FEB-2010::BUG 9384614
6214: -- Use the base table instead of the vl view
6215: FROM ahl_visit_tasks_b VST
6216: START WITH visit_task_id = x_task_id
6217: -- anraj changed coz the nvl on the RHS is not required
6218: -- AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
6219: AND NVL(VST.status_code, 'Y') <> 'DELETED'
6222: -- Added by Jerry on 08/13/2004 per Yan and Alex's request
6223: -- Yan added the condition to filter out summary tasks on 08/31/2005
6224: CURSOR get_independent_tasks(c_visit_id IN NUMBER) IS
6225: select t.visit_task_id
6226: from ahl_visit_tasks_b t,
6227: ahl_visits_b v
6228: where v.visit_id = c_visit_id
6229: and v.visit_id = t.visit_id
6230: --and v.status_code = 'PARTIALLY RELEASED'
6265: CURSOR get_wo_dtls_for_mrtasks_cur (p_task_id IN NUMBER) IS
6266: --TCHIMIRA::Bug 9149770 ::09-FEB-2010
6267: --use substrb and lengthb instead of substr and length respectively
6268: SELECT ar.route_no||'.'||substrb(ar.title,1,(240 - (lengthb(ar.route_no) + 1))) workorder_description
6269: FROM ahl_routes_vl ar,ahl_visit_tasks_b avt, ahl_mr_routes mrr
6270: WHERE avt.visit_task_id = p_task_id
6271: AND nvl(avt.status_code,'Y') = 'PLANNING'
6272: AND avt.mr_route_id = mrr.mr_route_id
6273: AND mrr.route_id = ar.route_id;
6276: -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063
6277: -- Get all the planning stages' tasks and duplicate tasks of the visit
6278: CURSOR get_visit_tasks_to_cancel (c_visit_id IN NUMBER) IS
6279: SELECT wo.workorder_id, stage.stage_id, task.task_type_code
6280: FROM ahl_visit_tasks_b task, ahl_vwp_stages_b stage, ahl_workorders wo
6281: WHERE task.visit_id = c_visit_id
6282: AND task.stage_id = stage.stage_id(+)
6283: -- do not select the default stage task
6284: AND ((task.task_type_code = 'STAGE' AND task.stage_id IS NOT NULL)
6290: -- TCHIMIRA :: 28-JUN-2011 :: VWPE: ER:12673125 :: START
6291: -- Cursor to fetch default stage task ID
6292: CURSOR c_get_vst_def_stage_task (c_visit_id IN NUMBER) IS
6293: SELECT VISIT_TASK_ID
6294: FROM AHL_VISIT_TASKS_B
6295: WHERE STAGE_ID IS NULL
6296: AND TASK_TYPE_CODE = 'STAGE'
6297: AND VISIT_ID = c_visit_id;
6298: -- TCHIMIRA :: 28-JUN-2011 :: VWPE: ER:12673125 :: END
6298: -- TCHIMIRA :: 28-JUN-2011 :: VWPE: ER:12673125 :: END
6299:
6300: -- SKPATHAK :: ER:12730539 :: 01-AUG-2011
6301: CURSOR get_task_status (c_task_id IN NUMBER) IS
6302: SELECT status_code FROM ahl_visit_tasks_b
6303: WHERE visit_task_id = c_task_id;
6304: l_task_status VARCHAR2(30);
6305:
6306: -- MANESING::Component Maintenance Planning Project, 02-Nov-2011, added following cursor
6325: -- MANESING::Component Maintenance Planning Project, 02-Nov-2011, added following cursor
6326: -- Cursor to find a serialized instance in any of Repair Batches within the given Component Visit
6327: CURSOR get_serialized_inst_csr (c_visit_id NUMBER) IS
6328: SELECT INST.inventory_item_id, INST.instance_id
6329: FROM AHL_VISIT_TASKS_B TSK,
6330: CSI_ITEM_INSTANCES INST
6331: WHERE TSK.visit_id = c_visit_id
6332: AND TSK.status_code NOT IN ('DELETED', 'CANCELLED', 'CLOSED')
6333: AND TSK.instance_id = INST.instance_id
6683:
6684: -- Delete UMP BOM data when UE is pushed to production (any status)
6685: DELETE FROM AHL_UMP_RESOURCE_REQMNTS
6686: WHERE object_type = 'UE'
6687: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6688:
6689: DELETE FROM AHL_UMP_MATERIAL_REQMNTS
6690: WHERE object_type = 'UE'
6691: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6687: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6688:
6689: DELETE FROM AHL_UMP_MATERIAL_REQMNTS
6690: WHERE object_type = 'UE'
6691: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6692:
6693: DELETE FROM AHL_UMP_MAINT_REQMNTS
6694: WHERE object_type = 'UE'
6695: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6691: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6692:
6693: DELETE FROM AHL_UMP_MAINT_REQMNTS
6694: WHERE object_type = 'UE'
6695: AND MAINTENANCE_REQMNT_ID IN (SELECT unit_effectivity_id FROM ahl_visit_tasks_b WHERE visit_id = p_visit_id);
6696: -- SKPATHAK :: ER:12730539 :: 01-AUG-2011 :: END
6697:
6698: -- Start for 11.5.10 release
6699: -- By shbhanda 05-Jun-03
7497:
7498: IF ( p_module_type <> 'CST' ) THEN
7499: IF p_orig_visit_id is null THEN
7500:
7501: UPDATE AHL_VISIT_TASKS_B
7502: SET STATUS_CODE = 'RELEASED',
7503: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
7504: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
7505: LAST_UPDATE_DATE = SYSDATE,
7509: AND STATUS_CODE IN ('PLANNING', 'PARTIALLY RELEASED')
7510: --SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Update the status of stage and duplicate tasks to CANCELLED
7511: AND TASK_TYPE_CODE NOT IN ('STAGE','DUPLICATE');
7512:
7513: UPDATE AHL_VISIT_TASKS_B
7514: SET STATUS_CODE = 'CANCELLED',
7515: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
7516: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
7517: LAST_UPDATE_DATE = SYSDATE,
7522: AND ((TASK_TYPE_CODE = 'STAGE' AND STAGE_ID IS NOT NULL)
7523: OR TASK_TYPE_CODE = 'DUPLICATE');
7524:
7525: ELSE
7526: UPDATE AHL_VISIT_TASKS_B
7527: SET STATUS_CODE = 'RELEASED',
7528: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
7529: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
7530: LAST_UPDATE_DATE = SYSDATE,
7534: AND STATUS_CODE IN ('PLANNING', 'PARTIALLY RELEASED')
7535: --SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Need not s update the status of stage and duplicate tasks to RELEASED
7536: AND TASK_TYPE_CODE NOT IN ('STAGE','DUPLICATE')
7537: AND VISIT_TASK_NUMBER in (Select VISIT_TASK_NUMBER
7538: FROM ahl_visit_tasks_b
7539: where visit_id = p_orig_visit_id
7540: AND STATUS_CODE = 'RELEASED');
7541:
7542: --SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Update the status of stage and duplicate tasks to CANCELLED
7539: where visit_id = p_orig_visit_id
7540: AND STATUS_CODE = 'RELEASED');
7541:
7542: --SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Update the status of stage and duplicate tasks to CANCELLED
7543: UPDATE AHL_VISIT_TASKS_B
7544: SET STATUS_CODE = 'CANCELLED',
7545: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
7546: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
7547: LAST_UPDATE_DATE = SYSDATE,
7550: WHERE VISIT_ID = p_visit_id
7551: AND STATUS_CODE = 'PLANNING'
7552: AND TASK_TYPE_CODE IN ('STAGE','DUPLICATE')
7553: AND VISIT_TASK_NUMBER in (Select VISIT_TASK_NUMBER
7554: FROM ahl_visit_tasks_b
7555: where visit_id = p_orig_visit_id
7556: AND STATUS_CODE = 'RELEASED');
7557: END IF;
7558: END IF;
7792: --CHANGES by jrotich bug #13028686 begin
7793: CURSOR c_stages_without_types(c_visit_id IN NUMBER ) IS
7794: SELECT DISTINCT STAGES.STAGE_ID ,
7795: STAGES.STAGE_NAME
7796: FROM AHL_VISIT_TASKS_B TASKS,
7797: AHL_VWP_STAGES_VL STAGES
7798: WHERE
7799: TASKS.VISIT_ID= c_visit_id AND
7800: STAGES.STAGE_ID=TASKS.STAGE_ID AND
8206: c_visit_info_rec c_visit_info%ROWTYPE;
8207:
8208: -- SKPATHAK :: ER:12730539 :: 01-AUG-2011
8209: CURSOR get_task_status (c_task_id IN NUMBER) IS
8210: SELECT status_code FROM ahl_visit_tasks_b
8211: WHERE visit_task_id = c_task_id;
8212: l_task_status VARCHAR2(30);
8213:
8214: BEGIN
8466:
8467: FOR i IN l_tasks_tbl.FIRST..l_tasks_tbl.LAST
8468: LOOP
8469: -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Call adjust_task_times only if past task dates are null
8470: SELECT past_task_start_date INTO l_tasks_tbl(i).past_task_start_date FROM ahl_visit_tasks_b WHERE visit_task_id = l_tasks_tbl(i).visit_task_id;
8471: IF l_tasks_tbl(i).past_task_start_date IS NULL THEN
8472: AHL_VWP_TIMES_PVT.adjust_task_times
8473: (p_api_version => l_api_version,
8474: p_init_msg_list => p_init_msg_list,
8704: --cursor to fetch the details of the tasks that have been pushe to production
8705: CURSOR c_task_dtls(x_vst_task_id IN NUMBER) IS
8706: SELECT mr_id,visit_id,visit_task_id,status_code,task_type_code,nvl(originating_task_id,0) as originating_task_id,
8707: summary_task_flag, inventory_item_id,item_organization_id,visit_task_number,end_date_time
8708: FROM ahl_visit_tasks_b
8709: WHERE visit_task_id = x_vst_task_id
8710: -- SKPATHAK :: 02-MAY-2011 :: VWPE:: Commented the below line since summary tasks may not be in planning
8711: --AND NVL(status_code,'X') in ('PLANNING')
8712: order by visit_task_id;
8716: --cursor to fetch the summary task id of the planned/unplanned task
8717: CURSOR c_summary_tsk_dtl (x_originating_tsk_id IN NUMBER) IS
8718: SELECT mr_id,visit_id,visit_task_id,status_code,task_type_code,nvl(originating_task_id,0) as originating_task_id,
8719: summary_task_flag, inventory_item_id,item_organization_id,visit_task_number,end_date_time
8720: FROM ahl_visit_tasks_b
8721: WHERE visit_task_id = x_originating_tsk_id
8722: AND NVL(status_code,'X') = 'PLANNING'
8723: AND task_type_code = 'SUMMARY';
8724:
8727: -- SKPATHAK :: 13-JUN-2011 :: VWPE:: START
8728: -- Fetch the summary tasks which are in partially released or released status
8729: CURSOR c_par_rel_sum_tsk_dtl (x_originating_tsk_id IN NUMBER) IS
8730: SELECT visit_task_id,nvl(originating_task_id,0) as originating_task_id,visit_task_number
8731: FROM ahl_visit_tasks_b
8732: WHERE visit_task_id = x_originating_tsk_id
8733: AND NVL(status_code,'X') IN ( 'PARTIALLY RELEASED', 'RELEASED')
8734: AND task_type_code = 'SUMMARY';
8735: c_par_rel_sum_tsk_rec c_par_rel_sum_tsk_dtl%ROWTYPE;
8736:
8737: CURSOR get_child_planning_tasks (c_task_id IN NUMBER)
8738: IS
8739: SELECT visit_task_id
8740: FROM ahl_visit_tasks_b
8741: WHERE originating_task_id = c_task_id
8742: AND status_code = 'PLANNING';
8743:
8744: l_is_all_tasks_rel VARCHAR2(1) := 'N';
8761:
8762: -- Get all the Parent Task Dependencies.
8763: CURSOR get_parent_task_dependencies (x_vst_task_id IN NUMBER) IS
8764: SELECT P.visit_task_number , P.visit_task_id
8765: FROM ahl_visit_tasks_b P,
8766: ahl_task_links L
8767: WHERE P.visit_task_id = L.parent_task_id
8768: AND L.visit_task_id = x_vst_task_id;
8769:
8769:
8770: -- Get all the Child Task Dependencies.
8771: CURSOR get_child_task_dependencies (x_vst_task_id IN NUMBER) IS
8772: SELECT C.visit_task_number ,C.visit_task_id
8773: FROM ahl_visit_tasks_b C,
8774: ahl_task_links L
8775: WHERE C.visit_task_id = L.visit_task_id
8776: AND L.parent_task_id = x_vst_task_id;
8777:
8781: /*cursor to fetch all the child MR's/tasks for the summary MR to ensure all the dtl tasks are pushed to production*/
8782: CURSOR c_dtl_task_sum (x_vst_task_id IN NUMBER) IS
8783: select mr_id,visit_id,visit_task_id,status_code,task_type_code,nvl(originating_task_id,0) as originating_task_id,
8784: summary_task_flag, inventory_item_id,item_organization_id,visit_task_number,end_date_time
8785: FROM ahl_visit_tasks_b
8786: -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: Summary tasks may be in partially implemented status
8787: WHERE NVL(status_code,'X') in ('PLANNING','PARTIALLY RELEASED')
8788: START WITH visit_task_id = x_vst_task_id
8789: CONNECT BY originating_task_id = PRIOR visit_task_id
8795: CURSOR c_task_stage_details(c_vst_task_id IN NUMBER) IS
8796: SELECT TASKS.STAGE_ID,
8797: STAGES.STAGE_NAME,
8798: STAGE_TYPES.STAGE_TYPE_CODE
8799: FROM AHL_VISIT_TASKS_B TASKS,
8800: AHL_VISIT_STAGE_TYP_ASOC STAGE_TYPES,
8801: AHL_VWP_STAGES_VL STAGES
8802: WHERE
8803: TASKS.VISIT_TASK_ID= c_vst_task_id AND
9007: x_tasks_tbl(j).originating_task_id := c_tsk_dtls_rec.originating_task_id;
9008: x_tasks_tbl(j).visit_task_number := c_tsk_dtls_rec.visit_task_number;
9009:
9010: --Also update the summary task to Released status
9011: UPDATE ahl_visit_tasks_b
9012: SET status_code = 'RELEASED',
9013: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
9014: LAST_UPDATE_DATE = SYSDATE,
9015: LAST_UPDATED_BY = Fnd_Global.USER_ID,
9279:
9280: --fetch work order for the task
9281: CURSOR c_task_wo (x_visit_id IN NUMBER,x_visit_task_id IN NUMBER) IS
9282: SELECT v.visit_task_id, wo.workorder_id, wo.status_code, wip.scheduled_start_date,wip.scheduled_completion_date,wo.object_version_number
9283: FROM ahl_visit_tasks_b v, ahl_workorders wo, wip_discrete_jobs wip
9284: WHERE v.visit_id = x_visit_id
9285: AND v.visit_id = wo.visit_id
9286: AND wo.visit_task_id IS NOT NULL
9287: AND wo.visit_task_id = x_visit_task_id
9291: c_task_wo_rec c_task_wo%ROWTYPE;
9292:
9293: --fetch summary task flag for the task
9294: CURSOR c_fet_sum_task_flg (x_visit_id IN NUMBER, x_visit_task_id IN NUMBER) IS
9295: SELECT summary_task_flag FROM ahl_visit_tasks_b
9296: WHERE visit_task_id = x_visit_task_id;
9297:
9298: --fetch all the tasks in the visit
9299: CURSOR c_all_task_dtl (x_visit_id IN NUMBER) IS
9296: WHERE visit_task_id = x_visit_task_id;
9297:
9298: --fetch all the tasks in the visit
9299: CURSOR c_all_task_dtl (x_visit_id IN NUMBER) IS
9300: SELECT count(visit_task_id) FROM ahl_visit_tasks_b
9301: WHERE visit_id = x_visit_id
9302: AND NVL(status_code,'X') IN ('PLANNING');
9303:
9304: --get summary task start, end time
9303:
9304: --get summary task start, end time
9305: CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
9306: SELECT min(start_date_time), max(end_date_time)
9307: FROM ahl_visit_tasks_b VST
9308: START WITH visit_task_id = x_task_id
9309: AND NVL(VST.status_code, 'Y') <> 'DELETED'
9310: CONNECT BY originating_task_id = PRIOR visit_task_id;
9311:
9327: --Inventory item id and instance id to be defaulted when
9328: --the item and instance are not specified at the header level
9329: CURSOR default_task_inst_dtls(c_task_id IN NUMBER) IS
9330: SELECT inventory_item_id,instance_id
9331: FROM ahl_visit_tasks_b
9332: WHERE visit_task_id = c_task_id;
9333:
9334: def_task_inst_rec default_task_inst_dtls%ROWTYPE;
9335:
9338: CURSOR get_wo_dtls_for_mrtasks_cur (p_task_id IN NUMBER) IS
9339: --TCHIMIRA::Bug 9149770 ::09-FEB-2010
9340: --use substrb and lengthb instead of substr and length respectively
9341: SELECT ar.route_no||'.'||substrb(ar.title,1,(240 - (lengthb(ar.route_no) + 1))) workorder_description
9342: FROM ahl_routes_vl ar,ahl_visit_tasks_b avt, ahl_mr_routes mrr
9343: WHERE avt.visit_task_id = p_task_id
9344: and nvl(avt.status_code,'Y') = 'PLANNING'
9345: and avt.mr_route_id = mrr.mr_route_id
9346: and mrr.route_id = ar.route_id;
9364: l_sr_mwo_rec c_get_sr_mwo_dtls%ROWTYPE;
9365:
9366: --get SR task details
9367: CURSOR c_get_sr_task_dtls(p_sr_task_id IN NUMBER) IS
9368: SELECT * FROM AHL_VISIT_TASKS_B vst
9369: WHERE vst.TASK_TYPE_CODE = 'SUMMARY'
9370: AND vst.MR_ID IS NULL
9371: AND vst.SERVICE_REQUEST_ID =
9372: (SELECT vst1.SERVICE_REQUEST_ID
9369: WHERE vst.TASK_TYPE_CODE = 'SUMMARY'
9370: AND vst.MR_ID IS NULL
9371: AND vst.SERVICE_REQUEST_ID =
9372: (SELECT vst1.SERVICE_REQUEST_ID
9373: FROM ahl_visit_tasks_b vst1
9374: WHERE vst1.visit_task_id = p_sr_task_id);
9375:
9376: --check if visit has planned tasks
9377: CURSOR c_visit_has_planned_tasks(p_visit_id IN NUMBER) IS
9374: WHERE vst1.visit_task_id = p_sr_task_id);
9375:
9376: --check if visit has planned tasks
9377: CURSOR c_visit_has_planned_tasks(p_visit_id IN NUMBER) IS
9378: SELECT 1 FROM ahl_visit_tasks_b
9379: WHERE visit_id = p_visit_id
9380: AND status_code = 'PLANNING'
9381: -- SATRAJEN :: Bug 13930098 :: Visit stays at PARTIALLY IMPLEMENTED status :: Do NOT consider the default stage task :: July 2012
9382: AND (TASK_TYPE_CODE <> 'STAGE'
9396: -- MANESING::Bug 13713141, 16-Apr-2012, added following cursors
9397: -- Cursor to find out the Repair Batch for the given task
9398: CURSOR get_rpr_batch_for_task_csr (c_visit_task_id NUMBER) IS
9399: SELECT repair_batch_name
9400: FROM AHL_VISIT_TASKS_B
9401: WHERE cost_parent_id IS NULL
9402: START WITH visit_task_id = c_visit_task_id
9403: CONNECT BY visit_task_id = Prior cost_parent_id;
9404:
9404:
9405: -- Cursor to check if Repair Batch has planned tasks
9406: CURSOR chk_rpr_batch_has_planed_tasks (c_repair_batch_name VARCHAR2) IS
9407: SELECT 'X'
9408: FROM AHL_VISIT_TASKS_B
9409: WHERE cost_parent_id IS NOT NULL -- to filter repair batch task
9410: AND status_code = 'PLANNING'
9411: START WITH repair_batch_name = c_repair_batch_name
9412: CONNECT BY cost_parent_id = Prior visit_task_id;
9417: SELECT stage.stage_id
9418: FROM ahl_vwp_stages_b stage
9419: WHERE stage.visit_id = c_visit_id
9420: AND EXISTS (SELECT 1
9421: FROM ahl_visit_tasks_b task
9422: WHERE stage.stage_id = task.stage_id
9423: AND stage.stage_status_code = 'PLANNING'
9424: AND task.task_type_code <> 'STAGE'
9425: AND task.status_code = 'RELEASED');
9426:
9427: -- Get the workorder id and task type for a given task
9428: CURSOR get_task_dets (c_task_id IN NUMBER) IS
9429: SELECT task.task_type_code, wo.workorder_id
9430: FROM ahl_visit_tasks_b task, ahl_workorders wo
9431: WHERE task.visit_task_id = c_task_id
9432: AND task.visit_task_id = wo.visit_task_id;
9433: task_dets_rec get_task_dets%ROWTYPE;
9434:
9656:
9657: IF (l_manual_summ_task_flag = 'Y') THEN
9658: -- No work order will be created for manually created summary task
9659: -- Just update the task status to Released.
9660: UPDATE ahl_visit_tasks_b
9661: SET status_code = 'RELEASED',
9662: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE OVN AND WHO COLUMNS
9663: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
9664: LAST_UPDATE_DATE = SYSDATE,
10075: OPEN get_task_dets (p_tasks_tbl(i).visit_task_id);
10076: FETCH get_task_dets INTO task_dets_rec;
10077: CLOSE get_task_dets;
10078: IF task_dets_rec.task_type_code = 'DUPLICATE' THEN
10079: UPDATE ahl_visit_tasks_b
10080: SET status_code = 'CANCELLED',
10081: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
10082: LAST_UPDATE_DATE = SYSDATE,
10083: LAST_UPDATED_BY = Fnd_Global.USER_ID,
10096: p_visit_id => NULL,
10097: p_unit_effectivity_id => NULL,
10098: p_workorder_id => task_dets_rec.workorder_id);
10099: ELSE
10100: UPDATE ahl_visit_tasks_b
10101: SET status_code = 'RELEASED',
10102: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
10103: LAST_UPDATE_DATE = SYSDATE,
10104: LAST_UPDATED_BY = Fnd_Global.USER_ID,
10147: IF (l_log_statement >= l_log_current_level) THEN
10148: FND_LOG.string(l_log_statement, l_debug_key, 'Repair Batch ' || l_repair_batch_name || ' has atleast one planned task.');
10149: END IF;
10150:
10151: UPDATE AHL_VISIT_TASKS_B
10152: SET status_code = 'PARTIALLY RELEASED',
10153: object_version_number = object_version_number + 1,
10154: last_update_date = SYSDATE,
10155: last_updated_by = Fnd_Global.USER_ID,
10161: IF (l_log_statement >= l_log_current_level) THEN
10162: FND_LOG.string(l_log_statement, l_debug_key, 'Repair Batch ' || l_repair_batch_name || ' has no planned tasks.');
10163: END IF;
10164:
10165: UPDATE AHL_VISIT_TASKS_B
10166: SET status_code = 'RELEASED',
10167: object_version_number = object_version_number + 1,
10168: last_update_date = SYSDATE,
10169: last_updated_by = Fnd_Global.USER_ID,
10415:
10416: CURSOR get_wo(c_visit_id NUMBER, c_unit_effectivity_id NUMBER) IS
10417: SELECT workorder_id
10418: FROM AHL_WORKORDERS
10419: WHERE VISIT_TASK_ID = (select visit_task_id from ahl_visit_tasks_b
10420: where visit_id = c_visit_id
10421: and unit_effectivity_id = c_unit_effectivity_id
10422: AND NVL(status_code, 'Y') <> 'DELETED'
10423: and originating_task_id is null)
10444:
10445: -- get end time of the root task for a given UE
10446: CURSOR get_summary_task_times_csr(x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER)IS
10447: SELECT max(end_date_time)
10448: FROM ahl_visit_tasks_b VST
10449: where visit_id = x_visit_id
10450: AND NVL(VST.status_code, 'Y') <> 'DELETED'
10451: START WITH unit_effectivity_id = x_unit_effectivity_id
10452: and originating_task_id is null
10454:
10455: -- Get all the parent tasks for a given UE that start in a past date
10456: CURSOR get_independent_tasks(x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
10457: select distinct t.visit_task_id
10458: from ahl_visit_tasks_b t,
10459: ahl_visits_b v
10460: where v.visit_id = x_visit_id
10461: and v.visit_id = t.visit_id
10462: --and v.status_code = 'PARTIALLY RELEASED'
10483: --CHANGES by jrotich bug #13028686 begin
10484: CURSOR c_stages_without_types(c_visit_id IN NUMBER, c_unit_effectivity_id IN NUMBER ) IS
10485: SELECT DISTINCT STAGES.STAGE_ID ,
10486: STAGES.STAGE_NAME
10487: FROM AHL_VISIT_TASKS_B TASKS,
10488: AHL_VWP_STAGES_VL STAGES
10489: WHERE
10490: TASKS.VISIT_ID= c_visit_id AND
10491: TASKS.UNIT_EFFECTIVITY_ID = c_unit_effectivity_id AND
10787: CURSOR get_tech_dependencies( c_visit_id NUMBER, c_unit_effectivity_id NUMBER )
10788: IS
10789: SELECT distinct PARENT.visit_task_number parent_task_number,
10790: CHILD.visit_task_number child_task_number
10791: FROM AHL_VISIT_TASKS_B PARENT,
10792: AHL_VISIT_TASKS_B CHILD,
10793: AHL_TASK_LINKS LINK
10794: WHERE PARENT.visit_task_id = LINK.parent_task_id
10795: AND CHILD.visit_task_id = LINK.visit_task_id
10788: IS
10789: SELECT distinct PARENT.visit_task_number parent_task_number,
10790: CHILD.visit_task_number child_task_number
10791: FROM AHL_VISIT_TASKS_B PARENT,
10792: AHL_VISIT_TASKS_B CHILD,
10793: AHL_TASK_LINKS LINK
10794: WHERE PARENT.visit_task_id = LINK.parent_task_id
10795: AND CHILD.visit_task_id = LINK.visit_task_id
10796: AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING' --Srini Bug #4075702
10795: AND CHILD.visit_task_id = LINK.visit_task_id
10796: AND NVL(PARENT.STATUS_CODE,'X') = 'PLANNING' --Srini Bug #4075702
10797: AND PARENT.visit_id = c_visit_id
10798: AND CHILD.visit_id = c_visit_id
10799: AND PARENT.visit_task_id in (select visit_task_id from ahl_visit_tasks_b
10800: where visit_id = c_visit_id
10801: /*NR-MR Changes - For SR's created from non-routines have the originating wo as their originating task, hence they are not null.*/
10802: START WITH unit_effectivity_id = c_unit_effectivity_id
10803: AND (( originating_task_id is null AND 'SR' <> p_module_type)
10805: -- Removed the condition "originating_task_id is not null"
10806: OR ( 'SR' = p_module_type
10807: and service_request_id is not null))
10808: CONNECT BY originating_task_id = PRIOR visit_task_id)
10809: AND CHILD.visit_task_id in ( select visit_task_id from ahl_visit_tasks_b
10810: where visit_id = c_visit_id
10811: /*NR-MR Changes - For SR's created from non-routines have the originating wo as their originating task, hence they are not null.*/
10812: START WITH unit_effectivity_id = c_unit_effectivity_id
10813: AND (( originating_task_id is null AND 'SR' <> p_module_type)
10878: task_type_code,
10879: service_request_id,
10880: mr_id,
10881: NVL(originating_task_id, -1)
10882: FROM AHL_VISIT_TASKS_B
10883: WHERE visit_id = c_visit_id
10884: AND task_type_code='SUMMARY'
10885: -- SKPATHAK :: Bug 9444849 :: 19-MAR-2010
10886: -- This condition is not needed, since after opening this cursor we have a check if the visit task id fetched by this cursor
10915: -- Get all the Tasks associated to a MR.
10916: CURSOR get_tasks_for_mr( c_visit_id NUMBER, c_mr_task_id NUMBER )
10917: IS
10918: SELECT visit_task_number, visit_task_id
10919: FROM AHL_VISIT_TASKS_B
10920: WHERE visit_id = c_visit_id
10921: AND originating_task_id = c_mr_task_id
10922: AND task_type_code <> 'SUMMARY'
10923: AND NVL(STATUS_CODE, 'X') = 'PLANNING';
11175: -- To get the count of all the tasks for the given UE
11176: CURSOR c_task_ct (x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
11177: -- SKPATHAK :: Bug 8340436 :: 23-MAR-2009
11178: -- Fetch distinct rows to avoid duplicates and select visit_task_id instead of *
11179: --SELECT count(*) FROM AHL_VISIT_TASKS_B
11180: SELECT count(distinct visit_task_id) FROM AHL_VISIT_TASKS_B
11181: WHERE VISIT_ID = x_visit_id
11182: AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
11183: /*NR-MR Changes*/
11176: CURSOR c_task_ct (x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
11177: -- SKPATHAK :: Bug 8340436 :: 23-MAR-2009
11178: -- Fetch distinct rows to avoid duplicates and select visit_task_id instead of *
11179: --SELECT count(*) FROM AHL_VISIT_TASKS_B
11180: SELECT count(distinct visit_task_id) FROM AHL_VISIT_TASKS_B
11181: WHERE VISIT_ID = x_visit_id
11182: AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
11183: /*NR-MR Changes*/
11184: START WITH unit_effectivity_id = x_unit_effectivity_id
11197: CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
11198: SELECT min(start_date_time), max(end_date_time)
11199: --TCHIMIRA::19-FEB-2010::BUG 9384614
11200: -- Use the base table instead of the vl view
11201: FROM ahl_visit_tasks_b VST
11202: START WITH visit_task_id = x_task_id
11203: AND NVL(VST.status_code, 'Y') <> 'DELETED'
11204: CONNECT BY originating_task_id = PRIOR visit_task_id;
11205:
11205:
11206: CURSOR c_visit_task_exists(x_visit_id IN NUMBER)
11207: IS
11208: SELECT 'x'
11209: FROM ahl_visit_tasks_b
11210: WHERE visit_id = x_visit_id
11211: AND STATUS_CODE = 'PLANNING'
11212: -- SKPATHAK :: Bug 13930098 :: 20-APR-2012 :: Do NOT consider the default stage task
11213: AND (TASK_TYPE_CODE <> 'STAGE'
11239: CURSOR get_wo_dtls_for_mrtasks_cur (c_visit_task_id IN NUMBER) IS
11240: --TCHIMIRA::Bug 9149770 ::09-FEB-2010
11241: --use substrb and lengthb instead of substr and length respectively
11242: SELECT ar.route_no||'.'||SUBSTRB(ar.title, 1, (240 - (LENGTHB(ar.route_no) + 1))) workorder_description
11243: FROM ahl_routes_vl ar, ahl_visit_tasks_b avt,
11244: ahl_mr_routes mrr
11245: WHERE avt.visit_task_id = c_visit_task_id
11246: AND NVL(avt.status_code, 'X') = 'PLANNING'
11247: AND avt.mr_route_id = mrr.mr_route_id
11268: -- MANESING::Bug 13713141, 16-Apr-2012, added following cursors
11269: -- Cursor to find out the Repair Batch for the given task
11270: CURSOR get_rpr_batch_for_task_csr (c_visit_task_id NUMBER) IS
11271: SELECT repair_batch_name
11272: FROM AHL_VISIT_TASKS_B
11273: WHERE cost_parent_id IS NULL
11274: START WITH visit_task_id = c_visit_task_id
11275: CONNECT BY visit_task_id = Prior cost_parent_id;
11276:
11276:
11277: -- Cursor to check if Repair Batch has planned tasks
11278: CURSOR chk_rpr_batch_has_planed_tasks (c_repair_batch_name VARCHAR2) IS
11279: SELECT 'X'
11280: FROM AHL_VISIT_TASKS_B
11281: WHERE cost_parent_id IS NOT NULL -- to filter repair batch task
11282: AND status_code = 'PLANNING'
11283: START WITH repair_batch_name = c_repair_batch_name
11284: CONNECT BY cost_parent_id = Prior visit_task_id;
11632: OPEN c_task(p_visit_id, p_unit_effectivity_id);
11633: FETCH c_task INTO c_task_rec;
11634: WHILE c_task%FOUND
11635: LOOP
11636: UPDATE AHL_VISIT_TASKS_B
11637: SET STATUS_CODE = 'RELEASED',
11638: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
11639: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
11640: LAST_UPDATE_DATE = SYSDATE,
11705: IF (l_log_statement >= l_log_current_level) THEN
11706: FND_LOG.string(l_log_statement, l_debug_key, 'Repair Batch ' || l_repair_batch_name || ' has no planned tasks.');
11707: END IF;
11708:
11709: UPDATE AHL_VISIT_TASKS_B
11710: SET status_code = 'RELEASED',
11711: object_version_number = object_version_number + 1,
11712: last_update_date = SYSDATE,
11713: last_updated_by = Fnd_Global.USER_ID,
11839:
11840: -- PRAKKUM :: PIE :: 13-OCT-2010
11841: -- Added new cursors
11842: CURSOR get_task_number_csr (c_visit_task_id IN NUMBER) IS
11843: SELECT visit_task_number, task_type_code from ahl_visit_tasks_b
11844: where visit_task_id = c_visit_task_id;
11845:
11846: CURSOR top_task_ref_csr (c_top_project_task_id IN NUMBER) IS
11847: SELECT pm_task_reference from pa_tasks
12127: END IF;
12128: END IF;
12129: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
12130: ELSE
12131: UPDATE AHL_VISIT_TASKS_B SET PROJECT_TASK_ID = l_task_id,
12132: OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1,
12133: --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
12134: LAST_UPDATE_DATE = SYSDATE,
12135: LAST_UPDATED_BY = Fnd_Global.USER_ID,
12135: LAST_UPDATED_BY = Fnd_Global.USER_ID,
12136: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
12137: WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
12138: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
12139: fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updated AHL_VISIT_TASKS_B: c_task_rec.visit_task_number = ' || c_task_rec.visit_task_number ||
12140: ', c_task_rec.visit_task_id = ' || c_task_rec.visit_task_id ||
12141: ', c_task_rec.originating_task_id = ' || c_task_rec.originating_task_id ||
12142: ', c_task_rec.unit_effectivity_id = ' || c_task_rec.unit_effectivity_id ||
12143: ', c_task_rec.service_request_id = ' || c_task_rec.service_request_id);
12214: END IF;
12215: END IF;
12216: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
12217: ELSE
12218: UPDATE AHL_VISIT_TASKS_B SET PROJECT_TASK_ID = l_task_id,
12219: OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
12220: WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
12221: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
12222: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Updated AHL_VISIT_TASKS_B: c_task_rec.visit_task_number = ' || c_task_rec.visit_task_number ||
12218: UPDATE AHL_VISIT_TASKS_B SET PROJECT_TASK_ID = l_task_id,
12219: OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
12220: WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
12221: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
12222: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Updated AHL_VISIT_TASKS_B: c_task_rec.visit_task_number = ' || c_task_rec.visit_task_number ||
12223: ', c_task_rec.visit_task_id = ' || c_task_rec.visit_task_id ||
12224: ', c_task_rec.originating_task_id = ' || c_task_rec.originating_task_id ||
12225: ', c_task_rec.unit_effectivity_id = ' || c_task_rec.unit_effectivity_id ||
12226: ', c_task_rec.service_request_id = ' || c_task_rec.service_request_id);
12300: IS
12301: -- get all tasks in planning status for a visit
12302: CURSOR get_visit_tasks_cur(c_visit_id NUMBER) IS
12303: SELECT visit_task_id
12304: FROM ahl_visit_tasks_b
12305: WHERE visit_id = c_visit_id
12306: AND NVL(status_code,'X') = 'PLANNING';
12307:
12308: -- Declare local variables
12675: -- This cursor gets the task details for all the tasks in the given project
12676: CURSOR get_all_prj_tsks_csr(c_project_id IN NUMBER, c_visit_id IN NUMBER) IS
12677: SELECT CURR.TASK_ID, CURR.DESCRIPTION, CURR.PM_TASK_REFERENCE, PARENT.PM_TASK_REFERENCE PARENT_TASK_REFERENCE, VTSK.VISIT_TASK_ID
12678: , CURR.PARENT_TASK_ID PARENT_TASK_ID -- PRAKKUM :: 08-JAN-2013 :: FPBug 16481709 for BaseBug 14828418 : In reference of bug 9047048
12679: FROM PA_TASKS CURR, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS PARENT, AHL_VISIT_TASKS_B VTSK
12680: WHERE CURR.PROJECT_ID = c_project_id
12681: AND PPEV.PROJ_ELEMENT_ID = CURR.TASK_ID
12682: AND PPEV.PROJECT_ID = c_project_id
12683: AND PARENT.TASK_ID (+) = CURR.PARENT_TASK_ID
12696: --fetches project task id, the task start and end date of visit task
12697: CURSOR get_task_dates_cur(p_wo_id IN NUMBER) IS
12698: SELECT AVT.PROJECT_TASK_ID, AVT.START_DATE_TIME, AVT.END_DATE_TIME,
12699: PAT.DESCRIPTION -- Pass the old description back again
12700: FROM AHL_VISIT_TASKS_B AVT, AHL_WORKORDERS WO,
12701: PA_TASKS PAT
12702: WHERE WO.VISIT_TASK_ID = AVT.VISIT_TASK_ID AND
12703: WO.WORKORDER_ID = p_wo_id AND
12704: PAT.TASK_ID (+) = AVT.PROJECT_TASK_ID;
13072: CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
13073: SELECT min(start_date_time), max(end_date_time)
13074: --TCHIMIRA::19-FEB-2010::BUG 9384614
13075: -- Use the base table instead of the vl view
13076: FROM ahl_visit_tasks_b VST
13077: START WITH visit_task_id = p_task_id
13078: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
13079: CONNECT BY originating_task_id = PRIOR visit_task_id;
13080:
13082: -- yazhou Jul-20-2005 start
13083: CURSOR c_visit_task_exists(x_visit_id IN NUMBER)
13084: IS
13085: SELECT 'x'
13086: FROM ahl_visit_tasks_b
13087: WHERE visit_id = x_visit_id
13088: AND STATUS_CODE = 'PLANNING';
13089:
13090: CURSOR c_get_wo_details(x_visit_id IN NUMBER)
13432: p_x_task_Tbl(i).workorder_id := c_chk_job_rec.workorder_id;
13433:
13434: --yazhou Jul-18-2005 start
13435: -- Fix for bug # 4078095
13436: UPDATE AHL_VISIT_TASKS_B
13437: SET STATUS_CODE = 'RELEASED',
13438: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
13439: WHERE VISIT_TASK_ID = p_x_task_Tbl(i).visit_task_id
13440: AND STATUS_CODE = 'PLANNING';
13732:
13733: --To validate visit task id
13734: Cursor validate_vst_tsk_id_csr (c_task_id IN NUMBER) IS
13735: SELECT 'X'
13736: FROM ahl_visit_tasks_b
13737: WHERE visit_task_id = c_task_id
13738: AND status_code <> 'DELETED';
13739:
13740: --To find the project task details
13740: --To find the project task details
13741: CURSOR get_prj_tsk_details_csr (c_task_id IN NUMBER) IS
13742: SELECT avt.project_task_id, avt.task_type_code,
13743: avt.cost_parent_id, pt.project_id, pt.top_task_id
13744: FROM ahl_visit_tasks_b avt, pa_tasks pt
13745: WHERE avt.project_task_id = pt.task_id
13746: AND avt.visit_task_id = c_task_id ;
13747: get_prj_tsk_details_csr_rec get_prj_tsk_details_csr%ROWTYPE;
13748:
13748:
13749: --To find the project task id and visit task type of the cost parent
13750: CURSOR get_cost_parent_details_csr (cost_parent_task_id IN NUMBER) IS
13751: SELECT project_task_id, task_type_code
13752: FROM ahl_visit_tasks_b
13753: WHERE visit_task_id = cost_parent_task_id ;
13754: get_cost_parent_details_rec get_cost_parent_details_csr%ROWTYPE;
13755:
13756:
13938: WHERE project_id = x_project_id ;*/
13939:
13940: CURSOR get_parent_prj_tsk_id_csr (c_project_id IN NUMBER) IS
13941: SELECT count(PTSK.parent_task_id)
13942: FROM pa_tasks PTSK, AHL_VISIT_TASKS_B VTSK
13943: WHERE
13944: VTSK.PROJECT_TASK_ID = PTSK.TASK_ID
13945: AND VTSK.VISIT_ID = p_visit_id
13946: AND PTSK.project_id = c_project_id ;
13959: -- SKPATHAK :: Bug 14016007 :: 03-MAY-2012 ::
13960: -- To find if the visit has a STAGE task
13961: CURSOR get_stage_task_count (c_visit_id IN NUMBER) IS
13962: SELECT count(visit_task_id)
13963: FROM ahl_visit_tasks_b
13964: WHERE task_type_code = 'STAGE'
13965: AND status_code <> 'DELETED'
13966: AND visit_id = c_visit_id;
13967:
14141:
14142: --Cursor to fetch SR Id's of Visit
14143: CURSOR get_sr_ids (c_visit_id IN NUMBER) IS
14144: SELECT DISTINCT service_request_id
14145: FROM ahl_visit_tasks_b
14146: WHERE visit_id = c_visit_id;
14147:
14148:
14149: BEGIN
14532: l_task_wo_rec c_task_wo%ROWTYPE;
14533:
14534: --fetch summary task flag for the task
14535: CURSOR c_fet_sum_task_flg (c_visit_id IN NUMBER, c_visit_task_id IN NUMBER) IS
14536: SELECT summary_task_flag FROM ahl_visit_tasks_b
14537: WHERE visit_task_id = c_visit_task_id;
14538:
14539: --get summary task start, end time
14540: CURSOR get_summary_task_times_csr(c_task_id IN NUMBER)IS
14538:
14539: --get summary task start, end time
14540: CURSOR get_summary_task_times_csr(c_task_id IN NUMBER)IS
14541: SELECT min(start_date_time), max(end_date_time)
14542: FROM ahl_visit_tasks_b VST
14543: START WITH visit_task_id = c_task_id
14544: AND NVL(VST.status_code, 'Y') <> 'DELETED'
14545: CONNECT BY originating_task_id = PRIOR visit_task_id;
14546:
14552: --Inventory item id and instance id to be defaulted when
14553: --the item and instance are not specified at the header level
14554: CURSOR default_task_inst_dtls(c_task_id IN NUMBER) IS
14555: SELECT inventory_item_id,instance_id
14556: FROM ahl_visit_tasks_b
14557: WHERE visit_task_id = c_task_id;
14558: def_task_inst_rec default_task_inst_dtls%ROWTYPE;
14559:
14560: --Fetches the route information for updating workorder
14560: --Fetches the route information for updating workorder
14561: --description for tasks created from Routes
14562: CURSOR get_wo_dtls_for_mrtasks_cur (p_task_id IN NUMBER) IS
14563: SELECT ar.route_no||'.'||substrb(ar.title,1,(240 - (lengthb(ar.route_no) + 1))) workorder_description
14564: FROM ahl_routes_vl ar,ahl_visit_tasks_b avt, ahl_mr_routes mrr
14565: WHERE avt.visit_task_id = p_task_id
14566: and nvl(avt.status_code,'Y') = 'PLANNING'
14567: and avt.mr_route_id = mrr.mr_route_id
14568: and mrr.route_id = ar.route_id;
14569: l_wo_dtls_for_mrtasks_rec get_wo_dtls_for_mrtasks_cur%ROWTYPE;
14570:
14571: --check if all the children of a summary task are in released/unreleased status
14572: CURSOR chk_summary_task_child(c_task_id IN NUMBER) IS
14573: SELECT 'PARTIALLY RELEASED' FROM ahl_visit_tasks_b
14574: WHERE task_type_code IN ('PLANNED','UNPLANNED')
14575: AND status_code = 'PLANNING'
14576: START WITH visit_task_id = c_task_id
14577: CONNECT BY PRIOR visit_task_id = originating_task_id;
14579:
14580:
14581: --check if visit has planned tasks
14582: CURSOR c_visit_has_planned_tasks(p_visit_id IN NUMBER) IS
14583: SELECT 1 FROM ahl_visit_tasks_b
14584: WHERE visit_id = p_visit_id
14585: AND status_code = 'PLANNING'
14586: -- SATRAJEN :: Bug 13930098 :: Visit stays at PARTIALLY IMPLEMENTED status :: Do NOT consider the default stage task :: July 2012
14587: AND (TASK_TYPE_CODE <> 'STAGE'
14610: --For summary tasks
14611: SELECT DISTINCT * FROM ahl_visit_tasks_vl
14612: WHERE task_type_code = 'SUMMARY'
14613: START WITH visit_task_id IN (SELECT originating_task_id
14614: FROM ahl_visit_tasks_b
14615: WHERE stage_id = c_stage_id )
14616: CONNECT BY PRIOR originating_task_id = visit_task_id)
14617: WHERE status_code NOT IN ('CANCELLED','DELETED')
14618: ORDER BY visit_task_number;
14622: --SKPATHAK :: VWPE ER-12424063 :: 09-JUN-2011 :: Get the stage and duplicate tasks as well
14623: CURSOR get_non_summary_stage_tasks (c_stage_id IN NUMBER)
14624: IS
14625: --For non-summary tasks
14626: SELECT * FROM ahl_visit_tasks_b
14627: WHERE stage_id = c_stage_id
14628: AND status_code NOT IN ('CANCELLED','DELETED')
14629: ORDER BY visit_task_number;
14630:
14633: --SKPATHAK :: VWPE ER-12424063 :: 13-JUN-2011 :: Get only the summary tasks
14634: CURSOR get_summary_stage_tasks (c_stage_id IN NUMBER)
14635: IS
14636: --For summary tasks
14637: SELECT DISTINCT * FROM ahl_visit_tasks_b
14638: WHERE task_type_code = 'SUMMARY'
14639: START WITH visit_task_id IN (SELECT originating_task_id
14640: FROM ahl_visit_tasks_b
14641: WHERE stage_id = c_stage_id )
14636: --For summary tasks
14637: SELECT DISTINCT * FROM ahl_visit_tasks_b
14638: WHERE task_type_code = 'SUMMARY'
14639: START WITH visit_task_id IN (SELECT originating_task_id
14640: FROM ahl_visit_tasks_b
14641: WHERE stage_id = c_stage_id )
14642: CONNECT BY PRIOR originating_task_id = visit_task_id
14643: ORDER BY visit_task_number;
14644:
15250: IF l_return_status <> 'S' THEN
15251: RAISE Fnd_Api.G_EXC_ERROR;
15252: END IF;
15253:
15254: UPDATE ahl_visit_tasks_b
15255: SET status_code = 'CANCELLED',
15256: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
15257: LAST_UPDATE_DATE = SYSDATE,
15258: LAST_UPDATED_BY = Fnd_Global.USER_ID,
15258: LAST_UPDATED_BY = Fnd_Global.USER_ID,
15259: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
15260: WHERE visit_task_id = l_non_summ_task_rec.visit_task_id;
15261: ELSIF l_non_summ_task_rec.task_type_code <> 'SUMMARY' THEN
15262: UPDATE ahl_visit_tasks_b
15263: SET status_code = 'RELEASED',
15264: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
15265: LAST_UPDATE_DATE = SYSDATE,
15266: LAST_UPDATED_BY = Fnd_Global.USER_ID,
15280: IF (chk_summary_task_child%NOTFOUND) THEN
15281: l_chk_status_code := 'RELEASED';
15282: END IF;
15283: CLOSE chk_summary_task_child;
15284: UPDATE ahl_visit_tasks_b
15285: SET status_code = l_chk_status_code,
15286: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
15287: LAST_UPDATE_DATE = SYSDATE,
15288: LAST_UPDATED_BY = Fnd_Global.USER_ID,
15479: pt.task_name LIKE c_task_trail_name /* Filter only project tasks whose name ends with -G_TRANS_TSK_TRAIL_NAME */
15480: AND pt.project_id=c_project_id
15481: AND pt.parent_task_id=c_repair_batch_task_id
15482: AND NOT EXISTS (
15483: select 1 from AHL_VISIT_TASKS_B tsk where tsk.visit_id = c_visit_id AND tsk.project_task_id = pt.task_id
15484: );
15485:
15486: l_transaction_tsk_id NUMBER :=NULL;
15487: