[Home] [Help]
PACKAGE BODY: APPS.PO_APPROVAL_REMINDER_SV
Source
1 PACKAGE BODY PO_APPROVAL_REMINDER_SV AS
2 /* $Header: POXWARMB.pls 120.12 2007/12/04 09:16:04 bisdas 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: <SVC_NOTIFICATIONS FPJ>
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
15
16 DESCRIPTION: This server procedure is defined as a concurrent
17 PL/SQL executable program and is scheduled to run
18 from the Concurrent Manager at a regular intervals
19 (e.g. every day).
20
21
22 CHANGE HISTORY: WLAU 7/15/1997 Created
23
24 ===========================================================================*/
25
26 PROCEDURE Select_Unapprove_docs IS
27
28 l_ItemType VARCHAR2(100) := 'APVRMDER';
29 l_itemkey VARCHAR2(100) := NULL;
30 l_progress VARCHAR2(300) := NULL;
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
40
41 Process_unapprove_reqs;
42
43 Process_unapprove_pos;
44
45 Process_unapprove_releases;
46
47 Process_po_acceptance;
48
49 Process_rel_acceptance;
50
51 Process_rfq_quote;
52
53 process_po_temp_labor_lines; -- <SVC_NOTIFICATIONS FPJ>
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
60
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);
70 END IF;
71
72 RAISE;
73
74
75 END Select_Unapprove_docs;
76
77
78
79 /*===========================================================================
80 PROCEDURE NAME: Process_unapprove_reqs
81
82 DESCRIPTION:
83 This procedure does the following:
84 - Open cursor PO_REQUISITION_HEADERS table to select
85 Incomplete or Requires_reapproval documents.
86
87 - For each unapprove document, initiate the
88 PO Approval Reminder workflow notification.
89
90
91
92 CHANGE HISTORY: WLAU 7/15/1997 Created
93 ===========================================================================*/
94 PROCEDURE Process_unapprove_reqs IS
95
96 -- Define cursor for selecting unapprove document to start the Purchasing
97 -- Approval Reminder workflow process.
98 --
99
100 CURSOR unapprove_req IS
101 SELECT Requisition_Header_ID, Segment1, Type_Lookup_Code, Preparer_ID
102 FROM PO_REQUISITION_HEADERS
103 WHERE NVL(authorization_status,'INCOMPLETE') IN
104 ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL', 'RETURNED')
105 AND NVL(cancel_flag,'N') = 'N'
106 AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
107
108
109 l_doc_header_id NUMBER;
110 l_agent_id NUMBER;
111 l_doc_type VARCHAR2(25);
112 l_doc_subtype VARCHAR2(25);
113 l_doc_number VARCHAR2(20);
114 l_release_num NUMBER := NULL;
115
116 l_ItemType VARCHAR2(100) := 'APVRMDER';
117 l_itemkey VARCHAR2(100) := NULL;
118
119 l_item_exist VARCHAR2(1);
120 l_item_end_date DATE;
121 l_progress VARCHAR2(300) := NULL;
122
123 BEGIN
124
125
126 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 01 - BEGIN ';
127 IF (g_po_wf_debug = 'Y') THEN
128 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
129 END IF;
130
131
132 l_itemkey := ' ';
133
134 -- Select unapproved requisition documents and initiate
135 -- Approval reminder workflow
136
137 OPEN unapprove_req ;
138
139 LOOP
140
141 FETCH Unapprove_req into l_doc_header_id,
142 l_doc_number,
143 l_doc_subtype,
144 l_agent_id;
145
146 l_doc_type := 'REQUISITION';
147 --bug#3709971 modified the structure of item key from
148 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
149 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
150 l_itemkey := l_doc_type ||'-'||
151 l_doc_subtype ||'-'||
152 to_char(l_doc_header_id) ||'-'||
153 to_char(l_agent_id);
154
155
156 IF Unapprove_req%FOUND THEN
157
158 PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
159 l_ItemKey,
160 l_Item_exist,
161 l_Item_end_date);
162
163
164 IF l_item_exist = 'Y' AND
165 l_item_end_date is NULL THEN
166
167 -- Workflow item exists and is still opened
168 -- Bypass this one
169
170 NULL;
171
172 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_reqs: 05 ' ||
173 'open WF item key exists ' ||l_itemkey;
174 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
175
176 ELSE
177
178
179 IF l_item_exist = 'Y' AND
180 l_item_end_date is NOT NULL THEN
181
182 -- Call workflow to remove the completed process
183
184 --<BUG 3351588>
185 --Force item purge even if an active child process exists.
186 WF_PURGE.ITEMS (itemtype => l_ItemType,
187 itemkey => l_Itemkey,
188 enddate => SYSDATE,
189 docommit => true, --<BUG 3351588>
190 force => true); --<BUG 3351588>
191
192 END IF;
193
194 --
195 -- Invoke the Start_Approval_Reminder workflow
196 -- for every unique workflow Item key.
197
198 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_reqs: 10 ' ||
199 'Start WF item key =' ||l_itemkey;
200 IF (g_po_wf_debug = 'Y') THEN
201 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
202 END IF;
203
204 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
205 l_doc_number,
206 l_doc_type,
207 l_doc_subtype,
208 l_release_num,
209 l_agent_id,
210 l_itemkey);
211
212
213 -- Commit the changes so that the notifications will be able
214 -- to pickup the reminder notifications
215
216 COMMIT;
217
218 END IF;
219
220 END IF;
221
222 EXIT WHEN Unapprove_req%NOTFOUND;
223
224 END LOOP;
225
226 CLOSE Unapprove_req;
227
228
229 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 900 - END ';
230 IF (g_po_wf_debug = 'Y') THEN
231 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
232 END IF;
233
234
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_reqs','No data found');
238 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 901 - ' ||
239 'EXCEPTION - no data found sql error: ' || sqlcode;
240 IF (g_po_wf_debug = 'Y') THEN
241 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
242 END IF;
243
244
245 WHEN OTHERS THEN
246 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_reqs','SQL error ' || sqlcode);
247 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_reqs: 990 - ' ||
248 'EXCEPTION - sql error: ' || sqlcode;
249 IF (g_po_wf_debug = 'Y') THEN
250 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
251 END IF;
252
253 RAISE;
254
255
256 END Process_unapprove_reqs;
257
258
259
260 /*===========================================================================
261 PROCEDURE NAME: Process_unapprove_pos
262
263 DESCRIPTION:
264 This procedure does the following:
265 - Open cursor PO_HEADERS table to select
266 Incomplete or Requires_reapproval documents.
267
268 - For each unapprove document, initiate the
269 PO Approval Reminder workflow notification.
270
271 CHANGE HISTORY: WLAU 7/15/1997 Created
272 ===========================================================================*/
273
274 PROCEDURE Process_unapprove_pos IS
275
276 -- Define cursor for selecting unapprove document to start the Purchasing
277 -- Approval Reminder workflow process.
278 --
279
280 CURSOR unapprove_PO IS
281 SELECT PO_Header_ID, Segment1, Type_Lookup_Code, Agent_ID
282 FROM PO_HEADERS
283 WHERE NVL(authorization_status,'INCOMPLETE') IN
284 ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
285 -- AND WF_ITEM_TYPE = NULL
286 -- AND WF_ITEM_KEY = NULL
287 AND type_lookup_code in ('STANDARD','PLANNED','BLANKET','CONTRACT')
288 AND NVL(cancel_flag,'N') = 'N'
289 AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
290
291
292 l_doc_header_id NUMBER;
293 l_agent_id NUMBER;
294 l_doc_type VARCHAR2(25);
295 l_doc_subtype VARCHAR2(25);
296 l_doc_number VARCHAR2(20);
297 l_release_num NUMBER := NULL;
298
299 l_ItemType VARCHAR2(100) := 'APVRMDER';
300 l_itemkey VARCHAR2(100) := NULL;
301
302 l_item_exist VARCHAR2(1);
303 l_item_end_date DATE;
304 l_progress VARCHAR2(300) := NULL;
305
306 BEGIN
307
308
309 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 01 - BEGIN ';
310 IF (g_po_wf_debug = 'Y') THEN
311 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
312 END IF;
313
314
315 l_itemkey := ' ';
316
317 -- Select unapproved po documents and initiate
318 -- Approval reminder workflow
319
320 OPEN unapprove_PO ;
321
322 LOOP
323
324 FETCH Unapprove_PO into l_doc_header_id,
325 l_doc_number,
326 l_doc_subtype,
327 l_agent_id;
328
329
330 IF l_doc_subtype IN ('STANDARD','PLANNED') THEN
331 l_doc_type := 'PO';
332 ELSIF l_doc_subtype IN ('BLANKET','CONTRACT') THEN
333 l_doc_type := 'PA';
334 END IF;
335 --bug#3709971 modified the structure of item key from
336 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
337 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
338
339 l_itemkey := l_doc_type || '-'||
340 l_doc_subtype ||'-'||
341 to_char(l_doc_header_id) ||'-'||
342 to_char(l_agent_id);
343
344
345 IF Unapprove_PO%FOUND THEN
346
347
348 PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
349 l_ItemKey,
350 l_Item_exist,
351 l_Item_end_date);
352
353
354 IF l_item_exist = 'Y' AND
355 l_item_end_date is NULL THEN
356
357 -- Workflow item exists and is still opened
358 -- Bypass this one
359
360 NULL;
361
362 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_pos: 05 ' ||
363 'open WF item key exists ' ||l_itemkey;
364 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
365
366 ELSE
367
368
369 IF l_item_exist = 'Y' AND
370 l_item_end_date is NOT NULL THEN
371
372 -- Call workflow to remove the completed process
373
374 --<BUG 3351588>
375 --Force item purge even if an active child process exists.
376 WF_PURGE.ITEMS (itemtype => l_ItemType,
377 itemkey => l_Itemkey,
378 enddate => SYSDATE,
379 docommit => true, --<BUG 3351588>
383 --
380 force => true); --<BUG 3351588>
381 END IF;
382
384 -- Invoke the Start_Approval_Reminder workflow
385 -- for every unique workflow Item key.
386
387 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_pos: 10 ' ||
388 'Start WF item key =' ||l_itemkey;
389 IF (g_po_wf_debug = 'Y') THEN
390 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
391 END IF;
392
393 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
394 l_doc_number,
395 l_doc_type,
396 l_doc_subtype,
397 l_release_num,
398 l_agent_id,
399 l_itemkey);
400
401
402
403 -- Commit the changes so that the notifications will be able
404 -- to pickup the reminder notifications
405
406 COMMIT;
407
408 END IF;
409
410 END IF;
411
412 EXIT WHEN Unapprove_PO%NOTFOUND;
413
414 END LOOP;
415
416 CLOSE Unapprove_PO;
417
418
419 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 900 - END ';
420 IF (g_po_wf_debug = 'Y') THEN
421 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
422 END IF;
423
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_pos','No data found');
427
428 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 901 - ' ||
429 'EXCEPTION - no data found sql error: ' || sqlcode;
430 IF (g_po_wf_debug = 'Y') THEN
431 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
432 END IF;
433
434 WHEN OTHERS THEN
435 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_pos','SQL error ' || sqlcode);
436
437 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_pos: 990 - ' ||
438 'EXCEPTION - sql error: ' || sqlcode;
439 IF (g_po_wf_debug = 'Y') THEN
440 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
441 END IF;
442
443
444 RAISE;
445
446
447 END Process_unapprove_pos;
448
449
450 /*===========================================================================
451 PROCEDURE NAME: Process_unapprove_releases
452
453 DESCRIPTION:
454 This procedure does the following:
455 - Open cursor PO_RELEASES table to select
456 Incomplete or Requires_reapproval documents.
457
458 - For each unapprove document, initiate the
459 PO Approval Reminder workflow notification.
460
461 CHANGE HISTORY: WLAU 7/15/1997 Created
462 ===========================================================================*/
463
464 PROCEDURE Process_unapprove_releases IS
465
466 -- Define cursor for selecting unapprove document to start the Purchasing
467 -- Approval Reminder workflow process.
468 --
469
470
471 CURSOR unapprove_REL IS
472 SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
473 POH.Type_Lookup_Code, PORH.Agent_ID
474 FROM PO_RELEASES_ALL PORH, -- <R12 MOAC>
475 PO_HEADERS POH
476 WHERE NVL(PORH.authorization_status,'INCOMPLETE') IN
477 ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
478 -- AND WF_ITEM_TYPE = NULL
479 -- AND WF_ITEM_KEY = NULL
480 AND NVL(PORH.cancel_flag,'N') = 'N'
481 AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
482 AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID;
483
484 l_doc_header_id NUMBER;
485 l_agent_id NUMBER;
486 l_doc_type VARCHAR2(25);
487 l_doc_subtype VARCHAR2(25);
488 l_doc_number VARCHAR2(20);
489 l_release_num NUMBER := NULL;
490
491 l_ItemType VARCHAR2(100) := 'APVRMDER';
492 l_itemkey VARCHAR2(100) := NULL;
493
494
495 l_item_exist VARCHAR2(1);
496 l_item_end_date DATE;
497 l_progress VARCHAR2(300) := NULL;
498
499 BEGIN
500
501
502 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_releases: 01 - BEGIN ';
503 IF (g_po_wf_debug = 'Y') THEN
504 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
505 END IF;
506
507 l_itemkey := ' ';
508
509 -- Select unapproved release documents and initiate
510 -- Approval reminder workflow
511
512 OPEN unapprove_REL ;
513
514 LOOP
515
516 FETCH Unapprove_REL into l_doc_header_id,
517 l_doc_number,
518 l_release_num,
519 l_doc_subtype,
520 l_agent_id;
524 IF l_doc_subtype = 'PLANNED' THEN
521
522 l_doc_type := 'RELEASE';
523
525 l_doc_subtype := 'SCHEDULED';
526 END IF;
527 --bug#3709971 modified the structure of item key from
528 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
529 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
530
531 l_itemkey := l_doc_type || '-'||
532 l_doc_subtype ||'-'||
533 to_char(l_doc_header_id) ||'-'||
534 to_char(l_agent_id);
535
536
537 IF Unapprove_REL%FOUND THEN
538
539
540 PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
541 l_ItemKey,
542 l_Item_exist,
543 l_Item_end_date);
544
545
546 IF l_item_exist = 'Y' AND
547 l_item_end_date is NULL THEN
548
549 -- Workflow item exists and is still opened
550 -- Bypass this one
551
552 NULL;
553
554 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_releases: 05 ' ||
555 'open WF item key exists ' ||l_itemkey;
556 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
557
558
559 ELSE
560
561 IF l_item_exist = 'Y' AND
562 l_item_end_date is NOT NULL THEN
563
564 -- Call workflow to remove the completed process
565
566 --<BUG 3351588>
567 --Force item purge even if an active child process exists.
568 WF_PURGE.ITEMS (itemtype => l_ItemType,
569 itemkey => l_Itemkey,
570 enddate => SYSDATE,
571 docommit => true, --<BUG 3351588>
572 force => true); --<BUG 3351588>
573
574 END IF;
575
576
577 --
578 -- Invoke the Start_Approval_Reminder workflow
579 -- for every unique workflow Item key.
580
581 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_unapprove_releases: 10 ' ||
582 'Start WF item key =' ||l_itemkey;
583 IF (g_po_wf_debug = 'Y') THEN
584 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
585 END IF;
586
587 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
588 l_doc_number,
589 l_doc_type,
590 l_doc_subtype,
591 l_release_num,
592 l_agent_id,
593 l_itemkey);
594
595
596 -- Commit the changes so that the notifications will be able
597 -- to pickup the reminder notifications
598
599 COMMIT;
600
601 END IF;
602
603 END IF;
604
605 EXIT WHEN Unapprove_REL%NOTFOUND;
606
607 END LOOP;
608
609 CLOSE Unapprove_REL;
610
611 l_progress := 'PO_APPROVAL_REMINDER_SV.Process unapprove_releases: 900 - END ';
612 IF (g_po_wf_debug = 'Y') THEN
613 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
614 END IF;
615
616 EXCEPTION
617 WHEN NO_DATA_FOUND THEN
618 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_releases','No data found');
619 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_Unapprove_releases: 901 - ' ||
620 'EXCEPTION - no data found sql error: ' || sqlcode;
621 IF (g_po_wf_debug = 'Y') THEN
622 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
623 END IF;
624
625
626 WHEN OTHERS THEN
627 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Unapprove_releases','SQL error ' || sqlcode);
628 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_Unapprove_releases: 990 - ' ||
629 'EXCEPTION - sql error: ' || sqlcode;
630 IF (g_po_wf_debug = 'Y') THEN
631 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
632 END IF;
633
634 RAISE;
635
636
637
638 END Process_unapprove_releases;
639
640
641 /*===========================================================================
642 PROCEDURE NAME: Process_po_acceptance
643
644 DESCRIPTION:
645 This procedure does the following:
646 - Search for Approved POs with acceptance required
647
648 - For each selected document, initiate the
649 PO Approval Reminder workflow notification
650
651 CHANGE HISTORY: WLAU 11/15/1997 Created
652 ===========================================================================*/
653
654 PROCEDURE Process_po_acceptance IS
655
656 -- Define cursor for selecting approved POs with acceptance required
657
658 /* Bug# 1595348: kagarwal
659 ** Desc: If the PO/Rel is accepted using Web Supplier Portal, the acceptance
663 -- Bug 5074128(forward fix4772820)
660 ** is registered in the PO Acceptances table. Hence we need to check the PO
661 ** Acceptances table also.
662 */
664 -- poh.acceptance_required_flag can be Y or D
665 CURSOR PO_acceptance IS
666 SELECT poh.PO_Header_ID, poh.Segment1, poh.Type_Lookup_Code,
667 poh.Agent_ID, NVL(poh.Acceptance_Due_Date, SYSDATE)
668 FROM PO_HEADERS poh
669 WHERE NVL(poh.authorization_status,'INCOMPLETE') = 'APPROVED'
670 AND NVL(poh.acceptance_required_flag,'N') in ('Y','D')
671 AND poh.type_lookup_code in
672 ('STANDARD','PLANNED','BLANKET','CONTRACT')
673 AND NVL(poh.cancel_flag,'N') = 'N'
674 AND NVL(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
675 AND not exists (
676 SELECT poa.ACCEPTANCE_ID
677 FROM PO_ACCEPTANCES poa
678 WHERE NVL(poa.accepted_flag, 'N') = 'Y'
679 AND poa.po_header_id = poh.po_header_id
680 AND nvl(poa.revision_num,0) = nvl(poh.revision_num,0));
681
682
683 l_doc_header_id NUMBER;
684 l_agent_id NUMBER;
685 l_doc_type VARCHAR2(25);
686 l_doc_subtype VARCHAR2(25);
687 l_doc_number VARCHAR2(20);
688 l_release_num NUMBER := NULL;
689 l_acceptance_due_date DATE;
690
691 l_ItemType VARCHAR2(100) := 'APVRMDER';
692 l_itemkey VARCHAR2(100) := NULL;
693
694
695 l_item_exist VARCHAR2(1);
696 l_item_end_date DATE;
697 l_progress VARCHAR2(300) := NULL;
698
699 l_message_name VARCHAR2(50) := NULL; --bug 3896181
700
701 BEGIN
702
703
704 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 01 - BEGIN ';
705 IF (g_po_wf_debug = 'Y') THEN
706 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
707 END IF;
708
709
710 l_itemkey := '';
711
712 -- Select POs required and initiate Approval reminder workflow
713
714 OPEN PO_acceptance ;
715
716 LOOP
717
718 FETCH PO_acceptance into l_doc_header_id,
719 l_doc_number,
720 l_doc_subtype,
721 l_agent_id,
722 l_acceptance_due_date;
723
724 l_doc_type := 'PO_ACCEPTANCE';
725 --bug#3709971 modified the structure of item key from
726 --doc_type||doc_subtype||doc_num(old structure) to
727 --doc_type||'-'||doc_subtype||'-'||doc_num
728
729 l_itemkey := l_doc_type || '-'||
730 l_doc_subtype ||'-'||
731 to_char(l_doc_header_id);
732
733 /* Bug 3896181 fixed.
734 Revamped the code for acceptances part to take care of multiple scenarios now.
735 After the fix, if a reminder notification already exists, then APVRMDER will not
736 send new notifications.
737 If the acceptance_due_date is passed and a reminder notification exists, then
738 this old notification is closed and a new 'past-due' notification is sent.
739 Also, if at any time, acceptance_due_date is changed to old/new dates, sending
740 of notifications is taken care appropriately.
741 THe below part fixes for PO acceptance notifications.
742 */
743 IF PO_acceptance%FOUND THEN
744
745
746 PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
747 l_ItemKey,
748 l_Item_exist,
749 l_Item_end_date);
750
751
752 IF l_item_exist = 'Y' THEN
753
754 -- IF to_date(to_char(l_acceptance_due_date,'DD/MM/YYYY'),'DD-MON-YYYY') >
755 -- to_date(to_char(SYSDATE,'DD-MON-YYYY'),'DD/MM/YYYY') THEN
756 -- bug: 1076985
757
758 -- bug 3896181 <start>
759 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 02 - item key exists ';
760 IF (g_po_wf_debug = 'Y') THEN
761 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
762 end if;
763 -- bug 3896181 <end>
764
765 IF trunc(l_acceptance_due_date) > trunc(sysdate) THEN
766
767 -- bug 3896181 <start>
768 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 03 - acc_due_date > sysdate ';
769 IF (g_po_wf_debug = 'Y') THEN
770 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
771 END IF;
772 IF l_Item_end_date is not null then
773 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 04 - end_date not null';
774 IF (g_po_wf_debug = 'Y') THEN
775 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
776 END IF;
777 -- bug 3896181 <end>
778 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
779 l_doc_header_id,
780 NULL);
781 -- bug 3896181 <start>
782
783 WF_PURGE.ITEMS (l_ItemType,
784 l_itemkey,
785 SYSDATE,
786 true,
787 true);
788 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
789 l_doc_number,
790 l_doc_type,
791 l_doc_subtype,
795 COMMIT;
792 l_release_num,
793 l_agent_id,
794 l_itemkey);
796 ELSE -- l_Item_end_date is not null
797 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 05 - end_date is null';
798 IF (g_po_wf_debug = 'Y') THEN
799 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
800 -- bug 3896181 <end>
801 END IF;
802
803 -- bug 3896181 <start>
804 select wfn.MESSAGE_NAME
805 into l_message_name
806 from wf_item_activity_statuses wias, wf_notifications wfn
807 where wias.notification_id = wfn.group_id
808 and wias.item_type = 'APVRMDER'
809 and wias.item_key = l_ItemKey ;
810 IF l_message_name = 'PO_ACCEPTANCE_PAST_DUE' then
811 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 06 - PO_ACCEPTANCE_PAST_DUE';
812 -- bug 3896181 <end>
813 IF (g_po_wf_debug = 'Y') THEN
814 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
815 END IF;
816 -- bug 3896181 <start>
817 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
818 l_doc_header_id,
819 NULL);
820 -- bug 3896181 <end>
821 --<BUG 3351588>
822 --Force item purge even if an active child process exists.
823
824 WF_PURGE.ITEMS (itemtype => l_ItemType,
825 itemkey => l_Itemkey,
826 enddate => SYSDATE,
827 docommit => true, --<BUG 3351588>
828 force => true); --<BUG 3351588>
829
830 -- bug 3896181 <start>
831 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
832 l_doc_number,
833 l_doc_type,
834 l_doc_subtype,
835 l_release_num,
836 l_agent_id,
837 l_itemkey);
838 COMMIT;
839 END IF; -- l_message_name = 'PO_ACCEPTANCE_PAST_DUE'
840 END IF; -- l_Item_end_date is not null
841 ELSE -- l_acceptance_due_date) > trunc(sysdate)
842 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 07 - acc_due_date < sysdate';
843 -- bug 3896181 <end>
844 IF (g_po_wf_debug = 'Y') THEN
845 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
846 END IF;
847
848 -- bug 3896181 <start>
849 IF l_Item_end_date is not NULL THEN
850 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 08 - end_date is not null';
851 IF (g_po_wf_debug = 'Y') THEN
852 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
853 END IF;
854 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
855 l_doc_header_id,
856 NULL);
857 WF_PURGE.ITEMS (l_ItemType,
858 l_itemkey,
859 SYSDATE,
860 true,
861 true);
862 -- bug 3896181 <end>
863
864 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
865 l_doc_number,
866 l_doc_type,
867 l_doc_subtype,
868 l_release_num,
869 l_agent_id,
870 l_itemkey);
871
872
873 -- Commit the changes so that the notifications will be able
874 -- to pickup the reminder notifications
875
876 COMMIT;
877 -- bug 3896181 <start>
878 ELSE -- l_Item_end_date is not NULL
879 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 09 - end_date is null';
880 IF (g_po_wf_debug = 'Y') THEN
881 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
882 -- bug 3896181 <end>
883 END IF;
884 -- bug 3896181 <start>
885 select wfn.MESSAGE_NAME
886 into l_message_name
887 from wf_item_activity_statuses wias, wf_notifications wfn
888 where wias.notification_id = wfn.group_id
889 and wias.item_type = 'APVRMDER'
890 and wias.item_key = l_ItemKey ;
891 IF l_message_name = 'PO_ACCEPTANCE_REQUIRED' then
895 END IF;
892 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 10 - PO_ACCEPTANCE_REQUIRED';
893 IF (g_po_wf_debug = 'Y') THEN
894 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
896 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
897 l_doc_header_id,
898 NULL);
899 WF_PURGE.ITEMS (l_ItemType,
900 l_itemkey,
901 SYSDATE,
902 true,
903 true);
904 -- bug 3896181 <end>
905
906 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
907 l_doc_number,
908 l_doc_type,
909 l_doc_subtype,
910 l_release_num,
911 l_agent_id,
912 l_itemkey);
913 -- bug 3896181 <start>
914 COMMIT;
915 END IF; -- l_message_name = 'PO_ACCEPTANCE_REQUIRED'
916 END IF; -- l_Item_end_date is not NULL
917 END IF; -- l_acceptance_due_date) > trunc(sysdate)
918 ELSE -- l_item_exist = 'Y'
919 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 11 - l_item_exists is N';
920 -- bug 3896181 <end>
921 IF (g_po_wf_debug = 'Y') THEN
922 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
923 END IF;
924 -- bug 3896181 <start>
925 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
926 l_doc_number,
927 l_doc_type,
928 l_doc_subtype,
929 l_release_num,
930 l_agent_id,
931 l_itemkey);
932 -- bug 3896181 <end>
933
934 -- Commit the changes so that the notifications will be able
935 -- to pickup the reminder notifications
936
937 COMMIT;
938
939 END IF; -- l_item_exist = 'Y'
940 END IF; -- PO_acceptance%FOUND
941
942 EXIT WHEN PO_acceptance%NOTFOUND;
943
944 END LOOP;
945
946 CLOSE PO_acceptance;
947
948
949 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 900 - END ';
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
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_po_acceptance','No data found');
957 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 901 - ' ||
958 'EXCEPTION - no data found sql error: ' || sqlcode;
959 IF (g_po_wf_debug = 'Y') THEN
960 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
961 END IF;
962
963 WHEN OTHERS THEN
964 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_po_acceptance','SQL error ' || sqlcode);
965 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_po_acceptance: 990 - ' ||
966 'EXCEPTION - sql error: ' || sqlcode;
967 IF (g_po_wf_debug = 'Y') THEN
968 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
969 END IF;
970
971 RAISE;
972
973
974
975 END Process_po_acceptance;
976
977
978 /*===========================================================================
979 PROCEDURE NAME: Process_rel_acceptance
980
981 DESCRIPTION:
982 This procedure does the following:
983 - Search for Approved releases with acceptance required
984
985 - For each selected document, initiate the
986 PO Approval Reminder workflow notification
987
988 CHANGE HISTORY: WLAU 11/15/1997 Created
989 ===========================================================================*/
990
991 PROCEDURE Process_rel_acceptance IS
992
993 -- Define cursor for selecting approved releases with acceptance required
994
995 /* Bug# 1595348: kagarwal
996 ** Desc: If the PO/Rel is accepted using Web Supplier Portal, the acceptance
997 ** is registered in the PO Acceptances table. Hence we need to check the PO
998 ** Acceptances table also.
999 */
1000
1001 /* Bug# 2633688: kagarwal
1002 ** Desc: When accepting releases from ISP, the po header id is
1003 ** left null in the po acceptances table (See Bug 2188005) hence
1004 ** removing the condition 'poa.po_header_id = poh.po_header_id'
1005 */
1006
1007
1008 CURSOR REL_acceptance IS
1009 SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
1010 POH.Type_Lookup_Code, PORH.Agent_ID,
1014 WHERE NVL(PORH.authorization_status,'INCOMPLETE') = 'APPROVED'
1011 NVL(PORH.Acceptance_Due_Date, SYSDATE)
1012 FROM PO_RELEASES_ALL PORH, -- <R12 MOAC>
1013 PO_HEADERS POH
1015 AND NVL(PORH.acceptance_required_flag,'N')= 'Y'
1016 AND NVL(PORH.cancel_flag,'N') = 'N'
1017 AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
1018 AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID
1019 AND not exists (
1020 SELECT poa.ACCEPTANCE_ID
1021 FROM PO_ACCEPTANCES poa
1022 WHERE NVL(poa.accepted_flag, 'N') = 'Y'
1023 /* AND poa.po_header_id = poh.po_header_id */
1024 AND porh.po_release_id = poa.po_release_id
1025 AND nvl(poa.revision_num,0) = nvl(porh.revision_num,0));
1026
1027 l_doc_header_id NUMBER;
1028 l_agent_id NUMBER;
1029 l_doc_type VARCHAR2(25);
1030 l_doc_subtype VARCHAR2(25);
1031 l_doc_number VARCHAR2(20);
1032 l_release_num NUMBER := NULL;
1033 l_acceptance_due_date DATE;
1034
1035 l_ItemType VARCHAR2(100) := 'APVRMDER';
1036 l_itemkey VARCHAR2(100) := NULL;
1037
1038
1039 l_item_exist VARCHAR2(1);
1040 l_item_end_date DATE;
1041 l_progress VARCHAR2(300) := NULL;
1042
1043 l_message_name VARCHAR2(300) := NULL; --bug 3896181
1044 BEGIN
1045
1046 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 01 - BEGIN ';
1047 IF (g_po_wf_debug = 'Y') THEN
1048 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1049 END IF;
1050
1051 l_itemkey := '';
1052
1053 -- Select releases with acceptance required and initiate
1054 -- Approval reminder workflow
1055
1056 OPEN REL_acceptance ;
1057
1058 LOOP
1059
1060 FETCH REL_acceptance into l_doc_header_id,
1061 l_doc_number,
1062 l_release_num,
1063 l_doc_subtype,
1064 l_agent_id,
1065 l_acceptance_due_date;
1066
1067 l_doc_type := 'REL_ACCEPTANCE';
1068
1069 IF l_doc_subtype = 'PLANNED' THEN
1070 l_doc_subtype := 'SCHEDULED';
1071 END IF;
1072 --bug#3709971 modified the structure of item key from
1073 --doc_type||doc_subtype||doc_num(old structure) to
1074 --doc_type||'-'||doc_subtype||'-'||doc_num
1075
1076 l_itemkey := l_doc_type || '-'||
1077 l_doc_subtype ||'-'||
1078 to_char(l_doc_header_id);
1079
1080 /* Bug 3896181 fixed.
1081 Revamped the code for acceptances part to take care of multiple scenarios now.
1082 After the fix, if a reminder notification already exists, then APVRMDER will not
1083 send new notifications.
1084 If the acceptance_due_date is passed and a reminder notification exists, then
1085 this old notification is closed and a new 'past-due' notification is sent.
1086 Also, if at any time, acceptance_due_date is changed to old/new dates, sending
1087 of notifications is taken care appropriately.
1088 The below part fixes for Release acceptance notifications.
1089 */
1090
1091 IF REL_acceptance%FOUND THEN
1092
1093
1094 -- Call Workflow to check if the itemkey already exists
1095 PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
1096 l_ItemKey,
1097 l_Item_exist,
1098 l_Item_end_date);
1099
1100 IF l_item_exist = 'Y' THEN
1101
1102 --bug 3896181 <start>
1103 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 02 - item key exists ';
1104 IF (g_po_wf_debug = 'Y') THEN
1105 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1106 END IF;
1107 IF trunc(l_acceptance_due_date) > trunc(sysdate) THEN
1108 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 03 - acc_due_date > sysdate ';
1109 IF (g_po_wf_debug = 'Y') THEN
1110 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1111 END IF;
1112 IF l_Item_end_date is not null then
1113 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 04 - end_date not null';
1114 IF (g_po_wf_debug = 'Y') THEN
1115 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1116 END IF;
1117 --bug 3896181 <end>
1118
1119 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1120 l_doc_header_id,
1121 NULL);
1122 --bug 3896181 <start>
1123 WF_PURGE.ITEMS (l_ItemType,
1124 l_itemkey,
1125 SYSDATE,
1126 true,
1127 true);
1128 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1129 l_doc_number,
1130 l_doc_type,
1131 l_doc_subtype,
1135 COMMIT;
1132 l_release_num,
1133 l_agent_id,
1134 l_itemkey);
1136 ELSE -- l_Item_end_date is not null
1137 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 05 - end_date is null';
1138 IF (g_po_wf_debug = 'Y') THEN
1139 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1140 --bug 3896181 <end>
1141 END IF;
1142
1143 --bug 3896181 <start>
1144 begin
1145 select wfn.MESSAGE_NAME
1146 into l_message_name
1147 from wf_item_activity_statuses wias, wf_notifications wfn
1148 where wias.notification_id = wfn.group_id
1149 and wias.item_type = 'APVRMDER'
1150 and wias.item_key = l_ItemKey ;
1151 exception
1152 when others then
1153 NULL;
1154 end ;
1155 IF l_message_name = 'REL_ACCEPTANCE_PAST_DUE' then
1156 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 06 - REL_ACCEPTANCE_PAST_DUE';
1157 --bug 3896181 <end>
1158 IF (g_po_wf_debug = 'Y') THEN
1159 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1160 END IF;
1161 --bug 3896181 <start>
1162 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1163 l_doc_header_id,
1164 NULL);
1165 --bug 3896181 <end>
1166
1167 --<BUG 3351588>
1168 --Force item purge even if an active child process exists.
1169 WF_PURGE.ITEMS (itemtype => l_ItemType,
1170 itemkey => l_Itemkey,
1171 enddate => SYSDATE,
1172 docommit => true, --<BUG 3351588>
1173 force => true); --<BUG 3351588>
1174
1175 --bug 3896181 <start>
1176 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1177 l_doc_number,
1178 l_doc_type,
1179 l_doc_subtype,
1180 l_release_num,
1181 l_agent_id,
1182 l_itemkey);
1183 COMMIT;
1184 END IF; -- l_message_name = 'REL_ACCEPTANCE_PAST_DUE'
1185 END IF; -- l_Item_end_date is not null
1186 ELSE -- trunc(l_acceptance_due_date) > trunc(sysdate)
1187 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 07 - acc_due_date < sysdate';
1188 --bug 3896181 <end>
1189 IF (g_po_wf_debug = 'Y') THEN
1190 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1191 END IF;
1192
1193 --bug 3896181 <end>
1194 IF l_Item_end_date is not NULL THEN
1195 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 08 - end_date is not null';
1196 IF (g_po_wf_debug = 'Y') THEN
1197 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1198 END IF;
1199 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1200 l_doc_header_id,
1201 NULL);
1202 WF_PURGE.ITEMS (l_ItemType,
1203 l_itemkey,
1204 SYSDATE,
1205 true,
1206 true);
1207
1208 --bug 3896181 <end>
1209
1210 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1211 l_doc_number,
1212 l_doc_type,
1213 l_doc_subtype,
1214 l_release_num,
1215 l_agent_id,
1216 l_itemkey);
1217
1218 -- Commit the changes so that the notifications will be able
1219 -- to pickup the reminder notifications
1220
1221 COMMIT;
1222 --bug 3896181 <start>
1223 ELSE -- l_Item_end_date is not NULL
1224 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 09 - end_date is null';
1225 IF (g_po_wf_debug = 'Y') THEN
1226 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1227 --bug 3896181 <end>
1228 END IF;
1229
1230 --bug 3896181 <start>
1231 begin
1232 select wfn.MESSAGE_NAME
1233 into l_message_name
1234 from wf_item_activity_statuses wias, wf_notifications wfn
1235 where wias.notification_id = wfn.group_id
1239 when others then
1236 and wias.item_type = 'APVRMDER'
1237 and wias.item_key = l_ItemKey ;
1238 exception
1240 NULL;
1241 end ;
1242 If l_message_name = 'REL_ACCEPTANCE_REQUIRED' then
1243 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 10 - REL_ACCEPTANCE_REQUIRED';
1244 IF (g_po_wf_debug = 'Y') THEN
1245 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1246 END IF;
1247 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_doc_type,
1248 l_doc_header_id,
1249 NULL);
1250 WF_PURGE.ITEMS (l_ItemType,
1251 l_itemkey,
1252 SYSDATE,
1253 true,
1254 true);
1255 --bug 3896181 <end>
1256 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1257 l_doc_number,
1258 l_doc_type,
1259 l_doc_subtype,
1260 l_release_num,
1261 l_agent_id,
1262 l_itemkey);
1263 --bug 3896181 <start>
1264 COMMIT;
1265 END IF; -- l_message_name = 'REL_ACCEPTANCE_REQUIRED'
1266 END IF; -- l_Item_end_date is not NULL
1267 END IF; -- trunc(l_acceptance_due_date) > trunc(sysdate)
1268 ELSE -- l_item_exist = 'Y'
1269 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 11 - l_item_exists is N';
1270 --bug 3896181 <end>
1271 IF (g_po_wf_debug = 'Y') THEN
1272 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1273 END IF;
1274 --bug 3896181 <start>
1275 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1276 l_doc_number,
1277 l_doc_type,
1278 l_doc_subtype,
1279 l_release_num,
1280 l_agent_id,
1281 l_itemkey);
1282
1283 --bug 3896181 <end>
1284 -- Commit the changes so that the notifications will be able
1285 -- to pickup the reminder notifications
1286
1287 COMMIT;
1288
1289 END IF; -- l_item_exist = 'Y'
1290
1291 END IF; -- REL_acceptance%FOUND
1292
1293 EXIT WHEN REL_acceptance%NOTFOUND;
1294
1295 END LOOP;
1296
1297 CLOSE REL_acceptance;
1298
1299 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 900 - END ';
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
1304
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN
1307 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rel_acceptance','No data found');
1308 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 901 - ' ||
1309 'EXCEPTION - no data found sql error: ' || sqlcode;
1310 IF (g_po_wf_debug = 'Y') THEN
1311 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1312 END IF;
1313
1314 WHEN OTHERS THEN
1315 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rel_acceptance','SQL error ' || sqlcode);
1316 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rel_acceptance: 990 - ' ||
1317 'EXCEPTION - sql error: ' || sqlcode;
1318 IF (g_po_wf_debug = 'Y') THEN
1319 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1320 END IF;
1321
1322
1323 RAISE;
1324
1325
1326
1327 END Process_rel_acceptance;
1328
1329
1330 /*===========================================================================
1331 PROCEDURE NAME: Process_rfq_quote
1332
1333 DESCRIPTION:
1334 This procedure does the following:
1335 - Open cursor PO_HEADERS table to select
1336 RFQ and Quote documents.
1337
1338 - For each selected document, initiate the
1339 PO Approval Reminder workflow notification.
1340
1341 CHANGE HISTORY: WLAU 11/15/1997 Created
1342 ===========================================================================*/
1343
1344 PROCEDURE Process_rfq_quote IS
1345
1346 -- Define cursor for selecting RFQ and Quote documents to start the Purchasing
1347 -- Approval Reminder workflow process.
1348 --
1349 /* Bug# 1541123: kagarwal
1350 ** Desc: If the End_date for RFQ or Quotation is null
1351 ** then it means that the RFQ or Quote does not expire
1352 ** hence we need to change the nvl value of End_date
1356 */
1353 ** to SYSDATE + 1.
1354 **
1355 ** Also changing for Reply_date and RFQ_close_date
1357 /* Bug# 1764388: kagarwal
1358 ** Desc: If the End_date for Quotation is null then it means that the Quote
1359 ** does not expire. In this case we should not consider the Quote_warning_delay
1360 ** in the CURSOR RFQ_QUOTE.
1361 */
1362
1363 CURSOR RFQ_QUOTE IS
1364 SELECT PO_Header_ID,
1365 Segment1,
1366 Type_Lookup_Code,
1367 Quote_type_lookup_code,
1368 Agent_id,
1369 Status_lookup_code,
1370 NVL(Reply_date,SYSDATE + 1),
1371 NVL(RFQ_close_date,SYSDATE + 1),
1372 NVL(End_date,SYSDATE + 1),
1373 decode(End_date, NULL, 0, NVL(Quote_warning_delay,0))
1374 Quote_warning_delay
1375 FROM PO_HEADERS
1376 WHERE NVL(Status_lookup_code,'I') IN ('I','A')
1377 AND type_lookup_code in ('RFQ','QUOTATION');
1378
1379
1380 l_doc_header_id NUMBER;
1381 l_agent_id NUMBER;
1382 l_doc_type VARCHAR2(25);
1383 l_doc_subtype VARCHAR2(25);
1384 l_doc_number VARCHAR2(25);
1385 l_release_num NUMBER := NULL;
1386 l_status_lookup_code VARCHAR2(20);
1387 l_rfq_reply_date DATE;
1388 l_rfq_close_date DATE;
1389 l_quote_end_date_active DATE;
1390 l_quote_end_date_temp DATE;
1391 l_quote_warning_delay NUMBER;
1392
1393 l_ItemType VARCHAR2(100) := 'APVRMDER';
1394 l_itemkey VARCHAR2(100) := NULL;
1395
1396 l_item_exist VARCHAR2(1);
1397 l_item_end_date DATE;
1398 l_start_ntfn_wf_ok VARCHAR2(1);
1399
1400 l_progress VARCHAR2(300) := NULL;
1401
1402 BEGIN
1403
1404 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 01 - BEGIN ';
1405 IF (g_po_wf_debug = 'Y') THEN
1406 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1407 END IF;
1408
1409 l_itemkey := ' ';
1410
1411 -- Select unapproved po documents and initiate
1412 -- Approval reminder workflow
1413
1414 OPEN RFQ_QUOTE;
1415
1416 LOOP
1417
1418 FETCH RFQ_QUOTE into l_doc_header_id,
1419 l_doc_number,
1420 l_doc_type,
1421 l_doc_subtype,
1422 l_agent_id,
1423 l_status_lookup_code,
1424 l_rfq_reply_date,
1425 l_rfq_close_date,
1426 l_quote_end_date_active,
1427 l_quote_warning_delay;
1428
1429
1430 -- Construct itemkey
1431
1432 l_itemkey := l_doc_type ||
1433 to_char(l_doc_header_id);
1434
1435
1436 -- Decide if a RFQ/Quotation notification workflow should be started
1437
1438 l_start_ntfn_wf_ok := 'N';
1439
1440 IF l_status_lookup_code = 'I' THEN
1441
1442 -- always start notification workflow if status is In_process
1443 l_start_ntfn_wf_ok := 'Y';
1444
1445 ELSE
1446
1447 IF l_doc_type = 'RFQ' THEN
1448
1449 -- check for RFQ date range
1450 IF to_date(to_char(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY') >=
1451 to_date(to_char(l_rfq_reply_date,'DD/MM/YYYY'),'DD/MM/YYYY') AND
1452 to_date(to_char(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY') <=
1453 to_date(to_char(l_RFQ_close_date,'DD/MM/YYYY'),'DD/MM/YYYY') THEN
1454 l_start_ntfn_wf_ok := 'Y';
1455
1456 END IF;
1457
1458
1459
1460 ELSIF l_doc_type = 'QUOTATION' THEN
1461
1462
1463 l_quote_end_date_temp := l_quote_end_date_active - l_quote_warning_delay;
1464
1465
1466 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - sysdate : '||
1467 to_char(SYSDATE,'DD/MM/YYYY');
1468 --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1469
1470
1471 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote end date active : '||
1472 to_char(l_quote_end_date_active,'DD/MM/YYYY');
1473 --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1474
1475 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote warning delay : '||
1476 l_quote_warning_delay;
1477 --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1478
1479
1480 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02 - quote end date temp : '||
1481 to_char(l_quote_end_date_temp,'DD/MM/YYYY');
1482 --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1483
1484
1485 -- check for Quotation date range
1486 IF to_date(to_char(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY') <=
1487 to_date(to_char(l_quote_end_date_active,'DD/MM/YYYY'),'DD/MM/YYYY') AND
1488 to_date(to_char(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY') >=
1489 to_date(to_char(l_quote_end_date_temp,'DD/MM/YYYY'),'DD/MM/YYYY') THEN
1490
1491 l_start_ntfn_wf_ok := 'Y';
1492
1493 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 02-set l_start_ntfn_wf_ok: '||
1494 l_start_ntfn_wf_ok;
1495 --/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1499 ELSE
1496
1497 END IF;
1498
1500
1501 l_start_ntfn_wf_ok := 'N';
1502
1503 END IF;
1504
1505 END IF;
1506
1507
1508 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 03 - l_start_ntfn_wf_ok: '||
1509 l_start_ntfn_wf_ok;
1510 IF (g_po_wf_debug = 'Y') THEN
1511 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1512 END IF;
1513
1514
1515 IF l_start_ntfn_wf_ok = 'Y' THEN
1516
1517 -- RFQ/Quotation notification workflow needs to be started
1518
1519 IF RFQ_QUOTE%FOUND THEN
1520
1521 -- Call Workflow to check if the itemkey already exists
1522 PO_APPROVAL_REMINDER_SV.item_exist (l_ItemType,
1523 l_ItemKey,
1524 l_Item_exist,
1525 l_Item_end_date);
1526
1527 IF l_item_exist = 'Y' THEN
1528
1529 -- Workflow item exists and is still opened
1530 -- Bypass this one
1531
1532 NULL;
1533
1534 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 05 ' ||
1535 'open WF item key exists ' ||l_itemkey;
1536 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1537
1538 ELSE
1539 -- Workflow item does not exist
1540 -- Invoke the Start_Approval_Reminder workflow
1541 -- for every unique workflow Item key.
1542
1543 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 10 ' ||
1544 'Start WF item key =' ||l_itemkey;
1545 IF (g_po_wf_debug = 'Y') THEN
1546 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1547 END IF;
1548
1549 PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder (l_doc_header_id,
1550 l_doc_number,
1551 l_doc_type,
1552 l_doc_subtype,
1553 l_release_num,
1554 l_agent_id,
1555 l_itemkey);
1556
1557
1558 -- Commit the changes so that the notifications will be able
1559 -- to pickup the reminder notifications
1560
1561 COMMIT;
1562
1563 END IF;
1564
1565 END IF;
1566
1567 END IF;
1568
1569
1570
1571 EXIT WHEN RFQ_QUOTE%NOTFOUND;
1572
1573 END LOOP;
1574
1575 CLOSE RFQ_QUOTE;
1576
1577
1578 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 900 - END ';
1579 IF (g_po_wf_debug = 'Y') THEN
1580 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1581 END IF;
1582
1583
1584 EXCEPTION
1585 WHEN NO_DATA_FOUND THEN
1586 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rfq_quote','No data found');
1587 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 901 - ' ||
1588 'EXCEPTION - no data found sql error: ' || sqlcode;
1589 IF (g_po_wf_debug = 'Y') THEN
1590 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1591 END IF;
1592
1593 WHEN OTHERS THEN
1594 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_rfq_quote','SQL error ' || sqlcode);
1595 l_progress := 'PO_APPROVAL_REMINDER_SV.Process_rfq_quote: 990 - ' ||
1596 'EXCEPTION - sql error: ' || sqlcode;
1597 IF (g_po_wf_debug = 'Y') THEN
1598 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1599 END IF;
1600
1601 RAISE;
1602
1603
1604
1605 END Process_rfq_quote;
1606
1607
1608
1609 /*===========================================================================
1610 PROCEDURE NAME: Start_Approval_Reminder
1611
1612 DESCRIPTION: This procedure creates and starts the Approval Reminder
1613 workflow process.
1614
1615 CHANGE HISTORY: WLAU 7/15/1997 Created
1616 ===========================================================================*/
1617
1618 PROCEDURE Start_Approval_Reminder (p_doc_header_id IN NUMBER,
1619 p_doc_number IN VARCHAR2,
1620 p_doc_type IN VARCHAR2,
1621 p_doc_subtype IN VARCHAR2,
1622 p_release_num IN NUMBER,
1623 p_agent_id IN NUMBER,
1624 p_WF_ItemKey IN VARCHAR2) IS
1625
1626
1627 l_ItemType VARCHAR2(100) := 'APVRMDER';
1628 l_ItemKey VARCHAR2(100) := p_WF_ItemKey;
1629
1630 l_agent_username VARCHAR2(240);
1631 l_agent_disp_name VARCHAR2(240);
1632 l_responsibility_id NUMBER;
1633 l_application_id NUMBER;
1634 l_progress VARCHAR2(300) := NULL;
1635
1636 BEGIN
1637
1638 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 01 ';
1639 IF (g_po_wf_debug = 'Y') THEN
1643 fnd_profile.get('RESP_ID', l_responsibility_id);
1640 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1641 END IF;
1642
1644 fnd_profile.get('RESP_APPL_ID', l_application_id);
1645 wf_engine.createProcess ( ItemType => l_ItemType,
1646 ItemKey => l_ItemKey,
1647 process => 'PO_APPROVAL_REMINDER' );
1648
1649 -- bug 852056: Need to create RESP_ID and APPLICATION_ID
1650 wf_engine.AddItemAttr( itemtype => l_itemtype,
1651 itemkey => l_itemkey,
1652 aname => 'RESP_ID');
1653
1654 wf_engine.AddItemAttr( itemtype => l_itemtype,
1655 itemkey => l_itemkey,
1656 aname => 'APPLICATION_ID');
1657
1658 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
1659 itemkey => l_itemkey,
1660 aname => 'RESP_ID',
1661 avalue => l_responsibility_id );
1662
1663 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
1664 itemkey => l_itemkey,
1665 aname => 'APPLICATION_ID',
1666 avalue => l_application_id );
1667
1668 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
1669 itemkey => l_itemkey,
1670 aname => 'DOCUMENT_ID',
1671 avalue => p_doc_header_id );
1672
1673 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1674 itemkey => l_itemkey,
1675 aname => 'DOCUMENT_NUMBER',
1676 avalue => p_doc_number );
1677
1678 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1679 itemkey => l_itemkey,
1680 aname => 'DOCUMENT_TYPE',
1681 avalue => p_doc_type );
1682
1683 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1684 itemkey => l_itemkey,
1685 aname => 'DOCUMENT_SUBTYPE',
1686 avalue => p_doc_subtype );
1687
1688 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
1689 itemkey => l_itemkey,
1690 aname => 'RELEASE_REV_NUM',
1691 avalue => p_release_num );
1692
1693
1694 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
1695 itemkey => l_itemkey,
1696 aname => 'AGENT_ID',
1697 avalue => p_agent_id );
1698
1699
1700 /*** DEBUG
1701 wf_directory.GetUserName ( p_orig_system => 'PER',
1702 p_orig_system_id => p_agent_id,
1703 p_name => l_agent_username,
1704 p_display_name => l_agent_disp_name);
1705 ***/
1706
1707
1708 PO_REQAPPROVAL_INIT1.Get_User_Name (p_agent_id,
1709 l_agent_username,
1710 l_agent_disp_name);
1711
1712 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1713 itemkey => l_itemkey,
1714 aname => 'AGENT_USER_NAME',
1715 avalue => l_agent_username );
1716
1717 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1718 itemkey => l_itemkey,
1719 aname => 'AGENT_DISP_NAME',
1720 avalue => l_agent_disp_name );
1721
1722 -- dbms_output.put_line ('Start_Approval_Reminder, agent username '|| l_agent_username);
1723
1724 -- dbms_output.put_line ('Start_Approval_Reminder, agent dispname '|| l_agent_disp_name);
1725 wf_engine.StartProcess ( ItemType => l_ItemType,
1726 ItemKey => l_ItemKey );
1727
1728 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 900 ';
1729 IF (g_po_wf_debug = 'Y') THEN
1730 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1731 END IF;
1732
1733
1734 EXCEPTION
1735
1736 WHEN OTHERS THEN
1737 wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Approval_Reminder','SQL error ' || sqlcode);
1738 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Approval_Reminder: 990 - ' ||
1739 'EXCEPTION - sql error: ' || sqlcode;
1740 IF (g_po_wf_debug = 'Y') THEN
1741 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
1742 END IF;
1743
1744
1745 RAISE;
1746
1747
1748
1749 END Start_Approval_Reminder;
1750
1751
1752
1753 /*===========================================================================
1754 PROCEDURE NAME: Set Doc Type
1755
1756 DESCRIPTION:
1757
1758
1759 CHANGE HISTORY: WLAU 7/15/1997 Created
1763 PROCEDURE Set_Doc_Type ( itemtype in varchar2,
1760 ===========================================================================*/
1761
1762
1764 itemkey in varchar2,
1765 actid in number,
1766 funmode in varchar2,
1767 result out NOCOPY varchar2 ) IS
1768
1769
1770 l_doc_header_id NUMBER;
1771 l_doc_type VARCHAR2(30);
1772 l_doc_type_temp VARCHAR2(30);
1773 l_doc_subtype VARCHAR2(30);
1774 l_doc_type_lookup_code VARCHAR2(30);
1775 l_doc_type_name VARCHAR2(80);
1776 l_error_msg Varchar2(500);
1777 l_req_status Varchar2(25);
1778
1779 l_can_change_forward_from_flag VARCHAR2(25);
1780 l_can_change_forward_to_flag VARCHAR2(25);
1781 l_can_change_approval_path VARCHAR2(25);
1782 l_default_approval_path_id NUMBER;
1783 l_can_preparer_approve_flag VARCHAR2(25);
1784 l_can_approver_modify_flag VARCHAR2(25);
1785
1786 l_acceptance_past_due VARCHAR2(25);
1787 l_acceptance_due_date DATE;
1788 l_rfq_reply_date DATE;
1789 l_rfq_close_date DATE;
1790 l_quote_end_date_active DATE;
1791 l_quote_warning_delay NUMBER;
1792 l_status_lookup_code VARCHAR2(25);
1793 l_quote_lookup_code_type VARCHAR2(25);
1794 l_quote_type_lookup_code VARCHAR2(25);
1795 l_quote_type_disp VARCHAR2(80);
1796 l_progress VARCHAR2(300) := NULL;
1797
1798 BEGIN
1799
1800 l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 01 ';
1801 IF (g_po_wf_debug = 'Y') THEN
1802 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1803 END IF;
1804
1805
1806 IF ( funmode = 'RUN' ) THEN
1807 --
1808 -- dbms_output.put_line ('Set doc type ' ||ItemKey);
1809
1810
1811 l_doc_header_id :=
1812 wf_engine.GetItemAttrNumber ( itemtype => ItemType,
1813 itemkey => itemkey,
1814 aname => 'DOCUMENT_ID');
1815
1816 l_doc_type :=
1817 wf_engine.GetItemAttrText ( itemtype => itemtype,
1818 itemkey => itemkey,
1819 aname => 'DOCUMENT_TYPE');
1820 l_doc_subtype :=
1821 wf_engine.GetItemAttrText ( itemtype => itemtype,
1822 itemkey => itemkey,
1823 aname => 'DOCUMENT_SUBTYPE');
1824
1825
1826 -- Set l_doc_type_temp to be the same as l_doc_type
1827 -- DO NOT modify the l_doc_type
1828
1829 l_doc_type_temp := l_doc_type;
1830
1831 IF l_doc_type_temp = 'PO_ACCEPTANCE' THEN
1832
1833 -- temporary set l_doc_type to 'PO','PA' for acceptance WF
1834
1835 IF l_doc_subtype IN ('STANDARD','PLANNED') THEN
1836 l_doc_type_temp := 'PO';
1837 ELSIF l_doc_subtype IN ('BLANKET','CONTRACT') THEN
1838 l_doc_type_temp := 'PA';
1839 END IF;
1840
1841 ELSIF l_doc_type_temp = 'REL_ACCEPTANCE' THEN
1842
1843 l_doc_type_temp := 'RELEASE';
1844
1845 END IF;
1846
1847 -- Get doc type display name
1848 --
1849 l_doc_type_name := '';
1850 l_doc_type_lookup_code := '';
1851
1852 PO_HEADERS_SV4.get_doc_type_lookup_code
1853 (l_doc_type_temp,
1854 l_doc_subtype,
1855 l_doc_type_name,
1856 l_doc_type_lookup_code);
1857
1858
1859 wf_engine.SetItemAttrText ( itemtype => itemType,
1860 itemkey => itemkey,
1861 aname => 'DOC_TYPE_DISP' ,
1862 avalue => l_doc_type_name);
1863
1864
1865 IF l_doc_type IN ('PO','PA','RELEASE','REQUISITION') THEN
1866 -- Bug 6082842; Setting the NEVER_APPROVED_MSG attribute text to REQUIRES_APPROVAL_MSG text, since never approved msg is wrong.
1867 -- Get has never been approved message
1868 --fnd_message.set_name ('PO','PO_WF_NOTIF_NEVER_APPROVED');
1869 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
1870
1871 l_error_msg := fnd_message.get;
1872
1873 wf_engine.SetItemAttrText ( itemtype => itemType,
1874 itemkey => itemkey,
1875 aname => 'NEVER_APPROVED_MSG' ,
1876 avalue => l_error_msg);
1877
1878
1879 -- Get requires approval message
1880 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
1881 l_error_msg := fnd_message.get;
1882
1883 wf_engine.SetItemAttrText ( itemtype => itemType,
1884 itemkey => itemkey,
1885 aname => 'REQUIRES_APPROVAL_MSG' ,
1886 avalue => l_error_msg);
1887
1888 -- Bug : 714491
1889
1890 If l_doc_type = 'REQUISITION' then
1891
1895
1892 select authorization_status into l_req_status
1893 from po_requisition_headers
1894 where requisition_header_id = l_doc_header_id;
1896 if NVL(l_req_status, 'INCOMPLETE') = 'RETURNED' then
1897
1898 -- Get requisition returned approval message
1899 fnd_message.set_name ('PO','PO_WF_NOTIF_RETURNED');
1900 l_error_msg := fnd_message.get;
1901
1902 wf_engine.SetItemAttrText ( itemtype => itemType,
1903 itemkey => itemkey,
1904 aname => 'REQUISITION_RETURNED_MSG' ,
1905 avalue => l_error_msg);
1906
1907 elsif NVL(l_req_status, 'INCOMPLETE') = 'REJECTED' then
1908
1909 -- Get requisition returned approval message
1910 fnd_message.set_name ('PO','PO_WF_NOTIF_REJECTED');
1911 l_error_msg := fnd_message.get;
1912
1913 wf_engine.SetItemAttrText ( itemtype => itemType,
1914 itemkey => itemkey,
1915 aname => 'REQUISITION_REJECTED_MSG' ,
1916 avalue => l_error_msg);
1917 end if;
1918
1919
1920 -- Bug6082842
1921 -- Commenting out following piece of code. NEVER_APPROVED_MSG has been set
1922 -- with call to : PO_WF_PO_NOTIFICATION.GetDisplayValue above.
1923 -- And REQUIRES_APPROVAL_MSG is also taken care off above before we
1924 -- check if l_doc_type is REQUISITION.
1925
1926 /*if NVL(l_req_status, 'INCOMPLETE') in ('RETURNED','REJECTED') then
1927
1928 wf_engine.SetItemAttrText ( itemtype => itemType,
1929 itemkey => itemkey,
1930 aname => 'NEVER_APPROVED_MSG' ,
1931 avalue => '');
1932
1933 wf_engine.SetItemAttrText ( itemtype => itemType,
1934 itemkey => itemkey,
1935 aname => 'REQUIRES_APPROVAL_MSG' ,
1936 avalue => '');
1937 end if;*/
1938
1939 end if;
1940
1941
1942 -- Set wrong forward to message to NULL until user enters
1943 -- an invalid forward to ID
1944 l_error_msg := '';
1945
1946 wf_engine.SetItemAttrText ( itemtype => itemType,
1947 itemkey => itemkey,
1948 aname => 'WRONG_FORWARD_TO_MSG' ,
1949 avalue => '');
1950
1951 END IF;
1952
1953
1954 IF l_doc_type IN ('PO_ACCEPTANCE','REL_ACCEPTANCE') THEN
1955
1956
1957 IF l_doc_type = 'PO_ACCEPTANCE' THEN
1958
1959 SELECT NVL(acceptance_due_date,SYSDATE)
1960 INTO l_acceptance_due_date
1961 FROM PO_HEADERS
1962 WHERE po_header_id = l_doc_header_id;
1963 ELSE
1964
1965 SELECT NVL(acceptance_due_date, SYSDATE)
1966 INTO l_acceptance_due_date
1967 FROM PO_RELEASES
1968 WHERE po_release_id = l_doc_header_id;
1969 END IF;
1970
1971 IF l_acceptance_due_date is not NULL AND
1972 l_acceptance_due_date > SYSDATE THEN
1973
1974 -- Acceptance is still active
1975 l_acceptance_past_due := 'N';
1976
1977 ELSE
1978
1979 -- Acceptance is past due
1980 l_acceptance_past_due := 'Y';
1981
1982 END IF;
1983
1984 wf_engine.SetItemAttrDate ( itemtype => ItemType,
1985 itemkey => itemkey,
1986 aname => 'ACCEPTANCE_DUE_DATE',
1987 avalue => l_acceptance_due_date );
1988
1989
1990 wf_engine.SetItemAttrText ( itemtype => itemtype,
1991 itemkey => itemkey,
1992 aname => 'ACCEPTANCE_PAST_DUE',
1993 avalue => l_acceptance_past_due );
1994
1995
1996 ELSIF l_doc_type IN ('RFQ','QUOTATION') THEN
1997
1998 /* Bug 606396. Changed end_date_active to end_date */
1999
2000 SELECT NVL(Reply_date,SYSDATE),
2001 NVL(RFQ_close_date,SYSDATE),
2002 NVL(End_date,SYSDATE),
2003 NVL(Quote_warning_delay,0),
2004 Status_lookup_code,
2005 Quote_type_lookup_code
2006 INTO l_rfq_reply_date,
2007 l_rfq_close_date,
2008 l_quote_end_date_active,
2009 l_quote_warning_delay,
2010 l_status_lookup_code,
2011 l_quote_type_lookup_code
2012 FROM PO_HEADERS
2013 WHERE PO_HEADER_ID = l_doc_header_id;
2014
2015
2016 l_quote_lookup_code_type := l_doc_type || ' SUBTYPE';
2017 l_quote_type_disp := '';
2018
2019 PO_HEADERS_SV4.get_lookup_code_dsp
2020 (l_quote_lookup_code_type,
2021 l_quote_type_lookup_code,
2022 l_quote_type_disp);
2023
2024
2025 wf_engine.SetItemAttrDate ( itemtype => ItemType,
2029
2026 itemkey => itemkey,
2027 aname => 'RFQ_REPLY_DATE',
2028 avalue => l_rfq_reply_date );
2030
2031 wf_engine.SetItemAttrDate ( itemtype => ItemType,
2032 itemkey => itemkey,
2033 aname => 'RFQ_CLOSE_DATE',
2034 avalue => l_RFQ_close_date );
2035
2036
2037 wf_engine.SetItemAttrDate ( itemtype => ItemType,
2038 itemkey => itemkey,
2039 aname => 'QUOTE_END_DATE_ACTIVE',
2040 avalue => l_quote_end_date_active );
2041
2042
2043 wf_engine.SetItemAttrNumber ( itemtype => ItemType,
2044 itemkey => itemkey,
2045 aname => 'QUOTE_WARNING_DELAY',
2046 avalue => l_quote_warning_delay );
2047
2048 wf_engine.SetItemAttrText ( itemtype => ItemType,
2049 itemkey => Itemkey,
2050 aname => 'RFQ_STATUS',
2051 avalue => l_status_lookup_code );
2052
2053 wf_engine.SetItemAttrText ( itemtype => ItemType,
2054 itemkey => Itemkey,
2055 aname => 'QUOTE_STATUS',
2056 avalue => l_status_lookup_code );
2057
2058
2059 wf_engine.SetItemAttrText ( itemtype => ItemType,
2060 itemkey => Itemkey,
2061 aname => 'QUOTE_TYPE_DISP',
2062 avalue => l_quote_type_disp );
2063
2064
2065 END IF;
2066
2067
2068 -- Set RESULT type
2069
2070 RESULT := l_doc_type;
2071
2072 l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 05 RESULT ' ||
2073 l_doc_type;
2074 IF (g_po_wf_debug = 'Y') THEN
2075 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2076 END IF;
2077
2078
2079 ELSIF ( funmode = 'CANCEL' ) THEN
2080 --
2081 null;
2082 --
2083 END IF;
2084
2085
2086 l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 900 ';
2087 IF (g_po_wf_debug = 'Y') THEN
2088 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2089 END IF;
2090
2091
2092 EXCEPTION
2093 WHEN NO_DATA_FOUND THEN
2094 wf_core.context ('PO_APPROVAL_REMINDER_SV','Set_Doc_Type','No data found');
2095 l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 901 - ' ||
2096 'EXCEPTION - no data found sql error: ' || sqlcode;
2097 IF (g_po_wf_debug = 'Y') THEN
2098 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2099 END IF;
2100
2101 WHEN OTHERS THEN
2102 wf_core.context ('PO_APPROVAL_REMINDER_SV','Set_Doc_Type','SQL error ' || sqlcode);
2103 l_progress := 'PO_APPROVAL_REMINDER_SV.Set_Doc_Type: 990 - ' ||
2104 'EXCEPTION - sql error: ' || sqlcode;
2105 IF (g_po_wf_debug = 'Y') THEN
2106 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2107 END IF;
2108
2109
2110 RAISE;
2111
2112
2113 END Set_Doc_Type;
2114
2115
2116 /*===========================================================================
2117 PROCEDURE NAME: Start Document Approval Workflow
2118
2119 DESCRIPTION:
2120
2121
2122 CHANGE HISTORY: WLAU 7/15/1997 Created
2123 ===========================================================================*/
2124
2125
2126 PROCEDURE Start_Doc_Approval ( itemtype in varchar2,
2127 itemkey in varchar2,
2128 actid in number,
2129 funmode in varchar2,
2130 result out NOCOPY varchar2 ) IS
2131
2132
2133 l_ActionOriginatedFrom VARCHAR2(30):= 'REMIND_NOTIF';
2134 l_PreparerID NUMBER;
2135 l_ResponsibilityID NUMBER;
2136 l_ApplicationID NUMBER;
2137 l_DocumentNumber VARCHAR2(60);
2138 l_DocumentID NUMBER;
2139 l_DocumentTypeCode VARCHAR2(30);
2140 l_DocumentSubtype VARCHAR2(60);
2141 l_DocumentStatus VARCHAR2(60):= NULL;
2142 l_RequestorAction VARCHAR2(60):= 'APPROVE';
2143 l_forwardToID NUMBER := NULL;
2144 l_forwardFromID NUMBER := NULL;
2145 l_DefaultApprovalPathID NUMBER := NULL;
2146 l_Note VARCHAR2(240);
2147 l_progress VARCHAR2(300) := NULL;
2148
2149 BEGIN
2150
2151 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 01 ';
2152 IF (g_po_wf_debug = 'Y') THEN
2153 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2154 END IF;
2158 --
2155
2156
2157 IF ( funmode = 'RUN' ) THEN
2159
2160 -- dbms_output.put_line ('Start Doc Approval ' ||ItemKey);
2161
2162 -- bug 852056: While responding to notification through the WEB
2163 -- user context is lost. So need pass responsibility_id and
2164 -- application_id through workflow attributes.
2165
2166 l_ResponsibilityID :=
2167 wf_engine.GetItemAttrNumber (itemtype => itemtype,
2168 itemkey => itemkey,
2169 aname => 'RESP_ID');
2170
2171 l_ApplicationID :=
2172 wf_engine.GetItemAttrNumber (itemtype => itemtype,
2173 itemkey => itemkey,
2174 aname => 'APPLICATION_ID');
2175
2176 l_DocumentTypeCode :=
2177 wf_engine.GetItemAttrText ( itemtype => itemtype,
2178 itemkey => itemkey,
2179 aname => 'DOCUMENT_TYPE');
2180
2181 l_DocumentSubtype :=
2182 wf_engine.GetItemAttrText ( itemtype => itemtype,
2183 itemkey => itemkey,
2184 aname => 'DOCUMENT_SUBTYPE');
2185 l_DocumentNumber :=
2186 wf_engine.GetItemAttrText ( itemtype => itemtype,
2187 itemkey => itemkey,
2188 aname => 'DOCUMENT_NUMBER');
2189 l_DocumentID :=
2190 wf_engine.GetItemAttrNumber ( itemtype => ItemType,
2191 itemkey => itemkey,
2192 aname => 'DOCUMENT_ID');
2193
2194
2195 l_PreparerID :=
2196 wf_engine.GetItemAttrNumber ( itemtype => ItemType,
2197 itemkey => itemkey,
2198 aname => 'AGENT_ID');
2199
2200
2201 l_forwardToID :=
2202 wf_engine.GetItemAttrNumber ( itemtype => ItemType,
2203 itemkey => itemkey,
2204 aname => 'FORWARD_TO_ID');
2205
2206 l_forwardFromID :=
2207 wf_engine.GetItemAttrNumber ( itemtype => ItemType,
2208 itemkey => itemkey,
2209 aname => 'FORWARD_FROM_ID');
2210
2211
2212 l_Note :=
2213 wf_engine.GetItemAttrText ( itemtype => itemtype,
2214 itemkey => itemkey,
2215 aname => 'NOTE');
2216
2217
2218 /*** DEBUG
2219 l_DefaultApprovalPathID :=
2220 wf_engine.GetItemAttrNumber ( itemtype => ItemType,
2221 itemkey => itemkey,
2222 aname => 'DEFAULT_APPROVAL_PATH_ID');
2223 ***/
2224
2225
2226
2227 -- Submit the document for approval to the workflow, passing it the
2228 -- appropriate arguments.
2229 -- Note that there are different workflows for PO, Change Order, and
2230 -- Requisition. We call the appropriate one depending upon the document
2231 -- type.
2232
2233 -- Setting up common parameters for the call to WF.
2234
2235 -- ActionOriginatedFrom := 'REMIND_NOTIF';
2236
2237
2238 /*** DEBUG for future only
2239 IF l_DocumentTypeCode = 'REQUISITION' THEN
2240
2241 -- Setup Requisition approval request to WF.
2242
2243 ELSIF l_DocumentTypeCode IN ('PO', 'PA') THEN
2244
2245 -- Setup PO / CHANGE ORDER approval request to WF.
2246
2247 ELSIF l_DocumentTypeCode = 'RELEASE' THEN
2248
2249 -- Setup RELEASE approval request to WF.
2250
2251 ELSE
2252 RESULT := 'FAILED';
2253
2254 END IF;
2255 ***/
2256
2257 -- Submit to PO APPROVAL work flow.
2258
2259 SetUpWorkFlow ( l_ActionOriginatedFrom,
2260 l_DocumentID,
2261 l_DocumentNumber,
2262 l_PreparerID,
2263 l_ResponsibilityID,
2264 l_ApplicationID,
2265 l_DocumentTypeCode,
2266 l_DocumentSubtype,
2267 l_RequestorAction,
2268 l_ForwardToID,
2269 l_ForwardFromID,
2270 l_DefaultApprovalPathID,
2271 l_DocumentStatus,
2272 l_Note );
2273
2274
2275 --
2276 ELSIF ( funmode = 'CANCEL' ) THEN
2277 --
2278 null;
2279 --
2280 END IF;
2281
2282
2283 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 900 - END ';
2284 IF (g_po_wf_debug = 'Y') THEN
2285 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2286 END IF;
2287
2288
2289 EXCEPTION
2290 WHEN NO_DATA_FOUND THEN
2291 wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Doc_Approval','No data found');
2292 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 901 - ' ||
2293 'EXCEPTION - no data found sql error: ' || sqlcode;
2297
2294 IF (g_po_wf_debug = 'Y') THEN
2295 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2296 END IF;
2298
2299 WHEN OTHERS THEN
2300 wf_core.context ('PO_APPROVAL_REMINDER_SV','Start_Doc_Approval','SQL error ' || sqlcode);
2301 l_progress := 'PO_APPROVAL_REMINDER_SV.Start_Doc_Approval: 990 - ' ||
2302 'EXCEPTION - sql error: ' || sqlcode;
2303 IF (g_po_wf_debug = 'Y') THEN
2304 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2305 END IF;
2306
2307
2308 RAISE;
2309
2310
2311 END Start_Doc_Approval;
2312
2313
2314 /*===========================================================================
2315 PROCEDURE NAME: SetUpWorkFlow
2316
2317 DESCRIPTION:
2318
2319
2320 CHANGE HISTORY: WLAU 7/15/1997 Created
2321 ===========================================================================*/
2322
2323 PROCEDURE SetUpWorkFlow ( p_ActionOriginatedFrom IN varchar2,
2324 p_DocumentID IN number,
2325 p_DocumentNumber IN varchar2,
2326 p_PreparerID IN number,
2327 p_ResponsibilityID IN number,
2328 p_ApplicationID IN number,
2329 p_DocumentTypeCode IN varchar2,
2330 p_DocumentSubtype IN varchar2,
2331 p_RequestorAction IN varchar2,
2332 p_ForwardToID IN number ,
2333 p_ForwardFromID IN number,
2334 p_DefaultApprovalPathID IN number,
2335 p_DocumentStatus IN varchar2,
2336 p_Note IN varchar2) IS
2337
2338 l_seq VARCHAR2(10);
2339 l_ItemType VARCHAR2(8);
2340 l_ItemKey VARCHAR2(240) := NULL;
2341 l_WorkflowProcess VARCHAR2(80);
2342 l_orgid NUMBER;
2343 l_user_id NUMBER;
2344 l_progress VARCHAR2(300) := NULL;
2345
2346 /* Bug 2780033 */
2347 l_document_num po_headers_all.segment1%type;
2348 l_default_method PO_VENDOR_SITES.SUPPLIER_NOTIF_METHOD%TYPE := null;
2349 l_emailaddress po_vendor_sites.email_Address%type := null;
2350 l_faxnum varchar2(30) := null; --Bug 5765243
2351 l_emailflag varchar2(1) := 'N';
2352 l_faxflag varchar2(1) := 'N';
2353 l_printflag varchar2(1) := 'N';
2354 l_preparerid po_headers.agent_id%type;
2355
2356 Cursor get_user_id is
2357 select user_id
2358 from fnd_user
2359 where employee_id = p_PreparerID;
2360
2361 /* RETROACTIVE FPI START */
2362 l_can_change_forward_from_flag
2363 po_document_types.can_change_forward_from_flag%type;
2364 l_can_change_forward_to_flag po_document_types.can_change_forward_to_flag%type;
2365 l_can_change_approval_path po_document_types.can_change_approval_path_flag%type;
2366 l_can_preparer_approve_flag po_document_types.can_preparer_approve_flag%type;
2367 l_default_approval_path_id po_document_types.default_approval_path_id%type;
2368 l_can_approver_modify_flag po_document_types.can_approver_modify_doc_flag%type;
2369 l_forwarding_mode_code po_document_types.forwarding_mode_code%type;
2370 l_type_name po_document_types.type_name%type;
2371
2372 /* RETROACTIVE FPI END */
2373
2374 BEGIN
2375
2376 l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 01 ' ||
2377 p_DocumentTypeCode || p_DocumentSubtype || p_DocumentNumber;
2378 IF (g_po_wf_debug = 'Y') THEN
2379 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',NULL,l_progress);
2380 END IF;
2381
2382
2383 PO_REQAPPROVAL_INIT1.get_multiorg_context (p_DocumentTypeCode, p_DocumentID, l_orgid);
2384
2385 IF l_orgid is NOT NULL THEN
2386
2387 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
2388
2389 END IF;
2390
2391
2392 select to_char(PO_WF_ITEMKEY_S.nextval) into l_seq from sys.dual;
2393
2394 -- Bug 3240928 start
2395 -- The itemkey should be p_DocumentID - l_seq. If we use the colon the
2396 -- approval notification functions fail when doing the substr
2397 l_ItemKey := to_char(p_DocumentID) || '-' || l_seq;
2398 -- Bug 3240928 end
2399
2400 /* Bug# 1691814: kagarwal
2401 ** Desc: Changed PO_DOCUMENT_TYPES_V to PO_DOCUMENT_TYPES to improve
2402 ** the perfomance of the SQL.
2403 */
2404 /* Bug 2780033.
2405 * We need to get the default communication method and
2406 * send it to start_wf_process. We do this when we approve the
2407 * document from the approval window in the PO form. We need
2408 * to do the same from here also. Now we will get email or
2409 * document will get printed depending on the setup when we
2410 * we approve the document from the Notification Summary screen.
2411 */
2412 l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 02 ' ||
2413 p_DocumentTypeCode || p_DocumentSubtype ||
2414 p_DocumentNumber;
2415 IF (g_po_wf_debug = 'Y') THEN
2416 PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
2417 END IF;
2418
2419 PO_VENDOR_SITES_SV.Get_Transmission_Defaults(
2423 p_preparer_id => l_PreparerID,
2420 p_document_id => p_DocumentID,
2421 p_document_type => p_DocumentTypeCode,
2422 p_document_subtype => p_DocumentSubtype,
2424 x_default_method => l_default_method,
2425 x_email_address => l_emailaddress,
2426 x_fax_number => l_faxnum,
2427 x_document_num => l_document_num);
2428
2429 If (l_default_method = 'EMAIL' ) and (l_emailaddress is not null) then
2430 l_emailflag := 'Y';
2431 l_faxnum := null;
2432 elsif l_default_method = 'FAX' and (l_faxnum is not null) then
2433 l_emailaddress := null;
2434
2435 l_faxflag := 'Y';
2436 elsif l_default_method = 'PRINT' then
2437 l_emailaddress := null;
2438 l_faxnum := null;
2439
2440 l_printflag := 'Y';
2441 else
2442 l_emailaddress := null;
2443 l_faxnum := null;
2444 end if;
2445 /* Bug 2780033 End */
2446
2447
2448 /* RETROACTIVE FPI START.
2449 * Deleted the sql query which selected wf_approval_itemtype and
2450 * wf_approval_process from PO_DOCUMENT_TYPES. Instead call the
2451 * new overloaded procedure po_approve_sv.get_document_types.
2452 * We call this here even though this is called in start_wf_process
2453 * since we need these values to set some workflow attributes
2454 * before we call start_Wf_process.
2455 */
2456
2457 l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 03 ' ||
2458 p_DocumentTypeCode || p_DocumentSubtype ||
2459 p_DocumentNumber;
2460 IF (g_po_wf_debug = 'Y') THEN
2461 PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
2462 END IF;
2463 po_approve_sv.get_document_types(
2464 p_document_type_code => p_DocumentTypeCode,
2465 p_document_subtype => p_DocumentSubtype,
2466 x_can_change_forward_from_flag =>l_can_change_forward_from_flag,
2467 x_can_change_forward_to_flag => l_can_change_forward_to_flag,
2468 x_can_change_approval_path => l_can_change_approval_path,
2469 x_default_approval_path_id => l_default_approval_path_id,
2470 x_can_preparer_approve_flag => l_can_preparer_approve_flag,
2471 x_can_approver_modify_flag => l_can_approver_modify_flag,
2472 x_forwarding_mode_code => l_forwarding_mode_code,
2473 x_wf_approval_itemtype => l_itemtype,
2474 x_wf_approval_process => l_workflowprocess,
2475 x_type_name => l_type_name);
2476
2477 /* RETROACTIVE FPI END */
2478
2479
2480
2481 /* Bug#2531926: kagarwal
2482 ** Desc: The user id and not the prepaper id (employee id) should be
2483 ** populated in the USER_ID attribute of Approval workflow and also for setting
2484 ** apps context we should use the user id.
2485 **
2486 ** The prepaper id (employee id) is not the same as user id.
2487 */
2488
2489 /* User Id should not be null */
2490 open get_user_id;
2491 fetch get_user_id into l_user_id;
2492 close get_user_id;
2493
2494 -- bug 852056: Need to create process here, and then set
2495 -- attributes: USER_ID, RESPONSIBILITY_ID, and APPLICATION_ID
2496 -- also need to initialize fnd_global
2497 wf_engine.CreateProcess ( ItemType => l_ItemType,
2498 ItemKey => l_ItemKey,
2499 process => l_WorkflowProcess);
2500
2501 wf_engine.SetItemAttrNumber ( ItemType => l_ItemType,
2502 ItemKey => l_ItemKey,
2503 aname => 'USER_ID',
2504 avalue => l_user_id);
2505
2506 wf_engine.SetItemAttrNumber ( ItemType => l_ItemType,
2507 ItemKey => l_ItemKey,
2508 aname => 'RESPONSIBILITY_ID',
2509 avalue => p_ResponsibilityID);
2510
2511 wf_engine.SetItemAttrNumber ( ItemType => l_ItemType,
2512 ItemKey => l_ItemKey,
2513 aname => 'APPLICATION_ID',
2514 avalue => p_ApplicationID);
2515
2516 -- Bug 4290541, replaced apps init call with set doc mgr context
2517 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(l_itemtype, l_itemkey);
2518
2519 /* Bug 2780033.
2520 * Add the communication flags to the call to
2521 * start_wf_process below.
2522 */
2523 l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 04 ' ||
2524 p_DocumentTypeCode || p_DocumentSubtype ||
2525 p_DocumentNumber;
2526 IF (g_po_wf_debug = 'Y') THEN
2527 PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
2528 END IF;
2529 PO_REQAPPROVAL_INIT1.Start_WF_Process ( ItemType => l_ItemType,
2530 ItemKey => l_ItemKey,
2531 WorkflowProcess => l_WorkflowProcess,
2532 ActionOriginatedFrom => p_ActionOriginatedFrom,
2533 DocumentID => p_DocumentID,
2534 DocumentNumber => p_DocumentNumber,
2535 PreparerID => p_PreparerID,
2536 DocumentTypeCode => p_DocumentTypeCode,
2537 DocumentSubtype => p_DocumentSubtype,
2541 DefaultApprovalPathID => p_DefaultApprovalPathID,
2538 SubmitterAction => p_RequestorAction,
2539 forwardToID => p_forwardToID,
2540 forwardFromID => p_forwardFromID,
2542 Note => p_Note,
2543 PrintFlag => l_printflag,
2544 FaxFlag => l_faxflag,
2545 FaxNumber => l_faxnum,
2546 EmailFlag => l_emailflag,
2547 EmailAddress => l_emailaddress);
2548
2549
2550 l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 900 ' ||
2551 p_DocumentTypeCode || p_DocumentSubtype || p_DocumentNumber;
2552 IF (g_po_wf_debug = 'Y') THEN
2553 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
2554 END IF;
2555
2556
2557
2558 EXCEPTION
2559
2560 WHEN OTHERS THEN
2561 wf_core.context ('PO_APPROVAL_REMINDER_SV','SetUpWorkFlow','SQL error ' || sqlcode);
2562 l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 990 - ' ||
2563 'EXCEPTION - sql error: ' || sqlcode;
2564 IF (g_po_wf_debug = 'Y') THEN
2565 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
2566 END IF;
2567
2568 RAISE;
2569
2570
2571 END SetUpWorkFlow ; -- PROCEDURE SetUpWorkFlow
2572
2573 /*===========================================================================
2574 PROCEDURE NAME: Is_Forward_To_Valid
2575
2576 DESCRIPTION:
2577
2578
2579 CHANGE HISTORY: WLAU 8/20/1997 Created
2580 ===========================================================================*/
2581
2582 procedure Is_Forward_To_Valid( itemtype in varchar2,
2583 itemkey in varchar2,
2584 actid in number,
2585 funcmode in varchar2,
2586 resultout out NOCOPY varchar2 ) is
2587
2588 x_user_id number;
2589 l_approver_empid number;
2590 l_forward_to_username_response varchar2(100);
2591 l_forward_to_username varchar2(100);
2592 l_forward_to_username_disp varchar2(240);
2593 l_forward_to_id number;
2594 l_error_msg varchar2(500);
2595 x_progress varchar2(300);
2596
2597
2598 BEGIN
2599
2600 x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 01';
2601 IF (g_po_wf_debug = 'Y') THEN
2602 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2603 END IF;
2604
2605
2606 -- Do nothing in cancel or timeout mode
2607 --
2608 if (funcmode <> wf_engine.eng_run) then
2609
2610 resultout := wf_engine.eng_null;
2611 return;
2612
2613 end if;
2614
2615 /* Check that the value entered by responder as the FORWARD-TO user, is actually
2616 ** a valid employee (has an employee id).
2617 ** If valid, then set the FORWARD-FROM USERNAME and ID from the old FORWARD-TO.
2618 ** Then set the Forward-To to the one the user entered in the response.
2619 */
2620 /* NOTE: We take the value entered by the user and set it to ALL CAPITAL LETTERS!!!
2621 */
2622 l_forward_to_username_response := wf_engine.GetItemAttrText (itemtype => itemtype,
2623 itemkey => itemkey,
2624 aname => 'FORWARD_TO_USERNAME_RESPONSE');
2625
2626 l_forward_to_username_response := UPPER(l_forward_to_username_response);
2627
2628 x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 02';
2629 x_progress := x_progress || ' Forward-To=' || l_forward_to_username_response;
2630 IF (g_po_wf_debug = 'Y') THEN
2631 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2632 END IF;
2633
2634 IF l_forward_to_username_response is NULL THEN
2635
2636 -- NULL is a valid case in the remainder process
2637 --
2638 resultout := wf_engine.eng_completed || ':' || 'Y';
2639 --
2640
2641 ELSIF PO_REQAPPROVAL_FINDAPPRV1.CheckForwardTo(l_forward_to_username_response,
2642 x_user_id) = 'Y' THEN
2643
2644 /* The FORWARD-FROM is now the old FORWARD-TO and the NEW FORWARD-TO is set
2645 ** to what the user entered in the response
2646 */
2647
2648 l_forward_to_username:= wf_engine.GetItemAttrText (itemtype => itemtype,
2649 itemkey => itemkey,
2650 aname => 'FORWARD_TO_USERNAME');
2651
2652 l_forward_to_username_disp:= wf_engine.GetItemAttrText (itemtype => itemtype,
2653 itemkey => itemkey,
2654 aname => 'FORWARD_TO_DISPLAY_NAME');
2655
2656 l_forward_to_id:= wf_engine.GetItemAttrNumber (itemtype => itemtype,
2657 itemkey => itemkey,
2658 aname => 'FORWARD_TO_ID');
2659 /* Set the FORWARD_FROM */
2660 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2661 itemkey => itemkey,
2662 aname => 'FORWARD_FROM_ID',
2663 avalue => l_forward_to_id);
2664
2665 wf_engine.SetItemAttrText ( itemtype => itemtype,
2666 itemkey => itemkey,
2667 aname => 'FORWARD_FROM_USER_NAME',
2668 avalue => l_forward_to_username);
2669
2670 wf_engine.SetItemAttrText ( itemtype => itemtype,
2671 itemkey => itemkey,
2672 aname => 'FORWARD_FROM_DISP_NAME',
2676
2673 avalue => l_forward_to_username_disp);
2674
2675 /* Set the FORWARD-TO */
2677 wf_engine.SetItemAttrText ( itemtype => itemtype,
2678 itemkey => itemkey,
2679 aname => 'FORWARD_TO_USERNAME',
2680 avalue => l_forward_to_username_response);
2681
2682 wf_engine.SetItemAttrNumber ( itemtype => itemType,
2683 itemkey => itemkey,
2684 aname => 'FORWARD_TO_ID',
2685 avalue => x_user_id);
2686
2687 /* Get the Display name for the user from the WF Directory */
2688 wf_engine.SetItemAttrText ( itemtype => itemtype,
2689 itemkey => itemkey,
2690 aname => 'FORWARD_TO_DISPLAY_NAME',
2691 avalue =>
2692 wf_directory.GetRoleDisplayName(l_forward_to_username_response));
2693
2694 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
2695 wf_engine.SetItemAttrText (itemtype => itemtype,
2696 itemkey => itemkey,
2697 aname => 'FORWARD_TO_USERNAME_RESPONSE',
2698 avalue => NULL);
2699
2700
2701
2702 /* Set the Subject of the Approval notification to "requires your approval".
2703 ** Since the user entered a valid forward-to, then set the
2704 ** "Invalid Forward-to" message to NULL.
2705 */
2706 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
2707 l_error_msg := fnd_message.get;
2708
2709 wf_engine.SetItemAttrText ( itemtype => itemType,
2710 itemkey => itemkey,
2711 aname => 'REQUIRES_APPROVAL_MSG' ,
2712 avalue => l_error_msg);
2713
2714
2715 wf_engine.SetItemAttrText ( itemtype => itemtype,
2716 itemkey => itemkey,
2717 aname => 'WRONG_FORWARD_TO_MSG',
2718 avalue => NULL);
2719
2720 --
2721 resultout := wf_engine.eng_completed || ':' || 'Y';
2722 --
2723
2724 ELSE
2725
2726
2727 /* Set the error message that will be shown to the user in the ERROR MESSAGE
2728 ** Field in the Notification.
2729 */
2730
2731 /* Set the Subject of the Approval notification to "Invalid forward-to"
2732 ** Since the user entered an invalid forward-to, then set the
2733 ** "requires your approval" message to NULL.
2734 */
2735 fnd_message.set_name ('PO','PO_WF_NOTIF_INVALID_FORWARD');
2736 l_error_msg := fnd_message.get;
2737
2738 wf_engine.SetItemAttrText ( itemtype => itemType,
2739 itemkey => itemkey,
2740 aname => 'REQUIRES_APPROVAL_MSG' ,
2741 avalue => '');
2742
2743 wf_engine.SetItemAttrText ( itemtype => itemType,
2744 itemkey => itemkey,
2745 aname => 'WRONG_FORWARD_TO_MSG' ,
2746 avalue => l_error_msg);
2747
2748 --
2749 resultout := wf_engine.eng_completed || ':' || 'N';
2750 --
2751
2752 END IF;
2753
2754
2755 x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 900';
2756 IF (g_po_wf_debug = 'Y') THEN
2757 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2758 END IF;
2759
2760
2761 EXCEPTION
2762
2763 WHEN OTHERS THEN
2764 wf_core.context ('PO_APPROVAL_REMINDER_SV','Is_Forward_To_Valid','SQL error ' || sqlcode);
2765 x_progress := 'PO_APPROVAL_REMINDER_SV.Is_Forward_To_Valid: 990 - ' ||
2766 'EXCEPTION - sql error: ' || sqlcode;
2767 IF (g_po_wf_debug = 'Y') THEN
2768 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2769 END IF;
2770
2771
2772 RAISE;
2773
2774
2775 END Is_Forward_To_Valid ;
2776
2777 /*===========================================================================
2778 PROCEDURE NAME: Cancel_Notif
2779
2780 DESCRIPTION:
2781
2782
2783 CHANGE HISTORY: HVADLAMU 8/20/1997 Created
2784 ===========================================================================*/
2785 PROCEDURE Cancel_Notif ( p_DocumentTypeCode IN varchar2,
2786 p_DocumentID IN number,
2787 p_ReleaseFlag IN varchar2 ) IS
2788
2789 l_itemtype VARCHAR2(100) := 'APVRMDER';
2790 l_itemkey VARCHAR2(100);
2791 l_agent_id NUMBER;
2792 l_notif_id NUMBER;
2793 l_doc_type VARCHAR2(25);
2794 l_doc_subtype VARCHAR2(25);
2795 act_status VARCHAR2(8);
2796 x_progress VARCHAR2(100) := '001';
2797 l_acceptance_due_date DATE;
2798
2799 --Bug 5500222
2800 --If the buyer on the PO is changed and then cancel_notif is called,
2801 --we want to make sure that the reminder notification with new item key
2802 --is aborted correctly.
2803 Cursor cand_active_wf(l_itemkey VARCHAR2) IS
2804 select wias.activity_status_code, wias.item_key
2805 from wf_item_activity_statuses_v wias, wf_items_v wi
2806 where wias.item_type = 'APVRMDER'
2807 and wias.item_key like l_itemkey||'%'
2808 and wias.item_type = wi.item_type
2809 and wias.item_key = wi.item_key
2810 and wias.activity_name = wi.root_activity;
2811
2812 BEGIN
2813 l_doc_subtype := p_DocumentTypeCode;
2814 if ((p_DocumentTypeCode = 'STANDARD') or (p_DocumentTypeCode = 'PLANNED')) then
2815
2819
2816 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-001';
2817
2818 l_doc_type := 'PO';
2820 select agent_id
2821 into l_agent_id
2822 from po_headers_all /*Bug6632095: using base table instead of view */
2823 where po_header_id = p_DocumentID;
2824 --bug#3709971 modified the structure of item key from
2825 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2826 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
2827
2828 --Bug 5500222 : do not append the agent to the item key
2829 l_itemkey := l_doc_type ||'-'||
2830 l_doc_subtype ||'-'||
2831 to_char(p_DocumentId) ||'-';
2832
2833 For rec in cand_active_wf(l_itemkey) LOOP
2834 if (po_approval_reminder_sv.is_active('APVRMDER',rec.item_key)) then
2835 WF_Engine.AbortProcess('APVRMDER',rec.item_key);
2836 end if;
2837 End Loop;
2838
2839 elsif ((p_DocumentTypeCode = 'BLANKET') or (p_DocumentTypeCode = 'CONTRACT')) then
2840
2841
2842 if (p_ReleaseFlag = 'Y') then /* the doc_type must be RELEASE */
2843
2844 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-002';
2845
2846 l_doc_type := 'RELEASE';
2847
2848 select agent_id
2849 into l_agent_id
2850 from po_releases_all /*Bug6632095: using base table instead of view */
2851 where po_release_id = p_DocumentID;
2852
2853 --bug#3709971 modified the structure of item key from
2854 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2855 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
2856
2857 l_itemkey := l_doc_type ||'-'||
2858 l_doc_subtype ||'-'||
2859 to_char(p_DocumentId) ||'-'||
2860 to_char(l_agent_id);
2861 else
2862
2863 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-003';
2864
2865 l_doc_type := 'PA';
2866
2867 select agent_id
2868 into l_agent_id
2869 from po_headers_all /*Bug6632095: using base table instead of view */
2870 where po_header_id = p_DocumentID;
2871 --bug#3709971 modified the structure of item key from
2872 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2873 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
2874
2875 l_itemkey := l_doc_type ||'-'||
2876 l_doc_subtype ||'-'||
2877 to_char(p_DocumentId) ||'-'||
2878 to_char(l_agent_id);
2879 end if;
2880 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2881 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2882 end if;
2883
2884
2885
2886 elsif ((p_DocumentTypeCode = 'INTERNAL') or (p_DocumentTypeCode = 'PURCHASE')) then
2887
2888 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-004';
2889
2890 l_doc_type := 'REQUISITION';
2891
2892 select preparer_id
2893 into l_agent_id
2894 from po_requisition_headers
2895 where requisition_header_id = p_DocumentID;
2896 --bug#3709971 modified the structure of item key from
2897 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2898 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
2899
2900 l_itemkey := l_doc_type ||'-'||
2901 l_doc_subtype ||'-'||
2902 to_char(p_DocumentId) ||'-'||
2903 to_char(l_agent_id);
2904
2905 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2906 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2907 end if;
2908
2909 elsif (p_DocumentTypeCode = 'SCHEDULED') then
2910
2911 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-005';
2912
2913 l_doc_type := 'RELEASE';
2914
2915 select agent_id
2916 into l_agent_id
2917 from po_releases_all /*Bug6632095: using base table instead of view */
2918 where po_release_id = p_DocumentID;
2919 --bug#3709971 modified the structure of item key from
2920 --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2921 --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
2922
2923 l_itemkey := l_doc_type ||'-'||
2924 l_doc_subtype ||'-'||
2925 to_char(p_DocumentId) ||'-'||
2926 to_char(l_agent_id);
2927
2928 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2929 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2930 end if;
2931 elsif (p_DocumentTypeCode = 'PO_ACCEPTANCE') then
2932
2933 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-006';
2934
2935 l_doc_type := 'PO_ACCEPTANCE';
2936
2937 select type_lookup_code
2938 into l_doc_subtype
2939 from po_headers_all /*Bug6632095: using base table instead of view */
2940 where po_header_id = p_DocumentID;
2941 --bug#3709971 modified the structure of item key from
2942 --doc_type||doc_subtype||doc_num(old structure) to
2943 --doc_type||'-'||doc_subtype||'-'||doc_num
2944
2945 l_itemkey := l_doc_type || '-'||
2946 l_doc_subtype ||'-'||
2947 to_char(p_DocumentId);
2948
2949
2950 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2951 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2952 end if;
2953
2957 --Force item purge even if an active child process exists.
2954 -- Bug 3593182: Removed the item exists condition before
2955 -- the purge as purge only purges existing items
2956 --<BUG 3351588>
2958 WF_PURGE.ITEMS (itemtype => l_ItemType,
2959 itemkey => l_Itemkey,
2960 enddate => SYSDATE,
2961 docommit => true, --<BUG 3351588>
2962 force => true); --<BUG 3351588>
2963
2964 elsif (p_DocumentTypeCode = 'REL_ACCEPTANCE') then
2965
2966 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-007';
2967
2968 l_doc_type := 'REL_ACCEPTANCE';
2969
2970 select poh.type_lookup_code
2971 into l_doc_subtype
2972 from po_headers_all poh, -- <R12 MOAC>
2973 po_releases_all por /*Bug6632095: using base table instead of view */
2974 where por.po_header_id = poh.po_header_id and
2975 por.po_release_id = p_DocumentId;
2976
2977 if (l_doc_subtype = 'PLANNED') then
2978 l_doc_subtype := 'SCHEDULED';
2979 end if;
2980 --bug#3709971 modified the structure of item key from
2981 --doc_type||doc_subtype||doc_num(old structure) to
2982 --doc_type||'-'||doc_subtype||'-'||doc_num
2983
2984 l_itemkey := l_doc_type || '-'||
2985 l_doc_subtype ||'-'||
2986 to_char(p_DocumentId);
2987
2988
2989 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
2990 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
2991 end if;
2992
2993 -- Bug 3593182: Removed the item exists condition before
2994 -- the purge as purge only purges existing items
2995
2996 --<BUG 3351588>
2997 --Force item purge even if an active child process exists.
2998 WF_PURGE.ITEMS (itemtype => l_ItemType,
2999 itemkey => l_Itemkey,
3000 enddate => SYSDATE,
3001 docommit => true, --<BUG 3351588>
3002 force => true); --<BUG 3351588>
3003
3004 elsif (p_DocumentTypeCode = 'RFQ') then
3005
3006 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-008';
3007 l_doc_type := 'RFQ';
3008
3009 l_itemkey := l_doc_type ||
3010 to_char(p_DocumentId);
3011
3012 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
3013 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
3014 end if;
3015
3016
3017 -- Bug 3593182: Removed the item exists condition before
3018 -- the purge as purge only purges existing items
3019 --<BUG 3351588>
3020 --Force item purge even if an active child process exists.
3021 WF_PURGE.ITEMS (itemtype => l_ItemType,
3022 itemkey => l_Itemkey,
3023 enddate => SYSDATE,
3024 docommit => true, --<BUG 3351588>
3025 force => true); --<BUG 3351588>
3026
3027 elsif (p_DocumentTypeCode = 'QUOTATION') then
3028
3029 x_progress := 'PO_APPROVAL_REMINDER_SV.cancel_notif-009';
3030 l_doc_type := 'QUOTATION';
3031
3032 l_itemkey := l_doc_type ||
3033 to_char(p_DocumentId);
3034
3035 if (po_approval_reminder_sv.is_active('APVRMDER',l_itemkey)) then
3036 WF_Engine.AbortProcess('APVRMDER',l_itemkey);
3037 end if;
3038
3039
3040 -- Bug 3593182: Removed the item exists condition before
3041 -- the purge as purge only purges existing items
3042 --<BUG 3351588>
3043 --Force item purge even if an active child process exists.
3044 WF_PURGE.ITEMS (itemtype => l_ItemType,
3045 itemkey => l_Itemkey,
3046 enddate => SYSDATE,
3047 docommit => true, --<BUG 3351588>
3048 force => true); --<BUG 3351588>
3049
3050 end if;
3051
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 IF (g_po_wf_debug = 'Y') THEN
3055 PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
3056 END IF;
3057 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Cancel_notification','SQL error ' || sqlcode);
3058 RAISE;
3059
3060 END Cancel_Notif;
3061
3062 FUNCTION is_active (x_item_type in varchar2,
3063 x_item_key in varchar2) RETURN BOOLEAN is
3064
3065 -- Bug 3693990
3066 x_act_status WF_ITEM_ACTIVITY_STATUSES.ACTIVITY_STATUS%TYPE;
3067 x_result varchar2(30);
3068 -- Bug 3693990
3069
3070 x_progress varchar2(100) := '001';
3071
3072 BEGIN
3073 x_progress := 'PO_APPROVAL_REMINDER_SV.is_active-001';
3074
3075 --bug#3693990 commented out the above sql because the view
3076 --wf_item_activity_statuses_v is a view which is based on
3077 --a union all between wf_item_activity_statuses and
3078 --wf_item_activity_statuses_h(history). A new record
3079 --gets inserted into history table when the wf process
3080 --raises an error.
3084 BEGIN
3081 --WF Team has provided an api wf_engine.itemstatus to check the
3082 --active status of any given activity. We need to call this
3083 --instead of query the wf_item_activity_statuses_v view directly.
3085 wf_engine.itemstatus (itemtype => x_item_type,
3086 itemkey => x_item_key,
3087 status => x_act_status,
3088 RESULT => x_result
3089 );
3090 EXCEPTION
3091 WHEN OTHERS THEN
3092 x_act_status:= NULL;
3093 END;
3094
3095
3096 if x_act_status not in ('COMPLETE', 'ERROR') then
3097 return TRUE;
3098 else return FALSE;
3099 end if;
3100 EXCEPTION
3101 WHEN NO_DATA_FOUND THEN
3102 return FALSE;
3103 WHEN OTHERS THEN
3104 IF (g_po_wf_debug = 'Y') THEN
3105 PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
3106 END IF;
3107 wf_core.context ('PO_APPROVAL_REMINDER_SV','Process_Check_Status','SQL error ' || sqlcode);
3108 RAISE;
3109 return FALSE;
3110 END;
3111
3112 PROCEDURE stop_process( item_type in varchar2,
3113 item_key in varchar2) is
3114
3115 BEGIN
3116
3117 if (po_approval_reminder_sv.is_active(item_type,item_key)) then
3118 WF_Engine.AbortProcess(item_type,item_key);
3119 end if;
3120
3121 END stop_process;
3122
3123
3124 PROCEDURE item_exist ( p_ItemType IN VARCHAR2,
3125 p_ItemKey IN VARCHAR2,
3126 p_Item_exist OUT NOCOPY VARCHAR2,
3127 p_Item_end_date OUT NOCOPY DATE) is
3128
3129
3130 l_progress VARCHAR2(300) := NULL;
3131
3132 BEGIN
3133
3134
3135 l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 01';
3136 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3137
3138 -- initialize the return variables
3139 p_item_exist := NULL;
3140 p_item_end_date := NULL;
3141
3142 SELECT 'Y', WI.end_date
3143 INTO p_item_exist, p_item_end_date
3144 FROM WF_ITEMS_V WI
3145 WHERE WI.ITEM_TYPE = p_ItemType
3146 AND WI.ITEM_KEY = p_ItemKey;
3147
3148
3149 l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 900 ';
3150 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3151
3152
3153 EXCEPTION
3154 WHEN NO_DATA_FOUND THEN
3155
3156 -- item key does not exist
3157 p_item_exist := 'N';
3158 p_item_end_date := NULL;
3159
3160 WHEN OTHERS THEN
3161
3162 wf_core.context ('PO_APPROVAL_REMINDER_SV','Item_exist','SQL error ' || sqlcode);
3163 l_progress := 'PO_APPROVAL_REMINDER_SV.Item_Exist: 990 - ' ||
3164 'EXCEPTION - sql error: ' || sqlcode;
3165 IF (g_po_wf_debug = 'Y') THEN
3166 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3167 END IF;
3168
3169 RAISE;
3170
3171
3172 END item_exist;
3173
3174 -- <SVC_NOTIFICATIONS FPJ START>
3175 -------------------------------------------------------------------------------
3176 --Start of Comments
3177 --Name: process_po_temp_labor_lines
3178 --Pre-reqs:
3179 -- None.
3180 --Modifies:
3181 -- None.
3182 --Locks:
3183 -- None.
3184 --Function:
3185 -- Starts the Reminder workflow to send notifications for Temp Labor lines
3186 -- that match the reminder criteria (Amount Billed Exceeds Budget,
3187 -- Contractor Assignment Nearing Completion).
3188 --Parameters:
3189 -- None.
3190 --End of Comments
3191 -------------------------------------------------------------------------------
3192 PROCEDURE process_po_temp_labor_lines IS
3193 l_proc_name CONSTANT VARCHAR2(30) := 'process_po_temp_labor_lines';
3194
3195 CURSOR l_temp_labor_lines_csr (
3196 p_amount_threshold NUMBER,
3197 p_completion_threshold NUMBER
3198 ) IS
3199 -- SQL What: Retrieve the Temp Labor lines that match either of the
3200 -- reminder criteria (Amount Billed Exceeds Budget, Contractor
3201 -- Assignment Nearing Completion), ignoring those that
3202 -- already had a notification sent.
3203 -- We only consider Standard PO lines with approved, open,
3204 -- non-cancelled shipments.
3205 -- SQL Why: To send notifications for these lines.
3206 --
3207 -- <Complex Work R12 START>: Re-architected query to select SUM of
3208 -- amount_billed accross all line locations, since Complex Work POs
3209 -- can have multiple pay items on fixed-price temp labor lines.
3210 SELECT POL2.po_line_id,
3211 POL2.svc_amount_notif_sent,
3212 POL2.amount,
3213 SUM_DATA.total_amount_billed amount_billed,
3214 POL2.svc_completion_notif_sent,
3215 POL2.expiration_date,
3216 POL2.contractor_first_name,
3217 POL2.contractor_last_name,
3218 PJ.name job_name
3219 FROM po_lines_all POL2,
3220 per_jobs_vl PJ,
3221 ( SELECT PLL.po_line_id
3222 , SUM(PLL.amount_billed) total_amount_billed
3223 FROM po_line_locations PLL
3224 , po_lines_all POL
3228 AND POL.purchase_basis = 'TEMP LABOR'
3225 , po_headers_all poh
3226 WHERE poh.type_lookup_code = 'STANDARD'
3227 AND poh.po_header_id = POL.po_header_id
3229 AND POL.po_line_id = PLL.po_line_id
3230 AND NVL(PLL.approved_flag, 'N') = 'Y'
3231 AND NVL(PLL.cancel_flag, 'N') = 'N'
3232 AND NVL(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3233 AND ( PLL.payment_type IS NULL
3234 OR PLL.payment_type NOT IN ('DELIVERY','ADVANCE')
3235 )
3236 GROUP BY PLL.po_line_id
3237 ) SUM_DATA
3238 WHERE POL2.po_line_id = SUM_DATA.po_line_id
3239 AND PJ.job_id = POL2.job_id
3240 AND ( ( p_amount_threshold IS NOT NULL
3241 AND SUM_DATA.total_amount_billed
3242 >= POL2.amount * p_amount_threshold / 100
3243 )
3244 OR ( p_completion_threshold IS NOT NULL
3245 AND TRUNC(sysdate) >= TRUNC(POL2.expiration_date) - p_completion_threshold
3246 )
3247 );
3248 -- <Complex Work R12 END>
3249
3250 l_tl_line_rec l_temp_labor_lines_csr%ROWTYPE;
3251 l_amount_threshold NUMBER;
3252 l_completion_threshold NUMBER;
3253 l_contractor_or_job VARCHAR2(500);
3254 l_requester_id PO_REQUISITION_LINES.to_person_id%TYPE;
3255 l_profile_name FND_PROFILE_OPTIONS_VL.profile_option_name%TYPE;
3256 l_user_profile_name FND_PROFILE_OPTIONS_VL.user_profile_option_name%TYPE;
3257 BEGIN
3258 -- Retrieve the profile options for the Reminder thresholds.
3259 BEGIN
3260 l_profile_name := 'PO_SVC_AMOUNT_THRESHOLD';
3261 l_amount_threshold := TO_NUMBER(FND_PROFILE.value(l_profile_name));
3262 l_profile_name := 'PO_SVC_COMPLETION_THRESHOLD';
3263 l_completion_threshold := TO_NUMBER(FND_PROFILE.value(l_profile_name));
3264 EXCEPTION
3265 WHEN value_error THEN
3266 SELECT user_profile_option_name
3267 INTO l_user_profile_name
3268 FROM FND_PROFILE_OPTIONS_VL
3269 WHERE profile_option_name = l_profile_name;
3270
3271 FND_MESSAGE.set_name ('PO', 'PO_PROFILE_OPTION_NUMERIC');
3272 FND_MESSAGE.set_token ( 'PROFILE_OPTION', l_user_profile_name );
3273 FND_FILE.put_line ( FND_FILE.OUTPUT, FND_MESSAGE.get );
3274 RAISE;
3275 END;
3276
3277 IF (g_fnd_debug = 'Y') THEN
3278 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3279 FND_LOG.string( log_level => FND_LOG.LEVEL_STATEMENT,
3280 module => g_module_prefix || l_proc_name,
3281 message => 'Amount threshold: ' || l_amount_threshold
3282 || '; Completion threshold: '
3283 || l_completion_threshold );
3284 END IF;
3285 END IF;
3286
3287 -- We do not need to send any notifications if the reminder thresholds
3288 -- are not set.
3289 IF (l_amount_threshold IS NULL) AND (l_completion_threshold IS NULL) THEN
3290 RETURN;
3291 END IF;
3292
3293 -- Loop through the Temp Labor lines that exceed either threshold.
3294 FOR l_tl_line_rec
3295 IN l_temp_labor_lines_csr (l_amount_threshold, l_completion_threshold) LOOP
3296
3297 -- For the subject of the notification, use the concatenated contractor
3298 -- name, if available, or otherwise the job name.
3299 IF (l_tl_line_rec.contractor_first_name IS NOT NULL)
3300 OR (l_tl_line_rec.contractor_last_name IS NOT NULL) THEN
3301 l_contractor_or_job :=
3302 PO_POAPPROVAL_INIT1.get_formatted_full_name (
3303 l_tl_line_rec.contractor_first_name,
3304 l_tl_line_rec.contractor_last_name );
3305 ELSE
3306 l_contractor_or_job := l_tl_line_rec.job_name;
3307 END IF;
3308
3309 -- Determine the requester for this Temp Labor PO line.
3310 PO_POAPPROVAL_INIT1.get_temp_labor_requester (
3311 p_po_line_id => l_tl_line_rec.po_line_id,
3312 x_requester_id => l_requester_id );
3313
3314 -- If the Amount Billed on this line exceeds the tolerance of the
3315 -- Budget Amount, send a reminder notification to the requester.
3316 IF (l_amount_threshold IS NOT NULL)
3317 AND (l_tl_line_rec.svc_amount_notif_sent IS NULL)
3318 AND (l_tl_line_rec.amount_billed >=
3319 l_tl_line_rec.amount * l_amount_threshold / 100) THEN
3320
3321 start_po_line_reminder_wf (
3322 p_po_line_id => l_tl_line_rec.po_line_id,
3323 p_line_reminder_type => 'SVC_AMOUNT',
3324 p_requester_id => l_requester_id,
3325 p_contractor_or_job => l_contractor_or_job,
3326 p_expiration_date => l_tl_line_rec.expiration_date
3327 );
3328
3329 -- SQL What: Set the "Amount Billed notification sent" flag to Y.
3330 UPDATE po_lines_all
3331 SET svc_amount_notif_sent = 'Y',
3332 last_update_date = sysdate,
3333 last_updated_by = fnd_global.user_id
3334 WHERE po_line_id = l_tl_line_rec.po_line_id;
3335
3336 END IF;
3337
3338 -- If the current date exceeds the completion tolerance of the
3339 -- Assignment End Date, send a reminder notification to the requester.
3340 IF (l_completion_threshold is NOT NULL)
3341 AND (l_tl_line_rec.svc_completion_notif_sent IS NULL)
3345 start_po_line_reminder_wf (
3342 AND (TRUNC(sysdate) >=
3343 TRUNC(l_tl_line_rec.expiration_date) - l_completion_threshold) THEN
3344
3346 p_po_line_id => l_tl_line_rec.po_line_id,
3347 p_line_reminder_type => 'SVC_COMPLETION',
3348 p_requester_id => l_requester_id,
3349 p_contractor_or_job => l_contractor_or_job,
3350 p_expiration_date => l_tl_line_rec.expiration_date
3351 );
3352
3353 -- SQL What: Set the "Assignment Completion notification sent" flag to Y.
3354 UPDATE po_lines_all
3355 SET svc_completion_notif_sent = 'Y',
3356 last_update_date = sysdate,
3357 last_updated_by = fnd_global.user_id
3358 WHERE po_line_id = l_tl_line_rec.po_line_id;
3359
3360 END IF;
3361
3362 END LOOP; -- l_po_temp_labor_rec
3363
3364 -- Issue a commit so that Workflow can pick up the reminder notifications.
3365 COMMIT;
3366
3367 EXCEPTION
3368 WHEN OTHERS THEN
3369 IF (g_fnd_debug = 'Y') THEN
3370 FND_MSG_PUB.Build_Exc_Msg ( p_pkg_name => g_pkg_name,
3371 p_procedure_name => l_proc_name );
3372 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3373 FND_LOG.string( log_level => FND_LOG.LEVEL_UNEXPECTED,
3374 module => g_module_prefix || l_proc_name,
3375 message => FND_MESSAGE.get );
3376 END IF;
3377 END IF;
3378
3379 RAISE;
3380 END process_po_temp_labor_lines;
3381
3382 -------------------------------------------------------------------------------
3383 --Start of Comments
3384 --Name: start_po_line_reminder_wf
3385 --Pre-reqs:
3386 -- None.
3387 --Modifies:
3388 -- None.
3389 --Locks:
3390 -- None.
3391 --Function:
3392 -- Starts the Reminder workflow for the given PO line and line reminder type.
3393 --Parameters:
3394 --IN:
3395 --p_po_line_id
3396 -- PO line for this reminder
3397 --p_line_reminder_type
3398 -- type of reminder - ex. SVC_AMOUNT (for Amount Approaching Budget)
3399 --End of Comments
3400 -------------------------------------------------------------------------------
3401 PROCEDURE start_po_line_reminder_wf (
3402 p_po_line_id IN PO_LINES.po_line_id%TYPE,
3403 p_line_reminder_type IN VARCHAR2,
3404 p_requester_id IN NUMBER,
3405 p_contractor_or_job IN VARCHAR2,
3406 p_expiration_date IN DATE
3407 ) IS
3408 l_proc_name CONSTANT VARCHAR2(30) := 'start_po_line_reminder_wf';
3409
3410 l_item_key WF_ITEMS.item_key%TYPE;
3411 l_item_exist VARCHAR2(1);
3412 l_item_end_date DATE;
3413 l_requester_user_name WF_USERS.name%TYPE;
3414 l_requester_disp_name WF_USERS.display_name%TYPE;
3415 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
3416 l_req_header_id PO_REQUISITION_HEADERS_ALL.requisition_header_id%TYPE;
3417 l_document_number PO_HEADERS_ALL.segment1%TYPE;
3418 BEGIN
3419 l_item_key := 'PO_LINE_REMINDER-' || p_line_reminder_type || '-'
3420 || p_po_line_id;
3421
3422 PO_REQAPPROVAL_INIT1.get_user_name (
3423 p_employee_id => p_requester_id,
3424 x_username => l_requester_user_name,
3425 x_user_display_name => l_requester_disp_name );
3426
3427 IF (g_fnd_debug = 'Y') THEN
3428 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
3429 FND_LOG.string( log_level => FND_LOG.LEVEL_EVENT,
3430 module => g_module_prefix || l_proc_name,
3431 message => 'Launching the reminder workflow:'
3432 || ' item key: ' || l_item_key
3433 || ', requester ID: ' || p_requester_id
3434 || ', user name: ' || l_requester_user_name
3435 || ', contractor/job: ' || p_contractor_or_job
3436 || ', expiration date: ' || p_expiration_date
3437 );
3438 END IF;
3439 END IF;
3440
3441 -- Cancel and purge any existing reminder process for this line.
3442 PO_APPROVAL_REMINDER_SV.item_exist (p_ItemType => 'APVRMDER',
3443 p_ItemKey => l_item_key,
3444 p_Item_exist => l_item_exist,
3445 p_Item_end_date => l_item_end_date);
3446 IF (l_item_exist = 'Y') THEN
3447 -- Abort the process if it is still active.
3448 IF (l_item_end_date IS NULL) THEN
3449 WF_ENGINE.AbortProcess(g_reminder_item_type, l_item_key );
3450 END IF;
3451
3452 -- Purge the process so we can re-use the item key.
3453 --<BUG 3351588>
3454 --Force item purge even if an active child process exists.
3455 WF_PURGE.ITEMS (itemtype => g_reminder_item_type,
3456 itemkey => l_item_key,
3457 enddate => SYSDATE,
3458 docommit => true, --<BUG 3351588>
3459 force => true); --<BUG 3351588>
3460
3461 END IF; -- l_item_exist
3462
3463 -- Create a Reminder workflow process for this PO line.
3464 wf_engine.CreateProcess ( ItemType => g_reminder_item_type,
3465 ItemKey => l_item_key,
3466 Process => 'PO_LINE_REMINDER' );
3467
3468 -- Set some workflow item attributes.
3472 avalue => p_po_line_id );
3469 po_wf_util_pkg.SetItemAttrNumber ( ItemType => g_reminder_item_type,
3470 ItemKey => l_item_key,
3471 aname => 'PO_LINE_ID',
3473
3474 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3475 ItemKey => l_item_key,
3476 aname => 'PO_LINE_REMINDER_TYPE',
3477 avalue => p_line_reminder_type );
3478
3479 po_wf_util_pkg.SetItemAttrNumber ( ItemType => g_reminder_item_type,
3480 ItemKey => l_item_key,
3481 aname => 'REQUESTER_ID',
3482 avalue => p_requester_id );
3483
3484 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3485 ItemKey => l_item_key,
3486 aname => 'REQUESTER_USER_NAME',
3487 avalue => l_requester_user_name );
3488
3489 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3490 ItemKey => l_item_key,
3491 aname => 'CONTRACTOR_OR_JOB',
3492 avalue => p_contractor_or_job );
3493
3494 po_wf_util_pkg.SetItemAttrDate ( ItemType => g_reminder_item_type,
3495 ItemKey => l_item_key,
3496 aname => 'EXPIRATION_DATE',
3497 avalue => p_expiration_date );
3498
3499 -- Bug 3441007 START
3500 -- For BLAF Compliance, we are now showing the links in the Related
3501 -- Applications section, so we need to set the URL attributes.
3502
3503 -- SQL What: Retrieve the PO_HEADER_ID and REQUISITION_HEADER_ID for the
3504 -- Temp Labor PO line.
3505
3506 --Bug 5483192 Start:
3507 -- With Actual Complex work POs its possible to have more than one line locations
3508 -- But its possible to have only one line location to with a backing req, if any
3509
3510 select POH.po_header_id,
3511 POH.segment1
3512 INTO l_po_header_id,
3513 l_document_number
3514 FROM po_lines POL,
3515 po_headers_all POH
3516 WHERE POL.po_line_id = p_po_line_id
3517 AND POL.po_header_id = POH.po_header_id;
3518
3519 BEGIN
3520
3521 select PRL.requisition_header_id
3522 INTO l_req_header_id
3523 FROM po_lines POL,
3524 po_line_locations_all PLL,
3525 po_requisition_lines_all PRL
3526 WHERE POL.po_line_id = p_po_line_id
3527 AND POL.po_line_id = PLL.po_line_id
3528 AND PLL.line_location_id = PRL.line_location_id;
3529
3530 EXCEPTION
3531 WHEN NO_DATA_FOUND THEN
3532 l_req_header_id := NULL;
3533 END;
3534 --Bug 5483192 End:
3535
3536 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3537 ItemKey => l_item_key,
3538 aname => 'DOCUMENT_NUMBER',
3539 avalue => l_document_number );
3540
3541 -- Show the 'View Purchase Order' link.
3542 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3543 ItemKey => l_item_key,
3544 aname => 'VIEW_PO_URL',
3545 avalue =>
3546 'OA.jsp?OAFunc=POS_VIEW_ORDER&PoHeaderId='||l_po_header_id );
3547
3548 -- Show the 'View Requisition' and 'Extend Assignment' links if there is a
3549 -- backing requisition.
3550 IF (l_req_header_id IS NOT NULL) THEN
3551
3552 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3553 ItemKey => l_item_key,
3554 aname => 'VIEW_REQ_URL',
3555 avalue =>
3556 'OA.jsp?OAFunc=ICX_POR_LAUNCH_IP&porMode=viewReq'
3557 ||'&porReqHeaderId='||l_req_header_id );
3558
3559 -- Bug 3562721 Changed to use the new iP URL for change requests.
3560 po_wf_util_pkg.SetItemAttrText ( ItemType => g_reminder_item_type,
3561 ItemKey => l_item_key,
3562 aname => 'EXTEND_ASSIGNMENT_URL',
3563 avalue =>
3564 'OA.jsp?OAFunc=ICX_POR_LAUNCH_IP&porMode=changeOrder'
3565 ||'&porReqHeaderId='||l_req_header_id );
3566
3567 END IF; -- l_req_header_id
3568 -- Bug 3441007 END
3569
3570 -- Start the Reminder workflow process for this PO line.
3571 wf_engine.StartProcess ( ItemType => g_reminder_item_type,
3572 ItemKey => l_item_key );
3573
3574 EXCEPTION
3575 WHEN OTHERS THEN
3576 IF (g_fnd_debug = 'Y') THEN
3577 FND_MSG_PUB.Build_Exc_Msg ( p_pkg_name => g_pkg_name,
3578 p_procedure_name => l_proc_name );
3579 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3580 FND_LOG.string( log_level => FND_LOG.LEVEL_UNEXPECTED,
3581 module => g_module_prefix || l_proc_name,
3582 message => FND_MESSAGE.get );
3583 END IF;
3584 END IF;
3585
3586 RAISE;
3587 END start_po_line_reminder_wf;
3588
3589 -------------------------------------------------------------------------------
3590 --Start of Comments
3591 --Name: get_po_line_reminder_type
3592 --Pre-reqs:
3593 -- None.
3594 --Modifies:
3595 -- None.
3596 --Locks:
3597 -- None.
3598 --Function:
3599 -- Returns the value of the PO Line Reminder Type item attribute.
3600 --Parameters:
3601 --IN:
3602 --itemtype
3603 -- Workflow Item Type
3604 --itemkey
3605 -- Workflow Item Key
3606 --actid
3607 -- Identifies the Workflow activity that is calling this procedure.
3608 --funcmode
3609 -- Workflow mode that this procedure is being called in: Run, Cancel, etc.
3610 --OUT:
3611 --resultout
3612 -- COMPLETED:<value of the Temp Labor Reminder Type item attribute>
3613 --End of Comments
3614 -------------------------------------------------------------------------------
3615 PROCEDURE get_po_line_reminder_type (
3616 itemtype IN VARCHAR2,
3617 itemkey IN VARCHAR2,
3618 actid IN NUMBER,
3619 funcmode IN VARCHAR2,
3620 resultout OUT NOCOPY VARCHAR2
3621 ) IS
3622 l_line_reminder_type VARCHAR2(20);
3623 BEGIN
3624 -- Do nothing if the Workflow mode is Cancel or Timeout.
3625 if (funcmode <> wf_engine.eng_run) then
3626 resultout := wf_engine.eng_null;
3627 return;
3628 end if;
3629
3630 l_line_reminder_type :=
3631 po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
3632 itemkey => itemkey,
3633 aname => 'PO_LINE_REMINDER_TYPE');
3634 resultout := wf_engine.eng_completed || ':' || l_line_reminder_type;
3635
3636 EXCEPTION
3637 WHEN OTHERS THEN
3638 wf_core.context( 'PO_APPROVAL_REMINDER_SV',
3639 'GET_TEMP_LABOR_REMINDER_TYPE' );
3640 RAISE;
3641 END get_po_line_reminder_type;
3642
3643 -- <SVC_NOTIFICATIONS FPJ END>
3644
3645 END PO_APPROVAL_REMINDER_SV;