[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_CREDIT_CARD_WF
Source
1 PACKAGE BODY AP_WEB_CREDIT_CARD_WF AS
2 /* $Header: apwccwfb.pls 120.61.12020000.2 2012/07/05 14:11:15 rveliche ship $ */
3
4 /**** TEMP ***/
5 th_bgcolor varchar2(9) := '"#cccc99"';
6 th_fontcolor varchar2(9) := '"#336699"';
7 th_fontface varchar2(80) := '"Arial, Helvetica, Geneva, sans-serif"';
8 td_bgcolor varchar2(9) := '"#f7f7e7"';
9 td_fontcolor varchar2(7) := '"black"';
10 td_fontface varchar2(80) := '"Arial, Helvetica, Geneva, sans-serif"';
11 /**** TEMP ***/
12
13
14 indent_start varchar2(200) := '<table style="{background-color:#ffffff}" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td width="20"></td><td>';
15 indent_end varchar2(200) := '</td></tr></table>';
16
17 ----------------------------------
18 --.OraTableTitle {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:13pt;background-color:#ffffff;color:#336699}
19 ----------------------------------
20 table_title_start varchar2(200) := '<br><font style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:11pt;font-weight:bold;background-color:#ffffff;color:#336699}">';
21 table_title_end varchar2(200) := '</font><br><table width="100%"><tr bgcolor="#cccc99"><td height="1"></td></tr><tr bgcolor="#ffffff"><td height="2"></td></tr></table>';
22
23 ----------------------------------
24 --.OraTable {background-color:#999966}
25 ----------------------------------
26 table_start varchar2(200) := '<table style="{background-color:#999966}" width="100%" border="0" cellpadding="3" cellspacing="1">';
27 table_end varchar2(15) := '</table>';
28
29 tr_start varchar2(80) := '<tr bgcolor="#cccc99">';
30 tr_end varchar2(15) := '</tr>';
31
32 ----------------------------------
33 --.OraTableColumnHeaderIconButton {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:center}
34 ----------------------------------
35 th_select varchar2(200) := '<td style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:center}">';
36
37 ----------------------------------
38 -- .OraTableColumnHeader {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;text-align:left;background-color:#cccc99;color:#336699;vertical-align:bottom}
39 ----------------------------------
40 th_text varchar2(200) := '<td style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;text-align:left;background-color:#cccc99;color:#336699;vertical-align:bottom}">';
41
42 ----------------------------------
43 -- .OraTableColumnHeaderNumber {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:right}
44 ----------------------------------
45 th_number varchar2(200) := '<td style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:right}">';
46
47 ----------------------------------
48 -- .OraTableCellSelect {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text-align:center;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
49 ----------------------------------
50 td_select varchar2(200) := '<td style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text-align:center;background-color:#f7f7e7;color:#000000;vertical-align:baseline}">';
51
52 ----------------------------------
53 -- .OraTableCellText {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
54 ----------------------------------
55 td_text varchar2(200) := '<td style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;background-color:#f7f7e7;color:#000000;vertical-align:baseline}">';
56
57 ----------------------------------
58 -- .OraTableCellNumber {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text-align:right;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
59 ----------------------------------
60 td_number varchar2(200) := '<td style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text-align:right;background-color:#f7f7e7;color:#000000;vertical-align:baseline}">';
61
62 td_start varchar2(10) := '<td>';
63 td_end varchar2(10) := '</td>';
64
65 ------------------------
66 -- Constants definition
67 ------------------------
68
69
70 ---------------------------------------------------------------------------
71 FUNCTION GetNextCardNotificationID RETURN VARCHAR2 IS
72 ---------------------------------------------------------------------------
73 l_itemKey VARCHAR2(100);
74 BEGIN
75 SELECT to_char(ap_cCard_Notification_ID_s.nextval)
76 INTO l_itemKey
77 FROM sys.dual;
78 return l_itemKey;
79 EXCEPTION
80 WHEN OTHERS THEN
81 AP_WEB_DB_UTIL_PKG.RaiseException('GetNextCardNotificationID');
82 END GetNextCardNotificationID;
83
84 ---------------------------------------------------------------------------
85 PROCEDURE sendPaymentNotification(p_checkNumber IN NUMBER,
86 p_employeeId IN NUMBER,
87 p_paymentCurrency IN VARCHAR2,
88 p_invoiceNumber IN VARCHAR2,
89 p_paidAmount IN NUMBER,
90 p_paymentTo IN VARCHAR2,
91 p_paymentMethod IN VARCHAR2,
92 p_account IN VARCHAR2,
93 p_bankName IN VARCHAR2,
94 p_cardIssuer IN VARCHAR2,
95 p_paymentDate IN VARCHAR2,
96 p_deferred IN BOOLEAN)
97 ---------------------------------------------------------------------------
98 IS
99 l_itemType VARCHAR2(100) := 'APCCARD';
100 l_itemKey VARCHAR2(100);
101 l_employeeName wf_users.name%type;
102 l_employeeDisplayName wf_users.display_name%type;
103 l_threshold number := wf_engine.threshold;
104 l_debugInfo VARCHAR2(200);
105 l_account IBY_EXT_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE := NULL;
106 l_invoiceNum AP_EXPENSE_REPORT_HEADERS_ALL.INVOICE_NUM%TYPE;
107 l_process_created VARCHAR2(1);
108
109 BEGIN
110
111 ------------------------------------------------------------
112 l_debugInfo := 'Set WF threshold to defer this WF process';
113 ------------------------------------------------------------
114 if (p_deferred) then
115 wf_engine.threshold := -1;
116 end if;
117
118 l_itemKey := GetNextCardNotificationID;
119
120 /* Bug 2301574: Need to mask the credit card number */
121 l_account := '************' || substr(p_account,-4);
122
123 ------------------------------------------------------------
124 l_debugInfo := 'Get Name Info Associated With employee_Id';
125 ------------------------------------------------------------
126 WF_DIRECTORY.GetUserName('PER',
127 p_employeeId,
128 l_employeeName,
129 l_employeeDisplayName);
130
131 --------------------------------------------------
132 l_debugInfo := 'Calling WorkFlow Create Process';
133 --------------------------------------------------
134
135 l_process_created := 'N';
136 if (p_paymentTo is not null AND p_paymentTo = c_paymentToCardIssuer) then
137 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'PAYMENT_TO_CARD_ISSUER');
138 l_process_created := 'Y';
139 elsif (p_paymentTo is not null AND p_paymentTo = c_voidPayment) then
140 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'VOID_PAYMENT_PROCESS');
141 l_process_created := 'Y';
142 elsif (p_paymentMethod = c_directDeposit and p_paidAmount<>0) then
143 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'PAYMENT_TO_EMPLOYEE');
144 l_process_created := 'Y';
145 elsif(p_paidAmount<>0) then
146 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'PAYMENT_TO_EMP_BY_CHECK');
147 l_process_created := 'Y';
148 end if;
149
150 -- 8412820 : Attribute 'EXPENSE_REPORT_NUMBER' does not exist for item
151 if nvl(l_process_created,'N') = 'Y' then
152 ----------------------------------------------------------
153 l_debugInfo := 'Set WF EXPENSE_REPORT_NUMBER Item Attribute';
154 ----------------------------------------------------------
155 /* Bug 4102991 : The notification of payment to the credit card
156 * provider should not use the .1 invoice number.
157 */
158
159 IF (p_paymentTo is not null AND p_paymentTo = c_paymentToCardIssuer) then
160
161 BEGIN
162 SELECT aerh2.invoice_num
163 INTO l_invoiceNum
164 FROM ap_expense_report_headers_all aerh1,
165 ap_expense_report_headers_all aerh2
166 WHERE aerh1.bothpay_parent_id = aerh2.report_header_id
167 AND aerh1.invoice_num = p_invoiceNumber
168 AND aerh1.source = 'Both Pay';
169
170 EXCEPTION WHEN NO_DATA_FOUND THEN
171 l_invoiceNum := p_invoiceNumber;
172 END;
173
174 ELSE
175 l_invoiceNum := p_invoiceNumber;
176
177 END IF;
178
179 WF_ENGINE.SetItemAttrText(l_itemType,
180 l_itemKey,
181 'EXPENSE_REPORT_NUMBER',
182 l_invoiceNum);
183 ----------------------------------------------------------
184 l_debugInfo := 'Set WF Amount Item Attribute';
185 ----------------------------------------------------------
186 WF_ENGINE.SetItemAttrText(l_itemType,
187 l_itemKey,
188 'AMOUNT',
189 to_char(p_paidAmount, FND_CURRENCY.Get_Format_Mask(p_paymentCurrency,22)) || ' ' || p_paymentCurrency);
190
191 ----------------------------------------------------------
192 l_debugInfo := 'Set CURRENCY Item Attribute';
193 ----------------------------------------------------------
194 WF_ENGINE.SetItemAttrText(l_itemType,
195 l_itemKey,
196 'CURRENCY',
197 p_paymentCurrency);
198
199 ----------------------------------------------------------
200 l_debugInfo := 'Set the Owner of Workflow Process.';
201 ----------------------------------------------------------
202 WF_ENGINE.SetItemOwner(l_itemType, l_itemKey, l_employeeName);
203
204 ------------------------------------------------------
205 l_debugInfo := 'Set WF EMPLOYEE_NAME Item Attribute';
206 ------------------------------------------------------
207 WF_ENGINE.SetItemAttrText(l_itemType,
208 l_itemKey,
209 'EMPLOYEE_NAME',
210 l_employeeName);
211
212 --------------------------------------------------------------
213 l_debugInfo := 'Set WF EMPLOYEE_DISPLAY_NAME Item Attribute';
214 --------------------------------------------------------------
215 WF_ENGINE.SetItemAttrText(l_itemType,
216 l_itemKey,
217 'EMP_DISPLAY_NAME',
218 l_employeeDisplayName);
219
220 ------------------------------------------------------
221 l_debugInfo := 'Set WF Employee_ID Item Attribute';
222 ------------------------------------------------------
223 WF_ENGINE.SetItemAttrNumber(l_itemType,
224 l_itemKey,
225 'EMPLOYEE_ID',
226 p_employeeId);
227 ------------------------------------------------------
228 l_debugInfo := 'Set WF CHECK_NUMBER Item Attribute';
229 ------------------------------------------------------
230 WF_ENGINE.SetItemAttrText(l_itemType,
231 l_itemKey,
232 'CHECK_NUMBER',
233 to_char(p_checkNumber));
234
235
236 if (p_paymentTo is not null AND p_paymentTo = c_paymentToCardIssuer) then
237 ------------------------------------------------------
238 l_debugInfo := 'Set WF CREDIT_CARD_COMPANY Item Attribute';
239 ------------------------------------------------------
240 WF_ENGINE.SetItemAttrText(l_itemType,
241 l_itemKey,
242 'CREDIT_CARD_COMPANY',
243 p_cardIssuer);
244 ------------------------------------------------------
245 l_debugInfo := 'Set WF PAYMENT_DATE Item Attribute';
246 ------------------------------------------------------
247 WF_ENGINE.SetItemAttrText(l_itemType,
248 l_itemKey,
249 'PAYMENT_DATE',
250 p_paymentDate);
251 else -- payment to employee
252 if (p_paymentMethod = c_directDeposit) then
253 ------------------------------------------------------
254 l_debugInfo := 'Set WF BANK_ACCOUNT Item Attribute';
255 ------------------------------------------------------
256 WF_ENGINE.SetItemAttrText(l_itemType,
257 l_itemKey,
258 'BANK_ACCOUNT',
259 l_account);
260
261 ------------------------------------------------------
262 l_debugInfo := 'Set WF BANK_NAME Item Attribute';
263 ------------------------------------------------------
264 WF_ENGINE.SetItemAttrText(l_itemType,
265 l_itemKey,
266 'BANK_NAME',
267 p_bankName);
268 end if; -- p_paymentMethod = c_directDeposit
269 end if;
270
271
272 BEGIN
273 ------------------------------------------------------------
274 l_debugInfo := 'Start the Expense Report Workflow Process';
275 ------------------------------------------------------------
276 WF_ENGINE.StartProcess(l_itemType,
277 l_itemKey);
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 wf_engine.threshold := l_threshold;
282 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'sendNotification',
283 l_itemType, l_itemKey, to_char(0), l_debugInfo);
284 raise;
285 END;
286
287 wf_engine.threshold := l_threshold;
288
289 end if; -- nvl(l_process_created,'N') = 'Y'
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 IF (SQLCODE <> -20001) THEN
294 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
295 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
296 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'sendPaymentNotification');
297 FND_MESSAGE.SET_TOKEN('PARAMETERS',
298 'p_checkNumber = ' || to_char(p_checkNumber) ||
299 ', p_employeeId = '|| to_char(p_employeeId) ||
300 ', p_paymentCurrency = ' || p_paymentCurrency ||
301 ', p_invoiceNumber = ' || p_invoiceNumber ||
302 ', p_paymentTo = ' || p_paymentTo ||
303 ', p_paymentMethod = ' || p_paymentMethod ||
304 ', p_account = ' || l_account ||
305 ', p_bankName = ' || p_bankName ||
306 ', p_cardIssuer = ' || p_cardIssuer ||
307 ', p_paymentDate = ' || p_paymentDate);
308 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
309 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
310 ELSE
311 -- Do not need to set the token since it has been done in the
312 -- child process
313 RAISE;
314 END IF;
315 END sendPaymentNotification;
316
317
318 PROCEDURE sendUnsubmittedChargesNote(p_employeeId IN NUMBER,
319 p_Amount IN NUMBER,
320 p_currency IN VARCHAR2,
321 p_cardIssuer IN VARCHAR2,
322 p_date1 IN VARCHAR2,
323 p_date2 IN VARCHAR2,
324 p_charge_type IN VARCHAR2,
325 p_send_notifications IN VARCHAR2 DEFAULT 'EM',
326 p_min_amount IN NUMBER DEFAULT null) -- Bug 6886855 (sodash) setting the wf attribute MIN_AMOUNT
327
328 IS
329 l_itemType VARCHAR2(100) := 'APCCARD';
330 l_itemKey VARCHAR2(100);
331 l_employeeName wf_users.name%type;
332 l_employeeID NUMBER;
333 l_employeeDisplayName wf_users.display_name%type;
334 l_managerId NUMBER;
335 l_managerName wf_users.name%type;
336 l_managerDisplayName wf_users.display_name%type;
337 l_currency AP_WEB_DB_EXPRPT_PKG.expHdr_defaultCurrCode;
338 l_debugInfo VARCHAR2(200);
339 l_cardProgramID AP_WEB_DB_CCARD_PKG.cardProgs_cardProgID;
340 l_orgId number;
341 l_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
342 l_mgr_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
343
344 BEGIN
345 ------------------------------------------------------------
346 l_debugInfo := 'Get Name Info Associated With employee_Id';
347 ------------------------------------------------------------
348 WF_DIRECTORY.GetUserName('PER', p_employeeId, l_employeeName, l_employeeDisplayName);
349
350 /* Bug 3877939: If a record does not exist in WF_Directory, then
351 * the program should not error out.
352 */
353 IF l_employeeName IS NULL THEN
354 RETURN;
355 END IF;
356 ------------------------------------------------------------
357 l_debugInfo := 'Get manager_Id';
358 ------------------------------------------------------------
359 AP_WEB_EXPENSE_WF.GetManager(p_employeeId, l_managerId);
360
361 ------------------------------------------------------------
362 l_debugInfo := 'Get Name Info Associated With managerId';
363 ------------------------------------------------------------
364 WF_DIRECTORY.GetUserName('PER', l_managerId, l_managerName, l_managerDisplayName);
365
366 ---------------------------------------------------------
367 l_debugInfo := ' Generate new key';
368 ---------------------------------------------------------
369 l_itemKey := GetNextCardNotificationID;
370
371 --------------------------------------------------
372 l_debugInfo := 'Calling WorkFlow Create Process';
373 --------------------------------------------------
374 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'UNSUBMITTED_CHARGES');
375
376 ----------------------------------------------------------
377 l_debugInfo := 'Set WF Amount Item Attribute';
378 ----------------------------------------------------------
379 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'AMOUNT',
380 to_char(p_Amount, FND_CURRENCY.Get_Format_Mask(p_currency,22)) || ' ' || p_currency);
381
382 ----------------------------------------------------------
383 l_debugInfo := 'Set CURRENCY Item Attribute';
384 ----------------------------------------------------------
385 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'CURRENCY',p_currency);
386
387 ----------------------------------------------------------
388 l_debugInfo := 'Set the Owner of Workflow Process.';
389 ----------------------------------------------------------
390 WF_ENGINE.SetItemOwner(l_itemType, l_itemKey, l_employeeName);
391
392 ------------------------------------------------------
393 l_debugInfo := 'Set WF EMPLOYEE_ID Item Attribute';
394 ------------------------------------------------------
395 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID', p_employeeId);
396
397 ------------------------------------------------------
398 l_debugInfo := 'Set WF EMPLOYEE_NAME Item Attribute';
399 ------------------------------------------------------
400 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMPLOYEE_NAME', l_employeeName);
401
402 --------------------------------------------------------------
403 l_debugInfo := 'Set WF EMPLOYEE_DISPLAY_NAME Item Attribute';
404 --------------------------------------------------------------
405 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMP_DISPLAY_NAME', l_employeeDisplayName);
406
407 ------------------------------------------------------
408 l_debugInfo := 'Set WF MANAGER_NAME Item Attribute';
409 ------------------------------------------------------
410 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_NAME', l_managerName);
411
412 ------------------------------------------------------
413 l_debugInfo := 'Set WF CREDIT_CARD_COMPANY Item Attribute';
414 ------------------------------------------------------
415 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'CREDIT_CARD_COMPANY', p_cardIssuer);
416
417 --------------------------------------------------------------
418 l_debugInfo := 'Set WF CREDIT_CARD_COMPANY Item Attribute';
419 --------------------------------------------------------------
420 IF (NOT AP_WEB_DB_CCARD_PKG.GetCardProgramID(p_cardIssuer,
421 l_cardProgramID ) ) THEN
422 NULL;
423 END IF;
424
425 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID', l_cardProgramID);
426
427 --------------------------------------------------------------
428 l_debugInfo := 'Get and Set ORG_ID attribute ';
429 --------------------------------------------------------------
430 --FND_PROFILE.GET('ORG_ID' , l_orgId );
431 -- 8990469 : MOAC
432 l_orgId := mo_global.get_current_org_id;
433 if l_orgId is null then
434 FND_PROFILE.GET('ORG_ID' , l_orgId );
435 end if;
436
437 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'ORG_ID', l_orgId);
438
439 ------------------------------------------------------
440 l_debugInfo := 'Set WF DATE1 Item Attribute';
441 ------------------------------------------------------
442 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'DATE1', p_date1);
443
444 ------------------------------------------------------
445 l_debugInfo := 'Set WF DATE2 Item Attribute';
446 ------------------------------------------------------
447 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'DATE2', p_date2);
448
449 ------------------------------------------------------
450 l_debugInfo := 'Set Charge Type Item Attribute';
451 ------------------------------------------------------
452 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'CHARGE_TYPE', p_charge_type);
453
454 -- Bug 6886855 (sodash) setting the attribute MIN_AMOUNT
455 ------------------------------------------------------
456 l_debugInfo := 'Set WF MIN_AMOUNT Item Attribute';
457 ------------------------------------------------------
458 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'MIN_AMOUNT', p_min_amount);
459
460 ------------------------------------------------------
461 l_debugInfo := 'Set Send Notifications Item Attribute';
462 ------------------------------------------------------
463 WF_ENGINE.setItemAttrText(l_itemType, l_itemKey, 'SEND_NOTIFICATIONS_PARAM',p_send_notifications); -- Bug 6026927
464
465
466 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_FIRST_DUNNING');
467 l_instructions := FND_MESSAGE.GET;
468
469 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_FIRST_INSTR');
470 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NAME', l_employeeDisplayName);
471 l_mgr_instructions := FND_MESSAGE.GET;
472
473 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'DUNNING_INSTR', l_instructions);
474 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_INSTR', l_mgr_instructions);
475
476 --------------------------------------------------------------
477 l_debugInfo := 'Set NUM_RECORDS value ';
478 --------------------------------------------------------------
479 WF_ENGINE.SetItemAttrText(l_itemType,
480 l_itemKey,
481 'RECORDS_INSTR',
482 'plsql:AP_WEB_CREDIT_CARD_WF.getNumofUnsubmittedRecords/'||l_itemType||':'||l_itemKey);
483
484
485 --------------------------------------------------------------
486 l_debugInfo := 'Set LIST value ';
487 --------------------------------------------------------------
488 WF_ENGINE.SetItemAttrText(l_itemType,
489 l_itemKey,
490 'LIST',
491 'plsqlclob:AP_WEB_CREDIT_CARD_WF.genUnsubmittedClobList/'||l_itemType||':'||l_itemKey);
492
493 WF_ENGINE.SetItemAttrText(l_itemType,
494 l_itemKey,
495 'OIE_LIST',
496 'JSP:/OA_HTML/OA.jsp?akRegionCode=UnSubmittedChargesRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&mgrList='||'N'||'&orgId='||l_orgId);
497
498 WF_ENGINE.SetItemAttrText(l_itemType,
499 l_itemKey,
500 'MGR_LIST',
501 'JSP:/OA_HTML/OA.jsp?akRegionCode=UnSubmittedChargesRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&mgrList='||'Y'||'&name='||l_employeeDisplayName||'&orgId='||l_orgId);
502
503 BEGIN
504 ------------------------------------------------------------
505 l_debugInfo := 'Start the Expense Report Workflow Process';
506 ------------------------------------------------------------
507 WF_ENGINE.StartProcess(l_itemType, l_itemKey);
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'sendNotification',
512 l_itemType, l_itemKey, to_char(0), l_debugInfo);
513 raise;
514 END;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 IF (SQLCODE <> -20001) THEN
519 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
520 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
521 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'sendUnsubmittedChargesNote');
522 FND_MESSAGE.SET_TOKEN('PARAMETERS',
523 'p_employeeId = ' || to_char(p_employeeId) ||
524 ', p_Amount = ' || to_char(p_Amount) ||
525 ', p_currency = ' || p_currency ||
526 ', p_cardIssuer = ' || p_cardIssuer ||
527 ', p_date1 = ' || p_date1 ||
528 ', p_date2 = ' || p_date2);
529 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
530 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
531 ELSE
532 -- Do not need to set the token since it has been done in the
533 -- child process
534 RAISE;
535 END IF;
536 END sendUnsubmittedChargesNote;
537
538 PROCEDURE SendDunningNotifications(p_employeeId IN NUMBER,
539 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
540 p_Amount IN NUMBER,
541 p_currency IN VARCHAR2,
542 p_min_bucket IN NUMBER,
543 p_max_bucket IN NUMBER,
544 p_dunning_number IN NUMBER,
545 p_send_notifications IN VARCHAR2,
546 p_esc_level IN NUMBER,
547 p_grace_days IN NUMBER,
548 p_manager_notified IN VARCHAR2)
549 IS
550 BEGIN
551 SendDunningNotifications(p_employeeId,
552 p_cardProgramId,
553 p_Amount,
554 p_currency,
555 p_min_bucket,
556 p_max_bucket,
557 p_dunning_number,
558 p_send_notifications,
559 p_esc_level,
560 p_grace_days,
561 p_manager_notified,
562 NULL);
563 END;
564
565 /* AMulya Mishra:Notification Escalation Project:
566 Passed 4 new parameters from report.
567 */
568
569 PROCEDURE SendDunningNotifications(p_employeeId IN NUMBER,
570 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
571 p_Amount IN NUMBER,
572 p_currency IN VARCHAR2,
573 p_min_bucket IN NUMBER,
574 p_max_bucket IN NUMBER,
575 p_dunning_number IN NUMBER,
576 p_send_notifications IN VARCHAR2,
577 p_esc_level IN NUMBER,
578 p_grace_days IN NUMBER,
579 p_manager_notified IN VARCHAR2,
580 p_surrogate_mgr_id IN NUMBER)
581 IS
582 l_itemType VARCHAR2(100) := 'APCCARD';
583 l_itemKey VARCHAR2(100);
584 l_employeeId number;
585 l_employeeName wf_users.name%type;
586 l_managerId NUMBER;
587 l_managerName wf_users.name%type;
588 l_managerDisplayName wf_users.display_name%type;
589 l_employeeDisplayName wf_users.display_name%type;
590 l_currency AP_WEB_DB_EXPRPT_PKG.expHdr_defaultCurrCode;
591 l_cardProgramName AP_WEB_DB_CCARD_PKG.cardProgs_cardProgName;
592 l_orgId number;
593 l_debugInfo VARCHAR2(200);
594 l_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
595 l_mgr_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
596 l_plus_sign VARCHAR2(1) := '+';
597
598 --Amulya Mishra : Notification Esclation Project
599
600 l_add_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
601 l_mgmt_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
602 l_notes Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
603 l_aging_oustanding NUMBER;
604 l_total_outstanding NUMBER;
605 l_esc_managerName wf_users.name%type;
606 l_esc_managerId NUMBER;
607 l_total_amount NUMBER;
608 i NUMBER := 1;
609 l_job_level NUMBER;
610 l_prev_job_level NUMBER;
611 l_sup1_manager_name wf_users.name%type;
612 l_sup1_manager_displaye_name wf_users.name%type;
613 l_sup2_manager_id NUMBER;
614 l_sup2_manager_name wf_users.name%type;
615 l_sup2_manager_display_name wf_users.name%type;
616 l_temp_employee_id NUMBER;
617 l_orig_manager_id NUMBER;
618
619 l_prev_manager_id NUMBER;
620 l_prev_manager_name wf_users.name%type;
621 l_prev_manager_display_name wf_users.name%type;
622
623
624 l_mgr_esc_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
625 l_mgr_esc_mgmt_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
626 l_mgr_esc_add_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
627
628 l_next_mgr_esc_instr VARCHAR2(2000);
629 l_next_mgr_esc_mgmt_instr VARCHAR2(2000);
630 l_next_mgr_esc_add_instr VARCHAR2(2000);
631
632 l_mgr_esc_amount NUMBER;
633 l_next_mgr_esc_amount NUMBER;
634
635 is_null_job_level boolean := FALSE;
636
637 l_next_manager_id NUMBER;
638 l_next_mgr_job_level NUMBER;
639 l_sup2_manager_job_level NUMBER;
640
641 l_temp_mgr_id NUMBER;
642 l_temp_next_mgr_id NUMBER;
643 l_temp_mgr_name wf_users.name%type;
644 l_temp_next_mgr_name wf_users.name%type;
645 l_temp_mgr_dname wf_users.name%type;
646 l_temp_next_mgr_dname wf_users.name%type;
647
648
649 --Amulya Mishra : Notification Esclation
650
651 BEGIN
652
653 ------------------------------------------------------------
654 l_debugInfo := 'Get Name Info Associated With employee_Id';
655 ------------------------------------------------------------
656 WF_DIRECTORY.GetUserName('PER', p_employeeId, l_employeeName, l_employeeDisplayName);
657
658 /* Bug 3877939: If a record does not exist in WF_Directory, then
659 * the program should not error out.
660 */
661 IF l_employeeName IS NULL THEN
662 RETURN;
663 END IF;
664 ------------------------------------------------------------
665 l_debugInfo := 'Get manager_Id';
666 ------------------------------------------------------------
667 AP_WEB_EXPENSE_WF.GetManager(p_employeeId, l_managerId);
668
669 ------------------------------------------------------------
670 l_debugInfo := 'Get Name Info Associated With managerId';
671 ------------------------------------------------------------
672 WF_DIRECTORY.GetUserName('PER', l_managerId, l_managerName, l_managerDisplayName);
673
674 ---------------------------------------------------------
675 l_debugInfo := ' Generate new key';
676 ---------------------------------------------------------
677 l_itemKey := GetNextCardNotificationID;
678
679 --------------------------------------------------
680 l_debugInfo := 'Calling WorkFlow Create Process';
681 --------------------------------------------------
682 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'DUNNING_NOTIFICATIONS');
683
684 ----------------------------------------------------------
685 l_debugInfo := 'Set the Owner of Workflow Process.';
686 ----------------------------------------------------------
687 WF_ENGINE.SetItemOwner(l_itemType, l_itemKey, l_employeeName);
688
689 ----------------------------------------------------------
690 l_debugInfo := 'Set the Subject';
691 ----------------------------------------------------------
692 if(p_dunning_number = 1) then
693 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_FIRST_DUNNING');
694 l_instructions := FND_MESSAGE.GET;
695 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_FIRST_INSTR');
696 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NAME', l_employeeDisplayName);
697 l_mgr_instructions := FND_MESSAGE.GET;
698
699
700 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_INSTR_DUNNING1');
701 l_mgr_esc_instructions := FND_MESSAGE.GET;
702
703
704 elsif(p_dunning_number = 2) then
705 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_SECOND_DUNNING');
706 l_instructions := FND_MESSAGE.GET;
707 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_SECOND_INSTR');
708 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NAME', l_employeeDisplayName);
709 l_mgr_instructions := FND_MESSAGE.GET;
710
711
712 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_INSTR_DUNNING2');
713 l_mgr_esc_instructions := FND_MESSAGE.GET;
714
715 elsif(p_dunning_number = 3) then
716 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_THIRD_DUNNING');
717 l_instructions := FND_MESSAGE.GET;
718 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_THIRD_INSTR');
719 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NAME', l_employeeDisplayName);
720 l_mgr_instructions := FND_MESSAGE.GET;
721
722
723 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_INSTR_DUNNING3');
724 l_mgr_esc_instructions := FND_MESSAGE.GET;
725
726 elsif(p_dunning_number = 4) then
727 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_FOURTH_DUNNING');
728 l_instructions := FND_MESSAGE.GET;
729 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_FOURTH_INSTR');
730 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NAME', l_employeeDisplayName);
731 l_mgr_instructions := FND_MESSAGE.GET;
732
733
734 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_INSTR_DUNNING4');
735 l_mgr_esc_instructions := FND_MESSAGE.GET;
736
737 end if;
738
739 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'DUNNING_INSTR', l_instructions);
740 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_INSTR', l_mgr_instructions);
741
742 --Direct Report
743
744
745 IF (p_dunning_number = 1 ) THEN
746 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'FIRST_DUNNING', 'Y');
747 ELSE
748 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'FIRST_DUNNING', 'N');
749 END IF;
750
751 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_ADD_INSTR');
752 l_mgr_esc_add_instructions := FND_MESSAGE.GET;
753
754
755 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_ESC_INSTR', l_mgr_esc_instructions);
756 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_ESC_ADD_INSTR', l_mgr_esc_add_instructions);
757
758
759 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_NEXT_MGR_ESC_INSTR');
760 l_next_mgr_esc_instr := FND_MESSAGE.GET;
761 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_NEXT_ESC_INSTR', l_next_mgr_esc_instr);
762
763 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_NEXT_ESC_ADD_INSTR', l_mgr_esc_add_instructions);
764
765
766 --Amulya Mishra : Notification Esc Project
767
768 FND_MESSAGE.SET_NAME('SQLAP','OIE_ADD_INFO_DUNNING');
769 FND_MESSAGE.SET_TOKEN('days', nvl(p_grace_days,0));
770 l_add_instructions := FND_MESSAGE.GET;
771 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'ADD_INSTRUCTIONS', l_add_instructions);
772
773 FND_MESSAGE.SET_NAME('SQLAP','OIE_NOTES_DUNNING');
774 l_notes := FND_MESSAGE.GET;
775 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'NOTES', l_notes);
776
777 --determining SUP1 and SUP2
778
779
780 IF (P_send_notifications = 'ES') THEN
781 l_temp_employee_id := p_employeeId;
782 l_orig_manager_id := l_managerId;
783 l_next_manager_id := l_managerId;
784 l_job_level := 0;
785
786
787 WHILE i<=p_dunning_number LOOP
788
789 AP_WEB_EXPENSE_WF.GetJobLevelAndSupervisor(l_managerId,l_job_level);
790 IF l_job_level < nvl(p_esc_level,999999999) THEN
791
792 AP_WEB_EXPENSE_WF.GetManager(l_temp_employee_id, l_managerId);
793 IF (l_managerId IS NULL) THEN
794 l_managerId := l_temp_employee_id;
795 EXIT;
796 END IF;
797
798
799 AP_WEB_EXPENSE_WF.GetJobLevelAndSupervisor(l_managerId,l_job_level);
800
801 AP_WEB_EXPENSE_WF.GetManager(l_managerId , l_next_manager_id);
802 IF (l_next_manager_id IS NOT NULL) THEN
803 AP_WEB_EXPENSE_WF.GetJobLevelAndSupervisor(l_next_manager_id , l_next_mgr_job_level);
804 IF(l_next_mgr_job_level > nvl(p_esc_level,999999999)) THEN
805 l_temp_employee_id := l_managerId;
806 EXIT;
807 END IF;
808 ELSE
809 l_next_manager_id := l_managerId;
810 END IF;
811
812 END IF;
813 l_temp_employee_id := l_managerId;
814 i := i + 1;
815
816 END LOOP;
817
818 IF l_job_level = 0 AND p_esc_level IS NOT NULL THEN --Bug 3337665
819 l_managerId := l_next_manager_id;
820 l_temp_employee_id := l_managerId;
821 END IF;
822
823 IF p_esc_level = 0 THEN
824 l_temp_employee_id := l_managerId;
825 END IF;
826
827 WF_DIRECTORY.GetUserName('PER', l_managerId, l_managerName, l_managerDisplayName);
828 AP_WEB_EXPENSE_WF.GetManager(l_temp_employee_id, l_sup2_manager_id);
829 AP_WEB_EXPENSE_WF.GetJobLevelAndSupervisor(l_sup2_manager_id , l_sup2_manager_job_level);
830 IF (l_sup2_manager_job_level > nvl(p_esc_level,999999999)) THEN
831 l_sup2_manager_display_name := null;
832 ELSE
833 WF_DIRECTORY.GetUserName('PER', l_sup2_manager_id, l_sup2_manager_name, l_sup2_manager_display_name);
834 END IF;
835
836
837
838 END IF;
839
840 IF (l_managerDisplayName IS NOT NULL) THEN
841
842 IF(p_dunning_number < 4 AND l_sup2_manager_display_name IS NOT NULL) THEN
843
844
845 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_NTF_DUNNING');
846 FND_MESSAGE.SET_TOKEN('SUP1', l_managerDisplayName);
847 FND_MESSAGE.SET_TOKEN('SUP2', l_sup2_manager_display_name);
848 l_mgmt_instructions := FND_MESSAGE.GET;
849 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGMT_INSTR', l_mgmt_instructions);
850
851 IF (p_dunning_number = 1) THEN
852
853 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_NTF_DUNNING1');
854 FND_MESSAGE.SET_TOKEN('SUP2', l_sup2_manager_display_name);
855 l_mgr_esc_mgmt_instructions := FND_MESSAGE.GET;
856
857 ELSE
858
859 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_NTF_DUNNING');
860 FND_MESSAGE.SET_TOKEN('SUP1', l_managerDisplayName);
861 FND_MESSAGE.SET_TOKEN('SUP2', l_sup2_manager_display_name);
862 l_mgr_esc_mgmt_instructions := FND_MESSAGE.GET;
863
864 END IF;
865 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGMT_ESC_INSTR', l_mgr_esc_mgmt_instructions);
866
867 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_NEXT_MGR_ESC_NTF');
868 FND_MESSAGE.SET_TOKEN('SUP1', l_sup2_manager_display_name);
869 l_next_mgr_esc_mgmt_instr := FND_MESSAGE.GET;
870 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGMT_NEXT_ESC_INSTR', l_next_mgr_esc_mgmt_instr);
871
872
873 ELSIF (p_dunning_number = 4 OR l_sup2_manager_display_name IS NULL OR l_managerDisplayName = l_sup2_manager_display_name) THEN
874
875 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_LAST_MGR_NTF_DUNNING');
876 FND_MESSAGE.SET_TOKEN('SUP1', l_managerDisplayName);
877 l_mgmt_instructions := FND_MESSAGE.GET;
878 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGMT_INSTR', l_mgmt_instructions);
879
880 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_MGR_ESC_LAST_NTF_DUNN');
881 FND_MESSAGE.SET_TOKEN('SUP2', l_managerDisplayName);
882 l_mgr_esc_mgmt_instructions := FND_MESSAGE.GET;
883 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGMT_ESC_INSTR', l_mgr_esc_mgmt_instructions);
884
885 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_NEXT_MGR_ESC_LAST_NTF');
886 l_next_mgr_esc_mgmt_instr := FND_MESSAGE.GET;
887 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGMT_NEXT_ESC_INSTR', l_next_mgr_esc_mgmt_instr);
888
889
890 END IF;
891
892 END IF;
893
894 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalCreditCardAmount(p_cardProgramID,p_employeeId,
895 l_total_amount ) ) THEN
896 NULL;
897 END IF;
898
899 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'TOTAL_OUTSTANDING',
900 to_char(l_total_amount, FND_CURRENCY.Get_Format_Mask(p_currency,22)) || ' ' || p_currency);
901
902
903 WF_ENGINE.setItemAttrText(l_itemType, l_itemKey, 'SEND_NOTIFICATIONS_PARAM',p_send_notifications);
904 WF_ENGINE.setItemAttrText(l_itemType, l_itemKey, 'AGING_GRACE_DAYS',nvl(p_grace_days,0));
905
906 --AMulya Mishra : Notification Esc Project
907
908 ------------------------------------------------------
909 l_debugInfo := 'Set WF EMPLOYEE_NAME Item Attribute';
910 ------------------------------------------------------
911 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMPLOYEE_NAME', l_employeeName);
912
913 --------------------------------------------------------------
914 l_debugInfo := 'Set WF EMP_DISPLAY_NAME Item Attribute';
915 --------------------------------------------------------------
916 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMP_DISPLAY_NAME', l_employeeDisplayName);
917
918 --------------------------------------------------------------
919 l_debugInfo := 'Set WF EMPLOYEE_ID Item Attribute';
920 --------------------------------------------------------------
921 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID', p_employeeID);
922
923 ------------------------------------------------------
924 l_debugInfo := 'Set WF MANAGER_NAME Item Attribute';
925 ------------------------------------------------------
926 l_temp_mgr_name := l_managerName;
927 IF (AP_WEB_EXPENSE_WF.CheckSurrogateManager(p_employeeId, l_managerId, p_surrogate_mgr_id)) THEN
928 WF_DIRECTORY.GetUserName('PER', p_surrogate_mgr_id, l_temp_mgr_name, l_temp_mgr_dname);
929 END IF;
930 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_NAME', l_temp_mgr_name);
931 --WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_NAME', l_managerName);
932
933 --Amulya Mishra : Notification Esc Project
934
935 IF (P_send_notifications = 'ES') THEN
936
937 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_ALREADY_NOTIFIED', p_manager_notified);
938 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'NEXT_MANAGER_NAME', l_temp_mgr_name);
939 WF_DIRECTORY.GetUserName('PER', l_orig_manager_id, l_managerName, l_managerDisplayName);
940 IF (AP_WEB_EXPENSE_WF.CheckSurrogateManager(p_employeeId, l_orig_manager_id, p_surrogate_mgr_id)) THEN
941 WF_DIRECTORY.GetUserName('PER', p_surrogate_mgr_id, l_temp_mgr_name, l_temp_mgr_dname);
942 ELSE
943 l_temp_mgr_name := l_managerName;
944 END IF;
945
946 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_NAME', l_temp_mgr_name);
947
948 ELSE
949
950 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_NAME', l_temp_mgr_name);
951
952 END IF;
953 --store latest manager id to workflow so that later it can be used.
954
955 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'AGING_MANAGER_ID', l_managerId);
956
957 --Bug 3337388
958
959 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'DUNNING_NUMBER', p_dunning_number);
960
961 --Amulya Mishra : Notification Esc project
962
963 --------------------------------------------------------------
964 l_debugInfo := 'Set WF CARD_PROG_ID Item Attribute';
965 --------------------------------------------------------------
966 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID', p_cardProgramID);
967
968 --------------------------------------------------------------
969 l_debugInfo := 'Set WF CREDIT_CARD_COMPANY Item Attribute';
970 --------------------------------------------------------------
971 IF (NOT AP_WEB_DB_CCARD_PKG.GetCardProgramName(p_cardProgramID,
972 l_cardProgramName ) ) THEN
973 NULL;
974 END IF;
975
976 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'CREDIT_CARD_COMPANY', l_cardProgramName);
977
978
979 ----------------------------------------------------------
980 l_debugInfo := 'Set CURRENCY Item Attribute';
981 ----------------------------------------------------------
982 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'CURRENCY',p_currency);
983
984 ------------------------------------------------------
985 l_debugInfo := 'Set Amount Item Attribute';
986 ------------------------------------------------------
987 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'AMOUNT',
988 to_char(p_Amount, FND_CURRENCY.Get_Format_Mask(p_currency,22)) || ' ' || p_currency);
989
990
991 ------------------------------------------------------
992 l_debugInfo := 'Set WF BUCKET1..2 Item Attribute';
993 ------------------------------------------------------
994 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'BUCKET1', to_char(p_min_bucket));
995 if( p_max_bucket = 1000000) then
996 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'BUCKET2', l_plus_sign);
997 else
998 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'BUCKET2', '- '||to_char(p_max_bucket));
999 end if;
1000
1001
1002
1003 --------------------------------------------------------------
1004 l_debugInfo := 'Get and Set ORG_ID attribute ';
1005 --------------------------------------------------------------
1006 -- FND_PROFILE.GET('ORG_ID' , l_orgId );
1007 -- 8990469 : MOAC
1008 l_orgId := mo_global.get_current_org_id;
1009 if l_orgId is null then
1010 FND_PROFILE.GET('ORG_ID' , l_orgId );
1011 end if;
1012
1013 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'ORG_ID', l_orgId);
1014
1015 --------------------------------------------------------------
1016 l_debugInfo := 'Set NUM_RECORDS value ';
1017 --------------------------------------------------------------
1018 WF_ENGINE.SetItemAttrText(l_itemType,
1019 l_itemKey,
1020 'RECORDS_INSTR',
1021 'plsql:AP_WEB_CREDIT_CARD_WF.getNumofDunningRecords/'||l_itemType||':'||l_itemKey);
1022
1023 --------------------------------------------------------------
1024 l_debugInfo := 'Set LIST value ';
1025 --------------------------------------------------------------
1026
1027 WF_ENGINE.SetItemAttrText(l_itemType,
1028 l_itemKey,
1029 'LIST',
1030 'plsqlclob:AP_WEB_CREDIT_CARD_WF.generateDunningClobList/'||l_itemType||':'||l_itemKey);
1031
1032 WF_ENGINE.SetItemAttrText(l_itemType,
1033 l_itemKey,
1034 'OIE_LIST',
1035 'JSP:/OA_HTML/OA.jsp?akRegionCode=DunningRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&graceDays='||p_grace_days||'&escNotif='||'N'||'&mgrList='||'N'||'&orgId='||l_orgId);
1036
1037 --------------------------------------------------------------
1038 l_debugInfo := 'Set MGR_LIST value ';
1039 --------------------------------------------------------------
1040 WF_ENGINE.SetItemAttrText(l_itemType,
1041 l_itemKey,
1042 'MGR_LIST',
1043 'JSP:/OA_HTML/OA.jsp?akRegionCode=DunningRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&graceDays='||p_grace_days||'&escNotif='||'N'||'&mgrList='||'Y'||'&name='||l_employeeName||'&orgId='||l_orgId);
1044
1045 --Amulya Mishra : Notification Esc Project
1046
1047 IF (P_send_notifications = 'ES') THEN
1048
1049 WF_ENGINE.SetItemAttrText(l_itemType,
1050 l_itemKey,
1051 'LIST',
1052 'plsqlclob:AP_WEB_CREDIT_CARD_WF.generateDunningClobList/'||l_itemType||':'||l_itemKey);
1053
1054 WF_ENGINE.SetItemAttrText(l_itemType,
1055 l_itemKey,
1056 'OIE_LIST',
1057 'JSP:/OA_HTML/OA.jsp?akRegionCode=DunningRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&graceDays='||p_grace_days||'&escNotif='||'Y'||'&mgrList='||'N'||'&orgId='||l_orgId);
1058
1059 --------------------------------------------------------------
1060 l_debugInfo := 'Set MGR_LIST value ';
1061 --------------------------------------------------------------
1062 WF_ENGINE.SetItemAttrText(l_itemType,
1063 l_itemKey,
1064 'MGR_LIST',
1065 'JSP:/OA_HTML/OA.jsp?akRegionCode=DunningRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&graceDays='||p_grace_days||'&escNotif='||'Y'||'&mgrList='||'Y'||'&orgId='||l_orgId);
1066
1067
1068 GetTotalOutstandingAttribute(p_employeeId,
1069 p_cardProgramId,
1070 p_min_bucket,
1071 p_max_bucket,
1072 p_grace_days,
1073 l_mgr_esc_amount);
1074 ------------------------------------------------------
1075 l_debugInfo := 'Set Amount Item Attribute';
1076 ------------------------------------------------------
1077 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_ESC_AMOUNT',
1078 to_char(l_mgr_esc_amount, FND_CURRENCY.Get_Format_Mask(p_currency,22)) || ' ' || p_currency);
1079
1080 WF_ENGINE.SetItemAttrText(l_itemType,
1081 l_itemKey,
1082 'LIST_ESC',
1083 'plsqlclob:AP_WEB_CREDIT_CARD_WF.generateManagerDunningList/'||l_itemType||':'||l_itemKey);
1084
1085 WF_ENGINE.SetItemAttrText(l_itemType,
1086 l_itemKey,
1087 'OIE_LIST_ESC',
1088 'JSP:/OA_HTML/OA.jsp?akRegionCode=EscNotifMgrRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&managerId='||l_orig_manager_id||'&orgId='||l_orgId);
1089
1090
1091 GetHierTotalOutstandingAttr(l_managerId,
1092 p_cardProgramId,
1093 p_min_bucket,
1094 p_max_bucket,
1095 p_grace_days,
1096 p_dunning_number,
1097 l_next_mgr_esc_amount);
1098
1099
1100 ------------------------------------------------------
1101 l_debugInfo := 'Set Amount Item Attribute';
1102 ------------------------------------------------------
1103 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_NEXT_ESC_AMOUNT',
1104 to_char(l_next_mgr_esc_amount, FND_CURRENCY.Get_Format_Mask(p_currency,22)) || ' ' || p_currency);
1105
1106
1107 WF_ENGINE.SetItemAttrText(l_itemType,
1108 l_itemKey,
1109 'LIST_NEXT_ESC',
1110 'plsqlclob:AP_WEB_CREDIT_CARD_WF.generateNextManagerDunningList/'||l_itemType||':'||l_itemKey);
1111
1112 WF_ENGINE.SetItemAttrText(l_itemType,
1113 l_itemKey,
1114 'OIE_LIST_NEXT_ESC',
1115 'JSP:/OA_HTML/OA.jsp?akRegionCode=EscNotifNextMgrRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&agingManager='||l_managerId||'&orgId='||l_orgId);
1116
1117 END IF;
1118
1119 --Amulya Mishra : Notification Esc project
1120
1121 BEGIN
1122 WF_ENGINE.SetItemAttrText(l_itemType,
1123 l_itemKey,
1124 '#FROM_ROLE',
1125 WF_ENGINE.GetItemAttrText(l_itemType,
1126 l_itemKey,
1127 'WF_ADMINISTRATOR'));
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 NULL;
1131 END;
1132
1133
1134 BEGIN
1135 ------------------------------------------------------------
1136 l_debugInfo := 'Start the Expense Report Workflow Process';
1137 ------------------------------------------------------------
1138 WF_ENGINE.StartProcess(l_itemType, l_itemKey);
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'SendDunningNotifications',
1143 l_itemType, l_itemKey, to_char(0), l_debugInfo);
1144 raise;
1145 END;
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 IF (SQLCODE <> -20001) THEN
1149 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1150 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1151 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'SendDunningNotifications');
1152 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1153 ' p_employeeId = ' || to_char(p_employeeId) ||
1154 ', p_cardProgramId = ' || to_char(p_cardProgramId) ||
1155 ', p_bucket1 = ' || to_char(p_min_bucket) ||
1156 ', p_bucket2 = ' || to_char(p_max_bucket));
1157 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
1158 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
1159 ELSE
1160 -- Do not need to set the token since it has been done in the
1161 -- child process
1162 RAISE;
1163 END IF;
1164 END SendDunningNotifications;
1165
1166
1167 PROCEDURE sendUnapprovedExpReportNote(
1168 p_expenseReportId IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
1169 p_current_approver IN AP_EXPENSE_REPORT_HEADERS.expense_current_approver_id%TYPE) --2628468
1170
1171 IS
1172 l_itemType VARCHAR2(100) := 'APCCARD';
1173 l_itemKey VARCHAR2(100);
1174 l_employeeName wf_users.name%type;
1175 l_employeeDisplayName wf_users.display_name%type;
1176 l_managerName wf_users.name%type;
1177 l_managerDisplayName wf_users.display_name%type;
1178 l_exp_info_rec AP_WEB_DB_EXPRPT_PKG.ExpInfoRec;
1179 l_debugInfo VARCHAR2(200);
1180
1181 BEGIN
1182 ------------------------------------------------------------
1183 l_debugInfo := 'Get employee_Id';
1184 ------------------------------------------------------------
1185
1186 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetReportInfo(p_expenseReportId, l_exp_info_rec)) THEN
1187 NULL;
1188 END IF;
1189
1190
1191 --2628468, I removed the code that found the manager ID
1192
1193 ------------------------------------------------------------
1194 l_debugInfo := 'Get Name Info Associated With employee_Id';
1195 ------------------------------------------------------------
1196 WF_DIRECTORY.GetUserName('PER', l_exp_info_rec.emp_id, l_employeeName, l_employeeDisplayName);
1197
1198 /* Bug 3877939: If a record does not exist in WF_Directory, then
1199 * the program should not error out.
1200 */
1201 IF l_employeeName IS NULL THEN
1202 RETURN;
1203 END IF;
1204
1205 ------------------------------------------------------------
1206 l_debugInfo := 'Get Name Info Associated With managerId';
1207 ------------------------------------------------------------
1208 --2628468, instead of using the manager, use the current approver
1209
1210 WF_DIRECTORY.GetUserName('PER', p_current_approver, l_managerName, l_managerDisplayName);
1211
1212 ---------------------------------------------------------
1213 l_debugInfo := ' Generate new key';
1214 -- p_expenseReportId is not used since this notification could be sent nore than once
1215 ---------------------------------------------------------
1216 l_itemKey := GetNextCardNotificationID;
1217
1218 --------------------------------------------------
1219 l_debugInfo := 'Calling WorkFlow Create Process';
1220 --------------------------------------------------
1221 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'UNAPPROVED_REPORT');
1222
1223 ----------------------------------------------------------
1224 l_debugInfo := 'Set WF Amount Item Attribute';
1225 ----------------------------------------------------------
1226 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'AMOUNT',
1227 to_char(l_exp_info_rec.total, FND_CURRENCY.Get_Format_Mask(l_exp_info_rec.default_curr_code,22)) || ' ' || l_exp_info_rec.default_curr_code);
1228
1229 ----------------------------------------------------------
1230 l_debugInfo := 'Set the Owner of Workflow Process.';
1231 ----------------------------------------------------------
1232 WF_ENGINE.SetItemOwner(l_itemType, l_itemKey, l_employeeName);
1233
1234 --------------------------------------------------------------
1235 l_debugInfo := 'Set WF EMP_DISPLAY_NAME Item Attribute';
1236 --------------------------------------------------------------
1237 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMP_DISPLAY_NAME', l_employeeDisplayName);
1238
1239 ------------------------------------------------------
1240 l_debugInfo := 'Set WF MANAGER_NAME Item Attribute';
1241 ------------------------------------------------------
1242 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MANAGER_NAME', l_managerName);
1243
1244 ----------------------------------------------------------
1245 l_debugInfo := 'Set WF EXPENSE_REPORT_NUMBER Item Attribute';
1246 ----------------------------------------------------------
1247 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EXPENSE_REPORT_NUMBER', l_exp_info_rec.doc_num);
1248
1249 BEGIN
1250 ------------------------------------------------------------
1251 l_debugInfo := 'Start the Expense Report Workflow Process';
1252 ------------------------------------------------------------
1253 WF_ENGINE.StartProcess(l_itemType, l_itemKey);
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'sendUnapprovedExpReportNote',
1258 l_itemType, l_itemKey, to_char(0), l_debugInfo);
1259 raise;
1260 END;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 IF (SQLCODE <> -20001) THEN
1265 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1266 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1267 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'sendUnapprovedExpReportNote');
1268 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_expenseReportId = ' || to_char(p_expenseReportId));
1269 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
1270 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
1271 ELSE
1272 -- Do not need to set the token since it has been done in the
1273 -- child process
1274 RAISE;
1275 END IF;
1276 END sendUnapprovedExpReportNote;
1277
1278
1279
1280 PROCEDURE sendDisputedChargesNote(p_employeeId IN NUMBER,
1281 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
1282 p_billedStartDate in date,
1283 p_billedEndDate in date,
1284 p_minimumAmount IN NUMBER)
1285 IS
1286 l_itemType VARCHAR2(100) := 'APCCARD';
1287 l_itemKey VARCHAR2(100);
1288 l_employeeId number;
1289 l_employeeName wf_users.name%type;
1290 l_employeeDisplayName wf_users.display_name%type;
1291 l_currency AP_WEB_DB_EXPRPT_PKG.expHdr_defaultCurrCode;
1292 l_cardProgramName AP_WEB_DB_CCARD_PKG.cardProgs_cardProgName;
1293 l_sysdate VARCHAR2(30);
1294 l_today DATE;
1295 l_orgId number;
1296 l_days number;
1297 l_debugInfo VARCHAR2(200);
1298
1299 BEGIN
1300 ---------------------------------------------------------
1301 l_debugInfo := ' Generate new key';
1302 ---------------------------------------------------------
1303 l_itemKey := GetNextCardNotificationID;
1304
1305 ------------------------------------------------------------
1306 l_debugInfo := 'Get Name Info Associated With employee_Id';
1307 ------------------------------------------------------------
1308 WF_DIRECTORY.GetUserName('PER', p_employeeId, l_employeeName, l_employeeDisplayName);
1309
1310 /* Bug 3877939: If a record does not exist in WF_Directory, then
1311 * the program should not error out.
1312 */
1313 IF l_employeeName IS NULL THEN
1314 RETURN;
1315 END IF;
1316
1317 --------------------------------------------------
1318 l_debugInfo := 'Calling WorkFlow Create Process';
1319 --------------------------------------------------
1320 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'DISPUTED_CHARGES');
1321
1322 ----------------------------------------------------------
1323 l_debugInfo := 'Set the Owner of Workflow Process.';
1324 ----------------------------------------------------------
1325 WF_ENGINE.SetItemOwner(l_itemType, l_itemKey, l_employeeName);
1326
1327 ------------------------------------------------------
1328 l_debugInfo := 'Set WF EMPLOYEE_NAME Item Attribute';
1329 ------------------------------------------------------
1330 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMPLOYEE_NAME', l_employeeName);
1331
1332 --------------------------------------------------------------
1333 l_debugInfo := 'Set WF EMP_DISPLAY_NAME Item Attribute';
1334 --------------------------------------------------------------
1335 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'EMP_DISPLAY_NAME', l_employeeDisplayName);
1336
1337 --------------------------------------------------------------
1338 l_debugInfo := 'Set WF EMPLOYEE_ID Item Attribute';
1339 --------------------------------------------------------------
1340 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID', p_employeeID);
1341
1342 --------------------------------------------------------------
1343 l_debugInfo := 'Set WF CARD_PROG_ID Item Attribute';
1344 --------------------------------------------------------------
1345 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID', p_cardProgramID);
1346
1347 --------------------------------------------------------------
1348 l_debugInfo := 'Set WF CREDIT_CARD_COMPANY Item Attribute';
1349 --------------------------------------------------------------
1350 IF (NOT AP_WEB_DB_CCARD_PKG.GetCardProgramName(p_cardProgramID,
1351 l_cardProgramName ) ) THEN
1352 NULL;
1353 END IF;
1354
1355 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'CREDIT_CARD_COMPANY', l_cardProgramName);
1356
1357 ------------------------------------------------------
1358 l_debugInfo := 'Set WF MIN_AMOUNT Item Attribute';
1359 ------------------------------------------------------
1360 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'MIN_AMOUNT', p_minimumAmount);
1361
1362 ------------------------------------------------------
1363 l_debugInfo := 'Set WF DATE_OBJ1 Item Attribute';
1364 ------------------------------------------------------
1365 WF_ENGINE.SetItemAttrDate(l_itemType, l_itemKey, 'DATE_OBJ1', p_billedStartDate);
1366
1367 ------------------------------------------------------
1368 l_debugInfo := 'Set WF DATE_OBJ2 Item Attribute';
1369 ------------------------------------------------------
1370 WF_ENGINE.SetItemAttrDate(l_itemType, l_itemKey, 'DATE_OBJ2', p_billedEndDate);
1371
1372 ------------------------------------------------------
1373 l_debugInfo := 'Set WF NUMBER_OF_DAYS Item Attribute';
1374 ------------------------------------------------------
1375 IF (AP_WEB_DB_UTIL_PKG.GetSysDate(l_sysdate)) THEN
1376 l_today := to_date(l_sysdate,AP_WEB_INFRASTRUCTURE_PKG.getDateFormat);
1377 END IF;
1378
1379 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'NUMBER_OF_DAYS', round(l_today - p_billedStartDate));
1380
1381 l_days := round(l_today - p_billedStartDate);
1382 --------------------------------------------------------------
1383 l_debugInfo := 'Get and Set ORG_ID attribute ';
1384 --------------------------------------------------------------
1385 --FND_PROFILE.GET('ORG_ID' , l_orgId );
1386 -- 8990469 : MOAC
1387 l_orgId := mo_global.get_current_org_id;
1388 if l_orgId is null then
1389 FND_PROFILE.GET('ORG_ID' , l_orgId );
1390 end if;
1391
1392 WF_ENGINE.SetItemAttrNumber(l_itemType, l_itemKey, 'ORG_ID', l_orgId);
1393
1394 --------------------------------------------------------------
1395 l_debugInfo := 'Set LIST value ';
1396 --------------------------------------------------------------
1397 WF_ENGINE.SetItemAttrText(l_itemType,
1398 l_itemKey,
1399 'LIST',
1400 'plsql:AP_WEB_CREDIT_CARD_WF.generateList/'||l_itemType||':'||l_itemKey);
1401
1402 WF_ENGINE.SetItemAttrText(l_itemType,
1403 l_itemKey,
1404 'OIE_LIST',
1405 'JSP:/OA_HTML/OA.jsp?akRegionCode=DisputedChargesRN&akRegionApplicationId=200&itemKey='||l_itemKey||'&cardCompany='||l_cardProgramName||'&days='||l_days||'&orgId='||l_orgId);
1406
1407 BEGIN
1408 ------------------------------------------------------------
1409 l_debugInfo := 'Start the Expense Report Workflow Process';
1410 ------------------------------------------------------------
1411 WF_ENGINE.StartProcess(l_itemType, l_itemKey);
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'sendDisputedChargesNote',
1416 l_itemType, l_itemKey, to_char(0), l_debugInfo);
1417 raise;
1418 END;
1419
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422 IF (SQLCODE <> -20001) THEN
1423 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1424 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1425 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'sendDisputedChargesNote');
1426 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1427 ' p_employeeId = ' || to_char(p_employeeId) ||
1428 ', p_cardProgramId = ' || to_char(p_cardProgramId) ||
1429 ', p_billedStartDate = ' || to_char(p_billedStartDate) ||
1430 ', p_billedEndDate = ' || to_char(p_billedEndDate) ||
1431 ', p_minimumAmount = ' || to_char(p_minimumAmount));
1432 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
1433 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
1434 ELSE
1435 -- Do not need to set the token since it has been done in the
1436 -- child process
1437 RAISE;
1438 END IF;
1439 END sendDisputedChargesNote;
1440
1441 /*
1442 Written by:
1443 Quan Le
1444 Purpose:
1445 To generate the LIST document attribute of Credit Card Workflow. This procedure follows
1446 predefined API. See Workflow API documentation for more informfation.
1447 Input:
1448 See Workflow API documentation.
1449 Output:
1450 See Workflow API documentation.
1451 Input Output:
1452 See Workflow API documentation.
1453 Assumption:
1454 document_id is assumed to have the following format:
1455 <item_key>:<item_id>
1456 Date:
1457 22/10/99
1458 */
1459 PROCEDURE GenerateList(document_id IN VARCHAR2,
1460 display_type IN VARCHAR2,
1461 document IN OUT NOCOPY VARCHAR2,
1462 document_type IN OUT NOCOPY VARCHAR2) IS
1463
1464 l_colon NUMBER;
1465 l_itemtype VARCHAR2(7);
1466 l_itemkey VARCHAR2(15);
1467 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
1468 l_billedStartDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
1469 l_billedEndDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
1470 l_minimumAmount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
1471 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
1472 l_dateFormat varchar2(30);
1473 l_transaction_date AP_WEB_DB_CCARD_PKG.ccTrxn_transDate;
1474 l_merchant_name1 AP_WEB_DB_CCARD_PKG.ccTrxn_merchantName1;
1475 l_billed_amount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
1476 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
1477 l_lineInfo VARCHAR2(2000);
1478 l_debugInfo VARCHAR2(1000);
1479 l_orgId number;
1480 l_disputedCharges_cursor AP_WEB_DB_CCARD_PKG.DisputedCCTrxnCursor;
1481 l_dispute_header_prompt VARCHAR2(200);
1482
1483 BEGIN
1484
1485 l_debugInfo := 'Decode document_id';
1486 l_colon := instrb(document_id, ':');
1487 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
1488 l_itemtype := substrb(document_id, 1, l_colon - 1);
1489 l_itemkey := substrb(document_id, l_colon + 1);
1490
1491 l_debugInfo := 'Get org_id';
1492 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
1493
1494 -- MOAC UPTAKE --
1495 -- Have new call back function defined for Item to set org context.
1496 --
1497 -- l_debugInfo := 'Set Org context';
1498 -- fnd_client_info.set_org_context(l_orgId);
1499
1500 l_debugInfo := 'Generate header';
1501 if (display_type = 'text/plain') then
1502 document := '';
1503 else -- html
1504 FND_MESSAGE.SET_NAME('SQLAP','OIE_DISPUTED_HEADER_DUNNING');
1505 l_dispute_header_prompt := FND_MESSAGE.GET;
1506 document := indent_start||table_title_start|| l_dispute_header_prompt||table_title_end;
1507 document := document|| table_start;
1508 document := document|| tr_start;
1509
1510 document := document || th_text ||'Receipt Date' ||td_end;
1511 document := document || th_text ||'Merchant'||td_end;
1512 document := document || th_text ||'Billed Amount'||td_end||tr_end;
1513
1514 end if;
1515
1516 ------------------------------------------------------
1517 l_debugInfo := 'Get WF MIN_AMOUNT Item Attribute';
1518 ------------------------------------------------------
1519 l_minimumAmount := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'MIN_AMOUNT');
1520
1521 ------------------------------------------------------
1522 l_debugInfo := 'Get WF DATE_OBJ1 Item Attribute';
1523 ------------------------------------------------------
1524 --l_billedStartDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'DATE_OBJ1');
1525
1526 ------------------------------------------------------
1527 l_debugInfo := 'Get WF DATE_OBJ2 Item Attribute';
1528 ------------------------------------------------------
1529 l_billedEndDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'DATE_OBJ2');
1530
1531 --------------------------------------------------------------
1532 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
1533 --------------------------------------------------------------
1534 l_cardProgramID := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
1535
1536 --------------------------------------------------------------
1537 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
1538 --------------------------------------------------------------
1539 l_employeeID := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
1540
1541 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
1542
1543 l_debugInfo := 'Loop over all the Disputed charges';
1544
1545 IF (NOT AP_WEB_DB_CCARD_PKG.GetDisputedCcardTrxnCursor(l_cardProgramId,
1546 l_minimumAmount, l_employeeId, l_billedStartDate,
1547 l_billedEndDate, l_DisputedCharges_cursor)) THEN
1548 NULL;
1549 END IF;
1550
1551 LOOP
1552 FETCH l_DisputedCharges_cursor
1553 INTO l_transaction_date,
1554 l_merchant_name1,
1555 l_billed_amount,
1556 l_billed_currency_code;
1557 EXIT WHEN l_DisputedCharges_cursor%NOTFOUND;
1558 IF (display_type = 'text/plain') THEN
1559 --------------------------------------------
1560 l_debugInfo := 'Format Expense Line Info';
1561 --------------------------------------------
1562 l_lineInfo := to_char(l_transaction_date,l_dateFormat) || ' ' ||
1563 l_merchant_name1 || ' ' || to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22));
1564 -- set a new line
1565 document := document || ' ' || l_lineInfo;
1566 l_lineInfo := '';
1567 ELSE -- HTML type
1568 document := document || tr_start|| td_text||
1569 to_char(l_transaction_date,l_dateFormat) || td_end;
1570 document := document || td_text|| WF_NOTIFICATION.SubstituteSpecialChars(l_merchant_name1) || td_end;
1571 document := document || td_number|| to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || td_end||tr_end;
1572 END IF;
1573 END LOOP;
1574
1575 close l_DisputedCharges_cursor;
1576
1577 if (display_type = 'text/html') then
1578 document := document || table_end ||indent_end;
1579 end if;
1580
1581 document_type := display_type;
1582
1583 EXCEPTION
1584 WHEN OTHERS THEN
1585 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GenerateList',
1586 document_id, l_debugInfo);
1587 raise;
1588 END GenerateList;
1589
1590 PROCEDURE GenerateUnsubmittedList(document_id IN VARCHAR2,
1591 display_type IN VARCHAR2,
1592 document IN OUT NOCOPY VARCHAR2,
1593 document_type IN OUT NOCOPY VARCHAR2) IS
1594
1595 l_document_max NUMBER := 25000; -- 27721 fails
1596 l_debug_info VARCHAR2(1000);
1597 l_message VARCHAR2(2000);
1598 l_temp_clob CLOB;
1599 l_colon NUMBER;
1600
1601 BEGIN
1602 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'start GenerateUnsubmittedList');
1603
1604 WF_NOTIFICATION.NewClob(l_temp_clob,document);
1605 GenUnsubmittedClobList(document_id,
1606 display_type,
1607 l_temp_clob,
1608 document_type);
1609
1610
1611 dbms_lob.read(l_temp_clob,l_document_max,1,document);
1612
1613 if (dbms_lob.getlength(l_temp_clob) > l_document_max) then
1614
1615 l_colon := instr(document, '</tr>',-1);
1616 document := substr(document,1,l_colon+4);
1617 document := document || '</table><br>';
1618
1619 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_EXP_UNABLE_TO_SHOWLINES');
1620 l_message := FND_MESSAGE.GET;
1621 document := document || '<table>';
1622 document := document || '<tr>' || '&' || 'nbsp;</tr>';
1623 document := document || '<tr>' || '&' || 'nbsp;</tr>';
1624 document := document || '<tr>' ||td_text|| l_message || '</td></tr>';
1625 document := document || '</table>';
1626
1627 end if;
1628
1629 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'end GenerateUnsubmittedList');
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'GenerateUnsubmittedList',
1633 document_id, l_debug_info);
1634 raise;
1635
1636 END GenerateUnsubmittedList;
1637
1638 PROCEDURE GenUnsubmittedClobList(document_id IN VARCHAR2,
1639 display_type IN VARCHAR2,
1640 document IN OUT NOCOPY CLOB,
1641 document_type IN OUT NOCOPY VARCHAR2) IS
1642
1643 l_colon NUMBER;
1644 l_itemtype VARCHAR2(7);
1645 l_itemkey VARCHAR2(15);
1646 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
1647 l_billedStartDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
1648 l_billedEndDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
1649 l_minimumAmount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
1650 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
1651 l_dateFormat varchar2(30);
1652 l_transaction_date AP_WEB_DB_CCARD_PKG.ccTrxn_transDate;
1653 l_merchant_name1 AP_WEB_DB_CCARD_PKG.ccTrxn_merchantName1;
1654 l_billed_amount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
1655 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
1656 l_lineInfo VARCHAR2(2000);
1657 l_debugInfo VARCHAR2(1000);
1658 l_orgId number;
1659 l_UnsubmittedCharges_cursor AP_WEB_DB_CCARD_PKG.UnsubmittedCCTrxnCursor;
1660 l_expense_report_number VARCHAR2(60);
1661 l_expense_report_status VARCHAR2(30);
1662 l_displayed_status VARCHAR2(60);
1663 l_chargeType VARCHAR2(60);
1664 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
1665 l_title AK_REGIONS_VL.name%TYPE;
1666 l_trxID AP_WEB_DB_CCARD_PKG.ccTrxn_trxID;
1667 l_detail_header_prompt VARCHAR2(200);
1668 l_document long;
1669 l_document_max NUMBER := 25000;
1670
1671
1672 BEGIN
1673
1674
1675 l_debugInfo := 'Decode document_id';
1676 l_colon := instrb(document_id, ':');
1677 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
1678 l_itemtype := substrb(document_id, 1, l_colon - 1);
1679 l_itemkey := substrb(document_id, l_colon + 1);
1680
1681 l_debugInfo := 'Get org_id';
1682 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
1683
1684 -- MOAC UPTAKE --
1685 -- Have new call back function defined for Item to set org context.
1686 --
1687 -- l_debugInfo := 'Set Org context';
1688 -- fnd_client_info.set_org_context(l_orgId);
1689
1690
1691 ------------------------------------------------------------
1692 l_debugInfo := 'Get prompts';
1693 ------------------------------------------------------------
1694 AP_WEB_DISC_PKG.getPrompts(200,'AP_WEB_WF_CC_LINETABLE',l_title,l_prompts);
1695
1696
1697 l_debugInfo := 'Generate header';
1698 if (display_type = 'text/plain') then
1699 l_document := '';
1700 else -- html
1701 FND_MESSAGE.SET_NAME('SQLAP','OIE_DETAIL_HEADER_DUNNING');
1702 l_detail_header_prompt := FND_MESSAGE.GET;
1703
1704 l_document := indent_start||table_title_start|| l_detail_header_prompt||table_title_end;
1705
1706 l_document := l_document||table_start;
1707 l_document := l_document||tr_start;
1708
1709 -- 'Receipt Date'
1710 l_document := l_document || th_text || l_prompts(6) || td_end;
1711 -- 'Billed Amount'
1712 l_document := l_document || th_number || l_prompts(2) || td_end;
1713 -- 'Merchant Name'
1714 l_document := l_document || th_text || l_prompts(3) || td_end;
1715 -- 'Report Number'
1716 l_document := l_document || th_text || l_prompts(4) || td_end;
1717 -- 'Status'
1718 l_document := l_document || th_text || l_prompts(5) || td_end;
1719 l_document := l_document ||tr_end;
1720 end if;
1721
1722 ------------------------------------------------------
1723 l_debugInfo := 'Get WF MIN_AMOUNT Item Attribute';
1724 ------------------------------------------------------
1725 l_minimumAmount := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'MIN_AMOUNT');
1726
1727 ------------------------------------------------------
1728 l_debugInfo := 'Get WF DATE1 Item Attribute';
1729 ------------------------------------------------------
1730 l_billedStartDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'DATE1');
1731
1732 ------------------------------------------------------
1733 l_debugInfo := 'Get WF DATE2 Item Attribute';
1734 ------------------------------------------------------
1735 l_billedEndDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'DATE2');
1736
1737 --------------------------------------------------------------
1738 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
1739 --------------------------------------------------------------
1740 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
1741
1742 --------------------------------------------------------------
1743 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
1744 --------------------------------------------------------------
1745 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
1746
1747 --------------------------------------------------------------
1748 l_debugInfo := 'Get Charge Type Item Attribute';
1749 --------------------------------------------------------------
1750 l_chargeType := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'CHARGE_TYPE');
1751
1752
1753 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
1754
1755 l_debugInfo := 'Loop over all the Unsubmitted charges ';
1756
1757 IF (NOT AP_WEB_DB_CCARD_PKG.GetUnsubmittedCcardTrxnCursor(l_cardProgramId,
1758 l_employeeId, l_billedStartDate,
1759 l_billedEndDate, l_minimumAmount, l_chargeType, l_UnsubmittedCharges_cursor)) THEN
1760 NULL;
1761 END IF;
1762
1763 LOOP
1764 FETCH l_UnsubmittedCharges_cursor
1765 INTO l_transaction_date,
1766 l_merchant_name1,
1767 l_billed_amount,
1768 l_billed_currency_code,
1769 l_expense_report_number,
1770 l_expense_report_status,
1771 l_trxID; -- Bug 3241358
1772
1773 EXIT WHEN l_UnsubmittedCharges_cursor%NOTFOUND;
1774
1775 IF lengthb(l_document) >= l_document_max THEN
1776 -- Appends document to end of document (CLOB object)
1777 WF_NOTIFICATION.WriteToClob(document,l_document);
1778 l_document := '';
1779 END IF;
1780
1781 BEGIN
1782 select displayed_field
1783 into l_displayed_status
1784 from ap_lookup_codes
1785 where lookup_type = AP_WEB_OA_ACTIVE_PKG.C_EXPENSE_REPORT_STATUS
1786 and lookup_code = l_expense_report_status;
1787
1788 EXCEPTION
1789 WHEN NO_DATA_FOUND THEN NULL;
1790 END;
1791
1792 IF (display_type = 'text/plain') THEN
1793 --------------------------------------------
1794 l_debugInfo := 'Format Expense Line Info';
1795 --------------------------------------------
1796 l_lineInfo := to_char(l_transaction_date,l_dateFormat) || ' ' ||
1797 to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || ' ' ||
1798 l_merchant_name1 || ' ' ||
1799 l_expense_report_number || ' ' ||
1800 l_expense_report_status;
1801
1802 -- set a new line
1803 l_document := l_document || ' ' || l_lineInfo;
1804 l_lineInfo := '';
1805 ELSE -- HTML type
1806 l_document := l_document || tr_start;
1807 l_document := l_document || td_text ||to_char(l_transaction_date,l_dateFormat)|| td_end;
1808 l_document := l_document || td_number||to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || td_end;
1809 l_document := l_document || td_text|| WF_NOTIFICATION.SubstituteSpecialChars(l_merchant_name1) || td_end;
1810 l_document := l_document || td_text|| l_expense_report_number ||td_end;
1811 l_document := l_document || td_text|| l_displayed_status ||td_end;
1812 l_document := l_document || tr_end;
1813 END IF;
1814 END LOOP;
1815
1816 close l_UnsubmittedCharges_cursor;
1817
1818 if (display_type = 'text/html') then
1819 l_document := l_document || table_end||indent_end;
1820 end if;
1821
1822 WF_NOTIFICATION.WriteToClob(document,l_document);
1823
1824 document_type := display_type;
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GenerateUnsubmittedList',
1829 document_id, l_debugInfo);
1830 raise;
1831 END GenUnsubmittedClobList;
1832
1833 /*Written By : Amulya Mishra
1834 Purpose: Notification Escalation project.
1835 Rewrote the existing procedure to call GenerateDunningClobList().
1836 */
1837
1838 PROCEDURE GenerateDunningList(document_id IN VARCHAR2,
1839 display_type IN VARCHAR2,
1840 document IN OUT NOCOPY VARCHAR2,
1841 document_type IN OUT NOCOPY VARCHAR2) IS
1842
1843 l_document_max NUMBER := 25000; -- 27721 fails
1844 l_debug_info VARCHAR2(1000);
1845 l_message VARCHAR2(2000);
1846 l_temp_clob CLOB;
1847 l_colon NUMBER;
1848 BEGIN
1849
1850 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'start GenerateDunningList');
1851
1852 WF_NOTIFICATION.NewClob(l_temp_clob,document);
1853 GenerateDunningClobList(document_id,
1854 display_type,
1855 l_temp_clob,
1856 document_type);
1857 dbms_lob.read(l_temp_clob,l_document_max,1,document);
1858
1859 if (dbms_lob.getlength(l_temp_clob) > l_document_max) then
1860
1861 l_colon := instr(document, '</tr>',-1);
1862 document := substr(document,1,l_colon+4);
1863 document := document || '</table><br>';
1864
1865 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_EXP_UNABLE_TO_SHOWLINES');
1866 l_message := FND_MESSAGE.GET;
1867 document := document || '<table>';
1868 document := document || '<tr>' || '&' || 'nbsp;</tr>';
1869 document := document || '<tr>' || '&' || 'nbsp;</tr>';
1870 document := document || '<tr>' ||td_text|| l_message || '</td></tr>';
1871 document := document || '</table>';
1872
1873 end if;
1874
1875 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_EXPENSE_WF', 'end GenerateDunningList');
1876
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GenerateDunningList',
1880 document_id, l_debug_info);
1881 raise;
1882 END GenerateDunningList;
1883
1884
1885 /*Written By : Amulya Mishra
1886 Purpose: Notification Escalation project.
1887 New Procedure to display CLOB based document GenerateDunningClobList().
1888 */
1889
1890
1891
1892 PROCEDURE GenerateDunningClobList(document_id IN VARCHAR2,
1893 display_type IN VARCHAR2,
1894 document IN OUT NOCOPY CLOB,--Notification Esc
1895 document_type IN OUT NOCOPY VARCHAR2) IS
1896
1897 l_colon NUMBER;
1898 l_itemtype VARCHAR2(7);
1899 l_itemkey VARCHAR2(15);
1900 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
1901 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
1902 l_dateFormat VARCHAR2(30);
1903 l_transaction_date AP_WEB_DB_CCARD_PKG.ccTrxn_transDate;
1904 l_merchant_name1 AP_WEB_DB_CCARD_PKG.ccTrxn_merchantName1;
1905 l_billed_amount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
1906 l_billed_date AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
1907 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
1908 l_lineInfo VARCHAR2(2000);
1909 l_debugInfo VARCHAR2(1000);
1910 l_orgId NUMBER;
1911 l_Dunning_cursor AP_WEB_DB_CCARD_PKG.DunningCCTrxnCursor;
1912 l_expense_report_number VARCHAR2(60);
1913 l_expense_report_status VARCHAR2(30);
1914 l_displayed_status VARCHAR2(60);
1915 l_min_bucket NUMBER;
1916 l_max_bucket NUMBER;
1917 l_min_bucket1 VARCHAR2(30);
1918 l_max_bucket1 VARCHAR2(30);
1919 l_instructions VARCHAR2(200);
1920 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
1921 l_title AK_REGIONS_VL.name%TYPE;
1922
1923 --Notification Project
1924 l_document long ;
1925 l_document_max NUMBER := 25000;
1926
1927 l_posted_date AP_CREDIT_CARD_TRXNS_ALL.POSTED_DATE%TYPE; --Notification Esc
1928 l_posted_currency_code AP_CREDIT_CARD_TRXNS_ALL.POSTED_CURRENCY_CODE%TYPE;--3339380
1929 l_transaction_amount AP_CREDIT_CARD_TRXNS_ALL.TRANSACTION_AMOUNT%TYPE;--Notification Esc
1930 l_location AP_EXPENSE_REPORT_LINES_ALL.LOCATION%TYPE; --Notification Esc
1931 l_Dispute_cursor AP_WEB_DB_CCARD_PKG.DisputeCCTrxnCursor; --Notification Esc
1932
1933 l_detail_header_prompt VARCHAR2(2000);
1934 l_dispute_header_prompt VARCHAR2(2000);
1935 l_grace_days NUMBER;
1936
1937 l_total_dispute NUMBER; --Bug 3326035
1938 l_total_amt_dispute NUMBER; --Bug 3326035
1939
1940
1941
1942 --Notification Project
1943 l_trxID AP_WEB_DB_CCARD_PKG.ccTrxn_trxID;
1944
1945 BEGIN
1946
1947
1948 l_debugInfo := 'Decode document_id';
1949 l_colon := instrb(document_id, ':');
1950 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
1951 l_itemtype := substrb(document_id, 1, l_colon - 1);
1952 l_itemkey := substrb(document_id, l_colon + 1);
1953
1954 l_debugInfo := 'Get org_id';
1955 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
1956
1957 -- MOAC UPTAKE --
1958 -- Have new call back function defined for Item to set org context.
1959 --
1960 -- l_debugInfo := 'Set Org context';
1961 -- fnd_client_info.set_org_context(l_orgId);
1962
1963 ------------------------------------------------------------
1964 l_debugInfo := 'Get prompts';
1965 ------------------------------------------------------------
1966 AP_WEB_DISC_PKG.getPrompts(200,'AP_WEB_WF_CC_LINETABLE',l_title,l_prompts);
1967
1968
1969 l_debugInfo := 'Generate header';
1970 l_document := '';--Notification Esc :Change all document to l_document
1971
1972 --Notification esc : moved code to top..
1973
1974 l_grace_days := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'AGING_GRACE_DAYS');
1975 --------------------------------------------------------------
1976 l_debugInfo := 'Get WF BUCKET1..4 Item Attribute';
1977 --------------------------------------------------------------
1978 l_min_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET1');
1979 l_max_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET2');
1980
1981 l_min_bucket := to_number(l_min_bucket1);
1982 if(l_max_bucket1 = '+' ) then
1983 l_max_bucket := 1000000;
1984 else
1985 l_max_bucket := to_number(substr(l_max_bucket1,3));
1986 end if;
1987
1988 if (display_type = 'text/plain') then
1989 l_document := '';
1990 else -- html
1991 --Notification Esc : SET HEADER
1992 FND_MESSAGE.SET_NAME('SQLAP','OIE_DETAIL_HEADER_DUNNING');
1993 l_detail_header_prompt := FND_MESSAGE.GET;
1994
1995 l_document := indent_start||table_title_start || l_detail_header_prompt || table_title_end;
1996
1997 l_document := l_document||table_start;
1998 l_document := l_document||tr_start;
1999 -- 'Transaction date'
2000 l_document := l_document || th_text || l_prompts(8) || td_end;
2001 -- 'Posted Date'
2002 l_document := l_document || th_text || l_prompts(9) || td_end;
2003 -- 'Transaction Amount'
2004 l_document := l_document || th_number || l_prompts(7) || td_end;
2005 -- 'Billed Amount'
2006 l_document := l_document || th_number || l_prompts(2) || td_end;
2007 --Notification Esc :
2008 -- 'Merchant Name'
2009 l_document := l_document || th_text || l_prompts(3) || td_end;
2010 -- 'Location'
2011 l_document := l_document || th_text || l_prompts(10) || td_end;
2012 -- 'Report Number'
2013 l_document := l_document || th_text || l_prompts(4) || td_end;
2014 -- 'Status'
2015 l_document := l_document || th_text || l_prompts(5) || td_end;
2016 l_document := l_document || tr_end;
2017 end if;
2018
2019 --------------------------------------------------------------
2020 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
2021 --------------------------------------------------------------
2022 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
2023 --------------------------------------------------------------
2024 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
2025 --------------------------------------------------------------
2026 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
2027 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
2028
2029 l_debugInfo := 'Loop over all the Dunning Charges ';
2030
2031 IF (NOT AP_WEB_DB_CCARD_PKG.GetDunningCcardTrxnCursor(l_cardProgramId,
2032 l_employeeId, l_min_bucket, l_max_bucket,
2033 l_Dunning_cursor)) THEN
2034 NULL;
2035 END IF;
2036 LOOP
2037 FETCH l_Dunning_cursor
2038 INTO l_transaction_date,
2039 l_merchant_name1,
2040 l_billed_amount,
2041 l_billed_currency_code,
2042 l_posted_currency_code, --bug 3339380
2043 l_expense_report_number,
2044 l_expense_report_status,
2045 l_billed_date,
2046 l_posted_date, --Notification Esc
2047 l_transaction_amount,--Notification Esc
2048 l_location, --Notification Esc
2049 l_trxID; -- Bug 3241358
2050
2051
2052 EXIT WHEN l_Dunning_cursor%NOTFOUND;
2053 --Notification Esc
2054 IF lengthb(l_document) >= l_document_max THEN
2055 -- Appends document to end of document (CLOB object)
2056 WF_NOTIFICATION.WriteToClob(document,l_document);
2057 l_document := '';
2058 END IF;
2059 --Notification Esc
2060
2061 BEGIN
2062 select displayed_field
2063 into l_displayed_status
2064 from ap_lookup_codes
2065 where lookup_type = AP_WEB_OA_ACTIVE_PKG.C_EXPENSE_REPORT_STATUS
2066 and lookup_code = l_expense_report_status;
2067
2068 EXCEPTION
2069 WHEN NO_DATA_FOUND THEN NULL;
2070 END;
2071 IF (display_type = 'text/plain') THEN
2072 --------------------------------------------
2073 l_debugInfo := 'Format Expense Line Info';
2074 --------------------------------------------
2075 --Bug 3339380 : Added posted currency code to Transaction Amount
2076 l_lineInfo := to_char(l_transaction_date,l_dateFormat) || ' ' || to_char(l_posted_date,l_dateFormat) || ' ' ||
2077 to_char(l_transaction_amount, FND_CURRENCY.Get_Format_Mask(l_posted_currency_code,22)) || ' ' || l_posted_currency_code|| ' ' ||
2078 to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || ' ' ||
2079 l_merchant_name1 || ' ' || l_location || ' ' ||
2080 l_expense_report_number || ' ' ||
2081 l_displayed_status;
2082
2083 -- set a new line
2084 l_document := l_document || '
2085 ' ;
2086 l_document := l_document || l_lineInfo;
2087 l_lineInfo := '';
2088 ELSE -- HTML type
2089 l_document := l_document || tr_start;
2090
2091 --Amulya Mishra Notification Esc:
2092
2093 l_document := l_document ||td_text || to_char(l_transaction_date,l_dateFormat) ||td_end;
2094 l_document := l_document ||td_text || to_char(l_posted_date,l_dateFormat) ||td_end;
2095 --Bug 3339380 : Added posted currency code to Transaction Amount
2096 l_document := l_document || td_number || to_char(l_transaction_amount, FND_CURRENCY.Get_Format_Mask(l_posted_currency_code,22));
2097
2098 l_document := l_document || ' ' || l_posted_currency_code || td_end;
2099
2100
2101 l_document := l_document || td_number || to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || td_end;
2102 l_document := l_document || td_text || WF_NOTIFICATION.SubstituteSpecialChars(l_merchant_name1) || td_end;
2103 l_document := l_document || td_text || l_location ||td_end;
2104 l_document := l_document || td_text || l_expense_report_number || td_end;
2105 l_document := l_document || td_text || l_displayed_status || td_end;
2106 l_document := l_document || tr_end;
2107
2108 END IF;
2109
2110 END LOOP;
2111
2112 close l_Dunning_cursor;
2113
2114 --AMulya Mishra : Notification Esc:
2115
2116 if (display_type = 'text/html') then
2117 l_document := l_document||table_end|| indent_end;
2118 l_document := l_document || '<br><br>';
2119 end if;
2120 WF_NOTIFICATION.WriteToClob(document,l_document); --Notification Esc
2121
2122 l_document := '';
2123
2124
2125 --Amulya Mishra : Notification Esc Project: Disputed Trxns
2126
2127 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(l_cardProgramId,l_employeeId,
2128 l_min_bucket, l_max_bucket ,
2129 l_grace_days,l_total_dispute,
2130 l_total_amt_dispute)) THEN
2131 NULL;
2132 END IF;
2133
2134 IF l_total_dispute > 0 THEN --Bug 3326035
2135 if (display_type = 'text/plain') then
2136 l_document := '';
2137 else -- html
2138 FND_MESSAGE.SET_NAME('SQLAP','OIE_DISPUTED_HEADER_DUNNING');
2139 l_dispute_header_prompt := FND_MESSAGE.GET;
2140 l_document := indent_start||table_title_start|| l_dispute_header_prompt ||table_title_end;
2141 l_document := l_document|| table_start;
2142 l_document := l_document|| tr_start;
2143
2144 -- 'Transaction Date'
2145 l_document := l_document || th_text|| l_prompts(8) || td_end;
2146 -- 'Posted Date'
2147 l_document := l_document || th_text|| l_prompts(9) || td_end;
2148 -- 'Transaction Amount'
2149 l_document := l_document || th_number|| l_prompts(7) || td_end;
2150 -- 'Billed Amount'
2151 l_document := l_document || th_number|| l_prompts(2) || td_end;
2152 -- 'Merchant Name'
2153 l_document := l_document || th_text|| l_prompts(3) || td_end;
2154 -- 'Location'
2155 l_document := l_document || th_text|| l_prompts(10) ||td_end;
2156 l_document := l_document || tr_end;
2157
2158 end if;
2159
2160 l_debugInfo := 'Loop over all the Disputed Charges ';
2161
2162 IF (NOT AP_WEB_DB_CCARD_PKG.GetDisputeCcardTrxnCursor(l_cardProgramId,
2163 l_employeeId, l_min_bucket, l_max_bucket,l_grace_days,
2164 l_Dispute_cursor)) THEN
2165 NULL;
2166 END IF;
2167
2168 LOOP
2169 FETCH l_Dispute_cursor
2170 INTO l_transaction_date,
2171 l_merchant_name1,
2172 l_billed_amount,
2173 l_billed_currency_code,
2174 l_posted_currency_code,--3339380
2175 l_billed_date,
2176 l_posted_date, --Notification Esc
2177 l_transaction_amount,--Notification Esc
2178 l_location; --Notification Esc
2179
2180
2181 EXIT WHEN l_Dispute_cursor%NOTFOUND;
2182
2183 --Amulya Mishra : Notification Esc Project
2184
2185 IF lengthb(l_document) >= l_document_max THEN
2186 -- Appends document to end of document (CLOB object)
2187 WF_NOTIFICATION.WriteToClob(document,l_document);
2188 l_document := '';
2189 END IF;
2190 --Amulya Mishra : Notification Esc Project
2191
2192 IF (display_type = 'text/plain') THEN
2193 --------------------------------------------
2194 l_debugInfo := 'Format Expense Line Info';
2195 --------------------------------------------
2196 --Bug 3339380 : Added posted currency code to Transaction Amount
2197 l_lineInfo := to_char(l_transaction_date,l_dateFormat) || ' ' || to_char(l_posted_date,l_dateFormat) || ' ' ||
2198 to_char(l_transaction_amount, FND_CURRENCY.Get_Format_Mask(l_posted_currency_code,22)) || ' ' || l_posted_currency_code || ' ' ||
2199 to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || ' ' ||
2200 l_merchant_name1 || ' ' || l_location;
2201
2202 -- set a new line
2203
2204 -- set a new line
2205 l_document := l_document || '
2206 ' ;
2207 l_document := l_document || l_lineInfo;
2208
2209 l_lineInfo := '';
2210
2211 ELSE -- HTML type
2212
2213 l_document := l_document || tr_start;
2214 l_document := l_document || td_text|| to_char(l_transaction_date,l_dateFormat) ||td_end;
2215 l_document := l_document || td_text|| to_char(l_posted_date,l_dateFormat) ||td_end;
2216 --Bug 3339380 : Added posted currency code to Transaction Amount
2217 l_document := l_document || td_number|| to_char(l_transaction_amount, FND_CURRENCY.Get_Format_Mask(l_posted_currency_code,22));
2218
2219 l_document := l_document || ' ' || l_posted_currency_code ||td_end;
2220
2221 l_document := l_document || td_number|| to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || td_end;
2222 l_document := l_document || td_text || WF_NOTIFICATION.SubstituteSpecialChars(l_merchant_name1) || td_end;
2223 l_document := l_document || td_text|| l_location ||td_end;
2224 l_document := l_document || tr_end;
2225
2226
2227 END IF;
2228 END LOOP;
2229
2230 close l_Dispute_cursor;
2231 END IF; --End of IF l_total_dispute > 0 - bug 3326035
2232 if (display_type = 'text/html') then
2233 l_document := l_document || table_end||indent_end;
2234 end if;
2235 WF_NOTIFICATION.WriteToClob(document,l_document); --Notification Esc
2236 document_type := display_type;
2237
2238 --Amulya Mishra : Notification esc project :
2239
2240 EXCEPTION
2241 WHEN OTHERS THEN
2242 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GenerateDunningClobList',
2243 document_id, l_debugInfo);
2244 raise;
2245 END GenerateDunningClobList;
2246
2247
2248
2249 PROCEDURE getNumofUnsubmittedrecords(document_id IN VARCHAR2,
2250 display_type IN VARCHAR2,
2251 document IN OUT NOCOPY VARCHAR2,
2252 document_type IN OUT NOCOPY VARCHAR2) IS
2253
2254 l_colon NUMBER;
2255 l_itemtype VARCHAR2(7);
2256 l_itemkey VARCHAR2(15);
2257 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
2258 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
2259 l_dateFormat VARCHAR2(30);
2260 l_transaction_date AP_WEB_DB_CCARD_PKG.ccTrxn_transDate;
2261 l_merchant_name1 AP_WEB_DB_CCARD_PKG.ccTrxn_merchantName1;
2262 l_billed_amount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
2263 l_billed_date AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
2264 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
2265 l_lineInfo VARCHAR2(2000);
2266 l_debugInfo VARCHAR2(1000);
2267 l_orgId NUMBER;
2268 l_Total_cursor AP_WEB_DB_CCARD_PKG.UnsubmittedCCTrxnCursor;
2269 l_expense_report_number VARCHAR2(60);
2270 l_expense_report_status VARCHAR2(30);
2271 l_displayed_status VARCHAR2(60);
2272 l_instructions VARCHAR2(2000);
2273
2274 l_billedStartDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
2275 l_billedEndDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
2276 l_minimumAmount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
2277 l_chargeType VARCHAR2(60);
2278 l_trxID AP_WEB_DB_CCARD_PKG.ccTrxn_trxID;
2279
2280 BEGIN
2281
2282
2283 l_debugInfo := 'Decode document_id';
2284 l_colon := instrb(document_id, ':');
2285 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
2286 l_itemtype := substrb(document_id, 1, l_colon - 1);
2287 l_itemkey := substrb(document_id, l_colon + 1);
2288
2289 l_debugInfo := 'Get org_id';
2290 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
2291
2292 -- MOAC UPTAKE --
2293 -- Have new call back function defined for Item to set org context.
2294 --
2295 -- l_debugInfo := 'Set Org context';
2296 -- fnd_client_info.set_org_context(l_orgId);
2297
2298 --------------------------------------------------------------
2299 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
2300 --------------------------------------------------------------
2301 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
2302
2303 --------------------------------------------------------------
2304 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
2305 --------------------------------------------------------------
2306 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
2307
2308 ------------------------------------------------------
2309 l_debugInfo := 'Get WF MIN_AMOUNT Item Attribute';
2310 ------------------------------------------------------
2311 l_minimumAmount := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'MIN_AMOUNT');
2312
2313 ------------------------------------------------------
2314 l_debugInfo := 'Get WF DATE1 Item Attribute';
2315 ------------------------------------------------------
2316 l_billedStartDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'DATE1');
2317
2318 ------------------------------------------------------
2319 l_debugInfo := 'Get WF DATE2 Item Attribute';
2320 ------------------------------------------------------
2321 l_billedEndDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'DATE2');
2322
2323 --------------------------------------------------------------
2324 l_debugInfo := 'Get Charge Type Item Attribute';
2325 --------------------------------------------------------------
2326 l_chargeType := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'CHARGE_TYPE');
2327
2328 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
2329
2330 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalUnsubmittedCCCursor(l_cardProgramId,
2331 l_employeeId, l_billedStartDate,
2332 l_billedEndDate, l_minimumAmount, l_chargeType, l_Total_cursor)) THEN
2333 NULL;
2334 END IF;
2335
2336 LOOP
2337 FETCH l_Total_cursor
2338 INTO l_transaction_date,
2339 l_merchant_name1,
2340 l_billed_amount,
2341 l_billed_currency_code,
2342 l_expense_report_number,
2343 l_expense_report_status,
2344 l_trxID; -- Bug 3241358
2345
2346 EXIT WHEN l_Total_cursor%NOTFOUND;
2347 END LOOP;
2348 if(l_Total_cursor%ROWCOUNT>= 40) then
2349 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_NUM_RECORDS');
2350 FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRANS', l_Total_cursor%ROWCOUNT);
2351 l_instructions := FND_MESSAGE.GET;
2352 document := document || '<table>';
2353 document := document || '<tr>' || '&' || 'nbsp;</tr>';
2354 document := document || '<tr>' || '&' || 'nbsp;</tr>';
2355 document := document || '<tr>' ||td_text|| l_instructions || '</td></tr>';
2356 document := document || '</table>';
2357
2358 end if;
2359
2360 close l_Total_cursor;
2361
2362 if (display_type = 'text/html') then
2363 document := document || '</table>';
2364 end if;
2365
2366 document_type := display_type;
2367
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'getNumofUnsubmittedrecords',
2371 document_id, l_debugInfo);
2372 raise;
2373 END getNumofUnsubmittedrecords;
2374
2375
2376 PROCEDURE getNumofDunningrecords(document_id IN VARCHAR2,
2377 display_type IN VARCHAR2,
2378 document IN OUT NOCOPY VARCHAR2,
2379 document_type IN OUT NOCOPY VARCHAR2) IS
2380
2381 l_colon NUMBER;
2382 l_itemtype VARCHAR2(7);
2383 l_itemkey VARCHAR2(15);
2384 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
2385 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
2386 l_dateFormat VARCHAR2(30);
2387 l_transaction_date AP_WEB_DB_CCARD_PKG.ccTrxn_transDate;
2388 l_merchant_name1 AP_WEB_DB_CCARD_PKG.ccTrxn_merchantName1;
2389 l_billed_amount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
2390 l_billed_date AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
2391 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
2392 l_lineInfo VARCHAR2(2000);
2393 l_debugInfo VARCHAR2(1000);
2394 l_orgId NUMBER;
2395 l_Total_cursor AP_WEB_DB_CCARD_PKG.TotalCCTrxnCursor;
2396 l_expense_report_number VARCHAR2(60);
2397 l_expense_report_status VARCHAR2(30);
2398 l_displayed_status VARCHAR2(60);
2399 l_min_bucket NUMBER;
2400 l_max_bucket NUMBER;
2401 l_min_bucket1 VARCHAR2(30);
2402 l_max_bucket1 VARCHAR2(30);
2403 l_instructions VARCHAR2(2000);
2404 l_trxID AP_WEB_DB_CCARD_PKG.ccTrxn_trxID;
2405
2406 BEGIN
2407
2408
2409 l_debugInfo := 'Decode document_id';
2410 l_colon := instrb(document_id, ':');
2411 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
2412 l_itemtype := substrb(document_id, 1, l_colon - 1);
2413 l_itemkey := substrb(document_id, l_colon + 1);
2414
2415 l_debugInfo := 'Get org_id';
2416 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
2417
2418 -- MOAC UPTAKE --
2419 -- Have new call back function defined for Item to set org context.
2420 --
2421 -- l_debugInfo := 'Set Org context';
2422 -- fnd_client_info.set_org_context(l_orgId);
2423
2424 --------------------------------------------------------------
2425 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
2426 --------------------------------------------------------------
2427 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
2428
2429 --------------------------------------------------------------
2430 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
2431 --------------------------------------------------------------
2432 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
2433
2434 --------------------------------------------------------------
2435 l_debugInfo := 'Get WF BUCKET1..4 Item Attribute';
2436 --------------------------------------------------------------
2437 l_min_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET1');
2438 l_max_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET2');
2439
2440
2441 l_min_bucket := to_number(l_min_bucket1);
2442 if(l_max_bucket1 = '+' ) then
2443 l_max_bucket := 1000000;
2444 else
2445 l_max_bucket := to_number(substr(l_max_bucket1,3));
2446 end if;
2447
2448 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
2449
2450 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalCcardTrxnCursor(l_cardProgramId,
2451 l_employeeId, l_min_bucket, l_max_bucket,
2452 l_Total_cursor)) THEN
2453 NULL;
2454 END IF;
2455
2456 LOOP
2457 FETCH l_Total_cursor
2458 INTO l_transaction_date,
2459 l_merchant_name1,
2460 l_billed_amount,
2461 l_billed_currency_code,
2462 l_expense_report_number,
2463 l_expense_report_status,
2464 l_billed_date,
2465 l_trxID; -- Bug 3241358
2466
2467 EXIT WHEN l_Total_cursor%NOTFOUND;
2468 END LOOP;
2469 if(l_Total_cursor%ROWCOUNT>= 40) then
2470 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_NUM_RECORDS');
2471 FND_MESSAGE.SET_TOKEN('NUMBER_OF_TRANS', l_Total_cursor%ROWCOUNT);
2472 l_instructions := FND_MESSAGE.GET;
2473 document := document || '<table>';
2474 document := document || '<tr>' || '&' || 'nbsp;</tr>';
2475 document := document || '<tr>' || '&' || 'nbsp;</tr>';
2476 document := document || '<tr>' ||td_text|| l_instructions || '</td></tr>';
2477 document := document || '</table>';
2478
2479 end if;
2480
2481 close l_Total_cursor;
2482
2483 if (display_type = 'text/html') then
2484 document := document || '</table>';
2485 end if;
2486
2487 document_type := display_type;
2488
2489 EXCEPTION
2490 WHEN OTHERS THEN
2491 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'getNumofDunningrecords',
2492 document_id, l_debugInfo);
2493 raise;
2494 END getNumofDunningrecords;
2495
2496
2497 /*Written By: Amulya Mishra
2498 Purpose : Notification Escalation project.
2499 Procedure that prints the body of manager's notification
2500 Body.
2501 */
2502
2503 PROCEDURE GenerateManagerDunningList(document_id IN VARCHAR2,
2504 display_type IN VARCHAR2,
2505 document IN OUT NOCOPY CLOB,
2506 document_type IN OUT NOCOPY VARCHAR2) IS
2507
2508 l_colon NUMBER;
2509 l_itemtype VARCHAR2(7);
2510 l_itemkey VARCHAR2(15);
2511 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
2512 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
2513 l_dateFormat VARCHAR2(30);
2514 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
2515 l_lineInfo VARCHAR2(2000);
2516 l_debugInfo VARCHAR2(1000);
2517 l_orgId NUMBER;
2518 l_min_bucket NUMBER;
2519 l_max_bucket NUMBER;
2520 l_min_bucket1 VARCHAR2(30);
2521 l_max_bucket1 VARCHAR2(30);
2522 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
2523 l_title AK_REGIONS_VL.name%TYPE;
2524
2525 l_document long ;
2526 l_document_max NUMBER := 25000;
2527
2528 l_detail_header_prompt VARCHAR2(2000);
2529 l_grace_days NUMBER;
2530
2531 l_employee_cursor EmployeeCursor;
2532 l_supervisor_id NUMBER;
2533
2534 l_employee_name PER_PEOPLE_F.FULL_NAME%TYPE;
2535
2536 l_total_outstanding NUMBER;
2537 l_total_amt_outstanding NUMBER;
2538 l_total_dispute NUMBER;
2539 l_total_amt_dispute NUMBER;
2540 l_total_amount NUMBER;
2541
2542 l_total_num_outstanding NUMBER := 0; --Bug 3310243
2543
2544 l_gross_outstanding NUMBER := 0;
2545 l_gross_amount NUMBER := 0;
2546
2547 l_count NUMBER := 0; --Direct Report
2548
2549
2550 BEGIN
2551
2552
2553 l_debugInfo := 'Decode document_id';
2554 l_colon := instrb(document_id, ':');
2555 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
2556 l_itemtype := substrb(document_id, 1, l_colon - 1);
2557 l_itemkey := substrb(document_id, l_colon + 1);
2558
2559 l_debugInfo := 'Get org_id';
2560 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
2561
2562 -- MOAC UPTAKE --
2563 -- Have new call back function defined for Item to set org context.
2564 --
2565 -- l_debugInfo := 'Set Org context';
2566 -- fnd_client_info.set_org_context(l_orgId);
2567 ------------------------------------------------------------
2568 l_debugInfo := 'Get prompts';
2569 ------------------------------------------------------------
2570 AP_WEB_DISC_PKG.getPrompts(200,'AP_WEB_WF_CC_LINETABLE',l_title,l_prompts);
2571
2572
2573
2574 l_debugInfo := 'Generate header';
2575 l_document := '';
2576
2577 l_grace_days := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'AGING_GRACE_DAYS');
2578
2579 l_billed_currency_code := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'CURRENCY');
2580 --------------------------------------------------------------
2581 l_debugInfo := 'Get WF BUCKET1..4 Item Attribute';
2582 --------------------------------------------------------------
2583 l_min_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET1');
2584 l_max_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET2');
2585
2586 l_min_bucket := to_number(l_min_bucket1);
2587 if(l_max_bucket1 = '+' ) then
2588 l_max_bucket := 1000000;
2589 else
2590 l_max_bucket := to_number(substr(l_max_bucket1,3));
2591 end if;
2592
2593 if (display_type = 'text/plain') then
2594 l_document := '';
2595 else -- html
2596
2597 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_SUMMARY_OUTSTANDING_MSG');
2598 l_detail_header_prompt := FND_MESSAGE.GET;
2599
2600 l_document := indent_start||table_title_start || l_detail_header_prompt ||table_title_end;
2601
2602 l_document := l_document || table_start||tr_start;
2603 -- 'Employee'
2604 l_document := l_document || th_text|| l_prompts(11) || td_end;
2605 -- 'Number Outstanding'
2606 l_document := l_document || th_number || l_prompts(12) || td_end;
2607 -- 'Disputes Outstanding'
2608 l_document := l_document || th_number || l_prompts(13) || td_end;
2609 -- 'Aging Period Outstanding'
2610 l_document := l_document || th_number || l_prompts(14) ||td_end;
2611 -- 'Total Outstanding'
2612 l_document := l_document || th_number || l_prompts(15) || td_end;
2613 l_document := l_document || tr_end;
2614 end if;
2615
2616
2617 --------------------------------------------------------------
2618 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
2619 --------------------------------------------------------------
2620 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
2621 --------------------------------------------------------------
2622 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
2623 --------------------------------------------------------------
2624 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
2625 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
2626
2627 l_debugInfo := 'Loop over all the Dunning Charges ';
2628
2629 AP_WEB_EXPENSE_WF.GetManager(l_employeeId, l_supervisor_id);
2630
2631 IF (NOT GetEmployeeCursor(l_supervisor_id,l_employee_cursor)) THEN
2632 NULL;
2633 END IF;
2634
2635 LOOP
2636 FETCH l_employee_cursor
2637 INTO l_employeeId,l_employee_name;
2638
2639
2640 EXIT WHEN l_employee_cursor%NOTFOUND;
2641
2642 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberOutstanding(l_cardProgramId,l_employeeId,
2643 l_min_bucket, l_max_bucket , l_total_outstanding,
2644 l_total_amt_outstanding)) THEN
2645 NULL;
2646 END IF;
2647
2648
2649
2650 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(l_cardProgramId,l_employeeId,
2651 l_min_bucket, l_max_bucket ,
2652 l_grace_days,l_total_dispute,
2653 l_total_amt_dispute)) THEN
2654 NULL;
2655 END IF;
2656
2657
2658
2659 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalCreditCardAmount(l_cardProgramId,l_employeeId,
2660 l_total_amount ) ) THEN
2661 NULL;
2662 END IF;
2663
2664
2665
2666 IF lengthb(l_document) >= l_document_max THEN
2667 -- Appends document to end of document (CLOB object)
2668 WF_NOTIFICATION.WriteToClob(document,l_document);
2669 l_document := '';
2670 END IF;
2671
2672 IF ((nvl(l_total_outstanding,0) <> 0 OR nvl(l_total_dispute,0) <> 0) AND l_count <= 100) THEN --Direct Report
2673
2674 l_total_amt_outstanding := nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
2675
2676 --Bug 3310243
2677 l_total_num_outstanding := nvl(l_total_outstanding,0) + nvl(l_total_dispute,0);
2678
2679 IF (display_type = 'text/plain') THEN
2680
2681 --------------------------------------------
2682 -- 'Format Expense Line Info';
2683 --------------------------------------------
2684
2685 l_lineInfo := l_employee_name || ' ' || l_total_num_outstanding || ' ' || l_total_dispute
2686 || ' ' || l_total_amt_outstanding ||' '|| l_total_amount ;
2687 -- set a new line
2688 l_document := l_document || ' ' || l_lineInfo;
2689 l_lineInfo := '';
2690 ELSE -- HTML type
2691
2692 l_document := l_document || tr_start;
2693
2694 l_document := l_document || td_text|| l_employee_name ||td_end;
2695 l_document := l_document || td_number|| l_total_num_outstanding ||td_end;
2696 l_document := l_document || td_number ||l_total_dispute||td_end;
2697 l_document := l_document || td_number|| LPAD(to_char(l_total_amt_outstanding, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
2698 l_document := l_document || td_number || LPAD(to_char(l_total_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
2699
2700 l_document := l_document || tr_end;
2701
2702 END IF;
2703
2704 l_count := l_count + 1;
2705
2706 END IF;
2707
2708 IF (l_count > 100) THEN
2709 l_total_amt_outstanding := nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
2710 END IF;
2711
2712 l_gross_outstanding := l_gross_outstanding + nvl(l_total_amt_outstanding,0);
2713 l_gross_amount := l_gross_amount + nvl(l_total_amount,0);
2714
2715 END LOOP;
2716 close l_employee_cursor;
2717
2718
2719 --------------------------------------------
2720 l_debugInfo := 'Generate Total Row';
2721 --------------------------------------------
2722
2723 l_document := l_document || tr_start;
2724 l_document := l_document || '<td colspan=3 style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:right}">' || l_prompts(17) || td_end;
2725
2726 l_document := l_document || td_number|| LPAD(to_char(l_gross_outstanding, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
2727 l_document := l_document || td_number || LPAD(to_char(l_gross_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
2728
2729 l_document := l_document || tr_end;
2730
2731 if (display_type = 'text/html') then
2732 l_document := l_document||table_end|| indent_end;
2733 l_document := l_document || '<br><br>';
2734 end if;
2735 WF_NOTIFICATION.WriteToClob(document,l_document); --Notification Esc
2736 document_type := display_type;
2737
2738
2739 EXCEPTION
2740 WHEN OTHERS THEN
2741 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GenerateManagerDunningList',
2742 document_id, l_debugInfo);
2743 raise;
2744 END GenerateManagerDunningList;
2745
2746 -------------------------------------------------------------------------
2747
2748 /*Written By: Amulya Mishra
2749 Purpose : Notification Escalation project.
2750 Procedure that prints the body of next manager's notification
2751 Body.
2752 */
2753
2754 PROCEDURE GenerateNextManagerDunningList(document_id IN VARCHAR2,
2755 display_type IN VARCHAR2,
2756 document IN OUT NOCOPY CLOB,
2757 document_type IN OUT NOCOPY VARCHAR2) IS
2758
2759 l_colon NUMBER;
2760 l_itemtype VARCHAR2(7);
2761 l_itemkey VARCHAR2(15);
2762 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
2763 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
2764 l_dateFormat VARCHAR2(30);
2765 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
2766 l_lineInfo VARCHAR2(2000);
2767 l_debugInfo VARCHAR2(1000);
2768 l_orgId NUMBER;
2769 l_min_bucket NUMBER;
2770 l_max_bucket NUMBER;
2771 l_min_bucket1 VARCHAR2(30);
2772 l_max_bucket1 VARCHAR2(30);
2773 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
2774 l_title AK_REGIONS_VL.name%TYPE;
2775
2776 l_document long ;
2777 l_document_max NUMBER := 25000;
2778
2779 l_detail_header_prompt VARCHAR2(2000);
2780 l_grace_days NUMBER;
2781
2782 l_employee_cursor EmployeeCursor;
2783 l_supervisor_id NUMBER;
2784
2785 l_supervisor_name PER_PEOPLE_F.FULL_NAME%TYPE;
2786 l_supervisor_display_name PER_PEOPLE_F.FULL_NAME%TYPE;
2787
2788 l_direct_manager_name PER_PEOPLE_F.FULL_NAME%TYPE; --Direct Report
2789 l_count NUMBER := 0; --Direct Report
2790
2791 l_employee_name PER_PEOPLE_F.FULL_NAME%TYPE;
2792 l_employee_display_name PER_PEOPLE_F.FULL_NAME%TYPE;
2793
2794 l_total_outstanding NUMBER;
2795 l_total_amt_outstanding NUMBER;
2796 l_total_dispute NUMBER;
2797 l_total_amt_dispute NUMBER;
2798 l_total_amount NUMBER;
2799
2800 l_gross_outstanding NUMBER := 0;
2801 l_gross_amount NUMBER := 0;
2802
2803
2804 l_final_manager_id NUMBER; --bug 3337443
2805 l_direct_report_name PER_PEOPLE_F.FULL_NAME%TYPE; --Bug 3337443
2806
2807 l_level NUMBER := 0; --Bug 3337388
2808 l_dunning_number NUMBER; --Bug 3337388
2809
2810 l_total_num_outstanding NUMBER := 0; --bug 3310243
2811
2812
2813
2814
2815 BEGIN
2816
2817 l_debugInfo := 'Decode document_id';
2818 l_colon := instrb(document_id, ':');
2819 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
2820 l_itemtype := substrb(document_id, 1, l_colon - 1);
2821 l_itemkey := substrb(document_id, l_colon + 1);
2822
2823 l_debugInfo := 'Get org_id';
2824 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
2825
2826 l_supervisor_id := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'AGING_MANAGER_ID');
2827 l_final_manager_id := l_supervisor_id; --Bug 3337443
2828
2829 -- MOAC UPTAKE --
2830 -- Have new call back function defined for Item to set org context.
2831 --
2832 -- l_debugInfo := 'Set Org context';
2833 -- fnd_client_info.set_org_context(l_orgId);
2834 ------------------------------------------------------------
2835 l_debugInfo := 'Get prompts';
2836 ------------------------------------------------------------
2837 AP_WEB_DISC_PKG.getPrompts(200,'AP_WEB_WF_CC_LINETABLE',l_title,l_prompts);
2838
2839
2840 l_debugInfo := 'Generate header';
2841 l_document := '';
2842
2843 l_grace_days := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'AGING_GRACE_DAYS');
2844
2845 l_billed_currency_code := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'CURRENCY');
2846 --------------------------------------------------------------
2847 l_debugInfo := 'Get WF BUCKET1..4 Item Attribute';
2848 --------------------------------------------------------------
2849 l_min_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET1');
2850 l_max_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET2');
2851
2852 l_min_bucket := to_number(l_min_bucket1);
2853 if(l_max_bucket1 = '+' ) then
2854 l_max_bucket := 1000000;
2855 else
2856 l_max_bucket := to_number(substr(l_max_bucket1,3));
2857 end if;
2858
2859 if (display_type = 'text/plain') then
2860 l_document := '';
2861 else -- html
2862
2863 FND_MESSAGE.SET_NAME('SQLAP','OIE_CC_SUMMARY_BY_EMPLOYEE'); --Direct Reports
2867
2864 l_detail_header_prompt := FND_MESSAGE.GET;
2865
2866 l_document := indent_start||table_title_start || l_detail_header_prompt || table_title_end;
2868 l_document := l_document||table_start;
2869 l_document := l_document||tr_start;
2870
2871 -- 'Employee'
2872 l_document := l_document || th_text || l_prompts(11) || td_end;
2873 -- 'Employee's Supervisor'
2874 l_document := l_document || th_text || l_prompts(16) || td_end;
2875 -- 'Direct Report'
2876 l_document := l_document || th_text || l_prompts(18) || td_end; --Direct Reports
2877 -- 'Number Outstanding'
2878 l_document := l_document || th_number || l_prompts(12) || td_end;
2879 -- 'Disputes Outstanding'
2880 l_document := l_document || th_number || l_prompts(13) || td_end;
2881 -- 'Aging Period Outstanding'
2882 l_document := l_document || th_number || l_prompts(14) || td_end;
2883 -- 'Total Outstanding'
2884 l_document := l_document || th_number || l_prompts(15) || td_end;
2885
2886 l_document := l_document || tr_end;
2887 end if;
2888
2889 --------------------------------------------------------------
2890 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
2891 --------------------------------------------------------------
2892 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
2893 --------------------------------------------------------------
2894 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
2895 --------------------------------------------------------------
2896 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
2897 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
2898
2899 l_debugInfo := 'Loop over all the Dunning Charges ';
2900
2901 l_dunning_number := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'DUNNING_NUMBER'); --Bug 3337388
2902
2903 -- IF (NOT GetHierarchialEmployeeCursor(l_supervisor_id,l_employee_cursor)) THEN
2904 -- 5049215 -- Added the dunning number to the function level as only records of this dunning level are processed later.
2905 IF (NOT GetHierarchialEmployeeCursor(l_supervisor_id,l_employee_cursor, l_dunning_number)) THEN
2906 NULL;
2907 END IF;
2908
2909 LOOP
2910 FETCH l_employee_cursor
2911 INTO l_employeeId,l_supervisor_id,l_level; --Direct Report --Bug 3337388
2912
2913
2914 EXIT WHEN l_employee_cursor%NOTFOUND;
2915
2916
2917 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberOutstanding(l_cardProgramId,l_employeeId,
2918 l_min_bucket, l_max_bucket , l_total_outstanding,
2919 l_total_amt_outstanding)) THEN
2920 NULL;
2921 END IF;
2922
2923 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(l_cardProgramId,l_employeeId,
2924 l_min_bucket, l_max_bucket ,
2925 l_grace_days,l_total_dispute,
2926 l_total_amt_dispute)) THEN
2927 NULL;
2928 END IF;
2929
2930 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalCreditCardAmount(l_cardProgramId,l_employeeId,
2931 l_total_amount ) ) THEN
2932 NULL;
2933 END IF;
2934
2935
2939 l_document := '';
2936 IF lengthb(l_document) >= l_document_max THEN
2937 -- Appends document to end of document (CLOB object)
2938 WF_NOTIFICATION.WriteToClob(document,l_document);
2940 END IF;
2941
2942 --Bug 3337388: If l_level is not as dunning number then dont disply thoese employee's records.
2943
2944 IF ((nvl(l_total_outstanding,0) <> 0 OR nvl(l_total_dispute,0) <> 0)
2945 AND l_count <= 100 AND l_level = l_dunning_number) THEN --Direct Report
2946
2947 l_total_amt_outstanding := nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
2948
2949 --Bug 3310243
2950 l_total_num_outstanding := nvl(l_total_outstanding,0) + nvl(l_total_dispute,0);
2951
2952 WF_DIRECTORY.GetUserName('PER', l_supervisor_id, l_supervisor_name, l_supervisor_display_name);
2953 WF_DIRECTORY.GetUserName('PER', l_employeeId, l_employee_name, l_employee_display_name);
2954 --Bug 3337443
2955 l_direct_report_name := GetDirectReport(l_employeeId , l_final_manager_id );
2956
2957 IF (display_type = 'text/plain') THEN
2958 --------------------------------------------
2959 -- 'Format Expense Line Info';
2960 --------------------------------------------
2961
2962 l_lineInfo := l_employee_display_name || ' ' || l_supervisor_display_name || ' ' || l_direct_report_name || ' '||
2963 l_total_num_outstanding || ' ' || l_total_dispute || ' ' || l_total_amt_outstanding ||' '|| l_total_amount ;--Direct Report
2964 -- set a new line
2965 l_document := l_document || ' ' || l_lineInfo;
2966 l_lineInfo := '';
2967 ELSE -- HTML type
2968
2969 l_document := l_document || tr_start;
2973 l_document := l_document || td_text ||l_direct_report_name ||td_end; --Direct Report
2970
2971 l_document := l_document || td_text ||l_employee_display_name ||td_end;
2972 l_document := l_document || td_text||l_supervisor_display_name ||td_end;
2974 l_document := l_document || td_number|| l_total_num_outstanding ||td_end;
2975 l_document := l_document || td_number|| l_total_dispute ||td_end;
2976 l_document := l_document || td_number|| LPAD(to_char(l_total_amt_outstanding, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
2977 l_document := l_document || td_number|| LPAD(to_char(l_total_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
2978
2979 END IF;
2980
2981 l_count := l_count + 1; --Direct Report
2982
2983 END IF;
2984
2985 IF (l_count > 100) THEN
2986 l_total_amt_outstanding := nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
2987 END IF;
2988
2989 /* Bug 3386832: The totalling should be done only for that level */
2990 IF l_level = l_dunning_number THEN
2991 l_gross_outstanding := l_gross_outstanding + nvl(l_total_amt_outstanding,0);
2992 l_gross_amount := l_gross_amount + nvl(l_total_amount,0);
2993 END IF;
2994
2995 END LOOP;
2996 close l_employee_cursor;
2997
2998 --Set Outstanding total amount attribute.
2999 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_ESC_AMOUNT', l_employee_name);
3000
3001 --------------------------------------------
3002 l_debugInfo := 'Generate Total Row';
3003 --------------------------------------------
3004
3005 l_document := l_document || tr_start;
3006 l_document := l_document ||
3007 '<td colspan=5 style="{font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:right}">' ||l_prompts(17) ||
3008 td_end; --Direct Report changed colspan to 5.
3009
3010 l_document := l_document || td_number|| LPAD(to_char(l_gross_outstanding, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
3011 l_document := l_document || td_number|| LPAD(to_char(l_gross_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || td_end;
3012
3013 l_document := l_document || tr_end;
3014
3015 if (display_type = 'text/html') then
3016 l_document := l_document||table_end|| indent_end;
3017 l_document := l_document || '<br><br>';
3018 end if;
3019 WF_NOTIFICATION.WriteToClob(document,l_document); --Notification Esc
3020 document_type := display_type;
3021
3022
3023 EXCEPTION
3024 WHEN OTHERS THEN
3025 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GenerateNextManagerDunningList',
3026 document_id, l_debugInfo);
3027 raise;
3028 END GenerateNextManagerDunningList;
3029
3030 ---------------------------------------------------------------
3031
3032 /*Written By: Amulya Mishra
3033 Purpose : Notification Escalation project.
3034 Procedure that gives the employees who are reporting
3035 to a manager with superisor id p_supervisor_id.
3036 */
3037
3038 FUNCTION GetEmployeeCursor(
3039 p_supervisor_id IN NUMBER,
3040 p_employee_cursor OUT NOCOPY EmployeeCursor)
3041 RETURN BOOLEAN IS
3042 -------------------------------------
3043
3044 BEGIN
3045
3046 IF p_supervisor_id IS NULL THEN
3047 return FALSE;
3048 END IF;
3049
3050 OPEN p_employee_cursor FOR
3051
3052 SELECT emp.employee_id,emp.full_name
3053 FROM per_employees_x emp
3054 WHERE emp.supervisor_id = p_supervisor_id
3055 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y';
3056
3057 return TRUE;
3058
3059 EXCEPTION
3060
3061 WHEN NO_DATA_FOUND THEN
3062 return FALSE;
3063 RAISE;
3064
3065 END GetEmployeeCursor;
3066
3067 -------------------------------------------------------------------
3068
3069 /*Written By: Amulya Mishra
3070 Purpose : Notification Escalation project.
3071 Gets the total outstanding amount of a bucket for employees
3072 directly reporting a manager.
3073 Sets the WF attribute MGR_ESC_AMOUNT to be shown in notification
3074 Subject.
3075 */
3076
3077 PROCEDURE GetTotalOutstandingAttribute(
3078 p_employee_id IN NUMBER,
3079 p_cardProgramId IN NUMBER,
3080 p_min_bucket IN NUMBER,
3081 p_max_bucket IN NUMBER,
3082 p_grace_days IN NUMBER,
3083 p_total_amount OUT NOCOPY NUMBER) IS
3084 -----------------------------------------------
3085 l_supervisor_id NUMBER;
3086 l_employee_cursor EmployeeCursor;
3087 l_employee_name per_employees_x.FULL_NAME%TYPE;
3088 l_total_amount NUMBER := 0;
3089 l_total_outstanding NUMBER;
3090 l_total_amt_outstanding NUMBER;
3091 l_total_dispute NUMBER;
3095
3092 l_total_amt_dispute NUMBER;
3093 l_employee_id NUMBER;
3094
3096 BEGIN
3097 AP_WEB_EXPENSE_WF.GetManager(p_employee_id, l_supervisor_id);
3098
3099 IF (NOT GetEmployeeCursor(l_supervisor_id,l_employee_cursor)) THEN
3100 NULL;
3101 END IF;
3102
3103 LOOP
3104 FETCH l_employee_cursor
3105 INTO l_employee_id,l_employee_name;
3106
3107 EXIT WHEN l_employee_cursor%NOTFOUND;
3108
3109 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalNumberOutstanding(p_cardProgramId,l_employee_id,
3110 p_min_bucket, p_max_bucket , l_total_outstanding,
3111 l_total_amt_outstanding)) THEN
3112 NULL;
3113 END IF;
3114
3115 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(p_cardProgramId,l_employee_id,
3116 p_min_bucket, p_max_bucket ,
3117 p_grace_days,l_total_dispute,
3118 l_total_amt_dispute)) THEN
3119 NULL;
3120 END IF;
3121
3122
3123 l_total_amount := l_total_amount + nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
3124
3125 END LOOP;
3126
3127 close l_employee_cursor;
3128
3129 p_total_amount := l_total_amount ;
3130
3131
3132 EXCEPTION
3133 WHEN OTHERS THEN
3134 p_total_amount :=0;
3135
3136 END GetTotalOutstandingAttribute;
3137
3138 -------------------------------------------------------------------------------
3139
3140 /*Written By: Amulya Mishra
3141 Purpose : Notification Escalation project.
3142 Procedure that gives the employees info ina heirarchy.
3143 Casey Brown(Emp Id 31)
3144 |
3145 |
3146 |
3147 Horton Ms Conner Esq(Emp Id 29)
3148 |
3149 |
3150 |
3151 Johnson Ms Alex(Emp Id 27)
3152 /\
3153 / \
3154 / \
3155 Green Terry(24) Kerry Jones(33)
3156 /\
3157 / \
3158 / \
3159 Jammie Frost(32) Elizabeth Smith(280)
3160
3161 For Emp Id 31 passed it gives info all the employees shown above
3162 with their corresponding immediate manager.
3163
3164 Assumption : If There exists a LOOP Oracle Connect BY gives Error.
3165
3166 */
3167 /*
3168
3169 Bug : 5049215
3170 Modified by : Sankar Balaji S
3171 Change: Added a new parameter p_level_id to the function that will scope the level that should be retrieved.
3172 If no level is specified, then all employees upto 6 levels gets retrieved.
3173 */
3174
3175 -------------------------------------------
3176 FUNCTION GetHierarchialEmployeeCursor(
3177 p_supervisor_id IN NUMBER,
3178 p_employee_cursor OUT NOCOPY EmployeeCursor,
3179 p_level_id IN NUMBER DEFAULT NULL)
3180 RETURN BOOLEAN IS
3181 -------------------------------------------
3182
3183 BEGIN
3184
3185 IF p_supervisor_id IS NULL THEN
3186 return FALSE;
3187 END IF;
3188
3189 /*
3190 OPEN p_employee_cursor FOR
3191
3192 SELECT employee_id,supervisor_id,level-1
3193 FROM per_employees_x emp
3194 WHERE NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
3195 CONNECT BY prior employee_id = supervisor_id
3196 and level < 6
3197 START WITH employee_id = p_supervisor_id order by 2;
3198 */
3199 IF ( p_level_id IS NOT NULL )
3200 THEN
3201 OPEN p_employee_cursor FOR
3202 SELECT P.PERSON_ID, H.SUPERVISOR_ID, H.LVL
3203 FROM PER_PEOPLE_F P,
3204 ( SELECT UNIQUE ass.PERSON_ID,ass.SUPERVISOR_ID, LEVEL -1 LVL
3205 FROM PER_ALL_ASSIGNMENTS_F ASS,
3206 PER_PERIODS_OF_SERVICE B2
3207 WHERE ASS.PRIMARY_FLAG = 'Y' AND ASS.ASSIGNMENT_TYPE = 'E' AND
3208 B2.period_of_service_id = Ass.period_of_service_id AND
3209 B2.DATE_START <= TRUNC(SYSDATE) AND
3210 greatest( NVL(B2.ACTUAL_TERMINATION_DATE, TRUNC(SYSDATE) ) ) between Ass.EFFECTIVE_START_DATE and Ass.EFFECTIVE_END_DATE
3211 AND LEVEL <= ( p_level_id + 1 )
3212 CONNECT BY
3213 PRIOR ass.PERSON_ID = SUPERVISOR_ID AND
3214 level <= (p_level_id + 1) and
3215 greatest( NVL(B2.ACTUAL_TERMINATION_DATE, TRUNC(SYSDATE) ) ) between Ass.EFFECTIVE_START_DATE and Ass.EFFECTIVE_END_DATE
3216 START WITH ass.pERSON_ID = p_supervisor_id
3217 ) H
3218 WHERE H.PERSON_ID = P.PERSON_ID AND
3219 TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND
3220 NOT Ap_Web_Db_Hr_Int_Pkg.ispersoncwk(P.PERSON_ID)='Y' AND
3221 P.EMPLOYEE_NUMBER IS NOT NULL
3222 ORDER BY 2;
3223
3224
3225 ELSE
3226
3227 OPEN p_employee_cursor FOR
3228 SELECT P.PERSON_ID, H.SUPERVISOR_ID, H.LVL
3229 FROM PER_PEOPLE_F P,
3230 ( SELECT UNIQUE ass.PERSON_ID,ass.SUPERVISOR_ID, LEVEL -1 LVL
3231 FROM PER_ALL_ASSIGNMENTS_F ASS,
3232 PER_PERIODS_OF_SERVICE B2
3233 WHERE ASS.PRIMARY_FLAG = 'Y' AND ASS.ASSIGNMENT_TYPE = 'E' AND
3234 B2.period_of_service_id = Ass.period_of_service_id AND
3235 B2.DATE_START <= TRUNC(SYSDATE) AND
3236 greatest( NVL(B2.ACTUAL_TERMINATION_DATE, TRUNC(SYSDATE) ) ) between Ass.EFFECTIVE_START_DATE and Ass.EFFECTIVE_END_DATE
3237 CONNECT BY
3238 PRIOR ass.PERSON_ID = SUPERVISOR_ID AND
3239 level < 6 and
3240 greatest( NVL(B2.ACTUAL_TERMINATION_DATE, TRUNC(SYSDATE) ) ) between Ass.EFFECTIVE_START_DATE and Ass.EFFECTIVE_END_DATE
3241 START WITH ass.pERSON_ID = p_supervisor_id
3242 ) H
3243 WHERE H.PERSON_ID = P.PERSON_ID AND
3244 TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND
3245 NOT Ap_Web_Db_Hr_Int_Pkg.ispersoncwk(P.PERSON_ID)='Y' AND
3246 P.EMPLOYEE_NUMBER IS NOT NULL
3247 ORDER BY 2;
3248
3249 END IF;
3250
3251 return TRUE;
3252
3253 EXCEPTION
3254
3255 WHEN OTHERS THEN
3256 BEGIN
3257 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'start GetHierarchialEmployeeCursor Exception p_supervisor_id='||to_char(p_supervisor_id));
3258
3259 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3260 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3261 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetHierarchialEmployeeCursor');
3262 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
3263 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',' ');
3264 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
3265 END;
3266 END GetHierarchialEmployeeCursor;
3267 -----------------------------------------------
3268
3269
3270 /*Written By: Amulya Mishra
3271 Purpose : Notification Escalation project.
3272 Gets the total outstanding amount of a bucket for employees
3273 in a hierarchial manner.
3274 Sets the WF attribute MGR_NEXT_ESC_AMOUNT to be shown in notification
3275 Subject.
3276 */
3277
3278 ------------------------------------------------
3279 PROCEDURE GetHierTotalOutstandingAttr(
3280 p_supervisor_id IN NUMBER,
3281 p_cardProgramId IN NUMBER,
3282 p_min_bucket IN NUMBER,
3283 p_max_bucket IN NUMBER,
3284 p_grace_days IN NUMBER,
3285 p_dunning_number IN NUMBER,
3286 p_total_amount OUT NOCOPY NUMBER) IS
3287 -----------------------------------------------
3288 l_supervisor_id NUMBER;
3289 l_employee_cursor EmployeeCursor;
3290 l_employee_name per_employees_x.full_name%TYPE;
3291 l_direct_manager_name per_employees_x.full_name%TYPE; --Direct Report
3292 l_total_amount NUMBER := 0;
3293 l_total_outstanding NUMBER;
3294 l_total_amt_outstanding NUMBER;
3295 l_total_dispute NUMBER;
3296 l_total_amt_dispute NUMBER;
3297 l_employee_id NUMBER;
3298 l_level NUMBER := 0; --Bug 3337388
3299
3300 BEGIN
3301
3302 -- IF (NOT GetHierarchialEmployeeCursor(l_supervisor_id,l_employee_cursor)) THEN
3303 -- 5049215 -- Added the dunning number to the function level as only records of this dunning level are processed later.
3304 IF (NOT GetHierarchialEmployeeCursor(l_supervisor_id,l_employee_cursor, p_dunning_number)) THEN
3305 NULL;
3306 END IF;
3307
3308 LOOP
3309 FETCH l_employee_cursor
3310 INTO l_employee_id,l_supervisor_id,l_level; --Direct Manager --Bug 3337388
3311
3312 EXIT WHEN l_employee_cursor%NOTFOUND;
3313
3314 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalNumberOutstanding(p_cardProgramId,l_employee_id,
3315 p_min_bucket, p_max_bucket , l_total_outstanding,
3316 l_total_amt_outstanding)) THEN
3317 NULL;
3318 END IF;
3319
3320 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(p_cardProgramId,l_employee_id,
3321 p_min_bucket, p_max_bucket ,
3322 p_grace_days,l_total_dispute,
3323 l_total_amt_dispute)) THEN
3324 NULL;
3325 END IF;
3326
3327
3328 IF l_level = p_dunning_number THEN --Bug 3849357
3329 l_total_amount := l_total_amount + nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute , 0);
3330 END IF;
3331
3332 END LOOP;
3333
3334
3335 close l_employee_cursor;
3336 p_total_amount := l_total_amount ;
3337
3338
3339 EXCEPTION
3340 WHEN OTHERS THEN
3341 p_total_amount := 0;
3342
3343 END GetHierTotalOutstandingAttr;
3344 --------------------------------------------------
3345
3346
3347
3348 /*Written By: Amulya Mishra
3349 Purpose : Notification Escalation project.
3350 Gets the value of WF attribute 'MGR_ALREADY_NOTIFIED'
3351 and sends notifications accordingly.
3352 */
3353
3354 ----------------------------------------------------------------------
3355 PROCEDURE IsNotificationRepeated(p_item_type IN VARCHAR2,
3356 p_item_key IN VARCHAR2,
3357 p_actid IN NUMBER,
3358 p_funmode IN VARCHAR2,
3359 p_result OUT NOCOPY VARCHAR2) IS
3360 ----------------------------------------------------------------------
3361
3362 BEGIN
3363 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'start IsNotificationRepeated');
3364 p_result := WF_ENGINE.GetItemAttrText(p_item_type,
3365 p_item_key,
3366 'MGR_ALREADY_NOTIFIED');
3367 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'end IsNotificationRepeated');
3368
3369 END IsNotificationRepeated;
3370
3371 --------------------------------------------------------------------
3372
3373 /*Written By: Amulya Mishra
3374 Purpose : Notification Escalation project.
3375 Gets the value of WF attribute 'SEND_NOTIFICATIONS_PARAM'
3376 and sends notifications accordingly.
3377 */
3378 ----------------------------------------------------------------------
3379 PROCEDURE SendNotifications(p_item_type IN VARCHAR2,
3380 p_item_key IN VARCHAR2,
3381 p_actid IN NUMBER,
3382 p_funmode IN VARCHAR2,
3383 p_result OUT NOCOPY VARCHAR2) IS
3384 ----------------------------------------------------------------------
3385
3386 BEGIN
3387 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'start SendNotifications');
3388 p_result := WF_ENGINE.GetItemAttrText(p_item_type,
3389 p_item_key,
3390 'SEND_NOTIFICATIONS_PARAM');
3391 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'end SendNotifications');
3392 END SendNotifications;
3393
3394 ---------------------------------------------------------------------
3395
3396
3397 /*Written By: Amulya Mishra
3398 Purpose : Notification Escalation project.
3399 Gets the value of WF attribute 'FIRST_DUNNING'
3400 and sends notifications accordingly.
3401 */
3402
3403 ----------------------------------------------------------------------
3404 PROCEDURE IsFirstDunning(p_item_type IN VARCHAR2,
3405 p_item_key IN VARCHAR2,
3406 p_actid IN NUMBER,
3407 p_funmode IN VARCHAR2,
3408 p_result OUT NOCOPY VARCHAR2) IS
3409 ----------------------------------------------------------------------
3410
3411 BEGIN
3412 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'start IsFirstDunning');
3413 p_result := WF_ENGINE.GetItemAttrText(p_item_type,
3414 p_item_key,
3415 'FIRST_DUNNING');
3416 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'end IsFirstDunning');
3417
3418 END IsFirstDunning;
3419
3420 --------------------------------------------------------------------
3421
3422 /*Written By: Amulya Mishra
3423 Purpose : Notification Escalation project.
3424 Gets the value of WF attribute 'FIRST_DUNNING'
3425 and sends notifications accordingly.
3426 */
3427
3428 ----------------------------------------------------------------------
3429 FUNCTION GetDirectReport(
3430 p_employee_id IN NUMBER,
3431 p_final_manager_id IN NUMBER) RETURN VARCHAR2 IS
3432
3433 l_temp_manager_id NUMBER;
3434 l_employee_id NUMBER := p_employee_id;
3435 l_manager_name wf_users.display_name%type;
3436 l_direct_report_name wf_users.display_name%type;
3437
3438 ----------------------------------------------------------------------
3439 BEGIN
3440 IF p_employee_id IS NULL OR p_final_manager_id IS NULL THEN
3441 RETURN null;
3442 END IF;
3443
3444 WHILE TRUE LOOP
3445
3446 AP_WEB_EXPENSE_WF.GetManager(l_employee_id, l_temp_manager_id );
3447 IF l_temp_manager_id = p_final_manager_id THEN
3448 EXIT;
3449 ELSE
3450 l_employee_id := l_temp_manager_id ;
3451 END IF;
3452
3453 END LOOP;
3454
3455 WF_DIRECTORY.GetUserName('PER', l_employee_id, l_manager_name, l_direct_report_name);
3456 return l_direct_report_name;
3457
3458 EXCEPTION
3459 WHEN OTHERS THEN
3460 return null;
3461
3462 END GetDirectReport;
3463 --------------------------------------------------------------------
3464
3465 PROCEDURE GetWebNextEscManager(p_itemType IN VARCHAR2,
3466 p_itemKey IN VARCHAR2) IS
3467
3468 l_itemtype VARCHAR2(7);
3469 l_itemkey VARCHAR2(15);
3470 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
3471 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
3472 l_dateFormat VARCHAR2(30);
3473 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
3474 l_lineInfo VARCHAR2(2000);
3475 l_debugInfo VARCHAR2(1000);
3476 l_orgId NUMBER;
3477 l_min_bucket NUMBER;
3478 l_max_bucket NUMBER;
3479 l_min_bucket1 VARCHAR2(30);
3480 l_max_bucket1 VARCHAR2(30);
3481 l_grace_days NUMBER;
3482
3483 l_employee_cursor EmployeeCursor;
3484 l_supervisor_id NUMBER;
3485
3486 l_supervisor_name PER_PEOPLE_F.FULL_NAME%TYPE;
3487 l_supervisor_display_name PER_PEOPLE_F.FULL_NAME%TYPE;
3488
3489 l_direct_manager_name PER_PEOPLE_F.FULL_NAME%TYPE; --Direct Report
3490
3491 l_employee_name PER_PEOPLE_F.FULL_NAME%TYPE;
3492 l_employee_display_name PER_PEOPLE_F.FULL_NAME%TYPE;
3493
3494 l_total_outstanding NUMBER;
3495 l_total_amt_outstanding NUMBER;
3499
3496 l_total_dispute NUMBER;
3497 l_total_amt_dispute NUMBER;
3498 l_total_amount NUMBER;
3500 l_gross_outstanding NUMBER := 0;
3501 l_gross_amount NUMBER := 0;
3502
3503
3504 l_final_manager_id NUMBER;
3505 l_direct_report_name PER_PEOPLE_F.FULL_NAME%TYPE;
3506
3507 l_level NUMBER := 0;
3508 l_dunning_number NUMBER;
3509
3510 l_total_num_outstanding NUMBER := 0;
3511
3512
3513
3514
3515 BEGIN
3516
3517 l_debugInfo := 'Decode document_id';
3518
3519 l_itemType := p_itemType;
3520 l_itemKey := p_itemKey;
3521
3522 l_debugInfo := 'Get org_id';
3523 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
3524
3525 l_supervisor_id := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'AGING_MANAGER_ID');
3526 l_final_manager_id := l_supervisor_id;
3527
3528 -- MOAC UPTAKE --
3529 -- Have new call back function defined for Item to set org context.
3530 --
3531 -- l_debugInfo := 'Set Org context';
3532 -- fnd_client_info.set_org_context(l_orgId);
3533
3534 l_grace_days := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'AGING_GRACE_DAYS');
3535
3536 l_billed_currency_code := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'CURRENCY');
3537 --------------------------------------------------------------
3538 l_debugInfo := 'Get WF BUCKET1..4 Item Attribute';
3539 --------------------------------------------------------------
3540 l_min_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET1');
3541 l_max_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET2');
3542
3543 l_min_bucket := to_number(l_min_bucket1);
3544 if(l_max_bucket1 = '+' ) then
3545 l_max_bucket := 1000000;
3546 else
3547 l_max_bucket := to_number(substr(l_max_bucket1,3));
3548 end if;
3549
3550
3551 --------------------------------------------------------------
3552 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
3553 --------------------------------------------------------------
3554 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
3555 --------------------------------------------------------------
3556 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
3557 --------------------------------------------------------------
3558 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
3559 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
3560
3561 l_debugInfo := 'Loop over all the Dunning Charges ';
3562
3563 l_dunning_number := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'DUNNING_NUMBER');
3564
3565 DELETE FROM ap_ccard_esc_next_gt;
3566
3567 -- IF (NOT GetHierarchialEmployeeCursor(l_supervisor_id,l_employee_cursor)) THEN
3568 -- 5049215 -- Added the dunning number to the function level as only records of this dunning level are processed later.
3569 IF (NOT GetHierarchialEmployeeCursor(l_supervisor_id,l_employee_cursor, l_dunning_number)) THEN
3570 NULL;
3571 END IF;
3572
3573 LOOP
3574 FETCH l_employee_cursor
3575 INTO l_employeeId,l_supervisor_id,l_level;
3576
3577 EXIT WHEN l_employee_cursor%NOTFOUND;
3578
3579 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberOutstanding(l_cardProgramId,l_employeeId,
3580 l_min_bucket, l_max_bucket , l_total_outstanding,
3581 l_total_amt_outstanding)) THEN
3582 NULL;
3583 END IF;
3584 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(l_cardProgramId,l_employeeId,
3585 l_min_bucket, l_max_bucket ,
3586 l_grace_days,l_total_dispute,
3587 l_total_amt_dispute)) THEN
3588 NULL;
3589 END IF;
3590
3591 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalCreditCardAmount(l_cardProgramId,l_employeeId,
3592 l_total_amount ) ) THEN
3593 NULL;
3594 END IF;
3595
3596
3597 IF ((nvl(l_total_outstanding,0) <> 0 OR nvl(l_total_dispute,0) <> 0)
3598 AND l_level = l_dunning_number) THEN --Direct Report
3599
3603
3600 l_total_amt_outstanding := nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
3601
3602 l_total_num_outstanding := nvl(l_total_outstanding,0) + nvl(l_total_dispute,0);
3604 WF_DIRECTORY.GetUserName('PER', l_supervisor_id, l_supervisor_name, l_supervisor_display_name);
3605 WF_DIRECTORY.GetUserName('PER', l_employeeId, l_employee_name, l_employee_display_name);
3606 l_direct_report_name := GetDirectReport(l_employeeId , l_final_manager_id );
3607
3608
3609 IF l_level = l_dunning_number THEN
3610 l_gross_outstanding := l_gross_outstanding + nvl(l_total_amt_outstanding,0);
3611 l_gross_amount := l_gross_amount + nvl(l_total_amount,0);
3612 END IF;
3613
3614 INSERT INTO ap_ccard_esc_next_gt
3615 (aging_manager_id,
3616 employee_display_name,
3617 supervisor_display_name,
3618 direct_report_name,
3619 total_num_outstanding,
3620 total_dispute,
3621 total_amt_outstanding,
3622 total_amount )
3623 VALUES (l_final_manager_id,
3624 l_employee_display_name,
3625 l_supervisor_display_name,
3626 l_direct_report_name,
3627 l_total_num_outstanding,
3628 l_total_dispute,
3629 l_total_amt_outstanding,
3630 l_total_amount
3631 );
3632 END IF;
3633
3634 END LOOP;
3635 close l_employee_cursor;
3636 commit;
3637 --Set Outstanding total amount attribute.
3638 WF_ENGINE.SetItemAttrText(l_itemType, l_itemKey, 'MGR_ESC_AMOUNT', l_employee_name);
3639
3640 EXCEPTION
3641 WHEN OTHERS THEN
3642 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GetWebNextEscManager',
3643 l_itemKey, l_debugInfo);
3644 raise;
3645 END GetWebNextEscManager;
3646
3647
3648 --------------------------------------------------------------------
3649
3650 PROCEDURE GetWebEscManager(p_itemType IN VARCHAR2,
3651 p_itemKey IN VARCHAR2) IS
3652
3653 l_colon NUMBER;
3654 l_itemtype VARCHAR2(7);
3655 l_itemkey VARCHAR2(15);
3656 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
3657 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
3658 l_dateFormat VARCHAR2(30);
3659 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
3660 l_lineInfo VARCHAR2(2000);
3661 l_debugInfo VARCHAR2(1000);
3662 l_orgId NUMBER;
3663 l_min_bucket NUMBER;
3664 l_max_bucket NUMBER;
3665 l_min_bucket1 VARCHAR2(30);
3666 l_max_bucket1 VARCHAR2(30);
3667 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
3668 l_title AK_REGIONS_VL.name%TYPE;
3669
3670 l_document long ;
3671 l_document_max NUMBER := 25000;
3672
3673 l_detail_header_prompt VARCHAR2(2000);
3674 l_grace_days NUMBER;
3675
3676 l_employee_cursor EmployeeCursor;
3677 l_supervisor_id NUMBER;
3678
3679 l_employee_name PER_PEOPLE_F.FULL_NAME%TYPE;
3680
3681 l_total_outstanding NUMBER;
3682 l_total_amt_outstanding NUMBER;
3683 l_total_dispute NUMBER;
3684 l_total_amt_dispute NUMBER;
3685 l_total_amount NUMBER;
3686
3687 l_total_num_outstanding NUMBER := 0; --Bug 3310243
3688
3689 l_gross_outstanding NUMBER := 0;
3690 l_gross_amount NUMBER := 0;
3691
3692 l_count NUMBER := 0; --Direct Report
3693
3694 BEGIN
3695
3696
3697
3698 l_itemType := p_itemType;
3699 l_itemKey := p_itemKey;
3700
3701 l_debugInfo := 'Get org_id';
3702 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
3703
3704 -- MOAC UPTAKE --
3705 -- Have new call back function defined for Item to set org context.
3706 --
3707 -- l_debugInfo := 'Set Org context';
3708 -- fnd_client_info.set_org_context(l_orgId);
3709
3710 l_grace_days := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'AGING_GRACE_DAYS');
3711
3712
3713 l_billed_currency_code := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'CURRENCY');
3714 --------------------------------------------------------------
3715 l_debugInfo := 'Get WF BUCKET1..4 Item Attribute';
3716 --------------------------------------------------------------
3717 l_min_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET1');
3718 l_max_bucket1 := WF_ENGINE.GetItemAttrText(l_itemType, l_itemKey, 'BUCKET2');
3719
3720 l_min_bucket := to_number(l_min_bucket1);
3721 if(l_max_bucket1 = '+' ) then
3722 l_max_bucket := 1000000;
3723 else
3724 l_max_bucket := to_number(substr(l_max_bucket1,3));
3725 end if;
3726
3727 --------------------------------------------------------------
3728 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
3732 --------------------------------------------------------------
3729 --------------------------------------------------------------
3730 l_cardProgramId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
3731
3733 l_debugInfo := 'Get WF EMPLOYEE_ID Item Attribute';
3734 --------------------------------------------------------------
3735 l_employeeId := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'EMPLOYEE_ID');
3736 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
3737
3738 l_debugInfo := 'Loop over all the Dunning Charges ';
3739
3740 AP_WEB_EXPENSE_WF.GetManager(l_employeeId, l_supervisor_id);
3741
3742 DELETE FROM ap_ccard_esc_next_gt;
3743
3744 IF (NOT GetEmployeeCursor(l_supervisor_id,l_employee_cursor)) THEN
3745 NULL;
3746 END IF;
3747
3748 LOOP
3749 FETCH l_employee_cursor
3750 INTO l_employeeId,l_employee_name;
3751
3752
3753 EXIT WHEN l_employee_cursor%NOTFOUND;
3754
3755 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberOutstanding(l_cardProgramId,l_employeeId,
3756 l_min_bucket, l_max_bucket , l_total_outstanding,
3757 l_total_amt_outstanding)) THEN
3758 NULL;
3759 END IF;
3760
3761
3762
3763 IF (AP_WEB_DB_CCARD_PKG.GetTotalNumberDispute(l_cardProgramId,l_employeeId,
3764 l_min_bucket, l_max_bucket ,
3765 l_grace_days,l_total_dispute,
3766 l_total_amt_dispute)) THEN
3767 NULL;
3768 END IF;
3769
3770
3771
3775 END IF;
3772 IF (NOT AP_WEB_DB_CCARD_PKG.GetTotalCreditCardAmount(l_cardProgramId,l_employeeId,
3773 l_total_amount ) ) THEN
3774 NULL;
3776
3777
3778 IF ((nvl(l_total_outstanding,0) <> 0 OR nvl(l_total_dispute,0) <> 0) ) THEN --Direct Report
3779
3780 l_total_amt_outstanding := nvl(l_total_amt_outstanding,0) + nvl(l_total_amt_dispute,0);
3781
3782 l_total_num_outstanding := nvl(l_total_outstanding,0) + nvl(l_total_dispute,0);
3783
3784 INSERT INTO ap_ccard_esc_next_gt
3785 (
3786 aging_manager_id,
3787 employee_display_name,
3788 total_num_outstanding,
3789 total_dispute,
3790 total_amt_outstanding,
3791 total_amount
3792 )
3793 VALUES (
3794 l_supervisor_id,
3795 l_employee_name,
3796 l_total_num_outstanding,
3797 l_total_dispute,
3798 l_total_amt_outstanding,
3799 l_total_amount
3800 );
3801
3802 END IF;
3803
3804 END LOOP;
3805 close l_employee_cursor;
3806
3807 commit;
3808
3809 EXCEPTION
3810 WHEN OTHERS THEN
3811 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GetWebEscManager',
3812 null, l_debugInfo);
3813 raise;
3814 END GetWebEscManager;
3815
3816 ---------------------------------------------------------------
3817 PROCEDURE SendDeactivatedNotif(p_employeeId IN NUMBER,
3818 p_cardProgramId IN NUMBER,
3819 p_endDate IN VARCHAR2)
3820 IS
3821 l_itemType VARCHAR2(100) := 'APCCARD';
3822 l_itemKey VARCHAR2(100);
3823 l_employeeName wf_users.name%type;
3824 l_employeeID NUMBER;
3825 l_employeeDisplayName wf_users.display_name%type;
3826 l_debugInfo VARCHAR2(200);
3827 l_cardProgramName AP_WEB_DB_CCARD_PKG.cardProgs_cardProgName;
3828 l_orgId number;
3829 BEGIN
3830
3831 WF_DIRECTORY.GetUserName('PER',
3832 p_employeeId,
3833 l_employeeName,
3834 l_employeeDisplayName);
3835
3836 IF l_employeeName IS NULL THEN
3837 RETURN;
3838 END IF;
3839
3840 ---------------------------------------------------------
3841 l_debugInfo := ' Generate new key';
3842 ---------------------------------------------------------
3843 l_itemKey := GetNextCardNotificationID;
3844
3845 --------------------------------------------------
3846 l_debugInfo := 'Calling WorkFlow Create Process';
3847 --------------------------------------------------
3848 WF_ENGINE.CreateProcess(l_itemType, l_itemKey, 'DEACTIVATED_TRXNS');
3849
3850 ----------------------------------------------------------
3851 l_debugInfo := 'Set DATE2 Item Attribute';
3852 ----------------------------------------------------------
3853 --
3854 -- Set the date in canonical format so that the web pages
3855 -- can convert this into appropriate user preference format.
3856 --
3857 WF_ENGINE.SetItemAttrText( l_itemType,
3858 l_itemKey,
3859 'DATE2',
3860 FND_DATE.date_to_canonical(to_date(p_endDate,
3861 nvl(icx_sec.g_date_format,
3862 icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT')
3863 )
3864 )
3865 )
3866 );
3867
3868 ----------------------------------------------------------
3869 l_debugInfo := 'Set EMPLOYEE_NAME Item Attribute';
3870 ----------------------------------------------------------
3871 WF_ENGINE.SetItemAttrText( l_itemType,
3872 l_itemKey,
3873 'EMPLOYEE_NAME',
3874 l_employeeName);
3875
3876 ----------------------------------------------------------
3877 l_debugInfo := 'Set EMPLOYEE_NAME Item Attribute';
3878 ----------------------------------------------------------
3879 WF_ENGINE.SetItemAttrText( l_itemType,
3880 l_itemKey,
3881 'EMP_DISPLAY_NAME',
3882 l_employeeDisplayName);
3883
3884 ------------------------------------------------------
3885 l_debugInfo := 'Set WF CREDIT_CARD_COMPANY Item Attribute';
3886 ------------------------------------------------------
3887 IF (NOT AP_WEB_DB_CCARD_PKG.GetCardProgramName(p_cardProgramID,
3888 l_cardProgramName ) ) THEN
3889 NULL;
3890 END IF;
3891
3892 WF_ENGINE.SetItemAttrText( l_itemType,
3893 l_itemKey,
3894 'CREDIT_CARD_COMPANY',
3895 l_cardProgramName);
3896
3897 WF_ENGINE.StartProcess(l_itemType, l_itemKey);
3898
3899 END SendDeactivatedNotif;
3900 ---------------------------------------------------------------
3901
3902
3903
3904 ----------------------------------------------------------------------
3905 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
3906 p_item_key IN VARCHAR2,
3907 p_actid IN NUMBER,
3908 p_funmode IN VARCHAR2,
3909 p_result OUT NOCOPY VARCHAR2) IS
3910 ----------------------------------------------------------------------
3911 l_debug_info VARCHAR2(200);
3912
3913 l_org_id number;
3914 l_expense_report_id number;
3915 l_card_prog_id Number;
3916
3917 BEGIN
3918
3919 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'start CallbackFunction');
3920
3921 begin
3922 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3923 p_item_key,
3924 'ORG_ID');
3925 exception
3926 when others then
3927 -- This wf is called by multiple conc programs and some set
3928 -- CARD_PROG_ID and others set CARD_PROGRAM_ID
3929 -- p_s_item_key is a sequence value and not report_header_id
3930 -- hence remove the call GetOrgIdByReportHeaderId and instead
3931 -- get the org_id from card program(ap_card_programs).
3932 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
3933 begin
3937
3934 l_card_prog_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3935 p_item_key,
3936 'CARD_PROG_ID');
3938 exception
3942 if l_card_prog_id is null then
3939 when others then
3940 null;
3941 end;
3943 begin
3944 l_card_prog_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3945 p_item_key,
3946 'CARD_PROGRAM_ID');
3947 exception
3948 when others then
3949 null;
3950 end;
3951 end if;
3952
3953 if l_card_prog_id is not null then
3954 begin
3955 select org_id into l_org_id
3956 from ap_card_programs_all
3957 where card_program_id = l_card_prog_id;
3958 exception
3959 when others then
3960 l_org_id := NULL;
3961 end;
3962 else
3963 l_org_id := NULL;
3964 end if;
3965
3966 -- ORG_ID item attribute doesn't exist, need to add it
3967 wf_engine.AddItemAttr(p_item_type, p_item_key, 'ORG_ID');
3968 WF_ENGINE.SetItemAttrNumber(p_item_type,
3969 p_item_key,
3970 'ORG_ID',
3971 l_org_id);
3972 else
3973 raise;
3974 end if;
3975
3976 end;
3977
3978 /*
3979 if ( p_funmode = 'RUN' ) then
3980 --<your RUN executable statements>
3981
3982 p_result := 'TRUE';
3983
3984 return;
3985 end if;
3986 */
3987
3988 if ( p_funmode = 'SET_CTX' ) then
3989 --<your executable statements for establishing context information>
3990
3991 if (l_org_id is not null) then
3992 mo_global.set_policy_context(p_access_mode => 'S',
3993 p_org_id => l_org_id);
3994 end if;
3995
3996 p_result := 'TRUE';
3997
3998 return;
3999 end if;
4000
4001 if ( p_funmode = 'TEST_CTX' and l_org_id is not null) then
4002 --<your executable statements for testing the validity of the current context information>
4003
4004 IF ((nvl(mo_global.get_access_mode, 'NULL') <> 'S') OR
4005 (nvl(mo_global.get_current_org_id, -99) <> nvl(l_org_id, -99)) ) THEN
4006 p_result := 'FALSE';
4007 ELSE
4008 p_result := 'TRUE';
4009 END IF;
4010
4011 return;
4012 end if;
4013
4014 /*
4015 if ( p_funmode = '<other command>' ) then
4016 p_result := ' ';
4017
4018 return;
4019 end if;
4020 */
4021
4022 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_CREDIT_CARD_WF', 'end CallbackFunction');
4023
4024 EXCEPTION
4025 WHEN OTHERS THEN
4026 Wf_Core.Context('AP_WEB_CREDIT_CARD_WF', 'CallbackFunction',
4027 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
4028 raise;
4029 END CallbackFunction;
4030
4031
4032 END AP_WEB_CREDIT_CARD_WF;