[Home] [Help]
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
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'
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=')
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:
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')
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,
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
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,
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
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
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;
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:
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
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: