1 PACKAGE BODY po_notifications_sv1 AS
2 /* $Header: POXBWN1B.pls 115.3 2002/11/26 23:56:07 sbull ship $*/
3
4 DOC_OWNER NUMBER := 1;
5 DESCRIPTION NUMBER := 2;
6 AUTHORIZATION_STATUS_DISP NUMBER := 3;
7 OWNER_ID NUMBER := 20;
8 SEGMENT1 NUMBER := 21;
9 RELEASE_NUM NUMBER := 22;
10 AUTHORIZATION_STATUS NUMBER := 23;
11 ARRAY_FIRST_NULL NUMBER := 4;
12 ARRAY_LAST_NULL NUMBER := 19;
13 ARRAY_LB NUMBER := 1;
14 ARRAY_UB NUMBER := 23;
15
16 /*===========================================================================
17
18 PROCEDURE NAME: delete_po_notif
19
20 ===========================================================================*/
21
22 PROCEDURE delete_po_notif (x_document_type_code IN VARCHAR2,
23 x_object_id IN NUMBER) IS
24
25 x_progress VARCHAR2(3) := '';
26 x_release_id NUMBER;
27
28 CURSOR C is
29 SELECT po_release_id
30 FROM po_releases
31 WHERE po_header_id = x_object_id;
32
33 BEGIN
34
35 null;
36
37 /* Commenting out this whole procedure since it will no longer be used in R11
38
39 -- Delete all notifications from the fnd_notifications
40 -- table for this document.
41
42 x_progress := '010';
43 delete_notif_by_id_type(x_object_id, x_document_type_code);
44
45 -- Delete all notifications from the po_notifications table
46 -- for this document.
47
48 x_progress := '020';
49 po_notifications_sv2.delete_from_po_notif (x_document_type_code,
50 x_object_id);
51
52 -- If document is a blanket PO, delete notifications for
53 -- all releases against this blanket. Note: This
54 -- procedure must be called prior to deleting the blanket PO.
55 -- Otherwise, all the releases against that PO will automatically
56 -- be deleted along with the PO, and no data will be fetched into
57 -- cursor C.
58
59 IF x_document_type_code = 'BLANKET' THEN
60
61 x_progress := '020';
62 OPEN C;
63 LOOP
64
65 x_progress := '030';
66 FETCH C into x_release_id;
67 EXIT WHEN C%NOTFOUND;
68
69 -- Delete notifications from fnd_notifications.
70
71 x_progress := '040';
72 delete_notif_by_id_type(x_release_id, 'RELEASE');
73
74 -- Delete notifications from po_notifications
75
76 x_progress := '050';
77 po_notifications_sv2.delete_from_po_notif ('RELEASE',
78 x_release_id);
79
80 END LOOP;
81 CLOSE C;
82
83 END IF;
84 */
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 dbms_output.put_line('exception occurred in delete_po_notif ');
89 PO_MESSAGE_S.SQL_ERROR('DELETE_PO_NOTIF', x_progress, sqlcode);
90 RAISE;
91 END;
92
93 /*===========================================================================
94
95 PROCEDURE NAME: delete_notif_by_id_type
96
97 ===========================================================================*/
98
99 PROCEDURE delete_notif_by_id_type(x_object_id NUMBER,
100 x_doc_type VARCHAR2) IS
101 x_progress VARCHAR2(3) := '';
102 x_notification_id NUMBER;
103 x_return_code NUMBER;
104
105 -- Cursor will return notification ids for all notifications
106 -- for this document.
107
108 /* commenting out since it is no longer used in R11
109 CURSOR C IS
110 SELECT notification_id
111 FROM fnd_notifications
112 WHERE object_id = x_object_id
113 AND doc_type = x_doc_type; */
114
115 BEGIN
116 null;
117
118 /* commenting out since it is no longer used in R11
119 x_progress := '010';
120 OPEN C;
121 LOOP
122 x_progress := '020';
123 FETCH C into x_notification_id;
124 EXIT WHEN C%NOTFOUND;
125
126 dbms_output.put_line('before deleting notifications');
127
128 -- Call the procedure to delete notifications.
129
130 ntn.delete_notification(x_notification_id, x_return_code);
131
132 dbms_output.put_line('after deleting notifications');
133
134 END LOOP;
135 CLOSE C;
136 */
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 dbms_output.put_line('exception occurred in delete_notif_by_id_type');
141 PO_MESSAGE_S.SQL_ERROR('DELETE_NOTIF_BY_ID_TYPE', x_progress, sqlcode);
142 RAISE;
143 END;
144
145 /*===========================================================================
146
147 PROCEDURE NAME: send_po_notif
148
149 ===========================================================================*/
150
151 PROCEDURE send_po_notif(x_document_type_code IN VARCHAR2,
152 x_object_id IN NUMBER,
153 x_currency_code IN VARCHAR2 DEFAULT NULL,
154 x_start_date_active IN DATE DEFAULT NULL,
155 x_end_date_active IN DATE DEFAULT NULL,
156 x_forward_to_id IN NUMBER DEFAULT NULL,
157 x_forward_from_id IN NUMBER DEFAULT NULL,
158 x_note IN VARCHAR2 DEFAULT NULL) IS
159 x_message_name VARCHAR2(255) := '';
160 x_doc_num VARCHAR2(255) := '';
161 x_employee_id NUMBER := x_forward_to_id;
162 x_from_id NUMBER := x_forward_from_id;
163 x_sender_note VARCHAR2(300) := x_note;
164 x_start_date DATE;
165 x_forward_release_to NUMBER;
166 x_forward_release_from NUMBER := '';
167 x_release_currency_code VARCHAR2(25) := '';
168 x_doc_currency_code VARCHAR2(25) := x_currency_code;
169 x_release_note VARCHAR2(240) := '';
170 x_release_id NUMBER;
171 x_return_code NUMBER;
172 x_notification_id NUMBER;
173 x_progress VARCHAR2(3) := '';
174 --x_attribute_array ntn.char_array;
175 x_array_lb NUMBER;
176 x_array_up NUMBER;
177 x_doc_creation_date DATE;
178 x_expiration_date DATE;
179 x_close_date DATE;
180 x_acceptance_due_date DATE;
181
182 CURSOR C is
183 SELECT po_release_id
184 FROM po_releases
185 WHERE po_header_id = x_object_id
186 AND nvl(cancel_flag,'N') = 'N';
187 BEGIN
188
189 -- Delete all notifications for this document. If the
190 -- document is a blanket PO, notifications for all
191 -- releases against it will also be deleted.
192
193 null;
194
195 /* commenting out since it is no longer used in R11
196
197 x_progress := '010';
198
199 delete_po_notif(x_document_type_code, x_object_id);
200
201 IF (x_document_type_code = 'BLANKET') THEN
202
203 -- Insert new notification for releases against
204 -- this blanket.
205
206 x_progress := '020';
207 OPEN C;
208 LOOP
209
210 -- Fetch po_release_id for each release
211 -- against this blanket PO into cursor C.
212
213 x_progress := '030';
214 FETCH C into x_release_id;
215 EXIT WHEN C%NOTFOUND;
216
217 -- Get all the information required for inserting
218 -- the notification for the release into the
219 -- fnd_notifications table.
220
221 x_forward_release_to := NULL;
222 x_forward_release_from := NULL;
223 x_release_note := NULL;
224 x_start_date := NULL;
225
226 po_notifications_sv1.get_notif_data ('RELEASE',
227 x_release_id,
228 NULL, -- no end_date_active
229 x_start_date,
230 x_forward_release_to,
231 x_message_name,
232 x_doc_num,
233 x_doc_creation_date,
234 x_release_currency_code,
235 x_forward_release_from,
236 x_release_note,
237 x_expiration_date,
238 x_close_date,
239 x_acceptance_due_date,
240 x_attribute_array);
241
242 -- IF x_message_name is null then a notification is
243 -- not required.
244
245 IF x_message_name IS NOT NULL THEN
246
247 -- Send the notification for the release.
248
249 ntn.Send_Notification(
250 x_forward_release_to,
251 x_message_name,
252 x_release_id,
253 1, -- priority
254 'N', -- deletable
255 x_forward_release_from,
256 201,
257 'RELEASE',
258 x_doc_num,
259 NULL,
260 x_release_currency_code,
261 x_release_note,
262 x_start_date,
263 NULL,
264 x_doc_creation_date,
265 x_expiration_date,
266 x_close_date,
267 x_acceptance_due_date,
268 x_attribute_array,
269 ARRAY_LB,
270 ARRAY_UB,
271 x_return_code,
272 x_notification_id);
273
274 -- Insert the same notification into the po_notifications
275 -- table to keep the two tables in sync.
276
277
278 po_notifications_sv2.insert_into_po_notif (
279 x_forward_release_to,
280 x_message_name,
281 'RELEASE',
282 x_release_id,
283 x_doc_creation_date,
284 x_start_date,
285 NULL);
286
287 END IF;
288
289 END LOOP;
290 CLOSE C;
291
292 END IF;
293
294 -- Get the information needed for this notification.
295
296 x_start_date := x_start_date_active;
297 po_notifications_sv1.get_notif_data (x_document_type_code,
298 x_object_id,
299 x_end_date_active,
300 x_start_date,
301 x_employee_id,
302 x_message_name,
303 x_doc_num,
304 x_doc_creation_date,
305 x_doc_currency_code,
306 x_from_id,
307 x_sender_note,
308 x_expiration_date,
309 x_close_date,
310 x_acceptance_due_date,
311 x_attribute_array);
312
313 -- Check that a notification is required for this document.
314
315 IF x_message_name IS NOT NULL THEN
316
317 -- Send the notification.
318
319 ntn.Send_Notification(x_employee_id,
320 x_message_name,
321 x_object_id,
322 1, -- priority
323 'N', -- deletable
324 x_from_id,
325 201,
326 x_document_type_code,
327 x_doc_num,
328 NULL,
329 x_doc_currency_code,
330 x_sender_note,
331 x_start_date,
332 x_end_date_active,
333 x_doc_creation_date,
334 x_expiration_date,
335 x_close_date,
336 x_acceptance_due_date,
337 x_attribute_array,
338 ARRAY_LB,
339 ARRAY_UB,
340 x_return_code,
341 x_notification_id);
342
343 -- Insert the same notification into the po_notification table
344 -- to keep the two tables in sync.
345
346 po_notifications_sv2.insert_into_po_notif (
347 x_employee_id,
348 x_message_name,
349 x_document_type_code,
350 x_object_id,
351 x_doc_creation_date,
352 x_start_date,
353 x_end_date_active);
354
355 END IF; */
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 dbms_output.put_line('Exception occurred in send_po_notif');
360 PO_MESSAGE_S.SQL_ERROR('SEND_PO_NOTIFICATION', x_progress, sqlcode);
361 RAISE;
362 END;
363
364 /*===========================================================================
365
366 PROCEDURE NAME: get_notif_data
367
368 ===========================================================================*/
369
370 PROCEDURE get_notif_data (x_document_type_code IN VARCHAR2,
371 x_object_id IN NUMBER,
372 x_end_date_active IN DATE,
373 x_start_date_active IN OUT NOCOPY DATE,
374 x_employee_id IN OUT NOCOPY NUMBER,
375 x_message_name IN OUT NOCOPY VARCHAR2,
376 x_doc_num IN OUT NOCOPY VARCHAR2,
377 x_doc_creation_date IN OUT NOCOPY DATE,
378 x_currency_code IN OUT NOCOPY VARCHAR2,
379 x_from_id IN OUT NOCOPY NUMBER,
380 x_note IN OUT NOCOPY VARCHAR2,
381 x_expiration_date IN OUT NOCOPY DATE,
382 x_close_date IN OUT NOCOPY DATE,
383 x_acceptance_due_date IN OUT NOCOPY DATE) IS
384
385 x_progress VARCHAR2(3) := '';
386 x_counter NUMBER;
387 x_type VARCHAR2(25) := '';
388 x_subtype VARCHAR2(25) := '';
389 x_doc_owner_id NUMBER;
390 x_forward_to_id NUMBER := x_employee_id;
391 x_code VARCHAR2(1) := '';
392
393 BEGIN
394
395 x_progress := '010';
396
397 /* Obsolete in R11
398
399 -- Get the necessary data for inserting a notification into the
400 -- fnd_notifications table. Message name is as follows:
401
402 -- If document is on hold, insert an 'ON_HOLD' notification.
403 -- If document is APPROVED, insert an 'ACCEPTANCE_PAST_DUE'
404 -- notification if acceptance is required.
405 -- For other document statuses, insert the following types
406 -- of notifications:
407
408 -- Document Status Message Name
409 -- --------------- -------------
410 -- REQUIRES REAPPROVAL REQUIRES_REAPPROVAL
414 -- NEVER APPROVED NEVER_APPROVED
411 -- REJECTED REJECTED_BY_APPROVER
412 -- PRE-APPROVED AWAITING_YOUR_APPROVAL
413 -- IN PROCESS AWAITING_YOUR_APPROVAL
415 -- RETURNED REJECTED_BY_PURCHASING
416
417 -- If no notification is required for this document, message_name
418 -- will be returned with NULL value.
419
420 BEGIN
421
422 IF x_document_type_code IN ('RELEASE', 'SCHEDULED') THEN
423
424 x_progress := '020';
425
426 SELECT DECODE(x_forward_to_id,
427 NULL, pr.agent_id,
428 x_forward_to_id),
429 pr.agent_id,
430 ph.comments,
431 ph.segment1,
432 pr.release_num,
433 pr.creation_date,
434 pr.agent_id,
435 he.full_name,
436 nvl(pr.authorization_status, 'INCOMPLETE'),
437 ph.currency_code,
438 DECODE(pr.acceptance_required_flag,
439 'Y', pr.acceptance_due_date, NULL),
440 NULL,
441 NULL,
442 DECODE(PR.HOLD_FLAG,
443 'Y', 'ON_HOLD',
444 DECODE(PR.AUTHORIZATION_STATUS,
445 'APPROVED', DECODE(pr.acceptance_required_flag,
446 'Y', 'ACCEPTANCE_PAST_DUE',
447 NULL),
448 'REQUIRES REAPPROVAL', 'REQUIRES_REAPPROVAL',
449 'REJECTED', 'REJECTED_BY_APPROVER',
450 'PRE-APPROVED', 'AWAITING_YOUR_APPROVAL',
451 'IN PROCESS', 'AWAITING_YOUR_APPROVAL',
452 'NEVER_APPROVED'))
453 INTO x_forward_to_id,
454 x_doc_owner_id,
455 x_attribute_array(DESCRIPTION),
456 x_attribute_array(SEGMENT1),
457 x_attribute_array(RELEASE_NUM),
458 x_doc_creation_date,
459 x_attribute_array(OWNER_ID),
460 x_attribute_array(DOC_OWNER),
461 x_attribute_array(AUTHORIZATION_STATUS),
462 x_currency_code,
463 x_acceptance_due_date,
464 x_expiration_date,
465 x_close_date,
466 x_message_name
467 FROM PO_RELEASES PR,
468 PO_HEADERS PH,
469 HR_EMPLOYEES HE
470 WHERE NVL(PR.CANCEL_FLAG,'N') = 'N'
471 AND pr.po_release_id = x_object_id
472 AND pr.po_header_id = ph.po_header_id
473 AND pr.agent_id = he.employee_id;
474
475 ELSIF x_document_type_code IN ('STANDARD', 'PLANNED', 'BLANKET',
476 'CONTRACT', 'RFQ', 'QUOTATION') THEN
477
478 x_progress := '030';
479
480 SELECT DECODE(x_forward_to_id,
481 NULL, ph.agent_id,
482 x_forward_to_id),
483 ph.agent_id,
484 ph.comments,
485 ph.segment1,
486 NULL,
487 ph.creation_date,
488 ph.agent_id,
489 he.full_name,
490 nvl(ph.authorization_status, 'INCOMPLETE'),
491 ph.currency_code,
492 DECODE(ph.acceptance_required_flag,
493 'Y', ph.acceptance_due_date, NULL),
494 DECODE(x_document_type_code,
495 'QUOTATION', ph.end_date,
496 NULL),
497 DECODE(x_document_type_code,
498 'RFQ', ph.rfq_close_date,
499 NULL),
500 DECODE(x_document_type_code,
501 'RFQ',DECODE(ph.STATUS_LOOKUP_CODE,
502 'I','REQUIRES_COMPLETION',
503 DECODE(x_end_date_active,
504 NULL,'AWAITING_REPLIES',
505 'NEAR_CLOSE')),
506 'QUOTATION',DECODE(ph.STATUS_LOOKUP_CODE,
507 'I','REQUIRES_COMPLETION',
508 DECODE(x_end_date_active,
509 NULL,'ACTIVE',
510 'NEAR_EXPIRATION')),
511 DECODE(ph.USER_HOLD_FLAG,
512 'Y', 'ON_HOLD',
513 DECODE(PH.AUTHORIZATION_STATUS,
514 'APPROVED', DECODE(ph.acceptance_required_flag,
515 'Y', 'ACCEPTANCE_PAST_DUE',
516 NULL),
517 'REQUIRES REAPPROVAL', 'REQUIRES_REAPPROVAL',
518 'REJECTED', 'REJECTED_BY_APPROVER',
519 'IN PROCESS', 'AWAITING_YOUR_APPROVAL',
520 'PRE-APPROVED', 'AWAITING_YOUR_APPROVAL',
521 'NEVER_APPROVED')))
522 INTO x_forward_to_id,
523 x_doc_owner_id,
524 x_attribute_array(DESCRIPTION),
525 x_attribute_array(SEGMENT1),
526 x_attribute_array(RELEASE_NUM),
527 x_doc_creation_date,
528 x_attribute_array(OWNER_ID),
529 x_attribute_array(DOC_OWNER),
530 x_attribute_array(AUTHORIZATION_STATUS),
531 x_currency_code,
532 x_acceptance_due_date,
533 x_expiration_date,
534 x_close_date,
535 x_message_name
536 FROM PO_HEADERS ph,
537 hr_employees he
538 WHERE NVL(ph.CANCEL_FLAG,'N') != 'Y'
539 AND NVL(ph.STATUS_LOOKUP_CODE, 'I') != 'C'
540 AND po_header_id = x_object_id
541 AND he.employee_id = ph.agent_id;
542
543 ELSIF x_document_type_code IN ('INTERNAL', 'PURCHASE') THEN
544
545 x_progress := '040';
546
547 SELECT DECODE(x_forward_to_id,
548 NULL, prh.preparer_id,
549 x_forward_to_id),
550 prh.preparer_id,
551 prh.description,
552 prh.segment1,
553 NULL,
554 prh.creation_date,
555 prh.preparer_id,
556 he.full_name,
557 nvl(prh.authorization_status, 'INCOMPLETE'),
558 NULL,
559 NULL,
560 NULL,
561 DECODE(prh.authorization_status,
562 'REJECTED', 'REJECTED_BY_APPROVER',
563 'RETURNED', 'REJECTED_BY_PURCHASING',
564 'APPROVED', NULL,
565 'IN PROCESS', 'AWAITING_YOUR_APPROVAL',
566 'PRE-APPROVED', 'AWAITING_YOUR_APPROVAL',
567 'NEVER_APPROVED')
568 INTO x_forward_to_id,
572 x_attribute_array(RELEASE_NUM),
569 x_doc_owner_id,
570 x_attribute_array(DESCRIPTION),
571 x_attribute_array(SEGMENT1),
573 x_doc_creation_date,
574 x_attribute_array(OWNER_ID),
575 x_attribute_array(DOC_OWNER),
576 x_attribute_array(AUTHORIZATION_STATUS),
577 x_acceptance_due_date,
578 x_expiration_date,
579 x_close_date,
580 x_message_name
581 FROM po_requisition_headers prh,
582 hr_employees he
583 WHERE NVL(CANCEL_FLAG,'N') = 'N'
584 AND requisition_header_id = x_object_id
585 AND he.employee_id = prh.preparer_id;
586
587 IF (x_currency_code IS NULL) THEN
588
589 -- get the currency code
590
591 x_currency_code := po_core_s2.get_base_currency;
592
593 END IF;
594
595 ELSE
596 x_progress := '070';
597 PO_MESSAGE_S.SQL_ERROR('GET_DOCUMENT_INFO', x_progress, sqlcode);
598 END IF;
599
600 EXCEPTION
601 WHEN OTHERS THEN
602 dbms_output.put_line('Exception occurred when selecting from header table');
603 dbms_output.put_line('Document is '||x_document_type_code||' '||TO_CHAR(x_object_id));
604 RAISE;
605 END;
606
607 IF x_message_name IS NOT NULL THEN
608
609 dbms_output.put_line('Message name is '||x_message_name);
610
611 -- Determine the type and subtype of the document based on
612 -- doc_type from the fnd_notifications table.
613
614 dbms_output.put_line('before call to get_doc_type_subtype');
615 po_notifications_sv2.get_doc_type_subtype(x_document_type_code,
616 x_type,
617 x_subtype);
618
619 -- IF x_from_id does not have a value, get the id of the sender.
620
621 IF x_from_id IS NULL THEN
622
623 IF x_message_name IN ('AWAITING_YOUR_APPROVAL', 'REJECTED_BY_APPROVER',
624 'REJECTED_BY_PURCHASING') THEN
625
626 -- Need to get sender and note from po_action_history. Sender is the
627 -- employee that approved, rejected or returned document. This
628 -- should only be necessary for release notifications (when the PO or PA becomes
629 -- unapproved and the notification for the release is updated) and
630 -- for notifications that are transfered from po_notifications.
631
632 dbms_output.put_line('before select from po_action_history');
633
634 BEGIN
635
636 SELECT poa.employee_id,
637 poa.note
638 INTO x_from_id,
639 x_note
640 FROM po_action_history poa
641 WHERE poa.object_type_code = x_type
642 AND poa.object_id = x_object_id
643 AND poa.sequence_num = (SELECT max(sequence_num)
644 FROM po_action_history pv
645 WHERE pv.object_type_code = poa.object_type_code
646 AND pv.object_id = poa.object_id
647 AND pv.action_code IN ('FORWARD',
648 'SUBMIT', 'REJECT', 'RETURN','APPROVE'));
649
650 EXCEPTION
651 WHEN OTHERS THEN
652
653 -- Bug 412292: For reqimport, insert into po_action_history
654 -- occurs after insert into po_notifications, so we cannot get
655 -- the forward from person from po_action_history.
656
657 x_from_id := x_doc_owner_id;
658 x_note := '';
659
660 END;
661
662 ELSE
663
664 -- For all other documents, sender is document owner.
665
666 x_from_id := x_doc_owner_id;
667 x_note := '';
668
669 END IF;
670 END IF;
671
672 dbms_output.put_line('Forward-from id = '||x_from_id);
673
674 IF (x_message_name = 'AWAITING_YOUR_APPROVAL' and x_employee_id IS NULL) THEN
675
676 -- Find the id of the recipient from po_action_history. This should only
677 -- be necessary for release notifications (when the base PO or PA becomes
678 -- unapproved and the notification for the release is updated).
679
680 BEGIN
681
682 SELECT employee_id
683 INTO x_forward_to_id
684 FROM po_action_history
685 WHERE object_type_code = x_type
686 AND object_id = x_object_id
687 AND action_code IS NULL;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 dbms_output.put_line('Cannot get employee_id from po_action_history');
692 RAISE;
693 END;
694
695 END IF;
696
697 -- Copy value from local variable into OUT variable.
698
699 x_employee_id := x_forward_to_id;
700 dbms_output.put_line('Forward-to id = '||x_employee_id);
701
702 -- Document number is segment1-release_num
703
704 IF x_attribute_array(RELEASE_NUM) IS NULL THEN
705 x_doc_num := x_attribute_array(SEGMENT1);
706 ELSE
707 x_doc_num := x_attribute_array(SEGMENT1)||'-'||x_attribute_array(RELEASE_NUM);
708 END IF;
709
710 -- Determine the displayed value for document authorization_status
711
712 dbms_output.put_line('before select from po_lookup_codes');
713
714 BEGIN
715
716 SELECT displayed_field
717 INTO x_attribute_array(AUTHORIZATION_STATUS_DISP)
718 FROM po_lookup_codes
719 WHERE lookup_type = 'AUTHORIZATION STATUS'
720 AND lookup_code = x_attribute_array(AUTHORIZATION_STATUS);
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 dbms_output.put_line('cannot get displayed value for authorization status');
725 x_attribute_array(AUTHORIZATION_STATUS_DISP) := NULL;
726 END;
727
728 IF x_message_name = 'ACCEPTANCE_PAST_DUE' THEN
729
733 x_start_date_active := x_acceptance_due_date;
730 -- If ACCEPTANCE_PAST_DUE notifications, the start
731 -- effective date is the acceptance due date.
732
734
735 ELSIF x_message_name IN ('AWAITING_REPLIES', 'REQUIRES_COMPLETION',
736 'ACTIVE') THEN
737
738 -- Clear the start_effective_date for these types of
739 -- notifications. This step is needed to fix a Release 10
740 -- bug that assigns the wrong start effective date to these
741 -- types of notifications.
742
743 x_start_date_active := NULL;
744
745 END IF;
746
747
748 -- Elements 1 through 19 in x_attribute_array contain
749 -- data that is displayed in the notification. Elements 20 and
750 -- above contain hidden values. Since we are using only
751 -- elements 1 through ARRAY_FIRST_NULL-1 and elements 20
752 -- and above, we need to populate elements FIRST_NULL
753 -- through 19 with NULL values.
754
755 FOR x_counter IN ARRAY_FIRST_NULL..ARRAY_LAST_NULL LOOP
756 x_attribute_array(x_counter) := NULL;
757 END LOOP;
758 END IF;
759 */
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 dbms_output.put_line('In Exception');
764 PO_MESSAGE_S.SQL_ERROR('GET_NOTIF_DATA', x_progress, sqlcode);
765 RAISE;
766 END;
767
768 END po_notifications_sv1;