[Home] [Help]
107: FROM pay_state_rules SR,
108: hr_organization_information HOI,
109: pay_payroll_actions PPA,
110: pay_payroll_actions PPA1
111: WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
112: ('TRANSFER_PAYROLL_ACTION_ID')
113: AND ppa1.effective_date = ppa.effective_date
114: AND ppa1.report_qualifier = sr.state_code
115: AND HOI.organization_id =
112: ('TRANSFER_PAYROLL_ACTION_ID')
113: AND ppa1.effective_date = ppa.effective_date
114: AND ppa1.report_qualifier = sr.state_code
115: AND HOI.organization_id =
116: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
117: AND SR.state_code =
118: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
119: AND HOI.org_information_context = 'W2 Reporting Rules'
120: AND PPA.report_type = 'YREND'
114: AND ppa1.report_qualifier = sr.state_code
115: AND HOI.organization_id =
116: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
117: AND SR.state_code =
118: pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
119: AND HOI.org_information_context = 'W2 Reporting Rules'
120: AND PPA.report_type = 'YREND'
121: AND HOI.ORGANIZATION_ID
122: = substr(PPA.legislative_parameters,
122: = substr(PPA.legislative_parameters,
123: instr(PPA.legislative_parameters,
124: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
125: AND to_char(PPA.effective_date,'YYYY')
126: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
127: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
128:
129: /* Context and Parameter Set in the cursor are
130: Context :
158: 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
159: FROM hr_organization_information HOI,
160: pay_payroll_actions PPA
161: WHERE HOI.organization_id =
162: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
163: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
164: AND HOI.org_information_context = 'W2 Reporting Rules'
165: AND PPA.report_type = 'YREND'
166: AND HOI.ORGANIZATION_ID
159: FROM hr_organization_information HOI,
160: pay_payroll_actions PPA
161: WHERE HOI.organization_id =
162: pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
163: AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
164: AND HOI.org_information_context = 'W2 Reporting Rules'
165: AND PPA.report_type = 'YREND'
166: AND HOI.ORGANIZATION_ID
167: = substr(PPA.legislative_parameters,
167: = substr(PPA.legislative_parameters,
168: instr(PPA.legislative_parameters,
169: 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
170: AND to_char(PPA.effective_date,'YYYY')
171: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
172: AND to_char(PPA.effective_date,'DD-MM') = '31-12';
173:
174: --
175: -- Sets up the tax unit context for each employer to be reported on NB. sets
203: 'TRANSFER_EMP_CODE=P', 'R'
204: FROM hr_all_organization_units hou,
205: pay_payroll_actions ppa,
206: pay_assignment_actions AA
207: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
208: ('TRANSFER_PAYROLL_ACTION_ID')
209: AND ppa.report_type = 'YREND'
210: AND to_char(ppa.effective_date,'YYYY')
211: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
207: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
208: ('TRANSFER_PAYROLL_ACTION_ID')
209: AND ppa.report_type = 'YREND'
210: AND to_char(ppa.effective_date,'YYYY')
211: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
212: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
213: AND AA.tax_unit_id
214: = substr(ppa.legislative_parameters,
215: instr(ppa.legislative_parameters,
228: 'TRANSFER_EMP_CODE=P' , 'R'
229: FROM hr_all_organization_units hou,
230: pay_payroll_actions ppa,
231: pay_assignment_actions AA
232: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
233: ('TRANSFER_PAYROLL_ACTION_ID')
234: AND ppa.report_type = 'YREND'
235: AND to_char(ppa.effective_date,'YYYY')
236: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
232: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
233: ('TRANSFER_PAYROLL_ACTION_ID')
234: AND ppa.report_type = 'YREND'
235: AND to_char(ppa.effective_date,'YYYY')
236: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
237: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
238: AND AA.tax_unit_id
239: = substr(ppa.legislative_parameters,
240: instr(ppa.legislative_parameters,
250: 'TRANSFER_EMP_CODE=P' , 'Q'
251: FROM hr_all_organization_units hou,
252: pay_payroll_actions ppa,
253: pay_assignment_actions AA
254: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
255: ('TRANSFER_PAYROLL_ACTION_ID')
256: AND ppa.report_type = 'YREND'
257: AND to_char(ppa.effective_date,'YYYY')
258: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
254: WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
255: ('TRANSFER_PAYROLL_ACTION_ID')
256: AND ppa.report_type = 'YREND'
257: AND to_char(ppa.effective_date,'YYYY')
258: = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
259: AND to_char(ppa.effective_date,'DD-MM') = '31-12'
260: AND AA.tax_unit_id
261: = substr(ppa.legislative_parameters,
262: instr(ppa.legislative_parameters,
274: --
275: CURSOR mmrf_employee IS
276: SELECT /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
277: 'ASSIGNMENT_ID=C', AA.assignment_id,
278: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
279: (PA.effective_date, AA.assignment_id)),
280: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
281: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
282: FROM pay_payroll_actions PA,
276: SELECT /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
277: 'ASSIGNMENT_ID=C', AA.assignment_id,
278: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
279: (PA.effective_date, AA.assignment_id)),
280: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
281: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
282: FROM pay_payroll_actions PA,
283: pay_assignment_actions AA,
284: pay_action_interlocks AI,
283: pay_assignment_actions AA,
284: pay_action_interlocks AI,
285: per_all_assignments_f SS,
286: per_all_people_f PE
287: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
288: ('TRANSFER_PAYROLL_ACTION_ID')
289: AND AA.payroll_action_id = PA.payroll_action_id
290: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
291: ('TAX_UNIT_ID')
286: per_all_people_f PE
287: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
288: ('TRANSFER_PAYROLL_ACTION_ID')
289: AND AA.payroll_action_id = PA.payroll_action_id
290: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
291: ('TAX_UNIT_ID')
292: AND AI.locking_action_id = AA.assignment_action_id
293: AND SS.assignment_id = AA.assignment_id
294: AND PE.person_id = SS.person_id
307:
308: CURSOR govt_mmrf_employee IS
309: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
310: 'ASSIGNMENT_ID=C', AA.assignment_id,
311: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
312: (PA.effective_date, AA.assignment_id)),
313: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
314: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
315: FROM per_all_people_f PE,
309: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
310: 'ASSIGNMENT_ID=C', AA.assignment_id,
311: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
312: (PA.effective_date, AA.assignment_id)),
313: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
314: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
315: FROM per_all_people_f PE,
316: per_all_assignments_f SS,
317: pay_action_interlocks AI,
319: pay_assignment_actions paa,
320: pay_payroll_actions PA,
321: ff_archive_items arch,
322: ff_user_entities fue
323: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
324: ('TRANSFER_PAYROLL_ACTION_ID')
325: AND AA.payroll_action_id = PA.payroll_action_id
326: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
327: ('TAX_UNIT_ID')
322: ff_user_entities fue
323: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
324: ('TRANSFER_PAYROLL_ACTION_ID')
325: AND AA.payroll_action_id = PA.payroll_action_id
326: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
327: ('TAX_UNIT_ID')
328: AND AI.locking_action_id = AA.assignment_action_id
329: AND SS.assignment_id = AA.assignment_id
330: AND PE.person_id = SS.person_id
337: AND SS.effective_end_date >= PA.start_date
338: AND SS.assignment_type = 'E'
339: AND LEAST(SS.effective_end_date, PA.effective_date)
340: between PE.effective_start_date and PE.effective_end_date
341: AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
342: ('PAYROLL_ACTION_ID')
343: AND paa.assignment_id = AA.assignment_id
344: AND arch.context1 = paa.assignment_action_id
345: AND arch.user_entity_id = fue.user_entity_id
343: AND paa.assignment_id = AA.assignment_id
344: AND arch.context1 = paa.assignment_action_id
345: AND arch.user_entity_id = fue.user_entity_id
346: AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
347: AND arch.value = pay_magtape_generic.get_parameter_value
348: ('TRANSFER_EMP_CODE')
349: ORDER BY PE.last_name, PE.first_name, PE.middle_names;
350:
351:
355:
356: CURSOR mmrf_pr_employee IS
357: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
358: 'ASSIGNMENT_ID=C', AA.assignment_id,
359: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
360: (PA.effective_date, AA.assignment_id)),
361: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
362: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
363: FROM per_all_people_f PE,
357: SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
358: 'ASSIGNMENT_ID=C', AA.assignment_id,
359: 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
360: (PA.effective_date, AA.assignment_id)),
361: 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
362: 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
363: FROM per_all_people_f PE,
364: per_all_assignments_f SS,
365: pay_action_interlocks AI,
364: per_all_assignments_f SS,
365: pay_action_interlocks AI,
366: pay_assignment_actions AA,
367: pay_payroll_actions PA
368: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
369: ('TRANSFER_PAYROLL_ACTION_ID')
370: AND AA.payroll_action_id = PA.payroll_action_id
371: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
372: ('TAX_UNIT_ID')
367: pay_payroll_actions PA
368: WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
369: ('TRANSFER_PAYROLL_ACTION_ID')
370: AND AA.payroll_action_id = PA.payroll_action_id
371: AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
372: ('TAX_UNIT_ID')
373: AND AI.locking_action_id = AA.assignment_action_id
374: AND SS.assignment_id = AA.assignment_id
375: AND PE.person_id = SS.person_id
398: ff_database_items fdi,
399: ff_archive_item_contexts faic, -- JD
400: pay_assignment_actions paa
401: where paa.assignment_action_id
402: = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
403: and paa.assignment_action_id = fai.context1
404: and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
405: and fdi.user_entity_id = fai.user_entity_id
406: and faic.archive_item_id = fai.archive_item_id
420: ff_database_items fdi,
421: ff_archive_item_contexts faic, -- JD
422: pay_assignment_actions paa
423: where paa.assignment_action_id =
424: pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
425: and paa.assignment_action_id = fai.context1
426: and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
427: fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
428: fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')