1 PACKAGE BODY PAY_PAYROLL_EXTRACT as
2 /* $Header: payextract.pkb 120.3 2007/02/23 13:54:55 jdevasah noship $ */
3
4 /******************************************************************
5 ** private package global declarations
6 ******************************************************************/
7
8 g_package VARCHAR2(50) := 'pay_payroll_extract';
9 g_debug boolean := FALSE;
10
11 -- Enter procedure, function bodies as shown below
12
13 --FUNCTION get_xmldoc_clob
14 -- ( p_payroll_action_id IN varchar2
15 -- ) RETURN CLOB IS
16
17 FUNCTION get_xmldoc_clob
18 ( p_payroll_action_id IN varchar2,
19 p_process_type IN Varchar2,
20 p_assignment_set_id IN Varchar2,
21 p_element_set_id IN Varchar2) RETURN CLOB IS
22
23 l_proc VARCHAR2(100) := g_package||'.get_xmldoc_clob';
24 tmp_clob CLOB;
25 l_query VARCHAR2(32000);
26 l_qryCtx DBMS_XMLGEN.ctxHandle;
27 storage_limit integer;
28 clob_length number;
29
30 lv_assignment_set_id varchar2(30);
31 lv_element_set_id varchar2(30);
32 rowKount Number(10);
33 noDataFound exception;
34
35 BEGIN
36
37 hr_utility.trace('Entering '||l_proc);
38
39 hr_utility.trace('p_payroll_action_id ='||p_payroll_action_id);
40 hr_utility.trace('p_process_type ='||p_process_type);
41 hr_utility.trace('p_assignment_set_id ='||p_assignment_set_id);
42 hr_utility.trace('p_element_set_id ='||p_element_set_id);
43 if p_assignment_set_id is null then
44 lv_assignment_set_id := 'NULL';
45 else
46 lv_assignment_set_id := p_assignment_set_id;
47 end if;
48 hr_utility.trace('lv_assignment_set_id ='||lv_assignment_set_id);
49
50 if p_element_set_id is null then
51 lv_element_set_id := 'NULL';
52 else
53 lv_element_set_id := p_element_set_id;
54 end if;
55 hr_utility.trace('lv_element_set_id ='||lv_element_set_id);
56
57 -- Original Query without Assignment Set and Element Set
58 --
59 /*
60 l_query := 'SELECT payroll_name as "@Payroll"
61 ,ppa.effective_date as "@Date_Paid"
62 ,full_name as "@Name"
63 ,assignment_number as "@Assignment_Number"
64 ,paf.assignment_id as "@Assignment_Id"
65 ,run_type_name as "@Run_Type"
66 ,element_name as "@Element"
67 ,jurisdiction_code as "@Jurisdiction"
68 ,NAME as "@Input_Value"
69 ,result_value as "@Run_Result"
70 ,balance_name as "@Balance"
71 ,DECODE(scale,1, ''+'', -1, ''-'') as "@ADD_Subtract"
72 ,pp.person_id as "@person_id"
73 FROM pay_payrolls_f ppf
74 ,pay_payroll_actions ppa
75 ,pay_assignment_actions paa
76 ,per_assignments_f paf
77 ,pay_run_results prr
78 ,per_people_f pp
79 ,pay_run_result_values prv
80 ,pay_element_types_f pet
81 ,pay_input_values_f piv
82 ,pay_balance_types bt
83 ,pay_balance_feeds_f bf
84 ,pay_run_types_f rt
85 WHERE ppa.payroll_action_id = '||p_payroll_action_id ||'
86 AND ppa.payroll_id = ppf.payroll_id
87 and paa.payroll_action_id = ppa.payroll_action_id
88 AND ppa.effective_date BETWEEN ppf.effective_start_date
89 AND ppf.effective_end_date
90 and ppf.payroll_id = paf.payroll_id
91 AND paf.person_id = pp.person_id
92 AND ppa.effective_date BETWEEN pp.effective_start_date
93 AND pp.effective_end_date
94 AND ppa.effective_date BETWEEN paf.effective_start_date
95 AND paf.effective_end_date
96 AND paf.assignment_id = paa.assignment_id
97 AND prr.assignment_action_id = paa.assignment_action_id
98 AND pet.element_type_id = prr.element_type_id
99 AND ppa.effective_date BETWEEN pet.effective_start_date
100 AND pet.effective_end_date
101 AND prr.run_result_id = prv.run_result_id
102 AND piv.input_value_id = prv.input_value_id
103 AND ppa.effective_date BETWEEN piv.effective_start_date
104 AND piv.effective_end_date
105 AND piv.input_value_id = bf.input_value_id
106 AND bt.balance_type_id = bf.balance_type_id
107 AND ppa.effective_date BETWEEN bf.effective_start_date
108 AND bf.effective_end_date
109 AND rt.run_type_id = NVL(paa.run_type_id, ppa.run_type_id)
110 union all
111 SELECT payroll_name Payroll
112 ,ppa.effective_date Date_Paid
113 ,full_name Name
114 ,assignment_number Assignment_Number
115 ,paf.assignment_id Assignment_Id
116 ,NULL Run_Type
117 ,NULL Element
118 ,NULL Jurisdiction
119 ,NULL Input_Value
120 ,NULL Run_Result
121 ,NULL Balance
122 ,NULL ADD_Subtract
123 ,pp.person_id person_id
124 FROM pay_payrolls_f ppf
125 ,pay_payroll_actions ppa
126 ,per_people_f pp
127 ,per_assignments_f paf
128 where
129 ppa.payroll_action_id = '||p_payroll_action_id ||'
130 AND ppa.payroll_id = ppf.payroll_id
131 AND ppa.effective_date BETWEEN ppf.effective_start_date
132 AND ppf.effective_end_date
133 and ppf.payroll_id = paf.payroll_id
134 AND paf.person_id = pp.person_id
135 AND ppa.effective_date BETWEEN pp.effective_start_date
136 AND pp.effective_end_date
137 AND ppa.effective_date BETWEEN paf.effective_start_date
138 AND paf.effective_end_date
139 AND not exists ( select 1 from pay_assignment_actions paa
140 where paf.assignment_id = paa.assignment_id
141 and paa.payroll_action_id = '||p_payroll_action_id ||' )
142 ORDER BY 1,2,13, 5, 7, 9, 11';
143
144 */
145 /* Bug# 5886859 if process type = tax
146 extract in balance mode
147 else extract balance and element mode */
148 if(p_process_type = 'TAX') then
149 l_query := 'SELECT full_name as "@Name"
150 ,assignment_number as "@Assignment_Number"
151 ,paf.assignment_id as "@Assignment_Id"
152 ,balance_name as "@Balance"
153 ,sum(nvl(result_value,0)) as "@Run_Result"
154 FROM pay_payrolls_f ppf
155 ,pay_payroll_actions ppa
156 ,pay_assignment_actions paa
157 ,per_assignments_f paf
158 ,pay_run_results prr
159 ,per_people_f pp
160 ,pay_run_result_values prv
161 ,pay_element_types_f pet
162 ,pay_balance_types bt
163 ,pay_balance_feeds_f bf
164 ,pay_run_types_f rt
165 WHERE ppa.payroll_action_id = '||p_payroll_action_id ||'
166 AND ppa.payroll_id = ppf.payroll_id
167 and paa.payroll_action_id = ppa.payroll_action_id
168 AND ppa.effective_date BETWEEN ppf.effective_start_date
169 AND ppf.effective_end_date
170 and ppf.payroll_id = paf.payroll_id
171 AND paf.person_id = pp.person_id
172 AND ppa.effective_date BETWEEN pp.effective_start_date
173 AND pp.effective_end_date
174 AND ppa.effective_date BETWEEN paf.effective_start_date
175 AND paf.effective_end_date
176 AND paf.assignment_id = paa.assignment_id
177 AND prr.assignment_action_id = paa.assignment_action_id
178 AND pet.element_type_id = prr.element_type_id
179 AND ppa.effective_date BETWEEN pet.effective_start_date
180 AND pet.effective_end_date
181 AND prr.run_result_id = prv.run_result_id
182 AND prv.input_value_id = bf.input_value_id
183 AND prv.input_value_id in (
184 select input_value_id
185 from pay_input_values_f piv
186 where ppa.effective_date BETWEEN piv.effective_start_date
187 AND piv.effective_end_date)
188 AND bt.balance_type_id = bf.balance_type_id
189 AND ppa.effective_date BETWEEN bf.effective_start_date
190 AND bf.effective_end_date
191 AND rt.run_type_id = NVL(paa.run_type_id, ppa.run_type_id)
192 and ('||lv_assignment_set_id ||' is NULL
193 or ( '||lv_assignment_set_id ||' is not NULL
194 and '||lv_assignment_set_id ||' in
195 (select assignment_set_id
196 from hr_assignment_set_amendments hasa
197 where hasa.assignment_set_id = '||lv_assignment_set_id ||'
198 and paf.assignment_id = hasa.assignment_id
199 )
200 )
201 )
202 and ('||lv_element_set_id ||' is null
203 or ('||lv_element_set_id ||' is not null
204 and exists
205 (select ''x'' from pay_element_type_rules petr
206 where petr.element_set_id = '||lv_element_set_id ||'
207 and petr.element_type_id = pet.element_type_id
208 and petr.include_or_exclude = ''I''
209 union all
210 select ''x'' from pay_element_types_f pet1
211 where pet1.classification_id in
212 (select classification_id
213 from pay_ele_classification_rules
214 where element_set_id = '||lv_element_set_id ||')
215 and pet1.element_type_id = pet.element_type_id
216 minus
217 select ''x'' from pay_element_type_rules petr
218 where petr.element_set_id = '||lv_element_set_id ||'
219 and petr.element_type_id = pet.element_type_id
220 and petr.include_or_exclude = ''E''
221 )
222 )
223 )
224 group by pp.person_id,paf.assignment_id,balance_name,full_name,assignment_number
225 ORDER BY 3, 4';
226 else
227 l_query := 'SELECT payroll_name as "@Payroll"
228 ,ppa.effective_date as "@Date_Paid"
229 ,full_name as "@Name"
230 ,assignment_number as "@Assignment_Number"
231 ,paf.assignment_id as "@Assignment_Id"
232 ,run_type_name as "@Run_Type"
233 ,element_name as "@Element"
234 ,jurisdiction_code as "@Jurisdiction"
235 ,NAME as "@Input_Value"
236 ,result_value as "@Run_Result"
237 ,balance_name as "@Balance"
238 ,DECODE(scale,1, ''+'', -1, ''-'') as "@ADD_Subtract"
239 ,pp.person_id as "@person_id"
240 FROM pay_payrolls_f ppf
241 ,pay_payroll_actions ppa
242 ,pay_assignment_actions paa
243 ,per_assignments_f paf
244 ,pay_run_results prr
245 ,per_people_f pp
246 ,pay_run_result_values prv
247 ,pay_element_types_f pet
248 ,pay_input_values_f piv
249 ,pay_balance_types bt
250 ,pay_balance_feeds_f bf
251 ,pay_run_types_f rt
252 WHERE ppa.payroll_action_id = '||p_payroll_action_id ||'
253 AND ppa.payroll_id = ppf.payroll_id
254 and paa.payroll_action_id = ppa.payroll_action_id
255 AND ppa.effective_date BETWEEN ppf.effective_start_date
256 AND ppf.effective_end_date
257 and ppf.payroll_id = paf.payroll_id
258 AND paf.person_id = pp.person_id
259 AND ppa.effective_date BETWEEN pp.effective_start_date
260 AND pp.effective_end_date
261 AND ppa.effective_date BETWEEN paf.effective_start_date
262 AND paf.effective_end_date
263 AND paf.assignment_id = paa.assignment_id
264 AND prr.assignment_action_id = paa.assignment_action_id
265 AND pet.element_type_id = prr.element_type_id
266 AND ppa.effective_date BETWEEN pet.effective_start_date
267 AND pet.effective_end_date
268 AND prr.run_result_id = prv.run_result_id
269 AND piv.input_value_id = prv.input_value_id
270 AND ppa.effective_date BETWEEN piv.effective_start_date
271 AND piv.effective_end_date
272 AND piv.input_value_id = bf.input_value_id
273 AND bt.balance_type_id = bf.balance_type_id
274 AND ppa.effective_date BETWEEN bf.effective_start_date
275 AND bf.effective_end_date
276 AND rt.run_type_id = NVL(paa.run_type_id, ppa.run_type_id)
277 and ('||lv_assignment_set_id ||' is NULL
278 or ( '||lv_assignment_set_id ||' is not NULL
279 and '||lv_assignment_set_id ||' in
280 (select assignment_set_id
281 from hr_assignment_set_amendments hasa
282 where hasa.assignment_set_id = '||lv_assignment_set_id ||'
283 and paf.assignment_id = hasa.assignment_id
284 )
285 )
286 )
287 and ('||lv_element_set_id ||' is null
288 or ('||lv_element_set_id ||' is not null
289 and exists
290 (select ''x'' from pay_element_type_rules petr
291 where petr.element_set_id = '||lv_element_set_id ||'
292 and petr.element_type_id = pet.element_type_id
293 and petr.include_or_exclude = ''I''
294 union all
295 select ''x'' from pay_element_types_f pet1
296 where pet1.classification_id in
297 (select classification_id
298 from pay_ele_classification_rules
299 where element_set_id = '||lv_element_set_id ||')
300 and pet1.element_type_id = pet.element_type_id
301 minus
302 select ''x'' from pay_element_type_rules petr
303 where petr.element_set_id = '||lv_element_set_id ||'
304 and petr.element_type_id = pet.element_type_id
305 and petr.include_or_exclude = ''E''
306 )
307 )
308 )
309 ORDER BY 1,2,13, 5, 7, 9, 11';
310 end if; /*Bug# 5886859 ending here*/
311
312 hr_utility.trace('lenght of l_query = '||to_char(length(l_query)));
313
314 hr_utility.trace ('Processing '||p_payroll_action_id);
315
316 hr_utility.trace('setting l_qryCtx');
317
318 l_qryCtx := dbms_xmlgen.newcontext(l_query);
319
320 hr_utility.trace('calling xml l_qryCtx');
321
322 tmp_clob := dbms_xmlgen.getxml(l_qryCtx);
323
324 rowKount := dbms_xmlgen.getnumrowsprocessed(l_qryCtx);
325 hr_utility.trace('rowKount = '||rowKount);
326
327 --storage_limit := DBMS_LOB.GET_STORAGE_LIMIT();
328 --hr_utility.trace ('storage_limit '||to_char(storage_limit));
329
330 if rowKount = 0 then
331 debug_mesg := 'In get_xmldoc_clob Query return: No Data Found';
332 raise noDataFound;
333 else
334 clob_length := dbms_lob.getlength(tmp_clob);
335 hr_utility.trace('clob length '||to_char(clob_length));
336 end if;
337
338 DBMS_XMLGEN.closeContext(l_qryCtx);
339 hr_utility.trace('Leaving '||l_proc);
340 return tmp_clob;
341
342 EXCEPTION
343 when noDataFound then
344 if debug_mesg IS NULL THEN
345 debug_mesg := 'In get_xmldoc_clob Exception : noDataFound';
346 end if;
347 hr_utility.trace(debug_mesg) ;
348 hr_utility.raise_error;
349 WHEN others THEN
350 if debug_mesg IS NULL THEN
351 debug_mesg := 'In get_xmldoc_clob Exception : OTHERS';
352 end if;
353 hr_utility.trace(debug_mesg) ;
354 raise;
355 END;
356
357 --begin
358 --hr_utility.trace_on(null, 'payextract');
359
360 END PAY_PAYROLL_EXTRACT;
[Home] [Help]