1 package body pay_kr_hia_pkg as
2 /* $Header: pykrhia.pkb 120.3 2006/01/05 03:40:50 pdesu noship $ */
3 --
4 -- Constants
5 --
6 l_package varchar2(31) := ' pay_kr_hia_pkg.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- Global Variables
10 --
11 TYPE t_pact IS RECORD(
12 payroll_action_id NUMBER,
13 report_type pay_payroll_actions.report_type%TYPE,
14 report_qualifier pay_payroll_actions.report_qualifier%TYPE,
15 report_category pay_payroll_actions.report_category%TYPE,
16 business_group_id NUMBER,
17 effective_date date,
18 bp_hi_number varchar2(250), --3506171
19 reported_date date,
20 year_start_date date );
21
22 g_pact t_pact;
23
24 --
25 --Bug 2931128 . This global value stores defined balance id
26 --for defined balance HI_PREM_EE_WO_ADJ_ASG_MTD_MTH
27
28 g_dbl_id_hi_prem pay_defined_balances.defined_balance_id%type;
29
30 --------------------------------------------------------------------------------+
31 -- Range cursor returns the ids of the assignments to be archived
32 --------------------------------------------------------------------------------+
33 PROCEDURE range_code(
34 p_payroll_action_id IN NUMBER,
35 p_sqlstr OUT NOCOPY VARCHAR2)
36 IS
37 l_proc_name VARCHAR2(100) := l_package || 'range_code';
38 BEGIN
39
40 if g_debug then
41 hr_utility.set_location(l_proc_name, 10);
42 end if;
43
44 p_sqlstr :=
45 'SELECT DISTINCT person_id
46 FROM per_people_f ppf,
47 pay_payroll_actions ppa
48 WHERE ppa.payroll_action_id = :payroll_action_id
49 AND ppa.business_group_id = ppf.business_group_id
50 ORDER BY ppf.person_id';
51 if g_debug then
52 hr_utility.set_location(l_proc_name, 20);
53 end if;
54 end range_code;
55
56 --------------------------------------------------------------------------------
57 -- Initialization Code
58 --------------------------------------------------------------------------------
59 procedure initialization_code(p_payroll_action_id in number)
60 is
61
62 l_proc_name VARCHAR2(100) := l_package || 'initialization_code';
63
64 begin
65
66 if g_debug then
67 hr_utility.set_location(l_proc_name, 10);
68 end if;
69
70 IF g_pact.payroll_Action_id is null then
71 select ppa.payroll_action_id,
72 ppa.report_type,
73 ppa.report_qualifier,
74 ppa.report_category,
75 ppa.business_group_id,
76 ppa.effective_date,
77 pay_core_utils.get_parameter('BP_HI_NUMBER',ppa.legislative_parameters) bp_hi_number, --3506171
78 fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',ppa.legislative_parameters)) reported_date,
79 trunc(fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',ppa.legislative_parameters)),'YYYY') year_start_date
80 into g_pact
81 from pay_payroll_actions ppa
82 where ppa.payroll_action_id = p_payroll_action_id;
83 END IF;
84
85 if g_debug then
86 hr_utility.set_location(l_proc_name, 20);
87 end if;
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 hr_utility.set_location('Error in initialization code ',10);
92 RAISE;
93 end initialization_code;
94
95 --------------------------------------------------------------------------------+
96 -- Creates assignment action id for all the valid person id's in
97 -- the range selected by the Range code.
98 --------------------------------------------------------------------------------+
99 PROCEDURE assignment_action_code(
100 p_payroll_action_id IN NUMBER,
101 p_start_person_id IN NUMBER,
102 p_end_person_id IN NUMBER,
103 p_chunk_number IN NUMBER)
104 IS
105 l_proc_name VARCHAR2(100) := l_package || 'assignment_action_code';
106 l_locking_action_id NUMBER;
107 CURSOR csr_asg -- 3506171
108 IS
109 SELECT DISTINCT asg.assignment_id,
110 asg.establishment_id
111 FROM per_assignments_f asg,
112 pay_payroll_actions xppa,
113 hr_organization_units hou1,
114 hr_organization_information hoi1
115 WHERE xppa.payroll_action_id = p_payroll_action_id
116 and hou1.business_group_id = g_pact.business_group_id --3506171
117 and hoi1.organization_id = hou1.organization_id
118 and hoi1.org_information_context = 'KR_HI_INFORMATION'
119 and hoi1.org_information1 = g_pact.bp_hi_number
120 AND asg.business_group_id = g_pact.business_group_id
121 AND asg.establishment_id = hou1.organization_id
122 AND asg.person_id BETWEEN p_start_person_id AND p_end_person_id
123 -- BUG 3453612
124 AND xppa.effective_date between asg.effective_start_date and asg.effective_end_date
125 AND NOT EXISTS (SELECT NULL
126 FROM pay_payroll_actions ppa4,
127 pay_assignment_actions paa4
128 WHERE paa4.assignment_id = asg.assignment_id
129 AND paa4.source_action_id IS NULL
130 AND ppa4.payroll_action_id = paa4.payroll_action_id
131 AND ppa4.action_type = 'X'
132 AND ppa4.report_type = 'HIA'
133 AND ppa4.report_qualifier = 'KR'
134 AND ppa4.report_category = 'A'
135 AND trunc(ppa4.effective_date, 'YYYY') = trunc(xppa.effective_date, 'YYYY')
136 UNION ALL -- Bug : 4859742
137 (SELECT NULL
138 FROM per_people_extra_info pei
139 WHERE pei.person_id = asg.person_id
140 AND pei.pei_information6 = 'Y'
141 AND pei.information_type = 'PER_KR_HEALTH_INSURANCE_INFO'))
142 AND EXISTS (SELECT NULL
143 FROM pay_payroll_actions ppa,
144 pay_assignment_actions paa
145 WHERE ppa.effective_date BETWEEN
146 trunc(fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',xppa.legislative_parameters)), 'YYYY')
147 AND fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',xppa.legislative_parameters))
148 AND ppa.action_type in ('R','Q')
149 AND paa.action_status = 'C'
150 AND paa.payroll_action_id = ppa.payroll_action_id
151 AND paa.source_action_id IS NULL
152 AND paa.assignment_id = asg.assignment_id);
153 BEGIN
154 if g_debug then
155 hr_utility.set_location(l_proc_name, 10);
156 end if;
157
158 initialization_code(p_payroll_action_id);
159
160 FOR l_asg IN csr_asg -- 3506171
161 LOOP
162 SELECT pay_assignment_actions_s.nextval
163 INTO l_locking_action_id
164 FROM dual;
165 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
166 assignid => l_asg.assignment_id,
167 pactid => p_payroll_action_id,
168 chunk => p_chunk_number,
169 greid => l_asg.establishment_id,
170 prepayid => null,
171 status => 'U');
172 END LOOP;
173
174 if g_debug then
175 hr_utility.set_location(l_proc_name, 20);
176 end if;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 hr_utility.set_location('Error in assignment action code ',10);
181 RAISE;
182 END assignment_action_code;
183 --------------------------------------------------------------------------------+
184 -- Archives item
185 --------------------------------------------------------------------------------+
186 PROCEDURE archive_item
187 ( p_item IN ff_user_entities.user_entity_name%TYPE,
188 p_context1 IN pay_assignment_actions.assignment_action_id%TYPE,
189 p_value IN OUT NOCOPY ff_archive_items.value%TYPE)
190 IS
191 CURSOR csr_get_user_entity_id(c_user_entity_name IN VARCHAR2)
192 IS
193 SELECT fue.user_entity_id,
194 dbi.data_type
195 FROM ff_user_entities fue,
196 ff_database_items dbi
197 WHERE user_entity_name = c_user_entity_name
198 AND fue.user_entity_id = dbi.user_entity_id;
199 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
200 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
201 l_data_type ff_database_items.data_type%TYPE;
202 l_object_version_number ff_archive_items.object_version_number%TYPE;
203 l_some_warning BOOLEAN;
204 BEGIN
205 if g_debug then
206 hr_utility.set_location('Entering : archive_item',1);
207 end if;
208
209 OPEN csr_get_user_entity_id (p_item);
210 FETCH csr_get_user_entity_id INTO l_user_entity_id,l_data_type;
211 IF csr_get_user_entity_id%found THEN
212 CLOSE csr_get_user_entity_id;
213 ff_archive_api.create_archive_item
214 (p_validate => false -- boolean in default
215 ,p_archive_item_id => l_archive_item_id -- NUMBER out
216 ,p_user_entity_id => l_user_entity_id -- NUMBER in
217 ,p_archive_value => p_value -- VARCHAR2 in
218 ,p_archive_type => 'AAP' -- VARCHAR2 in default
219 ,p_action_id => p_context1 -- NUMBER in
220 ,p_legislation_code => 'KR' -- VARCHAR2 in
221 ,p_object_version_number => l_object_version_number -- NUMBER out
222 ,p_some_warning => l_some_warning); -- boolean out
223 ELSE
224 CLOSE csr_get_user_entity_id;
225
226 if g_debug then
227 hr_utility.set_location('User entity not found :'||p_item,20);
228 end if;
229
230 END IF;
231
232 if g_debug then
233 hr_utility.set_location('Leaving : archive_item',1);
234 end if;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 IF csr_get_user_entity_id%isopen THEN
239 CLOSE csr_get_user_entity_id;
240 hr_utility.set_location('closing..',117);
241 END IF;
242 hr_utility.set_location('Error in archive_item',20);
243 RAISE;
244 END archive_item;
245
246 --------------------------------------------------------------------------------+
247 -- Archive code selects the items to be archived.
248 --------------------------------------------------------------------------------+
249 PROCEDURE archive_code(
250 p_assignment_action_id IN NUMBER,
251 p_effective_date IN DATE)
252 IS
253 l_proc_name VARCHAR2(100) := l_package || 'archive_code';
254 l_assignment_id NUMBER;
255 l_payroll_id NUMBER;
256 l_establishment_id number; -- 3506171
257
258 --Bug 2931128
259 --
260 l_arch_val ff_archive_items.value%type;
261
262 TYPE t_arch_rec IS RECORD(item VARCHAR2(50)
263 ,value VARCHAR2(1000));
264 TYPE t_arch_tab IS TABLE OF t_arch_rec INDEX BY BINARY_INTEGER;
265 l_arch_tab t_arch_tab;
266
267 -- Bug 4199014
268 type t_assact_tbl is table of number index by binary_integer ;
269 type t_ppa_mth_tbl is table of number index by binary_integer ;
270 l_assact_tbl t_assact_tbl ;
271 l_ppa_mth_tbl t_ppa_mth_tbl ;
272 l_no_mths_prem_paid number ;
273 l_last_month_found number ;
274 l_each_row number ;
275 -- End of 4199014
276
277 CURSOR csr_get_context_values
278 IS
279 SELECT paa.assignment_id,
280 pa.payroll_id,
281 pa.establishment_id -- 3506171
282 FROM per_assignments_f pa,
283 pay_assignment_actions paa
284 WHERE paa.assignment_action_id = p_assignment_action_id
285 AND pa.assignment_id = paa.assignment_id
286 AND g_pact.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date;
287
288 --employee details cursor
289 CURSOR csr_employee_details
290 IS
291 SELECT pp.last_name || pp.first_name employee_name,
292 pp.national_identifier national_identifier,
293 pei.pei_information1 hi_number,
294 nvl(pei.pei_information4,pei.pei_information2) qualified_date,
295 hhoi.org_information1 business_place_code,
296 NULL business_place_unit
297 FROM per_people_extra_info pei,
298 per_people_f pp,
299 per_assignments_f pa,
300 pay_assignment_actions paa,
301 pay_payroll_actions ppa,
302 hr_organization_information hhoi,
303 per_periods_of_service pds
304 WHERE ppa.payroll_action_id = g_pact.payroll_action_id
305 AND paa.payroll_action_id = ppa.payroll_action_id
306 AND pa.assignment_id = paa.assignment_id
307 AND pp.person_id = pa.person_id
308 AND pei.person_id(+) = pp.person_id
309 AND pei.information_type(+) = 'PER_KR_HEALTH_INSURANCE_INFO'
310 AND paa.tax_unit_id = hhoi.organization_id
311 AND hhoi.org_information_context = 'KR_HI_INFORMATION'
312 AND pp.person_id = pds.person_id
313 AND NVL(pds.actual_termination_date,ppa.effective_date) BETWEEN pa.effective_start_date AND pa.effective_end_date
314 AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
315 AND pp.business_group_id = g_pact.business_group_id
316 AND paa.assignment_action_id = p_assignment_action_id;
317 --
318 --Bug 2931128
319 --
320 cursor csr_get_dbl_id (p_balance_name varchar2, p_dimension_name varchar2 )
321 is
322 select defined_balance_id
323 from pay_balance_types pbt ,
324 pay_balance_dimensions pbd ,
325 pay_defined_balances pdb
326 where pbt.balance_name =p_balance_name
327 and pbt.legislation_code ='KR'
328 and pbd.dimension_name =p_dimension_name
329 and pbd.legislation_code ='KR'
330 and pbt.balance_type_id =pdb.balance_type_id
331 and pbd.balance_dimension_id =pdb.balance_dimension_id ;
332
333 /* Bug 4199014: Performance update - Removed check for balance value (call to PAY_BALANCE_PKG.GET_VALUE)
334 from the WHERE clause. Changed the SELECT clause, added extra WHERE predicate
335 (PAA.ACTION_STATUS).
336 */
337 cursor csr_hi_no_of_mths_prem_paid(p_assact_id pay_assignment_actions.assignment_action_id%type ,
338 p_start_date date ,
339 p_end_date date )
340 is
341 select paa.assignment_action_id
342 ,to_number(to_char(ppa.effective_date,'MM') )
343 from pay_assignment_actions xpaa
344 ,pay_payroll_actions xppa
345 ,pay_payroll_actions ppa
346 ,pay_assignment_actions paa
347 ,pay_run_types_f prt
348 where xpaa.assignment_action_id = p_assact_id
349 and xppa.payroll_action_id = xpaa.payroll_action_id
350 and xpaa.assignment_id = paa.assignment_id
351 and ppa.payroll_action_id = paa.payroll_action_id
352 and paa.action_status = 'C' -- Bug 4199014
353 and ppa.action_type in ('B', 'I', 'V', 'R', 'Q')
354 and prt.run_type_id = paa.run_type_id
355 and prt.run_type_name = 'MTH'
356 and ppa.effective_date between prt.effective_start_date
357 and prt.effective_end_date
358 and prt.legislation_code = 'KR'
359 and ppa.effective_date between p_start_date
360 and p_end_date
361 and xppa.business_group_id = ppa.business_group_id
362 order by 2 ; -- IMPORTANT: Logic below depends on this ORDER BY
363 -- End of 4199014
364 -- Bug 3438946
365 Cursor csr_last_year_asg_action
366 IS
367 SELECT paa.assignment_action_id
368 FROM pay_assignment_actions paa,
369 per_assignments_f pa,
370 pay_payroll_actions ppa
371 WHERE pa.assignment_id = l_assignment_id
372 AND paa.assignment_id = l_assignment_id
373 AND paa.assignment_id = pa.assignment_id
374 AND ppa.payroll_action_id = paa.payroll_action_id
375 AND ppa.effective_date between trunc(p_effective_date, 'YYYY') and (add_months(trunc(p_effective_date,'YYYY'),12)-1)
376 AND ppa.action_type in ('B', 'V', 'R', 'Q', 'I')
377 AND paa.action_status = 'C'
378 AND p_effective_date between pa.effective_start_date and pa.effective_end_date
379 ORDER BY paa.action_sequence desc;
380
381 l_last_year_assignment_action NUMBER(15);
382 l_defined_balance_id NUMBER(9);
383
384 BEGIN
385 if g_debug then
386 hr_utility.set_location(l_proc_name, 10);
387 end if;
388 --
389 OPEN csr_get_context_values;
390 FETCH csr_get_context_values INTO l_assignment_id,l_payroll_id,l_establishment_id; -- 3506171
391 CLOSE csr_get_context_values;
392
393 if g_debug then
394 hr_utility.set_location(l_proc_name, 20);
395 end if;
396 -- Bug 3438946
397 open csr_last_year_asg_action;
398 fetch csr_last_year_asg_action into l_last_year_assignment_action;
399 close csr_last_year_asg_action;
400 -- End of bug 3438946
401 --
402 pay_archive.g_context_values.name(1) := 'BUSINESS_GROUP_ID';
403 pay_archive.g_context_values.value(1) := to_char(g_pact.business_group_id);
404 pay_archive.g_context_values.name(2) := 'PAYROLL_ID';
405 pay_archive.g_context_values.value(2) := to_char(l_payroll_id);
406 pay_archive.g_context_values.name(3) := 'PAYROLL_ACTION_ID';
407 pay_archive.g_context_values.value(3) := to_char(g_pact.payroll_action_id);
408 pay_archive.g_context_values.name(4) := 'ASSIGNMENT_ID';
409 pay_archive.g_context_values.value(4) := to_char(l_assignment_id);
410 pay_archive.g_context_values.name(5) := 'ASSIGNMENT_ACTION_ID';
411 -- Bug 3438946
412 pay_archive.g_context_values.value(5) := to_char(l_last_year_assignment_action);
413 --
414 pay_archive.g_context_values.name(6) := 'DATE_EARNED';
415 pay_archive.g_context_values.value(6) := fnd_date.date_to_canonical(g_pact.effective_date);
416 pay_archive.g_context_values.name(7) := 'TAX_UNIT_ID';
417 pay_archive.g_context_values.value(7) := to_char(l_establishment_id); -- 3506171
418 pay_archive.g_context_values.sz := 7;
419
420 if g_debug then
421 hr_utility.set_location(l_proc_name, 30);
422 end if;
423
424 /* Start of Archiving Employee Details */
425 -----------------------------------------+
426 -- note : the fetch order FROM the cursor
427 -- should be same as the order
428 -- defined in the pl/sql table below
429 -----------------------------------------+
430 l_arch_tab.delete;
431 l_arch_tab(1).item := 'X_KR_HIA_EMPLOYEE_NAME';
432 l_arch_tab(2).item := 'X_KR_HIA_REGISTRATION_NUMBER';
433 l_arch_tab(3).item := 'X_KR_HIA_HI_NUMBER';
434 l_arch_tab(4).item := 'X_KR_HIA_QUALIFIED_DATE';
435 l_arch_tab(5).item := 'X_KR_HIA_BUSINESS_PLACE_CODE';
436 l_arch_tab(6).item := 'X_KR_HIA_BUSINESS_PLACE_UNIT';
437 -- Bug 3438946
438 l_arch_tab(8).item := 'X_HI_PREM_EE_WO_ADJ';
439 l_arch_tab(9).item := 'X_EARNINGS_SUBJ_HI';
440 l_arch_tab(10).item := 'X_HI_WORKING_MONTHS';
441 --
442 -- Bug 2931128
443 --
444 l_arch_tab(7).item := 'X_KR_HI_NUM_OF_MTHS_PREM_PAID';
445
446 if g_dbl_id_hi_prem is null then
447 open csr_get_dbl_id('HI_PREM_EE_WO_ADJ', '_ASG_MTD_MTH');
448 fetch csr_get_dbl_id into g_dbl_id_hi_prem ;
449 close csr_get_dbl_id;
450 end if ;
451
452 /* Bug 4199014: (Performance update)
453 csr_hi_no_of_mths_prem_paid now gets only the
454 assignment actions corresponding to a monthly
455 run. IT NO LONGER FILTERS THE DATA BASED ON THE
456 VALUE OF BALANCE HI_PREM_EE_WO_ADJ_ASG_MTD_MTH.
457 We place this check after the cursor's execution.
458 */
459
460 l_assact_tbl.delete ;
461 l_ppa_mth_tbl.delete ;
462
463 open csr_hi_no_of_mths_prem_paid(p_assignment_action_id ,
464 g_pact.year_start_date ,
465 g_pact.reported_date ) ;
466 fetch csr_hi_no_of_mths_prem_paid bulk collect into l_assact_tbl, l_ppa_mth_tbl ;
467 close csr_hi_no_of_mths_prem_paid;
468
469 l_no_mths_prem_paid := 0 ;
470 l_last_month_found := 0 ;
471
472 -- This loop finds out DISTINCT months for which the balance was non-zero.
473 -- The ORDER BY on month number (ASC) in cursor CSR_HI_NO_OF_MTHS_PREM_PAID is used in the loop.
474 --
475 l_each_row := l_assact_tbl.first ;
476 loop
477 exit when l_each_row is null ;
478 if l_ppa_mth_tbl(l_each_row) = l_last_month_found then
479 -- This month has already been included in l_no_mths_prem_paid; do nothing
480 null ;
481 elsif pay_balance_pkg.get_value(g_dbl_id_hi_prem, l_assact_tbl(l_each_row) ) > 0 then
482 l_last_month_found := l_ppa_mth_tbl(l_each_row) ; -- Now this is the latest month accounted for
483 l_no_mths_prem_paid := l_no_mths_prem_paid + 1 ;
484 end if ;
485 l_each_row := l_assact_tbl.next(l_each_row) ;
486 end loop ;
487 --
488 l_arch_tab(7).value := l_no_mths_prem_paid ;
489 -- End of 4199014
490 --
491 --End of changes for Bug 2931128
492 --------------------------------------------------------------
493 -- Bug 3438946
494 open csr_get_dbl_id('HI_PREM_EE_WO_ADJ', '_ASG_YTD');
495 fetch csr_get_dbl_id into l_defined_balance_id;
496 if csr_get_dbl_id%notfound then
497 raise no_data_found;
498 end if;
499 close csr_get_dbl_id;
500
501 l_arch_tab(8).value := pay_balance_pkg.get_value(l_defined_balance_id, l_last_year_assignment_action);
502 --
503 open csr_get_dbl_id('EARNINGS_SUBJ_HI', '_ASG_YTD');
504 fetch csr_get_dbl_id into l_defined_balance_id;
505 if csr_get_dbl_id%notfound then
506 raise no_data_found;
507 end if;
508 close csr_get_dbl_id;
509
510 l_arch_tab(9).value := pay_balance_pkg.get_value(l_defined_balance_id, l_last_year_assignment_action);
511 --
512 l_arch_tab(10).value := pay_balance_pkg.run_db_item('HI_WORKING_MONTHS', null, 'KR');
513 --
514 --End of changes for Bug 3438946
515 --------------------------------------------------------------
516 if g_debug then
517 hr_utility.set_location('Entering : Archiving emp Details ',1);
518 hr_utility.set_location('Assignments action id is '||p_assignment_action_id,2);
519 end if;
520
521 OPEN csr_employee_details ;
522 LOOP
523 FETCH csr_employee_details
524 INTO l_arch_tab(1).value,
525 l_arch_tab(2).value,
526 l_arch_tab(3).value,
527 l_arch_tab(4).value,
528 l_arch_tab(5).value,
529 l_arch_tab(6).value;
530 EXIT WHEN csr_employee_details%NOTFOUND;
531
532 if g_debug then
533 hr_utility.set_location('Creating Archive Item ',3);
534 end if;
535
536 FOR i IN 1..l_arch_tab.count
537 LOOP
538 archive_item(p_item => l_arch_tab(i).item
539 ,p_context1 => p_assignment_action_id
540 ,p_value => l_arch_tab(i).value);
541 END LOOP;
542 END LOOP;
543 CLOSE csr_employee_details;
544
545 if g_debug then
546 hr_utility.set_location('Exiting : Archiving emp Details ',200);
547 end if;
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 hr_utility.set_location('Error in archiving emp details ',10);
552 RAISE;
553 /* End of Archiving Employee Details */
554 END archive_code;
555 --------------------------------------------------------------------------
556 -- This Procedure Actually Calls the Health Insurance Adjustment Report.
557 --------------------------------------------------------------------------
558 FUNCTION SUBMIT_REPORT
559 RETURN NUMBER
560 IS
561 l_count NUMBER := 0;
562 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
563 l_bp_hi_number hr_organization_information.org_information1%type := NULL; --3506171
564 l_reported_date DATE := NULL;
565 l_number_of_copies NUMBER := 0;
566 l_request_id NUMBER := 0;
567 l_print_return BOOLEAN;
568 l_report_short_name varchar2(30);
569 l_formula_id number;
570 l_error_text varchar2(255);
571 e_missing_formula exception;
572 e_submit_error exception;
573 -- Cursor to get the report print options.
574 CURSOR csr_get_print_options(p_payroll_action_id NUMBER)
575 IS
576 SELECT printer,
577 print_style,
578 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
579 FROM pay_payroll_actions pact,
580 fnd_concurrent_requests fcr
581 WHERE fcr.request_id = pact.request_id
582 AND pact.payroll_action_id = p_payroll_action_id;
583 rec_print_options csr_get_print_options%ROWTYPE;
584 BEGIN
585 -- Get all of the parameters needed to submit the report. Parameters defined
586 -- in the concurrent program definition are passed through here by the PAR
587 -- process. End the loop by the exception clause because we don't know
588 -- what order the parameters will be in.
589 -- Default the parameters in case they are not found.
590
591 if g_debug then
592 hr_utility.set_location('Submit report called',1);
593 hr_utility.set_location('payroll action id'||l_payroll_action_id,1);
594 end if;
595
596 BEGIN
597 LOOP
598 l_count := l_count + 1;
599 IF pay_mag_tape.internal_prm_names(l_count) = 'TRANSFER_PAYROLL_ACTION_ID' THEN
600 l_payroll_action_id := to_number(pay_mag_tape.internal_prm_values(l_count));
601 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'REPORTED_DATE' THEN
602 l_reported_date := fnd_date.canonical_to_date(pay_mag_tape.internal_prm_values(l_count));
603 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'BP_HI_NUMBER' THEN -- 3506171
604 l_bp_hi_number := pay_mag_tape.internal_prm_values(l_count);
605 END IF;
606 END LOOP;
607 EXCEPTION
608 WHEN NO_DATA_FOUND THEN
609 hr_utility.set_location('No data found',1);
610 NULL;
611 WHEN VALUE_ERROR THEN
612 hr_utility.set_location('Value error',1);
613 NULL;
614 END;
615 -- Default the number of report copies to 0.
616 l_number_of_copies := 0;
617 -- Set up the printer options.
618 OPEN csr_get_print_options(l_payroll_action_id);
619 FETCH csr_get_print_options INTO rec_print_options;
620 CLOSE csr_get_print_options;
621
622 if g_debug then
623 hr_utility.set_location('fnd_request.set_print_options',1);
624 end if;
625
626 l_print_return := fnd_request.set_print_options
627 (printer => rec_print_options.printer,
628 style => rec_print_options.print_style,
629 copies => l_number_of_copies,
630 save_output => hr_general.char_to_bool(rec_print_options.save_output),
631 print_together => 'N');
632 l_report_short_name := 'PAYKRHCL';
633 -- Submit the report
634 BEGIN
635
636 if g_debug then
637 hr_utility.set_location('fnd_request.submit_request',1);
638 end if;
639
640 l_request_id := fnd_request.submit_request
641 (application => 'PAY',
642 program => l_report_short_name,
643 argument1 => 'P_PAYROLL_ACTION_ID='||l_payroll_action_id,
644 argument2 => 'P_BP_HI_NUMBER='||l_bp_hi_number, --3506171
645 argument3 => 'P_REPORTED_DATE='||l_reported_date);
646 -- If an error submitting report then get message and put to log.
647
648 if g_debug then
649 hr_utility.set_location('l_request_id : '||l_request_id,1);
650 end if;
651
652 IF l_request_id = 0 THEN
653 RAISE e_submit_error;
654 END IF;
655 RETURN l_request_id;
656 EXCEPTION
657 WHEN e_submit_error THEN
658 ROLLBACK;
659 RAISE_APPLICATION_ERROR(-20001, 'Could Not submit report');
660 RETURN 0;
661 WHEN OTHERS THEN
662 ROLLBACK;
663 RAISE_APPLICATION_ERROR(-20001, sqlerrm);
664 RETURN 0;
665 END;
666 END SUBMIT_REPORT;
667
668
669 END pay_kr_hia_pkg;
[Home] [Help]