[Home] [Help]
2015: /* cursor is used to get retro component info for AU legislation. */
2016: cursor c_retro_info(cp_legislation_code in varchar2) is
2017: select retro_component_id, pts.time_span_id, ptd.short_name, ptd2.short_name
2018: from pay_retro_components prc,
2019: pay_time_spans pts,
2020: pay_time_definitions ptd,
2021: pay_time_definitions ptd2
2022: where pts.creator_id = prc.retro_component_id
2023: and prc.legislation_code = 'AU'
2914: -- then the start and end time definition ids will be updated.
2915: -- else a new time span will be created.
2916: --------------------------------------------------------------------------
2917: FUNCTION create_time_spans
2918: (p_creator_id pay_time_spans.creator_id%TYPE
2919: ,p_creator_type pay_time_spans.creator_type%TYPE
2920: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2921: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2922: RETURN NUMBER
2915: -- else a new time span will be created.
2916: --------------------------------------------------------------------------
2917: FUNCTION create_time_spans
2918: (p_creator_id pay_time_spans.creator_id%TYPE
2919: ,p_creator_type pay_time_spans.creator_type%TYPE
2920: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2921: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2922: RETURN NUMBER
2923: IS
2916: --------------------------------------------------------------------------
2917: FUNCTION create_time_spans
2918: (p_creator_id pay_time_spans.creator_id%TYPE
2919: ,p_creator_type pay_time_spans.creator_type%TYPE
2920: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2921: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2922: RETURN NUMBER
2923: IS
2924: --
2917: FUNCTION create_time_spans
2918: (p_creator_id pay_time_spans.creator_id%TYPE
2919: ,p_creator_type pay_time_spans.creator_type%TYPE
2920: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2921: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2922: RETURN NUMBER
2923: IS
2924: --
2925: l_time_span_id pay_time_spans.time_span_id%TYPE;
2921: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2922: RETURN NUMBER
2923: IS
2924: --
2925: l_time_span_id pay_time_spans.time_span_id%TYPE;
2926: --
2927: CURSOR csr_time_span_exists
2928: IS
2929: SELECT time_span_id
2926: --
2927: CURSOR csr_time_span_exists
2928: IS
2929: SELECT time_span_id
2930: FROM pay_time_spans
2931: WHERE creator_id = p_creator_id
2932: AND creator_type = p_creator_type
2933: AND start_time_def_id = p_start_time_def_id
2934: AND end_time_def_id = p_end_time_def_id;
2934: AND end_time_def_id = p_end_time_def_id;
2935:
2936: CURSOR csr_get_time_span
2937: IS
2938: select pay_time_spans_s.nextval
2939: from dual;
2940: --
2941: BEGIN
2942: --
2949: open csr_get_time_span;
2950: fetch csr_get_time_span into l_time_span_id;
2951: close csr_get_time_span;
2952:
2953: INSERT INTO pay_time_spans
2954: (time_span_id
2955: ,creator_id
2956: ,creator_type
2957: ,start_time_def_id
2963: , p_end_time_def_id);
2964: --
2965: ELSE
2966: --
2967: UPDATE pay_time_spans
2968: SET start_time_def_id = p_start_time_def_id
2969: , end_time_def_id = p_end_time_def_id
2970: WHERE time_span_id = l_time_span_id;
2971: --
2993: ,p_time_span_id IN pay_element_span_usages.time_span_id%TYPE
2994: )
2995: IS
2996: --
2997: l_time_span_id pay_time_spans.time_span_id%TYPE;
2998: l_element_type_id pay_element_types_f.element_type_id%TYPE;
2999: l_element_span_usage_id pay_element_span_usages.element_span_usage_id%TYPE;
3000: --
3001: CURSOR csr_get_element_type_id IS
3272: l_db_lumpsum_comp pay_retro_components.retro_component_id%TYPE;
3273: l_defn_comp_id pay_retro_defn_components.definition_component_id%TYPE;
3274: l_start_time_id pay_time_definitions.time_definition_id%TYPE;
3275: l_end_time_id pay_time_definitions.time_definition_id%TYPE;
3276: l_time_span_id pay_time_spans.time_span_id%TYPE;
3277:
3278: /* bug 10388533 - check if Statutory PPL Payment element has retro event group */
3279:
3280: CURSOR c_get_event_group_le IS