[Home] [Help]
PACKAGE BODY: APPS.OZF_PRICELIST_APPROVAL_PVT
Source
1 PACKAGE BODY OZF_PriceList_Approval_PVT AS
2 /* $Header: ozfvplwb.pls 120.0 2005/06/01 03:08:24 appldev noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_PRICELIST_APPROVAL_PVT';
5 g_file_name CONSTANT VARCHAR2(15) := 'ozfvplwb.pls';
6
7 --------------------------------------------------------------------------
8 -- PROCEDURE
9 -- notify_requestor_fyi
10 --
11 -- PURPOSE
12 -- Generate the FYI Document for display in messages, either text or html
13 --
14 -- IN
15 -- document_id - Item Key
16 -- display_type - either 'text/plain' or 'text/html'
17 -- document - document buffer
18 -- document_type - type of document buffer created, either 'text/plain'
19 -- or 'text/html'
20 -- OUT
21 --
22 -- USED BY
23 -- Oracle MArketing Generic Apporval
24 --
25 -- HISTORY
26 -- 08/20/2001 julou created
27 --------------------------------------------------------------------------
28 PROCEDURE notify_requestor_fyi(
29 document_id IN VARCHAR2
30 ,display_type IN VARCHAR2
31 ,document IN OUT NOCOPY VARCHAR2
32 ,document_type IN OUT NOCOPY VARCHAR2
33 )
34 IS
35 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_FYI';
36 l_hyphen_pos1 NUMBER;
37 l_fyi_notification VARCHAR2(10000);
38 l_activity_type VARCHAR2(30);
39 l_item_type VARCHAR2(30);
40 l_item_key VARCHAR2(30);
41 l_approver VARCHAR2(30);
42 l_subject VARCHAR2(500);
43 l_body VARCHAR2(3500);
44
45 l_list_header_id NUMBER;
46 l_name VARCHAR2(240);
47 l_setup_id NUMBER;
48 l_start_date DATE;
49 l_end_date DATE;
50 l_currency VARCHAR2(30);
51 l_description VARCHAR2(2000);
52 l_owner_id NUMBER;
53 l_status_name VARCHAR2(4000);
54 l_status_date DATE;
55
56 CURSOR c_pricelist_rec(p_list_header_id IN NUMBER) IS
57 SELECT name
58 , custom_setup_id
59 , start_date_active
60 , end_date_active
61 , description
62 , owner_id
63 , currency_code
64 , user_status_name
65 , status_date
66 FROM ozf_price_lists_v
67 WHERE list_header_id = p_list_header_id;
68
69 BEGIN
70 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
71 document_type := 'text/plain';
72 -- parse document_id for the ':' dividing item type name from item key value
73 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
74 -- release 2.5 version of this demo
75 l_hyphen_pos1 := INSTR(document_id, ':');
76 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
77 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
78
79 l_activity_type := wf_engine.getitemattrtext(
80 itemtype => l_item_type
81 ,itemkey => l_item_key
82 ,aname => 'AMS_ACTIVITY_TYPE'
83 );
84
85 l_list_header_id := wf_engine.getitemattrtext(
86 itemtype => l_item_type
87 ,itemkey => l_item_key
88 ,aname => 'AMS_ACTIVITY_ID'
89 );
90
91 l_approver := wf_engine.getitemattrtext(
92 itemtype => l_item_type
93 ,itemkey => l_item_key
94 ,aname => 'AMS_APPROVER'
95 );
96
97 OPEN c_pricelist_rec(l_list_header_id);
98 FETCH c_pricelist_rec INTO l_name
99 , l_setup_id
100 , l_start_date
101 , l_end_date
102 , l_description
103 , l_owner_id
104 , l_currency
105 , l_status_name
106 , l_status_date;
107 CLOSE c_pricelist_rec;
108
109 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_FORWARD_SUBJ');
110 fnd_message.set_token('PRICELIST_NAME', l_name, false);
111 l_subject := fnd_message.get;
112
113 wf_engine.setitemattrtext(
114 itemtype => l_item_type
115 ,itemkey => l_item_key
116 ,aname => 'FYI_SUBJECT'
117 ,avalue => l_subject
118 );
119
120 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_FORWARD_INFO');
121 fnd_message.set_token('APPROVER_NAME', l_approver, false);
122 fnd_message.set_token('PRICELIST_NAME', l_name, false);
123 fnd_message.set_token('START_DATE', l_start_date, false);
124 fnd_message.set_token('END_DATE', l_end_date, false);
125 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
126 fnd_message.set_token('DESCRIPTION', l_description, false);
127
128 l_body := fnd_message.get;
129 l_fyi_notification := l_body;
130 document := document || l_fyi_notification;
131 document_type := 'text/plain';
132 RETURN;
133 EXCEPTION
134 WHEN OTHERS THEN
135 wf_core.context( 'OZFGAPP'
136 , 'Notify_requestor_FYI'
137 , l_item_type
138 , l_item_key
139 );
140 RAISE;
141 END notify_requestor_fyi;
142
143
144 --------------------------------------------------------------------------
145 -- PROCEDURE
146 -- notify_approval_required
147 --
148 -- PURPOSE
149 -- Generate the Rejection Document for display in messages, either text or html
150 --
151 -- IN
152 -- document_id - Item Key
153 -- display_type - either 'text/plain' or 'text/html'
154 -- document - document buffer
155 -- document_type - type of document buffer created, either 'text/plain'
156 -- or 'text/html'
157 --
158 -- OUT
159 --
160 -- USED BY
161 -- Oracle MArketing Generic Apporval
162 --
163 -- HISTORY
164 -- 08/20/2001 julou created
165 -------------------------------------------------------------------------------
166 PROCEDURE notify_approval_required(
167 document_id IN VARCHAR2
168 ,display_type IN VARCHAR2
169 ,document IN OUT NOCOPY VARCHAR2
170 ,document_type IN OUT NOCOPY VARCHAR2
171 )
172 IS
173 l_api_name VARCHAR2(100) := g_pkg_name || 'notify_approval_required';
174 l_hyphen_pos1 NUMBER;
175 l_activity_type VARCHAR2(30);
176 l_item_type VARCHAR2(30);
177 l_item_key VARCHAR2(30);
178 l_approver VARCHAR2(30);
179 l_subject VARCHAR2(500);
180 l_body VARCHAR2(3500);
181 l_requester VARCHAR2(30);
182
183 l_list_header_id NUMBER;
184 l_name VARCHAR2(240);
185 l_setup_id NUMBER;
186 l_start_date DATE;
187 l_end_date DATE;
188 l_currency VARCHAR2(30);
189 l_description VARCHAR2(2000);
190 l_owner_id NUMBER;
191 l_status_name VARCHAR2(4000);
192 l_status_date DATE;
193
194 CURSOR c_pricelist_rec(p_list_header_id IN NUMBER) IS
195 SELECT name
196 , custom_setup_id
197 , start_date_active
198 , end_date_active
199 , description
200 , owner_id
201 , currency_code
202 , user_status_name
203 , status_date
204 FROM ozf_price_lists_v
205 WHERE list_header_id = p_list_header_id;
206
207 BEGIN
208 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
209 document_type := 'text/plain';
210 -- parse document_id for the ':' dividing item type name from item key value
211 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
212 -- release 2.5 version of this demo
213 l_hyphen_pos1 := INSTR(document_id, ':');
214 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
215 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
216 l_activity_type := wf_engine.getitemattrtext(
217 itemtype => l_item_type
218 ,itemkey => l_item_key
219 ,aname => 'AMS_ACTIVITY_TYPE'
220 );
221
222 l_list_header_id := wf_engine.getitemattrtext(
223 itemtype => l_item_type
224 ,itemkey => l_item_key
225 ,aname => 'AMS_ACTIVITY_ID'
226 );
227
228 l_requester := wf_engine.getitemattrtext(
229 itemtype => l_item_type
230 ,itemkey => l_item_key
231 ,aname => 'AMS_REQUESTER'
232 );
233
234 l_approver := wf_engine.getitemattrtext(
235 itemtype => l_item_type
236 ,itemkey => l_item_key
237 ,aname => 'AMS_APPROVER'
238 );
239
240 OPEN c_pricelist_rec(l_list_header_id);
241 FETCH c_pricelist_rec INTO l_name
242 , l_setup_id
243 , l_start_date
244 , l_end_date
245 , l_description
246 , l_owner_id
247 , l_currency
248 , l_status_name
249 , l_status_date;
250 CLOSE c_pricelist_rec;
251
252 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPROVAL_SUBJ');
253 fnd_message.set_token('PRICELIST_NAME', l_name, false);
254 l_subject := fnd_message.get;
255
256 wf_engine.setitemattrtext(
257 itemtype => l_item_type
258 ,itemkey => l_item_key
259 ,aname => 'APP_SUBJECT'
260 ,avalue => l_subject
261 );
262
263 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPROVAL_INFO');
264 fnd_message.set_token('REQUESTER', l_requester, false);
265 fnd_message.set_token('PRICELIST_NAME', l_name, false);
266 fnd_message.set_token('START_DATE', l_start_date, false);
267 fnd_message.set_token('END_DATE', l_end_date, false);
268 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
269 fnd_message.set_token('DESCRIPTION', l_description, false);
270 l_body := fnd_message.get;
271
272 document := document || l_body;
273 document_type := 'text/plain';
274 RETURN;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 wf_core.context( 'OZFGAPP'
279 , 'Notify_requestor_FYI'
280 , l_item_type
281 , l_item_key
282 );
283 RAISE;
284 END notify_approval_required;
285
286
287 --------------------------------------------------------------------------
288 -- PROCEDURE
289 -- notify_appr_req_reminder
290 --
291 -- PURPOSE
292 -- Generate the Rejection Document for display in messages, either text or html
293 --
294 -- IN
295 -- document_id - Item Key
296 -- display_type - either 'text/plain' or 'text/html'
297 -- document - document buffer
298 -- document_type - type of document buffer created, either 'text/plain'
299 -- or 'text/html'
300 -- OUT
301 --
302 -- USED BY
303 -- Oracle MArketing Generic Apporval
304 --
305 -- HISTORY
306 -- 08/20/2001 julou created
307 -------------------------------------------------------------------------------
308 PROCEDURE notify_appr_req_reminder(
309 document_id IN VARCHAR2
310 ,display_type IN VARCHAR2
311 ,document IN OUT NOCOPY VARCHAR2
312 ,document_type IN OUT NOCOPY VARCHAR2
313 )
314 IS
315 l_api_name VARCHAR2(100) := g_pkg_name || 'notify_appr_req_reminder';
316 l_hyphen_pos1 NUMBER;
317 l_activity_type VARCHAR2(30);
318 l_item_type VARCHAR2(30);
319 l_item_key VARCHAR2(30);
320 l_approver VARCHAR2(30);
321 l_subject VARCHAR2(500);
322 l_body VARCHAR2(3500);
323 l_requester VARCHAR2(30);
324
325 l_list_header_id NUMBER;
326 l_name VARCHAR2(240);
327 l_setup_id NUMBER;
328 l_start_date DATE;
329 l_end_date DATE;
330 l_currency VARCHAR2(30);
331 l_description VARCHAR2(2000);
332 l_owner_id NUMBER;
333 l_status_name VARCHAR2(4000);
334 l_status_date DATE;
335
336 CURSOR c_pricelist_rec(p_list_header_id IN NUMBER) IS
337 SELECT name
338 , custom_setup_id
339 , start_date_active
340 , end_date_active
341 , description
342 , owner_id
343 , currency_code
344 , user_status_name
345 , status_date
346 FROM ozf_price_lists_v
347 WHERE list_header_id = p_list_header_id;
348
349 BEGIN
350 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
351 document_type := 'text/plain';
352 -- parse document_id for the ':' dividing item type name from item key value
353 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
354 -- release 2.5 version of this demo
355 l_hyphen_pos1 := INSTR(document_id, ':');
356 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
357 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
358
359 l_activity_type := wf_engine.getitemattrtext(
360 itemtype => l_item_type
361 ,itemkey => l_item_key
362 ,aname => 'AMS_ACTIVITY_TYPE'
363 );
364
365 l_list_header_id := wf_engine.getitemattrtext(
366 itemtype => l_item_type
367 ,itemkey => l_item_key
368 ,aname => 'AMS_ACTIVITY_ID'
369 );
370
371 l_requester := wf_engine.getitemattrtext(
372 itemtype => l_item_type
373 ,itemkey => l_item_key
374 ,aname => 'AMS_REQUESTER'
375 );
376
377 OPEN c_pricelist_rec(l_list_header_id);
378 FETCH c_pricelist_rec INTO l_name
379 , l_setup_id
380 , l_start_date
381 , l_end_date
382 , l_description
383 , l_owner_id
384 , l_currency
385 , l_status_name
386 , l_status_date;
387 CLOSE c_pricelist_rec;
388
389 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPR_REM_SUBJ');
390 fnd_message.set_token('PRICELIST_NAME', l_name, false);
391 l_subject := fnd_message.get;
392
393 wf_engine.setitemattrtext(
394 itemtype => l_item_type
395 ,itemkey => l_item_key
396 ,aname => 'APP_SUBJECT'
397 ,avalue => l_subject
398 );
399
400 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPR_REM_INFO');
401 fnd_message.set_token('REQUESTER', l_requester, false);
402 fnd_message.set_token('PRICELIST_NAME', l_name, false);
403 fnd_message.set_token('START_DATE', l_start_date, false);
404 fnd_message.set_token('END_DATE', l_end_date, false);
405 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
406 fnd_message.set_token('DESCRIPTION', l_description, false);
407 l_body := fnd_message.get;
408
409 document := document || l_body;
410 document_type := 'text/plain';
411 RETURN;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 wf_core.context( 'OZFGAPP'
416 , 'notify_appr_req_reminder'
417 , l_item_type
418 , l_item_key
419 );
420 RAISE;
421 END notify_appr_req_reminder;
422
423
424 --------------------------------------------------------------------------
425 -- PROCEDURE
426 -- Notify_requestor_of Approval
427 --
428 -- PURPOSE
429 -- Generate the Approval Document for display in messages, either text or html
430 --
431 -- IN
432 -- document_id - Item Key
433 -- display_type - either 'text/plain' or 'text/html'
434 -- document - document buffer
435 -- document_type - type of document buffer created, either 'text/plain'
436 -- or 'text/html'
437 -- OUT
438 --
439 -- USED BY
440 -- Oracle MArketing Generic Apporval
441 --
442 -- HISTORY
443 -- 08/20/2001 julou created
444 ----------------------------------------------------------------------------
445 PROCEDURE notify_requestor_of_approval(
446 document_id IN VARCHAR2
447 ,display_type IN VARCHAR2
448 ,document IN OUT NOCOPY VARCHAR2
449 ,document_type IN OUT NOCOPY VARCHAR2
450 )
451 IS
452 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_of_approval';
453 l_hyphen_pos1 NUMBER;
454 l_activity_type VARCHAR2(30);
455 l_item_type VARCHAR2(30);
456 l_item_key VARCHAR2(30);
457 l_approver VARCHAR2(30);
458 l_subject VARCHAR2(500);
459 l_body VARCHAR2(3500);
460 l_requester VARCHAR2(30);
461
462 l_list_header_id NUMBER;
463 l_name VARCHAR2(240);
464 l_setup_id NUMBER;
465 l_start_date DATE;
466 l_end_date DATE;
467 l_currency VARCHAR2(30);
468 l_description VARCHAR2(2000);
469 l_owner_id NUMBER;
470 l_status_name VARCHAR2(4000);
471 l_status_date DATE;
472
473 CURSOR c_pricelist_rec(p_list_header_id IN NUMBER) IS
474 SELECT name
475 , custom_setup_id
476 , start_date_active
477 , end_date_active
478 , description
479 , owner_id
480 , currency_code
481 , user_status_name
482 , status_date
483 FROM ozf_price_lists_v
484 WHERE list_header_id = p_list_header_id;
485
486 BEGIN
487 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
488 document_type := 'text/plain';
489 -- parse document_id for the ':' dividing item type name from item key value
490 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
491 -- release 2.5 version of this demo
492 l_hyphen_pos1 := INSTR(document_id, ':');
493 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
494 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
495
496 l_activity_type := wf_engine.getitemattrtext(
497 itemtype => l_item_type
498 ,itemkey => l_item_key
499 ,aname => 'AMS_ACTIVITY_TYPE'
500 );
501
502 l_list_header_id := wf_engine.getitemattrtext(
503 itemtype => l_item_type
504 ,itemkey => l_item_key
505 ,aname => 'AMS_ACTIVITY_ID'
506 );
507
508 l_approver := wf_engine.getitemattrtext(
509 itemtype => l_item_type
510 ,itemkey => l_item_key
511 ,aname => 'AMS_APPROVER'
512 );
513
514 OPEN c_pricelist_rec(l_list_header_id);
515 FETCH c_pricelist_rec INTO l_name
516 , l_setup_id
517 , l_start_date
518 , l_end_date
519 , l_description
520 , l_owner_id
521 , l_currency
522 , l_status_name
523 , l_status_date;
524 CLOSE c_pricelist_rec;
525
526 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPROVED_SUBJ');
527 fnd_message.set_token('PRICELIST_NAME', l_name, false);
528 l_subject := fnd_message.get;
529
530 wf_engine.setitemattrtext(
531 itemtype => l_item_type
532 ,itemkey => l_item_key
533 ,aname => 'APRV_SUBJECT'
534 ,avalue => l_subject
535 );
536
537 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPROVED_INFO');
538 fnd_message.set_token('APPROVER_NAME', l_approver, false);
539 fnd_message.set_token('PRICELIST_NAME', l_name, false);
540 fnd_message.set_token('START_DATE', l_start_date, false);
541 fnd_message.set_token('END_DATE', l_end_date, false);
542 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
543 fnd_message.set_token('DESCRIPTION', l_description, false);
544 l_body := fnd_message.get;
545
546 document := document || l_body;
547 document_type := 'text/plain';
548 RETURN;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 wf_core.context( 'OZFGAPP'
553 , 'notify_requestor_of_approval'
554 , l_item_type
555 , l_item_key
556 );
557 RAISE;
558 END notify_requestor_of_approval;
559
560
561 --------------------------------------------------------------------------
562 -- PROCEDURE
563 -- Notify_requestor_of rejection
564 --
565 -- PURPOSE
566 -- Generate the Rejection Document for display in messages, either text or html
567 --
568 -- IN
569 -- document_id - Item Key
570 -- display_type - either 'text/plain' or 'text/html'
571 -- document - document buffer
572 -- document_type - type of document buffer created, either 'text/plain'
573 -- or 'text/html'
574 -- OUT
575 --
576 -- USED BY
577 -- Oracle MArketing Generic Apporval
578 --
579 -- HISTORY
580 -- 08/20/2001 julou created
581 -------------------------------------------------------------------------------
582 PROCEDURE notify_requestor_of_rejection(
583 document_id IN VARCHAR2
584 ,display_type IN VARCHAR2
585 ,document IN OUT NOCOPY VARCHAR2
586 ,document_type IN OUT NOCOPY VARCHAR2
587 )
588 IS
589 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_of_rejection';
590 l_hyphen_pos1 NUMBER;
591 l_activity_type VARCHAR2(30);
592 l_item_type VARCHAR2(30);
593 l_item_key VARCHAR2(30);
594 l_approver VARCHAR2(30);
595 l_subject VARCHAR2(500);
596 l_body VARCHAR2(3500);
597 l_requester VARCHAR2(30);
598
599 l_list_header_id NUMBER;
600 l_name VARCHAR2(240);
601 l_setup_id NUMBER;
602 l_start_date DATE;
603 l_end_date DATE;
604 l_currency VARCHAR2(30);
605 l_description VARCHAR2(2000);
606 l_owner_id NUMBER;
607 l_status_name VARCHAR2(4000);
608 l_status_date DATE;
609
610 CURSOR c_pricelist_rec(p_list_header_id IN NUMBER) IS
611 SELECT name
612 , custom_setup_id
613 , start_date_active
614 , end_date_active
615 , description
616 , owner_id
617 , currency_code
618 , user_status_name
619 , status_date
620 FROM ozf_price_lists_v
621 WHERE list_header_id = p_list_header_id;
622
623 BEGIN
624 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
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 := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
631 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
632
633 l_activity_type := wf_engine.getitemattrtext(
634 itemtype => l_item_type
635 ,itemkey => l_item_key
636 ,aname => 'AMS_ACTIVITY_TYPE'
637 );
638
639 l_list_header_id := wf_engine.getitemattrtext(
640 itemtype => l_item_type
641 ,itemkey => l_item_key
642 ,aname => 'AMS_ACTIVITY_ID'
643 );
644
645 l_approver := wf_engine.getitemattrtext(
646 itemtype => l_item_type
647 ,itemkey => l_item_key
648 ,aname => 'AMS_APPROVER'
649 );
650
651 OPEN c_pricelist_rec(l_list_header_id);
652 FETCH c_pricelist_rec INTO l_name
653 , l_setup_id
654 , l_start_date
655 , l_end_date
656 , l_description
657 , l_owner_id
658 , l_currency
659 , l_status_name
660 , l_status_date;
661 CLOSE c_pricelist_rec;
662
663 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_REJECTED_SUBJ');
664 fnd_message.set_token('PRICELIST_NAME', l_name, false);
665 l_subject := fnd_message.get;
666
667 wf_engine.setitemattrtext(
668 itemtype => l_item_type
669 ,itemkey => l_item_key
670 ,aname => 'REJECT_SUBJECT'
671 ,avalue => l_subject
672 );
673
674 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_REJECTED_INFO');
675 fnd_message.set_token('APPROVER_NAME', l_approver, false);
676 fnd_message.set_token('PRICELIST_NAME', l_name, false);
677 fnd_message.set_token('START_DATE', l_start_date, false);
678 fnd_message.set_token('END_DATE', l_end_date, false);
679 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
680 fnd_message.set_token('DESCRIPTION', l_description, false);
681 l_body := fnd_message.get;
682
683 document := document || l_body;
684 document_type := 'text/plain';
685 RETURN;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 wf_core.context( 'OZFGAPP'
690 , 'notify_requestor_of_rejection'
691 , l_item_type
692 , l_item_key
693 );
694 RAISE;
695 END notify_requestor_of_rejection;
696
697 ---------------------------------------------------------------------
698 -- PROCEDURE
699 -- Set_PriceList_Activity_Details
700 --
701 -- PURPOSE
702 -- This Procedure will set all the item attribute details
703 --
704 -- IN
705 --
706 -- OUT
707 --
708 -- Used By Activities
709 --
710 -- NOTES
711 --
712 -- HISTORY
713 -- 08/20/2001 julou created
714 -------------------------------------------------------------------------------
715 PROCEDURE Set_PriceList_Activity_Details(
716 itemtype IN VARCHAR2
717 ,itemkey IN VARCHAR2
718 ,actid IN NUMBER
719 ,funcmode IN VARCHAR2
720 ,resultout OUT NOCOPY VARCHAR2
721 )
722 IS
723 l_api_version CONSTANT NUMBER := 1.0;
724 l_api_name CONSTANT VARCHAR2(30) := 'Set_PriceList_Activity_Details';
725 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
726
727 l_activity_id NUMBER;
728 l_activity_type VARCHAR2(30) := 'PRIC';
729 l_approval_type VARCHAR2(30) := 'CONCEPT';
730 l_object_details ams_gen_approval_pvt.ObjRecTyp;
731 l_approval_detail_id NUMBER;
732 l_approver_seq NUMBER;
733 l_return_status VARCHAR2(1);
734
735 l_msg_count NUMBER;
736 l_msg_data VARCHAR2(4000);
737 l_error_msg VARCHAR2(4000);
738 l_approver VARCHAR2(30);
739 l_subject VARCHAR2(500);
740 l_status_date DATE;
741 l_status_name VARCHAR2(4000);
742 l_lookup_meaning VARCHAR2(240);
743
744 CURSOR c_pricelist_obj(p_act_id IN NUMBER) IS
745 SELECT name
746 , custom_setup_id
747 , 'PRIC'
748 , start_date_active
749 , end_date_active
750 , description
751 , owner_id
752 , currency_code
753 , user_status_name
754 , status_date
755 FROM ozf_price_lists_v
756 WHERE list_header_id = p_act_id;
757
758 BEGIN
759 fnd_msg_pub.initialize;
760
761 l_activity_id := wf_engine.getitemattrnumber(
762 itemtype => itemtype
763 ,itemkey => itemkey
764 ,aname => 'AMS_ACTIVITY_ID'
765 );
766
767 OPEN c_pricelist_obj(l_activity_id);
768 FETCH c_pricelist_obj INTO l_object_details.name
769 , l_object_details.setup_type_id
770 , l_object_details.object_type
771 , l_object_details.start_date
772 , l_object_details.end_date
773 , l_object_details.description
774 , l_object_details.owner_id
775 , l_object_details.currency
776 , l_status_name
777 , l_status_date;
778 CLOSE c_pricelist_obj;
779
780 IF (funcmode = 'RUN') THEN
781 ams_gen_approval_pvt.get_approval_details(
782 p_activity_id => l_activity_id
783 ,p_activity_type => l_activity_type
784 ,p_approval_type => l_approval_type
785 ,p_object_details => l_object_details
786 ,x_approval_detail_id => l_approval_detail_id
787 ,x_approver_seq => l_approver_seq
788 ,x_return_status => l_return_status
789 );
790
791 IF l_return_status = fnd_api.g_ret_sts_success THEN
792 wf_engine.setitemattrnumber(
793 itemtype => itemtype
794 ,itemkey => itemkey
795 ,aname => 'AMS_APPROVAL_DETAIL_ID'
796 ,avalue => l_approval_detail_id
797 );
798 wf_engine.setitemattrnumber(
799 itemtype => itemtype
800 ,itemkey => itemkey
801 ,aname => 'AMS_APPROVER_SEQ'
802 ,avalue => l_approver_seq
803 );
804
805 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_FORWARD_SUBJ');
806 fnd_message.set_token('PRICELIST_NAME', l_object_details.name, false);
807 l_subject := fnd_message.get;
808
809 wf_engine.setitemattrtext(
810 itemtype => itemtype
811 ,itemkey => itemkey
812 ,aname => 'FYI_SUBJECT'
813 ,avalue => l_subject
814 );
815
816 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPROVAL_SUBJ');
817 fnd_message.set_token('PRICELIST_NAME', l_object_details.name, false);
818 l_subject := fnd_message.get;
819
820 wf_engine.setitemattrtext(
821 itemtype => itemtype
822 ,itemkey => itemkey
823 ,aname => 'APP_SUBJECT'
824 ,avalue => l_subject
825 );
826
827 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_APPROVED_SUBJ');
828 fnd_message.set_token('PRICELIST_NAME', l_object_details.name, false);
829 l_subject := fnd_message.get;
830
831 wf_engine.setitemattrtext(
832 itemtype => itemtype
833 ,itemkey => itemkey
834 ,aname => 'APRV_SUBJECT'
835 ,avalue => l_subject
836 );
837
838 fnd_message.set_name('OZF', 'OZF_PRICLST_NTF_REJECTED_SUBJ');
839 fnd_message.set_token('PRICELIST_NAME', l_object_details.name, false);
840 l_subject := fnd_message.get;
841
842 wf_engine.setitemattrtext(
843 itemtype => itemtype
844 ,itemkey => itemkey
845 ,aname => 'REJECT_SUBJECT'
846 ,avalue => l_subject
847 );
848 -- julou 07/02/2002 added for implementation of BUG 2352621
849 l_lookup_meaning := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','PRIC');
850 wf_engine.setitemattrtext (
851 itemtype=> itemtype,
852 itemkey=> itemkey,
853 aname => 'AMS_APPROVAL_OBJECT_MEANING',
854 avalue=> l_lookup_meaning
855 );
856
857 wf_engine.setitemattrtext (
858 itemtype=> itemtype,
859 itemkey=> itemkey,
860 aname => 'AMS_APPROVAL_OBJECT_NAME',
861 avalue=> l_object_details.name
862 );
863 -- End of Addition for Bug 2352621
864
865 resultout := 'COMPLETE:SUCCESS';
866 ELSE
867 fnd_msg_pub.count_and_get(
868 p_encoded => fnd_api.g_false
869 ,p_count => l_msg_count
870 ,p_data => l_msg_data
871 );
872
873 ams_gen_approval_pvt.handle_err(
874 p_itemtype => itemtype
875 ,p_itemkey => itemkey
876 ,p_msg_count => l_msg_count
877 ,p_msg_data => l_msg_data
878 ,p_attr_name => 'AMS_ERROR_MSG'
879 ,x_error_msg => l_error_msg
880 );
881
882 wf_core.context(
883 'ams_gen_approval_pvt'
884 ,'Set_Activity_Details'
885 ,itemtype
886 ,itemkey
887 ,actid
888 ,l_error_msg
889 );
890 -- RAISE FND_API.G_EXC_ERROR;
891 resultout := 'COMPLETE:ERROR';
892 END IF;
893 END IF;
894
895 --
896 -- CANCEL mode
897 --
898 IF (funcmode = 'CANCEL') THEN
899 resultout := 'COMPLETE:';
900 RETURN;
901 END IF;
902
903 --
904 -- TIMEOUT mode
905 --
906 IF (funcmode = 'TIMEOUT') THEN
907 resultout := 'COMPLETE:';
908 RETURN;
909 END IF;
910 --
911
912 EXCEPTION
913 WHEN fnd_api.g_exc_error THEN
914 wf_core.context(
915 'OZF_PriceList_Approval_PVT'
916 ,'Set_PriceList_Activity_Details'
917 ,itemtype
918 ,itemkey
919 ,actid
920 ,funcmode
921 ,l_error_msg
922 );
923 RAISE;
924 WHEN OTHERS THEN
925 fnd_msg_pub.count_and_get(
926 p_encoded => fnd_api.g_false
927 ,p_count => l_msg_count
928 ,p_data => l_msg_data
929 );
930 RAISE;
931 END Set_PriceList_Activity_Details;
932
933
934 ---------------------------------------------------------------------
935 -- PROCEDURE
936 -- Update_PriceList_Status
937 --
938 -- PURPOSE
939 -- This Procedure will update the status
940 --
941 -- IN
942 --
943 -- OUT
944 --
945 -- Used By Activities
946 --
947 -- NOTES
948 --
949 -- HISTORY
950 -- 08/20/2001 julou created
951 -------------------------------------------------------------------------------
952 PROCEDURE Update_PriceList_Status(
953 itemtype IN VARCHAR2
954 ,itemkey IN VARCHAR2
955 ,actid IN NUMBER
956 ,funcmode IN VARCHAR2
957 ,resultout OUT NOCOPY VARCHAR2
958 )
959 IS
960 l_api_version CONSTANT NUMBER := 1.0;
961 l_api_name CONSTANT VARCHAR2(30) := 'Update_PriceList_Status';
962 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
963 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
964 l_msg_count NUMBER;
965 l_msg_data VARCHAR2(4000);
966 l_error_msg VARCHAR2(4000);
967
968 l_status_code VARCHAR2(30);
969 l_next_status_id NUMBER;
970 l_approval_status VARCHAR2(12);
971 l_object_version_number NUMBER;
972 l_list_header_id NUMBER;
973
974 BEGIN
975 IF funcmode = 'RUN' THEN
976 l_approval_status := wf_engine.getitemattrtext(
977 itemtype => itemtype
978 ,itemkey => itemkey
979 ,aname => 'UPDATE_GEN_STATUS'
980 );
981
982 IF l_approval_status = 'APPROVED' THEN
983 l_next_status_id := wf_engine.getitemattrnumber(
984 itemtype => itemtype
985 ,itemkey => itemkey
986 ,aname => 'AMS_NEW_STAT_ID'
987 );
988 ELSIF l_approval_status = 'REJECTED' THEN
989 l_next_status_id := wf_engine.getitemattrnumber(
990 itemtype => itemtype
991 ,itemkey => itemkey
992 ,aname => 'AMS_REJECT_STAT_ID'
993 );
994 -- julou added 07/02/2002 for bug 2352621
995 -- if Workflow status is ERROR revert status of price list to original status
996 ELSE
997 l_next_status_id := wf_engine.GetItemAttrNumber(
998 itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'AMS_ORIG_STAT_ID' );
1001 END IF;
1002
1003 l_object_version_number := wf_engine.getitemattrnumber(
1004 itemtype => itemtype
1005 ,itemkey => itemkey
1006 ,aname => 'AMS_OBJECT_VERSION_NUMBER'
1007 );
1008 l_list_header_id := wf_engine.getitemattrnumber(
1009 itemtype => itemtype
1010 ,itemkey => itemkey
1011 ,aname => 'AMS_ACTIVITY_ID'
1012 );
1013
1014 l_status_code := ozf_utility_pvt.get_system_status_code(l_next_status_id);
1015
1016 ozf_utility_pvt.debug_message(l_full_name || ' ' || l_status_code || ' ' || l_approval_status);
1017
1018 UPDATE ozf_price_list_attributes
1019 SET user_status_id = l_next_status_id,
1020 status_code = l_status_code,
1021 status_date = SYSDATE,
1022 last_update_date = SYSDATE,
1023 last_updated_by = FND_GLOBAL.user_id,
1024 last_update_login = FND_GLOBAL.conc_login_id,
1025 object_version_number = object_version_number + 1
1026 WHERE qp_list_header_id = l_list_header_id;
1027
1028 -- bug 3835674 make price list active in QP only when approval is passed
1029 IF l_status_code = 'ACTIVE' THEN
1030 UPDATE qp_list_headers_b
1031 SET active_flag = 'Y',
1032 last_update_date = SYSDATE,
1033 last_updated_by = FND_GLOBAL.user_id,
1034 last_update_login = FND_GLOBAL.conc_login_id
1035 WHERE list_header_id = l_list_header_id;
1036 END IF;
1037 -- bug 3835674 end
1038
1039 --COMMIT;
1040
1041 resultout := 'COMPLETE:SUCCESS';
1042 RETURN;
1043 END IF;
1044
1045 -- CANCEL mode
1046 --
1047 IF (funcmode = 'CANCEL') THEN
1048 resultout := 'COMPLETE:';
1049 RETURN;
1050 END IF;
1051
1052 --
1053 -- TIMEOUT mode
1054 --
1055 IF (funcmode = 'TIMEOUT') THEN
1056 resultout := 'COMPLETE:';
1057 RETURN;
1058 END IF;
1059
1060 fnd_msg_pub.count_and_get(
1061 p_encoded => fnd_api.g_false
1062 ,p_count => l_msg_count
1063 ,p_data => l_msg_data
1064 );
1065
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 FND_MSG_PUB.Count_And_Get (
1069 p_encoded => FND_API.G_FALSE,
1070 p_count => l_msg_count,
1071 p_data => l_msg_data);
1072
1073 ams_gen_approval_pvt.Handle_Err(
1074 p_itemtype => itemtype ,
1075 p_itemkey => itemkey ,
1076 p_msg_count => l_msg_count, -- Number of error Messages
1077 p_msg_data => l_msg_data ,
1078 p_attr_name => 'AMS_ERROR_MSG',
1079 x_error_msg => l_error_msg);
1080
1081 wf_core.context('OZF_PRICELIST_APPROVAL_PVT',
1082 'UPDATE_PRICELIST_STATUS',
1083 itemtype, itemkey,to_char(actid),l_error_msg);
1084
1085 resultout := 'COMPLETE:ERROR';
1086 END Update_PriceList_Status;
1087
1088 END OZF_PriceList_Approval_PVT;