[Home] [Help]
166: where person_id = p_person_id
167: and p_effective_date between effective_start_date and effective_end_date;
168:
169: /* Bug#6887749
170: * Removed join with per_time_periods to fetch the latest assignment action
171: * id of the assignment irrespective of the session date.*/
172: cursor csr_get_action_id (asg_id number) is
173: select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
174: from pay_payroll_actions pa,
172: cursor csr_get_action_id (asg_id number) is
173: select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
174: from pay_payroll_actions pa,
175: pay_assignment_actions aa
176: -- per_time_periods ptp
177: where aa.action_status = 'C'
178: and pa.payroll_action_id = aa.payroll_action_id
179: and aa.assignment_id = asg_id
180: --and ptp.payroll_id = pa.payroll_id
339: from pay_all_payrolls_f pp1
340: ,pay_all_payrolls_f pp2
341: ,pay_payroll_actions pa1
342: ,pay_payroll_actions pa2
343: ,per_time_periods tp1
344: ,per_time_periods tp2
345: ,pay_assignment_actions aa1
346: ,pay_assignment_actions aa2
347: where pa1.payroll_action_id = aa1.payroll_action_id
340: ,pay_all_payrolls_f pp2
341: ,pay_payroll_actions pa1
342: ,pay_payroll_actions pa2
343: ,per_time_periods tp1
344: ,per_time_periods tp2
345: ,pay_assignment_actions aa1
346: ,pay_assignment_actions aa2
347: where pa1.payroll_action_id = aa1.payroll_action_id
348: --We are considering effective_date(Date Paid) which some
379: ,fnd_date.date_to_displaydate(aa.end_date,2) COL06 -- change as per bug 11830805
380: ,fnd_date.date_to_displaydate(tp.start_date,2) COL02 -- change as per bug 11830805
381: ,tp.period_type COL07
382: from pay_payroll_actions pa
383: ,per_time_periods tp
384: ,pay_assignment_actions aa
385: where pa.payroll_action_id = aa.payroll_action_id
386: -- Bug 9877851 : Included join on time period id rather than on effective date
387: /* and pa.effective_date = tp.regular_payment_date */
398: ,fnd_date.date_to_displaydate(aa.end_date,2) COL06 -- change as per bug 11830805
399: ,fnd_date.date_to_displaydate(tp.start_date,2) COL02 -- change as per bug 11830805
400: ,tp.period_type COL07
401: from pay_payroll_actions pa
402: ,per_time_periods tp
403: ,pay_assignment_actions aa
404: where pa.payroll_action_id = aa.payroll_action_id
405: and pa.time_period_id = tp.time_period_id
406: and aa.assignment_action_id = :assignment_action_id';
1155:
1156: SELECT ptp.start_date
1157: ,ptp.end_date
1158: ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1159: FROM per_time_periods ptp,
1160: pay_payroll_actions ppa,
1161: pay_assignment_actions paa,
1162: pay_run_results prr,
1163: pay_element_entries_f pee
1177: ) is
1178: SELECT ptp.start_date
1179: ,ptp.end_date
1180: ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1181: FROM per_time_periods ptp,
1182: pay_payroll_actions ppa,
1183: pay_assignment_actions paa,
1184: pay_run_results prr,
1185: pay_element_entries_f pee
1199: ) is
1200: SELECT ptp.start_date
1201: ,ptp.end_date
1202: ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1203: FROM per_time_periods ptp,
1204: pay_payroll_actions ppa,
1205: pay_assignment_actions paa,
1206: pay_run_results prr,
1207: pay_element_entries_f pee
1221: ) is
1222: SELECT ptp.start_date
1223: ,ptp.end_date
1224: ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1225: FROM per_time_periods ptp,
1226: pay_payroll_actions ppa,
1227: pay_assignment_actions paa,
1228: pay_element_entries_f pee
1229: WHERE pee.element_entry_id=c_element_entry_id