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