[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_REPORTS_PKG
Source
1 package body pay_kr_yea_reports_pkg as
2 /* $Header: pykryearept.pkb 120.8.12020000.4 2013/02/04 11:14:01 mdubasi ship $ */
3 --------------------------------------------------------------------------------
4 function submit_yea_report (
5 p_bus_grp_id number,
6 p_bus_plc_id number,
7 p_year varchar2,
8 p_asg_id number,
9 p_assact_id number,
10 p_report_name varchar2
11 ) return number
12 is
13 --
14 l_request_id number ;
15 l_template_name varchar2(100); -- Bug 9918977
16 --
17 -- Bug 9918977: Added code to pick the custom template name
18 -- from the EIT at the Business Group level.
19 --
20 cursor get_custom_template
21 is
22 select decode(p_report_name,'PAYKRYRS',hoi.org_information1
23 ,'PAYKRYTR',hoi.org_information2
24 ,'PAYKRYLG',hoi.org_information3)
25 from hr_all_organization_units hou,
26 hr_organization_information hoi
27 where hoi.organization_id = hou.organization_id
28 and hou.business_group_id= p_bus_grp_id
29 and hoi.org_information_context = 'KR_YEA_REPORT_TEMPLATE_INFO';
30 --
31 begin
32 -- Start of Bug 9918977
33 l_template_name := null;
34 --
35 open get_custom_template;
36 fetch get_custom_template into l_template_name;
37 close get_custom_template;
38
39 if l_template_name IS NULL then
40 if to_number(p_year) > 2010 then
41 if p_report_name = 'PAYKRYTR' then -- Bug 13080235
42 if to_number(p_year) >= 2012 then
43 l_template_name := 'PAYKRYTR2012';
44 elsif to_number(p_year) > 2010 and to_number(p_year) <= 2011 then
45 l_template_name := 'PAYKRYTR2011';
46 end if;
47 elsif p_report_name = 'PAYKRYRS' then -- Bug 13080235
48 if to_number(p_year) >= 2012 then
49 l_template_name := 'PAYKRYRS2012';
50 elsif to_number(p_year) > 2010 and to_number(p_year) <= 2011 then
51 l_template_name := 'PAYKRYRS2011';
52 end if;
53 elsif p_report_name = 'PAYKRYLG' then -- Bug 13080235
54 if to_number(p_year) >= 2012 then
55 l_template_name := 'PAYKRYLG2012';
56 else
57 l_template_name := 'PAYKRYLG';
58 end if;
59 end if;
60 else
61 l_template_name := p_report_name;
62 end if;
63 end if;
64 --
65 -- End of Bug 9918977
66 --
67 l_request_id := fnd_request.submit_request(
68 application => 'PAY',
69 program => 'PAYKRYEA_XDO',
70 description => 'Submit KR YEA Reports (XDO)',
71 argument1 => p_bus_grp_id,
72 argument2 => p_bus_plc_id,
73 argument3 => p_year,
74 argument4 => p_asg_id,
75 argument5 => p_assact_id,
76 argument6 => p_report_name,
77 argument7 => l_template_name -- Bug 9918977
78 ) ;
79
80 -- Check the status
81 if l_request_id <> 0 then
82 commit;
83 end if;
84 --
85 return l_request_id;
86 end submit_yea_report ;
87 --------------------------------------------------------------------------------
88 function submit_xml_report (
89 p_bus_grp_id in number,
90 p_bus_plc_id in number,
91 p_year in varchar2,
92 p_asg_id in number,
93 p_assact_id in number,
94 p_report_name in varchar2
95 ) return number
96 is
97 /* Bug # 5563442 Date passed in the wrong format - Changing l_report_date datatype to VARCHAR2 */
98 --
99 l_report_date varchar2(20);
100 --
101 l_request_id number ;
102 l_phase varchar2(100);
103 l_status varchar2(100);
104 l_dev_status varchar2(100);
105 l_dev_phase varchar2(100);
106 l_message varchar2(2000);
107 l_action_completed boolean;
108 l_asg_id number;
109 --
110 begin
111
112 if p_asg_id = 0 then
113
114 l_asg_id := null;
115
116 else
117 l_asg_id := p_asg_id;
118
119 end if;
120 --
121 /* Bug # 5563442 Date passed in the wrong format - Passing l_report_date in Canonical form */
122 --
123 l_report_date := fnd_date.date_to_canonical(to_date('3112'||p_year, 'DDMMYYYY')) ;
124 --
125
126 -- Submit the appropriate report by looking at p_report_name
127 if p_report_name = 'PAYKRYRS' then -- Submit YEA Reclaim Sheet
128 l_request_id := fnd_request.submit_request(
129 application => 'PAY',
130 program => 'PAYKRYRS_XML',
131 description => 'KR Year End Adjustment Reclaim Sheet - XML',
132 argument1 => p_bus_grp_id,
133 argument2 => p_bus_plc_id,
134 argument3 => p_year,
135 argument4 => l_asg_id
136 );
137 elsif p_report_name = 'PAYKRYLG' then -- Submit YEA Ledger
138 l_request_id := fnd_request.submit_request(
139 application => 'PAY',
140 program => 'PAYKRYLG_XML',
141 description => 'KR Year End Adjustment Ledger - XML',
142 argument1 => p_bus_plc_id,
143 argument2 => p_asg_id,
144 argument3 => p_assact_id,
145 argument4 => l_report_date
146 );
147 elsif p_report_name = 'PAYKRYTR' then -- Submit YEA Tax Receipt
148 l_request_id := fnd_request.submit_request(
149 application => 'PAY',
150 program => 'PAYKRYTR_XML',
151 description => 'KR Year End Adjustment Tax Receipt - XML',
152 argument1 => p_bus_plc_id,
153 argument2 => p_asg_id,
154 argument3 => p_assact_id,
155 argument4 => l_report_date,
156 argument5 => 'R',
157 argument6 => 'EK'
158 );
159 end if ;
160
161 -- Check the status
162 if l_request_id <> 0 then
163 -- Save the request and wait for completion
164 commit;
165 l_dev_phase := 'dummy';
166 while (l_dev_phase <> 'COMPLETE') loop
167 l_action_completed := fnd_concurrent.wait_for_request(
168 request_id => l_request_id,
169 interval => 1,
170 max_wait => 10,
171 phase => l_phase,
172 status => l_status,
173 dev_phase => l_dev_phase,
174 dev_status => l_dev_status,
175 message => l_message
176 );
177 end loop;
178 end if;
179
180 return l_request_id;
181 end submit_xml_report ;
182
183 function submit_yea_xml_report (
184 p_bus_grp_id in number,
185 p_asg_id in number,
186 p_assact_id in number,
187 p_report_date in varchar2,
188 p_report_type in varchar2,
189 p_report_title in varchar2,
190 p_report_format in varchar2,
191 p_assignment_set_id in number,
192 p_effective_date_from in varchar2,
193 p_effective_date_to in varchar2,
194 p_payroll_action_id in number
195 ) return number
196 is
197
198
199 l_request_id number ;
200 l_phase varchar2(100);
201 l_status varchar2(100);
202 l_dev_status varchar2(100);
203 l_dev_phase varchar2(100);
204 l_message varchar2(2000);
205 l_action_completed boolean;
206 l_assignment_set_id number;
207
208 begin
209
210 if p_assignment_set_id = 0 then
211 l_assignment_set_id := null;
212 else
213 l_assignment_set_id := p_assignment_set_id;
214 end if;
215
216 if p_report_format = 'PAYKRYTR_ASSACT' then
217
218 l_request_id := fnd_request.submit_request(
219 application => 'PAY',
220 program => 'PAYKRYTR_ASSACT',
221 description => 'KR Year End Adjustment Tax Receipt (Assignment)',
222 argument1 => p_bus_grp_id,
223 argument2 => p_asg_id,
224 argument3 => p_assact_id,
225 argument4 => p_report_date,
226 argument5 => p_report_type,
227 argument6 => p_report_title
228
229 );
230 elsif p_report_format = 'PAYKRYTR_BP' then
231
232
233 l_request_id := fnd_request.submit_request(
234 application => 'PAY',
235 program => 'PAYKRYTR_BP',
236 description => 'KR Year End Adjustment Tax Receipt (Business Place)',
237 argument1 => p_bus_grp_id,
238 argument2 => l_assignment_set_id,
239 argument3 => p_report_date,
240 argument4 => p_effective_date_from,
241 argument5 => p_effective_date_to,
242 argument6 => p_report_type,
243 argument7 => p_report_title
244 );
245 elsif p_report_format = 'PAYKRYTR_PACT' then
246
247 l_request_id := fnd_request.submit_request(
248 application => 'PAY',
249 program => 'PAYKRYTR_PACT',
250 description => 'KR Year End Adjustment Tax Receipt (Payroll Action)',
251 argument1 => p_payroll_action_id,
252 argument2 => l_assignment_set_id,
253 argument3 => p_report_date,
254 argument4 => p_report_type,
255 argument5 => p_report_title
256 );
257 end if;
258
259 -- Check the status
260 if l_request_id <> 0 then
261 -- Save the request and wait for completion
262 commit;
263 l_dev_phase := 'dummy';
264 while (l_dev_phase <> 'COMPLETE') loop
265 l_action_completed := fnd_concurrent.wait_for_request(
266 request_id => l_request_id,
267 interval => 1,
268 max_wait => 10,
269 phase => l_phase,
270 status => l_status,
271 dev_phase => l_dev_phase,
272 dev_status => l_dev_status,
273 message => l_message
274 );
275 end loop;
276 end if;
277
278 return l_request_id;
279 end submit_yea_xml_report;
280
281 function submit_yea_ledger_report (
282 p_bus_grp_id in number,
283 p_asg_id in number,
284 p_assact_id in number,
285 p_report_date in varchar2,
286 p_report_format in varchar2,
287 p_assignment_set_id in number,
288 p_effective_date_from in varchar2,
289 p_effective_date_to in varchar2,
290 p_payroll_action_id in number
291 ) return number
292 is
293
294
295 l_request_id number ;
296 l_phase varchar2(100);
297 l_status varchar2(100);
298 l_dev_status varchar2(100);
299 l_dev_phase varchar2(100);
300 l_message varchar2(2000);
301 l_action_completed boolean;
302 l_assignment_set_id number;
303
304 begin
305
306 if p_assignment_set_id = 0 then
307 l_assignment_set_id := null;
308 else
309 l_assignment_set_id := p_assignment_set_id;
310 end if;
311
312 if p_report_format = 'PAYKRYLG_ASSACT' then
313
314 l_request_id := fnd_request.submit_request(
315 application => 'PAY',
316 program => 'PAYKRYLG_ASSACT',
317 description => 'KR Year End Adjustment Ledger (Assignment - XML)',
318 argument1 => p_bus_grp_id,
319 argument2 => p_asg_id,
320 argument3 => p_assact_id,
321 argument4 => p_report_date
322 );
323 elsif p_report_format = 'PAYKRYLG_BP' then
324
325
326 l_request_id := fnd_request.submit_request(
327 application => 'PAY',
328 program => 'PAYKRYLG_BP',
329 description => 'KR Year End Adjustment Ledger (Business Place - XML)',
330 argument1 => p_bus_grp_id,
331 argument2 => l_assignment_set_id,
332 argument3 => p_effective_date_from,
333 argument4 => p_effective_date_to,
334 argument5 => p_report_date
335 );
336 elsif p_report_format = 'PAYKRYLG_PACT' then
337
338 l_request_id := fnd_request.submit_request(
339 application => 'PAY',
340 program => 'PAYKRYLG_PACT',
341 description => 'KR Year End Adjustment Ledger (Payroll Action - XML)',
342 argument1 => p_payroll_action_id,
343 argument2 => l_assignment_set_id,
344 argument3 => p_report_date
345 );
346 end if;
347
348 -- Check the status
349 if l_request_id <> 0 then
350 -- Save the request and wait for completion
351 commit;
352 l_dev_phase := 'dummy';
353 while (l_dev_phase <> 'COMPLETE') loop
354 l_action_completed := fnd_concurrent.wait_for_request(
355 request_id => l_request_id,
356 interval => 1,
357 max_wait => 10,
358 phase => l_phase,
359 status => l_status,
360 dev_phase => l_dev_phase,
361 dev_status => l_dev_status,
362 message => l_message
363 );
364 end loop;
365 end if;
366
367 return l_request_id;
368 end submit_yea_ledger_report;
369 --------------------------------------------------------------------------------
370
371 end pay_kr_yea_reports_pkg ;