[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_ACCTG_PKG
Source
1 PACKAGE BODY AP_WEB_ACCTG_PKG AS
2 /* $Header: apwacctb.pls 120.26.12020000.2 2012/11/01 10:08:01 rveliche ship $ */
3
4 --Cache for FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
5 gqs_cost_center_segnum t_cost_center_segnum_table;
6
7 PROCEDURE GetEmployeeCostCenter(
8 p_employee_id IN NUMBER,
9 p_emp_ccid IN NUMBER,
10 p_cost_center OUT NOCOPY VARCHAR2) IS
11
12 l_debug_info varchar2(200);
13
14 BEGIN
15
16 --
17 -- Call CustomDefaultCostCenter for cost center, otherwise retrieve
18 -- cost center segment by deriving from employee ccid.
19 --
20 -----------------------------------------------------
21 l_debug_info := 'AP_WEB_CUST_DFLEX_PKG.CustomDefaultCostCenter';
22 -----------------------------------------------------
23 p_cost_center := AP_WEB_CUST_DFLEX_PKG.CustomDefaultCostCenter(p_employee_id);
24
25 if (p_cost_center is null) then
26 p_cost_center := GetCostCenter(p_emp_ccid);
27 end if;
28 END GetEmployeeCostCenter;
29
30 FUNCTION GetCostCenter(
31 p_ccid IN NUMBER,
32 p_chart_of_accounts_id IN NUMBER)
33 RETURN VARCHAR2 IS
34
35 l_debug_info varchar2(200);
36
37 l_chart_of_accounts_id number := p_chart_of_accounts_id;
38 l_segments FND_FLEX_EXT.SEGMENTARRAY;
39 l_num_of_segments NUMBER;
40 l_cost_ctr_seg_num NUMBER;
41
42 BEGIN
43
44 -- Only get the chart of accounts ID if none/null is passed in
45 IF (l_chart_of_accounts_id IS NULL) THEN
46 -----------------------------------------------------
47 l_debug_info := 'AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
48 -----------------------------------------------------
49 IF (NOT AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id)) THEN
50 l_chart_of_accounts_id := NULL;
51 END IF;
52 END IF;
53
54 -----------------------------------------------------
55 l_debug_info := 'Retrieve segments from code combination id';
56 -----------------------------------------------------
57 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL',
58 'GL#',
59 l_chart_of_accounts_id,
60 p_ccid,
61 l_num_of_segments,
62 l_segments)) THEN
63 return NULL;
64 END IF;
65
66 -----------------------------------------------------
67 l_debug_info := 'Get Cost Center segment number';
68 -----------------------------------------------------
69 BEGIN
70 -- Check cache
71 l_cost_ctr_seg_num := gqs_cost_center_segnum(l_chart_of_accounts_id);
72 EXCEPTION
73 -- Not found in cache, retrieve segment number
74 WHEN NO_DATA_FOUND THEN
75 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
76 101,
77 'GL#',
78 l_chart_of_accounts_id,
79 'FA_COST_CTR',
80 l_cost_ctr_seg_num)) THEN
81 return NULL;
82 END IF;
83
84 -- Put segment number in cache for chart of accounts ID
85 gqs_cost_center_segnum(l_chart_of_accounts_id) := l_cost_ctr_seg_num;
86 END;
87 -----------------------------------------------------
88 l_debug_info := 'Get Cost Center segment';
89 -----------------------------------------------------
90 return l_segments(l_cost_ctr_seg_num);
91
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCostCenter' );
96 APP_EXCEPTION.RAISE_EXCEPTION;
97
98 END GetCostCenter;
99
100
101 PROCEDURE GetCostCenterApprovalRule(
102 p_alphanumeric_allowed_flag OUT NOCOPY VARCHAR2,
103 p_uppercase_only_flag OUT NOCOPY VARCHAR2,
104 p_numeric_mode_enabled_flag OUT NOCOPY VARCHAR2,
105 p_maximum_size OUT NOCOPY NUMBER) IS
106
107 l_debug_info varchar2(200);
108
109 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
110 l_cost_center_seg_num NUMBER;
111 l_appcol_name fnd_id_flex_segments_vl.application_column_name%type;
112 l_seg_name fnd_id_flex_segments_vl.segment_name%type;
113 l_prompt fnd_id_flex_segments_vl.form_left_prompt%type;
114 l_value_set_name fnd_flex_value_sets.flex_value_set_name%type;
115
116 BEGIN
117
118 -----------------------------------------------------
119 l_debug_info := 'AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
120 -----------------------------------------------------
121 IF (NOT AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id)) THEN
122 l_chart_of_accounts_id := NULL;
123 END IF;
124
125 -----------------------------------------------------
126 l_debug_info := 'Get the segment number corresponding to the costcenter qualifier.';
127 -----------------------------------------------------
128 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
129 101,
130 'GL#',
131 l_chart_of_accounts_id,
132 'FA_COST_CTR',
133 l_cost_center_seg_num)) then
134 return;
135 END IF;
136
137 -----------------------------------------------------
138 l_debug_info := 'Get the valueset associated with the Department (costcenter qualifier)';
139 -----------------------------------------------------
140 IF (NOT FND_FLEX_APIS.GET_SEGMENT_INFO(
141 101,
142 'GL#',
143 l_chart_of_accounts_id,
144 l_cost_center_seg_num,
145 l_appcol_name,
146 l_seg_name,
147 l_prompt,
148 l_value_set_name)) then
149 return;
150 END IF;
151
152 -----------------------------------------------------
153 l_debug_info := 'Get Cost Center Approval rules';
154 -----------------------------------------------------
155 select alphanumeric_allowed_flag, uppercase_only_flag, numeric_mode_enabled_flag, maximum_size
156 into p_alphanumeric_allowed_flag, p_uppercase_only_flag ,p_numeric_mode_enabled_flag, p_maximum_size
157 from fnd_flex_value_sets
158 where flex_value_set_name like l_value_set_name;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCostCenterApprovalRule' );
163 APP_EXCEPTION.RAISE_EXCEPTION;
164
165 END GetCostCenterApprovalRule;
166
167
168 /*Bug 2690715 : Declared a function to get the dependent segment
169 of cost center segment.
170 */
171 FUNCTION GetDependentSegment(
172 p_value_set_name IN fnd_flex_value_sets.flex_value_set_name%type,
173 p_chart_of_accounts_id IN AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID,
174 p_dependent_seg_num OUT NOCOPY NUMBER) RETURN BOOLEAN IS
175
176 l_parent_flex_value_set_id fnd_flex_value_sets.parent_flex_value_set_id%type;
177
178 BEGIN
179
180 select PARENT_FLEX_VALUE_SET_ID into l_parent_flex_value_set_id
181 from fnd_flex_value_sets
182 where flex_value_set_name like p_value_set_name
183 and PARENT_FLEX_VALUE_SET_ID is not null;
184
185 IF (l_parent_flex_value_set_id IS NOT NULL) THEN
186
187 SELECT s.segment_num into p_dependent_seg_num
188 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
189 fnd_segment_attribute_types sat
190 WHERE s.application_id = 101
191 AND s.id_flex_code = 'GL#'
192 AND s.id_flex_num = p_chart_of_accounts_id
193 AND s.enabled_flag = 'Y'
194 AND s.application_column_name = sav.application_column_name
195 AND sav.application_id = 101
196 AND sav.id_flex_code = 'GL#'
197 AND sav.id_flex_num = p_chart_of_accounts_id
198 AND sav.attribute_value = 'Y'
199 AND sav.segment_attribute_type = sat.segment_attribute_type
200 AND sat.application_id = 101
201 AND sat.id_flex_code = 'GL#'
202 AND sat.unique_flag = 'Y'
203 AND s.FLEX_VALUE_SET_ID=l_parent_flex_value_set_id
204 AND rownum =1;
205 END IF;
206
207
208 return TRUE;
209
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 return FALSE;
213 WHEN OTHERS THEN
214 AP_WEB_DB_UTIL_PKG.RaiseException('GetDependentSegment');
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 return FALSE;
217 END GetDependentSegment;
218
219
220 PROCEDURE ValidateCostCenter(
221 p_cost_center IN VARCHAR2,
222 p_employee_id IN NUMBER,
223 p_emp_set_of_books_id IN NUMBER,
224 p_default_emp_ccid IN VARCHAR2,
225 p_chart_of_accounts_id IN NUMBER,
226 p_cost_center_valid OUT NOCOPY BOOLEAN) IS
227
228 l_debug_info varchar2(200);
229
230 l_flex_segment_delimiter varchar2(1);
231 l_cost_center_seg_num number;
232 l_num_segments number;
233 l_default_emp_segments FND_FLEX_EXT.SEGMENTARRAY;
234 l_appcol_name fnd_id_flex_segments_vl.application_column_name%type;
235 l_seg_name fnd_id_flex_segments_vl.segment_name%type;
236 l_prompt fnd_id_flex_segments_vl.form_left_prompt%type;
237 l_value_set_name fnd_flex_value_sets.flex_value_set_name%type;
238 l_dependent_seg_num number := 0;
239 l_concatenated_segments varchar2(2000);
240
241 BEGIN
242
243 -- Get the character used as the segment delimiter. This would be
244 -- used to prepare the concatenated segment from segment array to a string.
245 -----------------------------------------------------
246 l_debug_info := 'Get segment delimiter';
247 -----------------------------------------------------
248 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
249 'SQLGL',
250 'GL#',
251 p_chart_of_accounts_id);
252
253 -- Get the segment number corresponding to the costcenter qualifier.
254 -- This is used to overlay the costcenter segment
255 -----------------------------------------------------
256 l_debug_info := 'Get cost center segment number';
257 -----------------------------------------------------
258 if (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
259 101,
260 'GL#',
261 p_chart_of_accounts_id,
262 'FA_COST_CTR',
263 l_cost_center_seg_num)) then
264 p_cost_center_valid := FALSE;
265 return;
266 end if;
267
268 -- Get the segment array and number of segments for the employee
269 -- code combination id.
270 -----------------------------------------------------
271 l_debug_info := 'Get employee default segments';
272 -----------------------------------------------------
273 if (NOT FND_FLEX_EXT.GET_SEGMENTS(
274 'SQLGL',
275 'GL#',
276 p_chart_of_accounts_id,
277 p_default_emp_ccid,
278 l_num_segments,
279 l_default_emp_segments)) then
280 p_cost_center_valid := FALSE;
281 return;
282 end if;
283
284 /*Bug 2690715 : Called function to get the dependent segment
285 of cost center segment and then do the validation.
286 */
287 -----------------------------------------------------
288 l_debug_info := 'FND_FLEX_APIS.GET_SEGMENT_INFO';
289 -----------------------------------------------------
290 IF (FND_FLEX_APIS.GET_SEGMENT_INFO(
291 101,
292 'GL#',
293 p_chart_of_accounts_id,
294 l_cost_center_seg_num,
295 l_appcol_name,
296 l_seg_name,
297 l_prompt,
298 l_value_set_name)) then
299
300 -----------------------------------------------------
301 l_debug_info := 'GetDependentSegment';
302 -----------------------------------------------------
303 IF (GetDependentSegment(l_value_set_name,
304 p_chart_of_accounts_id,
305 l_dependent_seg_num)) THEN
306 NULL;
307 END IF;
308
309 END IF;
310
311 /*Bug 2690715 : Dont set the segment to NULL , if dependent valueset
312 of cost center segment exists.
313 */
314 IF (l_dependent_seg_num IS NULL) THEN
315 FOR i in 1 .. l_num_segments LOOP
316 l_default_emp_segments(i) := '';
317 END LOOP;
318 END IF;
319
320 -- Overlay the costcenter segment with the costcenter entered in the
321 -- expense report
322 -----------------------------------------------------
323 l_debug_info := 'Overlay cost center segment with expense report cost center';
324 -----------------------------------------------------
325 l_default_emp_segments(l_cost_center_seg_num) := p_cost_center;
326
327 -- Get the concatenated segments with all segments set to null, except the
328 -- costcenter, Concatenates segments from segment array
329 -- (l_default_emp_segments) to a string(l_concatenated_segments).
330 -----------------------------------------------------
331 l_debug_info := 'Concatenate segments';
332 -----------------------------------------------------
333 l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments,
334 l_default_emp_segments,
335 l_flex_segment_delimiter);
336
337 /* -----------------------------------------------------------------------+
338 + Validate only the costcenter segment, since we are passing other +
339 + segments as null we need to set allow_nulls to true +
340 + Allow_nulls will allow required segments that are NULL to be valid +
341 + allow_orphans will validate dependent segment values without associated +
342 + parent values, allowing ANY possible dependent value, regardless +
343 + of what the parent value would be, to be considered valid. +
344 +------------------------------------------------------------------------*/
345 /*Bug 2706584: Passed VRULE as a parameter to Validate_Segs so that
346 it does not pass the cost center for which
347 Posting Allowed is Unchecked.
348 */
349
350 /*Bug 2690715:Validate using only segment of COST CENTER if
351 No Dependent Segment is present.Else validate
352 using Combination,Flex Field does not support
353 partial combination validation. i.e Like 01-520----
354 can not be successfully validated.
355 */
356 -----------------------------------------------------
357 l_debug_info := 'Validate segments';
358 -----------------------------------------------------
359 IF (l_dependent_seg_num IS NULL) THEN
360 if ( fnd_flex_keyval.validate_segs(operation=>'CHECK_SEGMENTS',
361 appl_short_name=>'SQLGL',
362 key_flex_code=>'GL#',
363 structure_number=>p_chart_of_accounts_id,
364 concat_segments=>l_concatenated_segments,
365 VRULE=>'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN',
366 allow_nulls=>TRUE,
367 allow_orphans=>TRUE ) ) then
368 p_cost_center_valid := TRUE;
369 return;
370 else
371 p_cost_center_valid := FALSE;
372 return;
373 end if;
374 ELSE
375 if ( fnd_flex_keyval.validate_segs(operation=>'CHECK_COMBINATION',
376 appl_short_name=>'SQLGL',
377 key_flex_code=>'GL#',
378 structure_number=>p_chart_of_accounts_id,
379 concat_segments=>l_concatenated_segments,
380 VRULE=>'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN',
381 allow_nulls=>TRUE,
382 allow_orphans=>TRUE ) ) then
383 p_cost_center_valid := TRUE;
384 return;
385 else
386 p_cost_center_valid := FALSE;
387 return;
388 end if;
389 END IF;
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 p_cost_center_valid := FALSE;
394
395 WHEN OTHERS THEN
396 p_cost_center_valid := FALSE;
397
398 END ValidateCostCenter;
399
400
401 PROCEDURE GetExpenseTypeCostCenter(
402 p_exp_type_parameter_id IN NUMBER,
403 p_cost_center OUT NOCOPY VARCHAR2) IS
404
405 l_debug_info varchar2(200);
406
407 l_chart_of_accounts_id number;
408 l_flex_segment_delimiter varchar2(1);
409 l_cost_center_seg_num number;
410 l_num_segments number;
411 l_exp_type_template_array FND_FLEX_EXT.SEGMENTARRAY;
412 l_FlexConcactenated AP_EXPENSE_REPORT_PARAMS.flex_concactenated%TYPE;
413
414 BEGIN
415
416 -----------------------------------------------------
417 l_debug_info := 'AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
418 -----------------------------------------------------
419 IF (NOT AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id)) THEN
420 l_chart_of_accounts_id := NULL;
421 END IF;
422
423 -- Get the character used as the segment delimiter. This would be
424 -- used to prepare the concatenated segment from segment array to a string.
425 -----------------------------------------------------
426 l_debug_info := 'Get segment delimeter';
427 -----------------------------------------------------
428 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
429 'SQLGL',
430 'GL#',
431 l_chart_of_accounts_id);
432
433 -- Get the segment number corresponding to the costcenter qualifier.
434 -- This is used to get the cost center for the expense type
435 -----------------------------------------------------
436 l_debug_info := 'Get segment number';
437 -----------------------------------------------------
438 if (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
439 101,
440 'GL#',
441 l_chart_of_accounts_id,
442 'FA_COST_CTR',
443 l_cost_center_seg_num)) then
444 return;
445 end if;
446
447 begin
448 -----------------------------------------------------
449 l_debug_info := 'Get FLEX_CONCACTENATED';
450 -----------------------------------------------------
451 SELECT FLEX_CONCACTENATED into l_FlexConcactenated
452 FROM ap_expense_report_params_all
453 WHERE parameter_id = p_exp_type_parameter_id;
454 exception
455 when others then
456 return;
457 end;
458
459 /* Bug 4212250: Since l_FlexConcactenated is null, no need to
460 * proceed further.
461 */
462 IF l_FlexConcactenated IS NULL THEN
463 return;
464 END IF;
465
466 -----------------------------------------------------
467 l_debug_info := 'Break up segments';
468 -----------------------------------------------------
469 l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated,
470 l_flex_segment_delimiter,
471 l_exp_type_template_array);
472
473 IF nvl(l_num_segments,0) = 0 THEN
474 return;
475 ELSE
476 IF (l_exp_type_template_array(l_cost_center_seg_num) IS NOT NULL) THEN
477 p_cost_center := l_exp_type_template_array(l_cost_center_seg_num);
478 ELSE
479 return;
480 END IF;
481 END IF;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetExpenseTypeCostCenter' );
486 APP_EXCEPTION.RAISE_EXCEPTION;
487
488 END GetExpenseTypeCostCenter;
489
490
491 PROCEDURE GetCostCenterSegmentName(
492 p_cost_center_segment_name OUT NOCOPY VARCHAR2) IS
493
494 l_debug_info varchar2(200);
495
496 l_chart_of_accounts_id number;
497
498 BEGIN
499
500 /* This query will exclude terminated contingent
501 workers and terminated employees who became
502 contingent workers to avoid duplicate rows.
503 Otherwise 2 rows for the same person could be
504 possible. */
505 -- Query up the Chart of Accounts Id for the Employee
506 SELECT GS.chart_of_accounts_id
507 INTO l_chart_of_accounts_id
508 FROM ap_system_parameters S,
509 gl_sets_of_books GS
510 WHERE GS.set_of_books_id = S.set_of_books_id
511 AND rownum = 1;
512 -- Get the Column Name which implements the Cost Center Segment
513 IF ( FND_FLEX_APIS.GET_SEGMENT_COLUMN(101,
514 'GL#',
515 l_chart_of_accounts_id,
516 'FA_COST_CTR',
517 p_cost_center_segment_name) )
518 THEN
519 RETURN;
520 ELSE
521 -- RAISE EXCEPTION THAT COST CTR IMPLEMENTATION COULD NOT BE FOUND!!!
522 p_cost_center_segment_name := 'SEGMENT2';
523 RETURN;
524 END IF;
525
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 -- RAISE EXCEPTION FOR INVALID CHART OF ACCOUNTS
529 p_cost_center_segment_name := 'SEGMENT2';
530 RETURN;
531 WHEN OTHERS THEN
532 -- RAISE SOME EXCEPTION
533 p_cost_center_segment_name := 'SEGMENT2';
534 RETURN;
535
536 END GetCostCenterSegmentName;
537
538
539 -------------------------------------------------------------------------
540 --
541 -- PROCEDURE GetDistributionSegments
542 --
543 -- Parameters
544 -- p_chart_of_accounts_id Flexfield structure num
545 -- p_report_distribution_id report_distribution_id
546 -- p_segments AP_OIE_KFF_SEGMENTS_T
547 --
548 -- Returns VARCHAR2
549 -- Returns the segment values array of the key flexfield
550 --
551 -- Description
552 -- There is no equivalent for this function. This function takes
553 -- the segment values stored in ap_exp_report_dists and returns
554 -- the segment values array according to the kff structure.
555 --
556 -- Modification History
557 -- ALING 30-SEP-04 Created.
558 --
559 -------------------------------------------------------------------------
560 PROCEDURE GetDistributionSegments(
561 p_chart_of_accounts_id IN GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE,
562 p_report_distribution_id IN NUMBER,
563 p_segments OUT NOCOPY AP_OIE_KFF_SEGMENTS_T) IS
564 --------------------------------------------------------------------------------
565
566 l_debug_info VARCHAR2(1000);
567 sqlstmt varchar2(2000);
568 colname fnd_id_flex_segments.application_column_name%type;
569 c integer;
570 l_concat_segments VARCHAR2(1000);
571 rows integer;
572 l_num_segments NUMBER;
573 l_segment_array FND_FLEX_EXT.SEGMENTARRAY;
574 l_flex_segment_delimiter varchar2(1);
575
576 cursor cflex(p_chart_accounts_id IN NUMBER) is
577 SELECT flex.application_column_name
578 FROM fnd_id_flex_segments flex
579 WHERE flex.application_id = 101
580 AND flex.id_flex_code = 'GL#'
581 AND flex.id_flex_num = p_chart_accounts_id
582 AND flex.enabled_flag='Y'
583 AND flex.display_flag='Y'
584 order by segment_num;
585
586 BEGIN
587
588 -- Get the character used as the segment delimiter. This would be
589 -- used to prepare the concatenated segment from segment array to a string.
590 -----------------------------------------------------
591 l_debug_info := 'Get segment delimiter';
592 -----------------------------------------------------
593 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
594 'SQLGL',
595 'GL#',
596 p_chart_of_accounts_id);
597
598 ------------------------------------------------------------
599 l_debug_info := 'First build the select statment';
600 ------------------------------------------------------------
601 sqlstmt := null;
602 if cflex%isopen then close cflex;
603 end if;
604
605 open cflex(p_chart_of_accounts_id);
606
607 LOOP
608 FETCH cflex INTO colname;
609 EXIT WHEN cflex%NOTFOUND;
610
611 IF sqlstmt IS NOT NULL THEN
612 sqlstmt := sqlstmt || '||''' || l_flex_segment_delimiter || '''||' ||colname;
613 ELSE
614 sqlstmt := colname;
615 END IF;
616
617 END LOOP;
618
619 sqlstmt := 'SELECT '||sqlstmt||' FROM AP_EXP_REPORT_DISTS
620 WHERE report_distribution_id = :report_distribution_id';
621
622 close cflex;
623
624 -----------------
625 -- Now execute it
626 -----------------
627 c := dbms_sql.open_cursor;
628 -----------------------------------------------------
629 l_debug_info := 'parse cursor';
630 -----------------------------------------------------
631 dbms_sql.parse(c, sqlstmt, dbms_sql.native);
632
633 -----------------------------------------------------
634 l_debug_info := 'bind values to the placeholder';
635 -----------------------------------------------------
636 dbms_sql.bind_variable(c, ':report_distribution_id', p_report_distribution_id);
637
638 -----------------------------------------------------
639 l_debug_info := 'setup output';
640 -----------------------------------------------------
641 dbms_sql.define_column(c, 1, l_concat_segments, 1000);
642
643 -----------------------------------------------------
644 l_debug_info := 'execute cursor';
645 -----------------------------------------------------
646 rows := dbms_sql.execute(c);
647
648 -----------------------------------------------------
649 l_debug_info := 'fetch a row';
650 -----------------------------------------------------
651 IF dbms_sql.fetch_rows(c) > 0 then
652 -- fetch columns from the row
653 dbms_sql.column_value(c, 1, l_concat_segments);
654 END IF;
655
656 dbms_sql.column_value(c, 1, l_concat_segments);
657
658 dbms_sql.close_cursor(c);
659
660
661 IF cflex%isopen THEN
662 CLOSE cflex;
663 END IF;
664
665 --------------------------------------------------------------
666 l_debug_info:='Break Up Segments';
667 --------------------------------------------------------------
668 l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_concat_segments,
669 l_flex_segment_delimiter,
670 l_segment_array);
671
672
673 -----------------------------------------------------
674 l_debug_info := 'Assign values to p_segments';
675 -----------------------------------------------------
676 p_segments := AP_OIE_KFF_SEGMENTS_T('');
677 p_segments.extend(l_segment_array.count);
678 FOR i IN 1..l_segment_array.count LOOP
679 p_segments(i) := l_segment_array(i);
680 END LOOP;
681
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 IF cflex%isopen THEN
686 close cflex;
687 END IF;
688 IF dbms_sql.is_open(c) THEN
689 dbms_sql.close_cursor(c);
690 END IF;
691
692 IF (SQLCODE <> -20001) THEN
693 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
694 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
695 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetDistributionSegments');
696 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
697 END IF;
698 APP_EXCEPTION.RAISE_EXCEPTION;
699 END GetDistributionSegments;
700
701
702 PROCEDURE GetConcatenatedSegments(
703 p_chart_of_accounts_id IN NUMBER,
704 p_segments IN AP_OIE_KFF_SEGMENTS_T,
705 p_concatenated_segments OUT NOCOPY VARCHAR2) IS
706
707 l_debug_info VARCHAR2(100);
708
709 l_flex_segment_delimiter VARCHAR2(1);
710 l_segments FND_FLEX_EXT.SEGMENTARRAY;
711
712 BEGIN
713
714 IF (p_chart_of_accounts_id is null) THEN
715 FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');
716 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
717 END IF;
718
719 ----------------------------------------
720 l_debug_info := 'Get Segment Delimiter like .';
721 ----------------------------------------
722 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
723 'SQLGL',
724 'GL#',
725 p_chart_of_accounts_id);
726
727 IF (l_flex_segment_delimiter IS NULL) THEN
728 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
729 END IF; /* l_flex_segment_delimiter IS NULL */
730
731 ------------------------------------------------------------------------
732 l_debug_info := 'Convert AP_OIE_KFF_SEGMENTS_T to FND_FLEX_EXT.SEGMENTARRAY';
733 ------------------------------------------------------------------------
734 FOR i IN 1..p_segments.count LOOP
735 l_segments(i) := p_segments(i);
736 END LOOP;
737
738 ------------------------------------------------------------------------
739 l_debug_info := 'calling FND_FLEX_EXT.concatenate_segments';
740 ------------------------------------------------------------------------
741 p_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_segments.count,
742 l_segments,
743 l_flex_segment_delimiter);
744
745 EXCEPTION
746 WHEN OTHERS THEN
747 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetConcatenatedSegments' );
748 APP_EXCEPTION.RAISE_EXCEPTION;
749
750 END GetConcatenatedSegments;
751
752
753 PROCEDURE BuildAccount(
754 p_report_header_id IN NUMBER,
755 p_report_line_id IN NUMBER,
756 p_employee_id IN NUMBER,
757 p_cost_center IN VARCHAR2,
758 p_line_cost_center IN VARCHAR2,
759 p_exp_type_parameter_id IN NUMBER,
760 p_segments IN AP_OIE_KFF_SEGMENTS_T,
761 p_ccid IN NUMBER,
762 p_build_mode IN VARCHAR2,
763 p_new_segments OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
764 p_new_ccid OUT NOCOPY NUMBER,
765 p_return_error_message OUT NOCOPY VARCHAR2) IS
766
767
768 l_debug_info VARCHAR2(100);
769
770 l_default_emp_ccid AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;
771 l_chart_of_accounts_id NUMBER;
772 l_cost_center_seg_num NUMBER;
773 l_flex_segment_delimiter VARCHAR2(1);
774 l_default_emp_segments FND_FLEX_EXT.SEGMENTARRAY;
775 l_FlexConcactenated AP_EXPENSE_REPORT_PARAMS.FLEX_CONCACTENATED%TYPE;
776 l_exp_type_template_array FND_FLEX_EXT.SEGMENTARRAY;
777 l_exp_line_acct_segs_array FND_FLEX_EXT.SEGMENTARRAY;
778 l_num_segments NUMBER:=NULL;
779 l_concatenated_segments varchar2(2000);
780
781 BEGIN
782
783 if (AP_WEB_CUS_ACCTG_PKG.BuildAccount(
784 p_report_header_id => p_report_header_id,
785 p_report_line_id => p_report_line_id,
786 p_employee_id => p_employee_id,
787 p_cost_center => p_cost_center,
788 p_exp_type_parameter_id => p_exp_type_parameter_id,
789 p_segments => p_segments,
790 p_ccid => p_ccid,
791 p_build_mode => p_build_mode,
792 p_new_segments => p_new_segments,
793 p_new_ccid => p_new_ccid,
794 p_return_error_message => p_return_error_message)) then
795
796 --------------------------------------------------------------------
797 l_debug_info := 'Custom BuildAccount';
798 --------------------------------------------------------------------
799
800 return;
801
802 end if;
803
804 if (p_build_mode not in (C_DEFAULT, C_DEFAULT_VALIDATE, C_BUILD_VALIDATE, C_VALIDATE)) then
805
806 --------------------------------------------------------------------
807 l_debug_info := 'Invalid p_build_mode';
808 --------------------------------------------------------------------
809
810 return;
811
812 end if;
813
814 -- Bug: 9467530, Set AFF Validation to null to avoid Value not found in value set error.
815 GL_GLOBAL.set_aff_validation('XX', null);
816
817 -----------------------------------------------------
818 l_debug_info := 'Get the HR defaulted Employee CCID';
819 -----------------------------------------------------
820 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetDefaultEmpCCID(
821 p_employee_id => p_employee_id,
822 p_default_emp_ccid => l_default_emp_ccid)) THEN
823 NULL;
824 END IF;
825
826 IF (l_default_emp_ccid is null) THEN
827 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_MISSING_EMP_CCID');
828 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
829 END IF;
830
831 -----------------------------------------------------
832 l_debug_info := 'Get the Employee Chart of Accounts ID';
833 -----------------------------------------------------
834 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetChartOfAccountsID(
835 p_employee_id => p_employee_id,
836 p_chart_of_accounts_id => l_chart_of_accounts_id)) THEN
837 NULL;
838 END IF;
839
840 IF (l_chart_of_accounts_id is null) THEN
841 FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');
842 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
843 END IF;
844
845 -----------------------------------------------------------------
846 l_debug_info := 'Get employee default ccid account segments';
847 -----------------------------------------------------------------
848 IF (l_default_emp_ccid IS NOT NULL) THEN
849 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
850 'SQLGL',
851 'GL#',
852 l_chart_of_accounts_id,
853 l_default_emp_ccid,
854 l_num_segments,
855 l_default_emp_segments)) THEN
856 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
857 END IF; /* GET_SEGMENTS */
858 END IF;
859
860 ----------------------------------------
861 l_debug_info := 'Get Cost Center Segment Number';
862 ----------------------------------------
863 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
864 101,
865 'GL#',
866 l_chart_of_accounts_id,
867 'FA_COST_CTR',
868 l_cost_center_seg_num)) THEN
869 /* We could not find the cost center segment, but we can still overlay the
870 * expense type mask, so do nothing */
871 null;
872 END IF;
873
874 ----------------------------------------
875 l_debug_info := 'Get Segment Delimiter like .';
876 ----------------------------------------
877 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
878 'SQLGL',
879 'GL#',
880 l_chart_of_accounts_id);
881
882 IF (l_flex_segment_delimiter IS NULL) THEN
883 FND_MSG_PUB.Add;
884 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
885 END IF;
886
887
888 ----------------------------------------
889 l_debug_info := 'Check if p_ccid or p_segments is passed';
890 ----------------------------------------
891 if (p_ccid is not null) then
892
893 ----------------------------------------
894 l_debug_info := 'Convert p_ccid into a segment array';
895 ----------------------------------------
896 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL',
897 'GL#',
898 l_chart_of_accounts_id,
899 p_ccid,
900 l_num_segments,
901 l_exp_line_acct_segs_array)) THEN
902 return;
903 END IF;
904
905 elsif (p_segments is not null and p_segments.count > 0) then
906
907 ----------------------------------------
908 l_debug_info := 'Convert p_segments into a segment array';
909 ----------------------------------------
910
911 IF (l_num_segments IS NULL) THEN
912 l_num_segments := p_segments.count;
913 END IF;
914
915 -----------------------------------------------------
916 l_debug_info := 'Assign values to l_exp_line_acct_segs_array';
917 -----------------------------------------------------
918 FOR i IN 1..l_num_segments LOOP
919 l_exp_line_acct_segs_array(i) := p_segments(i);
920 END LOOP;
921
922 end if /* p_ccid is not null or p_segments is not null */;
923
924
925 if (p_exp_type_parameter_id is not null) then
926 ------------------------------------------------------------------------
927 l_debug_info := 'calling AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated';
928 ------------------------------------------------------------------------
929 IF (AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated(
930 p_parameter_id => p_exp_type_parameter_id,
931 p_FlexConcactenated => l_FlexConcactenated)) THEN
932
933 IF l_FlexConcactenated is not null THEN
934
935 --------------------------------------------------------------
936 l_debug_info:='Break Up Segments';
937 --------------------------------------------------------------
938 l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated,
939 l_flex_segment_delimiter,
940 l_exp_type_template_array);
941 END IF;
942
943 END IF;
944
945 end if; /* p_exp_type_parameter_id is not null */
946
947
948 --------------------------------------------------------------
949 l_debug_info:='Check Build Account Mode';
950 --------------------------------------------------------------
951 if (p_build_mode in (C_DEFAULT, C_DEFAULT_VALIDATE, C_BUILD_VALIDATE)) then
952
953
954 -- Overlay the incoming segment values with the segment values
955 -- defined in expense type template IF the incoming segment value
956 -- is NULL.
957
958 FOR i IN 1..l_num_segments LOOP
959 -- If the incoming segment is not null, then keep this value, do nothing.
960 IF (p_segments IS NOT NULL AND
961 p_segments.EXISTS(i) AND
962 p_segments(i) IS NOT NULL) THEN
963
964 NULL;
965
966 ELSIF (l_exp_type_template_array is not null and
967 l_exp_type_template_array.count > 0 and
968 l_exp_type_template_array(i) IS NOT NULL) THEN
969
970 l_exp_line_acct_segs_array(i) := l_exp_type_template_array(i);
971
972 ELSE
973
974 /* If cost center is not defined on the expense type mask, override it from line
975 * or header, if defined, in that order. */
976
977 IF i = l_cost_center_seg_num AND p_line_cost_center is not null THEN
978 l_exp_line_acct_segs_array(i) := p_line_cost_center;
979
980 ELSIF i = l_cost_center_seg_num AND p_cost_center is not null THEN
981 l_exp_line_acct_segs_array(i) := p_cost_center;
982
983 ELSIF (p_build_mode in (C_DEFAULT, C_DEFAULT_VALIDATE)) THEN
984 l_exp_line_acct_segs_array(i) := l_default_emp_segments(i);
985 END IF;
986
987 END IF; /* l_exp_type_template_array(i) IS NOT NULL */
988
989 END LOOP; /* 1..l_num_segments */
990
991 end if; /* p_build_mode in (C_DEFAULT, C_DEFAULT_VALIDATE, C_BUILD_VALIDATE) */
992
993
994 --------------------------------------------------------------
995 l_debug_info:='Check Build Account Mode';
996 --------------------------------------------------------------
997 if (p_build_mode in (C_DEFAULT_VALIDATE, C_BUILD_VALIDATE, C_VALIDATE)) then
998
999 --------------------------------------------------------------
1000 l_debug_info:='Build Account Mode contains VALIDATE';
1001 --------------------------------------------------------------
1002
1003 --------------------------------------------------------------
1004 l_debug_info := 'Retrieve new ccid';
1005 --------------------------------------------------------------
1006 -- Work around for bug 1569108
1007 l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments,
1008 l_exp_line_acct_segs_array,
1009 l_flex_segment_delimiter);
1010
1011 ------------------------------------------------------------------------
1012 l_debug_info := 'calling FND_FLEX_KEYVAL.validate_segs';
1013 ------------------------------------------------------------------------
1014 -- Bug: 12363929, Summary flag on CCIDs not to be allowed.
1015 IF (FND_FLEX_KEYVAL.validate_segs(operation=>'CREATE_COMBINATION',
1016 appl_short_name=>'SQLGL',
1017 key_flex_code=>'GL#',
1018 structure_number=>l_chart_of_accounts_id,
1019 concat_segments=>l_concatenated_segments,
1020 VRULE=>'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN')) THEN
1021
1022 p_new_ccid := FND_FLEX_KEYVAL.combination_id;
1023
1024 ELSE
1025
1026 p_return_error_message := FND_FLEX_KEYVAL.error_message;
1027 FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);
1028 fnd_msg_pub.add();
1029
1030 END IF; /* FND_FLEX_KEYVAL.validate_segs */
1031
1032 end if; /* p_build_mode in (C_DEFAULT_VALIDATE, C_BUILD_VALIDATE, C_VALIDATE) */
1033
1034 -----------------------------------------------------
1035 l_debug_info := 'Assign values to p_new_segments';
1036 -----------------------------------------------------
1037 p_new_segments := AP_OIE_KFF_SEGMENTS_T('');
1038 p_new_segments.extend(l_num_segments);
1039 FOR i IN 1..l_num_segments LOOP
1040 p_new_segments(i) := l_exp_line_acct_segs_array(i);
1041 END LOOP;
1042
1043
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 AP_WEB_DB_UTIL_PKG.RaiseException( 'BuildAccount' );
1047 APP_EXCEPTION.RAISE_EXCEPTION;
1048
1049 END BuildAccount;
1050
1051
1052 PROCEDURE BuildDistProjectAccount(
1053 p_report_header_id IN NUMBER,
1054 p_report_line_id IN NUMBER,
1055 p_report_distribution_id IN NUMBER,
1056 p_exp_type_parameter_id IN NUMBER,
1057 p_new_segments OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
1058 p_new_ccid OUT NOCOPY NUMBER,
1059 p_return_error_message OUT NOCOPY VARCHAR2,
1060 p_return_status OUT NOCOPY VARCHAR2) IS
1061
1062 CURSOR pa_cur IS
1063 SELECT
1064 aerh.employee_id,
1065 aerh.flex_concatenated header_cost_center,
1066 aerh.default_currency_code reimbursement_currency_code,
1067 aerh.week_end_date,
1068 aerh.attribute_category header_attribute_category,
1069 aerh.attribute1 header_attribute1,
1070 aerh.attribute2 header_attribute2,
1071 aerh.attribute3 header_attribute3,
1072 aerh.attribute4 header_attribute4,
1073 aerh.attribute5 header_attribute5,
1074 aerh.attribute6 header_attribute6,
1075 aerh.attribute7 header_attribute7,
1076 aerh.attribute8 header_attribute8,
1077 aerh.attribute9 header_attribute9,
1078 aerh.attribute10 header_attribute10,
1079 aerh.attribute11 header_attribute11,
1080 aerh.attribute12 header_attribute12,
1081 aerh.attribute13 header_attribute13,
1082 aerh.attribute14 header_attribute14,
1083 aerh.attribute15 header_attribute15,
1084 aerl.start_expense_date,
1085 dist.amount,
1086 aerl.credit_card_trx_id,
1087 aerl.expenditure_item_date,
1088 aerl.expenditure_type,
1089 aerl.pa_quantity,
1090 dist.expenditure_organization_id,
1091 aerl.adjustment_reason,
1092 aerl.category_code,
1093 aerl.attribute_category line_attribute_category,
1094 aerl.attribute1 line_attribute1,
1095 aerl.attribute2 line_attribute2,
1096 aerl.attribute3 line_attribute3,
1097 aerl.attribute4 line_attribute4,
1098 aerl.attribute5 line_attribute5,
1099 aerl.attribute6 line_attribute6,
1100 aerl.attribute7 line_attribute7,
1101 aerl.attribute8 line_attribute8,
1102 aerl.attribute9 line_attribute9,
1103 aerl.attribute10 line_attribute10,
1104 aerl.attribute11 line_attribute11,
1105 aerl.attribute12 line_attribute12,
1106 aerl.attribute13 line_attribute13,
1107 aerl.attribute14 line_attribute14,
1108 aerl.attribute15 line_attribute15,
1109 dist.cost_center line_flex_concat,
1110 aerl.ap_validation_error,
1111 dist.project_id,
1112 dist.task_id,
1113 dist.award_id,
1114 s.base_currency_code,
1115 s.default_exchange_rate_type,
1116 sob.chart_of_accounts_id,
1117 erp.pa_expenditure_type new_expenditure_type
1118 FROM ap_expense_report_headers aerh,
1119 ap_expense_report_lines aerl,
1120 ap_exp_report_dists dist,
1121 ap_system_parameters s,
1122 fnd_currencies_vl c,
1123 gl_sets_of_books sob,
1124 ap_expense_report_params erp
1125 WHERE c.currency_code = s.base_currency_code
1126 AND sob.set_of_books_id = s.set_of_books_id
1127 AND erp.parameter_id = p_exp_type_parameter_id
1128 AND dist.report_distribution_id = p_report_distribution_id
1129 AND dist.report_line_id = aerl.report_line_id
1130 AND aerh.report_header_id = aerl.report_header_id;
1131
1132 ln_default_exchange_rate NUMBER;
1133 ln_acct_raw_cost NUMBER;
1134 ln_vendor_id NUMBER;
1135 ln_award_set_id NUMBER;
1136 lv_expense_report_cost_center AP_WEB_DB_EXPRPT_PKG.expHdr_flexConcat;
1137 lv_procedure_billable_flag VARCHAR2(200);
1138 lv_msg_type VARCHAR2(2000);
1139 l_pa_CCID gl_code_combinations.code_combination_id%TYPE;
1140 lv_concat_segs VARCHAR2(2000);
1141 lv_concat_ids VARCHAR2(2000);
1142 lv_concat_description VARCHAR2(2000);
1143 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1144 ln_segment_count NUMBER := 0;
1145 lv_flex_segment_delimiter VARCHAR2(1);
1146 lb_gms_accounting_created BOOLEAN := false;
1147
1148 pa_rec pa_cur%ROWTYPE;
1149 l_debug_info varchar2(200);
1150 l_default_emp_ccid AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;
1151 BEGIN
1152
1153 IF p_report_header_id is null
1154 OR p_report_line_id is null
1155 OR p_report_distribution_id is null
1156 OR p_exp_type_parameter_id is null THEN
1157 p_new_ccid := to_number(null);
1158 p_return_status := 'ERROR';
1159 RETURN;
1160 END IF;
1161
1162 if (AP_WEB_CUS_ACCTG_PKG.BuildDistProjectAccount(
1163 p_report_header_id => p_report_header_id,
1164 p_report_line_id => p_report_line_id,
1165 p_report_distribution_id => p_report_distribution_id,
1166 p_exp_type_parameter_id => p_exp_type_parameter_id,
1167 p_new_segments => p_new_segments,
1168 p_new_ccid => p_new_ccid,
1169 p_return_error_message => p_return_error_message,
1170 p_return_status => p_return_status)) then
1171
1172 --------------------------------------------------------------------
1173 l_debug_info := 'Custom BuildDistProjectAccount';
1174 --------------------------------------------------------------------
1175
1176 return;
1177
1178 end if;
1179
1180 p_return_status := 'SUCCESS';
1181
1182 OPEN pa_cur;
1183 FETCH pa_cur into pa_rec;
1184 IF pa_cur%NOTFOUND THEN
1185 CLOSE pa_cur;
1186 p_new_ccid := to_number(null);
1187 p_return_status := 'ERROR';
1188 RETURN;
1189 END IF;
1190 CLOSE pa_cur;
1191
1192 -----------------------------------------------------
1193 l_debug_info := 'Get vendor ID if exists for this employee';
1194 -----------------------------------------------------
1195 IF (NOT AP_WEB_DB_AP_INT_PKG.GetVendorID(pa_rec.employee_id, ln_vendor_id)) THEN
1196 ln_vendor_id := NULL;
1197 END IF; /* GetVendorID */
1198
1199 -- For PATC: Get the default exchange rate for the week_end_date reimbursement currency/functional currency
1200 -----------------------------------------------------
1201 l_debug_info := 'AP_UTILITIES_PKG.get_exchange_rate';
1202 -----------------------------------------------------
1203 ln_default_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate(pa_rec.base_currency_code,
1204 pa_rec.reimbursement_currency_code,
1205 pa_rec.default_exchange_rate_type,
1206 pa_rec.week_end_date,
1207 'ValidatePATransaction');
1208
1209 -- Calculate the receipt amount in the functional currency
1210 ln_acct_raw_cost := NULL;
1211 IF ln_default_exchange_rate IS NOT NULL AND ln_default_exchange_rate <> 0 THEN
1212 ln_acct_raw_cost := AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(pa_rec.amount/ln_default_exchange_rate, pa_rec.base_currency_code);
1213 END IF;
1214
1215 -----------------------------------------------------
1216 l_debug_info := 'Call PATC to get billable flag value';
1217 -----------------------------------------------------
1218 AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id => pa_rec.project_id,
1219 p_task_id => pa_rec.task_id,
1220 p_ei_date => pa_rec.expenditure_item_date,
1221 p_expenditure_type => pa_rec.new_expenditure_type,
1222 p_non_labor_resource => NULL,
1223 p_person_id => pa_rec.employee_id,
1224 p_quantity => NULL,
1225 p_denom_currency_code=> pa_rec.reimbursement_currency_code,
1226 p_acct_currency_code => pa_rec.base_currency_code,
1227 p_denom_raw_cost => pa_rec.amount,
1228 p_acct_raw_cost => ln_acct_raw_cost,
1229 p_acct_rate_type => pa_rec.default_exchange_rate_type,
1230 p_acct_rate_date => pa_rec.week_end_date,
1231 p_acct_exchange_rate => ln_default_exchange_rate,
1232 p_transfer_ei => NULL,
1233 p_incurred_by_org_id => pa_rec.expenditure_organization_id,
1234 p_nl_resource_org_id => NULL,
1235 p_transaction_source => NULL,
1236 p_calling_module => 'SelfService',
1237 p_vendor_id => ln_vendor_id,
1238 p_entered_by_user_id => NULL,
1239 p_attribute_category => pa_rec.line_attribute_category,
1240 p_attribute1 => pa_rec.line_attribute1,
1241 p_attribute2 => pa_rec.line_attribute2,
1242 p_attribute3 => pa_rec.line_attribute3,
1243 p_attribute4 => pa_rec.line_attribute4,
1244 p_attribute5 => pa_rec.line_attribute5,
1245 p_attribute6 => pa_rec.line_attribute6,
1246 p_attribute7 => pa_rec.line_attribute7,
1247 p_attribute8 => pa_rec.line_attribute8,
1248 p_attribute9 => pa_rec.line_attribute9,
1249 p_attribute10 => pa_rec.line_attribute10,
1250 p_attribute11 => pa_rec.line_attribute11,
1251 p_attribute12 => pa_rec.line_attribute12,
1252 p_attribute13 => pa_rec.line_attribute13,
1253 p_attribute14 => pa_rec.line_attribute14,
1254 p_attribute15 => pa_rec.line_attribute15,
1255 p_msg_type => lv_msg_type,
1256 p_msg_data => p_return_error_message,
1257 p_billable_flag => lv_procedure_billable_flag);
1258
1259 IF (p_return_error_message is not null) AND (lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError) THEN
1260 /* validation failed, we cannot generate the projects CCID */
1261 p_new_ccid := to_number(null);
1262 p_return_status := 'VALIDATION_ERROR';
1263 RETURN;
1264 ELSE
1265 /* For some reason the projects API returns error even though validation is succesful.
1266 * When the message data is also provided then the validation actually failed. Since these
1267 * same variables are used elsewhere resetting them to null when validation was succesful */
1268 lv_msg_type := NULL;
1269 p_return_error_message := NULL;
1270 END IF;
1271
1272 -- Get Award Set ID before calling A/C generator
1273 ln_award_set_id := null;
1274
1275 IF (GMS_OIE_INT_PKG.IsGrantsEnabled() and pa_rec.award_id is not null) THEN
1276 -----------------------------------------------------
1277 l_debug_info := 'GMS_OIE_INT_PKG.CreateACGenADL';
1278 -----------------------------------------------------
1279 ln_award_set_id := GMS_OIE_INT_PKG.CreateACGenADL(p_award_id => pa_rec.award_id,
1280 p_project_id => pa_rec.project_id,
1281 p_task_id => pa_rec.task_id);
1282 lb_gms_accounting_created := true;
1283 END IF;
1284
1285 IF (pa_rec.line_flex_concat is not null) THEN
1286 lv_expense_report_cost_center := pa_rec.line_flex_concat;
1287 ELSE
1288 lv_expense_report_cost_center := pa_rec.header_cost_center;
1289 END IF;
1290
1291 --bug 4629320
1292 IF AP_WEB_DB_EXPRPT_PKG.GetDefaultEmpCCID(pa_rec.employee_id, l_default_emp_ccid) THEN
1293 null;
1294 END IF;
1295 -----------------------------------------------------
1296 l_debug_info := 'pa_acc_gen_wf_pkg.ap_er_generate_account';
1297 -----------------------------------------------------
1298 IF ( NOT pa_acc_gen_wf_pkg.ap_er_generate_account (p_project_id => pa_rec.project_id,
1299 p_task_id => pa_rec.task_id,
1300 p_expenditure_type => pa_rec.new_expenditure_type,
1301 p_vendor_id => ln_vendor_id,
1302 p_expenditure_organization_id=> pa_rec.expenditure_organization_id,
1303
1304 p_expenditure_item_date => pa_rec.expenditure_item_date,
1305 p_billable_flag => lv_procedure_billable_flag,
1306 p_chart_of_accounts_id => pa_rec.chart_of_accounts_id,
1307 p_calling_module => 'SelfService',
1308 p_employee_id => pa_rec.employee_id,
1309 p_employee_ccid => l_default_emp_ccid,
1310 p_expense_type => p_exp_type_parameter_id,
1311 p_expense_cc => lv_expense_report_cost_center,
1312 p_attribute_category => pa_rec.header_attribute_category,
1313 p_attribute1 => pa_rec.header_attribute1,
1314 p_attribute2 => pa_rec.header_attribute2,
1315 p_attribute3 => pa_rec.header_attribute3,
1316 p_attribute4 => pa_rec.header_attribute4,
1317 p_attribute5 => pa_rec.header_attribute5,
1318 p_attribute6 => pa_rec.header_attribute6,
1319 p_attribute7 => pa_rec.header_attribute7,
1320 p_attribute8 => pa_rec.header_attribute8,
1321 p_attribute9 => pa_rec.header_attribute9,
1322 p_attribute10 => pa_rec.header_attribute10,
1323 p_attribute11 => pa_rec.header_attribute11,
1324 p_attribute12 => pa_rec.header_attribute12,
1325 p_attribute13 => pa_rec.header_attribute13,
1326 p_attribute14 => pa_rec.header_attribute14,
1327 p_attribute15 => pa_rec.header_attribute15,
1328 p_line_attribute_category => pa_rec.line_attribute_category,
1329 p_line_attribute1 => pa_rec.line_attribute1,
1330 p_line_attribute2 => pa_rec.line_attribute2,
1331 p_line_attribute3 => pa_rec.line_attribute3,
1332 p_line_attribute4 => pa_rec.line_attribute4,
1333 p_line_attribute5 => pa_rec.line_attribute5,
1334 p_line_attribute6 => pa_rec.line_attribute6,
1335 p_line_attribute7 => pa_rec.line_attribute7,
1336 p_line_attribute8 => pa_rec.line_attribute8,
1337 p_line_attribute9 => pa_rec.line_attribute9,
1338 p_line_attribute10 => pa_rec.line_attribute10,
1339 p_line_attribute11 => pa_rec.line_attribute11,
1340 p_line_attribute12 => pa_rec.line_attribute12,
1341 p_line_attribute13 => pa_rec.line_attribute13,
1342 p_line_attribute14 => pa_rec.line_attribute14,
1343 p_line_attribute15 => pa_rec.line_attribute15,
1344 x_return_ccid => l_pa_ccid,
1345 x_concat_segs => lv_concat_segs,
1346 x_concat_ids => lv_concat_ids,
1347 x_concat_descrs => lv_concat_description,
1348 x_error_message => p_return_error_message,
1349 x_award_set_id => ln_award_set_id)) THEN
1350
1351 IF (p_return_error_message is not null) THEN
1352 /* could not generate the projects CCID */
1353 p_new_ccid := to_number(null);
1354 p_return_status := 'GENERATION_ERROR';
1355 RETURN;
1356
1357 END IF;
1358
1359 END IF; /* pa_acc_gen_wf_pkg.ap_er_generate_account */
1360
1361 IF (lb_gms_accounting_created = true) THEN
1362 -----------------------------------------------------
1363 l_debug_info := 'gms_oie_int_pkg.DeleteACGenADL';
1364 -----------------------------------------------------
1365 IF gms_oie_int_pkg.DeleteACGenADL(ln_award_set_id) THEN
1366 null;
1367 END IF;
1368 END IF;
1369
1370 -----------------------------------------------------
1371 l_debug_info := 'Get segment delimeter';
1372 -----------------------------------------------------
1373 lv_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL','GL#',pa_rec.chart_of_accounts_id);
1374
1375 -----------------------------------------------------
1376 l_debug_info := 'Break up segments';
1377 -----------------------------------------------------
1378 ln_segment_count := FND_FLEX_EXT.Breakup_Segments(lv_concat_segs, lv_flex_segment_delimiter, l_segments);
1379
1380 -----------------------------------------------------
1381 l_debug_info := 'Assign values to p_new_segments';
1382 -----------------------------------------------------
1383 p_new_segments := AP_OIE_KFF_SEGMENTS_T('');
1384 p_new_segments.extend(ln_segment_count);
1385 FOR i IN 1..ln_segment_count LOOP
1386 p_new_segments(i) := l_segments(i);
1387 END LOOP;
1388
1389 -- create account if don't already exist
1390 IF (l_pa_ccid = -1) THEN
1391
1392 -----------------------------------------------------
1393 l_debug_info := 'Validate segments';
1394 -----------------------------------------------------
1395 -- Bug: 12363929, Summary flag on CCIDs not to be allowed.
1396 IF (FND_FLEX_KEYVAL.validate_segs(operation=>'CREATE_COMBINATION',
1397 appl_short_name=>'SQLGL',
1398 key_flex_code=>'GL#',
1399 structure_number=>pa_rec.chart_of_accounts_id,
1400 concat_segments=>lv_concat_segs,
1401 VRULE=>'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN')) THEN
1402 l_pa_ccid := FND_FLEX_KEYVAL.combination_id;
1403 ELSE
1404 p_return_error_message := FND_FLEX_KEYVAL.error_message;
1405 /* could not generate the projects CCID */
1406 p_new_ccid := to_number(null);
1407 p_return_status := 'GENERATION_ERROR';
1408 return;
1409 END IF; /* FND_FLEX_KEYVAL.validate_segs */
1410
1411 END IF; /* l_pa_ccid = -1 */
1412
1413 IF (l_pa_ccid is null OR l_pa_ccid = -1) THEN
1414 p_new_ccid := to_number(null);
1415 p_return_status := 'GENERATION_ERROR';
1416 ELSE
1417 p_new_ccid := l_pa_ccid;
1418 END IF;
1419
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422 /* could not generate the projects CCID */
1423 p_new_ccid := to_number(null);
1424 p_return_status := 'ERROR';
1425
1426 END BuildDistProjectAccount;
1427
1428 PROCEDURE ValidateProjectAccounting(
1429 p_report_line_id IN NUMBER,
1430 p_web_parameter_id IN NUMBER,
1431 p_project_id IN NUMBER,
1432 p_task_id IN NUMBER,
1433 p_award_id IN NUMBER,
1434 p_award_number IN VARCHAR2,
1435 p_expenditure_org_id IN NUMBER,
1436 p_amount IN NUMBER,
1437 p_return_error_message OUT NOCOPY VARCHAR2,
1438 p_msg_count OUT NOCOPY NUMBER,
1439 p_msg_data OUT NOCOPY VARCHAR2
1440 ) IS
1441
1442 CURSOR pa_cur IS
1443 SELECT
1444 aerh.employee_id,
1445 aerh.flex_concatenated header_cost_center,
1446 aerh.default_currency_code reimbursement_currency_code,
1447 aerh.week_end_date,
1448 aerh.attribute_category header_attribute_category,
1449 aerh.attribute1 header_attribute1,
1450 aerh.attribute2 header_attribute2,
1451 aerh.attribute3 header_attribute3,
1452 aerh.attribute4 header_attribute4,
1453 aerh.attribute5 header_attribute5,
1454 aerh.attribute6 header_attribute6,
1455 aerh.attribute7 header_attribute7,
1456 aerh.attribute8 header_attribute8,
1457 aerh.attribute9 header_attribute9,
1458 aerh.attribute10 header_attribute10,
1459 aerh.attribute11 header_attribute11,
1460 aerh.attribute12 header_attribute12,
1461 aerh.attribute13 header_attribute13,
1462 aerh.attribute14 header_attribute14,
1463 aerh.attribute15 header_attribute15,
1464 aerl.start_expense_date,
1465 aerl.end_expense_date,
1466 aerl.credit_card_trx_id,
1467 aerl.expenditure_item_date,
1468 aerl.expenditure_type,
1469 aerl.pa_quantity,
1470 aerl.adjustment_reason,
1471 aerl.category_code,
1472 aerl.attribute_category line_attribute_category,
1473 aerl.attribute1 line_attribute1,
1474 aerl.attribute2 line_attribute2,
1475 aerl.attribute3 line_attribute3,
1476 aerl.attribute4 line_attribute4,
1477 aerl.attribute5 line_attribute5,
1478 aerl.attribute6 line_attribute6,
1479 aerl.attribute7 line_attribute7,
1480 aerl.attribute8 line_attribute8,
1481 aerl.attribute9 line_attribute9,
1482 aerl.attribute10 line_attribute10,
1483 aerl.attribute11 line_attribute11,
1484 aerl.attribute12 line_attribute12,
1485 aerl.attribute13 line_attribute13,
1486 aerl.attribute14 line_attribute14,
1487 aerl.attribute15 line_attribute15,
1488 aerl.ap_validation_error,
1489 s.base_currency_code,
1490 s.default_exchange_rate_type,
1491 sob.chart_of_accounts_id,
1492 erp.pa_expenditure_type new_expenditure_type
1493 FROM ap_expense_report_headers_all aerh,
1494 ap_expense_report_lines_all aerl,
1495 ap_system_parameters_all s,
1496 fnd_currencies_vl c,
1497 gl_sets_of_books sob,
1498 ap_expense_report_params_all erp
1499 WHERE c.currency_code = s.base_currency_code
1500 AND sob.set_of_books_id = s.set_of_books_id
1501 AND erp.parameter_id = p_web_parameter_id
1502 AND aerh.report_header_id = aerl.report_header_id
1503 AND aerl.report_line_id = p_report_line_id;
1504
1505 pa_rec pa_cur%ROWTYPE;
1506 lv_msg_type VARCHAR2(2000);
1507 lv_procedure_billable_flag VARCHAR2(200);
1508 ln_vendor_id NUMBER;
1509 ln_default_exchange_rate NUMBER;
1510 ln_acct_raw_cost NUMBER;
1511 l_errors AP_WEB_UTILITIES_PKG.expError;
1512 l_receipts_errors AP_WEB_UTILITIES_PKG.receipt_error_stack;
1513 I INTEGER;
1514 BEGIN
1515 IF p_report_line_id is null or p_web_parameter_id is null THEN
1516 p_return_error_message := 'ERROR';
1517 RETURN;
1518 END IF;
1519
1520 -- Bug: 7176464
1521 AP_WEB_UTILITIES_PKG.InitMessages(1, l_receipts_errors);
1522 IF (AP_WEB_CUS_ACCTG_PKG.CustomValidateProjectDist(
1523 p_report_line_id,
1524 p_web_parameter_id,
1525 p_project_id,
1526 p_task_id,
1527 p_award_id,
1528 p_expenditure_org_id,
1529 p_amount,
1530 p_return_error_message)) THEN
1531 -- Custom Validate Project Allocations
1532 IF (p_return_error_message is not null) THEN
1533
1534 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1535 p_return_error_message,
1536 lv_msg_type,
1537 'PATC',
1538 1,
1539 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1540 --p_msg_count := 1;
1541 --p_msg_data := p_return_error_message;
1542 --fnd_msg_pub.count_and_get(p_count => p_msg_count,p_data => p_msg_data);
1543 -- Bug 7497991 Commenting Return Statement to continue ValidationS
1544 -- return;
1545 END IF;
1546 END IF;
1547
1548 OPEN pa_cur;
1549
1550 FETCH pa_cur into pa_rec;
1551 IF pa_cur%NOTFOUND THEN
1552 CLOSE pa_cur;
1553 p_return_error_message := 'ERROR';
1554 RETURN;
1555 END IF;
1556 CLOSE pa_cur;
1557
1558 --AP_WEB_UTILITIES_PKG.InitMessages(1, l_receipts_errors);
1559
1560 IF (NOT AP_WEB_DB_AP_INT_PKG.GetVendorID(pa_rec.employee_id, ln_vendor_id)) THEN
1561 ln_vendor_id := NULL;
1562 END IF; /* GetVendorID */
1563
1564 ln_default_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate(pa_rec.base_currency_code,
1565 pa_rec.reimbursement_currency_code,
1566 pa_rec.default_exchange_rate_type,
1567 pa_rec.week_end_date,
1568 'ValidatePATransaction');
1569
1570 -- Calculate the receipt amount in the functional currency
1571 ln_acct_raw_cost := NULL;
1572 IF ln_default_exchange_rate IS NOT NULL AND ln_default_exchange_rate <> 0 THEN
1573 ln_acct_raw_cost := AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(p_amount/ln_default_exchange_rate, pa_rec.base_currency_code);
1574 END IF;
1575
1576
1577 AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id => p_project_id,
1578 p_task_id => p_task_id,
1579 p_ei_date => pa_rec.start_expense_date,
1580 p_expenditure_type => pa_rec.new_expenditure_type,
1581 p_non_labor_resource => NULL,
1582 p_person_id => pa_rec.employee_id,
1583 p_quantity => NULL,
1584 p_denom_currency_code=> pa_rec.reimbursement_currency_code,
1585 p_acct_currency_code => pa_rec.base_currency_code,
1586 p_denom_raw_cost => p_amount,
1587 p_acct_raw_cost => ln_acct_raw_cost,
1588 p_acct_rate_type => pa_rec.default_exchange_rate_type,
1589 p_acct_rate_date => pa_rec.week_end_date,
1590 p_acct_exchange_rate => ln_default_exchange_rate,
1591 p_transfer_ei => NULL,
1592 p_incurred_by_org_id => p_expenditure_org_id,
1593 p_nl_resource_org_id => NULL,
1594 p_transaction_source => NULL,
1595 p_calling_module => 'SelfService',
1596 p_vendor_id => ln_vendor_id,
1597 p_entered_by_user_id => NULL,
1598 p_attribute_category => pa_rec.line_attribute_category,
1599 p_attribute1 => pa_rec.line_attribute1,
1600 p_attribute2 => pa_rec.line_attribute2,
1601 p_attribute3 => pa_rec.line_attribute3,
1602 p_attribute4 => pa_rec.line_attribute4,
1603 p_attribute5 => pa_rec.line_attribute5,
1604 p_attribute6 => pa_rec.line_attribute6,
1605 p_attribute7 => pa_rec.line_attribute7,
1606 p_attribute8 => pa_rec.line_attribute8,
1607 p_attribute9 => pa_rec.line_attribute9,
1608 p_attribute10 => pa_rec.line_attribute10,
1609 p_attribute11 => pa_rec.line_attribute11,
1610 p_attribute12 => pa_rec.line_attribute12,
1611 p_attribute13 => pa_rec.line_attribute13,
1612 p_attribute14 => pa_rec.line_attribute14,
1613 p_attribute15 => pa_rec.line_attribute15,
1614 p_msg_type => lv_msg_type,
1615 p_msg_data => p_return_error_message,
1616 p_billable_flag => lv_procedure_billable_flag);
1617
1618 if (p_return_error_message IS NOT NULL AND lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError) then
1619 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1620 p_return_error_message,
1621 lv_msg_type,
1622 'PATC',
1623 1,
1624 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1625 else
1626 -- Bug: 6936055, GMS Integration, Award Validation
1627 if (GMS_OIE_INT_PKG.IsGrantsEnabled() and p_award_id is not null and
1628 not GMS_OIE_INT_PKG.DoGrantsValidation(p_project_id => p_project_id,
1629 p_task_id => p_task_id,
1630 p_award_id => p_award_id,
1631 p_award_number => p_award_number,
1632 p_expenditure_type => pa_rec.new_expenditure_type,
1633 p_expenditure_item_date => pa_rec.start_expense_date,
1634 p_calling_module => 'SelfService',
1635 p_err_msg => p_return_error_message)) then
1636 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1637 p_return_error_message,
1638 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1639 'GMS',
1640 1,
1641 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1642 else
1643 p_return_error_message := null;
1644 lv_msg_type := null;
1645 end if;
1646 end if;
1647 if (p_return_error_message is null and pa_rec.end_expense_date is not null) then
1648 AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id => p_project_id,
1649 p_task_id => p_task_id,
1650 p_ei_date => pa_rec.end_expense_date,
1651 p_expenditure_type => pa_rec.new_expenditure_type,
1652 p_non_labor_resource => NULL,
1653 p_person_id => pa_rec.employee_id,
1654 p_quantity => NULL,
1655 p_denom_currency_code=> pa_rec.reimbursement_currency_code,
1656 p_acct_currency_code => pa_rec.base_currency_code,
1657 p_denom_raw_cost => p_amount,
1658 p_acct_raw_cost => ln_acct_raw_cost,
1659 p_acct_rate_type => pa_rec.default_exchange_rate_type,
1660 p_acct_rate_date => pa_rec.week_end_date,
1661 p_acct_exchange_rate => ln_default_exchange_rate,
1662 p_transfer_ei => NULL,
1663 p_incurred_by_org_id => p_expenditure_org_id,
1664 p_nl_resource_org_id => NULL,
1665 p_transaction_source => NULL,
1666 p_calling_module => 'SelfService',
1667 p_vendor_id => ln_vendor_id,
1668 p_entered_by_user_id => NULL,
1669 p_attribute_category => pa_rec.line_attribute_category,
1670 p_attribute1 => pa_rec.line_attribute1,
1671 p_attribute2 => pa_rec.line_attribute2,
1672 p_attribute3 => pa_rec.line_attribute3,
1673 p_attribute4 => pa_rec.line_attribute4,
1674 p_attribute5 => pa_rec.line_attribute5,
1675 p_attribute6 => pa_rec.line_attribute6,
1676 p_attribute7 => pa_rec.line_attribute7,
1677 p_attribute8 => pa_rec.line_attribute8,
1678 p_attribute9 => pa_rec.line_attribute9,
1679 p_attribute10 => pa_rec.line_attribute10,
1680 p_attribute11 => pa_rec.line_attribute11,
1681 p_attribute12 => pa_rec.line_attribute12,
1682 p_attribute13 => pa_rec.line_attribute13,
1683 p_attribute14 => pa_rec.line_attribute14,
1684 p_attribute15 => pa_rec.line_attribute15,
1685 p_msg_type => lv_msg_type,
1686 p_msg_data => p_return_error_message,
1687 p_billable_flag => lv_procedure_billable_flag);
1688
1689 if (p_return_error_message IS NOT NULL AND lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError) then
1690 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1691 p_return_error_message,
1692 lv_msg_type,
1693 'PATC',
1694 1,
1695 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1696 else
1697 -- Bug: 6936055, GMS Integration, Award Validation
1698 if (GMS_OIE_INT_PKG.IsGrantsEnabled() and p_award_id is not null and
1699 not GMS_OIE_INT_PKG.DoGrantsValidation(p_project_id => p_project_id,
1700 p_task_id => p_task_id,
1701 p_award_id => p_award_id,
1702 p_award_number => p_award_number,
1703 p_expenditure_type => pa_rec.new_expenditure_type,
1704 p_expenditure_item_date => pa_rec.end_expense_date,
1705 p_calling_module => 'SelfService',
1706 p_err_msg => p_return_error_message)) then
1707 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1708 p_return_error_message,
1709 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1710 'GMS',
1711 1,
1712 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1713 else
1714 p_return_error_message := null;
1715 lv_msg_type := null;
1716 end if;
1717 end if;
1718 end if;
1719
1720 fnd_msg_pub.count_and_get(p_count => p_msg_count,p_data => p_msg_data);
1721
1722 EXCEPTION
1723 WHEN OTHERS THEN
1724 fnd_msg_pub.count_and_get(p_count => p_msg_count,p_data => p_msg_data);
1725 END ValidateProjectAccounting;
1726
1727 -- Bug: 6631437, CC Segment not rendered if it has a parent segment
1728 PROCEDURE GetDependentSegmentValue(p_employee_id IN NUMBER,
1729 p_vset_name IN VARCHAR2,
1730 p_seg_value OUT NOCOPY VARCHAR2) IS
1731 l_EmpInfoRec AP_WEB_DB_HR_INT_PKG.EmployeeInfoRec;
1732 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
1733 l_dep_seg_num NUMBER;
1734 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1735 l_num_of_segments NUMBER;
1736 BEGIN
1737 -- Fetch the Employee Info
1738 IF (AP_WEB_DB_HR_INT_PKG.GetEmployeeInfo(p_employee_id, l_EmpInfoRec)) THEN
1739 NULL;
1740 END IF;
1741 -- Fetch the Chart of Accounts
1742 IF (AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id)) THEN
1743 NULL;
1744 END IF;
1745 -- Get the dependent segment number
1746 IF (GetDependentSegment(p_vset_name, l_chart_of_accounts_id, l_dep_seg_num)) THEN
1747 NULL;
1748 END IF;
1749 -- Get the segments
1750 IF (FND_FLEX_EXT.GET_SEGMENTS('SQLGL',
1751 'GL#',
1752 l_chart_of_accounts_id,
1753 l_EmpInfoRec.emp_ccid,
1754 l_num_of_segments,
1755 l_segments)) THEN
1756 -- Get the segment value
1757 p_seg_value := l_segments(l_dep_seg_num);
1758 END IF;
1759
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDependentSegmentValue' );
1763 APP_EXCEPTION.RAISE_EXCEPTION;
1764
1765 END GetDependentSegmentValue;
1766
1767 END AP_WEB_ACCTG_PKG;