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