eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_SE_SOE

Source


1 PACKAGE BODY pay_se_soe as
2 /* $Header: pysesoer.pkb 120.0 2005/05/29 08:37:54 appldev noship $ */
3 --
4 l_sql long;
5 g_debug boolean := hr_utility.debug_enabled;
6 g_max_action number;
7 g_min_action number;
8 
9 --
10 /* ---------------------------------------------------------------------
11 Function : getElements
12 
13 Text
14 ------------------------------------------------------------------------ */
15 function getElements(p_assignment_action_id number
16                     ,p_element_set_name varchar2) return long is
17 begin
18 --
19    --
20    if g_debug then
21      hr_utility.set_location('Entering pay_soe_glb.getElements', 10);
22    end if;
23    --
24 l_sql :=
25 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
26 ,        nvl(ettl.reporting_name,ettl.element_name) COL02
27 ,        hoi.org_information2 COL03
28 ,        to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
29 ,        decode(count(*),1,''1'',''2'') COL17 -- destination indicator
30 ,        decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
31 from pay_assignment_actions aa
32 ,    pay_run_results rr
33 ,    pay_run_result_values rrv
34 ,    pay_input_values_f iv
35 ,    pay_input_values_f_tl ivtl
36 ,    pay_element_types_f et
37 ,    pay_element_types_f_tl ettl
38 ,    pay_element_set_members esm
39 ,    pay_element_sets es
40 ,    hr_organization_information hoi
41 where aa.assignment_action_id :action_clause
42 and   aa.assignment_action_id = rr.assignment_action_id
43 and   rr.status in (''P'',''PA'')
44 and   rr.run_result_id = rrv.run_result_id
45 and   rr.element_type_id = et.element_type_id
46 and   :effective_date between
47        et.effective_start_date and et.effective_end_date
48 and   et.element_type_id = ettl.element_type_id
49 and   rrv.input_value_id = iv.input_value_id
50 and   iv.name = ''Pay Value''
51 and   :effective_date between
52        iv.effective_start_date and iv.effective_end_date
53 and   iv.input_value_id = ivtl.input_value_id
54 and   ettl.language = userenv(''LANG'')
55 and   ivtl.language = userenv(''LANG'')
56 and   et.element_type_id = esm.element_type_id
57 and   esm.element_set_id = es.element_set_id
58 and ( es.BUSINESS_GROUP_ID IS NULL
59    OR es.BUSINESS_GROUP_ID = :business_group_id )
60 AND ( es.LEGISLATION_CODE IS NULL
61    OR es.LEGISLATION_CODE = '':legislation_code'' )
62 and   es.element_set_name = '''|| p_element_set_name ||'''
63 and hoi.organization_id = :business_group_id
64 and hoi.org_information_context (+)=''SE_SOE_ELEMENT_ADD_DETAILS''
65 and nvl(hoi.org_information1,et.element_type_id) = et.element_type_id
66 group by nvl(ettl.reporting_name,ettl.element_name)
67 , ettl.reporting_name, hoi.org_information2
68 ,nvl(ettl.reporting_name,et.element_type_id)
69 order by nvl(ettl.reporting_name,ettl.element_name)';
70 --
71    --
72    if g_debug then
73      hr_utility.set_location('Leaving pay_soe_glb.getElements', 20);
74    end if;
75    --
76 return l_sql;
77 --
78 end getElements;
79 --
80 /* ---------------------------------------------------------------------
81 Function : getInformation
82 
83 Text
84 ------------------------------------------------------------------------ */
85 function getInformation(p_assignment_action_id number
86                        ,p_element_set_name varchar2) return long is
87 begin
88 --
89 hr_utility.trace('in getInformation' || p_element_set_name || p_assignment_action_id);
90 l_sql :=
91 'select  distinct ettl.element_name COL01
92 ,        nvl(ettl.reporting_name, ettl.element_name) COL02      -- for BUG 3880887
93 ,        ivtl.name COL03
94 ,        rrv.result_value COL04
95 ,        1  COL05  -- to indicate that we should drilldown directly to run_result_values
96 ,        hoi.org_information2 COL06
97 ,        rr.run_result_id COL18
98 from pay_assignment_actions aa
99 ,    pay_run_results rr
100 ,    pay_run_result_values rrv
101 ,    pay_input_values_f iv
102 ,    pay_input_values_f_tl ivtl
103 ,    pay_element_types_f et
104 ,    pay_element_types_f_tl ettl
105 ,    pay_element_set_members esm
106 ,    pay_element_sets es
107 ,    hr_organization_information oi
108 ,    hr_organization_information hoi
109 where aa.assignment_action_id :action_clause
110 and   aa.assignment_action_id = rr.assignment_action_id
111 and   rr.status in (''P'',''PA'')
112 and   rr.run_result_id = rrv.run_result_id
113 and   rr.element_type_id = et.element_type_id
114 and   rrv.input_value_id = iv.input_value_id
115 and   to_char(iv.input_value_id) = oi.org_information3
116 and   iv.input_value_id = ivtl.input_value_id
117 and   ivtl.language = userenv(''LANG'')
118 and   :effective_date between
119        iv.effective_start_date and iv.effective_end_date
120 and   to_char(et.element_type_id) = oi.org_information2
121 and   :effective_date between
122        et.effective_start_date and et.effective_end_date
123 and   et.element_type_id = ettl.element_type_id
124 and   ettl.language = userenv(''LANG'')
125 and   et.element_type_id = esm.element_type_id
126 and ( esm.BUSINESS_GROUP_ID IS NULL
127    OR esm.BUSINESS_GROUP_ID = :business_group_id)
128 AND ( esm.LEGISLATION_CODE IS NULL
129    OR esm.LEGISLATION_CODE = '':legislation_code'')
130 and   esm.element_set_id = es.element_set_id
131 and ( es.BUSINESS_GROUP_ID IS NULL
132    OR es.BUSINESS_GROUP_ID = :business_group_id)
133 AND ( es.LEGISLATION_CODE IS NULL
134    OR es.LEGISLATION_CODE =  '':legislation_code'' )
135 and   es.element_set_name = ''' || p_element_set_name || '''
136 --
137 and   oi.org_information1 = ''ELEMENT''
138 --
139 and   oi.org_information_context = ''Business Group:SOE Detail''
140 and   oi.organization_id = :business_group_id
141 and   hoi.organization_id = :business_group_id
142 and   hoi.org_information_context (+)=''SE_SOE_ELEMENT_ADD_DETAILS''
143 and   nvl(hoi.org_information1,et.element_type_id) = et.element_type_id';
144 --
145 return l_sql;
146 end getInformation;
147 --
148 --
149 /* ---------------------------------------------------------------------
150 Function : Elements1
151 
152 Text
153 ------------------------------------------------------------------------ */
154 function Elements1(p_assignment_action_id number) return long is
155 begin
156   hr_utility.trace('Entering elements1');
157   return getElements(p_assignment_action_id
158                     ,pay_soe_util.getConfig('ELEMENTS1'));
159   hr_utility.trace('Leaving Elements1');
160 end Elements1;
161 --
162 /* ---------------------------------------------------------------------
163 Function : Elements2
164 
165 Text
166 ------------------------------------------------------------------------ */
167 function Elements2(p_assignment_action_id number) return long is
168 begin
169   return getElements(p_assignment_action_id
170                     ,pay_soe_util.getConfig('ELEMENTS2'));
171 end Elements2;
172 --
173 
174 /* ---------------------------------------------------------------------
175 Function : Information1
176 
177 Text
178 ------------------------------------------------------------------------ */
179 function Information1(p_assignment_action_id number) return long is
180 begin
181   hr_utility.trace('in Information1');
182   return getInformation(p_assignment_action_id
183                     ,pay_soe_util.getConfig('INFORMATION1'));
184 end Information1;
185 --
186 
187 end pay_se_soe;