[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_TETR
Source
1 PACKAGE BODY PAY_SE_TETR AS
2 /* $Header: pysetetr.pkb 120.0.12000000.1 2007/07/11 12:32:26 dbehera noship $ */
3 PROCEDURE GET_DATA (
4 p_business_group_id IN NUMBER,
5 p_payroll_action_id IN VARCHAR2 ,
6 p_template_name IN VARCHAR2,
7 p_xml OUT NOCOPY CLOB
8 )
9
10 IS
11
12 /* Start of declaration*/
13
14 -- Variables needed for the report
15 l_counter number := 0;
16 l_payroll_action_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE;
17
18 /* End of declaration*/
19
20 /* Cursors */
21
22 Cursor csr_tetr_header_rpt(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE)
23 IS
24 SELECT *
25 FROM PAY_ACTION_INFORMATION
26 WHERE ACTION_CONTEXT_TYPE = 'PA'
27 AND ACTION_CONTEXT_ID = csr_v_pa_id
28 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT DETAILS'
29 AND ACTION_INFORMATION1='PYSETETA';
30
31
32 Cursor csr_tetr_ph_body(csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE)
33 IS
34 SELECT *
35 FROM PAY_ACTION_INFORMATION
36 WHERE ACTION_CONTEXT_TYPE = 'PA'
37 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
38 AND ACTION_INFORMATION1='PYSETETA'
39 AND ACTION_INFORMATION2=csr_v_pa_id
40 AND ACTION_INFORMATION11='PH'
41 ORDER BY TO_DATE(action_information8) DESC;
42
43 Cursor csr_tetr_ch_body(csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE)
44 IS
45 SELECT *
46 FROM PAY_ACTION_INFORMATION
47 WHERE ACTION_CONTEXT_TYPE = 'AAP'
48 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
49 AND ACTION_INFORMATION1='PYSETETA'
50 AND ACTION_INFORMATION2=csr_v_pa_id
51 AND ACTION_INFORMATION11='CH'
52 ORDER BY TO_DATE(action_information8) DESC;
53
54
55 Cursor csr_tetr_body_rpt(csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE)
56 IS
57 SELECT *
58 FROM PAY_ACTION_INFORMATION
59 WHERE ACTION_CONTEXT_TYPE = 'AAP'
60 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
61 AND ACTION_INFORMATION1='PYSETETA'
62 AND ACTION_INFORMATION2=csr_v_pa_id
63 AND ACTION_INFORMATION11='CS'
64 ORDER BY action_information4,action_information6;
65
66
67 Cursor csr_tetr_footer_rpt(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE)
68 IS
69 SELECT *
70 FROM PAY_ACTION_INFORMATION
71 WHERE ACTION_CONTEXT_TYPE = 'PA'
72 AND ACTION_CONTEXT_ID = csr_v_pa_id
73 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
74 AND ACTION_INFORMATION1='PYSETETA'
75 AND ACTION_INFORMATION2='S';
76
77 rg_tetr_footer_rpt csr_tetr_footer_rpt%rowtype;
78
79
80 /* End of Cursors */
81
82 BEGIN
83
84 IF p_payroll_action_id IS NULL THEN
85
86 BEGIN
87
88 SELECT payroll_action_id
89 INTO l_payroll_action_id
90 FROM pay_payroll_actions ppa,
91 fnd_conc_req_summary_v fcrs,
92 fnd_conc_req_summary_v fcrs1
93 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
94 AND fcrs.priority_request_id = fcrs1.priority_request_id
95 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
96 AND ppa.request_id = fcrs1.request_id;
97
98 EXCEPTION
99 WHEN others THEN
100 NULL;
101 END ;
102
103 ELSE
104
105 l_payroll_action_id :=p_payroll_action_id;
106
107 END IF;
108
109 hr_utility.set_location('Entered Procedure GETDATA',10);
110
111
112 OPEN csr_tetr_footer_rpt( l_payroll_action_id);
113 FETCH csr_tetr_footer_rpt INTO rg_tetr_footer_rpt;
114 CLOSE csr_tetr_footer_rpt;
115
116
117 FOR rg_tetr_header_rpt IN csr_tetr_header_rpt( l_payroll_action_id)
118 LOOP
119
120 gtagdata(l_counter).TagName := 'PERSON';
121 gtagdata(l_counter).TagValue := 'PERSON';
122 l_counter := l_counter + 1;
123
124 gtagdata(l_counter).TagName := 'EMPLOYEE_NUM';
125 gtagdata(l_counter).TagValue := rg_tetr_header_rpt.action_information5;
126 l_counter := l_counter + 1;
127
128 gtagdata(l_counter).TagName := 'PERSON_NAME';
129 gtagdata(l_counter).TagValue := rg_tetr_header_rpt.action_information3;
130 l_counter := l_counter + 1;
131
132 gtagdata(l_counter).TagName := 'PIN';
133 gtagdata(l_counter).TagValue := rg_tetr_header_rpt.action_information4;
134 l_counter := l_counter + 1;
135
136 FOR rg_tetr_ph_body IN csr_tetr_ph_body( l_payroll_action_id)
137 LOOP
138
139 gtagdata(l_counter).TagName := 'PH';
140 gtagdata(l_counter).TagValue := 'PH';
141 l_counter := l_counter + 1;
142
143 gtagdata(l_counter).TagName := 'COMPANY_NAME';
144 gtagdata(l_counter).TagValue := rg_tetr_ph_body.action_information5;
145 l_counter := l_counter + 1;
146
147 gtagdata(l_counter).TagName := 'START_DATE';
148 gtagdata(l_counter).TagValue := rg_tetr_ph_body.action_information8;
149 l_counter := l_counter + 1;
150
151 gtagdata(l_counter).TagName := 'END_DATE';
152 gtagdata(l_counter).TagValue := rg_tetr_ph_body.action_information9;
153 l_counter := l_counter + 1;
154
155
156 gtagdata(l_counter).TagName := 'DAYS_W';
157 gtagdata(l_counter).TagValue := rg_tetr_ph_body.action_information10;
158 l_counter := l_counter + 1;
159
160
161 gtagdata(l_counter).TagName := 'PH';
162 gtagdata(l_counter).TagValue := 'PH_END';
163 l_counter := l_counter + 1;
164
165 END LOOP;
166
167 FOR rg_tetr_ch_body IN csr_tetr_ch_body( l_payroll_action_id)
168 LOOP
169
170 gtagdata(l_counter).TagName := 'CH';
171 gtagdata(l_counter).TagValue := 'CH';
172 l_counter := l_counter + 1;
173
174 gtagdata(l_counter).TagName := 'COMPANY_NAME';
175 gtagdata(l_counter).TagValue := rg_tetr_ch_body.action_information5;
176 l_counter := l_counter + 1;
177
178 gtagdata(l_counter).TagName := 'ASSIGN_CATEGORY';
179 gtagdata(l_counter).TagValue := rg_tetr_ch_body.action_information7;
180 l_counter := l_counter + 1;
181
182 gtagdata(l_counter).TagName := 'START_DATE';
183 gtagdata(l_counter).TagValue := rg_tetr_ch_body.action_information8;
184 l_counter := l_counter + 1;
185
186 gtagdata(l_counter).TagName := 'END_DATE';
187 gtagdata(l_counter).TagValue := rg_tetr_ch_body.action_information9;
188 l_counter := l_counter + 1;
189
190
191 gtagdata(l_counter).TagName := 'DAYS_W';
192 gtagdata(l_counter).TagValue := rg_tetr_ch_body.action_information10;
193 l_counter := l_counter + 1;
194
195
196 gtagdata(l_counter).TagName := 'CH';
197 gtagdata(l_counter).TagValue := 'CH_END';
198 l_counter := l_counter + 1;
199
200 END LOOP;
201
202 FOR rg_tetr_body_rpt IN csr_tetr_body_rpt( l_payroll_action_id)
203 LOOP
204
205 gtagdata(l_counter).TagName := 'BODY';
206 gtagdata(l_counter).TagValue := 'BODY';
207 l_counter := l_counter + 1;
208
209 gtagdata(l_counter).TagName := 'COMPANY_NAME';
210 gtagdata(l_counter).TagValue := rg_tetr_body_rpt.action_information5;
211 l_counter := l_counter + 1;
212
213 gtagdata(l_counter).TagName := 'ASSIGN_CATEGORY';
214 gtagdata(l_counter).TagValue := rg_tetr_body_rpt.action_information7;
215 l_counter := l_counter + 1;
216
217 gtagdata(l_counter).TagName := 'DAYS_W';
218 gtagdata(l_counter).TagValue := rg_tetr_body_rpt.action_information8;
219 l_counter := l_counter + 1;
220
221
222 gtagdata(l_counter).TagName := 'BODY';
223 gtagdata(l_counter).TagValue := 'BODY_END';
224 l_counter := l_counter + 1;
225
226
227 END LOOP;
228
229
230 gtagdata(l_counter).TagName := 'SUMMARY';
231 gtagdata(l_counter).TagValue := 'SUMMARY';
232 l_counter := l_counter + 1;
233
234
235 gtagdata(l_counter).TagName := 'TOT_YEARS';
236 gtagdata(l_counter).TagValue := rg_tetr_footer_rpt.action_information3;
237 l_counter := l_counter + 1;
238
239 gtagdata(l_counter).TagName := 'TOT_DAYS';
240 gtagdata(l_counter).TagValue := rg_tetr_footer_rpt.action_information4;
241 l_counter := l_counter + 1;
242
243
244 gtagdata(l_counter).TagName := 'SUMMARY';
245 gtagdata(l_counter).TagValue := 'SUMMARY_END';
246 l_counter := l_counter + 1;
247
248 gtagdata(l_counter).TagName := 'PERSON';
249 gtagdata(l_counter).TagValue := 'PERSON_END';
250 l_counter := l_counter + 1;
251
252 END LOOP;
253
254 hr_utility.set_location('After populating pl/sql table',30);
255
256
257 WritetoCLOB (p_xml );
258
259
260 END GET_DATA;
261
262 -----------------------------------------------------------------------------------------------------------------
263 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
264 l_xfdf_string clob;
265 l_str1 varchar2(1000);
266 l_str2 varchar2(20);
267 l_str3 varchar2(20);
268 l_str4 varchar2(20);
269 l_str5 varchar2(20);
270 l_str6 varchar2(30);
271 l_str7 varchar2(1000);
272 l_str8 varchar2(240);
273 l_str9 varchar2(240);
274 l_str10 varchar2(20);
275 l_str11 varchar2(20);
276
277 current_index pls_integer;
278 l_IANA_charset VARCHAR2 (50);
279
280 BEGIN
281 l_IANA_charset :=hr_se_utility.get_IANA_charset ;
282 hr_utility.set_location('Entering WritetoCLOB ',70);
283 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>' ;
284 l_str2 := '<';
285 l_str3 := '>';
286 l_str4 := '</';
287 l_str5 := '>';
288 l_str6 := '</ROOT>';
289 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
290 l_str10 := '<PERSON>';
291 l_str11 := '</PERSON>';
292
293
294 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
295 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
296
297 current_index := 0;
298
299 IF gtagdata.count > 0 THEN
300
301 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
302
303 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
304
305 l_str8 := gtagdata(table_counter).TagName;
306 l_str9 := gtagdata(table_counter).TagValue;
307
308
309
310 IF l_str9 IN ('PERSON' ,'PERSON_END','BODY',
311 'BODY_END','PH','PH_END','CH','CH_END','SUMMARY','SUMMARY_END') THEN
312
313 IF l_str9 IN ('PERSON' ,'BODY','PH','CH','SUMMARY') THEN
314 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
315 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
316 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
317 ELSE
318 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
319 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
320 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
321 END IF;
322
323 ELSE
324
325 if l_str9 is not null then
326
327 l_str9 := '<![CDATA[' || l_str9 || ']]>';
328
329 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
330 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
331 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
332 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
333 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
334 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
335 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
336 else
337
338 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
339 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
340 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
341 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
342 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
343 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
344
345 end if;
346
347 END IF;
348
349 END LOOP;
350
351 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
352
353 ELSE
354 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
355 END IF;
356
357 p_xfdf_clob := l_xfdf_string;
358
359 hr_utility.set_location('Leaving WritetoCLOB ',40);
360
361 EXCEPTION
362 WHEN OTHERS then
363 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
364 HR_UTILITY.RAISE_ERROR;
365 END WritetoCLOB;
366 -------------------------------------------------------------------------------------------------------------------------
367
368 END PAY_SE_TETR;