PACKAGE BODY: APPS.PER_FR_REPORT_UTILITIES

Source

1 package body per_fr_report_utilities as
2 /* $Header: pefrutil.pkb 120.2 2005/10/03 02:36 sbairagi noship $ */
3 function get_job_names (p_job_id in number
4                        , p_job_definition_id in number
5 		       , p_report_name in varchar2 default null)
6 		       return varchar2 is
7 Type segment_values is ref cursor;
8 csr_segment_values segment_values;
9   cursor get_segments is
10   select fsav.application_column_name,
11          fifst.concatenated_segment_delimiter delimeter
12     from fnd_segment_attribute_values fsav,
13          fnd_id_flex_segments fifs,
14          per_job_definitions pjd,
15          per_jobs pj,
16          fnd_id_flex_structures fifst
17    where pj.job_id = p_job_id
18      and pj.job_definition_id = p_job_definition_id
19      and pjd.job_definition_id = pj.job_definition_id
20      and fsav.id_flex_code = 'JOB'
21      and fsav.id_flex_num = pjd.id_flex_num
22      and fsav.attribute_value = 'Y'
23      and fsav.segment_attribute_type = 'FR_REPORTING'
24      and fifs.id_flex_code = fsav.id_flex_code
25      and fifs.id_flex_num = fsav.id_flex_num
26      and fifs.application_id = fsav.application_id
27      and fifs.application_column_name = fsav.application_column_name
28      and fifst.id_flex_code = fsav.id_flex_code
29      and fifst.id_flex_num = pjd.id_flex_num
30      and fifst.application_id = fsav.application_id
31 order by fifs.segment_num;
32 l_column_name   varchar2(200);
33 l_select        varchar2(50);
34 l_resultant     per_jobs_tl.name%type;
35 l_sql_statement varchar2(1000);
36 l_delimeter     fnd_id_flex_structures.concatenated_segment_delimiter%type;
37 l_proc          varchar2(400);
38 BEGIN
39 l_proc := 'pay_fr_report_utilities.get_job_names';
40 hr_utility.set_location('Entering '||l_proc, 10);
41 l_column_name   := NULL;
42 l_select        := NULL;
43 hr_utility.set_location('Obtaining segment names '||l_proc, 20);
44 open get_segments;
45 loop
46    fetch get_segments into l_select, l_delimeter;
47    exit when get_segments%notfound;
48    if (p_report_name is not null and p_report_name = 'DADS') then
49       l_delimeter := '.';
50    end if;
51    l_column_name := l_column_name ||
52                     'nvl(' || l_select ||', ''_NULL_'')'||
53                     ' || '''|| l_delimeter||''' ||';
54 end loop;
55 hr_utility.set_location('Obtaining segment values '||l_proc, 30);
56 if l_column_name is not null then
57    l_column_name := substr(l_column_name, 1, length(l_column_name) - 10);
58    open csr_segment_values for 'select '||l_column_name||
59                                 ' from per_job_definitions
60                                  where job_definition_id = '||p_job_definition_id;
61    fetch csr_segment_values into l_resultant;
62    close csr_segment_values;
63    l_resultant := replace(l_resultant, l_delimeter||'_NULL_', NULL);
64    -- to replace, when _NULL_ comes at the first segment
65    l_resultant := replace(l_resultant, '_NULL_'||l_delimeter, NULL);
66    /* 4428595 Commented because, substr is used only for RUP and it is already
67               present in rdf file */
68 --   return substr(l_resultant, 1, 20);
69    return l_resultant;
70 else
71    hr_utility.set_location('No segment names '||l_proc, 40);
72    return null;
73 end if;
74 hr_utility.set_location('Leaving '||l_proc, 50);
75 exception
76 when others then
77 hr_utility.set_location('Errored out in '||l_proc, 50);
78 hr_utility.set_location('Error is '||sqlerrm, 60);
79 end get_job_names;
80 end per_fr_report_utilities;