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