[Home] [Help]
451: date_earned. */
452:
453: cursor c_get_le_period_num is
454: select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
455: from per_time_periods ptp
456: ,per_time_periods ptp1
457: where exists (select 'EXISTS' from
458: per_assignments_f paf,
459: hr_soft_coding_keyflex hsck
452:
453: cursor c_get_le_period_num is
454: select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
455: from per_time_periods ptp
456: ,per_time_periods ptp1
457: where exists (select 'EXISTS' from
458: per_assignments_f paf,
459: hr_soft_coding_keyflex hsck
460: where paf.assignment_id = p_assignment_id
477:
478: cursor c_get_period_num (v_payroll_id number,
479: v_hire_date date) is
480: select period_num
481: from per_time_periods
482: where payroll_id = v_payroll_id
483: and v_hire_date between start_date and end_date;
484:
485:
489:
490: CURSOR c_get_period_end_date(c_date date)
491: IS
492: select ptp.end_date
493: from per_time_periods ptp
494: where ptp.payroll_id = p_payroll_id
495: and c_date between
496: ptp.start_date and ptp.end_date;
497:
515: cursor get_period_num(c_start_date date,
516: c_end_date date)
517: is
518: select count(*)
519: from per_time_periods ptp
520: where payroll_id = p_payroll_id
521: and ptp.end_date
522: between c_start_date and c_end_date;
523:
548: 2. IF Date Earrned and Eff Date in Different Fin Year,
549: Start Date : Financial Year Start Date (Effective Date)
550: End Date : End Date ( Effective Date)
551:
552: Count No of periods in per_time_periods between Start Date/End Date
553: */
554:
555: /* Bug# 5371901 - Changed the above logic to take out "Date_Earned" if Condition
556: and instead use only "Effective Date"
801: c_start_date in date,
802: c_end_date in date)
803: is
804: select count(DISTINCT ptp.time_period_id)
805: from per_time_periods ptp
806: where exists (select 'EXISTS' from
807: per_assignments_f paf,
808: hr_soft_coding_keyflex hsck
809: where paf.assignment_id = p_assignment_id
825: c_start_date in date,
826: c_end_date in date)
827: is
828: select count(DISTINCT ptp.time_period_id)
829: from per_time_periods ptp
830: where exists (select 'EXISTS' from
831: per_assignments_f paf,
832: hr_soft_coding_keyflex hsck
833: where paf.assignment_id = p_assignment_id
846: c_start_date in date,
847: c_end_date in date)
848: is
849: select count(DISTINCT ptp.time_period_id)
850: from per_time_periods ptp
851: where exists (select 'EXISTS' from
852: per_assignments_f paf,
853: hr_soft_coding_keyflex hsck
854: where paf.assignment_id = p_assignment_id
1222: /*Bug 4438644 - Cursor introduced to return time period of the current payroll period*/
1223: cursor c_get_period_id
1224: is
1225: select time_period_id
1226: from per_time_periods
1227: where payroll_id = p_payroll_id
1228: and start_Date = p_period_start;
1229:
1230: /* Bug: 3724089 - Performance Fix in the Cursor below. Added table per_assignments_f and its joins in the inner sub-query */
1235: v_assignment_id number,
1236: v_tax_unit_id number --2610141
1237: ) is
1238: select DISTINCT ptp.time_period_id, ptp.period_num /*Bug 4438644*/
1239: from per_time_periods ptp
1240: where exists (select 'EXISTS' from
1241: per_assignments_f paf,
1242: pay_payroll_actions ppa,
1243: pay_assignment_actions paa
1484: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1485: c_date_earned in pay_payroll_actions.date_earned%TYPE
1486: ) is
1487: SELECT ptp.start_date,ptp.end_date
1488: FROM per_time_periods ptp,
1489: pay_payroll_actions ppa,
1490: pay_assignment_actions paa,
1491: pay_run_results prr,
1492: pay_element_entries_f pee
1506: ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1507: c_date_earned in pay_payroll_actions.date_earned%TYPE
1508: ) is
1509: SELECT ptp.start_date,ptp.end_date
1510: FROM per_time_periods ptp,
1511: pay_payroll_actions ppa,
1512: pay_assignment_actions paa,
1513: pay_element_entries_f pee
1514: WHERE pee.element_entry_id=c_element_entry_id
1629: IS
1630: SELECT ptp.start_date
1631: ,ptp.end_date
1632: ,ppa.effective_date
1633: FROM per_time_periods ptp,
1634: pay_payroll_actions ppa,
1635: pay_assignment_actions paa,
1636: pay_run_results prr,
1637: pay_element_entries_f pee
1653: IS
1654: SELECT ptp.start_date
1655: ,ptp.end_date
1656: ,ppa.effective_date
1657: FROM per_time_periods ptp,
1658: pay_payroll_actions ppa,
1659: pay_assignment_actions paa,
1660: pay_element_entries_f pee
1661: WHERE pee.element_entry_id=c_element_entry_id
2810: pay_run_result_values prrv,
2811: pay_run_results prr,
2812: pay_assignment_actions paa,
2813: pay_payroll_actions ppa,
2814: per_time_periods ptp,
2815: per_all_assignments_f paaf
2816: where pet.element_name = 'Extra Withholding Payments'
2817: and piv.name='Withholding Amount'
2818: and pet.element_type_id=piv.element_type_id
4340: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
4341: from pay_assignment_actions paa
4342: , pay_payroll_actions ppa
4343: , per_assignments_f paf
4344: , per_time_periods ptp
4345: where paa.assignment_id = paf.assignment_id
4346: and paf.assignment_id = p_assignment_id
4347: and ppa.payroll_action_id = paa.payroll_action_id
4348: and ppa.payroll_id = paf.payroll_id