[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_REQST_MATRL_PVT
Source
1 PACKAGE BODY AHL_LTP_REQST_MATRL_PVT AS
2 /* $Header: AHLVRMTB.pls 120.27.12020000.2 2012/12/07 13:37:52 sareepar ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_REQST_MATRL_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 ------------------------------------
8 -- Common constants and variables --
9 ------------------------------------
10 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
11 l_log_statement NUMBER := fnd_log.level_statement;
12 l_log_procedure NUMBER := fnd_log.level_procedure;
13 l_log_error NUMBER := fnd_log.level_error;
14 l_log_unexpected NUMBER := fnd_log.level_unexpected;
15 -----------------------------------------------------------------
16
17 -- Definition of private procedure.
18 --
19 PROCEDURE Modify_Visit_Reservations (
20 p_visit_id IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2);
22
23 -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010 :: START
24 TYPE dff_default_values_type IS RECORD
25 (
26 ATTRIBUTE_CATEGORY VARCHAR2(30),
27 ATTRIBUTE1 VARCHAR2(150),
28 ATTRIBUTE2 VARCHAR2(150),
29 ATTRIBUTE3 VARCHAR2(150),
30 ATTRIBUTE4 VARCHAR2(150),
31 ATTRIBUTE5 VARCHAR2(150),
32 ATTRIBUTE6 VARCHAR2(150),
33 ATTRIBUTE7 VARCHAR2(150),
34 ATTRIBUTE8 VARCHAR2(150),
35 ATTRIBUTE9 VARCHAR2(150),
36 ATTRIBUTE10 VARCHAR2(150),
37 ATTRIBUTE11 VARCHAR2(150),
38 ATTRIBUTE12 VARCHAR2(150),
39 ATTRIBUTE13 VARCHAR2(150),
40 ATTRIBUTE14 VARCHAR2(150),
41 ATTRIBUTE15 VARCHAR2(150)
42 );
43
44 PROCEDURE Get_DFF_Default_Values (
45 flexfield_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
46 context_code IN VARCHAR2,
47 flex_fields_defaults OUT NOCOPY dff_default_values_type);
48 -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010 :: END
49
50 -- PROCEDURE
51 -- anraj added
52 Procedure Unschedule_Visit_Materials (
53 p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
55 p_commit IN VARCHAR2 := FND_API.g_false,
56 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
57 p_visit_id IN NUMBER,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2
61 )
62 IS
63 -- Cursor modified by surrkuma on 14-JUL-2010 for bug 9901811
64 CURSOR c_sch_mat_cur (c_visit_id IN NUMBER)
65 IS
66 SELECT asm.scheduled_material_id,
67 asm.object_version_number,
68 asm.inventory_item_id,
69 asm.visit_id,
70 asm.scheduled_quantity,
71 asm.scheduled_date,
72 asm.visit_task_id,
73 asm.organization_id,
74 asm.requested_quantity,
75 asm.uom,
76 asm.requested_date,
77 asm.status,
78 mtl.concatenated_segments
79 FROM ahl_schedule_materials asm,
80 mtl_system_items_kfv mtl
81 WHERE asm.visit_id = c_visit_id
82 AND mtl.inventory_item_id = asm.inventory_item_id
83 AND mtl.organization_id = asm.organization_id;
84
85 l_sch_mtls_Rec c_sch_mat_cur%rowtype;
86
87 CURSOR c_visit_task_matrl_cur(c_sch_mat_id IN NUMBER)
88 IS
89 SELECT scheduled_date,scheduled_quantity
90 FROM ahl_visit_task_matrl_v
91 WHERE schedule_material_id = c_sch_mat_id;
92
93 l_api_name CONSTANT VARCHAR2(30) := 'Unschedule_Visit_Materials';
94 l_api_version CONSTANT NUMBER := 1.0;
95 l_msg_count NUMBER;
96 l_return_status VARCHAR2(1);
97 l_msg_data VARCHAR2(2000);
98 l_dummy NUMBER;
99 l_scheduled_date DATE;
100 l_scheduled_quantity NUMBER;
101
102 -- Variables added by surrkuma on 14-JUL-2010 for bug 9901811
103 l_unsched_mtl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
104 l_unsched_mtl_index NUMBER := 1;
105
106 /*_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
107 i NUMBER := 0;
108 l_visit_name VARCHAR2(80);
109 */
110 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
111 BEGIN
112 --------------------Initialize ----------------------------------
113 -- Standard Start of API savepoint
114 SAVEPOINT unschedule_visit;
115 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
116 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin',
117 'At the start of PLSQL procedure');
118 END IF;
119 -- Standard call to check for call compatibility.
120 IF FND_API.to_boolean(p_init_msg_list)THEN
121 FND_MSG_PUB.initialize;
122 END IF;
123 -- Initialize API return status to success
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125 -- Initialize message list if p_init_msg_list is set to TRUE.
126 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
127 p_api_version,
128 l_api_name,G_PKG_NAME)
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132 --Check for material scheduling
133 OPEN c_sch_mat_cur(p_visit_id);
134 LOOP
135 -- Changes by surrkuma on 15-JUL-2010 for bug 9901811
136 FETCH c_sch_mat_cur INTO l_sch_mtls_Rec;
137 EXIT WHEN c_sch_mat_cur%NOTFOUND;
138 IF l_sch_mtls_Rec.scheduled_material_id IS NOT NULL THEN
139 --Check for Item scheduled
140 OPEN c_visit_task_matrl_cur(l_sch_mtls_Rec.scheduled_material_id);
141 FETCH c_visit_task_matrl_cur INTO l_scheduled_date,l_scheduled_quantity;
142 IF l_scheduled_date IS NOT NULL THEN
143 Fnd_Message.SET_NAME('AHL','AHL_LTP_MRP_SCHEDUl_ITEM');
144 Fnd_Msg_Pub.ADD;
145 CLOSE c_visit_task_matrl_cur;
146 RAISE Fnd_Api.G_EXC_ERROR;
147 ELSE
148 -- Added by surrkuma on 14-JUL-2010 for bug 9901811
149 IF (NVL(l_sch_mtls_Rec.status, 'X') = 'ACTIVE') THEN
150 -- Add this requirement to l_unsched_mtl_tbl for ATP unscheduling
151 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
152 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
153 'l_unsched_mtl_index = ' || l_unsched_mtl_index ||
154 ', Adding Requirement with id ' || l_sch_mtls_Rec.scheduled_material_id || ' to l_unsched_mtl_tbl for Unscheduling.');
155 END IF;
156 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_id := l_sch_mtls_Rec.visit_id;
157 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_task_id := l_sch_mtls_Rec.visit_task_id;
158 l_unsched_mtl_tbl(l_unsched_mtl_index).inventory_item_id := l_sch_mtls_Rec.inventory_item_id;
159 l_unsched_mtl_tbl(l_unsched_mtl_index).item_description := l_sch_mtls_Rec.concatenated_segments;
160 l_unsched_mtl_tbl(l_unsched_mtl_index).organization_id := l_sch_mtls_Rec.organization_id;
161 l_unsched_mtl_tbl(l_unsched_mtl_index).schedule_material_id := l_sch_mtls_Rec.scheduled_material_id;
162 l_unsched_mtl_tbl(l_unsched_mtl_index).required_quantity := l_sch_mtls_Rec.requested_quantity;
163 l_unsched_mtl_tbl(l_unsched_mtl_index).primary_uom_code := l_sch_mtls_Rec.uom;
164 l_unsched_mtl_tbl(l_unsched_mtl_index).requested_date := l_sch_mtls_Rec.requested_date;
165 l_unsched_mtl_index := l_unsched_mtl_index + 1;
166 END IF;
167 -- End addition by surrkuma on 14-JUL-2010 for bug 9901811
168 UPDATE ahl_schedule_materials
169 SET requested_quantity = 0,
170 status = 'DELETED',
171 object_version_number = l_sch_mtls_Rec.object_version_number + 1,
172 last_update_date = SYSDATE,
173 last_updated_by = Fnd_Global.user_id,
174 last_update_login = Fnd_Global.login_id
175 WHERE scheduled_material_id = l_sch_mtls_Rec.scheduled_material_id;
176 --
177 END IF; --Scheduled date
178 CLOSE c_visit_task_matrl_cur;
179 END IF;-- Scheduled mat id
180 END LOOP;
181 CLOSE c_sch_mat_cur;
182
183 -- Added by surrkuma on 14-JUL-2010 for bug 9901811
184 IF (l_unsched_mtl_tbl.COUNT > 0) THEN
185 -- Call ATP to unschedule the soft Deleted requirements
186 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
187 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
188 'Before calling AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials. l_unsched_mtl_tbl.COUNT = ' || l_unsched_mtl_tbl.COUNT);
189 END IF;
190 AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials(p_api_version => 1.0,
191 p_deleted_matrl_tbl => l_unsched_mtl_tbl,
192 x_return_status => l_return_status,
193 x_msg_count => l_msg_count,
194 x_msg_data => l_msg_data);
195 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
196 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
197 'After returning from AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials, l_return_status = ' || l_return_status);
198 END IF;
199 END IF;
200 -- End addition by surrkuma on 14-JUL-2010 for bug 9901811
201
202 -- Serial Number reservation Enh.
203 -- When a Visit is unscheduled, all the reservations made for the Visit should also be deleted
204 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
205 X_RETURN_STATUS => X_RETURN_STATUS,
206 P_VISIT_ID => p_visit_id);
207 IF (l_log_statement >= l_log_current_level)THEN
208 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
209 ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS. Error Message Status: ' || X_RETURN_STATUS);
210 END IF;
211
212 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
213 RAISE Fnd_Api.g_exc_error;
214 END IF;
215
216 IF FND_API.TO_BOOLEAN(p_commit) THEN
217 COMMIT WORK;
218 END IF;
219
220 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
221 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
222 'At the end of normal execution of the PLSQL procedure.');
223 END IF;
224
225 EXCEPTION
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 ROLLBACK TO unschedule_visit;
228 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
230 p_count => x_msg_count,
231 p_data => x_msg_data);
232 WHEN FND_API.G_EXC_ERROR THEN
233 ROLLBACK TO unschedule_visit;
234 X_return_status := FND_API.G_RET_STS_ERROR;
235 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
236 p_count => x_msg_count,
237 p_data => X_msg_data);
238 WHEN OTHERS THEN
239 ROLLBACK TO unschedule_visit;
240 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
242 THEN
243 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
244 p_procedure_name => 'Unschedule_Visit_Materials ',
245 p_error_text => SUBSTR(SQLERRM,1,240));
246 END IF;
247 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
248 p_count => x_msg_count,
249 p_data => X_msg_data);
250 END Unschedule_Visit_Materials;
251
252
253
254 -- PROCEDURE
255 -- Insert_Planned_Matrls
256 --
257 -- PURPOSE
258 -- Creates record in ahl_schedule_materials
259 --
260 -- PARAMETERS
261 --
262 -- NOTES
263
264 PROCEDURE Insert_Planned_Matrls(
265 p_visit_id IN NUMBER,
266 p_visit_task_id IN NUMBER,
267 p_task_start_date IN DATE,
268 p_inventory_item_id IN NUMBER,
269 p_requested_quantity IN NUMBER,
270 p_uom_code IN VARCHAR2,
271 p_item_group_id IN NUMBER,
272 p_rt_oper_material_id IN NUMBER,
273 p_position_path_id IN NUMBER,
274 p_relationship_id IN NUMBER,
275 p_mr_route_id IN NUMBER default null,
276 p_item_comp_detail_id IN NUMBER default null,
277 p_inv_master_org_id IN NUMBER default null,
278 p_mc_header_id IN NUMBER, -- Added by surrkuma on 07-Jun-2011 for Service Bulletin
279 p_position_key IN NUMBER, -- Added by surrkuma on 07-Jun-2011 for Service Bulletin
280 x_return_status OUT NOCOPY VARCHAR2,
281 x_msg_count OUT NOCOPY NUMBER,
282 x_msg_data OUT NOCOPY VARCHAR2
283 )
284 IS
285 -- Check for record already exists
286 -- Modified by surrkuma on 28-Jul-2011 for handling control position
287 -- based material requirements.
288 CURSOR check_matrl_cur (c_visit_id IN NUMBER,
289 c_visit_task_id IN NUMBER,
290 c_rt_oper_mat_id IN NUMBER,
291 c_position_key IN NUMBER)
292 IS
293 -- yazhou 17-May-2006 starts
294 -- bug fix#5232544
295
296 -- yazhou 03-JUL-2006 starts
297 -- bug fix#5303378
298
299 SELECT scheduled_material_id
300 FROM AHL_SCHEDULE_MATERIALS
301 WHERE visit_id = c_visit_id
302 AND visit_task_id = c_visit_task_id
303 -- AND requested_quantity <> 0
304 AND NVL(status,'') = 'ACTIVE'
305 AND rt_oper_material_id = c_rt_oper_mat_id
306 -- surrkuma on 28-Jul-2011 for handling control position based material
307 -- requirements
308 AND NVL(position_key, 0) = NVL(c_position_key, 0);
309
310 -- yazhou 03-JUL-2006 ends
311
312 -- yazhou 17-May-2006 ends
313
314 -- Cursor to get organization and schedule designator
315 CURSOR get_org_cur (c_visit_id IN NUMBER)
316 IS
317 SELECT organization_id
318 FROM ahl_visits_b
319 WHERE visit_id = c_visit_id;
320 --Get priority item from item associations
321 CURSOR Get_Prior_Item_Cur(C_ITEM_GROUP_ID IN NUMBER,
322 C_ORG_ID IN NUMBER)
323 IS
324 SELECT it.inventory_item_id,
325 it.priority,
326 it.uom_code,
327 it.quantity
328 FROM ahl_item_associations_vl it,
329 mtl_system_items_vl mt
330 WHERE it.inventory_item_id = mt.inventory_item_id
331 AND item_group_id = C_ITEM_GROUP_ID
332 AND mt.organization_id = C_ORG_ID
333 -- Fix for bug # 4109330
334 AND it.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
335 ORDER BY priority;
336 --Bug Fix #4104968
337 CURSOR get_route_cur (c_visit_task_id IN NUMBER)
338 IS
339 SELECT route_id
340 FROM ahl_mr_routes mr,
341 ahl_visit_tasks_b vt
342 WHERE mr.mr_route_id = vt.mr_route_id
343 AND visit_task_id = c_visit_task_id;
344 --Modifed the cursor for Bug #4104968
345 -- Cursor to get operation sequence and operation id
346 CURSOR get_oper_seq_cur (c_rt_oper_mat_id IN NUMBER,
347 c_route_id IN NUMBER)
348 IS
349 SELECT ro.step,
350 ro.operation_id,
351 ro.concatenated_segments
352 FROM ahl_route_operations_v ro,
353 ahl_rt_oper_materials rm
354 WHERE ro.operation_id = rm.object_id
355 AND ro.route_id = c_route_id
356 AND rm.rt_oper_material_id = c_rt_oper_mat_id
357 AND rm.association_type_code = 'OPERATION';
358 -- Inventory item should exists in visit org
359 CURSOR Check_item_org (C_ITEM_ID IN NUMBER,
360 C_ORG_ID IN NUMBER)
361 IS
362 SELECT inventory_item_id,
363 primary_uom_code
364 FROM mtl_system_items_vl
365 WHERE inventory_item_id = C_ITEM_ID
366 AND organization_id = C_ORG_ID;
367 --Get quanity from rt oper materisl if null
368 CURSOR Quantity_cur (c_rt_oper_mat_id IN NUMBER)
369 IS
370 SELECT quantity,
371 in_service, --B5865210 - sowsubra
372 replace_percent,
373 association_type_code
374 FROM ahl_rt_oper_materials
375 WHERE rt_oper_material_id = c_rt_oper_mat_id;
376
377 --Standard local variables
378 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Planned_Matrls';
379 l_api_version CONSTANT NUMBER := 1.0;
380 l_msg_data VARCHAR2(2000);
381 l_return_status VARCHAR2(1);
382 l_msg_count NUMBER;
383 l_init_msg_list VARCHAR2(10) := FND_API.g_false;
384 --
385 l_schedule_material_id NUMBER;
386 l_dummy NUMBER;
387 l_organization_id NUMBER;
388 l_operation_id NUMBER;
389 l_inventory_item_id NUMBER := p_inventory_item_id;
390 l_requested_quantity NUMBER := p_requested_quantity;
391 l_inventory_org_item_id NUMBER;
392 l_uom_code VARCHAR2(3) := p_uom_code;
393 l_step NUMBER;
394 l_operation_code VARCHAR2(80);
395 l_prim_uom_code VARCHAR2(3) := null;
396 l_prim_quantity NUMBER;
397 l_replace_percent NUMBER;
398 l_assoc_type_code VARCHAR2(30);
399 l_sched_prim_quantity NUMBER; -- yazhou 04Aug2005
400 l_route_id NUMBER;
401 --
402 l_task_type_code VARCHAR2(30);
403 l_material_request_type VARCHAR2(30);
404 l_Prior_Item_Rec Get_Prior_Item_Cur%ROWTYPE;
405 l_isInservice AHL_RT_OPER_MATERIALS.IN_SERVICE%TYPE; --Added by sowsubra for Issue 105
406 l_mat_status AHL_SCHEDULE_MATERIALS.STATUS%TYPE; --Added by sowsubra for Issue 105
407 -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010
408 l_default_dff_values dff_default_values_type;
409
410 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
411
412
413 BEGIN
414
415 IF (l_log_procedure >= l_log_current_level)THEN
416 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin',
417 'At the start of PLSQL procedure');
418 END IF;
419 -- Standard start of API savepoint
420 SAVEPOINT Insert_Planned_Matrls;
421 -- Initialize message list if p_init_msg_list is set to TRUE
422 IF FND_API.To_Boolean( l_init_msg_list) THEN
423 FND_MSG_PUB.Initialize;
424 END IF;
425 -- Initialize API return status to success
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427 --
428 IF (l_log_statement >= l_log_current_level)THEN
429 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
430 'Request for Create Planned Material for Visit Id: ' || p_visit_id);
431 END IF;
432
433 --Get the sequence number
434 SELECT ahl_schedule_materials_s.nextval INTO l_schedule_material_id FROM DUAL;
435 --Check for record exists
436 -- Surrkuma on 28-Jul-2011 for handling control position based material requirements
437 OPEN check_matrl_cur(p_visit_id,p_visit_task_id,p_rt_oper_material_id,p_position_key);
438 FETCH check_matrl_cur INTO l_dummy;
439 CLOSE check_matrl_cur;
440 --Get visit Organization
441 OPEN get_org_cur(p_visit_id);
442 FETCH get_org_cur INTO l_organization_id;
443 CLOSE get_org_cur;
444 --Get Route id
445 OPEN get_route_cur(p_visit_task_id);
446 FETCH get_route_cur INTO l_route_id;
447 CLOSE get_route_cur;
448 --During org change in schedule visits UI
449 IF p_inv_master_org_id IS NOT NULL THEN
450 l_organization_id := p_inv_master_org_id;
451 END IF;
452 -- Get operation sequence
453 OPEN get_oper_seq_cur(p_rt_oper_material_id,l_route_id);
454 FETCH get_oper_seq_cur INTO l_step,l_operation_id,l_operation_code;
455 CLOSE get_oper_seq_cur;
456
457 -- surrkuma :: Bug #9901811 :: 15-JUL-2010
458 -- Modified the below IF block
459 IF (p_item_group_id IS NOT NULL) THEN
460 --Get from item associations
461 OPEN Get_Prior_Item_Cur(p_item_group_id,l_organization_id);
462 FETCH Get_Prior_Item_Cur INTO l_Prior_Item_rec;
463 CLOSE Get_Prior_Item_Cur;
464 --Assign returned values
465 l_inventory_item_id := l_prior_Item_rec.inventory_item_id;
466 END IF;
467
468 --Check for item exists in inventory Ord
469 OPEN Check_item_org(l_inventory_item_id,l_organization_id);
470 FETCH Check_item_org INTO l_inventory_org_item_id,l_prim_uom_code;
471 CLOSE Check_item_org;
472
473 --Check for primary UOM Code
474 IF l_uom_code <> l_prim_uom_code THEN
475 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
476 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
477 'Primary Uom Code : '|| l_prim_uom_code || ', Source Uom Code : '|| l_uom_code);
478 END IF;
479
480 -- yazhou 04Aug2005 Starts
481 l_prim_quantity := AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(l_inventory_item_id, l_uom_code, l_requested_quantity);
482
483 -- Required to check the UOM Conversion exists in mtl_units_of_measure
484 IF l_prim_quantity IS NULL THEN
485 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_UOM_CONV_NOT_EXIST' );
486 FND_MESSAGE.Set_Token('FUOM', l_uom_code);
487 FND_MESSAGE.Set_Token('TUOM', l_prim_uom_code);
488 FND_MSG_PUB.add;
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 l_sched_prim_quantity := l_prim_quantity;
493
494 -- yazhou 04Aug2005 Ends
495
496 --sowsubra
497 --changes done to collect inservice material
498 OPEN Quantity_cur(p_rt_oper_material_id);
499 FETCH Quantity_cur INTO l_requested_quantity,l_isInservice,l_replace_percent,l_assoc_type_code;
500 CLOSE Quantity_cur;
501
502 -- For Bug # 4007058
503 -- IF l_assoc_type_code = 'DISPOSITION' AND NVL(l_replace_percent,0) < 100 THEN
504 IF NVL(l_replace_percent,100) < 100 THEN
505 l_prim_quantity := 0;
506 END IF;
507
508 --Added by sowsubra for Issue 105
509 IF NVL(l_isInservice,'N') = 'N' THEN
510 l_mat_status := 'ACTIVE';
511 ELSE
512 l_mat_status := 'IN-SERVICE';
513 END IF;
514
515 ELSE
516
517 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
518 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
519 'ELSE Primary Uom Code: ' || l_prim_uom_code ||
520 ', Source Uom Code: ' || l_uom_code ||
521 ', Requested quantity: ' || l_requested_quantity);
522 END IF;
523
524 -- yazhou 04Aug2005 Starts
525
526 l_sched_prim_quantity := l_requested_quantity;
527
528 --sowsubra
529 --changes done to collect inservice material
530 OPEN Quantity_cur(p_rt_oper_material_id);
531 FETCH Quantity_cur INTO l_requested_quantity,l_isInservice,l_replace_percent,l_assoc_type_code;
532 CLOSE Quantity_cur;
533 -- else passsed value
534 -- For Bug # 4007058
535 -- IF l_assoc_type_code = 'DISPOSITION' AND NVL(l_replace_percent,0) < 100 THEN
536 IF NVL(l_replace_percent,100) < 100 THEN
537 l_prim_quantity := 0;
538 ELSE
539 l_prim_quantity := l_sched_prim_quantity;
540 END IF;
541
542 --Added by sowsubra for Issue 105
543 IF NVL(l_isInservice,'N') = 'N' THEN
544 l_mat_status := 'ACTIVE';
545 ELSE
546 l_mat_status := 'IN-SERVICE';
547 END IF;
548
549 -- yazhou 04Aug2005 Ends
550
551 END IF;
552
553 --Check for visit task type
554 SELECT TASK_TYPE_CODE INTO l_task_type_code
555 FROM ahl_visit_tasks_b
556 WHERE visit_task_id = p_visit_task_id;
557 --From unplanned and Unassociated
558 IF l_task_type_code IN ('UNPLANNED','UNASSOCIATED') THEN
559 l_material_request_type := 'UNPLANNED';
560 ELSE
561 l_material_request_type := 'PLANNED';
562 END IF;
563
564 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
565 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
566 'Before Insert Schedule Materials for Visit Id: '|| p_visit_id ||
567 ', Visit Task Id: ' || p_visit_task_id ||
568 ', Schedule Material Id: ' || l_schedule_material_id);
569 END IF;
570
571 -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010
572 -- Get the default Material Requirement DFF values
573 Get_DFF_Default_Values(flexfield_name => 'Material Reqmt Flex Field',
574 context_code => NULL,
575 flex_fields_defaults => l_default_dff_values);
576
577 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
578 -- Added MC_ID and POSITION_KEY to the INSERT command
579 -- Insert the record into schedule materials
580 IF (l_dummy IS NULL AND l_inventory_org_item_id IS NOT NULL )THEN
581 INSERT INTO AHL_SCHEDULE_MATERIALS
582 (SCHEDULED_MATERIAL_ID,
583 OBJECT_VERSION_NUMBER,
584 LAST_UPDATE_DATE,
585 LAST_UPDATED_BY,
586 CREATION_DATE,
587 CREATED_BY,
588 LAST_UPDATE_LOGIN,
589 INVENTORY_ITEM_ID,
590 SCHEDULE_DESIGNATOR,
591 VISIT_ID,
592 VISIT_START_DATE,
593 VISIT_TASK_ID,
594 ORGANIZATION_ID,
595 SCHEDULED_DATE,
596 REQUESTED_QUANTITY,
597 REQUEST_ID,
598 REQUESTED_DATE,
599 SCHEDULED_QUANTITY,
600 PROCESS_STATUS,
601 ERROR_MESSAGE,
602 TRANSACTION_ID,
603 UOM,
604 RT_OPER_MATERIAL_ID,
605 OPERATION_CODE,
606 ITEM_GROUP_ID,
607 OPERATION_SEQUENCE,
608 POSITION_PATH_ID,
609 RELATIONSHIP_ID,
610 MR_ROUTE_ID,
611 MATERIAL_REQUEST_TYPE,
612 STATUS,
613 MC_HEADER_ID,
614 POSITION_KEY,
615 ATTRIBUTE_CATEGORY,
616 ATTRIBUTE1,
617 ATTRIBUTE2,
618 ATTRIBUTE3,
619 ATTRIBUTE4,
620 ATTRIBUTE5,
621 ATTRIBUTE6,
622 ATTRIBUTE7,
623 ATTRIBUTE8,
624 ATTRIBUTE9,
625 ATTRIBUTE10,
626 ATTRIBUTE11,
627 ATTRIBUTE12,
628 ATTRIBUTE13,
629 ATTRIBUTE14,
630 ATTRIBUTE15
631 )
632 VALUES
633 (l_schedule_material_id,
634 1,
635 SYSDATE,
636 fnd_global.user_id,
637 SYSDATE,
638 fnd_global.user_id,
639 fnd_global.login_id,
640 l_inventory_item_id,
641 NULL,
642 p_visit_id,
643 NULL,
644 p_visit_task_id,
645 l_organization_id,
646 NULL,
647 l_prim_quantity,
648 NULL,
649 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
650 trunc(p_task_start_date),
651 l_sched_prim_quantity, -- yazhou 04Aug2005
652 NULL,
653 NULL,
654 NULL,
655 l_uom_code,
656 p_rt_oper_material_id,
657 l_operation_code,
658 p_item_group_id,
659 l_step,
660 p_position_path_id,
661 p_relationship_id,
662 p_mr_route_id,
663 l_material_request_type,
664 l_mat_status, --Added by sowsubra for Issue 105
665 p_mc_header_id, -- Added by SURRKUMA :: Service Bulletin :: 07-Jun-2011
666 p_position_key, -- Added by SURRKUMA :: Service Bulletin :: 07-Jun-2011
667 -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010 :: START
668 l_default_dff_values.ATTRIBUTE_CATEGORY,
669 l_default_dff_values.ATTRIBUTE1,
670 l_default_dff_values.ATTRIBUTE2,
671 l_default_dff_values.ATTRIBUTE3,
672 l_default_dff_values.ATTRIBUTE4,
673 l_default_dff_values.ATTRIBUTE5,
674 l_default_dff_values.ATTRIBUTE6,
675 l_default_dff_values.ATTRIBUTE7,
676 l_default_dff_values.ATTRIBUTE8,
677 l_default_dff_values.ATTRIBUTE9,
678 l_default_dff_values.ATTRIBUTE10,
679 l_default_dff_values.ATTRIBUTE11,
680 l_default_dff_values.ATTRIBUTE12,
681 l_default_dff_values.ATTRIBUTE13,
682 l_default_dff_values.ATTRIBUTE14,
683 l_default_dff_values.ATTRIBUTE15);
684 -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010 :: END
685 END IF; --Record doesnt exist
686
687 -- Check Error Message stack.
688 l_msg_count := FND_MSG_PUB.count_msg;
689 IF l_msg_count > 0 THEN
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692
693 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
694 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
695 'At the end of PLSQL procedure.');
696 END IF;
697 EXCEPTION
698 WHEN FND_API.G_EXC_ERROR THEN
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 ROLLBACK TO Insert_Planned_Matrls;
701 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
702 p_data => x_msg_data,
703 p_encoded => fnd_api.g_false);
704
705
706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708 ROLLBACK TO Insert_Planned_Matrls;
709 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
710 p_data => x_msg_data,
711 p_encoded => fnd_api.g_false);
712
713
714 WHEN OTHERS THEN
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 ROLLBACK TO Insert_Planned_Matrls;
717 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
718 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
719 p_procedure_name => 'INSERT_PLANNED_MATRLS',
720 p_error_text => SUBSTR(SQLERRM,1,500));
721 END IF;
722 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
723 p_data => x_msg_data,
724 p_encoded => fnd_api.g_false);
725
726
727 END Insert_Planned_Matrls;
728
729 -- Start of Comments --
730 -- Procedure name : Update_Planned_Materials
731 -- Type : Private
732 -- Function : This procedure Updates Planned materials information associated to scheduled
733 -- visit, which are defined at Route Operation and Disposition level
734 -- Pre-reqs :
735 -- Parameters :
736 --
737 -- Standard IN Parameters :
738 -- p_api_version IN NUMBER Required
739 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
740 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
741 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
742 -- Based on this flag, the API will set the default attributes.
743 -- p_module_type In VARCHAR2 Default NULL
744 -- This will be null.
745 -- Standard out Parameters :
746 -- x_return_status OUT VARCHAR2 Required
747 -- x_msg_count OUT NUMBER Required
748 -- x_msg_data OUT VARCHAR2 Required
749 --
750 -- Update_Planned_Materials Parameters :
751 -- p_planned_materials_tbl IN Planned_Materials_Tbl,Required
752 --
753 --
754 PROCEDURE Update_Planned_Materials (
755 p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
757 p_commit IN VARCHAR2 := FND_API.g_false,
758 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
759 p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
760 x_return_status OUT NOCOPY VARCHAR2,
761 x_msg_count OUT NOCOPY NUMBER,
762 x_msg_data OUT NOCOPY VARCHAR2)
763
764 IS
765
766 CURSOR Get_Planned_Items_cur (c_sched_mat_id IN NUMBER)
767 IS
768 SELECT scheduled_material_id,
769 object_version_number,
770 inventory_item_id,
771 requested_quantity,
772 visit_task_id,
773 organization_id,
774 completed_quantity,
775 requested_date,
776 visit_id
777 FROM ahl_schedule_materials
778 WHERE scheduled_material_id = c_sched_mat_id;
779
780 CURSOR Get_Inv_Item_cur (c_item_desc IN VARCHAR2,
781 c_org_id IN NUMBER)
782 IS
783 SELECT inventory_item_id
784 FROM mtl_system_items_vl
785 WHERE concatenated_segments = c_item_desc
786 AND organization_id = c_org_id;
787
788 -- Serial Number Resrvation Change Starts
789 CURSOR Get_Visit_Dates_cur (c_visit_id IN NUMBER)
790 IS
791 SELECT start_date_time, close_date_time
792 FROM ahl_visits_b
793 WHERE visit_id = c_visit_id;
794
795 -- Serial Number Resrvation Change ends
796
797 --Standard local variables
798 l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
799 l_api_version CONSTANT NUMBER := 1.0;
800 l_msg_data VARCHAR2(2000);
801 l_return_status VARCHAR2(1);
802 l_msg_count NUMBER;
803 --
804 l_planned_materials_tbl ahl_ltp_reqst_matrl_pub.planned_materials_tbl := p_planned_materials_tbl;
805 l_Planned_Items_rec Get_Planned_Items_cur%ROWTYPE;
806
807 l_rsvd_quantity NUMBER;
808
809 -- Serial Number Resrvation Change Starts
810 l_visit_start_date DATE;
811 l_visit_end_date DATE;
812 -- Serial Number Resrvation Change ends
813
814 BEGIN
815
816 IF (l_log_procedure >= l_log_current_level)THEN
817 fnd_log.string
818 (
819 l_log_procedure,
820 'ahl.plsql.'||g_pkg_name||'.'||l_api_name || '.begin',
821 'At the start of PLSQL procedure'
822 );
823 END IF;
824 -- Standard start of API savepoint
825 SAVEPOINT Update_Planned_Materials;
826 -- Initialize message list if p_init_msg_list is set to TRUE
827 IF FND_API.To_Boolean( p_init_msg_list) THEN
828 FND_MSG_PUB.Initialize;
829 END IF;
830 -- Initialize API return status to success
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832 --
833 IF (l_log_statement >= l_log_current_level)THEN
834 fnd_log.string
835 (
836 l_log_statement,
837 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
838 'Request for Update Material Number of Records : '|| l_planned_materials_tbl.COUNT
839 );
840
841 END IF;
842
843 IF l_planned_materials_tbl.COUNT > 0 THEN
844 FOR i IN l_planned_materials_tbl.FIRST..l_planned_materials_tbl.LAST
845 LOOP
846 --Check for Schedule material Record exists
847 IF (l_planned_materials_tbl(i).schedule_material_id IS NOT NULL AND
848 l_planned_materials_tbl(i).schedule_material_id <> FND_API.G_MISS_NUM ) THEN
849 --
850 OPEN Get_Planned_Items_cur(l_planned_materials_tbl(i).schedule_material_id);
851 FETCH Get_Planned_Items_cur INTO l_Planned_Items_rec;
852 IF Get_Planned_Items_cur%NOTFOUND THEN
853 FND_MESSAGE.set_name( 'AHL','AHL_LTP_SCHE_ID_INVALID' );
854 FND_MSG_PUB.add;
855 IF (l_log_error >= l_log_current_level)THEN
856 fnd_log.string
857 (
858 l_log_error,
859 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
860 'Schedule Material id not found in ahl_schedule_materials table'
861 );
862 END IF;
863 CLOSE Get_Planned_Items_cur;
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866 CLOSE Get_Planned_Items_cur;
867 END IF;
868
869 --Check for Record has been modified by someother user
870 IF (l_planned_materials_tbl(i).object_version_number IS NOT NULL AND
871 l_planned_materials_tbl(i).object_version_number <> FND_API.G_MISS_NUM ) THEN
872 --
873 IF (l_planned_materials_tbl(i).object_version_number <> l_Planned_Items_rec.object_version_number )
874 THEN
875 FND_MESSAGE.set_name( 'AHL','AHL_LTP_RECORD_INVALID' );
876 FND_MSG_PUB.add;
877 IF (l_log_error >= l_log_current_level)THEN
878 fnd_log.string
879 (
880 l_log_error,
881 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
882 'Schedule Material Record has been modified by someother user'
883 );
884 END IF;
885 RAISE FND_API.G_EXC_ERROR;
886 END IF;
887 END IF;
888 --Convert item description to item id
889 IF (l_planned_materials_tbl(i).item_description IS NOT NULL AND
890 l_planned_materials_tbl(i).item_description <> FND_API.G_MISS_CHAR ) THEN
891 --
892 OPEN Get_Inv_Item_cur(l_planned_materials_tbl(i).item_description,
893 l_planned_items_rec.organization_id);
894 FETCH Get_Inv_Item_cur INTO l_planned_materials_tbl(i).inventory_item_id;
895 IF Get_Inv_Item_cur%NOTFOUND THEN
896 FND_MESSAGE.set_name( 'AHL','AHL_LTP_ITEM_INVALID' );
897 FND_MSG_PUB.add;
898 IF (l_log_error >= l_log_current_level)THEN
899 fnd_log.string
900 (
901 l_log_error,
902 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
903 'Inventory Item doesnt exist in Mtl System Items Vl'
904 );
905 END IF;
906 CLOSE Get_Inv_Item_Cur;
907 RAISE FND_API.G_EXC_ERROR;
908 END IF;
909 CLOSE Get_Inv_Item_cur;
910 END IF;
911
912 -- AnRaj: Moved this code down, after the id in l_planned_materials_tbl has been populated
913 -- Serial Number Reservation Enhancement Changes Start.
914 -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
915 IF l_planned_materials_tbl(i).inventory_item_id <> l_Planned_Items_rec.inventory_item_id
916 THEN
917 SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
918 INTO l_rsvd_quantity
919 FROM mtl_reservations MR,
920 ahl_schedule_materials SM
921 WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
922 AND MR.external_source_code = 'AHL'
923 AND MR.demand_source_line_detail = SM.scheduled_material_id
924 AND MR.organization_id = SM.organization_id
925 AND MR.requirement_date = SM.requested_date
926 AND MR.inventory_item_id = SM.inventory_item_id;
927
928 -- This is based on PRIMARY_RESERVATION_QUANTITY is not null in mtl_reservations
929 IF l_rsvd_quantity IS NOT NULL THEN
930 FND_MESSAGE.set_name( 'AHL','AHL_LTP_ITEM_RSV_EXISTS' );
931 -- Cannot change the item required because at least one reservation already exists for this item.
932 FND_MSG_PUB.add;
933 RAISE FND_API.G_EXC_ERROR;
934 END IF;
935 END IF;
936 -- Serial Number Reservation Enhancement Changes Ends.
937
938 -- Validation for requested quantity
939 IF (l_planned_materials_tbl(i).quantity IS NOT NULL AND
940 l_planned_materials_tbl(i).quantity <> FND_API.G_MISS_NUM) THEN
941
942 -- yazhou 03-JUL-2006 starts
943 -- bug fix#5303378
944 -- Will allow quantity to be changed to zero
945
946 IF l_planned_materials_tbl(i).quantity < 0 THEN
947
948 -- yazhou 03-JUL-2006 ends
949 --
950 Fnd_message.SET_NAME('AHL','AHL_LTP_QUANTITY_INVALID');
951 Fnd_Msg_Pub.ADD;
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954
955 -- Serial Number Reservation Enhancement Changes Starts.
956 -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
957 IF NVL(l_planned_materials_tbl(i).quantity,-9) <> NVL(l_Planned_Items_rec.requested_quantity,-99)
958 THEN
959 SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
960 INTO l_rsvd_quantity
961 FROM mtl_reservations MR,
962 ahl_schedule_materials SM
963 WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
964 AND MR.external_source_code = 'AHL'
965 AND MR.demand_source_line_detail = SM.scheduled_material_id
966 AND MR.organization_id = SM.organization_id
967 AND MR.requirement_date = SM.requested_date
968 AND MR.inventory_item_id = SM.inventory_item_id;
969
970 IF NVL((NVL(l_rsvd_quantity,0) + nvl(l_Planned_Items_rec.completed_quantity,0)),-9) > NVL(l_planned_materials_tbl(i).quantity,-9)
971 THEN
972 Fnd_message.SET_NAME('AHL','AHL_LTP_QTY_EXCEEDS');
973 --Completed quantity plus reserved quantity exceeded scheduled quantity
974 Fnd_Msg_Pub.ADD;
975 RAISE FND_API.G_EXC_ERROR;
976 END IF;
977 END IF;
978 END IF;
979 -- Serial Number Reservation Enhancement Changes Ends.
980 -- Validation for requested date
981 IF (l_planned_materials_tbl(i).requested_date IS NOT NULL AND
982 l_planned_materials_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
983 IF l_planned_materials_tbl(i).requested_date < trunc(sysdate) THEN
984 --
985 Fnd_message.SET_NAME('AHL','AHL_LTP_DATE_INVALID');
986 Fnd_Msg_Pub.ADD;
987 RAISE FND_API.G_EXC_ERROR;
988
989 END IF;
990
991 -- Serial Number Reservation Enhancement Changes. Starts
992 IF l_planned_materials_tbl(i).requested_date IS NOT NULL
993 AND l_Planned_Items_rec.requested_date <> l_planned_materials_tbl(i).requested_date
994 THEN
995
996 -- New Required Date has to fall between Visit start date and Visit End Date
997 OPEN Get_Visit_Dates_cur(l_planned_items_rec.visit_id);
998 FETCH Get_Visit_Dates_cur into l_visit_start_date, l_visit_end_date;
999 CLOSE Get_Visit_Dates_cur;
1000
1001 IF (TRUNC(l_planned_materials_tbl(i).requested_date) < TRUNC(l_visit_start_date)) OR
1002 (l_visit_end_date is not NULL AND
1003 (TRUNC(l_planned_materials_tbl(i).requested_date) > TRUNC(l_visit_end_date))) THEN
1004
1005 Fnd_message.SET_NAME('AHL','AHL_LTP_REQ_DATE_RANGE');
1006 Fnd_Msg_Pub.ADD;
1007 RAISE FND_API.G_EXC_ERROR;
1008
1009 END IF;
1010
1011 AHL_RSV_RESERVATIONS_PVT.UPDATE_RESERVATION(
1012 P_API_VERSION => 1.0,
1013 /*P_INIT_MSG_LIST
1014 P_COMMIT
1015 P_VALIDATION_LEVEL */
1016 P_MODULE_TYPE => NULL,
1017 X_RETURN_STATUS => l_return_Status,
1018 X_MSG_COUNT => l_msg_count,
1019 X_MSG_DATA => X_MSG_DATA,
1020 P_SCHEDULED_MATERIAL_ID => l_planned_materials_tbl(i).schedule_material_id,
1021 P_REQUESTED_DATE => l_planned_materials_tbl(i).requested_date);
1022 END IF;
1023
1024 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1025 l_msg_count := FND_MSG_PUB.count_msg;
1026 RAISE FND_API.G_EXC_ERROR;
1027 END IF;
1028
1029 -- Serial Number Reservation Enhancement Changes. Ends
1030
1031 END IF;
1032 --
1033 END LOOP;
1034 END IF;
1035 -- Check Error Message stack.
1036 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1037 l_msg_count := FND_MSG_PUB.count_msg;
1038 IF l_msg_count > 0 THEN
1039 RAISE FND_API.G_EXC_ERROR;
1040 END IF;
1041 END IF;
1042 --
1043 IF l_planned_materials_tbl.COUNT > 0 THEN
1044 FOR i IN l_planned_materials_tbl.FIRST..l_planned_materials_tbl.LAST
1045 LOOP
1046 --
1047 IF l_planned_materials_tbl(i).schedule_material_id IS NOT NULL THEN
1048 --
1049 UPDATE ahl_schedule_materials
1050 SET inventory_item_id = l_planned_materials_tbl(i).inventory_item_id,
1051 requested_quantity = l_planned_materials_tbl(i).quantity,
1052 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1053 requested_date = trunc(l_planned_materials_tbl(i).requested_date),
1054 object_version_number = l_planned_materials_tbl(i).object_version_number + 1
1055 WHERE scheduled_material_id = l_planned_materials_tbl(i).schedule_material_id;
1056 END IF;
1057 --
1058 END LOOP;
1059 END IF;
1060 -- Check Error Message stack.
1061 x_msg_count := FND_MSG_PUB.count_msg;
1062 IF x_msg_count > 0 THEN
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065
1066 -- Standard check of p_commit
1067 IF FND_API.TO_BOOLEAN(p_commit) THEN
1068 COMMIT WORK;
1069 END IF;
1070
1071 IF (l_log_procedure >= l_log_current_level)THEN
1072 fnd_log.string
1073 (
1074 l_log_procedure,
1075 'ahl.plsql.'||g_pkg_name||'.'||l_api_name || '.end',
1076 'At the end of PLSQL procedure'
1077 );
1078 END IF;
1079 EXCEPTION
1080 WHEN FND_API.G_EXC_ERROR THEN
1081 x_return_status := FND_API.G_RET_STS_ERROR;
1082 ROLLBACK TO Update_Planned_Materials;
1083 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1084 p_data => x_msg_data,
1085 p_encoded => fnd_api.g_false);
1086
1087
1088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090 ROLLBACK TO Update_Planned_Materials;
1091 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1092 p_data => x_msg_data,
1093 p_encoded => fnd_api.g_false);
1094
1095
1096 WHEN OTHERS THEN
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098 ROLLBACK TO Update_Planned_Materials;
1099 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1100 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1101 p_procedure_name => 'Update_Planned_Materials',
1102 p_error_text => SUBSTR(SQLERRM,1,500));
1103 END IF;
1104 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1105 p_data => x_msg_data,
1106 p_encoded => fnd_api.g_false);
1107
1108 END Update_Planned_Materials;
1109
1110 --
1111 -- Start of Comments --
1112 -- Procedure name : Create_Task_Materials
1113 -- Type : Private
1114 -- Function : This procedure Created Planned materials information associated to scheduled
1115 -- visit, which are defined at Route Operation and Disposition level
1116 -- Pre-reqs :
1117 -- Parameters :
1118 --
1119 -- Standard IN Parameters :
1120 -- p_api_version IN NUMBER Required
1121 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1122 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1123 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1124 -- Based on this flag, the API will set the default attributes.
1125 -- p_module_type In VARCHAR2 Default NULL
1126 -- This will be null.
1127 -- Standard out Parameters :
1128 -- x_return_status OUT VARCHAR2 Required
1129 -- x_msg_count OUT NUMBER Required
1130 -- x_msg_data OUT VARCHAR2 Required
1131 --
1132 -- Create_Planned_Materials Parameters :
1133 -- p_visit_id IN NUMBER,Required
1134 --
1135 --
1136 PROCEDURE Create_Task_Materials (
1137 p_api_version IN NUMBER,
1138 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1139 p_commit IN VARCHAR2 := FND_API.g_false,
1140 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1141 p_visit_id IN NUMBER,
1142 p_visit_task_id IN NUMBER := NULL,
1143 p_start_time IN DATE := NULL,
1144 p_org_id IN NUMBER := NULL,
1145 x_return_status OUT NOCOPY VARCHAR2,
1146 x_msg_count OUT NOCOPY NUMBER,
1147 x_msg_data OUT NOCOPY VARCHAR2)
1148
1149 IS
1150 --Get visit details
1151 /*B6271339 - sowsubra - Modified the where clause to filter the records based on visit task id alone*/
1152 CURSOR Get_Visit_Tasks_cur(c_visit_task_id IN NUMBER) IS
1153 SELECT vs.visit_id,
1154 vs.organization_id,
1155 vt.visit_task_id,
1156 vt.mr_route_id,
1157 vt.instance_id,
1158 vt.start_date_time
1159 FROM ahl_visits_b vs,
1160 ahl_visit_tasks_b vt
1161 WHERE vs.visit_id = vt.visit_id
1162 AND vt.visit_task_id = C_VISIT_TASK_ID;
1163
1164 --Get Route details
1165 /*B6271339 - sowsubra - Modified the cursor to fetch only the route id*/
1166 CURSOR Get_Routes_cur(c_mr_route_id IN NUMBER)
1167 IS
1168 SELECT mr.route_id
1169 FROM ahl_mr_routes_app_v mr
1170 WHERE mr.mr_route_id = C_MR_ROUTE_ID;
1171
1172 CURSOR Visit_Valid_Cur(c_visit_id IN NUMBER)
1173 IS
1174 SELECT 1
1175 FROM ahl_visits_vl
1176 WHERE visit_id = C_VISIT_ID
1177 AND (organization_id IS NULL
1178 OR start_date_time IS NULL);
1179
1180 --Standard local variables
1181 l_api_name CONSTANT VARCHAR2(30) := 'Create_Task_Materials';
1182 l_api_version CONSTANT NUMBER := 1.0;
1183 l_msg_data VARCHAR2(2000);
1184 l_return_status VARCHAR2(1);
1185 l_msg_count NUMBER;
1186 l_dummy NUMBER;
1187 --Variables for derieve start times
1188 l_visit_start_time DATE := nvl(p_start_time,null);
1189 --
1190 l_route_id NUMBER;
1191 l_instance_id NUMBER;
1192 l_requirement_date DATE;
1193 l_visit_tasks_rec Get_visit_tasks_cur%ROWTYPE;
1194 l_route_mtl_req_tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1195
1196 BEGIN
1197
1198 IF (l_log_procedure >= l_log_current_level)THEN
1199 fnd_log.string
1200 (
1201 l_log_procedure,
1202 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1203 'At the start of PLSQL procedure'
1204 );
1205 END IF;
1206 -- Standard start of API savepoint
1207 SAVEPOINT Create_Task_Materials;
1208 -- Initialize message list if p_init_msg_list is set to TRUE
1209 IF FND_API.To_Boolean( p_init_msg_list) THEN
1210 FND_MSG_PUB.Initialize;
1211 END IF;
1212 -- Initialize API return status to success
1213 x_return_status := FND_API.G_RET_STS_SUCCESS;
1214 --
1215 IF (l_log_statement >= l_log_current_level)THEN
1216 fnd_log.string
1217 (
1218 l_log_statement,
1219 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1220 'Request for Create Task Materials for Visit Id : '|| p_visit_id
1221 );
1222
1223 END IF;
1224
1225 --Get visit details
1226 /*B6271339 - sowsubra - Modified the where clause to filter the records based on visit task id alone*/
1227 OPEN Get_visit_tasks_cur(p_visit_task_id);
1228 FETCH Get_visit_tasks_cur INTO l_visit_tasks_rec;
1229 CLOSE Get_visit_tasks_cur;
1230
1231 IF (l_log_statement >= l_log_current_level)THEN
1232 fnd_log.string
1233 (
1234 l_log_statement,
1235 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1236 ' Visit Id: ' || p_visit_id
1237 );
1238 fnd_log.string
1239 (
1240 l_log_statement,
1241 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1242 ' Organization Id: ' || l_visit_tasks_rec.organization_id
1243 );
1244
1245 END IF;
1246
1247 --Check for visit Org, Dept, Start date should be not null
1248 OPEN Visit_Valid_Cur(p_visit_id);
1249 FETCH Visit_Valid_Cur INTO l_dummy;
1250 CLOSE Visit_Valid_Cur;
1251
1252 -- Derieve task start times
1253 IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM
1254 AND l_dummy IS NULL) THEN
1255 -- Derive task start time
1256
1257 IF (l_log_statement >= l_log_current_level)THEN
1258 fnd_log.string
1259 (
1260 l_log_statement,
1261 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1262 ' Visit Task Id: ' || l_visit_tasks_rec.visit_task_id
1263 );
1264 fnd_log.string
1265 (
1266 l_log_statement,
1267 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1268 ' Task Start Time: ' || l_visit_tasks_rec.start_date_time
1269 );
1270 fnd_log.string
1271 (
1272 l_log_statement,
1273 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1274 ' Mr Route Id: ' || l_visit_tasks_rec.mr_route_id
1275 );
1276
1277 END IF;
1278 -- Process all the items associated
1279 IF l_visit_tasks_rec.mr_route_id IS NOT NULL THEN
1280 -- Retrieve route and instance
1281
1282 /*B6271339 - sowsubra - Modified the cursor to fetch only the route id*/
1283 OPEN Get_Routes_cur(l_visit_tasks_rec.mr_route_id);
1284 FETCH Get_Routes_cur INTO l_route_id;
1285 CLOSE Get_Routes_cur;
1286 --
1287 IF (l_visit_tasks_rec.start_date_time IS NOT NULL AND TRUNC(l_visit_tasks_rec.start_date_time) < TRUNC(sysdate)
1288 ) THEN
1289 l_requirement_date := SYSDATE;
1290 --
1291 ELSE
1292 l_requirement_date := l_visit_tasks_rec.start_date_time;
1293 END IF;
1294
1295 IF (l_log_statement >= l_log_current_level)THEN
1296 fnd_log.string
1297 (
1298 l_log_statement,
1299 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1300 ' Before Calling Get Route Mtl Req, Route Id: ' || l_route_id
1301 );
1302 fnd_log.string
1303 (
1304 l_log_statement,
1305 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1306 ' Before Calling Get Route Mtl Req, Instance Id: ' || l_instance_id
1307 );
1308
1309 END IF;
1310
1311 IF (l_log_procedure >= l_log_current_level) THEN
1312 fnd_log.string
1313 (
1314 l_log_procedure,
1315 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1316 'Before calling ahl ltp mtl req pvt.Get Route Mtl Req'
1317 );
1318
1319 END IF;
1320
1321 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
1322 (p_api_version => l_api_version,
1323 p_init_msg_list => p_init_msg_list,
1324 p_validation_level => p_validation_level,
1325 x_return_status => l_return_status,
1326 x_msg_count => l_msg_count,
1327 x_msg_data => l_msg_data,
1328 p_route_id => l_route_id,
1329 p_mr_route_id => l_visit_tasks_rec.mr_route_id,
1330 p_item_instance_id => l_visit_tasks_rec.instance_id, /*B6271339 - sowsubra*/
1331 p_visit_id => p_visit_id, -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
1332 p_requirement_date => l_requirement_date,
1333 p_request_type => 'PLANNED',
1334 x_route_mtl_req_tbl => l_route_mtl_req_tbl);
1335 END IF; --MR Route not null
1336
1337 IF (l_log_procedure >= l_log_current_level) THEN
1338 fnd_log.string
1339 (
1340 l_log_procedure,
1341 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1342 'After calling ahl ltp mtl req pvt.Get Route Mtl Req, Return Status : '|| l_return_status
1343 );
1344 END IF;
1345 -- Check Error Message stack.
1346 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1347 l_msg_count := FND_MSG_PUB.count_msg;
1348 IF l_msg_count > 0 THEN
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF;
1351 END IF;
1352
1353 IF (l_log_statement >= l_log_current_level)THEN
1354 fnd_log.string
1355 (
1356 l_log_statement,
1357 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1358 ' After Calling Get Route Mtl Req, l_route_mtl_req_tbl COUNT: ' || l_route_mtl_req_tbl.COUNT
1359 );
1360
1361 END IF;
1362 -- Create planned item in schedule material entity
1363 IF l_route_mtl_req_tbl.COUNT > 0 THEN
1364 --Loop through
1365 FOR i IN l_route_mtl_req_tbl.FIRST..l_route_mtl_req_tbl.LAST
1366 LOOP
1367 --Call insert procedure
1368 Insert_Planned_Matrls(
1369 p_visit_id => p_visit_id,
1370 p_visit_task_id => l_visit_tasks_rec.visit_task_id,
1371 p_task_start_date => l_visit_tasks_rec.start_date_time,
1372 p_inventory_item_id => l_route_mtl_req_tbl(i).inventory_item_id,
1373 p_requested_quantity => l_route_mtl_req_tbl(i).quantity,
1374 p_uom_code => l_route_mtl_req_tbl(i).uom_code,
1375 p_item_group_id => l_route_mtl_req_tbl(i).item_group_id,
1376 p_rt_oper_material_id => l_route_mtl_req_tbl(i).rt_oper_material_id,
1377 p_position_path_id => l_route_mtl_req_tbl(i).position_path_id,
1378 p_relationship_id => l_route_mtl_req_tbl(i).relationship_id,
1379 p_mr_route_id => l_visit_tasks_rec.mr_route_id,
1380 p_item_comp_detail_id => l_route_mtl_req_tbl(i).item_comp_detail_id,
1381 p_inv_master_org_id => l_visit_tasks_rec.organization_id,
1382 --SURRKUMA :: Service Bulletin :: 07-Jun-2011
1383 --Added params mc_header_id and position_key to support Position based requirement
1384 p_mc_header_id => l_route_mtl_req_tbl(i).mc_header_id,
1385 p_position_key => l_route_mtl_req_tbl(i).position_key,
1386 x_return_status => l_return_status,
1387 x_msg_count => l_msg_count,
1388 x_msg_data => l_msg_data );
1389 --
1390 END LOOP;
1391 END IF; --l_route_mtl_req_tbl
1392 END IF;
1393
1394 IF (l_log_procedure >= l_log_current_level) THEN
1395 fnd_log.string
1396 (
1397 l_log_procedure,
1398 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1399 'After calling Insert Planned Materials, Return Status : '|| l_return_status
1400 );
1401 END IF;
1402
1403 -- Check Error Message stack.
1404 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1405 l_msg_count := FND_MSG_PUB.count_msg;
1406 IF l_msg_count > 0 THEN
1407 RAISE FND_API.G_EXC_ERROR;
1408 END IF;
1409 END IF;
1410
1411
1412 -- Standard check of p_commit
1413 IF FND_API.TO_BOOLEAN(p_commit) THEN
1414 COMMIT WORK;
1415 END IF;
1416
1417 IF (l_log_procedure >= l_log_current_level)THEN
1418 fnd_log.string
1419 (
1420 l_log_procedure,
1421 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create Task Materials.end',
1422 'At the end of PLSQL procedure'
1423 );
1424 END IF;
1425 EXCEPTION
1426 WHEN FND_API.G_EXC_ERROR THEN
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 ROLLBACK TO Create_Task_Materials;
1429 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1430 p_data => x_msg_data,
1431 p_encoded => fnd_api.g_false);
1432
1433
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436 ROLLBACK TO Create_Task_Materials;
1437 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1438 p_data => x_msg_data,
1439 p_encoded => fnd_api.g_false);
1440
1441
1442 WHEN OTHERS THEN
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444 ROLLBACK TO Create_Task_Materials;
1445 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1446 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1447 p_procedure_name => 'Create_Task_Materials',
1448 p_error_text => SUBSTR(SQLERRM,1,500));
1449 END IF;
1450 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1451 p_data => x_msg_data,
1452 p_encoded => fnd_api.g_false);
1453
1454
1455 END Create_Task_Materials;
1456 --
1457 -- Start of Comments --
1458 -- Procedure name : Modify_Visit_Task_Matrls
1459 -- Type : Private
1460 -- Function : This procedure modifies materials information associated to scheduled
1461 -- visit, which are defined at Route Operation and Disposition level
1462 -- Pre-reqs :
1463 -- Parameters :
1464 --
1465 -- Standard IN Parameters :
1466 -- p_api_version IN NUMBER Required
1467 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1468 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1469 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1470 -- Based on this flag, the API will set the default attributes.
1471 -- p_module_type In VARCHAR2 Default NULL
1472 -- This will be null.
1473 -- Standard out Parameters :
1474 -- x_return_status OUT VARCHAR2 Required
1475 -- x_msg_count OUT NUMBER Required
1476 -- x_msg_data OUT VARCHAR2 Required
1477 --
1478 -- Modify_Visit_Task_Matrls Parameters :
1479 -- p_visit_id IN NUMBER,Required
1480 -- p_visit_task_id IN NUMBER
1481 -- p_start_time IN DATE
1482 -- p_ord_id IN NUMBER
1483 -- p_ue_id IN NUMBER
1484 --
1485 PROCEDURE Modify_Visit_Task_Matrls (
1486 p_api_version IN NUMBER,
1487 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1488 p_commit IN VARCHAR2 := FND_API.g_false,
1489 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1490 p_visit_id IN NUMBER,
1491 p_visit_task_id IN NUMBER := NULL,
1492 p_start_time IN DATE := NULL,
1493 p_org_id IN NUMBER := NULL,
1494 p_ue_id IN NUMBER := NULL, --Added by SURRKUMA for Service Bulletin on 17-Jun-11
1495 x_return_status OUT NOCOPY VARCHAR2,
1496 x_msg_count OUT NOCOPY NUMBER,
1497 x_msg_data OUT NOCOPY VARCHAR2)
1498 IS
1499
1500 -- Modified by Surrkuma for SB on 29-Jun-2011
1501 -- Modified the Cursor to get visit tasks whose parent task's ue_id is given
1502 CURSOR Get_Visit_Tasks_Cur(c_visit_id IN NUMBER)
1503 IS
1504 SELECT vs.visit_id,
1505 vs.organization_id,
1506 vt.visit_task_id,
1507 vt.mr_route_id,
1508 vt.instance_id,
1509 NVL(vt.start_date_time,vs.start_date_time) start_date_time,
1510 mr.route_id
1511 FROM ahl_visits_vl vs,
1512 ahl_visit_tasks_vl vt,
1513 ahl_mr_routes_app_v mr
1514 WHERE vs.visit_id = vt.visit_id
1515 AND vt.mr_route_id = mr.mr_route_id
1516 AND vt.unit_effectivity_id = nvl2(p_ue_id,-1,vt.unit_effectivity_id)
1517 AND vs.visit_id = c_visit_id
1518 UNION
1519 SELECT vs.visit_id,
1520 vs.organization_id,
1521 vt.visit_task_id,
1522 vt.mr_route_id,
1523 vt.instance_id,
1524 NVL(vt.start_date_time,vs.start_date_time) start_date_time,
1525 mr.route_id
1526 FROM ahl_visits_vl vs,
1527 ahl_visit_tasks_vl vt,
1528 ahl_mr_routes_app_v mr
1529 WHERE vs.visit_id = vt.visit_id
1530 AND vt.mr_route_id = mr.mr_route_id
1531 AND vt.unit_effectivity_id IN
1532 (SELECT tsk2.unit_effectivity_id
1533 FROM ahl_visit_tasks_b tsk1,
1534 ahl_visit_tasks_b tsk2
1535 WHERE tsk1.unit_effectivity_id = p_ue_id
1536 AND tsk1.visit_task_id = tsk2.originating_task_id)
1537 AND vs.visit_id = c_visit_id;
1538
1539 -- yazhou 17-May-2006 starts
1540 -- bug fix#5232544
1541
1542 --Retrieve visit materials
1543 -- AnRaj: Added the condition for picking up materials for tasks in status DELETED also
1544 -- for soft deleting materials of deleted tasks from schedule materials table.
1545 -- Cursor modified by surrkuma on 15-JUL-2010 for bug 9901811
1546 CURSOR Deleted_Items_Cur (c_visit_id IN NUMBER) IS
1547 SELECT asm.visit_id,
1548 asm.scheduled_material_id,
1549 asm.object_version_number,
1550 asm.scheduled_quantity,
1551 asm.scheduled_date,
1552 asm.visit_task_id,
1553 asm.inventory_item_id,
1554 asm.organization_id,
1555 asm.requested_quantity,
1556 asm.uom,
1557 asm.requested_date,
1558 mtl.concatenated_segments
1559 FROM ahl_visit_tasks_b tsk,
1560 ahl_schedule_materials asm,
1561 mtl_system_items_kfv mtl
1562 WHERE asm.visit_id = C_VISIT_ID
1563 AND asm.visit_task_id = tsk.visit_task_id
1564 AND tsk.status_code ='DELETED'
1565 AND asm.status <> 'DELETED'
1566 AND mtl.inventory_item_id = asm.inventory_item_id
1567 AND mtl.organization_id = asm.organization_id;
1568
1569 -- Cursor modified by surrkuma on 15-JUL-2010 for bug 9901811
1570 -- Modified by surrkuma on 28-Sep-2011 for Service Bulletin
1571 CURSOR Planned_Items_cur (c_visit_task_id IN NUMBER, c_rt_oper_material_id IN NUMBER, c_position_key IN NUMBER) IS
1572 SELECT asm.requested_quantity,
1573 asm.scheduled_material_id,
1574 asm.object_version_number,
1575 asm.visit_id,
1576 asm.visit_task_id,
1577 asm.inventory_item_id,
1578 asm.organization_id,
1579 asm.uom,
1580 asm.requested_date,
1581 asm.scheduled_date,
1582 mtl.concatenated_segments
1583 FROM ahl_schedule_materials asm,
1584 mtl_system_items_kfv mtl
1585 WHERE asm.visit_task_id = c_visit_task_id
1586 AND asm.rt_oper_material_id = c_rt_oper_material_id
1587 AND NVL(asm.STATUS, 'X') = 'ACTIVE'
1588 AND mtl.inventory_item_id = asm.inventory_item_id
1589 AND mtl.organization_id = asm.organization_id
1590 AND NVL(asm.position_key, 0) = NVL(c_position_key, 0);
1591
1592 l_Deleted_Items_Rec Deleted_Items_Cur%rowtype;
1593 l_requested_qty NUMBER;
1594
1595 -- yazhou 17-May-2006 ends
1596
1597 -- Added to fix the unlogged bug where org id was being incorrectly updated to inventory item's org id
1598 -- when a task was being deleted.
1599 l_visit_org_id NUMBER;
1600 CURSOR Get_Visit_Org_Id_Cur(c_visit_id IN NUMBER)IS
1601 SELECT organization_id
1602 FROM ahl_visits_b
1603 WHERE visit_id = C_VISIT_ID
1604 AND ( organization_id IS NOT NULL
1605 OR start_date_time IS NOT NULL
1606 OR department_id IS NOT NULL);
1607
1608 -- Cursor added by surrkuma on 15-JUL-2010 for Bug 9901811
1609 CURSOR Item_exists_in_org_Cur(c_item_id IN NUMBER, c_org_id IN NUMBER ) IS
1610 SELECT 1
1611 FROM mtl_system_items_kfv mtl
1612 WHERE mtl.inventory_item_id = c_item_id
1613 AND mtl.organization_id = c_org_id;
1614
1615 -- surrkuma :: Bug #9901811 :: 15-JUL-2010
1616 -- Added this cursor to get top priority item id from item associations
1617 CURSOR Get_top_priority_item_id_cur(C_ITEM_GROUP_ID IN NUMBER,
1618 C_ORG_ID IN NUMBER)
1619 IS
1620 SELECT it.inventory_item_id
1621 FROM ahl_item_associations_vl it,
1622 mtl_system_items_vl mt
1623 WHERE it.inventory_item_id = mt.inventory_item_id
1624 AND item_group_id = C_ITEM_GROUP_ID
1625 AND mt.organization_id = C_ORG_ID
1626 AND it.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1627 ORDER BY priority;
1628
1629 -- Added by SURRKUMA for Service Bulletin on 27-Jun-2011
1630 -- Cursor to check whether WIP requirement exists for ASM record
1631 CURSOR Item_exists_in_wip_cur(c_scheduled_material_id IN NUMBER)
1632 IS
1633 SELECT 1
1634 FROM wip_requirement_operations wip,
1635 ahl_schedule_materials asm,
1636 ahl_workorders wo
1637 WHERE wo.visit_task_id = asm.visit_task_id
1638 AND wo.wip_entity_id = wip.wip_entity_id
1639 AND asm.operation_sequence = wip.operation_seq_num
1640 AND asm.inventory_item_id = wip.inventory_item_id
1641 AND asm.organization_id = wip.organization_id
1642 AND asm.scheduled_material_id = c_scheduled_material_id;
1643
1644 --Standard local variables
1645 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Visit_Task_Matrls';
1646 l_api_version CONSTANT NUMBER := 1.0;
1647 l_msg_data VARCHAR2(2000);
1648 l_return_status VARCHAR2(1);
1649 l_msg_count NUMBER;
1650 j NUMBER := 0;
1651 l_visit_tasks_rec Get_visit_tasks_cur%ROWTYPE;
1652 l_route_mtl_req_tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1653 l_Planned_Items_Rec Planned_Items_cur%ROWTYPE;
1654 l_requirement_date DATE;
1655 l_Visit_Task_Route_Tbl Visit_Task_Route_Tbl_Type;
1656 i_x NUMBER;
1657 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
1658 -- Variables added by surrkuma on 15-JUL-2010 for bug 9901811
1659 l_unsched_mtl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1660 l_unsched_mtl_index NUMBER := 1;
1661 --Variables added by surrkuma on 27-Jun-2011 for SB
1662 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
1663 l_req_mtl_index NUMBER := 1;
1664 l_mr_id NUMBER; -- Added by tchimira for VWPE 12673125
1665 l_unit_eff_id NUMBER; -- Added by tchimira for VWPE 12673125
1666 l_instance_id NUMBER; -- Added by tchimira for VWPE 12673125
1667
1668 BEGIN
1669
1670 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1671 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin',
1672 'At the start of PLSQL procedure. p_visit_id = ' || p_visit_id ||
1673 ', p_visit_task_id = ' || p_visit_task_id ||
1674 ', p_start_time = ' || p_start_time ||
1675 ', p_org_id = ' || p_org_id);
1676 END IF;
1677 -- Standard start of API savepoint
1678 SAVEPOINT Modify_Visit_Task_Matrls;
1679 -- Initialize message list if p_init_msg_list is set to TRUE
1680 IF FND_API.To_Boolean( p_init_msg_list) THEN
1681 FND_MSG_PUB.Initialize;
1682 END IF;
1683 -- Initialize API return status to success
1684 x_return_status := FND_API.G_RET_STS_SUCCESS;
1685 -- yazhou 17-May-2006 starts
1686 -- bug fix#5232544
1687 -- Delete all the requirements belong to tasks in DELETED status
1688 OPEN Deleted_Items_cur(p_visit_id);
1689 LOOP
1690 FETCH Deleted_Items_cur INTO l_Deleted_Items_Rec;
1691 EXIT WHEN Deleted_Items_cur%NOTFOUND;
1692 --
1693 IF l_Deleted_Items_Rec.scheduled_material_id IS NOT NULL THEN
1694 -- Added by surrkuma on 15-JUL-2010 for bug 9901811
1695 -- Add this requirement to l_unsched_mtl_tbl for ATP unscheduling
1696 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1697 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1698 'l_unsched_mtl_index = ' || l_unsched_mtl_index ||
1699 ', Adding Requirement with id ' || l_Deleted_Items_Rec.scheduled_material_id || ' to l_unsched_mtl_tbl for Unscheduling.');
1700 END IF;
1701 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_id := l_Deleted_Items_Rec.visit_id;
1702 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_task_id := l_Deleted_Items_Rec.visit_task_id;
1703 l_unsched_mtl_tbl(l_unsched_mtl_index).inventory_item_id := l_Deleted_Items_Rec.inventory_item_id;
1704 l_unsched_mtl_tbl(l_unsched_mtl_index).item_description := l_Deleted_Items_Rec.concatenated_segments;
1705 l_unsched_mtl_tbl(l_unsched_mtl_index).organization_id := l_Deleted_Items_Rec.organization_id;
1706 l_unsched_mtl_tbl(l_unsched_mtl_index).schedule_material_id := l_Deleted_Items_Rec.scheduled_material_id;
1707 l_unsched_mtl_tbl(l_unsched_mtl_index).required_quantity := l_Deleted_Items_Rec.requested_quantity;
1708 l_unsched_mtl_tbl(l_unsched_mtl_index).primary_uom_code := l_Deleted_Items_Rec.uom;
1709 l_unsched_mtl_tbl(l_unsched_mtl_index).requested_date := l_Deleted_Items_Rec.requested_date;
1710 l_unsched_mtl_index := l_unsched_mtl_index + 1;
1711 -- End addition by surrkuma on 15-JUL-2010 for bug 9901811
1712 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1713 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1714 'Updating the status to DELETED for Material Requirement Id ' || l_Deleted_Items_Rec.scheduled_material_id);
1715 END IF;
1716
1717 UPDATE ahl_schedule_materials
1718 SET requested_quantity =0,
1719 status = 'DELETED',
1720 object_version_number = l_Deleted_Items_Rec.object_version_number + 1
1721 WHERE scheduled_material_id = l_Deleted_Items_Rec.scheduled_material_id ;
1722 END IF;
1723 END LOOP;
1724 CLOSE Deleted_Items_cur;
1725 -- yazhou 17-May-2006 ends
1726
1727 -- AnRaj : Added to fix the unlogged bug where org id was being incorrectly updated to inventory item's org id
1728 -- when a task was being deleted.
1729 -- START of Fix
1730 OPEN Get_Visit_Org_Id_Cur(p_visit_id);
1731 FETCH Get_Visit_Org_Id_Cur INTO l_visit_org_id;
1732 CLOSE Get_Visit_Org_Id_Cur;
1733 -- If the visit does not have a org id, can do an early exit - no need to insert the materials again
1734 IF l_visit_org_id IS NULL THEN
1735 RETURN;
1736 ELSE
1737 IF p_org_id IS NOT NULL THEN
1738 l_visit_org_id := p_org_id;
1739 END IF;
1740 END IF;
1741 -- END of Fix
1742
1743
1744 IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM) THEN
1745 OPEN Get_Visit_Tasks_Cur(p_visit_id);
1746 i_x := 0;
1747 LOOP
1748 FETCH Get_Visit_Tasks_Cur INTO l_visit_tasks_rec;
1749 EXIT WHEN Get_Visit_Tasks_Cur%NOTFOUND;
1750 IF l_visit_tasks_rec.route_id IS NOT NULL THEN
1751 l_Visit_Task_Route_Tbl(i_x).visit_task_id := l_visit_tasks_rec.visit_task_id;
1752 l_Visit_Task_Route_Tbl(i_x).mr_route_id := l_visit_tasks_rec.mr_route_id;
1753 l_Visit_Task_Route_Tbl(i_x).route_id := l_visit_tasks_rec.route_id;
1754 l_Visit_Task_Route_Tbl(i_x).instance_id := l_visit_tasks_rec.instance_id;
1755 l_Visit_Task_Route_Tbl(i_x).task_start_date := l_visit_tasks_rec.start_date_time;
1756 i_x := i_x + 1;
1757 END IF;
1758 END LOOP;
1759 CLOSE Get_Visit_Tasks_Cur;
1760 END IF;
1761
1762 IF (l_log_statement >= l_log_current_level)THEN
1763 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1764 'l_Visit_Task_Route_Tbl.COUNT: ' || l_Visit_Task_Route_Tbl.COUNT);
1765 END IF;
1766
1767 IF l_Visit_Task_Route_Tbl.COUNT > 0 THEN
1768 FOR i IN l_Visit_Task_Route_Tbl.FIRST..l_Visit_Task_Route_Tbl.LAST
1769 LOOP
1770 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1771 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Inside the loop of tasks, i = ' || i ||
1772 ', Route Id: ' || l_Visit_Task_Route_Tbl(i).route_id ||
1773 ', Instance Id: ' || l_Visit_Task_Route_Tbl(i).instance_id ||
1774 ', Task Start Time: ' || l_Visit_Task_Route_Tbl(i).task_start_date);
1775 END IF;
1776
1777 IF (l_Visit_Task_Route_Tbl(i).task_start_date IS NOT NULL AND
1778 TRUNC(l_Visit_Task_Route_Tbl(i).task_start_date) < TRUNC(SYSDATE) )THEN
1779 l_requirement_date := sysdate;
1780 ELSE
1781 l_requirement_date := l_Visit_Task_Route_Tbl(i).task_start_date;
1782 END IF;
1783
1784 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1785 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1786 'Before calling AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req');
1787 END IF;
1788
1789 --Call to get items
1790 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
1791 (p_api_version => l_api_version,
1792 p_init_msg_list => p_init_msg_list,
1793 p_validation_level => p_validation_level,
1794 x_return_status => l_return_status,
1795 x_msg_count => l_msg_count,
1796 x_msg_data => l_msg_data,
1797 p_route_id => l_Visit_Task_Route_Tbl(i).route_id,
1798 p_mr_route_id => l_Visit_Task_Route_Tbl(i).mr_route_id,
1799 p_item_instance_id => l_Visit_Task_Route_Tbl(i).instance_id,
1800 --Added by surrkuma on 07-Jun-2011 for Service Bulletin
1801 p_visit_id => p_visit_id,
1802 p_requirement_date => l_requirement_date,
1803 p_request_type => 'PLANNED',
1804 x_route_mtl_req_tbl => l_route_mtl_req_tbl);
1805 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1806 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1807 'After calling AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req, Return Status: ' || l_return_status);
1808 END IF;
1809 -- Check Error Message stack.
1810 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1811 l_msg_count := FND_MSG_PUB.count_msg;
1812 IF l_msg_count > 0 THEN
1813 RAISE FND_API.G_EXC_ERROR;
1814 END IF;
1815 END IF;
1816
1817 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1818 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1819 'After Calling AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req, l_route_mtl_req_tbl.COUNT = ' || l_route_mtl_req_tbl.COUNT);
1820 END IF;
1821
1822 IF l_route_mtl_req_tbl.COUNT > 0 THEN
1823 FOR j IN l_route_mtl_req_tbl.FIRST..l_route_mtl_req_tbl.LAST
1824 LOOP
1825 l_requested_qty := null;
1826 OPEN Planned_Items_cur(l_Visit_Task_Route_Tbl(i).visit_task_id,l_route_mtl_req_tbl(j).rt_oper_material_id, l_route_mtl_req_tbl(j).position_key);
1827 FETCH Planned_Items_cur INTO l_Planned_Items_Rec;
1828 IF Planned_Items_cur%found THEN
1829 IF l_Planned_Items_Rec.scheduled_material_id IS NOT NULL THEN
1830 -- surrkuma :: Bug #9901811 :: 15-JUL-2010
1831 -- Added the below IF block to get the inventory_item_id for item groups
1832 IF (l_route_mtl_req_tbl(j).item_group_id IS NOT NULL) THEN
1833 --Get from item associations
1834 OPEN Get_top_priority_item_id_cur(l_route_mtl_req_tbl(j).item_group_id,l_visit_org_id);
1835 FETCH Get_top_priority_item_id_cur INTO l_route_mtl_req_tbl(j).inventory_item_id;
1836 CLOSE Get_top_priority_item_id_cur;
1837 END IF;
1838 -- Begin Changes by surrkuma on 15-JUL-2010 for Bug 9901811
1839 -- When Visit or Task is rescheduled, retain the ASM record, updating its Date or Org.
1840 -- Avoid soft deleting and creating new records.
1841 -- No need to automatically Reschedule or Unschedule for ATP
1842 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1843 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1844 'l_Planned_Items_Rec.inventory_item_id = ' || l_Planned_Items_Rec.inventory_item_id ||
1845 ', l_route_mtl_req_tbl(j).inventory_item_id = ' || l_route_mtl_req_tbl(j).inventory_item_id ||
1846 ', l_Planned_Items_Rec.organization_id = ' || l_Planned_Items_Rec.organization_id ||
1847 ', l_visit_org_id = ' || l_visit_org_id ||
1848 ', l_Planned_Items_Rec.requested_date = ' || l_Planned_Items_Rec.requested_date ||
1849 ', l_Visit_Task_Route_Tbl(i).task_start_date = ' || l_Visit_Task_Route_Tbl(i).task_start_date);
1850 END IF;
1851 -- See if the existing record has to be modified
1852 IF ((NVL(l_Planned_Items_Rec.inventory_item_id, -99) <> NVL(l_route_mtl_req_tbl(j).inventory_item_id, -99)) OR
1853 (NVL(l_Planned_Items_Rec.organization_id, -99) <> NVL(l_visit_org_id, -99)) OR
1854 (trunc(NVL(l_Planned_Items_Rec.requested_date, sysdate)) <> trunc(NVL(l_Visit_Task_Route_Tbl(i).task_start_date, sysdate)))) THEN
1855 -- Significant changeable attributes have changed: Update the record
1856 -- Modified by surrkuma on 15-JUL-2010 for Bug 9901811
1857 -- If the item is not defined in the new Org, flag it as DELETED, don't modify it
1858 OPEN Item_exists_in_org_Cur(c_item_id => l_route_mtl_req_tbl(j).inventory_item_id, c_org_id => l_visit_org_id);
1859 FETCH Item_exists_in_org_Cur INTO i_x;
1860 IF (Item_exists_in_org_Cur%NOTFOUND) THEN
1861 -- Item is not applicable in the New Org: Flag the ASM record as DELETED
1862 UPDATE ahl_schedule_materials
1863 SET STATUS = 'DELETED',
1864 object_version_number = l_Planned_Items_Rec.object_version_number + 1,
1865 last_update_date = sysdate,
1866 last_updated_by = Fnd_Global.USER_ID,
1867 last_update_login = Fnd_Global.LOGIN_ID
1868 WHERE scheduled_material_id = l_Planned_Items_Rec.scheduled_material_id;
1869 ELSE
1870 -- Begin Changes by surrkuma on 27-Jun-2011 for SB
1871 OPEN Item_exists_in_wip_cur(l_Planned_Items_Rec.scheduled_material_id);
1872 FETCH Item_exists_in_wip_cur INTO i_x;
1873 IF (Item_exists_in_wip_cur%FOUND AND (NVL(l_Planned_Items_Rec.inventory_item_id, -99) <> NVL(l_route_mtl_req_tbl(j).inventory_item_id, -99))) THEN
1874 -- WIP requirement exists for the record
1875 l_req_material_tbl(l_req_mtl_index).schedule_material_id := l_Planned_Items_Rec.scheduled_material_id;
1876 l_req_material_tbl(l_req_mtl_index).object_version_number := l_Planned_Items_Rec.object_version_number;
1877 l_req_material_tbl(l_req_mtl_index).inventory_item_id := l_route_mtl_req_tbl(j).inventory_item_id;
1878 l_req_material_tbl(l_req_mtl_index).requested_date := l_Visit_Task_Route_Tbl(i).task_start_date;
1879 l_req_material_tbl(l_req_mtl_index).operation_flag := 'I';
1880 l_req_mtl_index := l_req_mtl_index + 1;
1881 ELSE
1882 -- No WIP requirement exists for the record.
1883 -- Update the ASM record with the changed attributes
1884 UPDATE ahl_schedule_materials
1885 SET inventory_item_id = l_route_mtl_req_tbl(j).inventory_item_id,
1886 organization_id = l_visit_org_id,
1887 requested_date = trunc(l_Visit_Task_Route_Tbl(i).task_start_date),
1888 object_version_number = l_Planned_Items_Rec.object_version_number + 1,
1889 last_update_date = sysdate,
1890 last_updated_by = Fnd_Global.USER_ID,
1891 last_update_login = Fnd_Global.LOGIN_ID
1892 WHERE scheduled_material_id = l_Planned_Items_Rec.scheduled_material_id ;
1893 END IF;
1894 CLOSE Item_exists_in_wip_cur;
1895 -- End of changes by surrkuma for SB
1896 END IF;
1897 CLOSE Item_exists_in_org_Cur;
1898 -- End changes by surrkuma on 15-JUL-2010 for Bug 9901811
1899 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1900 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1901 'Updated ahl_schedule_materials for scheduled_material_id = ' || l_Planned_Items_Rec.scheduled_material_id);
1902 END IF;
1903 -- No need to automatically Reschedule or Unschedule for ATP
1904 ELSE
1905 -- No significant changes: No need to update
1906 NULL;
1907 END IF;
1908 END IF; -- scheduled_material_id IS NOT NULL
1909 ELSE
1910 -- The requirement does not already exist: Create a new one
1911 l_requested_qty := l_route_mtl_req_tbl(j).quantity;
1912 -- End Changes by surrkuma on 15-JUL-2010 for Bug 9901811
1913 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1914 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1915 'About to call Insert_Planned_Matrls with p_visit_id = ' || p_visit_id ||
1916 ', p_visit_task_id = ' || l_Visit_Task_Route_Tbl(i).visit_task_id ||
1917 ', p_task_start_date = ' || l_Visit_Task_Route_Tbl(i).task_start_date ||
1918 ', p_inventory_item_id = ' || l_route_mtl_req_tbl(j).inventory_item_id ||
1919 ', p_requested_quantity = ' || l_requested_qty ||
1920 ', p_uom_code = ' || l_route_mtl_req_tbl(j).uom_code ||
1921 ', p_rt_oper_material_id = ' || l_route_mtl_req_tbl(j).rt_oper_material_id ||
1922 ', p_inv_master_org_id = ' || l_visit_org_id);
1923 END IF;
1924 Insert_Planned_Matrls(
1925 p_visit_id => p_visit_id,
1926 p_visit_task_id => l_Visit_Task_Route_Tbl(i).visit_task_id,
1927 p_task_start_date => l_Visit_Task_Route_Tbl(i).task_start_date,
1928 p_inventory_item_id => l_route_mtl_req_tbl(j).inventory_item_id,
1929 p_requested_quantity => l_requested_qty,
1930 p_uom_code => l_route_mtl_req_tbl(j).uom_code,
1931 p_item_group_id => l_route_mtl_req_tbl(j).item_group_id,
1932 p_rt_oper_material_id => l_route_mtl_req_tbl(j).rt_oper_material_id,
1933 p_position_path_id => l_route_mtl_req_tbl(j).position_path_id,
1934 p_relationship_id => l_route_mtl_req_tbl(j).relationship_id,
1935 p_mr_route_id => l_Visit_Task_Route_Tbl(i).mr_route_id,
1936 p_item_comp_detail_id => l_route_mtl_req_tbl(j).item_comp_detail_id,
1937 -- AnRaj: changed the paramter, for fixing bug where org id was being incorrectly updated
1938 p_inv_master_org_id => l_visit_org_id ,
1939 --SURRKUMA :: Service Bulletin :: 07-Jun-2011
1940 --Added params mc_header_id and position_key to support Position based requirement
1941 p_mc_header_id => l_route_mtl_req_tbl(j).mc_header_id,
1942 p_position_key => l_route_mtl_req_tbl(j).position_key,
1943 x_return_status => l_return_status,
1944 x_msg_count => l_msg_count,
1945 x_msg_data => l_msg_data );
1946
1947 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1948 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1949 'Returned from call to Insert_Planned_Matrls. x_return_status = ' || l_return_status);
1950 END IF;
1951
1952 -- Changed by surrkuma: Moved the following lines to within the loop
1953 -- Check Error Message stack.
1954 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1955 l_msg_count := FND_MSG_PUB.count_msg;
1956 IF l_msg_count > 0 THEN
1957 RAISE FND_API.G_EXC_ERROR;
1958 END IF;
1959 END IF;
1960 END IF; -- Planned_Items_cur FOUND or NOT
1961 CLOSE Planned_Items_cur;
1962 END LOOP; -- Loop j on l_route_mtl_req_tbl (All materials for the given Route)
1963 -- Begin changes by surrkuma for SB on 28-Jun-2011
1964 -- Calling production API to change the item in ASM and WIP requirement
1965 IF l_req_material_tbl.COUNT > 0 THEN
1966 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1967 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1968 'Before Calling AHL_PP_MATERIALS_PVT.Process_Material_Request');
1969 END IF;
1970 AHL_PP_MATERIALS_PVT.Process_Material_Request(
1971 p_api_version => l_api_version,
1972 p_x_req_material_tbl => l_req_material_tbl,
1973 x_return_status => l_return_status,
1974 x_msg_count => l_msg_count,
1975 x_msg_data => l_msg_data
1976 );
1977 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1978 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1979 'Returned from call to AHL_PP_MATERIALS_PVT.Process_Material_Request. x_return_status = ' || l_return_status);
1980 END IF;
1981 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1982 l_msg_count := FND_MSG_PUB.count_msg;
1983 IF l_msg_count > 0 THEN
1984 RAISE FND_API.G_EXC_ERROR;
1985 END IF;
1986 END IF;
1987 END IF; -- l_req_material_tbl.COUNT > 0
1988 -- End of changes by surrkuma for SB
1989 END IF; -- l_route_mtl_req_tbl.COUNT > 0
1990 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1991 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1992 'Outside the Route Materials loop, Return Status: ' || l_return_status);
1993 END IF;
1994
1995 END LOOP; -- Loop i on l_Visit_Task_Route_Tbl (All routes/tasks for the Visit)
1996 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1997 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1998 'Completed all tasks - Outside the Tasks loop.');
1999 END IF;
2000 END IF; -- l_Visit_Task_Route_Tbl.COUNT > 0
2001
2002 -- Added by surrkuma on 15-JUL-2010 for bug 9901811
2003 IF (l_unsched_mtl_tbl.COUNT > 0) THEN
2004 -- Call ATP to unschedule the soft Deleted requirements
2005 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2006 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2007 'Before calling AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials. l_unsched_mtl_tbl.COUNT = ' || l_unsched_mtl_tbl.COUNT);
2008 END IF;
2009 AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials(p_api_version => 1.0,
2010 p_deleted_matrl_tbl => l_unsched_mtl_tbl,
2011 x_return_status => l_return_status,
2012 x_msg_count => l_msg_count,
2013 x_msg_data => l_msg_data);
2014 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2015 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2016 'After returning from AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials, l_return_status = ' || l_return_status);
2017 END IF;
2018 END IF;
2019 -- End addition by surrkuma on 15-JUL-2010 for bug 9901811
2020
2021 -- Serial Number Reservation Enhancement Changes.
2022 -- If the date of the visit has changed then all reservation dates also should change accordingly
2023 Modify_Visit_Reservations (
2024 p_visit_id => p_visit_id,
2025 x_return_status => l_return_status);
2026
2027 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2028 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2029 'After calling Modify_Visit_Reservations, Return Status: ' || l_return_status);
2030 END IF;
2031
2032 -- Check Error Message stack.
2033 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2034 l_msg_count := FND_MSG_PUB.count_msg;
2035 RAISE FND_API.G_EXC_ERROR;
2036 END IF;
2037
2038 -- Check Error Message stack.
2039 x_msg_count := FND_MSG_PUB.count_msg;
2040 IF x_msg_count > 0 THEN
2041 RAISE FND_API.G_EXC_ERROR;
2042 END IF;
2043
2044 -- Standard check of p_commit
2045 IF FND_API.TO_BOOLEAN(p_commit) THEN
2046 COMMIT WORK;
2047 END IF;
2048
2049 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2050 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
2051 'At the normal execution end of PLSQL procedure.');
2052 END IF;
2053 EXCEPTION
2054 WHEN FND_API.G_EXC_ERROR THEN
2055 x_return_status := FND_API.G_RET_STS_ERROR;
2056 ROLLBACK TO Modify_Visit_Task_Matrls;
2057 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2058 p_data => x_msg_data,
2059 p_encoded => fnd_api.g_false);
2060
2061
2062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064 ROLLBACK TO Modify_Visit_Task_Matrls;
2065 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2066 p_data => x_msg_data,
2067 p_encoded => fnd_api.g_false);
2068
2069
2070 WHEN OTHERS THEN
2071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2072 ROLLBACK TO Modify_Visit_Task_Matrls;
2073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2074 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2075 p_procedure_name => 'Modify_Visit_Task_Matrls',
2076 p_error_text => SUBSTR(SQLERRM,1,500));
2077 END IF;
2078 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2079 p_data => x_msg_data,
2080 p_encoded => fnd_api.g_false);
2081
2082
2083 END Modify_Visit_Task_Matrls;
2084 --
2085 -- Start of Comments --
2086 -- Procedure name : Unschedule_Visit_task_Items
2087 -- Type : Private
2088 -- Function : This procedure Checks any items scheduled
2089 -- which are defined at Route Operation and Disposition level
2090 -- Pre-reqs :
2091 -- Parameters :
2092 --
2093 -- Standard IN Parameters :
2094 -- p_api_version IN NUMBER Required
2095 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2096 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2097 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2098 -- Based on this flag, the API will set the default attributes.
2099 -- This will be null.
2100 -- Standard out Parameters :
2101 -- x_return_status OUT VARCHAR2 Required
2102 -- x_msg_count OUT NUMBER Required
2103 -- x_msg_data OUT VARCHAR2 Required
2104 --
2105 -- Unschedule_Visit_Task_Items Parameters :
2106 -- p_visit_id IN NUMBER, Required
2107 -- p_visit_task_id IN NUMBER, Optional
2108 --
2109
2110 PROCEDURE Unschedule_visit_Task_Items
2111 (p_api_version IN NUMBER,
2112 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2113 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2114 p_visit_id IN NUMBER,
2115 p_visit_task_id IN NUMBER := NULL,
2116 x_return_status OUT NOCOPY VARCHAR2,
2117 x_msg_count OUT NOCOPY NUMBER,
2118 x_msg_data OUT NOCOPY VARCHAR2 )
2119 IS
2120 --
2121 CURSOR check_items_cur (C_VISIT_ID IN NUMBER)
2122 IS
2123 -- AnRaj :Changed for fixing performance bug#4919562
2124 SELECT ASMT.visit_id,
2125 ASMT.visit_task_id,
2126 ASMT.scheduled_material_id schedule_material_id,
2127 decode(sign( trunc(ASMT.scheduled_date) - trunc(requested_date)), 1, ASMT.scheduled_date, null) SCHEDULED_DATE,
2128 ASMT.SCHEDULED_QUANTITY
2129 FROM AHL_SCHEDULE_MATERIALS ASMT,
2130 AHL_VISIT_TASKS_B VTSK
2131 WHERE ASMT.STATUS <> 'DELETED'
2132 AND EXISTS ( Select 1
2133 from AHL_RT_OPER_MATERIALS RTOM
2134 where RTOM.RT_OPER_MATERIAL_ID = ASMT.RT_OPER_MATERIAL_ID)
2135 AND VTSK.VISIT_ID = ASMT.VISIT_ID
2136 AND VTSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
2137 AND NVL(VTSK.STATUS_CODE,'X') <> 'DELETED'
2138 AND ASMT.VISIT_ID = C_VISIT_ID
2139 AND scheduled_date IS NOT NULL;
2140 /*
2141 SELECT visit_id,visit_task_id,schedule_material_id,
2142 scheduled_date,scheduled_quantity
2143 FROM ahl_visit_task_matrl_v
2144 WHERE visit_id = C_VISIT_ID
2145 AND scheduled_date IS NOT NULL;
2146 */
2147 --
2148 l_api_name CONSTANT VARCHAR2(30) := 'Unschedule_visit_Task_Items';
2149 l_api_version CONSTANT NUMBER := 1.0;
2150 l_return_status VARCHAR2(1);
2151 l_msg_data VARCHAR2(200);
2152 l_msg_count NUMBER;
2153 l_schedule_items_rec check_items_cur%ROWTYPE;
2154 l_req_material_rec ahl_ltp_reqst_matrl_pub.Schedule_Mr_Rec;
2155 --
2156 BEGIN
2157 --------------------Initialize ----------------------------------
2158 -- Standard Start of API savepoint
2159 SAVEPOINT Unschedule_Task_Items;
2160 -- Check if API is called in debug mode. If yes, enable debug.
2161 IF G_DEBUG='Y' THEN
2162 AHL_DEBUG_PUB.enable_debug;
2163 END IF;
2164 -- Debug info.
2165 IF G_DEBUG='Y' THEN
2166 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_reqst_matrl_pvt Unchedule Task Items ','+MAATP+');
2167 END IF;
2168 -- Standard call to check for call compatibility.
2169 IF FND_API.to_boolean(p_init_msg_list)
2170 THEN
2171 FND_MSG_PUB.initialize;
2172 END IF;
2173 -- Initialize API return status to success
2174 x_return_status := FND_API.G_RET_STS_SUCCESS;
2175 -- Initialize message list if p_init_msg_list is set to TRUE.
2176 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2177 p_api_version,
2178 l_api_name,G_PKG_NAME)
2179 THEN
2180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2181 END IF;
2182 ------------------------Start API Body ---------------------------------
2183 -- Check for any visit task items has been scheduled from MRP
2184 OPEN check_items_cur(p_visit_id);
2185 LOOP
2186 FETCH check_items_cur INTO l_schedule_items_rec;
2187 EXIT WHEN check_items_cur%NOTFOUND;
2188 IF (l_schedule_items_rec.visit_id IS NOT NULL AND
2189 l_schedule_items_rec.visit_task_id IS NOT NULL AND
2190 p_visit_task_id IS NULL) THEN
2191 -- Call Unschedule to load record into interface table
2192 --Assign the values
2193 l_req_material_rec.schedule_mat_id := l_schedule_items_rec.schedule_material_id;
2194 --
2195 -- Unschedule_Request (
2196 -- p_req_material_rec => l_req_material_rec);
2197 --
2198 ELSIF (l_schedule_items_rec.visit_id IS NOT NULL AND
2199 l_schedule_items_rec.visit_task_id IS NOT NULL AND
2200 l_schedule_items_rec.visit_task_id = p_visit_task_id ) THEN
2201 --Assign the values
2202 l_req_material_rec.schedule_mat_id := l_schedule_items_rec.schedule_material_id;
2203 --
2204 -- Unschedule_Request (
2205 -- p_req_material_rec => l_req_material_rec);
2206 --
2207 END IF;
2208 END LOOP;
2209 CLOSE check_items_cur;
2210
2211 ---------------------------End of Body---------------------------------------
2212 --Standard check to count messages
2213 l_msg_count := Fnd_Msg_Pub.count_msg;
2214
2215 IF l_msg_count > 0 THEN
2216 X_msg_count := l_msg_count;
2217 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2218 RAISE Fnd_Api.G_EXC_ERROR;
2219 END IF;
2220
2221 --Standard check for commit
2222 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2223 COMMIT;
2224 END IF;
2225 -- Debug info
2226 IF G_DEBUG='Y' THEN
2227 Ahl_Debug_Pub.debug( 'End of private api Unschedule Task Items ','+MAMRP+');
2228 -- Check if API is called in debug mode. If yes, disable debug.
2229 Ahl_Debug_Pub.disable_debug;
2230 --
2231 END IF;
2232 EXCEPTION
2233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2234 ROLLBACK TO Unschedule_Task_Items;
2235 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2236 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2237 p_count => x_msg_count,
2238 p_data => x_msg_data);
2239 IF G_DEBUG='Y' THEN
2240
2241 AHL_DEBUG_PUB.log_app_messages (
2242 x_msg_count, x_msg_data, 'ERROR' );
2243 AHL_DEBUG_PUB.debug( 'ahl_ltp_reqst_matrl_pvt. Unschedule Task Items ','+MAMRP+');
2244 -- Check if API is called in debug mode. If yes, disable debug.
2245 AHL_DEBUG_PUB.disable_debug;
2246 END IF;
2247
2248 WHEN FND_API.G_EXC_ERROR THEN
2249 ROLLBACK TO search_schedule_materials;
2250 X_return_status := FND_API.G_RET_STS_ERROR;
2251 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2252 p_count => x_msg_count,
2253 p_data => X_msg_data);
2254 IF G_DEBUG='Y' THEN
2255
2256 -- Debug info.
2257 AHL_DEBUG_PUB.log_app_messages (
2258 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2259 AHL_DEBUG_PUB.debug( 'ahl_ltp_reqst_matrl_pvt. Unschedule Task Items','+MAMRP+');
2260 -- Check if API is called in debug mode. If yes, disable debug.
2261 AHL_DEBUG_PUB.disable_debug;
2262 END IF;
2263
2264 WHEN OTHERS THEN
2265 ROLLBACK TO Unschedule_Task_Items;
2266 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2267 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2268 THEN
2269 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
2270 p_procedure_name => 'UNSCHEDULE_TASK_ITEMS',
2271 p_error_text => SUBSTR(SQLERRM,1,240));
2272 END IF;
2273 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2274 p_count => x_msg_count,
2275 p_data => X_msg_data);
2276 IF G_DEBUG='Y' THEN
2277
2278 -- Debug info.
2279 AHL_DEBUG_PUB.log_app_messages (
2280 x_msg_count, x_msg_data, 'SQL ERROR' );
2281 AHL_DEBUG_PUB.debug( 'ahl_ltp_reqst_matrl_pvt. Unschedule Task Items','+MTMRP+');
2282 -- Check if API is called in debug mode. If yes, disable debug.
2283 AHL_DEBUG_PUB.disable_debug;
2284 END IF;
2285
2286 END Unschedule_visit_Task_Items;
2287 --
2288 -- Start of Comments --
2289 -- Procedure name : Process_Planned_Materials
2290 -- Type : Private
2291 -- Function : This procedure Creates, Updates and Removes Planned materials information associated to scheduled
2292 -- visit, which are defined at Route Operation and Disposition level
2293 -- Pre-reqs :
2294 -- Parameters :
2295 --
2296 -- Standard IN Parameters :
2297 -- p_api_version IN NUMBER Required
2298 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2299 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2300 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2301 -- Based on this flag, the API will set the default attributes.
2302 -- p_module_type In VARCHAR2 Default NULL
2303 -- This will be null.
2304 -- Standard out Parameters :
2305 -- x_return_status OUT VARCHAR2 Required
2306 -- x_msg_count OUT NUMBER Required
2307 -- x_msg_data OUT VARCHAR2 Required
2308 --
2309 -- Process_Planned_Materials Parameters :
2310 --
2311 --
2312 PROCEDURE Process_Planned_Materials (
2313 p_api_version IN NUMBER,
2314 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2315 p_commit IN VARCHAR2 := FND_API.g_false,
2316 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2317 p_visit_id IN NUMBER,
2318 p_visit_task_id IN NUMBER := NULL,
2319 p_org_id IN NUMBER := NULL,
2320 p_start_date IN DATE := NULL,
2321 p_visit_status IN VARCHAR2 := NULL,
2322 p_ue_id IN NUMBER := NULL, /* Added by surrkuma for Service Bulletin on 21-Jun-11 */
2323 p_operation_flag IN VARCHAR2,
2324 x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2325 x_return_status OUT NOCOPY VARCHAR2,
2326 x_msg_count OUT NOCOPY NUMBER,
2327 x_msg_data OUT NOCOPY VARCHAR2
2328 ) IS
2329
2330 --Standard local variables
2331 l_api_name CONSTANT VARCHAR2(30) := 'Process_Planned_Materials';
2332 l_api_version CONSTANT NUMBER := 1.0;
2333 l_msg_data VARCHAR2(2000);
2334 l_return_status VARCHAR2(1);
2335 l_msg_count NUMBER;
2336 l_commit VARCHAR2(10) := FND_API.g_false;
2337 l_planned_order_flag VARCHAR2(1) := 'N';
2338 l_assoc_id NUMBER ;
2339
2340 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2341
2342 --priyan begin
2343 CURSOR get_assoc_primary_id (c_visit_id IN NUMBER)
2344 IS
2345 SELECT asso_primary_visit_id
2346 FROM ahl_visits_b
2347 WHERE visit_id = c_visit_id;
2348 --priyan end
2349 BEGIN
2350
2351 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2352 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin',
2353 'At the start of PLSQL procedure. p_visit_id = ' || p_visit_id ||
2354 ', p_visit_task_id = ' || p_visit_task_id ||
2355 ', p_operation_flag = ' || p_operation_flag);
2356 END IF;
2357
2358 -- Standard start of API savepoint
2359 SAVEPOINT Process_Planned_Materials;
2360 -- Initialize message list if p_init_msg_list is set to TRUE
2361 IF FND_API.To_Boolean( p_init_msg_list) THEN
2362 FND_MSG_PUB.Initialize;
2363 END IF;
2364
2365 -- Initialize API return status to success
2366 x_return_status := FND_API.G_RET_STS_SUCCESS;
2367 --
2368
2369 OPEN get_assoc_primary_id (p_visit_id);
2370 FETCH get_assoc_primary_id INTO l_assoc_id;
2371 CLOSE get_assoc_primary_id;
2372
2373 --priyan
2374 -- Added the check l_assoc_id IS NULL
2375 IF (p_visit_task_id IS NOT NULL AND l_assoc_id IS NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'C' ) THEN
2376 -- if create
2377 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2378 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Before calling Create_Task_Materials');
2379 END IF;
2380
2381 Create_Task_Materials (
2382 p_api_version => l_api_version,
2383 p_init_msg_list => p_init_msg_list,
2384 p_commit => l_commit,
2385 p_validation_level => p_validation_level,
2386 p_visit_id => p_visit_id,
2387 p_visit_task_id => p_visit_task_id,
2388 x_return_status => l_return_status,
2389 x_msg_count => l_msg_count,
2390 x_msg_data => l_msg_data );
2391
2392 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2393 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY,
2394 'After calling Create_Task_Materials, Return Status: ' || l_return_status);
2395 END IF;
2396 -- Check Error Message stack.
2397 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2398 l_msg_count := FND_MSG_PUB.count_msg;
2399 IF l_msg_count > 0 THEN
2400 RAISE FND_API.G_EXC_ERROR;
2401 END IF;
2402 END IF;
2403 --priyan
2404 -- Added the check l_assoc_id IS NULL
2405 ELSIF (p_visit_id IS NOT NULL AND l_assoc_id IS NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'U' ) THEN
2406 -- if update
2407 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2408 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2409 'Request for Visit Org or Start date change: Org Id: ' || p_org_id || ', Start date: ' || p_start_date);
2410 END IF;
2411
2412 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2413 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2414 'Before calling Modify_Visit_Task_Matrls');
2415 END IF;
2416
2417 Modify_Visit_Task_Matrls (
2418 p_api_version => l_api_version,
2419 p_init_msg_list => p_init_msg_list,
2420 p_commit => l_commit,
2421 p_validation_level => p_validation_level,
2422 p_visit_id => p_visit_id,
2423 p_start_time => p_start_date,
2424 p_org_id => p_org_id,
2425 p_ue_id => p_ue_id, /* Added by surrkuma for Service Bulletin on 21-Jun-11 */
2426 x_return_status => l_return_status,
2427 x_msg_count => l_msg_count,
2428 x_msg_data => l_msg_data);
2429
2430 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2431 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2432 'After calling Modify_Visit_Task_Matrls, Return Status: ' || l_return_status
2433 );
2434 END IF;
2435 -- Check Error Message stack.
2436 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2437 l_msg_count := FND_MSG_PUB.count_msg;
2438 IF l_msg_count > 0 THEN
2439 RAISE FND_API.G_EXC_ERROR;
2440 END IF;
2441 END IF;
2442
2443 -- anraj added
2444 ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'D') THEN
2445 -- delete mode called if org or dept or start date is nullified
2446 Unschedule_Visit_Materials (
2447 p_api_version => l_api_version,
2448 p_init_msg_list => p_init_msg_list,
2449 p_commit => l_commit,
2450 p_validation_level => p_validation_level,
2451 p_visit_id => p_visit_id,
2452 x_return_status => l_return_status,
2453 x_msg_count => l_msg_count,
2454 x_msg_data => l_msg_data);
2455
2456 -- Check Error Message stack.
2457 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2458 l_msg_count := FND_MSG_PUB.count_msg;
2459 IF l_msg_count > 0 THEN
2460 RAISE FND_API.G_EXC_ERROR;
2461 END IF;
2462 END IF;
2463 -- anraj
2464
2465 ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'R'
2466 AND p_visit_task_id IS NULL) THEN
2467 -- remove mode , with no task id
2468 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2469 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2470 'Before calling Remove_Visit_Task_Matrls');
2471 END IF;
2472
2473 Remove_Visit_Task_Matrls (
2474 p_api_version => l_api_version,
2475 p_init_msg_list => p_init_msg_list,
2476 p_commit => l_commit,
2477 p_validation_level => p_validation_level,
2478 p_visit_id => p_visit_id,
2479 x_planned_order_flag => l_planned_order_flag ,
2480 x_return_status => l_return_status,
2481 x_msg_count => l_msg_count,
2482 x_msg_data => l_msg_data);
2483
2484 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2485 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2486 'After calling Remove_Visit_Task_Matrls, Return Status: '|| l_return_status
2487 );
2488 END IF;
2489
2490 -- Check Error Message stack.
2491 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2492 l_msg_count := FND_MSG_PUB.count_msg;
2493 IF l_msg_count > 0 THEN
2494 RAISE FND_API.G_EXC_ERROR;
2495 END IF;
2496 END IF;
2497
2498 ELSIF (p_visit_task_id IS NOT NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'R')
2499 THEN
2500 -- Remove mode with Task ID
2501 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2502 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2503 'Before calling Remove_Visit_Task_Matrls');
2504 END IF;
2505
2506 Remove_Visit_Task_Matrls (
2507 p_api_version => l_api_version,
2508 p_init_msg_list => p_init_msg_list,
2509 p_commit => l_commit,
2510 p_validation_level => p_validation_level,
2511 p_visit_id => p_visit_id,
2512 p_visit_task_id => p_visit_task_id,
2513 x_planned_order_flag => l_planned_order_flag ,
2514 x_return_status => l_return_status,
2515 x_msg_count => l_msg_count,
2516 x_msg_data => l_msg_data);
2517
2518
2519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2520 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2521 'After calling Remove_Visit_Task_Matrls, Return Status: '|| l_return_status
2522 );
2523 END IF;
2524 -- Check Error Message stack.
2525 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2526 l_msg_count := FND_MSG_PUB.count_msg;
2527 IF l_msg_count > 0 THEN
2528 RAISE FND_API.G_EXC_ERROR;
2529 END IF;
2530 END IF;
2531
2532 ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_visit_status IN ('CLOSED', 'CANCELLED'))
2533 -- visitis in Closed or Cancelled status
2534 THEN
2535 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2536 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2537 'Before calling Update_Unplanned_Matrls');
2538 END IF;
2539
2540 Update_Unplanned_Matrls (
2541 p_api_version => l_api_version,
2542 p_init_msg_list => p_init_msg_list,
2543 p_commit => l_commit,
2544 p_validation_level => p_validation_level,
2545 p_visit_id => p_visit_id,
2546 x_return_status => l_return_status,
2547 x_msg_count => l_msg_count,
2548 x_msg_data => l_msg_data);
2549
2550 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2551 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2552 'After calling Update_Unplanned_Matrls, Return Status: '|| l_return_status
2553 );
2554 END IF;
2555 -- Check Error Message stack.
2556 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2557 l_msg_count := FND_MSG_PUB.count_msg;
2558 IF l_msg_count > 0 THEN
2559 RAISE FND_API.G_EXC_ERROR;
2560 END IF;
2561 END IF;
2562 END IF;
2563
2564 -- Standard check of p_commit
2565 IF FND_API.TO_BOOLEAN(p_commit) THEN
2566 COMMIT WORK;
2567 END IF;
2568
2569 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2570 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
2571 'At the end of PLSQL procedure');
2572 END IF;
2573 EXCEPTION
2574 WHEN FND_API.G_EXC_ERROR THEN
2575 x_return_status := FND_API.G_RET_STS_ERROR;
2576 ROLLBACK TO Process_Planned_Materials;
2577 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2578 p_data => x_msg_data,
2579 p_encoded => fnd_api.g_false);
2580
2581
2582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584 ROLLBACK TO Process_Planned_Materials;
2585 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2586 p_data => x_msg_data,
2587 p_encoded => fnd_api.g_false);
2588
2589
2590 WHEN OTHERS THEN
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592 ROLLBACK TO Process_Planned_Materials;
2593 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2594 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2595 p_procedure_name => 'Process_Planned_Materials',
2596 p_error_text => SUBSTR(SQLERRM,1,500));
2597 END IF;
2598 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2599 p_data => x_msg_data,
2600 p_encoded => fnd_api.g_false);
2601
2602
2603 END Process_Planned_Materials;
2604 --
2605 -- Start of Comments --
2606 -- Procedure name : Remove_Visit_Task_Matrls
2607 -- Type : Private
2608 -- Function : This procedure Created Planned materials information associated to scheduled
2609 -- visit, which are defined at Route Operation and Disposition level
2610 -- Pre-reqs :
2611 -- Parameters :
2612 --
2613 -- Standard IN Parameters :
2614 -- p_api_version IN NUMBER Required
2615 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2616 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2617 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2618 -- Based on this flag, the API will set the default attributes.
2619 -- p_module_type In VARCHAR2 Default NULL
2620 -- This will be null.
2621 -- Standard out Parameters :
2622 -- x_return_status OUT VARCHAR2 Required
2623 -- x_msg_count OUT NUMBER Required
2624 -- x_msg_data OUT VARCHAR2 Required
2625 --
2626 -- Create_Planned_Materials Parameters :
2627 -- p_visit_id IN NUMBER,Required
2628 --
2629 --
2630 PROCEDURE Remove_Visit_Task_Matrls (
2631 p_api_version IN NUMBER,
2632 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2633 p_commit IN VARCHAR2 := FND_API.g_false,
2634 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2635 p_visit_id IN NUMBER,
2636 p_visit_task_id IN NUMBER := NULL,
2637 x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2638 x_return_status OUT NOCOPY VARCHAR2,
2639 x_msg_count OUT NOCOPY NUMBER,
2640 x_msg_data OUT NOCOPY VARCHAR2)
2641 IS
2642
2643 CURSOR visit_task_details_cur (c_visit_id IN NUMBER,
2644 c_visit_task_id IN NUMBER)
2645 IS
2646 SELECT vs.visit_id,
2647 vs.organization_id,
2648 vt.visit_task_id
2649 FROM ahl_visits_vl vs,
2650 ahl_visit_tasks_vl vt
2651 WHERE vs.visit_id = vt.visit_id
2652 AND vs.visit_id = c_visit_id
2653 AND vt.visit_task_id = c_visit_task_id;
2654 --To Retrieve visit task planned materials
2655 -- Cursor modified by surrkuma on 15-JUL-2010 for bug 9901811
2656 CURSOR visit_task_mtrls_cur (c_visit_task_id IN NUMBER)
2657 IS
2658 SELECT vtm.visit_id,
2659 vtm.visit_task_id,
2660 vtm.schedule_material_id,
2661 vtm.object_version_number,
2662 vtm.inventory_item_id,
2663 vtm.scheduled_date,
2664 vtm.scheduled_quantity,
2665 vtm.item_number,
2666 asm.organization_id,
2667 asm.requested_quantity,
2668 asm.uom,
2669 asm.requested_date,
2670 asm.scheduled_date asm_scheduled_date
2671 FROM ahl_visit_task_matrl_v vtm,
2672 ahl_schedule_materials asm
2673 WHERE vtm.visit_task_id = c_visit_task_id
2674 AND asm.scheduled_material_id = vtm.schedule_material_id;
2675
2676 --Retrieve visit level planned materials
2677 -- Cursor modified by surrkuma on 15-JUL-2010 for bug 9901811
2678 CURSOR visit_mtrls_cur (c_visit_id IN NUMBER)
2679 IS
2680 SELECT vtm.visit_id,
2681 vtm.visit_task_id,
2682 vtm.schedule_material_id,
2683 vtm.object_version_number,
2684 vtm.inventory_item_id,
2685 vtm.scheduled_date,
2686 vtm.scheduled_quantity,
2687 vtm.item_number,
2688 asm.organization_id,
2689 asm.requested_quantity,
2690 asm.uom,
2691 asm.requested_date,
2692 asm.scheduled_date asm_scheduled_date
2693 FROM ahl_visit_task_matrl_v vtm,
2694 ahl_schedule_materials asm
2695 WHERE vtm.visit_id = c_visit_id
2696 AND asm.scheduled_material_id = vtm.schedule_material_id;
2697
2698 --Standard local variables
2699 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Visit_Task_Matrls';
2700 l_api_version CONSTANT NUMBER := 1.0;
2701 l_msg_data VARCHAR2(2000);
2702 l_return_status VARCHAR2(1);
2703 l_msg_count NUMBER;
2704 l_visit_task_details_rec visit_task_details_cur%ROWTYPE;
2705 l_visit_task_mtrls_rec visit_task_mtrls_cur%ROWTYPE;
2706 l_visit_mtrls_rec visit_mtrls_cur%ROWTYPE;
2707 l_visit_id NUMBER := p_visit_id;
2708 l_visit_task_id NUMBER := p_visit_task_id;
2709 l_planned_order_flag VARCHAR2(1):= 'N';
2710 -- Variables added by surrkuma on 15-JUL-2010 for bug 9901811
2711 l_unsched_mtl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
2712 l_unsched_mtl_index NUMBER := 1;
2713
2714 L_DEBUG_KEY VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2715 BEGIN
2716 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2717 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin',
2718 'At the start of PLSQL procedure.');
2719 END IF;
2720
2721 -- Standard start of API savepoint
2722 SAVEPOINT Remove_Visit_Task_Matrls;
2723
2724 -- Initialize message list if p_init_msg_list is set to TRUE
2725 IF FND_API.To_Boolean( p_init_msg_list) THEN
2726 FND_MSG_PUB.Initialize;
2727 END IF;
2728
2729 -- Initialize API return status to success
2730 x_return_status := FND_API.G_RET_STS_SUCCESS;
2731
2732 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2733 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2734 'Request for Remove Task Materials for Visit Id: ' || l_visit_id ||
2735 ' and Visit Task Id: ' || l_visit_task_id);
2736 END IF;
2737
2738 IF ( l_visit_id IS NOT NULL AND l_visit_id <> fnd_api.g_miss_num ) THEN
2739 --Get details
2740 OPEN visit_task_details_cur(l_visit_id,l_visit_task_id);
2741 FETCH visit_task_details_cur INTO l_visit_task_details_rec;
2742 CLOSE visit_task_details_cur;
2743
2744 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2745 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2746 ' After visit task details cur, Visit Id: ' || l_visit_id);
2747 END IF;
2748
2749 --Check for deleting a visit
2750 IF (l_visit_task_id IS NOT NULL AND l_visit_task_id <> fnd_api.g_miss_num) THEN
2751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2752 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2753 'Before Retrieving task materials cur, Visit Task Id: ' || l_visit_task_id);
2754 END IF;
2755 --Retrieve task materials only
2756 OPEN visit_task_mtrls_cur(l_visit_task_id);
2757 LOOP
2758 FETCH visit_task_mtrls_cur INTO l_visit_task_mtrls_rec;
2759 EXIT WHEN visit_task_mtrls_cur%NOTFOUND;
2760 -- update request quanity zero
2761 IF l_visit_task_mtrls_rec.schedule_material_id IS NOT NULL THEN
2762 -- Added by surrkuma on 15-JUL-2010 for bug 9901811
2763 -- Add this requirement to l_unsched_mtl_tbl for ATP unscheduling
2764 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2765 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2766 'l_unsched_mtl_index = ' || l_unsched_mtl_index ||
2767 ', Adding Requirement with id ' || l_visit_task_mtrls_rec.schedule_material_id || ' to l_unsched_mtl_tbl for Unscheduling.');
2768 END IF;
2769 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_id := l_visit_task_mtrls_rec.visit_id;
2770 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_task_id := l_visit_task_mtrls_rec.visit_task_id;
2771 l_unsched_mtl_tbl(l_unsched_mtl_index).inventory_item_id := l_visit_task_mtrls_rec.inventory_item_id;
2772 l_unsched_mtl_tbl(l_unsched_mtl_index).item_description := l_visit_task_mtrls_rec.item_number;
2773 l_unsched_mtl_tbl(l_unsched_mtl_index).organization_id := l_visit_task_mtrls_rec.organization_id;
2774 l_unsched_mtl_tbl(l_unsched_mtl_index).schedule_material_id := l_visit_task_mtrls_rec.schedule_material_id;
2775 l_unsched_mtl_tbl(l_unsched_mtl_index).required_quantity := l_visit_task_mtrls_rec.requested_quantity;
2776 l_unsched_mtl_tbl(l_unsched_mtl_index).primary_uom_code := l_visit_task_mtrls_rec.uom;
2777 l_unsched_mtl_tbl(l_unsched_mtl_index).requested_date := l_visit_task_mtrls_rec.requested_date;
2778 l_unsched_mtl_index := l_unsched_mtl_index + 1;
2779 -- End addition by surrkuma on 15-JUL-2010 for bug 9901811
2780
2781 UPDATE ahl_schedule_materials
2782 SET requested_quantity = 0,
2783 status = 'DELETED',
2784 object_version_number = l_visit_task_mtrls_rec.object_version_number + 1
2785 WHERE scheduled_material_id = l_visit_task_mtrls_rec.schedule_material_id;
2786 END IF; --Schedule material not null
2787 END LOOP;
2788 CLOSE visit_task_mtrls_cur;
2789 ELSE
2790 -- l_visit_task_id is NULL: Remove for entire visit
2791 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2792 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2793 ' Before Retrieving all visit task materials cur, For Visit Id: ' || l_visit_id
2794 );
2795 END IF;
2796
2797 -- Retrieve the materials for all tasks in the visit
2798 OPEN visit_mtrls_cur(l_visit_id);
2799 LOOP
2800 FETCH visit_mtrls_cur INTO l_visit_mtrls_rec;
2801 EXIT WHEN visit_mtrls_cur%NOTFOUND;
2802 -- update request quanity zero
2803 IF l_visit_mtrls_rec.schedule_material_id IS NOT NULL THEN
2804 -- Added by surrkuma on 15-JUL-2010 for bug 9901811
2805 -- Add this requirement to l_unsched_mtl_tbl for ATP unscheduling
2806 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2807 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2808 'l_unsched_mtl_index = ' || l_unsched_mtl_index ||
2809 ', Adding Requirement with id ' || l_visit_mtrls_rec.schedule_material_id || ' to l_unsched_mtl_tbl for Unscheduling.');
2810 END IF;
2811 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_id := l_visit_mtrls_rec.visit_id;
2812 l_unsched_mtl_tbl(l_unsched_mtl_index).visit_task_id := l_visit_mtrls_rec.visit_task_id;
2813 l_unsched_mtl_tbl(l_unsched_mtl_index).inventory_item_id := l_visit_mtrls_rec.inventory_item_id;
2814 l_unsched_mtl_tbl(l_unsched_mtl_index).item_description := l_visit_mtrls_rec.item_number;
2815 l_unsched_mtl_tbl(l_unsched_mtl_index).organization_id := l_visit_mtrls_rec.organization_id;
2816 l_unsched_mtl_tbl(l_unsched_mtl_index).schedule_material_id := l_visit_mtrls_rec.schedule_material_id;
2817 l_unsched_mtl_tbl(l_unsched_mtl_index).required_quantity := l_visit_mtrls_rec.requested_quantity;
2818 l_unsched_mtl_tbl(l_unsched_mtl_index).primary_uom_code := l_visit_mtrls_rec.uom;
2819 l_unsched_mtl_tbl(l_unsched_mtl_index).requested_date := l_visit_mtrls_rec.requested_date;
2820 l_unsched_mtl_index := l_unsched_mtl_index + 1;
2821 -- End addition by surrkuma on 15-JUL-2010 for bug 9901811
2822
2823 UPDATE ahl_schedule_materials
2824 SET requested_quantity = 0,
2825 status = 'DELETED',
2826 object_version_number = l_visit_mtrls_rec.object_version_number + 1
2827 WHERE scheduled_material_id = l_visit_mtrls_rec.schedule_material_id;
2828 END IF; --Schedule material not null
2829 END LOOP;
2830 CLOSE visit_mtrls_cur;
2831
2832 -- Serial Number reservation Enh.
2833 -- delete all reservations for this visit on organization change
2834 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
2835 X_RETURN_STATUS => X_RETURN_STATUS,
2836 P_VISIT_ID => p_visit_id);
2837
2838 IF (l_log_statement >= l_log_current_level)THEN
2839 fnd_log.string
2840 (
2841 l_log_statement,
2842 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2843 ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
2844 );
2845 END IF;
2846
2847 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2848 RAISE Fnd_Api.g_exc_error;
2849 END IF;
2850 END IF; --Just task deletion
2851
2852 -- Added by surrkuma on 15-JUL-2010 for bug 9901811
2853 IF (l_unsched_mtl_tbl.COUNT > 0) THEN
2854 -- Call ATP to unschedule the soft Deleted requirements
2855 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2856 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2857 'Before calling AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials. l_unsched_mtl_tbl.COUNT = ' || l_unsched_mtl_tbl.COUNT);
2858 END IF;
2859 AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials(p_api_version => 1.0,
2860 p_deleted_matrl_tbl => l_unsched_mtl_tbl,
2861 x_return_status => l_return_status,
2862 x_msg_count => l_msg_count,
2863 x_msg_data => l_msg_data);
2864 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2865 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2866 'After returning from AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials, l_return_status = ' || l_return_status);
2867 END IF;
2868 END IF;
2869 -- End addition by surrkuma on 15-JUL-2010 for bug 9901811
2870
2871 END IF;
2872 x_planned_order_flag := l_planned_order_flag;
2873
2874 --Standard check to count messages
2875 l_msg_count := Fnd_Msg_Pub.count_msg;
2876
2877 IF l_msg_count > 0 THEN
2878 X_msg_count := l_msg_count;
2879 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2880 RAISE Fnd_Api.G_EXC_ERROR;
2881 END IF;
2882
2883 -- Standard check of p_commit
2884 IF FND_API.TO_BOOLEAN(p_commit) THEN
2885 COMMIT WORK;
2886 END IF;
2887
2888 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2889 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
2890 'At the end of PLSQL procedure.');
2891 END IF;
2892 EXCEPTION
2893 WHEN FND_API.G_EXC_ERROR THEN
2894 x_return_status := FND_API.G_RET_STS_ERROR;
2895 ROLLBACK TO Remove_Visit_Task_Matrls;
2896 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2897 p_data => x_msg_data,
2898 p_encoded => fnd_api.g_false);
2899
2900
2901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2903 ROLLBACK TO Remove_Visit_Task_Matrls;
2904 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2905 p_data => x_msg_data,
2906 p_encoded => fnd_api.g_false);
2907
2908
2909 WHEN OTHERS THEN
2910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2911 ROLLBACK TO Remove_Visit_Task_Matrls;
2912 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2913 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2914 p_procedure_name => 'REMOVE_VISIT_TASK_MATRLS',
2915 p_error_text => SUBSTR(SQLERRM,1,500));
2916 END IF;
2917 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2918 p_data => x_msg_data,
2919 p_encoded => fnd_api.g_false);
2920
2921
2922 END Remove_Visit_Task_Matrls;
2923 --
2924 -- Start of Comments --
2925 -- Procedure name : Update_Unplanned_Matrls
2926 -- Type : Private
2927 -- Function : This procedure Created Planned materials information associated to scheduled
2928 -- visit, which are defined at Route Operation and Disposition level
2929 -- Pre-reqs :
2930 -- Parameters :
2931 --
2932 -- Standard IN Parameters :
2933 -- p_api_version IN NUMBER Required
2934 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2935 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2936 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2937 -- Based on this flag, the API will set the default attributes.
2938 -- p_module_type In VARCHAR2 Default NULL
2939 -- This will be null.
2940 -- Standard out Parameters :
2941 -- x_return_status OUT VARCHAR2 Required
2942 -- x_msg_count OUT NUMBER Required
2943 -- x_msg_data OUT VARCHAR2 Required
2944 --
2945 -- Update_Unplanned_Materials Parameters :
2946 -- p_visit_id IN NUMBER,Required
2947 --
2948 --
2949 PROCEDURE Update_Unplanned_Matrls (
2950 p_api_version IN NUMBER,
2951 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2952 p_commit IN VARCHAR2 := FND_API.g_false,
2953 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2954 p_visit_id IN NUMBER,
2955 x_return_status OUT NOCOPY VARCHAR2,
2956 x_msg_count OUT NOCOPY NUMBER,
2957 x_msg_data OUT NOCOPY VARCHAR2)
2958 IS
2959 CURSOR visit_mtrls_cur (c_visit_id IN NUMBER)
2960 IS
2961 SELECT visit_id,
2962 visit_task_id,
2963 scheduled_material_id,
2964 object_version_number
2965 FROM ahl_schedule_materials
2966 WHERE visit_id = c_visit_id
2967 AND status = 'ACTIVE';
2968
2969 --Standard local variables
2970 l_api_name CONSTANT VARCHAR2(30) := 'Update_Unplanned_Matrls';
2971 l_api_version CONSTANT NUMBER := 1.0;
2972 l_msg_data VARCHAR2(2000);
2973 l_return_status VARCHAR2(1);
2974 l_msg_count NUMBER;
2975 l_visit_mtrls_rec visit_mtrls_cur%ROWTYPE;
2976
2977 BEGIN
2978 IF (l_log_procedure >= l_log_current_level)THEN
2979 fnd_log.string
2980 (
2981 l_log_procedure,
2982 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update_Unplanned_Matrls' || '.begin',
2983 'At the start of PLSQL procedure'
2984 );
2985 END IF;
2986
2987 -- Standard start of API savepoint
2988 SAVEPOINT Update_Unplanned_Matrls;
2989
2990 -- Initialize message list if p_init_msg_list is set to TRUE
2991 IF FND_API.To_Boolean( p_init_msg_list) THEN
2992 FND_MSG_PUB.Initialize;
2993 END IF;
2994 -- Initialize API return status to success
2995 x_return_status := FND_API.G_RET_STS_SUCCESS;
2996
2997 IF (l_log_statement >= l_log_current_level)THEN
2998 fnd_log.string
2999 (
3000 l_log_statement,
3001 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3002 'Request for Update Materials for Visit Id : '|| p_visit_id
3003 );
3004 END IF;
3005
3006 --Retrieve all the materials
3007 OPEN visit_mtrls_cur(p_visit_id);
3008 LOOP
3009 FETCH visit_mtrls_cur INTO l_visit_mtrls_rec;
3010 EXIT WHEN visit_mtrls_cur%NOTFOUND;
3011 IF l_visit_mtrls_rec.scheduled_material_id IS NOT NULL THEN
3012 UPDATE ahl_schedule_materials
3013 SET STATUS = 'HISTORY',
3014 OBJECT_VERSION_NUMBER = l_visit_mtrls_rec.object_version_number
3015 WHERE scheduled_material_id = l_visit_mtrls_rec.scheduled_material_id;
3016 END IF;
3017 END LOOP;
3018 CLOSE visit_mtrls_cur;
3019
3020 -- Serial Number reservation Enh.
3021 -- Delete all the reservations for this visit
3022 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
3023 X_RETURN_STATUS => X_RETURN_STATUS,
3024 P_VISIT_ID => p_visit_id);
3025
3026 IF (l_log_statement >= l_log_current_level)THEN
3027 fnd_log.string
3028 (
3029 l_log_statement,
3030 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3031 ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
3032 );
3033 END IF;
3034
3035 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3036 RAISE Fnd_Api.g_exc_error;
3037 END IF;
3038
3039 -- Standard check of p_commit
3040 IF FND_API.TO_BOOLEAN(p_commit) THEN
3041 COMMIT WORK;
3042 END IF;
3043
3044 IF (l_log_procedure >= l_log_current_level)THEN
3045 fnd_log.string
3046 (
3047 l_log_procedure,
3048 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update Unplanned Matrls.end',
3049 'At the end of PLSQL procedure'
3050 );
3051 END IF;
3052 EXCEPTION
3053 WHEN FND_API.G_EXC_ERROR THEN
3054 x_return_status := FND_API.G_RET_STS_ERROR;
3055 ROLLBACK TO Update_Unplanned_Matrls;
3056 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3057 p_data => x_msg_data,
3058 p_encoded => fnd_api.g_false);
3059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061 ROLLBACK TO Update_Unplanned_Matrls;
3062 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3063 p_data => x_msg_data,
3064 p_encoded => fnd_api.g_false);
3065 WHEN OTHERS THEN
3066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3067 ROLLBACK TO Update_Unplanned_Matrls;
3068 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3069 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3070 p_procedure_name => 'UPDATE_UNPLANNED_MATRLS',
3071 p_error_text => SUBSTR(SQLERRM,1,500));
3072 END IF;
3073 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3074 p_data => x_msg_data,
3075 p_encoded => fnd_api.g_false);
3076 END Update_Unplanned_Matrls;
3077
3078 --
3079 -- Start of Comments --
3080 -- Procedure name : MODIFY_VISIT_RESERVATIONS
3081 -- Type : Private
3082 -- Function : Handles Material reservation incase of change in Visit Organisation.
3083 -- : Added for Serial NUmber Reservation by Senthil.
3084 --
3085 -- Pre-reqs :
3086 -- Parameters :
3087 --
3088 -- Standard out Parameters :
3089 -- x_return_status OUT VARCHAR2 Required
3090 --
3091 -- Modify_Visit_Reservations Parameters :
3092 -- p_visit_id IN NUMBER,Required
3093 --
3094 --
3095 PROCEDURE Modify_Visit_Reservations (
3096 p_visit_id IN NUMBER,
3097 x_return_status OUT NOCOPY VARCHAR2)
3098
3099 IS
3100 -- AnRaj: Changed the WHERE clause , for Performance improvement
3101 CURSOR get_del_mtl_req_csr(c_visit_id IN NUMBER) IS
3102 SELECT mat.scheduled_material_id
3103 FROM ahl_schedule_materials mat,
3104 ahl_visit_tasks_b vt
3105 WHERE vt.visit_id = c_visit_id
3106 AND vt.status_code = 'DELETED'
3107 AND vt.visit_task_id = mat.visit_task_id
3108 AND EXISTS (SELECT reservation_id
3109 FROM mtl_reservations RSV
3110 WHERE RSV.external_source_code = 'AHL'
3111 AND RSV.demand_source_line_detail = mat.scheduled_material_id
3112 AND RSV.organization_id = mat.organization_id
3113 AND RSV.requirement_date = mat.requested_date
3114 AND RSV.inventory_item_id = mat.inventory_item_id );
3115
3116 CURSOR get_cur_org_csr(p_visit_id IN NUMBER) IS
3117 SELECT organization_id
3118 FROM ahl_visits_b
3119 WHERE visit_id = p_visit_id;
3120
3121 CURSOR get_prev_org_csr(p_visit_id IN NUMBER) IS
3122 SELECT organization_id
3123 FROM mtl_reservations
3124 WHERE external_source_code = 'AHL'
3125 AND demand_source_header_id in ( SELECT visit_task_id
3126 FROM ahl_visit_tasks_b
3127 WHERE visit_id = p_visit_id);
3128 --Standard local variables
3129 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Visit_Reservations';
3130 l_api_version CONSTANT NUMBER := 1.0;
3131 l_msg_data VARCHAR2(2000);
3132 l_return_status VARCHAR2(1);
3133 l_msg_count NUMBER;
3134
3135 l_cur_org_id NUMBER;
3136 l_prev_org_id NUMBER;
3137 l_org_count NUMBER;
3138 l_scheduled_material_id NUMBER;
3139 BEGIN
3140 IF (l_log_procedure >= l_log_current_level)THEN
3141 fnd_log.string
3142 (
3143 l_log_procedure,
3144 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations.Start',
3145 'At the end of PLSQL procedure'
3146 );
3147 END IF;
3148
3149 OPEN get_cur_org_csr(p_visit_id) ;
3150 FETCH get_cur_org_csr into l_cur_org_id;
3151 CLOSE get_cur_org_csr;
3152
3153
3154 OPEN get_prev_org_csr (p_visit_id) ;
3155 FETCH get_prev_org_csr into l_prev_org_id;
3156 CLOSE get_prev_org_csr;
3157
3158
3159 SELECT count(distinct organization_id)
3160 INTO l_org_count
3161 FROM mtl_reservations
3162 WHERE external_source_code = 'AHL'
3163 AND demand_source_header_id in ( SELECT visit_task_id
3164 FROM ahl_visit_tasks_b
3165 WHERE visit_id = p_visit_id);
3166
3167 IF (l_log_statement >= l_log_current_level)THEN
3168 fnd_log.string
3169 (
3170 l_log_statement,
3171 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3172 'l_org_count : '||l_org_count||' l_cur_org_id:'||l_cur_org_id||
3173 ' l_prev_org_id:'||l_prev_org_id
3174 );
3175 END IF;
3176
3177
3178 IF l_prev_org_id IS NULL THEN
3179 Return;
3180 ELSIF l_org_count > 1 THEN
3181 FND_MESSAGE.set_name('AHL', 'AHL_LTP_MULTI_ORG');
3182 FND_MSG_PUB.ADD;
3183 RAISE Fnd_Api.g_exc_error;
3184 END IF;
3185
3186 IF l_prev_org_id <> l_cur_org_id THEN
3187 -- delete all reservations for this visit on organization change
3188 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
3189 X_RETURN_STATUS => X_RETURN_STATUS,
3190 P_VISIT_ID => p_visit_id);
3191
3192 IF (l_log_statement >= l_log_current_level)THEN
3193 fnd_log.string
3194 (
3195 l_log_statement,
3196 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3197 'After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS:X_RETURN_STATUS '||X_RETURN_STATUS
3198 );
3199 END IF;
3200
3201 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3202 RAISE Fnd_Api.g_exc_error;
3203 END IF;
3204 ELSE
3205 IF (l_log_statement >= l_log_current_level)THEN
3206 fnd_log.string
3207 (
3208 l_log_statement,
3209 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3210 'In the else part of check l_prev_org_id <> l_cur_org_id'
3211 );
3212 END IF;
3213 -- Get all the material requirements with reservation created for deleted tasks
3214 OPEN get_del_mtl_req_csr (p_visit_id);
3215 LOOP
3216 Fetch get_del_mtl_req_csr into l_scheduled_material_id;
3217 EXIT WHEN get_del_mtl_req_csr%NOTFOUND;
3218 -- Delete all the reservations made for this requirement
3219 AHL_RSV_RESERVATIONS_PVT.Delete_Reservation(
3220 p_module_type => NULL,
3221 x_return_status => l_return_status,
3222 x_msg_count => l_msg_count,
3223 x_msg_data => l_msg_data,
3224 p_scheduled_material_id => l_scheduled_material_id
3225 );
3226
3227 IF (l_log_statement >= l_log_current_level)THEN
3228 fnd_log.string
3229 (
3230 l_log_statement,
3231 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3232 'After calling AHL_RSV_RESERVATIONS_PVT.Delete_Reservation:l_return_status '||l_return_status
3233 );
3234 END IF;
3235 -- Return status check and throw exception if return status is not success;
3236 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3237 CLOSE get_del_mtl_req_csr;
3238 RAISE Fnd_Api.g_exc_error;
3239 END IF;
3240 END LOOP; -- For all the material requirements of the deleted tasks
3241 CLOSE get_del_mtl_req_csr;
3242 -- Update all the reservations made for this visit with new requested date and scheduled material ID
3243
3244 AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations(
3245 X_RETURN_STATUS => x_return_status,
3246 P_VISIT_ID => p_visit_id);
3247
3248 IF (l_log_statement >= l_log_current_level)THEN
3249 fnd_log.string
3250 (
3251 l_log_statement,
3252 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3253 'After calling AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations:x_return_status '||x_return_status
3254 );
3255 END IF;
3256
3257 -- Return status check and throw exception if return status is not success;
3258 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3259 RAISE Fnd_Api.g_exc_error;
3260 END IF;
3261 END IF; -- IF l_prev_org_id <> l_cur_org_id
3262
3263 IF (l_log_procedure >= l_log_current_level)THEN
3264 fnd_log.string
3265 (
3266 l_log_procedure,
3267 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations.end',
3268 'At the end of PLSQL procedure'
3269 );
3270 END IF;
3271 END Modify_Visit_Reservations;
3272
3273 --------------------------------------------------------------------
3274 -- PROCEDURE
3275 -- Update_Material_Reqrs_status
3276 --
3277 -- PURPOSE
3278 -- To update the status of material requirements to 'HISTORY'
3279 -- when the work-order is CANCELLED.
3280 --
3281 -- Bug#6898408 Initial Version Created by Richa
3282 --------------------------------------------------------------------
3283 PROCEDURE Update_Material_Reqrs_status
3284 ( p_api_version IN NUMBER,
3285 p_init_msg_list IN VARCHAR2,
3286 p_commit IN VARCHAR2,
3287 p_validation_level IN NUMBER,
3288 p_module_type IN VARCHAR2,
3289 p_visit_task_id IN NUMBER,
3290 x_return_status OUT NOCOPY VARCHAR2,
3291 x_msg_count OUT NOCOPY NUMBER,
3292 x_msg_data OUT NOCOPY VARCHAR2
3293 )
3294 IS
3295 -- Declare local variables
3296 l_api_name CONSTANT VARCHAR2(30) := 'Update_Material_Reqrs_status';
3297 l_api_version CONSTANT NUMBER := 1.0;
3298 l_init_msg_list VARCHAR2(1) := 'F';
3299 l_return_status VARCHAR2(1);
3300 l_msg_count NUMBER;
3301 l_msg_data VARCHAR2(2000);
3302 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3303 l_sch_material_id NUMBER := 0;
3304
3305 BEGIN
3306 -- Standard start of API savepoint
3307 SAVEPOINT Update_Material_Reqrs_sts;
3308
3309 -- Initialize return status to success before any code logic/validation
3310 x_return_status:= FND_API.G_RET_STS_SUCCESS;
3311
3312 -- Standard call to check for call compatibility
3313 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315 END IF;
3316
3317 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3318 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3319 FND_MSG_PUB.INITIALIZE;
3320 END IF;
3321
3322 -- Log API entry point
3323 IF (l_log_procedure >= l_log_current_level) THEN
3324 fnd_log.string( l_log_procedure,L_DEBUG_KEY ||'.begin','At the start of PL SQL procedure - Task id = '||p_visit_task_id);
3325 END IF;
3326
3327 IF (p_visit_task_id IS NULL) THEN
3328 IF (l_log_statement >= l_log_current_level) THEN
3329 fnd_log.string( l_log_statement,L_DEBUG_KEY,'Task id is null' );
3330 END IF;
3331 Fnd_Message.SET_NAME('AHL','AHL_VISIT_TASKID_NULL');
3332 Fnd_Msg_Pub.ADD;
3333 RAISE Fnd_Api.G_EXC_ERROR;
3334 END IF;
3335
3336 --Update the status of the record to 'HISTORY'
3337 UPDATE ahl_Schedule_materials
3338 SET STATUS = 'HISTORY',
3339 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3340 LAST_UPDATE_DATE = sysdate,
3341 LAST_UPDATED_BY = Fnd_Global.USER_ID,
3342 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
3343 WHERE visit_task_id = p_visit_task_id
3344 AND STATUS = 'ACTIVE';
3345
3346 -- Standard check of p_commit
3347 IF Fnd_Api.To_Boolean (p_commit) THEN
3348 COMMIT WORK;
3349 END IF;
3350
3351 IF (l_log_procedure >= l_log_current_level) THEN
3352 fnd_log.string(l_log_procedure,
3353 L_DEBUG_KEY ||'.end',
3354 'No of rows updated - '||SQL%ROWCOUNT);
3355 fnd_log.string(l_log_procedure,
3356 L_DEBUG_KEY ||'.end',
3357 'At the end of PL SQL procedure. Return Status =' || x_return_status);
3358 END IF;
3359
3360 EXCEPTION
3361 WHEN Fnd_Api.g_exc_error THEN
3362 ROLLBACK TO Update_Material_Reqrs_sts;
3363 x_return_status := Fnd_Api.g_ret_sts_error;
3364 Fnd_Msg_Pub.count_and_get (
3365 p_encoded => Fnd_Api.g_false,
3366 p_count => x_msg_count,
3367 p_data => x_msg_data);
3368
3369 WHEN Fnd_Api.g_exc_unexpected_error THEN
3370 ROLLBACK TO Update_Material_Reqrs_sts;
3371 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3372 Fnd_Msg_Pub.count_and_get (
3373 p_encoded => Fnd_Api.g_false,
3374 p_count => x_msg_count,
3375 p_data => x_msg_data);
3376
3377 WHEN OTHERS THEN
3378 ROLLBACK TO Update_Material_Reqrs_sts;
3379 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3380 Fnd_Msg_Pub.count_and_get (
3381 p_encoded => Fnd_Api.g_false,
3382 p_count => x_msg_count,
3383 p_data => x_msg_data );
3384 END Update_Material_Reqrs_status;
3385
3386 -- -- SKPATHAK :: Bug 8604722 :: 04-MAR-2010 :: Added new procedure
3387 PROCEDURE Get_DFF_Default_Values (
3388 flexfield_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
3389 context_code IN VARCHAR2,
3390 flex_fields_defaults OUT NOCOPY dff_default_values_type
3391 ) IS
3392
3393 flexfield fnd_dflex.dflex_r;
3394 flexinfo fnd_dflex.dflex_dr;
3395 contexts fnd_dflex.contexts_dr;
3396 i BINARY_INTEGER;
3397 j BINARY_INTEGER;
3398 segments fnd_dflex.segments_dr;
3399
3400 l_api_name CONSTANT VARCHAR2(30) := 'Get_DFF_Default_Values';
3401 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
3402
3403 BEGIN
3404 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3405 fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin', 'Entering Procedure. flexfield_name: ' || flexfield_name);
3406 END IF;
3407
3408 fnd_dflex.get_flexfield('AHL', flexfield_name, flexfield, flexinfo);
3409
3410 IF (context_code IS NULL) THEN
3411 flex_fields_defaults.ATTRIBUTE_CATEGORY := flexinfo.default_context_value;
3412 ELSE
3413 flex_fields_defaults.ATTRIBUTE_CATEGORY := context_code;
3414 END IF;
3415
3416
3417 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3418 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'flex_fields_defaults.ATTRIBUTE_CATEGORY: ' || flex_fields_defaults.ATTRIBUTE_CATEGORY);
3419 END IF;
3420
3421 IF(flex_fields_defaults.ATTRIBUTE_CATEGORY IS NOT NULL)THEN
3422 -- Get all the contexts
3423 fnd_dflex.get_contexts(flexfield, contexts);
3424 -- Find the required Contexts (Just Global or Global+User Selected)
3425 FOR j IN 1 .. contexts.ncontexts LOOP
3426 IF(contexts.is_enabled(j) AND (flex_fields_defaults.ATTRIBUTE_CATEGORY = contexts.context_code(j) OR contexts.is_global(j))) THEN
3427 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3428 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Context is enabled. contexts.context_code(j): ' || contexts.context_code(j));
3429 IF (contexts.is_global(j)) THEN
3430 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Context is global.');
3431 ELSE
3432 fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Context is not global.');
3433 END IF;
3434 END IF;
3435 -- Get Segments for current context
3436 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield, contexts.context_code(j)), segments, TRUE);
3437 -- Transfer the default value for each each enabled segment to the OUT parameter
3438 FOR i IN 1 .. segments.nsegments LOOP
3439 IF(segments.is_enabled(i)) THEN
3440 IF(segments.application_column_name(i) = 'ATTRIBUTE1')THEN
3441 flex_fields_defaults.ATTRIBUTE1 := to_char(segments.default_value(i));
3442 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE2')THEN
3443 flex_fields_defaults.ATTRIBUTE2 := to_char(segments.default_value(i));
3444 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE3')THEN
3445 flex_fields_defaults.ATTRIBUTE3 := to_char(segments.default_value(i));
3446 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE4')THEN
3447 flex_fields_defaults.ATTRIBUTE4 := to_char(segments.default_value(i));
3448 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE5')THEN
3449 flex_fields_defaults.ATTRIBUTE5 := to_char(segments.default_value(i));
3450 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE6')THEN
3451 flex_fields_defaults.ATTRIBUTE6 := to_char(segments.default_value(i));
3452 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE7')THEN
3453 flex_fields_defaults.ATTRIBUTE7 := to_char(segments.default_value(i));
3454 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE8')THEN
3455 flex_fields_defaults.ATTRIBUTE8 := to_char(segments.default_value(i));
3456 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE9')THEN
3457 flex_fields_defaults.ATTRIBUTE9 := to_char(segments.default_value(i));
3458 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE10')THEN
3459 flex_fields_defaults.ATTRIBUTE10 := to_char(segments.default_value(i));
3460 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE11')THEN
3461 flex_fields_defaults.ATTRIBUTE11 := to_char(segments.default_value(i));
3462 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE12')THEN
3463 flex_fields_defaults.ATTRIBUTE12 := to_char(segments.default_value(i));
3464 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE13')THEN
3465 flex_fields_defaults.ATTRIBUTE13 := to_char(segments.default_value(i));
3466 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE14')THEN
3467 flex_fields_defaults.ATTRIBUTE14 := to_char(segments.default_value(i));
3468 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE15')THEN
3469 flex_fields_defaults.ATTRIBUTE15 := to_char(segments.default_value(i));
3470 END IF;
3471 END IF; -- If Segment(i) is enabled
3472 END LOOP; -- Loop on i (all segments)
3473 END IF; -- If Context (j) is enabled
3474 END LOOP; -- Loop on j (all Contexts)
3475 END IF; -- Attribute Category is not null
3476
3477 END Get_DFF_Default_Values;
3478
3479 -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 :: START
3480 -------------------------------------------------------------------
3481 -- Procedure name : Associate_Stage_Materials
3482 -- Type : Private
3483 -- Function : Procedure to associate materials, based on NR profile, to stage tasks
3484 -- Parameters :
3485 --
3486 -- Standard IN Parameters :
3487 -- p_api_version IN NUMBER Required
3488 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3489 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3490 -- p_module_type IN VARCHAR2 Default Null
3491 --
3492 -- Standard OUT Parameters :
3493 -- x_return_status OUT VARCHAR2 Required
3494 -- x_msg_count OUT NUMBER Required
3495 -- x_msg_data OUT VARCHAR2 Required
3496 --
3497 -- Associate_Stage_Materials Parameters:
3498 -- p_visit_id IN NUMBER Required
3499 -- p_mr_header_id IN NUMBER Required
3500 -- p_instance_id IN NUMBER Required
3501 -- p_ue_id IN NUMBER Required
3502
3503 --
3504 -------------------------------------------------------------------
3505 PROCEDURE Associate_Stage_Materials (
3506 p_api_version IN NUMBER := 1.0,
3507 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3508 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3510 p_module_type IN VARCHAR2 := 'JSP',
3511 p_visit_id IN NUMBER,
3512 p_mr_header_id IN NUMBER,
3513 p_instance_id IN NUMBER,
3514 p_ue_id IN NUMBER,
3515 x_return_status OUT NOCOPY VARCHAR2,
3516 x_msg_count OUT NOCOPY NUMBER,
3517 x_msg_data OUT NOCOPY VARCHAR2)IS
3518
3519 -- Local Variables
3520
3521 -- Standard in/out parameters
3522 l_api_name VARCHAR2(30) := 'Associate_Stage_Materials';
3523 l_api_version NUMBER := 1.0;
3524 l_msg_count NUMBER;
3525 l_msg_data VARCHAR2(2000);
3526 l_return_status VARCHAR2(1);
3527 l_init_msg_list VARCHAR2(10):= p_init_msg_list;
3528 l_commit VARCHAR2(30) := p_commit;
3529 l_prim_quantity NUMBER;
3530 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Associate_Stage_Materials';
3531 l_nrp_mat_req_tbl AHL_RA_NR_PROFILE_PVT.nrp_mat_req_tbl_type;
3532 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
3533 l_requested_date DATE;
3534 l_workorder_id NUMBER;
3535 l_operation_sequence NUMBER;
3536 l_workorder_operation_id NUMBER;
3537 l_requested_quantity NUMBER;
3538 l_scheduled_quantity NUMBER;
3539 l_uom_code VARCHAR2(30);
3540 l_prim_uom_code VARCHAR2(30);
3541 k NUMBER := 0;
3542
3543
3544
3545 Cursor get_visit_org_id (c_visit_id IN NUMBER)
3546 IS
3547 SELECT organization_id
3548 FROM ahl_visits_b
3549 WHERE visit_id = c_visit_id;
3550 l_visit_org_id NUMBER;
3551
3552 Cursor get_scheduled_mat_detls (c_task_id IN NUMBER, c_item_id IN NUMBER)
3553 IS
3554 SELECT scheduled_material_id, requested_quantity, scheduled_quantity, uom
3555 FROM ahl_schedule_materials
3556 WHERE visit_task_id = c_task_id
3557 AND inventory_item_id = c_item_id
3558 AND status <> 'DELETED';
3559
3560
3561 Cursor get_scheduled_mat_id (c_task_id IN NUMBER, c_item_id IN NUMBER)
3562 IS
3563 SELECT scheduled_material_id
3564 FROM ahl_schedule_materials
3565 WHERE visit_task_id = c_task_id
3566 AND inventory_item_id = c_item_id
3567 AND status <> 'DELETED';
3568 l_scheduled_material_id NUMBER;
3569
3570 --Get the visit stage task for the given stage type
3571 --If the stage type is not found in any of the visit stages, get the default stage task of the visit
3572 Cursor get_stage_task (c_visit_id IN NUMBER,
3573 c_stage_type_code IN VARCHAR2)
3574 IS
3575 SELECT task.visit_task_id, stage.planned_start_date
3576 FROM ahl_visit_stage_typ_asoc assoc, ahl_visit_tasks_b task, ahl_vwp_stages_b stage
3577 WHERE (assoc.stage_id = task.stage_id
3578 OR task.stage_id IS NULL)
3579 AND task.task_type_code = 'STAGE'
3580 AND task.visit_id = c_visit_id
3581 AND (assoc.stage_type_code = NVL(c_stage_type_code, '-1')
3582 OR task.stage_id IS NULL)
3583 AND task.status_code = 'PLANNING'
3584 AND stage.visit_id = c_visit_id
3585 AND (stage.stage_id = task.stage_id
3586 OR task.stage_id IS NULL)
3587 ORDER BY task.stage_id NULLS LAST;
3588 l_stage_task_rec get_stage_task%ROWTYPE;
3589
3590 CURSOR get_workorder_detls (c_task_id IN NUMBER)
3591 IS
3592 SELECT wip.scheduled_start_date, wo.workorder_id, opr.operation_sequence_num, opr.workorder_operation_id
3593 FROM ahl_workorders wo, wip_discrete_jobs wip, ahl_workorder_operations opr
3594 WHERE wo.visit_task_id = c_task_id
3595 AND wo.wip_entity_id = wip.wip_entity_id
3596 AND opr.workorder_id = wo.workorder_id;
3597
3598 -- Get primary UOM
3599 CURSOR Get_primary_uom (C_ITEM_ID IN NUMBER,
3600 C_ORG_ID IN NUMBER)
3601 IS
3602 SELECT primary_uom_code
3603 FROM mtl_system_items_vl
3604 WHERE inventory_item_id = C_ITEM_ID
3605 AND organization_id = C_ORG_ID;
3606
3607 BEGIN
3608
3609 IF (fnd_log.level_procedure >= l_log_current_level)THEN
3610 fnd_log.string (fnd_log.level_procedure,
3611 l_debug_key,
3612 'At the start of PLSQL procedure');
3613 END IF;
3614
3615 -- Standard start of API savepoint
3616 SAVEPOINT Associate_Stage_Materials_pvt;
3617
3618 -- Initialize message list if p_init_msg_list is set to TRUE
3619
3620 IF FND_API.To_Boolean( p_init_msg_list) THEN
3621 FND_MSG_PUB.Initialize;
3622 END IF;
3623
3624 -- Initialize API return status to success
3625 x_return_status := FND_API.G_RET_STS_SUCCESS;
3626
3627 -- Standard call to check for call compatibility.
3628 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3629 p_api_version,
3630 l_api_name,G_PKG_NAME)
3631 THEN
3632 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3633 END IF;
3634
3635 -- TCHIMIRA :: 15-JUL-2011 :: VWPE: ER12730539 :: If the visit is old, NR Profile is not applicable
3636 IF AHL_VWP_VISITS_PVT.Is_Old_Visit(p_visit_id) = 'Y' THEN
3637 RETURN;
3638 END IF;
3639
3640 IF (l_log_statement >= l_log_current_level)THEN
3641 fnd_log.string (l_log_statement,
3642 l_debug_key,
3643 'Before calling AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ, p_mr_header_id : '
3644 || p_mr_header_id || ' , p_instance_id: '||p_instance_id ||' , p_ue_id: '||p_ue_id);
3645 END IF;
3646 --Get all the NR Profile material for the given MR
3647 AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ
3648 (p_mr_header_id => p_mr_header_id,
3649 p_instance_id => p_instance_id,
3650 p_ue_id => p_ue_id,
3651 p_plan_id => null,
3652 x_return_status => l_return_status,
3653 x_msg_count => l_msg_count,
3654 x_msg_data => l_msg_data,
3655 x_prof_mat_req_tbl => l_nrp_mat_req_tbl
3656 );
3657
3658 IF (l_log_statement >= l_log_current_level) THEN
3659 fnd_log.string(l_log_statement,
3660 L_DEBUG_KEY,
3661 'After calling POPULATE_PROF_MAT_REQ. l_return_status = ' || l_return_status ||' FND_MSG_PUB.count_msg= '||FND_MSG_PUB.count_msg||'l_nrp_mat_req_tbl.count is : '||l_nrp_mat_req_tbl.count);
3662 END IF;
3663
3664 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3665 x_msg_count := FND_MSG_PUB.count_msg;
3666 IF (l_log_statement >= l_log_current_level) THEN
3667 fnd_log.string(l_log_statement,
3668 L_DEBUG_KEY,
3669 'Errors from POPULATE_PROF_MAT_REQ. Message count: ' || x_msg_count);
3670 END IF;
3671 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3672 RAISE FND_API.G_EXC_ERROR;
3673 ELSE
3674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675 END IF;
3676 END IF;
3677
3678 IF l_nrp_mat_req_tbl.count > 0 THEN
3679 --Loop through all the materials populated by POPULATE_PROF_MAT_REQ and print the details in the debug log
3680 FOR i IN l_nrp_mat_req_tbl.FIRST..l_nrp_mat_req_tbl.LAST LOOP
3681 IF (l_log_statement >= l_log_current_level)THEN
3682 fnd_log.string (l_log_statement,
3683 l_debug_key,
3684 'Attributes of l_nrp_mat_req_tbl as returned from POPULATE_PROF_MAT_REQ : Row - '|| i ||' - INVENTORY_ITEM_ID: '
3685 || l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID || ' , QUANTITY : ' || l_nrp_mat_req_tbl(i).QUANTITY || ' , UOM_CODE: '||l_nrp_mat_req_tbl(i).UOM_CODE
3686 ||' , STAGE_TYPE_CODE: '||l_nrp_mat_req_tbl(i).STAGE_TYPE_CODE);
3687 END IF;
3688 END LOOP;
3689
3690 OPEN get_visit_org_id (p_visit_id);
3691 FETCH get_visit_org_id INTO l_visit_org_id;
3692 CLOSE get_visit_org_id;
3693 --Loop through all the materials populated by POPULATE_PROF_MAT_REQ
3694 FOR i IN l_nrp_mat_req_tbl.FIRST..l_nrp_mat_req_tbl.LAST LOOP
3695 --For the stage type in l_nrp_mat_req_tbl, get the corresponding stage task
3696 --If the stage type is not present in any of the stages, get the default stage task of the visit
3697 OPEN get_stage_task (p_visit_id, l_nrp_mat_req_tbl(i).STAGE_TYPE_CODE);
3698 FETCH get_stage_task INTO l_stage_task_rec;
3699 CLOSE get_stage_task;
3700
3701 --For the required quantity in l_req_material_tbl, get the quantity in primary UOM of the item
3702 l_prim_quantity := AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID, l_nrp_mat_req_tbl(i).UOM_CODE, l_nrp_mat_req_tbl(i).QUANTITY);
3703 IF (l_log_statement >= l_log_current_level)THEN
3704 fnd_log.string (l_log_statement,
3705 l_debug_key,
3706 'Inside the loop for NR materials, Visit_id: '|| p_visit_id ||' ,visit_task_id: '|| l_stage_task_rec.visit_task_id ||' ,INVENTORY_ITEM_ID: '
3707 || l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID || ' , QUANTITY : ' || l_nrp_mat_req_tbl(i).QUANTITY || ' , UOM_CODE: '
3708 ||l_nrp_mat_req_tbl(i).UOM_CODE ||' , STAGE_TYPE_CODE: '||l_nrp_mat_req_tbl(i).STAGE_TYPE_CODE );
3709 END IF;
3710
3711 OPEN get_scheduled_mat_detls (l_stage_task_rec.visit_task_id, l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID);
3712 FETCH get_scheduled_mat_detls INTO l_scheduled_material_id, l_requested_quantity, l_scheduled_quantity, l_uom_code;
3713 IF get_scheduled_mat_detls%FOUND THEN
3714
3715 OPEN get_workorder_detls (l_stage_task_rec.visit_task_id);
3716 FETCH get_workorder_detls INTO l_requested_date, l_workorder_id, l_operation_sequence, l_workorder_operation_id;
3717 IF get_workorder_detls%FOUND THEN
3718 IF (l_log_statement >= l_log_current_level)THEN
3719 fnd_log.string (l_log_statement,
3720 l_debug_key,
3721 'l_requested_date: '|| l_requested_date ||' ,l_workorder_id: '|| l_workorder_id ||' ,l_operation_sequence: ' || l_operation_sequence
3722 || ' , l_workorder_operation_id : ' || l_workorder_operation_id || ' , l_scheduled_material_id : ' || l_scheduled_material_id);
3723 END IF;
3724 -- Populate l_req_material_tbl that will be used to update WIP table with these materials
3725 -- SKPATHAK :: 18-JUL-2011 :: VWPE 12730539
3726 -- The NR Profile materials may again be repeated for the same stage, so need to handle the repetition of NR Profile material records
3727 IF l_req_material_tbl.count > 0 THEN
3728 For j IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST LOOP
3729 IF l_req_material_tbl(j).SCHEDULE_MATERIAL_ID = l_scheduled_material_id THEN
3730 l_req_material_tbl(j).REQUESTED_QUANTITY := l_req_material_tbl(j).REQUESTED_QUANTITY + l_prim_quantity;
3731 l_req_material_tbl(j).SCHEDULED_QUANTITY := l_req_material_tbl(j).SCHEDULED_QUANTITY + l_prim_quantity;
3732 k := k+1;
3733 END IF;
3734 END LOOP;
3735 END IF;
3736 IF l_req_material_tbl.count < (i-k) THEN
3737 l_req_material_tbl(i-k).SCHEDULE_MATERIAL_ID := l_scheduled_material_id;
3738 l_req_material_tbl(i-k).INVENTORY_ITEM_ID := l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID;
3739 l_req_material_tbl(i-k).REQUESTED_QUANTITY := l_prim_quantity + l_requested_quantity;
3740 l_req_material_tbl(i-k).SCHEDULED_QUANTITY := l_prim_quantity + l_scheduled_quantity;
3741 l_req_material_tbl(i-k).REQUESTED_DATE := l_requested_date;
3742 l_req_material_tbl(i-k).UOM_CODE := l_uom_code;
3743 l_req_material_tbl(i-k).OPERATION_FLAG := 'U';
3744 l_req_material_tbl(i-k).WORKORDER_ID := l_workorder_id;
3745 l_req_material_tbl(i-k).OPERATION_SEQUENCE := l_operation_sequence;
3746 l_req_material_tbl(i-k).WORKORDER_OPERATION_ID := l_workorder_operation_id;
3747 END IF;
3748 ELSE --get_workorder_detls%FOUND
3749 -- IF the corresponding WO does not exists, update only the ASM table
3750 UPDATE ahl_schedule_materials
3751 SET requested_quantity = requested_quantity + l_prim_quantity,
3752 scheduled_quantity = scheduled_quantity + l_prim_quantity,
3753 object_version_number = object_version_number + 1,
3754 last_update_date = SYSDATE,
3755 last_updated_by = Fnd_Global.USER_ID,
3756 last_update_login = Fnd_Global.LOGIN_ID
3757 WHERE scheduled_material_id = l_scheduled_material_id;
3758 END IF;
3759 CLOSE get_workorder_detls;
3760 CLOSE get_scheduled_mat_detls;
3761
3762 ELSE --else of get_scheduled_mat_detls%FOUND
3763 CLOSE get_scheduled_mat_detls;
3764 -- New row for this material requirement needs to be inserted
3765 --Get the primary UOM
3766 OPEN Get_primary_uom(l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID,l_visit_org_id);
3767 FETCH Get_primary_uom INTO l_prim_uom_code;
3768 CLOSE Get_primary_uom;
3769
3770 IF (l_log_statement >= l_log_current_level)THEN
3771 fnd_log.string (l_log_statement,
3772 l_debug_key,
3773 'l_scheduled_material_id : '|| l_scheduled_material_id ||' ,visit_task_id: '|| l_stage_task_rec.visit_task_id
3774 ||' ,INVENTORY_ITEM_ID: ' || l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID || ' , QUANTITY : ' || l_nrp_mat_req_tbl(i).QUANTITY
3775 || ' , UOM_CODE: '||l_nrp_mat_req_tbl(i).UOM_CODE );
3776 END IF;
3777
3778 OPEN get_workorder_detls (l_stage_task_rec.visit_task_id);
3779 FETCH get_workorder_detls INTO l_requested_date, l_workorder_id, l_operation_sequence, l_workorder_operation_id;
3780 IF get_workorder_detls%FOUND THEN
3781 IF (l_log_statement >= l_log_current_level)THEN
3782 fnd_log.string (l_log_statement,
3783 l_debug_key,
3784 'l_requested_date: '|| l_requested_date ||' ,l_workorder_id: '|| l_workorder_id ||' ,l_operation_sequence: ' || l_operation_sequence
3785 || ' , l_workorder_operation_id : ' || l_workorder_operation_id );
3786 END IF;
3787 -- Update WIP table with these materials
3788 IF l_req_material_tbl.count > 0 THEN
3789 For j IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST LOOP
3790 IF l_req_material_tbl(j).INVENTORY_ITEM_ID = l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID AND l_req_material_tbl(j).WORKORDER_ID = l_workorder_id THEN
3791 l_req_material_tbl(j).REQUESTED_QUANTITY := l_req_material_tbl(j).REQUESTED_QUANTITY + l_prim_quantity;
3792 l_req_material_tbl(j).SCHEDULED_QUANTITY := l_req_material_tbl(j).SCHEDULED_QUANTITY + l_prim_quantity;
3793 k := k+1;
3794 END IF;
3795 END LOOP;
3796 END IF;
3797 IF l_req_material_tbl.count < (i-k) THEN
3798 l_req_material_tbl(i-k).INVENTORY_ITEM_ID := l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID;
3799 l_req_material_tbl(i-k).REQUESTED_QUANTITY := l_prim_quantity;
3800 l_req_material_tbl(i-k).SCHEDULED_QUANTITY := l_prim_quantity;
3801 l_req_material_tbl(i-k).REQUESTED_DATE := l_requested_date;
3802 l_req_material_tbl(i-k).UOM_CODE := l_prim_uom_code;
3803 l_req_material_tbl(i-k).OPERATION_FLAG := 'C';
3804 l_req_material_tbl(i-k).WORKORDER_ID := l_workorder_id;
3805 l_req_material_tbl(i-k).OPERATION_SEQUENCE := l_operation_sequence;
3806 l_req_material_tbl(i-k).WORKORDER_OPERATION_ID := l_workorder_operation_id;
3807 END IF;
3808
3809 ELSE
3810 -- IF the corresponding WO does not exists, create only records in the ASM table
3811 IF (l_log_statement >= l_log_current_level)THEN
3812 fnd_log.string (l_log_statement,
3813 l_debug_key,
3814 ' visit_task_id: '|| l_stage_task_rec.visit_task_id
3815 ||' ,INVENTORY_ITEM_ID: ' || l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID || ' , QUANTITY : ' || l_prim_quantity
3816 || ' , UOM_CODE: '||l_prim_uom_code||'l_stage_task_rec.planned_start_date: '||l_stage_task_rec.planned_start_date ||'l_visit_org_id: '||l_visit_org_id);
3817 END IF;
3818 Insert_Planned_Matrls(
3819 p_visit_id => p_visit_id,
3820 p_visit_task_id => l_stage_task_rec.visit_task_id,
3821 p_task_start_date => l_stage_task_rec.planned_start_date,
3822 p_inventory_item_id => l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID,
3823 p_requested_quantity => l_prim_quantity, --Quantity in primary UOM
3824 p_uom_code => l_prim_uom_code, --Primary UOM code
3825 p_item_group_id => NULL,
3826 p_rt_oper_material_id => NULL,
3827 p_position_path_id => NULL,
3828 p_relationship_id => NULL,
3829 p_mr_route_id => NULL,
3830 p_item_comp_detail_id => NULL,
3831 p_inv_master_org_id => l_visit_org_id,
3832 p_mc_header_id => NULL,
3833 p_position_key => NULL,
3834 x_return_status => l_return_status,
3835 x_msg_count => l_msg_count,
3836 x_msg_data => l_msg_data );
3837
3838 IF (l_log_statement >= l_log_current_level) THEN
3839 fnd_log.string(l_log_statement,
3840 L_DEBUG_KEY,
3841 'After calling AHL_LTP_REQST_MATRL_PVT.Insert_Planned_Matrls. l_return_status = ' || l_return_status);
3842 END IF;
3843
3844 -- Check Error Message stack.
3845 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3846 x_msg_count := l_msg_count;
3847 x_return_status := l_return_status;
3848 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3849 RAISE Fnd_Api.g_exc_error;
3850 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3851 RAISE Fnd_Api.g_exc_unexpected_error;
3852 END IF;
3853 END IF;
3854
3855 END IF; --get_workorder_detls%FOUND
3856 CLOSE get_workorder_detls;
3857 END IF; --get_scheduled_mat_detls%FOUND
3858 END LOOP;
3859
3860 IF (fnd_log.level_procedure >= l_log_current_level) THEN
3861 fnd_log.string ( fnd_log.level_procedure,
3862 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3863 'Before calling AHL_PP_MATERIALS_PVT.Process_Material_Request, l_req_material_tbl.count: '||l_req_material_tbl.count );
3864 END IF;
3865
3866 --Call AHL_PP_MATERIALS_PVT.Process_Material_Request to update materials both in WIP table and in ASM and to create in only WIP table
3867 IF l_req_material_tbl.count > 0 THEN
3868 FOR i IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST LOOP
3869 IF (l_log_statement >= l_log_current_level)THEN
3870 fnd_log.string (l_log_statement,
3871 l_debug_key,
3872 'Attributes of l_req_material_tbl as passed to AHL_PP_MATERIALS_PVT.Process_Material_Request : Row - '|| i ||' ,SCHEDULE_MATERIAL_ID: '
3873 || l_req_material_tbl(i).SCHEDULE_MATERIAL_ID||' ,INVENTORY_ITEM_ID: ' || l_req_material_tbl(i).INVENTORY_ITEM_ID || ' , REQ QUANTITY : '
3874 || l_req_material_tbl(i).REQUESTED_QUANTITY || ' , SCH QTY: '||l_req_material_tbl(i).SCHEDULED_QUANTITY||' ,WORKORDER_ID: '||l_req_material_tbl(i).WORKORDER_ID
3875 ||' ,WORKORDER_OPERATION_ID: '||l_req_material_tbl(i).WORKORDER_OPERATION_ID ||' ,OPERATION_FLAG: '||l_req_material_tbl(i).OPERATION_FLAG);
3876 END IF;
3877 END LOOP;
3878
3879 AHL_PP_MATERIALS_PVT.Process_Material_Request (
3880 p_api_version => 1.0 ,
3881 p_init_msg_list => FND_API.G_TRUE,
3882 p_commit => FND_API.G_FALSE,
3883 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3884 -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
3885 -- TCHIMIRA :: bug 14137417: uncommented the below line
3886 p_module_type => 'VWP',
3887 p_x_req_material_tbl => l_req_material_tbl,
3888 x_return_status => l_return_status,
3889 x_msg_count => l_msg_count,
3890 x_msg_data => l_msg_data );
3891 END IF;
3892
3893 IF (l_log_statement >= l_log_current_level) THEN
3894 fnd_log.string(l_log_statement,
3895 L_DEBUG_KEY,
3896 'After calling AHL_PP_MATERIALS_PVT.Process_Material_Request. l_return_status = ' || l_return_status);
3897 END IF;
3898
3899 -- Check Error Message stack.
3900 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3901 x_msg_count := l_msg_count;
3902 x_return_status := l_return_status;
3903 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3904 RAISE Fnd_Api.g_exc_error;
3905 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3906 RAISE Fnd_Api.g_exc_unexpected_error;
3907 END IF;
3908 END IF;
3909 END IF; --l_nrp_mat_req_tbl.count > 0
3910
3911
3912 IF (fnd_log.level_procedure >= l_log_current_level)THEN
3913 fnd_log.string
3914 (
3915 fnd_log.level_procedure,
3916 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Associate_Stage_Materials.end',
3917 'At the end of PLSQL procedure'
3918 );
3919 END IF;
3920
3921 EXCEPTION
3922
3923 WHEN FND_API.G_EXC_ERROR THEN
3924
3925 x_return_status := FND_API.G_RET_STS_ERROR;
3926 ROLLBACK TO Associate_Stage_Materials_pvt;
3927 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3928 p_data => x_msg_data,
3929 p_encoded => fnd_api.g_false);
3930
3931 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3933 ROLLBACK TO Associate_Stage_Materials_pvt;
3934 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3935 p_data => x_msg_data,
3936 p_encoded => fnd_api.g_false);
3937
3938 WHEN OTHERS THEN
3939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3940 ROLLBACK TO Associate_Stage_Materials_pvt;
3941
3942 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3943 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3944 p_procedure_name => 'Associate_Stage_Materials',
3945 p_error_text => SUBSTR(SQLERRM,1,500));
3946 END IF;
3947
3948 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3949 p_data => x_msg_data,
3950 p_encoded => fnd_api.g_false);
3951 END Associate_Stage_Materials;
3952
3953
3954 -------------------------------------------------------------------
3955 -- Procedure name : Remove_Stage_Materials
3956 -- Type : Private
3957 -- Function : Procedure to remove materials, based on NR profile, from stage tasks
3958 -- Parameters :
3959 --
3960 -- Standard IN Parameters :
3961 -- p_api_version IN NUMBER Required
3962 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3963 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3964 -- p_module_type IN VARCHAR2 Default Null
3965 --
3966 -- Standard OUT Parameters :
3967 -- x_return_status OUT VARCHAR2 Required
3968 -- x_msg_count OUT NUMBER Required
3969 -- x_msg_data OUT VARCHAR2 Required
3970 --
3971 -- Remove_Stage_Materials Parameters:
3972 -- p_visit_id IN NUMBER Required
3973 -- p_mr_header_id IN NUMBER Required
3974 -- p_instance_id IN NUMBER Required
3975 -- p_ue_id IN NUMBER Required
3976
3977 --
3978 -------------------------------------------------------------------
3979 PROCEDURE Remove_Stage_Materials (
3980 p_api_version IN NUMBER := 1.0,
3981 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3982 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3983 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3984 p_module_type IN VARCHAR2 := 'JSP',
3985 p_visit_id IN NUMBER,
3986 p_mr_header_id IN NUMBER,
3987 p_instance_id IN NUMBER,
3988 p_ue_id IN NUMBER,
3989 x_return_status OUT NOCOPY VARCHAR2,
3990 x_msg_count OUT NOCOPY NUMBER,
3991 x_msg_data OUT NOCOPY VARCHAR2)IS
3992
3993 -- Local Variables
3994
3995 -- Standard in/out parameters
3996 l_api_name VARCHAR2(30) := 'Remove_Stage_Materials';
3997 l_api_version NUMBER := 1.0;
3998 l_msg_count NUMBER;
3999 l_msg_data VARCHAR2(2000);
4000 l_return_status VARCHAR2(1);
4001 l_init_msg_list VARCHAR2(10):= p_init_msg_list;
4002 l_commit VARCHAR2(30) := p_commit;
4003 l_requested_quantity NUMBER;
4004 l_uom_code VARCHAR2(30);
4005 l_prim_req_quantity NUMBER;
4006 l_prim_sch_quantity NUMBER;
4007 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Stage_Materials';
4008 l_nrp_mat_req_tbl AHL_RA_NR_PROFILE_PVT.nrp_mat_req_tbl_type;
4009 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
4010 l_requested_date DATE;
4011 l_workorder_id NUMBER;
4012 l_operation_sequence NUMBER;
4013 l_workorder_operation_id NUMBER;
4014 k NUMBER := 0;
4015 l_nr_profile_qty NUMBER;
4016
4017
4018 Cursor get_visit_org_id (c_visit_id IN NUMBER)
4019 IS
4020 SELECT organization_id
4021 FROM ahl_visits_b
4022 WHERE visit_id = c_visit_id;
4023 l_visit_org_id get_visit_org_id%ROWTYPE;
4024
4025 Cursor get_scheduled_mat_detls (c_task_id IN NUMBER, c_item_id IN NUMBER)
4026 IS
4027 SELECT scheduled_material_id, requested_quantity, scheduled_quantity, uom
4028 FROM ahl_schedule_materials
4029 WHERE visit_task_id = c_task_id
4030 AND inventory_item_id = c_item_id
4031 AND status <> 'DELETED';
4032 l_scheduled_material_id NUMBER;
4033
4034 --Get the visit stage task for the given stage type
4035 --If the stage type is not found in any of the visit stages, get the default stage task of the visit
4036 Cursor get_stage_task (c_visit_id IN NUMBER,
4037 c_stage_type_code VARCHAR2)
4038 IS
4039 SELECT task.visit_task_id, task.start_date_time
4040 FROM ahl_visit_stage_typ_asoc assoc, ahl_visit_tasks_b task
4041 WHERE (assoc.stage_id = task.stage_id
4042 OR task.stage_id IS NULL)
4043 AND task.task_type_code = 'STAGE'
4044 AND task.visit_id = c_visit_id
4045 AND (assoc.stage_type_code = NVL(c_stage_type_code, '-1')
4046 OR task.stage_id IS NULL)
4047 AND task.status_code = 'PLANNING'
4048 ORDER BY task.stage_id NULLS LAST;
4049 l_stage_task_rec get_stage_task%ROWTYPE;
4050
4051 CURSOR get_workorder_detls (c_task_id IN NUMBER)
4052 IS
4053 SELECT wip.scheduled_start_date, wo.workorder_id, opr.operation_sequence_num, opr.workorder_operation_id
4054 FROM ahl_workorders wo, wip_discrete_jobs wip, ahl_workorder_operations opr
4055 WHERE wo.visit_task_id = c_task_id
4056 AND wo.wip_entity_id = wip.wip_entity_id
4057 AND opr.workorder_id = wo.workorder_id;
4058
4059 BEGIN
4060
4061 IF (fnd_log.level_procedure >= l_log_current_level)THEN
4062 fnd_log.string (fnd_log.level_procedure,
4063 L_DEBUG_KEY,
4064 'At the start of PLSQL procedure');
4065 END IF;
4066
4067 -- Standard start of API savepoint
4068 SAVEPOINT Remove_Stage_Materials_pvt;
4069
4070 -- Initialize message list if p_init_msg_list is set to TRUE
4071
4072 IF FND_API.To_Boolean( p_init_msg_list) THEN
4073 FND_MSG_PUB.Initialize;
4074 END IF;
4075
4076 -- Initialize API return status to success
4077 x_return_status := FND_API.G_RET_STS_SUCCESS;
4078
4079 -- Standard call to check for call compatibility.
4080 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4081 p_api_version,
4082 l_api_name,G_PKG_NAME)
4083 THEN
4084 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4085 END IF;
4086
4087 -- TCHIMIRA :: 15-JUL-2011 :: VWPE: ER12730539 :: If the visit is old, NR Profile is not applicable
4088 IF AHL_VWP_VISITS_PVT.Is_Old_Visit(p_visit_id) = 'Y' THEN
4089 RETURN;
4090 END IF;
4091
4092 IF (l_log_statement >= l_log_current_level)THEN
4093 fnd_log.string ( l_log_statement,
4094 l_debug_key,
4095 'Before calling AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ, p_mr_header_id : '
4096 || p_mr_header_id || ' , p_instance_id: '||p_instance_id ||' , p_ue_id: '||p_ue_id);
4097 END IF;
4098 --Get all the NR Profile material for the given MR
4099 AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ
4100 (p_mr_header_id => p_mr_header_id,
4101 p_instance_id => p_instance_id,
4102 p_ue_id => p_ue_id,
4103 p_plan_id => null,
4104 x_return_status => l_return_status,
4105 x_msg_count => l_msg_count,
4106 x_msg_data => l_msg_data,
4107 x_prof_mat_req_tbl => l_nrp_mat_req_tbl
4108 );
4109
4110 IF (l_log_statement >= l_log_current_level) THEN
4111 fnd_log.string(l_log_statement,
4112 L_DEBUG_KEY,
4113 'After calling AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ. l_return_status = ' || l_return_status);
4114 END IF;
4115
4116 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4117 x_msg_count := FND_MSG_PUB.count_msg;
4118 IF (l_log_statement >= l_log_current_level) THEN
4119 fnd_log.string(l_log_statement,
4120 L_DEBUG_KEY,
4121 'Errors from AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ. Message count: ' || x_msg_count);
4122 END IF;
4123 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4124 RAISE FND_API.G_EXC_ERROR;
4125 ELSE
4126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4127 END IF;
4128 END IF;
4129
4130 IF l_nrp_mat_req_tbl.count > 0 THEN
4131
4132 OPEN get_visit_org_id (p_visit_id);
4133 FETCH get_visit_org_id INTO l_visit_org_id;
4134 CLOSE get_visit_org_id;
4135
4136 FOR i IN l_nrp_mat_req_tbl.FIRST..l_nrp_mat_req_tbl.LAST LOOP
4137 --For the stage type in l_nrp_mat_req_tbl, get the corresponding stage task
4138 --If the stage type is not present in any of the stages, get the default stage task of the visit
4139 OPEN get_stage_task (p_visit_id, l_nrp_mat_req_tbl(i).STAGE_TYPE_CODE);
4140 FETCH get_stage_task INTO l_stage_task_rec;
4141 CLOSE get_stage_task;
4142
4143 OPEN get_scheduled_mat_detls (l_stage_task_rec.visit_task_id, l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID);
4144 FETCH get_scheduled_mat_detls INTO l_scheduled_material_id, l_requested_quantity, l_prim_sch_quantity, l_uom_code;
4145 IF get_scheduled_mat_detls%FOUND THEN
4146 l_prim_req_quantity := AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID, l_nrp_mat_req_tbl(i).UOM_CODE, l_nrp_mat_req_tbl(i).QUANTITY);
4147 l_nr_profile_qty := l_prim_req_quantity;
4148
4149 IF l_prim_req_quantity > l_prim_sch_quantity THEN
4150 l_prim_sch_quantity := 0;
4151 ELSE
4152 --Since this API is used to remove the items subtract the quantity passed in l_nrp_mat_req_tbl from the quantity in ASM
4153 --The record in ASM is always in primary UOM
4154 l_prim_sch_quantity := l_prim_sch_quantity - l_prim_req_quantity;
4155 END IF;
4156 IF l_prim_req_quantity > l_requested_quantity THEN
4157 l_prim_req_quantity := 0;
4158 ELSE
4159 --Since this API is used to remove the items subtract the quantity passed in l_nrp_mat_req_tbl from the quantity in ASM
4160 --The record in ASM is always in primary UOM
4161 l_prim_req_quantity := l_requested_quantity - l_prim_req_quantity;
4162 END IF;
4163
4164 OPEN get_workorder_detls (l_stage_task_rec.visit_task_id);
4165 FETCH get_workorder_detls INTO l_requested_date, l_workorder_id, l_operation_sequence, l_workorder_operation_id;
4166 IF get_workorder_detls%FOUND THEN
4167 -- Update WIP table with these materials
4168 IF (l_log_statement >= l_log_current_level)THEN
4169 fnd_log.string
4170 (
4171 l_log_statement,
4172 l_debug_key,
4173 'Attributes of l_req_material_tbl passed to AHL_PP_MATERIALS_PVT.Process_Material_Request : Row - '|| i ||' - INVENTORY_ITEM_ID: ' || l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID
4174 || ' , Req QUANTITY : ' || l_prim_req_quantity || ' , Sch QUANTITY : ' || l_prim_sch_quantity || ' , UOM_CODE: '||l_uom_code || ' , l_workorder_id: '||l_workorder_id
4175 );
4176 END IF;
4177 -- SKPATHAK :: 18-JUL-2011 :: VWPE 12730539
4178 -- The NR Profile materials may again be repeated for the same stage, so need to handle the repetition of NR Profile material records
4179 IF l_req_material_tbl.count > 0 THEN
4180 For j IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST LOOP
4181 IF l_req_material_tbl(j).SCHEDULE_MATERIAL_ID = l_scheduled_material_id THEN
4182 IF (l_req_material_tbl(j).REQUESTED_QUANTITY - l_nr_profile_qty) < 0 THEN
4183 l_req_material_tbl(j).REQUESTED_QUANTITY := 0;
4184 ELSE
4185 l_req_material_tbl(j).REQUESTED_QUANTITY := l_req_material_tbl(j).REQUESTED_QUANTITY - l_nr_profile_qty;
4186 END IF;
4187 IF (l_req_material_tbl(j).SCHEDULED_QUANTITY - l_nr_profile_qty) < 0 THEN
4188 l_req_material_tbl(j).SCHEDULED_QUANTITY := 0;
4189 ELSE
4190 l_req_material_tbl(j).SCHEDULED_QUANTITY := l_req_material_tbl(j).SCHEDULED_QUANTITY - l_nr_profile_qty;
4191 END IF;
4192 k := k+1;
4193 END IF;
4194 END LOOP;
4195 END IF;
4196 IF l_req_material_tbl.count < (i-k) THEN
4197 l_req_material_tbl(i-k).SCHEDULE_MATERIAL_ID := l_scheduled_material_id;
4198 l_req_material_tbl(i-k).INVENTORY_ITEM_ID := l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID;
4199 l_req_material_tbl(i-k).REQUESTED_QUANTITY := l_prim_req_quantity;
4200 l_req_material_tbl(i-k).SCHEDULED_QUANTITY := l_prim_sch_quantity;
4201 l_req_material_tbl(i-k).REQUESTED_DATE := l_requested_date;
4202 l_req_material_tbl(i-k).UOM_CODE := l_uom_code;
4203 l_req_material_tbl(i-k).OPERATION_FLAG := 'U';
4204 l_req_material_tbl(i-k).WORKORDER_ID := l_workorder_id;
4205 l_req_material_tbl(i-k).OPERATION_SEQUENCE := l_operation_sequence;
4206 l_req_material_tbl(i-k).WORKORDER_OPERATION_ID := l_workorder_operation_id;
4207 END IF;
4208 ELSE
4209 -- IF the corresponding WO does not exists, update only the ASM table
4210 UPDATE ahl_schedule_materials
4211 SET requested_quantity = l_prim_req_quantity,
4212 scheduled_quantity = l_prim_sch_quantity,
4213 object_version_number = object_version_number + 1,
4214 last_update_date = SYSDATE,
4215 last_updated_by = Fnd_Global.USER_ID,
4216 last_update_login = Fnd_Global.LOGIN_ID
4217 WHERE scheduled_material_id = l_scheduled_material_id;
4218 END IF;
4219 CLOSE get_workorder_detls;
4220
4221 END IF; --get_scheduled_mat_detls%FOUND
4222
4223 CLOSE get_scheduled_mat_detls;
4224 END LOOP;
4225
4226 IF (fnd_log.level_procedure >= l_log_current_level) THEN
4227 fnd_log.string(fnd_log.level_procedure,
4228 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4229 'Before calling AHL_PP_MATERIALS_PVT.Process_Material_Request, l_req_material_tbl.count= '||l_req_material_tbl.count );
4230 END IF;
4231
4232 --Call AHL_PP_MATERIALS_PVT.Process_Material_Request to update materials both in WIP table and in ASM
4233 IF l_req_material_tbl.count > 0 THEN
4234 FOR i IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST LOOP
4235 IF (l_log_statement >= l_log_current_level)THEN
4236 fnd_log.string (l_log_statement,
4237 l_debug_key,
4238 'Attributes of l_req_material_tbl as passed to AHL_PP_MATERIALS_PVT.Process_Material_Request : Row - '|| i
4239 ||' ,INVENTORY_ITEM_ID: ' || l_req_material_tbl(i).INVENTORY_ITEM_ID || ' , REQ QUANTITY : ' || l_req_material_tbl(i).REQUESTED_QUANTITY
4240 || ' , SCH QTY: '||l_req_material_tbl(i).SCHEDULED_QUANTITY||' ,WORKORDER_ID: '||l_req_material_tbl(i).WORKORDER_ID
4241 ||' ,WORKORDER_OPERATION_ID: '||l_req_material_tbl(i).WORKORDER_OPERATION_ID ||' ,OPERATION_FLAG: '||l_req_material_tbl(i).OPERATION_FLAG);
4242 END IF;
4243 END LOOP;
4244 AHL_PP_MATERIALS_PVT.Process_Material_Request (
4245 p_api_version => 1.0 ,
4246 p_init_msg_list => FND_API.G_TRUE,
4247 p_commit => FND_API.G_FALSE,
4248 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4249 -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4250 -- TCHIMIRA :: bug 14137417: uncommented the below line
4251 p_module_type => 'VWP',
4252 p_x_req_material_tbl => l_req_material_tbl,
4253 x_return_status => l_return_status,
4254 x_msg_count => l_msg_count,
4255 x_msg_data => l_msg_data
4256 );
4257 END IF;
4258
4259 IF (l_log_statement >= l_log_current_level) THEN
4260 fnd_log.string(l_log_statement,
4261 L_DEBUG_KEY,
4262 'After calling AHL_PP_MATERIALS_PVT.Process_Material_Request. l_return_status = ' || l_return_status);
4263 END IF;
4264
4265 -- Check Error Message stack.
4266 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4267 x_msg_count := l_msg_count;
4268 x_return_status := l_return_status;
4269 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4270 RAISE Fnd_Api.g_exc_error;
4271 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4272 RAISE Fnd_Api.g_exc_unexpected_error;
4273 END IF;
4274 END IF;
4275
4276 END IF; --l_nrp_mat_req_tbl.count > 0
4277
4278
4279 IF (fnd_log.level_procedure >= l_log_current_level)THEN
4280 fnd_log.string
4281 (
4282 fnd_log.level_procedure,
4283 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Stage_Materials.end',
4284 'At the end of PLSQL procedure'
4285 );
4286 END IF;
4287
4288 EXCEPTION
4289
4290 WHEN FND_API.G_EXC_ERROR THEN
4291
4292 x_return_status := FND_API.G_RET_STS_ERROR;
4293 ROLLBACK TO Remove_Stage_Materials_pvt;
4294 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4295 p_data => x_msg_data,
4296 p_encoded => fnd_api.g_false);
4297
4298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4300 ROLLBACK TO Remove_Stage_Materials_pvt;
4301 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4302 p_data => x_msg_data,
4303 p_encoded => fnd_api.g_false);
4304
4305 WHEN OTHERS THEN
4306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4307 ROLLBACK TO Remove_Stage_Materials_pvt;
4308
4309 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4310 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4311 p_procedure_name => 'Remove_Stage_Materials',
4312 p_error_text => SUBSTR(SQLERRM,1,500));
4313 END IF;
4314
4315 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4316 p_data => x_msg_data,
4317 p_encoded => fnd_api.g_false);
4318 END Remove_Stage_Materials;
4319 -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 :: END
4320
4321 -- TCHIMIRA :: 22-JUN-2011 :: VWPE: ER:12673125 :: START
4322 -------------------------------------------------------------------
4323 -- Procedure name : Delete_Default_Stage_Materials
4324 -- Type : Private
4325 -- Function : Procedure to delete materials of default stage task when a visit is P2Ped
4326 -- Parameters :
4327 --
4328 -- Standard IN Parameters :
4329 -- p_api_version IN NUMBER Required
4330 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4331 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4332 -- p_module_type IN VARCHAR2 Default Null
4333 --
4334 -- Standard OUT Parameters :
4335 -- x_return_status OUT VARCHAR2 Required
4336 -- x_msg_count OUT NUMBER Required
4337 -- x_msg_data OUT VARCHAR2 Required
4338 --
4339 -- Delete_Default_Stage_Materials Parameters:
4340 -- p_visit_task_id IN NUMBER Required
4341 --
4342 -------------------------------------------------------------------
4343 PROCEDURE Delete_Default_Stage_Materials (
4344 p_api_version IN NUMBER := 1.0,
4345 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4346 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4347 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4348 p_module_type IN VARCHAR2 := 'JSP',
4349 p_visit_task_id IN NUMBER,
4350 x_return_status OUT NOCOPY VARCHAR2,
4351 x_msg_count OUT NOCOPY NUMBER,
4352 x_msg_data OUT NOCOPY VARCHAR2)IS
4353
4354 -- Local Variables
4355
4356 -- Standard in/out parameters
4357 l_api_name VARCHAR2(30) := 'Delete_Default_Stage_Materials';
4358 l_api_version NUMBER := 1.0;
4359 l_msg_count NUMBER;
4360 l_msg_data VARCHAR2(2000);
4361 l_return_status VARCHAR2(1);
4362 l_init_msg_list VARCHAR2(10):= p_init_msg_list;
4363 l_commit VARCHAR2(30) := p_commit;
4364 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Delete_Default_Stage_Materials';
4365 l_requested_date DATE;
4366 l_workorder_id NUMBER;
4367 l_operation_sequence NUMBER;
4368 l_workorder_operation_id NUMBER;
4369 l_visit_id NUMBER;
4370 i NUMBER := 0 ;
4371 l_workorder_tbl AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_TBL;
4372 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
4373
4374 -- Cursor to get the materials associated to the default stage task
4375 Cursor get_scheduled_mat_detls (c_task_id IN NUMBER)
4376 IS
4377 SELECT scheduled_material_id, uom,inventory_item_id
4378 FROM ahl_schedule_materials
4379 WHERE visit_task_id = c_task_id
4380 AND status <> 'DELETED';
4381
4382 -- Cursor to workorder details of the default stage task
4383 CURSOR get_workorder_detls (c_task_id IN NUMBER)
4384 IS
4385 SELECT wip.scheduled_start_date, wo.workorder_id, opr.operation_sequence_num, opr.workorder_operation_id, wo.visit_id
4386 FROM ahl_workorders wo, wip_discrete_jobs wip, ahl_workorder_operations opr
4387 WHERE wo.visit_task_id = c_task_id
4388 AND wo.wip_entity_id = wip.wip_entity_id
4389 AND opr.workorder_id = wo.workorder_id;
4390
4391 BEGIN
4392
4393 IF (fnd_log.level_procedure >= l_log_current_level)THEN
4394 fnd_log.string (fnd_log.level_procedure,
4395 L_DEBUG_KEY,
4396 'At the start of PLSQL procedure');
4397 END IF;
4398
4399 -- Standard start of API savepoint
4400 SAVEPOINT Delete_Default_Stage_Materials;
4401
4402 -- Initialize message list if p_init_msg_list is set to TRUE
4403
4404 IF FND_API.To_Boolean( p_init_msg_list) THEN
4405 FND_MSG_PUB.Initialize;
4406 END IF;
4407
4408 -- Initialize API return status to success
4409 x_return_status := FND_API.G_RET_STS_SUCCESS;
4410
4411 -- Standard call to check for call compatibility.
4412 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4413 p_api_version,
4414 l_api_name,G_PKG_NAME)
4415 THEN
4416 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4417 END IF;
4418
4419 OPEN get_workorder_detls (p_visit_task_id);
4420 FETCH get_workorder_detls INTO l_requested_date, l_workorder_id, l_operation_sequence, l_workorder_operation_id, l_visit_id;
4421 IF (fnd_log.level_procedure >= l_log_current_level) THEN
4422 fnd_log.string(fnd_log.level_procedure,L_DEBUG_KEY,
4423 'l_workorder_id: '||l_workorder_id||', l_workorder_operation_id: '||l_workorder_operation_id||',l_operation_sequence '||l_operation_sequence||', l_requested_date: '|| l_requested_date);
4424 END IF;
4425 l_workorder_tbl(1).WORKORDER_ID := l_workorder_id;
4426 l_workorder_tbl(1).VISIT_TASK_ID := p_visit_task_id;
4427 l_workorder_tbl(1).VISIT_ID := l_visit_id;
4428 CLOSE get_workorder_detls;
4429
4430 -- TCHIMIRA :: 15-JUL-2011 :: VWPE: ER12730539 :: If the visit is old, NR Profile is not applicable
4431 IF AHL_VWP_VISITS_PVT.Is_Old_Visit(l_visit_id) = 'Y' THEN
4432 RETURN;
4433 END IF;
4434
4435 -- Loop through the records of the default stage materials
4436 FOR l_scheduled_mat_rec IN get_scheduled_mat_detls(p_visit_task_id) LOOP
4437 -- Update WIP table with requested quantity as zero
4438 l_req_material_tbl(i).SCHEDULE_MATERIAL_ID := l_scheduled_mat_rec.scheduled_material_id;
4439 l_req_material_tbl(i).INVENTORY_ITEM_ID := l_scheduled_mat_rec.inventory_item_id;
4440 l_req_material_tbl(i).REQUESTED_QUANTITY := 0;
4441 l_req_material_tbl(i).SCHEDULED_QUANTITY := 0;
4442 l_req_material_tbl(i).REQUESTED_DATE := l_requested_date;
4443 l_req_material_tbl(i).UOM_CODE := l_scheduled_mat_rec.uom;
4444 l_req_material_tbl(i).OPERATION_FLAG := 'U';
4445 l_req_material_tbl(i).WORKORDER_ID := l_workorder_id;
4446 l_req_material_tbl(i).OPERATION_SEQUENCE := l_operation_sequence;
4447 l_req_material_tbl(i).WORKORDER_OPERATION_ID := l_workorder_operation_id;
4448 i := i + 1;
4449 END LOOP;
4450
4451 IF (fnd_log.level_procedure >= l_log_current_level) THEN
4452 fnd_log.string(fnd_log.level_procedure,L_DEBUG_KEY,
4453 'Before calling AHL_PP_MATERIALS_PVT.Process_Material_Request, l_req_material_tbl.count= '||l_req_material_tbl.count );
4454 END IF;
4455
4456 --Call AHL_PP_MATERIALS_PVT.Process_Material_Request to update materials both in WIP table and in ASM
4457 IF l_req_material_tbl.count > 0 THEN
4458 AHL_PP_MATERIALS_PVT.Process_Material_Request (
4459 p_api_version => 1.0 ,
4460 p_init_msg_list => FND_API.G_TRUE,
4461 p_commit => FND_API.G_FALSE,
4462 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4463 -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4464 -- TCHIMIRA :: bug 14137417: uncommented the below line
4465 p_module_type => 'VWP',
4466 p_x_req_material_tbl => l_req_material_tbl,
4467 x_return_status => l_return_status,
4468 x_msg_count => l_msg_count,
4469 x_msg_data => l_msg_data
4470 );
4471 END IF;
4472
4473 IF (l_log_statement >= l_log_current_level) THEN
4474 fnd_log.string(l_log_statement,
4475 L_DEBUG_KEY,
4476 'After calling AHL_PP_MATERIALS_PVT.Process_Material_Request. l_return_status = ' || l_return_status);
4477 END IF;
4478
4479 -- Check Error Message stack.
4480 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4481 x_msg_count := l_msg_count;
4482 x_return_status := l_return_status;
4483 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4484 RAISE Fnd_Api.g_exc_error;
4485 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4486 RAISE Fnd_Api.g_exc_unexpected_error;
4487 END IF;
4488 END IF;
4489
4490 IF (fnd_log.level_procedure >= l_log_current_level) THEN
4491 fnd_log.string(fnd_log.level_procedure,L_DEBUG_KEY,
4492 'Before calling AHL_PRD_WORKORDER_PVT.process_stage_jobs and l_workorder_tbl count is:'||l_workorder_tbl.count);
4493 END IF;
4494
4495 --Call AHL_PRD_WORKORDER_PVT.process_stage_jobs to remove resource requirements
4496 -- from default stage work order after visit is P2Ped.
4497 IF l_workorder_tbl.count > 0 THEN
4498 AHL_PRD_WORKORDER_PVT.process_stage_jobs (
4499 p_api_version => 1.0 ,
4500 p_init_msg_list => FND_API.G_TRUE,
4501 p_commit => FND_API.G_FALSE,
4502 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4503 p_module_type => 'VWP',
4504 p_workorder_tbl => l_workorder_tbl,
4505 x_return_status => l_return_status,
4506 x_msg_count => l_msg_count,
4507 x_msg_data => l_msg_data
4508 );
4509 END IF;
4510
4511 IF (l_log_statement >= l_log_current_level) THEN
4512 fnd_log.string(l_log_statement,
4513 L_DEBUG_KEY,
4514 'After calling AHL_PRD_WORKORDER_PVT.process_stage_jobs. l_return_status = ' || l_return_status);
4515 END IF;
4516
4517 -- Check Error Message stack.
4518 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4519 x_msg_count := l_msg_count;
4520 x_return_status := l_return_status;
4521 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4522 RAISE Fnd_Api.g_exc_error;
4523 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4524 RAISE Fnd_Api.g_exc_unexpected_error;
4525 END IF;
4526 END IF;
4527
4528 IF (fnd_log.level_procedure >= l_log_current_level)THEN
4529 fnd_log.string(fnd_log.level_procedure,
4530 L_DEBUG_KEY,
4531 'At the end of PLSQL procedure');
4532 END IF;
4533
4534 EXCEPTION
4535
4536 WHEN FND_API.G_EXC_ERROR THEN
4537
4538 x_return_status := FND_API.G_RET_STS_ERROR;
4539 ROLLBACK TO Delete_Default_Stage_Materials;
4540 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4541 p_data => x_msg_data,
4542 p_encoded => fnd_api.g_false);
4543
4544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4546 ROLLBACK TO Delete_Default_Stage_Materials;
4547 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4548 p_data => x_msg_data,
4549 p_encoded => fnd_api.g_false);
4550
4551 WHEN OTHERS THEN
4552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4553 ROLLBACK TO Delete_Default_Stage_Materials;
4554
4555 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4556 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4557 p_procedure_name => 'Delete_Default_Stage_Materials',
4558 p_error_text => SUBSTR(SQLERRM,1,500));
4559 END IF;
4560
4561 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4562 p_data => x_msg_data,
4563 p_encoded => fnd_api.g_false);
4564 END Delete_Default_Stage_Materials;
4565
4566 -- TCHIMIRA :: 22-JUN-2011 :: VWPE: ER:12673125 :: END
4567
4568 END AHL_LTP_REQST_MATRL_PVT;