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_UMP_FORECAST_REQ_PVT dependencies on AHL_SCHEDULE_MATERIALS

Line 71: -- Insert these material requirements into AHL_SCHEDULE_MATERIALS for ASCP/DP to pick up and plan the

67: -- Start of Comments --
68: -- Procedure name : Process_Mrl_Req_Forecast
69: -- Type : Private
70: -- Function : Private API to collect the material requirements for unit effectivities of a given set of item instances.
71: -- Insert these material requirements into AHL_SCHEDULE_MATERIALS for ASCP/DP to pick up and plan the
72: -- forecasted material requirements.
73: -- If a unit effectivity does not have due date, the material forecast is not done.
74: -- Pre-reqs :
75: -- Parameters :

Line 111: /* Delete orphan forecast record in AHL_SCHEDULE_MATERIALS table for

107: ) IS
108:
109: l_Route_Mtl_Req_Tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
110:
111: /* Delete orphan forecast record in AHL_SCHEDULE_MATERIALS table for
112: * deleted unit effectivities
113: * unit effectivities where due date is null after the re-calculation
114: */
115: /*CURSOR delete_schedule_mtl_csr IS

Line 116: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM

112: * deleted unit effectivities
113: * unit effectivities where due date is null after the re-calculation
114: */
115: /*CURSOR delete_schedule_mtl_csr IS
116: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
117: WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
118: AND SM.unit_effectivity_id IS NOT NULL
119: AND NOT EXISTS(SELECT 'x' FROM AHL_UNIT_EFFECTIVITIES_APP_V UE WHERE UE.unit_effectivity_id = SM.unit_effectivity_id)
120: UNION ALL

Line 121: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM, AHL_UNIT_EFFECTIVITIES_APP_V UE

117: WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
118: AND SM.unit_effectivity_id IS NOT NULL
119: AND NOT EXISTS(SELECT 'x' FROM AHL_UNIT_EFFECTIVITIES_APP_V UE WHERE UE.unit_effectivity_id = SM.unit_effectivity_id)
120: UNION ALL
121: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM, AHL_UNIT_EFFECTIVITIES_APP_V UE
122: WHERE (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
123: AND UE.due_date IS NULL
124: AND SM.unit_effectivity_id IS NOT NULL
125: AND SM.material_request_type = G_REQ_TYPE_FORECAST

Line 149: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM

145: /*
146: * Find out the schedule material records for expired routes
147: */
148: CURSOR del_exp_route_schedule_mtl_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER) IS
149: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
150: WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
151: AND SM.mr_route_id = p_mr_route_id
152: AND SM.unit_effectivity_id IS NOT NULL
153: AND SM.unit_effectivity_id = p_unit_effectivity_id;

Line 156: * in AHL_SCHEDULE_MATERIALS table

152: AND SM.unit_effectivity_id IS NOT NULL
153: AND SM.unit_effectivity_id = p_unit_effectivity_id;
154:
155: /* Validates whether a forecast record with unique key combination exisits
156: * in AHL_SCHEDULE_MATERIALS table
157: * output record used for DML update operation
158: */
159: CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER, p_inventory_item_id IN NUMBER, p_rt_oper_material_id IN NUMBER) IS
160: SELECT * FROM AHL_SCHEDULE_MATERIALS SM

Line 160: SELECT * FROM AHL_SCHEDULE_MATERIALS SM

156: * in AHL_SCHEDULE_MATERIALS table
157: * output record used for DML update operation
158: */
159: CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER, p_inventory_item_id IN NUMBER, p_rt_oper_material_id IN NUMBER) IS
160: SELECT * FROM AHL_SCHEDULE_MATERIALS SM
161: WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
162: AND NVL(SM.rt_oper_material_id,-1) = NVL(p_rt_oper_material_id,-1)
163: AND SM.inventory_item_id = p_inventory_item_id
164: AND SM.mr_route_id = p_mr_route_id

Line 175: l_Schedule_Mtl_Req_rec AHL_SCHEDULE_MATERIALS%ROWTYPE;

171: SELECT DISTINCT visit_id
172: FROM ahl_visit_tasks_b
173: WHERE unit_effectivity_id = p_unit_effectivity_id;
174:
175: l_Schedule_Mtl_Req_rec AHL_SCHEDULE_MATERIALS%ROWTYPE;
176:
177: l_requirement_date DATE;
178: l_previous_route_id NUMBER;
179: l_previous_req_date DATE;

Line 208: AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => delete_mtl_forecast_rec.scheduled_material_id);

204:
205: -- perform orphan forcast delete. Which ones? refer to comments for cursor.
206: -- delete operation transferred to due date calculation API
207: /*FOR delete_mtl_forecast_rec IN delete_schedule_mtl_csr LOOP
208: AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => delete_mtl_forecast_rec.scheduled_material_id);
209: END LOOP;*/
210:
211: IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
212: fnd_log.string

Line 259: AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);

255: || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id
256: );
257: END IF;
258: FOR del_exp_route_rec IN del_exp_route_schedule_mtl_csr(ue_mr_routes_rec.unit_effectivity_id, ue_mr_routes_rec.mr_route_id) LOOP
259: AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);
260: END LOOP;
261: ELSE
262: -- route is valid and proceed to forecast
263: IF(TRUNC(ue_mr_routes_rec.due_date) < TRUNC(SYSDATE))THEN

Line 328: 'Inserting record in AHL_SCHEDULE_MATERIALS '

324: fnd_log.string
325: (
326: G_DEBUG_STMT,
327: 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
328: 'Inserting record in AHL_SCHEDULE_MATERIALS '
329: );
330: fnd_log.string
331: (
332: G_DEBUG_STMT,

Line 367: AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW

363: 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
364: 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code
365: );
366: END IF;
367: AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW
368: (
369: X_SCHEDULED_MATERIAL_ID => NULL,
370: X_OBJECT_VERSION_NUMBER => 1,
371: X_LAST_UPDATE_DATE => SYSDATE,

Line 431: 'Updating record in AHL_SCHEDULE_MATERIALS '

427: fnd_log.string
428: (
429: G_DEBUG_STMT,
430: 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
431: 'Updating record in AHL_SCHEDULE_MATERIALS '
432: );
433: fnd_log.string
434: (
435: G_DEBUG_STMT,

Line 470: AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW

466: 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
467: 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code
468: );
469: END IF;
470: AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW
471: (
472: X_SCHEDULED_MATERIAL_ID => l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID,
473: X_OBJECT_VERSION_NUMBER => l_Schedule_Mtl_Req_rec.OBJECT_VERSION_NUMBER,--Update Column
474: X_LAST_UPDATE_DATE => SYSDATE,

Line 1042: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM

1038: IS
1039:
1040: -- Find out the schedule material records for expired routes
1041: CURSOR del_exp_route_schedule_mtl_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER) IS
1042: SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
1043: WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
1044: AND SM.mr_route_id = p_mr_route_id
1045: AND SM.unit_effectivity_id IS NOT NULL
1046: AND SM.unit_effectivity_id = p_unit_effectivity_id;

Line 1049: -- in AHL_SCHEDULE_MATERIALS table

1045: AND SM.unit_effectivity_id IS NOT NULL
1046: AND SM.unit_effectivity_id = p_unit_effectivity_id;
1047:
1048: -- Validates whether a forecast record with unique key combination exisits
1049: -- in AHL_SCHEDULE_MATERIALS table
1050: -- output record used for DML update operation
1051: CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER,
1052: p_mr_route_id IN NUMBER,
1053: p_inventory_item_id IN NUMBER,

Line 1055: SELECT * FROM AHL_SCHEDULE_MATERIALS SM

1051: CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER,
1052: p_mr_route_id IN NUMBER,
1053: p_inventory_item_id IN NUMBER,
1054: p_rt_oper_material_id IN NUMBER) IS
1055: SELECT * FROM AHL_SCHEDULE_MATERIALS SM
1056: WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
1057: AND NVL(SM.rt_oper_material_id,-1) = NVL(p_rt_oper_material_id,-1)
1058: AND SM.inventory_item_id = p_inventory_item_id
1059: AND SM.mr_route_id = p_mr_route_id

Line 1071: l_Schedule_Mtl_Req_rec AHL_SCHEDULE_MATERIALS%ROWTYPE;

1067: FROM ahl_visit_tasks_b
1068: WHERE unit_effectivity_id = p_unit_effectivity_id;
1069:
1070: l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Material_Req';
1071: l_Schedule_Mtl_Req_rec AHL_SCHEDULE_MATERIALS%ROWTYPE;
1072: l_requirement_date DATE;
1073: l_Route_Mtl_Req_Tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1074: l_visit_id NUMBER;
1075:

Line 1102: AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);

1098: 'p_route_id :p_mr_route_id : ' || p_route_id || ':' || p_mr_route_id);
1099: END IF;
1100:
1101: FOR del_exp_route_rec IN del_exp_route_schedule_mtl_csr(p_unit_effectivity_id,p_mr_route_id) LOOP
1102: AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);
1103: END LOOP;
1104:
1105: ELSE
1106: -- route is valid and proceed to forecast

Line 1167: 'Inserting record in AHL_SCHEDULE_MATERIALS ');

1163: FETCH schedule_mtl_exists_csr INTO l_Schedule_Mtl_Req_rec;
1164: IF(schedule_mtl_exists_csr%NOTFOUND)THEN
1165: IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1166: fnd_log.string (G_DEBUG_STMT, l_debug_module,
1167: 'Inserting record in AHL_SCHEDULE_MATERIALS ');
1168: fnd_log.string (G_DEBUG_STMT,l_debug_module,
1169: 'unit_effectivity_id : ' || p_unit_effectivity_id);
1170: fnd_log.string (G_DEBUG_STMT,l_debug_module,
1171: 'requested_date : ' || p_due_date);

Line 1181: AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW

1177: 'quantity : ' || l_Route_Mtl_Req_Tbl(j).quantity);
1178: fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1179: 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code);
1180: END IF;
1181: AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW
1182: (
1183: X_SCHEDULED_MATERIAL_ID => NULL,
1184: X_OBJECT_VERSION_NUMBER => 1,
1185: X_LAST_UPDATE_DATE => SYSDATE,

Line 1242: 'Updating record in AHL_SCHEDULE_MATERIALS ');

1238: );
1239: ELSE -- schedule_mtl_exists
1240: IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1241: fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1242: 'Updating record in AHL_SCHEDULE_MATERIALS ');
1243: fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1244: 'unit_effectivity_id : ' || p_unit_effectivity_id);
1245: fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1246: 'requested_date : ' || p_due_date);

Line 1258: AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW

1254: 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code);
1255: fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1256: 'SCHEDULED_MATERIAL_ID:' || l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID);
1257: END IF;
1258: AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW
1259: (
1260: X_SCHEDULED_MATERIAL_ID => l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID,
1261: X_OBJECT_VERSION_NUMBER => l_Schedule_Mtl_Req_rec.OBJECT_VERSION_NUMBER,--Update Column
1262: X_LAST_UPDATE_DATE => SYSDATE,