[Home] [Help]
PACKAGE BODY: APPS.PER_MX_SSAFFL_EXTRACT_PKG
Source
1 PACKAGE BODY per_mx_ssaffl_extract_pkg AS
2 /* $Header: pemxssrp.pkb 120.0 2005/05/31 11:28:19 appldev noship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : per_mx_ssaffl_extract_pkg
22
23 Description : Package for the SS Affiliation Reports. The package
24 generated the output file in the specified user
25 format. The current formats supported are
26 - HTML
27 - CSV
28
29 Change List
30 -----------
31 Date Name Vers Bug No Description
32 ---- ---- ------ ------- -----------
33 07-MAY-2004 kthirmiy 115.0 Created.
34 19-MAY-2004 kthirmiy 115.1 Changed affltype to check
35 for R - Hires/Rehires
36 for B - Separations
37 11-JUN-2004 kthirmiy 115.2 Changed affltype to check
38 for HIRES - Hires/Rehires
39 for SEPARATIONS - Separations
40 17-JUN-2004 kthirmiy 115.3 version update
41 */
42
43 /************************************************************
44 ** Local Package Variables
45 ************************************************************/
46 gv_title VARCHAR2(100);
47 gc_csv_delimiter VARCHAR2(1) ;
48 gc_csv_data_delimiter VARCHAR2(1) ;
49
50 gv_html_start_data VARCHAR2(5) ;
51 gv_html_end_data VARCHAR2(5) ;
52
53 gv_package_name VARCHAR2(50) ;
54 g_output_file_type VARCHAR2(10) ;
55
56 /******************************************************************
57 ** Function Returns the formated input string based on the
58 ** Output format. If the format is CSV then the values are returned
59 ** seperated by comma (,). If the format is HTML then the returned
60 ** string as the HTML tags. The parameter p_bold only works for
61 ** the HTML format.
62 ******************************************************************/
63 FUNCTION formated_data_string
64 (p_input_string in varchar2
65 )
66 RETURN VARCHAR2
67 IS
68
69 lv_format varchar2(1000);
70 lv_bold varchar2(1);
71 BEGIN
72 lv_bold :='N' ;
73
74 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
75 if g_output_file_type = 'CSV' then
76 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
77 lv_format := gc_csv_data_delimiter || p_input_string ||
78 gc_csv_data_delimiter || gc_csv_delimiter;
79 elsif g_output_file_type = 'HTML' then
80 if p_input_string is null then
81 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
82 lv_format := gv_html_start_data || '\ ' || gv_html_end_data;
83 else
84 if lv_bold = 'Y' then
85 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
86 lv_format := gv_html_start_data || '<b> ' || p_input_string
87 || '</b>' || gv_html_end_data;
88 else
89 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
90 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
91 end if;
92 end if;
93 end if;
94
95 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
96 return lv_format;
97
98 END formated_data_string;
99
100 /************************************************************
101 ** Function returns the string with the HTML Header tags
102 ************************************************************/
103 FUNCTION formated_header_string
104 (p_input_string in varchar2
105 )
106 RETURN VARCHAR2
107 IS
108
109 lv_format varchar2(1000);
110
111 BEGIN
112 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
113 if g_output_file_type = 'CSV' then
114 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
115 lv_format := p_input_string;
116 elsif g_output_file_type = 'HTML' then
117 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
118 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
119 '</B></H1></CENTER></HEAD>';
120 end if;
121
122 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
123 return lv_format;
124
125 END formated_header_string;
126
127
128
129 /*****************************************************************
130 ** This is the main procedure which is called from the Concurrent
131 ** Request. All the paramaters are passed based on which it will
132 ** either print a CSV format or an HTML format file.
133 *****************************************************************/
134 PROCEDURE ssaffl_extract
135 (errbuf out nocopy varchar2
136 ,retcode out nocopy number
137 ,p_business_group_id in number
138 ,p_tax_unit_id in number
139 ,p_affl_type in varchar2
140 ,p_output_file_type in varchar2
141 )
142 IS
143
144
145 /************************************************************
146 ** Cursor to get the hire/rehire affiliation records from
147 ** pay_action_information table
148 ************************************************************/
149 cursor c_hire_details( cp_tax_unit_id in number
150 ) is
151 select formated_data_string(action_information1)||
152 formated_data_string(action_information2)||
153 formated_data_string(action_information3) ||
154 formated_data_string(action_information4) ||
155 formated_data_string(action_information5) ||
156 formated_data_string(action_information6) ||
157 formated_data_string(action_information7) ||
158 formated_data_string(action_information8) ||
159 formated_data_string(' ') ||
160 formated_data_string(action_information10) ||
161 formated_data_string(action_information11) ||
162 formated_data_string(action_information12) ||
163 formated_data_string(action_information13) ||
164 formated_data_string(action_information14) ||
165 formated_data_string(' ') ||
166 formated_data_string(action_information16) ||
167 formated_data_string(action_information17) ||
168 formated_data_string(action_information18) ||
169 formated_data_string(' ') ||
170 formated_data_string(action_information20) ||
171 formated_data_string(action_information21)
172 from pay_action_information
173 where tax_unit_id = cp_tax_unit_id
174 and action_context_type ='AAP'
175 and action_information_category = 'MX SS HIRE DETAILS'
176 and action_information22 ='A' ;
177
178
179
180 /************************************************************
181 ** Cursor to get the HIRE/reHIRE affiliation records from
182 ** pay_action_information table
183 ************************************************************/
184 cursor c_sep_details( cp_tax_unit_id in number
185 ) is
186 select
187 formated_data_string(action_information1) ||
188 formated_data_string(action_information2) ||
189 formated_data_string(action_information3) ||
190 formated_data_string(action_information4) ||
191 formated_data_string(action_information5) ||
192 formated_data_string(action_information6) ||
193 formated_data_string(action_information7) ||
194 formated_data_string('000000000000000') ||
195 formated_data_string(action_information9) ||
196 formated_data_string(' ') ||
197 formated_data_string(action_information11) ||
198 formated_data_string(action_information12) ||
199 formated_data_string(action_information13) ||
200 formated_data_string(action_information14) ||
201 formated_data_string(' ') ||
202 formated_data_string(action_information16)
203 from pay_action_information
204 where tax_unit_id = cp_tax_unit_id
205 and action_context_type ='AAP'
206 and action_information_category = 'MX SS SEPARATION DETAILS'
207 and action_information22 ='A' ;
208
209 /*************************************************************
210 ** Local Variables
211 *************************************************************/
212
213 lv_title1 VARCHAR2(100);
214 lv_title2 VARCHAR2(100);
215
216 lv_data_row VARCHAR2(32000);
217
218 BEGIN
219
220 gv_package_name := 'per_mx_ssaffl_extract_pkg';
221 g_output_file_type := p_output_file_type ;
222 gc_csv_delimiter := ',';
223 gc_csv_data_delimiter := '"';
224
225 gv_html_start_data := '<td>' ;
226 gv_html_end_data := '</td>' ;
227
228
229 lv_data_row := null ;
230
231 hr_utility.set_location(gv_package_name || '.ssaffl_extract', 10);
232
233 if p_affl_type = 'HIRES' then
234 lv_title1 := 'Soical Security Hire/Rehire Affiliation Transactions' ;
235 elsif p_affl_type ='SEPARATIONS' then
236 lv_title1 := 'Soical Security Separation Transactions' ;
237 end if;
238
239 /*
240 fnd_file.put_line(fnd_file.output, formated_header_string(
241 lv_title1
242 ));
243
244 fnd_file.put_line(fnd_file.output, formated_header_string(
245 ' '
246 ));
247 */
248
249
250 if p_affl_type='HIRES' then
251 hr_utility.set_location(gv_package_name || '.ssaffl_extract', 20);
252 open c_HIRE_details(p_tax_unit_id );
253 loop
254 fetch c_HIRE_details into lv_data_row;
255 if c_HIRE_details%notfound then
256 hr_utility.set_location(gv_package_name || '.ssaffl_extract', 100);
257 exit;
258 end if;
259 if g_output_file_type ='HTML' then
260 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
261 end if;
262 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
263 end loop ;
264 close c_HIRE_details ;
265
266
267 update pay_action_information
268 set action_information22='M',
269 action_information23='Reported in the IMSS Mag Tape'
270 where tax_unit_id = p_tax_unit_id
271 and action_context_type ='AAP'
272 and action_information_category = 'MX SS HIRE DETAILS'
273 and action_information22 ='A' ;
274
275
276 elsif p_affl_type='SEPARATIONS' then
277
278 hr_utility.set_location(gv_package_name || '.ssaffl_extract', 30);
279 open c_SEP_details(p_tax_unit_id );
280 loop
281 fetch c_sep_details into lv_data_row ;
282 if c_sep_details%notfound then
283 hr_utility.set_location(gv_package_name || '.ssaffl_extract', 100);
284 exit;
285 end if;
286 if g_output_file_type ='HTML' then
287 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
288 end if;
289 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
290 end loop ;
291 close c_sep_details ;
292
293 update pay_action_information
294 set action_information22='M',
295 action_information23='Reported in the IMSS Mag Tape'
296 where tax_unit_id = p_tax_unit_id
297 and action_context_type ='AAP'
298 and action_information_category = 'MX SS SEPARATION DETAILS'
299 and action_information22 ='A' ;
300
301 end if;
302 hr_utility.set_location(gv_package_name || '.ssaffl_extract', 40);
303
304 if p_output_file_type ='HTML' then
305 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
306 end if;
307 commit ;
308
309 END ssaffl_extract;
310
311 --begin
312 --hr_utility.trace_on(null, 'ELE');
313 end per_mx_ssaffl_extract_pkg;