[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_PCARD_WORKFLOW_PKG
Source
1 PACKAGE BODY AP_WEB_PCARD_WORKFLOW_PKG AS
2 /* $Header: apwpcwfb.pls 120.14 2010/10/21 18:21:24 ctetala ship $ */
3
4 PROCEDURE DistributeEmpVerifications (errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_card_program_id IN NUMBER DEFAULT NULL,
7 p_employee_id IN NUMBER DEFAULT NULL,
8 p_status_lookup_code IN VARCHAR2,
9 p_org_id IN NUMBER)
10 IS
11 l_item_type VARCHAR2(100) := 'APEMPVFY';
12 l_item_key VARCHAR2(100);
13 l_employee_id NUMBER;
14 l_status_lookup_code VARCHAR2(15);
15 l_employee_name wf_users.name%type; --8770726
16 l_employee_display_name wf_users.display_name%type; --8770726
17 l_employee_notification_method AP_CARD_PROFILES.emp_notification_lookup_code%TYPE;
18 l_new_emp_verification_id NUMBER;
19 l_num_records_updated NUMBER;
20 l_lines_without_dists_flag BOOLEAN := FALSE;
21 l_url VARCHAR2(1000);
22 l_debug_info VARCHAR2(200);
23 l_test number;
24 --bug5058949
25 --Performance fix
26
27 CURSOR Emp_Verification_Workflows IS
28 SELECT decode(nvl(fl.create_distribution_flag,'N'),
29 'N', 'I', cp.emp_notification_lookup_code),
30 decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
31 decode(cp.emp_notification_lookup_code, 'Y',
32 fd.status_lookup_code, null)
33 FROM ap_expense_feed_lines fl,
34 ap_expense_feed_dists_all fd,
35 ap_cards_all c,
36 ap_card_profiles_all cp,
37 IBY_CREDITCARD IBY
38 WHERE fl.create_distribution_flag = 'Y' AND
39 fd.feed_line_id = fl.feed_line_id AND
40 fd.status_lookup_code =
41 nvl(p_status_lookup_code,fd.status_lookup_code) AND
42 fd.status_lookup_code IN ('VALIDATED', 'HOLD', 'REJECTED') AND
43 fd.employee_verification_id IS NULL AND
44 fl.card_id = c.card_id AND
45 c.card_reference_id=IBY.instrid AND
46 c.profile_id = cp.profile_id AND
47 (
48 (p_card_program_id IS NULL) OR
49 (cp.card_program_id = p_card_program_id)
50 )
51 AND (
52 (p_employee_id IS NULL) OR
53 (fl.employee_id = p_employee_id)
54 )
55 GROUP BY decode(nvl(fl.create_distribution_flag,'N'),
56 'N', 'I', cp.emp_notification_lookup_code),
57 decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
58 decode(cp.emp_notification_lookup_code, 'Y',
59 fd.status_lookup_code, null),fl.org_id
60 UNION
61 SELECT decode(nvl(fl.create_distribution_flag,'N'),
62 'N', 'I', cp.emp_notification_lookup_code),
63 decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
64 decode(cp.emp_notification_lookup_code, 'Y',
65 fd.status_lookup_code, null)
66 FROM ap_expense_feed_lines fl,
67 ap_expense_feed_dists_all fd,
68 ap_cards_all c,
69 ap_card_profiles_all cp,
70 IBY_CREDITCARD IBY
71 WHERE
72 nvl(fl.create_distribution_flag,'N') = 'N' AND
73 fl.employee_verification_id IS NULL AND
74 fd.feed_line_id(+) = fl.feed_line_id AND
75 fl.employee_verification_id is null AND
76 fl.card_id = c.card_id AND
77 c.card_reference_id=IBY.instrid AND
78 c.profile_id = cp.profile_id AND
79 (
80 (p_card_program_id IS NULL) OR
81 (cp.card_program_id = p_card_program_id)
82 ) AND
83 (
84 (p_employee_id IS NULL) OR
85 (fl.employee_id = p_employee_id)
86 )
87 GROUP BY decode(nvl(fl.create_distribution_flag,'N'),
88 'N', 'I', cp.emp_notification_lookup_code),
89 decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
90 decode(cp.emp_notification_lookup_code, 'Y',
91 fd.status_lookup_code, null),fl.org_id;
92
93
94
95
96
97
98
99 /*MOAC Changes->Group it according to org-id to
100 send one notification for every org*/
101 BEGIN
102 -----------------------------
103 l_debug_info := 'Set Org ID';
104 -----------------------------
105 /* FND_CLIENT_INFO.SetUp_Client_Info(200,
106 FND_GLOBAL.Resp_ID,
107 FND_GLOBAL.User_ID,
108 FND_GLOBAL.Security_Group_ID);*/
109 /*Setting the MOAC Access*/
110 Mo_global.init('SQLAP');
111 if p_org_id is not null then
112 Mo_Global.set_policy_context('S',p_org_id);
113 else
114 Mo_Global.set_policy_context('M',null);
115 end if;
116
117 OPEN Emp_Verification_Workflows;
118
119 LOOP
120
121 FETCH Emp_Verification_Workflows INTO l_employee_notification_method,
122 l_employee_id,
123 l_status_lookup_code;
124
125 EXIT WHEN Emp_Verification_Workflows%NOTFOUND;
126
127 ---------------------------------------------------------
128 l_debug_info := ' Generate New Employee Verification ID';
129 ---------------------------------------------------------
130 SELECT ap_card_emp_verify_s.nextval
131 INTO l_new_emp_verification_id
132 FROM sys.dual;
133
134 ------------------------------------------------------------------------
135 l_debug_info := 'Mark records to be included with this verification_id';
136 ------------------------------------------------------------------------
137 --bug5058949
138 --Performance fix
139
140 UPDATE ap_expense_feed_lines fl
141 SET employee_verification_id = l_new_emp_verification_id
142 WHERE (create_distribution_flag = 'N' OR
143 create_distribution_flag IS NULL)
144 AND employee_verification_id IS NULL
145 AND EXISTS (SELECT 'feed distribution falls in this workflow'
146 FROM ap_expense_feed_dists fd,
147 ap_cards c,
148 ap_card_profiles cp,
149 IBY_CREDITCARD IBY
150 WHERE fl.feed_line_id = fd.feed_line_id
151 AND c.card_reference_id=IBY.instrid
152 AND fl.card_id = c.card_id
153 AND c.profile_id = cp.profile_id
154 AND cp.emp_notification_lookup_code
155 = l_employee_notification_method
156 AND decode(cp.emp_notification_lookup_code, 'N',
157 1, fl.employee_id) = nvl(l_employee_id,1)
158 AND decode(cp.emp_notification_lookup_code, 'R',
159 fd.status_lookup_code,1) = nvl(l_status_lookup_code,1)
160 AND ((p_card_program_id IS NULL) OR
161 (cp.card_program_id = p_card_program_id))
162 AND ((p_employee_id IS NULL) OR
163 (fl.employee_id = p_employee_id)) );
164 --bug5058949
165 --Performance fix
166
167 UPDATE ap_expense_feed_dists fd
168 SET employee_verification_id = l_new_emp_verification_id
169 WHERE status_lookup_code in ('VALIDATED', 'HOLD', 'REJECTED')
170 AND employee_verification_id IS NULL
171 AND EXISTS (SELECT 'feed distribution falls in this workflow'
172 FROM ap_expense_feed_lines fl,
173 ap_cards c,
174 ap_card_profiles cp,
175 IBY_CREDITCARD IBY
176 WHERE fl.feed_line_id = fd.feed_line_id
177 AND c.card_reference_id=IBY.instrid
178 AND fl.card_id = c.card_id
179 AND c.profile_id = cp.profile_id
180 AND cp.emp_notification_lookup_code
181 = l_employee_notification_method
182 AND decode(cp.emp_notification_lookup_code, 'N',
183 1, fl.employee_id) = nvl(l_employee_id,1));
184
185 -------------------------------------------------------------------
186 l_debug_info := 'Make sure records where updated with this verification_id';
187 --------------------------------------------------------------------
188 SELECT count(*)
189 INTO l_num_records_updated
190 FROM ap_expense_feed_lines fl,
191 ap_expense_feed_dists fd
192 WHERE fl.employee_verification_id = l_new_emp_verification_id
193 OR fd.employee_verification_id = l_new_emp_verification_id;
194
195 IF (l_num_records_updated > 0) THEN
196 l_item_key := to_char(l_new_emp_verification_id);
197
198 --------------------------------------------------
199 l_debug_info := 'Calling WorkFlow Create Process';
200 --------------------------------------------------
201 WF_ENGINE.CreateProcess(l_item_type,
202 l_item_key,
203 'MAIN_PROCESS');
204
205 -------------------------------------------------------------
206 l_debug_info := 'Set Emp Notification ID Item Attribute';
207 -------------------------------------------------------------
208 WF_ENGINE.SetItemAttrNumber(l_item_type,
209 l_item_key,
210 'EMPLOYEE_VERIFICATION_ID',
211 l_new_emp_verification_id);
212
213 -------------------------------------------------------------
214 l_debug_info := 'Set Emp Notification Method Item Attribute';
215 -------------------------------------------------------------
216 WF_ENGINE.SetItemAttrText(l_item_type,
217 l_item_key,
218 'EMPLOYEE_NOTIFICATION_METHOD',
219 l_employee_notification_method);
220
221 IF (l_employee_id IS NOT NULL) THEN
222
223 ------------------------------------------------------------
224 l_debug_info := 'Get Name Info Associated With Employee_Id';
225 ------------------------------------------------------------
226 WF_DIRECTORY.GetUserName('PER',
227 l_employee_id,
228 l_employee_name,
229 l_employee_display_name);
230
231 ------------------------------------------------------
232 l_debug_info := 'Set WF Employee_ID Item Attribute';
233 ------------------------------------------------------
234 WF_ENGINE.SetItemAttrNumber(l_item_type,
235 l_item_key,
236 'EMPLOYEE_ID',
237 l_employee_id);
238
239 ------------------------------------------------------
240 l_debug_info := 'Set WF Employee_Name Item Attribute';
241 ------------------------------------------------------
242 WF_ENGINE.SetItemAttrText(l_item_type,
243 l_item_key,
244 'EMPLOYEE_NAME',
245 l_employee_name);
246
247 --------------------------------------------------------------
248 l_debug_info := 'Set WF Preparer_Display_Name Item Attribute';
249 --------------------------------------------------------------
250 WF_ENGINE.SetItemAttrText(l_item_type,
251 l_item_key,
252 'EMPLOYEE_DISPLAY_NAME',
253 l_employee_display_name);
254
255 END IF;
256
257 IF (l_status_lookup_code IS NOT NULL) THEN
258
259 ------------------------------------------------------
260 l_debug_info := 'Set WF Employee_Name Item Attribute';
261 ------------------------------------------------------
262 WF_ENGINE.SetItemAttrText(l_item_type,
263 l_item_key,
264 'STATUS_LOOKUP_CODE',
265 l_status_lookup_code);
266
267 END IF;
268
269 --------------------------------------------------------
270 l_debug_info := 'Call JumpIntoFunction to retrieve URL';
271 --------------------------------------------------------
272 /*AP_WEB_INTERFACE_PKG.JumpIntoFunction(l_new_emp_verification_id,
273 'PCARD EMP VERI',
274 l_url);*/
275
276 -----------------------------------------------------
277 l_debug_info := 'Set EXPENSE DETAILS Item Attribute';
278 -----------------------------------------------------
279
280 -- Be sure to clear these values. If we are resubmitting, we don't want
281 -- the values from the previous process traversal to hang around.
282 WF_ENGINE.SetItemAttrText(l_item_type,
283 l_item_key,
284 'PCARD_TRANS_DETAILS',
285 l_url);
286 /*MOAC CHANGES MADE FOR PCARD PROJECT*/
287 WF_ENGINE.SetItemAttrNumber(l_item_type,
288 l_item_key,
289 'ORG_ID',
290 nvl(p_org_id,mo_utils.get_default_org_id));
291
292 ------------------------------------------------------------
293 l_debug_info := 'Start the Expense Report Workflow Process';
294 ------------------------------------------------------------
295 WF_ENGINE.StartProcess(l_item_type,
296 l_item_key);
297
298 ELSE
299
300 l_lines_without_dists_flag := TRUE;
301
302 END IF;
303 END LOOP;
304
305 CLOSE Emp_Verification_Workflows;
306
307 COMMIT;
308
309 IF (l_lines_without_dists_flag) THEN
310 errbuf := 'There are credit card lines out there without associated' ||
311 ' distributions and as a result did not get processed.';
312 retcode := 1;
313 ELSE
314 retcode := 0;
315 END IF;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF (SQLCODE <> -20001) THEN
320 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
321 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
322 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DistributeEmpVerificationss');
323 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
324 errbuf := FND_MESSAGE.Get;
325 retcode := 2;
326 END IF;
327 END DistributeEmpVerifications;
328
329
330 PROCEDURE DistributeManagerApprovals (errbuf OUT NOCOPY VARCHAR2,
331 retcode OUT NOCOPY NUMBER,
332 p_manager_id IN NUMBER DEFAULT NULL,
333 p_org_id IN NUMBER)IS
334 l_item_type VARCHAR2(100) := 'APPCMGR';
335 l_item_key VARCHAR2(100);
336 l_employee_id NUMBER;
337 l_employee_name wf_users.name%type; --Bug 10220928
338 l_employee_display_name wf_users.display_name%type; --Bug 10220928
339 l_manager_id NUMBER;
340 l_manager_name wf_users.name%type; --Bug 10220928
341 l_manager_display_name wf_users.display_name%type; --Bug 10220928
342 l_manager_approval_method AP_CARD_PROFILES.mgr_approval_lookup_code%TYPE;
343 l_new_manager_approval_id NUMBER;
344 l_url VARCHAR2(1000);
345 l_debug_info VARCHAR2(200);
346 l_records_undistributed_flag BOOLEAN := FALSE;
347 l_num_records_updated NUMBER;
348
349 l_log_employee_name HR_EMPLOYEES_CURRENT_V.full_name%TYPE;
350 l_log_employee_num HR_EMPLOYEES_CURRENT_V.employee_num%TYPE;
351 --bug5058949
352 --Performance fix
353
354 CURSOR Manager_Approval_Workflows IS
355 SELECT cp.mgr_approval_lookup_code,
356 decode(cp.mgr_approval_lookup_code, 'N', null, hr.supervisor_id),
357 decode(cp.mgr_approval_lookup_code, 'N', null, fl.employee_id)
358 FROM ap_expense_feed_lines fl,
359 ap_expense_feed_dists_all fd,
360 ap_cards_all c,
361 ap_card_profiles_all cp,
362 per_employees_x hr,
363 IBY_CREDITCARD IBY
364 WHERE fd.status_lookup_code = 'VERIFIED'
365 AND fd.manager_approval_id IS NULL
366 AND fd.feed_line_id = fl.feed_line_id
367 AND fl.card_id = c.card_id
368 AND c.card_reference_id=IBY.instrid
369 AND c.profile_id = cp.profile_id
370 AND fl.employee_id = hr.employee_id
371 AND (hr.supervisor_id = p_manager_id OR
372 p_manager_id IS NULL)
373 GROUP BY cp.mgr_approval_lookup_code,
374 decode(cp.mgr_approval_lookup_code, 'N', null, hr.supervisor_id),
375 decode(cp.mgr_approval_lookup_code, 'N', null, fl.employee_id),fd.org_id;
376 /*Send one notification for every org*/
377 BEGIN
378
379 -----------------------------
380 l_debug_info := 'Set Org ID';
381 -----------------------------
382 /*FND_CLIENT_INFO.SetUp_Client_Info(200,
383 FND_GLOBAL.Resp_ID,
384 FND_GLOBAL.User_ID,
385 FND_GLOBAL.Security_Group_ID)*/
386 /*MOAC Changes to be done*/
387 Mo_global.init('SQLAP');
388 if p_org_id is not null then
389 Mo_Global.set_policy_context('S',p_org_id);
390 else
391 Mo_Global.set_policy_context('M',null);
392 end if;
393
394 OPEN Manager_Approval_Workflows;
395
396 LOOP
397
398 FETCH Manager_Approval_Workflows INTO l_manager_approval_method,
399 l_manager_id,
400 l_employee_id;
401
402 ----------------------------------------------------
403 -- Do not process records if there is no manager or
404 -- employee information.
405 ----------------------------------------------------
406 EXIT WHEN Manager_Approval_Workflows%NOTFOUND;
407
408 ------------------------------------------------------------
409 -- Check for null manager_id, which is a setup error if
410 -- employee_id is not null
411 ------------------------------------------------------------
412 if (l_manager_id is null and l_employee_id is not null) then
413
414 -----------------------------
415 l_debug_info := 'Retrieve employee info for log.';
416 -----------------------------
417 select full_name,
418 employee_num
419 into l_log_employee_name,
420 l_log_employee_num
421 from hr_employees_current_v
422 where employee_id = l_employee_id;
423
424 -----------------------------
425 l_debug_info := 'Write employee info to log.';
426 -----------------------------
427 fnd_file.put_line(FND_FILE.LOG,'Warning: Employee '||
428 l_log_employee_name||' ('||
429 l_log_employee_num ||') does not have a manager.');
430
431 fnd_file.put_line(FND_FILE.LOG,
432 'No transactions will be processed for this employee.');
433
434 fnd_file.put_line(FND_FILE.LOG,'');
435
436 else
437
438 ----------------------------------------------------
439 l_debug_info := ' Generate New Manager Approval ID';
440 ----------------------------------------------------
441 SELECT ap_card_mgr_approval_s.nextval
442 INTO l_new_manager_approval_id
443 FROM sys.dual;
444
445 --bug5058949
446 --Performance fix
447
448 UPDATE ap_expense_feed_dists fd
449 SET manager_approval_id = l_new_manager_approval_id
450 WHERE status_lookup_code = 'VERIFIED'
451 AND manager_approval_id IS NULL
452 AND EXISTS (SELECT 'feed distribution falls in this workflow'
453 FROM ap_expense_feed_lines fl,
454 ap_cards c,
455 ap_card_profiles cp,
456 hr_employees_current_v hr,
457 IBY_CREDITCARD IBY
458 WHERE fl.feed_line_id = fd.feed_line_id
459 AND c.card_reference_id=IBY.instrid
460 AND fl.card_id = c.card_id
461 AND c.profile_id = cp.profile_id
462 AND fl.employee_id = hr.employee_id
463 AND (hr.supervisor_id = p_manager_id OR
464 p_manager_id IS NULL)
465 AND decode(cp.mgr_approval_lookup_code, 'N',
466 1, hr.supervisor_id) = nvl(l_manager_id,1)
467 AND decode(cp.mgr_approval_lookup_code, 'N',
468 1, hr.employee_id) = nvl(l_employee_id,1));
469
470 -------------------------------------------------------------------------
471 l_debug_info := 'Check if no records got assigned to this current ' ||
472 'workflow process.';
473 -------------------------------------------------------------------------
474 SELECT count(*)
475 INTO l_num_records_updated
476 FROM ap_expense_feed_dists
477 WHERE manager_approval_id = l_new_manager_approval_id;
478
479
480 IF (l_num_records_updated > 0) THEN
481 l_item_key := to_char(l_new_manager_approval_id);
482
483 --------------------------------------------------
484 l_debug_info := 'Calling WorkFlow Create Process';
485 --------------------------------------------------
486 WF_ENGINE.CreateProcess(l_item_type,
487 l_item_key,
488 'AP_PC_MGR_APRVL_MAIN_PROCESS');
489
490 ---------------------------------------------------------
491 l_debug_info := 'Set Emp Notification ID Item Attribute';
492 ---------------------------------------------------------
493 WF_ENGINE.SetItemAttrNumber(l_item_type,
494 l_item_key,
495 'MANAGER_APPROVAL_ID',
496 l_new_manager_approval_id);
497
498 -------------------------------------------------------------
499 l_debug_info := 'Set Emp Notification Method Item Attribute';
500 -------------------------------------------------------------
501 WF_ENGINE.SetItemAttrText(l_item_type,
502 l_item_key,
503 'MANAGER_APPROVAL_METHOD',
504 l_manager_approval_method);
505
506 IF (l_manager_id IS NOT NULL) THEN
507
508 ------------------------------------------------------------
509 l_debug_info := 'Get Name Info Associated With Manager_Id';
510 ------------------------------------------------------------
511 WF_DIRECTORY.GetUserName('PER',
512 l_manager_id,
513 l_manager_name,
514 l_manager_display_name);
515
516 ---------------------------------------------------
517 l_debug_info := 'Set WF Manager_ID Item Attribute';
518 ---------------------------------------------------
519 WF_ENGINE.SetItemAttrNumber(l_item_type,
520 l_item_key,
521 'MANAGER_ID',
522 l_manager_id);
523
524 ------------------------------------------------------
525 l_debug_info := 'Set WF Manager_Name Item Attribute';
526 ------------------------------------------------------
527 WF_ENGINE.SetItemAttrText(l_item_type,
528 l_item_key,
529 'MANAGER_NAME',
530 l_manager_name);
531
532 --------------------------------------------------------------
533 l_debug_info := 'Set WF Manager_Display_Name Item Attribute';
534 --------------------------------------------------------------
535 WF_ENGINE.SetItemAttrText(l_item_type,
536 l_item_key,
537 'MANAGER_DISPLAY_NAME',
538 l_manager_display_name);
539
540 END IF;
541
542 IF (l_employee_id IS NOT NULL) THEN
543
544 ------------------------------------------------------------
545 l_debug_info := 'Get Name Info Associated With Employee_Id';
546 ------------------------------------------------------------
547 WF_DIRECTORY.GetUserName('PER',
548 l_employee_id,
549 l_employee_name,
550 l_employee_display_name);
551
552 ------------------------------------------------------
553 l_debug_info := 'Set WF Employee_ID Item Attribute';
554 ------------------------------------------------------
555 WF_ENGINE.SetItemAttrNumber(l_item_type,
556 l_item_key,
557 'EMPLOYEE_ID',
558 l_employee_id);
559
560 ------------------------------------------------------
561 l_debug_info := 'Set WF Employee_Name Item Attribute';
562 ------------------------------------------------------
563 WF_ENGINE.SetItemAttrText(l_item_type,
564 l_item_key,
565 'EMPLOYEE_NAME',
566 l_employee_name);
567
568 --------------------------------------------------------------
569 l_debug_info := 'Set WF Preparer_Display_Name Item Attribute';
570 --------------------------------------------------------------
571 WF_ENGINE.SetItemAttrText(l_item_type,
572 l_item_key,
573 'EMPLOYEE_DISPLAY_NAME',
574 l_employee_display_name);
575
576 END IF;
577
578 --------------------------------------------------------
579 l_debug_info := 'Call JumpIntoFunction to retrieve URL';
580 --------------------------------------------------------
581 /*AP_WEB_INTERFACE_PKG.JumpIntoFunction(l_new_manager_approval_id,
582 'PCARD MANAGER APPR',
583 l_url);*/
584
585 -----------------------------------------------------
586 l_debug_info := 'Set EXPENSE DETAILS Item Attribute';
587 -----------------------------------------------------
588
589 -- Be sure to clear these values. If we are resubmitting, we don't want
590 -- the values from the previous process traversal to hang around.
591 WF_ENGINE.SetItemAttrText(l_item_type,
592 l_item_key,
593 'PCARD_TRANS_DETAILS',
594 l_url);
595 /*MOAC CHANGES MADE FOR PCARD PROJECT*/
596 WF_ENGINE.SetItemAttrNumber(l_item_type,
597 l_item_key,
598 'ORG_ID',
599 nvl(p_org_id,mo_utils.get_default_org_id));
600
601
602 ------------------------------------------------------------
603 l_debug_info := 'Start the Expense Report Workflow Process';
604 ------------------------------------------------------------
605 WF_ENGINE.StartProcess(l_item_type,
606 l_item_key);
607
608
609 ELSE
610
611 l_records_undistributed_flag := TRUE;
612 errbuf := 'No records selected for Manager Approval';
613 retcode := '1';
614
615 END IF;
616
617 end if; -- (l_manager_id is not null)
618 END LOOP;
619
620 CLOSE Manager_Approval_Workflows;
621
622 COMMIT;
623
624 IF (l_records_undistributed_flag) THEN
625 errbuf := 'There were records out there that somehow did not get ' ||
626 'distributed to a manager.';
627 retcode := '1';
628 ELSE
629 retcode := 0;
630 END IF;
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 IF (SQLCODE <> -20001) THEN
635 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
636 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
637 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DistributeManagerApprovals');
638 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
639 errbuf := FND_MESSAGE.Get;
640 retcode := 2;
641 END IF;
642 END DistributeManagerApprovals;
643
644
645 -------------------------------------
646 PROCEDURE OpenP(p1 varchar2,
647 p2 varchar2,
648 p11 varchar2 Default NULL) IS
649 -------------------------------------
650 l_param varchar2(240);
651 c_rowid varchar2(20);
652 l_document_type varchar2(30) := icx_call.decrypt(p2);
653 l_session_id number;
654 --l_icx_application_id number := AP_WEB_INTERFACE_PKG.GetICXApplicationId;
655 l_application_id number := 200;
656 BEGIN
657 /*
658 IF AP_WEB_INTERFACE_PKG.ValidateSession THEN
659
660 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
661 AP_WEB_INTERFACE_PKG.ICXSetOrgContext(l_session_id, p11);
662
663 -- The following information needs to be set up through ON forms, on
664 -- particular page relations.
665
666 IF (l_document_type = 'PCARD EMP VERI') THEN
667
668 SELECT rowidtochar(ROWID)
669 INTO c_rowid
670 FROM AK_FLOW_REGION_RELATIONS
671 WHERE FROM_REGION_CODE = 'AP_EXP_NOTIFICATIONS'
672 AND FROM_REGION_APPL_ID = l_application_id
673 AND FROM_PAGE_CODE = 'AP_EXP_EMP_NOTIFY'
674 AND FROM_PAGE_APPL_ID = l_application_id
675 AND TO_PAGE_CODE = 'AP_EXP_FEED_DISTS_OPEN'
676 AND TO_PAGE_APPL_ID = l_application_id
677 AND FLOW_CODE = 'AP_CARD_INQUIRIES'
678 AND FLOW_APPLICATION_ID = l_application_id;
679
680 l_param := 'D*****1****' || c_rowid || '*AP_CARD_NOTIFICATIONS_PK1*' || icx_call.decrypt(p1) || '**]';
681
682 -- l_param := 'W*200*AP_CARD_INQUIRIES*200*AP_EXP_FEED_DIST_OPEN*where employee_verification_id = ' || icx_call.decrypt(p1) || '**]';
683
684 ELSIF (l_document_type = 'PCARD MANAGER APPR' ) THEN
685
686 SELECT rowidtochar(ROWID)
687 INTO c_rowid
688 FROM AK_FLOW_REGION_RELATIONS
689 WHERE FROM_REGION_CODE = 'AP_EXP_NOTIFICATIONS'
690 AND FROM_REGION_APPL_ID = l_application_id
691 AND FROM_PAGE_CODE = 'AP_EXP_MGR_NOTIFY'
692 AND FROM_PAGE_APPL_ID = l_application_id
693 AND TO_PAGE_CODE = 'AP_EXP_FEED_DISTS_HIST'
694 AND TO_PAGE_APPL_ID = l_application_id
695 AND FLOW_CODE = 'AP_CARD_INQUIRIES'
696 AND FLOW_APPLICATION_ID = l_application_id;
697
698 -- l_param := 'D*****1****' || c_rowid || '*AP_EXP_FEED_DISTS_PK1*' || icx_call.decrypt(p1) || '**]';
699
700 l_param := 'D*****1****' || c_rowid || '*AP_CARD_NOTIFICATIONS_PK1*' || icx_call.decrypt(p1) || '**]';
701
702 END IF;
703
704
705 IF (l_session_id IS NULL) THEN
706 OracleOn.IC(Y=>icx_call.encrypt2(l_param,-999));
707 ELSE
708 OracleOn.IC(Y=>icx_call.encrypt2(l_param,l_session_id));
709 END IF;
710
711 END IF;
712 */
713 null;
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 c_rowid := NULL;
717 WHEN OTHERS THEN
718 htp.p(SQLERRM);
719 END OpenP;
720
721
722 PROCEDURE EmpVfyPCardSetOrgContext(p_employee_verification_id IN NUMBER) IS
723 l_org_id NUMBER;
724 l_debug_info VARCHAR2(2000);
725 BEGIN
726 SELECT distinct(nvl(fl.org_id,fd.org_id))
727 INTO l_org_id
728 FROM ap_expense_feed_lines_all fl,
729 ap_expense_feed_dists_all fd
730 WHERE fl.employee_verification_id = p_employee_verification_id
731 OR (fd.feed_line_id = fl.feed_line_id AND
732 fd.employee_verification_id = p_employee_verification_id);
733 if (l_org_id is not null) then
734 Mo_Global.set_policy_context('S', l_org_id);
735 else
736 raise NO_DATA_FOUND;
737 end if;
738
739 EXCEPTION
740 WHEN OTHERS THEN
741 IF (SQLCODE <> -20001) THEN
742 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
743 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
744 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'EmpVfyPCardSetOrgContext');
745 END IF;
746 APP_EXCEPTION.RAISE_EXCEPTION;
747 END EmpVfyPCardSetOrgContext;
748
749 PROCEDURE MgrAprvlPCardSetOrgContext(p_manager_approval_id IN NUMBER) IS
750 l_org_id NUMBER;
751 BEGIN
752
753 SELECT distinct(org_id)
754 INTO l_org_id
755 FROM ap_expense_feed_dists_all fd
756 WHERE fd.manager_approval_id = p_manager_approval_id;
757
758 if (l_org_id is not null) then
759 Mo_Global.set_policy_context('S', l_org_id);
760 else
761 raise NO_DATA_FOUND;
762 end if;
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 IF (SQLCODE <> -20001) THEN
767 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
768 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
769 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MgrAprvlPCardSetOrgContext');
770 END IF;
771 APP_EXCEPTION.RAISE_EXCEPTION;
772 END MgrAprvlPCardSetOrgContext;
773
774 ------------------------------------------------------------------------
775 PROCEDURE CheckEmpNotificationMethod(p_item_type IN VARCHAR2,
776 p_item_key IN VARCHAR2,
777 p_actid IN NUMBER,
778 p_funmode IN VARCHAR2,
779 p_result OUT NOCOPY VARCHAR2) IS
780 ------------------------------------------------------------------------
781 l_emp_notification_method VARCHAR2(1);
782 l_return_error_message VARCHAR2(2000) := NULL;
783 l_debug_info VARCHAR2(200);
784 BEGIN
785
786 IF (p_funmode = 'RUN') THEN
787
788 -------------------------------------------------------------
789 l_debug_info := 'Set Emp Notification Method Item Attribute';
790 -------------------------------------------------------------
791 l_emp_notification_method := WF_ENGINE.GetItemAttrText(p_item_type,
792 p_item_key,
793 'EMPLOYEE_NOTIFICATION_METHOD');
794
795 IF (l_emp_notification_method = 'I') THEN
796 p_result := 'COMPLETE:AP_INFORM_ONLY';
797 ELSIF (l_emp_notification_method = 'Y') THEN
798 p_result := 'COMPLETE:AP_VERIFICATION_REQ';
799 ELSE
800 p_result := 'COMPLETE:AP_NONE';
801 END IF;
802
803 ELSIF (p_funmode = 'CANCEL') THEN
804
805 p_result := 'COMPLETE';
806
807 END IF;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 Wf_Core.Context('AP_WEB_PCARD_WORKFLOW_PKG', 'CheckEmpNotificationMethod',
812 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
813 raise;
814 END CheckEmpNotificationMethod;
815
816 ------------------------------------------------------------------------
817 PROCEDURE MarkRemainingTransVerified(p_item_type IN VARCHAR2,
818 p_item_key IN VARCHAR2,
819 p_actid IN NUMBER,
820 p_funmode IN VARCHAR2,
821 p_result OUT NOCOPY VARCHAR2) IS
822 ------------------------------------------------------------------------
823 l_emp_verification_id NUMBER;
824 l_status_lookup_code VARCHAR2(25);
825 l_return_error_message VARCHAR2(2000) := NULL;
826 l_debug_info VARCHAR2(200);
827 l_org_id NUMBER;
828 BEGIN
829
830 IF (p_funmode = 'RUN') THEN
831
832 -------------------------------------------------------------
833 l_debug_info := 'Set Emp Notification Method Item Attribute';
834 -------------------------------------------------------------
835 l_emp_verification_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
836 p_item_key,
837 'EMPLOYEE_VERIFICATION_ID');
838
839 --------------------------------------------------------
840 l_debug_info := 'Set Status Lookup Code Item Attribute';
841 ---------------------------------------------------------
842 l_status_lookup_code := WF_ENGINE.GetItemAttrText(p_item_type,
843 p_item_key,
844 'STATUS_LOOKUP_CODE');
845
846 ----------------------------------
847 l_debug_info := 'Set Org Context';
848 ----------------------------------
849 -- EmpVfyPCardSetOrgContext(l_emp_verification_id);
850 l_org_id:= WF_ENGINE.GetItemAttrNumber(p_item_type,
851 p_item_key,
852 'ORG_ID');
853 Mo_Global.set_policy_context('S', l_org_id);
854 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
855
856
857 -------------------------------------------
858 l_debug_info := 'Update Expense Feed Dists';
859 -------------------------------------------
860 BEGIN
861 UPDATE ap_expense_feed_dists
862 SET status_lookup_code = 'VERIFIED'
863 WHERE employee_verification_id = l_emp_verification_id
864 AND (status_lookup_code = l_status_lookup_code
865 OR l_status_lookup_code IS NULL);
866 EXCEPTION
867 WHEN OTHERS THEN
868 NULL;
869 END;
870
871 ELSIF (p_funmode = 'CANCEL') THEN
872
873 p_result := 'COMPLETE';
874
875 END IF;
876
877 EXCEPTION
878 WHEN OTHERS THEN
879 Wf_Core.Context('AP_WEB_PCARD_WORKFLOW_PKG', 'MarkRemainingTransVerified',
880 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
881 raise;
882 END MarkRemainingTransVerified;
883
884 ------------------------------------------------------------------------
885 PROCEDURE AutoApprvVeriTransNotReqAprvl(p_item_type IN VARCHAR2,
886 p_item_key IN VARCHAR2,
887 p_actid IN NUMBER,
888 p_funmode IN VARCHAR2,
889 p_result OUT NOCOPY VARCHAR2) IS
890 ------------------------------------------------------------------------
891 l_emp_verification_id NUMBER;
892 l_return_error_message VARCHAR2(2000) := NULL;
893 l_debug_info VARCHAR2(200);
894 l_org_id NUMBER;
895 BEGIN
896
897 IF (p_funmode = 'RUN') THEN
898
899 -------------------------------------------------------------
900 l_debug_info := 'Set Emp Notification Method Item Attribute';
901 -------------------------------------------------------------
902 l_emp_verification_id := WF_ENGINE.GetItemAttrText(p_item_type,
903 p_item_key,
904 'EMPLOYEE_VERIFICATION_ID');
905
906 ----------------------------------
907 l_debug_info := 'Set Org Context';
908 ----------------------------------
909 --EmpVfyPCardSetOrgContext(l_emp_verification_id);
910 l_org_id:= WF_ENGINE.GetItemAttrNumber(p_item_type,
911 p_item_key,
912 'ORG_ID');
913 Mo_Global.set_policy_context('S', l_org_id);
914 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
915 BEGIN
916 --bug5058949
917 --Performance fix
918 UPDATE ap_expense_feed_dists fd
919 SET status_lookup_code = 'APPROVED'
920 WHERE employee_verification_id = l_emp_verification_id
921 AND status_lookup_code = 'VERIFIED'
922 AND exists (select 'no manager approval required'
923 from ap_expense_feed_lines fl,
924 ap_cards c,
925 ap_card_profiles cp,
926 IBY_CREDITCARD IBY
927 WHERE fl.feed_line_id = fd.feed_line_id
928 AND c.card_reference_id=IBY.instrid
929 AND fl.card_id = c.card_id
930 AND c.profile_id = cp.profile_id
931 AND nvl(cp.mgr_approval_lookup_code,'N') = 'N');
932 EXCEPTION
933 WHEN OTHERS THEN
934 NULL;
935 END;
936
937 ELSIF (p_funmode = 'CANCEL') THEN
938
939 p_result := 'COMPLETE';
940
941 END IF;
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 Wf_Core.Context('AP_WEB_PCARD_WORKFLOW_PKG',
946 'AutoApprvVeriTransNotReqAprvl',
947 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
948 raise;
949 END AutoApprvVeriTransNotReqAprvl;
950
951 ------------------------------------------------------------------------
952 PROCEDURE CheckEmpVerificationComplete(p_item_type IN VARCHAR2,
953 p_item_key IN VARCHAR2,
954 p_actid IN NUMBER,
955 p_funmode IN VARCHAR2,
956 p_result OUT NOCOPY VARCHAR2) IS
957 ------------------------------------------------------------------------
958 l_emp_verification_id NUMBER;
959 l_orig_status_lookup_code VARCHAR2(25);
960 l_num_dists_not_processed NUMBER;
961 l_return_error_message VARCHAR2(2000) := NULL;
962 l_debug_info VARCHAR2(200);
963 l_org_id number;
964 BEGIN
965
966 IF (p_funmode = 'RUN') THEN
967
968 -------------------------------------------------------------
969 l_debug_info := 'Set Emp Notification Method Item Attribute';
970 -------------------------------------------------------------
971 l_emp_verification_id := WF_ENGINE.GetItemAttrText(p_item_type,
972 p_item_key,
973 'EMPLOYEE_VERIFICATION_ID');
974
975 --------------------------------------------------------
976 l_debug_info := 'Set Status Lookup Code Item Attribute';
977 ---------------------------------------------------------
978 l_orig_status_lookup_code := WF_ENGINE.GetItemAttrText(p_item_type,
979 p_item_key,
980 'STATUS_LOOKUP_CODE');
981
982 ----------------------------------
983 l_debug_info := 'Set Org Context';
984 ----------------------------------
985 --EmpVfyPCardSetOrgContext(l_emp_verification_id);
986 l_org_id:= WF_ENGINE.GetItemAttrNumber(p_item_type,
987 p_item_key,
988 'ORG_ID');
989 Mo_Global.set_policy_context('S', l_org_id);
990 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
991
992
993 SELECT count(*)
994 INTO l_num_dists_not_processed
995 FROM ap_expense_feed_dists
996 WHERE employee_verification_id = l_emp_verification_id
997 AND status_lookup_code = l_orig_status_lookup_code;
998
999 IF (l_num_dists_not_processed > 0) THEN
1000 p_result := 'COMPLETE:N';
1001 ELSE
1002 p_result := 'COMPLETE:Y';
1003 END IF;
1004
1005 ELSIF (p_funmode = 'CANCEL') THEN
1006
1007 p_result := 'COMPLETE';
1008
1009 END IF;
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 IF (SQLCODE <> -20001) THEN
1014 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1015 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1016 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','CheckEmpVerificationComplete');
1017 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1018 END IF;
1019 APP_EXCEPTION.RAISE_EXCEPTION;
1020 END CheckEmpVerificationComplete;
1021
1022 ------------------------------------------------------------------------------
1023 PROCEDURE BuildEmpVerificationMessage(p_item_type IN VARCHAR2,
1024 p_item_key IN VARCHAR2,
1025 p_actid IN NUMBER,
1026 p_funmode IN VARCHAR2,
1027 p_result OUT NOCOPY VARCHAR2) IS
1028 ------------------------------------------------------------------------------
1029 l_emp_verification_id NUMBER;
1030 l_status_lookup_code VARCHAR2(25);
1031 l_transaction_date VARCHAR2(15);
1032 l_merchant_name VARCHAR2(30);
1033 l_amount NUMBER;
1034 l_currency_code VARCHAR2(15);
1035 l_message_name VARCHAR2(30);
1036 l_debug_info VARCHAR2(1000);
1037 l_line_info VARCHAR2(200);
1038 l_attribute_name VARCHAR2(30);
1039 l_line_info_body1 VARCHAR2(2000) := '';
1040 l_line_info_body2 VARCHAR2(2000) := '';
1041 -- c_prompts AP_WEB_INTERFACE_PKG.prompts_table;
1042 c_title VARCHAR2(80);
1043 i NUMBER;
1044 j NUMBER;
1045 l_num_lines NUMBER;
1046 l_message_text VARCHAR2(2000);
1047 l_description VARCHAR2(240);
1048 --bug5058949
1049 --Performance fix
1050
1051 CURSOR EmpPCardTransactions IS
1052 SELECT fl.transaction_date,
1053 rpad(merchant_name,30),
1054 fl.amount,
1055 nvl(fl.posted_currency_code, cpr.card_program_currency_code),
1056 fd.description
1057 FROM ap_expense_feed_dists fd,
1058 ap_expense_feed_lines fl,
1059 ap_cards c,
1060 ap_card_profiles cp,
1061 ap_card_programs cpr,
1062 IBY_CREDITCARD IBY
1063 WHERE ((fd.employee_verification_id = l_emp_verification_id AND
1064 fd.feed_line_id = fl.feed_line_id) OR
1065 (fl.employee_verification_id = l_emp_verification_id))
1066 AND fl.card_id = c.card_id
1067 AND c.card_reference_id=IBY.instrid
1068 AND c.profile_id = cp.profile_id
1069 AND cp.card_program_id = cpr.card_program_id;
1070 l_org_id number;
1071 BEGIN
1072
1073 IF (p_funmode = 'RUN') THEN
1074
1075 --------------------------------------------------------------------
1076 l_debug_info := 'Retrieve Employee Verification ID Item Attribute';
1077 ---------------------------------------------------------------------
1078 l_emp_verification_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1079 p_item_key,
1080 'EMPLOYEE_VERIFICATION_ID');
1081 l_org_id:=WF_ENGINE.GetItemAttrNumber(p_item_type,
1082 p_item_key,
1083 'ORG_ID');
1084
1085 Mo_Global.set_policy_context('S', l_org_id);
1086
1087 l_status_lookup_code := WF_ENGINE.GetItemAttrNumber(p_item_type,
1088 p_item_key,
1089 'STATUS_LOOKUP_CODE');
1090
1091 IF (l_status_lookup_code = 'VALIDATED') THEN
1092
1093 l_message_name := 'AP_PCARD_EMP_VERIFY_VALIDATED';
1094
1095 ELSIF (l_status_lookup_code = 'HOLD') THEN
1096
1097 l_message_name := 'AP_PCARD_EMP_VERIFY_HELD';
1098
1099 ELSIF (l_status_lookup_code = 'REJECTED') THEN
1100
1101 l_message_name := 'AP_PCARD_EMP_VERIFY_REJECTED';
1102
1103 ELSE
1104
1105 -- raise exception
1106 NULL;
1107
1108 END IF;
1109
1110 fnd_message.set_name('SQLAP', l_message_name);
1111 l_message_text := FND_MESSAGE.Get;
1112 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'EMPLOYEE_VERIFICATION_ID',l_emp_verification_id);
1113 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
1114
1115 WF_ENGINE.SetItemAttrText(p_item_type,
1116 p_item_key,
1117 'EMP_VERIFY_MSG_INTRO',
1118 l_message_text);
1119
1120 ----------------------------------
1121 l_debug_info := 'Set Org Context';
1122 ----------------------------------
1123 --EmpVfyPCardSetOrgContext(l_emp_verification_id);
1124
1125 ----------------------------------------------------------
1126 l_debug_info := 'Open Employee Verification Lines Cursor';
1127 ----------------------------------------------------------
1128 /*OPEN EmpPCardTransactions;
1129
1130
1131 FOR i IN 1..100 LOOP
1132
1133 -----------------------------------------------------------
1134 l_debug_info := 'Fetch employee verification lines Cursor';
1135 -----------------------------------------------------------
1136 FETCH EmpPCardTransactions INTO l_transaction_date,
1137 l_merchant_name,
1138 l_amount,
1139 l_currency_code,
1140 l_description;
1141
1142 EXIT WHEN EmpPCardTransactions%NOTFOUND;
1143
1144 ---------------------------------------------------------
1145 l_debug_info := 'Format Employee Verification Line Info';
1146 ---------------------------------------------------------
1147 l_line_info := '> ' || l_transaction_date || ' ' || l_currency_code || ' '
1148 || LPAD(to_char(l_amount,
1149 FND_CURRENCY.Get_Format_Mask(l_currency_code,22)),14)
1150 || ' ' || l_merchant_name;
1151
1152 --------------------------------------------------------
1153 l_debug_info := 'Set Item Attribute Name for line_info';
1154 --------------------------------------------------------
1155 l_attribute_name := 'LINE_INFO' || to_char(2*i-1);
1156
1157 ------------------------------------------------------------------------
1158 l_debug_info := 'Set Line_Info Item Attribute with formatted expense line';
1159 ---------------------------------------------------------------------------
1160 WF_ENGINE.SetItemAttrText(p_item_type,
1161 p_item_key,
1162 l_attribute_name,
1163 l_line_info);
1164
1165
1166 -----------------------------------------------------------------
1167 l_debug_info := 'Set Item Attribute Name for line justification';
1168 -----------------------------------------------------------------
1169 l_attribute_name := 'LINE_INFO' || to_char(2*i);
1170
1171 ---------------------------------------------------------
1172 l_debug_info := 'Set Line Justification Item Attribute';
1173 ---------------------------------------------------------
1174 WF_ENGINE.SetItemAttrText(p_item_type,
1175 p_item_key,
1176 l_attribute_name,
1177 '----> ' || l_description);
1178
1179 l_line_info := '';
1180 l_num_lines := i;
1181 END LOOP;
1182
1183 ---------------------------------------------------------
1184 l_debug_info := 'Populating line_info_body with tokens';
1185 ---------------------------------------------------------
1186
1187 FOR j in 1..l_num_lines LOOP
1188
1189 ------------------------------------------
1190 l_debug_info := 'j equals ' || to_char(j);
1191 ------------------------------------------
1192 IF (j < 50) THEN
1193
1194 l_line_info_body1 := l_line_info_body1 || '
1195 ' || '&LINE_INFO' || to_char(2*j-1) || '
1196 ' || '&LINE_INFO' || to_char(2*j);
1197
1198 ELSE
1199
1200 l_line_info_body2 := l_line_info_body2 || '
1201 ' || '&LINE_INFO' || to_char(2*j-1) || '
1202 ' || '&LINE_INFO' || to_char(2*j);
1203
1204 END IF;
1205
1206 END LOOP;*/
1207
1208 ---------------------------------------------------------
1209 l_debug_info := 'Set Item Attribute Line_Info_Body1';
1210 ---------------------------------------------------------
1211 WF_ENGINE.SetItemAttrText(p_item_type,
1212 p_item_key,
1213 'LINE_BODY_00001',
1214 l_line_info_body1);
1215
1216 IF (i > 50) THEN
1217
1218 ---------------------------------------------------------
1219 l_debug_info := 'Set Item Attribute Line_Info_Body2';
1220 ---------------------------------------------------------
1221 WF_ENGINE.SetItemAttrText(p_item_type,
1222 p_item_key,
1223 'LINE_BODY_00002',
1224 l_line_info_body2);
1225
1226 END IF;
1227
1228 ELSIF (p_funmode = 'CANCEL') THEN
1229
1230 p_result := 'COMPLETE';
1231
1232 END IF;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'BuildEmpVerificationMessage',
1237 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1238 raise;
1239 END BuildEmpVerificationMessage;
1240
1241 ------------------------------------------------------------------------
1242 PROCEDURE CheckManagerApprovalMethod(p_item_type IN VARCHAR2,
1243 p_item_key IN VARCHAR2,
1244 p_actid IN NUMBER,
1245 p_funmode IN VARCHAR2,
1246 p_result OUT NOCOPY VARCHAR2) IS
1247 ------------------------------------------------------------------------
1248 l_manager_approval_method VARCHAR2(1);
1249 l_return_error_message VARCHAR2(2000) := NULL;
1250 l_debug_info VARCHAR2(200);
1251 BEGIN
1252
1253 IF (p_funmode = 'RUN') THEN
1254
1255 -------------------------------------------------------------
1256 l_debug_info := 'Set Emp Notification Method Item Attribute';
1257 -------------------------------------------------------------
1258 l_manager_approval_method := WF_ENGINE.GetItemAttrText(p_item_type,
1259 p_item_key,
1260 'MANAGER_APPROVAL_METHOD');
1261
1262 IF (l_manager_approval_method = 'I') THEN
1263 p_result := 'COMPLETE:AP_INFORM_ONLY';
1264 ELSIF (l_manager_approval_method = 'Y') THEN
1265 p_result := 'COMPLETE:AP_VERIFICATION_REQ';
1266 ELSE
1267 p_result := 'COMPLETE:AP_NONE';
1268 END IF;
1269
1270 ELSIF (p_funmode = 'CANCEL') THEN
1271
1272 p_result := 'COMPLETE';
1273
1274 END IF;
1275
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 IF (SQLCODE <> -20001) THEN
1279 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1280 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1281 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CheckManagerApprovalMethod');
1282 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1283 END IF;
1284 APP_EXCEPTION.RAISE_EXCEPTION;
1285 END CheckManagerApprovalMethod;
1286
1287 ------------------------------------------------------------------------
1288 PROCEDURE MarkTransactionsAsRejected(p_item_type IN VARCHAR2,
1289 p_item_key IN VARCHAR2,
1290 p_actid IN NUMBER,
1291 p_funmode IN VARCHAR2,
1292 p_result OUT NOCOPY VARCHAR2) IS
1293 ------------------------------------------------------------------------
1294 l_manager_approval_id NUMBER;
1295 l_return_error_message VARCHAR2(2000) := NULL;
1296 l_debug_info VARCHAR2(200);
1297 l_org_id number;
1298 BEGIN
1299
1300 IF (p_funmode = 'RUN') THEN
1301
1302 -------------------------------------------------------------
1303 l_debug_info := 'Set Emp Notification Method Item Attribute';
1304 -------------------------------------------------------------
1305 l_manager_approval_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1306 p_item_key,
1307 'MANAGER_APPROVAL_ID');
1308
1309 ----------------------------------
1310 l_debug_info := 'Set Org Context';
1311 ----------------------------------
1312 --MgrAprvlPCardSetOrgContext(l_manager_approval_id);
1313 l_org_id:= WF_ENGINE.GetItemAttrNumber(p_item_type,
1314 p_item_key,
1315 'ORG_ID');
1316 Mo_Global.set_policy_context('S', l_org_id);
1317 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
1318
1319
1320 BEGIN
1321 UPDATE ap_expense_feed_dists
1322 SET status_lookup_code = 'REJECTED'
1323 WHERE manager_approval_id = l_manager_approval_id;
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 NULL;
1327 END;
1328
1329 ELSIF (p_funmode = 'CANCEL') THEN
1330
1331 p_result := 'COMPLETE';
1332
1333 END IF;
1334
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 IF (SQLCODE <> -20001) THEN
1338 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1339 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1340 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MarkTransactionsAsRejected');
1341 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1342 END IF;
1343 APP_EXCEPTION.RAISE_EXCEPTION;
1344 END MarkTransactionsAsRejected;
1345
1346 ------------------------------------------------------------------------
1347 PROCEDURE MarkTransactionsAsApproved(p_item_type IN VARCHAR2,
1348 p_item_key IN VARCHAR2,
1349 p_actid IN NUMBER,
1350 p_funmode IN VARCHAR2,
1351 p_result OUT NOCOPY VARCHAR2) IS
1352 ------------------------------------------------------------------------
1353 l_manager_approval_id NUMBER;
1354 l_return_error_message VARCHAR2(2000) := NULL;
1355 l_debug_info VARCHAR2(200);
1356 l_org_id number;
1357 BEGIN
1358
1359 IF (p_funmode = 'RUN') THEN
1360
1361 -------------------------------------------------------------
1362 l_debug_info := 'Set Emp Notification Method Item Attribute';
1363 -------------------------------------------------------------
1364 l_manager_approval_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1365 p_item_key,
1366 'MANAGER_APPROVAL_ID');
1367 ----------------------------------
1368 l_debug_info := 'Set Org Context';
1369 ----------------------------------
1370 --MgrAprvlPCardSetOrgContext(l_manager_approval_id);
1371 l_org_id:= WF_ENGINE.GetItemAttrNumber(p_item_type,
1372 p_item_key,
1373 'ORG_ID');
1374 Mo_Global.set_policy_context('S', l_org_id);
1375 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
1376
1377 BEGIN
1378 UPDATE ap_expense_feed_dists
1379 SET status_lookup_code = 'APPROVED'
1380 WHERE manager_approval_id = l_manager_approval_id;
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 NULL;
1384 END;
1385
1386 ELSIF (p_funmode = 'CANCEL') THEN
1387
1388 p_result := 'COMPLETE';
1389
1390 END IF;
1391
1392 EXCEPTION
1393 WHEN OTHERS THEN
1394 IF (SQLCODE <> -20001) THEN
1395 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1396 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1397 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'MarkTransactionsAsApproved');
1398 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1399 END IF;
1400 APP_EXCEPTION.RAISE_EXCEPTION;
1401 END MarkTransactionsAsApproved;
1402
1403 ------------------------------------------------------------------------------
1404 PROCEDURE BuildManagerApprovalMessage(p_item_type IN VARCHAR2,
1405 p_item_key IN VARCHAR2,
1406 p_actid IN NUMBER,
1407 p_funmode IN VARCHAR2,
1408 p_result OUT NOCOPY VARCHAR2) IS
1409 ------------------------------------------------------------------------------
1410 l_manager_approval_id NUMBER;
1411 l_employee_id NUMBER;
1412 l_employee_name wf_users.name%type; --Bug 10220928
1413 l_employee_display_name wf_users.display_name%type; --Bug 10220928
1414 l_transaction_date VARCHAR2(15);
1415 l_merchant_name VARCHAR2(30);
1416 l_amount NUMBER;
1417 l_currency_code VARCHAR2(15);
1418 l_description VARCHAR2(240);
1419 l_debug_info VARCHAR2(1000);
1420 l_line_info_body1 VARCHAR2(2000) := '';
1421 l_line_info_body2 VARCHAR2(2000) := '';
1422 -- c_prompts AP_WEB_INTERFACE_PKG.prompts_table;
1423 c_title VARCHAR2(80);
1424 i NUMBER;
1425 j NUMBER;
1426 l_num_lines NUMBER;
1427 l_line_info VARCHAR2(2000);
1428 l_attribute_name VARCHAR2(30);
1429 l_org_id number;
1430 CURSOR MgrPCardTransactions IS
1431 SELECT fl.transaction_date,
1432 fl.employee_id,
1433 rpad(merchant_name,30),
1434 fl.amount,
1435 nvl(fl.posted_currency_code, cpr.card_program_currency_code),
1436 fd.description
1437 FROM ap_expense_feed_dists fd,
1438 ap_expense_feed_lines fl,
1439 ap_cards c,
1440 ap_card_profiles cp,
1441 ap_card_programs cpr,
1442 IBY_CREDITCARD IBY
1443 WHERE fd.manager_approval_id = l_manager_approval_id
1444 AND fd.feed_line_id = fl.feed_line_id
1445 AND fl.card_id = c.card_id
1446 AND c.card_reference_id=IBY.instrid
1447 AND c.profile_id = cp.profile_id
1448 AND cp.card_program_id = cpr.card_program_id
1449 ORDER BY fl.employee_id, fl.transaction_date;
1450 BEGIN
1451
1452 IF (p_funmode = 'RUN') THEN
1453
1454 --------------------------------------------------------------------
1455 l_debug_info := 'Retrieve Employee Verification ID Item Attribute';
1456 ---------------------------------------------------------------------
1457 l_manager_approval_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1458 p_item_key,
1459 'MANAGER_APPROVAL_ID');
1460
1461 ----------------------------------
1462 l_debug_info := 'Set Org Context';
1463 ----------------------------------
1464 --MgrAprvlPCardSetOrgContext(l_manager_approval_id);
1465 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'MANAGER_APPROVAL_ID',l_manager_approval_id);
1466 l_org_id:= WF_ENGINE.GetItemAttrNumber(p_item_type,
1467 p_item_key,
1468 'ORG_ID');
1469 Mo_Global.set_policy_context('S', l_org_id);
1470 WF_ENGINE.SetItemAttrNumber(p_item_type,p_item_key,'ORG_ID',l_org_id);
1471
1472 --------------------------------------------
1473 l_debug_info := 'Open Expense Lines Cursor';
1474 --------------------------------------------
1475 OPEN MgrPCardTransactions;
1476
1477
1478 FOR i IN 1..100 LOOP
1479
1480 --------------------------------------------
1481 l_debug_info := 'Fetch Expense Lines Cursor';
1482 --------------------------------------------
1483 FETCH MgrPCardTransactions INTO l_transaction_date,
1484 l_employee_id,
1485 l_merchant_name,
1486 l_amount,
1487 l_currency_code,
1488 l_description;
1489
1490 EXIT WHEN MgrPCardTransactions%NOTFOUND;
1491
1492
1493 ------------------------------------------------------------
1494 l_debug_info := 'Get Name Info Associated With Employee_Id';
1495 ------------------------------------------------------------
1496 WF_DIRECTORY.GetUserName('PER',
1497 l_employee_id,
1498 l_employee_name,
1499 l_employee_display_name);
1500 /*
1501 --------------------------------------------
1502 l_debug_info := 'Format Expense Line Info';
1503 --------------------------------------------
1504 l_line_info := '> ' || l_transaction_date || ' ' || l_currency_code || ' '
1505 || LPAD(to_char(l_amount,
1506 FND_CURRENCY.Get_Format_Mask(l_currency_code,22)),14)
1507 || ' ' || l_merchant_name;
1508
1509 --------------------------------------------------------
1510 l_debug_info := 'Set Item Attribute Name for line_info';
1511 --------------------------------------------------------
1512 l_attribute_name := 'LINE_INFO' || to_char(2*i-1);
1513
1514 ------------------------------------------------------------------------
1515 l_debug_info := 'Set Line_Info Item Attribute with formatted expense line';
1516 ---------------------------------------------------------------------------
1517 WF_ENGINE.SetItemAttrText(p_item_type,
1518 p_item_key,
1519 l_attribute_name,
1520 l_line_info);
1521
1522
1523 -----------------------------------------------------------------
1524 l_debug_info := 'Set Item Attribute Name for line justification';
1525 -----------------------------------------------------------------
1526 l_attribute_name := 'LINE_INFO' || to_char(2*i);
1527
1528 ---------------------------------------------------------
1529 l_debug_info := 'Set Line Justification Item Attribute';
1530 ---------------------------------------------------------
1531 WF_ENGINE.SetItemAttrText(p_item_type,
1532 p_item_key,
1533 l_attribute_name,
1534 '----> ' || l_description);
1535 */
1536 l_line_info := '';
1537 l_num_lines := i;
1538 END LOOP;
1539
1540 ---------------------------------------------------------
1541 l_debug_info := 'Populating line_info_body with tokens';
1542 ---------------------------------------------------------
1543 /*
1544 FOR j in 1..l_num_lines LOOP
1545
1546 ------------------------------------------
1547 l_debug_info := 'j equals ' || to_char(j);
1548 ------------------------------------------
1549 IF (j < 50) THEN
1550
1551 l_line_info_body1 := l_line_info_body1 || '
1552 ' || '&LINE_INFO' || to_char(2*j-1) || '
1553 ' || '&LINE_INFO' || to_char(2*j);
1554
1555 ELSE
1556
1557 l_line_info_body2 := l_line_info_body2 || '
1558 ' || '&LINE_INFO' || to_char(2*j-1) || '
1559 ' || '&LINE_INFO' || to_char(2*j);
1560
1561 END IF;
1562
1563 END LOOP;*/
1564
1565 ---------------------------------------------------------
1566 l_debug_info := 'Set Item Attribute Line_Info_Body1';
1567 ---------------------------------------------------------
1568 WF_ENGINE.SetItemAttrText(p_item_type,
1569 p_item_key,
1570 'LINE_BODY_00001',
1571 l_line_info_body1);
1572
1573 IF (i > 50) THEN
1574
1575 ---------------------------------------------------------
1576 l_debug_info := 'Set Item Attribute Line_Info_Body2';
1577 ---------------------------------------------------------
1578 WF_ENGINE.SetItemAttrText(p_item_type,
1579 p_item_key,
1580 'LINE_BODY_00002',
1581 l_line_info_body2);
1582
1583 END IF;
1584
1585 ELSIF (p_funmode = 'CANCEL') THEN
1586
1587 p_result := 'COMPLETE';
1588
1589 END IF;
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'BuildManagerApprvalMessage',
1594 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1595 raise;
1596 END BuildManagerApprovalMessage;
1597
1598 END AP_WEB_PCARD_WORKFLOW_PKG;