[Home] [Help]
PACKAGE BODY: APPS.AMS_APPROVAL_SUBMIT_PVT
Source
1 PACKAGE BODY Ams_Approval_Submit_Pvt AS
2 /* $Header: amsvapsb.pls 120.1 2005/12/28 00:24:38 vmodur noship $ */
3
4 PROCEDURE Submit_Approval(
5 p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
7 p_commit IN VARCHAR2 := FND_API.g_false,
8 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
9 p_object_id IN NUMBER, -- from pageid
10 p_object_type IN VARCHAR2, -- from pageid
11 p_new_status_id IN NUMBER, -- will come from status dropdown on approval detail page
12
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2
16 )
17 IS
18
19 L_API_VERSION CONSTANT NUMBER := 1.0;
20 L_API_NAME CONSTANT VARCHAR2(30) := 'Submit_Approval';
21 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
22
23 l_obj_type VARCHAR2(20);
24 l_old_status_id NUMBER;
25 l_version_number NUMBER;
26 l_old_status_code VARCHAR2(20);
27 l_setup_id NUMBER;
28 l_other VARCHAR2(80);
29 l_return_status VARCHAR2(1);
30 x_approval_type VARCHAR2(20);
31 l_sys_status_type VARCHAR2(30);
32 l_new_sys_stat_code VARCHAR2(30);
33 l_old_sys_stat_code VARCHAR2(30);
34 l_rej_status_id NUMBER;
35
36 PROCEDURE Get_Object_Details
37 ( p_object_type IN VARCHAR2,
38 p_object_id IN NUMBER,
39
40 x_obj_type OUT NOCOPY VARCHAR2,
41 x_old_status_id OUT NOCOPY NUMBER,
42 x_version_number OUT NOCOPY NUMBER,
43 x_old_status_code OUT NOCOPY VARCHAR2,
44 x_setup_id OUT NOCOPY NUMBER,
45 x_other OUT NOCOPY VARCHAR2, -- like DFF
46 x_return_status OUT NOCOPY VARCHAR2 )
47 IS
48
49 TYPE obj_csr_type IS REF CURSOR ;
50 l_obj_details obj_csr_type;
51 l_meaning VARCHAR2(80);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(4000);
54 l_error_msg VARCHAR2(4000);
55
56 BEGIN
57 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
58 IF p_object_type = 'CAMP' THEN
59 OPEN l_obj_details FOR
60 SELECT DECODE(rollup_type,'RCAM','RCAM','CAMP') object_type,
61 user_status_id,
62 object_version_number,
63 status_code,
64 custom_setup_id,
65 null
66 FROM ams_campaigns_all_b
67 WHERE campaign_id = p_object_id;
68 ELSIF p_object_type = 'CSCH' THEN
69 OPEN l_obj_details FOR
70 SELECT 'CSCH',
71 user_status_id,
72 object_version_number,
73 status_code,
74 custom_setup_id,
75 null
76 FROM ams_campaign_schedules_vl
77 WHERE schedule_id = p_object_id;
78 ELSIF p_object_type = 'EVEH' THEN
79 OPEN l_obj_details FOR
80 SELECT 'EVEH',
81 user_status_id,
82 object_version_number,
83 system_status_code,
84 setup_type_id,
85 null
86 FROM ams_event_headers_vl
87 WHERE event_header_id = p_object_id;
88 ELSIF p_object_type IN ('EVEO','EONE') THEN
89 OPEN l_obj_details FOR
90 SELECT event_object_type,
91 user_status_id,
92 object_version_number,
93 system_status_code,
94 setup_type_id,
95 null
96 FROM ams_event_offers_vl
97 WHERE event_offer_id = p_object_id
98 AND event_object_type = p_object_type;
99 ELSIF p_object_type = 'DELV' THEN
100 OPEN l_obj_details FOR
101 SELECT 'DELV',
102 user_status_id,
103 object_version_number,
104 status_code,
105 custom_setup_id,
106 null
107 FROM ams_deliverables_vl
108 WHERE deliverable_id = p_object_id;
109 ELSIF p_object_type = 'FUND' THEN -- RFRQ
110 OPEN l_obj_details FOR
111 SELECT 'FUND',
112 user_status_id,
113 object_version_number,
114 status_code,
115 custom_setup_id,
116 null
117 FROM ozf_fund_details_v
118 WHERE fund_id = p_object_id;
119 -- extend for other objects too
120 ELSIF p_object_type IN ('FREQ','BUDG') THEN
121 OPEN l_obj_details FOR
122 SELECT 'FREQ',
123 user_status_id,
124 object_version_number,
125 status_code,
126 null,
127 null
128 FROM ozf_act_budgets
129 WHERE activity_budget_id = p_object_id;
130 ELSIF p_object_type = 'PRIC' THEN
131 OPEN l_obj_details FOR
132 SELECT 'PRIC',
133 user_status_id,
134 object_version_number,
135 status_code,
136 custom_setup_id,
137 null
138 FROM ozf_price_lists_v
139 WHERE list_header_id = p_object_id;
140 ELSIF p_object_type = 'OFFR' THEN
141 OPEN l_obj_details FOR
142 SELECT 'OFFR',
143 user_status_id,
144 object_version_number,
145 status_code,
146 custom_setup_id,
147 offer_type
148 FROM ozf_offers
149 WHERE qp_list_header_id = p_object_id;
150 ELSIF p_object_type = 'CLAM' THEN
151 OPEN l_obj_details FOR
152 SELECT 'CLAM',
153 user_status_id,
154 object_version_number,
155 status_code,
156 custom_setup_id,
157 null
158 FROM ozf_claims_all
159 WHERE claim_id = p_object_id;
160 -- extend for other objects too
161 ELSE
162 Fnd_Message.Set_Name('AMS','AMS_BAD_APPROVAL_OBJECT_TYPE');
163 Fnd_Msg_Pub.ADD;
164 x_return_status := Fnd_Api.G_RET_STS_ERROR;
165 RETURN;
166 END IF ;
167
168 FETCH l_obj_details INTO x_obj_type,
169 x_old_status_id,
170 x_version_number,
171 x_old_status_code,
172 x_setup_id,
173 x_other;
174
175 IF l_obj_details%NOTFOUND THEN
176 CLOSE l_obj_details;
177 Fnd_Message.Set_Name('AMS','AMS_APPR_BAD_DETAILS');
178 Fnd_Msg_Pub.ADD;
179 x_return_status := Fnd_Api.G_RET_STS_ERROR;
180 RETURN;
181 END IF;
182 CLOSE l_obj_details;
183
184 END Get_Object_Details;
185
186 --
187 BEGIN
188 --------------------- initialize -----------------------
189 --SAVEPOINT submit_approval;
190 -- Individual Update API's will start and rollback to
191 -- savepoints
192 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
193 IF FND_API.to_boolean (p_init_msg_list) THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196 IF NOT FND_API.compatible_api_call(
197 l_api_version,
198 p_api_version,
199 l_api_name,
200 g_pkg_name
201 ) THEN
202 RAISE FND_API.g_exc_unexpected_error;
203 END IF;
204 x_return_status := FND_API.g_ret_sts_success;
205
206 --------------------- Get Current Status -----------------------
207 Get_Object_Details(p_object_type => p_object_type,
208 p_object_id => p_object_id,
209 x_obj_type => l_obj_type,
210 x_old_status_id => l_old_status_id,
211 x_version_number => l_version_number,
212 x_old_status_code => l_old_status_code,
213 x_setup_id => l_setup_id,
214 x_other => l_other,
215 x_return_status => x_return_status );
216
217 IF x_return_status <> FND_API.g_ret_sts_success THEN
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220 --------------------- check for valid status change -----------------------
221 -- Most probably this call not required as respective API's will check anyway
222 -- For FREQ, there is no custom setup and this call will cause error
223 /*
224 IF p_object_type <> 'FREQ' THEN
225 AMS_Utility_PVT.check_new_status_change(
226 p_object_type => l_obj_type, -- RCAM vs CAMP
227 p_object_id => p_object_id,
228 p_old_status_id => l_old_status_id,
229 p_new_status_id => p_new_status_id,
230 p_custom_setup_id => l_setup_id,
231 x_approval_type => x_approval_type, -- what does this return
232 x_return_status => x_return_status
233 );
234
235 IF x_return_status <> FND_API.g_ret_sts_success THEN
236 RAISE FND_API.G_EXC_ERROR;
237 END IF;
238 END IF;
239 */
240 --------------------- get system status type -----------------------
241 -- l_sys_status_type := Ams_Utility_Pvt.get_system_status_type(p_object_type);
242
243 --------------------- get new and old sys status code -----------------------
244
245 -- l_new_sys_stat_code := Ams_Utility_Pvt.get_system_status_code(p_new_status_id);
246
247 -- l_old_sys_stat_code := Ams_Utility_Pvt.get_system_status_code(l_old_status_id);
248
249 --------------------- Actual Submission -----------------------
250
251 -- for each object different call to different API's
252
253 IF p_object_type = 'EVEH' THEN
254
255 DECLARE
256 l_eveh_rec Ams_EventHeader_Pvt.evh_rec_type;
257 BEGIN
258
259 Ams_EventHeader_Pvt.init_evh_rec(l_eveh_rec);
260
261 l_eveh_rec.event_header_id := p_object_id;
262 l_eveh_rec.object_version_number := l_version_number;
263 l_eveh_rec.user_status_id := p_new_status_id;
264
265
266 -- Main call out
267 ams_EventHeader_pvt.update_event_Header(
268 p_api_version => p_api_version,
269 p_init_msg_list => p_init_msg_list,
270 p_commit => p_commit,
271 p_validation_level => p_validation_level,
272
273 p_evh_rec => l_eveh_rec,
274
275 x_return_status => x_return_status,
276 x_msg_count => x_msg_count,
277 x_msg_data => x_msg_data ) ;
278
279 END;
280 ELSIF p_object_type IN ('EVEO','EONE') THEN
281
282 DECLARE
283 l_eveo_rec Ams_EventOffer_Pvt.evo_rec_type;
284 BEGIN
285
286 Ams_EventOffer_Pvt.init_evo_rec(l_eveo_rec);
287
288 --l_eveo_rec.event_header_id := p_object_id;
289 l_eveo_rec.event_offer_id := p_object_id;
290 l_eveo_rec.object_version_number := l_version_number;
291 l_eveo_rec.user_status_id := p_new_status_id;
292
293
294 Ams_EventOffer_Pvt.update_event_offer(
295 p_api_version => p_api_version,
296 p_init_msg_list => p_init_msg_list,
297 p_commit => p_commit,
298 p_validation_level => p_validation_level,
299
300 p_evo_rec => l_eveo_rec,
301
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data ) ;
305
306 END;
307 ELSIF p_object_type = 'CAMP' THEN
308
309 DECLARE
310 l_camp_rec AMS_Campaign_Pvt.camp_rec_type;
311 BEGIN
312
313 Ams_Campaign_Pvt.init_camp_rec(l_camp_rec);
314
315 l_camp_rec.campaign_id := p_object_id;
316 l_camp_rec.object_version_number := l_version_number;
317 l_camp_rec.user_status_id := p_new_status_id;
318
319
320 Ams_Campaign_Pvt.update_campaign(
321 p_api_version => p_api_version,
322 p_init_msg_list => p_init_msg_list,
323 p_commit => p_commit,
324 p_validation_level => p_validation_level,
325
326 p_camp_rec => l_camp_rec,
327
328 x_return_status => x_return_status,
329 x_msg_count => x_msg_count,
330 x_msg_data => x_msg_data ) ;
331
332 END;
333 ELSIF p_object_type = 'CSCH' THEN
334
335 DECLARE
336 l_schedule_rec Ams_Camp_Schedule_Pvt.schedule_rec_type;
337 l_obj_ver_num NUMBER;
338 BEGIN
339
340 Ams_Camp_Schedule_Pvt.init_schedule_rec(l_schedule_rec);
341
342 l_schedule_rec.schedule_id := p_object_id;
343 l_schedule_rec.object_version_number := l_version_number;
344 l_schedule_rec.user_status_id := p_new_status_id;
345
346
347 Ams_Camp_Schedule_Pvt.update_camp_schedule(
348 p_api_version_number => p_api_version,
349 p_init_msg_list => p_init_msg_list,
350 p_commit => p_commit,
351 p_validation_level => p_validation_level,
352
353 p_schedule_rec => l_schedule_rec,
354
355 x_object_version_number => l_obj_ver_num,
356 x_return_status => x_return_status,
357 x_msg_count => x_msg_count,
358 x_msg_data => x_msg_data ) ;
359
360 END;
361 ELSIF p_object_type = 'DELV' THEN
362
363 DECLARE
364 l_deliv_rec Ams_Deliverable_Pvt.deliv_rec_type;
365 BEGIN
366
367 Ams_Deliverable_Pvt.init_deliv_rec(l_deliv_rec);
368
369 l_deliv_rec.deliverable_id := p_object_id;
370 l_deliv_rec.object_version_number := l_version_number;
371 l_deliv_rec.user_status_id := p_new_status_id;
372
373 -- Main call out
374 Ams_Deliverable_Pvt.update_deliverable(
375 p_api_version => p_api_version,
376 p_init_msg_list => p_init_msg_list,
377 p_commit => p_commit,
378 p_validation_level => p_validation_level,
379
380 p_deliv_rec => l_deliv_rec,
381
382 x_return_status => x_return_status,
383 x_msg_count => x_msg_count,
384 x_msg_data => x_msg_data ) ;
385 END;
386 ELSIF p_object_type = 'FUND' THEN -- RFRQ
387
388 DECLARE
389 l_fund_rec Ozf_Funds_Pvt.fund_rec_type;
390 BEGIN
391
392 Ozf_Funds_Pvt.init_fund_rec(l_fund_rec);
393
394 l_fund_rec.fund_id := p_object_id;
395 l_fund_rec.object_version_number := l_version_number;
396 l_fund_rec.user_status_id := p_new_status_id;
397 l_fund_rec.status_code :=
398 ams_utility_pvt.get_system_status_code(p_new_status_id);
399
400 -- Main call out
401 Ozf_Funds_Pvt.update_fund(
402 p_api_version => p_api_version,
403 p_init_msg_list => p_init_msg_list,
404 p_commit => p_commit,
405 p_validation_level => p_validation_level,
406 p_mode => jtf_plsql_api.g_update,
407
408 p_fund_rec => l_fund_rec,
409
410 x_return_status => x_return_status,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data ) ;
413 END;
414 ELSIF p_object_type IN ('FREQ','BUDG') THEN
415
416 DECLARE
417 l_act_budgets_rec Ozf_ActBudgets_Pvt.act_budgets_rec_type;
418 BEGIN
419
420 Ozf_ActBudgets_Pvt.init_act_budgets_rec(l_act_budgets_rec);
421
422 l_act_budgets_rec.activity_budget_id := p_object_id;
423 l_act_budgets_rec.object_version_number := l_version_number;
424 l_act_budgets_rec.user_status_id := p_new_status_id;
425
426 -- Main call out
427 Ozf_ActBudgets_Pvt.update_act_budgets (
428 p_api_version => p_api_version,
429 p_init_msg_list => p_init_msg_list,
430 p_commit => p_commit,
431 p_validation_level => fnd_api.g_valid_level_full, -- full
432
433 p_act_budgets_rec => l_act_budgets_rec,
434
435 x_return_status => x_return_status,
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data ) ;
438 END;
439
440 ELSIF p_object_type = 'PRIC' THEN
441
442 DECLARE
443 l_pric_rec ozf_pricelist_pvt.ozf_price_list_rec_type;
444 l_pric_line_tbl ozf_pricelist_pvt.price_list_line_tbl_type;
445 l_pricing_attr_tbl ozf_pricelist_pvt.pricing_attr_tbl_type;
446 l_qualifier_tbl ozf_pricelist_pvt.qualifiers_tbl_type;
447
448 l_list_header_id NUMBER;
449 l_error_source VARCHAR2(360);
450 l_error_location NUMBER;
451
452 BEGIN
453
454 --Ams_pricelist_Pvt.init_pricelist_rec(l_pric_rec);
455
456 l_pric_rec.list_header_id := p_object_id;
457 l_pric_rec.qp_list_header_id := p_object_id;
458 l_pric_rec.object_version_number := l_version_number;
459 l_pric_rec.custom_setup_id := l_setup_id;
460 l_pric_rec.user_status_id := p_new_status_id;
461 l_pric_rec.operation := 'UPDATE';
462
463 OZF_PRICELIST_PVT.process_price_list(
464 p_api_version => p_api_version,
465 p_init_msg_list => p_init_msg_list,
466 p_commit => p_commit,
467 p_validation_level => p_validation_level,
468 x_return_status => x_return_status,
469 x_msg_count => x_msg_count,
470 x_msg_data => x_msg_data,
471 p_price_list_rec => l_pric_rec,
472 p_price_list_line_tbl => l_pric_line_tbl,
473 p_pricing_attr_tbl => l_pricing_attr_tbl,
474 p_qualifiers_tbl => l_qualifier_tbl,
475 x_list_header_id => l_list_header_id,
476 x_error_source => l_error_source,
477 x_error_location => l_error_location);
478
479 END;
480 ELSIF p_object_type = 'OFFR' THEN
481
482 DECLARE
483 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
484 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
485
486 l_qp_list_header_id NUMBER;
487 l_error_location NUMBER;
488
489 BEGIN
490
491 -- ams_offer_pvt.init_modifier_list_rec(l_modifier_list_rec);
492
493 l_modifier_list_rec.qp_list_header_id := p_object_id;
494 l_modifier_list_rec.object_version_number := l_version_number;
495 l_modifier_list_rec.user_status_id := p_new_status_id;
496 l_modifier_list_rec.status_code :=
497 ams_utility_pvt.get_system_status_code(p_new_status_id);
498 l_modifier_list_rec.custom_setup_id := l_setup_id;
499 l_modifier_list_rec.offer_type := l_other;
500 l_modifier_list_rec.modifier_operation := 'UPDATE';
501 l_modifier_list_rec.offer_operation := 'UPDATE';
502
503 OZF_OFFER_PVT.process_modifiers(
504 p_init_msg_list => p_init_msg_list
505 ,p_api_version => p_api_version
506 ,p_commit => p_commit
507 ,x_return_status => x_return_status
508 ,x_msg_count => x_msg_count
509 ,x_msg_data => x_msg_data
510 ,p_offer_type => l_other
511 ,p_modifier_list_rec => l_modifier_list_rec
512 ,p_modifier_line_tbl => l_modifier_line_tbl
513 ,x_qp_list_header_id => l_qp_list_header_id
514 ,x_error_location => l_error_location);
515
516 END;
517
518 ELSIF p_object_type = 'CLAM' THEN
519
520 DECLARE
521 l_claim_rec Ozf_Claim_Pvt.claim_rec_type;
522 l_version NUMBER;
523 BEGIN
524
525 l_claim_rec.claim_id := p_object_id;
526 l_claim_rec.object_version_number := l_version_number;
527 l_claim_rec.user_status_id := p_new_status_id;
528
529 OZF_Claim_PVT.Update_Claim (
530 p_api_version => p_api_version
531 ,p_init_msg_list => p_init_msg_list
532 ,p_commit => p_commit
533 ,p_validation_level => p_validation_level
534
535 ,x_return_status => x_return_status
536 ,x_msg_data => x_msg_data
537 ,x_msg_count => x_msg_count
538 ,x_object_version_number => l_version
539
540 ,p_claim => l_claim_rec
541 ,p_event => 'UPDATE'
542 ,p_mode => 'AUTO');
543 END;
544 END IF;
545
546
547
548 --------------------- Get Current Status and other Details -----------------------
549
550 EXCEPTION
551 --ROLLBACK TO submit_approval;
552 WHEN fnd_api.g_exc_error THEN
553 x_return_status := fnd_api.g_ret_sts_error;
554 fnd_msg_pub.count_and_get(
555 p_encoded => fnd_api.g_false
556 ,p_count => x_msg_count
557 ,p_data => x_msg_data);
558 WHEN fnd_api.g_exc_unexpected_error THEN
559 x_return_status := fnd_api.g_ret_sts_unexp_error;
560 fnd_msg_pub.count_and_get(
561 p_encoded => fnd_api.g_false
562 ,p_count => x_msg_count
563 ,p_data => x_msg_data);
564 WHEN OTHERS THEN
565 --ROLLBACK TO submit_approval;
566 x_return_status := fnd_api.g_ret_sts_unexp_error;
567 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
568 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
569 END IF;
570 fnd_msg_pub.count_and_get(
571 p_encoded => fnd_api.g_false
572 ,p_count => x_msg_count
573 ,p_data => x_msg_data);
574
575 END Submit_Approval;
576 End Ams_Approval_Submit_Pvt;