[Home] [Help]
PACKAGE BODY: APPS.AP_APXCCOUT_XMLP_PKG
Source
1 PACKAGE BODY AP_APXCCOUT_XMLP_PKG AS
2 /* $Header: APXCCOUT_SUMMARYB.pls 120.0 2007/12/28 11:10:52 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 INIT_FAILURE EXCEPTION;
7 BEGIN
8 IF ((P_BUCKET1 > P_BUCKET2) OR (P_BUCKET1 > P_BUCKET3)) THEN
9 NULL;
10 RAISE INIT_FAILURE;
11 ELSIF ((P_BUCKET2 > P_BUCKET3)) THEN
12 NULL;
13 RAISE INIT_FAILURE;
14 END IF;
15 IF (P_OPERATION_TYPE = 'CC_DETAIL_REPORT') THEN
16
17 NULL;
18 ELSIF (P_OPERATION_TYPE = 'CC_SUMMARY_REPORT') THEN
19
20 NULL;
21 ELSIF (P_OPERATION_TYPE = 'CC_AGING_REPORT') THEN
22
23 NULL;
24 ELSIF (P_OPERATION_TYPE = 'CC_INACT_EMPL_REPORT') THEN
25
26 NULL;
27 END IF;
28 IF (P_BUCKET1 IS NOT NULL) THEN
29 FND_MESSAGE.SET_NAME('SQLAP'
30 ,'OIE_CC_BUCKET1_NAME');
31 FND_MESSAGE.SET_TOKEN('BUCKET1'
32 ,P_BUCKET1);
33 CP_BUCKET1_NAME := FND_MESSAGE.GET;
34 NULL;
35 END IF;
36 IF (P_BUCKET2 IS NOT NULL) THEN
37 FND_MESSAGE.SET_NAME('SQLAP'
38 ,'OIE_CC_BUCKET2_NAME');
39 FND_MESSAGE.SET_TOKEN('BUCKET1'
40 ,P_BUCKET1 + 1);
41 FND_MESSAGE.SET_TOKEN('BUCKET2'
42 ,P_BUCKET2);
43 CP_BUCKET2_NAME := FND_MESSAGE.GET;
44 NULL;
45 END IF;
46 IF (P_BUCKET3 IS NOT NULL) THEN
47 FND_MESSAGE.SET_NAME('SQLAP'
48 ,'OIE_CC_BUCKET3_NAME');
49 FND_MESSAGE.SET_TOKEN('BUCKET2'
50 ,P_BUCKET2 + 1);
51 FND_MESSAGE.SET_TOKEN('BUCKET3'
52 ,P_BUCKET3);
53 CP_BUCKET3_NAME := FND_MESSAGE.GET;
54 NULL;
55 FND_MESSAGE.SET_NAME('SQLAP'
56 ,'OIE_CC_BUCKET4_NAME');
57 FND_MESSAGE.SET_TOKEN('BUCKET4'
58 ,P_BUCKET3 + 1);
59 CP_BUCKET4_NAME := FND_MESSAGE.GET;
60 NULL;
61 END IF;
62 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
63
64 IF (GETCOMPANYNAME <> TRUE) THEN
65 RAISE INIT_FAILURE;
66 END IF;
67
68 IF (GETBASECURRDATA <> TRUE) THEN
69 RAISE INIT_FAILURE;
70 END IF;
71
72 IF (GETNLSSTRINGS <> TRUE) THEN
73 RAISE INIT_FAILURE;
74 END IF;
75
76 IF (GETCARDPROGRAMNAME <> TRUE) THEN
77 RAISE INIT_FAILURE;
78 END IF;
79 IF (P_MIN_AMOUNT IS NOT NULL) THEN
80 LP_MIN_AMT_WHERE := 'and cct1.billed_amount >= ' || P_MIN_AMOUNT || ' ';
81 ELSE
82 LP_MIN_AMT_WHERE := ' ';
83 END IF;
84
85 IF (P_OPERATION_TYPE in ('CC_DETAIL_REPORT','CC_SUMMARY_REPORT','CC_INACT_EMPL_REPORT')) THEN
86 IF (P_STATUS = 'C') THEN
87 LP_SELECT_EMP := 'emp.employee_id ';
88 LP_SELECT_EMP_NAME := 'emp.full_name ';
89 LP_SELECT_SUP := 'emp.supervisor_id ';
90 LP_GROUP_BY := ' group by emp.employee_id, emp.supervisor_id';
91 ELSE
92 LP_SELECT_EMP := 'perf.person_id ';
93 LP_SELECT_EMP_NAME := 'perf.full_name ';
94 LP_SELECT_SUP := 'pera.supervisor_id ';
95 LP_GROUP_BY := ' group by perf.person_id, pera.supervisor_id';
96 END IF;
97 IF (P_STATUS in ('C','B')) THEN
98 LP_EMPLOYEE_STATUS := ' ''Active'' ';
99 ELSE
100 LP_EMPLOYEE_STATUS := ' ''Inactive'' ';
101 END IF;
102 IF (P_STATUS = 'C') THEN
103 LP_EMPLOYEE_FROM := ', per_employees_current_x emp';
104 ELSIF (P_STATUS = 'B') THEN
105 LP_EMPLOYEE_FROM := ', per_people_f perf
106 , per_assignments_f pera';
107 ELSIF (P_STATUS = 'T') THEN
108 LP_EMPLOYEE_FROM := ', per_people_f perf
109 , per_assignments_f pera
110 , per_assignment_status_types peras';
111 ELSE
112 LP_EMPLOYEE_FROM := ' ';
113 END IF;
114 IF (P_STATUS = 'C') THEN
115 LP_EMPLOYEE_WHERE := ' and ac.employee_id = emp.employee_id';
116 ELSIF (P_STATUS = 'T') THEN
117 LP_EMPLOYEE_WHERE := ' and ac.employee_id = perf.person_id
118 and perf.business_group_id+0 = (select business_group_id from financials_system_parameters fsp
119 where perf.business_group_id = fsp.business_group_id)
120 and perf.person_id = pera.person_id
121 and pera.assignment_status_type_id = peras.assignment_status_type_id
122 and peras.per_system_status in (''TERM_ASSIGN'', ''SUSP_ASSIGN'')
123 and pera.primary_flag = ''Y''
124 and pera.assignment_type in (''E'',''C'')
125 and perf.employee_number is not null
126 and trunc(sysdate) between perf.effective_start_date and perf.effective_end_date
127 and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date';
128 ELSIF (P_STATUS = 'B') THEN
129 LP_EMPLOYEE_WHERE := 'and ac.employee_id = perf.person_id
130 and perf.person_id = pera.person_id
131 and pera.assignment_type = ''E''
132 and pera.primary_flag = ''Y''
133 and perf.employee_number is not null
134 and trunc(sysdate) between perf.effective_start_date and perf.effective_end_date
135 and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
136 and trunc(sysdate) < perf.effective_end_date
137 and trunc(sysdate) < pera.effective_end_date';
138 ELSE
139 LP_EMPLOYEE_WHERE := ' ';
140 END IF;
141 END IF;
142 IF (P_EMPLOYEE IS NOT NULL) THEN
143 IF (P_INCLUDE_DIRECTS = 'Y') THEN
144 LP_EMP_MGR := ' and (ac.employee_id in (select distinct employee_id
145 from per_employees_x emp1 -- Bug 3176205: Changed HR view to include all employees(inactive also) and exclude contingent workers.
146 where emp1.supervisor_id = ' || P_EMPLOYEE || ')
147 OR ac.employee_id = ' || P_EMPLOYEE || ') ';
148 LP_EMP_MGR1 := ' and (ac.employee_id in (select distinct person_id
149 from per_assignments_f pera1
150 where pera1.supervisor_id = ' || P_EMPLOYEE || ' and pera1.assignment_type in (''E'',''C'') )
151 OR ac.employee_id = ' || P_EMPLOYEE || ') ';
152 ELSIF (P_INCLUDE_DIRECTS = 'N') THEN
153 LP_EMP_MGR := ' and ac.employee_id = ' || P_EMPLOYEE || ' ';
154 LP_EMP_MGR1 := ' and ac.employee_id = ' || P_EMPLOYEE || ' ';
155 END IF;
156 ELSE
157 LP_EMP_MGR := ' ';
158 LP_EMP_MGR1 := ' ';
159 END IF;
160 IF (P_OPERATION_TYPE = 'CC_INACT_EMPL_REPORT') THEN
161 LP_INACTIVE_WHERE := 'and inactive_emp_wf_item_key is null';
162 ELSE
163 LP_INACTIVE_WHERE := ' ';
164 END IF;
165
166 RETURN (TRUE);
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 NULL;
170 WHEN OTHERS THEN
171 RAISE_APPLICATION_ERROR(-20101,null);
172 END;
173 RETURN NULL;
174 END BEFOREREPORT;
175
176 FUNCTION AFTERREPORT RETURN BOOLEAN IS
177 V_MIN_BUCKET NUMBER;
178 V_MAX_BUCKET NUMBER;
179 V_DUNNING_NUMBER NUMBER;
180 V_ERRNUM NUMBER;
181 V_ERRMSG VARCHAR2(300);
182 V_SEND_NOTIFICATIONS VARCHAR2(20);
183 V_ESC_LEVEL NUMBER;
184 V_GRACE_DAYS NUMBER;
185 BEGIN
186
187 IF (P_SEND_NOTIFICATIONS = 'Y' AND P_OPERATION_TYPE = 'CC_DETAIL_REPORT' AND P_EMPLOYEE IS NULL AND P_STATUS <> 'T') THEN
188 SENDUNSUBMITTED;
189 SENDMGRUNAPPROVED;
190 SENDDISPUTED;
191 END IF;
192 IF (P_SEND_NOTIFICATIONS <> 'N' AND P_OPERATION_TYPE = 'CC_AGING_REPORT') THEN
193 V_SEND_NOTIFICATIONS := P_SEND_NOTIFICATIONS;
194 V_ESC_LEVEL := P_ESC_LEVEL;
195 V_GRACE_DAYS := P_GRACE_DAYS;
196
197 IF (P_BUCKET1 IS NOT NULL AND P_EMPLOYEE IS NULL) THEN
198 V_MIN_BUCKET := 0;
199 V_MAX_BUCKET := P_BUCKET1;
200 V_DUNNING_NUMBER := 1;
201 SEND1DUNNINGNOTIFICATIONS(V_MIN_BUCKET
202 ,V_MAX_BUCKET
203 ,V_DUNNING_NUMBER
204 ,V_SEND_NOTIFICATIONS
205 ,V_ESC_LEVEL
206 ,V_GRACE_DAYS);
207
208 END IF;
209 IF (P_BUCKET2 IS NOT NULL AND P_EMPLOYEE IS NULL) THEN
210
211 V_MIN_BUCKET := P_BUCKET1 + 1;
212 V_MAX_BUCKET := P_BUCKET2;
213 V_DUNNING_NUMBER := 2;
214 SEND1DUNNINGNOTIFICATIONS(V_MIN_BUCKET
215 ,V_MAX_BUCKET
216 ,V_DUNNING_NUMBER
217 ,V_SEND_NOTIFICATIONS
218 ,V_ESC_LEVEL
219 ,V_GRACE_DAYS);
220
221 END IF;
222 IF (P_BUCKET3 IS NOT NULL AND P_EMPLOYEE IS NULL) THEN
223
224 V_MIN_BUCKET := P_BUCKET2 + 1;
225 V_MAX_BUCKET := P_BUCKET3;
226 V_DUNNING_NUMBER := 3;
227 SEND1DUNNINGNOTIFICATIONS(V_MIN_BUCKET
228 ,V_MAX_BUCKET
229 ,V_DUNNING_NUMBER
230 ,V_SEND_NOTIFICATIONS
231 ,V_ESC_LEVEL
232 ,V_GRACE_DAYS);
233 V_MIN_BUCKET := P_BUCKET3 + 1;
234 V_MAX_BUCKET := 1000000;
235 V_DUNNING_NUMBER := 4;
236 SEND1DUNNINGNOTIFICATIONS(V_MIN_BUCKET
237 ,V_MAX_BUCKET
238 ,V_DUNNING_NUMBER
239 ,V_SEND_NOTIFICATIONS
240 ,V_ESC_LEVEL
241 ,V_GRACE_DAYS);
242
243 END IF;
244 END IF;
245 IF (P_OPERATION_TYPE = 'CC_INACT_EMPL_REPORT') THEN
246
247 AP_WEB_START_INACT_PRO(P_CARD_PROGRAM_ID
248 ,P_BILLED_START_DATE
249 ,P_BILLED_END_DATE
250 ,V_ERRNUM
251 ,V_ERRMSG);
252 END IF;
253
254 RETURN TRUE;
255 RETURN NULL;
256 RETURN NULL;
257 EXCEPTION
258 WHEN OTHERS THEN
259 RAISE_APPLICATION_ERROR(-20101,null);
260 END AFTERREPORT;
261
262 FUNCTION GETNLSSTRINGS RETURN BOOLEAN IS
263 BEGIN
264 SELECT
265 LY.MEANING,
266 LN.MEANING,
267 LA.DISPLAYED_FIELD,
268 LM.DISPLAYED_FIELD,
269 LP.DISPLAYED_FIELD,
270 LD.DISPLAYED_FIELD,
271 LR.DISPLAYED_FIELD
272 INTO CP_NLS_YES,CP_NLS_NO,CP_NLS_ALL,CP_NLS_DISPUTED,CP_NLS_MGR_UNAPPROVED,CP_NLS_AP_UNAPPROVED,CP_NLS_REJECTED
273 FROM
274 FND_LOOKUPS LY,
275 FND_LOOKUPS LN,
276 AP_LOOKUP_CODES LA,
277 AP_LOOKUP_CODES LM,
278 AP_LOOKUP_CODES LP,
279 AP_LOOKUP_CODES LD,
280 AP_LOOKUP_CODES LR
281 WHERE LY.LOOKUP_TYPE = 'YES_NO'
282 AND LY.LOOKUP_CODE = 'Y'
283 AND LN.LOOKUP_TYPE = 'YES_NO'
284 AND LN.LOOKUP_CODE = 'N'
285 AND LA.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
286 AND LA.LOOKUP_CODE = 'ALL'
287 AND LD.LOOKUP_TYPE = 'CC_STATUS'
288 AND LD.LOOKUP_CODE = 'DISPUTED'
289 AND LM.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
290 AND LM.LOOKUP_CODE = 'PENDMGR'
291 AND LP.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
292 AND LP.LOOKUP_CODE = 'MGRAPPR'
293 AND LR.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
294 AND LR.LOOKUP_CODE = 'REJECTED';
295 RETURN (TRUE);
296 RETURN NULL;
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 NULL;
300 WHEN OTHERS THEN
301 RETURN (FALSE);
302 RETURN NULL;
303 END GETNLSSTRINGS;
304
305 FUNCTION CF_REPORT_NUMFORMULA(C_REPORT_HEADER_ID IN NUMBER) RETURN VARCHAR2 IS
306 BEGIN
307 DECLARE
308 L_REPNUM VARCHAR2(50);
309 BEGIN
310 IF (C_REPORT_HEADER_ID = -1) THEN
311 RETURN '';
312 END IF;
313 FND_PROFILE.GET('AP_WEB_REPNUM_PREFIX'
314 ,L_REPNUM);
315 L_REPNUM := L_REPNUM || TO_CHAR(C_REPORT_HEADER_ID);
316 RETURN L_REPNUM;
317 END;
318 RETURN NULL;
319 END CF_REPORT_NUMFORMULA;
320
321 FUNCTION GETCOMPANYNAME RETURN BOOLEAN IS
322 BEGIN
323 SELECT
324 NAME,
325 CHART_OF_ACCOUNTS_ID
326 INTO CP_COMPANY_NAME_HEADER,CP_CHART_OF_ACCOUNTS_ID
327 FROM
328 GL_SETS_OF_BOOKS G,
329 AP_SYSTEM_PARAMETERS A
330 WHERE G.SET_OF_BOOKS_ID = A.SET_OF_BOOKS_ID;
331 RETURN (TRUE);
332 RETURN NULL;
333 EXCEPTION
334 WHEN OTHERS THEN
335 RETURN (FALSE);
336 END GETCOMPANYNAME;
337
338 FUNCTION CF_STATUSFORMULA(C_STATUS IN VARCHAR2
339 ,C_BILLED_AMOUNT IN NUMBER) RETURN VARCHAR2 IS
340 V_DISPLAY_STATUS VARCHAR2(80);
341 BEGIN
342 IF (C_STATUS = 'UNUSED') THEN
343 CP_UNSUBMITTED := CP_UNSUBMITTED + C_BILLED_AMOUNT;
344 END IF;
345 IF (C_STATUS = 'PENDMGR') THEN
346 CP_MGR_UNAPPROVED := CP_MGR_UNAPPROVED + C_BILLED_AMOUNT;
347 END IF;
348 IF (C_STATUS = 'MGRAPPR') THEN
349 CP_AP_UNAPPROVED := CP_AP_UNAPPROVED + C_BILLED_AMOUNT;
350 END IF;
351 IF (C_STATUS = 'DISPUTED') THEN
352 CP_DISPUTED := CP_DISPUTED + C_BILLED_AMOUNT;
353 END IF;
354 IF (C_STATUS = 'REJECTED') THEN
355 CP_REJECTED := CP_REJECTED + C_BILLED_AMOUNT;
356 END IF;
357 IF (C_STATUS = 'EMPAPPR') THEN
358 CP_EMP_APPR := CP_EMP_APPR + C_BILLED_AMOUNT;
359 END IF;
360 IF (C_STATUS = 'ERROR') THEN
361 CP_ERROR := CP_ERROR + C_BILLED_AMOUNT;
362 END IF;
363 IF (C_STATUS = 'WITHDRAWN') THEN
364 CP_WITHDRAWN := CP_WITHDRAWN + C_BILLED_AMOUNT;
365 END IF;
366 IF (C_STATUS = 'INVOICED') THEN
367 CP_INVOICED := CP_INVOICED + C_BILLED_AMOUNT;
368 END IF;
369 IF (C_STATUS in ('SAVED','INPROGRESS')) THEN
370 CP_SAVED := CP_SAVED + C_BILLED_AMOUNT;
371 END IF;
372 IF (C_STATUS = 'RETURNED') THEN
373 CP_RETURNED := CP_RETURNED + C_BILLED_AMOUNT;
374 END IF;
375 IF (C_STATUS = 'RESOLUTN') THEN
376 CP_RESOLUTN := CP_RESOLUTN + C_BILLED_AMOUNT;
377 END IF;
378 IF (C_STATUS in ('EMPAPPR','ERROR','INVOICED','WITHDRAWN','RESOLUTN','RETURNED','MGRAPPR','PENDMGR','REJECTED','SAVED','INPROGRESS','UNUSED')) THEN
379 BEGIN
380 SELECT
381 ALC.DISPLAYED_FIELD
382 INTO V_DISPLAY_STATUS
383 FROM
384 AP_LOOKUP_CODES ALC
385 WHERE ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
386 AND ALC.LOOKUP_CODE = C_STATUS;
387 RETURN V_DISPLAY_STATUS;
388 END;
389 ELSIF (C_STATUS in ('DISPUTED')) THEN
390 BEGIN
391 SELECT
392 ALC.DISPLAYED_FIELD
393 INTO V_DISPLAY_STATUS
394 FROM
395 AP_LOOKUP_CODES ALC
396 WHERE ALC.LOOKUP_TYPE = 'SSE_CCARD_TRXN_CATEGORY'
397 AND ALC.LOOKUP_CODE = C_STATUS;
398 RETURN V_DISPLAY_STATUS;
399 END;
400 END IF;
401 RETURN 'Unknown1';
402 END CF_STATUSFORMULA;
403
404 FUNCTION CF_CP_CURRENCY_CODEFORMULA(C_CP_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
405 BEGIN
406 IF (C_CP_CURRENCY_CODE IS NOT NULL) THEN
407 RETURN C_CP_CURRENCY_CODE;
408 ELSE
409 RETURN C_BASE_CURRENCY_CODE;
410 END IF;
411 RETURN NULL;
412 END CF_CP_CURRENCY_CODEFORMULA;
413
414 FUNCTION CF_EMP_CURRENCY_CODEFORMULA(C_EMP_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
415 BEGIN
416 IF (C_EMP_CURRENCY_CODE IS NOT NULL) THEN
417 RETURN C_EMP_CURRENCY_CODE;
418 ELSE
419 RETURN C_BASE_CURRENCY_CODE;
420 END IF;
421 RETURN NULL;
422 END CF_EMP_CURRENCY_CODEFORMULA;
423
424 FUNCTION GETBASECURRDATA RETURN BOOLEAN IS
425 BEGIN
426 SELECT
427 P.BASE_CURRENCY_CODE,
428 C.PRECISION,
429 C.MINIMUM_ACCOUNTABLE_UNIT,
430 C.DESCRIPTION
431 INTO C_BASE_CURRENCY_CODE,C_BASE_PRECISION,C_BASE_MIN_ACCT_UNIT,C_BASE_DESCRIPTION
432 FROM
433 AP_SYSTEM_PARAMETERS P,
434 FND_CURRENCIES_VL C
435 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
436 RETURN (TRUE);
437 RETURN NULL;
438 EXCEPTION
439 WHEN OTHERS THEN
440 RETURN (FALSE);
441 END GETBASECURRDATA;
442
443 PROCEDURE SENDUNSUBMITTED IS
444 V_EMPLOYEE_ID AP_CARDS.EMPLOYEE_ID%TYPE;
445 V_BILLED_AMOUNT AP_CREDIT_CARD_TRXNS.BILLED_AMOUNT%TYPE;
446 V_BILLED_CURRENCY_CODE AP_CREDIT_CARD_TRXNS.BILLED_CURRENCY_CODE%TYPE;
447 V_CARD_PROGRAM_NAME AP_CARD_PROGRAMS.CARD_PROGRAM_NAME%TYPE;
448 V_SAVED_CHARGE_TYPE VARCHAR2(20);
449 V_UNSUBMITTED_CHARGE_TYPE VARCHAR2(20) := 'UNUSED';
450 CURSOR UNSUBMITTEDCHARGES IS
451 SELECT
452 EMPLOYEE_ID,
453 SUM(BILLED_AMOUNT),
454 BILLED_CURRENCY_CODE
455 FROM
456 ( SELECT
457 AC.EMPLOYEE_ID,
458 CCT.BILLED_AMOUNT,
459 CCT.BILLED_CURRENCY_CODE,
460 CCT.TRX_ID
461 FROM
462 AP_CREDIT_CARD_TRXNS CCT,
463 AP_CARDS_ALL AC
464 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
465 AND CCT.VALIDATE_CODE = 'Y'
466 AND CCT.PAYMENT_FLAG <> 'Y'
467 AND NVL(CCT.CATEGORY
468 ,'BUSINESS') <> 'DEACTIVATED'
469 AND ( NVL(CCT.EXPENSED_AMOUNT
470 ,0) = 0
471 OR ( CCT.EXPENSED_AMOUNT <> 0
472 AND CCT.CATEGORY = 'PERSONAL'
473 AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
474 ,CCT.TRX_ID) = 'TRUE' ) )
475 AND NVL(CCT.CATEGORY
476 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' )
477 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
478 AND AC.CARD_ID = CCT.CARD_ID
479 AND NVL(CCT.BILLED_DATE
480 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
481 ,NVL(CCT.BILLED_DATE
482 ,CCT.POSTED_DATE) - 1)
483 AND NVL(P_BILLED_END_DATE
484 ,NVL(CCT.BILLED_DATE
485 ,CCT.POSTED_DATE) + 1)
486 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
487 ,NULL
488 ,-999999999999
489 ,P_MIN_AMOUNT)
490 AND AC.EMPLOYEE_ID in (
491 SELECT
492 DISTINCT
493 EMPLOYEE_ID
494 FROM
495 PER_EMPLOYEES_X
496 WHERE SUPERVISOR_ID = P_EMPLOYEE )
497 AND P_INCLUDE_DIRECTS = 'Y'
498 UNION ALL
499 SELECT
500 AC.EMPLOYEE_ID,
501 CCT.BILLED_AMOUNT,
502 CCT.BILLED_CURRENCY_CODE,
503 CCT.TRX_ID
504 FROM
505 AP_CREDIT_CARD_TRXNS CCT,
506 AP_CARDS_ALL AC
507 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
508 AND CCT.VALIDATE_CODE = 'Y'
509 AND CCT.PAYMENT_FLAG <> 'Y'
510 AND NVL(CCT.CATEGORY
511 ,'BUSINESS') <> 'DEACTIVATED'
512 AND ( NVL(CCT.EXPENSED_AMOUNT
513 ,0) = 0
514 OR ( CCT.EXPENSED_AMOUNT <> 0
515 AND CCT.CATEGORY = 'PERSONAL'
516 AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
517 ,CCT.TRX_ID) = 'TRUE' ) )
518 AND NVL(CCT.CATEGORY
519 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' )
520 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
521 AND AC.CARD_ID = CCT.CARD_ID
522 AND NVL(CCT.BILLED_DATE
523 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
524 ,NVL(CCT.BILLED_DATE
525 ,CCT.POSTED_DATE) - 1)
526 AND NVL(P_BILLED_END_DATE
527 ,NVL(CCT.BILLED_DATE
528 ,CCT.POSTED_DATE) + 1)
529 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
530 ,NULL
531 ,-999999999999
532 ,P_MIN_AMOUNT)
533 AND AC.EMPLOYEE_ID = P_EMPLOYEE
534 AND P_INCLUDE_DIRECTS = 'N'
535 UNION ALL
536 SELECT
537 AC.EMPLOYEE_ID,
538 CCT.BILLED_AMOUNT,
539 CCT.BILLED_CURRENCY_CODE,
540 CCT.TRX_ID
541 FROM
542 AP_CREDIT_CARD_TRXNS CCT,
543 AP_CARDS_ALL AC
544 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
545 AND CCT.VALIDATE_CODE = 'Y'
546 AND CCT.PAYMENT_FLAG <> 'Y'
547 AND NVL(CCT.CATEGORY
548 ,'BUSINESS') <> 'DEACTIVATED'
549 AND ( NVL(CCT.EXPENSED_AMOUNT
550 ,0) = 0
551 OR ( CCT.EXPENSED_AMOUNT <> 0
552 AND CCT.CATEGORY = 'PERSONAL'
553 AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
554 ,CCT.TRX_ID) = 'TRUE' ) )
555 AND NVL(CCT.CATEGORY
556 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' )
557 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
558 AND AC.CARD_ID = CCT.CARD_ID
559 AND NVL(CCT.BILLED_DATE
560 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
561 ,NVL(CCT.BILLED_DATE
562 ,CCT.POSTED_DATE) - 1)
563 AND NVL(P_BILLED_END_DATE
564 ,NVL(CCT.BILLED_DATE
565 ,CCT.POSTED_DATE) + 1)
566 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
567 ,NULL
568 ,-999999999999
569 ,P_MIN_AMOUNT)
570 AND P_EMPLOYEE is null
571 UNION ALL
572 SELECT
573 DISTINCT
574 AC.EMPLOYEE_ID,
575 CCT.BILLED_AMOUNT,
576 CCT.BILLED_CURRENCY_CODE,
577 CCT.TRX_ID
578 FROM
579 AP_CREDIT_CARD_TRXNS CCT,
580 AP_CARDS_ALL AC,
581 AP_EXPENSE_REPORT_HEADERS ERH
582 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
583 AND CCT.VALIDATE_CODE = 'Y'
584 AND CCT.PAYMENT_FLAG <> 'Y'
585 AND CCT.EXPENSED_AMOUNT <> 0
586 AND NVL(CCT.CATEGORY
587 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'PERSONAL' , 'DEACTIVATED' )
588 AND ERH.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
589 AND ERH.EMPLOYEE_ID = AC.EMPLOYEE_ID
590 AND NVL(ERH.VOUCHNO
591 ,0) = 0
592 AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
593 ,ERH.WORKFLOW_APPROVED_FLAG
594 ,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
595 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
596 AND AC.CARD_ID = CCT.CARD_ID
597 AND NVL(CCT.BILLED_DATE
598 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
599 ,NVL(CCT.BILLED_DATE
600 ,CCT.POSTED_DATE) - 1)
601 AND NVL(P_BILLED_END_DATE
602 ,NVL(CCT.BILLED_DATE
603 ,CCT.POSTED_DATE) + 1)
604 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
605 ,NULL
606 ,-999999999999
607 ,P_MIN_AMOUNT)
608 AND AC.EMPLOYEE_ID in (
609 SELECT
610 DISTINCT
611 EMPLOYEE_ID
612 FROM
613 PER_EMPLOYEES_X
614 WHERE SUPERVISOR_ID = P_EMPLOYEE )
615 AND P_INCLUDE_DIRECTS = 'Y'
616 UNION ALL
617 SELECT
618 DISTINCT
619 AC.EMPLOYEE_ID,
620 CCT.BILLED_AMOUNT,
621 CCT.BILLED_CURRENCY_CODE,
622 CCT.TRX_ID
623 FROM
624 AP_CREDIT_CARD_TRXNS CCT,
625 AP_CARDS_ALL AC,
626 AP_EXPENSE_REPORT_HEADERS ERH
627 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
628 AND CCT.VALIDATE_CODE = 'Y'
629 AND CCT.PAYMENT_FLAG <> 'Y'
630 AND CCT.EXPENSED_AMOUNT <> 0
631 AND NVL(CCT.CATEGORY
632 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'PERSONAL' , 'DEACTIVATED' )
633 AND ERH.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
634 AND ERH.EMPLOYEE_ID = AC.EMPLOYEE_ID
635 AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
636 ,ERH.WORKFLOW_APPROVED_FLAG
637 ,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
638 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
639 AND AC.CARD_ID = CCT.CARD_ID
640 AND NVL(CCT.BILLED_DATE
641 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
642 ,NVL(CCT.BILLED_DATE
643 ,CCT.POSTED_DATE) - 1)
644 AND NVL(P_BILLED_END_DATE
645 ,NVL(CCT.BILLED_DATE
646 ,CCT.POSTED_DATE) + 1)
647 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
648 ,NULL
649 ,-999999999999
650 ,P_MIN_AMOUNT)
651 AND AC.EMPLOYEE_ID = P_EMPLOYEE
652 AND P_INCLUDE_DIRECTS = 'N'
653 UNION ALL
654 SELECT
655 DISTINCT
656 AC.EMPLOYEE_ID,
657 CCT.BILLED_AMOUNT,
658 CCT.BILLED_CURRENCY_CODE,
659 CCT.TRX_ID
660 FROM
661 AP_CREDIT_CARD_TRXNS CCT,
662 AP_CARDS_ALL AC,
663 AP_EXPENSE_REPORT_LINES ERL,
664 AP_EXPENSE_REPORT_HEADERS ERH
665 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
666 AND CCT.VALIDATE_CODE = 'Y'
667 AND CCT.PAYMENT_FLAG <> 'Y'
668 AND CCT.EXPENSED_AMOUNT <> 0
669 AND NVL(CCT.CATEGORY
670 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
671 AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
672 AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
673 AND NVL(ERH.VOUCHNO
674 ,0) = 0
675 AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
676 ,ERH.WORKFLOW_APPROVED_FLAG
677 ,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
678 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
679 AND AC.CARD_ID = CCT.CARD_ID
680 AND NVL(CCT.BILLED_DATE
681 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
682 ,NVL(CCT.BILLED_DATE
683 ,CCT.POSTED_DATE) - 1)
684 AND NVL(P_BILLED_END_DATE
685 ,NVL(CCT.BILLED_DATE
686 ,CCT.POSTED_DATE) + 1)
687 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
688 ,NULL
689 ,-999999999999
690 ,P_MIN_AMOUNT)
691 AND P_EMPLOYEE is null )
692 GROUP BY
693 EMPLOYEE_ID,
694 BILLED_CURRENCY_CODE;
695 BEGIN
696 SELECT
697 CARD_PROGRAM_NAME
698 INTO V_CARD_PROGRAM_NAME
699 FROM
700 AP_CARD_PROGRAMS
701 WHERE CARD_PROGRAM_ID = P_CARD_PROGRAM_ID;
702 OPEN UNSUBMITTEDCHARGES;
703 LOOP
704 FETCH UNSUBMITTEDCHARGES
705 INTO V_EMPLOYEE_ID,V_BILLED_AMOUNT,V_BILLED_CURRENCY_CODE;
706 EXIT WHEN UNSUBMITTEDCHARGES%NOTFOUND;
707 AP_WEB_CREDIT_CARD_WF.SENDUNSUBMITTEDCHARGESNOTE(V_EMPLOYEE_ID
708 ,V_BILLED_AMOUNT
709 ,V_BILLED_CURRENCY_CODE
710 ,V_CARD_PROGRAM_NAME
711 ,TO_CHAR(P_BILLED_START_DATE)
712 ,TO_CHAR(P_BILLED_END_DATE)
713 ,V_UNSUBMITTED_CHARGE_TYPE);
714 END LOOP;
715 CLOSE UNSUBMITTEDCHARGES;
716 EXCEPTION
717 WHEN OTHERS THEN
718 RAISE_APPLICATION_ERROR(-20101,null);
719 END SENDUNSUBMITTED;
720
721 PROCEDURE SENDMGRUNAPPROVED IS
722 V_REPORT_HEADER_ID AP_EXPENSE_REPORT_HEADERS.REPORT_HEADER_ID%TYPE;
723 V_CURRENT_APPROVER AP_EXPENSE_REPORT_HEADERS.EXPENSE_CURRENT_APPROVER_ID%TYPE;
724 CURSOR UNAPPROVEDCHARGES IS
725 SELECT
726 DISTINCT
727 ERH.REPORT_HEADER_ID,
728 ERH.EXPENSE_CURRENT_APPROVER_ID
729 FROM
730 AP_CREDIT_CARD_TRXNS CCT,
731 AP_EXPENSE_REPORT_LINES ERL,
732 AP_EXPENSE_REPORT_HEADERS ERH
733 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
734 AND CCT.VALIDATE_CODE = 'Y'
735 AND CCT.PAYMENT_FLAG <> 'Y'
736 AND CCT.EXPENSED_AMOUNT <> 0
737 AND NVL(CCT.CATEGORY
738 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
739 AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
740 AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
741 AND NVL(ERH.VOUCHNO
742 ,0) = 0
743 AND ( ERH.SOURCE = 'WebExpense'
744 AND ERH.WORKFLOW_APPROVED_FLAG is null
745 OR ERH.WORKFLOW_APPROVED_FLAG = 'P' )
746 AND NVL(CCT.BILLED_DATE
747 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
748 ,NVL(CCT.BILLED_DATE
749 ,CCT.POSTED_DATE) - 1)
750 AND NVL(P_BILLED_END_DATE
751 ,NVL(CCT.BILLED_DATE
752 ,CCT.POSTED_DATE) + 1)
753 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
754 ,NULL
755 ,-999999999999
756 ,P_MIN_AMOUNT)
757 AND ERH.EMPLOYEE_ID in (
758 SELECT
759 DISTINCT
760 EMPLOYEE_ID
761 FROM
762 PER_EMPLOYEES_X
763 WHERE SUPERVISOR_ID = P_EMPLOYEE )
764 AND P_INCLUDE_DIRECTS = 'Y'
765 AND ERH.EXPENSE_CURRENT_APPROVER_ID is not null
766 GROUP BY
767 ERH.REPORT_HEADER_ID,
768 ERH.EXPENSE_CURRENT_APPROVER_ID
769 UNION ALL
770 SELECT
771 DISTINCT
772 ERH.REPORT_HEADER_ID,
773 ERH.EXPENSE_CURRENT_APPROVER_ID
774 FROM
775 AP_CREDIT_CARD_TRXNS CCT,
776 AP_EXPENSE_REPORT_LINES ERL,
777 AP_EXPENSE_REPORT_HEADERS ERH
778 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
779 AND CCT.VALIDATE_CODE = 'Y'
780 AND CCT.PAYMENT_FLAG <> 'Y'
781 AND CCT.EXPENSED_AMOUNT <> 0
782 AND NVL(CCT.CATEGORY
783 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
784 AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
785 AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
786 AND ( ERH.SOURCE = 'WebExpense'
787 AND ERH.WORKFLOW_APPROVED_FLAG is null
788 OR ERH.WORKFLOW_APPROVED_FLAG = 'P' )
789 AND NVL(CCT.BILLED_DATE
790 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
791 ,NVL(CCT.BILLED_DATE
792 ,CCT.POSTED_DATE) - 1)
793 AND NVL(P_BILLED_END_DATE
794 ,NVL(CCT.BILLED_DATE
795 ,CCT.POSTED_DATE) + 1)
796 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
797 ,NULL
798 ,-999999999999
799 ,P_MIN_AMOUNT)
800 AND ERH.EMPLOYEE_ID = P_EMPLOYEE
801 AND P_INCLUDE_DIRECTS = 'N'
802 AND ERH.EXPENSE_CURRENT_APPROVER_ID is not null
803 GROUP BY
804 ERH.REPORT_HEADER_ID,
805 ERH.EXPENSE_CURRENT_APPROVER_ID
806 UNION ALL
807 SELECT
808 DISTINCT
809 ERH.REPORT_HEADER_ID,
810 ERH.EXPENSE_CURRENT_APPROVER_ID
811 FROM
812 AP_CREDIT_CARD_TRXNS CCT,
813 AP_EXPENSE_REPORT_LINES ERL,
814 AP_EXPENSE_REPORT_HEADERS ERH
815 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
816 AND CCT.VALIDATE_CODE = 'Y'
817 AND CCT.PAYMENT_FLAG <> 'Y'
818 AND CCT.EXPENSED_AMOUNT <> 0
819 AND NVL(CCT.CATEGORY
820 ,'BUSINESS') not in ( 'DISPUTED' , 'MATCHED' , 'CREDIT' , 'DEACTIVATED' )
821 AND ERL.CREDIT_CARD_TRX_ID = CCT.TRX_ID
822 AND ERH.REPORT_HEADER_ID = ERL.REPORT_HEADER_ID
823 AND NVL(ERH.VOUCHNO
824 ,0) = 0
825 AND ( ERH.SOURCE = 'WebExpense'
826 AND ERH.WORKFLOW_APPROVED_FLAG is null
827 OR ERH.WORKFLOW_APPROVED_FLAG = 'P' )
828 AND NVL(CCT.BILLED_DATE
829 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
830 ,NVL(CCT.BILLED_DATE
831 ,CCT.POSTED_DATE) - 1)
832 AND NVL(P_BILLED_END_DATE
833 ,NVL(CCT.BILLED_DATE
834 ,CCT.POSTED_DATE) + 1)
835 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
836 ,NULL
837 ,-999999999999
838 ,P_MIN_AMOUNT)
839 AND P_EMPLOYEE is null
840 AND ERH.EXPENSE_CURRENT_APPROVER_ID is not null
841 GROUP BY
842 ERH.REPORT_HEADER_ID,
843 ERH.EXPENSE_CURRENT_APPROVER_ID;
844 BEGIN
845 OPEN UNAPPROVEDCHARGES;
846 LOOP
847 FETCH UNAPPROVEDCHARGES
848 INTO V_REPORT_HEADER_ID,V_CURRENT_APPROVER;
849 EXIT WHEN UNAPPROVEDCHARGES%NOTFOUND;
850 AP_WEB_CREDIT_CARD_WF.SENDUNAPPROVEDEXPREPORTNOTE(V_REPORT_HEADER_ID
851 ,V_CURRENT_APPROVER);
852 END LOOP;
853 CLOSE UNAPPROVEDCHARGES;
854 EXCEPTION
855 WHEN OTHERS THEN
856 RAISE_APPLICATION_ERROR(-20101,null);
857 END SENDMGRUNAPPROVED;
858
859 PROCEDURE SENDDISPUTED IS
860 V_EMPLOYEE_ID AP_CARDS.EMPLOYEE_ID%TYPE;
861 CURSOR DISPUTEDCHARGES IS
862 SELECT
863 AC.EMPLOYEE_ID
864 FROM
865 AP_CREDIT_CARD_TRXNS CCT,
866 AP_CARDS_ALL AC
867 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
868 AND CCT.VALIDATE_CODE = 'Y'
869 AND CCT.PAYMENT_FLAG <> 'Y'
870 AND NVL(CCT.EXPENSED_AMOUNT
871 ,0) = 0
872 AND NVL(CCT.CATEGORY
873 ,'BUSINESS') = 'DISPUTED'
874 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
875 AND AC.CARD_ID = CCT.CARD_ID
876 AND NVL(CCT.BILLED_DATE
877 ,CCT.POSTED_DATE) between NVL(P_BILLED_START_DATE
878 ,NVL(CCT.BILLED_DATE
879 ,CCT.POSTED_DATE) - 1)
880 AND NVL(P_BILLED_END_DATE
881 ,NVL(CCT.BILLED_DATE
882 ,CCT.POSTED_DATE) + 1)
883 AND CCT.BILLED_AMOUNT > P_MIN_AMOUNT
884 GROUP BY
885 EMPLOYEE_ID;
886 BEGIN
887 OPEN DISPUTEDCHARGES;
888 LOOP
889 FETCH DISPUTEDCHARGES
890 INTO V_EMPLOYEE_ID;
891 EXIT WHEN DISPUTEDCHARGES%NOTFOUND;
892 AP_WEB_CREDIT_CARD_WF.SENDDISPUTEDCHARGESNOTE(V_EMPLOYEE_ID
893 ,P_CARD_PROGRAM_ID
894 ,P_BILLED_START_DATE
895 ,P_BILLED_END_DATE
896 ,P_MIN_AMOUNT);
897 END LOOP;
898 CLOSE DISPUTEDCHARGES;
899 EXCEPTION
900 WHEN OTHERS THEN
901 RAISE_APPLICATION_ERROR(-20101,null);
902 END SENDDISPUTED;
903
904 FUNCTION GETCARDPROGRAMNAME RETURN BOOLEAN IS
905 BEGIN
906 SELECT
907 CARD_PROGRAM_NAME
908 INTO CP_CARD_PROGRAM_NAME
909 FROM
910 AP_CARD_PROGRAMS_ALL CP
911 WHERE CP.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID;
912 RETURN (TRUE);
913 RETURN NULL;
914 EXCEPTION
915 WHEN OTHERS THEN
916 RETURN (FALSE);
917 END GETCARDPROGRAMNAME;
918
919 FUNCTION CF_SUPERVISOR_NAMEFORMULA(SUPERVISOR_ID1 IN NUMBER) RETURN CHAR IS
920 L_SUPERVISOR_ID NUMBER;
921 L_SUPERVISOR_NAME VARCHAR2(240);
922 BEGIN
923 SELECT
924 FULL_NAME
925 INTO L_SUPERVISOR_NAME
926 FROM
927 PER_EMPLOYEES_X
928 WHERE EMPLOYEE_ID = SUPERVISOR_ID1;
929 CP_SUPERVISOR_NAME := L_SUPERVISOR_NAME;
930 RETURN (CP_SUPERVISOR_NAME);
931 EXCEPTION
932 WHEN OTHERS THEN
933 RETURN (NULL);
934 END CF_SUPERVISOR_NAMEFORMULA;
935
936 FUNCTION CF_AGING_BUCKETSFORMULA(AGE_POSTED_DATE IN DATE
937 ,AGING_AMOUNT IN NUMBER) RETURN CHAR IS
938 L_BUCKET_SUM1 NUMBER := 0;
939 L_BUCKET_SUM2 NUMBER := 0;
940 L_BUCKET_SUM3 NUMBER := 0;
941 L_BUCKET_SUM4 NUMBER := 0;
942 BEGIN
943 CP_BUCKET1 := 0;
944 CP_BUCKET2 := 0;
945 CP_BUCKET3 := 0;
946 CP_BUCKET4 := 0;
947 IF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN 0 AND P_BUCKET1) THEN
948 CP_BUCKET1 := AGING_AMOUNT;
949 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET1 + 1 AND P_BUCKET2) THEN
950 CP_BUCKET2 := AGING_AMOUNT;
951 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET2 + 1 AND P_BUCKET3) THEN
952 CP_BUCKET3 := AGING_AMOUNT;
953 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) > P_BUCKET3) THEN
954 CP_BUCKET4 := AGING_AMOUNT;
955 END IF;
956 RETURN NULL;
957 END CF_AGING_BUCKETSFORMULA;
958
959 FUNCTION CF_AGING_SUP_NAMEFORMULA(AGE_SUPERVISOR_ID IN NUMBER) RETURN CHAR IS
960 L_SUPERVISOR_NAME VARCHAR2(240);
961 BEGIN
962 SELECT
963 FULL_NAME
964 INTO L_SUPERVISOR_NAME
965 FROM
966 PER_EMPLOYEES_X
967 WHERE EMPLOYEE_ID = AGE_SUPERVISOR_ID;
968 CP_AGE_SUP_NAME := L_SUPERVISOR_NAME;
969 RETURN (NULL);
970 EXCEPTION
971 WHEN OTHERS THEN
972 RETURN (NULL);
973 END CF_AGING_SUP_NAMEFORMULA;
974
975 FUNCTION CF_AGE_AMP_NAMEFORMULA(AGE_EMPLOYEE_ID IN NUMBER
976 ,AGE_EMP_STATUS IN VARCHAR2) RETURN CHAR IS
977 L_EMPLOYEE_NAME VARCHAR2(240);
978 BEGIN
979 SELECT
980 FULL_NAME
981 INTO L_EMPLOYEE_NAME
982 FROM
983 PER_EMPLOYEES_X
984 WHERE EMPLOYEE_ID = AGE_EMPLOYEE_ID;
985 CP_AGE_EMP_NAME := L_EMPLOYEE_NAME || '/' || AGE_EMP_STATUS;
986 RETURN NULL;
987 END CF_AGE_AMP_NAMEFORMULA;
988
989 FUNCTION CF_EMP_NAME_SUMMFORMULA(EMPLOYEE_ID1 IN NUMBER) RETURN CHAR IS
990 L_EMP_NAME VARCHAR2(240);
991 BEGIN
992 SELECT
993 FULL_NAME
994 INTO L_EMP_NAME
995 FROM
996 PER_EMPLOYEES_X
997 WHERE EMPLOYEE_ID = EMPLOYEE_ID1;
998 CP_EMP_NAME_SUMM := L_EMP_NAME;
999 RETURN NULL;
1000 END CF_EMP_NAME_SUMMFORMULA;
1001
1002 FUNCTION CF_PENDING_AMOUNTSFORMULA(AGING_REPORT_STATUS_CODE IN VARCHAR2
1003 ,AGE_POSTED_DATE IN DATE
1004 ,AGING_AMOUNT IN NUMBER) RETURN CHAR IS
1005 BEGIN
1006 CP_EMP_PEND_BUCKET1 := 0;
1007 CP_EMP_PEND_BUCKET2 := 0;
1008 CP_EMP_PEND_BUCKET3 := 0;
1009 CP_EMP_PEND_BUCKET4 := 0;
1010 CP_MGR_PEND_BUCKET1 := 0;
1011 CP_MGR_PEND_BUCKET2 := 0;
1012 CP_MGR_PEND_BUCKET3 := 0;
1013 CP_MGR_PEND_BUCKET4 := 0;
1014 CP_APPR_PEND_BUCKET1 := 0;
1015 CP_APPR_PEND_BUCKET2 := 0;
1016 CP_APPR_PEND_BUCKET3 := 0;
1017 CP_APPR_PEND_BUCKET4 := 0;
1018 CP_SYS_PEND_BUCKET1 := 0;
1019 CP_SYS_PEND_BUCKET2 := 0;
1020 CP_SYS_PEND_BUCKET3 := 0;
1021 CP_SYS_PEND_BUCKET4 := 0;
1022 CP_EMP_PENDING := 0;
1023 CP_MGR_PENDING := 0;
1024 CP_APPR_PENDING := 0;
1025 BEGIN
1026 IF (AGING_REPORT_STATUS_CODE in ('SAVED','INPROGRESS','EMPAPPR','REJECTED','RESOLUTN','RETURNED','UNUSED','WITHDRAWN','DISPUTED')) THEN
1027 IF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN 0 AND P_BUCKET1) THEN
1028 CP_EMP_PEND_BUCKET1 := AGING_AMOUNT;
1029 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET1 + 1 AND P_BUCKET2) THEN
1030 CP_EMP_PEND_BUCKET2 := AGING_AMOUNT;
1031 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET2 + 1 AND P_BUCKET3) THEN
1032 CP_EMP_PEND_BUCKET3 := AGING_AMOUNT;
1033 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) >= P_BUCKET3 + 1) THEN
1034 CP_EMP_PEND_BUCKET4 := AGING_AMOUNT;
1035 END IF;
1036 CP_EMP_PENDING := CP_EMP_PEND_BUCKET1 + CP_EMP_PEND_BUCKET2 + CP_EMP_PEND_BUCKET3 + CP_EMP_PEND_BUCKET4;
1037 ELSIF (AGING_REPORT_STATUS_CODE in ('PENDMGR')) THEN
1038 IF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN 0 AND P_BUCKET1) THEN
1039 CP_MGR_PEND_BUCKET1 := AGING_AMOUNT;
1040 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET1 + 1 AND P_BUCKET2) THEN
1041 CP_MGR_PEND_BUCKET2 := AGING_AMOUNT;
1042 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET2 + 1 AND P_BUCKET3) THEN
1043 CP_MGR_PEND_BUCKET3 := AGING_AMOUNT;
1044 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) >= P_BUCKET3 + 1) THEN
1045 CP_MGR_PEND_BUCKET4 := AGING_AMOUNT;
1046 END IF;
1047 CP_MGR_PENDING := CP_MGR_PEND_BUCKET1 + CP_MGR_PEND_BUCKET2 + CP_MGR_PEND_BUCKET3 + CP_MGR_PEND_BUCKET4;
1048 ELSIF (AGING_REPORT_STATUS_CODE in ('MGRAPPR','INVOICED')) THEN
1049 IF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN 0 AND P_BUCKET1) THEN
1050 CP_APPR_PEND_BUCKET1 := AGING_AMOUNT;
1051 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET1 + 1 AND P_BUCKET2) THEN
1052 CP_APPR_PEND_BUCKET2 := AGING_AMOUNT;
1053 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET2 + 1 AND P_BUCKET3) THEN
1054 CP_APPR_PEND_BUCKET3 := AGING_AMOUNT;
1055 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) >= P_BUCKET3 + 1) THEN
1056 CP_APPR_PEND_BUCKET4 := AGING_AMOUNT;
1057 END IF;
1058 CP_APPR_PENDING := CP_APPR_PEND_BUCKET1 + CP_APPR_PEND_BUCKET2 + CP_APPR_PEND_BUCKET3 + CP_APPR_PEND_BUCKET4;
1059 ELSIF (AGING_REPORT_STATUS_CODE in ('ERROR')) THEN
1060 IF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN 0 AND P_BUCKET1) THEN
1061 CP_SYS_PEND_BUCKET1 := AGING_AMOUNT;
1062 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET1 + 1 AND P_BUCKET2) THEN
1063 CP_SYS_PEND_BUCKET2 := AGING_AMOUNT;
1064 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) BETWEEN P_BUCKET2 + 1 AND P_BUCKET3) THEN
1065 CP_SYS_PEND_BUCKET3 := AGING_AMOUNT;
1066 ELSIF ((TRUNC(SYSDATE) - AGE_POSTED_DATE) >= P_BUCKET3 + 1) THEN
1067 CP_SYS_PEND_BUCKET4 := AGING_AMOUNT;
1068 END IF;
1069 CP_SYS_PENDING := CP_SYS_PEND_BUCKET1 + CP_SYS_PEND_BUCKET2 + CP_SYS_PEND_BUCKET3 + CP_SYS_PEND_BUCKET4;
1070 END IF;
1071 CP_BUCKET1 := CP_EMP_PEND_BUCKET1 + CP_MGR_PEND_BUCKET1 + CP_APPR_PEND_BUCKET1 + CP_SYS_PEND_BUCKET1;
1072 CP_BUCKET2 := CP_EMP_PEND_BUCKET2 + CP_MGR_PEND_BUCKET2 + CP_APPR_PEND_BUCKET2 + CP_SYS_PEND_BUCKET2;
1073 CP_BUCKET3 := CP_EMP_PEND_BUCKET3 + CP_MGR_PEND_BUCKET3 + CP_APPR_PEND_BUCKET3 + CP_SYS_PEND_BUCKET3;
1074 CP_BUCKET4 := CP_EMP_PEND_BUCKET4 + CP_MGR_PEND_BUCKET4 + CP_APPR_PEND_BUCKET4 + CP_SYS_PEND_BUCKET4;
1075 END;
1076 RETURN NULL;
1077 END CF_PENDING_AMOUNTSFORMULA;
1078
1079 FUNCTION CF_AGE_SUP_PEND_BUCKLET1FORMUL(CS_SUP_BUCKET1 IN NUMBER
1080 ,CS_SUP_BUCKET2 IN NUMBER
1081 ,CS_SUP_BUCKET3 IN NUMBER
1082 ,CS_SUP_BUCKET4 IN NUMBER) RETURN CHAR IS
1083 BEGIN
1084 CP_SUP_PEND_BUCKET1 := NVL(CS_SUP_BUCKET1
1085 ,0);
1086 CP_SUP_PEND_BUCKET2 := NVL(CS_SUP_BUCKET2
1087 ,0);
1088 CP_SUP_PEND_BUCKET3 := NVL(CS_SUP_BUCKET3
1089 ,0);
1090 CP_SUP_PEND_BUCKET4 := NVL(CS_SUP_BUCKET4
1091 ,0);
1092 RETURN NULL;
1093 END CF_AGE_SUP_PEND_BUCKLET1FORMUL;
1094
1095 PROCEDURE SEND1DUNNINGNOTIFICATIONS(P_IN_MIN_BUCKET IN NUMBER
1096 ,P_IN_MAX_BUCKET IN NUMBER
1097 ,P_IN_DUNNING_NUMBER IN NUMBER
1098 ,P_IN_SEND_NOTIFICATIONS IN VARCHAR2
1099 ,P_IN_ESC_LEVEL IN NUMBER
1100 ,P_IN_GRACE_DAYS IN NUMBER) IS
1101 V_EMPLOYEE_ID PER_PEOPLE_F.PERSON_ID%TYPE;
1102 V_CARD_PROGRAM_ID AP_CREDIT_CARD_TRXNS.CARD_PROGRAM_ID%TYPE;
1103 V_MIN_BUCKET NUMBER;
1104 V_MAX_BUCKET NUMBER;
1105 V_BILLED_AMOUNT AP_CREDIT_CARD_TRXNS.BILLED_AMOUNT%TYPE;
1106 V_BILLED_CURRENCY_CODE AP_CREDIT_CARD_TRXNS.BILLED_CURRENCY_CODE%TYPE;
1107 V_DUNNING_NUMBER NUMBER;
1108 V_SEND_NOTIFICATIONS VARCHAR2(20);
1109 V_ESC_LEVEL NUMBER;
1110 V_GRACE_DAYS NUMBER;
1111 TYPE L_MANAGERIDLIST IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1112 L_MANAGER_IDLIST L_MANAGERIDLIST;
1113 L_COUNTER NUMBER := 1;
1114 L_MANAGER_ID NUMBER;
1115 L_MANAGER_NOTIFIED VARCHAR2(1) := 'N';
1116 L_TEMP_EMPLOYEE_ID NUMBER := 0;
1117 L_ORIG_MANAGER_ID NUMBER := 0;
1118 L_NEXT_MANAGER_ID NUMBER := 0;
1119 L_JOB_LEVEL NUMBER := 0;
1120 L_NEXT_MGR_JOB_LEVEL NUMBER := 0;
1121 I NUMBER := 1;
1122 CURSOR SEND1DUNNINGNOTIFICATIONS IS
1123 SELECT
1124 EMPLOYEE_ID,
1125 SUM(BILLED_AMOUNT),
1126 BILLED_CURRENCY_CODE
1127 FROM
1128 ( SELECT
1129 DISTINCT
1130 AC.EMPLOYEE_ID,
1131 CCT.BILLED_AMOUNT,
1132 CCT.BILLED_CURRENCY_CODE,
1133 CCT.TRX_ID
1134 FROM
1135 AP_CREDIT_CARD_TRXNS CCT,
1136 AP_CARDS AC,
1137 AP_CARD_PROGRAMS CP,
1138 AP_EXPENSE_REPORT_LINES ERL,
1139 AP_EXPENSE_REPORT_HEADERS ERH
1140 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1141 AND CCT.VALIDATE_CODE = 'Y'
1142 AND CCT.PAYMENT_FLAG <> 'Y'
1143 AND NVL(CCT.CATEGORY
1144 ,'BUSINESS') NOT IN ( 'DEACTIVATED' , 'MATCHED' , 'CREDIT' )
1145 AND erl.credit_card_trx_id (+) = CCT.TRX_ID
1146 AND erh.report_header_id (+) = ERL.REPORT_HEADER_ID
1147 AND NVL(ERH.VOUCHNO
1148 ,0) = 0
1149 AND NVL(AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
1150 ,ERH.WORKFLOW_APPROVED_FLAG
1151 ,ERH.REPORT_HEADER_ID)
1152 ,'UNUSED') in ( 'UNUSED' , 'SAVED' , 'INPROGRESS' , 'EMPAPPR' , 'REJECTED' , 'RESOLUTN' , 'WITHDRAWN' , 'RETURNED' )
1153 AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
1154 ,CCT.TRX_ID) = 'TRUE'
1155 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1156 AND AC.CARD_ID = CCT.CARD_ID
1157 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1158 AND ( AC.EMPLOYEE_ID in (
1159 SELECT
1160 DISTINCT
1161 EMPLOYEE_ID
1162 FROM
1163 PER_EMPLOYEES_X EMP1
1164 WHERE EMP1.SUPERVISOR_ID = P_EMPLOYEE )
1165 OR AC.EMPLOYEE_ID = P_EMPLOYEE )
1166 AND ( TRUNC(SYSDATE) - ( CCT.POSTED_DATE + DECODE(CCT.CATEGORY
1167 ,'DISPUTED'
1168 ,NVL(P_IN_GRACE_DAYS
1169 ,0)
1170 ,0) ) between P_IN_MIN_BUCKET
1171 AND P_IN_MAX_BUCKET )
1172 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
1173 ,NULL
1174 ,-999999999999
1175 ,P_MIN_AMOUNT)
1176 AND P_INCLUDE_DIRECTS = 'Y'
1177 AND exists (
1178 SELECT
1179 1
1180 FROM
1181 AP_EXPENSE_REPORT_HEADERS_ALL ERH2
1182 WHERE ERH2.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
1183 OR ( CCT.REPORT_HEADER_ID is null
1184 AND NVL(CCT.EXPENSED_AMOUNT
1185 ,0) = 0 )
1186 AND ROWNUM = 1 )
1187 UNION ALL
1188 SELECT
1189 DISTINCT
1190 AC.EMPLOYEE_ID,
1191 CCT.BILLED_AMOUNT,
1192 CCT.BILLED_CURRENCY_CODE,
1193 CCT.TRX_ID
1194 FROM
1195 AP_CREDIT_CARD_TRXNS CCT,
1196 AP_CARDS AC,
1197 AP_CARD_PROGRAMS CP,
1198 AP_EXPENSE_REPORT_LINES ERL,
1199 AP_EXPENSE_REPORT_HEADERS ERH
1200 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1201 AND CCT.VALIDATE_CODE = 'Y'
1202 AND CCT.PAYMENT_FLAG <> 'Y'
1203 AND NVL(CCT.CATEGORY
1204 ,'BUSINESS') NOT IN ( 'DEACTIVATED' , 'MATCHED' , 'CREDIT' )
1205 AND erl.credit_card_trx_id (+) = CCT.TRX_ID
1206 AND NVL(AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
1207 ,ERH.WORKFLOW_APPROVED_FLAG
1208 ,ERH.REPORT_HEADER_ID)
1209 ,'UNUSED') in ( 'UNUSED' , 'SAVED' , 'INPROGRESS' , 'EMPAPPR' , 'REJECTED' , 'RESOLUTN' , 'WITHDRAWN' , 'RETURNED' )
1210 AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
1211 ,CCT.TRX_ID) = 'TRUE'
1212 AND erh.report_header_id (+) = ERL.REPORT_HEADER_ID
1213 AND NVL(ERH.VOUCHNO
1214 ,0) = 0
1215 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1216 AND AC.CARD_ID = CCT.CARD_ID
1217 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1218 AND ( TRUNC(SYSDATE) - ( CCT.POSTED_DATE + DECODE(CCT.CATEGORY
1219 ,'DISPUTED'
1220 ,NVL(P_IN_GRACE_DAYS
1221 ,0)
1222 ,0) ) between P_IN_MIN_BUCKET
1223 AND P_IN_MAX_BUCKET )
1224 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
1225 ,NULL
1226 ,-999999999999
1227 ,P_MIN_AMOUNT)
1228 AND AC.EMPLOYEE_ID = P_EMPLOYEE
1229 AND P_INCLUDE_DIRECTS = 'N'
1230 AND exists (
1231 SELECT
1232 1
1233 FROM
1234 AP_EXPENSE_REPORT_HEADERS_ALL ERH2
1235 WHERE ERH2.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
1236 OR ( CCT.REPORT_HEADER_ID is null
1237 AND NVL(CCT.EXPENSED_AMOUNT
1238 ,0) = 0 )
1239 AND ROWNUM = 1 )
1240 UNION ALL
1241 SELECT
1242 DISTINCT
1243 AC.EMPLOYEE_ID,
1244 CCT.BILLED_AMOUNT,
1245 CCT.BILLED_CURRENCY_CODE,
1246 CCT.TRX_ID
1247 FROM
1248 AP_CREDIT_CARD_TRXNS CCT,
1249 AP_CARDS AC,
1250 AP_CARD_PROGRAMS CP,
1251 AP_EXPENSE_REPORT_LINES ERL,
1252 AP_EXPENSE_REPORT_HEADERS ERH
1253 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1254 AND CCT.VALIDATE_CODE = 'Y'
1255 AND CCT.PAYMENT_FLAG <> 'Y'
1256 AND NVL(CCT.CATEGORY
1257 ,'BUSINESS') NOT IN ( 'DEACTIVATED' , 'MATCHED' , 'CREDIT' )
1258 AND erl.credit_card_trx_id (+) = CCT.TRX_ID
1259 AND erh.report_header_id (+) = ERL.REPORT_HEADER_ID
1260 AND NVL(ERH.VOUCHNO
1261 ,0) = 0
1262 AND NVL(AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
1263 ,ERH.WORKFLOW_APPROVED_FLAG
1264 ,ERH.REPORT_HEADER_ID)
1265 ,'UNUSED') in ( 'UNUSED' , 'SAVED' , 'INPROGRESS' , 'EMPAPPR' , 'REJECTED' , 'RESOLUTN' , 'WITHDRAWN' , 'RETURNED' )
1266 AND AP_WEB_OA_ACTIVE_PKG.GETINCLUDENOTIFICATION(CCT.CATEGORY
1267 ,CCT.TRX_ID) = 'TRUE'
1268 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1269 AND AC.CARD_ID = CCT.CARD_ID
1270 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1271 AND ( TRUNC(SYSDATE) - ( CCT.POSTED_DATE + DECODE(CCT.CATEGORY
1272 ,'DISPUTED'
1273 ,NVL(P_IN_GRACE_DAYS
1274 ,0)
1275 ,0) ) between P_IN_MIN_BUCKET
1276 AND P_IN_MAX_BUCKET )
1277 AND CCT.BILLED_AMOUNT > DECODE(P_MIN_AMOUNT
1278 ,NULL
1279 ,-999999999999
1280 ,P_MIN_AMOUNT)
1281 AND P_EMPLOYEE is null
1282 AND exists (
1283 SELECT
1284 1
1285 FROM
1286 AP_EXPENSE_REPORT_HEADERS_ALL ERH2
1287 WHERE ERH2.REPORT_HEADER_ID = CCT.REPORT_HEADER_ID
1288 OR ( CCT.REPORT_HEADER_ID is null
1289 AND NVL(CCT.EXPENSED_AMOUNT
1290 ,0) = 0 )
1291 AND ROWNUM = 1 ) )
1292 GROUP BY
1293 EMPLOYEE_ID,
1294 BILLED_CURRENCY_CODE;
1295 BEGIN
1296 V_MIN_BUCKET := P_IN_MIN_BUCKET;
1297 V_MAX_BUCKET := P_IN_MAX_BUCKET;
1298 V_CARD_PROGRAM_ID := P_CARD_PROGRAM_ID;
1299 V_DUNNING_NUMBER := P_IN_DUNNING_NUMBER;
1300 V_SEND_NOTIFICATIONS := P_IN_SEND_NOTIFICATIONS;
1301 V_ESC_LEVEL := P_IN_ESC_LEVEL;
1302 V_GRACE_DAYS := P_IN_GRACE_DAYS;
1303
1304 OPEN SEND1DUNNINGNOTIFICATIONS;
1305 LOOP
1306 FETCH SEND1DUNNINGNOTIFICATIONS
1307 INTO V_EMPLOYEE_ID,V_BILLED_AMOUNT,V_BILLED_CURRENCY_CODE;
1308 EXIT WHEN SEND1DUNNINGNOTIFICATIONS%NOTFOUND;
1309 L_MANAGER_NOTIFIED := 'N';
1310
1311 IF V_EMPLOYEE_ID IS NOT NULL THEN
1312 AP_WEB_EXPENSE_WF.GETMANAGER(V_EMPLOYEE_ID
1313 ,L_MANAGER_ID);
1314 IF P_SEND_NOTIFICATIONS = 'ES' THEN
1315 L_TEMP_EMPLOYEE_ID := V_EMPLOYEE_ID;
1316 L_ORIG_MANAGER_ID := L_MANAGER_ID;
1317 L_NEXT_MANAGER_ID := L_MANAGER_ID;
1318 L_JOB_LEVEL := 0;
1319 I := 1;
1320 WHILE I <= V_DUNNING_NUMBER LOOP
1321
1322 AP_WEB_EXPENSE_WF.GETJOBLEVELANDSUPERVISOR(L_MANAGER_ID
1323 ,L_JOB_LEVEL);
1324 IF L_JOB_LEVEL < NVL(P_ESC_LEVEL
1325 ,999999999) THEN
1326 AP_WEB_EXPENSE_WF.GETMANAGER(L_TEMP_EMPLOYEE_ID
1327 ,L_MANAGER_ID);
1328 IF (L_MANAGER_ID IS NULL) THEN
1329 L_MANAGER_ID := L_TEMP_EMPLOYEE_ID;
1330 EXIT;
1331 END IF;
1332 AP_WEB_EXPENSE_WF.GETJOBLEVELANDSUPERVISOR(L_MANAGER_ID
1333 ,L_JOB_LEVEL);
1334 AP_WEB_EXPENSE_WF.GETMANAGER(L_MANAGER_ID
1335 ,L_NEXT_MANAGER_ID);
1336 IF (L_NEXT_MANAGER_ID IS NOT NULL) THEN
1337 AP_WEB_EXPENSE_WF.GETJOBLEVELANDSUPERVISOR(L_NEXT_MANAGER_ID
1338 ,L_NEXT_MGR_JOB_LEVEL);
1339 IF (L_NEXT_MGR_JOB_LEVEL > NVL(P_ESC_LEVEL
1340 ,999999999)) THEN
1341 L_TEMP_EMPLOYEE_ID := L_MANAGER_ID;
1342 EXIT;
1343 END IF;
1344 ELSE
1345 L_NEXT_MANAGER_ID := L_MANAGER_ID;
1346 END IF;
1347 END IF;
1348 L_TEMP_EMPLOYEE_ID := L_MANAGER_ID;
1349 I := I + 1;
1350 END LOOP;
1351 IF L_JOB_LEVEL = 0 AND P_ESC_LEVEL IS NOT NULL THEN
1352 L_MANAGER_ID := L_NEXT_MANAGER_ID;
1353 L_TEMP_EMPLOYEE_ID := L_MANAGER_ID;
1354 END IF;
1355 END IF;
1356 FOR i IN 1 .. (L_COUNTER - 1) LOOP
1357 IF L_MANAGER_ID = L_MANAGER_IDLIST(I) THEN
1358 L_MANAGER_NOTIFIED := 'Y';
1359 END IF;
1360 END LOOP;
1361 AP_WEB_CREDIT_CARD_WF.SENDDUNNINGNOTIFICATIONS(V_EMPLOYEE_ID
1362 ,V_CARD_PROGRAM_ID
1363 ,V_BILLED_AMOUNT
1364 ,V_BILLED_CURRENCY_CODE
1365 ,V_MIN_BUCKET
1366 ,V_MAX_BUCKET
1367 ,V_DUNNING_NUMBER
1368 ,V_SEND_NOTIFICATIONS
1369 ,V_ESC_LEVEL
1370 ,V_GRACE_DAYS
1371 ,L_MANAGER_NOTIFIED);
1372 L_MANAGER_IDLIST(L_COUNTER) := L_MANAGER_ID;
1373 L_COUNTER := L_COUNTER + 1;
1374 END IF;
1375 END LOOP;
1376 CLOSE SEND1DUNNINGNOTIFICATIONS;
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 RAISE_APPLICATION_ERROR(-20101,null);
1380 END SEND1DUNNINGNOTIFICATIONS;
1381
1382 FUNCTION AFTERPFORM RETURN BOOLEAN IS
1383 BEGIN
1384 RETURN (TRUE);
1385 END AFTERPFORM;
1386
1387 FUNCTION CF_MASKED_CARD_NUMBERFORMULA(C_CARD_NUMBER IN VARCHAR2) RETURN CHAR IS
1388 BEGIN
1389 CP_MASKED_CARD_NUMBER := C_CARD_NUMBER;
1390 RETURN NULL;
1391 END CF_MASKED_CARD_NUMBERFORMULA;
1392
1393 PROCEDURE AP_WEB_START_INACT_PRO(P_CARD_PROGRAM_ID IN NUMBER
1394 ,P_CC_BILLED_START_DATE IN DATE
1395 ,P_CC_BILLED_END_DATE IN DATE
1396 ,P_ERRNUM OUT NOCOPY NUMBER
1397 ,P_ERRMSG OUT NOCOPY VARCHAR2) IS
1398 L_CC_BILLED_START_DATE DATE;
1399 L_CC_BILLED_END_DATE DATE;
1400 L_BILLED_DATE DATE;
1401 L_WF_ITEM_KEY VARCHAR2(100);
1402 L_WF_ITEM_TYPE VARCHAR2(100) := 'APCCARD';
1403 L_TOTAL_AMT_POSTED NUMBER;
1404 CURSOR C_PREPARER_NOTIFIED(P_CARD_PROGRAM_ID IN NUMBER,P_CC_BILLED_START_DATE IN DATE,P_CC_BILLED_END_DATE IN DATE) IS
1405 SELECT
1406 CCT.CARD_PROGRAM_ID CARD_PROGRAM_ID,
1407 AC.EMPLOYEE_ID INACT_EMPLOYEE_ID,
1408 CCT.BILLED_CURRENCY_CODE BILLED_CURRENCY_CODE,
1409 SUM(CCT.BILLED_AMOUNT) TOTAL_AMT_POSTED
1410 FROM
1411 AP_CREDIT_CARD_TRXNS CCT,
1412 AP_CARDS_ALL AC
1413 WHERE AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1414 AND AC.CARD_ID = CCT.CARD_ID
1415 AND CCT.TRX_ID in (
1416 SELECT
1417 CCT.TRX_ID
1418 FROM
1419 AP_CREDIT_CARD_TRXNS CCT,
1420 AP_CARDS_ALL AC,
1421 AP_CARD_PROGRAMS_ALL CP,
1422 PER_PEOPLE_F PERF,
1423 PER_ASSIGNMENTS_F PERA,
1424 PER_ASSIGNMENT_STATUS_TYPES PERAS
1425 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1426 AND CCT.PAYMENT_FLAG <> 'Y'
1427 AND NVL(CCT.CATEGORY
1428 ,'BUSINESS') <> 'DEACTIVATED'
1429 AND NVL(CCT.EXPENSED_AMOUNT
1430 ,0) = 0
1431 AND CCT.VALIDATE_CODE = 'Y'
1432 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1433 AND AC.CARD_ID = CCT.CARD_ID
1434 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1435 AND AC.EMPLOYEE_ID = PERF.PERSON_ID
1436 AND PERF.PERSON_ID = PERA.PERSON_ID
1437 AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
1438 AND PERA.PRIMARY_FLAG = 'Y'
1439 AND PERA.ASSIGNMENT_TYPE = 'E'
1440 AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
1441 AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
1442 AND PERF.EFFECTIVE_END_DATE
1443 AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
1444 AND PERA.EFFECTIVE_END_DATE
1445 AND TRUNC(NVL(CCT.BILLED_DATE
1446 ,CCT.POSTED_DATE)) between NVL(P_CC_BILLED_START_DATE
1447 ,(NVL(CCT.BILLED_DATE
1448 ,CCT.POSTED_DATE) - 1))
1449 AND NVL(P_CC_BILLED_END_DATE
1450 ,(NVL(CCT.BILLED_DATE
1451 ,CCT.POSTED_DATE) + 1))
1452 AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL
1453 AND CCT.REPORT_HEADER_ID IS NULL
1454 UNION ALL
1455 SELECT
1456 CCT.TRX_ID
1457 FROM
1458 AP_CREDIT_CARD_TRXNS CCT,
1459 AP_CARDS_ALL AC,
1460 AP_CARD_PROGRAMS_ALL CP,
1461 PER_PEOPLE_F PERF,
1462 PER_ASSIGNMENTS_F PERA,
1463 PER_ASSIGNMENT_STATUS_TYPES PERAS,
1464 AP_EXPENSE_REPORT_HEADERS ERH
1465 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1466 AND CCT.PAYMENT_FLAG <> 'Y'
1467 AND CCT.EXPENSED_AMOUNT <> 0
1468 AND CCT.VALIDATE_CODE = 'Y'
1469 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1470 AND AC.CARD_ID = CCT.CARD_ID
1471 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1472 AND AC.EMPLOYEE_ID = PERF.PERSON_ID
1473 AND PERF.PERSON_ID = PERA.PERSON_ID
1474 AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
1475 AND PERA.PRIMARY_FLAG = 'Y'
1476 AND PERA.ASSIGNMENT_TYPE = 'E'
1477 AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
1478 AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
1479 AND PERF.EFFECTIVE_END_DATE
1480 AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
1481 AND PERA.EFFECTIVE_END_DATE
1482 AND CCT.REPORT_HEADER_ID = erh.report_header_id (+)
1483 AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
1484 ,ERH.WORKFLOW_APPROVED_FLAG
1485 ,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
1486 AND TRUNC(NVL(CCT.BILLED_DATE
1487 ,CCT.POSTED_DATE)) between NVL(P_CC_BILLED_START_DATE
1488 ,(NVL(CCT.BILLED_DATE
1489 ,CCT.POSTED_DATE) - 1))
1490 AND NVL(P_CC_BILLED_END_DATE
1491 ,(NVL(CCT.BILLED_DATE
1492 ,CCT.POSTED_DATE) + 1))
1493 AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL )
1494 AND NVL(CCT.CATEGORY
1495 ,'BUSINESS') <> 'DEACTIVATED'
1496 GROUP BY
1497 CCT.CARD_PROGRAM_ID,
1498 AC.EMPLOYEE_ID,
1499 CCT.BILLED_CURRENCY_CODE;
1500 R_PREPARER_NOTIFIED C_PREPARER_NOTIFIED%ROWTYPE;
1501 CURSOR C_CCTRX_UPDATE(P_CARD_PROGRAM_ID IN NUMBER,P_INACT_EMPLOYEE_ID IN NUMBER,P_CC_BILLED_START_DATE IN DATE,P_CC_BILLED_END_DATE IN DATE) IS
1502 SELECT
1503 DISTINCT
1504 CCT.TRX_ID
1505 FROM
1506 AP_CREDIT_CARD_TRXNS CCT,
1507 AP_CARDS_ALL AC,
1508 AP_CARD_PROGRAMS_ALL CP,
1509 PER_PEOPLE_F PERF,
1510 PER_ASSIGNMENTS_F PERA,
1511 PER_ASSIGNMENT_STATUS_TYPES PERAS
1512 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1513 AND CCT.PAYMENT_FLAG <> 'Y'
1514 AND NVL(CCT.EXPENSED_AMOUNT
1515 ,0) = 0
1516 AND CCT.VALIDATE_CODE = 'Y'
1517 AND NVL(CCT.CATEGORY
1518 ,'BUSINESS') <> 'DEACTIVATED'
1519 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1520 AND AC.CARD_ID = CCT.CARD_ID
1521 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1522 AND AC.EMPLOYEE_ID = P_INACT_EMPLOYEE_ID
1523 AND PERF.PERSON_ID = AC.EMPLOYEE_ID
1524 AND PERA.PERSON_ID = PERF.PERSON_ID
1525 AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
1526 AND PERA.PRIMARY_FLAG = 'Y'
1527 AND PERA.ASSIGNMENT_TYPE = 'E'
1528 AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
1529 AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
1530 AND PERF.EFFECTIVE_END_DATE
1531 AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
1532 AND PERA.EFFECTIVE_END_DATE
1533 AND NVL(CCT.BILLED_DATE
1534 ,CCT.POSTED_DATE) between NVL(P_CC_BILLED_START_DATE
1535 ,(NVL(CCT.BILLED_DATE
1536 ,CCT.POSTED_DATE) - 1))
1537 AND NVL(P_CC_BILLED_END_DATE
1538 ,(NVL(CCT.BILLED_DATE
1539 ,CCT.POSTED_DATE) + 1))
1540 AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL
1541 UNION
1542 SELECT
1543 DISTINCT
1544 CCT.TRX_ID
1545 FROM
1546 AP_CREDIT_CARD_TRXNS CCT,
1547 AP_CARDS_ALL AC,
1548 AP_CARD_PROGRAMS_ALL CP,
1549 PER_PEOPLE_F PERF,
1550 PER_ASSIGNMENTS_F PERA,
1551 PER_ASSIGNMENT_STATUS_TYPES PERAS,
1552 AP_EXPENSE_REPORT_HEADERS ERH
1553 WHERE CCT.CARD_PROGRAM_ID = P_CARD_PROGRAM_ID
1554 AND CCT.PAYMENT_FLAG <> 'Y'
1555 AND CCT.EXPENSED_AMOUNT <> 0
1556 AND CCT.VALIDATE_CODE = 'Y'
1557 AND AC.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1558 AND AC.CARD_ID = CCT.CARD_ID
1559 AND CP.CARD_PROGRAM_ID = CCT.CARD_PROGRAM_ID
1560 AND AC.EMPLOYEE_ID = P_INACT_EMPLOYEE_ID
1561 AND PERF.PERSON_ID = AC.EMPLOYEE_ID
1562 AND PERA.PERSON_ID = PERF.PERSON_ID
1563 AND PERA.ASSIGNMENT_STATUS_TYPE_ID = PERAS.ASSIGNMENT_STATUS_TYPE_ID
1564 AND PERA.PRIMARY_FLAG = 'Y'
1565 AND PERA.ASSIGNMENT_TYPE = 'E'
1566 AND PER_SYSTEM_STATUS in ( 'TERM_ASSIGN' , 'SUSP_ASSIGN' )
1567 AND TRUNC(SYSDATE) between PERF.EFFECTIVE_START_DATE
1568 AND PERF.EFFECTIVE_END_DATE
1569 AND TRUNC(SYSDATE) between PERA.EFFECTIVE_START_DATE
1570 AND PERA.EFFECTIVE_END_DATE
1571 AND CCT.REPORT_HEADER_ID = erh.report_header_id (+)
1572 AND NVL(CCT.CATEGORY
1573 ,'BUSINESS') <> 'DEACTIVATED'
1574 AND AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(ERH.SOURCE
1575 ,ERH.WORKFLOW_APPROVED_FLAG
1576 ,ERH.REPORT_HEADER_ID) in ( 'EMPAPPR' , 'RESOLUTN' , 'RETURNED' , 'REJECTED' , 'WITHDRAWN' , 'SAVED' , 'INPROGRESS' )
1577 AND NVL(CCT.BILLED_DATE
1578 ,CCT.POSTED_DATE) between NVL(P_CC_BILLED_START_DATE
1579 ,(NVL(CCT.BILLED_DATE
1580 ,CCT.POSTED_DATE) - 1))
1581 AND NVL(P_CC_BILLED_END_DATE
1582 ,(NVL(CCT.BILLED_DATE
1583 ,CCT.POSTED_DATE) + 1))
1584 AND CCT.INACTIVE_EMP_WF_ITEM_KEY IS NULL;
1585 R_CCTRX_UPDATE C_CCTRX_UPDATE%ROWTYPE;
1586 BEGIN
1587 L_CC_BILLED_START_DATE := P_CC_BILLED_START_DATE;
1588 L_CC_BILLED_END_DATE := P_CC_BILLED_END_DATE;
1589 OPEN C_PREPARER_NOTIFIED(P_CARD_PROGRAM_ID,P_CC_BILLED_START_DATE,P_CC_BILLED_END_DATE);
1590 LOOP
1591 FETCH C_PREPARER_NOTIFIED
1592 INTO R_PREPARER_NOTIFIED;
1593 EXIT WHEN C_PREPARER_NOTIFIED%NOTFOUND;
1594 BEGIN
1595 SELECT
1596 TO_CHAR(AP_CCARD_NOTIFICATION_ID_S.NEXTVAL)
1597 INTO L_WF_ITEM_KEY
1598 FROM
1599 SYS.DUAL;
1600 END;
1601 AP_WEB_INACTIVE_EMP_WF_PKG.START_INACTIVE_EMP_PROCESS(R_PREPARER_NOTIFIED.CARD_PROGRAM_ID
1602 ,R_PREPARER_NOTIFIED.INACT_EMPLOYEE_ID
1603 ,R_PREPARER_NOTIFIED.BILLED_CURRENCY_CODE
1604 ,R_PREPARER_NOTIFIED.TOTAL_AMT_POSTED
1605 ,L_CC_BILLED_START_DATE
1606 ,L_CC_BILLED_END_DATE
1607 ,L_WF_ITEM_TYPE
1608 ,L_WF_ITEM_KEY);
1609 OPEN C_CCTRX_UPDATE(R_PREPARER_NOTIFIED.CARD_PROGRAM_ID,R_PREPARER_NOTIFIED.INACT_EMPLOYEE_ID,L_CC_BILLED_START_DATE,L_CC_BILLED_END_DATE);
1610 LOOP
1611 FETCH C_CCTRX_UPDATE
1612 INTO R_CCTRX_UPDATE;
1613 EXIT WHEN C_CCTRX_UPDATE%NOTFOUND;
1614 UPDATE
1615 AP_CREDIT_CARD_TRXNS
1616 SET
1617 INACTIVE_EMP_WF_ITEM_KEY = L_WF_ITEM_KEY
1618 WHERE TRX_ID = R_CCTRX_UPDATE.TRX_ID;
1619 END LOOP;
1620 COMMIT;
1621 CLOSE C_CCTRX_UPDATE;
1622 END LOOP;
1623 CLOSE C_PREPARER_NOTIFIED;
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 P_ERRNUM := TO_NUMBER(SQLCODE);
1627 P_ERRMSG := SQLERRM;
1628 END AP_WEB_START_INACT_PRO;
1629
1630 FUNCTION CP_UNSUBMITTED_P RETURN NUMBER IS
1631 BEGIN
1632 RETURN CP_UNSUBMITTED;
1633 END CP_UNSUBMITTED_P;
1634
1635 FUNCTION CP_REJECTED_P RETURN NUMBER IS
1636 BEGIN
1637 RETURN CP_REJECTED;
1638 END CP_REJECTED_P;
1639
1640 FUNCTION CP_WITHDRAWN_P RETURN NUMBER IS
1641 BEGIN
1642 RETURN CP_WITHDRAWN;
1643 END CP_WITHDRAWN_P;
1644
1645 FUNCTION CP_SAVED_P RETURN NUMBER IS
1646 BEGIN
1647 RETURN CP_SAVED;
1648 END CP_SAVED_P;
1649
1650 FUNCTION CP_RETURNED_P RETURN NUMBER IS
1651 BEGIN
1652 RETURN CP_RETURNED;
1653 END CP_RETURNED_P;
1654
1655 FUNCTION CP_RESOLUTN_P RETURN NUMBER IS
1656 BEGIN
1657 RETURN CP_RESOLUTN;
1658 END CP_RESOLUTN_P;
1659
1660 FUNCTION CP_INVOICED_P RETURN NUMBER IS
1661 BEGIN
1662 RETURN CP_INVOICED;
1663 END CP_INVOICED_P;
1664
1665 FUNCTION CP_ERROR_P RETURN NUMBER IS
1666 BEGIN
1667 RETURN CP_ERROR;
1668 END CP_ERROR_P;
1669
1670 FUNCTION CP_EMP_APPR_P RETURN NUMBER IS
1671 BEGIN
1672 RETURN CP_EMP_APPR;
1673 END CP_EMP_APPR_P;
1674
1675 FUNCTION CP_MGR_UNAPPROVED_P RETURN NUMBER IS
1676 BEGIN
1677 RETURN CP_MGR_UNAPPROVED;
1678 END CP_MGR_UNAPPROVED_P;
1679
1680 FUNCTION CP_AP_UNAPPROVED_P RETURN NUMBER IS
1681 BEGIN
1682 RETURN CP_AP_UNAPPROVED;
1683 END CP_AP_UNAPPROVED_P;
1684
1685 FUNCTION CP_DISPUTED_P RETURN NUMBER IS
1686 BEGIN
1687 RETURN CP_DISPUTED;
1688 END CP_DISPUTED_P;
1689
1690 FUNCTION CP_MASKED_CARD_NUMBER_P RETURN VARCHAR2 IS
1691 BEGIN
1692 RETURN CP_MASKED_CARD_NUMBER;
1693 END CP_MASKED_CARD_NUMBER_P;
1694
1695 FUNCTION CP_SUPERVISOR_NAME_P RETURN VARCHAR2 IS
1696 BEGIN
1697 RETURN CP_SUPERVISOR_NAME;
1698 END CP_SUPERVISOR_NAME_P;
1699
1700 FUNCTION CP_EMP_NAME_SUMM_P RETURN VARCHAR2 IS
1701 BEGIN
1702 RETURN CP_EMP_NAME_SUMM;
1703 END CP_EMP_NAME_SUMM_P;
1704
1705 FUNCTION CP_AGE_SUP_NAME_P RETURN VARCHAR2 IS
1706 BEGIN
1707 RETURN CP_AGE_SUP_NAME;
1708 END CP_AGE_SUP_NAME_P;
1709
1710 FUNCTION CP_SUP_PEND_BUCKET1_P RETURN NUMBER IS
1711 BEGIN
1712 RETURN CP_SUP_PEND_BUCKET1;
1713 END CP_SUP_PEND_BUCKET1_P;
1714
1715 FUNCTION CP_SUP_PEND_BUCKET2_P RETURN NUMBER IS
1716 BEGIN
1717 RETURN CP_SUP_PEND_BUCKET2;
1718 END CP_SUP_PEND_BUCKET2_P;
1719
1720 FUNCTION CP_SUP_PEND_BUCKET3_P RETURN NUMBER IS
1721 BEGIN
1722 RETURN CP_SUP_PEND_BUCKET3;
1723 END CP_SUP_PEND_BUCKET3_P;
1724
1725 FUNCTION CP_SUP_PEND_BUCKET4_P RETURN NUMBER IS
1726 BEGIN
1727 RETURN CP_SUP_PEND_BUCKET4;
1728 END CP_SUP_PEND_BUCKET4_P;
1729
1730 FUNCTION CP_AGE_EMP_NAME_P RETURN VARCHAR2 IS
1731 BEGIN
1732 RETURN CP_AGE_EMP_NAME;
1733 END CP_AGE_EMP_NAME_P;
1734
1735 FUNCTION CP_APPR_PEND_BUCKET1_P RETURN NUMBER IS
1736 BEGIN
1737 RETURN CP_APPR_PEND_BUCKET1;
1738 END CP_APPR_PEND_BUCKET1_P;
1739
1740 FUNCTION CP_APPR_PEND_BUCKET3_P RETURN NUMBER IS
1741 BEGIN
1742 RETURN CP_APPR_PEND_BUCKET3;
1743 END CP_APPR_PEND_BUCKET3_P;
1744
1745 FUNCTION CP_APPR_PEND_BUCKET4_P RETURN NUMBER IS
1746 BEGIN
1747 RETURN CP_APPR_PEND_BUCKET4;
1748 END CP_APPR_PEND_BUCKET4_P;
1749
1750 FUNCTION CP_APPR_PEND_BUCKET2_P RETURN NUMBER IS
1751 BEGIN
1752 RETURN CP_APPR_PEND_BUCKET2;
1753 END CP_APPR_PEND_BUCKET2_P;
1754
1755 FUNCTION CP_EMP_PEND_BUCKET1_P RETURN NUMBER IS
1756 BEGIN
1757 RETURN CP_EMP_PEND_BUCKET1;
1758 END CP_EMP_PEND_BUCKET1_P;
1759
1760 FUNCTION CP_EMP_PEND_BUCKET2_P RETURN NUMBER IS
1761 BEGIN
1762 RETURN CP_EMP_PEND_BUCKET2;
1763 END CP_EMP_PEND_BUCKET2_P;
1764
1765 FUNCTION CP_EMP_PEND_BUCKET3_P RETURN NUMBER IS
1766 BEGIN
1767 RETURN CP_EMP_PEND_BUCKET3;
1768 END CP_EMP_PEND_BUCKET3_P;
1769
1770 FUNCTION CP_EMP_PEND_BUCKET4_P RETURN NUMBER IS
1771 BEGIN
1772 RETURN CP_EMP_PEND_BUCKET4;
1773 END CP_EMP_PEND_BUCKET4_P;
1774
1775 FUNCTION CP_SYS_PEND_BUCKET2_P RETURN NUMBER IS
1776 BEGIN
1777 RETURN CP_SYS_PEND_BUCKET2;
1778 END CP_SYS_PEND_BUCKET2_P;
1779
1780 FUNCTION CP_SYS_PEND_BUCKET3_P RETURN NUMBER IS
1781 BEGIN
1782 RETURN CP_SYS_PEND_BUCKET3;
1783 END CP_SYS_PEND_BUCKET3_P;
1784
1785 FUNCTION CP_SYS_PEND_BUCKET4_P RETURN NUMBER IS
1786 BEGIN
1787 RETURN CP_SYS_PEND_BUCKET4;
1788 END CP_SYS_PEND_BUCKET4_P;
1789
1790 FUNCTION CP_SYS_PEND_BUCKET1_P RETURN NUMBER IS
1791 BEGIN
1792 RETURN CP_SYS_PEND_BUCKET1;
1793 END CP_SYS_PEND_BUCKET1_P;
1794
1795 FUNCTION CP_MGR_PEND_BUCKET1_P RETURN NUMBER IS
1796 BEGIN
1797 RETURN CP_MGR_PEND_BUCKET1;
1798 END CP_MGR_PEND_BUCKET1_P;
1799
1800 FUNCTION CP_MGR_PEND_BUCKET2_P RETURN NUMBER IS
1801 BEGIN
1802 RETURN CP_MGR_PEND_BUCKET2;
1803 END CP_MGR_PEND_BUCKET2_P;
1804
1805 FUNCTION CP_MGR_PEND_BUCKET3_P RETURN NUMBER IS
1806 BEGIN
1807 RETURN CP_MGR_PEND_BUCKET3;
1808 END CP_MGR_PEND_BUCKET3_P;
1809
1810 FUNCTION CP_MGR_PEND_BUCKET4_P RETURN NUMBER IS
1811 BEGIN
1812 RETURN CP_MGR_PEND_BUCKET4;
1813 END CP_MGR_PEND_BUCKET4_P;
1814
1815 FUNCTION CP_BUCKET1_P RETURN NUMBER IS
1816 BEGIN
1817 RETURN CP_BUCKET1;
1818 END CP_BUCKET1_P;
1819
1820 FUNCTION CP_BUCKET2_P RETURN NUMBER IS
1821 BEGIN
1822 RETURN CP_BUCKET2;
1823 END CP_BUCKET2_P;
1824
1825 FUNCTION CP_BUCKET3_P RETURN NUMBER IS
1826 BEGIN
1827 RETURN CP_BUCKET3;
1828 END CP_BUCKET3_P;
1829
1830 FUNCTION CP_BUCKET4_P RETURN NUMBER IS
1831 BEGIN
1832 RETURN CP_BUCKET4;
1833 END CP_BUCKET4_P;
1834
1835 FUNCTION CP_EMP_PENDING_P RETURN NUMBER IS
1836 BEGIN
1837 RETURN CP_EMP_PENDING;
1838 END CP_EMP_PENDING_P;
1839
1840 FUNCTION CP_SYS_PENDING_P RETURN NUMBER IS
1841 BEGIN
1842 RETURN CP_SYS_PENDING;
1843 END CP_SYS_PENDING_P;
1844
1845 FUNCTION CP_MGR_PENDING_P RETURN NUMBER IS
1846 BEGIN
1847 RETURN CP_MGR_PENDING;
1848 END CP_MGR_PENDING_P;
1849
1850 FUNCTION CP_APPR_PENDING_P RETURN NUMBER IS
1851 BEGIN
1852 RETURN CP_APPR_PENDING;
1853 END CP_APPR_PENDING_P;
1854
1855 FUNCTION CP_NLS_YES_P RETURN VARCHAR2 IS
1856 BEGIN
1857 RETURN CP_NLS_YES;
1858 END CP_NLS_YES_P;
1859
1860 FUNCTION CP_NLS_NO_P RETURN VARCHAR2 IS
1861 BEGIN
1862 RETURN CP_NLS_NO;
1863 END CP_NLS_NO_P;
1864
1865 FUNCTION CP_NLS_ALL_P RETURN VARCHAR2 IS
1866 BEGIN
1867 RETURN CP_NLS_ALL;
1868 END CP_NLS_ALL_P;
1869
1870 FUNCTION CP_NLS_NO_DATA_FOUND_P RETURN VARCHAR2 IS
1871 BEGIN
1872 RETURN CP_NLS_NO_DATA_FOUND;
1873 END CP_NLS_NO_DATA_FOUND_P;
1874
1875 FUNCTION CP_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
1876 BEGIN
1877 RETURN CP_NLS_END_OF_REPORT;
1878 END CP_NLS_END_OF_REPORT_P;
1879
1880 FUNCTION CP_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
1881 BEGIN
1882 RETURN CP_COMPANY_NAME_HEADER;
1883 END CP_COMPANY_NAME_HEADER_P;
1884
1885 FUNCTION CP_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
1886 BEGIN
1887 RETURN CP_CHART_OF_ACCOUNTS_ID;
1888 END CP_CHART_OF_ACCOUNTS_ID_P;
1889
1890 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1891 BEGIN
1892 RETURN C_BASE_CURRENCY_CODE;
1893 END C_BASE_CURRENCY_CODE_P;
1894
1895 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1896 BEGIN
1897 RETURN C_BASE_DESCRIPTION;
1898 END C_BASE_DESCRIPTION_P;
1899
1900 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1901 BEGIN
1902 RETURN C_BASE_MIN_ACCT_UNIT;
1903 END C_BASE_MIN_ACCT_UNIT_P;
1904
1905 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1906 BEGIN
1907 RETURN C_BASE_PRECISION;
1908 END C_BASE_PRECISION_P;
1909
1910 FUNCTION CP_NLS_UNSUBMITTED_P RETURN VARCHAR2 IS
1911 BEGIN
1912 RETURN CP_NLS_UNSUBMITTED;
1913 END CP_NLS_UNSUBMITTED_P;
1914
1915 FUNCTION CP_NLS_MGR_UNAPPROVED_P RETURN VARCHAR2 IS
1916 BEGIN
1917 RETURN CP_NLS_MGR_UNAPPROVED;
1918 END CP_NLS_MGR_UNAPPROVED_P;
1919
1920 FUNCTION CP_NLS_AP_UNAPPROVED_P RETURN VARCHAR2 IS
1921 BEGIN
1922 RETURN CP_NLS_AP_UNAPPROVED;
1923 END CP_NLS_AP_UNAPPROVED_P;
1924
1925 FUNCTION CP_NLS_DISPUTED_P RETURN VARCHAR2 IS
1926 BEGIN
1927 RETURN CP_NLS_DISPUTED;
1928 END CP_NLS_DISPUTED_P;
1929
1930 FUNCTION CP_CARD_PROGRAM_NAME_P RETURN VARCHAR2 IS
1931 BEGIN
1932 RETURN CP_CARD_PROGRAM_NAME;
1933 END CP_CARD_PROGRAM_NAME_P;
1934
1935 FUNCTION CP_1_P RETURN NUMBER IS
1936 BEGIN
1937 RETURN CP_1;
1938 END CP_1_P;
1939
1940 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
1941 BEGIN
1942 RETURN CP_REPORT_TITLE;
1943 END CP_REPORT_TITLE_P;
1944
1945 FUNCTION CP_BUCKET1_NAME_P RETURN VARCHAR2 IS
1946 BEGIN
1947 RETURN CP_BUCKET1_NAME;
1948 END CP_BUCKET1_NAME_P;
1949
1950 FUNCTION CP_BUCKET2_NAME_P RETURN VARCHAR2 IS
1951 BEGIN
1952 RETURN CP_BUCKET2_NAME;
1953 END CP_BUCKET2_NAME_P;
1954
1955 FUNCTION CP_BUCKET3_NAME_P RETURN VARCHAR2 IS
1956 BEGIN
1957 RETURN CP_BUCKET3_NAME;
1958 END CP_BUCKET3_NAME_P;
1959
1960 FUNCTION CP_BUCKET4_NAME_P RETURN VARCHAR2 IS
1961 BEGIN
1962 RETURN CP_BUCKET4_NAME;
1963 END CP_BUCKET4_NAME_P;
1964
1965 FUNCTION CP_NLS_REJECTED_P RETURN VARCHAR2 IS
1966 BEGIN
1967 RETURN CP_NLS_REJECTED;
1968 END CP_NLS_REJECTED_P;
1969
1970 FUNCTION Q_agingFilter RETURN BOOLEAN IS
1971 BEGIN
1972 IF(P_OPERATION_TYPE = 'CC_DETAIL_REPORT' OR P_OPERATION_TYPE = 'CC_SUMMARY_REPORT' OR P_OPERATION_TYPE = 'CC_INACT_EMPL_REPORT') THEN
1973 RETURN(FALSE);
1974 END IF;
1975 END;
1976
1977 FUNCTION Q_TRANS_SUMMFilter RETURN BOOLEAN IS
1978 BEGIN
1979 IF(P_OPERATION_TYPE = 'CC_DETAIL_REPORT' OR P_OPERATION_TYPE = 'CC_AGING_REPORT' OR P_OPERATION_TYPE = 'CC_INACT_EMPL_REPORT') THEN
1980 RETURN(FALSE);
1981 END IF;
1982 END;
1983
1984 FUNCTION Q_TRXNFilter RETURN BOOLEAN IS
1985 BEGIN
1986 IF(P_OPERATION_TYPE = 'CC_SUMMARY_REPORT' OR P_OPERATION_TYPE = 'CC_AGING_REPORT' ) THEN
1987 RETURN(FALSE);
1988 END IF;
1989 END;
1990 END AP_APXCCOUT_XMLP_PKG;
1991
1992