[Home] [Help]
74: --
75: FUNCTION get_pension_type_details
76: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
77: ,p_date_earned IN DATE
78: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
79: ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
80: ,p_legislation_code IN pqp_pension_types_f.legislation_code%TYPE
81: ,p_column_name IN VARCHAR2
82: ,p_column_value OUT NOCOPY VARCHAR2
101: AND language = 'US';
102:
103: CURSOR c_get_person_age IS
104: SELECT to_char(per.date_of_birth,'RRRR')
105: FROM per_all_people_f per,per_all_assignments_f paa
106: WHERE per.person_id = paa.person_id
107: AND p_date_earned between paa.effective_start_date and paa.effective_end_date
108: AND p_date_earned between per.effective_start_date and per.effective_end_date
109: AND paa.assignment_id = p_assignment_id;
477: --
478: function prorate_amount
479: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
480: ,p_date_earned in date
481: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
482: ,p_amount in number
483: ,p_payroll_period in varchar2
484: ,p_work_pattern in varchar2
485: ,p_conversion_rule in varchar2
501: AND trunc(c_effective_date) BETWEEN effective_start_date AND effective_end_date
502: AND legislation_code = 'NL';
503:
504: CURSOR c_get_work_pattern(c_assignment_id
505: IN per_all_assignments_f.assignment_id%TYPE,
506: c_effective_date IN DATE) IS
507: SELECT work_pattern
508: FROM pqp_assignment_attributes_f
509: WHERE assignment_id = c_assignment_id
512: CURSOR c_get_start_end_date(c_assignment_id in NUMBER,
513: c_effective_date IN DATE
514: ) IS
515: SELECT ptp.start_date,ptp.end_date
516: FROM per_all_assignments_f pasf,per_time_periods ptp
517: WHERE pasf.payroll_id = ptp.payroll_id
518: AND pasf.assignment_id = c_assignment_id
519: AND trunc(c_effective_date) BETWEEN ptp.start_date AND ptp.end_date;
520:
558: c_get_average_days_per_month(c_assignment_id IN NUMBER,
559: c_effective_date IN DATE
560: ) IS
561: SELECT hoi.org_information5
562: FROM per_all_assignments_f paa,hr_organization_information hoi
563: WHERE paa.organization_id = hoi.organization_id
564: AND hoi.org_information_context='NL_ORG_INFORMATION'
565: AND paa.assignment_id = c_assignment_id
566: AND trunc(c_effective_date) between effective_start_date and effective_end_date;
1927: -- situation other than ABP late hires.
1928: --
1929: FUNCTION get_bal_val_de
1930: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
1931: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1932: ,p_date_earned IN DATE
1933: ,p_start_date IN DATE
1934: ,p_end_date IN DATE
1935: ,p_payroll_id IN NUMBER
2008: -- |-----------------------------< get_bal_val >-------------------------------|
2009: -- ----------------------------------------------------------------------------
2010: FUNCTION get_bal_val
2011: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
2012: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2013: ,p_effective_date IN DATE
2014: ,p_balance_name IN VARCHAR2
2015: ,p_dimension_name IN VARCHAR2)
2016: RETURN NUMBER IS
2058: -- ----------------------------------------------------------------------------
2059: function get_abp_pension_salary
2060: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
2061: ,p_date_earned in date
2062: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2063: ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
2064: ,p_period_start_date in date
2065: ,p_period_end_date in date
2066: ,p_scale_salary in number
2086:
2087: -- Cursor to get the hire date of the person
2088: CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
2089: SELECT max(date_start)
2090: FROM per_all_assignments_f asg
2091: ,per_periods_of_service pps
2092: WHERE pps.person_id = asg.person_id
2093: AND asg.assignment_id = c_asg_id
2094: AND pps.business_group_id = p_business_group_id
2106:
2107: --cursor to fetch the assignment start date
2108: CURSOR c_get_asg_start IS
2109: SELECT min(effective_start_date)
2110: FROM per_all_assignments_f
2111: WHERE assignment_id = p_assignment_id;
2112:
2113: -- =============================================================================
2114: -- Cursor to get the defined balance id for a given balance and dimension
2145: CURSOR c_cag_name (c_asg_id IN NUMBER
2146: ,c_eff_date IN DATE) IS
2147: SELECT cola.name
2148: FROM per_collective_agreements cola
2149: ,per_all_assignments_f asg
2150: WHERE asg.assignment_id = c_asg_id
2151: AND asg.collective_agreement_id = cola.collective_agreement_id
2152: AND cola.status = 'A'
2153: AND c_eff_date BETWEEN asg.effective_start_date
2240: --cursor to fetch the org id for the organization
2241: --attached to this assignment
2242: CURSOR c_get_org_id(c_eff_date IN DATE) IS
2243: SELECT organization_id
2244: FROM per_all_assignments_f
2245: WHERE assignment_id = p_assignment_id
2246: AND c_eff_date BETWEEN effective_start_date
2247: AND effective_end_date;
2248:
2260: l_dt_earned date; --11670586
2261:
2262: Cursor chk_term_asg(l_dt_earned date) IS --11670586
2263: SELECT 1
2264: FROM per_all_assignments_f asg
2265: WHERE assignment_id = p_assignment_id
2266: AND effective_start_date <= l_dt_earned --11670586
2267: AND assignment_status_type_id IN (SELECT assignment_status_type_id
2268: FROM per_assignment_status_types
3128: --
3129: function get_pension_salary
3130: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
3131: ,p_date_earned in date
3132: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3133: ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
3134: ,p_salary_balance_name in varchar2
3135: ,p_payroll_period in varchar2
3136: ,p_salary_balance_value out nocopy number
3159: l_error_status CHAR:='0';
3160: -- Cursor to get the hire date of the person
3161: CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
3162: SELECT max(date_start)
3163: FROM per_all_assignments_f asg
3164: ,per_periods_of_service pps
3165: WHERE pps.person_id = asg.person_id
3166: AND asg.assignment_id = c_asg_id
3167: AND pps.business_group_id = p_business_group_id
3192:
3193: -- Cursor to get the payroll_id as of a particular date
3194: CURSOR c_payroll_cur (c_effective_date IN DATE ) IS
3195: SELECT payroll_id
3196: FROM per_all_assignments_f
3197: WHERE assignment_id = p_assignment_id
3198: AND trunc(c_effective_date) BETWEEN effective_start_date
3199: AND effective_end_date;
3200:
3278: -- Get the current payroll_id for the person
3279: --
3280: SELECT payroll_id
3281: INTO l_payroll_id
3282: FROM per_all_assignments_f
3283: WHERE assignment_id = p_assignment_id
3284: AND trunc(p_date_earned) BETWEEN effective_start_date
3285: AND effective_end_date;
3286:
3390:
3391: --Cursor to find the org id from the assignment id
3392: CURSOR c_find_org_id IS
3393: SELECT organization_id
3394: FROM per_all_assignments_f
3395: WHERE assignment_id = p_assignment_id
3396: AND trunc(p_date_earned) between effective_start_date and effective_end_date
3397: AND business_group_id = p_business_group_id;
3398:
3639: ) return NUMBER IS
3640:
3641: --cursor to find the person id for the current assignment id
3642: cursor c_get_person_id IS
3643: select person_id from per_all_assignments_f
3644: where assignment_id = p_assignment_id
3645: and p_date_earned between effective_start_date and effective_end_date
3646: and business_group_id = p_business_group_id;
3647:
3647:
3648: --cursor to find all the assigment ids for a particular person id
3649: cursor c_get_all_assignmentid(c_person_id in number) IS
3650: select assignment_id
3651: from per_all_assignments_f asg
3652: where asg.person_id = c_person_id
3653: and asg.assignment_status_type_id in (select assignment_status_type_id from PER_ASS_STATUS_TYPE_AMENDS
3654: where business_group_id = p_business_group_id and pay_system_status = 'P' and active_flag = 'Y'
3655: union
3663: ))
3664: and p_date_earned between asg.effective_start_date and asg.effective_end_date;
3665:
3666: --Cursor to derive the part time percetage value
3667: CURSOR c_get_fte(c_assignment_id IN per_all_assignments_f.assignment_id%TYPE) IS
3668: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))
3669: FROM hr_soft_coding_keyflex target,
3670: per_all_assignments_f ASSIGN
3671: WHERE p_date_earned BETWEEN ASSIGN.effective_start_date AND
3666: --Cursor to derive the part time percetage value
3667: CURSOR c_get_fte(c_assignment_id IN per_all_assignments_f.assignment_id%TYPE) IS
3668: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))
3669: FROM hr_soft_coding_keyflex target,
3670: per_all_assignments_f ASSIGN
3671: WHERE p_date_earned BETWEEN ASSIGN.effective_start_date AND
3672: ASSIGN.effective_end_date
3673: AND ASSIGN.assignment_id = c_assignment_id
3674: AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
4512: -- |----------------------< get_bonus >-----------------------------|
4513: -- ------------------------------------------------------------------
4514: FUNCTION get_bonus
4515: ( p_date_earned in date
4516: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4517: ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
4518: ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
4519: ,p_pay_period_salary in number
4520: ,p_pay_period in varchar2
4580: FROM dual;
4581:
4582: CURSOR c_get_payroll_id(c_effective_date IN DATE) IS
4583: SELECT payroll_id
4584: FROM per_all_assignments_f
4585: WHERE assignment_id = p_assignment_id
4586: AND trunc(c_effective_date) BETWEEN
4587: effective_start_date AND effective_end_date;
4588:
4880: -- -----------------------------------------------------------------------
4881: function get_abp_entry_value
4882: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
4883: ,p_date_earned in date
4884: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4885: ,p_element_type_id in number
4886: ,p_input_value_name in varchar2
4887: ) return NUMBER IS
4888:
4888:
4889: -- Cursor to get the hire date of the person
4890: CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
4891: SELECT max(date_start)
4892: FROM per_all_assignments_f asg
4893: ,per_periods_of_service pps
4894: WHERE pps.person_id = asg.person_id
4895: AND asg.assignment_id = c_asg_id
4896: AND pps.business_group_id = p_business_group_id
4898:
4899: --cursor to get the start date for the assignment
4900: CURSOR c_get_asg_start IS
4901: SELECT min(effective_start_date)
4902: FROM per_all_assignments_f
4903: WHERE assignment_id = p_assignment_id;
4904:
4905: -- Cursor to get the entry value for the input name
4906: CURSOR c_entry_val_cur
5196:
5197: function get_avg_part_time_perc
5198: (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
5199: ,p_date_earned in date
5200: ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5201: ,p_assignment_action_id IN NUMBER
5202: ,p_period_start_date in DATE
5203: ,p_period_end_date in DATE
5204: ,p_avg_part_time_perc out NOCOPY number
5464: --
5465: FUNCTION get_pay_period_age
5466: (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
5467: ,p_date_earned IN DATE
5468: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5469: ,p_period_start_date IN DATE
5470: ) RETURN NUMBER IS
5471:
5472: --
5484: --
5485: CURSOR get_dob IS
5486: SELECT TRUNC(date_of_birth)
5487: FROM per_all_people_f per
5488: ,per_all_assignments_f paf
5489: WHERE per.person_id = paf.person_id
5490: AND paf.assignment_id = p_assignment_id
5491: AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
5492: AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
5495: -- Cursor to get the start date for the active asg
5496: --
5497: CURSOR c_get_assign_start_date IS
5498: SELECT min(asg.effective_start_date)
5499: FROM per_all_assignments_f asg
5500: ,per_assignment_status_types past
5501: WHERE asg.assignment_status_type_id = past.assignment_status_type_id
5502: AND past.per_system_status = 'ACTIVE_ASSIGN'
5503: --AND asg.effective_start_date <= trunc(p_period_start_date)