[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFERADJ_APPROVAL_PVT
Source
1 PACKAGE BODY ozf_offeradj_approval_pvt AS
2 /* $Header: ozfvoawb.pls 120.3.12010000.2 2009/05/04 08:13:25 kdass ship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- OZF_OFFER_ADJUST_APPRV_PVT
8 --
9 -- PURPOSE
10 -- This package contains all transactions to be done for
11 -- Offer Adjustment Approvals in Oracle Marketing
12 --
13 -- HISTORY
14 -- 4/25/2002 mgudivak CREATION
15 -- Wed Mar 29 2006:4/45 PM RSSHARMA New Offer Adjustment changes. If the next status is active then call close adjustment API to close/activate adjustment.
16 -- else update the status to the next possible status.
17 -- 05/04/2009 kdass fixed bug 8253195
18 g_pkg_name CONSTANT VARCHAR2 (30) := 'OZF_OFFER_ADJUST_APPRV_PVT';
19 g_file_name CONSTANT VARCHAR2 (15) := 'ozfvoawb.pls';
20
21
22 --------------------------------------------------------------------------
23 -- PROCEDURE
24 -- Notify_requestor_FYI
25 --
26 -- PURPOSE
27 -- Generate the FYI Document for display in messages, either
28 -- text or html
29 -- IN
30 -- document_id - Item Key
31 -- display_type - either 'text/plain' or 'text/html'
32 -- document - document buffer
33 -- document_type - type of document buffer created, either 'text/plain'
34 -- or 'text/html'
35 -- OUT
36 -- USED BY
37 -- - Oracle MArketing Generic Apporval
38 -- HISTORY
39 -- 4/25/2002 mgudivak CREATION
40
41 PROCEDURE notify_requestor_fyi (
42 document_id IN VARCHAR2,
43 display_type IN VARCHAR2,
44 document IN OUT NOCOPY VARCHAR2,
45 document_type IN OUT NOCOPY VARCHAR2
46 ) IS
47
48 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_FYI';
49 l_hyphen_pos1 NUMBER;
50 l_fyi_notification VARCHAR2(10000);
51 l_activity_type VARCHAR2(30);
52 l_item_type VARCHAR2(30);
53 l_item_key VARCHAR2(30);
54 l_approval_type VARCHAR2(30);
55 l_approver VARCHAR2(200);
56 l_note VARCHAR2(4000);
57 l_subject VARCHAR2(500);
58 l_body VARCHAR2(3500);
59 l_requester VARCHAR2(30);
60 l_string VARCHAR2 (1000);
61 l_string1 VARCHAR2 (2500);
62 l_string2 VARCHAR2 (2500);
63
64 l_offer_adjustment_id NUMBER;
65 l_offer_name VARCHAR2(240);
66 l_offer_code VARCHAR2(240);
67 l_offer_type VARCHAR2(240);
68 l_settlement_name VARCHAR2(240);
69 l_start_date_active DATE;
70 l_end_date_active DATE;
71 l_effective_date DATE;
72 l_offer_adj_name VARCHAR2(120);
73
74 CURSOR c_offer_adj_info (p_offer_adjustment_id IN NUMBER) IS
75 select a.name,
76 substr(a.description,1,240) description,
77 lkp1.meaning offer_type,
78 a.start_date_active,
79 a.end_date_active,
80 lkp.meaning settlement_name,
81 b.effective_date,
82 b.offer_adjustment_name
83 from qp_list_headers a,
84 ozf_offer_adjustments_vl b,
85 ozf_lookups lkp,
86 ozf_lookups lkp1,
87 ozf_offers offr
88 where a.list_header_id = b.list_header_id
89 and a.list_header_id = offr.qp_list_header_id
90 and b.offer_adjustment_id = p_offer_adjustment_id
91 and lkp.lookup_code = b.settlement_code
92 and lkp.lookup_type = 'OZF_OFFER_LUMPSUM_PAYMENT'
93 and lkp1.lookup_code = offr.offer_type
94 and lkp1.lookup_type = 'OZF_OFFER_TYPE';
95
96
97 BEGIN
98 ozf_utility_pvt.debug_message (
99 l_api_name
100 || 'Entering'
101 || 'document id '
102 || document_id
103 );
104 document_type := 'text/plain';
105 -- parse document_id for the ':' dividing item type name from item key value
106 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
107 -- release 2.5 version of this demo
108 l_hyphen_pos1 := INSTR (document_id, ':');
109 l_item_type :=
110 SUBSTR (document_id, 1, l_hyphen_pos1
111 - 1);
112 l_item_key := SUBSTR (document_id, l_hyphen_pos1
113 + 1);
114 l_activity_type :=
115 wf_engine.getitemattrtext (
116 itemtype=> l_item_type,
117 itemkey=> l_item_key,
118 aname => 'AMS_ACTIVITY_TYPE'
119 );
120 l_offer_adjustment_id :=
121 wf_engine.getitemattrtext (
122 itemtype=> l_item_type,
123 itemkey=> l_item_key,
124 aname => 'AMS_ACTIVITY_ID'
125 );
126
127 l_note := wf_engine.getitemattrtext(
128 itemtype => l_item_type
129 ,itemkey => l_item_key
130 ,aname => 'AMS_NOTES_FROM_REQUESTOR'
131 );
132
133 l_approver :=
134 wf_engine.getitemattrtext (
135 itemtype=> l_item_type,
136 itemkey=> l_item_key,
137 aname => 'AMS_APPROVER'
138 );
139
140 OPEN c_offer_adj_info( l_offer_adjustment_id);
141 FETCH c_offer_adj_info INTO
142 l_offer_code,
143 l_offer_name,
144 l_offer_type,
145 l_start_date_active,
146 l_end_date_active,
147 l_settlement_name,
148 l_effective_date,
149 l_offer_adj_name ;
150 CLOSE c_offer_adj_info;
151
152
153 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_FORWARD_SUBJ');
154 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
155 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
156 l_string := SUBSTR(fnd_message.get,1,1000);
157
158 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_FORWARD_INFO');
159 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
160 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
161 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
162 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
163 fnd_message.set_token ('OFFER_TYPE', l_offer_type, FALSE);
164 fnd_message.set_token ('START_DATE', l_start_date_active, FALSE);
165 fnd_message.set_token ('END_DATE', l_end_date_active, FALSE);
166 fnd_message.set_token ('SETTLEMENT_NAME', l_settlement_name, FALSE);
167 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
168
169 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
170
171 l_fyi_notification := SUBSTR(l_string
172 || fnd_global.local_chr (10)
173 || l_string1
174 || fnd_global.local_chr (10)
175 || l_string2,1,10000);
176 document := document
177 || l_fyi_notification;
178 document_type := 'text/plain';
179 RETURN;
180 EXCEPTION
181 WHEN OTHERS THEN
182 wf_core.context (
183 'OZFGAPP',
184 'Notify_requestor_FYI',
185 l_item_type,
186 l_item_key
187 );
188 RAISE;
189 END notify_requestor_fyi;
190
191
192 --------------------------------------------------------------------------
193 -- PROCEDURE
194 -- Notify_requestor_of Approval
195 --
196 -- PURPOSE
197 -- Generate the Approval Document for display in messages, either
198 -- text or html
199 -- IN
200 -- document_id - Item Key
201 -- display_type - either 'text/plain' or 'text/html'
202 -- document - document buffer
203 -- document_type - type of document buffer created, either 'text/plain'
204 -- or 'text/html'
205 -- OUT
206 -- USED BY
207 -- - Oracle MArketing Generic Apporval
208 -- HISTORY
209 -- 4/25/2002 mgudivak CREATION
210 ----------------------------------------------------------------------------
211
212 PROCEDURE notify_requestor_of_approval (
213 document_id IN VARCHAR2,
214 display_type IN VARCHAR2,
215 document IN OUT NOCOPY VARCHAR2,
216 document_type IN OUT NOCOPY VARCHAR2
217 ) IS
218 l_api_name VARCHAR2 (100)
219 := g_pkg_name
220 || 'Notify_Requestor_of_approval';
221 l_hyphen_pos1 NUMBER;
222 l_appr_notification VARCHAR2(10000);
223 l_activity_type VARCHAR2(30);
224 l_item_type VARCHAR2(30);
225 l_item_key VARCHAR2(30);
226 l_approval_type VARCHAR2(30);
227 l_approver VARCHAR2(200);
228 l_note VARCHAR2(4000);
229 l_subject VARCHAR2(500);
230 l_body VARCHAR2(3500);
231 l_requester VARCHAR2(30);
232 l_string VARCHAR2 (1000);
233 l_string1 VARCHAR2 (2500);
234 l_string2 VARCHAR2 (2500);
235
236 l_offer_adjustment_id NUMBER;
237 l_offer_name VARCHAR2(240);
238 l_offer_code VARCHAR2(240);
239 l_offer_type VARCHAR2(240);
240 l_settlement_name VARCHAR2(240);
241 l_start_date_active DATE;
242 l_end_date_active DATE;
243 l_effective_date DATE;
244 l_offer_adj_name VARCHAR2(120);
245
246 CURSOR c_offer_adj_info (p_offer_adjustment_id IN NUMBER) IS
247 select a.name,
248 substr(a.description,1,240) description,
249 lkp1.meaning offer_type,
250 a.start_date_active,
251 a.end_date_active,
252 lkp.meaning settlement_name,
253 b.effective_date,
254 b.offer_adjustment_name
255 from qp_list_headers a,
256 ozf_offer_adjustments_vl b,
257 ozf_lookups lkp,
258 ozf_lookups lkp1,
259 ozf_offers offr
260 where a.list_header_id = b.list_header_id
261 and a.list_header_id = offr.qp_list_header_id
262 and b.offer_adjustment_id = p_offer_adjustment_id
263 and lkp.lookup_code = b.settlement_code
264 and lkp.lookup_type = 'OZF_OFFER_LUMPSUM_PAYMENT'
265 and lkp1.lookup_code = offr.offer_type
266 and lkp1.lookup_type = 'OZF_OFFER_TYPE';
267
268
269 BEGIN
270 ozf_utility_pvt.debug_message (
271 l_api_name
272 || 'Entering'
273 || 'document id '
274 || document_id
275 );
276 document_type := 'text/plain';
277 -- parse document_id for the ':' dividing item type name from item key value
278 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
279 -- release 2.5 version of this demo
280 l_hyphen_pos1 := INSTR (document_id, ':');
281 l_item_type :=
282 SUBSTR (document_id, 1, l_hyphen_pos1
283 - 1);
284 l_item_key := SUBSTR (document_id, l_hyphen_pos1
285 + 1);
286 l_activity_type :=
287 wf_engine.getitemattrtext (
288 itemtype=> l_item_type,
289 itemkey=> l_item_key,
290 aname => 'AMS_ACTIVITY_TYPE'
291 );
292 l_offer_adjustment_id :=
293 wf_engine.getitemattrtext (
294 itemtype=> l_item_type,
295 itemkey=> l_item_key,
296 aname => 'AMS_ACTIVITY_ID'
297 );
298
299 l_note :=
300 wf_engine.getitemattrtext (
301 itemtype=> l_item_type,
302 itemkey=> l_item_key,
303 aname => 'AMS_NOTES_FROM_REQUESTOR'
304 );
305
306 l_approver :=
307 wf_engine.getitemattrtext (
308 itemtype=> l_item_type,
309 itemkey=> l_item_key,
310 aname => 'AMS_APPROVER'
311 );
312
313 OPEN c_offer_adj_info( l_offer_adjustment_id);
314 FETCH c_offer_adj_info INTO
315 l_offer_code,
316 l_offer_name,
317 l_offer_type,
318 l_start_date_active,
319 l_end_date_active,
320 l_settlement_name,
321 l_effective_date,
322 l_offer_adj_name;
323 CLOSE c_offer_adj_info;
324
325 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPROVED_SUBJ');
326 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
327 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
328 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
329 l_string := Substr(FND_MESSAGE.Get,1,1000);
330
331 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPROVED_INFO');
332 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
333 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
334 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
335 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
336 fnd_message.set_token ('OFFER_TYPE', l_offer_type, FALSE);
337 fnd_message.set_token ('START_DATE', l_start_date_active, FALSE);
338 fnd_message.set_token ('END_DATE', l_end_date_active, FALSE);
339 fnd_message.set_token ('SETTLEMENT_NAME', l_settlement_name, FALSE);
340 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
341
342 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
343
344 l_appr_notification := SUBSTR(l_string
345 || fnd_global.local_chr (10)
346 || l_string1
347 || fnd_global.local_chr (10)
348 || l_string2,1,10000);
349 document := document
350 || l_appr_notification;
351 document_type := 'text/plain';
352 RETURN;
353 EXCEPTION
354 WHEN OTHERS THEN
355 wf_core.context (
356 'OZFGAPP',
357 'Notify_Requestor_of_approval',
358 l_item_type,
359 l_item_key
360 );
361 RAISE;
362 END notify_requestor_of_approval;
363
364
365 --------------------------------------------------------------------------
366 -- PROCEDURE
367 -- Notify_requestor_of rejection
368 --
369 -- PURPOSE
370 -- Generate the Rejection Document for display in messages, either
371 -- text or html
372 -- IN
373 -- document_id - Item Key
374 -- display_type - either 'text/plain' or 'text/html'
375 -- document - document buffer
376 -- document_type - type of document buffer created, either 'text/plain'
377 -- or 'text/html'
378 -- OUT
379 -- USED BY
380 -- - Oracle MArketing Generic Apporval
381 -- HISTORY
382 -- 4/25/2002 mgudivak CREATION
383 -------------------------------------------------------------------------------
384
385 PROCEDURE notify_requestor_of_rejection (
386 document_id IN VARCHAR2,
387 display_type IN VARCHAR2,
388 document IN OUT NOCOPY VARCHAR2,
389 document_type IN OUT NOCOPY VARCHAR2
390 ) IS
391 l_api_name VARCHAR2 (100)
392 := g_pkg_name
393 || 'Notify_Requestor_of_rejection';
394 l_hyphen_pos1 NUMBER;
395 l_rej_notification VARCHAR2(10000);
396 l_activity_type VARCHAR2(30);
397 l_item_type VARCHAR2(30);
398 l_item_key VARCHAR2(30);
399 l_approval_type VARCHAR2(30);
400 l_approver VARCHAR2(200);
401 l_note VARCHAR2(4000);
402 l_subject VARCHAR2(500);
403 l_body VARCHAR2(3500);
404 l_requester VARCHAR2(30);
405 l_string VARCHAR2 (1000);
406 l_string1 VARCHAR2 (2500);
407 l_string2 VARCHAR2 (2500);
408
409 l_offer_adjustment_id NUMBER;
410 l_offer_name VARCHAR2(240);
411 l_offer_code VARCHAR2(240);
412 l_offer_type VARCHAR2(240);
413 l_settlement_name VARCHAR2(240);
414 l_start_date_active DATE;
415 l_end_date_active DATE;
416 l_effective_date DATE;
417 l_offer_adj_name VARCHAR2(120);
418
419 CURSOR c_offer_adj_info (p_offer_adjustment_id IN NUMBER) IS
420 select a.name,
421 substr(a.description,1,240) description,
422 lkp1.meaning offer_type,
423 a.start_date_active,
424 a.end_date_active,
425 lkp.meaning settlement_name,
426 b.effective_date,
427 b.offer_adjustment_name
428 from qp_list_headers a,
429 ozf_offer_adjustments_vl b,
430 ozf_lookups lkp,
431 ozf_lookups lkp1,
432 ozf_offers offr
433 where a.list_header_id = b.list_header_id
434 and a.list_header_id = offr.qp_list_header_id
435 and b.offer_adjustment_id = p_offer_adjustment_id
436 and lkp.lookup_code = b.settlement_code
437 and lkp.lookup_type = 'OZF_OFFER_LUMPSUM_PAYMENT'
438 and lkp1.lookup_code = offr.offer_type
439 and lkp1.lookup_type = 'OZF_OFFER_TYPE';
440
441
442 BEGIN
443 ozf_utility_pvt.debug_message (
444 l_api_name
445 || 'Entering'
446 || 'document id '
447 || document_id
448 );
449 document_type := 'text/plain';
450 -- parse document_id for the ':' dividing item type name from item key value
451 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
452 -- release 2.5 version of this demo
453 l_hyphen_pos1 := INSTR (document_id, ':');
454 l_item_type :=
455 SUBSTR (document_id, 1, l_hyphen_pos1
456 - 1);
457 l_item_key := SUBSTR (document_id, l_hyphen_pos1
458 + 1);
459 l_activity_type :=
460 wf_engine.getitemattrtext (
461 itemtype=> l_item_type,
462 itemkey=> l_item_key,
463 aname => 'AMS_ACTIVITY_TYPE'
464 );
465 l_offer_adjustment_id :=
466 wf_engine.getitemattrtext (
467 itemtype=> l_item_type,
468 itemkey=> l_item_key,
469 aname => 'AMS_ACTIVITY_ID'
470 );
471
472 l_note :=
473 wf_engine.getitemattrtext (
474 itemtype=> l_item_type,
475 itemkey=> l_item_key,
476 aname => 'AMS_NOTES_FROM_REQUESTOR'
477 );
478
479 l_approver :=
480 wf_engine.getitemattrtext (
481 itemtype=> l_item_type,
482 itemkey=> l_item_key,
483 aname => 'AMS_APPROVER'
484 );
485
486 OPEN c_offer_adj_info( l_offer_adjustment_id);
487 FETCH c_offer_adj_info INTO
488 l_offer_code,
489 l_offer_name,
490 l_offer_type,
491 l_start_date_active,
492 l_end_date_active,
493 l_settlement_name,
494 l_effective_date,
495 l_offer_adj_name;
496 CLOSE c_offer_adj_info;
497
498 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_REJECTED_SUBJ');
499 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
500 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
501 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
502 l_string := Substr(FND_MESSAGE.Get,1,1000);
503
504 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_REJECTED_INFO');
505 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
506 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
507 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
508 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
509 fnd_message.set_token ('OFFER_TYPE', l_offer_type, FALSE);
510 fnd_message.set_token ('START_DATE', l_start_date_active, FALSE);
511 fnd_message.set_token ('END_DATE', l_end_date_active, FALSE);
512 fnd_message.set_token ('SETTLEMENT_NAME', l_settlement_name, FALSE);
513 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
514 fnd_message.set_token('COMMENTS_NOTES', l_note, false);
515
516 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
517
518
519 l_rej_notification := SUBSTR(l_string
520 || fnd_global.local_chr (10)
521 || l_string1
522 || fnd_global.local_chr (10)
523 || l_string2,1,10000);
524 document := document
525 || l_rej_notification;
526 document_type := 'text/plain';
527 RETURN;
528 EXCEPTION
529 WHEN OTHERS THEN
530 wf_core.context (
531 'OZFGAPP',
532 'Notify_requestor_of_rejection',
533 l_item_type,
534 l_item_key
535 );
536 RAISE;
537 END notify_requestor_of_rejection;
538
539
540 --------------------------------------------------------------------------
541 -- PROCEDURE
542 -- notify_approval_required
543 --
544 -- PURPOSE
545 -- Generate the Rejection Document for display in messages, either
546 -- text or html
547 -- IN
548 -- document_id - Item Key
549 -- display_type - either 'text/plain' or 'text/html'
550 -- document - document buffer
551 -- document_type - type of document buffer created, either 'text/plain'
552 -- or 'text/html'
553 -- OUT
554 -- USED BY
555 -- - Oracle MArketing Generic Apporval
556 -- HISTORY
557 -- 4/25/2002 mgudivak CREATION
558
559
560 PROCEDURE notify_approval_required (
561 document_id IN VARCHAR2,
562 display_type IN VARCHAR2,
563 document IN OUT NOCOPY VARCHAR2,
564 document_type IN OUT NOCOPY VARCHAR2
565 ) IS
566 l_api_name VARCHAR2 (100)
567 := g_pkg_name
568 || 'Notify_approval_required';
569
570 l_hyphen_pos1 NUMBER;
571 l_appreq_notification VARCHAR2(10000);
572 l_activity_type VARCHAR2(30);
573 l_item_type VARCHAR2(30);
574 l_item_key VARCHAR2(30);
575 l_approval_type VARCHAR2(30);
576 l_approver VARCHAR2(200);
577 l_note VARCHAR2(4000);
578 l_subject VARCHAR2(500);
579 l_body VARCHAR2(3500);
580 l_requester VARCHAR2(30);
581 l_string VARCHAR2 (1000);
582 l_string1 VARCHAR2 (2500);
583 l_string2 VARCHAR2 (2500);
584
585 l_offer_adjustment_id NUMBER;
586 l_offer_name VARCHAR2(240);
587 l_offer_code VARCHAR2(240);
588 l_offer_type VARCHAR2(240);
589 l_settlement_name VARCHAR2(240);
590 l_start_date_active DATE;
591 l_end_date_active DATE;
592 l_effective_date DATE;
593 l_offer_adj_name VARCHAR2(120);
594
595 CURSOR c_offer_adj_info (p_offer_adjustment_id IN NUMBER) IS
596 select a.name,
597 substr(a.description,1,240) description,
598 lkp1.meaning offer_type,
599 a.start_date_active,
600 a.end_date_active,
601 lkp.meaning settlement_name,
602 b.effective_date,
603 b.offer_adjustment_name
604 from qp_list_headers a,
605 ozf_offer_adjustments_vl b,
606 ozf_lookups lkp,
607 ozf_lookups lkp1,
608 ozf_offers offr
609 where a.list_header_id = b.list_header_id
610 and a.list_header_id = offr.qp_list_header_id
611 and b.offer_adjustment_id = p_offer_adjustment_id
612 and lkp.lookup_code = b.settlement_code
613 and lkp.lookup_type = 'OZF_OFFER_LUMPSUM_PAYMENT'
614 and lkp1.lookup_code = offr.offer_type
615 and lkp1.lookup_type = 'OZF_OFFER_TYPE';
616
617
618 BEGIN
619 ozf_utility_pvt.debug_message (
620 l_api_name
621 || 'Entering'
622 || 'document id '
623 || document_id
624 );
625 document_type := 'text/plain';
626 -- parse document_id for the ':' dividing item type name from item key value
627 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
628 -- release 2.5 version of this demo
629 l_hyphen_pos1 := INSTR (document_id, ':');
630 l_item_type :=
631 SUBSTR (document_id, 1, l_hyphen_pos1
632 - 1);
633 l_item_key := SUBSTR (document_id, l_hyphen_pos1
634 + 1);
635 l_activity_type :=
636 wf_engine.getitemattrtext (
637 itemtype=> l_item_type,
638 itemkey=> l_item_key,
639 aname => 'AMS_ACTIVITY_TYPE'
640 );
641
642 l_offer_adjustment_id :=
643 wf_engine.getitemattrtext (
644 itemtype=> l_item_type,
645 itemkey=> l_item_key,
646 aname => 'AMS_ACTIVITY_ID'
647 );
648
649 l_requester :=
650 wf_engine.getitemattrtext (
651 itemtype=> l_item_type,
652 itemkey=> l_item_key,
653 aname => 'AMS_REQUESTER'
654 );
655
656 l_note :=
657 NVL(wf_engine.getitemattrtext (
658 itemtype=> l_item_type,
659 itemkey=> l_item_key,
660 aname => 'AMS_NOTES_FROM_REQUESTOR'
661 ),'-');
662
663
664 OPEN c_offer_adj_info( l_offer_adjustment_id);
665 FETCH c_offer_adj_info INTO
666 l_offer_code,
667 l_offer_name,
668 l_offer_type,
669 l_start_date_active,
670 l_end_date_active,
671 l_settlement_name,
672 l_effective_date,
673 l_offer_adj_name;
674 CLOSE c_offer_adj_info;
675
676 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPROVAL_SUBJ');
677 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
678 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
679 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
680 l_string := Substr(FND_MESSAGE.Get,1,1000);
681
682 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPROVAL_INFO');
683 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
684 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
685 fnd_message.set_token ('OFFER_TYPE', l_offer_type, FALSE);
686 fnd_message.set_token ('START_DATE', l_start_date_active, FALSE);
687 fnd_message.set_token ('END_DATE', l_end_date_active, FALSE);
688 fnd_message.set_token ('SETTLEMENT_NAME', l_settlement_name, FALSE);
689 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
690 fnd_message.set_token('COMMENTS_NOTES', l_note, false);
691
692 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
693
694 l_appreq_notification := l_string
695 || fnd_global.local_chr (10)
696 || l_string1
697 || fnd_global.local_chr (10)
698 || l_string2;
699 document := document
700 || l_appreq_notification;
701 document_type := 'text/plain';
702 RETURN;
703
704 EXCEPTION
705 WHEN OTHERS THEN
706 wf_core.context (
707 'OZFGAPP',
708 'notify_approval_required',
709 l_item_type,
710 l_item_key
711 );
712 RAISE;
713 END notify_approval_required;
714
715
716 --------------------------------------------------------------------------
717 -- PROCEDURE
718 -- notify_appr_req_reminder
719 --
720 -- PURPOSE
721 -- Generate the Rejection Document for display in messages, either
722 -- text or html
723 -- IN
724 -- document_id - Item Key
725 -- display_type - either 'text/plain' or 'text/html'
726 -- document - document buffer
727 -- document_type - type of document buffer created, either 'text/plain'
728 -- or 'text/html'
729 -- OUT
730 -- USED BY
731 -- - Oracle MArketing Generic Apporval
732 -- HISTORY
733 -- 4/25/2002 mgudivak CREATION
734
735 PROCEDURE notify_appr_req_reminder (
736 document_id IN VARCHAR2,
737 display_type IN VARCHAR2,
738 document IN OUT NOCOPY VARCHAR2,
739 document_type IN OUT NOCOPY VARCHAR2
740 ) IS
741 l_api_name VARCHAR2 (100)
742 := g_pkg_name
743 || 'notify_appr_req_reminder';
744 l_hyphen_pos1 NUMBER;
745 l_apprem_notification VARCHAR2(10000);
746 l_activity_type VARCHAR2(30);
747 l_item_type VARCHAR2(30);
748 l_item_key VARCHAR2(30);
749 l_approval_type VARCHAR2(30);
750 l_approver VARCHAR2(200);
751 l_note VARCHAR2(4000);
752 l_subject VARCHAR2(500);
753 l_body VARCHAR2(3500);
754 l_requester VARCHAR2(30);
755 l_string VARCHAR2 (1000);
756 l_string1 VARCHAR2 (2500);
757 l_string2 VARCHAR2 (2500);
758
759 l_offer_adjustment_id NUMBER;
760 l_offer_name VARCHAR2(240);
761 l_offer_code VARCHAR2(240);
762 l_offer_type VARCHAR2(240);
763 l_settlement_name VARCHAR2(240);
764 l_start_date_active DATE;
765 l_end_date_active DATE;
766 l_effective_date DATE;
767 l_offer_adj_name VARCHAR2(120);
768
769 CURSOR c_offer_adj_info (p_offer_adjustment_id IN NUMBER) IS
770 select a.name,
771 substr(a.description,1,240) description,
772 lkp1.meaning offer_type,
773 a.start_date_active,
774 a.end_date_active,
775 lkp.meaning settlement_name,
776 b.effective_date,
777 b.offer_adjustment_name
778 from qp_list_headers a,
779 ozf_offer_adjustments_vl b,
780 ozf_lookups lkp,
781 ozf_lookups lkp1,
782 ozf_offers offr
783 where a.list_header_id = b.list_header_id
784 and a.list_header_id = offr.qp_list_header_id
785 and b.offer_adjustment_id = p_offer_adjustment_id
786 and lkp.lookup_code = b.settlement_code
787 and lkp.lookup_type = 'OZF_OFFER_LUMPSUM_PAYMENT'
788 and lkp1.lookup_code = offr.offer_type
789 and lkp1.lookup_type = 'OZF_OFFER_TYPE';
790
791
792 BEGIN
793 ozf_utility_pvt.debug_message (
794 l_api_name
795 || 'Entering'
796 || 'document id '
797 || document_id
798 );
799 document_type := 'text/plain';
800 -- parse document_id for the ':' dividing item type name from item key value
801 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
802 -- release 2.5 version of this demo
803 l_hyphen_pos1 := INSTR (document_id, ':');
804 l_item_type :=
805 SUBSTR (document_id, 1, l_hyphen_pos1
806 - 1);
807 l_item_key := SUBSTR (document_id, l_hyphen_pos1
808 + 1);
809 l_activity_type :=
810 wf_engine.getitemattrtext (
811 itemtype=> l_item_type,
812 itemkey=> l_item_key,
813 aname => 'AMS_ACTIVITY_TYPE'
814 );
815 l_offer_adjustment_id :=
816 wf_engine.getitemattrtext (
817 itemtype=> l_item_type,
818 itemkey=> l_item_key,
819 aname => 'AMS_ACTIVITY_ID'
820 );
821
822 l_requester :=
823 wf_engine.getitemattrtext (
824 itemtype=> l_item_type,
825 itemkey=> l_item_key,
826 aname => 'AMS_REQUESTER'
827 );
828
829 l_note :=
830 NVL(wf_engine.getitemattrtext (
831 itemtype=> l_item_type,
832 itemkey=> l_item_key,
833 aname => 'AMS_NOTES_FROM_REQUESTOR'
834 ),'-');
835
836
837 OPEN c_offer_adj_info( l_offer_adjustment_id);
838 FETCH c_offer_adj_info INTO
839 l_offer_code,
840 l_offer_name,
841 l_offer_type,
842 l_start_date_active,
843 l_end_date_active,
844 l_settlement_name,
845 l_effective_date,
846 l_offer_adj_name;
847 CLOSE c_offer_adj_info;
848
849 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPR_REM_SUBJ');
850 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
851 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
852 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
853 l_string := Substr(FND_MESSAGE.Get,1,1000);
854
855 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPR_REM_INFO');
856 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
857 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
858 fnd_message.set_token ('OFFER_TYPE', l_offer_type, FALSE);
859 fnd_message.set_token ('START_DATE', l_start_date_active, FALSE);
860 fnd_message.set_token ('END_DATE', l_end_date_active, FALSE);
861 fnd_message.set_token ('SETTLEMENT_NAME', l_settlement_name, FALSE);
862 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
863 fnd_message.set_token('COMMENTS_NOTES', l_note, false);
864
865 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
866
867
868 l_apprem_notification := l_string
869 || fnd_global.local_chr (10)
870 || l_string1
871 || fnd_global.local_chr (10)
872 || l_string2;
873 document := document
874 || l_apprem_notification;
875 document_type := 'text/plain';
876 RETURN;
877 EXCEPTION
878 WHEN OTHERS THEN
879 wf_core.context (
880 'OZFGAPP',
881 'notify_appr_req_reminder',
882 l_item_type,
883 l_item_key
884 );
885 RAISE;
886 END notify_appr_req_reminder;
887
888 ---------------------------------------------------------------------
889 -- PROCEDURE
890 -- Set_OffrAdj_Activity_details
891 --
892 --
893 -- PURPOSE
894 -- This Procedure will set all the item attribute details
895 --
896 --
897 -- IN
898 --
899 --
900 -- OUT
901 --
902 -- Used By Activities
903 --
904 -- NOTES
905 --
906 --
907 --
908 -- HISTORY
909 -- 4/25/2002 mgudivak CREATION
910 -- End of Comments
911 --------------------------------------------------------------------
912
913 PROCEDURE Set_OffrAdj_Activity_Details (
914 itemtype IN VARCHAR2,
915 itemkey IN VARCHAR2,
916 actid IN NUMBER,
917 funcmode IN VARCHAR2,
918 resultout OUT NOCOPY VARCHAR2
919 ) IS
920
921 l_api_version CONSTANT NUMBER := 1.0;
922 l_api_name CONSTANT VARCHAR2(30) := 'Set_OffrAdj_Activity_Details';
923 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
924
925 l_activity_id NUMBER;
926 l_activity_type VARCHAR2 (30);
927 l_approval_type VARCHAR2 (30) := 'BUDGET';
928 l_object_details ams_gen_approval_pvt.objrectyp;
929 l_approval_detail_id NUMBER;
930 l_approver_seq NUMBER;
931 l_return_status VARCHAR2 (1);
932
933 l_msg_count NUMBER;
934 l_msg_data VARCHAR2 (4000);
935 l_error_msg VARCHAR2 (4000);
936
937 l_approver VARCHAR2 (200);
938
939 l_offer_type VARCHAR2(30);
940 l_settlement_name VARCHAR2(240);
941 l_effective_date DATE;
942
943 l_lookup_meaning VARCHAR2(240);
944
945 l_orig_stat_id NUMBER;
946 l_full_name VARCHAR2 (60);
947 l_fund_number VARCHAR2 (30);
948 l_requested_amt NUMBER;
949 l_string VARCHAR2 (3000);
950
951 l_list_header_id NUMBER;
952 l_offer_adj_name VARCHAR2(120);
953
954 CURSOR c_offer_adj_info (p_offer_adjustment_id IN NUMBER) IS
955 select
956 description,
957 effective_date,
958 list_header_id,
959 offer_adjustment_name
960 from ozf_offer_adjustments_vl
961 where offer_adjustment_id = p_offer_adjustment_id;
962
963 -- changed budget_amount to use budget_amount_tc from the hardcoded 0, so that approval rules with a range would work.
964 CURSOR c_get_activity_details(p_list_header_id IN NUMBER) IS
965 SELECT qlh.description,
966 '' business_unit_id,
967 '' country_code,
968 OFF.custom_setup_id,
969 nvl(OFF.budget_amount_tc,0),
970 qlh.orig_org_id org_id, --added for bugfix 8253195
971 OFF.offer_type,--'' activity_type_code, -- Changed to fix bug#2288550
972 '' priority,
973 qlh.start_date_active,
974 qlh.end_date_active ,
975 OFF.transaction_currency_code ,
976 OFF.owner_id
977 FROM ozf_offers OFF,
978 qp_list_headers_vl qlh
979 WHERE OFF.qp_list_header_id=qlh.list_header_id
980 AND qlh.list_header_id=p_list_header_id;
981
982 CURSOR c_get_budget_detail(p_list_header_id NUMBER) IS
983 SELECT fund.short_name,
984 fund.business_unit_id,
985 fund.custom_setup_id,
986 NVL(offr.budget_amount_tc,0),
987 fund.org_id,
988 TO_CHAR(fund.category_id),
989 fund.start_date_active,
990 fund.end_date_active,
991 fund.currency_code_tc,
992 fund.owner
993 FROM ozf_funds_all_vl fund, ozf_offers offr
994 WHERE fund.fund_number = offr.offer_code
995 AND offr.qp_list_header_id = p_list_header_id;
996
997
998
999 CURSOR c_budget_offer_yn(p_list_header_id NUMBER) IS
1000 SELECT NVL(budget_offer_yn, 'N')
1001 FROM ozf_offers
1002 WHERE qp_list_header_id = p_list_header_id;
1003
1004 l_budget_offer_yn VARCHAR2(1);
1005
1006 BEGIN
1007 fnd_msg_pub.initialize;
1008
1009 l_activity_id :=
1010 wf_engine.getitemattrnumber (
1011 itemtype=> itemtype,
1012 itemkey=> itemkey,
1013 aname => 'AMS_ACTIVITY_ID'
1014 );
1015
1016 OPEN c_offer_adj_info ( l_activity_id);
1017 FETCH c_offer_adj_info INTO
1018 l_object_details.description,
1019 l_effective_date,
1020 l_list_header_id,
1021 l_offer_adj_name;
1022 CLOSE c_offer_adj_info ;
1023
1024 OPEN c_budget_offer_yn(l_list_header_id);
1025 FETCH c_budget_offer_yn INTO l_budget_offer_yn;
1026 CLOSE c_budget_offer_yn;
1027
1028 IF l_budget_offer_yn = 'N' THEN
1029 l_activity_type := 'OFFR';
1030 OPEN c_get_activity_details(l_list_header_id);
1031 FETCH c_get_activity_details INTO
1032 l_object_details.name,
1033 l_object_details.business_unit_id,
1034 l_object_details.country_code,
1035 l_object_details.setup_type_id,
1036 l_object_details.total_header_amount,
1037 l_object_details.org_id ,
1038 l_object_details.object_type,
1039 l_object_details.priority,
1040 l_object_details.start_date ,
1041 l_object_details.end_date,
1042 l_object_details.currency,
1043 l_object_details.owner_id ;
1044 CLOSE c_get_activity_details ;
1045 ELSIF l_budget_offer_yn = 'Y' THEN
1046 l_activity_type := 'RFRQ';
1047 OPEN c_get_budget_detail(l_list_header_id);
1048 FETCH c_get_budget_detail INTO
1049 l_object_details.name,
1050 l_object_details.business_unit_id,
1051 l_object_details.setup_type_id,
1052 l_object_details.total_header_amount,
1053 l_object_details.org_id,
1054 l_object_details.object_type,
1055 l_object_details.start_date,
1056 l_object_details.end_date,
1057 l_object_details.currency,
1058 l_object_details.owner_id;
1059 END IF;
1060
1061 IF (funcmode = 'RUN') THEN
1062 ams_gen_approval_pvt.get_approval_details (
1063 p_activity_id=> l_activity_id,
1064 p_activity_type=> l_activity_type,
1065 p_approval_type=> l_approval_type,
1066 p_object_details=> l_object_details,
1067 x_approval_detail_id=> l_approval_detail_id,
1068 x_approver_seq=> l_approver_seq,
1069 x_return_status=> l_return_status
1070 );
1071
1072 IF l_return_status = fnd_api.g_ret_sts_success THEN
1073 wf_engine.setitemattrnumber (
1074 itemtype=> itemtype,
1075 itemkey=> itemkey,
1076 aname => 'AMS_APPROVAL_DETAIL_ID',
1077 avalue=> l_approval_detail_id
1078 );
1079 wf_engine.setitemattrnumber (
1080 itemtype=> itemtype,
1081 itemkey=> itemkey,
1082 aname => 'AMS_APPROVER_SEQ',
1083 avalue=> l_approver_seq
1084 );
1085
1086 --- set all the subjects here
1087
1088 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_FORWARD_SUBJ');
1089 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
1090 fnd_message.set_token ('OFFER_NAME', l_object_details.name, FALSE);
1091 l_string := SUBSTR(fnd_message.get,1,1000);
1092
1093 wf_engine.setitemattrtext(
1094 itemtype => itemtype
1095 ,itemkey => itemkey
1096 ,aname => 'FYI_SUBJECT'
1097 ,avalue => l_string
1098 );
1099
1100 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPROVAL_SUBJ');
1101 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
1102 fnd_message.set_token ('OFFER_NAME', l_object_details.name, FALSE);
1103 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
1104 l_string := Substr(FND_MESSAGE.Get,1,1000);
1105
1106 wf_engine.setitemattrtext(
1107 itemtype => itemtype
1108 ,itemkey => itemkey
1109 ,aname => 'APP_SUBJECT'
1110 ,avalue => l_string
1111 );
1112
1113
1114 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_APPROVED_SUBJ');
1115 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
1116 fnd_message.set_token ('OFFER_NAME', l_object_details.name, FALSE);
1117 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
1118 l_string := Substr(FND_MESSAGE.Get,1,1000);
1119
1120 wf_engine.setitemattrtext(
1121 itemtype => itemtype
1122 ,itemkey => itemkey
1123 ,aname => 'APRV_SUBJECT'
1124 ,avalue => l_string
1125 );
1126
1127 fnd_message.set_name ('OZF', 'OZF_OFFRADJ_NTF_REJECTED_SUBJ');
1128 fnd_message.set_token ('ADJUSTMENT_NAME', l_offer_adj_name, FALSE);
1129 fnd_message.set_token ('OFFER_NAME', l_object_details.name , FALSE);
1130 fnd_message.set_token ('EFFECTIVE_DATE', l_effective_date, FALSE);
1131 l_string := Substr(FND_MESSAGE.Get,1,1000);
1132
1133 wf_engine.setitemattrtext(
1134 itemtype => itemtype
1135 ,itemkey => itemkey
1136 ,aname => 'REJECT_SUBJECT'
1137 ,avalue => l_string
1138 );
1139
1140 -- BUG 2352621
1141
1142 l_lookup_meaning := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','OFFRADJ');
1143
1144 wf_engine.setitemattrtext (
1145 itemtype=> itemtype,
1146 itemkey=> itemkey,
1147 aname => 'AMS_APPROVAL_OBJECT_MEANING',
1148 avalue=> l_lookup_meaning
1149 );
1150
1151 wf_engine.setitemattrtext (
1152 itemtype=> itemtype,
1153 itemkey=> itemkey,
1154 aname => 'AMS_APPROVAL_OBJECT_NAME',
1155 avalue=> l_object_details.name
1156 );
1157
1158 -- End 2352621
1159
1160 resultout := 'COMPLETE:SUCCESS';
1161
1162 ELSE
1163 fnd_msg_pub.count_and_get (
1164 p_encoded=> fnd_api.g_false,
1165 p_count=> l_msg_count,
1166 p_data=> l_msg_data
1167 );
1168 ams_gen_approval_pvt.handle_err (
1169 p_itemtype=> itemtype,
1170 p_itemkey=> itemkey,
1171 p_msg_count=> l_msg_count, -- Number of error Messages
1172 p_msg_data=> l_msg_data,
1173 p_attr_name=> 'AMS_ERROR_MSG',
1174 x_error_msg=> l_error_msg
1175 );
1176 wf_core.context (
1177 'ams_gen_approval_pvt',
1178 'Set_Activity_Details',
1179 itemtype,
1180 itemkey,
1181 actid,
1182 l_error_msg
1183 );
1184
1185 resultout := 'COMPLETE:ERROR';
1186 END IF;
1187 END IF;
1188
1189 --
1190 -- CANCEL mode
1191 --
1192 IF (funcmode = 'CANCEL') THEN
1193 resultout := 'COMPLETE:';
1194 RETURN;
1195 END IF;
1196
1197 --
1198 -- TIMEOUT mode
1199 --
1200 IF (funcmode = 'TIMEOUT') THEN
1201 resultout := 'COMPLETE:';
1202 RETURN;
1203 END IF;
1204 --
1205
1206 EXCEPTION
1207 WHEN fnd_api.g_exc_error THEN
1208 wf_core.context (
1209 'OZF_FundApproval_pvt',
1210 'Set_ParBudget_Activity_Details',
1211 itemtype,
1212 itemkey,
1213 actid,
1214 funcmode,
1215 l_error_msg
1216 );
1217 RAISE;
1218 WHEN OTHERS THEN
1219 fnd_msg_pub.count_and_get (
1220 p_encoded=> fnd_api.g_false,
1221 p_count=> l_msg_count,
1222 p_data=> l_msg_data
1223 );
1224 RAISE;
1225 END Set_OffrAdj_Activity_Details;
1226
1227
1228 ---------------------------------------------------------------------
1229 -- PROCEDURE
1230 -- Update_OffrAdj_Status
1231 --
1232 --
1233 -- PURPOSE
1234 -- This Procedure will update the status
1235 --
1236 --
1237 -- IN
1238 --
1239 --
1240 -- OUT
1241 --
1242 -- Used By Activities
1243 --
1244 -- NOTES
1245 --
1246 --
1247 --
1248 -- HISTORY
1249 -- 4/25/2002 mgudivak CREATION
1250 -- End of Comments
1251 -------------------------------------------------------------------
1252
1253 PROCEDURE Update_OffrAdj_Status (
1254 itemtype IN VARCHAR2,
1255 itemkey IN VARCHAR2,
1256 actid IN NUMBER,
1257 funcmode IN VARCHAR2,
1258 resultout OUT NOCOPY VARCHAR2
1259 ) IS
1260 l_status_code VARCHAR2 (30);
1261 l_api_version CONSTANT NUMBER := 1.0;
1262 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1263 l_msg_count NUMBER;
1264 l_msg_data VARCHAR2 (4000);
1265 l_error_msg VARCHAR2 (4000);
1266 l_api_name CONSTANT VARCHAR2 (30) := 'Update_OffrAdj_Status';
1267 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1268 || '.'
1269 || l_api_name;
1270
1271 l_next_status_code VARCHAR2(30);
1272 l_approved_date DATE;
1273 l_approval_status VARCHAR2 (12);
1274 l_object_version_number NUMBER;
1275 l_offer_adjustment_id NUMBER;
1276 l_effective_date DATE;
1277
1278 CURSOR c_effective_date(p_offer_adjustment_id IN NUMBER) IS
1279 SELECT effective_date
1280 FROM ozf_offer_adjustments_b
1281 WHERE offer_adjustment_id = p_offer_adjustment_id ;
1282
1283 l_user_id NUMBER;
1284 l_resp_id NUMBER;
1285 l_appl_id NUMBER;
1286 l_security_group_id NUMBER;
1287
1288 BEGIN
1289 l_user_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1290 , itemkey => itemkey
1291 , aname => 'USER_ID'
1292 );
1293 l_resp_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1294 , itemkey => itemkey
1295 , aname => 'RESPONSIBILITY_ID'
1296 );
1297 l_appl_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1298 , itemkey => itemkey
1299 , aname => 'APPLICATION_ID'
1300 );
1301 l_security_group_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1302 , itemkey => itemkey
1303 , aname => 'SECURITY_GROUP_ID'
1304 );
1305
1306 IF funcmode = 'RUN' THEN
1307 l_approval_status :=
1308 wf_engine.getitemattrtext (
1309 itemtype=> itemtype,
1310 itemkey=> itemkey,
1311 aname => 'UPDATE_GEN_STATUS'
1312 );
1313
1314 IF l_approval_status = 'APPROVED' THEN
1315
1316 l_next_status_code := 'ACTIVE';
1317 ozf_utility_pvt.debug_message ( l_full_name || l_approval_status);
1318
1319 ELSIF l_approval_status = 'REJECTED' THEN
1320
1321 l_next_status_code := 'REJECTED';
1322
1323 ELSE
1324 -- BUG 2352621
1325 l_next_status_code := 'DRAFT';
1326
1327 END IF;
1328
1329 l_object_version_number :=
1330 wf_engine.getitemattrnumber (
1331 itemtype=> itemtype,
1332 itemkey=> itemkey,
1333 aname => 'AMS_OBJECT_VERSION_NUMBER'
1334 );
1335
1336 l_offer_adjustment_id :=
1337 wf_engine.getitemattrnumber (
1338 itemtype=> itemtype,
1339 itemkey=> itemkey,
1340 aname => 'AMS_ACTIVITY_ID'
1341 );
1342
1343 OPEN c_effective_date(l_offer_adjustment_id);
1344 FETCH c_effective_date INTO l_effective_date;
1345 CLOSE c_effective_date;
1346
1347 ozf_utility_pvt.debug_message ( l_full_name || l_next_status_code || l_approval_status);
1348
1349 IF ( l_next_status_code = 'ACTIVE' )
1350 THEN
1351 -- IF (l_effective_date < SYSDATE) THEN
1352 -- bug 2989406. initialize as GEN_WF does not do it
1353 FND_GLOBAL.apps_initialize(user_id => l_user_id
1354 , resp_id => l_resp_id
1355 , resp_appl_id => l_appl_id
1356 --, security_group_id => l_security_group_id
1357 );
1358 -- Call Discounts Update API only when effective_date is less than sysdate
1359 OZF_Offer_Backdate_PVT.Update_Offer_Discounts
1360 (
1361 p_init_msg_list => FND_API.G_FALSE
1362 ,p_api_version => l_api_version
1363 ,p_commit => FND_API.G_FALSE
1364 ,x_return_status => l_return_status
1365 ,x_msg_count => l_msg_count
1366 ,x_msg_data => l_msg_data
1367 ,p_offer_adjustment_id => l_offer_adjustment_id
1368 ) ;
1369 -- END IF;
1370 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1371 RAISE FND_API.G_EXC_ERROR;
1372 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1374 END IF;
1375 -- for active status , call api which activates/ closes adjustment depending on effective date
1376 OZF_Offer_Backdate_PVT.close_adjustment
1377 (
1378 p_offer_adjustment_id => l_offer_adjustment_id
1379 , x_return_status => l_return_status
1380 , x_msg_count => l_msg_count
1381 , x_msg_data => l_msg_data
1382 );
1383 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1384 RAISE FND_API.G_EXC_ERROR;
1385 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1387 END IF;
1388 else
1389 -- for rejected or some other status update the status to next status code
1390 UPDATE ozf_offer_adjustments_b
1391 SET status_code = l_next_status_code ,
1392 -- approved_date = sysdate ,
1393 object_version_number = l_object_version_number+1
1394 WHERE offer_adjustment_id = l_offer_adjustment_id;
1395 END IF;
1396 -- Changes done by mthumu
1397 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1398 resultout := 'COMPLETE:SUCCESS';
1399 ELSE
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402 END IF;
1403
1404 -- CANCEL mode
1405 --
1406 IF (funcmode = 'CANCEL') THEN
1407 resultout := 'COMPLETE:';
1408 RETURN;
1409 END IF;
1410
1411 --
1412 -- TIMEOUT mode
1413 --
1414 IF (funcmode = 'TIMEOUT') THEN
1415 resultout := 'COMPLETE:';
1416 RETURN;
1417 END IF;
1418
1419 fnd_msg_pub.count_and_get (
1420 p_encoded=> fnd_api.g_false,
1421 p_count=> l_msg_count,
1422 p_data=> l_msg_data
1423 );
1424 ozf_utility_pvt.debug_message (
1425 l_full_name
1426 || ': l_return_status'
1427 || l_return_status
1428 );
1429 EXCEPTION
1430 WHEN FND_API.G_EXC_ERROR THEN
1431 FND_MSG_PUB.Count_And_Get (
1432 p_encoded => FND_API.G_FALSE,
1433 p_count => l_msg_count,
1434 p_data => l_msg_data);
1435
1436 ams_gen_approval_pvt.Handle_Err(
1437 p_itemtype => itemtype ,
1438 p_itemkey => itemkey ,
1439 p_msg_count => l_msg_count, -- Number of error Messages
1440 p_msg_data => l_msg_data ,
1441 p_attr_name => 'AMS_ERROR_MSG',
1442 x_error_msg => l_error_msg);
1443
1444 wf_core.context('ozf_offeradj_approval_pvt',
1445 'Update_OffrAdj_Status',
1446 itemtype, itemkey,to_char(actid),l_error_msg);
1447
1448 resultout := 'COMPLETE:ERROR';
1449
1450 WHEN OTHERS THEN
1451 FND_MSG_PUB.Count_And_Get (
1452 p_encoded => FND_API.G_FALSE,
1453 p_count => l_msg_count,
1454 p_data => l_msg_data);
1455
1456 ams_gen_approval_pvt.Handle_Err(
1457 p_itemtype => itemtype ,
1458 p_itemkey => itemkey ,
1459 p_msg_count => l_msg_count, -- Number of error Messages
1460 p_msg_data => l_msg_data ,
1461 p_attr_name => 'AMS_ERROR_MSG',
1462 x_error_msg => l_error_msg);
1463
1464 wf_core.context('ozf_offeradj_approval_pvt',
1465 'Update_OffrAdj_Status',
1466 itemtype, itemkey,to_char(actid),l_error_msg);
1467
1468 resultout := 'COMPLETE:ERROR';
1469
1470 END Update_OffrAdj_Status;
1471
1472
1473 END ozf_offeradj_approval_pvt;