[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_CCARD_PKG
Source
1 PACKAGE BODY AP_WEB_DB_CCARD_PKG AS
2 /* $Header: apwdbccb.pls 120.60.12020000.2 2012/07/05 14:13:01 rveliche ship $ */
3 /* Credit Cards */
4
5 --------------------------------------------------------------------------------
6 FUNCTION GetExpRptCCTrxnCategoryCursor(
7 P_ReportHeaderID IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
8 p_category OUT NOCOPY CreditCardCategoryCursor
9 ) RETURN BOOLEAN IS
10 --------------------------------------------------------------------------------
11 BEGIN
12 OPEN p_category FOR
13 SELECT ACCT.CATEGORY
14 FROM AP_CREDIT_CARD_TRXNS ACCT,
15 AP_EXPENSE_REPORT_LINES AERL
16 WHERE AERL.REPORT_HEADER_ID = P_ReportHeaderID
17 AND ACCT.TRX_ID = AERL.CREDIT_CARD_TRX_ID;
18
19 RETURN TRUE;
20
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 RETURN FALSE;
24
25 WHEN OTHERS THEN
26 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetExpRptCCTrxnCategoryCursor' );
27 APP_EXCEPTION.RAISE_EXCEPTION;
28 return FALSE;
29
30 END GetExpRptCCTrxnCategoryCursor;
31
32 --------------------------------------------------------------------------------
33 FUNCTION GetCreditCardTrxnCursor(
34 p_user_id IN cards_employeeID,
35 p_reimb_curr_code IN ccTrxn_billedCurrCode,
36 p_card_prog_id IN cardProgs_cardProgID,
37 p_card_id IN cards_cardId,
38 p_paymentDueFrom IN varchar2,
39 p_cc_trxn_cursor OUT NOCOPY CreditCardTrxnCursor
40 ) RETURN BOOLEAN IS
41 --------------------------------------------------------------------------------
42 BEGIN
43 -- Added condition for paymentDueFrom so that Corresponsding TRXNS
44 -- will be fetched if by any chance multiple payments exists.
45
46 OPEN p_cc_trxn_cursor FOR
47 Select trx_id,
48 transaction_date,
49 folio_type, --shuh
50 merchant_name1,
51 merchant_city,
52 merchant_province_state,
53 billed_amount,
54 posted_currency_code,
55 transaction_amount,
56 cc.card_id,
57 nvl(cc.category, c_business)
58 FROM ap_credit_card_trxns cc,
59 ap_cards card
60 WHERE cc.validate_code = 'Y'
61 AND cc.payment_flag <> 'Y'
62 AND cc.billed_amount is not null
63 AND nvl(cc.expensed_amount,0) = 0
64 AND nvl(cc.category,'BUSINESS') <> 'DEACTIVATED'
65 AND cc.billed_currency_code = p_reimb_curr_code
66 AND cc.card_id = card.card_id
67 AND cc.card_program_id = card.card_program_id
68 AND card.employee_id = p_user_id
69 AND card.card_program_id = p_card_prog_id
70 AND card.card_id = p_card_id
71 AND cc.payment_due_from_code = p_paymentDueFrom
72 ORDER BY cc.transaction_date;
73
74 RETURN TRUE;
75
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 RETURN FALSE;
79
80 WHEN OTHERS THEN
81 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCreditCardTrxnCursor' );
82 APP_EXCEPTION.RAISE_EXCEPTION;
83 return FALSE;
84
85 END GetCreditCardTrxnCursor;
86
87 -------------------------------------------------------------------------------
88 FUNCTION GetCreditCardInfoCursor(
89 p_user_id IN cards_employeeID,
90 p_card_type IN cardProgs_cardTypeLookupCode,
91 p_cc_info_cursor OUT NOCOPY CreditCardInfoCursor
92 )
93 RETURN BOOLEAN IS
94 --------------------------------------------------------------------------------
95 BEGIN
96 OPEN p_cc_info_cursor FOR
97 SELECT DISTINCT
98 cp.card_program_id,
99 cp.card_program_name,
100 card.card_id,
101 trxn.payment_due_from_code
102 FROM ap_card_programs cp,
103 ap_cards card,
104 ap_credit_card_trxns trxn
105 WHERE card.employee_id = p_user_id
106 AND card.card_program_id = cp.card_program_id
107 AND cp.card_type_lookup_code = p_card_type
108 AND trxn.CARD_PROGRAM_ID = card.CARD_PROGRAM_ID
109 AND trxn.CARD_ID = card.CARD_ID
110 UNION
111 SELECT cp.card_program_id,
112 cp.card_program_name,
113 card.card_id,
114 cp.payment_due_from_code
115 FROM ap_card_programs cp,
116 ap_cards card
117 WHERE card.employee_id = p_user_id
118 AND card.card_program_id = cp.card_program_id
119 AND cp.card_type_lookup_code = p_card_type;
120
121 RETURN TRUE;
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 RETURN FALSE;
126
127 WHEN OTHERS THEN
128 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCreditCardInfoCursor' );
129 APP_EXCEPTION.RAISE_EXCEPTION;
130 return FALSE;
131
132 END GetCreditCardInfoCursor;
133
134 --------------------------------------------------------------------------------
135 FUNCTION GetUnpaidCreditCardTrxnCursor(
136 p_unpaid_ccTrxn_cursor OUT NOCOPY UnpaidCreditCardTrxnCursor,
137 p_card_prog_id IN ccTrxn_cardProgID,
138 p_payment_due_code IN VARCHAR2,
139 p_start_date IN DATE,
140 p_end_date IN DATE
141 ) RETURN BOOLEAN IS
142 --------------------------------------------------------------------------------
143 BEGIN
144 OPEN p_unpaid_ccTrxn_cursor FOR
145 SELECT DISTINCT
146 TRX_ID,
147 TRANSACTION_DATE, --3028505
148 BILLED_AMOUNT,
149 cc.CARD_ID THE_CARD_ID,
150 emp.full_name,
151 emp.person_id
152 FROM AP_CREDIT_CARD_TRXNS cc,
153 AP_CARDS card,
154 per_people_x emp
155 WHERE cc.CARD_PROGRAM_ID = p_card_prog_id
156 AND cc.VALIDATE_CODE = 'Y'
157 AND cc.payment_flag <> 'Y'
158 AND cc.COMPANY_PREPAID_INVOICE_ID IS NULL
159 AND cc.BILLED_AMOUNT IS NOT NULL
160 AND cc.CARD_ID = card.CARD_ID
161 AND (nvl(cc.billed_date, cc.posted_date) BETWEEN
162 nvl(p_start_date, nvl(cc.billed_date, cc.posted_date) - 1) AND
163 nvl(p_end_date, nvl(cc.billed_date, cc.posted_date) + 1)
164 )
165 AND cc.payment_due_from_code='COMPANY'
166 AND card.employee_id = emp.person_id
167 AND nvl(cc.category,'BUSINESS') <> 'DEACTIVATED'
168 ORDER BY
169 cc.transaction_date; --3028505
170
171 RETURN TRUE;
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 RETURN FALSE;
176
177 WHEN OTHERS THEN
178 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetUnpaidCreditCardTrxnCursor' );
179 APP_EXCEPTION.RAISE_EXCEPTION;
180 return FALSE;
181 END GetUnpaidCreditCardTrxnCursor;
182
183 ---------------------------------------------------------------------------
184 FUNCTION GetDisputedCcardTrxnCursor(
185 p_cardProgramId IN ccTrxn_cardProgID,
186 p_minimumAmount IN ccTrxn_billedAmount,
187 p_employeeId IN cards_employeeID,
188 p_billedStartDate IN ccTrxn_billedDate,
189 p_billedEndDate IN ccTrxn_billedDate,
190 p_disputed_cursor OUT NOCOPY DisputedCCTrxnCursor
191 ) RETURN BOOLEAN IS
192 ---------------------------------------------------------------------------
193 BEGIN
194 OPEN p_disputed_cursor FOR
195 SELECT transaction_date, merchant_name1, billed_amount, billed_currency_code
196 FROM
197 ap_credit_card_trxns cct,
198 ap_cards_all ac
199 WHERE
200 cct.card_program_id = p_cardProgramId
201 and cct.validate_code = 'Y'
202 and cct.payment_flag <> 'Y'
203 and nvl(cct.expensed_amount,0) = 0
204 and nvl(cct.category,c_business) = 'DISPUTED'
205 and ac.card_program_id = cct.card_program_id
206 and ac.card_id = cct.card_id
207 and nvl(cct.billed_date, cct.posted_date) between
208 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
209 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
210 and cct.billed_amount > p_minimumAmount
211 and ac.employee_id = p_employeeId
212 order by cct.transaction_date;
213
214 RETURN TRUE;
215
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 RETURN FALSE;
219
220 WHEN OTHERS THEN
221 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDisputedCcardTrxnCursor' );
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 return FALSE;
224
225 END GetDisputedCcardTrxnCursor;
226
227 ---------------------------------------------------------------------------
228 FUNCTION GetUnsubmittedCcardTrxnCursor(
229 p_cardProgramId IN ccTrxn_cardProgID,
230 p_employeeId IN cards_employeeID,
231 p_billedStartDate IN ccTrxn_billedDate,
232 p_billedEndDate IN ccTrxn_billedDate,
233 p_minimumAmount IN ccTrxn_billedAmount,
234 p_chargeType IN VARCHAR2,
235 p_unsubmitted_cursor OUT NOCOPY UnsubmittedCCTrxnCursor
236 ) RETURN BOOLEAN IS
237 ---------------------------------------------------------------------------
238 BEGIN
239 -- 3130923 remove ap_expense_report_lines from the 1st select
240 -- join erh.report_header_id = cct.report_header_id
241 OPEN p_unsubmitted_cursor FOR
242 SELECT distinct transaction_date,
243 merchant_name1,
244 billed_amount,
245 billed_currency_code,
246 erh.invoice_num,
247 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
248 (erh.source,erh.workflow_approved_flag,
249 erh.report_header_id), --2615448
250 cct.trx_id -- Bug 3241358
251 FROM
252 ap_credit_card_trxns cct,
253 ap_cards ac,
254 ap_expense_report_headers erh
255 WHERE
256 cct.card_program_id = p_cardProgramId
257 and cct.validate_code = 'Y'
258 and cct.payment_flag <> 'Y'
259 and cct.expensed_amount <> 0
260 and nvl(cct.category,c_business) NOT IN
261 ( 'DISPUTED', 'PERSONAL' , 'MATCHED' ,'CREDIT','DEACTIVATED') -- 3234232 , --3307864
262 and ac.card_program_id = cct.card_program_id
263 and ac.card_id = cct.card_id
264 and nvl(cct.billed_date, cct.posted_date) between
265 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
266 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
267 and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
268 and erh.report_header_id = cct.report_header_id --3130923
269 and ac.employee_id = erh.employee_id --3130923
270 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
271 (erh.source,erh.workflow_approved_flag,
272 erh.report_header_id) in
273 ('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED', 'INPROGRESS') --2615448
274 and ac.employee_id = p_employeeId
275 and rownum < 41
276 UNION ALL
277 SELECT transaction_date,
278 merchant_name1,
279 billed_amount,
280 billed_currency_code,
281 null,
282 'UNUSED',
283 cct.trx_id -- Bug 3241358
284 FROM
285 ap_credit_card_trxns cct,
286 ap_cards ac
287 WHERE
288 cct.card_program_id = p_cardProgramId
289 and cct.validate_code = 'Y'
290 and cct.payment_flag <> 'Y'
291 and nvl(cct.expensed_amount,0) = 0
292 and nvl(cct.category,c_business) NOT IN ('DISPUTED','MATCHED','CREDIT','DEACTIVATED') --Bug 3307864
293 and cct.report_header_id is NULL
294 and ac.card_program_id = cct.card_program_id
295 and ac.card_id = cct.card_id
296 and nvl(cct.billed_date, cct.posted_date) between
297 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
298 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
299 and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
300 and ac.employee_id = p_employeeId
301 and rownum < 41
302 and p_chargeType = 'UNUSED'
303 UNION ALL
304 SELECT distinct transaction_date,
305 merchant_name1,
306 billed_amount,
307 billed_currency_code,
308 erh.invoice_num,
309 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
310 (erh.source,erh.workflow_approved_flag,
311 erh.report_header_id), --2615448
312 cct.trx_id -- Bug 3241358
313 FROM
314 ap_credit_card_trxns cct,
315 ap_cards ac,
316 ap_expense_report_headers erh
317 WHERE
318 cct.card_program_id = p_cardProgramId
319 and cct.validate_code = 'Y'
320 and cct.payment_flag <> 'Y'
321 and cct.expensed_amount <> 0
322 and nvl(cct.category,c_business) = 'PERSONAL'
323 and ac.card_program_id = cct.card_program_id
324 and ac.card_id = cct.card_id
325 and nvl(cct.billed_date, cct.posted_date) between
326 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
327 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
328 and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
329 and erh.report_header_id = cct.report_header_id
330 and ac.employee_id = erh.employee_id --3130923
331 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
332 (erh.source,erh.workflow_approved_flag,
333 erh.report_header_id) in
334 ('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED', 'INPROGRESS') --2615448
335 and ac.employee_id = p_employeeId
336 and rownum < 41;
337 --order by cct.transaction_date;
338
339 RETURN TRUE;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 RETURN FALSE;
344
345 WHEN OTHERS THEN
346 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetUnsubmittedCcardTrxnCursor' );
347 APP_EXCEPTION.RAISE_EXCEPTION;
348 return FALSE;
349
350 END GetUnsubmittedCcardTrxnCursor;
351
352 ---------------------------------------------------------------------------
353 FUNCTION GetTotalUnsubmittedCCCursor(
354 p_cardProgramId IN ccTrxn_cardProgID,
355 p_employeeId IN cards_employeeID,
356 p_billedStartDate IN ccTrxn_billedDate,
357 p_billedEndDate IN ccTrxn_billedDate,
358 p_minimumAmount IN ccTrxn_billedAmount,
359 p_chargeType IN VARCHAR2,
360 p_total_cursor OUT NOCOPY UnsubmittedCCTrxnCursor
361 ) RETURN BOOLEAN IS
362 ---------------------------------------------------------------------------
363 BEGIN
364 -- 3130923 remove ap_expense_report_lines from the 1st select
365 -- join erh.report_header_id = cct.report_header_id
366 OPEN p_total_cursor FOR
367 SELECT distinct transaction_date,
368 merchant_name1,
369 billed_amount,
370 billed_currency_code,
371 erh.invoice_num,
372 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
373 (erh.source,erh.workflow_approved_flag,
374 erh.report_header_id), --2615448
375 cct.trx_id -- Bug 3241358
376 FROM
377 ap_credit_card_trxns cct,
378 ap_cards ac,
379 ap_expense_report_headers erh
380 WHERE
381 cct.card_program_id = p_cardProgramId
382 and cct.validate_code = 'Y'
383 and cct.payment_flag <> 'Y'
384 and cct.expensed_amount <> 0
385 and nvl(cct.category,c_business) NOT IN
386 ('DISPUTED','CREDIT', 'MATCHED','PERSONAL','DEACTIVATED') --Bug 3307864
387 and ac.card_program_id = cct.card_program_id
388 and ac.card_id = cct.card_id
389 and nvl(cct.billed_date, cct.posted_date) between
390 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
391 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
392 and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
393 and erh.report_header_id = cct.report_header_id --3130923
394 and ac.employee_id = erh.employee_id --3130923
395 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
396 erh.report_header_id) in
397 ('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED', 'INPROGRESS') --2615448
398 and ac.employee_id = p_employeeId
399 UNION ALL
400 SELECT transaction_date,
401 merchant_name1,
402 billed_amount,
403 billed_currency_code,
404 null,
405 'UNUSED',
406 cct.trx_id -- Bug 3241358
407 FROM
408 ap_credit_card_trxns cct,
409 ap_cards ac
410 WHERE
411 cct.card_program_id = p_cardProgramId
412 and cct.validate_code = 'Y'
413 and cct.payment_flag <> 'Y'
414 and nvl(cct.expensed_amount,0) = 0
415 and nvl(cct.category,c_business) NOT IN ( 'DISPUTED','CREDIT','MATCHED','DEACTIVATED') --Bug 3307864
416 and cct.report_header_id is NULL
417 and ac.card_program_id = cct.card_program_id
418 and ac.card_id = cct.card_id
419 and nvl(cct.billed_date, cct.posted_date) between
420 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
421 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
422 and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
423 and ac.employee_id = p_employeeId
424 and p_chargeType = 'UNUSED'
425 UNION ALL
426 SELECT distinct transaction_date,
427 merchant_name1,
428 billed_amount,
429 billed_currency_code,
430 erh.invoice_num,
431 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
432 (erh.source,erh.workflow_approved_flag,
433 erh.report_header_id), --2615448
434 cct.trx_id -- Bug 3241358
435 FROM
436 ap_credit_card_trxns cct,
437 ap_cards ac,
438 ap_expense_report_headers erh
439 WHERE
440 cct.card_program_id = p_cardProgramId
441 and cct.validate_code = 'Y'
442 and cct.payment_flag <> 'Y'
443 and cct.expensed_amount <> 0
444 and nvl(cct.category,c_business) = 'PERSONAL'
445 and ac.card_program_id = cct.card_program_id
446 and ac.card_id = cct.card_id
447 and nvl(cct.billed_date, cct.posted_date) between
448 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
449 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
450 and abs(cct.billed_amount) > nvl(p_minimumAmount,0)
451 and erh.report_header_id = cct.report_header_id
452 and ac.employee_id = erh.employee_id --3130923
453 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
454 erh.report_header_id) in
455 ('EMPAPPR', 'RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED', 'INPROGRESS') --2615448
456 and ac.employee_id = p_employeeId;
457 --order by cct.transaction_date;
458
459 RETURN TRUE;
460
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 RETURN FALSE;
464
465 WHEN OTHERS THEN
466 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTotalUnsubmittedCCCursor' );
467 APP_EXCEPTION.RAISE_EXCEPTION;
468 return FALSE;
469
470 END GetTotalUnsubmittedCCCursor;
471
472
473 ---------------------------------------------------------------------------
474 FUNCTION GetDunningCcardTrxnCursor(
475 p_cardProgramId IN ccTrxn_cardProgID,
476 p_employeeId IN cards_employeeID,
477 p_min_bucket IN NUMBER,
478 p_max_bucket IN NUMBER,
479 p_dunning_cursor OUT NOCOPY DunningCCTrxnCursor
480 ) RETURN BOOLEAN IS
481 ---------------------------------------------------------------------------
482 BEGIN
483 /*Bug 2625495: Added the second UNION for personal Credit Card
484 Expenses.For testing we need to comment out
485 row num checking.
486 */
487 OPEN p_dunning_cursor FOR
488 SELECT distinct transaction_date,
489 merchant_name1,
490 billed_amount,
491 billed_currency_code,
492 cct.posted_currency_code, --3339380
493 erh.invoice_num,
494 NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
495 (erh.source,erh.workflow_approved_flag,
496 erh.report_header_id),'UNUSED'), --2615505
497 nvl(cct.billed_date, cct.posted_date) billed_date,
498 cct.posted_date, --Notification Esc
499 cct.transaction_amount,--Notification Esc
500 AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state), --Notification Esc
501 cct.trx_id -- Bug 3241358
502 FROM
503 ap_credit_card_trxns cct,
504 ap_cards ac,
505 ap_expense_report_headers erh
506 WHERE
507 cct.card_program_id = p_cardProgramId
508 and cct.validate_code = 'Y'
509 and cct.payment_flag <> 'Y'
510 and nvl(cct.category,c_business) NOT IN ( 'DISPUTED','PERSONAL' ,'MATCHED','CREDIT','DEACTIVATED')
511 and cct.expensed_amount <> 0
512 and ac.card_program_id = cct.card_program_id
513 and ac.card_id = cct.card_id
514 and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
515 and erh.report_header_id = cct.report_header_id
516 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
517 erh.report_header_id)
518 in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN', 'INPROGRESS')
519 and ac.employee_id = p_employeeId
520 and rownum < 41
521 --order by cct.transaction_date;
522 UNION ALL
523 SELECT distinct transaction_date,
524 merchant_name1,
525 billed_amount,
526 billed_currency_code,
527 cct.posted_currency_code, --3339380
528 erh.invoice_num,
529 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
530 (erh.source,erh.workflow_approved_flag,
531 erh.report_header_id),
532 nvl(cct.billed_date, cct.posted_date) billed_date,
533 cct.posted_date, --Notification Esc
534 cct.transaction_amount,--Notification Esc
535 AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state), --Notification Esc
536 cct.trx_id -- Bug 3241358
537 FROM
538 ap_credit_card_trxns cct,
539 ap_cards ac,
540 ap_expense_report_headers erh
541 WHERE
542 cct.card_program_id = p_cardProgramId
543 and cct.validate_code = 'Y'
544 and cct.payment_flag <> 'Y'
545 and nvl(cct.category,c_business) = 'PERSONAL'
546 and cct.expensed_amount <> 0
547 and ac.card_program_id = cct.card_program_id
548 and ac.card_id = cct.card_id
549 and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
550 and erh.report_header_id = cct.report_header_id
551 and NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
552 erh.report_header_id),'UNUSED')
553 in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN', 'INPROGRESS')
554 and ac.employee_id = p_employeeId
555 and rownum < 41
556 UNION ALL
557 SELECT distinct transaction_date,
558 merchant_name1,
559 billed_amount,
560 billed_currency_code,
561 cct.posted_currency_code, --3339380
562 null,
563 'UNUSED',
564 nvl(cct.billed_date, cct.posted_date) billed_date,
565 cct.posted_date, --Notification Esc
566 cct.transaction_amount,--Notification Esc
567 AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state), --Notification Esc
568 cct.trx_id -- Bug 3241358
569 FROM
570 ap_credit_card_trxns cct,
571 ap_cards ac
572 WHERE
573 cct.card_program_id = p_cardProgramId
574 and cct.validate_code = 'Y'
575 and cct.payment_flag <> 'Y'
576 and nvl(cct.expensed_amount,0) = 0
577 and nvl(cct.category,'BUSINESS') <> 'DEACTIVATED'
578 and cct.report_header_id is null
579 and ac.card_program_id = cct.card_program_id
580 and ac.card_id = cct.card_id
581 and trunc(sysdate) - nvl(cct.posted_date,cct.billed_date) between p_min_bucket and p_max_bucket
582 and ac.employee_id = p_employeeId
583 and rownum < 41;
584
585
586 RETURN TRUE;
587
588 EXCEPTION
589 WHEN NO_DATA_FOUND THEN
590 RETURN FALSE;
591
592 WHEN OTHERS THEN
593 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDunningCcardTrxnCursor' );
594 APP_EXCEPTION.RAISE_EXCEPTION;
595 return FALSE;
596
597 END GetDunningCcardTrxnCursor;
598
599
600 ---------------------------------------------------------------------------
601 FUNCTION GetTotalCcardTrxnCursor(
602 p_cardProgramId IN ccTrxn_cardProgID,
603 p_employeeId IN cards_employeeID,
604 p_min_bucket IN NUMBER,
605 p_max_bucket IN NUMBER,
606 p_total_cursor OUT NOCOPY TotalCCTrxnCursor
607 ) RETURN BOOLEAN IS
608 ---------------------------------------------------------------------------
609 BEGIN
610
611 OPEN p_total_cursor FOR
612 SELECT distinct transaction_date,
613 merchant_name1,
614 billed_amount,
615 billed_currency_code,
616 erh.invoice_num,
617 NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
618 (erh.source,erh.workflow_approved_flag,
619 erh.report_header_id),'UNUSED'), --2615505
620 nvl(cct.billed_date, cct.posted_date) billed_date,
621 cct.trx_id -- Bug 3241358
622 FROM
623 ap_credit_card_trxns cct,
624 ap_cards ac,
625 ap_expense_report_headers erh
626 WHERE
627 cct.card_program_id = p_cardProgramId
628 and cct.validate_code = 'Y'
629 and cct.payment_flag <> 'Y'
630 and nvl(cct.category,c_business) NOT IN
631 ('DISPUTED' , 'CREDIT' , 'MATCHED','PERSONAL','DEACTIVATED') --Bug 3307864
632 and ac.card_program_id = cct.card_program_id
633 and ac.card_id = cct.card_id
634 and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
635 and erh.report_header_id = cct.report_header_id
636 and cct.expensed_amount <> 0
637 and erh.report_header_id = cct.report_header_id
638 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
639 erh.report_header_id)
640 in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN', 'INPROGRESS')
641 and ac.employee_id = p_employeeId
642 UNION ALL
643 SELECT DISTINCT transaction_date,
644 merchant_name1,
645 billed_amount,
646 billed_currency_code,
647 erh.invoice_num,
648 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode
649 (erh.source,erh.workflow_approved_flag,
650 erh.report_header_id),
651 nvl(cct.billed_date, cct.posted_date) billed_date,
652 cct.trx_id
653 FROM
654 ap_credit_card_trxns cct,
655 ap_cards ac,
656 ap_expense_report_headers erh
657 WHERE
658 cct.card_program_id = p_cardProgramId
659 and cct.validate_code = 'Y'
660 and cct.payment_flag <> 'Y'
661 and nvl(cct.category,c_business) = 'PERSONAL'
662 and cct.expensed_amount <> 0
663 and ac.card_program_id = cct.card_program_id
664 and ac.card_id = cct.card_id
665 and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
666 and erh.report_header_id = cct.report_header_id
667 and NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,
668 erh.report_header_id),'UNUSED')
669 in ('SAVED','REJECTED', 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN', 'INPROGRESS')
670 and ac.employee_id = p_employeeId
671 UNION ALL
672 SELECT distinct transaction_date,
673 merchant_name1,
674 billed_amount,
675 billed_currency_code,
676 null,
677 'UNUSED',
678 nvl(cct.billed_date, cct.posted_date) billed_date,
679 cct.trx_id -- Bug 3241358
680 FROM
681 ap_credit_card_trxns cct,
682 ap_cards ac
683 WHERE
684 cct.card_program_id = p_cardProgramId
685 and cct.validate_code = 'Y'
686 and cct.payment_flag <> 'Y'
687 and nvl(cct.expensed_amount,0) = 0
688 and nvl(cct.category,'BUSINESS') <> 'DEACTIVATED'
689 and cct.report_header_id is null
690 and ac.card_program_id = cct.card_program_id
691 and ac.card_id = cct.card_id
692 and trunc(sysdate) - nvl(cct.billed_date, cct.posted_date) between p_min_bucket and p_max_bucket
693 and ac.employee_id = p_employeeId;
694
695 RETURN TRUE;
696
697 EXCEPTION
698 WHEN NO_DATA_FOUND THEN
699 RETURN FALSE;
700
701 WHEN OTHERS THEN
702 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTotalCcardTrxnCursor' );
703 APP_EXCEPTION.RAISE_EXCEPTION;
704 return FALSE;
705
706 END GetTotalCcardTrxnCursor;
707
708
709 -------------------------------------------------------------------
710 FUNCTION GetCardProgramCurrencyCode(
711 p_card_prog_id IN cardProgs_cardProgID,
712 p_curr_code OUT NOCOPY cardProgs_cardProgCurrCode
713 ) RETURN BOOLEAN IS
714 -------------------------------------------------------------------
715 BEGIN
716
717 SELECT card_program_currency_code
718 INTO p_curr_code
719 FROM ap_card_programs
720 WHERE card_program_id = p_card_prog_id;
721
722 RETURN TRUE;
723
724 EXCEPTION
725 WHEN NO_DATA_FOUND THEN
726 RETURN FALSE;
727
728 WHEN OTHERS THEN
729 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCardProgramCurrencyCode' );
730 APP_EXCEPTION.RAISE_EXCEPTION;
731 return FALSE;
732
733 END GetCardProgramCurrencyCode;
734
735
736 -------------------------------------------------------------------
737 FUNCTION GetCCTrxnCategory(
738 p_trx_id IN ccTrxn_trxID,
739 p_category OUT NOCOPY ccTrxn_category )
740 RETURN BOOLEAN IS
741 -------------------------------------------------------------------
742 BEGIN
743 SELECT category
744 INTO p_category
745 FROM ap_credit_card_trxns
746 WHERE trx_id = p_trx_id;
747
748 RETURN TRUE;
749
750 EXCEPTION
751 WHEN NO_DATA_FOUND THEN
752 RETURN FALSE;
753 WHEN OTHERS THEN
754 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCCTrxnCategory' );
755 APP_EXCEPTION.RAISE_EXCEPTION;
756 return FALSE;
757
758
759 END GetCCTrxnCategory;
760
761
762 -------------------------------------------------------------------
763 FUNCTION GetCompPrepaidInvID(
764 p_trx_id IN ccTrxn_trxID,
765 p_prepaid_invoice_id OUT NOCOPY AP_EXPENSE_REPORT_LINES.company_prepaid_invoice_id%TYPE)
766 RETURN BOOLEAN IS
767 -------------------------------------------------------------------
768 BEGIN
769 p_prepaid_invoice_id := NULL;
770
771 IF (p_trx_id IS NOT NULL) THEN
772 SELECT company_prepaid_invoice_id
773 INTO p_prepaid_invoice_id
774 FROM ap_credit_card_trxns
775 WHERE trx_id = p_trx_id;
776 END IF;
777
778 RETURN TRUE;
779
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 RETURN FALSE;
783 WHEN OTHERS THEN
784 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCompPrepaidInvID' );
785 APP_EXCEPTION.RAISE_EXCEPTION;
786 return FALSE;
787
788
789 END GetCompPrepaidInvID;
790
791
792 -------------------------------------------------------------------------------
793 FUNCTION GetExpensedAmt(
794 p_id IN ccTrxn_trxID,
795 p_amt OUT NOCOPY ccTrxn_expensedAmt )
796 RETURN BOOLEAN IS
797 --------------------------------------------------------------------------------
798 BEGIN
799 select expensed_amount
800 into p_amt
801 from ap_credit_card_trxns
802 where trx_id = p_id;
803
804 return TRUE;
805
806 EXCEPTION
807 WHEN NO_DATA_FOUND THEN
808 RETURN FALSE;
809
810 WHEN OTHERS THEN
811 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetExpensedAmt' );
812 APP_EXCEPTION.RAISE_EXCEPTION;
813 return FALSE;
814
815 END GetExpensedAmt;
816
817
818 --------------------------------------------------------------------------------
819 FUNCTION GetCardProgramInfo(
820 p_card_prog_id IN cardProgs_cardProgID,
821 p_vendor_id OUT NOCOPY cardProgs_vendorID,
822 p_vendor_site_id OUT NOCOPY cardProgs_vendorSiteID,
823 p_invoice_curr_code OUT NOCOPY cardProgs_cardProgCurrCode
824 ) RETURN BOOLEAN IS
825 --------------------------------------------------------------------------------
826 BEGIN
827 SELECT VENDOR_ID,
828 VENDOR_SITE_ID,
829 CARD_PROGRAM_CURRENCY_CODE
830 INTO p_vendor_id,
831 p_vendor_site_id,
832 p_invoice_curr_code
833 FROM AP_CARD_PROGRAMS
834 WHERE CARD_PROGRAM_ID = p_card_prog_id;
835
836 RETURN TRUE;
837
838 EXCEPTION
839 WHEN NO_DATA_FOUND THEN
840 RETURN FALSE;
841 WHEN OTHERS THEN
842 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCardProgramInfo' );
843 APP_EXCEPTION.RAISE_EXCEPTION;
844 return FALSE;
845 END GetCardProgramInfo;
846
847
848 -------------------------------------------------------------------
849 FUNCTION GetVendorIDs(
850 p_report_header_id IN ccTrxn_headerID,
851 p_vendor_id OUT NOCOPY cardProgs_vendorID,
852 p_vendor_site_id OUT NOCOPY cardProgs_vendorSiteID
853 ) RETURN BOOLEAN IS
854 -------------------------------------------------------------------
855 BEGIN
856 SELECT distinct acp.vendor_id,
857 acp.vendor_site_id
858 INTO p_vendor_id,
859 p_vendor_site_id
860 FROM ap_credit_card_trxns cc,
861 ap_card_programs acp
862 WHERE cc.report_header_id = p_report_header_id
863 AND cc.card_program_id = acp.card_program_id;
864
865 RETURN TRUE;
866
867 EXCEPTION
868 WHEN NO_DATA_FOUND THEN
869 RETURN FALSE;
870
871 WHEN OTHERS THEN
872 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorIDs' );
873 APP_EXCEPTION.RAISE_EXCEPTION;
874 return FALSE;
875
876 END GetVendorIDs;
877
878 -------------------------------------------------------------------
879 FUNCTION GetCardProgramName(
880 p_cardProgramID IN cardProgs_cardProgID,
881 p_cardProgramName OUT NOCOPY cardProgs_cardProgName)
882 RETURN BOOLEAN IS
883 -------------------------------------------------------------------
884 BEGIN
885 select card_program_name
886 into p_cardProgramName
887 from ap_card_programs
888 where card_program_id = p_cardProgramID;
889
890 RETURN TRUE;
891
892 EXCEPTION
893 WHEN NO_DATA_FOUND THEN
894 RETURN FALSE;
895
896 WHEN OTHERS THEN
897 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCardProgramName' );
898 APP_EXCEPTION.RAISE_EXCEPTION;
899 return FALSE;
900
901 END GetCardProgramName;
902
903
904 -------------------------------------------------------------------
905 FUNCTION GetCardProgramID(
906 p_cardProgramName IN cardProgs_cardProgName,
907 p_cardProgramID OUT NOCOPY cardProgs_cardProgID)
908 RETURN BOOLEAN IS
909 -------------------------------------------------------------------
910 BEGIN
911 select card_program_id
912 into p_cardProgramID
913 from ap_card_programs
914 where card_program_name = p_cardProgramName;
915
916 RETURN TRUE;
917
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920 RETURN FALSE;
921
922 WHEN OTHERS THEN
923 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCardProgramID' );
924 APP_EXCEPTION.RAISE_EXCEPTION;
925 return FALSE;
926
927 END GetCardProgramID;
928
929
930
931 --------------------------------------------------------------------------------
932 FUNCTION CompanyHasTravelCardProgram(
933 p_companyHasCardProgram OUT NOCOPY VARCHAR2
934 ) RETURN BOOLEAN IS
935 --------------------------------------------------------------------------------
936 BEGIN
937
938 -- Determine if company has Card Program
939 select 'Y'
940 into p_companyHasCardProgram
941 from ap_card_programs
942 where card_type_lookup_code = 'TRAVEL';
943
944 RETURN TRUE;
945
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 RETURN FALSE;
949
950 WHEN OTHERS THEN
951 AP_WEB_DB_UTIL_PKG.RaiseException( 'CompanyHasTravelCardProgram' );
952 APP_EXCEPTION.RAISE_EXCEPTION;
953 return FALSE;
954
955 END CompanyHasTravelCardProgram;
956
957
958 --------------------------------------------------------------------------------
959 FUNCTION UserHasCreditCard(
960 p_userId IN cards_employeeID,
961 p_userHasCreditCard OUT NOCOPY VARCHAR2
962 ) RETURN BOOLEAN IS
963 --------------------------------------------------------------------------------
964 BEGIN
965
966 -- Determine if the user has Credit Card
967 SELECT 'Y'
968 INTO p_userHasCreditCard
969 FROM AP_CARD_PROGRAMS_all CP ,
970 AP_CARDS_all CARD ,
971 AP_LOOKUP_CODES ALC,
972 ( SELECT CARD_PROGRAM_ID,CARD_ID, PAYMENT_DUE_FROM_CODE
973 FROM AP_CREDIT_CARD_TRXNS_all
974 WHERE VALIDATE_CODE = 'Y'
975 AND PAYMENT_FLAG <> 'Y'
976 AND BILLED_AMOUNT IS NOT NULL
977 AND NVL ( CATEGORY , 'BUSINESS' ) <> 'DEACTIVATED' ) TRXN
978 WHERE CARD.EMPLOYEE_ID = p_userId
979 AND CARD.CARD_PROGRAM_ID = CP.CARD_PROGRAM_ID
980 AND CP.CARD_TYPE_LOOKUP_CODE = 'TRAVEL'
981 AND ALC.LOOKUP_TYPE = 'PAYMENT_DUE_FROM'
982 AND ( ALC.LOOKUP_CODE = CP.PAYMENT_DUE_FROM_CODE
983 OR ALC.LOOKUP_CODE = TRXN.PAYMENT_DUE_FROM_CODE)
984 AND TRUNC ( SYSDATE ) BETWEEN TRUNC ( NVL (ALC.START_DATE_ACTIVE , SYSDATE ) )
985 AND TRUNC ( NVL ( ALC.INACTIVE_DATE , SYSDATE ) )
986 AND TRXN.CARD_PROGRAM_ID(+) = CARD.CARD_PROGRAM_ID
987 AND TRXN.CARD_ID(+) = CARD.CARD_ID
988 AND ROWNUM = 1;
989
990 RETURN TRUE;
991
992 EXCEPTION
993 WHEN NO_DATA_FOUND THEN
994 RETURN FALSE;
995
996 WHEN OTHERS THEN
997 AP_WEB_DB_UTIL_PKG.RaiseException( 'UserHasCreditCard' );
998 APP_EXCEPTION.RAISE_EXCEPTION;
999 return FALSE;
1000
1001 END UserHasCreditCard;
1002
1003 -------------------------------------------------------------------
1004 FUNCTION SetCCTrxnReportHeaderID(
1005 p_report_header_id IN NUMBER,
1006 p_new_report_id IN NUMBER
1007 ) RETURN BOOLEAN IS
1008 -------------------------------------------------------------------
1009 BEGIN
1010 UPDATE ap_credit_card_trxns
1011 SET report_header_id = p_new_report_id
1012 WHERE report_header_id = p_report_header_id;
1013 -- Bug 2178676
1014 -- AND category <> c_personal;
1015
1016 return TRUE;
1017
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 RETURN FALSE;
1021
1022 WHEN OTHERS THEN
1023 AP_WEB_DB_UTIL_PKG.RaiseException( 'SetCCTrxnReportHeaderID' );
1024 APP_EXCEPTION.RAISE_EXCEPTION;
1025 return FALSE;
1026
1027 END SetCCTrxnReportHeaderID;
1028
1029 --------------------------------------------------------------------------------
1030 FUNCTION SetCCPolicyShortpaidReportID(
1031 p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
1032 p_new_expense_report_id IN ccTrxn_headerID )
1033 RETURN BOOLEAN IS
1034 --------------------------------------------------------------------------------
1035 BEGIN
1036 UPDATE ap_credit_card_trxns
1037 SET report_header_id = p_new_expense_report_id
1038 WHERE trx_id IN (SELECT credit_card_trx_id
1039 FROM ap_expense_report_lines
1040 WHERE report_header_id = p_new_expense_report_id
1041 AND nvl(policy_shortpay_flag,'N') = 'Y');
1042
1043 RETURN TRUE;
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 RETURN FALSE;
1047
1048 WHEN OTHERS THEN
1049 AP_WEB_DB_UTIL_PKG.RaiseException( 'SetCCPolicyShortPaidReportID' );
1050 APP_EXCEPTION.RAISE_EXCEPTION;
1051 return FALSE;
1052
1053 END SetCCPolicyShortpaidReportID;
1054
1055
1056 --------------------------------------------------------------------------------
1057 FUNCTION SetCCReceiptShortpaidReportID(
1058 p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
1059 p_new_expense_report_id IN ccTrxn_headerID
1060 ) RETURN BOOLEAN IS
1061 --------------------------------------------------------------------------------
1062 BEGIN
1063 UPDATE ap_credit_card_trxns
1064 SET report_header_id = p_new_expense_report_id
1065 WHERE trx_id IN (SELECT credit_card_trx_id
1066 FROM ap_expense_report_lines
1067 WHERE report_header_id = p_new_expense_report_id
1068 AND (receipt_required_flag = 'Y' OR image_receipt_required_flag = 'Y')
1069 AND nvl(receipt_verified_flag,'N') = 'N'
1070 AND nvl(policy_shortpay_flag, 'N') = 'N');
1071
1072 RETURN TRUE;
1073
1074 EXCEPTION
1075 WHEN NO_DATA_FOUND THEN
1076 RETURN FALSE;
1077
1078 WHEN OTHERS THEN
1079 AP_WEB_DB_UTIL_PKG.RaiseException( 'SetCCReceiptShortpaidReportID' );
1080 APP_EXCEPTION.RAISE_EXCEPTION;
1081 return FALSE;
1082
1083 END SetCCReceiptShortpaidReportID;
1084
1085
1086
1087 -------------------------------------------------------------------------------
1088 FUNCTION UpdateExpensedAmount(
1089 p_trxn_id IN ccTrxn_trxID,
1090 p_report_id IN ccTrxn_headerID,
1091 p_expensed_amount IN ccTrxn_expensedAmt
1092 ) RETURN BOOLEAN IS
1093 --------------------------------------------------------------------------------
1094 BEGIN
1095 UPDATE ap_credit_card_trxns
1096 SET expensed_amount = p_expensed_amount,
1097 report_header_id = p_report_id,
1098 category = null
1099 WHERE trx_id = p_trxn_id;
1100
1101 RETURN TRUE;
1102
1103 EXCEPTION
1104 WHEN NO_DATA_FOUND THEN
1105 RETURN FALSE;
1106
1107 WHEN OTHERS THEN
1108 AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateExpensedAmount' );
1109 APP_EXCEPTION.RAISE_EXCEPTION;
1110 return FALSE;
1111
1112 END UpdateExpensedAmount;
1113
1114 --------------------------------------------------------------------------------
1115 FUNCTION ResetCCLines(
1116 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE)
1117 RETURN BOOLEAN IS
1118 --------------------------------------------------------------------------------
1119 BEGIN
1120 UPDATE ap_credit_card_trxns cc
1121 SET expensed_amount = 0,
1122 report_header_id = null,
1123 category = null
1124 WHERE report_header_id = p_report_header_id;
1125 RETURN TRUE;
1126 EXCEPTION
1127 WHEN NO_DATA_FOUND THEN
1128 RETURN FALSE;
1129
1130 WHEN OTHERS THEN
1131 AP_WEB_DB_UTIL_PKG.RaiseException( 'ResetCCLines' );
1132 APP_EXCEPTION.RAISE_EXCEPTION;
1133 return FALSE;
1134
1135 END ResetCCLines;
1136
1137
1138 --------------------------------------------------------------------------------
1139 FUNCTION SetStatus(
1140 p_report_header_id IN ccTrxn_headerID,
1141 p_status IN ccTrxn_expenseStatus
1142 ) RETURN BOOLEAN IS
1143 --------------------------------------------------------------------------------
1144 BEGIN
1145 UPDATE ap_credit_card_trxns
1146 SET expense_status = p_status
1147 where report_header_id = p_report_header_id;
1148
1149 RETURN TRUE;
1150
1151 EXCEPTION
1152 WHEN NO_DATA_FOUND THEN
1153 RETURN FALSE;
1154
1155 WHEN OTHERS THEN
1156 AP_WEB_DB_UTIL_PKG.RaiseException( 'SetStatus' );
1157 APP_EXCEPTION.RAISE_EXCEPTION;
1158 return FALSE;
1159
1160 END SetStatus;
1161
1162
1163
1164 --------------------------------------------------------------------------------
1165 FUNCTION ResetCCMgrRejectedCCLines(
1166 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE
1167 ) RETURN BOOLEAN IS
1168 --------------------------------------------------------------------------------
1169 BEGIN
1170 UPDATE ap_credit_card_trxns
1171 SET expensed_amount = 0,
1172 report_header_id = NULL
1173 WHERE report_header_id IN (SELECT report_header_id
1174 FROM ap_expense_report_headers
1175 WHERE report_header_id = p_report_header_id
1176 AND workflow_approved_flag in
1177 (AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_REJECTED,
1178 AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_RETURNED,
1179 --ER 1552747 - withdraw expense report
1180 AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_WITHDRAW));
1181
1182 RETURN TRUE;
1183
1184 EXCEPTION
1185 WHEN NO_DATA_FOUND THEN
1186 RETURN FALSE;
1187
1188 WHEN OTHERS THEN
1189 AP_WEB_DB_UTIL_PKG.RaiseException( 'ResetCCMgrRejectedCCLines' );
1190 APP_EXCEPTION.RAISE_EXCEPTION;
1191 return FALSE;
1192
1193 END ResetCCMgrRejectedCCLines;
1194
1195
1196 --------------------------------------------------------------------------------
1197 FUNCTION ResetPersonalTrxns(
1198 p_reportID IN ccTrxn_headerID
1199 ) RETURN BOOLEAN IS
1200 --------------------------------------------------------------------------------
1201 BEGIN
1202 update ap_credit_card_trxns
1203 set expensed_amount = 0,
1204 report_header_id = null
1205 where (report_header_id = p_reportID)
1206 and (category = c_personal);
1207
1208 RETURN TRUE;
1209
1210 EXCEPTION
1211 WHEN NO_DATA_FOUND THEN
1212 RETURN FALSE;
1213
1214 WHEN OTHERS THEN
1215 AP_WEB_DB_UTIL_PKG.RaiseException( 'ResetPersonalTrxns' );
1216
1217 APP_EXCEPTION.RAISE_EXCEPTION;
1218 return FALSE;
1219 END ResetPersonalTrxns;
1220
1221 --------------------------------------------------------------------------------
1222 FUNCTION ResetMgrRejectPersonalTrxns(
1223 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE
1224 ) RETURN BOOLEAN IS
1225 --------------------------------------------------------------------------------
1226 BEGIN
1227
1228 UPDATE ap_credit_card_trxns cc
1229 SET expensed_amount = 0,
1230 report_header_id = null
1231 WHERE cc.report_header_id IN (SELECT report_header_id erh_headerID
1232 FROM ap_expense_report_headers
1233 WHERE report_header_id = p_report_header_id
1234 AND workflow_approved_flag in
1235 (AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_REJECTED,
1236 AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_RETURNED,
1237 --ER 1552747 - withdraw expense report
1238 AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_WITHDRAW))
1239 AND category = c_personal;
1240
1241 RETURN TRUE;
1242
1243 EXCEPTION
1244
1245 WHEN NO_DATA_FOUND THEN
1246 RETURN FALSE;
1247
1248 WHEN OTHERS THEN
1249 AP_WEB_DB_UTIL_PKG.RaiseException( 'ResetMgrRejectedPersonalTrxns' );
1250 APP_EXCEPTION.RAISE_EXCEPTION;
1251 return FALSE;
1252
1253 END ResetMgrRejectPersonalTrxns;
1254
1255
1256 --------------------------------------------------------------------------------
1257 FUNCTION SetCCTrxnInvoiceId(
1258 p_card_trxn_id IN ccTrxn_trxID,
1259 p_invoice_id IN ccTrxn_companyPrepaidInvID
1260 ) RETURN BOOLEAN IS
1261 --------------------------------------------------------------------------------
1262 BEGIN
1263 UPDATE ap_credit_card_trxns_all
1264 SET company_prepaid_invoice_id = p_invoice_id
1265 WHERE trx_id = p_card_trxn_id;
1266
1267 RETURN TRUE;
1268
1269 EXCEPTION
1270
1271 WHEN NO_DATA_FOUND THEN
1272 RETURN FALSE;
1273
1274 WHEN OTHERS THEN
1275 AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateInvoiceId' );
1276 APP_EXCEPTION.RAISE_EXCEPTION;
1277 return FALSE;
1278
1279 END SetCCTrxnInvoiceId;
1280
1281 --------------------------------------------------------------------------
1282 FUNCTION GetCCardTrxnInfoForTrxnId(
1283 p_trxn_id IN ccTrxn_trxID,
1284 p_trxn_info_rec OUT NOCOPY CCardTrxnInfoRec
1285 ) RETURN BOOLEAN IS
1286 ---------------------------------------------------------------------------
1287 BEGIN
1288 SELECT trx_id,
1289 transaction_date,
1290 folio_type, --shuh
1291 merchant_name1,
1292 merchant_city,
1293 merchant_province_state,
1294 billed_amount,
1295 posted_currency_code,
1296 transaction_amount,
1297 cc.card_id,
1298 nvl(cc.category, c_business),
1299 cc.card_program_id
1300 INTO p_trxn_info_rec.trxn_id, p_trxn_info_rec.trxn_date,
1301 p_trxn_info_rec.folio_type, p_trxn_info_rec.merchant_name,
1302 p_trxn_info_rec.merchant_city, p_trxn_info_rec.merchant_prov,
1303 p_trxn_info_rec.billed_amount, p_trxn_info_rec.posted_curr_code,
1304 p_trxn_info_rec.trxn_amount, p_trxn_info_rec.card_id,
1305 p_trxn_info_rec.category, p_trxn_info_rec.card_prog_id
1306 FROM ap_credit_card_trxns cc
1307 WHERE trx_id = p_trxn_id;
1308
1309 RETURN TRUE;
1310
1311 EXCEPTION
1312 WHEN NO_DATA_FOUND THEN
1313 RETURN FALSE;
1314
1315 WHEN OTHERS THEN
1316 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCCardTrxnInfoForTrxnId' );
1317 APP_EXCEPTION.RAISE_EXCEPTION;
1318 return FALSE;
1319
1320 END GetCCardTrxnInfoForTrxnId;
1321
1322 ------------------------------------------------------------------------------
1323 FUNCTION UpdateCCardCategory(
1324 p_trxn_id IN ccTrxn_trxID,
1325 p_category IN ccTrxn_category
1326 ) RETURN BOOLEAN IS
1327 ------------------------------------------------------------------------------
1328 BEGIN
1329 UPDATE ap_credit_card_trxns
1330 SET category = p_category
1331 WHERE trx_id = p_trxn_id;
1332
1333 RETURN TRUE;
1334
1335 EXCEPTION
1336 WHEN NO_DATA_FOUND THEN
1337 RETURN FALSE;
1338 WHEN OTHERS THEN
1339 AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateCCardCategory' );
1340
1341 APP_EXCEPTION.RAISE_EXCEPTION;
1342 return FALSE;
1343 END UpdateCCardCategory;
1344
1345 ------------------------------------------------------------------------------
1346 FUNCTION GetExpensedAmountForTrxnId(
1347 p_trxn_id IN ccTrxn_trxID,
1348 p_exp_amount OUT NOCOPY ccTrxn_expensedAmt
1349 ) RETURN BOOLEAN IS
1350 ------------------------------------------------------------------------------
1351 BEGIN
1352 SELECT expensed_amount
1353 INTO p_exp_amount
1354 FROM ap_credit_card_trxns
1355 WHERE trx_id = p_trxn_id;
1356
1357 RETURN TRUE;
1358
1359 EXCEPTION
1360 WHEN NO_DATA_FOUND THEN
1361 RETURN FALSE;
1362 WHEN OTHERS THEN
1363 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetExpensedAmountForTrxnId' );
1364 APP_EXCEPTION.RAISE_EXCEPTION;
1365 return FALSE;
1366 END GetExpensedAmountForTrxnId;
1367
1368 ------------------------------------------------------------------------------
1369 /*Written By :Amulya Mishra
1370 Purpose :To check if a card program has multiple payment Methods.
1371 */
1372 ------------------------------------------------------------------------------
1373 FUNCTION isMultPayments(
1374 p_cardProgramID IN cardProgs_cardProgID,
1375 p_card_id IN cards_cardId
1376 ) RETURN BOOLEAN IS
1377
1378 paymentMethodCount NUMBER;
1379 ------------------------------------------------------------------------------
1380 BEGIN
1381 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_CCARD_PKG', 'start isMultPayments');
1382 select count(distinct payment_due_from_code)
1383 into paymentMethodCount
1384 from ap_credit_card_trxns
1385 where card_program_id = p_cardProgramID
1386 and card_id = p_card_id;
1387
1388 IF (paymentMethodCount > 1) THEN
1389 RETURN TRUE;
1390 ELSE
1391 RETURN FALSE;
1392 END IF;
1393
1394 EXCEPTION
1395 WHEN NO_DATA_FOUND THEN
1396 RETURN FALSE;
1397 WHEN OTHERS THEN
1398 AP_WEB_DB_UTIL_PKG.RaiseException( 'isMultPayments');
1399 APP_EXCEPTION.RAISE_EXCEPTION;
1400 return FALSE;
1401 END isMultPayments;
1402 -------------------------------------------------------------------------------
1403 /*Written By :Amulya Mishra
1404 Purpose :Returns the UNIQUE PAYMENT_DUE_FROM_CODE from
1405 AP_CREDIT_CARD_TRXNS_ALL table.
1406 */
1407
1408 FUNCTION getPaymentDueCodeFromTrxn(
1409 p_trxn_id IN ccTrxn_trxID)
1410 RETURN VARCHAR2 IS
1411
1412 p_paymentDueCode VARCHAR2(30);
1413 -----------------------------------------------------------------------------
1414 BEGIN
1415
1416 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_CCARD_PKG', 'start getPaymentDueCodeFromTrxn');
1417
1418 SELECT DISTINCT payment_due_from_code
1419 INTO p_paymentDueCode
1420 FROM ap_credit_card_trxns trx
1421 WHERE trx.trx_id = p_trxn_id;
1422
1423 RETURN p_paymentDueCode;
1424
1425
1426 EXCEPTION
1427 WHEN NO_DATA_FOUND THEN
1428 RETURN null;
1429
1430 WHEN OTHERS THEN
1431 AP_WEB_DB_UTIL_PKG.RaiseException( 'getPaymentDueCodeFromTrxn' );
1432 APP_EXCEPTION.RAISE_EXCEPTION;
1433
1434 END getPaymentDueCodeFromTrxn;
1435 -----------------------------------------------------------------------------
1436 /*Written By :Amulya Mishra
1437 Purpose :Returns the INDEX of ExpLines array for which
1438 Credit Card Trxn Id is Not Null.
1439 */
1440
1441 -----------------------------------------------------------------------------
1442 FUNCTION getFirstLineWithCCTrxId(
1443 p_expLines IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
1444 p_personalReceipts IN AP_WEB_DFLEX_PKG.ExpReportLines_A)
1445 RETURN NUMBER IS
1446
1447 -----------------------------------------------------------------------------
1448 BEGIN
1449
1450 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_CCARD_PKG', 'start getFirstLineWithCCTrxId');
1451
1452 FOR i IN 1..p_expLines.Count LOOP
1453 IF p_expLines(i).cCardTrxnId IS NOT NULL THEN
1454 RETURN p_expLines(i).cCardTrxnId;
1455 END IF;
1456 END LOOP;
1457
1458 FOR i IN 1..p_personalReceipts.Count LOOP
1459 IF p_personalReceipts(i).cCardTrxnId IS NOT NULL THEN
1460 RETURN p_personalReceipts(i).cCardTrxnId;
1461 END IF;
1462 END LOOP;
1463
1464 RETURN 0;
1465
1466 END getFirstLineWithCCTrxId;
1467 -----------------------------------------------------------------------------
1468
1469 /*Written By : Ron Langi
1470 Purpose : To check if credit card is enabled for a user.
1471 Modified By: Amulya Mishra(Bug 3618604)
1472 To reuse the same function , if its called from BC4J
1473 then p_user_id will not be passed and will be determined
1474 locally.
1475 */
1476 ------------------------------------------------------------------------------
1477 FUNCTION isCreditCardEnabled(
1478 p_employee_id IN cards_employeeID,
1479 p_user_id IN NUMBER DEFAULT NULL)
1480 RETURN VARCHAR2 IS
1481 ------------------------------------------------------------------------------
1482
1483 l_debug_info VARCHAR2(1000);
1484 l_has VARCHAR2(1);
1485 l_cCardEnabled VARCHAR2(1);
1486 p_userId VARCHAR2(100);
1487 l_userId NUMBER;
1488
1489
1490 BEGIN
1491
1492
1493 IF p_user_id IS NULL THEN
1494 AP_WEB_OA_MAINFLOW_PKG.GetUserID(p_employee_id, p_userId);
1495 l_userId := to_number(p_userId);
1496 END IF;
1497
1498
1499 -------------------------------------------
1500 l_debug_info := 'Check if user is credit card enabled';
1501 -------------------------------------------
1502 l_cCardEnabled := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
1503 p_name => 'SSE_ENABLE_CREDIT_CARD',
1504 p_user_id => nvl(p_user_id,l_userId),
1505 p_resp_id => null,
1506 p_apps_id => null);
1507
1508 IF (AP_WEB_DB_CCARD_PKG.UserHasCreditCard(p_employee_id, l_has)) THEN
1509 if (AP_WEB_DB_HR_INT_PKG.IsPersonCwk(p_employee_id) = 'N' AND
1510 l_has = 'Y' AND l_cCardEnabled = 'Y') THEN
1511 return 'Y';
1512 end if;
1513 END IF;
1514 return 'N';
1515
1516 EXCEPTION
1517 WHEN NO_DATA_FOUND THEN
1518 RETURN 'N';
1519
1520 WHEN OTHERS THEN
1521 AP_WEB_DB_UTIL_PKG.RaiseException( 'isCreditCardEnabled', l_debug_info );
1522 APP_EXCEPTION.RAISE_EXCEPTION;
1523
1524 END isCreditCardEnabled;
1525
1526 /*Written By : Ron Langi
1527 Purpose : To get the prevent Cash CC Age Limit setting
1528 */
1529 ------------------------------------------------------------------------------
1530 FUNCTION getPreventCashCCAgeLimit
1531 RETURN NUMBER IS
1532 ------------------------------------------------------------------------------
1533
1534 l_debug_info VARCHAR2(1000);
1535 l_prevent_cash_cc_age_limit NUMBER;
1536
1537 BEGIN
1538
1539 -------------------------------------------
1540 l_debug_info := 'Get Prevent Cash CC Age Limit';
1541 -------------------------------------------
1542 select prevent_cash_cc_age_limit
1543 into l_prevent_cash_cc_age_limit
1544 from ap_expense_params;
1545
1546 return l_prevent_cash_cc_age_limit;
1547
1548 EXCEPTION
1549 WHEN NO_DATA_FOUND THEN
1550 RETURN null;
1551
1552 WHEN OTHERS THEN
1553 AP_WEB_DB_UTIL_PKG.RaiseException( 'getPreventCashCCAgeLimit', l_debug_info );
1554 APP_EXCEPTION.RAISE_EXCEPTION;
1555
1556 END getPreventCashCCAgeLimit;
1557
1558 /*Written By : Ron Langi
1559 Purpose : To check if prevent Cash CC Age Limit is set
1560 */
1561 ------------------------------------------------------------------------------
1562 FUNCTION isPreventCashCCAgeLimitSet
1563 RETURN BOOLEAN IS
1564 ------------------------------------------------------------------------------
1565
1566 l_debug_info VARCHAR2(1000);
1567
1568 BEGIN
1569
1570 -------------------------------------------
1571 l_debug_info := 'Check if Prevent Cash CC Age Limit is set';
1572 -------------------------------------------
1573 if (getPreventCashCCAgeLimit is null) then
1574 return false;
1575 end if;
1576
1577 return true;
1578
1579 EXCEPTION
1580 WHEN NO_DATA_FOUND THEN
1581 RETURN false;
1582
1583 WHEN OTHERS THEN
1584 AP_WEB_DB_UTIL_PKG.RaiseException( 'isPreventCashCCAgeLimitSet', l_debug_info );
1585 APP_EXCEPTION.RAISE_EXCEPTION;
1586
1587 END isPreventCashCCAgeLimitSet;
1588
1589 /*Written By : Ron Langi
1590 Purpose : To get the number of old transactions
1591 */
1592 ------------------------------------------------------------------------------
1593 PROCEDURE getNumOldTrxns(
1594 p_employee_id IN cards_employeeID,
1595 p_num_old OUT NOCOPY NUMBER) IS
1596 ------------------------------------------------------------------------------
1597
1598 l_debug_info VARCHAR2(1000);
1599 l_prevent_cash_cc_age_limit NUMBER;
1600
1601 BEGIN
1602
1603 if (not isPreventCashCCAgeLimitSet) then
1604 p_num_old := 0;
1605 else
1606
1607 l_prevent_cash_cc_age_limit := getPreventCashCCAgeLimit;
1608
1609 -------------------------------------------
1610 l_debug_info := 'Get number of Outstanding Trxns';
1611 -------------------------------------------
1612 select count(1)
1613 into p_num_old
1614 from ap_card_programs cp,
1615 ap_cards card,
1616 ap_credit_card_trxns trxns
1617 where card.employee_id = p_employee_id
1618 and card.card_program_id = cp.card_program_id
1619 and cp.card_type_lookup_code = 'TRAVEL'
1620 and trxns.card_program_id = card.card_program_id
1621 and trxns.card_id = card.card_id
1622 and trxns.validate_code = 'Y'
1623 and trxns.payment_flag <> 'Y'
1624 and trxns.billed_amount is not null
1625 and trxns.report_header_id is null
1626 and (nvl(trxns.category, 'BUSINESS') not in ('DISPUTED', 'CREDIT', 'MATCHED','DEACTIVATED'))
1627 and sysdate - trxns.posted_date > l_prevent_cash_cc_age_limit;
1628
1629 end if;
1630
1631 EXCEPTION
1632 WHEN NO_DATA_FOUND THEN
1633 p_num_old := 0;
1634
1635 WHEN OTHERS THEN
1636 AP_WEB_DB_UTIL_PKG.RaiseException( 'getNumOldTrxns', l_debug_info );
1637 APP_EXCEPTION.RAISE_EXCEPTION;
1638
1639 END getNumOldTrxns;
1640
1641 /*Written By : Ron Langi
1642 Purpose : To get the number of disputed transactions
1643 */
1644 ------------------------------------------------------------------------------
1645 PROCEDURE getNumDisputedTrxns(
1646 p_employee_id IN cards_employeeID,
1647 p_num_disputed OUT NOCOPY NUMBER) IS
1648 ------------------------------------------------------------------------------
1649 l_debug_info VARCHAR2(1000);
1650 BEGIN
1651 -------------------------------------------
1652 l_debug_info := 'Get number of Disputed';
1653 -------------------------------------------
1654 select count(1)
1655 into p_num_disputed
1656 from ap_card_programs cp,
1657 ap_cards card,
1658 ap_credit_card_trxns trxns
1659 where card.employee_id = p_employee_id
1660 and card.card_program_id = cp.card_program_id
1661 and cp.card_type_lookup_code = 'TRAVEL'
1662 and trxns.card_program_id = card.card_program_id
1663 and trxns.card_id = card.card_id
1664 and trxns.validate_code = 'Y'
1665 and trxns.payment_flag <> 'Y'
1666 and trxns.billed_amount is not null
1667 and trxns.report_header_id is null
1668 and nvl(trxns.category,c_business) = 'DISPUTED';
1669
1670 EXCEPTION
1671 WHEN NO_DATA_FOUND THEN
1672 p_num_disputed := 0;
1673
1674 WHEN OTHERS THEN
1675 AP_WEB_DB_UTIL_PKG.RaiseException( 'getNumDisputedTrxns', l_debug_info );
1676 APP_EXCEPTION.RAISE_EXCEPTION;
1677
1678 END getNumDisputedTrxns;
1679
1680 /*Written By : Ron Langi
1681 Purpose : To get the number of credits transactions
1682 */
1683 ------------------------------------------------------------------------------
1684 PROCEDURE getNumCredits(
1685 p_employee_id IN cards_employeeID,
1686 p_num_credits OUT NOCOPY NUMBER) IS
1687 ------------------------------------------------------------------------------
1688 l_debug_info VARCHAR2(1000);
1689 BEGIN
1690 -------------------------------------------
1691 l_debug_info := 'Get number of Credits';
1692 -------------------------------------------
1693 select count(1)
1694 into p_num_credits
1695 from ap_card_programs cp,
1696 ap_cards card,
1697 ap_credit_card_trxns trxns
1698 where card.employee_id = p_employee_id
1699 and card.card_program_id = cp.card_program_id
1700 and cp.card_type_lookup_code = 'TRAVEL'
1701 and trxns.card_program_id = card.card_program_id
1702 and trxns.card_id = card.card_id
1703 and trxns.validate_code = 'Y'
1704 and trxns.payment_flag <> 'Y'
1705 and trxns.billed_amount < 0
1706 and trxns.report_header_id is null
1707 and nvl(trxns.category, 'BUSINESS') not in ('DISPUTED', 'CREDIT', 'MATCHED','DEACTIVATED');
1708
1709 EXCEPTION
1710 WHEN NO_DATA_FOUND THEN
1711 p_num_credits := 0;
1712
1713 WHEN OTHERS THEN
1714 AP_WEB_DB_UTIL_PKG.RaiseException( 'getNumCredits', l_debug_info );
1715 APP_EXCEPTION.RAISE_EXCEPTION;
1716
1717 END getNumCredits;
1718
1719 /*Written By : Ron Langi
1720 Purpose : To check delegates for Homepage CC Alerts
1721 */
1722 ------------------------------------------------------------------------------
1723 PROCEDURE checkDelegatesForAlerts(
1724 p_employee_id IN cards_employeeID,
1725 p_user_id IN NUMBER,
1726 p_delegate_flag OUT NOCOPY VARCHAR2) IS
1727 ------------------------------------------------------------------------------
1728 l_debug_info VARCHAR2(1000);
1729 l_num_emps NUMBER := 0;
1730 l_employee_id NUMBER := 0;
1731 l_user_id NUMBER := 0;
1732 l_num_old NUMBER := 0;
1733 l_num_disputed NUMBER := 0;
1734 l_num_credits NUMBER := 0;
1735
1736 CURSOR delegates IS
1737 SELECT h.employee_id
1738 FROM per_employees_x h, ak_web_user_sec_attr_values a
1739 WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
1740 AND a.web_user_id = p_user_id
1741 AND h.employee_id = a.number_value
1742 AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)='Y'
1743 UNION ALL
1744 SELECT h.person_id employee_id
1745 FROM per_cont_workers_current_x h, ak_web_user_sec_attr_values a
1746 WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
1747 AND a.web_user_id = p_user_id
1748 AND h.person_id = a.number_value;
1749
1750 BEGIN
1751
1752 -------------------------------------------
1753 l_debug_info := 'Check Delegates for any CC Alerts';
1754 -------------------------------------------
1755 p_delegate_flag := 'N';
1756
1757 open delegates;
1758 loop
1759 fetch delegates into l_employee_id;
1760 exit when delegates%notfound;
1761
1762 l_num_emps := l_num_emps + 1;
1763
1764 AP_WEB_OA_MAINFLOW_PKG.GetUserID(l_employee_id, l_user_id);
1765 if (isCreditCardEnabled(l_employee_id, l_user_id) = 'Y') then
1766
1767 getNumOldTrxns(l_employee_id, l_num_old);
1768 getNumDisputedTrxns(l_employee_id, l_num_disputed);
1769 getNumCredits(l_employee_id, l_num_credits);
1770
1771 if (p_employee_id <> l_employee_id and
1772 (l_num_old > 0 or l_num_disputed > 0 or l_num_credits > 0)) then
1773 p_delegate_flag := 'Y';
1774 exit;
1775 end if;
1776
1777 end if;
1778
1779 end loop;
1780 close delegates;
1781
1782 EXCEPTION
1783 WHEN OTHERS THEN
1784 AP_WEB_DB_UTIL_PKG.RaiseException( 'checkDelegatesForAlerts', l_debug_info );
1785 APP_EXCEPTION.RAISE_EXCEPTION;
1786
1787 END checkDelegatesForAlerts;
1788
1789
1790 /*Written By : Ron Langi
1791 Purpose : To get Homepage CC Alerts
1792 */
1793 ------------------------------------------------------------------------------
1794 PROCEDURE getAlertsForHomepage(
1795 p_employee_id IN cards_employeeID,
1796 p_cc_flag OUT NOCOPY VARCHAR2,
1797 p_num_days OUT NOCOPY NUMBER,
1798 p_num_old OUT NOCOPY NUMBER,
1799 p_num_disputed OUT NOCOPY NUMBER,
1800 p_num_credits OUT NOCOPY NUMBER,
1801 p_delegate_flag OUT NOCOPY VARCHAR2) IS
1802 ------------------------------------------------------------------------------
1803
1804 l_debug_info VARCHAR2(1000);
1805 l_user_id NUMBER;
1806
1807 BEGIN
1808 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_CCARD_PKG', 'start getAlertsForHomepage');
1809
1810 p_cc_flag := 'N';
1811 p_num_days := 0;
1812 p_num_old := 0;
1813 p_num_disputed := 0;
1814 p_num_credits := 0;
1815 p_delegate_flag := 'N';
1816
1817 -------------------------------------------
1818 l_debug_info := 'Getting employee user ID';
1819 -------------------------------------------
1820 AP_WEB_OA_MAINFLOW_PKG.GetUserID(p_employee_id, l_user_id);
1821
1822 -- check if user is credit card enabled
1823 p_cc_flag := isCreditCardEnabled(p_employee_id, l_user_id);
1824
1825 if (p_cc_flag = 'Y') then
1826 -- get Prevent Cash CC Age Limit
1827 p_num_days := getPreventCashCCAgeLimit;
1828 -- get Outstanding
1829 getNumOldTrxns(p_employee_id, p_num_old);
1830 -- get Disputed
1831 getNumDisputedTrxns(p_employee_id, p_num_disputed);
1832 -- get Credits
1833 getNumCredits(p_employee_id, p_num_credits);
1834 end if;
1835
1836 -- check if delegates have CC Alerts
1837 checkDelegatesForAlerts(p_employee_id, l_user_id, p_delegate_flag);
1838
1839 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_CCARD_PKG', 'end getAlertsForHomepage');
1840
1841 EXCEPTION
1842 WHEN NO_DATA_FOUND THEN
1843 NULL;
1844 WHEN OTHERS THEN
1845 AP_WEB_DB_UTIL_PKG.RaiseException('getAlertsForHomepage', l_debug_info);
1846
1847 END getAlertsForHomepage;
1848
1849 ---------------------------------------------------------------------------
1850 /*Written By : Amulya Mishra
1851 Purpose : Gets the Cusrsor for DISPUTED Credit card trxns for an employee.
1852 */
1853
1854
1855 FUNCTION GetDisputeCcardTrxnCursor(
1856 p_cardProgramId IN ccTrxn_cardProgID,
1857 p_employeeId IN cards_employeeID,
1858 p_min_bucket IN NUMBER,
1859 p_max_bucket IN NUMBER,
1860 p_grace_days IN NUMBER,
1861 p_dispute_cursor OUT NOCOPY DisputeCCTrxnCursor
1862 ) RETURN BOOLEAN IS
1863 ---------------------------------------------------------------------------
1864
1865
1866 BEGIN
1867
1868 OPEN p_dispute_cursor FOR
1869 SELECT distinct transaction_date,
1870 merchant_name1,
1871 billed_amount,
1872 billed_currency_code,
1873 cct.posted_currency_code, --3339380
1874 nvl(cct.billed_date, cct.posted_date) billed_date,
1875 cct.posted_date, --Notification Esc
1876 cct.transaction_amount,--Notification Esc
1877 AP_WEB_DB_CCARD_PKG.GETLOCATION(cct.merchant_city , cct.merchant_province_state) --Notification Esc
1878 from
1879 ap_credit_card_trxns cct,
1880 ap_cards_all ac
1881 where
1882 cct.card_program_id = p_cardProgramId
1883 and cct.validate_code = 'Y'
1884 and cct.payment_flag <> 'Y'
1885 and nvl(cct.expensed_amount , 0) = 0
1886 and nvl(cct.category,'BUSINESS') = 'DISPUTED'
1887 and ac.card_program_id = cct.card_program_id
1888 and ac.card_id = cct.card_id
1889 and trunc(sysdate) - (cct.posted_date+nvl(p_grace_days,0)) between p_min_bucket and p_max_bucket
1890 and ac.employee_id = p_employeeId
1891 and rownum < 41;
1892
1893
1894 RETURN TRUE;
1895
1896 EXCEPTION
1897 WHEN NO_DATA_FOUND THEN
1898 RETURN FALSE;
1899
1900 WHEN OTHERS THEN
1901 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDisputeCcardTrxnCursor' );
1902 APP_EXCEPTION.RAISE_EXCEPTION;
1903 return FALSE;
1904
1905 END GetDisputeCcardTrxnCursor;
1906
1907 -------------------------------------------------------------------------------
1908 /*Written By : Amulya Mishra
1909 Purpose : Gets the total OUTSTANDING Amount for an employee.
1910 */
1911
1912 FUNCTION GetTotalCreditCardAmount(
1913 p_cardProgramId IN ccTrxn_cardProgID,
1914 p_employeeId IN cards_employeeID,
1915 p_totalAmount OUT NOCOPY NUMBER
1916 ) RETURN BOOLEAN IS
1917 ---------------------------------------------------------------------------
1918 BEGIN
1919 /* Bug 3356703: Only employee pending transactions should be
1920 * summed up */
1921 SELECT sum(amount)
1922 INTO p_totalAmount
1923 FROM
1924 (
1925 SELECT DISTINCT cct.trx_id, cct.billed_amount amount
1926 FROM
1927 ap_credit_card_trxns_all cct,
1928 ap_cards_all ac,
1929 ap_expense_report_headers_all erh
1930 WHERE
1931 cct.card_program_id = p_cardProgramId
1932 and cct.validate_code = 'Y'
1933 and cct.payment_flag <> 'Y'
1934 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(
1935 erh.source,erh.workflow_approved_flag,
1936 erh.report_header_id)
1937 in
1938 ('EMPAPPR', 'RESOLUTN','RETURNED',
1939 'REJECTED','SAVED','WITHDRAWN','UNUSED', 'INPROGRESS') -- Bug: 14148366
1940 and erh.report_header_id = cct.report_header_id
1941 and NVL(erh.vouchno, 0) = 0
1942 and ac.card_program_id = cct.card_program_id
1943 and ac.card_id = cct.card_id
1944 and ac.employee_id = p_employeeId
1945 UNION ALL
1946 SELECT DISTINCT cct.trx_id, cct.billed_amount amount
1947 FROM
1948 ap_credit_card_trxns_all cct,
1949 ap_cards_all ac
1950 WHERE
1951 cct.card_program_id = p_cardProgramId
1952 and cct.validate_code = 'Y'
1953 and cct.payment_flag <> 'Y'
1954 and nvl(cct.expensed_amount , 0) =0
1955 and nvl(cct.category,'BUSINESS') <> 'DEACTIVATED'
1956 and ac.card_program_id = cct.card_program_id
1957 and ac.card_id = cct.card_id
1958 and ac.employee_id = p_employeeId
1959 );
1960 RETURN TRUE;
1961
1962 EXCEPTION
1963 WHEN NO_DATA_FOUND THEN
1964 RETURN FALSE;
1965
1966 WHEN OTHERS THEN
1967 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTotalCreditCardAmount' );
1968 APP_EXCEPTION.RAISE_EXCEPTION;
1969 return FALSE;
1970
1971 END GetTotalCreditCardAmount;
1972
1973
1974 ---------------------------------------------------------------------------
1975 /*Written By : Amulya Mishra
1976 Purpose : Gets the total Number of NON DISPUTED OUTSTANDING Transactions
1977 and Total Amount.
1978 */
1979
1980
1981 FUNCTION GetTotalNumberOutstanding(
1982 p_cardProgramId IN ccTrxn_cardProgID,
1983 p_employeeId IN cards_employeeID,
1984 p_min_bucket IN NUMBER,
1985 p_max_bucket IN NUMBER,
1986 p_total_outstanding OUT NOCOPY NUMBER,
1987 p_total_amt_outstanding OUT NOCOPY NUMBER
1988 ) RETURN BOOLEAN IS
1989 ---------------------------------------------------------------------------
1990 BEGIN
1991
1992 SELECT count(1), sum(amount)
1993 INTO p_total_outstanding,
1994 p_total_amt_outstanding
1995 FROM
1996 ( SELECT DISTINCT trx_id, cct.billed_amount amount
1997 FROM
1998 ap_credit_card_trxns cct,
1999 ap_cards ac,
2000 ap_expense_report_headers erh
2001 WHERE
2002 cct.card_program_id = p_cardProgramId
2003 and cct.validate_code = 'Y'
2004 and cct.payment_flag <> 'Y'
2005 and nvl(cct.category,c_business) NOT IN
2006 ('DISPUTED','CREDIT','MATCHED','DEACTIVATED')--Bug 3307864
2007 and ac.card_program_id = cct.card_program_id
2008 and ac.card_id = cct.card_id
2009 and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
2010 and erh.report_header_id = cct.report_header_id
2011 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,
2012 erh.workflow_approved_flag,
2013 erh.report_header_id) --2615505
2014 in ('SAVED','UNUSED','REJECTED',
2015 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN', 'INPROGRESS') -- Bug: 14148366
2016 and ac.employee_id = p_employeeId
2017 UNION ALL
2018 SELECT DISTINCT trx_id, cct.billed_amount amount
2019 FROM
2020 ap_credit_card_trxns cct,
2021 ap_cards ac
2022 WHERE
2023 cct.card_program_id = p_cardProgramId
2024 and cct.validate_code = 'Y'
2025 and cct.payment_flag <> 'Y'
2026 and nvl(cct.expensed_amount , 0) = 0
2027 and nvl(cct.category,c_business) NOT IN
2028 ('DISPUTED','CREDIT','MATCHED','DEACTIVATED')--Bug 3307864
2029 and cct.report_header_id is null
2030 and ac.card_program_id = cct.card_program_id
2031 and ac.card_id = cct.card_id
2032 and trunc(sysdate) - cct.posted_date between p_min_bucket and p_max_bucket
2033 and ac.employee_id = p_employeeId
2034 );
2035
2036
2037 RETURN TRUE;
2038
2039 EXCEPTION
2040 WHEN NO_DATA_FOUND THEN
2041 RETURN FALSE;
2042
2043 WHEN OTHERS THEN
2044 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTotalNumberOutstanding' );
2045 APP_EXCEPTION.RAISE_EXCEPTION;
2046 return FALSE;
2047
2048 END GetTotalNumberOutstanding;
2049
2050
2051 -----------------------------------------------------------------------
2052 /*Written By : Amulya Mishra
2053 Purpose : Gets the total Number of DISPUTED Transactions and Total Amount.
2054 */
2055
2056 FUNCTION GetTotalNumberDispute(
2057 p_cardProgramId IN ccTrxn_cardProgID,
2058 p_employeeId IN cards_employeeID,
2059 p_min_bucket IN NUMBER,
2060 p_max_bucket IN NUMBER,
2061 p_grace_days IN NUMBER,
2062 p_total_dispute OUT NOCOPY NUMBER,
2063 p_total_amt_dispute OUT NOCOPY NUMBER
2064 ) RETURN BOOLEAN IS
2065 ---------------------------------------------------------------------------
2066 BEGIN
2067
2068 SELECT count(1), sum(amount)
2069 INTO p_total_dispute,
2070 p_total_amt_dispute
2071 FROM
2072 ( SELECT DISTINCT trx_id, cct.billed_amount amount
2073 FROM
2074 ap_credit_card_trxns cct,
2075 ap_cards ac,
2076 ap_expense_report_headers erh
2077 WHERE
2078 cct.card_program_id = p_cardProgramId
2079 and cct.validate_code = 'Y'
2080 and cct.payment_flag <> 'Y'
2081 and nvl(cct.category,c_business) = 'DISPUTED'
2082 and ac.card_program_id = cct.card_program_id
2083 and ac.card_id = cct.card_id
2084 and trunc(sysdate) - (cct.posted_date+nvl(p_grace_days,0))
2085 between p_min_bucket and p_max_bucket
2086 and erh.report_header_id(+) = cct.report_header_id
2087 and NVL(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,
2088 erh.workflow_approved_flag,
2089 erh.report_header_id),
2090 'UNUSED') --2615505
2091 in ('SAVED','UNUSED','REJECTED',
2092 'RESOLUTN','EMPAPPR','RETURNED','WITHDRAWN', 'INPROGRESS') -- Bug: 14148366
2093 and ac.employee_id = p_employeeId);
2094
2095
2096 RETURN TRUE;
2097
2098 EXCEPTION
2099 WHEN NO_DATA_FOUND THEN
2100 RETURN FALSE;
2101
2102 WHEN OTHERS THEN
2103 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTotalNumberOutstanding' );
2104 APP_EXCEPTION.RAISE_EXCEPTION;
2105 return FALSE;
2106
2107 END GetTotalNumberDispute;
2108
2109 --------------------------------------------------------------------------------------
2110
2111 -----------------------------------------------------------------------
2112 /*
2113 Written By : Ron Langi
2114 Purpose : Gets Posted Date for the Oldest Available Transaction
2115 */
2116
2117 FUNCTION GetOldestAvailPostedDate(
2118 p_cardProgramId IN ccTrxn_cardProgID,
2119 p_cardId IN cards_cardId,
2120 p_paymentDueFromCode IN ccTrxn_paymentDueFromCode,
2121 p_reimb_curr_code IN ccTrxn_billedCurrCode,
2122 p_report_header_id IN ccTrxn_headerID
2123 ) RETURN DATE IS
2124 -----------------------------------------------------------------------
2125
2126 l_oldest_posted_date DATE := sysdate;
2127
2128 BEGIN
2129
2130 select nvl(min(posted_date), sysdate)
2131 into l_oldest_posted_date
2132 from ap_credit_card_trxns cct
2133 where cct.card_program_id = p_cardProgramId
2134 and cct.card_id = p_cardId
2135 and cct.payment_due_from_code = p_paymentDueFromCode
2136 and cct.billed_currency_code = p_reimb_curr_code
2137 and cct.validate_code = 'Y'
2138 and cct.payment_flag <> 'Y'
2139 and nvl(cct.category, c_business) not in (c_disputed, c_credit, c_matched, c_deactivated)
2140 and (cct.report_header_id is null or cct.report_header_id = p_report_header_id)
2141 and cct.billed_amount is not null;
2142
2143 return l_oldest_posted_date;
2144
2145 EXCEPTION
2146 WHEN NO_DATA_FOUND THEN
2147 RETURN sysdate;
2148
2149 WHEN OTHERS THEN
2150 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetOldestAvailPostedDate' );
2151 APP_EXCEPTION.RAISE_EXCEPTION;
2152 return sysdate;
2153
2154 END GetOldestAvailPostedDate;
2155
2156 -----------------------------------------------------------------------
2157
2158 /*
2159 Written By : Amulya Mishra
2160 Bug : 3562287
2161 Purpose : Reurn location field using merchant_city and
2162 merchant_province_state.
2163 */
2164
2165 -----------------------------------------------------------------------
2166 FUNCTION GetLocation(
2167 merchant_city IN VARCHAR2,
2168 merchant_province_state IN VARCHAR2
2169 ) RETURN VARCHAR2 IS
2170 -----------------------------------------------------------------------
2171 BEGIN
2172 IF merchant_city IS NOT NULL and merchant_province_state IS NOT NULL THEN
2173 RETURN merchant_city || ', ' || merchant_province_state;
2174 ELSIF merchant_city IS NULL and merchant_province_state IS NOT NULL THEN
2175 RETURN merchant_province_state;
2176 ELSIF merchant_city IS NOT NULL and merchant_province_state IS NULL THEN
2177 RETURN merchant_city;
2178 ELSIF merchant_city IS NULL and merchant_province_state IS NULL THEN
2179 RETURN NULL;
2180 END IF;
2181 END GetLocation;
2182 -----------------------------------------------------------------------
2183
2184 END AP_WEB_DB_CCARD_PKG;