eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_VWP_RULES_PVT dependencies on AHL_VISIT_TASKS_B

Line 769: FROM AHL_VISIT_TASKS_B

765: ', Visit Task Number = ' || p_visit_task_number || ', Visit Id = ' || p_visit_id);
766: END IF;
767: IF (p_visit_task_id IS NOT NULL) THEN
768: SELECT Visit_Task_Id INTO x_visit_task_id
769: FROM AHL_VISIT_TASKS_B
770: WHERE Visit_Task_Id = p_visit_task_id AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
771: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
772: ELSIF (p_visit_task_number IS NOT NULL) THEN
773: SELECT Visit_Task_Id INTO x_visit_task_id

Line 774: FROM AHL_VISIT_TASKS_B

770: WHERE Visit_Task_Id = p_visit_task_id AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
771: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
772: ELSIF (p_visit_task_number IS NOT NULL) THEN
773: SELECT Visit_Task_Id INTO x_visit_task_id
774: FROM AHL_VISIT_TASKS_B
775: WHERE Visit_Task_Number = p_visit_task_number AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
776: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
777: ELSE
778: x_return_status:= Fnd_Api.G_RET_STS_ERROR;

Line 1196: FROM ahl_visit_tasks_b

1192: -- If the NR is created from another NR, then it should contain the Originating MR of the Originating NR
1193: -- If the NR is created from a group MR's child MR, the the originating mr is the child MR of the group MR
1194: CURSOR get_originating_mr_id (c_parent_id IN NUMBER, c_visit_id IN NUMBER) IS
1195: SELECT mr_id
1196: FROM ahl_visit_tasks_b
1197: WHERE visit_id = c_visit_id
1198: AND service_request_id IS NULL
1199: START WITH visit_task_id = c_parent_id
1200: CONNECT BY PRIOR originating_task_id = visit_task_id;

Line 1394: -- Insert a record in AHL_VISIT_TASKS base and translational tables

1390: -- After fetching the UE id, we need to lock the row to prevent any other process(BUE) from deleting the row corresponding to that UE id
1391: OPEN c_row_lock(p_unit_id);
1392: FETCH c_row_lock INTO l_lock_var;
1393:
1394: -- Insert a record in AHL_VISIT_TASKS base and translational tables
1395: Ahl_Visit_Tasks_Pkg.Insert_Row (
1396: X_ROWID => l_rowid,
1397: X_VISIT_TASK_ID => l_task_ID ,
1398: X_VISIT_TASK_NUMBER => l_task_number,

Line 1722: SELECT Ahl_Visit_Tasks_B_S.NEXTVAL

1718: RETURN NUMBER
1719: IS
1720: -- Define local cursors
1721: CURSOR c_seq_t IS
1722: SELECT Ahl_Visit_Tasks_B_S.NEXTVAL
1723: FROM dual;
1724:
1725: CURSOR c_id_exists_t (c_id IN NUMBER) IS
1726: SELECT 1

Line 1778: FROM Ahl_Visit_Tasks_B

1774: IS
1775: -- To find out the maximum task number value in the visit
1776: CURSOR c_task_number IS
1777: SELECT MAX(visit_task_number)
1778: FROM Ahl_Visit_Tasks_B
1779: WHERE Visit_Id = p_visit_id;
1780:
1781: x_Visit_Task_Number NUMBER;
1782: L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Number';

Line 2218: FROM AHL_VISIT_TASKS_B

2214:
2215: -- To find any visit task exists for the retrieve Serial Number, Unit Effectivity ID and MR Route ID and other info
2216: CURSOR c_task (x_mroute_id IN NUMBER, x_serial_id IN NUMBER, x_unit_id IN NUMBER) IS
2217: SELECT Visit_Id, Visit_Task_id
2218: FROM AHL_VISIT_TASKS_B
2219: WHERE MR_Route_Id = x_mroute_id
2220: AND Instance_Id = x_serial_id
2221: AND Unit_Effectivity_Id = x_unit_id
2222: AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');

Line 2238: FROM AHL_VISIT_TASKS_B AVTB, AHL_MR_HEADERS_APP_V AMHV

2234:
2235: -- To find any visit task exists for the retrieve Serial Number, Unit Effectivity ID and MR Route ID and other info
2236: CURSOR c_task_chk (x_id IN NUMBER, x_mr_id IN NUMBER, x_serial_id in NUMBER) IS
2237: SELECT AMHV.Title
2238: FROM AHL_VISIT_TASKS_B AVTB, AHL_MR_HEADERS_APP_V AMHV
2239: WHERE AVTB.MR_ID = AMHV.MR_HEADER_ID
2240: AND AVTB.MR_Id = x_mr_id
2241: AND AVTB.Instance_Id = x_serial_id
2242: AND AVTB.VISIT_ID = x_id

Line 2262: FROM ahl_visit_tasks_b

2258: CURSOR c_task_det(p_visit_task_id NUMBER) IS
2259: SELECT visit_task_id,
2260: start_date_time,
2261: end_date_time
2262: FROM ahl_visit_tasks_b
2263: WHERE visit_task_id = p_visit_task_id;
2264:
2265: l_task_det c_task_det%rowtype;
2266:

Line 2670: SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B

2666:
2667: -- Define local cursors
2668: -- For all children of cost_parent_id
2669: CURSOR c_child (c_cost_id IN NUMBER, c_id IN NUMBER) IS
2670: SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
2671: WHERE VISIT_ID = c_id
2672: START WITH COST_PARENT_ID = c_cost_id
2673: CONNECT BY PRIOR VISIT_TASK_ID = COST_PARENT_ID;
2674: c_child_rec c_child%ROWTYPE;

Line 2745: SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B

2741:
2742: -- Define local cursors
2743: -- For all children of originating_task_id
2744: CURSOR c_child (x_org_id IN NUMBER, x_id IN NUMBER) IS
2745: SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
2746: WHERE VISIT_ID = x_id
2747: AND NVL(STATUS_CODE,'X') <> 'DELETED'
2748: START WITH ORIGINATING_TASK_ID = x_org_id
2749: CONNECT BY PRIOR VISIT_TASK_ID = ORIGINATING_TASK_ID;

Line 3068: UPDATE AHL_VISIT_TASKS_B SET ORIGINATING_TASK_ID = NULL,

3064: fnd_log.string(l_log_statement,
3065: L_DEBUG_KEY,
3066: 'update origin');
3067: END IF;
3068: UPDATE AHL_VISIT_TASKS_B SET ORIGINATING_TASK_ID = NULL,
3069: OBJECT_VERSION_NUMBER = c_origin_rec.object_version_number + 1
3070: WHERE VISIT_TASK_ID = c_origin_rec.visit_task_id;
3071: END IF;
3072: END LOOP;

Line 3089: UPDATE AHL_VISIT_TASKS_B SET COST_PARENT_ID = l_parent_id,

3085: fnd_log.string(l_log_statement,
3086: L_DEBUG_KEY,
3087: 'update parent');
3088: END IF;
3089: UPDATE AHL_VISIT_TASKS_B SET COST_PARENT_ID = l_parent_id,
3090: OBJECT_VERSION_NUMBER = c_parent_rec.object_version_number + 1
3091: WHERE VISIT_TASK_ID = c_parent_rec.visit_task_id;
3092: END IF;
3093: END LOOP;

Line 3201: FROM AHL_VISIT_TASKS_B

3197: I NUMBER;
3198:
3199: CURSOR c_task(p_visit_id NUMBER, p_tsk_id NUMBER) IS
3200: SELECT visit_task_id, originating_task_id
3201: FROM AHL_VISIT_TASKS_B
3202: WHERE VISIT_ID = p_visit_id
3203: AND VISIT_TASK_ID = p_tsk_id
3204: AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
3205: c_task_rec c_task%ROWTYPE;

Line 3464: FROM ahl_visit_tasks_b

3460: Cursor get_cost_parent_id (c_cost_parent_number NUMBER,
3461: c_visit_id NUMBER)
3462: IS
3463: SELECT visit_task_id
3464: FROM ahl_visit_tasks_b
3465: WHERE visit_task_number = c_cost_parent_number
3466: AND visit_id = c_visit_id;
3467:
3468:

Line 3473: FROM ahl_visit_tasks_b

3469: -- To fetch the start and end of the first non-summary parent (in the cost hierarchy) of the task
3470: Cursor get_cost_parent_dates (c_id NUMBER)
3471: IS
3472: SELECT start_date_time, end_date_time
3473: FROM ahl_visit_tasks_b
3474: WHERE task_type_code<>'SUMMARY'
3475: START WITH visit_task_id = c_id
3476: CONNECT BY PRIOR cost_parent_id = visit_task_id;
3477: cost_parent_dates_rec get_cost_parent_dates%ROWTYPE;

Line 3483: FROM ahl_visit_tasks_b

3479: -- To fetch the start and end of the first non-summary child (in the cost hierarchy) of the task
3480: Cursor get_cost_child_dates (c_id NUMBER)
3481: IS
3482: SELECT start_date_time, end_date_time
3483: FROM ahl_visit_tasks_b
3484: WHERE task_type_code<>'SUMMARY'
3485: AND visit_task_id <> c_id
3486: START WITH visit_task_id = c_id
3487: CONNECT BY PRIOR visit_task_id = cost_parent_id;

Line 3830: FROM ahl_visit_tasks_b

3826: -- Cursors defined for the procedure
3827: --cursor to fetch the visit task type code
3828: CURSOR c_task_type (c_task_id IN NUMBER) IS
3829: SELECT TASK_TYPE_CODE
3830: FROM ahl_visit_tasks_b
3831: WHERE VISIT_TASK_ID = c_task_id;
3832:
3833: --cursor to fetch warranty template associated at MR
3834: CURSOR c_mr_template (c_task_id IN NUMBER) IS

Line 3861: FROM AHL_WARRANTY_ENTITLEMENTS AWE ,AHL_VISIT_TASKS_B ATSK

3857:
3858: --cursor to fetch warranty entitlement id for a given SR Id
3859: CURSOR c_get_entl_for_NR_task (c_sr_incident_id IN NUMBER) IS
3860: SELECT AWE.WARRANTY_ENTITLEMENT_ID,AWE.OBJECT_VERSION_NUMBER
3861: FROM AHL_WARRANTY_ENTITLEMENTS AWE ,AHL_VISIT_TASKS_B ATSK
3862: WHERE ATSK.SERVICE_REQUEST_ID = AWE.SR_INCIDENT_ID
3863: AND ATSK.TASK_TYPE_CODE = 'PLANNED'
3864: AND ATSK.MR_ROUTE_ID IS NULL
3865: AND AWE.VISIT_TASK_ID IS NULL

Line 3871: FROM AHL_VISIT_TASKS_B

3867:
3868: --cursor to check whether the task is a planned SR based task(SR must not have any NR)
3869: CURSOR c_is_planned_SR_task (c_task_id IN NUMBER) IS
3870: SELECT 'X'
3871: FROM AHL_VISIT_TASKS_B
3872: WHERE TASK_TYPE_CODE = 'PLANNED'
3873: AND MR_ROUTE_ID IS NULL
3874: AND VISIT_TASK_ID = c_task_id;
3875:

Line 4033: FROM ahl_visit_tasks_b

4029: -- Cursors defined for the procedure
4030: --cursor to fetch the visit task type code
4031: CURSOR c_task_type (c_task_id IN NUMBER) IS
4032: SELECT TASK_TYPE_CODE
4033: FROM ahl_visit_tasks_b
4034: WHERE VISIT_TASK_ID = c_task_id;
4035:
4036: --cursor to fetch the old entitlemet record from DB for the task
4037: CURSOR c_old_entitlement_rec (c_task_id IN NUMBER) IS

Line 4060: FROM AHL_VISIT_TASKS_B

4056:
4057: --cursor to fetch non summary child tasks
4058: CURSOR c_child_tasks(c_task_id NUMBER) IS
4059: SELECT VISIT_TASK_ID
4060: FROM AHL_VISIT_TASKS_B
4061: WHERE TASK_TYPE_CODE <> 'SUMMARY'
4062: AND ORIGINATING_TASK_ID = c_task_id;
4063:
4064: --cursor to check whether the task is a planned SR based task(SR must not have any NR)

Line 4067: FROM AHL_VISIT_TASKS_B

4063:
4064: --cursor to check whether the task is a planned SR based task(SR must not have any NR)
4065: CURSOR c_is_planned_SR_task (c_task_id IN NUMBER) IS
4066: SELECT 'X'
4067: FROM AHL_VISIT_TASKS_B
4068: WHERE TASK_TYPE_CODE = 'PLANNED'
4069: AND MR_ROUTE_ID IS NULL
4070: AND VISIT_TASK_ID = c_task_id;
4071:

Line 4266: FROM AHL_VWP_STAGES_VL stage, AHL_VISIT_TASKS_B task1

4262: --Cursor to get the stage details for a given task
4263: CURSOR c_stage_details(p_task_id IN NUMBER)
4264: IS
4265: SELECT task1.visit_task_id, stage.STAGE_ID, stage.STAGE_NAME
4266: FROM AHL_VWP_STAGES_VL stage, AHL_VISIT_TASKS_B task1
4267: WHERE task1.stage_id = stage.stage_id
4268: AND task1.visit_task_id IN ( SELECT task.visit_task_id
4269: FROM AHL_VISIT_TASKS_B task
4270: WHERE task.task_type_code IN ('PLANNED','UNPLANNED')

Line 4269: FROM AHL_VISIT_TASKS_B task

4265: SELECT task1.visit_task_id, stage.STAGE_ID, stage.STAGE_NAME
4266: FROM AHL_VWP_STAGES_VL stage, AHL_VISIT_TASKS_B task1
4267: WHERE task1.stage_id = stage.stage_id
4268: AND task1.visit_task_id IN ( SELECT task.visit_task_id
4269: FROM AHL_VISIT_TASKS_B task
4270: WHERE task.task_type_code IN ('PLANNED','UNPLANNED')
4271: AND task.status_code = 'PLANNING'
4272: START WITH task.visit_task_id = p_task_id
4273: CONNECT BY PRIOR task.visit_task_id = task.originating_task_id)

Line 4286: FROM ahl_visit_tasks_b

4282:
4283: --To fetch all the route tasks of a stage in a visit.
4284: Cursor get_stage_tasks (p_stage_id IN NUMBER) IS
4285: SELECT visit_task_id, visit_task_number
4286: FROM ahl_visit_tasks_b
4287: WHERE stage_id = p_stage_id
4288: AND task_type_code IN ('PLANNED', 'UNPLANNED')
4289: AND status_code NOT IN ('DELETED','RELEASED');
4290:

Line 4297: FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes mrt

4293: SELECT route.route_no,
4294: route.route_type_code,
4295: kfv.concatenated_segments,
4296: route.process_code
4297: FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes mrt
4298: WHERE tsk.mr_route_id = mrt.mr_route_id
4299: AND mrt.route_id = route.route_id
4300: AND tsk.visit_task_id = p_task_id
4301: AND kfv.route_id = route.route_id;

Line 4323: FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes assoc

4319:
4320: --To match the post route attributes with the route attributes of all the tasks that fall under the same stage.
4321: Cursor get_task_id (p_stage_id IN NUMBER, p_route_no IN VARCHAR2, p_route_type IN VARCHAR2, p_system_code IN VARCHAR2, p_process_code IN VARCHAR2) IS
4322: SELECT tsk.visit_task_id, tsk.visit_task_number
4323: FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes assoc
4324: WHERE kfv.route_id = route.route_id
4325: AND NVL2(p_route_no, route.ROUTE_NO,'X') = NVL(p_route_no, 'X')
4326: AND NVL2(p_route_type,route.ROUTE_TYPE_CODE,'X') = NVL(p_route_type, 'X')
4327: AND NVL2(p_system_code,kfv.CONCATENATED_SEGMENTS,'X') = NVL(p_system_code, 'X')

Line 4605: FROM AHL_VISIT_TASKS_B ATSK,

4601: AWCL.CLAIM_STATUS_CODE,
4602: AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY,
4603: AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY,
4604: 'Y' WARRANTY_NOT_APPL_FLAG
4605: FROM AHL_VISIT_TASKS_B ATSK,
4606: AHL_WARRANTY_ENTITLEMENTS ASWE,
4607: AHL_WARRANTY_CLAIMS_B AWCL,
4608: AHL_WARRANTY_CONTRACTS_B AWCB,
4609: FND_LOOKUP_VALUES_VL LKUP1,

Line 4785: FROM ahl_visit_tasks_b

4781: status_code,
4782: task_type_code,
4783: instance_id,
4784: return_to_supply_flag current_rts
4785: FROM ahl_visit_tasks_b
4786: WHERE visit_task_id = c_visit_task_id;
4787:
4788: -- Get the number of tasks flagged RTS for an instance, for all tasks other than this task
4789: CURSOR get_rts_count(c_visit_id NUMBER, c_visit_task_id NUMBER, c_instance_id NUMBER) IS

Line 4791: FROM ahl_visit_tasks_b

4787:
4788: -- Get the number of tasks flagged RTS for an instance, for all tasks other than this task
4789: CURSOR get_rts_count(c_visit_id NUMBER, c_visit_task_id NUMBER, c_instance_id NUMBER) IS
4790: SELECT count(visit_task_id) rts_count
4791: FROM ahl_visit_tasks_b
4792: WHERE (status_code = 'PLANNING' OR status_code = 'RELEASED')
4793: AND return_to_supply_flag = 'Y'
4794: AND visit_id = c_visit_id
4795: AND visit_task_id <> c_visit_task_id

Line 4806: (SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt

4802: SELECT wip_entity_id FROM ahl_workorders
4803: WHERE status_code IN ('1', '3', '6', '19', '20', '17')
4804: --unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
4805: AND visit_task_id IN
4806: (SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt
4807: WHERE vt.cost_parent_id IS NOT NULL
4808: AND NVL(vt.return_to_supply_flag,'N') = 'Y'
4809: AND vt.instance_id = p_instance_id
4810: START WITH vt.visit_task_id = p_rpr_batch_task_id

Line 4815: SELECT repair_batch_name, visit_task_id FROM ahl_visit_tasks_b WHERE

4811: CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
4812: );
4813: --Cursor to get the repair batch details for a given task
4814: CURSOR get_repair_batch_dtls_csr(p_task_id_csr IN NUMBER) IS
4815: SELECT repair_batch_name, visit_task_id FROM ahl_visit_tasks_b WHERE
4816: repair_batch_name IS NOT NULL
4817: START WITH visit_task_id = p_task_id_csr
4818: CONNECT BY PRIOR cost_parent_id = visit_task_id;
4819:

Line 4825: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;

4821:
4822: -- Define local variables
4823: l_visit_id NUMBER;
4824: l_visit_task_number NUMBER;
4825: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
4826: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
4827: l_instance_id NUMBER;
4828: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
4829: l_rts_count NUMBER;

Line 4826: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;

4822: -- Define local variables
4823: l_visit_id NUMBER;
4824: l_visit_task_number NUMBER;
4825: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
4826: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
4827: l_instance_id NUMBER;
4828: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
4829: l_rts_count NUMBER;
4830: l_task_rec get_task_prelim_details%ROWTYPE;

Line 4828: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;

4824: l_visit_task_number NUMBER;
4825: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
4826: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
4827: l_instance_id NUMBER;
4828: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
4829: l_rts_count NUMBER;
4830: l_task_rec get_task_prelim_details%ROWTYPE;
4831: l_count_rec get_rts_count%ROWTYPE;
4832: l_is_comp_visit VARCHAR2(1);

Line 4947: UPDATE AHL_VISIT_TASKS_B

4943:
4944:
4945: -- Perform update if the status is still success
4946: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4947: UPDATE AHL_VISIT_TASKS_B
4948: SET object_version_number = object_version_number + 1,
4949: last_update_date = sysdate,
4950: last_updated_by = FND_GLOBAL.user_id,
4951: last_update_login = FND_GLOBAL.login_id,

Line 5097: FROM ahl_visit_tasks_b

5093: SELECT visit_task_number,
5094: status_code,
5095: task_type_code,
5096: return_to_supply_flag current_rts
5097: FROM ahl_visit_tasks_b
5098: WHERE visit_task_id = c_visit_task_id;
5099:
5100: -- Define local variables
5101: l_visit_task_number NUMBER;

Line 5102: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;

5098: WHERE visit_task_id = c_visit_task_id;
5099:
5100: -- Define local variables
5101: l_visit_task_number NUMBER;
5102: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
5103: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
5104: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
5105: l_task_rec get_task_prelim_details%ROWTYPE;
5106:

Line 5103: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;

5099:
5100: -- Define local variables
5101: l_visit_task_number NUMBER;
5102: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
5103: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
5104: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
5105: l_task_rec get_task_prelim_details%ROWTYPE;
5106:
5107: BEGIN

Line 5104: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;

5100: -- Define local variables
5101: l_visit_task_number NUMBER;
5102: l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
5103: l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
5104: l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
5105: l_task_rec get_task_prelim_details%ROWTYPE;
5106:
5107: BEGIN
5108:

Line 5171: UPDATE AHL_VISIT_TASKS_B

5167: END IF;
5168:
5169: -- Perform update if the status is still success
5170: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5171: UPDATE AHL_VISIT_TASKS_B
5172: SET object_version_number = object_version_number + 1,
5173: last_update_date = sysdate,
5174: last_updated_by = FND_GLOBAL.user_id,
5175: last_update_login = FND_GLOBAL.login_id,

Line 5270: FROM ahl_visit_tasks_b ATSK,

5266: -- Define local cursors
5267: -- Get the instance numbers which are marked RTS in more than one instance in visit
5268: CURSOR get_vst_violating_inst(c_visit_id NUMBER) IS
5269: SELECT CSIS.instance_number
5270: FROM ahl_visit_tasks_b ATSK,
5271: csi_item_instances CSIS
5272: WHERE ATSK.instance_id = CSIS.instance_id
5273: AND ATSK.status_code = 'PLANNING'
5274: AND ATSK.return_to_supply_flag = 'Y'

Line 5348: ' FROM ahl_visit_tasks_b ATSK, ' ||

5344: IF taskStr IS NOT NULL THEN
5345:
5346: -- Formulate the query with the tasks obtained
5347: l_query_str := ' SELECT CSIS.instance_number ' ||
5348: ' FROM ahl_visit_tasks_b ATSK, ' ||
5349: ' csi_item_instances CSIS ' ||
5350: ' WHERE ATSK.instance_id = CSIS.instance_id ' ||
5351: ' AND ATSK.return_to_supply_flag = ''Y'' ' ||
5352: ' AND ATSK.status_code = ''PLANNING'' ' ||

Line 5440: FROM AHL_VISIT_TASKS_B TSK

5436: -- cursor to validate the given task is in 'PLANNING' and Task_Type is either 'PLANNED' OR 'UNPLANNED' (not unassociated or summary)
5437: CURSOR validate_task_csr (c_task_id NUMBER)
5438: IS
5439: SELECT 'X'
5440: FROM AHL_VISIT_TASKS_B TSK
5441: WHERE TSK.VISIT_TASK_ID = c_task_id
5442: AND TSK.STATUS_CODE = 'PLANNING'
5443: AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED');
5444: -- SATRAJEN :: Bug 13054370 :: Task which is a dependent task should not be duplicated.

Line 5456: FROM AHL_VISIT_TASKS_B TSK,

5452: -- cursor to validate if route for the task has its Duplicate Flag set and Return to Supply flag not set.
5453: CURSOR validate_route_csr (c_task_id NUMBER)
5454: IS
5455: SELECT 'X'
5456: FROM AHL_VISIT_TASKS_B TSK,
5457: AHL_ROUTES_B ROUTE,
5458: AHL_MR_ROUTES AMRR
5459: WHERE TSK.VISIT_TASK_ID = c_task_id
5460: AND NVL(TSK.RETURN_TO_SUPPLY_FLAG, 'X') <> 'Y'

Line 5469: FROM AHL_VISIT_TASKS_B TSK1,

5465: -- for the same route in the visit having task status "PLANNING" or "RELEASED" and operating on same instance.
5466: CURSOR validate_duplicate_task_csr (c_task_id NUMBER)
5467: IS
5468: SELECT 'X'
5469: FROM AHL_VISIT_TASKS_B TSK1,
5470: AHL_ROUTES_B ROUTE,
5471: AHL_MR_ROUTES AMRR1
5472: WHERE TSK1.VISIT_TASK_ID = c_task_id
5473: AND TSK1.STATUS_CODE = 'PLANNING'

Line 5480: FROM AHL_VISIT_TASKS_B TSK2,

5476: AND AMRR1.ROUTE_ID = ROUTE.ROUTE_ID
5477: AND ROUTE.DUPLICATE_FLAG = 'Y'
5478: AND EXISTS
5479: (SELECT 'X'
5480: FROM AHL_VISIT_TASKS_B TSK2,
5481: AHL_MR_ROUTES AMRR2
5482: WHERE TSK2.VISIT_ID = TSK1.VISIT_ID
5483: AND TSK2.STATUS_CODE IN ('RELEASED', 'PLANNING')
5484: AND TSK2.MR_ROUTE_ID = AMRR2.MR_ROUTE_ID

Line 5497: FROM AHL_VISIT_TASKS_B TSK

5493:
5494: CURSOR validate_mr_csr (c_task_id NUMBER)
5495: IS
5496: SELECT 'X'
5497: FROM AHL_VISIT_TASKS_B TSK
5498: WHERE TSK.VISIT_ID IN
5499: (SELECT VISIT_ID FROM AHL_VISIT_TASKS_B
5500: WHERE VISIT_TASK_ID = c_task_id)
5501: AND TSK.VISIT_TASK_ID <> c_task_id

Line 5499: (SELECT VISIT_ID FROM AHL_VISIT_TASKS_B

5495: IS
5496: SELECT 'X'
5497: FROM AHL_VISIT_TASKS_B TSK
5498: WHERE TSK.VISIT_ID IN
5499: (SELECT VISIT_ID FROM AHL_VISIT_TASKS_B
5500: WHERE VISIT_TASK_ID = c_task_id)
5501: AND TSK.VISIT_TASK_ID <> c_task_id
5502: AND TSK.STATUS_CODE IN ('PLANNING', 'RELEASED')
5503: AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')

Line 5506: FROM AHL_VISIT_TASKS_B TSK1

5502: AND TSK.STATUS_CODE IN ('PLANNING', 'RELEASED')
5503: AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
5504: START WITH TSK.VISIT_TASK_ID IN
5505: (SELECT TSK1.VISIT_TASK_ID
5506: FROM AHL_VISIT_TASKS_B TSK1
5507: WHERE TSK1.ORIGINATING_TASK_ID IS NULL
5508: START WITH TSK1.VISIT_TASK_ID = c_task_id
5509: CONNECT BY PRIOR TSK1.ORIGINATING_TASK_ID = TSK1.VISIT_TASK_ID
5510: )

Line 5517: FROM AHL_VISIT_TASKS_B TSK

5513: -- cursor to fetch OVN for a given task.
5514: CURSOR visit_task_csr (c_task_id NUMBER)
5515: IS
5516: SELECT TSK.OBJECT_VERSION_NUMBER
5517: FROM AHL_VISIT_TASKS_B TSK
5518: WHERE TSK.VISIT_TASK_ID = c_task_id
5519: AND TSK.STATUS_CODE <> 'DELETED';
5520:
5521: BEGIN

Line 5619: UPDATE AHL_VISIT_TASKS_B

5615: Fnd_Message.Set_Token('TASK_NO', p_task_rec_tbl(i).Visit_Task_Number);
5616: Fnd_Msg_Pub.ADD;
5617: RAISE FND_API.G_EXC_ERROR;
5618: ELSE
5619: UPDATE AHL_VISIT_TASKS_B
5620: SET TASK_TYPE_CODE = 'DUPLICATE' ,
5621: LAST_UPDATE_DATE = SYSDATE ,
5622: LAST_UPDATED_BY = Fnd_Global.USER_ID ,
5623: OBJECT_VERSION_NUMBER = p_task_rec_tbl(i).object_version_number + 1,

Line 5688: FROM AHL_VISIT_TASKS_B TSK

5684:
5685: -- cursor to validate the given task is in 'PLANNING' and Task_Type is 'DUPLICATE'
5686: CURSOR validate_task_csr (c_task_id NUMBER) IS
5687: SELECT 'X'
5688: FROM AHL_VISIT_TASKS_B TSK
5689: WHERE TSK.VISIT_TASK_ID = c_task_id --cursor param
5690: AND TSK.STATUS_CODE = 'PLANNING'
5691: AND TSK.TASK_TYPE_CODE = 'DUPLICATE';
5692:

Line 5696: FROM AHL_VISIT_TASKS_B TSK

5692:
5693: -- cursor to validate if none of the tasks with in a given task's root MR are released.
5694: CURSOR validate_task_status_csr (c_task_id NUMBER) IS
5695: SELECT 'X'
5696: FROM AHL_VISIT_TASKS_B TSK
5697: WHERE TSK.STATUS_CODE = 'RELEASED'
5698: START WITH TSK.VISIT_TASK_ID = (SELECT ORIGINATING_TASK_ID
5699: FROM AHL_VISIT_TASKS_B
5700: WHERE VISIT_TASK_ID = c_task_id)

Line 5699: FROM AHL_VISIT_TASKS_B

5695: SELECT 'X'
5696: FROM AHL_VISIT_TASKS_B TSK
5697: WHERE TSK.STATUS_CODE = 'RELEASED'
5698: START WITH TSK.VISIT_TASK_ID = (SELECT ORIGINATING_TASK_ID
5699: FROM AHL_VISIT_TASKS_B
5700: WHERE VISIT_TASK_ID = c_task_id)
5701: CONNECT BY PRIOR TSK.VISIT_TASK_ID = TSK.ORIGINATING_TASK_ID;
5702:
5703:

Line 5709: FROM AHL_VISIT_TASKS_B TSK,

5705: IS
5706: SELECT TSK.OBJECT_VERSION_NUMBER,
5707: TSK.SERVICE_REQUEST_ID,
5708: AUEF.MANUALLY_PLANNED_FLAG
5709: FROM AHL_VISIT_TASKS_B TSK,
5710: AHL_UNIT_EFFECTIVITIES_B AUEF
5711: WHERE TSK.VISIT_TASK_ID = c_task_id
5712: AND TSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID;
5713:

Line 5787: UPDATE AHL_VISIT_TASKS_B

5783: Fnd_Message.Set_Token('TASK_NO', p_task_rec_tbl(i).Visit_Task_Number);
5784: Fnd_Msg_Pub.ADD;
5785: RAISE FND_API.G_EXC_ERROR;
5786: ELSE
5787: UPDATE AHL_VISIT_TASKS_B
5788: SET TASK_TYPE_CODE = l_task_type_code ,
5789: LAST_UPDATE_DATE = SYSDATE ,
5790: LAST_UPDATED_BY = Fnd_Global.USER_ID ,
5791: OBJECT_VERSION_NUMBER = p_task_rec_tbl(i).object_version_number + 1,

Line 5873: FROM ahl_visit_tasks_b

5869:
5870: Cursor get_summary_task (c_UE_id IN NUMBER)
5871: IS
5872: SELECT visit_task_id, visit_id
5873: FROM ahl_visit_tasks_b
5874: WHERE unit_effectivity_id = c_UE_id
5875: AND status_code IN ( 'PLANNING', 'PARTIALLY RELEASED')
5876: AND task_type_code = 'SUMMARY';
5877: l_summary_task_id NUMBER;

Line 5883: FROM ahl_visits_b visit, ahl_visit_tasks_b task

5879:
5880: Cursor is_visit_implemented (c_visit_id IN NUMBER)
5881: IS
5882: SELECT 'N'
5883: FROM ahl_visits_b visit, ahl_visit_tasks_b task
5884: WHERE visit.visit_id = c_visit_id
5885: AND visit.visit_id = task.visit_id
5886: AND task.status_code = 'PLANNING';
5887: l_is_visit_implemented VARCHAR2(1);