1 PACKAGE BODY AHL_MATERIALS_GRP AS
2 /* $Header: AHLGMATB.pls 120.2.12020000.2 2012/12/06 22:26:31 sareepar ship $ */
3 /*
4 * This Group package body provides the apis which will be invoked from VCP for
5 * 1. Querying alternate items for a Work order's primary item requirement.
6 * 2. Updating CMRO material requirements with alternate items or dates as
7 * recommended by VCP/PS for a Work Order.
8 */
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_MATERIALS_GRP';
11 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
12 G_LOG_PREFIX CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.';
13
14
15 ----------------------------------------
16 -- Start of Comments --
17 -- Procedure name : GET_WO_ALTERNATES
18 -- Type : Public
19 -- Function : Gets all the material requirements for a Work order and its alternates
20 -- Parameters :
21 --
22 -- Standard IN Parameters :
23 -- p_api_version IN NUMBER Required
24 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
25 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
26 --
27 -- Standard OUT Parameters :
28 -- x_return_status OUT VARCHAR2 Required
29 -- x_msg_count OUT NUMBER Required
30 -- x_msg_data OUT VARCHAR2 Required
31 --
32 -- GET_WO_ALTERNATES Parameters:
33 -- p_wip_entity_id IN NUMBER Required
34 -- x_alt_item_tbl OUT Alt_Item_Tbl_Type Represents the table of items and alternates.
35 -- If a given primary Item has n applicable alternates, there will be n records having
36 -- the same PRIMARY_ITEM_ID+PRIMARY_ITEM_QUANTITY+PRIMARY_ITEM_UOM_CODE. These records will
37 -- differ in the values of ALTERNATE_ITEM_ID
38 --
39 -- Version :
40 -- Initial Version 1.0
41 --
42 -- End of Comments.
43
44 PROCEDURE GET_WO_ALTERNATES
45 (
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_wip_entity_id IN NUMBER,
53 x_alt_item_tbl OUT NOCOPY Alt_Item_Tbl_Type) IS
54
55
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_api_name CONSTANT VARCHAR2(30) := 'get_wo_alternates';
58 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || l_api_name;
59
60 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
61 l_prev_err_count NUMBER;
62 l_visit_task_id NUMBER;
63 l_alt_item_id NUMBER;
64 l_priority NUMBER;
65 i NUMBER;
66 j NUMBER;
67 l_tbl_index NUMBER := 1;
68 l_alt_items AHL_LTP_MTL_REQ_PVT.Alt_Items_Tbl_Type;
69
70 Cursor get_visit_task_id_csr(c_wip_entity_id IN NUMBER) IS
71 SELECT WO.VISIT_TASK_ID
72 FROM AHL_WORKORDERS WO
73 WHERE WIP_ENTITY_ID = c_wip_entity_id;
74
75 Cursor get_task_mtrls_csr(c_visit_task_id IN NUMBER) IS
76 SELECT *
77 FROM AHL_SCHEDULE_MATERIALS
78 WHERE VISIT_TASK_ID = c_visit_task_id
79 -- Fix for bug 13099886: Look at ACTIVE requirements only
80 AND STATUS = 'ACTIVE';
81
82 l_material_rec get_task_mtrls_csr%ROWTYPE;
83
84 BEGIN
85
86 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
87 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure. p_wip_entity_id = ' || p_wip_entity_id);
88 END IF;
89
90 -- Standard call to check for call compatibility
91 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME )
92 THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END IF;
95
96 -- Initialize message list if p_init_msg_list is set to TRUE
97 IF FND_API.To_Boolean(p_init_msg_list)
98 THEN
99 FND_MSG_PUB.Initialize;
100 END IF;
101
102 l_prev_err_count := NVL(FND_MSG_PUB.count_msg, 0);
103
104 -- Initialize API return status to success
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106
107 -- Begin Processing
108 -- From AHL_WORKORDERS, get the visit_task_id for the wip_entity_id
109 OPEN get_visit_task_id_csr(p_wip_entity_id);
110 FETCH get_visit_task_id_csr INTO l_visit_task_id;
111 IF l_visit_task_id IS NOT NULL THEN
112 -- Get all rows from ASM for the given visit task and process them one by one
113 OPEN get_task_mtrls_csr(l_visit_task_id);
114 i := 1;
115 LOOP
116 FETCH get_task_mtrls_csr INTO l_material_rec;
117 EXIT WHEN get_task_mtrls_csr%NOTFOUND;
118 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement index i = ' || i || ', scheduled_material_id = ' || l_material_rec.scheduled_material_id);
120 END IF;
121 -- If the record has item_group_id or (position_key and mc_header_id) and inventory_item_id, get all other applicable alternate items
122 -- taking into consideration the item group or the SB effectivity (for position based items)
123 IF ((l_material_rec.item_group_id IS NOT NULL OR (l_material_rec.mc_header_id IS NOT NULL AND l_material_rec.position_key IS NOT NULL))
124 AND l_material_rec.inventory_item_id IS NOT NULL) THEN
125 -- Get the alternates by calling the LTP procedure
126 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
127 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items with p_schedule_material_id as ' || l_material_rec.scheduled_material_id);
128 END IF;
129 AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
130 p_schedule_material_id => l_material_rec.scheduled_material_id,
131 x_alt_items => l_alt_items,
132 x_return_status => x_return_status,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data);
135 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned ' || x_return_status);
137 END IF;
138 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
139 IF (l_alt_items IS NULL OR l_alt_items.COUNT = 0) THEN
140 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No alternate items available for this requirement.');
142 END IF;
143 ELSE
144 -- This requirement has alternate items available
145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'This requirement has ' || l_alt_items.COUNT || ' alternate items.');
147 END IF;
148 FOR j IN l_alt_items.FIRST..l_alt_items.LAST LOOP
149 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Index j = ' || j || ', l_alt_items(j) = ' || l_alt_items(j));
151 END IF;
152 x_alt_item_tbl(l_tbl_index).PRIMARY_ITEM_ID := l_material_rec.inventory_item_id;
153 x_alt_item_tbl(l_tbl_index).PRIMARY_ITEM_QUANTITY := l_material_rec.REQUESTED_QUANTITY;
154 x_alt_item_tbl(l_tbl_index).PRIMARY_ITEM_UOM_CODE := l_material_rec.UOM;
155 x_alt_item_tbl(l_tbl_index).ALT_ITEM_QUANTITY := l_material_rec.REQUESTED_QUANTITY;
156 x_alt_item_tbl(l_tbl_index).ALT_ITEM_UOM_CODE := l_material_rec.UOM;
157 x_alt_item_tbl(l_tbl_index).REQUESTED_DATE := l_material_rec.REQUESTED_DATE;
158 x_alt_item_tbl(l_tbl_index).OPERATION_SEQUENCE := l_material_rec.OPERATION_SEQUENCE;
159 x_alt_item_tbl(l_tbl_index).SCHEDULED_MATERIAL_ID := l_material_rec.SCHEDULED_MATERIAL_ID;
160 -- Get the alternate item returned by AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items
161 x_alt_item_tbl(l_tbl_index).ALTERNATE_ITEM_ID := l_alt_items(j);
162 -- Use (index + 1) as the Priority since AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returns altenate items ordered by priority
163 x_alt_item_tbl(l_tbl_index).PRIORITY := j + 1;
164 -- Increment index
165 l_tbl_index := l_tbl_index + 1;
166 END LOOP; -- For all alternates returned by AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items
167 END IF; -- If there are alternate items available for this requirement
168 END IF; -- If the material requirement can have alternates
169 i := i + 1;
170 END LOOP; -- All materials for the task
171 CLOSE get_task_mtrls_csr;
172 END IF; --Visit Task Id is not null
173 CLOSE get_visit_task_id_csr;
174
175 -- Check Error Message stack.
176 x_msg_count := FND_MSG_PUB.count_msg;
177
178 IF x_msg_count - l_prev_err_count > 0 THEN
179 RAISE FND_API.G_EXC_ERROR;
180 END IF;
181
182 -- Standard call to get message count and if count is 1, get message info
183 FND_MSG_PUB.Count_And_Get
184 ( p_count => x_msg_count,
185 p_data => x_msg_data,
186 p_encoded => fnd_api.g_false
187 );
188
189 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
190 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end. Number of rows in the Output table: ' || (l_tbl_index - 1), 'End Procedure');
191 END IF;
192
193 EXCEPTION
194 WHEN FND_API.G_EXC_ERROR THEN
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
197 p_data => x_msg_data,
198 p_encoded => fnd_api.g_false);
199 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
200 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Error: ' || x_msg_data);
201 END IF;
202
203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
206 p_data => x_msg_data,
207 p_encoded => fnd_api.g_false);
208
209 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
210 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
211 END IF;
212
213 WHEN OTHERS THEN
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
216 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
217 p_procedure_name => l_api_name,
218 p_error_text => SUBSTR(SQLERRM,1,500));
219 END IF;
220 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
221 p_data => x_msg_data,
222 p_encoded => fnd_api.g_false);
223
224 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Other Exception: ' || x_msg_data);
226 END IF;
227
228 END GET_WO_ALTERNATES;
229
230 ----------------------------------------
231 -- Start of Comments --
232 -- Procedure name : UPDATE_WO_MATERIALS
233 -- Type : Public
234 -- Function : Updates CMRO material requirements with alternate items or dates
235 -- Parameters :
236 --
237 -- Standard IN Parameters :
238 -- p_api_version IN NUMBER Required
239 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
240 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
241 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
242 --
243 -- Standard OUT Parameters :
244 -- x_return_status OUT VARCHAR2 Required
245 -- x_msg_count OUT NUMBER Required
246 -- x_msg_data OUT VARCHAR2 Required
247 --
248 -- UPDATE_WO_MATERIALS Parameters:
249 -- p_wip_entity_id IN NUMBER Required
250 -- p_alt_item_tbl IN Alt_Item_Tbl_Type Represents the table of items and its alternates.
251 -- For each item and its recommended alternate, there will be one record in this table.
252 -- Table can be empty if only the date has changed.
253 --
254 -- Version :
255 -- Initial Version 1.0
256 --
257 -- End of Comments.
258
259 PROCEDURE UPDATE_WO_MATERIALS
260 (
261 p_api_version IN NUMBER,
262 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
263 p_commit IN VARCHAR2 := FND_API.G_FALSE,
264 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_wip_entity_id IN NUMBER,
269 p_alt_item_tbl IN Alt_Item_Tbl_Type) IS
270
271
272 l_api_version CONSTANT NUMBER := 1.0;
273 l_api_name CONSTANT VARCHAR2(30) := 'update_wo_materials';
274 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || l_api_name;
275
276 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
277 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
278 l_commit VARCHAR2(1) := FND_API.G_FALSE;
279 l_prev_err_count NUMBER;
280 l_requested_date DATE;
281 l_scheduled_material_id NUMBER;
282 i NUMBER;
283 l_num_items_changed NUMBER := 0;
284
285 -- To fetch the Workorder schedule start date for the given wip entity id
286 CURSOR get_scheduled_start_date IS
287 SELECT scheduled_start_date
288 FROM wip_discrete_jobs
289 WHERE wip_entity_id = p_wip_entity_id;
290
291 -- To get all the scheduled_material_ids for the given wip_entity_id
292 -- for which requested date is not equal to WO scheduled start date
293 CURSOR get_scheduled_material_id (c_wip_entity_id IN NUMBER,
294 c_requested_date IN DATE) IS
295 SELECT asmt.scheduled_material_id
296 FROM ahl_workorders awo, ahl_schedule_materials asmt
297 WHERE awo.visit_task_id = asmt.visit_task_id
298 AND awo.wip_entity_id= c_wip_entity_id
299 AND asmt.requested_date <> trunc(c_requested_date)
300 AND asmt.status <> 'DELETED';
301
302 -- To get all the active scheduled_material_ids for the given wip_entity_id
303 -- and matching the passed item and operation
304 CURSOR get_matching_sched_mat_id(c_wip_entity_id IN NUMBER,
305 c_inventory_item_id IN NUMBER,
306 c_operation_sequence IN NUMBER) IS
307 SELECT asmt.scheduled_material_id
308 FROM ahl_workorders awo, ahl_schedule_materials asmt
309 WHERE awo.wip_entity_id= c_wip_entity_id
310 AND awo.visit_task_id = asmt.visit_task_id
311 AND asmt.inventory_item_id = c_inventory_item_id
315 BEGIN
312 AND asmt.operation_sequence = c_operation_sequence
313 AND asmt.status <> 'DELETED';
314
316
317 -- Standard start of API savepoint
318 SAVEPOINT update_wo_materials_pvt;
319 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
320 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure. p_wip_entity_id = ' || p_wip_entity_id);
321 END IF;
322
323 -- Standard call to check for call compatibility
324 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME )
325 THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
329 -- Initialize message list if p_init_msg_list is set to TRUE
330 IF FND_API.To_Boolean(p_init_msg_list)
331 THEN
332 FND_MSG_PUB.Initialize;
333 END IF;
334
335 l_prev_err_count := NVL(FND_MSG_PUB.count_msg,0);
336
337 -- Initialize API return status to success
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 -- Begin Processing
341 -- Get the scheduled start date for the given wip_entity_id into l_requested_date
342 OPEN get_scheduled_start_date;
343 FETCH get_scheduled_start_date INTO l_requested_date;
344 CLOSE get_scheduled_start_date;
345 -- If the schedule start date corresponding the input wip entity id fetched above is null
346 -- then the wip entity id passed to this procedure is invalid
347 IF l_requested_date IS NULL THEN
348 Fnd_Message.SET_NAME('AHL','AHL_LTP_WIP_ENTITY_ID_INVLD');
349 Fnd_Msg_Pub.ADD;
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352
353 IF (p_alt_item_tbl IS NULL OR p_alt_item_tbl.COUNT = 0) THEN
354 -- Only Date Change, No Item Change
355 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
356 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_alt_item_tbl is null or empty');
357 END IF;
358 ELSE
359 -- Item Change has happened
360 l_num_items_changed := p_alt_item_tbl.COUNT;
361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
362 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Number of items changed = ' || l_num_items_changed);
363 END IF;
364 END IF;
365
366 -- Handle date change
367 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to handle date change.');
369 END IF;
370 OPEN get_scheduled_material_id(p_wip_entity_id, l_requested_date);
371 i := 1;
372 -- Loop for all the scheduled material ids for the given wip_entity_id
373 -- for which material requested date is not equal to WO scheduled start date
374 -- since if these are equal then dates are already synchronized, so no need to update the dates
375 LOOP
376 FETCH get_scheduled_material_id INTO l_scheduled_material_id;
377 EXIT WHEN get_scheduled_material_id%NOTFOUND;
378
379 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inside the loop i = ' || i || ', scheduled_material_id = ' || l_scheduled_material_id);
381 END IF;
382 -- This API updates all the reservation dates, if l_requested_date
383 -- and requested_date in AHL_SCHEDULE_MATERIALS table are different
384 AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
385 p_api_version => 1.0,
386 p_init_msg_list => FND_API.G_FALSE,
387 p_commit => FND_API.G_FALSE,
388 p_module_type => NULL,
389 x_return_status => l_return_status,
390 x_msg_count => x_msg_count,
391 x_msg_data => x_msg_data,
392 p_scheduled_material_id => l_scheduled_material_id,
393 p_requested_date => l_requested_date);
394 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
395 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
396 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned success from AHL_RSV_RESERVATIONS_PVT.Update_Reservation');
397 END IF;
398 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
399 RAISE FND_API.G_EXC_ERROR;
400 ELSE
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402 END IF;
403
404 -- Now update AHL_SCHEDULE_MATERIALS table with requested_date as l_requested_date
405 UPDATE AHL_SCHEDULE_MATERIALS
406 SET requested_date = trunc(l_requested_date),
407 last_update_date = sysdate,
408 last_updated_by = fnd_global.user_id,
409 last_update_login = fnd_global.login_id
410 WHERE scheduled_material_id = l_scheduled_material_id;
411 i := i + 1;
412 END LOOP;
413 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After end of the loop, total number of iteration to modify date = ' || (i-1));
415 END IF;
416 CLOSE get_scheduled_material_id;
417
418 IF (l_num_items_changed > 0) THEN
419 -- Handle Item change
420 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to handle item change.');
422 END IF;
423 -- Changes made by jaramana on 29-SEP-2011 to handle Item Change
424 -- NOTE: The VCP Release process will make changes to the item in the WIP requirement only under the following conditions:
425 -- a. The entire quantity is changed (no partial quantity changes)
426 -- b. There does NOT already exist a requirement in the Work Order/Operation for the new item
427 FOR i IN p_alt_item_tbl.FIRST..p_alt_item_tbl.LAST LOOP
428 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
429 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'i = ' || i ||
430 ', p_alt_item_tbl(i).PRIMARY_ITEM_ID = ' || p_alt_item_tbl(i).PRIMARY_ITEM_ID ||
431 ', p_alt_item_tbl(i).OPERATION_SEQUENCE = ' || p_alt_item_tbl(i).OPERATION_SEQUENCE ||
432 ', p_alt_item_tbl(i).ALTERNATE_ITEM_ID = ' || p_alt_item_tbl(i).ALTERNATE_ITEM_ID);
433 END IF;
434 -- PRIMARY_ITEM_ID is the original item and ALTERNATE_ITEM_ID id the newly substituted item
435 IF NOT p_alt_item_tbl(i).PRIMARY_ITEM_ID = p_alt_item_tbl(i).ALTERNATE_ITEM_ID THEN
436 -- Check if a requirement exists for PRIMARY_ITEM_ID and then change it to ALTERNATE_ITEM_ID
437 OPEN get_matching_sched_mat_id(p_wip_entity_id, p_alt_item_tbl(i).PRIMARY_ITEM_ID, p_alt_item_tbl(i).OPERATION_SEQUENCE);
438 FETCH get_matching_sched_mat_id INTO l_scheduled_material_id;
439 IF get_matching_sched_mat_id%FOUND THEN
440 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Found Matching Schedule Material Id: ' || l_scheduled_material_id);
442 END IF;
443 UPDATE AHL_SCHEDULE_MATERIALS
444 SET inventory_item_id = p_alt_item_tbl(i).ALTERNATE_ITEM_ID,
445 last_update_date = sysdate,
446 last_updated_by = fnd_global.user_id,
447 last_update_login = fnd_global.login_id
448 WHERE scheduled_material_id = l_scheduled_material_id;
449 ELSE
450 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
451 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Matching Schedule Material record found');
452 END IF;
453 END IF;
454 CLOSE get_matching_sched_mat_id;
455 END IF;
456 END LOOP;
457 END IF;
458
459 -- Check Error Message stack.
460 x_msg_count := FND_MSG_PUB.count_msg;
461
462 IF x_msg_count - l_prev_err_count > 0 THEN
463 RAISE FND_API.G_EXC_ERROR;
464 END IF;
465
466 -- Standard check of p_commit
467 IF FND_API.TO_BOOLEAN(p_commit) THEN
468 COMMIT WORK;
469 END IF;
470
471 -- Standard call to get message count and if count is 1, get message info
472 FND_MSG_PUB.Count_And_Get
473 ( p_count => x_msg_count,
474 p_data => x_msg_data,
475 p_encoded => fnd_api.g_false
476 );
477
478 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
479 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
480 END IF;
481
482 EXCEPTION
483 WHEN FND_API.G_EXC_ERROR THEN
484 Rollback to update_wo_materials_pvt;
485 x_return_status := FND_API.G_RET_STS_ERROR;
486 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
487 p_data => x_msg_data,
488 p_encoded => fnd_api.g_false);
489 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Error: ' || x_msg_data);
491 END IF;
492
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
494 Rollback to update_wo_materials_pvt;
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
497 p_data => x_msg_data,
498 p_encoded => fnd_api.g_false);
499 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
500 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
501 END IF;
502
503 WHEN OTHERS THEN
504 Rollback to update_wo_materials_pvt;
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
507 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
508 p_procedure_name => l_api_name,
509 p_error_text => SUBSTR(SQLERRM,1,500));
510 END IF;
511 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
512 p_data => x_msg_data,
513 p_encoded => fnd_api.g_false);
514 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
515 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Other Exception: ' || x_msg_data);
516 END IF;
517
518 END update_wo_materials;
519
520
521 END AHL_MATERIALS_GRP;
[Home] [Help]