1 PACKAGE BODY pay_costing_kff_util_pkg AS
2 /* $Header: pykffutl.pkb 120.0 2005/05/29 06:22:31 appldev noship $ */
3 /*===========================================================================*
4 | Copyright (c) 1994 Oracle Corporation |
5 | All rights reserved. |
6 *============================================================================*/
7 /*
8 rem
9 rem Version Date Author Reason
10 rem 110.0 28-Aug-1998 S.Billing Created file
11 rem 110.2 25-Feb-1999 S.Billing Modified cursor behaviour,
12 rem also return row if no segments
13 rem qualified at passed in lvl;
14 rem and if called from LEL form,
15 rem check if Balancing segments
16 rem are qualified, if not then return
17 rem row,
18 rem both changes avoid CAKFF errors
19 rem 110.4 29-Oct-1999 A.Logue New procedure
20 rem costing_kff_null_default_segs
21 rem to handle issue of segment
22 rem default values erroneously
23 rem getting into various levels
24 rem of costing.
25 rem 115.5 25-MAY-2003 A.Logue Further qualify by id_flex_code
26 rem = 'COST' when joining to
27 rem FND_SEGMENT_ATTRIBUTE_VALUES.
28 rem And NOCOPY changes.
29 rem Bug 2961843.
30 rem 115.6 25-Sep-2003 swinton Enhancement 3121279. Added:
31 rem - cost_keyflex_segment_defined function
32 rem - get_cost_keyflex_segment_value
33 rem function
34 rem - get_cost_keyflex_structure function
35 rem - validate_costing_keyflex procedure
36 rem The above are required to support the
37 rem View Cost Allocation Keyflex OA
38 rem Framework pages.
39 rem 115.8 05-JUL-2004 A.Logue Bug 3744957 : if no required segments
40 rem in the level when COST_MAND_SEG_CHECK
41 rem set to 'Y' allow nullset
42 rem 115.9 09-JUL-2004 A.Logue Bug 3756198. Performance fixes.
43 */
44
45 --
46 -- global package name
47 --
48 g_package VARCHAR2(33) := ' pay_costing_kff_util_pkg.';
49
50
51
52 -- ----------------------------------------------------------------------------
53 -- |--------------------< costing_kff_seg_behaviour >-------------------------|
54 -- ----------------------------------------------------------------------------
55 -- {Start Of Comments}
56 --
57 -- Description:
58 -- determines costing kff behaviour,
59 --
60 -- if HR:COST_MAND_SEG_CHECK is set to Y, the customer
61 -- has chosen to make required segments mandatory and not
62 -- nulls allowed (ie. nulls are not recognised as a valid value),
63 -- if required segements have been qualified at more than 1 level,
64 -- the costing process will take its input from the lowest level,
65 -- thus values defined at higher levels will be redundant,
66 --
67 -- if HR:COST_MAND_SEG_CHECK is set to N or is undefined,
68 -- the cursor is used to infer segment behaviour,
69 -- if the cursor returns no rows then required segements are made
70 -- mandatory and nulls are not recognised as valid values
71 --
72 -- Pre Conditions:
73 -- none
74 --
75 -- In Arguments:
76 -- level - current level the calling form is defined as,
77 -- ie. Assignment, Balancing, Element Link, Element,
78 -- Organization or Payroll
79 --
80 -- Post Success:
81 -- if HR:COST_MAND_SEG_CHECK is set to Y:
82 -- p_required = Y and p_allownulls = N
83 --
84 -- if HR:COST_MAND_SEG_CHECK is set to N or is undefined:
85 -- p_required = Y and p_allownulls = N if the following conditions are
86 -- true:
87 -- - no segements have been defined at multiple levels,
88 -- - segments have been defined at multiple levels but are not required,
89 -- - segments have been defined at multiple levels and are required but
90 -- do not apply to the current level
91 --
92 -- else p_required = N and p_allownulls = Y
93 --
94 -- Post Failure:
95 -- none
96 --
97 -- Access Status:
98 -- public
99 --
100 -- {End Of Comments}
101 --
102 PROCEDURE costing_kff_seg_behaviour(
103 p_level IN VARCHAR2,
104 p_cost_id_flex_num IN NUMBER,
105 p_required OUT NOCOPY VARCHAR2,
106 p_allownulls OUT NOCOPY VARCHAR2)
107 IS
108
109 l_proc VARCHAR2(72) := g_package||'costing_kff_seg_behaviour';
110
111 CURSOR csr_chk_qual_segments IS
112 SELECT 'M'
113 FROM FND_SEGMENT_ATTRIBUTE_VALUES sa2,
114 FND_ID_FLEX_SEGMENTS fs,
115 FND_SEGMENT_ATTRIBUTE_VALUES sa1
116 WHERE sa1.id_flex_num = p_cost_id_flex_num
117 and sa1.id_flex_code = 'COST'
118 and sa1.attribute_value = 'Y'
119 and sa1.segment_attribute_type <> 'BALANCING'
120 and fs.id_flex_num = p_cost_id_flex_num
121 and fs.id_flex_code = 'COST'
122 and fs.required_flag = 'Y'
123 and fs.enabled_flag = 'Y'
124 and fs.application_id = 801
125 and fs.application_column_name =
126 sa1.application_column_name
127 and sa2.id_flex_num = p_cost_id_flex_num
128 and sa2.id_flex_code = 'COST'
129 and sa2.attribute_value = 'Y'
130 and sa1.application_id = fs.application_id
131 and sa2.segment_attribute_type <> 'BALANCING'
132 and sa2.application_id = fs.application_id
133 and sa2.application_column_name =
134 sa1.application_column_name
135 and sa1.segment_attribute_type <> sa2.segment_attribute_type
136 and sa1.segment_attribute_type = p_level
137 UNION ALL
138 /*
139 ** also return a row if no segments have not been qualified at
140 ** passed in lvl,
141 ** avoids nasty ff error msg
142 */
143 SELECT 'N'
144 FROM DUAL
145 WHERE NOT EXISTS
146 (SELECT 'Y'
147 FROM FND_SEGMENT_ATTRIBUTE_VALUES sa
148 WHERE sa.id_flex_num = p_cost_id_flex_num
149 and sa.id_flex_code = 'COST'
150 and sa.application_id = 801
151 and sa.segment_attribute_type = p_level
152 and sa.attribute_value = 'Y'
153 )
154 UNION ALL
155 /*
156 ** special check for element link form,
157 ** if no balancing segments have been defined at this level
158 ** then return a row
159 */
160 SELECT 'B'
161 FROM DUAL
162 WHERE p_level = 'ELEMENT'
163 and NOT EXISTS
164 (SELECT 'Y'
165 FROM FND_SEGMENT_ATTRIBUTE_VALUES sa
166 WHERE sa.id_flex_num = p_cost_id_flex_num
167 and sa.id_flex_code = 'COST'
168 and sa.application_id = 801
169 and sa.segment_attribute_type = 'BALANCING'
170 and sa.attribute_value = 'Y'
171 )
172 ;
173
174 l_chk_qual_segments VARCHAR2(1);
175 l_profile_name VARCHAR2(60) := 'HR:COST_MAND_SEG_CHECK';
176 l_profile_value VARCHAR2(60);
177 l_required VARCHAR2(1) DEFAULT 'N';
178 l_allownulls VARCHAR2(1) DEFAULT 'Y';
179 l_num_reqs_in_this_level NUMBER;
180
181 BEGIN
182 Hr_Utility.Set_Location('Entering:' || l_proc, 5);
183
184 Fnd_Profile.Get(l_profile_name, l_profile_value);
185 Hr_Utility.Trace(' l_profile_value>' || l_profile_value || '<');
186
187 --
188 -- user chosen behaviour
189 --
190 IF (l_profile_value = 'Y') THEN
191 --
192 -- REQUIRED = Y - required segments must have a value (null is valid),
193 -- REQUIRED = N - required segments can be left null,
194 -- ALLOWNULLS = Y - required segments do not allow null values
195 --
196
197 SELECT count(*)
198 INTO l_num_reqs_in_this_level
199 FROM FND_ID_FLEX_SEGMENTS fs,
200 FND_SEGMENT_ATTRIBUTE_VALUES sa1
201 WHERE sa1.id_flex_num = p_cost_id_flex_num
202 and sa1.id_flex_code = 'COST'
203 and sa1.application_id = fs.application_id
204 and sa1.attribute_value = 'Y'
205 and sa1.segment_attribute_type <> 'BALANCING'
206 and sa1.segment_attribute_type = p_level
207 and fs.id_flex_num = p_cost_id_flex_num
208 and fs.id_flex_code = 'COST'
209 and fs.required_flag = 'Y'
210 and fs.enabled_flag = 'Y'
211 and fs.application_id = 801
212 and fs.application_column_name =
213 sa1.application_column_name;
214
215 -- Bug 3744957 : if no required segments in this level
216 -- then don't expect anything for them
217
218 IF (l_num_reqs_in_this_level = 0) THEN
219 l_required := 'N';
220 l_allownulls := 'Y';
221 ELSE
222 l_required := 'Y';
223 l_allownulls := 'N';
224 END IF;
225
226 --
227 -- decide which behaviour to implement for existing customer base
228 --
229 ELSE
230 OPEN csr_chk_qual_segments;
231 FETCH csr_chk_qual_segments
232 INTO l_chk_qual_segments;
233 CLOSE csr_chk_qual_segments;
234 Hr_Utility.Trace(' l_chk_qual_segments>' || l_chk_qual_segments || '<');
235
236 --
237 -- if csr returns no rows, l_chk_qual_segments remains null,
238 -- implement new behaviour,
239 -- if csr returns a row, l_chk_qual_segments is not null,
240 -- use old behaviour
241 --
242 IF (l_chk_qual_segments IS NULL) THEN
243 l_required := 'Y';
244 l_allownulls := 'N';
245 END IF;
246
247 END IF;
248
249 Hr_Utility.Trace(' l_required>' || l_required || '<');
250 Hr_Utility.Trace(' l_allownulls>' || l_allownulls || '<');
251
252 p_required := l_required;
253 p_allownulls := l_allownulls;
254
255 Hr_Utility.Set_Location('Leaving:' || l_proc, 10);
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 Raise;
260 END costing_kff_seg_behaviour;
261
262 -- ----------------------------------------------------------------------------
263 -- |--------------------< costing_kff_null_default_segs >---------------------|
264 -- ----------------------------------------------------------------------------
265 -- {Start Of Comments}
266 --
267 -- Description:
268 -- It nullifies any segment values that shouldn't actually have a value
269 -- at the level passed in ie not qualified
270 --
271 -- This procedure should be called from the various forms used for
272 -- costing data entry to get around the issue whereby any segment
273 -- given a default value in the flexfield definition will be given
274 -- that value by the FND API used to handle the entry of the
275 -- flexfield on the screen if a value was not supplied on the screen.
276 -- Thus a segment not qualified at a level will be given the
277 -- default value thus breaking our costing
278 -- hierarchial approach strategy.
279
280 --
281 -- Pre Conditions:
282 -- none
283 --
284 -- In Arguments:
285 -- level - current level the calling form is defined as,
286 -- ie. Assignment, Balancing, Element Link, Element,
287 -- Organization or Payroll
288 --
289 -- do not apply to the current level
290 --
291 -- Access Status:
292 -- public
293 --
294 -- {End Of Comments}
295 --
296 PROCEDURE costing_kff_null_default_segs(
297 p_level IN VARCHAR2,
298 p_cost_id_flex_num IN NUMBER,
299 p_segment1 IN OUT NOCOPY VARCHAR2,
300 p_segment2 IN OUT NOCOPY VARCHAR2,
301 p_segment3 IN OUT NOCOPY VARCHAR2,
302 p_segment4 IN OUT NOCOPY VARCHAR2,
303 p_segment5 IN OUT NOCOPY VARCHAR2,
304 p_segment6 IN OUT NOCOPY VARCHAR2,
305 p_segment7 IN OUT NOCOPY VARCHAR2,
306 p_segment8 IN OUT NOCOPY VARCHAR2,
307 p_segment9 IN OUT NOCOPY VARCHAR2,
308 p_segment10 IN OUT NOCOPY VARCHAR2,
309 p_segment11 IN OUT NOCOPY VARCHAR2,
310 p_segment12 IN OUT NOCOPY VARCHAR2,
311 p_segment13 IN OUT NOCOPY VARCHAR2,
312 p_segment14 IN OUT NOCOPY VARCHAR2,
313 p_segment15 IN OUT NOCOPY VARCHAR2,
314 p_segment16 IN OUT NOCOPY VARCHAR2,
315 p_segment17 IN OUT NOCOPY VARCHAR2,
316 p_segment18 IN OUT NOCOPY VARCHAR2,
317 p_segment19 IN OUT NOCOPY VARCHAR2,
318 p_segment20 IN OUT NOCOPY VARCHAR2,
319 p_segment21 IN OUT NOCOPY VARCHAR2,
320 p_segment22 IN OUT NOCOPY VARCHAR2,
321 p_segment23 IN OUT NOCOPY VARCHAR2,
322 p_segment24 IN OUT NOCOPY VARCHAR2,
323 p_segment25 IN OUT NOCOPY VARCHAR2,
324 p_segment26 IN OUT NOCOPY VARCHAR2,
325 p_segment27 IN OUT NOCOPY VARCHAR2,
326 p_segment28 IN OUT NOCOPY VARCHAR2,
327 p_segment29 IN OUT NOCOPY VARCHAR2,
328 p_segment30 IN OUT NOCOPY VARCHAR2)
329 IS
330
331 l_proc VARCHAR2(72) := g_package||'costing_kff_null_default_segs';
332
333 PROCEDURE check_seg_value(p_segment IN VARCHAR2,
334 p_segment_value IN OUT NOCOPY VARCHAR2)
335 IS
336
337 l_proc VARCHAR2(72) := g_package||'check_seg_value';
338
339 BEGIN
340 Hr_Utility.Set_Location('Entering: '|| l_proc, 5);
341
342 if p_segment_value is not null then
343
344 Hr_Utility.Set_Location(l_proc, 10);
345
346 SELECT decode(attribute_value, 'Y', p_segment_value, null)
347 INTO p_segment_value
348 FROM FND_SEGMENT_ATTRIBUTE_VALUES sa1
349 WHERE sa1.id_flex_num = p_cost_id_flex_num
350 AND sa1.id_flex_code = 'COST'
354
351 AND sa1.application_id = 801
352 AND sa1.application_column_name = p_segment
353 AND sa1.segment_attribute_type = p_level;
355 Hr_Utility.Set_Location(l_proc, 15);
356
357 end if;
358
359 Hr_Utility.Set_Location('Leaving: ' || l_proc, 20);
360
361 END check_seg_value;
362
363
364 BEGIN
365 Hr_Utility.Set_Location('Entering:' || l_proc, 5);
366
367 if (p_level <> 'BALANCING' and
368 p_level <> 'PAYROLL') then
369
370 Hr_Utility.Set_Location(l_proc, 10);
371
372 check_seg_value('SEGMENT1', p_segment1);
373 check_seg_value('SEGMENT2', p_segment2);
374 check_seg_value('SEGMENT3', p_segment3);
375 check_seg_value('SEGMENT4', p_segment4);
376 check_seg_value('SEGMENT5', p_segment5);
377 check_seg_value('SEGMENT6', p_segment6);
378 check_seg_value('SEGMENT7', p_segment7);
379 check_seg_value('SEGMENT8', p_segment8);
380 check_seg_value('SEGMENT9', p_segment9);
381 check_seg_value('SEGMENT9', p_segment9);
382 check_seg_value('SEGMENT10', p_segment10);
383 check_seg_value('SEGMENT11', p_segment11);
384 check_seg_value('SEGMENT12', p_segment12);
385 check_seg_value('SEGMENT13', p_segment13);
386 check_seg_value('SEGMENT14', p_segment14);
387 check_seg_value('SEGMENT15', p_segment15);
388 check_seg_value('SEGMENT16', p_segment16);
389 check_seg_value('SEGMENT17', p_segment17);
390 check_seg_value('SEGMENT18', p_segment18);
391 check_seg_value('SEGMENT19', p_segment19);
392 check_seg_value('SEGMENT20', p_segment20);
393 check_seg_value('SEGMENT21', p_segment21);
394 check_seg_value('SEGMENT22', p_segment22);
395 check_seg_value('SEGMENT23', p_segment23);
396 check_seg_value('SEGMENT24', p_segment24);
397 check_seg_value('SEGMENT25', p_segment25);
398 check_seg_value('SEGMENT26', p_segment26);
399 check_seg_value('SEGMENT27', p_segment27);
400 check_seg_value('SEGMENT28', p_segment28);
401 check_seg_value('SEGMENT29', p_segment29);
402 check_seg_value('SEGMENT30', p_segment30);
403
404 end if;
405
406 Hr_Utility.Set_Location('Leaving:' || l_proc, 20);
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 Raise;
411 END costing_kff_null_default_segs;
412
413
414 -- ----------------------------------------------------------------------------
415 -- |---------------------< cost_keyflex_segment_defined >---------------------|
416 -- ----------------------------------------------------------------------------
417 -- {Start Of Comments}
418 --
419 -- Description:
420 -- Returns 'Y' if the segment specified by p_segment_name is enabled
421 -- and displayed for the cost allocation structure defined by
422 -- p_cost_id_flex_num
423 -- Returns 'N' otherwise
424 --
425 -- Pre Conditions:
426 -- none
427 --
428 -- In Arguments:
429 -- p_cost_id_flex_num - id of the cost allocation structure
430 -- p_segment_name - should be one of SEGMENT1, SEGMENT2 .. SEGMENT30
431 --
432 -- Access Status:
433 -- public
434 --
435 -- {End Of Comments}
436 --
437 function cost_keyflex_segment_defined (
438 p_cost_id_flex_num in number,
439 p_segment_name in varchar2) return varchar2
440 is
441 --
442 cursor csr_segment_exists is
443 select 'Y'
444 from fnd_id_flex_segments
445 where application_id = 801
446 and id_flex_num = p_cost_id_flex_num
447 and id_flex_code = 'COST'
448 and application_column_name = p_segment_name
449 and enabled_flag = 'Y'
450 and display_flag = 'Y';
451 --
452 v_segment_exists varchar2(5) := 'N';
453 --
454 begin
455 --
456 open csr_segment_exists;
457 fetch csr_segment_exists into v_segment_exists;
458 close csr_segment_exists;
459 --
460 return v_segment_exists;
461 --
462 end cost_keyflex_segment_defined;
463
464
465 -- ----------------------------------------------------------------------------
466 -- |--------------------< get_cost_keyflex_segment_value >--------------------|
467 -- ----------------------------------------------------------------------------
468 -- {Start Of Comments}
469 --
470 -- Description:
471 -- Returns the appropriate value for the specified segment, cost
472 -- allocation structure, assignment and element entry. If the segment is
473 -- validated by a value set, returns the 'decoded' value, otherwise returns
474 -- the 'plain' value as stored in pay_people_groups.
475 --
479 -- In Arguments:
476 -- Pre Conditions:
477 -- none
478 --
480 -- p_segment_name - should be one of SEGMENT1, SEGMENT2 .. SEGMENT30
481 -- p_value_set_id - value set id used by the segment
482 -- p_value_set_application_id - value set application id used by the segment
483 -- p_assignment_id - assignment id
484 -- p_cost_allocation_id - id of the cost allocation
485 -- p_element_entry_id - element entry id
486 -- p_effective_date - effective date
487 --
488 -- Access Status:
489 -- public
490 --
491 -- {End Of Comments}
492 --
493 function get_cost_keyflex_segment_value (
494 p_segment_name in varchar2,
495 p_value_set_id in number,
496 p_value_set_application_id in number,
497 p_assignment_id in number,
498 p_cost_allocation_id in number,
499 p_element_entry_id in number,
500 p_effective_date in date) return varchar2
501 is
502 --
503 cursor csr_vset_display_value (
504 p_value_set_id in number,
505 p_value_set_application_id in number,
506 p_value in varchar2) is
507 select fnd_flex_val_util.to_display_value (
508 p_value,
509 format_type,
510 flex_value_set_name,
511 maximum_size,
512 number_precision,
513 alphanumeric_allowed_flag,
514 uppercase_only_flag,
515 'N',
516 maximum_value,
517 minimum_value ) display_value
518 from fnd_flex_value_sets
519 where flex_value_set_id = p_value_set_id;
520 --
521 cursor csr_cost_keyflex_segment_value (
522 p_segment_name in varchar2,
523 p_assignment_id in number,
524 p_cost_allocation_id in number,
525 p_element_entry_id in number,
526 p_effective_date in date) is
527 select decode (
528 p_segment_name
529 , 'SEGMENT1', nvl(E.segment1, nvl(A.segment1, nvl(O.segment1, nvl(L.segment1, P.segment1))))
530 , 'SEGMENT2', nvl(E.segment2, nvl(A.segment2, nvl(O.segment2, nvl(L.segment2, P.segment2))))
531 , 'SEGMENT3', nvl(E.segment3, nvl(A.segment3, nvl(O.segment3, nvl(L.segment3, P.segment3))))
532 , 'SEGMENT4', nvl(E.segment4, nvl(A.segment4, nvl(O.segment4, nvl(L.segment4, P.segment4))))
533 , 'SEGMENT5', nvl(E.segment5, nvl(A.segment5, nvl(O.segment5, nvl(L.segment5, P.segment5))))
534 , 'SEGMENT6', nvl(E.segment6, nvl(A.segment6, nvl(O.segment6, nvl(L.segment6, P.segment6))))
535 , 'SEGMENT7', nvl(E.segment7, nvl(A.segment7, nvl(O.segment7, nvl(L.segment7, P.segment7))))
536 , 'SEGMENT8', nvl(E.segment8, nvl(A.segment8, nvl(O.segment8, nvl(L.segment8, P.segment8))))
537 , 'SEGMENT9', nvl(E.segment9, nvl(A.segment9, nvl(O.segment9, nvl(L.segment9, P.segment9))))
538 , 'SEGMENT10', nvl(E.segment10, nvl(A.segment10, nvl(O.segment10, nvl(L.segment10, P.segment10))))
539 , 'SEGMENT11', nvl(E.segment11, nvl(A.segment11, nvl(O.segment11, nvl(L.segment11, P.segment11))))
540 , 'SEGMENT12', nvl(E.segment12, nvl(A.segment12, nvl(O.segment12, nvl(L.segment12, P.segment12))))
541 , 'SEGMENT13', nvl(E.segment13, nvl(A.segment13, nvl(O.segment13, nvl(L.segment13, P.segment13))))
542 , 'SEGMENT14', nvl(E.segment14, nvl(A.segment14, nvl(O.segment14, nvl(L.segment14, P.segment14))))
543 , 'SEGMENT15', nvl(E.segment15, nvl(A.segment15, nvl(O.segment15, nvl(L.segment15, P.segment15))))
544 , 'SEGMENT16', nvl(E.segment16, nvl(A.segment16, nvl(O.segment16, nvl(L.segment16, P.segment16))))
545 , 'SEGMENT17', nvl(E.segment17, nvl(A.segment17, nvl(O.segment17, nvl(L.segment17, P.segment17))))
546 , 'SEGMENT18', nvl(E.segment18, nvl(A.segment18, nvl(O.segment18, nvl(L.segment18, P.segment18))))
547 , 'SEGMENT19', nvl(E.segment19, nvl(A.segment19, nvl(O.segment19, nvl(L.segment19, P.segment19))))
548 , 'SEGMENT20', nvl(E.segment20, nvl(A.segment20, nvl(O.segment20, nvl(L.segment20, P.segment20))))
549 , 'SEGMENT21', nvl(E.segment21, nvl(A.segment21, nvl(O.segment21, nvl(L.segment21, P.segment21))))
550 , 'SEGMENT22', nvl(E.segment22, nvl(A.segment22, nvl(O.segment22, nvl(L.segment22, P.segment22))))
551 , 'SEGMENT23', nvl(E.segment23, nvl(A.segment23, nvl(O.segment23, nvl(L.segment23, P.segment23))))
552 , 'SEGMENT24', nvl(E.segment24, nvl(A.segment24, nvl(O.segment24, nvl(L.segment24, P.segment24))))
553 , 'SEGMENT25', nvl(E.segment25, nvl(A.segment25, nvl(O.segment25, nvl(L.segment25, P.segment25))))
554 , 'SEGMENT26', nvl(E.segment26, nvl(A.segment26, nvl(O.segment26, nvl(L.segment26, P.segment26))))
555 , 'SEGMENT27', nvl(E.segment27, nvl(A.segment27, nvl(O.segment27, nvl(L.segment27, P.segment27))))
556 , 'SEGMENT28', nvl(E.segment28, nvl(A.segment28, nvl(O.segment28, nvl(L.segment28, P.segment28))))
557 , 'SEGMENT29', nvl(E.segment29, nvl(A.segment29, nvl(O.segment29, nvl(L.segment29, P.segment29))))
558 , 'SEGMENT30', nvl(E.segment30, nvl(A.segment30, nvl(O.segment30, nvl(L.segment30, P.segment30))))
559 , null ) segment_value
560 from pay_cost_allocation_keyflex E,
561 pay_cost_allocation_keyflex A,
562 pay_cost_allocation_keyflex O,
563 pay_cost_allocation_keyflex L,
564 pay_cost_allocation_keyflex P,
565 pay_element_links_f EL,
566 hr_all_organization_units OU,
567 pay_payrolls_f PP,
568 pay_element_entries_f EE,
572 ASG1.organization_id,
569 (
570 select ASG1.assignment_id,
571 ASG1.payroll_id,
573 CA.cost_allocation_keyflex_id
574 from per_all_assignments_f ASG1,
575 pay_cost_allocations_f CA
576 where ASG1.assignment_id = CA.assignment_id (+)
577 and ASG1.assignment_id = p_assignment_id
578 and nvl( CA.cost_allocation_id, -1) = nvl( p_cost_allocation_id, -1)
579 and p_effective_date between ASG1.effective_start_date
580 and ASG1.effective_end_date
581 and p_effective_date between nvl(CA.effective_start_date,p_effective_date)
582 and nvl(CA.effective_end_date,p_effective_date)
583 union all
584 select to_number(ASG3.assignment_id) assignment_id,
585 to_number(ASG3.payroll_id) payroll_id,
586 to_number(ASG3.organization_id) organization_id,
587 to_number(null) cost_allocation_keyflex_id
588 from per_all_assignments_f ASG3
589 where ASG3.assignment_id = p_assignment_id
590 and p_effective_date between ASG3.effective_start_date and ASG3.effective_end_date
591 and not exists (
592 select 'X'
593 from pay_cost_allocations_f CA2
594 where CA2.assignment_id = ASG3.assignment_id
595 and p_effective_date between CA2.effective_start_date and CA2.effective_end_date
596 )
597 ) ASG2
598 where ASG2.assignment_id = EE.assignment_id
599 and EE.element_link_id = EL.element_link_id
600 and EE.element_entry_id = p_element_entry_id
601 and ASG2.payroll_id = PP.payroll_id
602 and ASG2.organization_id = OU.organization_id
603 and EE.cost_allocation_keyflex_id = E.cost_allocation_keyflex_id (+)
604 and ASG2.cost_allocation_keyflex_id = A.cost_allocation_keyflex_id (+)
605 and OU.cost_allocation_keyflex_id = O.cost_allocation_keyflex_id (+)
606 and EL.cost_allocation_keyflex_id = L.cost_allocation_keyflex_id (+)
607 and PP.cost_allocation_keyflex_id = P.cost_allocation_keyflex_id (+)
608 and p_effective_date between EE.effective_start_date and EE.effective_end_date
609 and p_effective_date between EL.effective_start_date and EL.effective_end_date
610 and p_effective_date between PP.effective_start_date and PP.effective_end_date
611 and EL.costable_type = 'C'
612 union all
613 select decode (
614 p_segment_name
615 , 'SEGMENT1', nvl(E.segment1, nvl(L.segment1, P.segment1))
616 , 'SEGMENT2', nvl(E.segment2, nvl(L.segment2, P.segment2))
617 , 'SEGMENT3', nvl(E.segment3, nvl(L.segment3, P.segment3))
618 , 'SEGMENT4', nvl(E.segment4, nvl(L.segment4, P.segment4))
619 , 'SEGMENT5', nvl(E.segment5, nvl(L.segment5, P.segment5))
620 , 'SEGMENT6', nvl(E.segment6, nvl(L.segment6, P.segment6))
621 , 'SEGMENT7', nvl(E.segment7, nvl(L.segment7, P.segment7))
622 , 'SEGMENT8', nvl(E.segment8, nvl(L.segment8, P.segment8))
623 , 'SEGMENT9', nvl(E.segment9, nvl(L.segment9, P.segment9))
624 , 'SEGMENT10', nvl(E.segment10, nvl(L.segment10, P.segment10))
625 , 'SEGMENT11', nvl(E.segment11, nvl(L.segment11, P.segment11))
626 , 'SEGMENT12', nvl(E.segment12, nvl(L.segment12, P.segment12))
627 , 'SEGMENT13', nvl(E.segment13, nvl(L.segment13, P.segment13))
628 , 'SEGMENT14', nvl(E.segment14, nvl(L.segment14, P.segment14))
629 , 'SEGMENT15', nvl(E.segment15, nvl(L.segment15, P.segment15))
630 , 'SEGMENT16', nvl(E.segment16, nvl(L.segment16, P.segment16))
631 , 'SEGMENT17', nvl(E.segment17, nvl(L.segment17, P.segment17))
632 , 'SEGMENT18', nvl(E.segment18, nvl(L.segment18, P.segment18))
633 , 'SEGMENT19', nvl(E.segment19, nvl(L.segment19, P.segment19))
634 , 'SEGMENT20', nvl(E.segment20, nvl(L.segment20, P.segment20))
635 , 'SEGMENT21', nvl(E.segment21, nvl(L.segment21, P.segment21))
636 , 'SEGMENT22', nvl(E.segment22, nvl(L.segment22, P.segment22))
637 , 'SEGMENT23', nvl(E.segment23, nvl(L.segment23, P.segment23))
638 , 'SEGMENT24', nvl(E.segment24, nvl(L.segment24, P.segment24))
639 , 'SEGMENT25', nvl(E.segment25, nvl(L.segment25, P.segment25))
640 , 'SEGMENT26', nvl(E.segment26, nvl(L.segment26, P.segment26))
641 , 'SEGMENT27', nvl(E.segment27, nvl(L.segment27, P.segment27))
642 , 'SEGMENT28', nvl(E.segment28, nvl(L.segment28, P.segment28))
643 , 'SEGMENT29', nvl(E.segment29, nvl(L.segment29, P.segment29))
644 , 'SEGMENT30', nvl(E.segment30, nvl(L.segment30, P.segment30))
645 , null) segment_value
646 from pay_cost_allocation_keyflex E,
647 pay_cost_allocation_keyflex L,
648 pay_cost_allocation_keyflex P,
649 pay_element_links_f EL,
650 pay_payrolls_f PP,
651 pay_element_entries_f EE,
652 per_all_assignments_f ASG
653 where ASG.assignment_id = EE.assignment_id
654 and ASG.assignment_id = p_assignment_id
655 and EE.element_link_id = EL.element_link_id
659 and EL.cost_allocation_keyflex_id = L.cost_allocation_keyflex_id (+)
656 and EE.element_entry_id = p_element_entry_id
657 and ASG.payroll_id = PP.payroll_id
658 and EE.cost_allocation_keyflex_id = E.cost_allocation_keyflex_id (+)
660 and PP.cost_allocation_keyflex_id = P.cost_allocation_keyflex_id (+)
661 and p_effective_date between ASG.effective_start_date and ASG.effective_end_date
662 and p_effective_date between EE.effective_start_date and EE.effective_end_date
663 and p_effective_date between EL.effective_start_date and EL.effective_end_date
664 and p_effective_date between PP.effective_start_date and PP.effective_end_date
665 and EL.costable_type = 'F';
666 --
667 v_segment_value varchar2(2000);
668 v_display_value varchar2(2000);
669 --
670 begin
671 --
672 open csr_cost_keyflex_segment_value (
673 p_segment_name ,
674 p_assignment_id ,
675 p_cost_allocation_id,
676 p_element_entry_id ,
677 p_effective_date );
678 fetch csr_cost_keyflex_segment_value into v_segment_value;
679 close csr_cost_keyflex_segment_value;
680 --
681 open csr_vset_display_value (
682 p_value_set_id,
683 p_value_set_application_id,
684 v_segment_value );
685 fetch csr_vset_display_value into v_display_value;
686 close csr_vset_display_value;
687 --
688 -- return nvl(v_display_value, v_segment_value);
689 return v_segment_value || ' - ' || v_display_value;
690 --
691 end;
692
693 -- ----------------------------------------------------------------------------
694 -- |-----------------------< validate_costing_keyflex >-----------------------|
695 -- ----------------------------------------------------------------------------
696 -- {Start Of Comments}
697 --
698 -- Pre Conditions:
699 -- none
700 --
701 -- Description:
702 -- Performs validation of the cost allocation specified by
703 -- p_concat_segments. A cost allocation entry is created in
704 -- pay_cost_allocation_keyflex if the combination is valid and doesn't
705 -- already exist. Returns the id of the combination if it is valid via
706 -- p_cost_allocation_keyflex_id, otherwise returns error information via
707 -- p_error_segment_num, p_error_segment_name and p_error_message.
708 --
709 -- In Arguments:
710 -- p_cost_id_flex_num - id of the cost allocation structure
711 -- p_concat_segments - the concatenated segments to be validated
712 -- p_validation_date - the validation date
713 -- p_resp_appl_id - application id of the responsibility performing the
714 -- validation
715 -- p_resp_id - id of the responsibility performing the validation.
716 -- p_user_id - id of the user who is performing the validation
717 --
718 -- Out Arguments:
719 -- p_cost_allocation_keyflex_id the id of the valid combination.
720 -- p_error_segment_num the number of the segment causing error in case of
721 -- an invalid combination (may be null).
722 -- p_error_segment_name the name of the segment causing error in case of
723 -- an invalid combination (may be null).
724 -- p_error_message the error message in case of an invalid combination
725 -- (may be null).
726 --
727 -- Access Status:
728 -- public
729 --
730 -- {End Of Comments}
731 --
732 procedure validate_costing_keyflex (
733 p_cost_id_flex_num in number
734 , p_concat_segments in varchar2
735 , p_validation_date in date
736 , p_resp_appl_id in number
737 , p_resp_id in number
738 , p_user_id in number
739 , p_cost_allocation_keyflex_id out nocopy number
740 , p_error_segment_num out nocopy number
741 , p_error_segment_name out nocopy varchar2
742 , p_application_col_name out nocopy varchar2
743 , p_error_message out nocopy varchar2
744 )
745 is
746 --
747 v_valid boolean;
748 v_flexfield fnd_flex_key_api.flexfield_type;
749 v_structure fnd_flex_key_api.structure_type;
750 v_segment fnd_flex_key_api.segment_type;
751 v_segment_list fnd_flex_key_api.segment_list;
752 v_nsegments number;
753 --
754 begin
755 --
756 v_valid := fnd_flex_keyval.validate_segs (
757 operation => 'CREATE_COMBINATION'
758 , appl_short_name => 'PAY'
759 , key_flex_code => 'COST'
760 , structure_number => p_cost_id_flex_num
761 , concat_segments => p_concat_segments
762 , values_or_ids => 'I'
763 , validation_date => p_validation_date
764 , resp_appl_id => p_resp_appl_id
765 , resp_id => p_resp_id
766 , user_id => p_user_id);
767 --
768 if v_valid then
769 --
770 p_cost_allocation_keyflex_id := fnd_flex_keyval.combination_id;
771 p_error_segment_num := null;
772 p_error_segment_name := null;
773 p_error_message := null;
774 --
775 -- update the cost allocation keyflex table with the concatenated
776 -- segments
777 --
778 update pay_cost_allocation_keyflex
779 set concatenated_segments = p_concat_segments
780 where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
781 commit;
782 --
783 else
784 --
785 p_cost_allocation_keyflex_id := -1;
786 p_error_segment_num := fnd_flex_keyval.error_segment;
787 p_error_message := fnd_flex_keyval.error_message;
788 --
789 if p_error_segment_num is not null then
790 --
791 -- Get the name of the error segment
792 --
793 fnd_flex_key_api.set_session_mode('customer_data');
797 flex_code => 'COST'
794 --
795 v_flexfield := fnd_flex_key_api.find_flexfield (
796 appl_short_name => 'PAY',
798 );
799 --
800 if p_error_segment_num = 0 then
801 --
802 p_application_col_name := v_flexfield.structure_column;
803 p_error_segment_name := null;
804 --
805 else
806 --
807 v_structure := fnd_flex_key_api.find_structure (
808 flexfield => v_flexfield,
809 structure_number => p_cost_id_flex_num
810 );
811 --
812 fnd_flex_key_api.get_segments (
813 flexfield => v_flexfield,
814 structure => v_structure,
815 enabled_only => true,
816 nsegments => v_nsegments,
817 segments => v_segment_list
818 );
819 --
820 p_error_segment_name := v_segment_list(p_error_segment_num);
821 --
822 v_segment := fnd_flex_key_api.find_segment (
823 flexfield => v_flexfield,
824 structure => v_structure,
825 segment_name => p_error_segment_name
826 );
827 --
828 p_application_col_name := v_segment.column_name;
829 p_error_segment_name := v_segment.window_prompt;
830 --
831 end if;
832 --
833 end if;
834 --
835 end if;
836 --
837 exception
838 --
839 when others then
840 p_cost_allocation_keyflex_id := -1;
841 --
842 end validate_costing_keyflex;
843
844 -- ----------------------------------------------------------------------------
845 -- |----------------------< get_cost_keyflex_structure >----------------------|
846 -- ----------------------------------------------------------------------------
847 -- {Start Of Comments}
848 --
849 -- Description:
850 -- Returns the code of the cost allocation keyflex structure denoted by
851 -- p_cost_id_flex_num.
852 --
853 -- Pre Conditions:
854 -- none
855 --
856 -- In Arguments:
857 -- p_cost_id_flex_num - the id of the cost allocation keyflex structure
858 --
859 -- Access Status:
860 -- public
861 --
862 -- {End Of Comments}
863 --
864 function get_cost_keyflex_structure (
865 p_cost_id_flex_num in number
866 ) return varchar2
867 is
868 --
869 v_flexfield fnd_flex_key_api.flexfield_type;
870 v_structure fnd_flex_key_api.structure_type;
871 --
872 begin
873 --
874 fnd_flex_key_api.set_session_mode('customer_data');
875 --
876 v_flexfield := fnd_flex_key_api.find_flexfield (
877 appl_short_name => 'PAY',
878 flex_code => 'COST'
879 );
880 --
881 v_structure := fnd_flex_key_api.find_structure (
882 flexfield => v_flexfield,
883 structure_number => p_cost_id_flex_num
884 );
885 --
886 return v_structure.structure_code;
887 --
888 end get_cost_keyflex_structure;
889
890 END pay_costing_kff_util_pkg;
[Home] [Help]