[Home] [Help]
1: PACKAGE BODY pay_us_magw2_reporting AS
2: /* $Header: pyyepmw2.pkb 115.15 2002/12/03 03:02:16 ppanda ship $ */
3: -----------------------------------------------------------------------------
4: -- Name : bal_db_item
5: -- Purpose : Given the name of a balance DB item as would be seen in a
25: AND UE.legislation_code = 'US'; /* Bug: 2296797 */
26: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
27: BEGIN
28: hr_utility.set_location
29: ('pay_us_magw2_reporting.bal_db_item - opening cursor', 10);
30: -- Open the cursor
31: OPEN csr_defined_balance;
32: -- Fetch the value
33: FETCH csr_defined_balance
34: INTO l_defined_balance_id;
35: IF csr_defined_balance%NOTFOUND THEN
36: CLOSE csr_defined_balance;
37: hr_utility.set_location
38: ('pay_us_magw2_reporting.bal_db_item - no rows found from cursor', 20);
39: hr_utility.raise_error;
40: ELSE
41: hr_utility.set_location
42: ('pay_us_magw2_reporting.bal_db_item - fetched from cursor', 30);
38: ('pay_us_magw2_reporting.bal_db_item - no rows found from cursor', 20);
39: hr_utility.raise_error;
40: ELSE
41: hr_utility.set_location
42: ('pay_us_magw2_reporting.bal_db_item - fetched from cursor', 30);
43: CLOSE csr_defined_balance;
44: END IF;
45: /* Return the value to the call */
46: RETURN (l_defined_balance_id);
74: p_business_group_id IN OUT nocopy NUMBER
75: ) IS
76: BEGIN
77: hr_utility.set_location
78: ('pay_us_magw2_reporting.get_report_parameters', 10);
79: SELECT ppa.start_date,
80: ppa.effective_date,
81: ppa.business_group_id,
82: ppa.report_qualifier,
93: INTO p_state_code
94: FROM pay_us_states
95: WHERE state_abbrev = p_state_abbrev;
96: hr_utility.set_location
97: ('pay_us_magw2_reporting.get_report_parameters', 20);
98: ELSE
99: p_state_code := '';
100: hr_utility.set_location
101: ('pay_us_magw2_reporting.get_report_parameters', 30);
97: ('pay_us_magw2_reporting.get_report_parameters', 20);
98: ELSE
99: p_state_code := '';
100: hr_utility.set_location
101: ('pay_us_magw2_reporting.get_report_parameters', 30);
102: END IF;
103: IF p_state_abbrev = 'FED' AND p_report_type = 'W2' THEN
104: p_report_type := 'FEDW2';
105: ELSIF p_report_type = 'W2' THEN
105: ELSIF p_report_type = 'W2' THEN
106: p_report_type := 'STW2';
107: END IF;
108: hr_utility.set_location
109: ('pay_us_magw2_reporting.get_report_parameters', 40);
110: END get_report_parameters;
111: -------------------------------------------------------------------------
112: -- Name : get_balance_value
113: --
133: ) RETURN NUMBER IS
134: l_jurisdiction_code VARCHAR2(20);
135: BEGIN
136: hr_utility.set_location
137: ('pay_us_magw2_reporting.get_balance_value', 10);
138: pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
139: IF p_state_abbrev <> 'FED' THEN
140: SELECT jurisdiction_code
141: INTO l_jurisdiction_code
141: INTO l_jurisdiction_code
142: FROM pay_state_rules
143: WHERE state_code = p_state_abbrev;
144: hr_utility.set_location
145: ('pay_us_magw2_reporting.get_balance_value', 15);
146: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
147: END IF;
148: hr_utility.trace(p_balance_name);
149: hr_utility.trace('Context');
149: hr_utility.trace('Context');
150: hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
151: hr_utility.trace('Jurisdiction: '|| l_jurisdiction_code);
152: hr_utility.set_location
153: ('pay_us_magw2_reporting.get_balance_value', 20);
154: RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
155: p_assignment_id, p_effective_date);
156: END get_balance_value;
157: --------------------------------------------------------------------------
532:
533: /* end of Transmitter Checking */
534:
535:
536: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 10);
537:
538: FOR gre_rec IN c_get_gre LOOP
539: /* set l_gre to gre Fethched */
540: l_gre := gre_rec.gre;
549: IF c_arch_errored_asg%FOUND THEN
550: --Some of the assignments have not been archived
551:
552:
553: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 70);
554: hr_utility.set_message(801, 'PAY_72729_ASG_NOT_ARCH');
555: l_package_error_status := 'Y';
556: /* message to user -- assignment has errored out */
557: -- hr_utility.raise_error;
560: OPEN c_arch_retry_pending (l_payroll_action_id);
561: FETCH c_arch_retry_pending INTO l_asg_retry_pend;
562: IF c_arch_retry_pending%FOUND THEN
563: --Some of the assignments have been marked for retry
564: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 80);
565: hr_utility.set_message(801, 'PAY_72730_ASG_MARKED_FOR_RETRY');
566: l_package_error_status := 'Y';
567: -- hr_utility.raise_error;
568: END IF;
624:
625: Open Cursor as per your Report type to check if GRE needs to be archived
626: or Not */
627: IF p_report_type = 'FEDW2' THEN --federal W2
628: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 99);
629: OPEN c_gre_fed(gre_rec.gre);
630: ELSIF p_report_type = 'STW2' THEN --state W2
631: OPEN c_gre_state(gre_rec.gre);
632: END IF;
635: IF p_report_type = 'FEDW2' THEN
636: FETCH c_gre_fed INTO l_person
637: ,l_assignment
638: ,l_asg_effective_dt;
639: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check',20);
640: hr_utility.trace('GRE:' || TO_CHAR(l_gre));
641: hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
642: hr_utility.trace('Person ID:' || TO_CHAR(l_person));
643: hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
647: ELSIF p_report_type = 'STW2' THEN
648: FETCH c_gre_state INTO l_person
649: ,l_assignment
650: ,l_asg_effective_dt;
651: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 40);
652: hr_utility.trace('GRE:' || TO_CHAR(l_gre));
653: hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
654: hr_utility.trace('Person ID:' || TO_CHAR(l_person));
655: hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
656: IF c_gre_state%NOTFOUND THEN
657: EXIT;
658: END IF;
659: END IF; /* report type = 'STW2' and etc */
660: hr_utility.trace('pay_us_magw2_reporting.preprocess_check');
661: hr_utility.trace('GRE - '||to_char(l_gre));
662: IF p_report_type = 'FEDW2' THEN
663: IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
664: l_gre, p_state_abbrev, l_assignment,
683: else
684: close c_gre_state;
685: end if;
686: hr_utility.set_location(
687: 'pay_us_magw2_reporting.preprocess_check', 12);
688: hr_utility.set_message(801, 'PAY_72728_ARCH_GRE_NOT_FOUND');
689: /* Check for state tax rules for the gre */
690: OPEN c_chk_archive_state_code (l_gre,l_payroll_action_id);
691: FETCH c_chk_archive_state_code INTO l_state_tax_rules_exist;
739:
740: IF l_no_of_gres_picked = 0 THEN
741: --It means that no archived GRE was
742: --found for the Organization. This is an error.
743: hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 110);
744: hr_utility.set_message(801, 'PAY_72728_ARCH_GRE_NOT_FOUND');
745: /* message to User -- No Gre Found for the archive */
746: hr_utility.raise_error;
747: END IF;
744: hr_utility.set_message(801, 'PAY_72728_ARCH_GRE_NOT_FOUND');
745: /* message to User -- No Gre Found for the archive */
746: hr_utility.raise_error;
747: END IF;
748: hr_utility.set_location( 'pay_us_magw2_reporting.preprocess_check', 120);
749: RETURN TRUE;
750: exception
751: when others then
752: -- add message for this
780: p_state_abbrev VARCHAR2(3);
781: p_state_code VARCHAR2(2);
782: p_report_type VARCHAR2(30);
783: BEGIN
784: hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor', 10);
785: get_report_parameters(
786: p_pactid,
787: p_year_start,
788: p_year_end,
790: p_state_code,
791: p_report_type,
792: p_business_group_id
793: );
794: hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor', 20);
795: IF preprocess_check(p_pactid,
796: p_year_start,
797: p_year_end,
798: p_business_group_id,
825: and hoi.org_information_context =
826: ''1099R Magnetic Report Rules'')
827: ORDER BY paf.person_id
828: ';
829: hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor',
830: 30);
831: ELSIF p_report_type = 'STW2' THEN
832: p_sqlstr := '
833: SELECT DISTINCT
874: and hoi.org_information_context =
875: ''1099R Magnetic Report Rules''
876: )
877: order by to_number(paa.serial_number)';
878: hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor',
879: 40);
880: END IF;
881: END IF;
882: END range_cursor;
1032: l_group_by_gre BOOLEAN;
1033: l_w2_box17 NUMBER; --SIT Wages
1034: BEGIN
1035: -- Get the report parameters. These define the report being run.
1036: hr_utility.set_location( 'pay_us_magw2_reporting.create_assignement_act',
1037: 10);
1038: get_report_parameters(
1039: p_pactid,
1040: l_year_start,
1046: );
1047: --Currently all reports group by GRE
1048: l_group_by_gre := TRUE;
1049: --Open the appropriate cursor
1050: hr_utility.set_location( 'pay_us_magw2_reporting.create_assignement_act',
1051: 20);
1052: IF l_report_type = 'FEDW2' THEN
1053: OPEN c_federal;
1054: ELSIF l_report_type = 'STW2' THEN
1061: l_tax_unit_id,
1062: l_effective_end_date,
1063: l_assignment_action_id;
1064: hr_utility.set_location(
1065: 'pay_us_magw2_reporting.create_assignement_act', 30);
1066: EXIT WHEN c_federal%NOTFOUND;
1067: ELSIF l_report_type = 'STW2' THEN
1068: FETCH c_state INTO l_person_id,
1069: l_assignment_id,
1071: l_effective_end_date,
1072: l_assignment_action_id,
1073: l_w2_box17;
1074: hr_utility.set_location(
1075: 'pay_us_magw2_reporting.create_assignement_act', 40);
1076: EXIT WHEN c_state%NOTFOUND;
1077: END IF;
1078: --Based on the groupin criteria, check if the record is the same
1079: --as the previous record.
1085: (NOT l_group_by_gre AND
1086: l_person_id = l_prev_person_id)) THEN
1087: --Do Nothing
1088: hr_utility.set_location(
1089: 'pay_us_magw2_reporting.create_assignement_act', 50);
1090: NULL;
1091: ELSE
1092: --Create the assignment action for the record
1093: hr_utility.trace('Assignment Fetched - ');
1109: SELECT pay_assignment_actions_s.nextval
1110: INTO lockingactid
1111: FROM dual;
1112: hr_utility.set_location(
1113: 'pay_us_magw2_reporting.create_assignement_act', 60);
1114: hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
1115: p_chunk, l_tax_unit_id);
1116: hr_utility.set_location(
1117: 'pay_us_magw2_reporting.create_assignement_act', 70);
1113: 'pay_us_magw2_reporting.create_assignement_act', 60);
1114: hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
1115: p_chunk, l_tax_unit_id);
1116: hr_utility.set_location(
1117: 'pay_us_magw2_reporting.create_assignement_act', 70);
1118: --update serial number for highly compensated people for the
1119: --state W2.
1120: IF l_report_type = 'STW2' THEN
1121: hr_utility.set_location(
1118: --update serial number for highly compensated people for the
1119: --state W2.
1120: IF l_report_type = 'STW2' THEN
1121: hr_utility.set_location(
1122: 'pay_us_magw2_reporting.create_assignement_act', 80);
1123: IF l_w2_box17 > 9999999.99 THEN
1124: UPDATE pay_assignment_actions
1125: SET serial_number = 999999
1126: WHERE assignment_action_id = lockingactid;
1127: END IF;
1128: END IF;
1129: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
1130: hr_utility.set_location(
1131: 'pay_us_magw2_reporting.create_assignement_act', 90);
1132: hr_utility.trace('Interlock Created - ');
1133: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
1134: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
1135: --Store the current person/GRE for comparision during the
1148:
1149: --begin
1150: -- hr_utility.trace_on(NULL, 'VIP');
1151:
1152: END pay_us_magw2_reporting;