[Home] [Help]
PACKAGE BODY: APPS.AHL_UMP_FORECAST_REQ_PVT
Source
1 PACKAGE BODY AHL_UMP_FORECAST_REQ_PVT AS
2 /* $Header: AHLVURQB.pls 120.1.12010000.2 2008/12/27 00:46:26 sracha ship $ */
3
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UMP_FORECAST_REQ_PVT';
8 --G_APP_MODULE CONSTANT VARCHAR2(30) := 'AHL';
9 G_APP_MODULE CONSTANT VARCHAR2(30) := RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
10
11 -- UMP Statuses
12 G_REQ_TYPE_FORECAST CONSTANT VARCHAR2(30) := 'FORECAST';
13
14 G_IS_PM_INSTALLED CONSTANT VARCHAR2(1) := AHL_UTIL_PKG.IS_PM_INSTALLED;
15
16 -- FND Logging Constants
17 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
18 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_DEBUG_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
21
22 ------------------------------
23 -- Declare Global Variables --
24 ------------------------------
25 G_previous_route_id NUMBER;
26 G_previous_req_date DATE;
27
28
29 ------------------------
30 -- Define Table Types --
31 ------------------------
32 -- number table.
33 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
34
35 -- varchar2 table.
36 TYPE vchar_tbl_type IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
37
38 -- date table
39 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
40
41 --------------------------------------
42 -- Declare Procedures and Functions --
43 --------------------------------------
44 -- To get the root item instance for the input item instance if exists.
45 FUNCTION Get_RootInstanceID(p_csi_item_instance_id IN NUMBER)
46 RETURN NUMBER;
47
48 -- Build the item instance tree containing root nodes and its components.
49 PROCEDURE Build_Config_Tree(p_csi_root_instance_id IN NUMBER,
50 p_master_config_id IN NUMBER,
51 x_config_node_tbl OUT NOCOPY AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type);
52
53 -- process material requirement for a UE.
54 PROCEDURE Process_Material_Req (p_unit_effectivity_id IN NUMBER,
55 p_csi_item_instance_id IN NUMBER,
56 p_due_date IN DATE,
57 p_mr_route_id IN NUMBER,
58 p_route_id IN NUMBER,
59 p_r_start_date_active IN DATE,
60 p_r_end_date_active IN DATE);
61
62 -- log error messages into concurrent log.
63 PROCEDURE log_error_messages;
64 -------------------------------------
65 -- Define Procedures and Functions --
66 -------------------------------------
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 :
76 --
77 -- Standard IN Parameters :
78 -- p_api_version IN NUMBER Required
79 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
80 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
81 --
82 -- Standard OUT Parameters :
83 -- x_return_status OUT VARCHAR2 Required
84 -- x_msg_count OUT NUMBER Required
85 -- x_msg_data OUT VARCHAR2 Required
86 --
87 -- Get_Route_Mtl_Req Parameters:
88 -- P_applicable_instances_tbl IN AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type Required
89 -- The table of records containing list of item instances for which the due
90 -- date calculation process is being performed.
91 --
92 -- Version :
93 -- Initial Version 1.0
94 -- Create By : Sunil Kumar
95 -- End of Comments.
96
97 PROCEDURE Process_Mrl_Req_Forecast
98 (
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 P_applicable_instances_tbl IN AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type
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
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
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
126 AND SM.unit_effectivity_id = UE.unit_effectivity_id;*/
127
128 /* Finds out all
129 * open(means with status null or init-due) unit effectivities applicable to item instances - with not null due date
130 * mr routes for these unit effectitivities
131 */
132 CURSOR ue_mr_routes_csr (p_item_indtance_id IN NUMBER) IS
133 SELECT UE.unit_effectivity_id, UE.due_date, MR.mr_route_id, R.route_id, R.start_date_active, R.end_date_active
134 -- FROM AHL_UNIT_EFFECTIVITIES_APP_V UE, AHL_MR_ROUTES_V MR
135 FROM AHL_UNIT_EFFECTIVITIES_B UE, AHL_ROUTES_B R, AHL_MR_ROUTES MR
136 WHERE UE.mr_header_id = MR.mr_header_id
137 AND R.route_id = MR.ROUTE_ID
138 --AND MR.APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')))
139 AND UE.APPLICATION_USG_CODE = G_APP_MODULE
140 AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
141 AND UE.due_date IS NOT NULL
142 AND UE.csi_item_instance_id = p_item_indtance_id
143 ORDER BY MR.route_id, UE.due_date;
144
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;
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
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
165 AND SM.unit_effectivity_id = p_unit_effectivity_id
166 FOR UPDATE OF REQUESTED_DATE NOWAIT;
167
168 l_Schedule_Mtl_Req_rec AHL_SCHEDULE_MATERIALS%ROWTYPE;
169
170 l_requirement_date DATE;
171 l_previous_route_id NUMBER;
172 l_previous_req_date DATE;
173
174 l_debug_module varchar2(400) := 'AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast';
175
176 BEGIN
177 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
178 fnd_log.string
179 (
180 G_DEBUG_PROC,
181 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast.begin',
182 'At the start of PLSQL procedure'
183 );
184 END IF;
185
186 -- Standard start of API savepoint
187 SAVEPOINT Process_Mrl_Req_Forecast;
188
189 -- Initialize message list if p_init_msg_list is set to TRUE
190 IF FND_API.To_Boolean( p_init_msg_list) THEN
191 FND_MSG_PUB.Initialize;
192 END IF;
193
194 -- Initialize API return status to success
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 -- perform orphan forcast delete. Which ones? refer to comments for cursor.
198 -- delete operation transferred to due date calculation API
199 /*FOR delete_mtl_forecast_rec IN delete_schedule_mtl_csr LOOP
200 AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => delete_mtl_forecast_rec.scheduled_material_id);
201 END LOOP;*/
202
203 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
204 fnd_log.string
205 (
206 G_DEBUG_STMT,
207 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
208 'Have succesfully deleted the orphan forecasts'
209 );
210 END IF;
211
212 --Collect the material requirements and forecast them
213 IF(P_applicable_instances_tbl IS NOT NULL AND P_applicable_instances_tbl.COUNT > 0)THEN
214 FOR i IN P_applicable_instances_tbl.FIRST..P_applicable_instances_tbl.LAST LOOP
215 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
216 fnd_log.string
217 (
218 G_DEBUG_STMT,
219 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
220 'Processing unit effectivities for csi_item_instance_id : ' || P_applicable_instances_tbl(i).csi_item_instance_id
221 );
222 END IF;
223 FOR ue_mr_routes_rec IN ue_mr_routes_csr (P_applicable_instances_tbl(i).csi_item_instance_id) LOOP
224 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
225 fnd_log.string
226 (
227 G_DEBUG_STMT ,
228 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
229 'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API :p_route_id :p_mr_route_id : p_item_instance_id : p_requirement_date'
230 || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id || ':' ||P_applicable_instances_tbl(i).csi_item_instance_id
231 || ':' ||ue_mr_routes_rec.due_date
232 );
233 -- log into concurrent log file.
234 fnd_file.put_line(fnd_file.log,l_debug_module || ':p_route_id :p_mr_route_id : p_item_instance_id : p_requirement_date' || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id
235 || ':' ||P_applicable_instances_tbl(i).csi_item_instance_id || ':' ||ue_mr_routes_rec.due_date);
236 END IF;
237
238 IF NOT(TRUNC(NVL(ue_mr_routes_rec.start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
239 AND TRUNC(NVL(ue_mr_routes_rec.end_date_active,SYSDATE+1))>TRUNC(SYSDATE))THEN
240 -- route is expired so delete forecast
241 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
242 fnd_log.string
243 (
244 G_DEBUG_STMT ,
245 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
246 'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API :p_route_id :p_mr_route_id : '
247 || ue_mr_routes_rec.route_id || ':' || ue_mr_routes_rec.mr_route_id
248 );
249 END IF;
250 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
251 AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);
252 END LOOP;
253 ELSE
254 -- route is valid and proceed to forecast
255 IF(TRUNC(ue_mr_routes_rec.due_date) < TRUNC(SYSDATE))THEN
256 l_requirement_date := SYSDATE;
257 ELSE
258 l_requirement_date := ue_mr_routes_rec.due_date;
259 END IF;
260
261 IF( NVL(l_previous_route_id,-1) <> ue_mr_routes_rec.route_id OR
262 TRUNC(NVL(l_previous_req_date,l_requirement_date - 1)) <> TRUNC(l_requirement_date))THEN
263
264 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
265 (
266 p_api_version => 1.0,
267 p_init_msg_list => FND_API.G_FALSE,
268 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
269 x_return_status => x_return_status,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data,
272 p_route_id => ue_mr_routes_rec.route_id,
273 p_mr_route_id => ue_mr_routes_rec.mr_route_id,
274 p_item_instance_id => P_applicable_instances_tbl(i).csi_item_instance_id,
275 p_requirement_date => l_requirement_date,
276 p_request_type => G_REQ_TYPE_FORECAST,
277 x_route_mtl_req_tbl => l_Route_Mtl_Req_Tbl
278 );
279 l_previous_route_id := ue_mr_routes_rec.route_id;
280 l_previous_req_date := l_requirement_date;
281
282 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
283 IF(fnd_log.level_error >= G_DEBUG_LEVEL)THEN
284 fnd_log.string
285 (
286 fnd_log.level_error,
287 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
288 'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req Threw error'
289 );
290 END IF;
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293 END IF;
294
295 IF (l_Route_Mtl_Req_Tbl IS NOT NULL AND l_Route_Mtl_Req_Tbl.COUNT > 0)THEN
296 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
297 fnd_log.string
298 (
299 G_DEBUG_STMT,
300 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
301 'After call AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API : l_Route_Mtl_Req_Tbl.COUNT : ' || l_Route_Mtl_Req_Tbl.COUNT
302 );
303 END IF;
304 FOR j IN l_Route_Mtl_Req_Tbl.FIRST..l_Route_Mtl_Req_Tbl.LAST LOOP
305 OPEN schedule_mtl_exists_csr(ue_mr_routes_rec.unit_effectivity_id,
306 ue_mr_routes_rec.mr_route_id, l_Route_Mtl_Req_Tbl(j).inventory_item_id, l_Route_Mtl_Req_Tbl(j).rt_oper_material_id);
307 FETCH schedule_mtl_exists_csr INTO l_Schedule_Mtl_Req_rec;
308 IF(schedule_mtl_exists_csr%NOTFOUND)THEN
309 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
310 fnd_log.string
311 (
312 G_DEBUG_STMT,
313 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
314 'Inserting record in AHL_SCHEDULE_MATERIALS '
315 );
316 fnd_log.string
317 (
318 G_DEBUG_STMT,
319 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
320 'unit_effectivity_id : ' || ue_mr_routes_rec.unit_effectivity_id
321 );
322 fnd_log.string
323 (
324 G_DEBUG_STMT,
325 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
326 'requested_date : ' || ue_mr_routes_rec.due_date
327 );
328 fnd_log.string
329 (
330 G_DEBUG_STMT,
331 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
332 'mr_route_id : '|| ue_mr_routes_rec.mr_route_id
333 );
334 fnd_log.string
335 (
336 G_DEBUG_STMT,
337 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
338 'inventory_item_id : ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id
339 );
340 fnd_log.string
341 (
342 G_DEBUG_STMT,
343 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
344 'quantity : ' || l_Route_Mtl_Req_Tbl(j).quantity
345 );
346 fnd_log.string
347 (
348 G_DEBUG_STMT,
349 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
350 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code
351 );
352 END IF;
353 AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW
354 (
355 X_SCHEDULED_MATERIAL_ID => NULL,
356 X_OBJECT_VERSION_NUMBER => 1,
357 X_LAST_UPDATE_DATE => SYSDATE,
358 X_LAST_UPDATED_BY => fnd_global.user_id,
359 X_CREATION_DATE => SYSDATE,
360 X_CREATED_BY => fnd_global.user_id,
361 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
362 X_INVENTORY_ITEM_ID => l_Route_Mtl_Req_Tbl(j).inventory_item_id,
363 X_SCHEDULE_DESIGNATOR => NULL,
364 X_VISIT_ID => NULL,
365 X_VISIT_START_DATE => NULL,
366 X_VISIT_TASK_ID => NULL,
367 X_ORGANIZATION_ID => NULL,
368 X_SCHEDULED_DATE => NULL,
369 X_REQUEST_ID => NULL,
370 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
371 X_REQUESTED_DATE => trunc(ue_mr_routes_rec.due_date),
372 X_SCHEDULED_QUANTITY => NULL,
373 X_PROCESS_STATUS => NULL,
374 X_ERROR_MESSAGE => NULL,
375 X_TRANSACTION_ID => NULL,
376 X_UOM => l_Route_Mtl_Req_Tbl(j).uom_code,
377 X_RT_OPER_MATERIAL_ID => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,
378 X_OPERATION_CODE => NULL,
379 X_OPERATION_SEQUENCE => NULL,
380 X_ITEM_GROUP_ID => l_Route_Mtl_Req_Tbl(j).item_group_id,
381 X_REQUESTED_QUANTITY => l_Route_Mtl_Req_Tbl(j).quantity,
382 X_PROGRAM_ID => NULL,
383 X_PROGRAM_UPDATE_DATE => NULL,
384 X_LAST_UPDATED_DATE => NULL,
385 X_WORKORDER_OPERATION_ID => NULL,
386 X_POSITION_PATH_ID => l_Route_Mtl_Req_Tbl(j).position_path_id,
387 X_RELATIONSHIP_ID => l_Route_Mtl_Req_Tbl(j).relationship_id,
388 X_UNIT_EFFECTIVITY_ID => ue_mr_routes_rec.unit_effectivity_id,
389 X_MR_ROUTE_ID => ue_mr_routes_rec.mr_route_id,
390 X_MATERIAL_REQUEST_TYPE => G_REQ_TYPE_FORECAST,
391 X_ATTRIBUTE_CATEGORY => NULL,
392 X_ATTRIBUTE1 => NULL,
393 X_ATTRIBUTE2 => NULL,
394 X_ATTRIBUTE3 => NULL,
395 X_ATTRIBUTE4 => NULL,
396 X_ATTRIBUTE5 => NULL,
397 X_ATTRIBUTE6 => NULL,
398 X_ATTRIBUTE7 => NULL,
399 X_ATTRIBUTE8 => NULL,
400 X_ATTRIBUTE9 => NULL,
401 X_ATTRIBUTE10 => NULL,
402 X_ATTRIBUTE11 => NULL,
403 X_ATTRIBUTE12 => NULL,
404 X_ATTRIBUTE13 => NULL,
405 X_ATTRIBUTE14 => NULL,
406 X_ATTRIBUTE15 => NULL
407 );
408 ELSE
409 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
410 fnd_log.string
411 (
412 G_DEBUG_STMT,
413 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
414 'Updating record in AHL_SCHEDULE_MATERIALS '
415 );
416 fnd_log.string
417 (
418 G_DEBUG_STMT,
419 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
420 'unit_effectivity_id : ' || ue_mr_routes_rec.unit_effectivity_id
421 );
422 fnd_log.string
423 (
424 G_DEBUG_STMT,
425 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
426 'requested_date : ' || ue_mr_routes_rec.due_date
427 );
428 fnd_log.string
429 (
430 G_DEBUG_STMT,
431 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
432 'mr_route_id : '|| ue_mr_routes_rec.mr_route_id
433 );
434 fnd_log.string
435 (
436 G_DEBUG_STMT,
437 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
438 'inventory_item_id : ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id
439 );
440 fnd_log.string
441 (
442 G_DEBUG_STMT,
443 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
444 'quantity : ' || l_Route_Mtl_Req_Tbl(j).quantity
445 );
446 fnd_log.string
447 (
448 G_DEBUG_STMT,
449 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
450 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code
451 );
452 END IF;
453 AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW
454 (
455 X_SCHEDULED_MATERIAL_ID => l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID,
456 X_OBJECT_VERSION_NUMBER => l_Schedule_Mtl_Req_rec.OBJECT_VERSION_NUMBER,--Update Column
457 X_LAST_UPDATE_DATE => SYSDATE,
458 X_LAST_UPDATED_BY => fnd_global.user_id,
459 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
460 X_INVENTORY_ITEM_ID => l_Schedule_Mtl_Req_rec.INVENTORY_ITEM_ID,
461 X_SCHEDULE_DESIGNATOR => l_Schedule_Mtl_Req_rec.SCHEDULE_DESIGNATOR,
462 X_VISIT_ID => l_Schedule_Mtl_Req_rec.VISIT_ID,
463 X_VISIT_START_DATE => l_Schedule_Mtl_Req_rec.VISIT_START_DATE,
464 X_VISIT_TASK_ID => l_Schedule_Mtl_Req_rec.VISIT_TASK_ID,
465 X_ORGANIZATION_ID => l_Schedule_Mtl_Req_rec.ORGANIZATION_ID,
466 X_SCHEDULED_DATE => l_Schedule_Mtl_Req_rec.SCHEDULED_DATE,
467 X_REQUEST_ID => l_Schedule_Mtl_Req_rec.REQUEST_ID,
468 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
469 X_REQUESTED_DATE => trunc(ue_mr_routes_rec.due_date),--Update Column
470 X_SCHEDULED_QUANTITY => l_Schedule_Mtl_Req_rec.SCHEDULED_QUANTITY,
471 X_PROCESS_STATUS => l_Schedule_Mtl_Req_rec.PROCESS_STATUS,
472 X_ERROR_MESSAGE => l_Schedule_Mtl_Req_rec.ERROR_MESSAGE,
473 X_TRANSACTION_ID => l_Schedule_Mtl_Req_rec.TRANSACTION_ID,
474 X_UOM => l_Route_Mtl_Req_Tbl(j).uom_code,--Update Column
475 X_RT_OPER_MATERIAL_ID => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,--Update Column
476 X_OPERATION_CODE => l_Schedule_Mtl_Req_rec.OPERATION_CODE,
477 X_OPERATION_SEQUENCE => l_Schedule_Mtl_Req_rec.OPERATION_SEQUENCE,
478 X_ITEM_GROUP_ID => l_Route_Mtl_Req_Tbl(j).item_group_id,--Update Column
479 X_REQUESTED_QUANTITY => l_Route_Mtl_Req_Tbl(j).quantity,--Update Column
480 X_PROGRAM_ID => l_Schedule_Mtl_Req_rec.PROGRAM_ID,
481 X_PROGRAM_UPDATE_DATE => l_Schedule_Mtl_Req_rec.PROGRAM_UPDATE_DATE,
482 X_LAST_UPDATED_DATE => l_Schedule_Mtl_Req_rec.LAST_UPDATED_DATE,
483 X_WORKORDER_OPERATION_ID => l_Schedule_Mtl_Req_rec.WORKORDER_OPERATION_ID,
484 X_POSITION_PATH_ID => l_Route_Mtl_Req_Tbl(j).position_path_id,--Update Column
485 X_RELATIONSHIP_ID => l_Route_Mtl_Req_Tbl(j).relationship_id,--Update Column
486 X_UNIT_EFFECTIVITY_ID => l_Schedule_Mtl_Req_rec.UNIT_EFFECTIVITY_ID,
487 X_MR_ROUTE_ID => l_Schedule_Mtl_Req_rec.MR_ROUTE_ID,
488 X_MATERIAL_REQUEST_TYPE => l_Schedule_Mtl_Req_rec.MATERIAL_REQUEST_TYPE,
489 X_ATTRIBUTE_CATEGORY => l_Schedule_Mtl_Req_rec.ATTRIBUTE_CATEGORY,
490 X_ATTRIBUTE1 => l_Schedule_Mtl_Req_rec.ATTRIBUTE1,
491 X_ATTRIBUTE2 => l_Schedule_Mtl_Req_rec.ATTRIBUTE2,
492 X_ATTRIBUTE3 => l_Schedule_Mtl_Req_rec.ATTRIBUTE3,
493 X_ATTRIBUTE4 => l_Schedule_Mtl_Req_rec.ATTRIBUTE4,
494 X_ATTRIBUTE5 => l_Schedule_Mtl_Req_rec.ATTRIBUTE5,
495 X_ATTRIBUTE6 => l_Schedule_Mtl_Req_rec.ATTRIBUTE6,
496 X_ATTRIBUTE7 => l_Schedule_Mtl_Req_rec.ATTRIBUTE7,
497 X_ATTRIBUTE8 => l_Schedule_Mtl_Req_rec.ATTRIBUTE8,
498 X_ATTRIBUTE9 => l_Schedule_Mtl_Req_rec.ATTRIBUTE9,
499 X_ATTRIBUTE10 => l_Schedule_Mtl_Req_rec.ATTRIBUTE10,
500 X_ATTRIBUTE11 => l_Schedule_Mtl_Req_rec.ATTRIBUTE11,
501 X_ATTRIBUTE12 => l_Schedule_Mtl_Req_rec.ATTRIBUTE12,
502 X_ATTRIBUTE13 => l_Schedule_Mtl_Req_rec.ATTRIBUTE13,
503 X_ATTRIBUTE14 => l_Schedule_Mtl_Req_rec.ATTRIBUTE14,
504 X_ATTRIBUTE15 => l_Schedule_Mtl_Req_rec.ATTRIBUTE15
505 );
506 END IF;
507 CLOSE schedule_mtl_exists_csr;
508 END LOOP; -- Finished forecasting for single mr route in a unit effectivity
509 END IF;-- if route material requirement has a list to be forecasted
510 END IF;
511 END LOOP;-- For all unit effectivities
512 END LOOP;-- for all item instances
513 END IF;-- if there is a list of item instances
514
515 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
516 fnd_log.string
517 (
518 G_DEBUG_STMT,
519 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast',
520 'Have succesfully finished forecasting'
521 );
522 END IF;
523 -- Check Error Message stack.
524 x_msg_count := FND_MSG_PUB.count_msg;
525 IF x_msg_count > 0 THEN
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528
529 -- Standard check of p_commit
530 IF FND_API.TO_BOOLEAN(p_commit) THEN
531 COMMIT WORK;
532 END IF;
533
534 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
535 fnd_log.string
536 (
537 G_DEBUG_PROC,
538 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Mrl_Req_Forecast.end',
539 'At the end of PLSQL procedure'
540 );
541 END IF;
542
543 EXCEPTION
544 WHEN FND_API.G_EXC_ERROR THEN
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 ROLLBACK TO Process_Mrl_Req_Forecast;
547 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
548 p_data => x_msg_data,
549 p_encoded => fnd_api.g_false);
550
551
552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554 ROLLBACK TO Process_Mrl_Req_Forecast;
555 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
556 p_data => x_msg_data,
557 p_encoded => fnd_api.g_false);
558
559
560 WHEN OTHERS THEN
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 ROLLBACK TO Process_Mrl_Req_Forecast;
563 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
564 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
565 p_procedure_name => 'Process_Mrl_Req_Forecast',
566 p_error_text => SUBSTR(SQLERRM,1,500));
567 END IF;
568 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
569 p_data => x_msg_data,
570 p_encoded => fnd_api.g_false);
571
572 END Process_Mrl_Req_Forecast;
573
574 ---
575 -- Called from concurrent program. This will create/update the material
576 -- forecast stream.
577 PROCEDURE Build_Mat_Forecast_Stream (
578 errbuf OUT NOCOPY VARCHAR2,
579 retcode OUT NOCOPY NUMBER,
580 p_unit_config_hdr_id IN NUMBER,
581 p_item_instance_id IN NUMBER)
582 IS
583
584 -- To validate instance.
585 CURSOR csi_item_instances_csr(p_csi_item_instance_id IN NUMBER) IS
586 SELECT instance_number, active_end_date
587 FROM csi_item_instances
588 WHERE instance_id = p_csi_item_instance_id;
589
590 -- To validate unit.
591 CURSOR ahl_unit_config_headers_csr (p_uc_header_id IN NUMBER) IS
592 SELECT name, csi_item_instance_id, master_config_id, unit_config_status_code
593 FROM ahl_unit_config_headers
594 WHERE unit_config_header_id = p_uc_header_id;
595
596 -- To get unit config id.
597 CURSOR ahl_unit_config_header_csr (p_item_instance_id IN NUMBER) IS
598 SELECT name, master_config_id, unit_config_status_code
599 FROM ahl_unit_config_headers
600 WHERE csi_item_instance_id = p_item_instance_id
601 AND parent_uc_header_id IS NULL;
602
603 -- get mr_route and route_id for a mr_header.
604 CURSOR ahl_mr_route_csr(p_mr_header_id IN NUMBER) IS
605 SELECT rt.mr_route_id, rt.route_id, R.start_date_active, R.end_date_active
606 FROM AHL_MR_ROUTES rt, ahl_routes_b R
607 WHERE rt.route_id = r.route_id
608 AND rt.mr_header_id = p_mr_header_id;
609
610 -- get mr headers
611 CURSOR ahl_mr_header_csr(appln_usg_code IN VARCHAR2) IS
612 SELECT mr_header_id
613 FROM ahl_mr_headers_b mr
614 WHERE application_usg_code = appln_usg_code
615 AND mr_status_code = 'COMPLETE'
616 AND EXISTS (SELECT 1
617 FROM ahl_unit_effectivities_b
618 WHERE mr_header_id = mr.mr_header_id
619 AND (status_code IS NULL OR status_code = 'INIT-DUE')
620 );
621
622 -- get open UE IDs for the MR header.
623 CURSOR get_ue_csr (p_mr_header_id IN NUMBER) IS
624 SELECT unit_effectivity_id, due_date, csi_item_instance_id
625 FROM ahl_unit_effectivities_b UE
626 WHERE mr_header_id = p_mr_header_id
627 AND due_date IS NOT NULL
628 AND (status_code IS NULL OR status_code = 'INIT-DUE');
629 --ORDER BY csi_item_instance_id, due_date;
630
631 l_debug_module VARCHAR2(400) := 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Mat_Forecast_Stream';
632
633 l_csi_item_instance_id NUMBER;
634 l_name ahl_unit_config_headers.name%TYPE;
635 l_instance_number csi_item_instances.instance_number%TYPE;
636 l_active_end_date DATE;
637
638 l_config_node_tbl AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type;
639 l_master_config_id NUMBER;
640 l_config_status_code fnd_lookup_values_vl.lookup_code%TYPE;
641
642 l_msg_data VARCHAR2(2000);
643 l_msg_count NUMBER;
644 l_return_status VARCHAR2(1);
645
646 l_mr_route_id_tbl nbr_tbl_type;
647 l_route_id_tbl nbr_tbl_type;
648 l_mr_header_id_tbl nbr_tbl_type;
649 l_r_start_date_tbl date_tbl_type;
650 l_r_end_date_tbl date_tbl_type;
651
652 l_ue_id_tbl nbr_tbl_type;
653 l_ue_due_date_tbl date_tbl_type;
654 l_ue_ii_id_tbl nbr_tbl_type;
655
656 l_buffer_limit NUMBER := 1000;
657
658 BEGIN
659
660 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
661 fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Start Build_Mat_Forecast_Stream');
662 fnd_log.string ( G_DEBUG_PROC, l_debug_module,
663 'Input Parameter: p_unit_config_hdr_id:' || p_unit_config_hdr_id);
664 fnd_log.string ( G_DEBUG_PROC, l_debug_module,
665 'Input Parameter: p_item_instance_id:' || p_item_instance_id);
666 END IF;
667
668 -- initialize return status.
669 retcode := 0;
670
671 IF (G_IS_PM_INSTALLED <> 'N') THEN
672 -- only valid for application usg code - AHL
673 RETURN;
674 END IF;
675
676 IF (p_unit_config_hdr_id IS NOT NULL OR p_item_instance_id IS NOT NULL) THEN
677 IF (p_item_instance_id IS NOT NULL) THEN
678 -- validate item instance.
679 OPEN csi_item_instances_csr (p_item_instance_id);
680 FETCH csi_item_instances_csr INTO l_instance_number, l_active_end_date;
681 IF (csi_item_instances_csr%NOTFOUND) THEN
682 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INSTID_NOTFOUND');
683 FND_MESSAGE.Set_Token('INST_ID', p_item_instance_id);
684 FND_MSG_PUB.ADD;
685 CLOSE csi_item_instances_csr;
686 --dbms_output.put_line('Instance not found');
687 errbuf := FND_MSG_PUB.GET;
688 retcode := 2;
689 ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
690 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
691 FND_MESSAGE.Set_Token('NUMBER', l_instance_number);
692 FND_MSG_PUB.ADD;
693 --dbms_output.put_line('Instance has expired');
694 errbuf := FND_MSG_PUB.GET;
695 retcode := 2;
696 ELSE
697 l_csi_item_instance_id := p_item_instance_id;
698
699 -- If item instance is not top node, find the root item instance.
700 l_csi_item_instance_id := Get_RootInstanceID(l_csi_item_instance_id);
701
702 -- get master Config ID if root instance is a UC.
703 OPEN ahl_unit_config_header_csr(l_csi_item_instance_id);
704 FETCH ahl_unit_config_header_csr INTO l_name, l_master_config_id, l_config_status_code;
705 IF (ahl_unit_config_header_csr%FOUND) THEN
706 IF (l_config_status_code = 'DRAFT') THEN
707 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_STATUS_INVALID');
708 FND_MESSAGE.Set_Token('NAME',l_name);
709 FND_MSG_PUB.ADD;
710 errbuf := FND_MSG_PUB.GET;
711 retcode := 2;
712 END IF;
713 ELSE
714 l_master_config_id := NULL;
715 END IF; -- ahl_unit_config_header_csr%FOUND
716 CLOSE ahl_unit_config_header_csr;
717
718 END IF; -- csi_item_instances_csr%NOTFOUND
719 CLOSE csi_item_instances_csr;
720
721 END IF; -- p_item_instance_id
722
723 IF (p_unit_config_hdr_id IS NOT NULL) THEN
724 -- Validate unit config id.
725 OPEN ahl_unit_config_headers_csr (p_unit_config_hdr_id);
726 FETCH ahl_unit_config_headers_csr INTO l_name, l_csi_item_instance_id, l_master_config_id,
727 l_config_status_code ;
728 IF (ahl_unit_config_headers_csr%NOTFOUND) THEN
729 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UNIT_NOTFOUND');
730 FND_MESSAGE.Set_Token('UNIT_ID',p_unit_config_hdr_id);
731 FND_MSG_PUB.ADD;
732 errbuf := FND_MSG_PUB.GET;
733 retcode := 2;
734 --dbms_output.put_line('Unit not found');
735 ELSE
736 IF (l_config_status_code = 'DRAFT') THEN
737 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_STATUS_INVALID');
738 FND_MESSAGE.Set_Token('NAME',l_name);
739 FND_MSG_PUB.ADD;
740 CLOSE ahl_unit_config_headers_csr;
741
742 errbuf := FND_MSG_PUB.GET;
743 retcode := 2;
744 END IF;
745 END IF;
746 END IF; -- p_unit_config_hdr_id
747
748 -- Check error code.
749 IF (retcode = 2) THEN
750 RETURN;
751 END IF;
752
753 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
754 fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Master Config ID:' || l_master_config_id);
755 fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Root Instance ID:' || l_csi_item_instance_id);
756 END IF;
757
758 -- Build the Configuration tree structure.(G_config_node_tbl).
759 Build_Config_Tree(l_csi_item_instance_id, l_master_config_id, l_config_node_tbl);
760
761 SAVEPOINT Build_Mrl_Forecast_Stream_s;
762
763 --call for material requirement forecast
764 AHL_UMP_FORECAST_REQ_PVT.process_mrl_req_forecast
765 (
766 p_api_version => 1.0,
767 p_init_msg_list => FND_API.G_TRUE,
768 p_commit => FND_API.G_FALSE,
769 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
770 x_return_status => l_return_status,
771 x_msg_count => l_msg_count,
772 x_msg_data => l_msg_data,
773 p_applicable_instances_tbl => l_config_node_tbl
774 );
775
776 l_msg_count := FND_MSG_PUB.Count_Msg;
777 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
778 THEN
779 retcode := 2; -- error based only on return status
780 ELSIF (l_msg_count > 0 AND l_return_status = FND_API.G_RET_STS_SUCCESS)
781 THEN
782 retcode := 1; -- warning based on return status + msg count
783 END IF;
784
785 -- success.
786 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
787 COMMIT WORK;
788 END IF;
789
790 END IF; -- p_unit_config_hdr_id IS NOT NULL OR p_item_instance_id IS NOT NULL
791
792 IF (p_unit_config_hdr_id IS NULL AND p_item_instance_id IS NULL) THEN
793 -- process all UEs.
794 OPEN ahl_mr_header_csr(G_APP_MODULE);
795 LOOP
796 FETCH ahl_mr_header_csr BULK COLLECT INTO l_mr_header_id_tbl LIMIT l_buffer_limit;
797 EXIT WHEN (l_mr_header_id_tbl.count = 0);
798
799 FOR j IN l_mr_header_id_tbl.FIRST..l_mr_header_id_tbl.LAST LOOP
800
801 -- set savepoint for MR and commit after processing MR.
802 SAVEPOINT Build_Mrl_Forecast_Stream_s;
803
804 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
805 fnd_log.string ( G_DEBUG_STMT, l_debug_module, 'MR Header ID:' || l_mr_header_id_tbl(j));
806 END IF;
807
808 OPEN ahl_mr_route_csr(l_mr_header_id_tbl(j));
809 FETCH ahl_mr_route_csr BULK COLLECT INTO l_mr_route_id_tbl, l_route_id_tbl,
810 l_r_start_date_tbl, l_r_end_date_tbl;
811 CLOSE ahl_mr_route_csr;
812
813 -- get UE IDs and due dates for the MR.
814 OPEN get_ue_csr(l_mr_header_id_tbl(j));
815 LOOP
816 FETCH get_ue_csr BULK COLLECT INTO l_ue_id_tbl, l_ue_due_date_tbl, l_ue_ii_id_tbl LIMIT l_buffer_limit;
817 EXIT WHEN (l_ue_id_tbl.COUNT = 0);
818 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST LOOP
819 IF (l_mr_route_id_tbl.COUNT > 0) THEN
820 FOR k IN l_mr_route_id_tbl.FIRST..l_mr_route_id_tbl.LAST LOOP
821 -- validate and update/insert into schedule materials for
822 -- every combination of UE and mr_route_id.
823 AHL_UMP_FORECAST_REQ_PVT.Process_Material_Req
824 (p_unit_effectivity_id => l_ue_id_tbl(i),
825 p_csi_item_instance_id => l_ue_ii_id_tbl(i),
826 p_due_date => l_ue_due_date_tbl(i),
827 p_mr_route_id => l_mr_route_id_tbl(k),
828 p_route_id => l_route_id_tbl(k),
829 p_r_start_date_active => l_r_start_date_tbl(k),
830 p_r_end_date_active => l_r_end_date_tbl(k)
831 );
832 END LOOP; -- l_mr_route_id_tbl.FIRST
833 END IF; -- l_mr_route_id_tbl.COUNT
834 END LOOP; -- l_ue_id_tbl.FIRST
835 END LOOP;
836 CLOSE get_ue_csr;
837
838 COMMIT WORK; -- commit after processing MR.
839
840 END LOOP; -- l_mr_header_id_tbl.FIRST
841 END LOOP; -- ahl_mr_header_csr
842 CLOSE ahl_mr_header_csr;
843
844 END IF; -- p_unit_config_hdr_id IS NULL AND p_item_instance_id IS NULL
845
846
847 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
848 fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Out Parameter: retcode:' || retcode);
849 fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'Out Parameter: errbuf:' || errbuf);
850 fnd_log.string ( G_DEBUG_PROC, l_debug_module, 'End Build_Mat_Forecast_Stream');
851 END IF;
852
853
854 EXCEPTION
855 WHEN FND_API.G_EXC_ERROR THEN
856 ROLLBACK TO Build_Mrl_Forecast_Stream_s;
857 retcode := 2;
858 log_error_messages;
859
860 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
861 ROLLBACK TO Build_Mrl_Forecast_Stream_s;
862 retcode := 2;
863 log_error_messages;
864
865
866 WHEN OTHERS THEN
867 ROLLBACK TO Build_Mrl_Forecast_Stream_s;
868 retcode := 2;
869 log_error_messages;
870 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
871 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
872 p_procedure_name => 'Build_Mrl_Forecast_Stream',
873 p_error_text => SUBSTR(SQLERRM,1,500));
874 END IF;
875
876 END Build_Mat_Forecast_Stream;
877
878 -----------------------------------------------------------------------
879 -- To get the root item instance for the input item instance if exists.
880
881 FUNCTION Get_RootInstanceID(p_csi_item_instance_id IN NUMBER)
882 RETURN NUMBER
883 IS
884
885 CURSOR csi_root_instance_csr (p_instance_id IN NUMBER) IS
886 SELECT root.object_id
887 FROM csi_ii_relationships root
888 WHERE NOT EXISTS (SELECT 'x'
889 FROM csi_ii_relationships
890 WHERE subject_id = root.object_id
891 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
892 )
893 START WITH root.subject_id = p_instance_id
894 AND root.relationship_type_code = 'COMPONENT-OF'
895 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
896 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
897 CONNECT BY PRIOR root.object_id = root.subject_id
898 AND root.relationship_type_code = 'COMPONENT-OF'
899 AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
900 AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
901
902 l_csi_instance_id NUMBER;
903
904 BEGIN
905
906 -- get root instance given an item instance_id.
907 OPEN csi_root_instance_csr (p_csi_item_instance_id);
908 FETCH csi_root_instance_csr INTO l_csi_instance_id;
909 IF (csi_root_instance_csr%NOTFOUND) THEN
910 -- input id is root instance.
911 l_csi_instance_id := p_csi_item_instance_id;
912 END IF;
913 CLOSE csi_root_instance_csr;
914 --dbms_output.put_line ('root instance' || l_csi_instance_id);
915
916 RETURN l_csi_instance_id;
917
918 END Get_RootInstanceID;
919
920 -------------------------------------------------------------
921 -- Build the item instance tree containing root nodes and its components.
922 PROCEDURE Build_Config_Tree(p_csi_root_instance_id IN NUMBER,
923 p_master_config_id IN NUMBER,
924 x_config_node_tbl OUT NOCOPY AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type)
925
926 IS
927
928 CURSOR csi_config_tree_csr ( p_csi_root_instance_id IN NUMBER) IS
929 SELECT subject_id , object_id, position_reference
930 FROM csi_ii_relationships
931 START WITH object_id = p_csi_root_instance_id
932 AND relationship_type_code = 'COMPONENT-OF'
933 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
934 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
935 CONNECT BY PRIOR subject_id = object_id
936 AND relationship_type_code = 'COMPONENT-OF'
937 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
938 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
939 ORDER BY level;
940
941 i NUMBER;
942 l_config_node_tbl AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type := x_config_node_tbl;
943
944 -- added for perf fix for bug# 6893404.
945 l_buffer_limit number := 1000;
946
947 l_subj_id_tbl nbr_tbl_type;
948 l_obj_id_tbl nbr_tbl_type;
949 l_posn_ref_tbl vchar_tbl_type;
950
951 BEGIN
952
953 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
954 fnd_log.string ( G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Config_Tree',
955 'Start Build_Config_Tree');
956 END IF;
957
958 -- For top node.
959 l_config_node_tbl(1).csi_item_instance_id := p_csi_root_instance_id;
960
961 -- For position reference.
962 IF (p_master_config_id IS NOT NULL) THEN
963 l_config_node_tbl(1).position_reference := to_char(p_master_config_id);
964 END IF;
965
966 i := 1;
967
968 -- add child nodes.
969 -- added for perf fix for bug# 6893404.
970 OPEN csi_config_tree_csr(p_csi_root_instance_id);
971 LOOP
972 FETCH csi_config_tree_csr BULK COLLECT INTO l_subj_id_tbl, l_obj_id_tbl, l_posn_ref_tbl
973 LIMIT l_buffer_limit;
974
975 EXIT WHEN (l_subj_id_tbl.count = 0);
976
977 FOR j IN l_subj_id_tbl.FIRST..l_subj_id_tbl.LAST LOOP
978
979 -- Loop through to get all components of the configuration.
980 i := i + 1;
981
982 l_config_node_tbl(i).csi_item_instance_id := l_subj_id_tbl(j);
983 l_config_node_tbl(i).object_id := l_obj_id_tbl(j);
984 l_config_node_tbl(i).position_reference := l_posn_ref_tbl(j);
985
986 END LOOP; -- l_subj_id_tbl.FIRST
987
988 -- reset tables and get the next batch of nodes.
989 l_subj_id_tbl.DELETE;
990 l_obj_id_tbl.DELETE;
991 l_posn_ref_tbl.DELETE;
992
993 END LOOP; -- FETCH csi_config_tree_csr
994 CLOSE csi_config_tree_csr;
995
996 X_CONFIG_NODE_TBL := l_config_node_tbl;
997
998 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
999 fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Config_Tree',
1000 'Count on config' || x_config_node_tbl.COUNT);
1001
1002 fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Build_Config_Tree',
1003 'End Build_Config_Tree');
1004 END IF;
1005
1006 END Build_Config_Tree;
1007 ---
1008 PROCEDURE Process_Material_Req (p_unit_effectivity_id IN NUMBER,
1009 p_csi_item_instance_id IN NUMBER,
1010 p_due_date IN DATE,
1011 p_mr_route_id IN NUMBER,
1012 p_route_id IN NUMBER,
1013 p_r_start_date_active IN DATE,
1014 p_r_end_date_active IN DATE)
1015 IS
1016
1017 -- Find out the schedule material records for expired routes
1018 CURSOR del_exp_route_schedule_mtl_csr(p_unit_effectivity_id IN NUMBER, p_mr_route_id IN NUMBER) IS
1019 SELECT scheduled_material_id FROM AHL_SCHEDULE_MATERIALS SM
1020 WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
1021 AND SM.mr_route_id = p_mr_route_id
1022 AND SM.unit_effectivity_id IS NOT NULL
1023 AND SM.unit_effectivity_id = p_unit_effectivity_id;
1024
1025 -- Validates whether a forecast record with unique key combination exisits
1026 -- in AHL_SCHEDULE_MATERIALS table
1027 -- output record used for DML update operation
1028 CURSOR schedule_mtl_exists_csr(p_unit_effectivity_id IN NUMBER,
1029 p_mr_route_id IN NUMBER,
1030 p_inventory_item_id IN NUMBER,
1031 p_rt_oper_material_id IN NUMBER) IS
1032 SELECT * FROM AHL_SCHEDULE_MATERIALS SM
1033 WHERE SM.material_request_type = G_REQ_TYPE_FORECAST
1034 AND NVL(SM.rt_oper_material_id,-1) = NVL(p_rt_oper_material_id,-1)
1035 AND SM.inventory_item_id = p_inventory_item_id
1036 AND SM.mr_route_id = p_mr_route_id
1037 AND SM.unit_effectivity_id = p_unit_effectivity_id
1038 FOR UPDATE OF REQUESTED_DATE NOWAIT;
1039
1040 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.Process_Material_Req';
1041 l_Schedule_Mtl_Req_rec AHL_SCHEDULE_MATERIALS%ROWTYPE;
1042 l_requirement_date DATE;
1043 l_Route_Mtl_Req_Tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1044
1045 l_msg_data VARCHAR2(2000);
1046 l_msg_count NUMBER;
1047 l_return_status VARCHAR2(1);
1048
1049 BEGIN
1050
1051 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1052 fnd_log.string (G_DEBUG_STMT , l_debug_module,
1053 'p_route_id:p_mr_route_id:p_item_instance_id:p_requirement_date:' || p_route_id || ':' || p_mr_route_id || ':' ||p_csi_item_instance_id || ':' || p_due_date);
1054
1055 -- log into concurrent log file.
1056 fnd_file.put_line(fnd_file.log,l_debug_module || ':p_route_id :p_mr_route_id : p_item_instance_id : p_requirement_date' || p_route_id || ':' || p_mr_route_id || ':' ||p_csi_item_instance_id || ':' || p_due_date);
1057
1058 END IF;
1059
1060
1061
1062 IF NOT(TRUNC(NVL(p_r_start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
1063 AND TRUNC(NVL(p_r_end_date_active,SYSDATE+1))>TRUNC(SYSDATE)) THEN
1064 -- route is expired so delete forecast
1065 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1066 fnd_log.string (G_DEBUG_STMT ,l_debug_module,
1067 'p_route_id :p_mr_route_id : ' || p_route_id || ':' || p_mr_route_id);
1068 END IF;
1069
1070 FOR del_exp_route_rec IN del_exp_route_schedule_mtl_csr(p_unit_effectivity_id,p_mr_route_id) LOOP
1071 AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => del_exp_route_rec.scheduled_material_id);
1072 END LOOP;
1073
1074 ELSE
1075 -- route is valid and proceed to forecast
1076 IF(TRUNC(p_due_date) < TRUNC(SYSDATE)) THEN
1077 l_requirement_date := SYSDATE;
1078 ELSE
1079 l_requirement_date := p_due_date;
1080 END IF;
1081
1082 -- IF (NVL(G_previous_route_id,-1) <> p_route_id OR
1083 -- TRUNC(NVL(G_previous_req_date,l_requirement_date - 1)) <> TRUNC(l_requirement_date))THEN
1084
1085 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
1086 (
1087 p_api_version => 1.0,
1088 p_init_msg_list => FND_API.G_FALSE,
1089 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1090 x_return_status => l_return_status,
1091 x_msg_count => l_msg_count,
1092 x_msg_data => l_msg_data,
1093 p_route_id => p_route_id,
1094 p_mr_route_id => p_mr_route_id,
1095 p_item_instance_id => p_csi_item_instance_id,
1096 p_requirement_date => l_requirement_date,
1097 p_request_type => G_REQ_TYPE_FORECAST,
1098 x_route_mtl_req_tbl => l_Route_Mtl_Req_Tbl
1099 );
1100
1101 --G_previous_route_id := p_route_id;
1102 --G_previous_req_date := l_requirement_date;
1103
1104 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1105 IF (fnd_log.level_error >= G_DEBUG_LEVEL) THEN
1106 fnd_log.string(fnd_log.level_error, l_debug_module,
1107 'AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req Threw error');
1108 END IF;
1109 RAISE FND_API.G_EXC_ERROR;
1110 END IF;
1111
1112 -- END IF; -- (NVL(G_previous_route_id,-1)
1113
1114 IF (l_Route_Mtl_Req_Tbl IS NOT NULL AND l_Route_Mtl_Req_Tbl.COUNT > 0) THEN
1115 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1116 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1117 'After call AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req API : l_Route_Mtl_Req_Tbl.COUNT : ' ||
1118 l_Route_Mtl_Req_Tbl.COUNT);
1119 END IF;
1120
1121 FOR j IN l_Route_Mtl_Req_Tbl.FIRST..l_Route_Mtl_Req_Tbl.LAST LOOP
1122 OPEN schedule_mtl_exists_csr(p_unit_effectivity_id,
1123 p_mr_route_id,
1124 l_Route_Mtl_Req_Tbl(j).inventory_item_id,
1125 l_Route_Mtl_Req_Tbl(j).rt_oper_material_id);
1126 FETCH schedule_mtl_exists_csr INTO l_Schedule_Mtl_Req_rec;
1127 IF(schedule_mtl_exists_csr%NOTFOUND)THEN
1128 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1129 fnd_log.string (G_DEBUG_STMT, l_debug_module,
1130 'Inserting record in AHL_SCHEDULE_MATERIALS ');
1131 fnd_log.string (G_DEBUG_STMT,l_debug_module,
1132 'unit_effectivity_id : ' || p_unit_effectivity_id);
1133 fnd_log.string (G_DEBUG_STMT,l_debug_module,
1134 'requested_date : ' || p_due_date);
1135 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1136 'mr_route_id : '|| p_mr_route_id);
1137 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1138 'inventory_item_id : ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id);
1139 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1140 'quantity : ' || l_Route_Mtl_Req_Tbl(j).quantity);
1141 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1142 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code);
1143 END IF;
1144 AHL_SCHEDULE_MATERIALS_PKG.INSERT_ROW
1145 (
1146 X_SCHEDULED_MATERIAL_ID => NULL,
1147 X_OBJECT_VERSION_NUMBER => 1,
1148 X_LAST_UPDATE_DATE => SYSDATE,
1149 X_LAST_UPDATED_BY => fnd_global.user_id,
1150 X_CREATION_DATE => SYSDATE,
1151 X_CREATED_BY => fnd_global.user_id,
1152 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1153 X_INVENTORY_ITEM_ID => l_Route_Mtl_Req_Tbl(j).inventory_item_id,
1154 X_SCHEDULE_DESIGNATOR => NULL,
1155 X_VISIT_ID => NULL,
1156 X_VISIT_START_DATE => NULL,
1157 X_VISIT_TASK_ID => NULL,
1158 X_ORGANIZATION_ID => NULL,
1159 X_SCHEDULED_DATE => NULL,
1160 X_REQUEST_ID => NULL,
1161 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1162 X_REQUESTED_DATE => trunc(p_due_date),
1163 X_SCHEDULED_QUANTITY => NULL,
1164 X_PROCESS_STATUS => NULL,
1165 X_ERROR_MESSAGE => NULL,
1166 X_TRANSACTION_ID => NULL,
1167 X_UOM => l_Route_Mtl_Req_Tbl(j).uom_code,
1168 X_RT_OPER_MATERIAL_ID => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,
1169 X_OPERATION_CODE => NULL,
1170 X_OPERATION_SEQUENCE => NULL,
1171 X_ITEM_GROUP_ID => l_Route_Mtl_Req_Tbl(j).item_group_id,
1172 X_REQUESTED_QUANTITY => l_Route_Mtl_Req_Tbl(j).quantity,
1173 X_PROGRAM_ID => NULL,
1174 X_PROGRAM_UPDATE_DATE => NULL,
1175 X_LAST_UPDATED_DATE => NULL,
1176 X_WORKORDER_OPERATION_ID => NULL,
1177 X_POSITION_PATH_ID => l_Route_Mtl_Req_Tbl(j).position_path_id,
1178 X_RELATIONSHIP_ID => l_Route_Mtl_Req_Tbl(j).relationship_id,
1179 X_UNIT_EFFECTIVITY_ID => p_unit_effectivity_id,
1180 X_MR_ROUTE_ID => p_mr_route_id,
1181 X_MATERIAL_REQUEST_TYPE => G_REQ_TYPE_FORECAST,
1182 X_ATTRIBUTE_CATEGORY => NULL,
1183 X_ATTRIBUTE1 => NULL,
1184 X_ATTRIBUTE2 => NULL,
1185 X_ATTRIBUTE3 => NULL,
1186 X_ATTRIBUTE4 => NULL,
1187 X_ATTRIBUTE5 => NULL,
1188 X_ATTRIBUTE6 => NULL,
1189 X_ATTRIBUTE7 => NULL,
1190 X_ATTRIBUTE8 => NULL,
1191 X_ATTRIBUTE9 => NULL,
1192 X_ATTRIBUTE10 => NULL,
1193 X_ATTRIBUTE11 => NULL,
1194 X_ATTRIBUTE12 => NULL,
1195 X_ATTRIBUTE13 => NULL,
1196 X_ATTRIBUTE14 => NULL,
1197 X_ATTRIBUTE15 => NULL
1198 );
1199 ELSE -- schedule_mtl_exists
1200 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1201 fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1202 'Updating record in AHL_SCHEDULE_MATERIALS ');
1203 fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1204 'unit_effectivity_id : ' || p_unit_effectivity_id);
1205 fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1206 'requested_date : ' || p_due_date);
1207 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1208 'mr_route_id : '|| p_mr_route_id);
1209 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1210 'inventory_item_id : ' || l_Route_Mtl_Req_Tbl(j).inventory_item_id);
1211 fnd_log.string ( G_DEBUG_STMT,l_debug_module,
1212 'quantity : ' || l_Route_Mtl_Req_Tbl(j).quantity);
1213 fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1214 'uom : ' || l_Route_Mtl_Req_Tbl(j).uom_code);
1215 fnd_log.string ( G_DEBUG_STMT, l_debug_module,
1216 'SCHEDULED_MATERIAL_ID:' || l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID);
1217 END IF;
1218 AHL_SCHEDULE_MATERIALS_PKG.UPDATE_ROW
1219 (
1220 X_SCHEDULED_MATERIAL_ID => l_Schedule_Mtl_Req_rec.SCHEDULED_MATERIAL_ID,
1221 X_OBJECT_VERSION_NUMBER => l_Schedule_Mtl_Req_rec.OBJECT_VERSION_NUMBER,--Update Column
1222 X_LAST_UPDATE_DATE => SYSDATE,
1223 X_LAST_UPDATED_BY => fnd_global.user_id,
1224 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1225 X_INVENTORY_ITEM_ID => l_Schedule_Mtl_Req_rec.INVENTORY_ITEM_ID,
1226 X_SCHEDULE_DESIGNATOR => l_Schedule_Mtl_Req_rec.SCHEDULE_DESIGNATOR,
1227 X_VISIT_ID => l_Schedule_Mtl_Req_rec.VISIT_ID,
1228 X_VISIT_START_DATE => l_Schedule_Mtl_Req_rec.VISIT_START_DATE,
1229 X_VISIT_TASK_ID => l_Schedule_Mtl_Req_rec.VISIT_TASK_ID,
1230 X_ORGANIZATION_ID => l_Schedule_Mtl_Req_rec.ORGANIZATION_ID,
1231 X_SCHEDULED_DATE => l_Schedule_Mtl_Req_rec.SCHEDULED_DATE,
1232 X_REQUEST_ID => l_Schedule_Mtl_Req_rec.REQUEST_ID,
1233 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1234 X_REQUESTED_DATE => trunc(p_due_date),--Update Column
1235 X_SCHEDULED_QUANTITY => l_Schedule_Mtl_Req_rec.SCHEDULED_QUANTITY,
1236 X_PROCESS_STATUS => l_Schedule_Mtl_Req_rec.PROCESS_STATUS,
1237 X_ERROR_MESSAGE => l_Schedule_Mtl_Req_rec.ERROR_MESSAGE,
1238 X_TRANSACTION_ID => l_Schedule_Mtl_Req_rec.TRANSACTION_ID,
1239 X_UOM => l_Route_Mtl_Req_Tbl(j).uom_code,--Update Column
1240 X_RT_OPER_MATERIAL_ID => l_Route_Mtl_Req_Tbl(j).rt_oper_material_id,--Update Column
1241 X_OPERATION_CODE => l_Schedule_Mtl_Req_rec.OPERATION_CODE,
1242 X_OPERATION_SEQUENCE => l_Schedule_Mtl_Req_rec.OPERATION_SEQUENCE,
1243 X_ITEM_GROUP_ID => l_Route_Mtl_Req_Tbl(j).item_group_id,--Update Column
1244 X_REQUESTED_QUANTITY => l_Route_Mtl_Req_Tbl(j).quantity,--Update Column
1245 X_PROGRAM_ID => l_Schedule_Mtl_Req_rec.PROGRAM_ID,
1246 X_PROGRAM_UPDATE_DATE => l_Schedule_Mtl_Req_rec.PROGRAM_UPDATE_DATE,
1247 X_LAST_UPDATED_DATE => l_Schedule_Mtl_Req_rec.LAST_UPDATED_DATE,
1248 X_WORKORDER_OPERATION_ID => l_Schedule_Mtl_Req_rec.WORKORDER_OPERATION_ID,
1249 X_POSITION_PATH_ID => l_Route_Mtl_Req_Tbl(j).position_path_id,--Update Column
1250 X_RELATIONSHIP_ID => l_Route_Mtl_Req_Tbl(j).relationship_id,--Update Column
1251 X_UNIT_EFFECTIVITY_ID => l_Schedule_Mtl_Req_rec.UNIT_EFFECTIVITY_ID,
1252 X_MR_ROUTE_ID => l_Schedule_Mtl_Req_rec.MR_ROUTE_ID,
1253 X_MATERIAL_REQUEST_TYPE => l_Schedule_Mtl_Req_rec.MATERIAL_REQUEST_TYPE,
1254 X_ATTRIBUTE_CATEGORY => l_Schedule_Mtl_Req_rec.ATTRIBUTE_CATEGORY,
1255 X_ATTRIBUTE1 => l_Schedule_Mtl_Req_rec.ATTRIBUTE1,
1256 X_ATTRIBUTE2 => l_Schedule_Mtl_Req_rec.ATTRIBUTE2,
1257 X_ATTRIBUTE3 => l_Schedule_Mtl_Req_rec.ATTRIBUTE3,
1258 X_ATTRIBUTE4 => l_Schedule_Mtl_Req_rec.ATTRIBUTE4,
1259 X_ATTRIBUTE5 => l_Schedule_Mtl_Req_rec.ATTRIBUTE5,
1260 X_ATTRIBUTE6 => l_Schedule_Mtl_Req_rec.ATTRIBUTE6,
1261 X_ATTRIBUTE7 => l_Schedule_Mtl_Req_rec.ATTRIBUTE7,
1262 X_ATTRIBUTE8 => l_Schedule_Mtl_Req_rec.ATTRIBUTE8,
1263 X_ATTRIBUTE9 => l_Schedule_Mtl_Req_rec.ATTRIBUTE9,
1264 X_ATTRIBUTE10 => l_Schedule_Mtl_Req_rec.ATTRIBUTE10,
1265 X_ATTRIBUTE11 => l_Schedule_Mtl_Req_rec.ATTRIBUTE11,
1266 X_ATTRIBUTE12 => l_Schedule_Mtl_Req_rec.ATTRIBUTE12,
1267 X_ATTRIBUTE13 => l_Schedule_Mtl_Req_rec.ATTRIBUTE13,
1268 X_ATTRIBUTE14 => l_Schedule_Mtl_Req_rec.ATTRIBUTE14,
1269 X_ATTRIBUTE15 => l_Schedule_Mtl_Req_rec.ATTRIBUTE15
1270 );
1271 END IF; -- schedule_mtl_exists_csr%NOTFOUND
1272 CLOSE schedule_mtl_exists_csr;
1273 END LOOP; -- l_Route_Mtl_Req_Tbl(j)
1274 END IF; -- l_Route_Mtl_Req_Tbl.COUNT > 0
1275 END IF; -- NOT(TRUNC(NVL(p_r_start_date_active
1276 END Process_Material_Req;
1277
1278 ---------------------------------------------------------------------------
1279 -- To log error messages into a log file if called from concurrent process.
1280
1281 PROCEDURE log_error_messages IS
1282
1283 l_msg_count NUMBER;
1284 l_msg_index_out NUMBER;
1285 l_msg_data VARCHAR2(2000);
1286
1287 BEGIN
1288
1289 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1290 fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.log_error_messages',
1291 'Start log error messages');
1292 END IF;
1293
1294 -- Standard call to get message count.
1295 l_msg_count := FND_MSG_PUB.Count_Msg;
1296
1297 FOR i IN 1..l_msg_count LOOP
1298 FND_MSG_PUB.get (
1299 p_msg_index => i,
1300 p_encoded => FND_API.G_FALSE,
1301 p_data => l_msg_data,
1302 p_msg_index_out => l_msg_index_out );
1303
1304 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_msg_data);
1305 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)THEN
1306 fnd_log.string (G_DEBUG_STMT, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.log_error_messages',
1307 'Err message-'||l_msg_index_out||':' || substr(l_msg_data,1,240));
1308 END IF;
1309
1310 END LOOP;
1311
1312 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
1313 fnd_log.string (G_DEBUG_PROC, 'ahl.plsql.AHL_UMP_FORECAST_REQ_PVT.log_error_messages',
1314 'End log error messages');
1315 END IF;
1316
1317 END log_error_messages;
1318
1319 END AHL_UMP_FORECAST_REQ_PVT;