[Home] [Help]
745: and pact2.effective_date between f.effective_start_date and f.effective_end_date);
746:
747: cursor c_ptp (taxable_update_payroll number, c_date_paid date) is
748: select min(PTP.start_date) start_date
749: from per_time_periods PTP
750: where PTP.payroll_id = taxable_update_payroll
751: and (PTP.REGULAR_PAYMENT_DATE ) >= (/*start of fyear prior to session date*/
752: to_date('06-04-'||
753: to_char(fnd_number.canonical_to_number(to_char(c_date_paid,'YYYY'))
873: SELECT to_date('06-04-'||
874: to_char(fnd_number.canonical_to_number(to_char(ptp.regular_payment_date,'YYYY'))
875: + least(sign(ptp.regular_payment_date - to_date('06-04-'
876: || to_char(ptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
877: FROM per_time_periods ptp, pay_assignment_actions act, pay_payroll_actions pact
878: WHERE act.assignment_action_id = p_asg_action_id
879: AND act.payroll_Action_id = pact.payroll_action_id
880: AND pact.time_period_id = ptp.time_period_id;
881: --
1887: select 1 from dual where exists
1888: (select paa.assignment_action_id
1889: from pay_assignment_actions paa,
1890: pay_payroll_actions ppa,
1891: per_time_periods ptp
1892: where ptp.time_period_id = ppa.time_period_id
1893: and ppa.payroll_action_id = paa.payroll_action_id
1894: and paa.assignment_id = c_assignment_id
1895: and ppa.action_type in ('R','Q','V','I','B')
1895: and ppa.action_type in ('R','Q','V','I','B')
1896: and ptp.regular_payment_date =
1897: (select max(ptp.regular_payment_date)
1898: from per_all_assignments_f paf,
1899: per_time_periods ptp
1900: where ptp.regular_payment_date <= c_last_process_date
1901: and paf.assignment_id = c_assignment_id
1902: and ptp.payroll_id = paf.payroll_id
1903: and c_effective_end_date between
2159: FROM per_all_people_f p,
2160: per_all_assignments_f a,
2161: per_assignment_status_types past,
2162: pay_all_payrolls_f ppf,
2163: per_time_periods ptp,
2164: per_periods_of_service serv,
2165: hr_soft_coding_keyflex flex
2166: WHERE a.person_id BETWEEN stperson AND endperson
2167: AND a.business_group_id +0 = g_business_group_id
2214: per_all_people_f p,
2215: per_all_assignments_f a,
2216: per_assignment_status_types past,
2217: pay_all_payrolls_f ppf,
2218: per_time_periods ptp,
2219: per_periods_of_service serv,
2220: hr_soft_coding_keyflex flex
2221: WHERE p.person_id = ppr.person_id
2222: AND ppr.chunk_number = chunk
2800: l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
2801: --
2802: l_student_loan_flag VARCHAR2(1);
2803: --
2804: l_period_no per_time_periods.period_num%TYPE;
2805: l_tax_reference VARCHAR2(20);
2806: l_tax_code pay_element_entry_values_f.screen_entry_value%TYPE;
2807: l_tax_basis pay_element_entry_values_f.screen_entry_value%TYPE;
2808: l_prev_pay_char pay_element_entry_values_f.screen_entry_value%TYPE;
2880: --
2881: -- Bug 5478073: Get period number in which assignment was terminated
2882: CURSOR csr_get_term_period_no(p_term_date DATE, p_payroll_id NUMBER) IS
2883: SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
2884: FROM per_time_periods ptp
2885: WHERE ptp.payroll_id = p_payroll_id
2886: AND p_term_date BETWEEN ptp.start_date AND ptp.end_date;
2887: --
2888: cursor csr_period_number(p_payroll_action_id NUMBER) IS
2886: AND p_term_date BETWEEN ptp.start_date AND ptp.end_date;
2887: --
2888: cursor csr_period_number(p_payroll_action_id NUMBER) IS
2889: SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
2890: FROM per_time_periods ptp,
2891: pay_payroll_actions pact
2892: WHERE pact.payroll_action_id = p_payroll_action_id
2893: AND ptp.payroll_id = pact.payroll_id
2894: AND pact.date_earned BETWEEN ptp.start_date AND ptp.end_date;
4018: (
4019: select to_number(substr(max(lpad(to_char(act.action_sequence), 20, '0')||to_char(pact.payroll_action_id)),21)) -- just to be consistent with rest of the code to get highest action based on the action sequence
4020: from pay_assignment_actions act,
4021: pay_payroll_actions pact,
4022: per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
4023: where pact.payroll_action_id = act.payroll_action_id
4024: and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
4025: and act.assignment_id = c_assignment_id
4026: and pact.action_sequence < c_action_sequence -- assuming you will write another sql to get p_p45_action_sequence, alternatively another join to pay_assignment_actions can get you this value in this sql