[Home] [Help]
1: PACKAGE BODY PO_APPROVAL_REMINDER_SV AS
2: /* $Header: POXWARMB.pls 120.14.12020000.3 2013/02/10 21:21:21 vegajula ship $*/
3:
4: -- Read the profile option that enables/disables the debug log
5: g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6: g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7:
8: -- Item Type for the Reminder Workflow:
9: g_reminder_item_type CONSTANT VARCHAR2(20) := 'APVRMDER';
10: g_pkg_name CONSTANT VARCHAR2(30) := 'PO_APPROVAL_REMINDER_SV';
11: g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
12:
13: /*===========================================================================
14: PROCEDURE NAME: Select_Unapprove_docs
31:
32: BEGIN
33:
34:
35: l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 01 - BEGIN ';
36: IF (g_po_wf_debug = 'Y') THEN
37: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
38: END IF;
39:
51: Process_rfq_quote;
52:
53: process_po_temp_labor_lines; --
54:
55: l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 900 - END ';
56: IF (g_po_wf_debug = 'Y') THEN
57: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
58: END IF;
59:
61: EXCEPTION
62:
63: WHEN OTHERS THEN
64:
65: wf_core.context ('PO_APPROVAL_REMINDER_SV','Select_Unapprove_docs ' || l_progress);
66: l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 990 - ' ||
67: 'EXCEPTION sql error: ' || sqlcode;
68: IF (g_po_wf_debug = 'Y') THEN
69: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
62:
63: WHEN OTHERS THEN
64:
65: wf_core.context ('PO_APPROVAL_REMINDER_SV','Select_Unapprove_docs ' || l_progress);
66: l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 990 - ' ||
67: 'EXCEPTION sql error: ' || sqlcode;
68: IF (g_po_wf_debug = 'Y') THEN
69: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
70: END IF;
134:
135: BEGIN
136:
137:
138: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 01 - BEGIN ';
139: IF (g_po_wf_debug = 'Y') THEN
140: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
141: END IF;
142:
166:
167:
168: IF Unapprove_req%FOUND THEN
169:
170: PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
171: l_ItemKey,
172: l_Item_exist,
173: l_Item_end_date);
174:
180: -- Bypass this one
181:
182: NULL;
183:
184: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_reqs: 05 ' ||
185: 'open WF item key exists ' ||l_itemkey;
186: -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
187:
188: ELSE
206: --
207: -- Invoke the Start_Approval_Reminder workflow
208: -- for every unique workflow Item key.
209:
210: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_reqs: 10 ' ||
211: 'Start WF item key =' ||l_itemkey;
212: IF (g_po_wf_debug = 'Y') THEN
213: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
214: END IF;
212: IF (g_po_wf_debug = 'Y') THEN
213: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
214: END IF;
215:
216: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
217: l_doc_number,
218: l_doc_type,
219: l_doc_subtype,
220: l_release_num,
237:
238: CLOSE Unapprove_req;
239:
240:
241: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 900 - END ';
242: IF (g_po_wf_debug = 'Y') THEN
243: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
244: END IF;
245:
245:
246:
247: EXCEPTION
248: WHEN NO_DATA_FOUND THEN
249: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_reqs','No data found');
250: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 901 - ' ||
251: 'EXCEPTION - no data found sql error: ' || sqlcode;
252: IF (g_po_wf_debug = 'Y') THEN
253: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
246:
247: EXCEPTION
248: WHEN NO_DATA_FOUND THEN
249: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_reqs','No data found');
250: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 901 - ' ||
251: 'EXCEPTION - no data found sql error: ' || sqlcode;
252: IF (g_po_wf_debug = 'Y') THEN
253: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
254: END IF;
254: END IF;
255:
256:
257: WHEN OTHERS THEN
258: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_reqs','SQL error ' || sqlcode);
259: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 990 - ' ||
260: 'EXCEPTION - sql error: ' || sqlcode;
261: IF (g_po_wf_debug = 'Y') THEN
262: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
255:
256:
257: WHEN OTHERS THEN
258: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_reqs','SQL error ' || sqlcode);
259: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 990 - ' ||
260: 'EXCEPTION - sql error: ' || sqlcode;
261: IF (g_po_wf_debug = 'Y') THEN
262: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
263: END IF;
317:
318: BEGIN
319:
320:
321: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 01 - BEGIN ';
322: IF (g_po_wf_debug = 'Y') THEN
323: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
324: END IF;
325:
356:
357: IF Unapprove_PO%FOUND THEN
358:
359:
360: PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
361: l_ItemKey,
362: l_Item_exist,
363: l_Item_end_date);
364:
370: -- Bypass this one
371:
372: NULL;
373:
374: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_pos: 05 ' ||
375: 'open WF item key exists ' ||l_itemkey;
376: -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
377:
378: ELSE
395: --
396: -- Invoke the Start_Approval_Reminder workflow
397: -- for every unique workflow Item key.
398:
399: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_pos: 10 ' ||
400: 'Start WF item key =' ||l_itemkey;
401: IF (g_po_wf_debug = 'Y') THEN
402: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
403: END IF;
401: IF (g_po_wf_debug = 'Y') THEN
402: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
403: END IF;
404:
405: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
406: l_doc_number,
407: l_doc_type,
408: l_doc_subtype,
409: l_release_num,
427:
428: CLOSE Unapprove_PO;
429:
430:
431: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 900 - END ';
432: IF (g_po_wf_debug = 'Y') THEN
433: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
434: END IF;
435:
434: END IF;
435:
436: EXCEPTION
437: WHEN NO_DATA_FOUND THEN
438: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_pos','No data found');
439:
440: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 901 - ' ||
441: 'EXCEPTION - no data found sql error: ' || sqlcode;
442: IF (g_po_wf_debug = 'Y') THEN
436: EXCEPTION
437: WHEN NO_DATA_FOUND THEN
438: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_pos','No data found');
439:
440: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 901 - ' ||
441: 'EXCEPTION - no data found sql error: ' || sqlcode;
442: IF (g_po_wf_debug = 'Y') THEN
443: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
444: END IF;
443: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
444: END IF;
445:
446: WHEN OTHERS THEN
447: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_pos','SQL error ' || sqlcode);
448:
449: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 990 - ' ||
450: 'EXCEPTION - sql error: ' || sqlcode;
451: IF (g_po_wf_debug = 'Y') THEN
445:
446: WHEN OTHERS THEN
447: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_pos','SQL error ' || sqlcode);
448:
449: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 990 - ' ||
450: 'EXCEPTION - sql error: ' || sqlcode;
451: IF (g_po_wf_debug = 'Y') THEN
452: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
453: END IF;
510:
511: BEGIN
512:
513:
514: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_releases: 01 - BEGIN ';
515: IF (g_po_wf_debug = 'Y') THEN
516: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
517: END IF;
518:
548:
549: IF Unapprove_REL%FOUND THEN
550:
551:
552: PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
553: l_ItemKey,
554: l_Item_exist,
555: l_Item_end_date);
556:
562: -- Bypass this one
563:
564: NULL;
565:
566: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_releases: 05 ' ||
567: 'open WF item key exists ' ||l_itemkey;
568: -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
569:
570:
589: --
590: -- Invoke the Start_Approval_Reminder workflow
591: -- for every unique workflow Item key.
592:
593: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_releases: 10 ' ||
594: 'Start WF item key =' ||l_itemkey;
595: IF (g_po_wf_debug = 'Y') THEN
596: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
597: END IF;
595: IF (g_po_wf_debug = 'Y') THEN
596: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
597: END IF;
598:
599: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
600: l_doc_number,
601: l_doc_type,
602: l_doc_subtype,
603: l_release_num,
619: END LOOP;
620:
621: CLOSE Unapprove_REL;
622:
623: l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_releases: 900 - END ';
624: IF (g_po_wf_debug = 'Y') THEN
625: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
626: END IF;
627:
626: END IF;
627:
628: EXCEPTION
629: WHEN NO_DATA_FOUND THEN
630: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_releases','No data found');
631: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_Unapprove_releases: 901 - ' ||
632: 'EXCEPTION - no data found sql error: ' || sqlcode;
633: IF (g_po_wf_debug = 'Y') THEN
634: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
627:
628: EXCEPTION
629: WHEN NO_DATA_FOUND THEN
630: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_releases','No data found');
631: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_Unapprove_releases: 901 - ' ||
632: 'EXCEPTION - no data found sql error: ' || sqlcode;
633: IF (g_po_wf_debug = 'Y') THEN
634: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
635: END IF;
635: END IF;
636:
637:
638: WHEN OTHERS THEN
639: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_releases','SQL error ' || sqlcode);
640: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_Unapprove_releases: 990 - ' ||
641: 'EXCEPTION - sql error: ' || sqlcode;
642: IF (g_po_wf_debug = 'Y') THEN
643: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
636:
637:
638: WHEN OTHERS THEN
639: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_releases','SQL error ' || sqlcode);
640: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_Unapprove_releases: 990 - ' ||
641: 'EXCEPTION - sql error: ' || sqlcode;
642: IF (g_po_wf_debug = 'Y') THEN
643: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
644: END IF;
712:
713: BEGIN
714:
715:
716: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 01 - BEGIN ';
717: IF (g_po_wf_debug = 'Y') THEN
718: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
719: END IF;
720:
754: */
755: IF PO_acceptance%FOUND THEN
756:
757:
758: PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
759: l_ItemKey,
760: l_Item_exist,
761: l_Item_end_date);
762:
767: -- to_date(to_char(SYSDATE,'DD-MON-YYYY'),'DD/MM/YYYY') THEN
768: -- bug: 1076985
769:
770: -- bug 3896181
771: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 02 - item key exists ';
772: IF (g_po_wf_debug = 'Y') THEN
773: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
774: end if;
775: -- bug 3896181
776:
777: IF trunc(l_acceptance_due_date) > trunc(sysdate) THEN
778:
779: -- bug 3896181
780: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 03 - acc_due_date > sysdate ';
781: IF (g_po_wf_debug = 'Y') THEN
782: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
783: END IF;
784: IF l_Item_end_date is not null then
781: IF (g_po_wf_debug = 'Y') THEN
782: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
783: END IF;
784: IF l_Item_end_date is not null then
785: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 04 - end_date not null';
786: IF (g_po_wf_debug = 'Y') THEN
787: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
788: END IF;
789: -- bug 3896181
786: IF (g_po_wf_debug = 'Y') THEN
787: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
788: END IF;
789: -- bug 3896181
790: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
791: l_doc_header_id,
792: NULL);
793: -- bug 3896181
794:
796: l_itemkey,
797: SYSDATE,
798: true,
799: true);
800: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
801: l_doc_number,
802: l_doc_type,
803: l_doc_subtype,
804: l_release_num,
805: l_agent_id,
806: l_itemkey);
807: COMMIT;
808: ELSE -- l_Item_end_date is not null
809: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 05 - end_date is null';
810: IF (g_po_wf_debug = 'Y') THEN
811: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
812: -- bug 3896181
813: END IF;
819: where wias.notification_id = wfn.group_id
820: and wias.item_type = 'APVRMDER'
821: and wias.item_key = l_ItemKey ;
822: IF l_message_name = 'PO_ACCEPTANCE_PAST_DUE' then
823: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 06 - PO_ACCEPTANCE_PAST_DUE';
824: -- bug 3896181
825: IF (g_po_wf_debug = 'Y') THEN
826: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
827: END IF;
825: IF (g_po_wf_debug = 'Y') THEN
826: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
827: END IF;
828: -- bug 3896181
829: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
830: l_doc_header_id,
831: NULL);
832: -- bug 3896181
833: --
839: docommit => true, --
840: force => true); --
841:
842: -- bug 3896181
843: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
844: l_doc_number,
845: l_doc_type,
846: l_doc_subtype,
847: l_release_num,
850: COMMIT;
851: END IF; -- l_message_name = 'PO_ACCEPTANCE_PAST_DUE'
852: END IF; -- l_Item_end_date is not null
853: ELSE -- l_acceptance_due_date) > trunc(sysdate)
854: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 07 - acc_due_date < sysdate';
855: -- bug 3896181
856: IF (g_po_wf_debug = 'Y') THEN
857: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
858: END IF;
858: END IF;
859:
860: -- bug 3896181
861: IF l_Item_end_date is not NULL THEN
862: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 08 - end_date is not null';
863: IF (g_po_wf_debug = 'Y') THEN
864: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
865: END IF;
866: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
862: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 08 - end_date is not null';
863: IF (g_po_wf_debug = 'Y') THEN
864: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
865: END IF;
866: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
867: l_doc_header_id,
868: NULL);
869: WF_PURGE.ITEMS (l_ItemType,
870: l_itemkey,
872: true,
873: true);
874: -- bug 3896181
875:
876: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
877: l_doc_number,
878: l_doc_type,
879: l_doc_subtype,
880: l_release_num,
887:
888: COMMIT;
889: -- bug 3896181
890: ELSE -- l_Item_end_date is not NULL
891: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 09 - end_date is null';
892: IF (g_po_wf_debug = 'Y') THEN
893: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
894: -- bug 3896181
895: END IF;
917: END IF;
918: END; --End 14019376
919:
920: IF l_message_name = 'PO_ACCEPTANCE_REQUIRED' then
921: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 10 - PO_ACCEPTANCE_REQUIRED';
922: IF (g_po_wf_debug = 'Y') THEN
923: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
924: END IF;
925: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
921: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 10 - PO_ACCEPTANCE_REQUIRED';
922: IF (g_po_wf_debug = 'Y') THEN
923: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
924: END IF;
925: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
926: l_doc_header_id,
927: NULL);
928: WF_PURGE.ITEMS (l_ItemType,
929: l_itemkey,
931: true,
932: true);
933: -- bug 3896181
934:
935: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
936: l_doc_number,
937: l_doc_type,
938: l_doc_subtype,
939: l_release_num,
944: END IF; -- l_message_name = 'PO_ACCEPTANCE_REQUIRED'
945: END IF; -- l_Item_end_date is not NULL
946: END IF; -- l_acceptance_due_date) > trunc(sysdate)
947: ELSE -- l_item_exist = 'Y'
948: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 11 - l_item_exists is N';
949: -- bug 3896181
950: IF (g_po_wf_debug = 'Y') THEN
951: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
952: END IF;
950: IF (g_po_wf_debug = 'Y') THEN
951: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
952: END IF;
953: -- bug 3896181
954: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
955: l_doc_number,
956: l_doc_type,
957: l_doc_subtype,
958: l_release_num,
974:
975: CLOSE PO_acceptance;
976:
977:
978: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 900 - END ';
979: IF (g_po_wf_debug = 'Y') THEN
980: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
981: END IF;
982:
981: END IF;
982:
983: EXCEPTION
984: WHEN NO_DATA_FOUND THEN
985: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_po_acceptance','No data found');
986: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 901 - ' ||
987: 'EXCEPTION - no data found sql error: ' || sqlcode;
988: IF (g_po_wf_debug = 'Y') THEN
989: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
982:
983: EXCEPTION
984: WHEN NO_DATA_FOUND THEN
985: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_po_acceptance','No data found');
986: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 901 - ' ||
987: 'EXCEPTION - no data found sql error: ' || sqlcode;
988: IF (g_po_wf_debug = 'Y') THEN
989: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
990: END IF;
989: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
990: END IF;
991:
992: WHEN OTHERS THEN
993: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_po_acceptance','SQL error ' || sqlcode);
994: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 990 - ' ||
995: 'EXCEPTION - sql error: ' || sqlcode;
996: IF (g_po_wf_debug = 'Y') THEN
997: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
990: END IF;
991:
992: WHEN OTHERS THEN
993: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_po_acceptance','SQL error ' || sqlcode);
994: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 990 - ' ||
995: 'EXCEPTION - sql error: ' || sqlcode;
996: IF (g_po_wf_debug = 'Y') THEN
997: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
998: END IF;
1071:
1072: l_message_name VARCHAR2(300) := NULL; --bug 3896181
1073: BEGIN
1074:
1075: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 01 - BEGIN ';
1076: IF (g_po_wf_debug = 'Y') THEN
1077: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1078: END IF;
1079:
1120: IF REL_acceptance%FOUND THEN
1121:
1122:
1123: -- Call Workflow to check if the itemkey already exists
1124: PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
1125: l_ItemKey,
1126: l_Item_exist,
1127: l_Item_end_date);
1128:
1128:
1129: IF l_item_exist = 'Y' THEN
1130:
1131: --bug 3896181
1132: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 02 - item key exists ';
1133: IF (g_po_wf_debug = 'Y') THEN
1134: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1135: END IF;
1136: IF trunc(l_acceptance_due_date) > trunc(sysdate) THEN
1133: IF (g_po_wf_debug = 'Y') THEN
1134: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1135: END IF;
1136: IF trunc(l_acceptance_due_date) > trunc(sysdate) THEN
1137: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 03 - acc_due_date > sysdate ';
1138: IF (g_po_wf_debug = 'Y') THEN
1139: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1140: END IF;
1141: IF l_Item_end_date is not null then
1138: IF (g_po_wf_debug = 'Y') THEN
1139: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1140: END IF;
1141: IF l_Item_end_date is not null then
1142: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 04 - end_date not null';
1143: IF (g_po_wf_debug = 'Y') THEN
1144: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1145: END IF;
1146: --bug 3896181
1144: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1145: END IF;
1146: --bug 3896181
1147:
1148: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1149: l_doc_header_id,
1150: NULL);
1151: --bug 3896181
1152: WF_PURGE.ITEMS (l_ItemType,
1153: l_itemkey,
1154: SYSDATE,
1155: true,
1156: true);
1157: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1158: l_doc_number,
1159: l_doc_type,
1160: l_doc_subtype,
1161: l_release_num,
1162: l_agent_id,
1163: l_itemkey);
1164: COMMIT;
1165: ELSE -- l_Item_end_date is not null
1166: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 05 - end_date is null';
1167: IF (g_po_wf_debug = 'Y') THEN
1168: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1169: --bug 3896181
1170: END IF;
1181: when others then
1182: NULL;
1183: end ;
1184: IF l_message_name = 'REL_ACCEPTANCE_PAST_DUE' then
1185: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 06 - REL_ACCEPTANCE_PAST_DUE';
1186: --bug 3896181
1187: IF (g_po_wf_debug = 'Y') THEN
1188: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1189: END IF;
1187: IF (g_po_wf_debug = 'Y') THEN
1188: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1189: END IF;
1190: --bug 3896181
1191: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1192: l_doc_header_id,
1193: NULL);
1194: --bug 3896181
1195:
1201: docommit => true, --
1202: force => true); --
1203:
1204: --bug 3896181
1205: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1206: l_doc_number,
1207: l_doc_type,
1208: l_doc_subtype,
1209: l_release_num,
1212: COMMIT;
1213: END IF; -- l_message_name = 'REL_ACCEPTANCE_PAST_DUE'
1214: END IF; -- l_Item_end_date is not null
1215: ELSE -- trunc(l_acceptance_due_date) > trunc(sysdate)
1216: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 07 - acc_due_date < sysdate';
1217: --bug 3896181
1218: IF (g_po_wf_debug = 'Y') THEN
1219: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1220: END IF;
1220: END IF;
1221:
1222: --bug 3896181
1223: IF l_Item_end_date is not NULL THEN
1224: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 08 - end_date is not null';
1225: IF (g_po_wf_debug = 'Y') THEN
1226: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1227: END IF;
1228: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1224: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 08 - end_date is not null';
1225: IF (g_po_wf_debug = 'Y') THEN
1226: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1227: END IF;
1228: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1229: l_doc_header_id,
1230: NULL);
1231: WF_PURGE.ITEMS (l_ItemType,
1232: l_itemkey,
1235: true);
1236:
1237: --bug 3896181
1238:
1239: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1240: l_doc_number,
1241: l_doc_type,
1242: l_doc_subtype,
1243: l_release_num,
1249:
1250: COMMIT;
1251: --bug 3896181
1252: ELSE -- l_Item_end_date is not NULL
1253: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 09 - end_date is null';
1254: IF (g_po_wf_debug = 'Y') THEN
1255: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1256: --bug 3896181
1257: END IF;
1268: when others then
1269: NULL;
1270: end ;
1271: If l_message_name = 'REL_ACCEPTANCE_REQUIRED' then
1272: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 10 - REL_ACCEPTANCE_REQUIRED';
1273: IF (g_po_wf_debug = 'Y') THEN
1274: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1275: END IF;
1276: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1272: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 10 - REL_ACCEPTANCE_REQUIRED';
1273: IF (g_po_wf_debug = 'Y') THEN
1274: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1275: END IF;
1276: PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1277: l_doc_header_id,
1278: NULL);
1279: WF_PURGE.ITEMS (l_ItemType,
1280: l_itemkey,
1281: SYSDATE,
1282: true,
1283: true);
1284: --bug 3896181
1285: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1286: l_doc_number,
1287: l_doc_type,
1288: l_doc_subtype,
1289: l_release_num,
1294: END IF; -- l_message_name = 'REL_ACCEPTANCE_REQUIRED'
1295: END IF; -- l_Item_end_date is not NULL
1296: END IF; -- trunc(l_acceptance_due_date) > trunc(sysdate)
1297: ELSE -- l_item_exist = 'Y'
1298: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 11 - l_item_exists is N';
1299: --bug 3896181
1300: IF (g_po_wf_debug = 'Y') THEN
1301: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1302: END IF;
1300: IF (g_po_wf_debug = 'Y') THEN
1301: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1302: END IF;
1303: --bug 3896181
1304: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1305: l_doc_number,
1306: l_doc_type,
1307: l_doc_subtype,
1308: l_release_num,
1324: END LOOP;
1325:
1326: CLOSE REL_acceptance;
1327:
1328: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 900 - END ';
1329: IF (g_po_wf_debug = 'Y') THEN
1330: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1331: END IF;
1332:
1332:
1333:
1334: EXCEPTION
1335: WHEN NO_DATA_FOUND THEN
1336: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rel_acceptance','No data found');
1337: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 901 - ' ||
1338: 'EXCEPTION - no data found sql error: ' || sqlcode;
1339: IF (g_po_wf_debug = 'Y') THEN
1340: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1333:
1334: EXCEPTION
1335: WHEN NO_DATA_FOUND THEN
1336: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rel_acceptance','No data found');
1337: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 901 - ' ||
1338: 'EXCEPTION - no data found sql error: ' || sqlcode;
1339: IF (g_po_wf_debug = 'Y') THEN
1340: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1341: END IF;
1340: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1341: END IF;
1342:
1343: WHEN OTHERS THEN
1344: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rel_acceptance','SQL error ' || sqlcode);
1345: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 990 - ' ||
1346: 'EXCEPTION - sql error: ' || sqlcode;
1347: IF (g_po_wf_debug = 'Y') THEN
1348: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1341: END IF;
1342:
1343: WHEN OTHERS THEN
1344: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rel_acceptance','SQL error ' || sqlcode);
1345: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 990 - ' ||
1346: 'EXCEPTION - sql error: ' || sqlcode;
1347: IF (g_po_wf_debug = 'Y') THEN
1348: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1349: END IF;
1430: l_progress VARCHAR2(300) := NULL;
1431:
1432: BEGIN
1433:
1434: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 01 - BEGIN ';
1435: IF (g_po_wf_debug = 'Y') THEN
1436: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1437: END IF;
1438:
1492:
1493: l_quote_end_date_temp := l_quote_end_date_active - l_quote_warning_delay;
1494:
1495:
1496: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - sysdate : '||
1497: to_char(SYSDATE,'DD/MM/YYYY');
1498: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1499:
1500:
1497: to_char(SYSDATE,'DD/MM/YYYY');
1498: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1499:
1500:
1501: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote end date active : '||
1502: to_char(l_quote_end_date_active,'DD/MM/YYYY');
1503: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1504:
1505: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote warning delay : '||
1501: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote end date active : '||
1502: to_char(l_quote_end_date_active,'DD/MM/YYYY');
1503: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1504:
1505: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote warning delay : '||
1506: l_quote_warning_delay;
1507: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1508:
1509:
1506: l_quote_warning_delay;
1507: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1508:
1509:
1510: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote end date temp : '||
1511: to_char(l_quote_end_date_temp,'DD/MM/YYYY');
1512: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1513:
1514:
1519: to_date(to_char(l_quote_end_date_temp,'DD/MM/YYYY'),'DD/MM/YYYY') THEN
1520:
1521: l_start_ntfn_wf_ok := 'Y';
1522:
1523: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02-set l_start_ntfn_wf_ok: '||
1524: l_start_ntfn_wf_ok;
1525: --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1526:
1527: END IF;
1534:
1535: END IF;
1536:
1537:
1538: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 03 - l_start_ntfn_wf_ok: '||
1539: l_start_ntfn_wf_ok;
1540: IF (g_po_wf_debug = 'Y') THEN
1541: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1542: END IF;
1548:
1549: IF RFQ_QUOTE%FOUND THEN
1550:
1551: -- Call Workflow to check if the itemkey already exists
1552: PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
1553: l_ItemKey,
1554: l_Item_exist,
1555: l_Item_end_date);
1556:
1560: -- Bypass this one
1561:
1562: NULL;
1563:
1564: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 05 ' ||
1565: 'open WF item key exists ' ||l_itemkey;
1566: -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1567:
1568: ELSE
1569: -- Workflow item does not exist
1570: -- Invoke the Start_Approval_Reminder workflow
1571: -- for every unique workflow Item key.
1572:
1573: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 10 ' ||
1574: 'Start WF item key =' ||l_itemkey;
1575: IF (g_po_wf_debug = 'Y') THEN
1576: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1577: END IF;
1575: IF (g_po_wf_debug = 'Y') THEN
1576: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1577: END IF;
1578:
1579: PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1580: l_doc_number,
1581: l_doc_type,
1582: l_doc_subtype,
1583: l_release_num,
1604:
1605: CLOSE RFQ_QUOTE;
1606:
1607:
1608: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 900 - END ';
1609: IF (g_po_wf_debug = 'Y') THEN
1610: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1611: END IF;
1612:
1612:
1613:
1614: EXCEPTION
1615: WHEN NO_DATA_FOUND THEN
1616: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rfq_quote','No data found');
1617: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 901 - ' ||
1618: 'EXCEPTION - no data found sql error: ' || sqlcode;
1619: IF (g_po_wf_debug = 'Y') THEN
1620: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1613:
1614: EXCEPTION
1615: WHEN NO_DATA_FOUND THEN
1616: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rfq_quote','No data found');
1617: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 901 - ' ||
1618: 'EXCEPTION - no data found sql error: ' || sqlcode;
1619: IF (g_po_wf_debug = 'Y') THEN
1620: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1621: END IF;
1620: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1621: END IF;
1622:
1623: WHEN OTHERS THEN
1624: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rfq_quote','SQL error ' || sqlcode);
1625: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 990 - ' ||
1626: 'EXCEPTION - sql error: ' || sqlcode;
1627: IF (g_po_wf_debug = 'Y') THEN
1628: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1621: END IF;
1622:
1623: WHEN OTHERS THEN
1624: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rfq_quote','SQL error ' || sqlcode);
1625: l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 990 - ' ||
1626: 'EXCEPTION - sql error: ' || sqlcode;
1627: IF (g_po_wf_debug = 'Y') THEN
1628: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1629: END IF;
1664: l_progress VARCHAR2(300) := NULL;
1665:
1666: BEGIN
1667:
1668: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 01 ';
1669: IF (g_po_wf_debug = 'Y') THEN
1670: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1671: END IF;
1672:
1754: -- dbms_output.put_line ('Start_Approval_Reminder, agent dispname '|| l_agent_disp_name);
1755: wf_engine.StartProcess ( ItemType => l_ItemType,
1756: ItemKey => l_ItemKey );
1757:
1758: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 900 ';
1759: IF (g_po_wf_debug = 'Y') THEN
1760: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1761: END IF;
1762:
1763:
1764: EXCEPTION
1765:
1766: WHEN OTHERS THEN
1767: wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Approval_Reminder','SQL error ' || sqlcode);
1768: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 990 - ' ||
1769: 'EXCEPTION - sql error: ' || sqlcode;
1770: IF (g_po_wf_debug = 'Y') THEN
1771: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1764: EXCEPTION
1765:
1766: WHEN OTHERS THEN
1767: wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Approval_Reminder','SQL error ' || sqlcode);
1768: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 990 - ' ||
1769: 'EXCEPTION - sql error: ' || sqlcode;
1770: IF (g_po_wf_debug = 'Y') THEN
1771: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1772: END IF;
1826: l_progress VARCHAR2(300) := NULL;
1827:
1828: BEGIN
1829:
1830: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 01 ';
1831: IF (g_po_wf_debug = 'Y') THEN
1832: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1833: END IF;
1834:
2102: -- Set RESULT type
2103:
2104: RESULT := l_doc_type;
2105:
2106: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 05 RESULT ' ||
2107: l_doc_type;
2108: IF (g_po_wf_debug = 'Y') THEN
2109: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2110: END IF;
2116: --
2117: END IF;
2118:
2119:
2120: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 900 ';
2121: IF (g_po_wf_debug = 'Y') THEN
2122: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2123: END IF;
2124:
2124:
2125:
2126: EXCEPTION
2127: WHEN NO_DATA_FOUND THEN
2128: wf_core.context ('PO_APPROVAL_REMINDER_SV','Set_Doc_Type','No data found');
2129: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 901 - ' ||
2130: 'EXCEPTION - no data found sql error: ' || sqlcode;
2131: IF (g_po_wf_debug = 'Y') THEN
2132: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2125:
2126: EXCEPTION
2127: WHEN NO_DATA_FOUND THEN
2128: wf_core.context ('PO_APPROVAL_REMINDER_SV','Set_Doc_Type','No data found');
2129: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 901 - ' ||
2130: 'EXCEPTION - no data found sql error: ' || sqlcode;
2131: IF (g_po_wf_debug = 'Y') THEN
2132: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2133: END IF;
2132: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2133: END IF;
2134:
2135: WHEN OTHERS THEN
2136: wf_core.context ('PO_APPROVAL_REMINDER_SV','Set_Doc_Type','SQL error ' || sqlcode);
2137: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 990 - ' ||
2138: 'EXCEPTION - sql error: ' || sqlcode;
2139: IF (g_po_wf_debug = 'Y') THEN
2140: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2133: END IF;
2134:
2135: WHEN OTHERS THEN
2136: wf_core.context ('PO_APPROVAL_REMINDER_SV','Set_Doc_Type','SQL error ' || sqlcode);
2137: l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 990 - ' ||
2138: 'EXCEPTION - sql error: ' || sqlcode;
2139: IF (g_po_wf_debug = 'Y') THEN
2140: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2141: END IF;
2181: l_progress VARCHAR2(300) := NULL;
2182:
2183: BEGIN
2184:
2185: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 01 ';
2186: IF (g_po_wf_debug = 'Y') THEN
2187: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2188: END IF;
2189:
2313: --
2314: END IF;
2315:
2316:
2317: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 900 - END ';
2318: IF (g_po_wf_debug = 'Y') THEN
2319: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2320: END IF;
2321:
2321:
2322:
2323: EXCEPTION
2324: WHEN NO_DATA_FOUND THEN
2325: wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Doc_Approval','No data found');
2326: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 901 - ' ||
2327: 'EXCEPTION - no data found sql error: ' || sqlcode;
2328: IF (g_po_wf_debug = 'Y') THEN
2329: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2322:
2323: EXCEPTION
2324: WHEN NO_DATA_FOUND THEN
2325: wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Doc_Approval','No data found');
2326: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 901 - ' ||
2327: 'EXCEPTION - no data found sql error: ' || sqlcode;
2328: IF (g_po_wf_debug = 'Y') THEN
2329: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2330: END IF;
2330: END IF;
2331:
2332:
2333: WHEN OTHERS THEN
2334: wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Doc_Approval','SQL error ' || sqlcode);
2335: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 990 - ' ||
2336: 'EXCEPTION - sql error: ' || sqlcode;
2337: IF (g_po_wf_debug = 'Y') THEN
2338: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2331:
2332:
2333: WHEN OTHERS THEN
2334: wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Doc_Approval','SQL error ' || sqlcode);
2335: l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 990 - ' ||
2336: 'EXCEPTION - sql error: ' || sqlcode;
2337: IF (g_po_wf_debug = 'Y') THEN
2338: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2339: END IF;
2406: /* RETROACTIVE FPI END */
2407:
2408: BEGIN
2409:
2410: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 01 ' ||
2411: p_DocumentTypeCode || p_DocumentSubtype || p_DocumentNumber;
2412: IF (g_po_wf_debug = 'Y') THEN
2413: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',NULL,l_progress);
2414: END IF;
2442: * to do the same from here also. Now we will get email or
2443: * document will get printed depending on the setup when we
2444: * we approve the document from the Notification Summary screen.
2445: */
2446: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 02 ' ||
2447: p_DocumentTypeCode || p_DocumentSubtype ||
2448: p_DocumentNumber;
2449: IF (g_po_wf_debug = 'Y') THEN
2450: PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
2487: * since we need these values to set some workflow attributes
2488: * before we call start_Wf_process.
2489: */
2490:
2491: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 03 ' ||
2492: p_DocumentTypeCode || p_DocumentSubtype ||
2493: p_DocumentNumber;
2494: IF (g_po_wf_debug = 'Y') THEN
2495: PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
2553: /* Bug 2780033.
2554: * Add the communication flags to the call to
2555: * start_wf_process below.
2556: */
2557: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 04 ' ||
2558: p_DocumentTypeCode || p_DocumentSubtype ||
2559: p_DocumentNumber;
2560: IF (g_po_wf_debug = 'Y') THEN
2561: PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
2580: EmailFlag => l_emailflag,
2581: EmailAddress => l_emailaddress);
2582:
2583:
2584: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 900 ' ||
2585: p_DocumentTypeCode || p_DocumentSubtype || p_DocumentNumber;
2586: IF (g_po_wf_debug = 'Y') THEN
2587: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
2588: END IF;
2591:
2592: EXCEPTION
2593:
2594: WHEN OTHERS THEN
2595: wf_core.context ('PO_APPROVAL_REMINDER_SV','SetUpWorkFlow','SQL error ' || sqlcode);
2596: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 990 - ' ||
2597: 'EXCEPTION - sql error: ' || sqlcode;
2598: IF (g_po_wf_debug = 'Y') THEN
2599: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
2592: EXCEPTION
2593:
2594: WHEN OTHERS THEN
2595: wf_core.context ('PO_APPROVAL_REMINDER_SV','SetUpWorkFlow','SQL error ' || sqlcode);
2596: l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 990 - ' ||
2597: 'EXCEPTION - sql error: ' || sqlcode;
2598: IF (g_po_wf_debug = 'Y') THEN
2599: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
2600: END IF;
2630:
2631:
2632: BEGIN
2633:
2634: x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 01';
2635: IF (g_po_wf_debug = 'Y') THEN
2636: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2637: END IF;
2638:
2658: aname => 'FORWARD_TO_USERNAME_RESPONSE');
2659:
2660: l_forward_to_username_response := UPPER(l_forward_to_username_response);
2661:
2662: x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 02';
2663: x_progress := x_progress || ' Forward-To=' || l_forward_to_username_response;
2664: IF (g_po_wf_debug = 'Y') THEN
2665: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2666: END IF;
2785:
2786: END IF;
2787:
2788:
2789: x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 900';
2790: IF (g_po_wf_debug = 'Y') THEN
2791: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2792: END IF;
2793:
2794:
2795: EXCEPTION
2796:
2797: WHEN OTHERS THEN
2798: wf_core.context ('PO_APPROVAL_REMINDER_SV','Is_Forward_To_Valid','SQL error ' || sqlcode);
2799: x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 990 - ' ||
2800: 'EXCEPTION - sql error: ' || sqlcode;
2801: IF (g_po_wf_debug = 'Y') THEN
2802: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2795: EXCEPTION
2796:
2797: WHEN OTHERS THEN
2798: wf_core.context ('PO_APPROVAL_REMINDER_SV','Is_Forward_To_Valid','SQL error ' || sqlcode);
2799: x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 990 - ' ||
2800: 'EXCEPTION - sql error: ' || sqlcode;
2801: IF (g_po_wf_debug = 'Y') THEN
2802: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2803: END IF;
2846: BEGIN
2847: l_doc_subtype := p_DocumentTypeCode;
2848: if ((p_DocumentTypeCode = 'STANDARD') or (p_DocumentTypeCode = 'PLANNED')) then
2849:
2850: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-001';
2851:
2852: l_doc_type := 'PO';
2853:
2854: select agent_id
2864: l_doc_subtype ||'-'||
2865: to_char(p_DocumentId) ||'-';
2866:
2867: For rec in cand_active_wf(l_itemkey) LOOP
2868: if (po_approval_reminder_sv.is_active('APVRMDER',rec.item_key)) then
2869: WF_Engine.AbortProcess('APVRMDER',rec.item_key);
2870: end if;
2871: End Loop;
2872:
2874:
2875:
2876: if (p_ReleaseFlag = 'Y') then /* the doc_type must be RELEASE */
2877:
2878: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-002';
2879:
2880: l_doc_type := 'RELEASE';
2881:
2882: select agent_id
2893: to_char(p_DocumentId) ||'-'||
2894: to_char(l_agent_id);
2895: else
2896:
2897: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-003';
2898:
2899: l_doc_type := 'PA';
2900:
2901: select agent_id
2910: l_doc_subtype ||'-'||
2911: to_char(p_DocumentId) ||'-'||
2912: to_char(l_agent_id);
2913: end if;
2914: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2915: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2916: end if;
2917:
2918:
2918:
2919:
2920: elsif ((p_DocumentTypeCode = 'INTERNAL') or (p_DocumentTypeCode = 'PURCHASE')) then
2921:
2922: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-004';
2923:
2924: l_doc_type := 'REQUISITION';
2925:
2926: select preparer_id
2935: l_doc_subtype ||'-'||
2936: to_char(p_DocumentId) ||'-'||
2937: to_char(l_agent_id);
2938:
2939: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2940: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2941: end if;
2942:
2943: elsif (p_DocumentTypeCode = 'SCHEDULED') then
2941: end if;
2942:
2943: elsif (p_DocumentTypeCode = 'SCHEDULED') then
2944:
2945: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-005';
2946:
2947: l_doc_type := 'RELEASE';
2948:
2949: select agent_id
2958: l_doc_subtype ||'-'||
2959: to_char(p_DocumentId) ||'-'||
2960: to_char(l_agent_id);
2961:
2962: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2963: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2964: end if;
2965: elsif (p_DocumentTypeCode = 'PO_ACCEPTANCE') then
2966:
2963: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2964: end if;
2965: elsif (p_DocumentTypeCode = 'PO_ACCEPTANCE') then
2966:
2967: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-006';
2968:
2969: l_doc_type := 'PO_ACCEPTANCE';
2970:
2971: select type_lookup_code
2980: l_doc_subtype ||'-'||
2981: to_char(p_DocumentId);
2982:
2983:
2984: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2985: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2986: end if;
2987:
2988: -- Bug 3593182: Removed the item exists condition before
2996: force => true); --
2997:
2998: elsif (p_DocumentTypeCode = 'REL_ACCEPTANCE') then
2999:
3000: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-007';
3001:
3002: l_doc_type := 'REL_ACCEPTANCE';
3003:
3004: select poh.type_lookup_code
3019: l_doc_subtype ||'-'||
3020: to_char(p_DocumentId);
3021:
3022:
3023: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
3024: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
3025: end if;
3026:
3027: -- Bug 3593182: Removed the item exists condition before
3036: force => true); --
3037:
3038: elsif (p_DocumentTypeCode = 'RFQ') then
3039:
3040: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-008';
3041: l_doc_type := 'RFQ';
3042:
3043: l_itemkey := l_doc_type ||
3044: to_char(p_DocumentId);
3042:
3043: l_itemkey := l_doc_type ||
3044: to_char(p_DocumentId);
3045:
3046: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
3047: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
3048: end if;
3049:
3050:
3059: force => true); --
3060:
3061: elsif (p_DocumentTypeCode = 'QUOTATION') then
3062:
3063: x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-009';
3064: l_doc_type := 'QUOTATION';
3065:
3066: l_itemkey := l_doc_type ||
3067: to_char(p_DocumentId);
3065:
3066: l_itemkey := l_doc_type ||
3067: to_char(p_DocumentId);
3068:
3069: if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
3070: WF_Engine.AbortProcess('APVRMDER',l_itemkey);
3071: end if;
3072:
3073:
3087: WHEN OTHERS THEN
3088: IF (g_po_wf_debug = 'Y') THEN
3089: PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
3090: END IF;
3091: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Cancel_notification','SQL error ' || sqlcode);
3092: RAISE;
3093:
3094: END Cancel_Notif;
3095:
3103:
3104: x_progress varchar2(100) := '001';
3105:
3106: BEGIN
3107: x_progress := 'PO_APPROVAL_REMINDER_SV.is_active-001';
3108:
3109: --bug#3693990 commented out the above sql because the view
3110: --wf_item_activity_statuses_v is a view which is based on
3111: --a union all between wf_item_activity_statuses and
3137: WHEN OTHERS THEN
3138: IF (g_po_wf_debug = 'Y') THEN
3139: PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
3140: END IF;
3141: wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Check_Status','SQL error ' || sqlcode);
3142: RAISE;
3143: return FALSE;
3144: END;
3145:
3147: item_key in varchar2) is
3148:
3149: BEGIN
3150:
3151: if (po_approval_reminder_sv.is_active(item_type,item_key)) then
3152: WF_Engine.AbortProcess(item_type,item_key,cascade=>true);
3153: end if;
3154:
3155: END stop_process;
3165:
3166: BEGIN
3167:
3168:
3169: l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 01';
3170: -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3171:
3172: -- initialize the return variables
3173: p_item_exist := NULL;
3179: WHERE WI.ITEM_TYPE = p_ItemType
3180: AND WI.ITEM_KEY = p_ItemKey;
3181:
3182:
3183: l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 900 ';
3184: -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3185:
3186:
3187: EXCEPTION
3192: p_item_end_date := NULL;
3193:
3194: WHEN OTHERS THEN
3195:
3196: wf_core.context ('PO_APPROVAL_REMINDER_SV','Item_exist','SQL error ' || sqlcode);
3197: l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 990 - ' ||
3198: 'EXCEPTION - sql error: ' || sqlcode;
3199: IF (g_po_wf_debug = 'Y') THEN
3200: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3193:
3194: WHEN OTHERS THEN
3195:
3196: wf_core.context ('PO_APPROVAL_REMINDER_SV','Item_exist','SQL error ' || sqlcode);
3197: l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 990 - ' ||
3198: 'EXCEPTION - sql error: ' || sqlcode;
3199: IF (g_po_wf_debug = 'Y') THEN
3200: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3201: END IF;
3472: END IF;
3473: END IF;
3474:
3475: -- Cancel and purge any existing reminder process for this line.
3476: PO_APPROVAL_REMINDER_SV.item_exist (p_ItemType => 'APVRMDER',
3477: p_ItemKey => l_item_key,
3478: p_Item_exist => l_item_exist,
3479: p_Item_end_date => l_item_end_date);
3480: IF (l_item_exist = 'Y') THEN
3668: resultout := wf_engine.eng_completed || ':' || l_line_reminder_type;
3669:
3670: EXCEPTION
3671: WHEN OTHERS THEN
3672: wf_core.context( 'PO_APPROVAL_REMINDER_SV',
3673: 'GET_TEMP_LABOR_REMINDER_TYPE' );
3674: RAISE;
3675: END get_po_line_reminder_type;
3676:
3675: END get_po_line_reminder_type;
3676:
3677: --
3678:
3679: END PO_APPROVAL_REMINDER_SV;