1 PACKAGE BODY AP_WEB_CC_VALIDATIONS_PKG as
2 /* $Header: apwccvlb.pls 120.31.12020000.2 2013/02/08 14:01:36 meesubra ship $ */
3
4 type t_gen_cur is ref cursor;
5
6 --
7 -- Globals
8 -- These will all get initialized in one of the "set" routines.
9 --
10 g_where_clause_type varchar2(30);
11 g_where_clause varchar2(1000);
12 g_trx_id number;
13 g_request_id number;
14 g_card_program_id number;
15 g_start_date date;
16 g_end_date date;
17 g_validate_code varchar2(25);
18
19 --
20 -- Package private functions/procedures
21 --
22 function set_row_set_internal return number;
23 -- procedure validate_internal;
24 function execute_update(p_stmt_str in varchar2, p_valid_only in boolean) return number;
25 procedure execute_select(c in out nocopy t_gen_cur,
26 p_stmt_str in out nocopy varchar2,
27 p_valid_only in boolean);
28
29
30 ------------------------------------------------------------------------------
31 --------------------------------- (1) ----------------------------------------
32 ------------------------------------------------------------------------------
33
34 ------------------------------------------------------------------------------
35 -- Sets the context to all credit card transactions.
36 -- (This should probably used sparingly since it really chooses everything
37 -- - even across orgs)
38 function set_all_row_set return number is
39 begin
40 g_where_clause_type := 'ALL';
41
42 return set_row_set_internal;
43 end set_all_row_set;
44
45 ------------------------------------------------------------------------------
46 -- Sets the context to the following criteria
47 -- (start/end dates are on the transaction date)
48 function set_row_set(p_request_id in number,
49 p_card_program_id in number,
50 p_start_date in date,
51 p_end_date in date) return number is
52 begin
53 g_where_clause_type := 'CARD_PROGRAM_ID';
54 g_request_id := p_request_id;
55 g_card_program_id := p_card_program_id;
56 g_start_date := p_start_date;
57 g_end_date := p_end_date;
58
59 if p_request_id is not null then
60 g_where_clause := ' AND CC.REQUEST_ID = :reqId ';
61 else
62 g_where_clause := ' AND :reqId IS NULL ';
63 end if;
64
65 if p_card_program_id is not null then
66 g_where_clause := g_where_clause || ' AND CC.CARD_PROGRAM_ID = :B_CARD_PROGRAM_ID ';
67 else
68 g_where_clause := g_where_clause || ' AND :B_CARD_PROGRAM_ID IS NULL ';
69 end if;
70
71 if p_start_date is not null and p_end_date is not null then
72 g_where_clause := g_where_clause || ' AND (CC.TRANSACTION_DATE BETWEEN :B_START_DATE AND :B_END_DATE OR CC.TRANSACTION_DATE IS NULL) ';
73 elsif p_start_date is not null then
74 g_where_clause := g_where_clause || ' AND (CC.TRANSACTION_DATE >= :B_START_DATE OR CC.TRANSACTION_DATE IS NULL) ';
75 g_where_clause := g_where_clause || ' AND :B_END_DATE IS NULL ';
76 elsif p_end_date is not null then
77 g_where_clause := g_where_clause || ' AND :B_START_DATE IS NULL ';
78 g_where_clause := g_where_clause || ' AND (CC.TRANSACTION_DATE <= :B_END_DATE OR CC.TRANSACTION_DATE IS NULL) ';
79 else
80 g_where_clause := g_where_clause || ' AND :B_START_DATE IS NULL ';
81 g_where_clause := g_where_clause || ' AND :B_END_DATE IS NULL ';
82 end if;
83
84
85 return set_row_set_internal;
86 end set_row_set;
87
88 ------------------------------------------------------------------------------
89 -- Sets the context to one specific transaction
90 function set_row_set(p_trx_id in number) return number is
91 begin
92 g_where_clause_type := 'TRX_ID';
93 g_trx_id := p_trx_id;
94
95 if p_trx_id is not null then
96 g_where_clause := ' AND CC.TRX_ID = :trxId ';
97 else
98 g_where_clause := ' AND :trxId IS NULL ';
99 end if;
100
101 return set_row_set_internal;
102 end set_row_set;
103
104 ------------------------------------------------------------------------------
105 --------------------------------- (2) ----------------------------------------
106 ------------------------------------------------------------------------------
107
108 ------------------------------------------------------------------------------
109 -- Default org_id - based on card program
110 function default_org_id(p_valid_only in boolean) return number is
111 begin
112 return execute_update( 'update ap_credit_card_trxns_all cc '||
113 'set org_id = (select org_id from ap_card_programs_all apcp where apcp.card_program_id = cc.card_program_id) '||
114 'where cc.org_id is null' , p_valid_only );
115 end default_org_id;
116
117 -- Set request_id only if the request_id is null, we do not want to update the request_id that is
118 -- populated during the creation or validation of cc transaction
119 function set_request_id(p_request_id in number) return number is
120 l_valid_only boolean;
121 begin
122 l_valid_only := true;
123 return execute_update( 'update ap_credit_card_trxns_all cc '||
124 'set request_id = ' || to_char(p_request_id) ||
125 'where cc.request_id is null' , l_valid_only );
126 end set_request_id;
127
128 function set_validate_request_id(p_request_id in number) return number is
129 l_valid_only boolean;
130 begin
131 l_valid_only := true;
132 return execute_update( 'update ap_credit_card_trxns_all cc '||
133 'set validate_request_id = ' || to_char(p_request_id) ||
134 'Where 1=1', l_valid_only );
135 end set_validate_request_id;
136
137 ------------------------------------------------------------------------------
138 -- Default folio type using folio type mapping rules
139 function default_folio_type(p_valid_only in boolean) return number
140 is
141 c t_gen_cur;
142 stmt varchar2(2000);
143
144 l_validate_where varchar2(50) := null;
145 l_stmt varchar2(2000);
146
147 l_card_program_id number;
148 l_map_type_code varchar2(30);
149 l_column_name varchar2(30);
150
151 l_count number := 0;
152 begin
153 stmt := 'select distinct cp.card_program_id, cp.card_exp_type_map_type_code, cp.card_exp_type_source_col '||
154 'from ap_credit_card_trxns_all cc, ap_card_programs_all cp '||
155 'where cc.card_program_id = cp.card_program_id ';
156
157 if p_valid_only then
158 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
159 end if;
160
161 execute_select(c, stmt, p_valid_only);
162 loop
163 fetch c into l_card_program_id, l_map_type_code, l_column_name;
164 exit when c%notfound;
165
166 if l_map_type_code is not null and l_column_name is not null then
167 l_stmt := 'update ap_credit_card_trxns_all cc '||
168 'set folio_type = ap_map_types_pkg.get_map_to_code(:r0, cc.'||l_column_name||') '||
169 'where cc.card_program_id = :r1';
170
171 if g_where_clause_type = 'ALL' then
172 execute immediate l_stmt || l_validate_where using l_map_type_code, l_card_program_id;
173 elsif g_where_clause_type = 'TRX_ID' then
174 execute immediate l_stmt || g_where_clause || l_validate_where using l_map_type_code, l_card_program_id, g_trx_id;
175 elsif g_where_clause_type = 'CARD_PROGRAM_ID' then
176 execute immediate l_stmt || g_where_clause || l_validate_where using l_map_type_code, l_card_program_id, g_request_id, g_card_program_id, g_start_date, g_end_date;
177 end if;
178
179 l_count := l_count + SQL%ROWCOUNT;
180 end if;
181 end loop;
182 close c;
183
184 return l_count;
185 end default_folio_type;
186
187 ------------------------------------------------------------------------------
188 -- Default eLocation country code using elocation mapping rules
189 function default_country_code(p_valid_only in boolean) return number
190 is
191 l_count number;
192 begin
193 --
194 -- If a null merchant country was passed in,
195 -- default it based on various information of the card program.
196 l_count := execute_update('update ap_credit_card_trxns_all cc '||
197 'set merchant_country = nvl(merchant_country, ap_web_locations_pkg.default_country(card_program_id)) '||
198 'where merchant_country is null ', p_valid_only);
199
200 --
201 -- Default merchant country code
202 -- based on mapping rules
203 l_count := execute_update( 'update ap_credit_card_trxns_all cc '||
204 'set merchant_country_code = ' ||
205 '(select ap_map_types_pkg.get_map_to_code(c.country_map_type_code, cc.merchant_country) '||
206 ' from ap_card_programs_all c '||
207 ' where c.card_program_id = cc.card_program_id) '||
208 'where 1=1', p_valid_only );
209
210 return l_count;
211 end default_country_code;
212
213
214
215 ------------------------------------------------------------------------------
216 -- Assign payment flags (based on card specific info)
217 function default_payment_flag(p_valid_only in boolean) return number is
218 num_rows number;
219 l_custom_pay_trxn boolean := FALSE;
220 begin
221 --------------------------------------------------------------------
222 -- Set Payment Flag
223 --
224 -- To distinguish payment transactions from debit/credit transactions
225 -- American Express (mis_industry_code = PA)
226 -- Diner's Club (transaction_type = PAYMENTS)
227 -- MasterCard (merchant_activity = A and transaction_amount < 0)
228 -- Visa (transaction_type = 0108)-- MC?
229 -- Bug 5976422 Description stores adjustment description and is specific for mastercard.
230 -- In case other loader program start storing description, code needs to be modified.
231 --------------------------------------------------------------------
232 -- OPEN ISSUE:
233 -- The following is straight out of APXCCVAL.rdf
234 -- The criteria for MasterCard IS incorrect.
235 -- 1) transaction_amount may be zero while billed_amount is negative
236 -- 2) may include refunds - not just payments
237 l_custom_pay_trxn := AP_WEB_CUST_DFLEX_PKG.CustomMarkPaymentTrxn;
238
239 IF (l_custom_pay_trxn = FALSE) THEN
240 num_rows := execute_update(
241 ' update ap_credit_card_trxns_all cc
242 set payment_flag = ''Y''
243 where (mis_industry_code = ''PA''
244 or upper(transaction_type) = ''PAYMENTS''
245 or (card_program_id in (select card_program_id from ap_card_programs_all where card_brand_lookup_code = ''MasterCard'') and
246 upper(description) like ''%PAYMENT%'' and
247 (upper(description) not like ''%FEE%''
248 and upper(description) not like ''%REVERSAL%''
249 and upper(description) not like ''%ADJUSTMENT%''
250 and upper(description) not like ''%CREDIT%''
251 and upper(description) not like ''%DEBIT%''))
252 or transaction_type in (''0108'',''0440'')) ', p_valid_only);
253 END IF;
254
255 return num_rows;
256 end default_payment_flag;
257
258
259 ------------------------------------------------------------------------------
260 -- Convert numeric currency codes into FND currency codes
261 function convert_currency_code(p_valid_only in boolean) return number is
262 num_rows number;
263 begin
264 num_rows := execute_update(
265 'update ap_credit_card_trxns_all cc
266 set cc.billed_currency_code =
267 (select currency_code
268 from ap_card_currencies
269 where numeric_currency_code = cc.billed_currency_code)
270 where cc.billed_currency_code is not null
271 and cc.billed_currency_code not in
272 (select fndcvl.currency_code
273 from fnd_currencies_vl fndcvl
274 where fndcvl.enabled_flag = ''Y''
275 and fndcvl.currency_flag = ''Y''
276 and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
277 and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate))
278 and exists
279 (select currency_code
280 from ap_card_currencies
281 where numeric_currency_code = cc.billed_currency_code)', p_valid_only
282 );
283
284
285 --------------------------------------------------------------------
286 -- Convert null billed currency codes
287 --------------------------------------------------------------------
288 num_rows := num_rows + execute_update(
289 'update ap_credit_card_trxns_all cc
290 set cc.billed_currency_code =
291 (select cp.card_program_currency_code
292 from ap_card_programs_all cp
293 where cp.card_program_id = cc.card_program_id)
294 where billed_currency_code is null', p_valid_only
295 );
296
297 --------------------------------------------------------------------
298 -- Convert posted currency codes
299 --------------------------------------------------------------------
300 num_rows := num_rows + execute_update(
301 'update ap_credit_card_trxns_all cc
302 set cc.posted_currency_code =
303 (select currency_code
304 from ap_card_currencies
305 where numeric_currency_code = cc.posted_currency_code)
306 where cc.posted_currency_code is not null
307 and exists
308 (select currency_code
309 from ap_card_currencies
310 where numeric_currency_code = cc.posted_currency_code)', p_valid_only
311 );
312
313 --------------------------------------------------------------------
314 -- Convert null posted currency codes
315 --------------------------------------------------------------------
316 num_rows := num_rows + execute_update(
317 'update ap_credit_card_trxns_all cc
318 set cc.posted_currency_code =
319 (select cp.card_program_currency_code
320 from ap_card_programs_all cp
321 where cp.card_program_id = cc.card_program_id)
322 where posted_currency_code is null', p_valid_only
323 );
324
325 return num_rows;
326 end convert_currency_code;
327
328
329 ------------------------------------------------------------------------------
330 -- eLocation integration
331 function get_locations(p_valid_only in boolean) return number is
332 stmt varchar2(2000);
333
334 l_loc_cur t_gen_cur;
335 l_cc_trx ap_credit_card_trxns_all%rowtype;
336
337 l_return_status varchar2(30);
338 l_msg_count number;
339 l_msg_data varchar2(2000);
340
341 num_rows number := 0;
342 begin
343 stmt := 'select *
344 from ap_credit_card_trxns_all cc
345 where location_id is null '||g_where_clause
346 || ' for update of location_id nowait';
347 execute_select(l_loc_cur, stmt, p_valid_only);
348 loop
349 fetch l_loc_cur into l_cc_trx;
350 exit when l_loc_cur%notfound;
351
352 num_rows := num_rows + 1;
353 ap_web_locations_pkg.get_location(l_cc_trx, l_return_status, l_msg_count, l_msg_data);
354
355 update ap_credit_card_trxns_all set location_id = l_cc_trx.location_id
356 where trx_id = l_cc_trx.trx_id;
357 end loop;
358 close l_loc_cur;
359
360 return num_rows;
361 end get_locations;
362
363 ------------------------------------------------------------------------------
364 -- Stamp CC Transactions with Payment Scenario of Card Program
365 function set_payment_scenario(p_valid_only in boolean) return number is
366 begin
367 return execute_update(
368 ' update ap_credit_card_trxns_all cc
369 set payment_due_from_code = (select payment_due_from_code
370 from ap_card_programs_all cp
371 where cp.card_program_id = cc.card_program_id)
372 where payment_due_from_code is null ', p_valid_only);
373 end set_payment_scenario;
374
375
376 ------------------------------------------------------------------------------
377 --------------------------------- (3) ----------------------------------------
378 ------------------------------------------------------------------------------
379
380 ------------------------------------------------------------------------------
381 -- Check for duplication transactions (card program, card number, reference number)
382 function duplicate_trx(p_valid_only in boolean) return number is
383 begin
384 --------------------------------------------------------------------
385 -- Duplicate transaction
386 --------------------------------------------------------------------
387 return execute_update(
388 'update ap_credit_card_trxns_all cc
389 set validate_code = ''DUPLICATE_TRANSACTION''
390 where exists
391 (select ''A corresponding transaction exists with this reference number''
392 from ap_credit_card_trxns_all cc2
393 where cc.reference_number = cc2.reference_number
394 and cc.trx_id <> cc2.trx_id
395 and cc.card_id = cc2.card_id
396 and cc.card_program_id = cc2.card_program_id)', p_valid_only
397 );
398 end duplicate_trx;
399
400 ------------------------------------------------------------------------------
401 -- Check for non-zero, non-null billed amount
402 function invalid_billed_amount(p_valid_only in boolean) return number is
403 BEGIN
404 --------------------------------------------------------------------
405 -- Invalid billed amount
406 --------------------------------------------------------------------
407 return execute_update(
408 'update ap_credit_card_trxns_all cc
409 set validate_code = ''INVALID_BILL_AMOUNT''
410 where (billed_amount is null
411 or billed_amount = 0)', p_valid_only
412 );
413 end invalid_billed_amount;
414
415 -- Check for valid billed currency code
416 function invalid_billed_currency_code(p_valid_only in boolean) return number is
417 begin
418 --------------------------------------------------------------------
419 -- Invalid billed currency code
420 --------------------------------------------------------------------
421 return execute_update(
422 'update ap_credit_card_trxns_all cc
423 set validate_code = ''INVALID_BILL_CURR_CODE''
424 where billed_currency_code is not null
425 and not exists
426 (select ''A corresponding currency exists in FND_CURRENCIES''
427 from fnd_currencies_vl fndcvl
428 where fndcvl.enabled_flag = ''Y''
429 and fndcvl.currency_flag = ''Y''
430 and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
431 and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate)
432 and fndcvl.currency_code = cc.billed_currency_code)', p_valid_only
433 );
434 end invalid_billed_currency_code;
435
436 ------------------------------------------------------------------------------
437 -- Check for non-null billed date
438 function invalid_billed_date(p_valid_only in boolean) return number is
439 begin
440 --------------------------------------------------------------------
441 -- Invalid billed date
442 --------------------------------------------------------------------
443 return execute_update(
444 'update ap_credit_card_trxns_all cc
445 set validate_code = ''INVALID_BILL_DATE''
446 where billed_date is null', p_valid_only
447 );
448 end invalid_billed_date;
449
450
451 ------------------------------------------------------------------------------
452 -- Check for inactive card number
453 function inactive_card_number(p_valid_only in boolean) return number is
454 num_rows number;
455 begin
456 --------------------------------------------------------------------
457 -- Inactive card number
458 --------------------------------------------------------------------
459 num_rows := execute_update(
460 'update ap_credit_card_trxns_all cc
461 set validate_code = ''INACTIVE_CARD_NUMBER''
462 where cc.transaction_date >=
463 (select max(nvl(apc.inactive_date,cc.transaction_date+1))
464 from ap_cards_all apc
465 where apc.card_program_id = cc.card_program_id
466 and apc.card_id = cc.card_id)', p_valid_only
467 );
468 return num_rows;
469 end inactive_card_number;
470
471 ------------------------------------------------------------------------------
472 -- Check for invalid card number
473 function invalid_card_number(p_valid_only in boolean) return number is
474 num_rows number;
475 begin
476 --------------------------------------------------------------------
477 -- Invalid card number
478 --------------------------------------------------------------------
479 IF(IBY_FNDCPT_SETUP_PUB.Get_Encryption_Patch_Level = 'PADSS') THEN
480 num_rows := execute_update('update ap_credit_card_trxns_all cc
481 set validate_code = ''INVALID_CARD_NUMBER''
482 where card_id in
483 (select card_id
484 from ap_cards_all apc, iby_creditcard icc
485 where apc.card_program_id = cc.card_program_id and
486 apc.card_id = cc.card_id and
487 icc.instrid = apc.card_reference_id and
488 icc.invalid_flag = ''Y'')
489 and cc.card_number is null
490 and cc.validate_code != ''Y''', p_valid_only);
491 ELSE
492 num_rows := execute_update('update ap_credit_card_trxns_all cc
493 set validate_code = ''INVALID_CARD_NUMBER''
494 where card_id not in
495 (select card_id
496 from ap_cards_all apc
497 where apc.card_program_id = cc.card_program_id and
498 apc.card_id = cc.card_id)
499 and cc.card_number is null
500 and cc.validate_code != ''Y''', p_valid_only);
501 END IF;
502 return num_rows;
503 end invalid_card_number;
504
505 ------------------------------------------------------------------------------
506 -- Check for non-null merchant name
507 function invalid_merchant_name(p_valid_only in boolean) return number is
508 BEGIN
509 --------------------------------------------------------------------
510 -- Invalid merchant name
511 --------------------------------------------------------------------
512 --4730543 : skip validation for AME mis_industry_code 'SP' - Stop Payment
513 --and Transaction Type '05'
514 return execute_update(
515 'update ap_credit_card_trxns_all cc
516 set validate_code = ''INVALID_MERCH_NAME''
517 where (merchant_name1 is null
518 and merchant_name2 is null)
519 and ( (transaction_type in (''11'',''20'',''22'',''80'')
520 and sic_code <> ''6012''
521 and (card_program_id in (select card_program_id
522 from ap_card_programs_all
523 where card_brand_lookup_code = ''Visa'')))
524 or
525 (merchant_activity <> ''A''
526 and (card_program_id in (select card_program_id
527 from ap_card_programs_all
528 where card_brand_lookup_code = ''MasterCard'')))
529 or
530 (mis_industry_code <> ''SP''
531 and transaction_type <> ''05''
532 and card_program_id in (select card_program_id
533 from ap_card_programs_all
534 where card_brand_lookup_code = ''American Express''))
535 or
536 card_program_id not in (select card_program_id
537 from ap_card_programs_all
538 where card_brand_lookup_code in (''American Express'',''Visa'',''MasterCard''))
539 )
540 ', p_valid_only
541 );
542 end invalid_merchant_name;
543
544
545 ------------------------------------------------------------------------------
546 -- Check for valid posted currency code
547 function invalid_posted_currency_code(p_valid_only in boolean) return number is
548 begin
549 --------------------------------------------------------------------
550 -- Invalid posted currency code
551 --------------------------------------------------------------------
552 return execute_update(
553 'update ap_credit_card_trxns_all cc
554 set validate_code = ''INVALID_POST_CURR_CODE''
555 where posted_currency_code is not null
556 and posted_currency_code not in (select currency_code from ap_card_currencies) ', p_valid_only
557 );
558 end invalid_posted_currency_code;
559
560 ------------------------------------------------------------------------------
561 -- Check for non-zero, non-null transaction amount
562 function invalid_trx_amount(p_valid_only in boolean) return number is
563 BEGIN
564 --------------------------------------------------------------------
565 -- Invalid transaction amount
566 --------------------------------------------------------------------
567 return execute_update(
568 'update ap_credit_card_trxns_all cc
569 set validate_code = ''INVALID_TRX_AMOUNT''
570 where (transaction_amount is null
571 or transaction_amount = 0)', p_valid_only
572 );
573 end invalid_trx_amount;
574
575 ------------------------------------------------------------------------------
576 -- Check for non-null transaction date
577 function invalid_trx_date(p_valid_only in boolean) return number is
578 BEGIN
579 --------------------------------------------------------------------
580 -- Invalid transaction date
581 --------------------------------------------------------------------
582 return execute_update(
583 'update ap_credit_card_trxns_all cc
584 set validate_code = ''INVALID_TRX_DATE''
585 where transaction_date is null
586 and ((mis_industry_code <> ''PA''
587 and card_program_id in (select card_program_id
588 from ap_card_programs_all
589 where card_brand_lookup_code = ''American Express''))
590 or
591 card_program_id not in (select card_program_id
592 from ap_card_programs_all
593 where card_brand_lookup_code = ''American Express'')
594 )
595 ', p_valid_only
596 );
597 end invalid_trx_date;
598
599 ------------------------------------------------------------------------------
600 -- Marks the rows that are still valid as valid, and returns the number of
601 -- rows that are still valid
602 function valid_trx return number is
603 begin
604 return execute_update(
605 'update ap_credit_card_trxns_all cc
606 set validate_code = ''Y''
607 where validate_code = ''UNTESTED''', true);
608 end valid_trx;
609
610
611 -- sic_code is required if transaction_type code is 10,11,20,22,80
612 -- sic_code Must be equal to 6010, 6011, 6012, 6050 or 6051 if the
613 -- transaction type code is 20,22,80
614 -- The validation is required for Visa VCF 4.0 Format
615 function invalid_sic_code(p_valid_only in boolean) return number is
616 BEGIN
617 --------------------------------------------------------------------
618 -- Invalid mis_industry_code
619 --------------------------------------------------------------------
620 return execute_update(
621 'update ap_credit_card_trxns_all cc
622 set validate_code = ''INVALID_SIC_CODE''
623 where card_program_id in (select card_program_id
624 from ap_card_programs_all
625 where card_brand_lookup_code = ''Visa'')
626 and ((sic_code is null
627 and transaction_type in (''10'',''11'',''20'',''22'',''80''))
628 or
629 (transaction_type in (''20'',''22'',''80'')
630 and sic_code not in (''6010'', ''6011'', ''6012'', ''6050'', ''6051''))
631 )', p_valid_only
632 );
633 end invalid_sic_code;
634 ------------------------------------------------------------------------------
635 --------------------------------- (4) ----------------------------------------
636 ------------------------------------------------------------------------------
637
638 ------------------------------------------------------------------------------
639 -- Returns the lesser of date1 and date2. Null values are considered to
640 -- be a date in the infinite future.
641 function get_min_date(date1 in date, date2 in date) return date is
642 begin
643 if date1 is null and date2 is null then
644 return null;
645 elsif date1 is null then
646 return date2;
647 elsif date2 is null then
648 return date1;
649 elsif date1 < date2 then
650 return date1;
651 else
652 return date2;
653 end if;
654 end get_min_date;
655
656
657 ------------------------------------------------------------------------------
658 -- Assign the employee to the card and activate it.
659 procedure assign_employee(p_card_id in number, p_employee_id in number) is
660 l_full_name VARCHAR2(80);
661 begin
662 select full_name into l_full_name from ap_card_details where card_id = p_card_id;
663
664 if (l_full_name is null) then
665 select substr(FIRST_NAME|| ' ' || MIDDLE_NAME|| ' ' || LAST_NAME,1,80)
666 into l_full_name from ap_card_details where card_id = p_card_id;
667 end if;
668
669 if (l_full_name is null or trim(l_full_name) is null) then
670 select substrb(full_name, 1, 80) into l_full_name
671 from per_employees_x pap, financials_system_parameters fsp
672 where pap.business_group_id = fsp.business_group_id
673 and pap.employee_id = p_employee_id
674 and rownum = 1;
675 end if;
676
677 assign_employee(p_card_id, p_employee_id, l_full_name);
678 end;
679
680 --
681 -- This version should only be called by the web interface
682 -- This version does not delete the AP_CARD_DETAILS record
683 -- and assumes that the web version will take care of that.
684 -- (Kind of a workaround)
685 procedure assign_employee(p_card_id in number, p_employee_id in number, p_full_name in varchar2)
686 is
687
688 x_return_status VARCHAR2(4000);
689 x_msg_count NUMBER;
690 x_msg_data VARCHAR2(4000);
691 p_card_instrument IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE;
692 l_instrid NUMBER;
693 l_party_id NUMBER;
694 x_response IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
695 l_bool boolean := true;
696
697 begin
698 update ap_cards_all
699 set employee_id = p_employee_id,
700 cardmember_name = null,
701 physical_card_flag = 'Y',
702 paper_statement_req_flag = 'N',
703 last_update_date = sysdate,
704 last_updated_by = FND_GLOBAL.USER_ID,
705 last_update_login = FND_GLOBAL.LOGIN_ID
706 where card_id = p_card_id
707 and employee_id is null;-- bug 5224047
708
709 -- 8799736 - PADSS- Setting card_member_name to IBY tables
710 p_card_instrument.Card_Holder_Name := p_full_name;
711
712 -- this part of code keeps iby in synch with party assignments for a card
713 -- note that source of truth is oie (ap_cards_all.employee_id)
714 -- and not iby for assignments check.
715 begin
716 select card_reference_id into l_instrid
717 from ap_cards_all
718 where card_id = p_card_id;
719 p_card_instrument.card_id := l_instrid;
720 exception when others then
721 p_card_instrument.card_id := null;
722 l_bool := false;
723 end;
724 if (l_bool) then
725 begin
726 select party_id into l_party_id
727 from per_people_f ppf
728 where ppf.person_id = p_employee_id
729 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
730 and rownum = 1;
731 p_card_instrument.Owner_Id := l_party_id;
732 exception when others then
733 p_card_instrument.Owner_Id := null;
734 end;
735 iby_fndcpt_setup_pub.update_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data,
736 p_card_instrument, x_response);
737 end if;
738
739 delete from ap_card_emp_candidates where card_id = p_card_id;
740 end assign_employee;
741
742
743 ------------------------------------------------------------------------------
744 ------------------------------ (PRIVATE) -------------------------------------
745 ------------------------------------------------------------------------------
746
747 ------------------------------------------------------------------------------
748 --
749 -- Update VALIDATE_CODE columns to UNTESTED for the selected rows.
750 function set_row_set_internal
751 return number is
752 num_rows number;
753 begin
754 return execute_update( 'update ap_credit_card_trxns_all cc '||
755 'set validate_code = ''UNTESTED'' '||
756 'where validate_code <> ''Y'' ' ||
757 'and nvl(category,''BUSINESS'') <> ''DEACTIVATED'' ', false );
758 end set_row_set_internal;
759
760 ------------------------------------------------------------------------------
761 --
762 -- Execute an update statement on the set of transactions
763 -- (Builds SQL statement dynamically)
764 function execute_update(p_stmt_str in varchar2, p_valid_only in boolean) return number is
765 l_validate_where varchar2(50) := null;
766 begin
767
768 if p_valid_only then
769 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
770 end if;
771
772 if g_where_clause_type = 'ALL' then
773 execute immediate p_stmt_str || l_validate_where;
774 elsif g_where_clause_type = 'TRX_ID' then
775 execute immediate p_stmt_str || g_where_clause || l_validate_where using g_trx_id;
776 elsif g_where_clause_type = 'CARD_PROGRAM_ID' then
777 execute immediate p_stmt_str || g_where_clause || l_validate_where
778 using g_request_id, g_card_program_id, g_start_date, g_end_date;
779 end if;
780
781 return SQL%ROWCOUNT;
782
783 exception
784 when others then
785 declare
786 module varchar2(50) := 'ap.oie_cc_validations_pkg.execute_update';
787 begin
788 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
789 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, module, sqlerrm);
790 end if;
791 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
792 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, module, 'stmt = '||p_stmt_str);
793 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, module, 'where = '||g_where_clause);
794 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, module, 'valid = '||l_validate_where);
795 end if;
796 end;
797 raise;
798 end execute_update;
799
800 ------------------------------------------------------------------------------
801 --
802 -- Execute a select statement on the set of transactions
803 -- (Builds SQL statement dynamically)
804 procedure execute_select(c in out nocopy t_gen_cur,
805 p_stmt_str in out nocopy varchar2,
806 p_valid_only in boolean) is
807 l_validate_where varchar2(50) := null;
808 begin
809
810 if p_valid_only is not null then
811 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
812 end if;
813
814 if g_where_clause_type = 'ALL' then
815 open c for p_stmt_str || l_validate_where;
816 elsif g_where_clause_type = 'TRX_ID' then
817 open c for p_stmt_str || g_where_clause || l_validate_where using g_trx_id;
818 elsif g_where_clause_type = 'CARD_PROGRAM_ID' then
819 open c for p_stmt_str || g_where_clause || l_validate_where using g_request_id, g_card_program_id, g_start_date, g_end_date;
820 end if;
821 end execute_select;
822
823 ------------------------------------------------------------------------------
824 --
825 -- check_employee_termination
826 -- Author: Kristian Widjaja
827 -- Purpose: To set transaction category to personal of those transactions
828 -- that belong to terminated employees' credit cards and
829 -- have a transaction date greater than the employee's termination date.
830 -- Bug 3243527: Inactive Employees and Contingent Workers project
831 --
832 -- Input: p_valid_only - update valid transactions only or not
833 --
834 -- Output: the number of updated lines.
835 --
836
837 function check_employee_termination(p_valid_only in boolean) return number is
838 BEGIN
839 return execute_update(
840 -- Bug 3313557: Replaced TRXN alias with CC, so that it is compatible
841 -- with the rest of the dynamic SQL.
842 'UPDATE AP_CREDIT_CARD_TRXNS_ALL CC
843 SET CATEGORY = ''PERSONAL''
844 WHERE CATEGORY <> ''PERSONAL''
845 AND EXISTS (SELECT 1
846 FROM AP_CARDS_ALL CARD,
847 PER_EMPLOYEES_X P
848 WHERE AP_WEB_DB_HR_INT_PKG.IsPersonTerminated(CARD.employee_id)=''Y''
849 AND CARD.employee_id=P.employee_id
850 AND P.inactive_date < CC.transaction_date
851 AND CARD.card_program_id=CC.card_program_id
852 AND CARD.card_id=CC.card_id)', p_valid_only --
853 );
854 END check_employee_termination;
855
856 function validate_trx_detail_amount return number is
857 begin
858 return execute_update(
859 'update ap_credit_card_trxns_all cc
860 set trxn_detail_flag = NULL
861 where trxn_detail_flag = ''Y''
862 and abs(transaction_amount) <
863 (select abs(sum(transaction_amount))
864 from ap_cc_trx_details c
865 where c.trx_id = cc.trx_id)', true);
866 end validate_trx_detail_amount;
867
868 ------------------------------------------------------------------------------
869 --------------------------------- (2) ----------------------------------------
870 ------------------------------------------------------------------------------
871
872 ------------------------------------------------------------------------------
873 -- Default merchant name for AMEX for trxn types 01,02,03,06,09,10,11,12
874 -- based on card program
875 -- Bug 5516466 / 5526525 Also for Transaction Type 08
876 -- Bug 6743651 For adjustment transactions for MasterCard
877
878 function default_merchant_name(p_valid_only in boolean) return number is
879 begin
880 return execute_update( 'update ap_credit_card_trxns_all cc ' ||
881 'set merchant_name1 = (' ||
882 'select card_program_name ' ||
883 'from ap_card_programs_all apcp ' ||
884 'where apcp.card_program_id = cc.card_program_id and rownum = 1) ' ||
885 'where merchant_name1 is null ' ||
886 'and merchant_name2 is null ' ||
887 'and ( '||
888 '(transaction_type in (''01'',''02'',''03'',''06'',''08'',''09'',''10'',''11'',''12'') '||
889 'and (card_program_id in (select card_program_id '||
890 'from ap_card_programs_all where card_brand_lookup_code = ''American Express''))) '||
891 'or '||
892 '(merchant_activity = ''A'' '||
893 'and (card_program_id in (select card_program_id '||
894 'from ap_card_programs_all where card_brand_lookup_code = ''MasterCard'')))'||
895 ') ',
896 p_valid_only );
897 end default_merchant_name;
898
899
900 -- delete invalid records.
901 function delete_invalid_rows(p_valid_only in boolean, card_program_id in number ) return number is
902 num_rows number;
903 card_prog_where varchar2(80) := '';
904 begin
905
906 -- Bug 6616092
907 IF card_program_id is not null THEN
908 card_prog_where := ' and cc.card_program_id = ' || card_program_id ;
909 END IF;
910
911
912 --------------------------------------------------------------------
913 -- Delete invalid records
914 --------------------------------------------------------------------
915 IF(IBY_FNDCPT_SETUP_PUB.Get_Encryption_Patch_Level = 'PADSS') THEN
916 num_rows := execute_update('delete ap_credit_card_trxns_all cc where card_id in
917 (select card_id
918 from ap_cards_all apc, iby_creditcard icc
919 where apc.card_program_id = cc.card_program_id and
920 apc.card_id = cc.card_id and
921 icc.instrid = apc.card_reference_id and
922 icc.invalid_flag = ''Y'')
923 and cc.card_number is null
924 and cc.validate_code != ''Y''' || card_prog_where, p_valid_only);
925 ELSE
926 num_rows := execute_update('delete ap_credit_card_trxns_all cc where card_id not in
927 (select card_id
928 from ap_cards_all apc
929 where apc.card_program_id = cc.card_program_id and
930 apc.card_id = cc.card_id)
931 and cc.card_number is null
932 and cc.validate_code != ''Y''' || card_prog_where, p_valid_only);
933 END IF;
934 return num_rows;
935 end delete_invalid_rows;
936
937 -- Check for duplication transactions (reference number)
938 function duplicate_global_trx(p_valid_only in boolean) return number is
939 begin
940 --------------------------------------------------------------------
941 -- Duplicate transaction
942 --------------------------------------------------------------------
943 return execute_update(
944 'update ap_credit_card_trxns_all cc
945 set validate_code = ''DUPLICATE_TRANSACTION''
946 where
947 cc.card_program_id in
948 (select acp.card_program_id from ap_card_programs_all acp where gl_program_name =
949 (select gl_program_name from ap_card_programs_all where card_program_id = cc.card_program_id))
950 and exists
951 (select ''A corresponding transaction exists with this reference number''
952 from ap_credit_card_trxns_all cc2
953 where cc.reference_number = cc2.reference_number
954 and cc.trx_id <> cc2.trx_id)', p_valid_only
955 );
956 end duplicate_global_trx;
957
958 function default_detail_folio_type(p_valid_only in boolean) return number
959 is
960 c t_gen_cur;
961 stmt varchar2(2000);
962
963 l_validate_where varchar2(50) := null;
964 l_stmt varchar2(2000);
965
966 l_card_program_id number;
967 l_map_type_code varchar2(30);
968
969 l_count number := 0;
970 begin
971 stmt := 'select distinct cp.card_program_id, cp.card_exp_type_detail_map_code '||
972 'from ap_credit_card_trxns_all cc, ap_card_programs_all cp '||
973 'where cc.card_program_id = cp.card_program_id ';
974
975 if p_valid_only then
976 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
977 end if;
978
979 execute_select(c, stmt, p_valid_only);
980 loop
981 fetch c into l_card_program_id, l_map_type_code;
982 exit when c%notfound;
983
984 if l_map_type_code is not null then
985 l_stmt := 'update ap_cc_trx_details ac '||
986 'set folio_type = ap_map_types_pkg.get_map_to_code(:r0, ac.ext_folio_type) '||
987 'where trx_id in (select trx_id from ap_credit_card_trxns_all cc where cc.card_program_id = :r1';
988
989 if g_where_clause_type = 'ALL' THEN
990 l_stmt := l_stmt || l_validate_where || ')';
991 execute immediate l_stmt using l_map_type_code, l_card_program_id;
992 elsif g_where_clause_type = 'TRX_ID' THEN
993 l_stmt := l_stmt || g_where_clause || l_validate_where || ')';
994 execute immediate l_stmt using l_map_type_code, l_card_program_id, g_trx_id;
995 elsif g_where_clause_type = 'CARD_PROGRAM_ID' THEN
996 l_stmt := l_stmt || g_where_clause || l_validate_where || ')';
997 execute immediate l_stmt using l_map_type_code, l_card_program_id, g_request_id, g_card_program_id, g_start_date, g_end_date;
998 end if;
999
1000 l_count := l_count + SQL%ROWCOUNT;
1001 end if;
1002 end loop;
1003 close c;
1004
1005 return l_count;
1006 end default_detail_folio_type;
1007
1008
1009
1010 end ap_web_cc_validations_pkg;
[Home] [Help]