1 PACKAGE BODY AP_WEB_CC_VALIDATIONS_PKG as
2 /* $Header: apwccvlb.pls 120.16 2008/02/12 11:49:32 nram 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 -- Default folio type using folio type mapping rules
129 function default_folio_type(p_valid_only in boolean) return number
130 is
131 c t_gen_cur;
132 stmt varchar2(2000);
133
134 l_validate_where varchar2(50) := null;
135 l_stmt varchar2(2000);
136
137 l_card_program_id number;
138 l_map_type_code varchar2(30);
139 l_column_name varchar2(30);
140
141 l_count number := 0;
142 begin
143 stmt := 'select distinct cp.card_program_id, cp.card_exp_type_map_type_code, cp.card_exp_type_source_col '||
144 'from ap_credit_card_trxns_all cc, ap_card_programs_all cp '||
145 'where cc.card_program_id = cp.card_program_id ';
146
147 if p_valid_only then
148 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
149 end if;
150
151 execute_select(c, stmt, p_valid_only);
152 loop
153 fetch c into l_card_program_id, l_map_type_code, l_column_name;
154 exit when c%notfound;
155
156 if l_map_type_code is not null and l_column_name is not null then
157 l_stmt := 'update ap_credit_card_trxns_all cc '||
158 'set folio_type = ap_map_types_pkg.get_map_to_code(:r0, cc.'||l_column_name||') '||
159 'where cc.card_program_id = :r1';
160
161 if g_where_clause_type = 'ALL' then
162 execute immediate l_stmt || l_validate_where using l_map_type_code, l_card_program_id;
163 elsif g_where_clause_type = 'TRX_ID' then
164 execute immediate l_stmt || g_where_clause || l_validate_where using l_map_type_code, l_card_program_id, g_trx_id;
165 elsif g_where_clause_type = 'CARD_PROGRAM_ID' then
166 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;
167 end if;
168
169 l_count := l_count + SQL%ROWCOUNT;
170 end if;
171 end loop;
172 close c;
173
174 return l_count;
175 end default_folio_type;
176
177 ------------------------------------------------------------------------------
178 -- Default eLocation country code using elocation mapping rules
179 function default_country_code(p_valid_only in boolean) return number
180 is
181 l_count number;
182 begin
183 --
184 -- If a null merchant country was passed in,
185 -- default it based on various information of the card program.
186 l_count := execute_update('update ap_credit_card_trxns_all cc '||
187 'set merchant_country = nvl(merchant_country, ap_web_locations_pkg.default_country(card_program_id)) '||
188 'where merchant_country is null ', p_valid_only);
189
190 --
191 -- Default merchant country code
192 -- based on mapping rules
193 l_count := execute_update( 'update ap_credit_card_trxns_all cc '||
194 'set merchant_country_code = ' ||
195 '(select ap_map_types_pkg.get_map_to_code(c.country_map_type_code, cc.merchant_country) '||
196 ' from ap_card_programs_all c '||
197 ' where c.card_program_id = cc.card_program_id) '||
198 'where 1=1', p_valid_only );
199
200 return l_count;
201 end default_country_code;
202
203
204
205 ------------------------------------------------------------------------------
206 -- Assign payment flags (based on card specific info)
207 function default_payment_flag(p_valid_only in boolean) return number is
208 num_rows number;
209 begin
210 --------------------------------------------------------------------
211 -- Set Payment Flag
212 --
213 -- To distinguish payment transactions from debit/credit transactions
214 -- American Express (mis_industry_code = PA)
215 -- Diner's Club (transaction_type = PAYMENTS)
216 -- MasterCard (merchant_activity = A and transaction_amount < 0)
217 -- Visa (transaction_type = 0108)-- MC?
218 -- Bug 5976422 Description stores adjustment description and is specific for mastercard.
219 -- In case other loader program start storing description, code needs to be modified.
220 --------------------------------------------------------------------
221 -- OPEN ISSUE:
222 -- The following is straight out of APXCCVAL.rdf
223 -- The criteria for MasterCard IS incorrect.
224 -- 1) transaction_amount may be zero while billed_amount is negative
225 -- 2) may include refunds - not just payments
226 num_rows := execute_update(
227 ' update ap_credit_card_trxns_all cc
228 set payment_flag = ''Y''
229 where (mis_industry_code = ''PA''
230 or transaction_type = ''PAYMENTS''
231 or (upper(description) like ''%PAYMENT%'' and
232 (upper(description) not like ''%FEE%''
233 and upper(description) not like ''%REVERSAL%''
234 and upper(description) not like ''%ADJUSTMENT%''
235 and upper(description) not like ''%CREDIT%''
236 and upper(description) not like ''%DEBIT%''))
237 or transaction_type in (''0108'',''0440'')) ', p_valid_only);
238
239 return num_rows;
240 end default_payment_flag;
241
242
243 ------------------------------------------------------------------------------
244 -- Convert numeric currency codes into FND currency codes
245 function convert_currency_code(p_valid_only in boolean) return number is
246 num_rows number;
247 begin
248 num_rows := execute_update(
249 'update ap_credit_card_trxns_all cc
250 set cc.billed_currency_code =
251 (select currency_code
252 from ap_card_currencies
253 where numeric_currency_code = cc.billed_currency_code)
254 where cc.billed_currency_code is not null
255 and cc.billed_currency_code not in
256 (select fndcvl.currency_code
257 from fnd_currencies_vl fndcvl
258 where fndcvl.enabled_flag = ''Y''
259 and fndcvl.currency_flag = ''Y''
260 and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
261 and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate))
262 and exists
263 (select currency_code
264 from ap_card_currencies
265 where numeric_currency_code = cc.billed_currency_code)', p_valid_only
266 );
267
268
269 --------------------------------------------------------------------
270 -- Convert null billed currency codes
271 --------------------------------------------------------------------
272 num_rows := num_rows + execute_update(
273 'update ap_credit_card_trxns_all cc
274 set cc.billed_currency_code =
275 (select cp.card_program_currency_code
276 from ap_card_programs_all cp
277 where cp.card_program_id = cc.card_program_id)
278 where billed_currency_code is null', p_valid_only
279 );
280
281 --------------------------------------------------------------------
282 -- Convert posted currency codes
283 --------------------------------------------------------------------
284 num_rows := num_rows + execute_update(
285 'update ap_credit_card_trxns_all cc
286 set cc.posted_currency_code =
287 (select currency_code
288 from ap_card_currencies
289 where numeric_currency_code = cc.posted_currency_code)
290 where cc.posted_currency_code is not null
291 and exists
292 (select currency_code
293 from ap_card_currencies
294 where numeric_currency_code = cc.posted_currency_code)', p_valid_only
295 );
296
297 --------------------------------------------------------------------
298 -- Convert null 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 cp.card_program_currency_code
304 from ap_card_programs_all cp
305 where cp.card_program_id = cc.card_program_id)
306 where posted_currency_code is null', p_valid_only
307 );
308
309 return num_rows;
310 end convert_currency_code;
311
312
313 ------------------------------------------------------------------------------
314 -- eLocation integration
315 function get_locations(p_valid_only in boolean) return number is
316 stmt varchar2(2000);
317
318 l_loc_cur t_gen_cur;
319 l_cc_trx ap_credit_card_trxns_all%rowtype;
320
321 l_return_status varchar2(30);
322 l_msg_count number;
323 l_msg_data varchar2(2000);
324
325 num_rows number := 0;
326 begin
327 stmt := 'select *
328 from ap_credit_card_trxns_all cc
329 where location_id is null '||g_where_clause
330 || ' for update of location_id nowait';
331 execute_select(l_loc_cur, stmt, p_valid_only);
332 loop
333 fetch l_loc_cur into l_cc_trx;
334 exit when l_loc_cur%notfound;
335
336 num_rows := num_rows + 1;
337 ap_web_locations_pkg.get_location(l_cc_trx, l_return_status, l_msg_count, l_msg_data);
338
339 update ap_credit_card_trxns_all set location_id = l_cc_trx.location_id
340 where trx_id = l_cc_trx.trx_id;
341 end loop;
342 close l_loc_cur;
343
344 return num_rows;
345 end get_locations;
346
347 ------------------------------------------------------------------------------
348 -- Stamp CC Transactions with Payment Scenario of Card Program
349 function set_payment_scenario(p_valid_only in boolean) return number is
350 begin
351 return execute_update(
352 ' update ap_credit_card_trxns_all cc
353 set payment_due_from_code = (select payment_due_from_code
354 from ap_card_programs_all cp
355 where cp.card_program_id = cc.card_program_id)
356 where payment_due_from_code is null ', p_valid_only);
357 end set_payment_scenario;
358
359
360 ------------------------------------------------------------------------------
361 --------------------------------- (3) ----------------------------------------
362 ------------------------------------------------------------------------------
363
364 ------------------------------------------------------------------------------
365 -- Check for duplication transactions (card program, card number, reference number)
366 function duplicate_trx(p_valid_only in boolean) return number is
367 begin
368 --------------------------------------------------------------------
369 -- Duplicate transaction
370 --------------------------------------------------------------------
371 return execute_update(
372 'update ap_credit_card_trxns_all cc
373 set validate_code = ''DUPLICATE_TRANSACTION''
377 where cc.reference_number = cc2.reference_number
374 where exists
375 (select ''A corresponding transaction exists with this reference number''
376 from ap_credit_card_trxns_all cc2
378 and cc.trx_id <> cc2.trx_id
379 and cc.card_id = cc2.card_id
380 and cc.card_program_id = cc2.card_program_id)', p_valid_only
381 );
382 end duplicate_trx;
383
384 ------------------------------------------------------------------------------
385 -- Check for non-zero, non-null billed amount
386 function invalid_billed_amount(p_valid_only in boolean) return number is
387 BEGIN
388 --------------------------------------------------------------------
389 -- Invalid billed amount
390 --------------------------------------------------------------------
391 return execute_update(
392 'update ap_credit_card_trxns_all cc
393 set validate_code = ''INVALID_BILL_AMOUNT''
394 where (billed_amount is null
395 or billed_amount = 0)', p_valid_only
396 );
397 end invalid_billed_amount;
398
399 -- Check for valid billed currency code
400 function invalid_billed_currency_code(p_valid_only in boolean) return number is
401 begin
402 --------------------------------------------------------------------
403 -- Invalid billed currency code
404 --------------------------------------------------------------------
405 return execute_update(
406 'update ap_credit_card_trxns_all cc
407 set validate_code = ''INVALID_BILL_CURR_CODE''
408 where billed_currency_code is not null
409 and not exists
410 (select ''A corresponding currency exists in FND_CURRENCIES''
411 from fnd_currencies_vl fndcvl
412 where fndcvl.enabled_flag = ''Y''
413 and fndcvl.currency_flag = ''Y''
414 and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
415 and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate)
416 and fndcvl.currency_code = cc.billed_currency_code)', p_valid_only
417 );
418 end invalid_billed_currency_code;
419
420 ------------------------------------------------------------------------------
421 -- Check for non-null billed date
422 function invalid_billed_date(p_valid_only in boolean) return number is
423 begin
424 --------------------------------------------------------------------
425 -- Invalid billed date
426 --------------------------------------------------------------------
427 return execute_update(
428 'update ap_credit_card_trxns_all cc
429 set validate_code = ''INVALID_BILL_DATE''
430 where billed_date is null', p_valid_only
431 );
432 end invalid_billed_date;
433
434
435 ------------------------------------------------------------------------------
436 -- Check for inactive card number
437 function inactive_card_number(p_valid_only in boolean) return number is
438 num_rows number;
439 begin
440 --------------------------------------------------------------------
441 -- Inactive card number
442 --------------------------------------------------------------------
443 num_rows := execute_update(
444 'update ap_credit_card_trxns_all cc
445 set validate_code = ''INACTIVE_CARD_NUMBER''
446 where cc.transaction_date >=
447 (select max(nvl(apc.inactive_date,cc.transaction_date+1))
448 from ap_cards_all apc
449 where apc.card_program_id = cc.card_program_id
450 and apc.card_id = cc.card_id)', p_valid_only
451 );
452 return num_rows;
453 end inactive_card_number;
454
455 ------------------------------------------------------------------------------
456 -- Check for existing card number
457 function invalid_card_number(p_valid_only in boolean) return number is
458 num_rows number;
459 begin
460 --------------------------------------------------------------------
461 -- Invalid card number
462 --------------------------------------------------------------------
463 num_rows := execute_update(
464 'update ap_credit_card_trxns_all cc
465 set validate_code = ''INVALID_CARD_NUMBER''
466 where card_id not in
467 (select apc.card_id from ap_cards_all apc
468 where apc.card_program_id = cc.card_program_id
469 and apc.card_id = cc.card_id)', p_valid_only
470 );
471 return num_rows;
472 end invalid_card_number;
473
474 ------------------------------------------------------------------------------
475 -- Check for non-null merchant name
476 function invalid_merchant_name(p_valid_only in boolean) return number is
477 BEGIN
478 --------------------------------------------------------------------
479 -- Invalid merchant name
480 --------------------------------------------------------------------
481 --4730543 : skip validation for AME mis_industry_code 'SP' - Stop Payment
482 --and Transaction Type '05'
483 return execute_update(
484 'update ap_credit_card_trxns_all cc
485 set validate_code = ''INVALID_MERCH_NAME''
486 where (merchant_name1 is null
487 and merchant_name2 is null)
488 and ( (transaction_type in (''11'',''20'',''22'',''80'')
489 and sic_code <> ''6012''
490 and (card_program_id in (select card_program_id
491 from ap_card_programs_all
492 where card_brand_lookup_code = ''Visa'')))
493 or
497 where card_brand_lookup_code = ''MasterCard'')))
494 (merchant_activity <> ''A''
495 and (card_program_id in (select card_program_id
496 from ap_card_programs_all
498 or
499 (mis_industry_code <> ''SP''
500 and transaction_type <> ''05''
501 and card_program_id in (select card_program_id
502 from ap_card_programs_all
503 where card_brand_lookup_code = ''American Express''))
504 or
505 card_program_id not in (select card_program_id
506 from ap_card_programs_all
507 where card_brand_lookup_code in (''American Express'',''Visa'',''MasterCard''))
508 )
509 ', p_valid_only
510 );
511 end invalid_merchant_name;
512
513
514 ------------------------------------------------------------------------------
515 -- Check for valid posted currency code
516 function invalid_posted_currency_code(p_valid_only in boolean) return number is
517 begin
518 --------------------------------------------------------------------
519 -- Invalid posted currency code
520 --------------------------------------------------------------------
521 return execute_update(
522 'update ap_credit_card_trxns_all cc
523 set validate_code = ''INVALID_POST_CURR_CODE''
524 where posted_currency_code is not null
525 and posted_currency_code not in (select currency_code from ap_card_currencies) ', p_valid_only
526 );
527 end invalid_posted_currency_code;
528
529 ------------------------------------------------------------------------------
530 -- Check for non-zero, non-null transaction amount
531 function invalid_trx_amount(p_valid_only in boolean) return number is
532 BEGIN
533 --------------------------------------------------------------------
534 -- Invalid transaction amount
535 --------------------------------------------------------------------
536 return execute_update(
537 'update ap_credit_card_trxns_all cc
538 set validate_code = ''INVALID_TRX_AMOUNT''
539 where (transaction_amount is null
540 or transaction_amount = 0)', p_valid_only
541 );
542 end invalid_trx_amount;
543
544 ------------------------------------------------------------------------------
545 -- Check for non-null transaction date
546 function invalid_trx_date(p_valid_only in boolean) return number is
547 BEGIN
548 --------------------------------------------------------------------
549 -- Invalid transaction date
550 --------------------------------------------------------------------
551 return execute_update(
552 'update ap_credit_card_trxns_all cc
553 set validate_code = ''INVALID_TRX_DATE''
554 where transaction_date is null
555 and ((mis_industry_code <> ''PA''
556 and card_program_id in (select card_program_id
557 from ap_card_programs_all
558 where card_brand_lookup_code = ''American Express''))
559 or
560 card_program_id not in (select card_program_id
561 from ap_card_programs_all
562 where card_brand_lookup_code = ''American Express'')
563 )
564 ', p_valid_only
565 );
566 end invalid_trx_date;
567
568 ------------------------------------------------------------------------------
569 -- Marks the rows that are still valid as valid, and returns the number of
570 -- rows that are still valid
571 function valid_trx return number is
572 begin
573 return execute_update(
574 'update ap_credit_card_trxns_all cc
575 set validate_code = ''Y''
576 where validate_code = ''UNTESTED''', true);
577 end valid_trx;
578
579
580 -- sic_code is required if transaction_type code is 10,11,20,22,80
581 -- sic_code Must be equal to 6010, 6011, 6012, 6050 or 6051 if the
582 -- transaction type code is 20,22,80
583 -- The validation is required for Visa VCF 4.0 Format
584 function invalid_sic_code(p_valid_only in boolean) return number is
585 BEGIN
586 --------------------------------------------------------------------
587 -- Invalid mis_industry_code
588 --------------------------------------------------------------------
589 return execute_update(
590 'update ap_credit_card_trxns_all cc
591 set validate_code = ''INVALID_SIC_CODE''
592 where card_program_id in (select card_program_id
593 from ap_card_programs_all
594 where card_brand_lookup_code = ''Visa'')
595 and ((sic_code is null
596 and transaction_type in (''10'',''11'',''20'',''22'',''80''))
597 or
598 (transaction_type in (''20'',''22'',''80'')
599 and sic_code not in (''6010'', ''6011'', ''6012'', ''6050'', ''6051''))
600 )', p_valid_only
601 );
602 end invalid_sic_code;
603 ------------------------------------------------------------------------------
604 --------------------------------- (4) ----------------------------------------
605 ------------------------------------------------------------------------------
606
607 ------------------------------------------------------------------------------
608 -- Returns the lesser of date1 and date2. Null values are considered to
612 if date1 is null and date2 is null then
609 -- be a date in the infinite future.
610 function get_min_date(date1 in date, date2 in date) return date is
611 begin
613 return null;
614 elsif date1 is null then
615 return date2;
616 elsif date2 is null then
617 return date1;
618 elsif date1 < date2 then
619 return date1;
620 else
621 return date2;
622 end if;
623 end get_min_date;
624
625
626 ------------------------------------------------------------------------------
627 -- Assign the employee to the card and activate it.
628 procedure assign_employee(p_card_id in number, p_employee_id in number) is
629 l_full_name VARCHAR2(80);
630 begin
631 select full_name into l_full_name from ap_card_details where card_id = p_card_id;
632 if (l_full_name is null) then
633 select substrb(full_name, 1, 80) into l_full_name
634 from per_employees_x pap, financials_system_parameters fsp
635 where pap.business_group_id = fsp.business_group_id
636 and pap.employee_id = p_employee_id;
637 end if;
638
639 assign_employee(p_card_id, p_employee_id, l_full_name);
640 delete from ap_card_details where card_id = p_card_id;
641 -- commented as this is a duplicate statement in code flow.
642 -- delete from ap_card_emp_candidates where card_id = p_card_id;
643 end;
644
645 --
646 -- This version should only be called by the web interface
647 -- This version does not delete the AP_CARD_DETAILS record
648 -- and assumes that the web version will take care of that.
649 -- (Kind of a workaround)
650 procedure assign_employee(p_card_id in number, p_employee_id in number, p_full_name in varchar2)
651 is
652
653 x_return_status VARCHAR2(4000);
654 x_msg_count NUMBER;
655 x_msg_data VARCHAR2(4000);
656 p_card_instrument APPS.IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE;
657 l_instrid NUMBER;
658 l_party_id NUMBER;
659 x_response APPS.IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
660 l_bool boolean := true;
661
662 begin
663 update ap_cards_all
664 set employee_id = p_employee_id,
665 cardmember_name = p_full_name,
666 physical_card_flag = 'Y',
667 paper_statement_req_flag = 'N'
668 where card_id = p_card_id
669 and employee_id is null;-- bug 5224047
670
671 -- this part of code keeps iby in synch with party assignments for a card
672 -- note that source of truth is oie (ap_cards_all.employee_id)
673 -- and not iby for assignments check.
674 begin
675 select card_reference_id into l_instrid
676 from ap_cards_all
677 where card_id = p_card_id;
678 p_card_instrument.card_id := l_instrid;
679 exception when others then
680 p_card_instrument.card_id := null;
681 l_bool := false;
682 end;
683 if (l_bool) then
684 begin
685 select party_id into l_party_id
686 from per_people_f ppf
687 where ppf.person_id = p_employee_id
688 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
689 and rownum = 1;
690 p_card_instrument.Owner_Id := l_party_id;
691 exception when others then
692 p_card_instrument.Owner_Id := null;
693 end;
694 iby_fndcpt_setup_pub.update_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data,
695 p_card_instrument, x_response);
696 end if;
697
698 delete from ap_card_emp_candidates where card_id = p_card_id;
699 end assign_employee;
700
701
702 ------------------------------------------------------------------------------
703 ------------------------------ (PRIVATE) -------------------------------------
704 ------------------------------------------------------------------------------
705
706 ------------------------------------------------------------------------------
707 --
708 -- Update VALIDATE_CODE columns to UNTESTED for the selected rows.
709 function set_row_set_internal
710 return number is
711 num_rows number;
712 begin
713 return execute_update( 'update ap_credit_card_trxns_all cc '||
714 'set validate_code = ''UNTESTED'' '||
715 'where validate_code <> ''Y'' ' ||
716 'and nvl(category,''BUSINESS'') <> ''DEACTIVATED'' ', false );
717 end set_row_set_internal;
718
719 ------------------------------------------------------------------------------
720 --
721 -- Execute an update statement on the set of transactions
722 -- (Builds SQL statement dynamically)
723 function execute_update(p_stmt_str in varchar2, p_valid_only in boolean) return number is
724 l_validate_where varchar2(50) := null;
725 begin
726
727 if p_valid_only then
728 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
729 end if;
730
731 if g_where_clause_type = 'ALL' then
732 execute immediate p_stmt_str || l_validate_where;
733 elsif g_where_clause_type = 'TRX_ID' then
734 execute immediate p_stmt_str || g_where_clause || l_validate_where using g_trx_id;
735 elsif g_where_clause_type = 'CARD_PROGRAM_ID' then
736 execute immediate p_stmt_str || g_where_clause || l_validate_where
737 using g_request_id, g_card_program_id, g_start_date, g_end_date;
738 end if;
739
740 return SQL%ROWCOUNT;
741
742 exception
743 when others then
747 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
744 declare
745 module varchar2(50) := 'ap.oie_cc_validations_pkg.execute_update';
746 begin
748 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, module, sqlerrm);
749 end if;
750 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
751 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, module, 'stmt = '||p_stmt_str);
752 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, module, 'where = '||g_where_clause);
753 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, module, 'valid = '||l_validate_where);
754 end if;
755 end;
756 raise;
757 end execute_update;
758
759 ------------------------------------------------------------------------------
760 --
761 -- Execute a select statement on the set of transactions
762 -- (Builds SQL statement dynamically)
763 procedure execute_select(c in out nocopy t_gen_cur,
764 p_stmt_str in out nocopy varchar2,
765 p_valid_only in boolean) is
766 l_validate_where varchar2(50) := null;
767 begin
768
769 if p_valid_only is not null then
770 l_validate_where := ' AND CC.VALIDATE_CODE = ''UNTESTED''';
771 end if;
772
773 if g_where_clause_type = 'ALL' then
774 open c for p_stmt_str || l_validate_where;
775 elsif g_where_clause_type = 'TRX_ID' then
776 open c for p_stmt_str || g_where_clause || l_validate_where using g_trx_id;
777 elsif g_where_clause_type = 'CARD_PROGRAM_ID' then
778 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;
779 end if;
780 end execute_select;
781
782 ------------------------------------------------------------------------------
783 --
784 -- check_employee_termination
785 -- Author: Kristian Widjaja
786 -- Purpose: To set transaction category to personal of those transactions
787 -- that belong to terminated employees' credit cards and
788 -- have a transaction date greater than the employee's termination date.
789 -- Bug 3243527: Inactive Employees and Contingent Workers project
790 --
791 -- Input: p_valid_only - update valid transactions only or not
792 --
793 -- Output: the number of updated lines.
794 --
795
796 function check_employee_termination(p_valid_only in boolean) return number is
797 BEGIN
798 return execute_update(
799 -- Bug 3313557: Replaced TRXN alias with CC, so that it is compatible
800 -- with the rest of the dynamic SQL.
801 'UPDATE AP_CREDIT_CARD_TRXNS_ALL CC
802 SET CATEGORY = ''PERSONAL''
803 WHERE CATEGORY <> ''PERSONAL''
804 AND EXISTS (SELECT 1
805 FROM AP_CARDS_ALL CARD,
806 PER_EMPLOYEES_X P
807 WHERE AP_WEB_DB_HR_INT_PKG.IsPersonTerminated(CARD.employee_id)=''Y''
808 AND CARD.employee_id=P.employee_id
809 AND P.inactive_date < CC.transaction_date
810 AND CARD.card_program_id=CC.card_program_id
811 AND CARD.card_id=CC.card_id)', p_valid_only --
812 );
813 END check_employee_termination;
814
815 function validate_trx_detail_amount return number is
816 begin
817 return execute_update(
818 'update ap_credit_card_trxns_all cc
819 set trxn_detail_flag = NULL
820 where trxn_detail_flag = ''Y''
821 and abs(transaction_amount) <
822 (select abs(sum(transaction_amount))
823 from ap_cc_trx_details c
824 where c.trx_id = cc.trx_id)', true);
825 end validate_trx_detail_amount;
826
827 ------------------------------------------------------------------------------
828 --------------------------------- (2) ----------------------------------------
829 ------------------------------------------------------------------------------
830
831 ------------------------------------------------------------------------------
832 -- Default merchant name for AMEX for trxn types 01,02,03,06,09,10,11,12
833 -- based on card program
834 -- Bug 5516466 / 5526525 Also for Transaction Type 08
835 -- Bug 6743651 For adjustment transactions for MasterCard
836
837 function default_merchant_name(p_valid_only in boolean) return number is
838 begin
839 return execute_update( 'update ap_credit_card_trxns_all cc ' ||
840 'set merchant_name1 = (' ||
841 'select card_program_name ' ||
842 'from ap_card_programs_all apcp ' ||
843 'where apcp.card_program_id = cc.card_program_id and rownum = 1) ' ||
844 'where merchant_name1 is null ' ||
845 'and merchant_name2 is null ' ||
846 'and ( '||
847 '(transaction_type in (''01'',''02'',''03'',''06'',''08'',''09'',''10'',''11'',''12'') '||
848 'and (card_program_id in (select card_program_id '||
849 'from ap_card_programs_all where card_brand_lookup_code = ''American Express''))) '||
850 'or '||
851 '(merchant_activity = ''A'' '||
852 'and (card_program_id in (select card_program_id '||
853 'from ap_card_programs_all where card_brand_lookup_code = ''MasterCard'')))'||
854 ') ',
855 p_valid_only );
856 end default_merchant_name;
857
858
859 -- delete invalid records.
860 function delete_invalid_rows(p_valid_only in boolean, card_program_id in number ) return number is
861 num_rows number;
862 card_prog_where varchar2(80) := '';
863 begin
864
865 -- Bug 6616092
866 IF card_program_id is not null THEN
867 card_prog_where := ' and cc.card_program_id = ' || card_program_id ;
868 END IF;
869
870
871 --------------------------------------------------------------------
872 -- Delete invalid records
873 --------------------------------------------------------------------
874 num_rows := execute_update('delete ap_credit_card_trxns_all cc where card_id not in
875 (select card_id
876 from ap_cards_all apc
877 where apc.card_program_id = cc.card_program_id and
878 apc.card_id = cc.card_id)
879 and cc.card_number is null
880 and cc.validate_code != ''Y''' || card_prog_where, p_valid_only);
881 return num_rows;
882 end delete_invalid_rows;
883
884 end ap_web_cc_validations_pkg;
[Home] [Help]