1 PACKAGE BODY AP_CARD_VALIDATE_PKG AS
2 /* $Header: apwcvalb.pls 120.15.12020000.3 2012/07/14 21:13:29 pgayen ship $ */
3
4 ---------------------------------------------------------------------------
5 --
6 -- Procedure VALIDATE
7 --
8 -- Populates foreign keys and validates user-provided FKs.
9 -- Determines exceptions, populates REJECT_CODE
10 -- o CARD_ID - FK to AP_CARDS. Match on CARD_NUMBER
11 -- o CODE_ID - FK to AP_CARD_CODES.
12 -- Match on CODE_VALUE for CARD_CODE_SET defined for this card program
13 -- (CARD_PROGRAM_ID)
14 -- o EMPLOYEE_ID - denormalized from AP_CARDS.
15 -- o Validates Currency Codes, CARD_PROGRAM_ID
16 -- o Insures no duplicate reference numbers for this card program
17 -- o Exception raised if card profile is such that account is built from
18 -- code (BAFCF=Y) and distributions are created from line (CDF=Y) and
19 -- CODE_ID cannot be determined from CODE_VALUE.
20 -- o Exception raised if card profile is such that verification is
21 -- required (EVM=Y) but it is not specified that distributions be created
22 -- from lines (CDF=N or null).
23 --
24 -- Where
25 -- o EVM denotes AP_CARD_PROFILES.EMP_NOTIFICATION_LOOKUP_CODE
26 -- o MAM denotes AP_CARD_PROFILES.MGR_APPROVAL_LOOKUP_CODE
27 -- o CDF denotes AP_EXPENSE_FEED_LINES.CREATE_DISTRIBUTION_FLAG
28 -- o BAFCF denotes AP_CARD_PROFILES.BUILD_ACCT_FROM_CODE_FLAG
29 -- (AP_CARD_PROFILES is parent of AP_CARDS).
30 --
31 --
32 -- Assigned Exceptions. Exception code stored in
33 -- AP_EXPENSE_FEED_LINES.REJECT_CODE
34 --
35 -- AP_LOOKUP_CODES.LOOKUP_TYPE='CARD EXCEPTION'
36 --
37 -- Lookup Code Displayed Field Description
38 -- ==================== ====================== =============================
39 -- INVALID CARD NUM Invalid Card Number The Credit Card Number
40 -- does not match a defined
41 -- credit card
42 -- DUPLICATE REFERENCE Duplicate Reference Another transaction exists
43 -- with this reference number
44 -- INVALID POST CURR Invalid Post Currency The Posting Currency is not
45 -- recognized
46 -- INVALID TRX CURR Invalid Currency The Transaction Currency is
47 -- not recognized
48 -- INVALID CARD CODE Invalid Card Code The merchant category code is
49 -- not recognized for this card
50 -- program
51 -- DIST REQUIRED Distribution Required A distribution must be created
52 -- because employee or manager
53 -- audit is required
54 -- INVALID ACCOUNT Cannot determine An account cannot be
55 -- account determined.
56 --
57 -- EMPLOYEE NOT VALID Employee Not Valid The value of the employee id
58 -- (employee_id) is invalid. You
59 -- must enter a valid employee id
60 -- for supplier card transactions
61 -- when employee verification or
62 -- manager approval is enabled.
63 -- EMPLOYEE REQUIRED Employee Required You must enter a valid employee
64 -- id (employee_id) for supplier card
65 -- transactions when employee verification
66 -- or manager approval is enabled.
67 --
68 ---------------------------------------------------------------------------
69 PROCEDURE VALIDATE(
70 P_CARD_PROGRAM_ID IN NUMBER,
71 P_START_DATE IN DATE DEFAULT NULL,
72 P_END_DATE IN DATE DEFAULT NULL) IS
73 l_debug_info VARCHAR2(1000);
74 x_return_status VARCHAR2(4000);
75 x_msg_count NUMBER;
76 x_msg_data VARCHAR2(4000);
77 /* p_card_instrument APPS.IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE; Bug9789870 */
78 p_card_instrument IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE;
79 x_instr_id NUMBER;
80 l_card number;
81 /*x_response APPS.IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE; Bug9789870 */
82 x_response IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
83 l_card_id NUMBER;
84 l_card_reference_id ap_cards_all.card_reference_id%type ;
85 BEGIN
86
87 --------------------------------------------------------------------
88 l_debug_info :=
89 'Set REJECT_CODE to UNTESTED for those lines we will be validating.';
90 --------------------------------------------------------------------
91 --
92 -- No need to validate lines for which distributions have already
93 -- been created
94 --
95 update ap_expense_feed_lines efl
96 set reject_code = 'UNTESTED'
97 where card_program_id = P_CARD_PROGRAM_ID
98 and posted_date between nvl(P_START_DATE, posted_date - 1) and
99 nvl(P_END_DATE, posted_date + 1)
100 and not exists
101 (select 'A distribution exists for this expense feed line'
102 from ap_expense_feed_dists efd
103 where efd.feed_line_id = efl.feed_line_id);
104
105 --------------------------------------------------------------------
106 l_debug_info := 'Find the matching card number in AP_CARDS';
107 --------------------------------------------------------------------
108 /* update ap_expense_feed_lines efl
109 set card_id =
110 (select c.card_id
111 from ap_cards c
112 where c.card_number = efl.card_number)
113 where card_id is null
114 and reject_code = 'UNTESTED';*/
115
116 /*-------------------------------------------------------------------------------------------------------*/
117 --Changes done for PCARD project to reflect the new IBY Cards Model
118 /*We would allow the user to enter the card number from the control file(sql loader) and then
119 store it in ap_expense_feed_lines.Now while validation we would check if the card number
120 exists which would return instrument id if it exists and this with card-program-id would
121 be used to get the card_id which would be populated eventually*/
122 /*-------------------------------------------------------------------------------------------------------*/
123 for i in (select card_number,card_id,feed_line_id
124 from ap_expense_feed_lines where reject_code='UNTESTED'
125 )
126 loop
127 --8726861
128 BEGIN
129
130 select aca.card_reference_id
131 into l_card_reference_id
132 from ap_cards aca
133 where aca.card_id = i.card_id
134 and aca.card_reference_id is not null ;
135
136
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 update ap_expense_feed_lines efl
140 set reject_code = 'INVALID CARD ID'
141 where feed_line_id = i.feed_line_id ;
142 END ;
143
144 /* iby_fndcpt_setup_pub.card_exists(1.0,NULL,
145 x_return_status, x_msg_count, x_msg_data,
146 null ,trim(i.card_number), -- party id is null as we reference cards through ap_cards_all.employee_id
147 p_card_instrument, x_response);
148
149 if (x_return_status = 'S') then
150 x_instr_id := p_card_instrument.card_id;
151
152 if (x_instr_id is null) then
153 --------------------------------------------------------------------
154 l_debug_info := 'Reject Line if invalid card';
155 --------------------------------------------------------------------
156 update ap_expense_feed_lines efl
157 set reject_code = 'INVALID CARD NUM'
158 where feed_line_id=i.feed_line_id;
159 else
160 begin
161 select card_id into l_card from
162 ap_cards where card_reference_id=x_instr_id and rownum=1;
163 update ap_expense_feed_lines
164 set
165 card_id=l_card
166 where
167 feed_line_id=i.feed_line_id;
168 exception
169 when NO_DATA_FOUND then
170 --------------------------------------------------------------------
171 l_debug_info := 'Reject Line if invalid card';
172 --------------------------------------------------------------------
173 update ap_expense_feed_lines efl
174 set reject_code = 'INVALID CARD NUM'
175 where feed_line_id=i.feed_line_id;
176 end;
177 end if;
178 else -- Bug 5586412
179 --------------------------------------------------------------------
180 l_debug_info := 'Reject Line if invalid card';
181 --------------------------------------------------------------------
182 update ap_expense_feed_lines efl
183 set reject_code = 'INVALID CARD NUM'
184 where feed_line_id=i.feed_line_id;
185 end if; */
186 --8726861
187 end loop;
188 update ap_expense_feed_lines
189 set
190 card_number=-1
191 where
192 card_id is not null and reject_code='UNTESTED';
193
194 --------------------------------------------------------------------
195 l_debug_info := 'Update employee_id on line ';
196 --------------------------------------------------------------------
197 -- Bug 10238210. Added NOT EXISTS condition.
198 -- Employee id will not be updated for supplier
199 -- card transactions.
200
201 update ap_expense_feed_lines efl
202 set employee_id =
203 (select c.employee_id
204 from ap_cards c
205 where c.card_id = efl.card_id)
206 where card_id is not null
207 and NOT EXISTS (select 1 from ap_card_programs cp
208 where cp.card_program_id = efl.card_program_id
209 and cp.card_type_lookup_code = 'SUPPLIER')
210 and reject_code = 'UNTESTED';
211
212
213 --------------------------------------------------------------------
214 l_debug_info := 'Check for duplicate reference number. ';
215 --------------------------------------------------------------------
216 update ap_expense_feed_lines efl
217 set reject_code = 'DUPLICATE REFERENCE'
218 where exists
219 (select 'A corresponding line already exists with this reference number'
220 from ap_expense_feed_lines efl2
221 where efl.reference_number = efl2.reference_number
222 and efl.feed_line_id <> efl2.feed_line_id
223 and efl2.card_program_id = P_CARD_PROGRAM_ID)
224 and reject_code = 'UNTESTED';
225
226 --------------------------------------------------------------------
227 l_debug_info := 'Check for invalid Posted Currency Code';
228 --------------------------------------------------------------------
229 update ap_expense_feed_lines efl
230 set reject_code = 'INVALID POST CURR'
231 where posted_currency_code is not null
232 and not exists
233 (select 'A corresponding currency exists in FND_CURRENCIES'
234 from fnd_currencies_vl fndcvl
235 where fndcvl.enabled_flag = 'Y'
236 and fndcvl.currency_flag = 'Y'
237 and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
238 and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate)
239 and fndcvl.currency_code = efl.posted_currency_code)
240 and reject_code = 'UNTESTED';
241
242 --------------------------------------------------------------------
243 l_debug_info :=
244 'If a posted currency has not been specified, default from card program';
245 --------------------------------------------------------------------
246 update ap_expense_feed_lines efl
247 set posted_currency_code =
248 (select cp.card_program_currency_code
249 from ap_card_programs cp
250 where cp.card_program_id = efl.card_program_id)
251 where posted_currency_code is null
252 and reject_code = 'UNTESTED';
253
254 --------------------------------------------------------------------
255 l_debug_info := 'Check for invalid Transaction Currency Code';
256 --------------------------------------------------------------------
257 update ap_expense_feed_lines efl
258 set reject_code = 'INVALID TRX CURR'
259 where not exists
260 (select 'A corresponding currency exists in FND_CURRENCIES'
261 from fnd_currencies_vl fndcvl
262 where fndcvl.enabled_flag = 'Y'
263 and fndcvl.currency_flag = 'Y'
264 and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
265 and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate)
266 and fndcvl.currency_code = efl.posted_currency_code)
267 and reject_code = 'UNTESTED';
268
269 --------------------------------------------------------------------
270 l_debug_info := 'Check for invalid Card Code';
271 --------------------------------------------------------------------
272 update ap_expense_feed_lines efl
273 set reject_code = 'INVALID CARD CODE'
274 where not exists
275 (select 'A corresponding card code exists in AP_CARD_CODES'
276 from ap_card_codes cc,
277 ap_card_programs cp
278 where cc.code_value = efl.card_code_value
279 and cc.code_set_id = cp.card_code_set_id
280 and cp.card_program_id = P_CARD_PROGRAM_ID)
281 and nvl(create_distribution_flag,'N') = 'Y'
282 and exists
283 (select 'Profile mandates building account from card code'
284 from ap_card_profiles cp,
285 ap_cards c
286 where cp.profile_id = c.profile_id
287 and c.card_id = efl.card_id
288 and nvl(cp.build_acct_from_code_flag,'N') = 'Y')
289 and reject_code = 'UNTESTED';
290
291 --------------------------------------------------------------------
292 l_debug_info := 'Check for Distribution Required';
293 --------------------------------------------------------------------
294 update ap_expense_feed_lines efl
295 set reject_code = 'DIST REQUIRED'
296 where exists
297 (select 'Employee verification or manager approval required'
298 from ap_card_profiles cp,
299 ap_cards c
300 where cp.profile_id = c.profile_id
301 and c.card_id = efl.card_id
302 and (cp.emp_notification_lookup_code = 'Y' OR
303 cp.mgr_approval_lookup_code = 'Y'))
304 and nvl(create_distribution_flag,'N') <> 'Y'
305 and reject_code = 'UNTESTED';
306
307 /* Bug 10238210. Start
308 -- It is mandatory to have a valid employee in ap_expense_feed_lines
309 -- for supplier card transactions when notifications are enabled
310 -- for the card profile. If notifications are not enabled, then
311 -- employee validation will not be performed. Following two checks
312 -- for valid employee and null employee cases.
313 */
314
315 --------------------------------------------------------------------
316 l_debug_info := 'Check for valid employee for supplier card transactions ' ||
317 'when employee or manager notifications are set.' ;
318 --------------------------------------------------------------------
319 update ap_expense_feed_lines efl
320 set reject_code = 'EMPLOYEE NOT VALID'
321 where not exists
322 (select 1
323 from hr_employees_current_v hremp
324 where efl.employee_id = hremp.employee_id
325 )
326 and exists (select 1 from ap_card_programs cp
327 where cp.card_program_id = efl.card_program_id
328 and cp.card_type_lookup_code = 'SUPPLIER')
329 and employee_id is not null
330 and reject_code = 'UNTESTED';
331
332
333 --------------------------------------------------------------------
334 --bug 13071875,
335 l_debug_info := 'check if the employee is inactive';
336 ----------------------------------------------------------------------
337
338 update ap_expense_feed_lines efl
339 set reject_code = 'EMPLOYEE INACTIVE'
340 where reject_code = 'UNTESTED'
341 and not exists (select 'employee is in hr_employees_current_v'
342 from hr_employees_current_v hremp
343 where hremp.employee_id = efl.employee_id)
344 and card_id is not null
345 and card_program_id = P_CARD_PROGRAM_ID
346 and posted_date between nvl(P_START_DATE, posted_date - 1) and
347 nvl(P_END_DATE, posted_date + 1);
348
349 --------------------------------------------------------------------
350 l_debug_info := 'Check for null employee id for supplier card transactions ' ||
351 'when employee or manager notifications are set.' ;
352 --------------------------------------------------------------------
353 update ap_expense_feed_lines efl
354 set reject_code = 'EMPLOYEE REQUIRED'
355 where not exists
356 (select 1
357 from ap_card_profiles cp
358 where efl.card_program_id = cp.card_program_id
359 and nvl(cp.emp_notification_lookup_code, 'N') = 'N'
360 and nvl(cp.mgr_approval_lookup_code, 'N') = 'N'
361 )
362 and exists (select 1 from ap_card_programs cp
363 where cp.card_program_id = efl.card_program_id
364 and cp.card_type_lookup_code = 'SUPPLIER')
365 and employee_id is null
366 and reject_code = 'UNTESTED';
367
368 -- Bug 10238210. End.
369
370 --------------------------------------------------------------------
371 l_debug_info :=
372 'Set REJECT_CODE to null for those lines which remain UNTESTED.';
373 --------------------------------------------------------------------
374 --
375 -- These are valid lines
376 --
377 update ap_expense_feed_lines
378 set reject_code = ''
379 where card_program_id = P_CARD_PROGRAM_ID
380 and posted_date between nvl(P_START_DATE, posted_date - 1) and
381 nvl(P_END_DATE, posted_date + 1)
382 and reject_code = 'UNTESTED';
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 IF (SQLCODE <> -20001) THEN
387 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
388 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
389 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'VALIDATE');
390 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
391 END IF;
392 APP_EXCEPTION.RAISE_EXCEPTION;
393
394 END;
395
396 ---------------------------------------------------------------------------
397 --
398 -- Procedure CREATE_DISTRIBUTIONS
399 --
400 -- Creates records in AP_EXPENSE_FEED_DISTS where CDF=Y.
401 -- CCID determined by looking at the BAFCF.
402 -- o If BAFCF=Y then use overlay account defined by CODE_ID on
403 -- top of DEFAULT_ACCT_TEMPLATE. Overlay resulting segments on
404 -- top of Employee Acct CCID (from HR_EMPLOYEES_CURRENT_V).
405 -- o If BAFCF=N overlay account segments defined in
406 -- DEFAULT_ACCT_TEMPLATE on top of Employee Acct CCID (from
407 -- HR_EMPLOYEES_CURRENT_V).
408 -- o If error results from operations above, use
409 -- AP_CARD_PROFILES.EXCEPTION_CLEARING_CCID. If CCID not defined at
410 -- profile, use AP_CARD_PROGRAMS.EXCEPTION_CLEARING_CCID.
411 -- o If CCID still cannot be determined, populate REJECT_CODE to flag
412 -- as invalid account exception.
413 --
414 -- Where
415 -- o EVM denotes AP_CARD_PROFILES.EMP_NOTIFICATION_LOOKUP_CODE
416 -- o MAM denotes AP_CARD_PROFILES.MGR_APPROVAL_LOOKUP_CODE
417 -- o CDF denotes AP_EXPENSE_FEED_LINES.CREATE_DISTRIBUTION_FLAG
418 -- o BAFCF denotes AP_CARD_PROFILES.BUILD_ACCT_FROM_CODE_FLAG
419 -- (AP_CARD_PROFILES is parent of AP_CARDS).
420 --
421 ---------------------------------------------------------------------------
422 PROCEDURE CREATE_DISTRIBUTIONS(
423 P_CARD_PROGRAM_ID IN NUMBER,
424 P_START_DATE IN DATE DEFAULT NULL,
425 P_END_DATE IN DATE DEFAULT NULL,
426 P_RETURN_ERROR_MESSAGE IN OUT NOCOPY VARCHAR2,
427 P_REQUEST_ID IN NUMBER) IS
428 l_default_emp_ccid HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE;
429 l_default_emp_segments FND_FLEX_EXT.SEGMENTARRAY;
430 l_final_segments FND_FLEX_EXT.SEGMENTARRAY;
431 l_def_acct_template_array FND_FLEX_EXT.SEGMENTARRAY;
432 l_exp_line_acct_segs_array FND_FLEX_EXT.SEGMENTARRAY;
433 l_exp_line_ccid NUMBER;
434 l_cant_flexbuild_reason VARCHAR2(2000);
435 l_cant_flexbuild_flag BOOLEAN := FALSE;
436 l_num_segments NUMBER;
437 l_flex_segment_number NUMBER;
438 l_cc_flex_segment_number NUMBER;
439 l_flex_segment_delimiter VARCHAR2(1);
440 l_employee_ccid NUMBER(15);
441 l_employee_id NUMBER(15);
442 INVALID_ACCT_EXCEPTION EXCEPTION;
443 l_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
444 l_debug_info VARCHAR2(100);
445 l_feed_line_id NUMBER;
446 l_amount NUMBER;
447 l_card_code_value AP_EXPENSE_FEED_LINES.card_code_value%TYPE;
448 l_exception_clearing_ccid NUMBER;
449 l_build_acct_from_code_flag AP_CARD_PROFILES.build_acct_from_code_flag%TYPE;
450 l_default_acct_template AP_CARD_PROFILES.default_acct_template%TYPE;
451 l_distribution_status AP_EXPENSE_FEED_DISTS.status_lookup_code%TYPE;
452 l_cost_center AP_EXPENSE_FEED_DISTS.cost_center%TYPE;
453 l_account_segment_value AP_EXPENSE_FEED_DISTS.account_segment_value%TYPE;
454 l_card_code_set_id NUMBER;
455 l_org_id NUMBER;
456 l_description VARCHAR2(240); -- Bug 977059
457
458 --
459 -- Cursor that loops through lines for which distributions should be created.
460 --
461 -- Legend to AP_CARD_PROFILES lookup codes
462 -- emp_notification_lookup_code = Employee Verification Method
463 -- Y = Verification Required
464 -- I = Notification Only
465 -- N = None
466 --
467 -- mgr_approval_lookup_code = Manager Approval Method
468 -- Y = Approval Required
469 -- I = Notification Only
470 -- N = None
471 --
472 cursor lines_cursor is
473 select efl.feed_line_id,
474 efl.amount,
475 efl.card_code_value,
476 nvl(cpr.exception_clearing_ccid,cpg.exception_clearing_ccid),
477 nvl(cpr.build_acct_from_code_flag,'N'),
478 cpr.default_acct_template,
479 hremp.default_code_combination_id,
480 cpg.card_code_set_id,
481 decode(cpr.mgr_approval_lookup_code,
482 'Y',decode(cpr.emp_notification_lookup_code,
483 'Y','VALIDATED',
484 'I','VALIDATED',
485 'VERIFIED'),
486 'I',decode(cpr.emp_notification_lookup_code,
487 'Y','VALIDATED',
488 'I','VALIDATED',
489 'VERIFIED'),
490 decode(cpr.emp_notification_lookup_code,
491 'Y','VALIDATED',
492 'I','VALIDATED',
493 'APPROVED')),
494 efl.description
495 from ap_expense_feed_lines efl,
496 ap_card_programs cpg,
497 ap_card_profiles cpr,
498 ap_cards c,
499 hr_employees_current_v hremp,
500 IBY_FNDCPT_PAYER_ALL_INSTRS_V IBY
501 where efl.card_id = c.card_id
502 and c.card_reference_id=IBY.instrument_id
503 and c.profile_id = cpr.profile_id
504 and cpr.card_program_id = cpg.card_program_id
505 and efl.employee_id = hremp.employee_id (+) -- Bug 10238210.
506 and efl.create_distribution_flag = 'Y'
507 and posted_date between nvl(P_START_DATE, posted_date - 1) and
508 nvl(P_END_DATE, posted_date + 1)
509 and reject_code is NULL
510 AND iby.instrument_type='CREDITCARD' -- veramach added for bug 7196074
511 and not exists
512 (select 'A distribution exists for this expense feed line'
513 from ap_expense_feed_dists efd
514 where efd.feed_line_id = efl.feed_line_id)
515 and efl.card_program_id = P_CARD_PROGRAM_ID;
516
517 BEGIN
518 ----------------------------------------
519 l_debug_info := 'Get Chart of Accounts ID';
520 ----------------------------------------
521 select GS.chart_of_accounts_id
522 into l_chart_of_accounts_id
523 from ap_system_parameters S,
524 gl_sets_of_books GS
525 where GS.set_of_books_id = S.set_of_books_id;
526 ----------------------------------------
527 l_debug_info := 'Get Segment Delimiter';
528 ----------------------------------------
529 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
530 'SQLGL',
531 'GL#',
532 l_chart_of_accounts_id);
533
534 IF (l_flex_segment_delimiter IS NULL) THEN
535 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
536 return;
537 END IF;
538
539 -----------------------------------------------
540 l_debug_info := 'Get Cost Center Qualifier Segment Number';
541 -----------------------------------------------
542
543 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
544 101,
545 'GL#',
546 l_chart_of_accounts_id,
547 'FA_COST_CTR',
548 l_cc_flex_segment_number)) THEN
549 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
550 return;
551 END IF;
552
553 -----------------------------------------------
554 l_debug_info := 'Get Account Qualifier Segment Number';
555 -----------------------------------------------
556
557 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
558 101,
559 'GL#',
560 l_chart_of_accounts_id,
561 'GL_ACCOUNT',
562 l_flex_segment_number)) THEN
563 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
564 return;
565 END IF;
566
567 OPEN lines_cursor;
568
569 LOOP
570
571 FETCH lines_cursor INTO l_feed_line_id,
572 l_amount,
573 l_card_code_value,
574 l_exception_clearing_ccid,
575 l_build_acct_from_code_flag,
576 l_default_acct_template,
577 l_default_emp_ccid,
578 l_card_code_set_id,
579 l_distribution_status,
580 l_description;
581
582 EXIT WHEN lines_cursor%NOTFOUND;
583
584 BEGIN
585
586 -----------------------------------------------
587 l_debug_info := 'Resolve the account from card code';
588 -----------------------------------------------
589
590 if (l_build_acct_from_code_flag='Y') then
591
592 BEGIN
593 select account_segment_value
594 into l_account_segment_value
595 from ap_card_codes
596 where code_set_id = l_card_code_set_id
597 and code_value = l_card_code_value;
598 EXCEPTION
599 when NO_DATA_FOUND then
600 null;
601 END;
602
603 end if;
604
605 -----------------------------------------------------------------
606 l_debug_info := 'Get employee default ccid account segments';
607 -----------------------------------------------------------------
608
609 if (nvl(l_default_emp_ccid,-1) <> -1) then
610
611 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
612 'SQLGL',
613 'GL#',
614 l_chart_of_accounts_id,
615 l_default_emp_ccid,
616 l_num_segments,
617 l_default_emp_segments)) THEN
618
619 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
620 raise INVALID_ACCT_EXCEPTION;
621 END IF;
622
623 else --9553865 added else clause
624 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
625 raise INVALID_ACCT_EXCEPTION;
626
627 end if;
628
629 if (l_default_acct_template is not null) then
630
631 l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_default_acct_template,
632 l_flex_segment_delimiter,
633 l_def_acct_template_array);
634 end if ;
635
636 -- 9553865 added above if clause so that l_num_segments is not overriden
637
638
639
640 FOR i IN 1..l_num_segments LOOP
641
642 IF (l_default_acct_template IS NOT NULL AND -- 9553865 added l_default_acct_template
643 l_def_acct_template_array(i) IS NOT NULL) THEN
644
645 l_exp_line_acct_segs_array(i) := l_def_acct_template_array(i);
646
647 ELSE
648
649 l_exp_line_acct_segs_array(i) :=l_default_emp_segments(i);
650
651 END IF;
652
653
654 END LOOP;
655
656 ---------------------------------------------------
657 l_debug_info := 'Overlay the account segment';
658 ---------------------------------------------------
659
660 if (l_build_acct_from_code_flag='Y'
661 and l_account_segment_value is not null) then
662 l_exp_line_acct_segs_array(l_flex_segment_number) :=
663 l_account_segment_value;
664 end if;
665
666 --------------------------------------------------------------
667 l_debug_info := 'Retrieve new ccid with overlaid account';
668 --------------------------------------------------------------
669
670 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID(
671 'SQLGL',
672 'GL#',
673 l_chart_of_accounts_id,
674 SYSDATE,
675 l_num_segments,
676 l_exp_line_acct_segs_array,
677 l_exp_line_ccid)) THEN
678
679 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
680 raise INVALID_ACCT_EXCEPTION;
681 END IF;
682
683 EXCEPTION
684 WHEN INVALID_ACCT_EXCEPTION THEN
685 l_cant_flexbuild_flag := TRUE;
686
687 END;
688
689 if (l_cant_flexbuild_flag and
690 nvl(l_exception_clearing_ccid,-1) = -1) then
691 --
692 -- Acct build resulted in an error and cannot use exception clearing CCID
693 --
694 --------------------------------------------------------------
695 l_debug_info := 'Update the REJECT_CODE for INVALID ACCOUNT';
696 --------------------------------------------------------------
697
698 update ap_expense_feed_lines
699 set reject_code = 'INVALID ACCOUNT'
700 where feed_line_id = l_feed_line_id;
701 else
702 if (l_cant_flexbuild_flag) then
703 --
704 -- Acct build resulted in an error; use exception clearing CCID
705 --
706 l_exp_line_ccid := l_exception_clearing_ccid;
707 end if;
708
709 -----------------------------------------------------------------
710 l_debug_info := 'Get final ccid account segments';
711 -----------------------------------------------------------------
712
713 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
714 'SQLGL',
715 'GL#',
716 l_chart_of_accounts_id,
717 l_exp_line_ccid,
718 l_num_segments,
719 l_final_segments)) THEN
720
721 p_return_error_message := l_debug_info||': '||FND_MESSAGE.GET;
722 return;
723 END IF;
724
725 --
726 -- Extract account segment and cost center values from final flex
727 --
728 l_account_segment_value := l_final_segments(l_flex_segment_number);
729 l_cost_center := l_final_segments(l_cc_flex_segment_number);
730
731 --
732 -- Insert record into AP_EXPENSE_FEED_DISTS
733 --
734 select org_id into l_org_id
735 from
736 ap_expense_feed_lines
737 where
738 feed_line_id=l_feed_line_id;
739 Mo_Global.set_policy_context('S',l_org_id);
740 insert into AP_EXPENSE_FEED_DISTS_ALL
741 (FEED_LINE_ID,
742 FEED_DISTRIBUTION_ID,
743 AMOUNT,
744 DIST_CODE_COMBINATION_ID,
745 STATUS_CHANGE_DATE,
746 STATUS_LOOKUP_CODE,
747 ACCOUNT_SEGMENT_VALUE,
748 ACCOUNT_SEGMENT_VALUE_DEFAULT,
749 COST_CENTER,
750 DESCRIPTION,
751 LAST_UPDATE_DATE,
752 LAST_UPDATED_BY,
753 LAST_UPDATE_LOGIN,
754 CREATION_DATE,
755 CREATED_BY,ORG_ID,
756 CONC_REQUEST_ID) VALUES
757 (l_feed_line_id,
758 ap_expense_feed_dists_s.nextval,
759 l_amount,
760 l_exp_line_ccid,
761 sysdate,
762 l_distribution_status,
763 l_account_segment_value,
764 l_account_segment_value,
765 l_cost_center,
766 l_description,
767 sysdate,
768 -1,
769 -1,
770 sysdate,
771 -1,l_org_id,P_REQUEST_ID);
772
773 end if;
774
775 l_cant_flexbuild_flag := FALSE;
776 l_account_segment_value := '';
777 l_cost_center := '';
778
779 END LOOP;
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 IF (SQLCODE <> -20001) THEN
784 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
785 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
786 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CREATE_DISTRIBUTIONS');
787 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
788 END IF;
789 APP_EXCEPTION.RAISE_EXCEPTION;
790
791 END;
792
793 ---------------------------------------------------------------------------
794 --
795 -- Procedure CREATE_INTERFACE_RECORDS
796 --
797 -- Creates Invoice in new AP Interface table for Credit Card Transactions
798 --
799 -- Temporary stub that calls AP_CARD_INVOICE_PKG.CREATE_INVOICE
800 --
801 ---------------------------------------------------------------------------
802 PROCEDURE CREATE_INTERFACE_RECORDS(
803 P_CARD_PROGRAM_ID IN NUMBER,
804 P_INVOICE_ID IN OUT NOCOPY NUMBER,
805 P_START_DATE IN DATE DEFAULT NULL,
806 P_END_DATE IN DATE DEFAULT NULL,
807 P_ROLLUP_FLAG IN VARCHAR2 DEFAULT 'Y') IS
808
809 l_debug_info VARCHAR2(100);
810 BEGIN
811 --
812 -- Call implementation AP_CARD_INVOICE_PKG.CREATE_INVOICE
813 --
814 AP_CARD_INVOICE_PKG.CREATE_INVOICE(
815 P_CARD_PROGRAM_ID,
816 P_INVOICE_ID,
817 P_START_DATE,
818 P_END_DATE,
819 P_ROLLUP_FLAG);
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 IF (SQLCODE <> -20001) THEN
824 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
825 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
826 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'CREATE_INTERFACE_RECORDS');
827 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
828 END IF;
829 APP_EXCEPTION.RAISE_EXCEPTION;
830
831 END;
832
833 END AP_CARD_VALIDATE_PKG;
[Home] [Help]