[Home] [Help]
1812: /* cursor is used to get retro component info for AU legislation. */
1813: cursor c_retro_info(cp_legislation_code in varchar2) is
1814: select retro_component_id, pts.time_span_id, ptd.short_name, ptd2.short_name
1815: from pay_retro_components prc,
1816: pay_time_spans pts,
1817: pay_time_definitions ptd,
1818: pay_time_definitions ptd2
1819: where pts.creator_id = prc.retro_component_id
1820: and prc.legislation_code = 'AU'
2645: -- then the start and end time definition ids will be updated.
2646: -- else a new time span will be created.
2647: --------------------------------------------------------------------------
2648: FUNCTION create_time_spans
2649: (p_creator_id pay_time_spans.creator_id%TYPE
2650: ,p_creator_type pay_time_spans.creator_type%TYPE
2651: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2652: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2653: RETURN NUMBER
2646: -- else a new time span will be created.
2647: --------------------------------------------------------------------------
2648: FUNCTION create_time_spans
2649: (p_creator_id pay_time_spans.creator_id%TYPE
2650: ,p_creator_type pay_time_spans.creator_type%TYPE
2651: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2652: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2653: RETURN NUMBER
2654: IS
2647: --------------------------------------------------------------------------
2648: FUNCTION create_time_spans
2649: (p_creator_id pay_time_spans.creator_id%TYPE
2650: ,p_creator_type pay_time_spans.creator_type%TYPE
2651: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2652: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2653: RETURN NUMBER
2654: IS
2655: --
2648: FUNCTION create_time_spans
2649: (p_creator_id pay_time_spans.creator_id%TYPE
2650: ,p_creator_type pay_time_spans.creator_type%TYPE
2651: ,p_start_time_def_id pay_time_spans.start_time_def_id%TYPE
2652: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2653: RETURN NUMBER
2654: IS
2655: --
2656: l_time_span_id pay_time_spans.time_span_id%TYPE;
2652: ,p_end_time_def_id pay_time_spans.end_time_def_id%TYPE)
2653: RETURN NUMBER
2654: IS
2655: --
2656: l_time_span_id pay_time_spans.time_span_id%TYPE;
2657: --
2658: CURSOR csr_time_span_exists
2659: IS
2660: SELECT time_span_id
2657: --
2658: CURSOR csr_time_span_exists
2659: IS
2660: SELECT time_span_id
2661: FROM pay_time_spans
2662: WHERE creator_id = p_creator_id
2663: AND creator_type = p_creator_type
2664: AND start_time_def_id = p_start_time_def_id
2665: AND end_time_def_id = p_end_time_def_id;
2665: AND end_time_def_id = p_end_time_def_id;
2666:
2667: CURSOR csr_get_time_span
2668: IS
2669: select pay_time_spans_s.nextval
2670: from dual;
2671: --
2672: BEGIN
2673: --
2680: open csr_get_time_span;
2681: fetch csr_get_time_span into l_time_span_id;
2682: close csr_get_time_span;
2683:
2684: INSERT INTO pay_time_spans
2685: (time_span_id
2686: ,creator_id
2687: ,creator_type
2688: ,start_time_def_id
2694: , p_end_time_def_id);
2695: --
2696: ELSE
2697: --
2698: UPDATE pay_time_spans
2699: SET start_time_def_id = p_start_time_def_id
2700: , end_time_def_id = p_end_time_def_id
2701: WHERE time_span_id = l_time_span_id;
2702: --
2724: ,p_time_span_id IN pay_element_span_usages.time_span_id%TYPE
2725: )
2726: IS
2727: --
2728: l_time_span_id pay_time_spans.time_span_id%TYPE;
2729: l_element_type_id pay_element_types_f.element_type_id%TYPE;
2730: l_element_span_usage_id pay_element_span_usages.element_span_usage_id%TYPE;
2731: --
2732: CURSOR csr_get_element_type_id IS
2942: l_db_lumpsum_comp pay_retro_components.retro_component_id%TYPE;
2943: l_defn_comp_id pay_retro_defn_components.definition_component_id%TYPE;
2944: l_start_time_id pay_time_definitions.time_definition_id%TYPE;
2945: l_end_time_id pay_time_definitions.time_definition_id%TYPE;
2946: l_time_span_id pay_time_spans.time_span_id%TYPE;
2947:
2948: BEGIN
2949:
2950: -- Initialize global variables