[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_LTFR
Source
1 PACKAGE BODY PAY_FI_LTFR AS
2 /* $Header: pyfiltfr.pkb 120.1.12000000.3 2007/03/20 05:41:23 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 L_SALARY number;
18 l_sr_no NUMBER ;
19
20 /* End of declaration*/
21
22 /* Cursors */
23 Cursor csr_ltfr_header_rpt(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE)
24 IS
25 SELECT *
26 FROM PAY_ACTION_INFORMATION
27 WHERE ACTION_CONTEXT_TYPE = 'PA'
28 AND ACTION_CONTEXT_ID = csr_v_pa_id
29 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT DETAILS';
30
31 rg_ltfr_header_rpt csr_ltfr_header_rpt%rowtype;
32
33 Cursor csr_ltfr_body_rpt(csr_v_pa_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE)
34 IS
35 SELECT *
36 FROM PAY_ACTION_INFORMATION
37 WHERE ACTION_CONTEXT_TYPE = 'AAP'
38 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT INFORMATION'
39 AND ACTION_INFORMATION2='PER'
40 AND ACTION_INFORMATION10=csr_v_pa_id
41 ORDER BY action_information4;
42
43 rg_ltfr_body_rpt csr_ltfr_body_rpt%rowtype;
44
45 /* End of Cursors */
46
47 BEGIN
48
49 IF p_payroll_action_id IS NULL THEN
50
51 BEGIN
52
53 SELECT payroll_action_id
54 INTO l_payroll_action_id
55 FROM pay_payroll_actions ppa,
56 fnd_conc_req_summary_v fcrs,
57 fnd_conc_req_summary_v fcrs1
58 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
59 AND fcrs.priority_request_id = fcrs1.priority_request_id
60 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
61 AND ppa.request_id = fcrs1.request_id;
62
63 EXCEPTION
64 WHEN others THEN
65 NULL;
66 END ;
67
68 ELSE
69
70 l_payroll_action_id :=p_payroll_action_id;
71
72 END IF;
73
74 hr_utility.set_location('Entered Procedure GETDATA',10);
75
76 /* Pick up the details belonging to Header */
77
78 OPEN csr_ltfr_header_rpt( l_payroll_action_id);
79 FETCH csr_ltfr_header_rpt INTO rg_ltfr_header_rpt;
80 CLOSE csr_ltfr_header_rpt;
81
82 hr_utility.set_location('Before populating pl/sql table',20);
83
84 FOR rg_ltfr_body_rpt IN csr_ltfr_body_rpt( l_payroll_action_id)
85 LOOP
86
87
88 gtagdata(l_counter).TagName := 'PERSON';
89 gtagdata(l_counter).TagValue := 'PERSON';
90 l_counter := l_counter + 1;
91
92 gtagdata(l_counter).TagName := 'RECORD_TYPE_K';
93 gtagdata(l_counter).TagValue := 'RECORD_TYPE_K';
94 l_counter := l_counter + 1;
95
96
97 gtagdata(l_counter).TagName := 'K_RECORD_NAME';
98 gtagdata(l_counter).TagValue :=hr_general.decode_lookup('FI_FORM_LABELS','REC_K');
99 l_counter := l_counter + 1;
100
101 gtagdata(l_counter).TagName := 'INS_POLICY_NUM';
102 gtagdata(l_counter).TagValue := pay_fi_general.xml_parser(rg_ltfr_header_rpt.action_information2);
103 l_counter := l_counter + 1;
104
105 gtagdata(l_counter).TagName := 'PIN';
106 gtagdata(l_counter).TagValue := pay_fi_general.xml_parser(rg_ltfr_body_rpt.action_information5);
107 l_counter := l_counter + 1;
108
109 gtagdata(l_counter).TagName := 'LOCAL_UNIT';
110 gtagdata(l_counter).TagValue := rg_ltfr_header_rpt.action_information11;
111 l_counter := l_counter + 1;
112
113 gtagdata(l_counter).TagName :='EMPLOYEE_NAME';
114 gtagdata(l_counter).TagValue := pay_fi_general.xml_parser(rg_ltfr_body_rpt.action_information4);
115 l_counter := l_counter + 1;
116
117
118 gtagdata(l_counter).TagName := 'RECORD_TYPE_K';
119 gtagdata(l_counter).TagValue := 'RECORD_TYPE_K_END';
120 l_counter := l_counter + 1;
121
122 gtagdata(l_counter).TagName := 'RECORD_TYPE_L';
123 gtagdata(l_counter).TagValue := 'RECORD_TYPE_L';
124 l_counter := l_counter + 1;
125
126
127 gtagdata(l_counter).TagName := 'L_RECORD_NAME';
128 gtagdata(l_counter).TagValue :=hr_general.decode_lookup('FI_FORM_LABELS','REC_L');
129 l_counter := l_counter + 1;
130
131 gtagdata(l_counter).TagName := 'ACTION_ID';
132 gtagdata(l_counter).TagValue := '2';
133 l_counter := l_counter + 1;
134
135 gtagdata(l_counter).TagName := 'TARGET_YEAR';
136 gtagdata(l_counter).TagValue := rg_ltfr_header_rpt.action_information7;
137 l_counter := l_counter + 1;
138
139
140 l_salary:= FND_NUMBER.CANONICAL_TO_NUMBER(rg_ltfr_body_rpt.action_information7) +
141 FND_NUMBER.CANONICAL_TO_NUMBER(rg_ltfr_body_rpt.action_information8) +
142 FND_NUMBER.CANONICAL_TO_NUMBER(rg_ltfr_body_rpt.action_information9);
143
144 gtagdata(l_counter).TagName := 'SALARY';
145 gtagdata(l_counter).TagValue :=l_salary ;
146 l_counter := l_counter + 1;
147
148
149 gtagdata(l_counter).TagName := 'PAYMENT_MONTH';
150 gtagdata(l_counter).TagValue := lpad(rg_ltfr_header_rpt.action_information6,2,'0');
151 l_counter := l_counter + 1;
152
153
154 gtagdata(l_counter).TagName :='REPORTING_METHOD';
155 gtagdata(l_counter).TagValue := rg_ltfr_body_rpt.action_information6;
156 l_counter := l_counter + 1;
157
158 l_sr_no:= l_sr_no + 1;
159 gtagdata(l_counter).TagName := 'RECORD_TYPE_L';
160 gtagdata(l_counter).TagValue := 'RECORD_TYPE_L_END';
161 l_counter := l_counter + 1;
162
163
164 gtagdata(l_counter).TagName := 'PERSON';
165 gtagdata(l_counter).TagValue := 'PERSON_END';
166 l_counter := l_counter + 1;
167
168 END LOOP;
169
170 hr_utility.set_location('After populating pl/sql table',30);
171
172
173 WritetoCLOB (p_xml );
174
175
176 END GET_DATA;
177
178 -----------------------------------------------------------------------------------------------------------------
179 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
180 l_xfdf_string clob;
181 l_str1 varchar2(1000);
182 l_str2 varchar2(20);
183 l_str3 varchar2(20);
184 l_str4 varchar2(20);
185 l_str5 varchar2(20);
186 l_str6 varchar2(30);
187 l_str7 varchar2(1000);
188 l_str8 varchar2(240);
189 l_str9 varchar2(240);
190 l_str10 varchar2(20);
191 l_str11 varchar2(20);
192
193 current_index pls_integer;
194 l_IANA_charset VARCHAR2 (50);
195
196 BEGIN
197 l_IANA_charset :=hr_fi_utility.get_IANA_charset ;
198 hr_utility.set_location('Entering WritetoCLOB ',70);
199 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>' ;
200 l_str2 := '<';
201 l_str3 := '>';
202 l_str4 := '</';
203 l_str5 := '>';
204 l_str6 := '</ROOT>';
205 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
206 l_str10 := '<PERSON>';
207 l_str11 := '</PERSON>';
208
209
210 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
211 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
212
213 current_index := 0;
214
215 IF gtagdata.count > 0 THEN
216
217 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
218
219
220 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
221
222 l_str8 := gtagdata(table_counter).TagName;
223 l_str9 := gtagdata(table_counter).TagValue;
224
225 IF l_str9 IN ('PERSON' ,'PERSON_END','RECORD_TYPE_K',
226 'RECORD_TYPE_K_END','RECORD_TYPE_L','RECORD_TYPE_L_END') THEN
227
228 IF l_str9 IN ('PERSON' ,'RECORD_TYPE_K','RECORD_TYPE_L') THEN
229 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
230 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
231 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
232 ELSE
233 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
234 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
235 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
236 END IF;
237
238 ELSE
239
240 if l_str9 is not null then
241
242 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
243 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
244 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
245 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
246 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
247 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
248 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
249 else
250
251 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
252 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
253 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
254 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
255 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
256 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
257
258 end if;
259
260 END IF;
261
262 END LOOP;
263
264 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
265
266 ELSE
267 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
268 END IF;
269
270 p_xfdf_clob := l_xfdf_string;
271
272 hr_utility.set_location('Leaving WritetoCLOB ',40);
273
274 EXCEPTION
275 WHEN OTHERS then
276 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
277 HR_UTILITY.RAISE_ERROR;
278 END WritetoCLOB;
279 -------------------------------------------------------------------------------------------------------------------------
280
281 END PAY_FI_LTFR;