[Home] [Help]
95: FROM pay_state_rules SR,
96: hr_organization_information HOI,
97: pay_payroll_actions PPA,
98: pay_payroll_actions PPA1
99: WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
100: ('TRANSFER_PAYROLL_ACTION_ID')
101: AND ppa1.effective_date = ppa.effective_date
102: AND ppa1.report_qualifier = sr.state_code
103: AND HOI.organization_id =
100: ('TRANSFER_PAYROLL_ACTION_ID')
101: AND ppa1.effective_date = ppa.effective_date
102: AND ppa1.report_qualifier = sr.state_code
103: AND HOI.organization_id =
104: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
105: AND SR.state_code =
106: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
107: AND HOI.org_information_context = 'W2 Reporting Rules'
108: AND PPA.report_type = 'YREND'
102: AND ppa1.report_qualifier = sr.state_code
103: AND HOI.organization_id =
104: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
105: AND SR.state_code =
106: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
107: AND HOI.org_information_context = 'W2 Reporting Rules'
108: AND PPA.report_type = 'YREND'
109: AND HOI.ORGANIZATION_ID
110: = substr(PPA.legislative_parameters,
110: = substr(PPA.legislative_parameters,
111: instr(PPA.legislative_parameters,
112: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
113: AND to_char(PPA.effective_date,'YYYY')
114: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
115: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
116:
117: /* Context and Parameter Set in the cursor are
118: Context :
146: 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
147: FROM hr_organization_information HOI,
148: pay_payroll_actions PPA
149: WHERE HOI.organization_id =
150: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
151: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
152: AND HOI.org_information_context = 'W2 Reporting Rules'
153: AND PPA.report_type = 'YREND'
154: AND HOI.ORGANIZATION_ID
147: FROM hr_organization_information HOI,
148: pay_payroll_actions PPA
149: WHERE HOI.organization_id =
150: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
151: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
152: AND HOI.org_information_context = 'W2 Reporting Rules'
153: AND PPA.report_type = 'YREND'
154: AND HOI.ORGANIZATION_ID
155: = substr(PPA.legislative_parameters,
155: = substr(PPA.legislative_parameters,
156: instr(PPA.legislative_parameters,
157: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
158: AND to_char(PPA.effective_date,'YYYY')
159: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
160: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
161:
162: --
163: -- Sets up the tax unit context for each employer to be reported on NB. sets
191: 'TRANSFER_EMP_CODE=P', 'R'
192: FROM hr_all_organization_units hou,
193: pay_payroll_actions ppa,
194: pay_assignment_actions AA
195: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
196: ('TRANSFER_PAYROLL_ACTION_ID')
197: AND ppa.report_type = 'YREND'
198: AND to_char(ppa.effective_date,'YYYY')
199: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
195: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
196: ('TRANSFER_PAYROLL_ACTION_ID')
197: AND ppa.report_type = 'YREND'
198: AND to_char(ppa.effective_date,'YYYY')
199: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
200: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
201: AND AA.tax_unit_id
202: = substr(ppa.legislative_parameters,
203: instr(ppa.legislative_parameters,
215: 'TRANSFER_TAX_UNIT_ID=P', AA.tax_unit_id
216: FROM hr_all_organization_units hou,
217: pay_payroll_actions ppa,
218: pay_assignment_actions AA
219: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
220: ('TRANSFER_PAYROLL_ACTION_ID')
221: AND ppa.report_type = 'YREND'
222: AND to_char(ppa.effective_date,'YYYY')
223: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
219: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
220: ('TRANSFER_PAYROLL_ACTION_ID')
221: AND ppa.report_type = 'YREND'
222: AND to_char(ppa.effective_date,'YYYY')
223: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
224: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
225: AND AA.tax_unit_id
226: = substr(ppa.legislative_parameters,
227: instr(ppa.legislative_parameters,
240: 'TRANSFER_EMP_CODE=P' , 'R'
241: FROM hr_all_organization_units hou,
242: pay_payroll_actions ppa,
243: pay_assignment_actions AA
244: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
245: ('TRANSFER_PAYROLL_ACTION_ID')
246: AND ppa.report_type = 'YREND'
247: AND to_char(ppa.effective_date,'YYYY')
248: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
244: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
245: ('TRANSFER_PAYROLL_ACTION_ID')
246: AND ppa.report_type = 'YREND'
247: AND to_char(ppa.effective_date,'YYYY')
248: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
249: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
250: AND AA.tax_unit_id
251: = substr(ppa.legislative_parameters,
252: instr(ppa.legislative_parameters,
262: 'TRANSFER_EMP_CODE=P' , 'Q'
263: FROM hr_all_organization_units hou,
264: pay_payroll_actions ppa,
265: pay_assignment_actions AA
266: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
267: ('TRANSFER_PAYROLL_ACTION_ID')
268: AND ppa.report_type = 'YREND'
269: AND to_char(ppa.effective_date,'YYYY')
270: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
266: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
267: ('TRANSFER_PAYROLL_ACTION_ID')
268: AND ppa.report_type = 'YREND'
269: AND to_char(ppa.effective_date,'YYYY')
270: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
271: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
272: AND AA.tax_unit_id
273: = substr(ppa.legislative_parameters,
274: instr(ppa.legislative_parameters,
286: --
287:
288: CURSOR mmrf_employee_main IS
289: /* SELECT 'TRANSFER_ACT_ID=P',
290: pay_magtape_generic.get_parameter_value(
291: 'TRANSFER_ACT_ID')
292: FROM DUAL;
293: */
294: SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
293: */
294: SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
295: FROM pay_assignment_actions paa
296: WHERE paa.payroll_action_id =
297: pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
298: AND paa.tax_unit_id =
299: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID');
300:
301:
295: FROM pay_assignment_actions paa
296: WHERE paa.payroll_action_id =
297: pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
298: AND paa.tax_unit_id =
299: pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID');
300:
301:
302:
303: CURSOR mmrf_employee IS
302:
303: CURSOR mmrf_employee IS
304: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
305: 'ASSIGNMENT_ID=C', AA.assignment_id,
306: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
307: (PA.effective_date, AA.assignment_id)),
308: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
309: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
310: FROM per_all_people_f PE,
304: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
305: 'ASSIGNMENT_ID=C', AA.assignment_id,
306: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
307: (PA.effective_date, AA.assignment_id)),
308: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
309: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
310: FROM per_all_people_f PE,
311: per_all_assignments_f SS,
312: pay_action_interlocks AI,
311: per_all_assignments_f SS,
312: pay_action_interlocks AI,
313: pay_assignment_actions AA,
314: pay_payroll_actions PA
315: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
316: ('TRANSFER_PAYROLL_ACTION_ID')
317: AND AA.payroll_action_id = PA.payroll_action_id
318: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
319: ('TAX_UNIT_ID')
314: pay_payroll_actions PA
315: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
316: ('TRANSFER_PAYROLL_ACTION_ID')
317: AND AA.payroll_action_id = PA.payroll_action_id
318: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
319: ('TAX_UNIT_ID')
320: AND AI.locking_action_id = AA.assignment_action_id
321: AND SS.assignment_id = AA.assignment_id
322: AND PE.person_id = SS.person_id
335:
336: CURSOR govt_mmrf_employee IS
337: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
338: 'ASSIGNMENT_ID=C', AA.assignment_id,
339: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
340: (PA.effective_date, AA.assignment_id)),
341: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
342: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
343: FROM per_all_people_f PE,
337: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
338: 'ASSIGNMENT_ID=C', AA.assignment_id,
339: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
340: (PA.effective_date, AA.assignment_id)),
341: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
342: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
343: FROM per_all_people_f PE,
344: per_all_assignments_f SS,
345: pay_action_interlocks AI,
347: pay_assignment_actions paa,
348: pay_payroll_actions PA,
349: ff_archive_items arch,
350: ff_user_entities fue
351: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
352: ('TRANSFER_PAYROLL_ACTION_ID')
353: AND AA.payroll_action_id = PA.payroll_action_id
354: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
355: ('TAX_UNIT_ID')
350: ff_user_entities fue
351: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
352: ('TRANSFER_PAYROLL_ACTION_ID')
353: AND AA.payroll_action_id = PA.payroll_action_id
354: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
355: ('TAX_UNIT_ID')
356: AND AI.locking_action_id = AA.assignment_action_id
357: AND SS.assignment_id = AA.assignment_id
358: AND PE.person_id = SS.person_id
365: AND SS.effective_end_date >= PA.start_date
366: AND SS.assignment_type = 'E'
367: AND LEAST(SS.effective_end_date, PA.effective_date)
368: between PE.effective_start_date and PE.effective_end_date
369: AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
370: ('PAYROLL_ACTION_ID')
371: AND paa.assignment_id = AA.assignment_id
372: AND arch.context1 = paa.assignment_action_id
373: AND arch.user_entity_id = fue.user_entity_id
371: AND paa.assignment_id = AA.assignment_id
372: AND arch.context1 = paa.assignment_action_id
373: AND arch.user_entity_id = fue.user_entity_id
374: AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
375: AND arch.value = pay_magtape_generic.get_parameter_value
376: ('TRANSFER_EMP_CODE')
377: ORDER BY PE.last_name, PE.first_name, PE.middle_names;
378:
379:
383:
384: CURSOR mmrf_pr_employee IS
385: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
386: 'ASSIGNMENT_ID=C', AA.assignment_id,
387: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
388: (PA.effective_date, AA.assignment_id)),
389: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
390: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
391: FROM per_all_people_f PE,
385: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
386: 'ASSIGNMENT_ID=C', AA.assignment_id,
387: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
388: (PA.effective_date, AA.assignment_id)),
389: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
390: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
391: FROM per_all_people_f PE,
392: per_all_assignments_f SS,
393: pay_action_interlocks AI,
392: per_all_assignments_f SS,
393: pay_action_interlocks AI,
394: pay_assignment_actions AA,
395: pay_payroll_actions PA
396: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
397: ('TRANSFER_PAYROLL_ACTION_ID')
398: AND AA.payroll_action_id = PA.payroll_action_id
399: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
400: ('TAX_UNIT_ID')
395: pay_payroll_actions PA
396: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
397: ('TRANSFER_PAYROLL_ACTION_ID')
398: AND AA.payroll_action_id = PA.payroll_action_id
399: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
400: ('TAX_UNIT_ID')
401: AND AI.locking_action_id = AA.assignment_action_id
402: AND SS.assignment_id = AA.assignment_id
403: AND PE.person_id = SS.person_id
426: ff_database_items fdi,
427: ff_archive_item_contexts faic, -- JD
428: pay_assignment_actions paa
429: where paa.assignment_action_id
430: = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
431: and paa.assignment_action_id = fai.context1
432: and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
433: and fdi.user_entity_id = fai.user_entity_id
434: and faic.archive_item_id = fai.archive_item_id
448: ff_database_items fdi,
449: ff_archive_item_contexts faic, -- JD
450: pay_assignment_actions paa
451: where paa.assignment_action_id =
452: pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
453: and paa.assignment_action_id = fai.context1
454: and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
455: fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
456: fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')
465:
466:
467: CURSOR mmrf_employee_act IS
468: SELECT 'TRANSFER_ACT_ID=P',
469: pay_magtape_generic.get_parameter_value(
470: 'TRANSFER_ACT_ID'),
471: 'PAYROLL_ACTION_ID=C',pay_magtape_generic.get_parameter_value
472: ('TRANSFER_PAYROLL_ACTION_ID'),
473: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
467: CURSOR mmrf_employee_act IS
468: SELECT 'TRANSFER_ACT_ID=P',
469: pay_magtape_generic.get_parameter_value(
470: 'TRANSFER_ACT_ID'),
471: 'PAYROLL_ACTION_ID=C',pay_magtape_generic.get_parameter_value
472: ('TRANSFER_PAYROLL_ACTION_ID'),
473: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
474: 'ASSIGNMENT_ID=C', AA.assignment_id,
475: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
471: 'PAYROLL_ACTION_ID=C',pay_magtape_generic.get_parameter_value
472: ('TRANSFER_PAYROLL_ACTION_ID'),
473: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
474: 'ASSIGNMENT_ID=C', AA.assignment_id,
475: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
476: (PA.effective_date, AA.assignment_id)),
477: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
478: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id,
479: 'TAX_UNIT_ID=C',AA.TAX_UNIT_ID
473: 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
474: 'ASSIGNMENT_ID=C', AA.assignment_id,
475: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
476: (PA.effective_date, AA.assignment_id)),
477: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
478: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id,
479: 'TAX_UNIT_ID=C',AA.TAX_UNIT_ID
480: FROM per_all_people_f PE,
481: per_all_assignments_f SS,
481: per_all_assignments_f SS,
482: pay_action_interlocks AI,
483: pay_assignment_actions AA,
484: pay_payroll_actions PA
485: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
486: ('TRANSFER_PAYROLL_ACTION_ID')
487: AND aa.assignment_action_id = pay_magtape_generic.get_parameter_value(
488: 'TRANSFER_ACT_ID')
489: AND AA.payroll_action_id = PA.payroll_action_id
483: pay_assignment_actions AA,
484: pay_payroll_actions PA
485: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
486: ('TRANSFER_PAYROLL_ACTION_ID')
487: AND aa.assignment_action_id = pay_magtape_generic.get_parameter_value(
488: 'TRANSFER_ACT_ID')
489: AND AA.payroll_action_id = PA.payroll_action_id
490: AND AI.locking_action_id = AA.assignment_action_id
491: AND SS.assignment_id = AA.assignment_id