[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_ROUTE_PUB
Source
1 PACKAGE BODY AHL_RM_ROUTE_PUB AS
2 /* $Header: AHLPROUB.pls 120.0.12000000.2 2007/10/18 13:55:37 adivenka ship $ */
3 -- G_PKG_NAME VARCHAR2(50):= 'AHL_RM_ROUTE_PUB';
4
5
6
7 PROCEDURE Create_Route
8 (
9 -- standard IN params
10 p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
12 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
13 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
14 p_default IN VARCHAR2 :=FND_API.G_FALSE,
15 p_module_type IN VARCHAR2 :=NULL,
16 -- standard OUT params
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2,
20 -- procedure params
21 p_x_route_rec IN OUT NOCOPY AHL_RM_ROUTE_PVT.route_rec_type,
22 p_x_route_doc_tbl IN OUT NOCOPY AHL_RM_ASSO_DOCASO_PVT.doc_association_tbl,
23 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type,
24 p_x_route_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_resource_tbl_type,
25 p_x_route_material_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type,
26 p_x_route_panel_tbl IN OUT NOCOPY AHL_RM_RT_OPER_PANEL_PVT.rt_oper_panel_tbl_type
27 )
28 IS
29 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ROUTE';
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
32 l_x_rt_oper_cost_rec AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_cost_rec_type;
33
34 BEGIN
35
36 -- Initialize API return status to success
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39 -- Standard Start of API savepoint
40 SAVEPOINT create_route_pub;
41
42 -- Standard call to check for call compatibility.
43 IF NOT FND_API.compatible_api_call
44 (
45 l_api_version,
46 p_api_version,
47 l_api_name,
48 G_PKG_NAME
49 )
50 THEN
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52 END IF;
53
54 -- Initialize message list if p_init_msg_list is set to TRUE.
55 IF FND_API.to_boolean( p_init_msg_list ) THEN
56 FND_MSG_PUB.initialize;
57 END IF;
58
59 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
60 fnd_log.string
61 (
62 fnd_log.level_procedure,
63 l_debug_module ||'.begin',
64 'At the start of PLSQL procedure'
65 );
66 END IF;
67
68 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
69
70 fnd_log.string
71 (
72 fnd_log.level_statement,
73 l_debug_module ,
74 'Before calling the private API AHL_RM_ROUTE_PVT.process_route.....'
75 );
76
77 END IF;
78
79 -- PRITHWI: discuss with Shailaja whether to default to 'C' or to throw error if there is no Defaul
80 IF p_x_route_rec.DML_OPERATION = 'C'
81 THEN
82
83 AHL_RM_ROUTE_PVT.process_route
84 (
85 p_api_version => 1.0,
86 p_init_msg_list => FND_API.G_FALSE,
87 p_commit => FND_API.G_FALSE,
88 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
89 p_default => p_default,
90 p_module_type => p_module_type,
91 x_return_status => x_return_status,
92 x_msg_count => x_msg_count,
93 x_msg_data => x_msg_data,
94 p_x_route_rec => p_x_route_rec
95 );
96
97 -- to raise errors from AHL_RM_ROUTE_PVT.process_ROUTE
98 x_msg_count := FND_MSG_PUB.count_msg;
99
100 IF x_msg_count > 0 THEN
101 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 RAISE FND_API.G_EXC_ERROR;
103 END IF;
104
105 -- if the ROUTE ID returned is null then throw error
106 IF p_x_route_rec.ROUTE_ID IS NULL THEN
107
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109
110 END IF;
111
112
113 ElSE
114 -- If the DML operation is not Cretae then its invalid
115 FND_MESSAGE.SET_NAME('AHL','AHL_COM_INVALID_DML_FLAG');
116 FND_MSG_PUB.ADD;
117 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
118 fnd_log.string
119 (
120 fnd_log.level_error,
121 l_debug_module ,
122 'Invalid DML Operation is specified....DML Operation should be create'
123 );
124 END IF;
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127
128 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
129
130 fnd_log.string
131 (
132 fnd_log.level_statement,
133 l_debug_module ,
134 'Route Id of the Route created .....' || p_x_route_rec.ROUTE_ID
135 );
136
137 END IF;
138
139
140 -- populate the record attributes that are necessary for the API
141
142 -- Attach documents
143 IF p_x_route_doc_tbl.count > 0 THEN
144
145 FOR i in p_x_route_doc_tbl.FIRST .. p_x_route_doc_tbl.LAST
146 LOOP
147
148 p_x_route_doc_tbl(i).OBJECT_TYPE_CODE := 'ROUTE';
149 p_x_route_doc_tbl(i).OBJECT_ID := p_x_route_rec.ROUTE_ID;
150 p_x_route_doc_tbl(i).DML_OPERATION := 'C';
151
152 END LOOP;
153
154 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
155
156 fnd_log.string
157 (
158 fnd_log.level_statement,
159 l_debug_module ,
160 'Before calling the private API AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION.....'
161 );
162
163 END IF;
164
165 AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION
166 (
167
168 p_api_version => 1.0,
169 p_init_msg_list => FND_API.G_FALSE,
170 p_commit => FND_API.G_FALSE,
171 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
172 p_default => p_default,
173 p_module_type => p_module_type,
174 x_return_status => x_return_status,
175 x_msg_count => x_msg_count,
176 x_msg_data => x_msg_data,
177 p_x_association_tbl => p_x_route_doc_tbl
178 );
179
180 -- to raise errors from AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION
181 x_msg_count := FND_MSG_PUB.count_msg;
182
183 IF x_msg_count > 0 THEN
184 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 END IF;
189
190 -- Attach Resources
191 IF p_x_route_resource_tbl.count > 0 THEN
192
193 FOR i in p_x_route_resource_tbl.FIRST .. p_x_route_resource_tbl.LAST
194 LOOP
195
196 p_x_route_resource_tbl(i).DML_OPERATION := 'C';
197
198 END LOOP;
199
200 AHL_RM_RT_OPER_RESOURCE_PVT.process_rt_oper_resource
201 (
202 p_api_version => 1.0,
203 p_init_msg_list => FND_API.G_FALSE,
204 p_commit => FND_API.G_FALSE,
205 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
206 p_default => p_default,
207 p_module_type => p_module_type,
208 x_return_status => x_return_status,
209 x_msg_count => x_msg_count,
210 x_msg_data => x_msg_data,
211 p_x_rt_oper_resource_tbl => p_x_route_resource_tbl,
212 p_association_type_code => 'ROUTE',
213 p_object_id => p_x_route_rec.ROUTE_ID
214 );
215
216 -- to raise errors from AHL_RM_RT_OPER_RESOURCE_PVT.process_rt_oper_resource
217 x_msg_count := FND_MSG_PUB.count_msg;
218
219 IF x_msg_count > 0 THEN
220 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223 -- populate the cost rec from the resource rec and call the cost api to add costing info
224 -- per resource rec
225 FOR i IN 1..p_x_route_resource_tbl.count LOOP
226
227 l_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID := p_x_route_resource_tbl(i).RT_OPER_RESOURCE_ID ;
228 l_x_rt_oper_cost_rec.OBJECT_VERSION_NUMBER := p_x_route_resource_tbl(i).OBJECT_VERSION_NUMBER;
229 l_x_rt_oper_cost_rec.ACTIVITY_ID := p_x_route_resource_tbl(i).ACTIVITY_ID;
230 l_x_rt_oper_cost_rec.ACTIVITY := p_x_route_resource_tbl(i).ACTIVITY;
231 l_x_rt_oper_cost_rec.COST_BASIS_ID := p_x_route_resource_tbl(i).COST_BASIS_ID;
232 l_x_rt_oper_cost_rec.COST_BASIS := p_x_route_resource_tbl(i).COST_BASIS;
233 l_x_rt_oper_cost_rec.SCHEDULED_TYPE_ID := p_x_route_resource_tbl(i).SCHEDULED_TYPE_ID;
234 l_x_rt_oper_cost_rec.SCHEDULED_TYPE := p_x_route_resource_tbl(i).SCHEDULED_TYPE;
235 l_x_rt_oper_cost_rec.AUTOCHARGE_TYPE_ID := p_x_route_resource_tbl(i).AUTOCHARGE_TYPE_ID;
236 l_x_rt_oper_cost_rec.AUTOCHARGE_TYPE := p_x_route_resource_tbl(i).AUTOCHARGE_TYPE;
237 l_x_rt_oper_cost_rec.STANDARD_RATE_FLAG := p_x_route_resource_tbl(i).STANDARD_RATE_FLAG;
238 l_x_rt_oper_cost_rec.STANDARD_RATE := p_x_route_resource_tbl(i).STANDARD_RATE;
239
240 AHL_RM_RT_OPER_RESOURCE_PVT.define_cost_parameter
241 (
242 p_api_version => 1.0,
243 p_init_msg_list => FND_API.G_FALSE,
244 p_commit => FND_API.G_FALSE,
245 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
246 p_default => FND_API.G_FALSE,
247 p_module_type => NULL,
248 x_return_status => x_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data,
251 p_x_rt_oper_cost_rec => l_x_rt_oper_cost_rec
252 );
253
254 x_msg_count := FND_MSG_PUB.count_msg;
255
256 IF x_msg_count > 0 THEN
257 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 RAISE FND_API.G_EXC_ERROR;
259 END IF;
260
261 END LOOP;
262
263 END IF;
264
265
266 --Attach Material Requirements
267 IF p_x_route_material_tbl.count > 0 THEN
268
269 FOR i in p_x_route_material_tbl.FIRST .. p_x_route_material_tbl.LAST
270 LOOP
271
272 p_x_route_material_tbl(i).DML_OPERATION := 'C';
273
274 END LOOP;
275
276 AHL_RM_MATERIAL_AS_PVT.process_material_req
277 (
278 p_api_version => 1.0,
279 p_init_msg_list => FND_API.G_FALSE,
280 p_commit => FND_API.G_FALSE,
281 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
282 p_default => p_default,
283 p_module_type => p_module_type,
284 x_return_status => x_return_status,
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data,
287 p_x_material_req_tbl => p_x_route_material_tbl,
288 p_object_id => p_x_route_rec.ROUTE_ID,
289 p_association_type => 'ROUTE'
290 );
291 -- to raise errors from AHL_RM_MATERIAL_AS_PVT.process_material_req
292 x_msg_count := FND_MSG_PUB.count_msg;
293
294 IF x_msg_count > 0 THEN
295 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 RAISE FND_API.G_EXC_ERROR;
297 END IF;
298
299 END IF;
300
301 --Adithya added for Access Panels ER# 6143687.
302 --Attach Access Panels
303
304 IF p_x_route_panel_tbl.count > 0 THEN
305
306 FOR i in p_x_route_panel_tbl.FIRST .. p_x_route_panel_tbl.LAST
307 LOOP
308 p_x_route_panel_tbl(i).DML_OPERATION := 'C';
309 END LOOP;
310
311 AHL_RM_RT_OPER_PANEL_PVT.process_rt_oper_panel
312 (
313 p_api_version => 1.0,
314 p_init_msg_list => FND_API.G_FALSE,
315 p_commit => FND_API.G_FALSE,
316 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
317 p_default => p_default,
318 p_module_type => p_module_type,
319 x_return_status => x_return_status,
320 x_msg_count => x_msg_count,
321 x_msg_data => x_msg_data,
322 p_x_rt_oper_panel_tbl => p_x_route_panel_tbl,
323 p_association_type_code => 'ROUTE',
324 p_object_id => p_x_route_rec.ROUTE_ID
325 );
326 -- to raise errors from AHL_RM_RT_OPER_PANEL_PVT.process_rt_oper_panel
327 x_msg_count := FND_MSG_PUB.count_msg;
328
329 IF x_msg_count > 0 THEN
330 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331 RAISE FND_API.G_EXC_ERROR;
332 END IF;
333
334 END IF;
335
336 --Attach Operations
337 IF p_x_route_operation_tbl.count > 0 THEN
338
339 FOR i in p_x_route_operation_tbl.FIRST .. p_x_route_operation_tbl.LAST
340 LOOP
341
342 p_x_route_operation_tbl(i).DML_OPERATION := 'C';
343
344 END LOOP;
345
346 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
347 (
348 p_api_version => 1.0,
349 p_init_msg_list => FND_API.G_FALSE,
350 p_commit => FND_API.G_FALSE,
351 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
352 p_default => p_default,
353 p_module_type => p_module_type,
354 x_return_status => x_return_status,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 p_x_route_operation_tbl => p_x_route_operation_tbl,
358 p_route_id => p_x_route_rec.ROUTE_ID
359 );
360
361 -- to raise errors from AHL_RM_MATERIAL_AS_PVT.process_material_req
362 x_msg_count := FND_MSG_PUB.count_msg;
363
364 IF x_msg_count > 0 THEN
365 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 END IF;
370
371 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
372 fnd_log.string
373 (
374 fnd_log.level_procedure,
375 l_debug_module ||'.End',
376 'At the end of PLSQL procedure...'
377 );
378 END IF;
379
380 -- Check Error Message stack.
381 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
382 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
383 fnd_log.string
384 (
385 fnd_log.level_error,
386 l_debug_module ,
387 'Private API raised expected error....'
388 );
389 END IF;
390 RAISE FND_API.G_EXC_ERROR;
391 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
392 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
393 fnd_log.string
394 (
395 fnd_log.level_error,
396 l_debug_module ,
397 'Private API raised unexpected error....'
398 );
399 END IF;
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 -- Standard check of p_commit
404 IF FND_API.To_Boolean(p_commit) THEN
405 COMMIT WORK;
406 END IF;
407
408 --
409 EXCEPTION
410 WHEN FND_API.G_EXC_ERROR THEN
411 x_return_status := FND_API.G_RET_STS_ERROR;
412 Rollback to create_route_pub;
413 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
414 p_data => x_msg_data,
415 p_encoded => fnd_api.g_false);
416
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420 Rollback to create_route_pub;
421 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
422 p_data => x_msg_data,
423 p_encoded => fnd_api.g_false);
424
425 WHEN OTHERS THEN
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 Rollback to create_route_pub;
428 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
429 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
430 p_procedure_name => 'CREATE_ROUTE',
431 p_error_text => SQLERRM);
432 END IF;
433 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
434 p_data => x_msg_data,
435 p_encoded => fnd_api.g_false);
436
437 END Create_Route;
438
439
440
441
442 PROCEDURE Modify_Route
443 (
444 -- standard IN params
445 p_api_version IN NUMBER,
446 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
447 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
448 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
449 p_default IN VARCHAR2 :=FND_API.G_FALSE,
450 p_module_type IN VARCHAR2 :=NULL,
451 -- standard OUT params
452 x_return_status OUT NOCOPY VARCHAR2,
453 x_msg_count OUT NOCOPY NUMBER,
454 x_msg_data OUT NOCOPY VARCHAR2,
455 -- procedure params
456 p_route_rec IN AHL_RM_ROUTE_PVT.route_rec_type,
457 p_x_route_doc_tbl IN OUT NOCOPY AHL_RM_ASSO_DOCASO_PVT.doc_association_tbl,
458 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type,
459 p_x_route_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_resource_tbl_type,
460 p_x_route_material_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type,
461 p_x_route_panel_tbl IN OUT NOCOPY AHL_RM_RT_OPER_PANEL_PVT.rt_oper_panel_tbl_type
462 )
463 IS
464 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_ROUTE';
465 l_api_version CONSTANT NUMBER := 1.0;
466 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
467 l_route_id NUMBER;
468 l_route_rec AHL_RM_ROUTE_PVT.route_rec_type;
469 l_x_rt_oper_cost_rec AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_cost_rec_type;
470
471 BEGIN
472
473 -- Initialize API return status to success
474 x_return_status := FND_API.G_RET_STS_SUCCESS;
475
476 -- Standard Start of API savepoint
477 SAVEPOINT modify_route_pub;
478
479 -- Standard call to check for call compatibility.
480 IF NOT FND_API.compatible_api_call
481 (
482 l_api_version,
483 p_api_version,
484 l_api_name,
485 G_PKG_NAME
486 )
487 THEN
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489 END IF;
490
491 -- Initialize message list if p_init_msg_list is set to TRUE.
492 IF FND_API.to_boolean( p_init_msg_list ) THEN
493 FND_MSG_PUB.initialize;
494 END IF;
495
496
497 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
498 fnd_log.string
499 (
500 fnd_log.level_procedure,
501 l_debug_module ||'.begin',
502 'At the start of PLSQL procedure'
503 );
504 END IF;
505
506 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
507
508 fnd_log.string
509 (
510 fnd_log.level_statement,
511 l_debug_module ,
512 'Before calling the private API AHL_RM_ROUTE_PVT.process_route.....'
513 );
514
515 END IF;
516
517
518 -- If Id is null derive Route id from Route Number and revision
519
520 l_route_id := p_route_rec.route_id;
521 IF l_route_id IS NULL THEN
522 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
523 (
524 p_route_number => p_route_rec.route_no,
525 p_route_revision => p_route_rec.revision_number,
526 x_route_id => l_route_id ,
527 x_return_status => x_return_status
528 );
529 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
530 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
531 fnd_log.string
532 (
533 fnd_log.level_error,
534 l_debug_module ,
535 'Error in AHL_RM_ROUTE_UTIL.Route_Number_To_Id API'
536 );
537 END IF;
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540
541 END IF;
542
543 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
544
545 fnd_log.string
546 (
547 fnd_log.level_statement,
548 l_debug_module ,
549 'Route Id of the Route that is being updated .....' || l_route_id
550 );
551
552 END IF;
553
554 -- if DML is 'U' then the operation has to be updated
555 -- no check on OBJ VER NO as PVT already does it.
556 IF p_route_rec.DML_OPERATION = 'U'
557 THEN
558 l_route_rec := p_route_rec ;
559 AHL_RM_ROUTE_PVT.process_route
560 (
561 p_api_version => 1.0,
562 p_init_msg_list => FND_API.G_FALSE,
563 p_commit => FND_API.G_FALSE,
564 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
565 p_default => p_default,
566 p_module_type => p_module_type,
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data,
570 p_x_route_rec => l_route_rec
571 );
572 -- to raise errors from AHL_RM_ROUTE_PVT.process_route
573 x_msg_count := FND_MSG_PUB.count_msg;
574
575 IF x_msg_count > 0 THEN
576 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579
580 ElSIF p_route_rec.DML_OPERATION IS NOT NULL THEN
581 -- If the DML operation is not 'U' and is also not NULL, then throw an error
582 FND_MESSAGE.SET_NAME('AHL','AHL_COM_INVALID_DML_FLAG');
583 FND_MSG_PUB.ADD;
584 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
585 fnd_log.string
586 (
587 fnd_log.level_error,
588 l_debug_module ,
589 'Invalid DML Operation is specified....DML Operation should be Update'
590 );
591 END IF;
592 RAISE FND_API.G_EXC_ERROR;
593 END IF;
594
595
596
597
598 -- populate the record attributes that are necessary for the API
599
600 -- Attach documents
601 IF p_x_route_doc_tbl.count > 0 THEN
602
603 FOR i in p_x_route_doc_tbl.FIRST .. p_x_route_doc_tbl.LAST
604 LOOP
605
606 p_x_route_doc_tbl(i).OBJECT_TYPE_CODE := 'ROUTE';
607 p_x_route_doc_tbl(i).OBJECT_ID := l_route_id;
608
609 END LOOP;
610
611 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
612
613 fnd_log.string
614 (
615 fnd_log.level_statement,
616 l_debug_module ,
617 'Before calling the private API AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION.....'
618 );
619
620 END IF;
621
622
623 AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION
624 (
625
626 p_api_version => 1.0,
627 p_init_msg_list => FND_API.G_FALSE,
628 p_commit => FND_API.G_FALSE,
629 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
630 p_default => p_default,
631 p_module_type => p_module_type,
632 x_return_status => x_return_status,
633 x_msg_count => x_msg_count,
634 x_msg_data => x_msg_data,
635 p_x_association_tbl => p_x_route_doc_tbl
636 );
637
638 -- to raise errors from AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION
639 x_msg_count := FND_MSG_PUB.count_msg;
640
641
642 IF x_msg_count > 0 THEN
643 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647 END IF;
648
649 -- Attach Resources
650 IF p_x_route_resource_tbl.count > 0 THEN
651
652 AHL_RM_RT_OPER_RESOURCE_PVT.process_rt_oper_resource
653 (
654 p_api_version => 1.0,
655 p_init_msg_list => FND_API.G_FALSE,
656 p_commit => FND_API.G_FALSE,
657 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
658 p_default => p_default,
659 p_module_type => p_module_type,
660 x_return_status => x_return_status,
661 x_msg_count => x_msg_count,
662 x_msg_data => x_msg_data,
663 p_x_rt_oper_resource_tbl => p_x_route_resource_tbl,
664 p_association_type_code => 'ROUTE',
665 p_object_id => l_route_id
666 );
667
668 -- to raise errors from AHL_RM_RT_OPER_RESOURCE_PVT.process_rt_oper_resource
669 x_msg_count := FND_MSG_PUB.count_msg;
670
671 IF x_msg_count > 0 THEN
672 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673 RAISE FND_API.G_EXC_ERROR;
674 END IF;
675
676 -- populate the cost rec from the resource rec and call the cost api to add costing info
677 -- per resource rec
678 FOR i IN 1..p_x_route_resource_tbl.count LOOP
679
680 l_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID := p_x_route_resource_tbl(i).RT_OPER_RESOURCE_ID ;
681 l_x_rt_oper_cost_rec.OBJECT_VERSION_NUMBER := p_x_route_resource_tbl(i).OBJECT_VERSION_NUMBER;
682 l_x_rt_oper_cost_rec.ACTIVITY_ID := p_x_route_resource_tbl(i).ACTIVITY_ID;
683 l_x_rt_oper_cost_rec.ACTIVITY := p_x_route_resource_tbl(i).ACTIVITY;
684 l_x_rt_oper_cost_rec.COST_BASIS_ID := p_x_route_resource_tbl(i).COST_BASIS_ID;
685 l_x_rt_oper_cost_rec.COST_BASIS := p_x_route_resource_tbl(i).COST_BASIS;
686 l_x_rt_oper_cost_rec.SCHEDULED_TYPE_ID := p_x_route_resource_tbl(i).SCHEDULED_TYPE_ID;
687 l_x_rt_oper_cost_rec.SCHEDULED_TYPE := p_x_route_resource_tbl(i).SCHEDULED_TYPE;
688 l_x_rt_oper_cost_rec.AUTOCHARGE_TYPE_ID := p_x_route_resource_tbl(i).AUTOCHARGE_TYPE_ID;
689 l_x_rt_oper_cost_rec.AUTOCHARGE_TYPE := p_x_route_resource_tbl(i).AUTOCHARGE_TYPE;
690 l_x_rt_oper_cost_rec.STANDARD_RATE_FLAG := p_x_route_resource_tbl(i).STANDARD_RATE_FLAG;
691 l_x_rt_oper_cost_rec.STANDARD_RATE := p_x_route_resource_tbl(i).STANDARD_RATE;
692
693 AHL_RM_RT_OPER_RESOURCE_PVT.define_cost_parameter
694 (
695 p_api_version => 1.0,
696 p_init_msg_list => FND_API.G_FALSE,
697 p_commit => FND_API.G_FALSE,
698 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
699 p_default => FND_API.G_FALSE,
700 p_module_type => NULL,
701 x_return_status => x_return_status,
702 x_msg_count => x_msg_count,
703 x_msg_data => x_msg_data,
704 p_x_rt_oper_cost_rec => l_x_rt_oper_cost_rec
705 );
706
707 x_msg_count := FND_MSG_PUB.count_msg;
708
709 IF x_msg_count > 0 THEN
710 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713
714 END LOOP;
715 END IF;
716
717
718 --Attach Material Requirements
719 IF p_x_route_material_tbl.count > 0 THEN
720
721
722 AHL_RM_MATERIAL_AS_PVT.process_material_req
723 (
724 p_api_version => 1.0,
725 p_init_msg_list => FND_API.G_FALSE,
726 p_commit => FND_API.G_FALSE,
727 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
728 p_default => p_default,
729 p_module_type => p_module_type,
730 x_return_status => x_return_status,
731 x_msg_count => x_msg_count,
732 x_msg_data => x_msg_data,
733 p_x_material_req_tbl => p_x_route_material_tbl,
734 p_object_id => l_route_id,
735 p_association_type => 'ROUTE'
736 );
737 -- to raise errors from AHL_RM_MATERIAL_AS_PVT.process_material_req
738 x_msg_count := FND_MSG_PUB.count_msg;
739
740 IF x_msg_count > 0 THEN
741 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
742 RAISE FND_API.G_EXC_ERROR;
743 END IF;
744
745 END IF;
746
747 --Adithya added for Access Panels ER# 6143687.
748 --Attach Access Panels
749 IF p_x_route_panel_tbl.count > 0 THEN
750
751
752 AHL_RM_RT_OPER_PANEL_PVT.process_rt_oper_panel
753 (
754 p_api_version => 1.0,
755 p_init_msg_list => FND_API.G_FALSE,
756 p_commit => FND_API.G_FALSE,
757 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
758 p_default => p_default,
759 p_module_type => p_module_type,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data,
763 p_x_rt_oper_panel_tbl => p_x_route_panel_tbl,
764 p_association_type_code => 'ROUTE',
765 p_object_id => l_route_id
766 );
767 -- to raise errors from AHL_RM_RT_OPER_PANEL_PVT.process_rt_oper_panel
768 x_msg_count := FND_MSG_PUB.count_msg;
769
770 IF x_msg_count > 0 THEN
771 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774
775 END IF;
776
777 --Attach Operations
778 IF p_x_route_operation_tbl.count > 0 THEN
779
780 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
781 (
782 p_api_version => 1.0,
783 p_init_msg_list => FND_API.G_FALSE,
784 p_commit => FND_API.G_FALSE,
785 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
786 p_default => p_default,
787 p_module_type => p_module_type,
788 x_return_status => x_return_status,
789 x_msg_count => x_msg_count,
790 x_msg_data => x_msg_data,
791 p_x_route_operation_tbl => p_x_route_operation_tbl,
792 p_route_id => l_route_id
793 );
794
795 -- to raise errors from AHL_RM_MATERIAL_AS_PVT.process_material_req
796 x_msg_count := FND_MSG_PUB.count_msg;
797
798 IF x_msg_count > 0 THEN
799 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802
803 END IF;
804
805 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
806 fnd_log.string
807 (
808 fnd_log.level_procedure,
809 l_debug_module ||'.End',
810 'At the end of PLSQL procedure...'
811 );
812 END IF;
813
814 -- Check Error Message stack.
815 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
816 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
817 fnd_log.string
818 (
819 fnd_log.level_error,
820 l_debug_module ,
821 'Private API raised expected error....'
822 );
823 END IF;
824 RAISE FND_API.G_EXC_ERROR;
825 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
826 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
827 fnd_log.string
828 (
829 fnd_log.level_error,
830 l_debug_module ,
831 'Private API raised unexpected error....'
832 );
833 END IF;
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836
837 -- Standard check of p_commit
838 IF FND_API.To_Boolean(p_commit) THEN
839 COMMIT WORK;
840 END IF;
841
842 --
843 EXCEPTION
844 WHEN FND_API.G_EXC_ERROR THEN
845 x_return_status := FND_API.G_RET_STS_ERROR;
846 Rollback to modify_route_pub;
847 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
848 p_data => x_msg_data,
849 p_encoded => fnd_api.g_false);
850
851
852 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 Rollback to modify_route_pub;
855 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
856 p_data => x_msg_data,
857 p_encoded => fnd_api.g_false);
858
859 WHEN OTHERS THEN
860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861 Rollback to modify_route_pub;
862 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
863 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
864 p_procedure_name => 'MODIFY_ROUTE',
865 p_error_text => SQLERRM);
866 END IF;
867 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
868 p_data => x_msg_data,
869 p_encoded => fnd_api.g_false);
870
871 END Modify_Route;
872
873
874
875 PROCEDURE Delete_Route
876 (
877 -- standard IN params
878 p_api_version IN NUMBER,
879 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
880 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
881 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
882 p_default IN VARCHAR2 :=FND_API.G_FALSE,
883 p_module_type IN VARCHAR2 :=NULL,
884 -- standard OUT params
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2,
888 -- procedure params
889 p_route_id IN VARCHAR2,
890 p_route_number IN VARCHAR2,
891 p_route_revision IN NUMBER,
892 p_route_object_version IN NUMBER
893 )
894 IS
895 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROUTE';
896 l_api_version CONSTANT NUMBER := 1.0;
897 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
898 l_route_id NUMBER;
899 BEGIN
900
901 -- Initialize API return status to success
902 x_return_status := FND_API.G_RET_STS_SUCCESS;
903
904 -- Standard Start of API savepoint
905 SAVEPOINT delete_operation_pub;
906
907 -- Standard call to check for call compatibility.
908 IF NOT FND_API.compatible_api_call
909 (
910 l_api_version,
911 p_api_version,
912 l_api_name,
913 G_PKG_NAME
914 )
915 THEN
916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
917 END IF;
918
919 -- Initialize message list if p_init_msg_list is set to TRUE.
920 IF FND_API.to_boolean( p_init_msg_list ) THEN
921 FND_MSG_PUB.initialize;
922 END IF;
923
924 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
925 fnd_log.string
926 (
927 fnd_log.level_procedure,
928 l_debug_module ||'.begin',
929 'At the start of PLSQL procedure'
930 );
931 END IF;
932
933 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
934
935 fnd_log.string
936 (
937 fnd_log.level_statement,
938 l_debug_module ,
939 'Before calling the private API AHL_RM_ROUTE_PVT.delete_route.....'
940 );
941
942 END IF;
943
944 l_route_id := p_route_id;
945
946 -- If Id is null derive Route id from Route Number and revision
947 IF l_route_id IS NULL THEN
948 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
949 (
950 p_route_number => p_route_number,
951 p_route_revision => p_route_revision,
952 x_route_id => l_route_id,
953 x_return_status => x_return_status
954 );
955 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
956 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
957 fnd_log.string
958 (
959 fnd_log.level_error,
960 l_debug_module ,
961 'Error in AHL_RM_ROUTE_UTIL.Route_Number_To_Id API'
962 );
963 END IF;
964 RAISE FND_API.G_EXC_ERROR;
965 END IF;
966
967 END IF;
968
969 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
970
971 fnd_log.string
972 (
973 fnd_log.level_statement,
974 l_debug_module ,
975 'Route Id of the Route that is being deleted .....' || l_route_id
976 );
977
978 END IF;
979
980
981 AHL_RM_ROUTE_PVT.delete_route
982 (
983 p_api_version => 1.0,
984 p_init_msg_list => FND_API.G_FALSE,
985 p_commit => FND_API.G_FALSE,
986 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
987 p_default => p_default,
988 p_module_type => p_module_type,
989 x_return_status => x_return_status,
990 x_msg_count => x_msg_count,
991 x_msg_data => x_msg_data,
992 p_route_id => l_route_id,
993 p_object_version_number => p_route_object_version
994 );
995 -- to raise errors from AHL_RM_ROUTE_PVT.process_route
996 x_msg_count := FND_MSG_PUB.count_msg;
997
998 IF x_msg_count > 0 THEN
999 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002
1003
1004 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1005 fnd_log.string
1006 (
1007 fnd_log.level_procedure,
1008 l_debug_module ||'.End',
1009 'At the end of PLSQL procedure...'
1010 );
1011 END IF;
1012
1013 -- Check Error Message stack.
1014 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1015 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1016 fnd_log.string
1017 (
1018 fnd_log.level_error,
1019 l_debug_module ,
1020 'Private API raised expected error....'
1021 );
1022 END IF;
1023 RAISE FND_API.G_EXC_ERROR;
1024 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1025 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1026 fnd_log.string
1027 (
1028 fnd_log.level_error,
1029 l_debug_module ,
1030 'Private API raised unexpected error....'
1031 );
1032 END IF;
1033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1034 END IF;
1035
1036 -- Standard check of p_commit
1037 IF FND_API.To_Boolean(p_commit) THEN
1038 COMMIT WORK;
1039 END IF;
1040
1041 --
1042 EXCEPTION
1043 WHEN FND_API.G_EXC_ERROR THEN
1044 x_return_status := FND_API.G_RET_STS_ERROR;
1045 Rollback to delete_route_pub;
1046 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1047 p_data => x_msg_data,
1048 p_encoded => fnd_api.g_false);
1049
1050
1051 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 Rollback to delete_route_pub;
1054 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1055 p_data => x_msg_data,
1056 p_encoded => fnd_api.g_false);
1057
1058 WHEN OTHERS THEN
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 Rollback to delete_route_pub;
1061 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1062 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1063 p_procedure_name => 'DELETE_ROUTE',
1064 p_error_text => SQLERRM);
1065 END IF;
1066 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1067 p_data => x_msg_data,
1068 p_encoded => fnd_api.g_false);
1069
1070 END Delete_Route;
1071
1072
1073
1074
1075 PROCEDURE Process_Route_Dispositions
1076 (
1077 -- standard IN params
1078 p_api_version IN NUMBER,
1079 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1080 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1081 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1082 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1083 p_module_type IN VARCHAR2 :=NULL,
1084 -- standard OUT params
1085 x_return_status OUT NOCOPY VARCHAR2,
1086 x_msg_count OUT NOCOPY NUMBER,
1087 x_msg_data OUT NOCOPY VARCHAR2,
1088 -- procedure params
1089 p_route_id IN NUMBER,
1090 p_route_number IN VARCHAR2,
1091 p_route_revision IN NUMBER,
1092 p_x_route_efct_rec IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.route_efct_rec_type,
1093 p_x_route_efct_mat_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type
1094 )
1095 IS
1096 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_DISPOSITION';
1097 l_api_version CONSTANT NUMBER := 1.0;
1098 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1099 l_x_route_efct_tbl AHL_RM_MATERIAL_AS_PVT.route_efct_tbl_type;
1100 l_route_id NUMBER;
1101 BEGIN
1102
1103 -- Initialize API return status to success
1104 x_return_status := FND_API.G_RET_STS_SUCCESS;
1105
1106 -- Standard Start of API savepoint
1107 SAVEPOINT route_disp_pub;
1108
1109 -- Standard call to check for call compatibility.
1110 IF NOT FND_API.compatible_api_call
1111 (
1112 l_api_version,
1113 p_api_version,
1114 l_api_name,
1115 G_PKG_NAME
1116 )
1117 THEN
1118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1119 END IF;
1120
1121 -- Initialize message list if p_init_msg_list is set to TRUE.
1122 IF FND_API.to_boolean( p_init_msg_list ) THEN
1123 FND_MSG_PUB.initialize;
1124 END IF;
1125
1126
1127 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1128 fnd_log.string
1129 (
1130 fnd_log.level_procedure,
1131 l_debug_module ||'.begin',
1132 'At the start of PLSQL procedure'
1133 );
1134 END IF;
1135
1136 l_route_id := p_route_id;
1137
1138 -- If Id is null derive object id from object Number and revision
1139 IF ( l_route_id IS NULL OR
1140 l_route_id = FND_API.G_MISS_NUM )
1141 THEN
1142
1143 -- Function to convert Operation number, operation revision to id
1144 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1145 (
1146 p_route_number => p_route_number,
1147 p_route_revision => p_route_revision,
1148 x_route_id => l_route_id,
1149 x_return_status => x_return_status
1150 );
1151 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1152 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1153 fnd_log.string
1154 (
1155 fnd_log.level_error,
1156 l_debug_module ,
1157 'Error in converting Object Number, Object Revision to ID'
1158 );
1159 END IF;
1160 RAISE FND_API.G_EXC_ERROR;
1161 END IF;
1162 END IF;
1163
1164
1165 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1166 fnd_log.string
1167 (
1168 fnd_log.level_statement,
1169 l_debug_module ,
1170 'Before calling the private API.....'
1171 );
1172 END IF;
1173
1174 l_x_route_efct_tbl(1) := p_x_route_efct_rec;
1175 --Call the private API to create/modify effecvtivity header.
1176
1177
1178 AHL_RM_MATERIAL_AS_PVT.process_route_efcts
1179 (
1180 p_api_version => 1.0,
1181 p_init_msg_list => FND_API.G_FALSE,
1182 p_commit => FND_API.G_FALSE,
1183 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1184 p_default => p_default,
1185 p_module_type => p_module_type,
1186 p_object_id => l_route_id ,
1187 x_return_status => x_return_status,
1188 x_msg_count => x_msg_count,
1189 x_msg_data => x_msg_data,
1190 p_x_route_efct_tbl => l_x_route_efct_tbl
1191 );
1192
1193
1194 IF l_x_route_efct_tbl(1).ROUTE_EFFECTIVITY_ID IS NULL
1195 THEN
1196 FND_MESSAGE.set_Name('AHL','AHL_RM_PUB_EFFEC_ID_NULL');
1197 FND_MSG_PUB.add;
1198 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1199 fnd_log.string
1200 (
1201 fnd_log.level_error,
1202 l_debug_module ,
1203 'Error Effectivity id is null'
1204 );
1205 END IF;
1206 RAISE FND_API.G_EXC_ERROR;
1207
1208 END IF;
1209
1210 --Call the private API to create/modify effectivity details for the header above.
1211 AHL_RM_MATERIAL_AS_PVT.process_material_req
1212 (
1213 p_api_version => 1.0,
1214 p_init_msg_list => FND_API.G_FALSE,
1215 p_commit => FND_API.G_FALSE,
1216 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1217 p_default => p_default,
1218 p_module_type => p_module_type,
1219 x_return_status => x_return_status,
1220 x_msg_count => x_msg_count,
1221 x_msg_data => x_msg_data,
1222 p_x_material_req_tbl => p_x_route_efct_mat_tbl,
1223 p_object_id => l_x_route_efct_tbl(1).ROUTE_EFFECTIVITY_ID, -- this is the object id for association type DISPOSITION
1224 p_association_type => 'DISPOSITION'
1225 );
1226
1227 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1228 fnd_log.string
1229 (
1230 fnd_log.level_procedure,
1231 l_debug_module ||'.End',
1232 'At the end of PLSQL procedure...'
1233 );
1234 END IF;
1235
1236 -- Check Error Message stack.
1237 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1238 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1239 fnd_log.string
1240 (
1241 fnd_log.level_error,
1242 l_debug_module ,
1243 'Private API raised expected error....'
1244 );
1245 END IF;
1246 RAISE FND_API.G_EXC_ERROR;
1247 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1248 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1249 fnd_log.string
1250 (
1251 fnd_log.level_error,
1252 l_debug_module ,
1253 'Private API raised unexpected error....'
1254 );
1255 END IF;
1256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1257 END IF;
1258
1259 -- Standard check of p_commit
1260 IF FND_API.To_Boolean(p_commit) THEN
1261 COMMIT WORK;
1262 END IF;
1263
1264 --
1265 EXCEPTION
1266 WHEN FND_API.G_EXC_ERROR THEN
1267 x_return_status := FND_API.G_RET_STS_ERROR;
1268 Rollback to route_disp_pub;
1269 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1270 p_data => x_msg_data,
1271 p_encoded => fnd_api.g_false);
1272
1273
1274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276 Rollback to route_disp_pub;
1277 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1278 p_data => x_msg_data,
1279 p_encoded => fnd_api.g_false);
1280
1281 WHEN OTHERS THEN
1282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1283 Rollback to route_disp_pub;
1284 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1285 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1286 p_procedure_name => 'PROCESS_ROUTE_DISPOSITION',
1287 p_error_text => SQLERRM);
1288 END IF;
1289 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1290 p_data => x_msg_data,
1291 p_encoded => fnd_api.g_false);
1292
1293 END Process_Route_Dispositions;
1294
1295
1296
1297
1298
1299 PROCEDURE Create_Route_Revision
1300 (
1301 p_api_version IN NUMBER ,
1302 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1303 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1304 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1305 p_default IN VARCHAR2 := FND_API.G_FALSE,
1306 p_module_type IN VARCHAR2 := NULL,
1307 x_return_status OUT NOCOPY VARCHAR2,
1308 x_msg_count OUT NOCOPY NUMBER,
1309 x_msg_data OUT NOCOPY VARCHAR2,
1310 p_route_id IN NUMBER,
1311 p_route_number IN VARCHAR2,
1312 p_route_revision IN NUMBER,
1313 p_route_object_version IN NUMBER,
1314 x_new_route_id OUT NOCOPY NUMBER
1315 )
1316 IS
1317
1318 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ROUTE_REVISION';
1319 l_api_version CONSTANT NUMBER := 1.0;
1320 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1321 l_route_id NUMBER;
1322
1323 BEGIN
1324
1325 -- Initialize API return status to success
1326 x_return_status := FND_API.G_RET_STS_SUCCESS;
1327
1328 -- Standard Start of API savepoint
1329 SAVEPOINT create_route_revision_pub;
1330
1331 -- Standard call to check for call compatibility.
1332 IF NOT FND_API.compatible_api_call
1333 (
1334 l_api_version,
1335 p_api_version,
1336 l_api_name,
1337 G_PKG_NAME
1338 )
1339 THEN
1340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1341 END IF;
1342
1343
1344 -- Initialize message list if p_init_msg_list is set to TRUE.
1345 IF FND_API.to_boolean( p_init_msg_list ) THEN
1346 FND_MSG_PUB.initialize;
1347 END IF;
1348
1349 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1350 fnd_log.string
1351 (
1352 fnd_log.level_procedure,
1353 l_debug_module ||'.begin',
1354 'At the start of PLSQL procedure'
1355 );
1356 END IF;
1357
1358 -- If Id is null derive Route id from Route Number and revision
1359 l_route_id := p_route_id;
1360 IF ( p_route_id IS NULL OR
1361 p_route_id = FND_API.G_MISS_NUM )
1362 THEN
1363 -- Function to convert Operation number, operation revision to id
1364 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1365 (
1366 p_route_number => p_route_number,
1367 p_route_revision => p_route_revision,
1368 x_route_id => l_route_id,
1369 x_return_status => x_return_status
1370 );
1371 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1372 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1373 fnd_log.string
1374 (
1375 fnd_log.level_error,
1376 l_debug_module ,
1377 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1378 );
1379 END IF;
1380 RAISE FND_API.G_EXC_ERROR;
1381 END IF;
1382 END IF;
1383
1384 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1385 fnd_log.string
1386 (
1387 fnd_log.level_statement,
1388 l_debug_module ,
1389 'Before calling the private API .....'
1390 );
1391 END IF;
1392
1393 -- Call the corresponding private API
1394 AHL_RM_ROUTE_PVT.create_route_revision
1395 (
1396 p_api_version => 1.0,
1397 p_init_msg_list => FND_API.G_FALSE,
1398 p_commit => FND_API.G_FALSE,
1399 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1400 p_default => p_default,
1401 p_module_type => p_module_type,
1402 x_return_status => x_return_status,
1403 x_msg_count => x_msg_count,
1404 x_msg_data => x_msg_data,
1405 p_route_id => l_route_id,
1406 p_object_version_number => p_route_object_version,
1407 x_route_id => x_new_route_id
1408 );
1409
1410 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1411 fnd_log.string
1412 (
1413 fnd_log.level_procedure,
1414 l_debug_module ||'.End',
1415 'At the end of PLSQL procedure...'
1416 );
1417 END IF;
1418
1419 -- Check Error Message stack.
1420 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1421 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1422 fnd_log.string
1423 (
1424 fnd_log.level_error,
1425 l_debug_module ,
1426 'Private API raised expected error....'
1427 );
1428 END IF;
1429 RAISE FND_API.G_EXC_ERROR;
1430 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1431 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1432 fnd_log.string
1433 (
1434 fnd_log.level_error,
1435 l_debug_module ,
1436 'Private API raised unexpected error....'
1437 );
1438 END IF;
1439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1440 END IF;
1441
1442 -- Standard check of p_commit
1443 IF FND_API.To_Boolean(p_commit) THEN
1444 COMMIT WORK;
1445 END IF;
1446
1447
1448 --
1449 EXCEPTION
1450 WHEN FND_API.G_EXC_ERROR THEN
1451 x_return_status := FND_API.G_RET_STS_ERROR;
1452 Rollback to create_route_revision_pub;
1453 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1454 p_data => x_msg_data,
1455 p_encoded => fnd_api.g_false);
1456
1457
1458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460 Rollback to create_route_revision_pub;
1461 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1462 p_data => x_msg_data,
1463 p_encoded => fnd_api.g_false);
1464
1465 WHEN OTHERS THEN
1466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1467 Rollback to create_route_revision_pub;
1468 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1469 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1470 p_procedure_name => 'create_route_revision',
1471 p_error_text => SQLERRM);
1472 END IF;
1473 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1474 p_data => x_msg_data,
1475 p_encoded => fnd_api.g_false);
1476
1477
1478 END Create_Route_Revision;
1479
1480
1481
1482
1483
1484 PROCEDURE Initiate_Route_Approval
1485 (
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_default IN VARCHAR2 := FND_API.G_FALSE,
1491 p_module_type IN VARCHAR2 := NULL,
1492 x_return_status OUT NOCOPY VARCHAR2,
1493 x_msg_count OUT NOCOPY NUMBER,
1494 x_msg_data OUT NOCOPY VARCHAR2,
1495 p_route_id IN NUMBER,
1496 p_route_number IN VARCHAR2,
1497 p_route_revision IN NUMBER,
1498 p_route_object_version IN NUMBER,
1499 p_apprv_type IN VARCHAR2
1500 )
1501 IS
1502 l_api_version CONSTANT NUMBER := 1.0;
1503 l_api_name CONSTANT VARCHAR2(30) := 'INITIATE_ROUTE_APPROVAL';
1504 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1505 l_source_route_id NUMBER;
1506
1507 BEGIN
1508
1509 -- Initialize API return status to success
1510 x_return_status := FND_API.G_RET_STS_SUCCESS;
1511
1512 -- Standard Start of API savepoint
1513 SAVEPOINT initiate_route_approval_pub;
1514
1515 -- Standard call to check for call compatibility.
1516 IF NOT FND_API.compatible_api_call
1517 (
1518 l_api_version,
1519 p_api_version,
1520 l_api_name,
1521 G_PKG_NAME
1522 )
1523 THEN
1524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1525 END IF;
1526
1527 -- Initialize message list if p_init_msg_list is set to TRUE.
1528 IF FND_API.to_boolean( p_init_msg_list ) THEN
1529 FND_MSG_PUB.initialize;
1530 END IF;
1531
1532 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1533 fnd_log.string
1534 (
1535 fnd_log.level_procedure,
1536 l_debug_module ||'.begin',
1537 'At the start of PLSQL procedure'
1538 );
1539 END IF;
1540 -- If Id is null derive Route id from Route Number and revision
1541 l_source_route_id := p_route_id ;
1542 IF ( p_route_id IS NULL OR
1543 p_route_id = FND_API.G_MISS_NUM )
1544 THEN
1545 -- Function to convert Operation number, operation revision to id
1546 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1547 (
1548 p_route_number => p_route_number,
1549 p_route_revision => p_route_revision,
1550 x_route_id => l_source_route_id,
1551 x_return_status => x_return_status
1552 );
1553 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1554 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1555 fnd_log.string
1556 (
1557 fnd_log.level_error,
1558 l_debug_module ,
1559 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1560 );
1561 END IF;
1562 RAISE FND_API.G_EXC_ERROR;
1563 END IF;
1564 END IF;
1565
1566 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1567 fnd_log.string
1568 (
1569 fnd_log.level_statement,
1570 l_debug_module ,
1571 'Before calling the private API.....'
1572 );
1573 END IF;
1574
1575
1576 -- Call the private API
1577 AHL_RM_APPROVAL_PVT.INITIATE_ROUTE_APPROVAL
1578 (
1579 p_api_version => 1.0,
1580 p_init_msg_list => FND_API.G_FALSE,
1581 p_commit => FND_API.G_FALSE,
1582 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1583 p_default => p_default,
1584 p_module_type => p_module_type,
1585 x_return_status => x_return_status,
1586 x_msg_count => x_msg_count,
1587 x_msg_data => x_msg_data,
1588 p_source_route_id => l_source_route_id,
1589 p_object_Version_number => p_route_object_version,
1590 p_apprvl_type => p_apprv_type
1591 );
1592
1593
1594 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1595 fnd_log.string
1596 (
1597 fnd_log.level_procedure,
1598 l_debug_module ||'.end',
1599 'At the end of PLSQL procedure...'
1600 );
1601 END IF;
1602
1603 -- Check Error Message stack.
1604 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1605 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1606 fnd_log.string
1607 (
1608 fnd_log.level_error,
1609 l_debug_module ,
1610 'Private API raised expected error....'
1611 );
1612 END IF;
1613 RAISE FND_API.G_EXC_ERROR;
1614 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1615 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1616 fnd_log.string
1617 (
1618 fnd_log.level_error,
1619 l_debug_module ,
1620 'Private API raised unexpected error....'
1621 );
1622 END IF;
1623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1624 END IF;
1625
1626 -- Standard check of p_commit
1627 IF FND_API.To_Boolean(p_commit) THEN
1628 COMMIT WORK;
1629 END IF;
1630
1631 --
1632 EXCEPTION
1633 WHEN FND_API.G_EXC_ERROR THEN
1634 x_return_status := FND_API.G_RET_STS_ERROR;
1635 Rollback to initiate_route_approval_pub;
1636 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1637 p_data => x_msg_data,
1638 p_encoded => fnd_api.g_false);
1639
1640
1641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1643 Rollback to initiate_route_approval_pub;
1644 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1645 p_data => x_msg_data,
1646 p_encoded => fnd_api.g_false);
1647
1648 WHEN OTHERS THEN
1649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1650 Rollback to initiate_route_approval_pub;
1651 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1652 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1653 p_procedure_name => 'INITIATE_ROUTE_APPROVAL',
1654 p_error_text => SQLERRM);
1655 END IF;
1656 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1657 p_data => x_msg_data,
1658 p_encoded => fnd_api.g_false);
1659
1660 END Initiate_Route_Approval;
1661
1662 PROCEDURE Process_Route_Oper_Assoc
1663 (
1664 p_api_version IN NUMBER := 1.0,
1665 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1666 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1667 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1668 p_default IN VARCHAR2 := FND_API.G_FALSE,
1669 p_module_type IN VARCHAR2 := NULL,
1670 x_return_status OUT NOCOPY VARCHAR2,
1671 x_msg_count OUT NOCOPY NUMBER,
1672 x_msg_data OUT NOCOPY VARCHAR2,
1673 p_route_id IN NUMBER,
1674 p_route_number IN VARCHAR2,
1675 p_route_revision IN NUMBER,
1676 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type
1677 )
1678 IS
1679 l_api_version CONSTANT NUMBER := 1.0;
1680 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_OPER_ASSOC';
1681 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1682 l_route_id NUMBER;
1683
1684 BEGIN
1685
1686 -- Initialize API return status to success
1687 x_return_status := FND_API.G_RET_STS_SUCCESS;
1688
1689 -- Standard Start of API savepoint
1690 SAVEPOINT process_route_operation_as_pub;
1691
1692 -- Standard call to check for call compatibility.
1693 IF NOT FND_API.compatible_api_call
1694 (
1695 l_api_version,
1696 p_api_version,
1697 l_api_name,
1698 G_PKG_NAME
1699 )
1700 THEN
1701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1702 END IF;
1703
1704 -- Initialize message list if p_init_msg_list is set to TRUE.
1705 IF FND_API.to_boolean( p_init_msg_list ) THEN
1706 FND_MSG_PUB.initialize;
1707 END IF;
1708
1709 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1710 fnd_log.string
1711 (
1712 fnd_log.level_procedure,
1713 l_debug_module ||'.begin',
1714 'At the start of PLSQL procedure'
1715 );
1716 END IF;
1717
1718 -- If Id is null derive Route id from Route Number and revision
1719 l_route_id := p_route_id;
1720 IF ( l_route_id IS NULL OR
1721 l_route_id = FND_API.G_MISS_NUM )
1722 THEN
1723 -- Function to convert Route number, Route revision to id
1724 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1725 (
1726 p_route_number => p_route_number,
1727 p_route_revision => p_route_revision,
1728 x_route_id => l_route_id,
1729 x_return_status => x_return_status
1730 );
1731 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1732 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1733 fnd_log.string
1734 (
1735 fnd_log.level_error,
1736 l_debug_module ,
1737 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1738 );
1739 END IF;
1740 RAISE FND_API.G_EXC_ERROR;
1741 END IF;
1742 END IF;
1743
1744 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1745 fnd_log.string
1746 (
1747 fnd_log.level_statement,
1748 l_debug_module ,
1749 'Before calling the private API.....'
1750 );
1751 END IF;
1752
1753 -- Call the private API
1754 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
1755 (
1756 p_api_version => 1.0,
1757 p_init_msg_list => FND_API.G_FALSE,
1758 p_commit => FND_API.G_FALSE,
1759 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1760 p_default => p_default,
1761 p_module_type => p_module_type,
1762 x_return_status => x_return_status,
1763 x_msg_count => x_msg_count,
1764 x_msg_data => x_msg_data,
1765 p_x_route_operation_tbl => p_x_route_operation_tbl,
1766 p_route_id => l_route_id
1767 );
1768
1769 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1770 fnd_log.string
1771 (
1772 fnd_log.level_procedure,
1773 l_debug_module ||'.End',
1774 'At the end of PLSQL procedure...'
1775 );
1776 END IF;
1777
1778 -- Check Error Message stack.
1779 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1780 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1781 fnd_log.string
1782 (
1783 fnd_log.level_error,
1784 l_debug_module ,
1785 'Private API raised expected error....'
1786 );
1787 END IF;
1788 RAISE FND_API.G_EXC_ERROR;
1789 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1790 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1791 fnd_log.string
1792 (
1793 fnd_log.level_error,
1794 l_debug_module ,
1795 'Private API raised unexpected error....'
1796 );
1797 END IF;
1798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1799 END IF;
1800
1801 -- Standard check of p_commit
1802 IF FND_API.To_Boolean(p_commit) THEN
1803 COMMIT WORK;
1804 END IF;
1805
1806
1807
1808 --
1809 EXCEPTION
1810 WHEN FND_API.G_EXC_ERROR THEN
1811 x_return_status := FND_API.G_RET_STS_ERROR;
1812 Rollback to process_route_operation_as_pub;
1813 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1814 p_data => x_msg_data,
1815 p_encoded => fnd_api.g_false);
1816
1817
1818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1820 Rollback to process_route_operation_as_pub;
1821 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1822 p_data => x_msg_data,
1823 p_encoded => fnd_api.g_false);
1824
1825 WHEN OTHERS THEN
1826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1827 Rollback to process_route_operation_as_pub;
1828 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1829 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1830 p_procedure_name => 'Process_Route_Oper_Assoc',
1831 p_error_text => SQLERRM);
1832 END IF;
1833 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1834 p_data => x_msg_data,
1835 p_encoded => fnd_api.g_false);
1836
1837 END Process_Route_Oper_Assoc;
1838
1839
1840 PROCEDURE Process_Route_Alt_Resources
1841 (
1842 -- standard IN params
1843 p_api_version IN NUMBER,
1844 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1845 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1846 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1847 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1848 p_module_type IN VARCHAR2 :=NULL,
1849 -- standard OUT params
1850 x_return_status OUT NOCOPY VARCHAR2,
1851 x_msg_count OUT NOCOPY NUMBER,
1852 x_msg_data OUT NOCOPY VARCHAR2,
1853 -- procedure params
1854 p_route_number IN VARCHAR2,
1855 p_route_revision IN NUMBER,
1856 p_route_id IN NUMBER,
1857 p_resource_id IN NUMBER,
1858 p_resource_name IN VARCHAR2,
1859 p_x_alt_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.alt_resource_tbl_type
1860 )
1861 IS
1862
1863 -- cursor to fetch the resource Id if the resource name is known
1864 CURSOR get_res_id( c_resource_name in number)
1865 IS
1866 SELECT RESOURCE_ID
1867 from AHL_RESOURCES
1868 where name = c_resource_name;
1869
1870
1871 -- cursor to get the resource association id given the operation and the resource details
1872 CURSOR get_rt_oper_resource (c_route_id IN NUMBER, c_resource_id IN NUMBER)
1873 IS
1874 SELECT rt_oper_resource_id
1875 from AHL_RT_OPER_RESOURCES
1876 where
1877 object_ID = c_route_id
1878 and aso_resource_id = c_resource_id;
1879
1880
1881
1882 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_ALT_RESOURCES';
1883 l_api_version CONSTANT NUMBER := 1.0;
1884 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1885
1886 l_rt_oper_resource_id NUMBER;
1887 l_resource_id NUMBER;
1888 l_route_id NUMBER;
1889
1890
1891 BEGIN
1892
1893
1894
1895 -- Initialize API return status to success
1896 x_return_status := FND_API.G_RET_STS_SUCCESS;
1897
1898 -- Standard Start of API savepoint
1899 SAVEPOINT Process_Alt_Resources;
1900
1901 -- Standard call to check for call compatibility.
1902 IF NOT FND_API.compatible_api_call
1903 (
1904 l_api_version,
1905 p_api_version,
1906 l_api_name,
1907 G_PKG_NAME
1908 )
1909 THEN
1910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1911 END IF;
1912
1913 -- Initialize message list if p_init_msg_list is set to TRUE.
1914 IF FND_API.to_boolean( p_init_msg_list ) THEN
1915 FND_MSG_PUB.initialize;
1916 END IF;
1917
1918 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1919 fnd_log.string
1920 (
1921 fnd_log.level_procedure,
1922 l_debug_module ||'.begin',
1923 'At the start of PLSQL procedure'
1924 );
1925 END IF;
1926
1927 l_route_id := p_route_id;
1928 -- If Id is null derive Route id from Route Number and revision
1929 IF l_route_id IS NULL THEN
1930
1931 -- Function to convert Route number, Route revision to id
1932 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1933 (
1934 p_route_number => p_route_number,
1935 p_route_revision => p_route_revision,
1936 x_route_id => l_route_id,
1937 x_return_status => x_return_status
1938 );
1939
1940 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1941 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1942 fnd_log.string
1943 (
1944 fnd_log.level_error,
1945 l_debug_module ||':',
1946 'Error in AHL_RM_ROUTE_UTIL.Route_Number_To_Id API'
1947 );
1948 END IF;
1949 RAISE FND_API.G_EXC_ERROR;
1950 END IF;
1951 END IF;
1952
1953 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1954
1955 fnd_log.string
1956 (
1957 fnd_log.level_statement,
1958 l_debug_module ,
1959 'Route Id of the Operation that is being updated .....' || l_route_id
1960 );
1961 END IF;
1962
1963 -- if Id is null derive Resource Id from Resource name
1964 -- if resource Id cannot be found then throw error
1965 l_resource_id := p_resource_id ;
1966 IF l_resource_id IS NULL THEN
1967
1968 OPEN get_res_id(p_resource_name );
1969 FETCH get_res_id INTO l_resource_id;
1970 IF get_res_id%NOTFOUND THEN
1971 -- PRITHWI : please run this message and check what it displays.
1972 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_ASO_RES_ID' );
1973 FND_MESSAGE.set_token( 'RECORD', p_resource_name );
1974 FND_MSG_PUB.add;
1975 RAISE FND_API.G_EXC_ERROR;
1976 END IF;
1977
1978 CLOSE get_res_id;
1979
1980 END IF;
1981
1982 -- check whether the association ID between Operation and resource exists.
1983 OPEN get_rt_oper_resource (l_route_id , l_resource_id);
1984 FETCH get_rt_oper_resource INTO l_rt_oper_resource_id;
1985 IF get_rt_oper_resource%NOTFOUND THEN
1986 -- PRITHWI : please run this message and check what it displays.
1987 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OBJECT' );
1988 FND_MSG_PUB.add;
1989 RAISE FND_API.G_EXC_ERROR;
1990 END IF;
1991
1992 CLOSE get_rt_oper_resource;
1993
1994 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1995
1996 fnd_log.string
1997 (
1998 fnd_log.level_statement,
1999 l_debug_module ,
2000 'Before calling Private API AHL_RM_RT_OPER_RESOURCE_PVT.process_alternate_resource'
2001 );
2002 END IF;
2003
2004 AHL_RM_RT_OPER_RESOURCE_PVT.process_alternate_resource
2005 (
2006 p_api_version => 1.0 ,
2007 p_init_msg_list => p_init_msg_list,
2008 p_commit => p_commit,
2009 p_validation_level => p_validation_level,
2010 p_default => p_default,
2011 p_module_type => p_module_type,
2012 x_return_status => x_return_status,
2013 x_msg_count => x_msg_count,
2014 x_msg_data => x_msg_data,
2015 p_rt_oper_resource_id => l_rt_oper_resource_id,
2016 p_x_alt_resource_tbl => p_x_alt_resource_tbl
2017 );
2018
2019
2020 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2021
2022 fnd_log.string
2023 (
2024 fnd_log.level_statement,
2025 l_debug_module ,
2026 'After calling Private API AHL_RM_RT_OPER_RESOURCE_PVT.process_alternate_resource'
2027 );
2028 END IF;
2029
2030
2031 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2032 fnd_log.string
2033 (
2034 fnd_log.level_procedure,
2035 l_debug_module ||'.End',
2036 'At the end of PLSQL procedure...'
2037 );
2038 END IF;
2039
2040 -- Check Error Message stack.
2041 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2042 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
2043 fnd_log.string
2044 (
2045 fnd_log.level_error,
2046 l_debug_module ,
2047 'Public API raised expected error....'
2048 );
2049 END IF;
2050 RAISE FND_API.G_EXC_ERROR;
2051 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2052 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2053 fnd_log.string
2054 (
2055 fnd_log.level_error,
2056 l_debug_module ,
2057 'Public API raised unexpected error....'
2058 );
2059 END IF;
2060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2061 END IF;
2062
2063 -- Standard check of p_commit
2064 IF FND_API.To_Boolean(p_commit) THEN
2065 COMMIT WORK;
2066 END IF;
2067
2068
2069
2070 --
2071 EXCEPTION
2072 WHEN FND_API.G_EXC_ERROR THEN
2073 x_return_status := FND_API.G_RET_STS_ERROR;
2074 Rollback to Process_Alt_Resources;
2075 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2076 p_data => x_msg_data,
2077 p_encoded => fnd_api.g_false);
2078
2079
2080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082 Rollback to Process_Alt_Resources;
2083 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2084 p_data => x_msg_data,
2085 p_encoded => fnd_api.g_false);
2086
2087 WHEN OTHERS THEN
2088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089 Rollback to Process_Alt_Resources;
2090 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2091 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2092 p_procedure_name => 'Process_Route_Alt_Resources',
2093 p_error_text => SQLERRM);
2094 END IF;
2095 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2096 p_data => x_msg_data,
2097 p_encoded => fnd_api.g_false);
2098
2099 END Process_Route_Alt_Resources;
2100
2101
2102
2103 END AHL_RM_ROUTE_PUB;