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