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