[Home] [Help]
36: CURSOR c_sch_mat_cur (c_visit_id IN NUMBER)
37: IS
38: SELECT scheduled_material_id,
39: object_version_number
40: FROM ahl_schedule_materials
41: WHERE visit_id = c_visit_id;
42:
43: CURSOR c_visit_task_matrl_cur(c_sch_mat_id IN NUMBER)
44: IS
110: Fnd_Msg_Pub.ADD;
111: CLOSE c_visit_task_matrl_cur;
112: RAISE Fnd_Api.G_EXC_ERROR;
113: ELSE
114: UPDATE ahl_schedule_materials
115: SET requested_quantity = 0,
116: status = 'DELETED',
117: object_version_number = l_object_version_number + 1,
118: last_update_date = SYSDATE,
202: -- PROCEDURE
203: -- Insert_Planned_Matrls
204: --
205: -- PURPOSE
206: -- Creates record in ahl_schedule_materials
207: --
208: -- PARAMETERS
209: --
210: -- NOTES
239: -- yazhou 03-JUL-2006 starts
240: -- bug fix#5303378
241:
242: SELECT scheduled_material_id
243: FROM AHL_SCHEDULE_MATERIALS
244: WHERE visit_id = c_visit_id
245: AND visit_task_id = c_visit_task_id
246: -- AND requested_quantity <> 0
247: AND NVL(status,'') = 'ACTIVE'
342: l_task_type_code VARCHAR2(30);
343: l_material_request_type VARCHAR2(30);
344: l_Prior_Item_Rec Get_Prior_Item_Cur%ROWTYPE;
345: l_isInservice AHL_RT_OPER_MATERIALS.IN_SERVICE%TYPE; --Added by sowsubra for Issue 105
346: l_mat_status AHL_SCHEDULE_MATERIALS.STATUS%TYPE; --Added by sowsubra for Issue 105
347:
348: BEGIN
349:
350: IF (l_log_procedure >= l_log_current_level)THEN
374:
375: END IF;
376:
377: --Get the sequence number
378: SELECT ahl_schedule_materials_s.nextval INTO l_schedule_material_id
379: FROM DUAL;
380: --Check for record exists
381: OPEN check_matrl_cur(p_visit_id,p_visit_task_id,p_rt_oper_material_id);
382: FETCH check_matrl_cur INTO l_dummy;
567: END IF;
568:
569: -- Insert the record into schedule materials
570: IF (l_dummy IS NULL AND l_inventory_org_item_id IS NOT NULL )THEN
571: INSERT INTO AHL_SCHEDULE_MATERIALS
572: (SCHEDULED_MATERIAL_ID,
573: OBJECT_VERSION_NUMBER,
574: LAST_UPDATE_DATE,
575: LAST_UPDATED_BY,
762: organization_id,
763: completed_quantity,
764: requested_date,
765: visit_id
766: FROM ahl_schedule_materials
767: WHERE scheduled_material_id = c_sched_mat_id;
768:
769: CURSOR Get_Inv_Item_cur (c_item_desc IN VARCHAR2,
770: c_org_id IN NUMBER)
845: fnd_log.string
846: (
847: l_log_error,
848: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
849: 'Schedule Material id not found in ahl_schedule_materials table'
850: );
851: END IF;
852: CLOSE Get_Planned_Items_cur;
853: RAISE FND_API.G_EXC_ERROR;
899: END IF;
900:
901: -- AnRaj: Moved this code down, after the id in l_planned_materials_tbl has been populated
902: -- Serial Number Reservation Enhancement Changes Start.
903: -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
904: IF l_planned_materials_tbl(i).inventory_item_id <> l_Planned_Items_rec.inventory_item_id
905: THEN
906: SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
907: INTO l_rsvd_quantity
905: THEN
906: SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
907: INTO l_rsvd_quantity
908: FROM mtl_reservations MR,
909: ahl_schedule_materials SM
910: WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
911: AND MR.external_source_code = 'AHL'
912: AND MR.demand_source_line_detail = SM.scheduled_material_id
913: AND MR.organization_id = SM.organization_id
941: RAISE FND_API.G_EXC_ERROR;
942: END IF;
943:
944: -- Serial Number Reservation Enhancement Changes Starts.
945: -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
946: IF NVL(l_planned_materials_tbl(i).quantity,-9) <> NVL(l_Planned_Items_rec.requested_quantity,-99)
947: THEN
948: SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
949: INTO l_rsvd_quantity
947: THEN
948: SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
949: INTO l_rsvd_quantity
950: FROM mtl_reservations MR,
951: ahl_schedule_materials SM
952: WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
953: AND MR.external_source_code = 'AHL'
954: AND MR.demand_source_line_detail = SM.scheduled_material_id
955: AND MR.organization_id = SM.organization_id
1034: LOOP
1035: --
1036: IF l_planned_materials_tbl(i).schedule_material_id IS NOT NULL THEN
1037: --
1038: UPDATE ahl_schedule_materials
1039: SET inventory_item_id = l_planned_materials_tbl(i).inventory_item_id,
1040: requested_quantity = l_planned_materials_tbl(i).quantity,
1041: -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1042: requested_date = trunc(l_planned_materials_tbl(i).requested_date),
1518: asm.scheduled_material_id scheduled_material_id,
1519: asm.object_version_number,
1520: asm.scheduled_quantity,
1521: asm.scheduled_date
1522: FROM ahl_visit_tasks_b tsk,ahl_schedule_materials asm
1523: WHERE asm.visit_id = C_VISIT_ID
1524: AND asm.visit_task_id = tsk.visit_task_id
1525: AND tsk.status_code ='DELETED'
1526: AND asm.status <> 'DELETED';
1530: IS
1531: SELECT requested_quantity,
1532: scheduled_material_id,
1533: object_version_number
1534: FROM ahl_schedule_materials
1535: WHERE visit_task_id = c_visit_task_id
1536: AND rt_oper_material_id = c_rt_oper_material_id
1537: AND NVL(STATUS, 'X') = 'ACTIVE';
1538:
1621: 'Updating the status to DELETED for Material Requirement' || l_Deleted_Items_Rec.scheduled_material_id
1622: );
1623: END IF;
1624:
1625: UPDATE ahl_schedule_materials
1626: SET requested_quantity =0,
1627: status = 'DELETED',
1628: object_version_number = l_Deleted_Items_Rec.object_version_number + 1
1629: WHERE scheduled_material_id = l_Deleted_Items_Rec.scheduled_material_id ;
1799: END IF;
1800:
1801: -- delete existing requirement for a given rt_oper_material_id and task combination
1802:
1803: UPDATE ahl_schedule_materials
1804: SET requested_quantity =0,
1805: status = 'DELETED',
1806: object_version_number = l_Planned_Items_Rec.object_version_number + 1
1807: WHERE scheduled_material_id = l_Planned_Items_Rec.scheduled_material_id ;
1978: ASMT.visit_task_id,
1979: ASMT.scheduled_material_id schedule_material_id,
1980: decode(sign( trunc(ASMT.scheduled_date) - trunc(requested_date)), 1, ASMT.scheduled_date, null) SCHEDULED_DATE,
1981: ASMT.SCHEDULED_QUANTITY
1982: FROM AHL_SCHEDULE_MATERIALS ASMT,
1983: AHL_VISIT_TASKS_B VTSK
1984: WHERE ASMT.STATUS <> 'DELETED'
1985: AND EXISTS ( Select 1
1986: from AHL_RT_OPER_MATERIALS RTOM
2741: FETCH visit_task_mtrls_cur INTO l_visit_task_mtrls_rec;
2742: EXIT WHEN visit_task_mtrls_cur%NOTFOUND;
2743: -- update request quanity zero
2744: IF l_visit_task_mtrls_rec.schedule_material_id IS NOT NULL THEN
2745: UPDATE ahl_schedule_materials
2746: SET requested_quantity = 0,
2747: status = 'DELETED',
2748: object_version_number = l_visit_task_mtrls_rec.object_version_number + 1
2749: WHERE scheduled_material_id = l_visit_task_mtrls_rec.schedule_material_id;
2766: FETCH visit_mtrls_cur INTO l_visit_mtrls_rec;
2767: EXIT WHEN visit_mtrls_cur%NOTFOUND;
2768: -- update request quanity zero
2769: IF l_visit_mtrls_rec.schedule_material_id IS NOT NULL THEN
2770: UPDATE ahl_schedule_materials
2771: SET requested_quantity = 0,
2772: status = 'DELETED',
2773: object_version_number = l_visit_mtrls_rec.object_version_number + 1
2774: WHERE scheduled_material_id = l_visit_mtrls_rec.schedule_material_id;
2892: SELECT visit_id,
2893: visit_task_id,
2894: scheduled_material_id,
2895: object_version_number
2896: FROM ahl_schedule_materials
2897: WHERE visit_id = c_visit_id
2898: AND status = 'ACTIVE';
2899:
2900: --Standard local variables
2939: LOOP
2940: FETCH visit_mtrls_cur INTO l_visit_mtrls_rec;
2941: EXIT WHEN visit_mtrls_cur%NOTFOUND;
2942: IF l_visit_mtrls_rec.scheduled_material_id IS NOT NULL THEN
2943: UPDATE ahl_schedule_materials
2944: SET STATUS = 'HISTORY',
2945: OBJECT_VERSION_NUMBER = l_visit_mtrls_rec.object_version_number
2946: WHERE scheduled_material_id = l_visit_mtrls_rec.scheduled_material_id;
2947: END IF;
3030: IS
3031: -- AnRaj: Changed the WHERE clause , for Performance improvement
3032: CURSOR get_del_mtl_req_csr(c_visit_id IN NUMBER) IS
3033: SELECT mat.scheduled_material_id
3034: FROM ahl_schedule_materials mat,
3035: ahl_visit_tasks_b vt
3036: WHERE vt.visit_id = c_visit_id
3037: AND vt.status_code = 'DELETED'
3038: AND vt.visit_task_id = mat.visit_task_id
3264: RAISE Fnd_Api.G_EXC_ERROR;
3265: END IF;
3266:
3267: --Update the status of the record to 'HISTORY'
3268: UPDATE ahl_Schedule_materials
3269: SET STATUS = 'HISTORY',
3270: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3271: LAST_UPDATE_DATE = sysdate,
3272: LAST_UPDATED_BY = Fnd_Global.USER_ID,