[Home] [Help]
179: from
180: ( SELECT DISTINCT NVL(pbt.reporting_name,pbt.balance_name) balance_name,
181: pdb.defined_balance_id def_id
182: FROM pay_element_types_f pet
183: ,per_all_assignments_f paa
184: ,pay_balance_types pbt
185: ,pay_defined_balances pdb
186: ,pay_balance_dimensions pbd
187: -- ,per_periods_of_service pps
224: ,c_tax_unit_id pay_assignment_actions.tax_unit_id%type)
225: IS
226: SELECT /*+ ORDERED */
227: DISTINCT pet.element_information2 balance_type_id
228: FROM per_all_assignments_f paf
229: ,pay_payroll_actions ppa
230: ,pay_assignment_actions paa
231: ,pay_run_results prr
232: ,pay_element_types_f pet
389: to restrict the max action_sequence fetch to types 'Q','R','B','I'
390: Bug: 4256506 - Changed cursor c_max_asg_action_id. Merged sub query to fetch max action sequemce in main query. Done for
391: better performance.
392: */
393: cursor c_max_asg_action_id (c_assignment_id per_all_assignments_f.assignment_id%TYPE,
394: c_business_group_id hr_all_organization_units.organization_id%TYPE,
395: c_tax_unit_id hr_all_organization_units.organization_id%TYPE,
396: c_year_start date,
397: c_year_end date ) is
909: ,actual_termination_date
910: ,date_start
911: ,pps.pds_information2
912: from per_all_people_f p,
913: per_all_assignments_f a,
914: per_periods_of_service pps
915: where a.person_id = p.person_id
916: and pps.person_id = p.person_id
917: and pps.period_of_service_id=a.period_of_service_id /*Bug 5397790 */
921: and p.effective_start_date = (SELECT max(pp.effective_start_date)
922: from per_all_people_f pp
923: where p.person_id = pp.person_id )
924: and a.effective_start_date = (SELECT max(aa.effective_start_date)
925: from per_all_assignments_f aa
926: where aa.assignment_id = c_assignment_id); /*Bug 4256486 */
927:
928: begin
929: if g_debug then
1202: end if;
1203: return 0;
1204: end get_assignment_id;
1205:
1206: function populate_exclusion_table(p_assignment_id per_all_assignments_f.assignment_id%type,
1207: p_financial_year varchar2,
1208: p_financial_year_end date,
1209: p_tax_unit_id number --2610141
1210: )
1209: p_tax_unit_id number --2610141
1210: )
1211: return number is
1212:
1213: Cursor c_ps_issued(c_assignment_id per_all_assignments_f.assignment_id%type,
1214: c_financial_year varchar2)
1215: is
1216: SELECT distinct paat.assignment_id
1217: from pay_action_interlocks pail,
1226: and pay_core_utils.get_parameter('FINANCIAL_YEAR',paas.legislative_parameters) = c_financial_year
1227: and pay_core_utils.get_parameter('REGISTERED_EMPLOYER',paas.legislative_parameters) = p_tax_unit_id; --2610141
1228:
1229:
1230: CURSOR c_get_details(c_assignment_id per_all_assignments_f.assignment_id%type,
1231: c_financial_yr_end date)
1232: is
1233: SELECT pap.last_name,
1234: paa.assignment_number
1231: c_financial_yr_end date)
1232: is
1233: SELECT pap.last_name,
1234: paa.assignment_number
1235: from per_all_people_f pap,per_all_assignments_f paa
1236: where pap.person_id=paa.person_id
1237: and paa.assignment_id=c_assignment_id
1238: and paa.effective_start_date = (SELECT max(paa1.effective_start_date)
1239: from per_all_assignments_f paa1
1235: from per_all_people_f pap,per_all_assignments_f paa
1236: where pap.person_id=paa.person_id
1237: and paa.assignment_id=c_assignment_id
1238: and paa.effective_start_date = (SELECT max(paa1.effective_start_date)
1239: from per_all_assignments_f paa1
1240: where paa1.assignment_id = c_assignment_id) /* Bug 4278407*/
1241: and pap.effective_start_date = (SELECT max(ppf.effective_start_date)
1242: from per_all_people_f ppf
1243: where pap.person_id = ppf.person_id);
1241: and pap.effective_start_date = (SELECT max(ppf.effective_start_date)
1242: from per_all_people_f ppf
1243: where pap.person_id = ppf.person_id);
1244:
1245: CURSOR c_eit_updated(c_assignment_id per_all_assignments_f.assignment_id%type,
1246: c_financial_year varchar2)
1247: is
1248: SELECT assignment_id
1249: from per_assignment_extra_info,
1258: /*Bug 4173809 - Cursor updated so that the assignment is reported in the exception section when Manual PS
1259: is issued against 'All' legal employers or a particular legal employer
1260: If the Manual PS is issued for 'All' the legal employers the aei_information2 would be -999*/
1261:
1262: l_assignment_id per_all_assignments_f.assignment_id%type;
1263: l_assignment_number per_all_assignments_f.assignment_number%type;
1264: l_employee_name per_all_people_f.last_name%type;
1265: l_reason fnd_new_messages.message_text%type;
1266:
1259: is issued against 'All' legal employers or a particular legal employer
1260: If the Manual PS is issued for 'All' the legal employers the aei_information2 would be -999*/
1261:
1262: l_assignment_id per_all_assignments_f.assignment_id%type;
1263: l_assignment_number per_all_assignments_f.assignment_number%type;
1264: l_employee_name per_all_people_f.last_name%type;
1265: l_reason fnd_new_messages.message_text%type;
1266:
1267:
1725: l_fbt_year_end date;
1726: l_lst_yr_term VARCHAR2(2);
1727:
1728:
1729: cursor get_params(c_payroll_action_id per_all_assignments_f.assignment_id%type)
1730: is
1731: select to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY')
1732: Financial_year_start
1733: ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY')