[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_ROUTE_PUB
Source
1 PACKAGE BODY AHL_RM_ROUTE_PUB AS
2 /* $Header: AHLPROUB.pls 120.5.12020000.3 2012/12/10 15:50:37 prakkum 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 := 2.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 -- Overloaded procedure retained for backaward compatibility (pre 12.0.4).
440 -- This procedure will call the above procedure with api_version = 2.
441 PROCEDURE Create_Route
442 (
443 -- standard IN params
444 p_api_version IN NUMBER,
445 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
446 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
447 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
448 p_default IN VARCHAR2 :=FND_API.G_FALSE,
449 p_module_type IN VARCHAR2 :=NULL,
450 -- standard OUT params
451 x_return_status OUT NOCOPY VARCHAR2,
452 x_msg_count OUT NOCOPY NUMBER,
453 x_msg_data OUT NOCOPY VARCHAR2,
454 -- procedure params
455 p_x_route_rec IN OUT NOCOPY AHL_RM_ROUTE_PVT.route_rec_type,
456 p_x_route_doc_tbl IN OUT NOCOPY AHL_RM_ASSO_DOCASO_PVT.doc_association_tbl,
457 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type,
458 p_x_route_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_resource_tbl_type,
459 p_x_route_material_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type
460 )
461 IS
462
463 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ROUTE';
464 l_api_version CONSTANT NUMBER := 1.0;
465
466 l_x_route_panel_tbl AHL_RM_RT_OPER_PANEL_PVT.rt_oper_panel_tbl_type;
467
468 BEGIN
469
470 -- Initialize API return status to success
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472
473 -- Standard call to check for call compatibility.
474 IF NOT FND_API.compatible_api_call
475 (
476 l_api_version,
477 p_api_version,
478 l_api_name,
479 G_PKG_NAME
480 )
481 THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484
485 AHL_RM_ROUTE_PUB.Create_Route(
486 p_api_version => 2.0,
487 p_init_msg_list => p_init_msg_list,
488 p_commit => p_commit,
489 p_validation_level => p_validation_level,
490 p_default => p_default,
491 p_module_type => p_module_type,
492 x_return_status => x_return_status,
493 x_msg_count => x_msg_count,
494 x_msg_data => x_msg_data,
495 p_x_route_rec => p_x_route_rec,
496 p_x_route_doc_tbl => p_x_route_doc_tbl,
497 p_x_route_operation_tbl => p_x_route_operation_tbl,
498 p_x_route_resource_tbl => p_x_route_resource_tbl,
499 p_x_route_material_tbl => p_x_route_material_tbl,
500 p_x_route_panel_tbl => l_x_route_panel_tbl);
501
502 --
503 EXCEPTION
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
507 p_data => x_msg_data,
508 p_encoded => fnd_api.g_false);
509
510 WHEN OTHERS THEN
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
513 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
514 p_procedure_name => 'CREATE_ROUTE',
515 p_error_text => SQLERRM);
516 END IF;
517 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
518 p_data => x_msg_data,
519 p_encoded => fnd_api.g_false);
520
521 END Create_Route;
522
523
524 PROCEDURE Modify_Route
525 (
526 -- standard IN params
527 p_api_version IN NUMBER,
528 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
529 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
530 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
531 p_default IN VARCHAR2 :=FND_API.G_FALSE,
532 p_module_type IN VARCHAR2 :=NULL,
533 -- standard OUT params
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_msg_count OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2,
537 -- procedure params
538 p_route_rec IN AHL_RM_ROUTE_PVT.route_rec_type,
539 p_x_route_doc_tbl IN OUT NOCOPY AHL_RM_ASSO_DOCASO_PVT.doc_association_tbl,
540 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type,
541 p_x_route_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_resource_tbl_type,
542 p_x_route_material_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type,
543 p_x_route_panel_tbl IN OUT NOCOPY AHL_RM_RT_OPER_PANEL_PVT.rt_oper_panel_tbl_type
544 )
545 IS
546 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_ROUTE';
547 l_api_version CONSTANT NUMBER := 2.0;
548 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
549 l_route_id NUMBER;
550 l_route_rec AHL_RM_ROUTE_PVT.route_rec_type;
551 l_x_rt_oper_cost_rec AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_cost_rec_type;
552
553 BEGIN
554
555 -- Initialize API return status to success
556 x_return_status := FND_API.G_RET_STS_SUCCESS;
557
558 -- Standard Start of API savepoint
559 SAVEPOINT modify_route_pub;
560
561 -- Standard call to check for call compatibility.
562 IF NOT FND_API.compatible_api_call
563 (
564 l_api_version,
565 p_api_version,
566 l_api_name,
567 G_PKG_NAME
568 )
569 THEN
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572
573 -- Initialize message list if p_init_msg_list is set to TRUE.
574 IF FND_API.to_boolean( p_init_msg_list ) THEN
575 FND_MSG_PUB.initialize;
576 END IF;
577
578
579 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
580 fnd_log.string
581 (
582 fnd_log.level_procedure,
583 l_debug_module ||'.begin',
584 'At the start of PLSQL procedure'
585 );
586 END IF;
587
588 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
589
590 fnd_log.string
591 (
592 fnd_log.level_statement,
593 l_debug_module ,
594 'Before calling the private API AHL_RM_ROUTE_PVT.process_route.....'
595 );
596
597 END IF;
598
599
600 -- If Id is null derive Route id from Route Number and revision
601
602 l_route_id := p_route_rec.route_id;
603 IF l_route_id IS NULL THEN
604 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
605 (
606 p_route_number => p_route_rec.route_no,
607 p_route_revision => p_route_rec.revision_number,
608 x_route_id => l_route_id ,
609 x_return_status => x_return_status
610 );
611 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
612 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
613 fnd_log.string
614 (
615 fnd_log.level_error,
616 l_debug_module ,
617 'Error in AHL_RM_ROUTE_UTIL.Route_Number_To_Id API'
618 );
619 END IF;
620 RAISE FND_API.G_EXC_ERROR;
621 END IF;
622
623 END IF;
624
625 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
626
627 fnd_log.string
628 (
629 fnd_log.level_statement,
630 l_debug_module ,
631 'Route Id of the Route that is being updated .....' || l_route_id
632 );
633
634 END IF;
635
636 -- if DML is 'U' then the operation has to be updated
637 -- no check on OBJ VER NO as PVT already does it.
638 IF p_route_rec.DML_OPERATION = 'U'
639 THEN
640 l_route_rec := p_route_rec ;
641 AHL_RM_ROUTE_PVT.process_route
642 (
643 p_api_version => 1.0,
644 p_init_msg_list => FND_API.G_FALSE,
645 p_commit => FND_API.G_FALSE,
646 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
647 p_default => p_default,
648 p_module_type => p_module_type,
649 x_return_status => x_return_status,
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data,
652 p_x_route_rec => l_route_rec
653 );
654 -- to raise errors from AHL_RM_ROUTE_PVT.process_route
655 x_msg_count := FND_MSG_PUB.count_msg;
656
657 IF x_msg_count > 0 THEN
658 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662 ElSIF p_route_rec.DML_OPERATION IS NOT NULL THEN
663 -- If the DML operation is not 'U' and is also not NULL, then throw an error
664 FND_MESSAGE.SET_NAME('AHL','AHL_COM_INVALID_DML_FLAG');
665 FND_MSG_PUB.ADD;
666 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
667 fnd_log.string
668 (
669 fnd_log.level_error,
670 l_debug_module ,
671 'Invalid DML Operation is specified....DML Operation should be Update'
672 );
673 END IF;
674 RAISE FND_API.G_EXC_ERROR;
675 END IF;
676
677
678
679
680 -- populate the record attributes that are necessary for the API
681
682 -- Attach documents
683 IF p_x_route_doc_tbl.count > 0 THEN
684
685 FOR i in p_x_route_doc_tbl.FIRST .. p_x_route_doc_tbl.LAST
686 LOOP
687
688 p_x_route_doc_tbl(i).OBJECT_TYPE_CODE := 'ROUTE';
689 p_x_route_doc_tbl(i).OBJECT_ID := l_route_id;
690
691 END LOOP;
692
693 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
694
695 fnd_log.string
696 (
697 fnd_log.level_statement,
698 l_debug_module ,
699 'Before calling the private API AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION.....'
700 );
701
702 END IF;
703
704
705 AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION
706 (
707
708 p_api_version => 1.0,
709 p_init_msg_list => FND_API.G_FALSE,
710 p_commit => FND_API.G_FALSE,
711 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
712 p_default => p_default,
713 p_module_type => p_module_type,
714 x_return_status => x_return_status,
715 x_msg_count => x_msg_count,
716 x_msg_data => x_msg_data,
717 p_x_association_tbl => p_x_route_doc_tbl
718 );
719
720 -- to raise errors from AHL_RM_ASSO_DOCASO_PVT.PROCESS_ASSOCIATION
721 x_msg_count := FND_MSG_PUB.count_msg;
722
723
724 IF x_msg_count > 0 THEN
725 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728
729 END IF;
730
731 -- Attach Resources
732 IF p_x_route_resource_tbl.count > 0 THEN
733
734 AHL_RM_RT_OPER_RESOURCE_PVT.process_rt_oper_resource
735 (
736 p_api_version => 1.0,
737 p_init_msg_list => FND_API.G_FALSE,
738 p_commit => FND_API.G_FALSE,
739 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
740 p_default => p_default,
741 p_module_type => p_module_type,
742 x_return_status => x_return_status,
743 x_msg_count => x_msg_count,
744 x_msg_data => x_msg_data,
745 p_x_rt_oper_resource_tbl => p_x_route_resource_tbl,
746 p_association_type_code => 'ROUTE',
747 p_object_id => l_route_id
748 );
749
750 -- to raise errors from AHL_RM_RT_OPER_RESOURCE_PVT.process_rt_oper_resource
751 x_msg_count := FND_MSG_PUB.count_msg;
752
753 IF x_msg_count > 0 THEN
754 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
755 RAISE FND_API.G_EXC_ERROR;
756 END IF;
757
758 -- populate the cost rec from the resource rec and call the cost api to add costing info
759 -- per resource rec
760 FOR i IN 1..p_x_route_resource_tbl.count LOOP
761
762 --JKJain, BUg 8762640 starts
763 IF (p_x_route_resource_tbl(i).dml_operation <> 'D' ) THEN
764
765 l_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID := p_x_route_resource_tbl(i).RT_OPER_RESOURCE_ID ;
766 l_x_rt_oper_cost_rec.OBJECT_VERSION_NUMBER := p_x_route_resource_tbl(i).OBJECT_VERSION_NUMBER;
767 l_x_rt_oper_cost_rec.ACTIVITY_ID := p_x_route_resource_tbl(i).ACTIVITY_ID;
768 l_x_rt_oper_cost_rec.ACTIVITY := p_x_route_resource_tbl(i).ACTIVITY;
769 l_x_rt_oper_cost_rec.COST_BASIS_ID := p_x_route_resource_tbl(i).COST_BASIS_ID;
770 l_x_rt_oper_cost_rec.COST_BASIS := p_x_route_resource_tbl(i).COST_BASIS;
771 l_x_rt_oper_cost_rec.SCHEDULED_TYPE_ID := p_x_route_resource_tbl(i).SCHEDULED_TYPE_ID;
772 l_x_rt_oper_cost_rec.SCHEDULED_TYPE := p_x_route_resource_tbl(i).SCHEDULED_TYPE;
773 l_x_rt_oper_cost_rec.AUTOCHARGE_TYPE_ID := p_x_route_resource_tbl(i).AUTOCHARGE_TYPE_ID;
774 l_x_rt_oper_cost_rec.AUTOCHARGE_TYPE := p_x_route_resource_tbl(i).AUTOCHARGE_TYPE;
775 l_x_rt_oper_cost_rec.STANDARD_RATE_FLAG := p_x_route_resource_tbl(i).STANDARD_RATE_FLAG;
776 l_x_rt_oper_cost_rec.STANDARD_RATE := p_x_route_resource_tbl(i).STANDARD_RATE;
777
778 AHL_RM_RT_OPER_RESOURCE_PVT.define_cost_parameter
779 (
780 p_api_version => 1.0,
781 p_init_msg_list => FND_API.G_FALSE,
782 p_commit => FND_API.G_FALSE,
783 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
784 p_default => FND_API.G_FALSE,
785 p_module_type => NULL,
786 x_return_status => x_return_status,
787 x_msg_count => x_msg_count,
788 x_msg_data => x_msg_data,
789 p_x_rt_oper_cost_rec => l_x_rt_oper_cost_rec
790 );
791
792 x_msg_count := FND_MSG_PUB.count_msg;
793
794 IF x_msg_count > 0 THEN
795 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796 RAISE FND_API.G_EXC_ERROR;
797 END IF;
798 END IF;
799 --JKJain, BUg 8762640 ends
800 END LOOP;
801 END IF;
802
803
804 --Attach Material Requirements
805 IF p_x_route_material_tbl.count > 0 THEN
806
807
808 AHL_RM_MATERIAL_AS_PVT.process_material_req
809 (
810 p_api_version => 1.0,
811 p_init_msg_list => FND_API.G_FALSE,
812 p_commit => FND_API.G_FALSE,
813 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
814 p_default => p_default,
815 p_module_type => p_module_type,
816 x_return_status => x_return_status,
817 x_msg_count => x_msg_count,
818 x_msg_data => x_msg_data,
819 p_x_material_req_tbl => p_x_route_material_tbl,
820 p_object_id => l_route_id,
821 p_association_type => 'ROUTE'
822 );
823 -- to raise errors from AHL_RM_MATERIAL_AS_PVT.process_material_req
824 x_msg_count := FND_MSG_PUB.count_msg;
825
826 IF x_msg_count > 0 THEN
827 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
828 RAISE FND_API.G_EXC_ERROR;
829 END IF;
830
831 END IF;
832
833 --Adithya added for Access Panels ER# 6143687.
834 --Attach Access Panels
835 IF p_x_route_panel_tbl.count > 0 THEN
836
837
838 AHL_RM_RT_OPER_PANEL_PVT.process_rt_oper_panel
839 (
840 p_api_version => 1.0,
841 p_init_msg_list => FND_API.G_FALSE,
842 p_commit => FND_API.G_FALSE,
843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
844 p_default => p_default,
845 p_module_type => p_module_type,
846 x_return_status => x_return_status,
847 x_msg_count => x_msg_count,
848 x_msg_data => x_msg_data,
849 p_x_rt_oper_panel_tbl => p_x_route_panel_tbl,
850 p_association_type_code => 'ROUTE',
851 p_object_id => l_route_id
852 );
853 -- to raise errors from AHL_RM_RT_OPER_PANEL_PVT.process_rt_oper_panel
854 x_msg_count := FND_MSG_PUB.count_msg;
855
856 IF x_msg_count > 0 THEN
857 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861 END IF;
862
863 --Attach Operations
864 IF p_x_route_operation_tbl.count > 0 THEN
865
866 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
867 (
868 p_api_version => 1.0,
869 p_init_msg_list => FND_API.G_FALSE,
870 p_commit => FND_API.G_FALSE,
871 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
872 p_default => p_default,
873 p_module_type => p_module_type,
874 x_return_status => x_return_status,
875 x_msg_count => x_msg_count,
876 x_msg_data => x_msg_data,
877 p_x_route_operation_tbl => p_x_route_operation_tbl,
878 p_route_id => l_route_id
879 );
880
881 -- to raise errors from AHL_RM_MATERIAL_AS_PVT.process_material_req
882 x_msg_count := FND_MSG_PUB.count_msg;
883
884 IF x_msg_count > 0 THEN
885 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888
889 END IF;
890
891 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
892 fnd_log.string
893 (
894 fnd_log.level_procedure,
895 l_debug_module ||'.End',
896 'At the end of PLSQL procedure...'
897 );
898 END IF;
899
900 -- Check Error Message stack.
901 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
902 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
903 fnd_log.string
904 (
905 fnd_log.level_error,
906 l_debug_module ,
907 'Private API raised expected error....'
908 );
909 END IF;
910 RAISE FND_API.G_EXC_ERROR;
911 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
912 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
913 fnd_log.string
914 (
915 fnd_log.level_error,
916 l_debug_module ,
917 'Private API raised unexpected error....'
918 );
919 END IF;
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922
923 -- Standard check of p_commit
924 IF FND_API.To_Boolean(p_commit) THEN
925 COMMIT WORK;
926 END IF;
927
928 --
929 EXCEPTION
930 WHEN FND_API.G_EXC_ERROR THEN
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 Rollback to modify_route_pub;
933 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
934 p_data => x_msg_data,
935 p_encoded => fnd_api.g_false);
936
937
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940 Rollback to modify_route_pub;
941 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
942 p_data => x_msg_data,
943 p_encoded => fnd_api.g_false);
944
945 WHEN OTHERS THEN
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
947 Rollback to modify_route_pub;
948 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
949 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
950 p_procedure_name => 'MODIFY_ROUTE',
951 p_error_text => SQLERRM);
952 END IF;
953 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
954 p_data => x_msg_data,
955 p_encoded => fnd_api.g_false);
956
957 END Modify_Route;
958
959
960 -- Overloaded procedure retained for backaward compatibility (pre 12.0.4).
961 -- This procedure will call the above procedure with api_version = 2.
962 PROCEDURE Modify_Route
963 (
964 -- standard IN params
965 p_api_version IN NUMBER,
966 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
967 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
968 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
969 p_default IN VARCHAR2 :=FND_API.G_FALSE,
970 p_module_type IN VARCHAR2 :=NULL,
971 -- standard OUT params
972 x_return_status OUT NOCOPY VARCHAR2,
973 x_msg_count OUT NOCOPY NUMBER,
974 x_msg_data OUT NOCOPY VARCHAR2,
975 -- procedure params
976 p_route_rec IN AHL_RM_ROUTE_PVT.route_rec_type,
977 p_x_route_doc_tbl IN OUT NOCOPY AHL_RM_ASSO_DOCASO_PVT.doc_association_tbl,
978 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type,
979 p_x_route_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.rt_oper_resource_tbl_type,
980 p_x_route_material_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type
981 )
982 IS
983
984 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_ROUTE';
985 l_api_version CONSTANT NUMBER := 1.0;
986
987 l_x_route_panel_tbl AHL_RM_RT_OPER_PANEL_PVT.rt_oper_panel_tbl_type;
988
989 BEGIN
990
991 -- Initialize API return status to success
992 x_return_status := FND_API.G_RET_STS_SUCCESS;
993
994 -- Standard call to check for call compatibility.
995 IF NOT FND_API.compatible_api_call
996 (
997 l_api_version,
998 p_api_version,
999 l_api_name,
1000 G_PKG_NAME
1001 )
1002 THEN
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 END IF;
1005
1006 AHL_RM_ROUTE_PUB.Modify_Route(
1007 p_api_version => 2.0,
1008 p_init_msg_list => p_init_msg_list,
1009 p_commit => p_commit,
1010 p_validation_level => p_validation_level,
1011 p_default => p_default,
1012 p_module_type => p_module_type,
1013 x_return_status => x_return_status,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data,
1016 p_route_rec => p_route_rec,
1017 p_x_route_doc_tbl => p_x_route_doc_tbl,
1018 p_x_route_operation_tbl => p_x_route_operation_tbl,
1019 p_x_route_resource_tbl => p_x_route_resource_tbl,
1020 p_x_route_material_tbl => p_x_route_material_tbl,
1021 p_x_route_panel_tbl => l_x_route_panel_tbl);
1022
1023 --
1024 EXCEPTION
1025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1027 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1028 p_data => x_msg_data,
1029 p_encoded => fnd_api.g_false);
1030
1031 WHEN OTHERS THEN
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1034 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1035 p_procedure_name => 'CREATE_ROUTE',
1036 p_error_text => SQLERRM);
1037 END IF;
1038 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1039 p_data => x_msg_data,
1040 p_encoded => fnd_api.g_false);
1041
1042 END Modify_Route;
1043
1044
1045
1046 PROCEDURE Delete_Route
1047 (
1048 -- standard IN params
1049 p_api_version IN NUMBER,
1050 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1051 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1052 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1053 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1054 p_module_type IN VARCHAR2 :=NULL,
1055 -- standard OUT params
1056 x_return_status OUT NOCOPY VARCHAR2,
1057 x_msg_count OUT NOCOPY NUMBER,
1058 x_msg_data OUT NOCOPY VARCHAR2,
1059 -- procedure params
1060 p_route_id IN VARCHAR2,
1061 p_route_number IN VARCHAR2,
1062 p_route_revision IN NUMBER,
1063 p_route_object_version IN NUMBER
1064 )
1065 IS
1066 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROUTE';
1067 l_api_version CONSTANT NUMBER := 1.0;
1068 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1069 l_route_id NUMBER;
1070 BEGIN
1071
1072 -- Initialize API return status to success
1073 x_return_status := FND_API.G_RET_STS_SUCCESS;
1074
1075 -- Standard Start of API savepoint
1076 -- Changed STHILAK bug # 10139384
1077 -- SAVEPOINT delete_operation_pub;
1078 SAVEPOINT delete_route_pub;
1079
1080 -- Standard call to check for call compatibility.
1081 IF NOT FND_API.compatible_api_call
1082 (
1083 l_api_version,
1084 p_api_version,
1085 l_api_name,
1086 G_PKG_NAME
1087 )
1088 THEN
1089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 END IF;
1091
1092 -- Initialize message list if p_init_msg_list is set to TRUE.
1093 IF FND_API.to_boolean( p_init_msg_list ) THEN
1094 FND_MSG_PUB.initialize;
1095 END IF;
1096
1097 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1098 fnd_log.string
1099 (
1100 fnd_log.level_procedure,
1101 l_debug_module ||'.begin',
1102 'At the start of PLSQL procedure'
1103 );
1104 END IF;
1105
1106 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1107
1108 fnd_log.string
1109 (
1110 fnd_log.level_statement,
1111 l_debug_module ,
1112 'Before calling the private API AHL_RM_ROUTE_PVT.delete_route.....'
1113 );
1114
1115 END IF;
1116
1117 l_route_id := p_route_id;
1118
1119 -- If Id is null derive Route id from Route Number and revision
1120 IF l_route_id IS NULL THEN
1121 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1122 (
1123 p_route_number => p_route_number,
1124 p_route_revision => p_route_revision,
1125 x_route_id => l_route_id,
1126 x_return_status => x_return_status
1127 );
1128 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1129 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1130 fnd_log.string
1131 (
1132 fnd_log.level_error,
1133 l_debug_module ,
1134 'Error in AHL_RM_ROUTE_UTIL.Route_Number_To_Id API'
1135 );
1136 END IF;
1137 RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139
1140 END IF;
1141
1142 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1143
1144 fnd_log.string
1145 (
1146 fnd_log.level_statement,
1147 l_debug_module ,
1148 'Route Id of the Route that is being deleted .....' || l_route_id
1149 );
1150
1151 END IF;
1152
1153
1154 AHL_RM_ROUTE_PVT.delete_route
1155 (
1156 p_api_version => 1.0,
1157 p_init_msg_list => FND_API.G_FALSE,
1158 p_commit => FND_API.G_FALSE,
1159 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1160 p_default => p_default,
1161 p_module_type => p_module_type,
1162 x_return_status => x_return_status,
1163 x_msg_count => x_msg_count,
1164 x_msg_data => x_msg_data,
1165 p_route_id => l_route_id,
1166 p_object_version_number => p_route_object_version
1167 );
1168 -- to raise errors from AHL_RM_ROUTE_PVT.process_route
1169 x_msg_count := FND_MSG_PUB.count_msg;
1170
1171 IF x_msg_count > 0 THEN
1172 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1173 RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175
1176
1177 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1178 fnd_log.string
1179 (
1180 fnd_log.level_procedure,
1181 l_debug_module ||'.End',
1182 'At the end of PLSQL procedure...'
1183 );
1184 END IF;
1185
1186 -- Check Error Message stack.
1187 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1188 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1189 fnd_log.string
1190 (
1191 fnd_log.level_error,
1192 l_debug_module ,
1193 'Private API raised expected error....'
1194 );
1195 END IF;
1196 RAISE FND_API.G_EXC_ERROR;
1197 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
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 'Private API raised unexpected error....'
1204 );
1205 END IF;
1206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1207 END IF;
1208
1209 -- Standard check of p_commit
1210 IF FND_API.To_Boolean(p_commit) THEN
1211 COMMIT WORK;
1212 END IF;
1213
1214 --
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 Rollback to delete_route_pub;
1219 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1220 p_data => x_msg_data,
1221 p_encoded => fnd_api.g_false);
1222
1223
1224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 Rollback to delete_route_pub;
1227 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1228 p_data => x_msg_data,
1229 p_encoded => fnd_api.g_false);
1230
1231 WHEN OTHERS THEN
1232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233 Rollback to delete_route_pub;
1234 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1235 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1236 p_procedure_name => 'DELETE_ROUTE',
1237 p_error_text => SQLERRM);
1238 END IF;
1239 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1240 p_data => x_msg_data,
1241 p_encoded => fnd_api.g_false);
1242
1243 END Delete_Route;
1244
1245
1246
1247
1248 PROCEDURE Process_Route_Dispositions
1249 (
1250 -- standard IN params
1251 p_api_version IN NUMBER,
1252 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1253 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1254 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1255 p_default IN VARCHAR2 :=FND_API.G_FALSE,
1256 p_module_type IN VARCHAR2 :=NULL,
1257 -- standard OUT params
1258 x_return_status OUT NOCOPY VARCHAR2,
1259 x_msg_count OUT NOCOPY NUMBER,
1260 x_msg_data OUT NOCOPY VARCHAR2,
1261 -- procedure params
1262 p_route_id IN NUMBER,
1263 p_route_number IN VARCHAR2,
1264 p_route_revision IN NUMBER,
1265 p_x_route_efct_rec IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.route_efct_rec_type,
1266 p_x_route_efct_mat_tbl IN OUT NOCOPY AHL_RM_MATERIAL_AS_PVT.material_req_tbl_type
1267 )
1268 IS
1269 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_DISPOSITION';
1270 l_api_version CONSTANT NUMBER := 1.0;
1271 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1272 l_x_route_efct_tbl AHL_RM_MATERIAL_AS_PVT.route_efct_tbl_type;
1273 l_route_id NUMBER;
1274 BEGIN
1275
1276 -- Initialize API return status to success
1277 x_return_status := FND_API.G_RET_STS_SUCCESS;
1278
1279 -- Standard Start of API savepoint
1280 SAVEPOINT route_disp_pub;
1281
1282 -- Standard call to check for call compatibility.
1283 IF NOT FND_API.compatible_api_call
1284 (
1285 l_api_version,
1286 p_api_version,
1287 l_api_name,
1288 G_PKG_NAME
1289 )
1290 THEN
1291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1292 END IF;
1293
1294 -- Initialize message list if p_init_msg_list is set to TRUE.
1295 IF FND_API.to_boolean( p_init_msg_list ) THEN
1296 FND_MSG_PUB.initialize;
1297 END IF;
1298
1299
1300 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1301 fnd_log.string
1302 (
1303 fnd_log.level_procedure,
1304 l_debug_module ||'.begin',
1305 'At the start of PLSQL procedure'
1306 );
1307 END IF;
1308
1309 l_route_id := p_route_id;
1310
1311 -- If Id is null derive object id from object Number and revision
1312 IF ( l_route_id IS NULL OR
1313 l_route_id = FND_API.G_MISS_NUM )
1314 THEN
1315
1316 -- Function to convert Operation number, operation revision to id
1317 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1318 (
1319 p_route_number => p_route_number,
1320 p_route_revision => p_route_revision,
1321 x_route_id => l_route_id,
1322 x_return_status => x_return_status
1323 );
1324 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1325 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1326 fnd_log.string
1327 (
1328 fnd_log.level_error,
1329 l_debug_module ,
1330 'Error in converting Object Number, Object Revision to ID'
1331 );
1332 END IF;
1333 RAISE FND_API.G_EXC_ERROR;
1334 END IF;
1335 END IF;
1336
1337
1338 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1339 fnd_log.string
1340 (
1341 fnd_log.level_statement,
1342 l_debug_module ,
1343 'Before calling the private API.....'
1344 );
1345 END IF;
1346
1347 l_x_route_efct_tbl(1) := p_x_route_efct_rec;
1348 --Call the private API to create/modify effecvtivity header.
1349
1350 AHL_RM_MATERIAL_AS_PVT.process_route_efcts
1351 (
1352 p_api_version => 1.0,
1353 p_init_msg_list => FND_API.G_FALSE,
1354 p_commit => FND_API.G_FALSE,
1355 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1356 p_default => p_default,
1357 p_module_type => p_module_type,
1358 p_object_id => l_route_id ,
1359 x_return_status => x_return_status,
1360 x_msg_count => x_msg_count,
1361 x_msg_data => x_msg_data,
1362 p_x_route_efct_tbl => l_x_route_efct_tbl
1363 );
1364
1365
1366 IF l_x_route_efct_tbl(1).ROUTE_EFFECTIVITY_ID IS NULL
1367 THEN
1368 FND_MESSAGE.set_Name('AHL','AHL_RM_PUB_EFFEC_ID_NULL');
1369 FND_MSG_PUB.add;
1370 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1371 fnd_log.string
1372 (
1373 fnd_log.level_error,
1374 l_debug_module ,
1375 'Error Effectivity id is null'
1376 );
1377 END IF;
1378 RAISE FND_API.G_EXC_ERROR;
1379
1380 END IF;
1381
1382 --Call the private API to create/modify effectivity details for the header above.
1383 -- bug 10315822 - sthilak
1384 -- added a condition to call process_material_req
1385 -- added an another condition "l_x_route_efct_tbl(1).dml_operation <> 'D'" to fix bug #10212181
1386 IF p_x_route_efct_mat_tbl.count >0 AND l_x_route_efct_tbl(1).dml_operation <> 'D' THEN
1387 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1388 fnd_log.string
1389 (
1390 fnd_log.level_error,
1391 l_debug_module ,
1392 'Route eff. has material req. records'
1393 );
1394 END IF;
1395 AHL_RM_MATERIAL_AS_PVT.process_material_req
1396 (
1397 p_api_version => 1.0,
1398 p_init_msg_list => FND_API.G_FALSE,
1399 p_commit => FND_API.G_FALSE,
1400 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1401 p_default => p_default,
1402 p_module_type => p_module_type,
1403 x_return_status => x_return_status,
1404 x_msg_count => x_msg_count,
1405 x_msg_data => x_msg_data,
1406 p_x_material_req_tbl => p_x_route_efct_mat_tbl,
1407 p_object_id => l_x_route_efct_tbl(1).ROUTE_EFFECTIVITY_ID, -- this is the object id for association type DISPOSITION
1408 p_association_type => 'DISPOSITION'
1409 );
1410 END IF; -- end of if condition for bug #10315822
1411
1412 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1413 fnd_log.string
1414 (
1415 fnd_log.level_procedure,
1416 l_debug_module ||'.End',
1417 'At the end of PLSQL procedure...'
1418 );
1419 END IF;
1420
1421 -- Check Error Message stack.
1422 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1423 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1424 fnd_log.string
1425 (
1426 fnd_log.level_error,
1427 l_debug_module ,
1428 'Private API raised expected error....'
1429 );
1430 END IF;
1431 RAISE FND_API.G_EXC_ERROR;
1432 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1433 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1434 fnd_log.string
1435 (
1436 fnd_log.level_error,
1437 l_debug_module ,
1438 'Private API raised unexpected error....'
1439 );
1440 END IF;
1441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1442 END IF;
1443
1444 -- Standard check of p_commit
1445 IF FND_API.To_Boolean(p_commit) THEN
1446 COMMIT WORK;
1447 END IF;
1448
1449 --
1450 EXCEPTION
1451 WHEN FND_API.G_EXC_ERROR THEN
1452 x_return_status := FND_API.G_RET_STS_ERROR;
1453 Rollback to route_disp_pub;
1454 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1455 p_data => x_msg_data,
1456 p_encoded => fnd_api.g_false);
1457
1458
1459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1461 Rollback to route_disp_pub;
1462 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1463 p_data => x_msg_data,
1464 p_encoded => fnd_api.g_false);
1465
1466 WHEN OTHERS THEN
1467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1468 Rollback to route_disp_pub;
1469 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1470 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1471 p_procedure_name => 'PROCESS_ROUTE_DISPOSITION',
1472 p_error_text => SQLERRM);
1473 END IF;
1474 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1475 p_data => x_msg_data,
1476 p_encoded => fnd_api.g_false);
1477
1478 END Process_Route_Dispositions;
1479
1480
1481
1482
1483
1484 PROCEDURE Create_Route_Revision
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 x_new_route_id OUT NOCOPY NUMBER
1500 )
1501 IS
1502
1503 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ROUTE_REVISION';
1504 l_api_version CONSTANT NUMBER := 1.0;
1505 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1506 l_route_id NUMBER;
1507
1508 BEGIN
1509
1510 -- Initialize API return status to success
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512
1513 -- Standard Start of API savepoint
1514 SAVEPOINT create_route_revision_pub;
1515
1516 -- Standard call to check for call compatibility.
1517 IF NOT FND_API.compatible_api_call
1518 (
1519 l_api_version,
1520 p_api_version,
1521 l_api_name,
1522 G_PKG_NAME
1523 )
1524 THEN
1525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1526 END IF;
1527
1528
1529 -- Initialize message list if p_init_msg_list is set to TRUE.
1530 IF FND_API.to_boolean( p_init_msg_list ) THEN
1531 FND_MSG_PUB.initialize;
1532 END IF;
1533
1534 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1535 fnd_log.string
1536 (
1537 fnd_log.level_procedure,
1538 l_debug_module ||'.begin',
1539 'At the start of PLSQL procedure'
1540 );
1541 END IF;
1542
1543 -- If Id is null derive Route id from Route Number and revision
1544 l_route_id := p_route_id;
1545 IF ( p_route_id IS NULL OR
1546 p_route_id = FND_API.G_MISS_NUM )
1547 THEN
1548 -- Function to convert Operation number, operation revision to id
1549 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1550 (
1551 p_route_number => p_route_number,
1552 p_route_revision => p_route_revision,
1553 x_route_id => l_route_id,
1554 x_return_status => x_return_status
1555 );
1556 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1557 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1558 fnd_log.string
1559 (
1560 fnd_log.level_error,
1561 l_debug_module ,
1562 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1563 );
1564 END IF;
1565 RAISE FND_API.G_EXC_ERROR;
1566 END IF;
1567 END IF;
1568
1569 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1570 fnd_log.string
1571 (
1572 fnd_log.level_statement,
1573 l_debug_module ,
1574 'Before calling the private API .....'
1575 );
1576 END IF;
1577
1578 -- Call the corresponding private API
1579 AHL_RM_ROUTE_PVT.create_route_revision
1580 (
1581 p_api_version => 1.0,
1582 p_init_msg_list => FND_API.G_FALSE,
1583 p_commit => FND_API.G_FALSE,
1584 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1585 p_default => p_default,
1586 p_module_type => p_module_type,
1587 x_return_status => x_return_status,
1588 x_msg_count => x_msg_count,
1589 x_msg_data => x_msg_data,
1590 p_route_id => l_route_id,
1591 p_object_version_number => p_route_object_version,
1592 x_route_id => x_new_route_id
1593 );
1594
1595 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1596 fnd_log.string
1597 (
1598 fnd_log.level_procedure,
1599 l_debug_module ||'.End',
1600 'At the end of PLSQL procedure...'
1601 );
1602 END IF;
1603
1604 -- Check Error Message stack.
1605 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1606 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1607 fnd_log.string
1608 (
1609 fnd_log.level_error,
1610 l_debug_module ,
1611 'Private API raised expected error....'
1612 );
1613 END IF;
1614 RAISE FND_API.G_EXC_ERROR;
1615 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1616 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1617 fnd_log.string
1618 (
1619 fnd_log.level_error,
1620 l_debug_module ,
1621 'Private API raised unexpected error....'
1622 );
1623 END IF;
1624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1625 END IF;
1626
1627 -- Standard check of p_commit
1628 IF FND_API.To_Boolean(p_commit) THEN
1629 COMMIT WORK;
1630 END IF;
1631
1632
1633 --
1634 EXCEPTION
1635 WHEN FND_API.G_EXC_ERROR THEN
1636 x_return_status := FND_API.G_RET_STS_ERROR;
1637 Rollback to create_route_revision_pub;
1638 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1639 p_data => x_msg_data,
1640 p_encoded => fnd_api.g_false);
1641
1642
1643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1645 Rollback to create_route_revision_pub;
1646 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1647 p_data => x_msg_data,
1648 p_encoded => fnd_api.g_false);
1649
1650 WHEN OTHERS THEN
1651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1652 Rollback to create_route_revision_pub;
1653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1654 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1655 p_procedure_name => 'create_route_revision',
1656 p_error_text => SQLERRM);
1657 END IF;
1658 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1659 p_data => x_msg_data,
1660 p_encoded => fnd_api.g_false);
1661
1662
1663 END Create_Route_Revision;
1664
1665
1666
1667
1668
1669 PROCEDURE Initiate_Route_Approval
1670 (
1671 p_api_version IN NUMBER ,
1672 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1673 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1674 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1675 p_default IN VARCHAR2 := FND_API.G_FALSE,
1676 p_module_type IN VARCHAR2 := NULL,
1677 x_return_status OUT NOCOPY VARCHAR2,
1678 x_msg_count OUT NOCOPY NUMBER,
1679 x_msg_data OUT NOCOPY VARCHAR2,
1680 p_route_id IN NUMBER,
1681 p_route_number IN VARCHAR2,
1682 p_route_revision IN NUMBER,
1683 p_route_object_version IN NUMBER,
1684 p_apprv_type IN VARCHAR2
1685 )
1686 IS
1687 l_api_version CONSTANT NUMBER := 1.0;
1688 l_api_name CONSTANT VARCHAR2(30) := 'INITIATE_ROUTE_APPROVAL';
1689 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1690 l_source_route_id NUMBER;
1691
1692 BEGIN
1693
1694 -- Initialize API return status to success
1695 x_return_status := FND_API.G_RET_STS_SUCCESS;
1696
1697 -- Standard Start of API savepoint
1698 SAVEPOINT initiate_route_approval_pub;
1699
1700 -- Standard call to check for call compatibility.
1701 IF NOT FND_API.compatible_api_call
1702 (
1703 l_api_version,
1704 p_api_version,
1705 l_api_name,
1706 G_PKG_NAME
1707 )
1708 THEN
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711
1712 -- Initialize message list if p_init_msg_list is set to TRUE.
1713 IF FND_API.to_boolean( p_init_msg_list ) THEN
1714 FND_MSG_PUB.initialize;
1715 END IF;
1716
1717 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1718 fnd_log.string
1719 (
1720 fnd_log.level_procedure,
1721 l_debug_module ||'.begin',
1722 'At the start of PLSQL procedure'
1723 );
1724 END IF;
1725 -- If Id is null derive Route id from Route Number and revision
1726 l_source_route_id := p_route_id ;
1727 IF ( p_route_id IS NULL OR
1728 p_route_id = FND_API.G_MISS_NUM )
1729 THEN
1730 -- Function to convert Operation number, operation revision to id
1731 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1732 (
1733 p_route_number => p_route_number,
1734 p_route_revision => p_route_revision,
1735 x_route_id => l_source_route_id,
1736 x_return_status => x_return_status
1737 );
1738 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1739 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1740 fnd_log.string
1741 (
1742 fnd_log.level_error,
1743 l_debug_module ,
1744 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1745 );
1746 END IF;
1747 RAISE FND_API.G_EXC_ERROR;
1748 END IF;
1749 END IF;
1750
1751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1752 fnd_log.string
1753 (
1754 fnd_log.level_statement,
1755 l_debug_module ,
1756 'Before calling the private API.....'
1757 );
1758 END IF;
1759
1760
1761 -- Call the private API
1762 AHL_RM_APPROVAL_PVT.INITIATE_ROUTE_APPROVAL
1763 (
1764 p_api_version => 1.0,
1765 p_init_msg_list => FND_API.G_FALSE,
1766 p_commit => FND_API.G_FALSE,
1767 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1768 p_default => p_default,
1769 p_module_type => p_module_type,
1770 x_return_status => x_return_status,
1771 x_msg_count => x_msg_count,
1772 x_msg_data => x_msg_data,
1773 p_source_route_id => l_source_route_id,
1774 p_object_Version_number => p_route_object_version,
1775 p_apprvl_type => p_apprv_type
1776 );
1777
1778
1779 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1780 fnd_log.string
1781 (
1782 fnd_log.level_procedure,
1783 l_debug_module ||'.end',
1784 'At the end of PLSQL procedure...'
1785 );
1786 END IF;
1787
1788 -- Check Error Message stack.
1789 IF (x_return_status = FND_API.G_RET_STS_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 expected error....'
1796 );
1797 END IF;
1798 RAISE FND_API.G_EXC_ERROR;
1799 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1800 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1801 fnd_log.string
1802 (
1803 fnd_log.level_error,
1804 l_debug_module ,
1805 'Private API raised unexpected error....'
1806 );
1807 END IF;
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809 END IF;
1810
1811 -- Standard check of p_commit
1812 IF FND_API.To_Boolean(p_commit) THEN
1813 COMMIT WORK;
1814 END IF;
1815
1816 --
1817 EXCEPTION
1818 WHEN FND_API.G_EXC_ERROR THEN
1819 x_return_status := FND_API.G_RET_STS_ERROR;
1820 Rollback to initiate_route_approval_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
1826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1828 Rollback to initiate_route_approval_pub;
1829 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1830 p_data => x_msg_data,
1831 p_encoded => fnd_api.g_false);
1832
1833 WHEN OTHERS THEN
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1835 Rollback to initiate_route_approval_pub;
1836 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1837 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1838 p_procedure_name => 'INITIATE_ROUTE_APPROVAL',
1839 p_error_text => SQLERRM);
1840 END IF;
1841 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1842 p_data => x_msg_data,
1843 p_encoded => fnd_api.g_false);
1844
1845 END Initiate_Route_Approval;
1846
1847 PROCEDURE Process_Route_Oper_Assoc
1848 (
1849 p_api_version IN NUMBER := 1.0,
1850 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1851 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1852 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1853 p_default IN VARCHAR2 := FND_API.G_FALSE,
1854 p_module_type IN VARCHAR2 := NULL,
1855 x_return_status OUT NOCOPY VARCHAR2,
1856 x_msg_count OUT NOCOPY NUMBER,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 p_route_id IN NUMBER,
1859 p_route_number IN VARCHAR2,
1860 p_route_revision IN NUMBER,
1861 p_x_route_operation_tbl IN OUT NOCOPY AHL_RM_OP_ROUTE_AS_PVT.route_operation_tbl_type
1862 )
1863 IS
1864 l_api_version CONSTANT NUMBER := 1.0;
1865 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_OPER_ASSOC';
1866 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
1867 l_route_id NUMBER;
1868
1869 BEGIN
1870
1871 -- Initialize API return status to success
1872 x_return_status := FND_API.G_RET_STS_SUCCESS;
1873
1874 -- Standard Start of API savepoint
1875 SAVEPOINT process_route_operation_as_pub;
1876
1877 -- Standard call to check for call compatibility.
1878 IF NOT FND_API.compatible_api_call
1879 (
1880 l_api_version,
1881 p_api_version,
1882 l_api_name,
1883 G_PKG_NAME
1884 )
1885 THEN
1886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1887 END IF;
1888
1889 -- Initialize message list if p_init_msg_list is set to TRUE.
1890 IF FND_API.to_boolean( p_init_msg_list ) THEN
1891 FND_MSG_PUB.initialize;
1892 END IF;
1893
1894 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1895 fnd_log.string
1896 (
1897 fnd_log.level_procedure,
1898 l_debug_module ||'.begin',
1899 'At the start of PLSQL procedure'
1900 );
1901 END IF;
1902
1903 -- If Id is null derive Route id from Route Number and revision
1904 l_route_id := p_route_id;
1905 IF ( l_route_id IS NULL OR
1906 l_route_id = FND_API.G_MISS_NUM )
1907 THEN
1908 -- Function to convert Route number, Route revision to id
1909 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
1910 (
1911 p_route_number => p_route_number,
1912 p_route_revision => p_route_revision,
1913 x_route_id => l_route_id,
1914 x_return_status => x_return_status
1915 );
1916 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1917 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1918 fnd_log.string
1919 (
1920 fnd_log.level_error,
1921 l_debug_module ,
1922 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1923 );
1924 END IF;
1925 RAISE FND_API.G_EXC_ERROR;
1926 END IF;
1927 END IF;
1928
1929 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1930 fnd_log.string
1931 (
1932 fnd_log.level_statement,
1933 l_debug_module ,
1934 'Before calling the private API.....'
1935 );
1936 END IF;
1937
1938 -- Call the private API
1939 AHL_RM_OP_ROUTE_AS_PVT.process_route_operation_as
1940 (
1941 p_api_version => 1.0,
1942 p_init_msg_list => FND_API.G_FALSE,
1943 p_commit => FND_API.G_FALSE,
1944 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1945 p_default => p_default,
1946 p_module_type => p_module_type,
1947 x_return_status => x_return_status,
1948 x_msg_count => x_msg_count,
1949 x_msg_data => x_msg_data,
1950 p_x_route_operation_tbl => p_x_route_operation_tbl,
1951 p_route_id => l_route_id
1952 );
1953
1954 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1955 fnd_log.string
1956 (
1957 fnd_log.level_procedure,
1958 l_debug_module ||'.End',
1959 'At the end of PLSQL procedure...'
1960 );
1961 END IF;
1962
1963 -- Check Error Message stack.
1964 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1965 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1966 fnd_log.string
1967 (
1968 fnd_log.level_error,
1969 l_debug_module ,
1970 'Private API raised expected error....'
1971 );
1972 END IF;
1973 RAISE FND_API.G_EXC_ERROR;
1974 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1975 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1976 fnd_log.string
1977 (
1978 fnd_log.level_error,
1979 l_debug_module ,
1980 'Private API raised unexpected error....'
1981 );
1982 END IF;
1983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1984 END IF;
1985
1986 -- Standard check of p_commit
1987 IF FND_API.To_Boolean(p_commit) THEN
1988 COMMIT WORK;
1989 END IF;
1990
1991
1992
1993 --
1994 EXCEPTION
1995 WHEN FND_API.G_EXC_ERROR THEN
1996 x_return_status := FND_API.G_RET_STS_ERROR;
1997 Rollback to process_route_operation_as_pub;
1998 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1999 p_data => x_msg_data,
2000 p_encoded => fnd_api.g_false);
2001
2002
2003 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2005 Rollback to process_route_operation_as_pub;
2006 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2007 p_data => x_msg_data,
2008 p_encoded => fnd_api.g_false);
2009
2010 WHEN OTHERS THEN
2011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2012 Rollback to process_route_operation_as_pub;
2013 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2014 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2015 p_procedure_name => 'Process_Route_Oper_Assoc',
2016 p_error_text => SQLERRM);
2017 END IF;
2018 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2019 p_data => x_msg_data,
2020 p_encoded => fnd_api.g_false);
2021
2022 END Process_Route_Oper_Assoc;
2023
2024
2025 PROCEDURE Process_Route_Alt_Resources
2026 (
2027 -- standard IN params
2028 p_api_version IN NUMBER,
2029 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
2030 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2031 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2032 p_default IN VARCHAR2 :=FND_API.G_FALSE,
2033 p_module_type IN VARCHAR2 :=NULL,
2034 -- standard OUT params
2035 x_return_status OUT NOCOPY VARCHAR2,
2036 x_msg_count OUT NOCOPY NUMBER,
2037 x_msg_data OUT NOCOPY VARCHAR2,
2038 -- procedure params
2039 p_route_number IN VARCHAR2,
2040 p_route_revision IN NUMBER,
2041 p_route_id IN NUMBER,
2042 p_resource_id IN NUMBER,
2043 p_resource_name IN VARCHAR2,
2044 p_x_alt_resource_tbl IN OUT NOCOPY AHL_RM_RT_OPER_RESOURCE_PVT.alt_resource_tbl_type
2045 )
2046 IS
2047
2048 -- cursor to fetch the resource Id if the resource name is known
2049 CURSOR get_res_id( c_resource_name in number)
2050 IS
2051 SELECT RESOURCE_ID
2052 from AHL_RESOURCES
2053 where name = c_resource_name;
2054
2055
2056 -- cursor to get the resource association id given the operation and the resource details
2057 CURSOR get_rt_oper_resource (c_route_id IN NUMBER, c_resource_id IN NUMBER)
2058 IS
2059 SELECT rt_oper_resource_id
2060 from AHL_RT_OPER_RESOURCES
2061 where
2062 object_ID = c_route_id
2063 and aso_resource_id = c_resource_id;
2064
2065
2066
2067 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ROUTE_ALT_RESOURCES';
2068 l_api_version CONSTANT NUMBER := 1.0;
2069 l_debug_module CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name||'.'||l_api_name;
2070
2071 l_rt_oper_resource_id NUMBER;
2072 l_resource_id NUMBER;
2073 l_route_id NUMBER;
2074
2075
2076 BEGIN
2077
2078
2079
2080 -- Initialize API return status to success
2081 x_return_status := FND_API.G_RET_STS_SUCCESS;
2082
2083 -- Standard Start of API savepoint
2084 SAVEPOINT Process_Alt_Resources;
2085
2086 -- Standard call to check for call compatibility.
2087 IF NOT FND_API.compatible_api_call
2088 (
2089 l_api_version,
2090 p_api_version,
2091 l_api_name,
2092 G_PKG_NAME
2093 )
2094 THEN
2095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2096 END IF;
2097
2098 -- Initialize message list if p_init_msg_list is set to TRUE.
2099 IF FND_API.to_boolean( p_init_msg_list ) THEN
2100 FND_MSG_PUB.initialize;
2101 END IF;
2102
2103 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2104 fnd_log.string
2105 (
2106 fnd_log.level_procedure,
2107 l_debug_module ||'.begin',
2108 'At the start of PLSQL procedure'
2109 );
2110 END IF;
2111
2112 l_route_id := p_route_id;
2113 -- If Id is null derive Route id from Route Number and revision
2114 IF l_route_id IS NULL THEN
2115
2116 -- Function to convert Route number, Route revision to id
2117 AHL_RM_ROUTE_UTIL.Route_Number_To_Id
2118 (
2119 p_route_number => p_route_number,
2120 p_route_revision => p_route_revision,
2121 x_route_id => l_route_id,
2122 x_return_status => x_return_status
2123 );
2124
2125 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2126 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2127 fnd_log.string
2128 (
2129 fnd_log.level_error,
2130 l_debug_module ||':',
2131 'Error in AHL_RM_ROUTE_UTIL.Route_Number_To_Id API'
2132 );
2133 END IF;
2134 RAISE FND_API.G_EXC_ERROR;
2135 END IF;
2136 END IF;
2137
2138 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2139
2140 fnd_log.string
2141 (
2142 fnd_log.level_statement,
2143 l_debug_module ,
2144 'Route Id of the Operation that is being updated .....' || l_route_id
2145 );
2146 END IF;
2147
2148 -- if Id is null derive Resource Id from Resource name
2149 -- if resource Id cannot be found then throw error
2150 l_resource_id := p_resource_id ;
2151 IF l_resource_id IS NULL THEN
2152
2153 OPEN get_res_id(p_resource_name );
2154 FETCH get_res_id INTO l_resource_id;
2155 IF get_res_id%NOTFOUND THEN
2156 -- PRITHWI : please run this message and check what it displays.
2157 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_ASO_RES_ID' );
2158 FND_MESSAGE.set_token( 'RECORD', p_resource_name );
2159 FND_MSG_PUB.add;
2160 RAISE FND_API.G_EXC_ERROR;
2161 END IF;
2162
2163 CLOSE get_res_id;
2164
2165 END IF;
2166
2167 -- check whether the association ID between Operation and resource exists.
2168 OPEN get_rt_oper_resource (l_route_id , l_resource_id);
2169 FETCH get_rt_oper_resource INTO l_rt_oper_resource_id;
2170 IF get_rt_oper_resource%NOTFOUND THEN
2171 -- PRITHWI : please run this message and check what it displays.
2172 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OBJECT' );
2173 FND_MSG_PUB.add;
2174 RAISE FND_API.G_EXC_ERROR;
2175 END IF;
2176
2177 CLOSE get_rt_oper_resource;
2178
2179 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2180
2181 fnd_log.string
2182 (
2183 fnd_log.level_statement,
2184 l_debug_module ,
2185 'Before calling Private API AHL_RM_RT_OPER_RESOURCE_PVT.process_alternate_resource'
2186 );
2187 END IF;
2188
2189 AHL_RM_RT_OPER_RESOURCE_PVT.process_alternate_resource
2190 (
2191 p_api_version => 1.0 ,
2192 p_init_msg_list => p_init_msg_list,
2193 p_commit => p_commit,
2194 p_validation_level => p_validation_level,
2195 p_default => p_default,
2196 p_module_type => p_module_type,
2197 x_return_status => x_return_status,
2198 x_msg_count => x_msg_count,
2199 x_msg_data => x_msg_data,
2200 p_rt_oper_resource_id => l_rt_oper_resource_id,
2201 p_x_alt_resource_tbl => p_x_alt_resource_tbl
2202 );
2203
2204
2205 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2206
2207 fnd_log.string
2208 (
2209 fnd_log.level_statement,
2210 l_debug_module ,
2211 'After calling Private API AHL_RM_RT_OPER_RESOURCE_PVT.process_alternate_resource'
2212 );
2213 END IF;
2214
2215
2216 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2217 fnd_log.string
2218 (
2219 fnd_log.level_procedure,
2220 l_debug_module ||'.End',
2221 'At the end of PLSQL procedure...'
2222 );
2223 END IF;
2224
2225 -- Check Error Message stack.
2226 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2227 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
2228 fnd_log.string
2229 (
2230 fnd_log.level_error,
2231 l_debug_module ,
2232 'Public API raised expected error....'
2233 );
2234 END IF;
2235 RAISE FND_API.G_EXC_ERROR;
2236 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2237 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2238 fnd_log.string
2239 (
2240 fnd_log.level_error,
2241 l_debug_module ,
2242 'Public API raised unexpected error....'
2243 );
2244 END IF;
2245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2246 END IF;
2247
2248 -- Standard check of p_commit
2249 IF FND_API.To_Boolean(p_commit) THEN
2250 COMMIT WORK;
2251 END IF;
2252
2253
2254
2255 --
2256 EXCEPTION
2257 WHEN FND_API.G_EXC_ERROR THEN
2258 x_return_status := FND_API.G_RET_STS_ERROR;
2259 Rollback to Process_Alt_Resources;
2260 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2261 p_data => x_msg_data,
2262 p_encoded => fnd_api.g_false);
2263
2264
2265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2267 Rollback to Process_Alt_Resources;
2268 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2269 p_data => x_msg_data,
2270 p_encoded => fnd_api.g_false);
2271
2272 WHEN OTHERS THEN
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2274 Rollback to Process_Alt_Resources;
2275 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2276 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2277 p_procedure_name => 'Process_Route_Alt_Resources',
2278 p_error_text => SQLERRM);
2279 END IF;
2280 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2281 p_data => x_msg_data,
2282 p_encoded => fnd_api.g_false);
2283
2284 END Process_Route_Alt_Resources;
2285
2286
2287
2288 END AHL_RM_ROUTE_PUB;