1 PACKAGE BODY AP_CARD_UTILITY_PKG AS
2 /* $Header: apwpcutb.pls 120.8 2006/10/26 15:32:10 pranpaul noship $ */
3
4 -- Constants
5 c_strImagePath CONSTANT varchar2(100) := '/OA_MEDIA/';
6
7
8
9 PROCEDURE WF_UTILS(
10 p_desc in VARCHAR,p_out out NOCOPY VARCHAR)
11 IS
12 l_desc varchar2(240):='SUperb';
13 BEGIN
14 p_out:=RTRIM(wf_notification.SubstituteSpecialChars(p_desc));
15 END;
16
17
18 --------------------------------------------------------------------------
19 -- Function BUILD_ACCOUNT
20 --
21 -- Returns code_combination_id (number) after overlaying segment values
22 -- qualified as cost center (FA_COST_CTR) and account (GL_ACCOUNT) on the
23 -- segments identified by P_CODE_COMBINATION_ID.
24 --
25 -- Cost center and account are validated independently before overlaying
26 -- is attempted. Errors resulting from independent segment validation are
27 -- returned as P_ERROR_MESSAGE and the operation is aborted.
28 --
29 -- If new combination results in an error, the flex API error will be
30 -- returned as P_ERROR_MESSAGE and the value -1 will be returned.
31 --
32 --------------------------------------------------------------------------
33 PROCEDURE BUILD_ACCOUNT(
34 P_CODE_COMBINATION_ID IN NUMBER,
35 P_COST_CENTER IN VARCHAR2,
36 P_ACCOUNT_SEGMENT_VALUE IN VARCHAR2,
37 P_ERROR_MESSAGE IN VARCHAR2,
38 P_CALLING_SEQUENCE IN VARCHAR2,
39 P_EMPLOYEE_ID IN NUMBER,
40 P_CCID OUT NOCOPY VARCHAR )
41 IS
42 l_segments FND_FLEX_EXT.SEGMENTARRAY;
43 l_code_combination_id NUMBER;
44 l_num_segments NUMBER;
45 l_flex_segment_number NUMBER;
46 l_cc_flex_segment_number NUMBER;
47 l_chart_of_accounts_id NUMBER;
48 l_concatenated_segments VARCHAR2(2000);
49 l_flex_segment_delimiter VARCHAR2(1);
50 l_debug_info VARCHAR2(100);
51 l_current_calling_sequence VARCHAR2(2000);
52 L_ERROR_MESSAGE varchar2(1000);
53 BEGIN
54
55 l_current_calling_sequence := 'AP_CARD_VERIFY_PKG.BUILD_ACCOUNT';
56 --
57 -- Return P_CODE_COMBINATION_ID is P_COST_CENTER and
58 -- P_ACCOUNT_SEGMENT_VALUE are null
59 --
60 if (P_COST_CENTER is null and
61 P_ACCOUNT_SEGMENT_VALUE='-1') then
62 P_CCID:=''||P_CODE_COMBINATION_ID||'';
63 return;
64 end if;
65
66 --
67 -- Validate Cost Center if passed to API.
68 --
69
70 if (P_COST_CENTER is not null) then
71 ----------------------------------------
72 l_debug_info := 'Validating Cost Center';
73 ----------------------------------------
74 ValidateCostCenter(
75 P_COST_CENTER,
76 L_ERROR_MESSAGE,
77 P_EMPLOYEE_ID); --2664451
78
79 if (L_ERROR_MESSAGE is not null) then
80 --2484206
81 --the validate cost center returns an encoded message for versions
82 --of apwvutlb.pls 115.56 and higher. The unique thing about an encoded
83 --message is that it will have a chr(0) in it. This seperates the
84 --application from the message name. So if we receive an encoded message
85 --we will get the message string. The reason I am doing this this way
86 --is to avoid a dependency between pcards and OIE.
87
88 if instrb(l_error_message,chr(0)) <> 0 then
89 fnd_message.set_encoded(p_error_message);
90 l_error_message := fnd_message.get();
91 end if;
92 P_CCID:=''||'-1'||'';
93 return;
94 end if;
95 end if;
96
97 /* FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID,
98 FND_GLOBAL.RESP_ID,
99 FND_GLOBAL.RESP_APPL_ID);*/
100
101
102 ----------------------------------------
103 l_debug_info := 'Get Chart of Accounts ID';
104 ----------------------------------------
105
106 IF (NOT GetCOAofSOB(l_chart_of_accounts_id)) THEN
107 NULL;
108 END IF;
109
110 ----------------------------------------
111 l_debug_info := 'Get Segment Delimiter';
112 ----------------------------------------
113
114 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
115 'SQLGL',
116 'GL#',
117 l_chart_of_accounts_id);
118
119 IF (l_flex_segment_delimiter IS NULL) THEN
120
121 l_error_message := l_debug_info||': '||FND_MESSAGE.GET;
122 P_CCID:=''||'-1'||'';
123 return;
124 END IF;
125
126 -----------------------------------------------
127 l_debug_info := 'Get Cost Center Qualifier Segment Number';
128 -----------------------------------------------
129
130 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
131 101,
132 'GL#',
133 l_chart_of_accounts_id,
134 'FA_COST_CTR',
135 l_cc_flex_segment_number)) THEN
136 l_error_message := FND_MESSAGE.GET;
137 P_CCID:=''||'-1'||'';
138 return;
139 END IF;
140
141 -----------------------------------------------
142 l_debug_info := 'Get Account Qualifier Segment Number';
143 -----------------------------------------------
144 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
145 101,
146 'GL#',
147 l_chart_of_accounts_id,
148 'GL_ACCOUNT',
149 l_flex_segment_number)) THEN
150 l_error_message := FND_MESSAGE.GET;
151 P_CCID:=''||'-1'||'';
152 return;
153 END IF;
154
155 -----------------------------------------------------------------
156 l_debug_info := 'Get ccid account segments';
157 -----------------------------------------------------------------
158 if (nvl(P_CODE_COMBINATION_ID,-1) <> -1) then
159
160 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
161 'SQLGL',
162 'GL#',
163 l_chart_of_accounts_id,
164 P_CODE_COMBINATION_ID,
165 l_num_segments,
166 l_segments)) THEN
167
168 l_error_message := FND_MESSAGE.GET;
169 P_CCID:=''||'-1'||'';
170 return;
171 END IF;
172 end if;
173
174 -------------------------------------------------
175 l_debug_info := 'Overlay the cost center segment';
176 ---------------------------------------------------
177 if (P_COST_CENTER is not null) then
178
179 l_segments(l_cc_flex_segment_number) := P_COST_CENTER;
180 end if;
181
182 ---------------------------------------------------
183 l_debug_info := 'Overlay the account segment';
184 ---------------------------------------------------
185 if (P_ACCOUNT_SEGMENT_VALUE is not null) then
186
187 l_segments(l_flex_segment_number) := P_ACCOUNT_SEGMENT_VALUE;
188 end if;
189
190 --------------------------------------------------------------
191 l_debug_info := 'Retrieve new ccid with overlaid segments';
192
193 l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments,
194 l_segments,
195 l_flex_segment_delimiter);
196
197 IF (NOT AP_CARD_UTILITY_PKG.GET_COMBINATION_ID(
198 'SQLGL',
199 'GL#',
200 l_chart_of_accounts_id,
201 SYSDATE,
202 l_num_segments,
203 l_segments,
204 l_concatenated_segments,
205 l_code_combination_id,
206 l_error_message)) THEN
207 P_CCID:=''||'-1'||'';
208 return;
209 END IF;
210 P_CCID:=''||l_code_combination_id||'';
211 END;
212
213
214
215
216 -------------------------------------------------------------------------------
217 FUNCTION get_combination_id(p_application_short_name IN VARCHAR2,
218 p_key_flex_code IN VARCHAR2,
219 p_structure_number IN NUMBER,
220 p_validation_date IN DATE,
221 p_n_segments IN NUMBER,
222 p_segments IN fnd_flex_ext.SegmentArray,
223 p_concatSegments IN VARCHAR2,
224 p_combination_id OUT NOCOPY NUMBER,
225 p_return_error_message IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
226 -------------------------------------------------------------------------------
227
228 BEGIN
229
230 IF (FND_FLEX_KEYVAL.validate_segs('CREATE_COMB_NO_AT',
231 p_application_short_name,
232 p_key_flex_code,
233 p_structure_number,
234 p_concatSegments)) THEN
235 p_combination_id := FND_FLEX_KEYVAL.combination_id;
236 return TRUE;
237 ELSE
238 p_return_error_message := FND_FLEX_KEYVAL.error_message;
239 return FALSE;
240 END IF;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 null;
245 END get_combination_id;
246
247
248
249 -------------------------------------------------------------------------------
250 FUNCTION validateSession(p_func in varchar2 default null,
251 p_commit in boolean default TRUE,
252 p_update in boolean default TRUE) return boolean is
253 -------------------------------------------------------------------------------
254 begin
255
256 return icx_sec.VALIDATESESSION(P_FUNC, '', P_COMMIT, P_UPDATE);
257
258 -- RETURN TRUE;
259
260 END;
261
262 -------------------------------------------------------------------------------
263 PROCEDURE JUMPINTOFUNCTION(P_ID IN NUMBER,
264 P_MODE IN VARCHAR2,
265 P_URL OUT NOCOPY VARCHAR2) IS
266 -------------------------------------------------------------------------------
267 L_ORG_ID AP_EXPENSE_FEED_LINES_ALL.ORG_ID%TYPE;
268 L_FUNCTION_CODE VARCHAR2(30);
269 L_DEBUG_INFO VARCHAR2(200);
270
271 BEGIN
272
273 ---------------------------------
274 L_DEBUG_INFO := 'GETTING ORG ID';
275 ---------------------------------
276
277 IF (P_MODE = 'PCARD EMP VERI') THEN
278 -- CHIHO:P-CARD RELATED, IGNORED:
279 SELECT DISTINCT(NVL(FL.ORG_ID,FD.ORG_ID))
280 INTO L_ORG_ID
281 FROM AP_EXPENSE_FEED_LINES_ALL FL,
282 AP_EXPENSE_FEED_DISTS_ALL FD
283 WHERE FL.EMPLOYEE_VERIFICATION_ID = P_ID
284 OR (FD.FEED_LINE_ID = FL.FEED_LINE_ID AND
285 FD.EMPLOYEE_VERIFICATION_ID = P_ID);
286
287 L_FUNCTION_CODE := 'ICX_AP_WEB_OPEN_PCARD_TRANS';
288
289 ELSIF (P_MODE = 'PCARD MANAGER APPR') THEN
290 -- CHIHO:P-CARD RELATED, IGNORED:
291 SELECT DISTINCT(ORG_ID)
292 INTO L_ORG_ID
293 FROM AP_EXPENSE_FEED_DISTS_ALL
294 WHERE MANAGER_APPROVAL_ID = P_ID;
295
296 L_FUNCTION_CODE := 'ICX_AP_WEB_OPEN_PCARD_TRANS';
297
298 END IF;
299
300 -----------------------------------------------
301 L_DEBUG_INFO := 'CALLING ICX JUMPINTOFUNCTION';
302 -----------------------------------------------
303 P_URL := ICX_SEC.JUMPINTOFUNCTION(
304 P_APPLICATION_ID => 200,
305 P_FUNCTION_CODE => L_FUNCTION_CODE,
306 P_PARAMETER1 => TO_CHAR(P_ID),
307 P_PARAMETER2 => P_MODE,
308 P_PARAMETER11 => TO_CHAR(L_ORG_ID));
309 EXCEPTION
310 WHEN OTHERS THEN
311 WF_CORE.CONTEXT('AP_CARD_UTILITY_PKG', 'JUMPINTOFUNCTION',
312 'APEXP', TO_CHAR(P_ID), TO_CHAR(0), L_DEBUG_INFO);
313 RAISE;
314 END JUMPINTOFUNCTION;
315
316 -------------------------------------------------------------------------------
317 PROCEDURE ICXSETORGCONTEXT(P_SESSION_ID IN VARCHAR2,
318 P_ORG_ID IN VARCHAR2) IS
319 -------------------------------------------------------------------------------
320 L_DEBUG_INFO VARCHAR2(200);
321 BEGIN
322
323 ----------------------------------------------
324 L_DEBUG_INFO := 'CALLING ICX SET_ORG_CONTEXT';
325 ----------------------------------------------
326 ICX_SEC.SET_ORG_CONTEXT(P_SESSION_ID, ICX_CALL.DECRYPT(P_ORG_ID));
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 WF_CORE.CONTEXT('AP_CARD_UTILITY_PKG', 'ICXGETORGCONTEXT',
331 'APEXP', NULL , TO_CHAR(0), L_DEBUG_INFO);
332 RAISE;
333 END ICXSETORGCONTEXT;
334
335 /*The procedures below have been just copied from i-expenses
336 packages to AP package.This is being done to remove any sort
337 of dependency on OIE for PCARDS*/
338
339 FUNCTION IsPersonCwk (p_person_id IN NUMBER) return VARCHAR2
340 IS
341 v_numRows NUMBER := 0;
342 BEGIN
343 -- This query returns rows if there exist an active employee or cwk
344 -- record for the given person ID.
345 SELECT 1
346 INTO v_numRows
347 FROM DUAL
348 WHERE EXISTS
349 (SELECT 1
350 FROM
351 per_cont_workers_current_x p
352 WHERE
353 p.person_id = p_person_id);
354
355 -- Return true if there were rows, return false otherwise
356 IF v_numRows = 1 THEN
357 RETURN 'Y';
358 ELSE
359 RETURN 'N';
360 END IF;
361
362 EXCEPTION
363 WHEN no_data_found THEN
364 return('N');
365 WHEN OTHERS THEN
366 raise;
367 END IsPersonCwk;
368
369 FUNCTION GetCOAofSOB(
370 p_chart_of_accounts OUT NOCOPY NUMBER
371 ) RETURN BOOLEAN IS
372 BEGIN
373 select GS.chart_of_accounts_id
374 into p_chart_of_accounts
375 from ap_system_parameters S,
376 gl_sets_of_books GS
377 where GS.set_of_books_id = S.set_of_books_id;
378
379 return TRUE;
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 RETURN FALSE;
384
385 WHEN OTHERS THEN
386 RETURN FALSE;
387
388 END GetCOAofSOB;
389
390 FUNCTION GetDependentSegment(
391 p_value_set_name IN fnd_flex_value_sets.flex_value_set_name%type,
392 p_chart_of_accounts_id IN NUMBER,
393 p_dependent_seg_num OUT NOCOPY NUMBER)
394 RETURN BOOLEAN IS
395 l_parent_flex_value_set_id fnd_flex_value_sets.parent_flex_value_set_id%type;
396 BEGIN
397 select PARENT_FLEX_VALUE_SET_ID into l_parent_flex_value_set_id
398 from fnd_flex_value_sets
399 where flex_value_set_name like p_value_set_name
400 and PARENT_FLEX_VALUE_SET_ID is not null;
401
402 IF (l_parent_flex_value_set_id IS NOT NULL) THEN
403
404 SELECT s.segment_num into p_dependent_seg_num
405 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
406 fnd_segment_attribute_types sat
407 WHERE s.application_id = 101
408 AND s.id_flex_code = 'GL#'
409 AND s.id_flex_num = p_chart_of_accounts_id
410 AND s.enabled_flag = 'Y'
411 AND s.application_column_name = sav.application_column_name
412 AND sav.application_id = 101
413 AND sav.id_flex_code = 'GL#'
414 AND sav.id_flex_num = p_chart_of_accounts_id
415 AND sav.attribute_value = 'Y'
416 AND sav.segment_attribute_type = sat.segment_attribute_type
417 AND sat.application_id = 101
418 AND sat.id_flex_code = 'GL#'
419 AND sat.unique_flag = 'Y'
420 AND s.FLEX_VALUE_SET_ID=l_parent_flex_value_set_id
421 AND rownum =1;
422 END IF;
423 return TRUE;
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 return FALSE;
427 WHEN OTHERS THEN
428 return FALSE;
429 END GetDependentSegment;
430
431 FUNCTION COSTCENTERVALID(
432 P_COST_CENTER IN EXPFEEDDISTS_COSTCENTER,
433 P_VALID OUT NOCOPY BOOLEAN,
434 P_EMPLOYEE_ID IN NUMBER
435 ) RETURN BOOLEAN IS
436 L_VALID VARCHAR2(1) := 'N';
437
438 L_EMPLOYEE_ID NUMBER;
439 L_CHART_OF_ACCOUNTS_ID NUMBER;
440 L_DEFAULT_EMP_CCID NUMBER;
441 L_FLEX_SEGMENT_DELIMITER VARCHAR2(1);
442 L_FLEX_SEGMENT_NUMBER NUMBER;
443 L_NUM_SEGMENTS NUMBER;
444 L_DEFAULT_EMP_SEGMENTS FND_FLEX_EXT.SEGMENTARRAY;
445 L_CONCATENATED_SEGMENTS VARCHAR2(2000);
446 L_APPCOL_NAME FND_ID_FLEX_SEGMENTS_VL.APPLICATION_COLUMN_NAME%TYPE;
447 L_SEG_NAME FND_ID_FLEX_SEGMENTS_VL.SEGMENT_NAME%TYPE;
448 L_PROMPT FND_ID_FLEX_SEGMENTS_VL.FORM_LEFT_PROMPT%TYPE;
449 L_VALUE_SET_NAME FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
450 L_PARENT_FLEX_VALUE_SET_ID FND_FLEX_VALUE_SETS.PARENT_FLEX_VALUE_SET_ID%TYPE;
451 L_DEPENDENT_SEG_NUM NUMBER := 0;
452
453 L_EMP_SET_OF_BOOKS_ID NUMBER;
454 l_ou_chart_of_accounts_id NUMBER;
455 l_emp_chart_of_accounts_id NUMBER;
456
457 BEGIN
458
459 l_employee_id := p_employee_id;
460 --bug5058949
461 --Performance fix
462 if not (ispersoncwk(l_employee_id)='Y') then
463 SELECT emp.set_of_books_id, emp.default_code_combination_id
464 INTO l_emp_set_of_books_id, l_default_emp_ccid
465 FROM per_employees_x emp
466 WHERE emp.employee_id = l_employee_id;
467 else
468 SELECT emp.set_of_books_id, emp.default_code_combination_id
469 INTO l_emp_set_of_books_id, l_default_emp_ccid
470 FROM per_cont_workers_current_x emp
471 WHERE emp.person_id = l_employee_id;
472 end if;
473 -- Get the chart_of_account_id from system parameters
474 IF (NOT GetCOAofSOB(l_ou_chart_of_accounts_id)) THEN
475 l_ou_chart_of_accounts_id := null;
476 END IF;
477
478 IF (l_emp_set_of_books_id is not null) THEN
479 SELECT GS.chart_of_accounts_id
480 INTO l_emp_chart_of_accounts_id
481 FROM gl_sets_of_books GS
482 WHERE GS.set_of_books_id = l_emp_set_of_books_id;
483
484 IF (l_emp_chart_of_accounts_id <> l_ou_chart_of_accounts_id) THEN
485 p_valid := FALSE;
486 return FALSE;
487 END IF;
488 END IF;
489
490 l_chart_of_accounts_id := l_ou_chart_of_accounts_id;
491
492 -- Get the character used as the segment delimiter. This would be
493 -- used to prepare the concatenated segment from segment array to a string.
494 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
495 'SQLGL',
496 'GL#',
497 l_chart_of_accounts_id);
498
499 -- Get the segment number corresponding to the costcenter qualifier.
500 -- This is used to overlay the costcenter segment
501 if (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
502 101,
503 'GL#',
504 l_chart_of_accounts_id,
505 'FA_COST_CTR',
506 l_flex_segment_number)) then
507 p_valid := FALSE;
508 return FALSE;
509 end if;
510
511 -- Get the segment array and number of segments for the employee
512 -- code combination id.
513 if (NOT FND_FLEX_EXT.GET_SEGMENTS(
514 'SQLGL',
515 'GL#',
516 l_chart_of_accounts_id,
517 l_default_emp_ccid,
518 l_num_segments,
519 l_default_emp_segments)) then
520 p_valid := FALSE;
521 return FALSE;
522 end if;
523
524 /*Bug 2690715 : Called function to get the dependent segment
525 of cost center segment and then do the validation.
526 */
527
528 IF (FND_FLEX_APIS.GET_SEGMENT_INFO(
529 101,
530 'GL#',
531 l_chart_of_accounts_id,
532 l_flex_segment_number,
533 l_appcol_name,
534 l_seg_name,
535 l_prompt,
536 l_value_set_name)) then
537
538 IF (GetDependentSegment(l_value_set_name,
539 l_chart_of_accounts_id,
540 l_dependent_seg_num)) THEN
541 NULL;
542 END IF;
543
544 END IF;
545
546 /*Bug 2690715 : Dont set the segment to NULL , if dependent valueset
547 of cost center segment exists.
548 */
549 IF (l_dependent_seg_num IS NULL) THEN
550 FOR i in 1 .. l_num_segments LOOP
551 l_default_emp_segments(i) := '';
552 END LOOP;
553 END IF;
554
555 -- Overlay the costcenter segment with the costcenter entered in the
556 -- expense report
557 l_default_emp_segments(l_flex_segment_number) := p_cost_center;
558
559 -- Get the concatenated segments with all segments set to null, except the
560 -- costcenter, Concatenates segments from segment array
561 -- (l_default_emp_segments) to a string(l_concatenated_segments).
562 l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments,
563 l_default_emp_segments,
564 l_flex_segment_delimiter);
565
566 /* -----------------------------------------------------------------------+
567 + Validate only the costcenter segment, since we are passing other +
568 + segments as null we need to set allow_nulls to true +
569 + Allow_nulls will allow required segments that are NULL to be valid +
570 + allow_orphans will validate dependent segment values without associated +
571 + parent values, allowing ANY possible dependent value, regardless +
572 + of what the parent value would be, to be considered valid. +
573 +------------------------------------------------------------------------*/
574 /*Bug 2706584: Passed VRULE as a parameter to Validate_Segs so that
575 it does not pass the cost center for which
576 Posting Allowed is Unchecked.
577 */
578
579 /*Bug 2690715:Validate using only segment of COST CENTER if
580 No Dependent Segment is present.Else validate
581 using Combination,Flex Field does not support
582 partial combination validation. i.e Like 01-520----
583 can not be successfully validated.
584 */
585 IF (l_dependent_seg_num IS NULL) THEN
586 if ( fnd_flex_keyval.validate_segs(operation=>'CHECK_SEGMENTS',
587 appl_short_name=>'SQLGL',
588 key_flex_code=>'GL#',
589 structure_number=>l_chart_of_accounts_id,
590 concat_segments=>l_concatenated_segments,
591 VRULE=>'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN',
592 allow_nulls=>TRUE,
593 allow_orphans=>TRUE ) ) then
594 p_valid := TRUE;
595 else
596 p_valid := FALSE;
597 end if;
598 ELSE
599 if ( fnd_flex_keyval.validate_segs(operation=>'CHECK_COMBINATION',
600 appl_short_name=>'SQLGL',
601 key_flex_code=>'GL#',
602 structure_number=>l_chart_of_accounts_id,
603 concat_segments=>l_concatenated_segments,
604 VRULE=>'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN',
605 allow_nulls=>TRUE,
606 allow_orphans=>TRUE ) ) then
607 p_valid := TRUE;
608 else
609 p_valid := FALSE;
610 end if;
611 END IF;
612
613 return TRUE;
614
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 p_valid := FALSE;
618 RETURN FALSE;
619
620 WHEN OTHERS THEN
621 return FALSE;
622
623 END CostCenterValid;
624
625
626
627 PROCEDURE ValidateCostCenter(p_costcenter IN varchar2,
628 p_cs_error OUT NOCOPY varchar2,
629 p_employee_id IN NUMBER) IS
630 p_CostCenterValid boolean;
631 l_customError varchar2(2000);
632
633 l_CostCenterValid BOOLEAN := FALSE;
634
635
636 BEGIN
637
638
639
640 --
641 -- Call custom cost center validation API
642 --
643 if (CustomValidateCostCenter(
644 l_customError,
645 p_costcenter,
646 p_CostCenterValid,
647 p_employee_id)) then
648 --
649 -- Custom validation API returned TRUE; therefore custom validation
650 -- is used in lieu of native cost center validation
651 --
652 if (p_CostCenterValid) then
653 --
654 -- If custom validation succeeds, clear the error text
655 --
656 p_cs_error := null;
657 else
658 --
659 -- Custom validation failed; supply standard failure message if
660 -- custom error message is null
661 --
662 if (l_customError is null) then
663 FND_MESSAGE.SET_NAME('SQLAP','AP_COST_CENTER_INVALID');
664 p_cs_error:= fnd_message.get_encoded();
665 else
666 p_cs_error := l_customError;
667 end if;
668 end if;
669 else
670 --
671 -- Custom validation API returned FALSE; therefore we validate using
672 -- the cursor declared above.
673 --
674 IF (NOT CostCenterValid(p_costCenter,
675 l_CostCenterValid,
676 p_employee_id)) THEN
677 NULL;
678 END IF;
679 if (NOT l_CostCenterValid) then
680 --
681 -- Failed; set standard failure message.
682 --
683 FND_MESSAGE.SET_NAME('SQLAP','AP_COST_CENTER_INVALID');
684 p_cs_error:= fnd_message.get_encoded();
685 end if;
686 end if;
687
688 END ValidateCostCenter;
689 ----------------------------------------------------------------------------
690 -- CUSTOMVALIDATECOSTCENTER:
691 -- Called by ValidateCostCenter();
692 -- API provides a means of bypassing native cost center segment validation
693 -- and using custom code to validate cost center value.
694 --
695 -- Function returns TRUE if custom cost center segment validation is
696 -- enabled, or FALSE if native validation should be used. By default,
697 -- we assume that native validation is used.
698 --
699 -- PARAMETERS:
700 --
701 -- p_cs_error - Set this variable with your custom error message.
702 -- If left blank, standard error message will be used.
703 -- p_CostCenterValue - The cost center entered by the user
704 -- p_CostCenterValid - TRUE if cost center is valid, otherwise FALSE;
705 --
706 ----------------------------------------------------------------------------
707 ----------------------------------------------------------------------------
708 FUNCTION CustomValidateCostCenter(
709 p_cs_error OUT NOCOPY VARCHAR2,
710 p_CostCenterValue IN VARCHAR2,
711 p_CostCenterValid IN OUT NOCOPY BOOLEAN,
712 p_employee_id IN NUMBER) return BOOLEAN IS
713 ----------------------------------------------------------------------------
714 BEGIN
715 --
716 -- Assume cost center is valid
717 --
718 p_CostCenterValid := TRUE;
719
720 return(FALSE); -- return TRUE if using this extension to perform validation
721
722 -- Note: If any error occurred and p_cs_error needs to be set by getting
723 -- a FND message, make sure to use the following syntax:
724 --
725 -- p_CostCenterValid := FALSE;
726 --
727 -- FND_MESSAGE.SET_NAME('SQLAP', '<MESSAGE NAME>');
728 -- p_cs_error := FND_MESSAGE.GET_ENCODED();
729 --
730 -- return(TRUE);
731
732 END CustomValidateCostCenter;
733
734
735 END AP_CARD_UTILITY_PKG;
[Home] [Help]