1 package body pay_hk_ir56_report as
2 /* $Header: pyhk56rp.pkb 115.7 2003/05/30 08:21:10 kaverma ship $ */
3 -----------------------------------------------------------------------------
4 -- Program: pay_hk_ir56_report (Package Body)
5 --
6 -- Description: Various procedures/functions to submit the HK IR56B Year
7 -- End Report.
8 --
9 -- Change History
10 -- Date Changed By Version Description of Change
11 -- --------- ---------- ------- ------------------------------------------
12 -- 05 Jul 01 S. Russell 115.0 Initial Version
13 -- 08 Aug 01 S. Russell 115.1 Update change history section. Add join
14 -- of ppa and ppa2 tables in
15 -- get_full_report_runs cursor.
16 -- 12 Nov 01 J.Lin 115.2 Defined l_error in assignment_action_code
17 -- function to replace g_error which is
18 -- removed from package header. Removed
19 -- variable g_error from submit_report
20 -- function. Bug 2087384
21 -- Added dbdrv line
22 -- 02 Dec 02 srrajago 115.3 Included 'nocopy' option for the 'OUT'
23 -- parameter of the procedure 'range_code'
24 -- Included checkfile command too.
25 -- 19 Feb 03 apunekar 115.4 Bug#2810178 Removed no. of copies passed to fnd_request.set_print_options
26 -- 25 Feb 03 apunekar 115.6 Bug#2810178 Reverted fix
27 -- 29 May 03 kaverma 115.7 Bug#2920731 Replaced per_all_assignments_f and per_all_people_f
28 -- by secured views per_assignments_f and per_people_f resp from queries.
29 -----------------------------------------------------------------------------
30
31 ------------------------------------------------------------------------
32 -- The SELECT statement in this procedure returns the Person Ids for
33 -- Assignments that require the report process to create an Assignment
34 -- Action.
35 -- Core Payroll recommends the select has minimal restrictions.
36 ------------------------------------------------------------------------
37 procedure range_code
38 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
39 p_sql out nocopy varchar2) is
40 begin
41 --hr_utility.trace_on(null, 'pay_hk_ir56_report');
42 hr_utility.set_location('Start of range_code',1);
43
44 p_sql := 'select distinct person_id ' ||
45 'from per_people_f ppf, ' ||
46 'pay_payroll_actions ppa ' ||
47 'where ppa.payroll_action_id = :payroll_action_id ' ||
48 'and ppa.business_group_id = ppf.business_group_id ' ||
49 'order by ppf.person_id';
50
51 hr_utility.set_location('End of range_code',2);
52 end range_code;
53 ------------------------------------------------------------------------
54 -- This procedure is used to restrict the Assignment Action Creation.
55 -- It calls the procedure that actually inserts the Assignment Actions.
56 -- The cursor selects the assignments that have had any payroll
57 -- processing for the Legal Entity within the Reporting Year.
58 -- The person must not have had any Magtape File produced for the same
59 -- Business Group, Legal Entity and Reporting Year. If they want to
60 -- re-report a person, they must ROLLBACK the magtape first, or use the
61 -- standard Re-try, Rollback payroll process.
62 ------------------------------------------------------------------------
63 procedure assignment_action_code
64 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
65 p_start_person_id in per_all_people_f.person_id%type,
66 p_end_person_id in per_all_people_f.person_id%type,
67 p_chunk in number) is
68
69 v_next_action_id pay_assignment_actions.assignment_action_id%type;
70
71 l_x_hk_archive_message varchar2(100);
72 l_full_or_partial varchar2(15);
73 l_reporting_year varchar2(4);
74 l_legal_entity_id number;
75 l_full_found number;
76 l_error number;
77
78
79 cursor next_action_id is
80 select pay_assignment_actions_s.nextval
81 from dual;
82 --
83 -- Cursor to select the parameters for the current IR56B run.
84 --
85 cursor get_full_or_partial
86 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
87 select pay_core_utils.get_parameter('FULL_OR_PARTIAL', ppa.legislative_parameters),
88 pay_core_utils.get_parameter('REPORTING_YEAR', ppa2.legislative_parameters),
89 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', ppa2.legislative_parameters)
90 from pay_payroll_actions ppa, -- report payroll action
91 pay_payroll_actions ppa2 -- archive payroll action
92 where ppa.payroll_action_id = c_payroll_action_id
93 and ppa2.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa.legislative_parameters);
94 --
95 -- Cursor to check if there are any other FULL runs.
96 --
97 cursor get_full_report_runs
98 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
99 c_reporting_year in varchar2,
100 c_legal_entity_id in number) is
101 select 1
102 from pay_payroll_actions ppa, -- report payroll action
103 pay_payroll_actions ppa2 -- archive payroll action
104 where pay_core_utils.get_parameter('FULL_OR_PARTIAL', ppa.legislative_parameters) = 'FULL'
105 and ppa.action_type = 'X'
106 and ppa.action_status = 'C'
107 and ppa.report_type = 'HK_IR56B_REPORT'
108 and ppa.payroll_action_id <> c_payroll_action_id
109 and ppa2.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa.legislative_parameters)
110 and pay_core_utils.get_parameter('REPORTING_YEAR', ppa2.legislative_parameters) = c_reporting_year
111 and pay_core_utils.get_parameter('LEGAL_ENTITY_ID', ppa2.legislative_parameters) = c_legal_entity_id;
112 --
113 -- Cursor to process all assignments.
114 --
115 cursor process_assignments
116 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
117 c_start_person_id in per_all_people_f.person_id%type,
118 c_end_person_id in per_all_people_f.person_id%type) is
119 select distinct paaf.assignment_id,
120 paa.assignment_action_id,
121 pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa.legislative_parameters) archive_action_id,
122 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', ppa2.legislative_parameters) legal_entity_id
123 from per_assignments_f paaf,
124 per_people_f papf,
125 pay_payroll_actions ppa, -- report payroll action
126 pay_payroll_actions ppa2, -- archive payroll action
127 pay_assignment_actions paa
128 where ppa.payroll_action_id = c_payroll_action_id
129 and papf.person_id between c_start_person_id and c_end_person_id
130 and papf.person_id = paaf.person_id
131 and papf.business_group_id = ppa.business_group_id
132 and ppa2.payroll_action_id = paa.payroll_action_id
133 and paaf.assignment_id = paa.assignment_id
134 and ppa2.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa.legislative_parameters)
135 and ppa2.action_type = 'X'
136 and ppa2.action_status = 'C'
137 --
138 -- if assignment_id was supplied for a partial run only return that
139 -- assignment else return them all for the archive action.
140 --
141 and paa.assignment_id = nvl(pay_core_utils.get_parameter('EMPLOYEE_NAME', ppa.legislative_parameters), paa.assignment_id)
142 --
143 -- don't process locked assignments
144 --
145 and not exists
146 (select locked_action_id
147 FROM pay_action_interlocks pai
148 WHERE pai.locked_action_id = paa.assignment_action_id)
149 ;
150
151 begin
152 --hr_utility.trace_on(null, 'pay_hk_ir56_report');
153 hr_utility.set_location('Start of assignment_action_code '||
154 p_payroll_action_id || ':' ||
155 p_start_person_id || ':' || p_end_person_id,3);
156
157 --
158 -- If user is submitting FULL report then no previous FULL must have been run.
159 -- Also, if run is PARTIAL then a FULL run must have been done before.
160 -- If either of these situations arise flag an error and don't submit the
161 -- report.
162
163 open get_full_or_partial(p_payroll_action_id);
164 fetch get_full_or_partial
165 into l_full_or_partial,
166 l_reporting_year,
167 l_legal_entity_id;
168 if get_full_or_partial%NOTFOUND then
169 l_full_or_partial := null;
170 end if;
171 close get_full_or_partial;
172
173 open get_full_report_runs(p_payroll_action_id,
174 l_reporting_year,
175 l_legal_entity_id);
176 fetch get_full_report_runs into l_full_found;
177 if get_full_report_runs%NOTFOUND then
178 l_full_found := 0;
179 end if;
180 close get_full_report_runs;
181
182 l_error := 0;
183 if l_full_or_partial = 'FULL' then
184 if l_full_found = 1 then
185 l_error := 1;
186 end if;
187 end if;
188
189 if l_full_or_partial = 'PARTIAL' then
190 if l_full_found = 0 then
191 l_error := 2;
192 end if;
193 end if;
194
195 -- loop through the assignments returned from the main cursor and for each
196 -- one that has no errors create the pay_assignment_actions entry.
197
198 if l_error = 0 then
199
200 for process_rec in process_assignments (p_payroll_action_id,
201 p_start_person_id,
202 p_end_person_id)
203 loop
204
205 hr_utility.set_location('Before calling hr_nonrun_asact.insact',4);
206
207 l_x_hk_archive_message :=
208 pay_hk_ir56_report.get_archive_value('X_HK_ARCHIVE_MESSAGE',
209 process_rec.assignment_action_id);
210
211 if l_x_hk_archive_message is null then
212
213 -- get the next assignment action id
214
215 open next_action_id;
216 fetch next_action_id into v_next_action_id;
217 close next_action_id;
218
219 -- create the pay assignment action record. Don't create the action_interlock
220 -- until the report has run (create in the after report trigger) because the
221 -- report also needs to update the archive sheet number (through
222 -- ff_archive_api) and it cannot do this if the assignment action is locked.
223
224 hr_nonrun_asact.insact(v_next_action_id,
225 process_rec.assignment_id,
226 p_payroll_action_id,
227 p_chunk,
228 null);
229 end if;
230
231 hr_utility.set_location('After calling hr_nonrun_asact.insint',4);
232
233 end loop;
234 elsif l_error = 1 then
235 raise_application_error(-20001, 'Cannot submit FULL IR56B report, Full Run Already Submitted') ;
236 elsif l_error = 2 then
237 raise_application_error(-20001, 'Cannot submit Partial IR56B report, No Previous Full Run') ;
238 else
239 raise_application_error(-20001, 'Cannot submit IR56B report') ;
240 end if;
241
242 hr_utility.set_location('End of assignment_action_code',5);
243 --hr_utility.trace_off;
244
245 end assignment_action_code;
246
247 -----------------------------------------------------------------------------
248 -- Submit the report.
249 -----------------------------------------------------------------------------
250 procedure submit_report is
251
252 l_count NUMBER := 0;
253 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
254 l_archive_action_id pay_payroll_actions.payroll_action_id%TYPE;
255 l_full_or_partial varchar2(15);
256 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
257
258 l_number_of_copies NUMBER := 0; /*Reverted fix for 2810178 */
259 l_request_id NUMBER := 0;
260 l_print_return BOOLEAN;
261 l_report_short_name varchar2(30);
262
263 l_formula_id number ;
264
265 l_error_text varchar2(255) ;
266 e_missing_formula exception ;
267 e_submit_error exception ;
268
269 -- Cursor to get the report print options.
270
271 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
272 SELECT printer,
273 print_style,
274 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
275 FROM pay_payroll_actions pact,
276 fnd_concurrent_requests fcr
277 WHERE fcr.request_id = pact.request_id
278 AND pact.payroll_action_id = p_payroll_action_id;
279
280 rec_print_options csr_get_print_options%ROWTYPE;
281
282 -- Cursor to obtain the formula id.
283
284 cursor c_formula (p_formula_name varchar2) is
285 select formula_id
286 from ff_formulas_f
287 where formula_name = p_formula_name
288 and business_group_id is null
289 and legislation_code = 'HK' ;
290
291 begin
292 --hr_utility.trace_on(null, 'pay_hk_ir56_report');
293
294 -- Get all of the parameters needed to submit the report. Parameters defined
295 -- in the concurrent program definition are passed through here by the PAR
296 -- process. End the loop by the exception clause because we don't know
297 -- what order the parameters will be in.
298
299 -- Default the parameters in case they are not found.
300
301 hr_utility.set_location('Start submit_report',1);
302 l_archive_action_id := 0;
303 l_full_or_partial := ' ';
304 l_assignment_id := 0;
305
306 -- Only process if no errors were found in regards to FULL and PARTIAL
307 -- processing.
308
309 begin
310 loop
311
312 l_count := l_count + 1;
313 if pay_mag_tape.internal_prm_names(l_count) =
314 'TRANSFER_PAYROLL_ACTION_ID' then
315 l_payroll_action_id :=
316 to_number(pay_mag_tape.internal_prm_values(l_count));
317 end if;
318
319 if pay_mag_tape.internal_prm_names(l_count) = 'ARCHIVE_ACTION_ID' THEN
320 l_archive_action_id :=
321 to_number(pay_mag_tape.internal_prm_values(l_count));
322 end if;
323
324 if pay_mag_tape.internal_prm_names(l_count) = 'FULL_OR_PARTIAL' THEN
325 l_full_or_partial := pay_mag_tape.internal_prm_values(l_count);
326 end if;
327
328 if pay_mag_tape.internal_prm_names(l_count) = 'EMPLOYEE_NAME' THEN
329 l_assignment_id :=
330 to_number(pay_mag_tape.internal_prm_values(l_count));
331 end if;
332
333 hr_utility.set_location(' prm_names (' || l_count || ') : ' ||
334 pay_mag_tape.internal_prm_names(l_count), 10);
335 hr_utility.set_location(' prm_values (' || l_count || ') : ' ||
336 pay_mag_tape.internal_prm_values(l_count), 10);
337
338 end loop;
339 exception
340 when no_data_found then
341 hr_utility.set_location('No data found',1);
342 null;
343 when value_error then
344 hr_utility.set_location('Value error',1);
345 null;
346 end;
347
348 hr_utility.set_location('submit_report : Parameters obtained',1);
349 hr_utility.set_location(' payroll action id : ' || l_payroll_action_id,1);
350 hr_utility.set_location(' archive action id : ' || l_archive_action_id,1);
351 hr_utility.set_location(' full or partial : ' || l_full_or_partial,1);
352 hr_utility.set_location(' assignment id : ' || l_assignment_id, 2);
353
354 -- Default the number of report copies to 0.
355
356 l_number_of_copies := 0;/*Reverted fix for 2810178 */
357
358 -- Set up the printer options.
359
360 OPEN csr_get_print_options(l_payroll_action_id);
361 FETCH csr_get_print_options INTO rec_print_options;
362 CLOSE csr_get_print_options;
363 /*Reverted fix for 2810178 */
364 l_print_return := fnd_request.set_print_options
365 (printer => rec_print_options.printer,
366 style => rec_print_options.print_style,
367 copies => l_number_of_copies,
368 save_output => hr_general.char_to_bool(rec_print_options.save_output),
369 print_together => 'N');
370
371 l_report_short_name := 'PAYHK56B';
372
373 -- Submit the report
374
375 begin
376 hr_utility.set_location('submit_report : Submit request',3);
377
378 -- Need to supply the parameters with keywords because it's a postscript report
379 -- and the option version=2.0b set in the SRS definition uses a keyword, hence
380 -- all the parameters need to as well.
381 -- Pass in assignment_id if it's been supplied.
382
383 if l_assignment_id <> 0 then
384 l_request_id := fnd_request.submit_request
385 (application => 'PAY',
386 program => l_report_short_name,
387 argument1 => 'P_ARCHIVE_ACTION_ID=' || l_archive_action_id,
388 argument2 => 'P_FULL_OR_PARTIAL=' || l_full_or_partial,
389 argument3 => 'P_PAYROLL_ACTION_ID=' || l_payroll_action_id,
390 argument4 => 'P_ASSIGNMENT_ID=' || l_assignment_id,
391 argument5 => 'BLANKPAGES=NO');
392 else
393 l_request_id := fnd_request.submit_request
394 (application => 'PAY',
395 program => l_report_short_name,
396 argument1 => 'P_ARCHIVE_ACTION_ID=' || l_archive_action_id,
397 argument2 => 'P_FULL_OR_PARTIAL=' || l_full_or_partial,
398 argument3 => 'P_PAYROLL_ACTION_ID=' || l_payroll_action_id,
399 argument4 => 'BLANKPAGES=NO');
400 end if;
401
402 -- If an error submitting report then get message and put to log.
403
404 IF l_request_id = 0 THEN
405 raise e_submit_error;
406 END IF;
407
408 exception
409 when e_submit_error then
410 rollback ;
411 raise_application_error(-20001, 'Unable to submit IR56B report') ;
412 when others then
413 rollback ;
414 raise_application_error(-20001, sqlerrm) ;
415 end;
416
417 -- Set up the details for the formula which will be invoked at the
418 -- end of the procedure. this formula is automatically invoked by the
419 -- PAR process after the magnetic_code procedure (submit_report in our case).
420
421 -- Setup some of the internal table values for the magtape.
422 -- Position 1 is reserved for No of Parameters.
423 -- Position 2 is reserved for formula id.
424 ----------------------------
425
426 ----------------------------
427 -- Get Formula ID for hk_end_submit
428 ----------------------------
429 begin
430 open c_formula ('HK_END_SUBMIT') ;
431 fetch c_formula into l_formula_id ;
432 if c_formula%notfound then
433 close c_formula ;
434 l_error_text := 'HK_END_SUBMIT' ;
435 raise e_missing_formula ;
436 end if ;
437 close c_formula ;
438
439 exception
440 when e_missing_formula then
441 rollback ;
442 raise_application_error(-20001, 'Missing formula: ' || l_error_text) ;
443 when others then
444 rollback ;
445 raise_application_error(-20001, sqlerrm) ;
446 end ;
447
448 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
449 pay_mag_tape.internal_prm_values(1) := '2';
450 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
451 pay_mag_tape.internal_prm_values(2) := l_formula_id;
452
453 hr_utility.set_location('end submit_report', 5);
454 --hr_utility.trace_off;
455
456 end submit_report;
457
458 -----------------------------------------------------------------------------
459 -- Retrieve an archive value.
460 -----------------------------------------------------------------------------
461
462 function get_archive_value
463 (p_archive_name in ff_user_entities.user_entity_name%type,
464 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type)
465 return ff_archive_items.value%type is
466
467 l_value ff_archive_items.value%type;
468 e_no_value_found exception;
469
470 -- cursor to fetch the archive value
471
472 cursor csr_get_archive_value(c_archive_name varchar2,
473 c_assignment_action_id number) is
474 select fai.value
475 from ff_archive_items fai,
476 ff_user_entities fue
477 where fai.context1 = c_assignment_action_id
478 and fai.user_entity_id = fue.user_entity_id
479 and fue.user_entity_name = c_archive_name;
480
481 begin
482
483 hr_utility.set_location('Start of get archive value ',1);
484
485 open csr_get_archive_value(p_archive_name,
486 p_assignment_action_id);
487 fetch csr_get_archive_value into l_value;
488
489 if csr_get_archive_value%notfound then
490 l_value := null;
491 end if;
492
493 close csr_get_archive_value;
494
495 hr_utility.set_location('End of get archive value ',2);
496
497 return(l_value);
498
499 exception
500 when others then
501 return (null);
502
503 end get_archive_value;
504
505 -----------------------------------------------------------------------------
506 end pay_hk_ir56_report;
[Home] [Help]