[Home] [Help]
1: PACKAGE BODY pay_us_mmref_reporting_mt AS
2: /* $Header: payusmmrfrecmt.pkb 120.0.12000000.1 2007/02/24 10:12:03 sackumar noship $ */
3: /*REM +======================================================================+
4: REM | Copyright (c) 1997 Oracle Corporation |
5: REM | Redwood Shores, California, USA |
4: REM | Copyright (c) 1997 Oracle Corporation |
5: REM | Redwood Shores, California, USA |
6: REM | All rights reserved. |
7: REM +======================================================================+
8: REM Package Body Name : pay_us_mmref_reporting_mt
9: REM Package File Name : payusmmrfrecmt.pkb
10: REM Description : This package declares functions and procedures to support
11: REM the genration of magnetic W2 reports for US legislative requirements
12: REM incorporating magtape resilience and the new end-of-year processing.
45: AND UE.legislation_code = 'US'; /* Bug:2296797 */
46: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
47: BEGIN
48: hr_utility.set_location
49: ('pay_us_mmref_reporting_mt.bal_db_item - opening cursor', 10);
50: -- Open the cursor
51: OPEN csr_defined_balance;
52: -- Fetch the value
53: FETCH csr_defined_balance
58: ('pay_us_mmref_reporting.bal_db_item - no rows found from cursor', 20);
59: hr_utility.raise_error;
60: ELSE
61: hr_utility.set_location
62: ('pay_us_mmref_reporting_mt.bal_db_item - fetched from cursor', 30);
63: CLOSE csr_defined_balance;
64: END IF;
65: /* Return the value to the call */
66: RETURN (l_defined_balance_id);
95: p_business_group_id IN OUT nocopy NUMBER
96: ) IS
97: BEGIN
98: hr_utility.set_location
99: ('pay_us_mmref_reporting_mt.get_report_parameters', 10);
100: SELECT ppa.start_date,
101: ppa.effective_date,
102: ppa.business_group_id,
103: ppa.report_qualifier,
114: INTO p_state_code
115: FROM pay_us_states
116: WHERE state_abbrev = p_state_abbrev;
117: hr_utility.set_location
118: ('pay_us_mmref_reporting_mt.get_report_parameters', 20);
119: ELSE
120: p_state_code := '';
121: hr_utility.set_location
122: ('pay_us_mmref_reporting_mt.get_report_parameters', 30);
118: ('pay_us_mmref_reporting_mt.get_report_parameters', 20);
119: ELSE
120: p_state_code := '';
121: hr_utility.set_location
122: ('pay_us_mmref_reporting_mt.get_report_parameters', 30);
123: END IF;
124: IF p_state_abbrev = 'FED' AND p_report_type = 'W2' THEN
125: p_report_type := 'FEDW2';
126: ELSIF p_report_type = 'W2' THEN
126: ELSIF p_report_type = 'W2' THEN
127: p_report_type := 'STW2';
128: END IF;
129: hr_utility.set_location
130: ('pay_us_mmref_reporting_mt.get_report_parameters', 40);
131: END get_report_parameters;
132: -------------------------------------------------------------------------
133: -- Name : get_balance_value
134: --
154: ) RETURN NUMBER IS
155: l_jurisdiction_code VARCHAR2(20);
156: BEGIN
157: hr_utility.set_location
158: ('pay_us_mmref_reporting_mt.get_balance_value', 10);
159: pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
160: IF p_state_abbrev <> 'FED' THEN
161: SELECT jurisdiction_code
162: INTO l_jurisdiction_code
162: INTO l_jurisdiction_code
163: FROM pay_state_rules
164: WHERE state_code = p_state_abbrev;
165: hr_utility.set_location
166: ('pay_us_mmref_reporting_mt.get_balance_value', 15);
167: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
168: END IF;
169: hr_utility.trace(p_balance_name);
170: hr_utility.trace('Context');
170: hr_utility.trace('Context');
171: hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
172: hr_utility.trace('Jurisdiction: '|| l_jurisdiction_code);
173: hr_utility.set_location
174: ('pay_us_mmref_reporting_mt.get_balance_value', 20);
175: RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
176: p_assignment_id, p_effective_date);
177: END get_balance_value;
178: --------------------------------------------------------------------------
479: CLOSE c_gre_payroll_action;
480:
481: /* end of Transmitter Checking */
482:
483: hr_utility.set_location('pay_us_mmref_reporting_mt.preprocess_check', 10);
484:
485: FOR gre_rec IN c_get_gre LOOP
486: /* set l_gre to gre Fethched */
487:
616:
617: /* Code commented to fix Bug # 3069840
618:
619: IF p_report_type = 'FEDW2' THEN --federal W2
620: hr_utility.set_location('pay_us_mmref_reporting_mt.preprocess_check', 99);
621: OPEN c_gre_fed(gre_rec.gre);
622: ELSIF p_report_type = 'STW2' THEN --state W2
623: OPEN c_gre_state(gre_rec.gre);
624: END IF;
630: -- Start feching Persons for GRE
631: FETCH c_gre_fed INTO l_person
632: ,l_assignment
633: ,l_asg_effective_dt;
634: hr_utility.set_location('pay_us_mmref_reporting_mt.preprocess_check',20);
635: hr_utility.trace('GRE:' || TO_CHAR(l_gre));
636: hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
637: hr_utility.trace('Person ID:' || TO_CHAR(l_person));
638: hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
658: END IF;
659: END IF; -- report type = 'STW2' and etc
660:
661:
662: hr_utility.trace('pay_us_mmref_reporting_mt.preprocess_check');
663: hr_utility.trace('GRE - '||to_char(l_gre));
664:
665: -- get the balance for person
666:
796: p_state_code VARCHAR2(2);
797: p_report_type VARCHAR2(30);
798: BEGIN
799: hr_utility.trace_on(null,'FEDW2');
800: hr_utility.set_location( 'pay_us_mmref_reporting_mt.range_cursor', 10);
801: get_report_parameters(
802: p_pactid,
803: p_year_start,
804: p_year_end,
806: p_state_code,
807: p_report_type,
808: p_business_group_id
809: );
810: hr_utility.set_location( 'pay_us_mmref_reporting_mt.range_cursor', 20);
811: /* IF preprocess_check(p_pactid,
812: p_year_start,
813: p_year_end,
814: p_business_group_id,
842: and hoi.org_information_context =
843: ''1099R Magnetic Report Rules'')
844: ORDER BY paf.person_id
845: ';
846: hr_utility.set_location( 'pay_us_mmref_reporting_mt.range_cursor',
847: 30);
848: ELSIF p_report_type = 'STW2' THEN
849: p_sqlstr := '
850: SELECT DISTINCT
891: and hoi.org_information_context =
892: ''1099R Magnetic Report Rules''
893: )
894: order by to_number(paa.serial_number)';
895: hr_utility.set_location( 'pay_us_mmref_reporting_mt.range_cursor',
896: 40);
897: END IF;
898: -- END IF;
899: END range_cursor;
1139: l_gre_id := -1;
1140: l_error_flag := 'N';
1141:
1142: -- Get the report parameters. These define the report being run.
1143: hr_utility.set_location( 'pay_us_mmref_reporting_mt.create_assignement_act',
1144: 10);
1145: get_report_parameters(
1146: p_pactid,
1147: l_year_start,
1153: );
1154: --Currently all reports group by GRE
1155: l_group_by_gre := TRUE;
1156: --Open the appropriate cursor
1157: hr_utility.set_location( 'pay_us_mmref_reporting_mt.create_assignement_act',
1158: 20);
1159: IF l_report_type = 'FEDW2' THEN
1160: OPEN c_federal;
1161: --
1173: l_tax_unit_id,
1174: l_effective_end_date,
1175: l_assignment_action_id;
1176: hr_utility.set_location(
1177: 'pay_us_mmref_reporting_mt.create_assignement_act', 30);
1178: EXIT WHEN c_federal%NOTFOUND;
1179: ELSIF l_report_type = 'STW2' and l_state_abbrev = 'PR' THEN
1180: FETCH c_pr_state INTO l_person_id,
1181: l_assignment_id,
1183: l_effective_end_date,
1184: l_assignment_action_id,
1185: l_w2_box17;
1186: hr_utility.set_location(
1187: 'pay_us_mmref_reporting_mt.create_assignement_act', 40);
1188: EXIT WHEN c_pr_state%NOTFOUND;
1189: -- Check the state Tax rules if new gre
1190: -- Set the Error Flag to Y so that Action Creation will Error
1191: -- At the End
1209: l_effective_end_date,
1210: l_assignment_action_id,
1211: l_w2_box17;
1212: hr_utility.set_location(
1213: 'pay_us_mmref_reporting_mt.create_assignement_act', 40);
1214: EXIT WHEN c_state%NOTFOUND;
1215: -- Check the state Tax rules if new gre
1216: -- Set the Error Flag to Y so that Action Creation will Error
1217: -- At the End
1240: (NOT l_group_by_gre AND
1241: l_person_id = l_prev_person_id)) THEN
1242: --Do Nothing
1243: hr_utility.set_location(
1244: 'pay_us_mmref_reporting_mt.create_assignement_act', 50);
1245: NULL;
1246: ELSE
1247: --Create the assignment action for the record
1248: hr_utility.trace('Assignment Fetched - ');
1305: SELECT pay_assignment_actions_s.nextval
1306: INTO lockingactid
1307: FROM dual;
1308: hr_utility.set_location(
1309: 'pay_us_mmref_reporting_mt.create_assignement_act', 60);
1310: hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
1311: p_chunk, l_tax_unit_id);
1312: hr_utility.set_location(
1313: 'pay_us_mmref_reporting_mt.create_assignement_act', 70);
1309: 'pay_us_mmref_reporting_mt.create_assignement_act', 60);
1310: hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
1311: p_chunk, l_tax_unit_id);
1312: hr_utility.set_location(
1313: 'pay_us_mmref_reporting_mt.create_assignement_act', 70);
1314: --update serial number for highly compensated people for the
1315: --state W2.
1316: /*IF l_report_type = 'STW2' THEN
1317: hr_utility.set_location(
1314: --update serial number for highly compensated people for the
1315: --state W2.
1316: /*IF l_report_type = 'STW2' THEN
1317: hr_utility.set_location(
1318: 'pay_us_mmref_reporting_mt.create_assignement_act', 80);
1319: IF l_w2_box17 > 9999999.99 THEN
1320: UPDATE pay_assignment_actions
1321: SET serial_number = 999999
1322: WHERE assignment_action_id = lockingactid;
1323: END IF;
1324: END IF;*/ -- 4490252
1325: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
1326: hr_utility.set_location(
1327: 'pay_us_mmref_reporting_mt.create_assignement_act', 90);
1328: hr_utility.trace('Interlock Created - ');
1329: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
1330: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
1331: --Store the current person/GRE for comparision during the
1683: p_effective_date date
1684: ) RETURN varchar2 IS
1685:
1686: --
1687: l_proc varchar2(100) := 'pay_us_mmref_reporting_mt.get_report_category';
1688: l_code varchar2(2);
1689: l_ue_id NUMBER;
1690: --
1691: --
1792: p_Federal_EIN := ' ';
1793: return l_tax_unit_name;
1794: END get_tax_unit_info;
1795:
1796: END pay_us_mmref_reporting_mt;