[Home] [Help]
32: --
33: FUNCTION get_pension_type_details
34: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
35: ,p_date_earned IN DATE
36: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
37: ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
38: ,p_legislation_code IN pqp_pension_types_f.legislation_code%TYPE
39: ,p_column_name IN VARCHAR2
40: ,p_column_value OUT NOCOPY VARCHAR2
59: AND language = 'US';
60:
61: CURSOR c_get_person_age IS
62: SELECT to_char(per.date_of_birth,'RRRR')
63: FROM per_all_people_f per,per_all_assignments_f paa
64: WHERE per.person_id = paa.person_id
65: AND p_date_earned between paa.effective_start_date and paa.effective_end_date
66: AND p_date_earned between per.effective_start_date and per.effective_end_date
67: AND paa.assignment_id = p_assignment_id;
435: --
436: function prorate_amount
437: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
438: ,p_date_earned in date
439: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
440: ,p_amount in number
441: ,p_payroll_period in varchar2
442: ,p_work_pattern in varchar2
443: ,p_conversion_rule in varchar2
459: AND trunc(c_effective_date) BETWEEN effective_start_date AND effective_end_date
460: AND legislation_code = 'NL';
461:
462: CURSOR c_get_work_pattern(c_assignment_id
463: IN per_all_assignments_f.assignment_id%TYPE,
464: c_effective_date IN DATE) IS
465: SELECT work_pattern
466: FROM pqp_assignment_attributes_f
467: WHERE assignment_id = c_assignment_id
470: CURSOR c_get_start_end_date(c_assignment_id in NUMBER,
471: c_effective_date IN DATE
472: ) IS
473: SELECT ptp.start_date,ptp.end_date
474: FROM per_all_assignments_f pasf,per_time_periods ptp
475: WHERE pasf.payroll_id = ptp.payroll_id
476: AND pasf.assignment_id = c_assignment_id
477: AND trunc(c_effective_date) BETWEEN ptp.start_date AND ptp.end_date;
478:
516: c_get_average_days_per_month(c_assignment_id IN NUMBER,
517: c_effective_date IN DATE
518: ) IS
519: SELECT hoi.org_information5
520: FROM per_all_assignments_f paa,hr_organization_information hoi
521: WHERE paa.organization_id = hoi.organization_id
522: AND hoi.org_information_context='NL_ORG_INFORMATION'
523: AND paa.assignment_id = c_assignment_id
524: AND trunc(c_effective_date) between effective_start_date and effective_end_date;
1862: -- situation other than ABP late hires.
1863: --
1864: FUNCTION get_bal_val_de
1865: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
1866: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1867: ,p_date_earned IN DATE
1868: ,p_start_date IN DATE
1869: ,p_end_date IN DATE
1870: ,p_payroll_id IN NUMBER
1943: -- |-----------------------------< get_bal_val >-------------------------------|
1944: -- ----------------------------------------------------------------------------
1945: FUNCTION get_bal_val
1946: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
1947: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1948: ,p_effective_date IN DATE
1949: ,p_balance_name IN VARCHAR2
1950: ,p_dimension_name IN VARCHAR2)
1951: RETURN NUMBER IS
1993: -- ----------------------------------------------------------------------------
1994: function get_abp_pension_salary
1995: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
1996: ,p_date_earned in date
1997: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1998: ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
1999: ,p_period_start_date in date
2000: ,p_period_end_date in date
2001: ,p_scale_salary in number
2021:
2022: -- Cursor to get the hire date of the person
2023: CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
2024: SELECT max(date_start)
2025: FROM per_all_assignments_f asg
2026: ,per_periods_of_service pps
2027: WHERE pps.person_id = asg.person_id
2028: AND asg.assignment_id = c_asg_id
2029: AND pps.business_group_id = p_business_group_id
2041:
2042: --cursor to fetch the assignment start date
2043: CURSOR c_get_asg_start IS
2044: SELECT min(effective_start_date)
2045: FROM per_all_assignments_f
2046: WHERE assignment_id = p_assignment_id;
2047:
2048: -- =============================================================================
2049: -- Cursor to get the defined balance id for a given balance and dimension
2080: CURSOR c_cag_name (c_asg_id IN NUMBER
2081: ,c_eff_date IN DATE) IS
2082: SELECT cola.name
2083: FROM per_collective_agreements cola
2084: ,per_all_assignments_f asg
2085: WHERE asg.assignment_id = c_asg_id
2086: AND asg.collective_agreement_id = cola.collective_agreement_id
2087: AND cola.status = 'A'
2088: AND c_eff_date BETWEEN asg.effective_start_date
2175: --cursor to fetch the org id for the organization
2176: --attached to this assignment
2177: CURSOR c_get_org_id(c_eff_date IN DATE) IS
2178: SELECT organization_id
2179: FROM per_all_assignments_f
2180: WHERE assignment_id = p_assignment_id
2181: AND c_eff_date BETWEEN effective_start_date
2182: AND effective_end_date;
2183:
2193: --7344670
2194: --Cursor to check if the assignment is terminated or not.
2195: Cursor chk_term_asg IS
2196: SELECT 1
2197: FROM per_all_assignments_f asg
2198: WHERE assignment_id = p_assignment_id
2199: AND effective_start_date <= p_date_earned
2200: AND assignment_status_type_id IN (SELECT assignment_status_type_id
2201: FROM per_assignment_status_types
3031: --
3032: function get_pension_salary
3033: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
3034: ,p_date_earned in date
3035: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3036: ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
3037: ,p_salary_balance_name in varchar2
3038: ,p_payroll_period in varchar2
3039: ,p_salary_balance_value out nocopy number
3062: l_error_status CHAR:='0';
3063: -- Cursor to get the hire date of the person
3064: CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
3065: SELECT max(date_start)
3066: FROM per_all_assignments_f asg
3067: ,per_periods_of_service pps
3068: WHERE pps.person_id = asg.person_id
3069: AND asg.assignment_id = c_asg_id
3070: AND pps.business_group_id = p_business_group_id
3095:
3096: -- Cursor to get the payroll_id as of a particular date
3097: CURSOR c_payroll_cur (c_effective_date IN DATE ) IS
3098: SELECT payroll_id
3099: FROM per_all_assignments_f
3100: WHERE assignment_id = p_assignment_id
3101: AND trunc(c_effective_date) BETWEEN effective_start_date
3102: AND effective_end_date;
3103:
3181: -- Get the current payroll_id for the person
3182: --
3183: SELECT payroll_id
3184: INTO l_payroll_id
3185: FROM per_all_assignments_f
3186: WHERE assignment_id = p_assignment_id
3187: AND trunc(p_date_earned) BETWEEN effective_start_date
3188: AND effective_end_date;
3189:
3293:
3294: --Cursor to find the org id from the assignment id
3295: CURSOR c_find_org_id IS
3296: SELECT organization_id
3297: FROM per_all_assignments_f
3298: WHERE assignment_id = p_assignment_id
3299: AND trunc(p_date_earned) between effective_start_date and effective_end_date
3300: AND business_group_id = p_business_group_id;
3301:
3542: ) return NUMBER IS
3543:
3544: --cursor to find the person id for the current assignment id
3545: cursor c_get_person_id IS
3546: select person_id from per_all_assignments_f
3547: where assignment_id = p_assignment_id
3548: and p_date_earned between effective_start_date and effective_end_date
3549: and business_group_id = p_business_group_id;
3550:
3550:
3551: --cursor to find all the assigment ids for a particular person id
3552: cursor c_get_all_assignmentid(c_person_id in number) IS
3553: select assignment_id
3554: from per_all_assignments_f asg
3555: where asg.person_id = c_person_id
3556: and asg.assignment_status_type_id in (select assignment_status_type_id from PER_ASS_STATUS_TYPE_AMENDS
3557: where business_group_id = p_business_group_id and pay_system_status = 'P' and active_flag = 'Y'
3558: union
3566: ))
3567: and p_date_earned between asg.effective_start_date and asg.effective_end_date;
3568:
3569: --Cursor to derive the part time percetage value
3570: CURSOR c_get_fte(c_assignment_id IN per_all_assignments_f.assignment_id%TYPE) IS
3571: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))
3572: FROM hr_soft_coding_keyflex target,
3573: per_all_assignments_f ASSIGN
3574: WHERE p_date_earned BETWEEN ASSIGN.effective_start_date AND
3569: --Cursor to derive the part time percetage value
3570: CURSOR c_get_fte(c_assignment_id IN per_all_assignments_f.assignment_id%TYPE) IS
3571: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))
3572: FROM hr_soft_coding_keyflex target,
3573: per_all_assignments_f ASSIGN
3574: WHERE p_date_earned BETWEEN ASSIGN.effective_start_date AND
3575: ASSIGN.effective_end_date
3576: AND ASSIGN.assignment_id = c_assignment_id
3577: AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
4415: -- |----------------------< get_bonus >-----------------------------|
4416: -- ------------------------------------------------------------------
4417: FUNCTION get_bonus
4418: ( p_date_earned in date
4419: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4420: ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
4421: ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
4422: ,p_pay_period_salary in number
4423: ,p_pay_period in varchar2
4483: FROM dual;
4484:
4485: CURSOR c_get_payroll_id(c_effective_date IN DATE) IS
4486: SELECT payroll_id
4487: FROM per_all_assignments_f
4488: WHERE assignment_id = p_assignment_id
4489: AND trunc(c_effective_date) BETWEEN
4490: effective_start_date AND effective_end_date;
4491:
4783: -- -----------------------------------------------------------------------
4784: function get_abp_entry_value
4785: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
4786: ,p_date_earned in date
4787: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4788: ,p_element_type_id in number
4789: ,p_input_value_name in varchar2
4790: ) return NUMBER IS
4791:
4791:
4792: -- Cursor to get the hire date of the person
4793: CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
4794: SELECT max(date_start)
4795: FROM per_all_assignments_f asg
4796: ,per_periods_of_service pps
4797: WHERE pps.person_id = asg.person_id
4798: AND asg.assignment_id = c_asg_id
4799: AND pps.business_group_id = p_business_group_id
4801:
4802: --cursor to get the start date for the assignment
4803: CURSOR c_get_asg_start IS
4804: SELECT min(effective_start_date)
4805: FROM per_all_assignments_f
4806: WHERE assignment_id = p_assignment_id;
4807:
4808: -- Cursor to get the entry value for the input name
4809: CURSOR c_entry_val_cur
5099:
5100: function get_avg_part_time_perc
5101: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
5102: ,p_date_earned in date
5103: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5104: ,p_assignment_action_id IN NUMBER
5105: ,p_period_start_date in DATE
5106: ,p_period_end_date in DATE
5107: ,p_avg_part_time_perc out NOCOPY number
5367: --
5368: FUNCTION get_pay_period_age
5369: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
5370: ,p_date_earned IN DATE
5371: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5372: ,p_period_start_date IN DATE
5373: ) RETURN NUMBER IS
5374:
5375: --
5387: --
5388: CURSOR get_dob IS
5389: SELECT TRUNC(date_of_birth)
5390: FROM per_all_people_f per
5391: ,per_all_assignments_f paf
5392: WHERE per.person_id = paf.person_id
5393: AND paf.assignment_id = p_assignment_id
5394: AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
5395: AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
5398: -- Cursor to get the start date for the active asg
5399: --
5400: CURSOR c_get_assign_start_date IS
5401: SELECT min(asg.effective_start_date)
5402: FROM per_all_assignments_f asg
5403: ,per_assignment_status_types past
5404: WHERE asg.assignment_status_type_id = past.assignment_status_type_id
5405: AND past.per_system_status = 'ACTIVE_ASSIGN'
5406: --AND asg.effective_start_date <= trunc(p_period_start_date)