[Home] [Help]
57: FROM pay_state_rules SR,
58: hr_organization_information HOI,
59: pay_payroll_actions PPA,
60: pay_payroll_actions PPA1
61: WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
62: ('TRANSFER_PAYROLL_ACTION_ID')
63: AND ppa1.effective_date = ppa.effective_date
64: AND ppa1.report_qualifier = sr.state_code
65: AND HOI.organization_id =
62: ('TRANSFER_PAYROLL_ACTION_ID')
63: AND ppa1.effective_date = ppa.effective_date
64: AND ppa1.report_qualifier = sr.state_code
65: AND HOI.organization_id =
66: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
67: AND SR.state_code =
68: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
69: AND HOI.org_information_context = 'W2 Reporting Rules'
70: AND PPA.report_type = 'YREND'
64: AND ppa1.report_qualifier = sr.state_code
65: AND HOI.organization_id =
66: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
67: AND SR.state_code =
68: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
69: AND HOI.org_information_context = 'W2 Reporting Rules'
70: AND PPA.report_type = 'YREND'
71: AND HOI.ORGANIZATION_ID
72: = substr(PPA.legislative_parameters,
72: = substr(PPA.legislative_parameters,
73: instr(PPA.legislative_parameters,
74: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
75: AND to_char(PPA.effective_date,'YYYY')
76: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
77: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
78:
79: /* Context and Parameter Set in the cursor are
80: Context :
108: 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
109: FROM hr_organization_information HOI,
110: pay_payroll_actions PPA
111: WHERE HOI.organization_id =
112: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
113: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
114: AND HOI.org_information_context = 'W2 Reporting Rules'
115: AND PPA.report_type = 'YREND'
116: AND HOI.ORGANIZATION_ID
109: FROM hr_organization_information HOI,
110: pay_payroll_actions PPA
111: WHERE HOI.organization_id =
112: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
113: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
114: AND HOI.org_information_context = 'W2 Reporting Rules'
115: AND PPA.report_type = 'YREND'
116: AND HOI.ORGANIZATION_ID
117: = substr(PPA.legislative_parameters,
117: = substr(PPA.legislative_parameters,
118: instr(PPA.legislative_parameters,
119: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
120: AND to_char(PPA.effective_date,'YYYY')
121: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
122: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
123:
124: --
125: -- Sets up the tax unit context for each employer to be reported on NB. sets
153: 'TRANSFER_EMP_CODE=P', 'R'
154: FROM hr_all_organization_units hou,
155: pay_payroll_actions ppa,
156: pay_assignment_actions AA
157: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
158: ('TRANSFER_PAYROLL_ACTION_ID')
159: AND ppa.report_type = 'YREND'
160: AND to_char(ppa.effective_date,'YYYY')
161: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
157: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
158: ('TRANSFER_PAYROLL_ACTION_ID')
159: AND ppa.report_type = 'YREND'
160: AND to_char(ppa.effective_date,'YYYY')
161: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
162: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
163: AND AA.tax_unit_id
164: = substr(ppa.legislative_parameters,
165: instr(ppa.legislative_parameters,
177: 'TRANSFER_TAX_UNIT_ID=P', AA.tax_unit_id
178: FROM hr_all_organization_units hou,
179: pay_payroll_actions ppa,
180: pay_assignment_actions AA
181: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
182: ('TRANSFER_PAYROLL_ACTION_ID')
183: AND ppa.report_type = 'YREND'
184: AND to_char(ppa.effective_date,'YYYY')
185: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
181: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
182: ('TRANSFER_PAYROLL_ACTION_ID')
183: AND ppa.report_type = 'YREND'
184: AND to_char(ppa.effective_date,'YYYY')
185: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
186: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
187: AND AA.tax_unit_id
188: = substr(ppa.legislative_parameters,
189: instr(ppa.legislative_parameters,
202: 'TRANSFER_EMP_CODE=P' , 'R'
203: FROM hr_all_organization_units hou,
204: pay_payroll_actions ppa,
205: pay_assignment_actions AA
206: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
207: ('TRANSFER_PAYROLL_ACTION_ID')
208: AND ppa.report_type = 'YREND'
209: AND to_char(ppa.effective_date,'YYYY')
210: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
206: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
207: ('TRANSFER_PAYROLL_ACTION_ID')
208: AND ppa.report_type = 'YREND'
209: AND to_char(ppa.effective_date,'YYYY')
210: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
211: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
212: AND AA.tax_unit_id
213: = substr(ppa.legislative_parameters,
214: instr(ppa.legislative_parameters,
224: 'TRANSFER_EMP_CODE=P' , 'Q'
225: FROM hr_all_organization_units hou,
226: pay_payroll_actions ppa,
227: pay_assignment_actions AA
228: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
229: ('TRANSFER_PAYROLL_ACTION_ID')
230: AND ppa.report_type = 'YREND'
231: AND to_char(ppa.effective_date,'YYYY')
232: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
228: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
229: ('TRANSFER_PAYROLL_ACTION_ID')
230: AND ppa.report_type = 'YREND'
231: AND to_char(ppa.effective_date,'YYYY')
232: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
233: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
234: AND AA.tax_unit_id
235: = substr(ppa.legislative_parameters,
236: instr(ppa.legislative_parameters,
248: --
249:
250: CURSOR mmrf_employee_main IS
251: /* SELECT 'TRANSFER_ACT_ID=P',
252: pay_magtape_generic.get_parameter_value(
253: 'TRANSFER_ACT_ID')
254: FROM DUAL;
255: */
256: SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
255: */
256: SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
257: FROM pay_assignment_actions paa
258: WHERE paa.payroll_action_id =
259: pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
260: AND paa.tax_unit_id =
261: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID');
262:
263:
257: FROM pay_assignment_actions paa
258: WHERE paa.payroll_action_id =
259: pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
260: AND paa.tax_unit_id =
261: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID');
262:
263:
264:
265: CURSOR mmrf_employee IS
264:
265: CURSOR mmrf_employee IS
266: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
267: 'ASSIGNMENT_ID=C', AA.assignment_id,
268: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
269: (PA.effective_date, AA.assignment_id)),
270: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
271: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
272: FROM per_all_people_f PE,
266: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
267: 'ASSIGNMENT_ID=C', AA.assignment_id,
268: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
269: (PA.effective_date, AA.assignment_id)),
270: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
271: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
272: FROM per_all_people_f PE,
273: per_all_assignments_f SS,
274: pay_action_interlocks AI,
273: per_all_assignments_f SS,
274: pay_action_interlocks AI,
275: pay_assignment_actions AA,
276: pay_payroll_actions PA
277: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
278: ('TRANSFER_PAYROLL_ACTION_ID')
279: AND AA.payroll_action_id = PA.payroll_action_id
280: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
281: ('TAX_UNIT_ID')
276: pay_payroll_actions PA
277: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
278: ('TRANSFER_PAYROLL_ACTION_ID')
279: AND AA.payroll_action_id = PA.payroll_action_id
280: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
281: ('TAX_UNIT_ID')
282: AND AI.locking_action_id = AA.assignment_action_id
283: AND SS.assignment_id = AA.assignment_id
284: AND PE.person_id = SS.person_id
296:
297: CURSOR govt_mmrf_employee IS
298: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
299: 'ASSIGNMENT_ID=C', AA.assignment_id,
300: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
301: (PA.effective_date, AA.assignment_id)),
302: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
303: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
304: FROM per_all_people_f PE,
298: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
299: 'ASSIGNMENT_ID=C', AA.assignment_id,
300: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
301: (PA.effective_date, AA.assignment_id)),
302: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
303: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
304: FROM per_all_people_f PE,
305: per_all_assignments_f SS,
306: pay_action_interlocks AI,
308: pay_assignment_actions paa,
309: pay_payroll_actions PA,
310: ff_archive_items arch,
311: ff_user_entities fue
312: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
313: ('TRANSFER_PAYROLL_ACTION_ID')
314: AND AA.payroll_action_id = PA.payroll_action_id
315: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
316: ('TAX_UNIT_ID')
311: ff_user_entities fue
312: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
313: ('TRANSFER_PAYROLL_ACTION_ID')
314: AND AA.payroll_action_id = PA.payroll_action_id
315: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
316: ('TAX_UNIT_ID')
317: AND AI.locking_action_id = AA.assignment_action_id
318: AND SS.assignment_id = AA.assignment_id
319: AND PE.person_id = SS.person_id
326: AND SS.effective_end_date >= PA.start_date
327: AND SS.assignment_type = 'E'
328: AND LEAST(SS.effective_end_date, PA.effective_date)
329: between PE.effective_start_date and PE.effective_end_date
330: AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
331: ('PAYROLL_ACTION_ID')
332: AND paa.assignment_id = AA.assignment_id
333: AND arch.context1 = paa.assignment_action_id
334: AND arch.user_entity_id = fue.user_entity_id
332: AND paa.assignment_id = AA.assignment_id
333: AND arch.context1 = paa.assignment_action_id
334: AND arch.user_entity_id = fue.user_entity_id
335: AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
336: AND arch.value = pay_magtape_generic.get_parameter_value
337: ('TRANSFER_EMP_CODE')
338: ORDER BY PE.last_name, PE.first_name, PE.middle_names;
339:
340:
344:
345: CURSOR mmrf_pr_employee IS
346: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
347: 'ASSIGNMENT_ID=C', AA.assignment_id,
348: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
349: (PA.effective_date, AA.assignment_id)),
350: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
351: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
352: FROM per_all_people_f PE,
346: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
347: 'ASSIGNMENT_ID=C', AA.assignment_id,
348: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
349: (PA.effective_date, AA.assignment_id)),
350: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
351: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
352: FROM per_all_people_f PE,
353: per_all_assignments_f SS,
354: pay_action_interlocks AI,
353: per_all_assignments_f SS,
354: pay_action_interlocks AI,
355: pay_assignment_actions AA,
356: pay_payroll_actions PA
357: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
358: ('TRANSFER_PAYROLL_ACTION_ID')
359: AND AA.payroll_action_id = PA.payroll_action_id
360: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
361: ('TAX_UNIT_ID')
356: pay_payroll_actions PA
357: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
358: ('TRANSFER_PAYROLL_ACTION_ID')
359: AND AA.payroll_action_id = PA.payroll_action_id
360: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
361: ('TAX_UNIT_ID')
362: AND AI.locking_action_id = AA.assignment_action_id
363: AND SS.assignment_id = AA.assignment_id
364: AND PE.person_id = SS.person_id
387: ff_database_items fdi,
388: ff_archive_item_contexts faic, -- JD
389: pay_assignment_actions paa
390: where paa.assignment_action_id
391: = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
392: and paa.assignment_action_id = fai.context1
393: and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
394: and fdi.user_entity_id = fai.user_entity_id
395: and faic.archive_item_id = fai.archive_item_id
409: ff_database_items fdi,
410: ff_archive_item_contexts faic, -- JD
411: pay_assignment_actions paa
412: where paa.assignment_action_id =
413: pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
414: and paa.assignment_action_id = fai.context1
415: and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
416: fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
417: fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')
426:
427:
428: CURSOR mmrf_employee_act IS
429: SELECT 'TRANSFER_ACT_ID=P',
430: pay_magtape_generic.get_parameter_value(
431: 'TRANSFER_ACT_ID'),
432: 'PAYROLL_ACTION_ID=C', pay_magtape_generic.get_parameter_value
433: ('TRANSFER_PAYROLL_ACTION_ID'),
434: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
428: CURSOR mmrf_employee_act IS
429: SELECT 'TRANSFER_ACT_ID=P',
430: pay_magtape_generic.get_parameter_value(
431: 'TRANSFER_ACT_ID'),
432: 'PAYROLL_ACTION_ID=C', pay_magtape_generic.get_parameter_value
433: ('TRANSFER_PAYROLL_ACTION_ID'),
434: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
435: 'ASSIGNMENT_ID=C', AA.assignment_id,
436: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
432: 'PAYROLL_ACTION_ID=C', pay_magtape_generic.get_parameter_value
433: ('TRANSFER_PAYROLL_ACTION_ID'),
434: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
435: 'ASSIGNMENT_ID=C', AA.assignment_id,
436: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
437: (PA.effective_date, AA.assignment_id)),
438: 'JURISDICTION_CODE=C', pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
439: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id,
440: 'TAX_UNIT_ID=C',AA.TAX_UNIT_ID
434: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
435: 'ASSIGNMENT_ID=C', AA.assignment_id,
436: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
437: (PA.effective_date, AA.assignment_id)),
438: 'JURISDICTION_CODE=C', pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
439: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id,
440: 'TAX_UNIT_ID=C',AA.TAX_UNIT_ID
441: FROM per_all_people_f PE,
442: per_all_assignments_f SS,
442: per_all_assignments_f SS,
443: pay_action_interlocks AI,
444: pay_assignment_actions AA,
445: pay_payroll_actions PA
446: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
447: ('TRANSFER_PAYROLL_ACTION_ID')
448: AND aa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
449: AND AA.payroll_action_id = PA.payroll_action_id
450: AND AI.locking_action_id = AA.assignment_action_id
444: pay_assignment_actions AA,
445: pay_payroll_actions PA
446: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
447: ('TRANSFER_PAYROLL_ACTION_ID')
448: AND aa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
449: AND AA.payroll_action_id = PA.payroll_action_id
450: AND AI.locking_action_id = AA.assignment_action_id
451: AND SS.assignment_id = AA.assignment_id
452: AND PE.person_id = SS.person_id