[Home] [Help]
288: counted based on the Regular Payment Date. */
289:
290: cursor c_get_le_period_num is
291: select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
292: from per_time_periods ptp
293: ,per_time_periods ptp1
294: where exists (select 'EXISTS' from
295: per_assignments_f paf,
296: hr_soft_coding_keyflex hsck
289:
290: cursor c_get_le_period_num is
291: select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
292: from per_time_periods ptp
293: ,per_time_periods ptp1
294: where exists (select 'EXISTS' from
295: per_assignments_f paf,
296: hr_soft_coding_keyflex hsck
297: where paf.assignment_id = p_assignment_id
315:
316: cursor c_get_period_num (v_payroll_id number,
317: v_hire_date date) is
318: select period_num
319: from per_time_periods
320: where payroll_id = v_payroll_id
321: and v_hire_date between start_date and end_date;
322:
323:
327:
328: CURSOR c_get_period_end_date(c_date date)
329: IS
330: select ptp.end_date
331: from per_time_periods ptp
332: where ptp.payroll_id = p_payroll_id
333: and c_date between
334: ptp.start_date and ptp.end_date;
335:
353: cursor get_period_num(c_start_date date,
354: c_end_date date)
355: is
356: select count(*)
357: from per_time_periods ptp
358: where payroll_id = p_payroll_id
359: and ptp.end_date
360: between c_start_date and c_end_date;
361:
386: 2. IF Date Earrned and Eff Date in Different Fin Year,
387: Start Date : Financial Year Start Date (Effective Date)
388: End Date : End Date ( Effective Date)
389:
390: Count No of periods in per_time_periods between Start Date/End Date
391: */
392:
393: /* Bug# 5371901 - Changed the above logic to take out "Date_Earned" if Condition
394: and instead use only "Effective Date"
631: c_start_date in date,
632: c_end_date in date)
633: is
634: select count(DISTINCT ptp.time_period_id)
635: from per_time_periods ptp
636: where exists (select 'EXISTS' from
637: per_assignments_f paf,
638: hr_soft_coding_keyflex hsck
639: where paf.assignment_id = p_assignment_id
971: /*Bug 4438644 - Cursor introduced to return time period of the current payroll period*/
972: cursor c_get_period_id
973: is
974: select time_period_id
975: from per_time_periods
976: where payroll_id = p_payroll_id
977: and start_Date = p_period_start;
978:
979: /* Bug: 3724089 - Performance Fix in the Cursor below. Added table per_assignments_f and its joins in the inner sub-query */
984: v_assignment_id number,
985: v_tax_unit_id number --2610141
986: ) is
987: select DISTINCT ptp.time_period_id, ptp.period_num /*Bug 4438644*/
988: from per_time_periods ptp
989: where exists (select 'EXISTS' from
990: per_assignments_f paf,
991: pay_payroll_actions ppa,
992: pay_assignment_actions paa
1233: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1234: c_date_earned in pay_payroll_actions.date_earned%TYPE
1235: ) is
1236: SELECT ptp.start_date,ptp.end_date
1237: FROM per_time_periods ptp,
1238: pay_payroll_actions ppa,
1239: pay_assignment_actions paa,
1240: pay_run_results prr,
1241: pay_element_entries_f pee
1255: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1256: c_date_earned in pay_payroll_actions.date_earned%TYPE
1257: ) is
1258: SELECT ptp.start_date,ptp.end_date
1259: FROM per_time_periods ptp,
1260: pay_payroll_actions ppa,
1261: pay_assignment_actions paa,
1262: pay_element_entries_f pee
1263: WHERE pee.element_entry_id=c_element_entry_id
1378: IS
1379: SELECT ptp.start_date
1380: ,ptp.end_date
1381: ,ppa.effective_date
1382: FROM per_time_periods ptp,
1383: pay_payroll_actions ppa,
1384: pay_assignment_actions paa,
1385: pay_run_results prr,
1386: pay_element_entries_f pee
1402: IS
1403: SELECT ptp.start_date
1404: ,ptp.end_date
1405: ,ppa.effective_date
1406: FROM per_time_periods ptp,
1407: pay_payroll_actions ppa,
1408: pay_assignment_actions paa,
1409: pay_element_entries_f pee
1410: WHERE pee.element_entry_id=c_element_entry_id
2062: pay_run_result_values prrv,
2063: pay_run_results prr,
2064: pay_assignment_actions paa,
2065: pay_payroll_actions ppa,
2066: per_time_periods ptp,
2067: per_all_assignments_f paaf
2068: where pet.element_name = 'Extra Withholding Payments'
2069: and piv.name='Withholding Amount'
2070: and pet.element_type_id=piv.element_type_id