1 package pay_us_w2c_reporting_utils AUTHID CURRENT_USER as
2 /* $Header: payusw2creputils.pkh 120.0.12020000.2 2012/10/30 14:50:23 pkoduri ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_w2c_reporting_utils
10
11 File Name
12 payusw2creputils.pkh
13
14 Purpose
15 The purpose of this package is to support the generation of magnetic tape
16 in MMREF - 2 Format. This magnetic tapes are for US legilsative requirements.
17
18
19 Notes
20 The generation of each Federal W-2c magnetic tape report is a two stage
21 process i.e.
22
23 1. Check if the "Employee W-2c Report" is not run for a "W-2c Pre-Process".
24 If not, then error out without processing further.
25
26 2. Create a payroll action for the report. Identify all the assignments
27 to be reported and record an assignment action against the payroll action
28 for each one of them.
29
30 3. Run the generic magnetic tape process which will drive off the data
31 created in stage two. This will result in the production of a structured
32 ascii file which can be transferred to magnetic tape and sent to the
33 relevant authority.
34
35 History
36 Date Author Verion Bug Details
37 ---------------------------------------------------------------------------
38 22-OCT-03 ppanda 115.0 2587381 Created
39 09-DEC-03 ppanda 115.2 3304932 w2c_mmrf2_employee cursor changed to
40 to avoid duplicate RCW or employee wage record
41 due to multiple Employee W-2c Report
42 30-OCT-12 pkoduri 115.3 14286448 Corrections for GRE name length issue.
43
44 ============================================================================*/
45
46 -- 'level_cnt' will allow the cursors to select function results,
47 -- whether it is a standard fuction such as to_char or a function
48 -- defined in a package (with the correct pragma restriction).
49
50 level_cnt NUMBER;
51
52 /* This cursor is for W-2c Magnetic Media Submitter
53
54 Context and Parameter Set in the cursor are
55
56 Context :
57 TAX_UNIT_ID - Submitter's Tax Unit ID
58 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
59 ASSIGNMENT_ID - Required for call to function - context not used
60 in the for Submitter
61 Date Earned - Always set to Effective date ie. in this case
62 for Mag tapes to 31-DEC-YYYY, in case of SQWL
63 this will be diffrent.
64 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
65
66 Parameters :
67 Transfer_HIGH_COUNT
68 TRANSFER_SCHOOL_DISTRICT
69 TRANSFER_COUNTY
70 TRANSFER_2678_FILER
71
72 */
73
74 CURSOR w2c_mmrf2_submitter
75 IS
76 SELECT 'TAX_UNIT_ID=C', HOI.organization_id,
77 'JURISDICTION_CODE=C', 'DUMMY_VALUE',
78 'TRANSFER_JD=P', 'DUMMY_VALUE',
79 'ASSIGNMENT_ID=C', '-1',
80 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
81 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id, -- payroll_action_id of YREND
82 'TRANSFER_HIGH_COUNT=P', '0',
83 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
84 'TRANSFER_COUNTY=P', '-1',
85 'TRANSFER_2678_FILER=P', HOI.org_information8,
86 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
87 FROM hr_organization_information HOI,
88 pay_payroll_actions PPA
89 WHERE HOI.organization_id =
90 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
91 AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
92 AND HOI.org_information_context = 'W2 Reporting Rules'
93 AND PPA.report_type = 'YREND'
94 AND HOI.ORGANIZATION_ID =
95 substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
96 AND to_char(PPA.effective_date,'YYYY') =
97 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
98 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
99
100 --
101 -- Sets up the tax unit context for each employer to be reported on W-2c Mag.
102 -- sets up a parameter holding the tax unit identifier which can then be used
103 -- by subsequent cursors to restrict to employees within the employer.
104 --
105 /* Context and Parameter in the cursor are
106 Payroll_action_id table looks for value related to Year End pre-processor
107 while the pay_assignment_actions looks for assignment actions of Mag. tapes
108 Context :
109 TAX_UNIT_ID - Submitter's Tax Unit ID
110 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
111 ASSIGNMENT_ID - Required for call to function - context not used
112 in the for Submitter
113 Date Earned - Always set to Effective date ie. in this case
114 for Mag tapes to 31-DEC-YYYY, in case of SQWL
115 this will be diffrent.
116 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
117
118 Parameters :
119 TAX_UNIT_ID - To be used in subsequent cusrsor
120 */
121
122 CURSOR w2c_mmrf2_employer IS
123 SELECT DISTINCT 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
124 'TAX_UNIT_ID=C', paa.tax_unit_id,
125 'TAX_UNIT_ID=P', paa.tax_unit_id,
126 'TAX_UNIT_NAME=P', substr(hou.name,1,80) -- Bug# 14286448 Gre length issue
127 FROM
128 hr_all_organization_units hou,
129 pay_payroll_actions ppa,
130 pay_assignment_actions paa
131 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value
132 ('TRANSFER_PAYROLL_ACTION_ID')
133 AND ppa.report_type = 'YREND'
134 AND to_char(ppa.effective_date,'YYYY') =
135 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
136 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
137 AND paa.tax_unit_id =
138 substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
139 AND hou.organization_id = paa.tax_unit_id
140 order by substr(hou.name,1,80); -- Bug# 14286448 Gre length issue
141
142 --
143 -- Used by W-2c Magnetic Media in MMREF-2 format
144 --
145 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
146 -- for an employee. The date_earned context is set to be the least of the
147 -- end of the period being reported and the maximum end date of the
148 -- assignment. This ensures that personal information ie. name etc... is
149 -- current relative to the period being reported on.
150 --
151
152 CURSOR w2c_mmrf2_employee IS
153 SELECT 'ASSIGNMENT_ACTION_ID=C', to_number(substr(AA.serial_number,1,15)), -- latest W2c Pre-Process Assignment Action Id
154 'ASSIGNMENT_ID=C', AA.assignment_id,
155 'DATE_EARNED=C', fnd_date.date_to_canonical(
156 pay_magtape_generic.date_earned(PA.effective_date, AA.assignment_id)),
157 'JURISDICTION_CODE=C', pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
158 'TRANSFER_OLD_ASG_ACTID=P', to_number(substr(AA.serial_number,16,15)), -- Originally Reported Assignment Action Id
159 'TRANSFER_NEW_ASG_ACTID=P', to_number(substr(AA.serial_number,1,15)), -- Corrected Assignment Action Id
160 'TRANSFER_TAX_UNIT_ID=P', AA.tax_unit_id
161 FROM per_all_people_f PE,
162 per_all_assignments_f SS,
163 pay_assignment_actions AA,
164 pay_payroll_actions PA
165 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
166 ('TRANSFER_PAYROLL_ACTION_ID')
167 AND AA.payroll_action_id = PA.payroll_action_id
168 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
169 AND SS.assignment_id = AA.assignment_id
170 AND PE.person_id = SS.person_id
171 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
172 BETWEEN SS.effective_start_date and SS.effective_end_date
173 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
174 BETWEEN PE.effective_start_date and PE.effective_end_date
175 AND exists (select 'x' from pay_action_interlocks pai,
176 pay_assignment_actions paa1,
177 pay_payroll_actions ppa1
178 where paa1.assignment_action_id = AA.assignment_action_id
179 and paa1.assignment_action_id = pai.locking_action_id
180 and ppa1.payroll_action_id = paa1.payroll_action_id)
181 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
182
183
184 FUNCTION bal_db_item (p_db_item_name IN VARCHAR2
185 ) RETURN NUMBER;
186
187 PROCEDURE get_payroll_action_info
188 (p_payroll_action_id in number,
189 p_start_date in out nocopy date,
190 p_end_date in out nocopy date,
191 p_report_type in out nocopy varchar2,
192 p_report_qualifier in out nocopy varchar2,
193 p_business_group_id in out nocopy number
194 );
195
196
197 FUNCTION get_balance_value (p_balance_name IN VARCHAR2,
198 p_tax_unit_id IN NUMBER,
199 p_state_abbrev IN VARCHAR2,
200 p_assignment_id IN NUMBER,
201 p_effective_date IN DATE
202 ) RETURN NUMBER;
203
204 PROCEDURE get_eoy_action_info(p_eoy_effective_date in date
205 ,p_eoy_tax_unit_id in number
206 ,p_assignment_id in number
207 ,p_eoy_pactid out nocopy number
208 ,p_eoy_asg_actid out nocopy number
209 );
210
211 FUNCTION preprocess_check (p_pactid IN NUMBER,
212 p_year_start IN DATE,
213 p_year_end IN DATE,
214 p_business_group_id IN NUMBER
215 ) RETURN BOOLEAN;
216
217 /*******************************************************************
218 ** Range Code to pick all the distinct assignment_ids
219 ** that need to be marked as submitted to governement.
220 *******************************************************************/
221 PROCEDURE w2c_mag_range_cursor( p_payroll_action_id in number
222 ,p_sqlstr out nocopy varchar2);
223
224 /*******************************************************************
225 ** Action Creation Code to create assignment actions for all the
226 ** the assignment_ids that need to be marked as submitted to governement
227 *******************************************************************/
228 PROCEDURE w2c_mag_action_creation( p_payroll_action_id in number
229 ,p_start_person_id in number
230 ,p_end_person_id in number
231 ,p_chunk in number);
232
233 END pay_us_w2c_reporting_utils;
[Home] [Help]