[Home] [Help]
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;