1 PACKAGE BODY Pay_Mag_Utils AS
2 /* $Header: pymagutl.pkb 120.1 2005/10/10 12:03:21 meshah noship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1993 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 : pay_mag_utils
22
23 Description : Contains procedures and functions used by magnetic reports.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 10-OCT-96 ATAYLOR 40.0 Created.
32 The messages will have to be replaced
33 as and when tape becomes unfrozen.
34 01-NOV-96 GPERRY 40.1 Added Insert_Lookup for seeding
35 1099R relevant lookups. Added Write as
36 a debugging tool for use when testing 1099R
37 reports on a site or internally.
38 06-NOV-96 GPERRY 40.2 Added function date_earned.
39 13-NOV-96 GPERRY 40.3 Removed function call to hr_api as not
40 valid on QA database.
41 17-DEC-96 HEKIM 40.4 Fixed block definition structure viewer
42 26-FEB-97 HEKIM 40.5 Changed message name from HR_ to PAY_
43 20-MAR-97 HEKIM 40.6 Added udf_Exists and Delete_udf.
44 14-JUL-97 HEKIM 40.7 Change message numbers to 5003x range
45 29/07/97 mfender 110.2 Corected untranslatable date formats
46 08-APR-99 DJOSHI Verfied and converted for Canonical
47 Complience of Date
48 18-jun-1999 achauhan 115.2 replaced dbms_output with
49 hr_utility.trace
50 17-aug-1999 rthakur 115.3 Added function get_parameter.
51 07-jan-2000 vmehta 115.4 Modified function get_parameter
52 to take care of the condition
53 where the second parameter is
54 passed in as null. bug 1069642
55 18-jan-2002 fusman 115.5 Changed the default date from 01/01/1996
56 to 01/01/1901 for p_creation_date,p_last_update_date
57 and p_effective_date.Also added dbdrv commands.
58 02-jul-2002 fusman 115.6 Bug:2296797 Added legislation code.
59 25-Apr-2005 sackumar 115.8 Bug 4055762 introduce ltrim,rtrim function
60 in get_parameter function.
61 */
62
63 -----------------------------------------------------------------------------
64 -- Name
65 -- Write
66 -- Purpose
67 -- Provides debugging information that can be picked up when running the
68 -- concurrent process from SRS. Write output to the TEST_1099R table that
69 -- should exist with the following columns.
70 -- SEQUENCE NUMBER
71 -- TEXT VARCHAR2(240)
72 -- Due to the fact that the debugging table will not be shipped as part
73 -- of the installation the write information will be editted out.
74 -- Arguments
75 -- p_action denotes action type (I,D) Insert or Delete
76 -- p_sequence denotes sequence number of insertion
77 -- p_message denotes message to be written to table
78 -- p_write_mode denotes whether info is written to table (default is FALSE)
79 -- Notes
80 -- For a non-test site this should never write to the TEST_1099R table.
81 -----------------------------------------------------------------------------
82 PROCEDURE Write (p_action IN VARCHAR2,
83 p_sequence IN NUMBER DEFAULT NULL,
84 p_message IN VARCHAR2 DEFAULT NULL,
85 p_write_mode IN BOOLEAN DEFAULT TRUE ) IS
86 BEGIN
87 --
88 IF upper(p_action) = 'D' THEN
89 --
90 -- Clear down table
91 --
92 --deLETE FROM TEST_1099R;
93 --
94 NULL;
95 --
96 ELSIF upper(p_action) = 'I' THEN
97 --
98 IF p_write_mode THEN
99 --
100 -- This if condition decides whether we actually write to the
101 -- the table or not. Obviously performance-wise it is better
102 -- to not bother but for debgugging purposes this is quite
103 -- useful.
104 --
105 --inSERT INTO TEST_1099R
106 --VALUES (p_sequence,p_message);
107 --
108 NULL;
109 --
110 END IF;
111 --
112 END IF;
113 --
114 -- Commit should be editted out as this will write records to the database
115 -- which we do not want to do unless we are testing the report.
116 --
117 -- COMMIT;
118 --
119 END Write;
120 --
121 --
122 -----------------------------------------------------------------------------
123 -- Name
124 -- Date_Earned
125 -- Purpose
126 -- Checks if dates are valid for the assignment effective dates and the
127 -- person effective dates.
128 -- Arguments
129 --
130 -- Notes
131 -- Used so that only one call is made to the database when comparing the
132 -- dates of the payroll_action and the report_date.
133 -----------------------------------------------------------------------------
134 FUNCTION Date_Earned ( p_report_date IN DATE,
135 p_assignment_id IN NUMBER,
136 p_ass_effective_start_date IN DATE,
137 p_ass_effective_end_date IN DATE,
138 p_per_effective_start_date IN DATE,
139 p_per_effective_end_date IN DATE) RETURN NUMBER IS
140 --
141 l_max_assignment_date date;
142 --
143 BEGIN
144 --
145 -- Bring back maximum effective end date for the assignment we are
146 -- dealing with.
147 --
148 SELECT MAX(paf.effective_end_date)
149 INTO l_max_assignment_date
150 FROM per_assignments_f paf
151 WHERE paf.assignment_id = p_assignment_id;
152 --
153 IF l_max_assignment_date < p_report_date AND
154 l_max_assignment_date >= p_ass_effective_start_date AND
155 l_max_assignment_date <= p_ass_effective_end_date AND
156 l_max_assignment_date >= p_per_effective_start_date AND
157 l_max_assignment_date <= p_per_effective_end_date THEN
158 --
159 -- Dates are valid for this person so return true
160 --
161 RETURN 1;
162 --
163 ELSIF p_report_date <= l_max_assignment_date AND
164 p_report_date >= p_ass_effective_start_date AND
165 p_report_date <= p_ass_effective_end_date AND
166 p_report_date >= p_per_effective_start_date AND
167 p_report_date <= p_per_effective_end_date THEN
168 --
169 RETURN 1;
170 --
171 ELSE
172 --
173 RETURN 0;
174 --
175 END IF;
176 --
177 END Date_Earned;
178 --
179 --
180 --
181 -----------------------------------------------------------------------------
182 -- Name
183 -- Lookup_Formula
184 -- Purpose
185 -- Given a formula name it returns its id.
186 -- Arguments
187 -- Notes
188 -----------------------------------------------------------------------------
189 --
190 FUNCTION Lookup_Formula ( p_session_date DATE,
191 p_business_group_id NUMBER,
192 p_legislation_code VARCHAR2,
193 p_formula_name VARCHAR2) RETURN NUMBER IS
194 --
195 -- Local variables
196 --
197 l_formula_id NUMBER;
198 --
199 -- Cursor to get the formula id for the specified formula.
200 --
201 CURSOR csr_formula IS
202 SELECT formula_id
203 FROM ff_formulas_f
204 WHERE legislation_code = p_legislation_code
205 AND formula_name = UPPER(p_formula_name)
206 AND p_session_date BETWEEN effective_start_date
207 AND effective_end_date;
208 --
209 BEGIN
210 --
211 OPEN csr_formula;
212 --
213 hr_utility.set_location('pay_mag_utils.lookup_formula',1);
214 --
215 FETCH csr_formula INTO l_formula_id;
216 --
217 hr_utility.set_location('pay_mag_utils.lookup_formula',2);
218 --
219 -- If formula not found, then raise exception in calling package.
220 --
221 IF csr_formula%NOTFOUND THEN
222 --
223 CLOSE csr_formula;
224 --
225 hr_utility.set_message(801,'PAY_50030_1099R_NO_FF');
226 RAISE hr_utility.hr_error;
227 --
228 ELSE
229 --
230 CLOSE csr_formula;
231 RETURN (l_formula_id);
232 --
233 END IF;
234 --
235 END Lookup_Formula;
236 --
237 --
238 -- --------------------------------------------------------------------------
239 -- Name
240 -- Lookup_Format
241 -- Purpose
242 -- Find the format to be applied when generating the report.
243 -- Arguments
244 -- p_period_end
245 -- p_report_type
246 -- p_state
247 -- Notes
248 -- --------------------------------------------------------------------------
249 --
250 FUNCTION Lookup_Format (p_period_end IN DATE,
251 p_report_type IN VARCHAR2,
252 p_state IN VARCHAR2) RETURN VARCHAR2 IS
253 --
254 CURSOR csr_format IS
255 SELECT report_format
256 FROM pay_report_format_mappings_f
257 WHERE report_type = p_report_type
258 AND report_qualifier = p_state
259 AND p_period_end BETWEEN effective_start_date AND effective_end_date;
260 --
261 l_format varchar2(30);
262 --
263 BEGIN
264 --
265 hr_utility.set_location('pay_mag_utils.lookup_format',1);
266 --
267 -- In the case of a yearly report, period end will be the same as year end.
268 --
269 OPEN csr_format;
270 FETCH csr_format INTO l_format;
271 --
272 IF csr_format%NOTFOUND THEN
273 --
274 CLOSE csr_format;
275 hr_utility.set_message(801,'PAY_50031_1099R_REP_FMT');
276 RAISE hr_utility.hr_error;
277 --
278 ELSE
279 --
280 CLOSE csr_format;
281 --
282 END IF;
283 --
284 RETURN (l_format);
285 --
286 END Lookup_Format;
287 --
288 --
289 -----------------------------------------------------------------------------
290 -- Name
291 -- Bal_db_Item
292 -- Purpose
293 -- Given the name of a balance DB item as would be seen in a fast formula
294 -- it returns the defined_balance_id of the balance it represents.
295 -- Arguments
296 -- p_db_item_name Item name
297 -- Notes
298 -- A defined balance_id is required by the PLSQL balance function.
299 -----------------------------------------------------------------------------
300 --
301 FUNCTION Bal_db_Item ( p_db_item_name VARCHAR2 ) RETURN NUMBER IS
302 --
303 -- Cursor to get the defined_balance_id for the specified balance db item.
304 --
305 CURSOR csr_defined_balance IS
306 SELECT fnd_number.canonical_to_number(ue.creator_id)
307 FROM ff_database_items di,
308 ff_user_entities ue
309 WHERE di.user_name = p_db_item_name
310 AND ue.user_entity_id = di.user_entity_id
311 AND ue.creator_type = 'B'
312 AND ue.legislation_code = 'US'; /* Bug: 2296797 */
313 --
314 l_defined_balance_id NUMBER;
315 --
316 BEGIN
317 --
318 OPEN csr_defined_balance;
319 --
320 FETCH csr_defined_balance INTO l_defined_balance_id;
321 --
322 IF csr_defined_balance%notfound THEN
323 --
324 CLOSE csr_defined_balance;
325 hr_utility.set_message(801,'PAY_50032_1099R_BAL_DB');
326 RAISE hr_utility.hr_error;
327 --
328 ELSE
329 CLOSE csr_defined_balance;
330 END IF;
331 --
332 RETURN (l_defined_balance_id);
333 --
334 END Bal_db_Item;
335 --
336
337 --
338 -----------------------------------------------------------------------------
339 -- Name
340 -- Lookup_Jurisdiction_Code
341 -- Purpose
342 -- Given a state code ie. AL it returns the jurisdiction code that
343 -- represents that state.
344 -- Arguments
345 -- p_state
346 -- Notes
347 -----------------------------------------------------------------------------
348 --
349 FUNCTION Lookup_Jurisdiction_Code ( p_state VARCHAR2 ) RETURN VARCHAR2 IS
350 --
351 -- Get the jurisdiction_code for the specified state code.
352 --
353 CURSOR csr_jurisdiction_code IS
354 SELECT sr.jurisdiction_code
355 FROM pay_state_rules sr
356 WHERE sr.state_code = p_state;
357 --
358 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
359 --
360 BEGIN
361 --
362 OPEN csr_jurisdiction_code;
363 FETCH csr_jurisdiction_code INTO l_jurisdiction_code;
364 --
365 IF csr_jurisdiction_code%NOTFOUND THEN
366 --
367 CLOSE csr_jurisdiction_code;
368 hr_utility.set_message(801,'PAY_50033_1099R_JU_CODE');
369 RAISE hr_utility.hr_error;
370 ELSE
371 --
372 CLOSE csr_jurisdiction_code;
373 --
374 END IF;
375 --
376 RETURN (l_jurisdiction_code);
377 --
378 END Lookup_Jurisdiction_Code;
379 --
380
381 --
382 -----------------------------------------------------------------------------
383 -- Name
384 -- Check_Report_Unique
385 -- Purpose
386 -- Makes sure that a report has not already been run which overlaps with
387 -- the report being started.
388 -- Arguments
389 -- Notes
390 -- Each report is uniquely defined by the EFFECTIVE_DATE and the
391 -- LEGISLATIVE_PARAMETERS of the payroll action. The LEGISLATIVE_PARAMETERS
392 -- is set to report_type || '-' || p_state. In order to resubmit this report
393 -- we need to add transmitter legal company id onto the LEGISLATIVE PARAMETERS.
394 -- To ensure that a report with a for the same state and same period is not run
395 -- for different transmitters. I added the '%' to where clause.
396 -----------------------------------------------------------------------------
397 --
398 PROCEDURE Check_Report_Unique ( p_business_group_id IN NUMBER,
399 p_period_end IN DATE,
400 p_report_type in VARCHAR2,
401 p_state in VARCHAR2 ) IS
402 --
403 CURSOR csr_payroll_action IS
404 SELECT payroll_action_id
405 FROM pay_payroll_actions ppa
406 WHERE ppa.business_group_id = p_business_group_id
407 AND ppa.effective_date = p_period_end
408 AND ppa.legislative_parameters like
409 'USMAGTAPE'|| '-' ||
410 lpad(p_report_type, 5)||'-'||
411 lpad(p_state, 5) || '%';
412 --
413 l_payroll_action_id NUMBER;
414 --
415 BEGIN
416 --
417 hr_utility.set_location('pay_mag_utils.check_report_unique',1);
418 --
419 OPEN csr_payroll_action;
420 --
421 FETCH csr_payroll_action INTO l_payroll_action_id;
422 --
423 IF csr_payroll_action%found THEN
424 --
425 CLOSE csr_payroll_action;
426 hr_utility.set_message(801,'PAY_50034_1099R_REP_RUN');
427 RAISE hr_utility.hr_error;
428 --
429 ELSE
430 CLOSE csr_payroll_action;
431 END IF;
432 --
433 END Check_Report_Unique;
434 --
435
436 --
437 -----------------------------------------------------------------------------
438 -- Name
439 -- Error_Payroll_Action
440 -- Purpose
441 -- Sets the status of a payroll action to 'E'rror.
442 -- Arguments
443 -- p_payroll_action_id
444 -- Notes
445 -- This should only be used when the magnetic report has failed.
446 -----------------------------------------------------------------------------
447 --
448 PROCEDURE Error_Payroll_Action ( p_payroll_action_id NUMBER ) IS
449 --
450 BEGIN
451 --
452 -- Set the payroll action status to Error if report has failed.
453 --
454 hr_utility.set_location('pay_mag_utils.error_payroll_action',1);
455 --
456 UPDATE pay_payroll_actions pa
457 SET pa.action_status = 'E'
458 WHERE pa.payroll_action_id = p_payroll_action_id;
459 --
460 COMMIT;
461 --
462 END Error_Payroll_Action;
463 --
464
465 --
466 -----------------------------------------------------------------------------
467 -- Name
468 -- Update_Action_Statuses
469 -- Purpose
470 -- Sets the payroll action to 'C'omplete. Sets all successful assignment
471 -- actions to 'C'omplete.
472 -- Arguments
473 -- Notes
474 -- This should only be used when the magnetic report has successfully run.
475 -- All the assignment actions are set to 'U'nprocessed before processing
476 -- starts. If an error occurs with an assignment action then it is set to
477 -- 'E'rror by the magnetic tape process. Having finished processing, all
478 -- assignment actions left with a status of 'U'nprocessed are assumed to
479 -- be successful and therefore set to 'C'omplete.
480 -----------------------------------------------------------------------------
481 --
482 PROCEDURE Update_Action_Status ( p_payroll_action_id NUMBER ) IS
483 --
484 BEGIN
485 --
486 -- Sets the payroll action to a status of 'C'omplete.
487 --
488 hr_utility.set_location('pay_mag_utils.update_action_status',1);
489 --
490 UPDATE pay_payroll_actions pa
491 SET pa.action_status = 'C'
492 WHERE pa.payroll_action_id = p_payroll_action_id;
493 --
494 -- Sets all successfully processed assignment actions to 'C'omplete.
495 --
496 hr_utility.set_location('pay_mag_utils.update_action_status',2);
497 --
498 UPDATE pay_assignment_actions aa
499 SET aa.action_status = 'C'
500 WHERE aa.payroll_action_id = p_payroll_action_id
501 AND aa.action_status = 'U';
502 --
503 COMMIT;
504 --
505 END Update_Action_Status;
506 --
507
508 --
509 -----------------------------------------------------------------------------
510 -- Name
511 -- Create_Payroll_Action
512 -- Purpose
513 -- Creates a payroll action identifying the production of a particular
514 -- magnetic tape report e.g. Federal 1099R. The list of people to be
515 -- reported on is created as assignment actions for the payroll action.
516 -- Arguments
517 -- Notes
518 -- The effective_date of the payroll action identifies the end of the
519 -- period being reported i.e. end of tax year or end of a quarter. The
520 -- legislative parameter is used to uniquely identify the report.
521 -----------------------------------------------------------------------------
522 --
523 FUNCTION Create_Payroll_Action ( p_report_type IN VARCHAR2,
524 p_state IN VARCHAR2,
525 p_trans_legal_co_id IN VARCHAR2,
526 p_business_group_id IN NUMBER,
527 p_period_end IN DATE,
528 p_param_text IN VARCHAR2
529 DEFAULT NULL ) RETURN NUMBER IS
530 --
531 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
532 --
533
534 l_legislative_parms VARCHAR2(240);
535 BEGIN
536 --
537 -- Get the next payroll_action_id value from the sequence.
538 --
539 hr_utility.set_location('pay_mag_utils.create_payroll_action',1);
540 --
541 select pay_payroll_actions_s.nextval
542 into l_payroll_action_id
543 from sys.dual;
544 --
545 -- Create a payroll action dated as of the end of the period being reported
546 -- on. Populate the legislative parameter to identify the report being run.
547 --
548 hr_utility.set_location('pay_mag_utils.create_payroll_action',2);
549 --
550 if p_report_type = '1099R' then
551 l_legislative_parms := 'USMAGTAPE'|| '-' ||
552 lpad(p_report_type,5) || '-' ||
553 lpad(p_state,5) || '-' ||
554 lpad(p_trans_legal_co_id, 5) || p_param_text;
555 else
556 l_legislative_parms := 'USMAGTAPE'|| '-' ||
557 lpad(p_report_type,5) || '-' ||
558 lpad(p_state,5) || '-' ||
559 lpad(p_trans_legal_co_id, 5);
560 end if;
561 --
562 --
563 INSERT INTO pay_payroll_actions
564 (payroll_action_id,
565 action_type,
566 business_group_id,
567 action_population_status,
568 action_status,
569 effective_date,
570 date_earned,
571 legislative_parameters,
572 object_version_number )
573 VALUES
574 ( l_payroll_action_id,
575 'X', -- (X) Magnetic Report
576 p_business_group_id,
577 'U', -- (U)npopulated
578 'U', -- (U)nprocessed
579 p_period_end,
580 p_period_end,
581 l_legislative_parms,
582 1);
583 --
584 hr_utility.set_location('pay_mag_utils.create_payroll_action',3);
585 --
586 -- Return payroll action id of new row.
587 --
588 RETURN (l_payroll_action_id);
589 --
590 END Create_Payroll_Action;
591 --
592
593 --
594 -----------------------------------------------------------------------------
595 -- Name
596 -- Create_Assignment_Action
597 -- Purpose
598 -- Create an assignment action for each person to be reported on within the
599 -- magnetic tape report identified by the parent payroll action.
600 -- Arguments
601 -- Notes
602 -----------------------------------------------------------------------------
603 --
604 FUNCTION Create_Assignment_Action ( p_payroll_action_id IN NUMBER,
605 p_assignment_id IN NUMBER,
606 p_tax_unit_id IN NUMBER )
607 RETURN NUMBER IS
608 --
609 -- Cursor to fetch the newly created assignment_action_id. There could
610 -- be several assignment actions for the same assignment and the only way
611 -- to find the newly created one is to fetch the one that has not had the
612 -- tax_unit_id updated yet.
613 --
614 CURSOR csr_assignment_action IS
615 SELECT aa.assignment_action_id
616 FROM pay_assignment_actions aa
617 WHERE aa.payroll_action_id = p_payroll_action_id
618 AND aa.assignment_id = p_assignment_id
619 AND aa.tax_unit_id IS NULL;
620 --
621 -- Local variables.
622 --
623 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
624 --
625 BEGIN
626 --
627 hr_utility.set_location('pay_mag_utils.create_assignment_action',1);
628 --
629 -- Create assignment action to identify a specific person's inclusion in the
630 -- magnetic tape report identified by the parent payroll action. The
631 -- assignment action has to be sequenced within the other assignment actions
632 -- according to the date of the payroll action so that the derivation of
633 -- any balances based on the assignment action is correct.
634 --
635 hrassact.inassact(p_payroll_action_id, p_assignment_id);
636 --
637 -- Get the assignment_action_id of the newly created assignment action.
638 --
639 hr_utility.set_location('pay_mag_utils.create_assignment_action',2);
640 --
641 OPEN csr_assignment_action;
642 FETCH csr_assignment_action INTO l_assignment_action_id;
643 CLOSE csr_assignment_action;
644 --
645 UPDATE pay_assignment_actions aa
646 SET aa.tax_unit_id = p_tax_unit_id
647 WHERE aa.assignment_action_id = l_assignment_action_id;
648 --
649 hr_utility.set_location('pay_mag_utils.create_assignment_action',3);
650 --
651 -- Return id of new row.
652 --
653 RETURN (l_assignment_action_id);
654 --
655 END Create_Assignment_Action;
656 --
657
658 --
659 -----------------------------------------------------------------------------
660 -- Name
661 -- Insert_Lookups
662 -- Purpose
663 -- Inserts lookups into the hr_lookups table. It firstly checks if the lookup
664 -- exists before inserting it thus avoiding duplication.
665 -- Arguments
666 -- p_lookup_code - lookup code to add
667 -- p_lookup_type - lookup type to add
668 -- p_meaning - lookup code meaning
669 -- Rest are defaulted.
670 -----------------------------------------------------------------------------
671 PROCEDURE Insert_Lookup
672 (p_lookup_code in varchar2,
673 p_lookup_type in varchar2,
674 p_application_id in number default 800,
675 p_created_by in number default 1,
676 p_creation_date in date default to_date('01/01/1901','DD/MM/YYYY'),
677 p_enabled_flag in varchar2 default 'Y',
678 p_last_updated_by in number default 1,
679 p_last_update_date in date default to_date('01/01/1901','DD/MM/YYYY'),
680 p_meaning in varchar2,
681 p_effective_date in date default to_date('01/01/1901','DD/MM/YYYY')) IS
682 --
683 l_dummy VARCHAR2(1);
684 --
685 CURSOR c1 IS
686 SELECT NULL
687 FROM HR_LOOKUPS HR
688 WHERE HR.lookup_type = p_lookup_type
689 AND HR.lookup_code = p_lookup_code;
690 --
691 BEGIN
692 --
693 OPEN c1;
694 --
695 FETCH c1 INTO l_dummy;
696 IF c1%notfound THEN
697 --
698 -- insert the lookup
699 --
700 INSERT INTO hr_lookups
701 (lookup_code,
702 lookup_type,
703 application_id,
704 created_by,
705 creation_date,
706 enabled_flag,
707 last_updated_by,
708 last_update_date,
709 meaning)
710 VALUES
711 (p_lookup_code,
712 p_lookup_type,
713 p_application_id,
714 p_created_by,
715 p_creation_date,
716 p_enabled_flag,
717 p_last_updated_by,
718 p_last_update_date,
719 p_meaning);
720 --
721 END IF;
722 --
723 CLOSE c1;
724 --
725 END Insert_Lookup;
726 --
727
728 --
729 -----------------------------------------------------------------------------
730 --
731 -- Name
732 -- Get_Dates
733 -- Purpose
734 -- Dates are dependent on the report being run i.e. a 1099R report shows
735 -- information for a tax year.
736 -- Arguments
737 -- Notes
738 -----------------------------------------------------------------------------
739 --
740 PROCEDURE Get_Dates ( p_report_type VARCHAR2,
741 p_year VARCHAR2,
742 p_year_start IN OUT nocopy DATE,
743 p_year_end IN OUT nocopy DATE,
744 p_rep_year IN OUT nocopy VARCHAR2 ) IS
745 --
746 BEGIN
747 --
748 -- 1099R is a yearly report where the identifier indicates the year
749 -- eg. 1995. The expected values for the example should be
750 --
751 -- p_year_start 01-JAN-1995
752 -- p_year_end 31-DEC-1995
753 -- p_reporting_year 1995
754 --
755 hr_utility.set_location('pay_mag_utils.get_dates',1);
756 --
757 IF p_report_type = '1099R' THEN
758 --
759 hr_utility.set_location('pay_mag_utils.get_dates',2);
760 --
761 p_rep_year := p_year;
762 --
763 END IF;
764 --
765 hr_utility.set_location('pay_mag_utils.get_dates',3);
766 --
767 p_year_start := to_date('01-01-'||p_rep_year, 'DD-MM-YYYY');
768 p_year_end := to_date('31-12-'||p_rep_year, 'DD-MM-YYYY');
769 --
770 END Get_Dates;
771 --
772 ----------------------------------------------------------------------------------------
773 -- Name
774 -- set_titles
775 -- Purpose
776 -- Writes titles to the screen for the block_name, main_block, next_block, formula
777 -- Arguments
778 -- p_report_format
779 ----------------------------------------------------------------------------------------
780 --
781 PROCEDURE Set_Titles (p_report_format in varchar2) is
782 --
783 l_string varchar2(80);
784 --
785 BEGIN
786 --
787 -- Put report title on screen
788 --
789 hr_utility.trace('****************************************************');
790 hr_utility.trace('Report Format '||p_report_format);
791 hr_utility.trace('****************************************************');
792 --
793 -- Put headings on screen
794 --
795
796 l_string := 'Block Name'||
797 ' '||
798 'Main'||
799 ' '||
800 'Next Block'||
801 ' '||
802 'Formula';
803 hr_utility.trace(l_string);
804 --
805 l_string := '----------'||
806 ' '||
807 '----'||
808 ' '||
809 '----------'||
810 ' '||
811 '-------';
812 hr_utility.trace(l_string);
813 --
814 END Set_Titles;
815 --
816 /*^L*/
817 ----------------------------------------------------------------------------------------
818 -- Name
819 -- format_output
820 -- Purpose
821 -- Formats report output for report format block structure
822 -- Arguments
823 -- p_block_name
824 -- p_main_block
825 -- p_next_block
826 -- p_formula
827 ----------------------------------------------------------------------------------------
828 --
829 PROCEDURE Format_Output(p_block_name in varchar2,
830 p_main_block in varchar2,
831 p_next_block in varchar2,
832 p_formula in varchar2) is
833 --
834 l_string varchar2(200) := '';
835 l_formula varchar2(50);
836 --
837 BEGIN
838 --
839 -- Format output as per the titles defined in Set_Titles
840 -- The code looks more complex than it is but all we are ensuring is that
841 -- we get a very tabular look to the output.
842 --
843 g_message := 'Inserting format for '||p_block_name;
844 --
845 hr_utility.trace('Inserting format for '||p_block_name);
846 --
847 l_formula := substr(p_formula,1,25);
848 l_string := concat(l_string,p_block_name);
849 l_string := concat(l_string,lpad(p_main_block,26-length(p_block_name),' '));
850 l_string := concat(l_string,lpad(p_next_block,5-length(p_main_block)
851 +length(p_next_block),' '));
852 l_string := concat(l_string,lpad(l_formula,25-length(p_next_block)
853 +length(l_formula),' '));
854 hr_utility.trace(l_string);
855 --
856 END Format_Output;
857
858 /*^L*/
859 ----------------------------------------------------------------------------------------
860 -- Name
861 -- recurse_block_structure
862 -- Purpose
863 -- This procedure recursively looks up and down a block structure hierarchy and
864 -- obtains the structure of the block definition.
865 -- Arguments
866 -- p_magnetc_block_id
867 ----------------------------------------------------------------------------------------
868 --
869 PROCEDURE Recurse_Block_Structure (p_magnetic_block_id number) is
870 --
871 CURSOR c_structure IS
872 SELECT pmb.block_name block_name,
873 pmb.main_block_flag main_block_flag,
874 pmb2.block_name next_block_name,
875 pmr.next_block_id next_block_id,
876 ff.formula_name formula_name
877 FROM pay_magnetic_blocks pmb,
878 pay_magnetic_records pmr,
879 pay_magnetic_blocks pmb2,
880 ff_formulas_f ff
881 WHERE pmb.magnetic_block_id = p_magnetic_block_id
882 AND pmb.magnetic_block_id = pmr.magnetic_block_id
883 AND pmr.next_block_id = pmb2.magnetic_block_id (+)
884 AND pmr.formula_id = ff.formula_id
885 ORDER by pmr.sequence;
886 --
887 BEGIN
888 --
889 -- Start recursive procedure to create block structure hierarchy
890 --
891 FOR ee IN c_structure LOOP
892 --
893 EXIT WHEN c_structure%notfound;
894 --
895 hr_utility.trace(ee.block_name);
896 --
897 -- We have to ensure that the next_block_name does not have a null
898 -- value as otherwise our formatting which uses lengths of items to
899 -- ensure correct padding will not work correctly. To get around this
900 -- problem we just assign a space to the passed parameter.
901 --
902 Format_Output(ee.block_name,
903 ee.main_block_flag,
904 nvl(ee.next_block_name,' '),
905 ee.formula_name);
906 --
907 IF ee.next_block_id IS NOT NULL THEN
908 Recurse_Block_Structure(ee.next_block_id);
909 END IF;
910 --
911 END LOOP;
912 --
913 END Recurse_Block_Structure;
914 /*^L*/
915 ----------------------------------------------------------------------------------------
916 -- Name
917 -- Org_Info_Exists
918 -- Purpose
919 -- Checks if p_org_info_type exists in HR_ORG_INFORMATION_TYPES
920 -- Arguments
921 -- p_org_info_type
922 ----------------------------------------------------------------------------------------
923 FUNCTION Org_Info_Exists ( p_org_info_type IN VARCHAR2) RETURN BOOLEAN IS
924 --
925 l_dummy varchar2(1);
926 --
927 -- Cursor to check for an existance of the report format in pay_magnetic_blocks
928 -- table.
929 --
930 CURSOR c_org_exists IS
931 SELECT null
932 FROM HR_ORG_INFORMATION_TYPES hoit
933 WHERE hoit.org_information_type = p_org_info_type;
934 --
935 BEGIN
936 --
937 -- Steps to check for valid report format are as follows :
938 -- 1) Open cursor
939 -- 2) Attempt fetch of record
940 -- 3) If fetch fails then raise NO_DATA_FOUND, this can be handled
941 -- by our exception handler in the main procedure.
942 -- 4) Otherwise processing continues
943 --
944 OPEN c_org_exists;
945 --
946 FETCH c_org_exists INTO l_dummy;
947 --
948 IF c_org_exists%notfound THEN
949 --
950 CLOSE c_org_exists;
951 return(FALSE);
952 --
953 ELSE
954 --
955 CLOSE c_org_exists;
956 return(TRUE);
957 --
958 END IF;
959 End Org_Info_Exists;
960 --
961 /*^L*/
962 ----------------------------------------------------------------------------------------
963 --
964 PROCEDURE Report_Exists (p_report_format in varchar2) is
965 --
966 l_dummy varchar2(1);
967 --
968 -- Cursor to check for an existance of the report format in pay_magnetic_blocks
969 -- table.
970 --
971 CURSOR c_report_exists IS
972 SELECT null
973 FROM pay_magnetic_blocks pmb
974 WHERE pmb.report_format = p_report_format;
975 --
976 BEGIN
977 --
978 -- Steps to check for valid report format are as follows :
979 -- 1) Open cursor
980 -- 2) Attempt fetch of record
981 -- 3) If fetch fails then raise NO_DATA_FOUND, this can be handled
982 -- by our exception handler in the main procedure.
983 -- 4) Otherwise processing continues
984 --
985 OPEN c_report_exists;
986 --
987 FETCH c_report_exists INTO l_dummy;
988 --
989 IF c_report_exists%notfound THEN
990 --
991 CLOSE c_report_exists;
992 --
993 -- raise NO_DATA_FOUND to force flow back to calling procedure
994 --
995 RAISE NO_DATA_FOUND;
996 --
997 END IF;
998 --
999 CLOSE c_report_exists;
1000 --
1001 End Report_Exists;
1002 --
1003 /*^L*/
1004 ----------------------------------------------------------------------------------------
1005 -- Name
1006 -- main
1007 -- Purpose
1008 -- Calls supporting procedures to display block information on the screen.
1009 -- Arguments
1010 -- None
1011 -- Notes
1012 ----------------------------------------------------------------------------------------
1013 --
1014 PROCEDURE Main(p_report_format in varchar2) IS
1015 --
1016 l_magnetic_block_id number;
1017 --
1018 -- This cursor returns the magnetic block id of the starting (main block) of
1019 -- the report format to be produced.
1020 --
1021 CURSOR c_magnetic_block_id IS
1022 SELECT pmb.magnetic_block_id
1023 FROM pay_magnetic_blocks pmb
1024 WHERE pmb.report_format = p_report_format
1025 AND pmb.main_block_flag = 'Y';
1026 --
1027 BEGIN
1028 -- **************************************************************************
1029 -- FORMAT EXISTS
1030 -- **************************************************************************
1031 --
1032 -- This procedure checks if the format for the report we want to view
1033 -- actually exists.
1034 --
1035 hr_utility.trace('Attempting to see if '||p_report_format||' exists');
1036 --
1037 g_message := 'Attempting to see if '||p_report_format||' exists';
1038 --
1039 Report_Exists(p_report_format);
1040 --
1041 -- *************************************************************************
1042 -- SET TITLES
1043 -- *************************************************************************
1044 --
1045 -- This procedure displays titles and headings for the report for which we are
1046 -- displaying the format.
1047 --
1048 hr_utility.trace('Setting Titles');
1049 --
1050 g_message := 'Setting Titles';
1051 --
1052 Set_Titles(p_report_format);
1053 --
1054 -- *************************************************************************
1055 -- RECURSE BLOCK STRUCTURE
1056 -- *************************************************************************
1057 --
1058 -- If the report exists we can start by calling the recursive procedure in
1059 -- order to output the definition of the report. The steps to do this are
1060 -- as follows :
1061 -- 1) Get Magnetic Block ID of main block for report format
1062 -- 2) Pass this ID to Recurse_Block_Structure procedure
1063 -- 3) The procedure will then recursively go down its hierarchy and print
1064 -- out the report format.
1065 --
1066 hr_utility.trace('Attempting to get main block for '||p_report_format);
1067 --
1068 g_message := 'Attempting to get main block for '||p_report_format;
1069 --
1070 OPEN c_magnetic_block_id;
1071 --
1072 FETCH c_magnetic_block_id INTO l_magnetic_block_id;
1073 --
1074 IF c_magnetic_block_id%notfound THEN
1075 --
1076 CLOSE c_magnetic_block_id;
1077 --
1078 -- Raise error as there is no magnetic block that is the main block so
1079 -- structure can not be defined.
1080 --
1081 RAISE NO_DATA_FOUND;
1082 --
1083 END IF;
1084 --
1085 CLOSE c_magnetic_block_id;
1086 --
1087 hr_utility.trace('Starting recursive call to produce format of '||p_report_format);
1088 --
1089 g_message := 'Starting recursive call to produce format of '||p_report_format;
1090 --
1091 Recurse_Block_Structure(l_magnetic_block_id);
1092 --
1093 -- *************************************************************************
1094 -- END
1095 -- *************************************************************************
1096 --
1097 EXCEPTION
1098 --
1099 WHEN NO_DATA_FOUND THEN
1100 --
1101 hr_utility.trace(g_message||' - ORA '||to_char(SQLCODE));
1102 hr_utility.trace(g_message||' - ORA '||to_char(SQLCODE));
1103 --
1104 WHEN OTHERS THEN
1105 --
1106 hr_utility.trace(g_message||' - ORA '||to_char(SQLCODE));
1107 hr_utility.trace(g_message||' - ORA '||to_char(SQLCODE));
1108 --
1109 END Main;
1110 --
1111 ----------------------------------------------------------------------------------------
1112 -- Name
1113 -- udf_Exists
1114 -- Purpose
1115 -- This procedure checks to see if udf is already stored in ff_functions
1116 -- Arguments
1117 -- p_udf_nam
1118 ----------------------------------------------------------------------------------------
1119 FUNCTION udf_Exists (p_udf_name in varchar2) RETURN NUMBER IS
1120
1121 l_udf_id number(9) := 0;
1122
1123 begin
1124
1125 select function_id
1126 into l_udf_id
1127 from ff_functions
1128 where upper(name) = upper(p_udf_name)
1129 and business_group_id is null
1130 and legislation_code = 'US';
1131
1132 return l_udf_id;
1133 --
1134 exception
1135 when no_data_found then
1136 return 0;
1137 --
1138 end udf_Exists;
1139 --
1140 ----------------------------------------------------------------------------------------
1141 -- Name
1142 -- Delete_udf
1143 -- Purpose
1144 -- removes udf from ff_function_parameters,ff_function_context_usages, ff_functions
1145 -- Arguments
1146 -- p_udf_nam
1147 ----------------------------------------------------------------------------------------
1148
1149 PROCEDURE Delete_udf (p_udf_name in varchar2) IS
1150 l_udf_id number(9) := 0;
1151
1152 begin
1153 l_udf_id := udf_exists(p_udf_name);
1154
1155 if l_udf_id <> 0 then
1156 delete from ff_function_parameters
1157 where function_id = l_udf_id;
1158
1159 delete from ff_function_context_usages
1160 where function_id = l_udf_id;
1161
1162 delete from ff_functions
1163 where function_id = l_udf_id;
1164 end if;
1165
1166 end Delete_udf;
1167
1168 --
1169 ----------------------------------------------------------------------------------------
1170 -- Name
1171 -- get_parameter
1172 -- Purpose
1173 -- returns parameters from a parameter list
1174 -- Arguments
1175 -- name
1176 -- parameter_lis
1177 ----------------------------------------------------------------------------------------
1178
1179 FUNCTION get_parameter(name in varchar2,
1180 end_name in varchar2,
1181 parameter_list varchar2) return varchar2
1182 is
1183 start_ptr number;
1184 end_ptr number;
1185 token_val ff_archive_items.value%type;
1186 par_value ff_archive_items.value%type;
1187
1188 begin
1189 --
1190 token_val := name||'=';
1191 --
1192 start_ptr := instr(parameter_list, token_val) + length(token_val);
1193 end_ptr := instr(parameter_list, end_name, start_ptr);
1194 --
1195 /* if there are spaces use then length of the string + 1*/
1196 /*
1197 Bug 1069642: if end_name is passed in as null, end_ptr will
1198 be null and this will cause substr to not return a value.
1199 nvl is being used to correct the situation
1200 */
1201 if nvl(end_ptr, 0) = 0 then
1202 end_ptr := length(parameter_list)+1;
1203 end if;
1204 --
1205 /* Did we find the token */
1206 if instr(parameter_list, token_val) = 0 then
1207 par_value := NULL;
1208 else
1209 par_value := LTRIM(RTRIM(substr(parameter_list, start_ptr, end_ptr - start_ptr)));
1210 end if;
1211 --
1212 return par_value;
1213 --
1214 end get_parameter;
1215
1216 END Pay_Mag_Utils;
[Home] [Help]