1 package pay_us_mmref_local as
2 /* $Header: pyusmmle.pkh 120.0.12000000.1 2007/01/18 02:39:31 appldev noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_mmref_reporting
10
11 Purpose
12
13 The purpose of this package is to support the generation of local
14 Magnetic media in MMREF - 1 Format. This magnetic tapes are for
15 us legilsative requirements. Currently we supprt the CCA and RITA
16 Reporting.
17
18 Notes
19 The generation of each magnetic tape report is a two stage process i.e.
20 1. Check if the year end pre-processor has been run for all the GREs.
21 If not, then error out without processing further.
22 2. Create a payroll action for the report. Identify all the assignments
23 to be reported and record an assignment action against the payroll
24 action for each one of them.
25 3. Run the generic magnetic tape process which will
26 drive off the data created in stage two. This will result in the
27 production of a structured ascii file which can be transferred to
28 magnetic tape and sent to the relevant authority.
29
30
31 History
32 Date Author Verion Bug Details
33 ---------------------------------------------------------------------------
34 22-jan-02 djoshi 115.0 Created
35 04-Nov-02 ppanda 115.1 For locality changes made to
36 get_report_parameters procedure
37 Cursor lc_mmrf_submitter modified to
38 have new parameter for State_code and
39 locality_code
40 15-Nov-02 ppanda 115.2 File is gscc compliant
41 02-Dec-02 ppanda 115.3 Nocopy hint added to OUT and IN OUT parameters
42 28-feb-03 djoshi 115.5 Changed the code for Locals . Cursor
43 OH_LC_EMPLOYEE was changed.
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 -- Sets up the tax unit context for the Submitter
53
54 /* Context and Parameter Set in the cursor are
55
56 Context :
57 --------------------------------------
58 TAX_UNIT_ID - Submitter's Tax Unit ID
59 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
60 ASSIGNMENT_ID - Required for call to function - context not used
61 in the for Submitter
62 Date Earned - Always set to Effective date ie. in this case
63 for Mag tapes to 31-DEC-YYYY, in case of SQWL
64 this will be diffrent.
65 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
66
67 Parameters :
68 Transfer_HIGH_COUNT
69 TRANSFER_SCHOOL_DISTRICT
70 TRANSFER_COUNTY
71 TRANSFER_2678_FILER
72 -- Following two parameters added for New locality
73 TRANSFER_LOCALITY_CODE
74 TRANSFER_STATE_CODE
75 */
76
77 /* Transmitter for the Local Megnetic Media in MMREF Format */
78
79 CURSOR lc_mmrf_submitter
80 IS
81 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
82 'JURISDICTION_CODE=C', SR.jurisdiction_code,
83 'TRANSFER_JD=P', SR.jurisdiction_code,
84 'ASSIGNMENT_ID=C' , '-1',
85 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
86 'TRANSFER_HIGH_COUNT=P', '0',
87 'TRANSFER_SCHOOL_DISTRICT=P', '-5',
88 'TRANSFER_COUNTY=P', '-1',
89 'TRANSFER_2678_FILER=P', HOI.org_information8,
90 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id,
91 'BUSINESS_GROUP_ID=C',PPA.business_group_id,
92 'TRANSFER_LOCALITY_CODE=P',
93 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa1.payroll_action_id,
94 'LC'),
95 -- substr(PPA1.legislative_parameters,instr(PPA1.legislative_parameters,'LOCALITY_CODE=')
96 -- + length('LOCALITY_CODE=')),
97 'TRANSFER_STATE_CODE=P', substr(sr.jurisdiction_code,1,2)
98 FROM pay_state_rules SR,
99 hr_organization_information HOI,
100 pay_payroll_actions PPA,
101 pay_payroll_actions PPA1
102 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
103 ('TRANSFER_PAYROLL_ACTION_ID')
104 AND ppa1.effective_date = ppa.effective_date
105 --AND ppa1.report_qualifier = sr.state_code
106 --CPE
107 AND substr(ppa1.report_qualifier,1,2) = sr.state_code
108 AND HOI.organization_id =
109 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
110 AND SR.state_code =
111 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
112 AND HOI.org_information_context = 'W2 Reporting Rules'
113 AND PPA.report_type = 'YREND'
114 AND HOI.ORGANIZATION_ID =
115 substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=')
116 + length('TRANSFER_GRE='))
117 AND to_char(PPA.effective_date,'YYYY') =
118 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
119 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
120
121
122
123 /* Context and Parameter Set in the cursor are
124
125 Context :
126 --------------------------------------
127 TAX_UNIT_ID - Tax Unit ID of GRE
128 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
129
130 Parameters :
131 TAX_UNIT_ID - Id of the GRE
132 TAX_UNIT_NAME - Name of GRE
133 */
134
135 --
136 --
137 --
138
139 CURSOR lc_mmrf_employer
140 IS
141 SELECT DISTINCT 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
142 'TAX_UNIT_ID=C' , AA.tax_unit_id,
143 'TAX_UNIT_ID=P' , AA.tax_unit_id,
144 'TAX_UNIT_NAME=P' , hou.name,
145 'TRANSFER_EMP_CODE=P', 'R'
146 FROM
147 hr_all_organization_units hou,
148 pay_payroll_actions ppa,
149 pay_assignment_actions AA
150 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
151 ('TRANSFER_PAYROLL_ACTION_ID')
152 AND ppa.report_type = 'YREND'
153 AND to_char(ppa.effective_date,'YYYY') =
154 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
155 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
156 AND AA.tax_unit_id =
157 substr(ppa.legislative_parameters,
158 instr(ppa.legislative_parameters,
159 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
160 AND hou.organization_id = AA.tax_unit_id
161 order by hou.name;
162
163
164
165 --
166 --
167 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
168 -- for an employee. The date_earned context is set to be the least of the
169 -- end of the period being reported and the maximum end date of the
170 -- assignment. This ensures that personal information ie. name etc... is
171 -- current relative to the period being reported on.
172 --
173
174
175 CURSOR lc_mmrf_employee
176 IS
177 SELECT
178 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
179 'ASSIGNMENT_ID=C', AA.assignment_id,
180 'DATE_EARNED=C',
181 fnd_date.date_to_canonical(pay_magtape_generic.date_earned(PA.effective_date,
182 AA.assignment_id)),
183 'JURISDICTION_CODE=C',
184 pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
185 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
186 FROM per_all_people_f PE,
187 per_all_assignments_f SS,
188 pay_action_interlocks AI,
189 pay_assignment_actions AA,
190 pay_payroll_actions PA
191 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
192 ('TRANSFER_PAYROLL_ACTION_ID') AND
193 AA.payroll_action_id = PA.payroll_action_id AND
194 AA.tax_unit_id = pay_magtape_generic.get_parameter_value
195 ('TAX_UNIT_ID') AND
196 AI.locking_action_id = AA.assignment_action_id AND
197 SS.assignment_id = AA.assignment_id AND
198 PE.person_id = SS.person_id AND
199 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
200 SS.effective_start_date and SS.effective_end_date AND
201 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
202 PE.effective_start_date and PE.effective_end_date
203 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
204
205
206 /* RITA and CCA Cursor */
207
208
209 CURSOR oh_lc_employee
210 IS
211 SELECT
212 'JURISDICTION_CODE=C',ltrim(rtrim(faic.context)),
213 'TRANSFER_YE_JURISDICTION_CODE=P',ltrim(rtrim(faic.context)),
214 'TRANSFER_YE_REC_TYPE=P','1'
215 from
216 ff_archive_items fai,
217 ff_contexts fc, -- JD
218 ff_database_items fdi,
219 ff_archive_item_contexts faic, -- JD
220 pay_payroll_actions ppa,
221 pay_assignment_actions paa
222 where
223 paa.assignment_action_id =
224 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
225 and paa.assignment_action_id = fai.context1
226 and fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
227 and fdi.user_entity_id = fai.user_entity_id
228 and faic.archive_item_id = fai.archive_item_id
229 and fc.context_name = 'JURISDICTION_CODE'
230 and faic.context_id = fc.context_id
231 and value <> '0'
232 and paa.payroll_action_id = ppa.payroll_action_id
233 and exists
234 (
235 /* Code has to join like becuase puctif is not
236 maintained as truely date tracked table
237 */
238 SELECT '1'
239 FROM pay_us_city_tax_info_f puctif
240 WHERE rtrim(ltrim(faic.context)) = puctif.jurisdiction_code
241 AND puctif.effective_start_date < ppa.effective_date
242 AND puctif.effective_end_date >= ppa.effective_date
243 AND puctif.city_information1 like
244 pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE')||'%'
245 )
246 order by faic.context ;
247
248
249
250 FUNCTION bal_db_item
251 (
252 p_db_item_name VARCHAR2
253 ) RETURN NUMBER;
254
255 PROCEDURE get_report_parameters
256 (
257 p_pactid IN NUMBER,
258 p_year_start IN OUT nocopy DATE,
259 p_year_end IN OUT nocopy DATE,
260 p_state_abbrev IN OUT nocopy VARCHAR2,
261 p_state_code IN OUT nocopy VARCHAR2,
262 p_report_type IN OUT nocopy VARCHAR2,
263 p_business_group_id IN OUT nocopy NUMBER,
264 -- Following parameter added for Locality Code
265 p_locality_code IN OUT nocopy VARCHAR2
266 );
267
268 FUNCTION get_balance_value (
269 p_balance_name VARCHAR2,
270 p_tax_unit_id NUMBER,
271 p_state_abbrev VARCHAR2,
272 p_assignment_id NUMBER,
273 p_effective_date DATE
274 ) RETURN NUMBER;
275
276 FUNCTION preprocess_check
277 (
278 p_pactid NUMBER,
279 p_year_start DATE,
280 p_year_end DATE,
281 p_business_group_id NUMBER,
282 p_state_abbrev VARCHAR2,
283 p_state_code VARCHAR2,
284 p_report_type VARCHAR2
285 ) RETURN BOOLEAN;
286
287 PROCEDURE range_cursor (
288 p_pactid IN NUMBER,
289 p_sqlstr OUT nocopy VARCHAR2
290 );
291
292 PROCEDURE create_assignment_act(
293 p_pactid IN NUMBER,
294 p_stperson IN NUMBER,
295 p_endperson IN NUMBER,
296 p_chunk IN NUMBER );
297
298 FUNCTION check_er_data (
299 p_pactid NUMBER,
300 p_ein_user_id NUMBER
301 ) RETURN varchar2;
302
303
304 FUNCTION check_state_er_data (
305 p_pactid NUMBER,
306 p_tax_unit NUMBER,
307 p_jurisdictions varchar2
308 ) RETURN varchar2;
309
310
311 PROCEDURE archive_eoy_data(
312 p_pactid IN NUMBER,
313 p_tax_id IN NUMBER );
314
315
316 PROCEDURE archive_state_eoy_data(
317 p_pactid IN NUMBER,
318 p_tax_id IN NUMBER,
319 p_state_code IN VARCHAR2);
320
321 FUNCTION check_state_data (
322 p_payroll_action_id NUMBER,
323 p_transfer_state varchar2
324 ) RETURN varchar2;
325
326
327 END pay_us_mmref_local;
[Home] [Help]