[Home] [Help]
114: FROM pay_state_rules SR,
115: hr_organization_information HOI,
116: pay_payroll_actions PPA,
117: pay_payroll_actions PPA1
118: WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
119: ('TRANSFER_PAYROLL_ACTION_ID')
120: AND ppa1.effective_date = ppa.effective_date
121: AND ppa1.report_qualifier = sr.state_code
122: AND HOI.organization_id =
119: ('TRANSFER_PAYROLL_ACTION_ID')
120: AND ppa1.effective_date = ppa.effective_date
121: AND ppa1.report_qualifier = sr.state_code
122: AND HOI.organization_id =
123: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
124: AND SR.state_code =
125: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
126: AND HOI.org_information_context = 'W2 Reporting Rules'
127: AND PPA.report_type = 'YREND'
121: AND ppa1.report_qualifier = sr.state_code
122: AND HOI.organization_id =
123: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
124: AND SR.state_code =
125: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
126: AND HOI.org_information_context = 'W2 Reporting Rules'
127: AND PPA.report_type = 'YREND'
128: AND HOI.ORGANIZATION_ID
129: = substr(PPA.legislative_parameters,
129: = substr(PPA.legislative_parameters,
130: instr(PPA.legislative_parameters,
131: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
132: AND to_char(PPA.effective_date,'YYYY')
133: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
134: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
135:
136: /* Context and Parameter Set in the cursor are
137: Context :
165: 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
166: FROM hr_organization_information HOI,
167: pay_payroll_actions PPA
168: WHERE HOI.organization_id =
169: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
170: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
171: AND HOI.org_information_context = 'W2 Reporting Rules'
172: AND PPA.report_type = 'YREND'
173: AND HOI.ORGANIZATION_ID
166: FROM hr_organization_information HOI,
167: pay_payroll_actions PPA
168: WHERE HOI.organization_id =
169: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
170: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
171: AND HOI.org_information_context = 'W2 Reporting Rules'
172: AND PPA.report_type = 'YREND'
173: AND HOI.ORGANIZATION_ID
174: = substr(PPA.legislative_parameters,
174: = substr(PPA.legislative_parameters,
175: instr(PPA.legislative_parameters,
176: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
177: AND to_char(PPA.effective_date,'YYYY')
178: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
179: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
180:
181: --
182: -- Sets up the tax unit context for each employer to be reported on NB. sets
210: 'TRANSFER_EMP_CODE=P', 'R'
211: FROM hr_all_organization_units hou,
212: pay_payroll_actions ppa,
213: pay_assignment_actions AA
214: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
215: ('TRANSFER_PAYROLL_ACTION_ID')
216: AND ppa.report_type = 'YREND'
217: AND to_char(ppa.effective_date,'YYYY')
218: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
214: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
215: ('TRANSFER_PAYROLL_ACTION_ID')
216: AND ppa.report_type = 'YREND'
217: AND to_char(ppa.effective_date,'YYYY')
218: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
219: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
220: AND AA.tax_unit_id
221: = substr(ppa.legislative_parameters,
222: instr(ppa.legislative_parameters,
235: 'TRANSFER_EMP_CODE=P' , 'R'
236: FROM hr_all_organization_units hou,
237: pay_payroll_actions ppa,
238: pay_assignment_actions AA
239: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
240: ('TRANSFER_PAYROLL_ACTION_ID')
241: AND ppa.report_type = 'YREND'
242: AND to_char(ppa.effective_date,'YYYY')
243: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
239: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
240: ('TRANSFER_PAYROLL_ACTION_ID')
241: AND ppa.report_type = 'YREND'
242: AND to_char(ppa.effective_date,'YYYY')
243: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
244: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
245: AND AA.tax_unit_id
246: = substr(ppa.legislative_parameters,
247: instr(ppa.legislative_parameters,
257: 'TRANSFER_EMP_CODE=P' , 'Q'
258: FROM hr_all_organization_units hou,
259: pay_payroll_actions ppa,
260: pay_assignment_actions AA
261: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
262: ('TRANSFER_PAYROLL_ACTION_ID')
263: AND ppa.report_type = 'YREND'
264: AND to_char(ppa.effective_date,'YYYY')
265: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
261: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
262: ('TRANSFER_PAYROLL_ACTION_ID')
263: AND ppa.report_type = 'YREND'
264: AND to_char(ppa.effective_date,'YYYY')
265: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
266: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
267: AND AA.tax_unit_id
268: = substr(ppa.legislative_parameters,
269: instr(ppa.legislative_parameters,
284: --
285: CURSOR mmrf_employee IS
286: SELECT /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
287: 'ASSIGNMENT_ID=C', AA.assignment_id,
288: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
289: (PA.effective_date, AA.assignment_id)),
290: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
291: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
292: FROM pay_payroll_actions PA,
286: SELECT /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
287: 'ASSIGNMENT_ID=C', AA.assignment_id,
288: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
289: (PA.effective_date, AA.assignment_id)),
290: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
291: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
292: FROM pay_payroll_actions PA,
293: pay_assignment_actions AA,
294: pay_action_interlocks AI,
293: pay_assignment_actions AA,
294: pay_action_interlocks AI,
295: per_all_assignments_f SS,
296: per_all_people_f PE
297: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
298: ('TRANSFER_PAYROLL_ACTION_ID')
299: AND AA.payroll_action_id = PA.payroll_action_id
300: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
301: ('TAX_UNIT_ID')
296: per_all_people_f PE
297: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
298: ('TRANSFER_PAYROLL_ACTION_ID')
299: AND AA.payroll_action_id = PA.payroll_action_id
300: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
301: ('TAX_UNIT_ID')
302: AND AI.locking_action_id = AA.assignment_action_id
303: AND SS.assignment_id = AA.assignment_id
304: AND PE.person_id = SS.person_id
317:
318: CURSOR govt_mmrf_employee IS
319: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
320: 'ASSIGNMENT_ID=C', AA.assignment_id,
321: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
322: (PA.effective_date, AA.assignment_id)),
323: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
324: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
325: FROM per_all_people_f PE,
319: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
320: 'ASSIGNMENT_ID=C', AA.assignment_id,
321: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
322: (PA.effective_date, AA.assignment_id)),
323: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
324: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
325: FROM per_all_people_f PE,
326: per_all_assignments_f SS,
327: pay_action_interlocks AI,
329: pay_assignment_actions paa,
330: pay_payroll_actions PA,
331: ff_archive_items arch,
332: ff_user_entities fue
333: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
334: ('TRANSFER_PAYROLL_ACTION_ID')
335: AND AA.payroll_action_id = PA.payroll_action_id
336: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
337: ('TAX_UNIT_ID')
332: ff_user_entities fue
333: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
334: ('TRANSFER_PAYROLL_ACTION_ID')
335: AND AA.payroll_action_id = PA.payroll_action_id
336: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
337: ('TAX_UNIT_ID')
338: AND AI.locking_action_id = AA.assignment_action_id
339: AND SS.assignment_id = AA.assignment_id
340: AND PE.person_id = SS.person_id
347: AND SS.effective_end_date >= PA.start_date
348: AND SS.assignment_type = 'E'
349: AND LEAST(SS.effective_end_date, PA.effective_date)
350: between PE.effective_start_date and PE.effective_end_date
351: AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
352: ('PAYROLL_ACTION_ID')
353: AND paa.assignment_id = AA.assignment_id
354: AND arch.context1 = paa.assignment_action_id
355: AND arch.user_entity_id = fue.user_entity_id
353: AND paa.assignment_id = AA.assignment_id
354: AND arch.context1 = paa.assignment_action_id
355: AND arch.user_entity_id = fue.user_entity_id
356: AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
357: AND arch.value = pay_magtape_generic.get_parameter_value
358: ('TRANSFER_EMP_CODE')
359: ORDER BY PE.last_name, PE.first_name, PE.middle_names;
360:
361:
365:
366: CURSOR mmrf_pr_employee IS
367: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
368: 'ASSIGNMENT_ID=C', AA.assignment_id,
369: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
370: (PA.effective_date, AA.assignment_id)),
371: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
372: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
373: FROM per_all_people_f PE,
367: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
368: 'ASSIGNMENT_ID=C', AA.assignment_id,
369: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
370: (PA.effective_date, AA.assignment_id)),
371: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
372: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
373: FROM per_all_people_f PE,
374: per_all_assignments_f SS,
375: pay_action_interlocks AI,
374: per_all_assignments_f SS,
375: pay_action_interlocks AI,
376: pay_assignment_actions AA,
377: pay_payroll_actions PA
378: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
379: ('TRANSFER_PAYROLL_ACTION_ID')
380: AND AA.payroll_action_id = PA.payroll_action_id
381: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
382: ('TAX_UNIT_ID')
377: pay_payroll_actions PA
378: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
379: ('TRANSFER_PAYROLL_ACTION_ID')
380: AND AA.payroll_action_id = PA.payroll_action_id
381: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
382: ('TAX_UNIT_ID')
383: AND AI.locking_action_id = AA.assignment_action_id
384: AND SS.assignment_id = AA.assignment_id
385: AND PE.person_id = SS.person_id
408: ff_database_items fdi,
409: ff_archive_item_contexts faic, -- JD
410: pay_assignment_actions paa
411: where paa.assignment_action_id
412: = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
413: and paa.assignment_action_id = fai.context1
414: and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
415: and fdi.user_entity_id = fai.user_entity_id
416: and faic.archive_item_id = fai.archive_item_id
430: ff_database_items fdi,
431: ff_archive_item_contexts faic, -- JD
432: pay_assignment_actions paa
433: where paa.assignment_action_id =
434: pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
435: and paa.assignment_action_id = fai.context1
436: and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
437: fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
438: fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')