[Home] [Help]
67: ,p_trace_on IN VARCHAR2
68: )
69: IS
70:
71: l_extract_attributes pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes%ROWTYPE;
72: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
73:
74: BEGIN
75:
77:
78: -- Set the trace flag, but only the first time around
79: IF g_trace IS NULL THEN
80:
81: OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
82: FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
83: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
84:
85: l_business_group_id := fnd_global.per_business_group_id;
78: -- Set the trace flag, but only the first time around
79: IF g_trace IS NULL THEN
80:
81: OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
82: FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
83: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
84:
85: l_business_group_id := fnd_global.per_business_group_id;
86:
79: IF g_trace IS NULL THEN
80:
81: OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
82: FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
83: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
84:
85: l_business_group_id := fnd_global.per_business_group_id;
86:
87: BEGIN
2436: BEGIN -- store_cross_bg_details
2437:
2438: debug_enter(l_proc_name);
2439:
2440: IF pqp_gb_tp_pension_extracts.g_parent_request_id <> -1 THEN
2441:
2442: -- We are running the TPEP, get the master bg id
2443: OPEN csr_multiproc_data(p_record_type => 'M');
2444: FETCH csr_multiproc_data INTO l_master_bg_dets;
2470: -- Answer is NO
2471: g_crossbg_enabled := 'N';
2472:
2473: -- Also change the flag in Type 4 pkg
2474: pqp_gb_tp_pension_extracts.g_crossbg_enabled := 'N';
2475:
2476: END IF;
2477:
2478: debug('MasterBG :'||to_char(g_master_bg_id)||
2492:
2493: debug(l_proc_name, 30);
2494:
2495: -- Store all criteria establishments from this BG
2496: FOR l_estb_details IN pqp_gb_tp_pension_extracts.csr_estb_details
2497: (p_business_group_id => l_BG_dets.business_group_id
2498: ,p_lea_estb_yn => 'Y')
2499: LOOP -- 2
2500:
2497: (p_business_group_id => l_BG_dets.business_group_id
2498: ,p_lea_estb_yn => 'Y')
2499: LOOP -- 2
2500:
2501: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
2502:
2503: debug('Establishment Details...', 40);
2504: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2505: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2500:
2501: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
2502:
2503: debug('Establishment Details...', 40);
2504: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2505: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2506: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2507: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2508: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2501: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
2502:
2503: debug('Establishment Details...', 40);
2504: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2505: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2506: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2507: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2508: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2509: debug('...Establishment Details', 50);
2502:
2503: debug('Establishment Details...', 40);
2504: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2505: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2506: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2507: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2508: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2509: debug('...Establishment Details', 50);
2510:
2503: debug('Establishment Details...', 40);
2504: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2505: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2506: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2507: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2508: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2509: debug('...Establishment Details', 50);
2510:
2511: END LOOP; -- 2
2504: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2505: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2506: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2507: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2508: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2509: debug('...Establishment Details', 50);
2510:
2511: END LOOP; -- 2
2512: --
2549: FETCH csr_request_dets INTO l_req_dets;
2550: CLOSE csr_request_dets;
2551: */
2552:
2553: IF pqp_gb_tp_pension_extracts.g_parent_request_id = -1 THEN
2554:
2555: debug(l_proc_name, 20);
2556: UPDATE pqp_ext_cross_person_records
2557: SET processing_status = 'U'
2604: debug(p_effective_date);
2605: g_effective_date := p_effective_date;
2606:
2607: -- Call the Type 4 function to set globals in Type 4 package
2608: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2609: pqp_gb_tp_pension_extracts.set_extract_globals
2610: (p_business_group_id
2611: ,p_effective_date
2612: ,p_assignment_id
2605: g_effective_date := p_effective_date;
2606:
2607: -- Call the Type 4 function to set globals in Type 4 package
2608: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2609: pqp_gb_tp_pension_extracts.set_extract_globals
2610: (p_business_group_id
2611: ,p_effective_date
2612: ,p_assignment_id
2613: );
2610: (p_business_group_id
2611: ,p_effective_date
2612: ,p_assignment_id
2613: );
2614: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2615:
2616: -- Set the globals in this package before exiting this function
2617: -- ********* Variables ***************
2618: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2614: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2615:
2616: -- Set the globals in this package before exiting this function
2617: -- ********* Variables ***************
2618: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2619: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2615:
2616: -- Set the globals in this package before exiting this function
2617: -- ********* Variables ***************
2618: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2619: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2616: -- Set the globals in this package before exiting this function
2617: -- ********* Variables ***************
2618: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2619: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2624: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2617: -- ********* Variables ***************
2618: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2619: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2624: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2625: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2618: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2619: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2624: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2625: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2626:
2619: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2624: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2625: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2626:
2627: /************ Collections ***********
2620: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2624: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2625: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2626:
2627: /************ Collections ***********
2628: Sh we duplicate or just use the one in Type 4??
2621: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2622: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2623: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2624: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2625: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2626:
2627: /************ Collections ***********
2628: Sh we duplicate or just use the one in Type 4??
2629: Current thought is reuse from Type 4
2858:
2859: debug_enter(l_proc_name);
2860:
2861: -- Call the Type 4 function to set run dates in Type 4 package
2862: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2863: pqp_gb_tp_pension_extracts.set_run_effective_dates;
2864: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2865:
2866: -- Set the globals in this package before exiting this function
2859: debug_enter(l_proc_name);
2860:
2861: -- Call the Type 4 function to set run dates in Type 4 package
2862: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2863: pqp_gb_tp_pension_extracts.set_run_effective_dates;
2864: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2865:
2866: -- Set the globals in this package before exiting this function
2867: -- ********* Variables ****************
2860:
2861: -- Call the Type 4 function to set run dates in Type 4 package
2862: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2863: pqp_gb_tp_pension_extracts.set_run_effective_dates;
2864: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2865:
2866: -- Set the globals in this package before exiting this function
2867: -- ********* Variables ****************
2868: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2864: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2865:
2866: -- Set the globals in this package before exiting this function
2867: -- ********* Variables ****************
2868: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2869: g_next_effective_date := pqp_gb_tp_pension_extracts.g_next_effective_date;
2870: g_effective_run_date := pqp_gb_tp_pension_extracts.g_effective_run_date;
2871:
2872: -- For the sake of periodic report only
2865:
2866: -- Set the globals in this package before exiting this function
2867: -- ********* Variables ****************
2868: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2869: g_next_effective_date := pqp_gb_tp_pension_extracts.g_next_effective_date;
2870: g_effective_run_date := pqp_gb_tp_pension_extracts.g_effective_run_date;
2871:
2872: -- For the sake of periodic report only
2873: -- pension year end date is being set as the run end date.
2866: -- Set the globals in this package before exiting this function
2867: -- ********* Variables ****************
2868: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2869: g_next_effective_date := pqp_gb_tp_pension_extracts.g_next_effective_date;
2870: g_effective_run_date := pqp_gb_tp_pension_extracts.g_effective_run_date;
2871:
2872: -- For the sake of periodic report only
2873: -- pension year end date is being set as the run end date.
2874: g_pension_year_end_date := g_effective_run_date;
2874: g_pension_year_end_date := g_effective_run_date;
2875:
2876: -- Changed by Raju T. on 08/05/2002 as a development bugfix
2877: -- Commented out as this is now being created differently for Type 1
2878: -- g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2879:
2880: -- Set the start date of the pension year, i.e. 01-04-YYYY
2881: SELECT TO_DATE('01-04-'||
2882: DECODE
2894: g_last_effective_date := GREATEST(g_last_effective_date
2895: ,g_pension_year_start_date
2896: );
2897:
2898: IF g_last_effective_date <> pqp_gb_tp_pension_extracts.g_last_effective_date THEN
2899:
2900: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2901:
2902: END IF;
2896: );
2897:
2898: IF g_last_effective_date <> pqp_gb_tp_pension_extracts.g_last_effective_date THEN
2899:
2900: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2901:
2902: END IF;
2903:
2904: g_header_system_element:=
2907: fnd_date.date_to_canonical(g_effective_run_date) ||':'||
2908: fnd_date.date_to_canonical(g_next_effective_date)||':';
2909:
2910: -- Assign the newly created system element to the type4 global
2911: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
2912:
2913: debug('New Type 1 Header System Element :'||g_header_system_element);
2914: -- Changes END here
2915:
2967: g_next_effective_date := g_effective_date;
2968: g_effective_run_date := g_pension_year_end_date;
2969:
2970: -- Now set the Type 4 globals
2971: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2972: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
2973: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
2974: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
2975:
2968: g_effective_run_date := g_pension_year_end_date;
2969:
2970: -- Now set the Type 4 globals
2971: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2972: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
2973: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
2974: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
2975:
2976: debug('Header System Element :'||g_header_system_element);
2969:
2970: -- Now set the Type 4 globals
2971: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2972: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
2973: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
2974: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
2975:
2976: debug('Header System Element :'||g_header_system_element);
2977:
2970: -- Now set the Type 4 globals
2971: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2972: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
2973: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
2974: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
2975:
2976: debug('Header System Element :'||g_header_system_element);
2977:
2978: debug_exit(l_proc_name);
3153: -- Variable Declaration
3154: l_no_of_events NUMBER;
3155:
3156: -- Rowtype Variable Declaration
3157: l_event_group_details pqp_gb_tp_pension_extracts.csr_event_group_details%ROWTYPE;
3158: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3159: l_assignment_action_id number;
3160: l_proc_name VARCHAR2(61):=
3161: g_proc_name||'get_events';
3375: -- which we are reporting for, check the next record
3376: -- Also, if the start date is outside our date range, not look at this record.
3377: IF l_asg_details.start_date <= g_effective_run_date
3378: AND
3379: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
3380: AND -- Bugfix 3873376:SUSP Ignore suspended assignments
3381: l_asg_details.status_type <> 'SUSP_ASSIGN'
3382: AND -- Bugfix 4336613 :IGNR_TERMASG_3A Ignore terminated assignments
3383: l_asg_details.status_type <> 'TERM_ASSIGN' THEN
3408: -- Include otherwise
3409:
3410: IF NOT
3411: (-- To be excluded
3412: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3413: = '0966'
3414: OR
3415: (l_asg_details.ext_emp_cat_cd = 'P'
3416: -- Bugfix 3641851:ENH7 : Added this AND clause to INCLUDE
3415: (l_asg_details.ext_emp_cat_cd = 'P'
3416: -- Bugfix 3641851:ENH7 : Added this AND clause to INCLUDE
3417: -- part-time asgs at LEA establishment only
3418: AND
3419: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_type
3420: <> 'LEA_ESTB'
3421: )
3422: OR
3423: (l_asg_details.ext_emp_cat_cd = 'F'
3421: )
3422: OR
3423: (l_asg_details.ext_emp_cat_cd = 'F'
3424: AND
3425: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_type
3426: = 'IND_ESTB'
3427: )
3428: ) THEN
3429:
3545: l_inclusion_flag VARCHAR2(1) := 'Y';
3546: l_new_event_itr NUMBER(5);
3547:
3548: -- Rowtype Variable Declaration
3549: l_event_group_details pqp_gb_tp_pension_extracts.csr_event_group_details%ROWTYPE;
3550: l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
3551: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3552: l_asg_details csr_asg_details_up%ROWTYPE;
3553: l_prev_asg_details csr_asg_details_up%ROWTYPE;
3551: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3552: l_asg_details csr_asg_details_up%ROWTYPE;
3553: l_prev_asg_details csr_asg_details_up%ROWTYPE;
3554: l_next_asg_details csr_asg_details_up%ROWTYPE;
3555: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3556: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3557:
3558: l_temp_asg_details csr_asg_details_up%ROWTYPE;
3559: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
3552: l_asg_details csr_asg_details_up%ROWTYPE;
3553: l_prev_asg_details csr_asg_details_up%ROWTYPE;
3554: l_next_asg_details csr_asg_details_up%ROWTYPE;
3555: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3556: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3557:
3558: l_temp_asg_details csr_asg_details_up%ROWTYPE;
3559: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
3560:
3806: debug('Event worth considering',100);
3807:
3808: -- Check if the locaiton change is a valid one to report as leaver
3809: -- The new establishment is not a criteria establishment
3810: IF NOT pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
3811:
3812: -- The assignment has had a location change such that it has become a leaver
3813: l_leaver := 'Y';
3814: l_leaver_date := LEAST((l_asg_details.start_date - 1)
3846: -- The current and new Establishment nos. are different
3847: -- This chk is to ensure that the current location
3848: -- was not corrected after doing a datetrack update
3849: (-- Bugfix 3641851:CBF4 : Added EXISTS just to avoid exception
3850: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3851: AND
3852: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3853: <>
3854: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3848: -- was not corrected after doing a datetrack update
3849: (-- Bugfix 3641851:CBF4 : Added EXISTS just to avoid exception
3850: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3851: AND
3852: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3853: <>
3854: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3855: ) THEN
3856:
3850: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3851: AND
3852: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3853: <>
3854: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3855: ) THEN
3856:
3857: debug('Location change is a new line of service event, storing', 130);
3858:
3865:
3866: -- Bugfix 3470242:BUG1 : Now using new_location_id, estb_number can
3867: -- always be sought using the location id
3868: --g_asg_events(l_new_event_itr).new_estb_number :=
3869: -- pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
3870: g_asg_events(l_new_event_itr).new_location_id := l_asg_details.location_id;
3871:
3872: debug('New Location Id :'||to_char(l_asg_details.location_id), 135);
3873:
3924: debug('Inside IF for Elected pension changes check',160);
3925: debug('Event date :'||to_char(l_proration_dates(l_itr),'DD/MM/YYYY'), 165);
3926: debug('Update Type :'||l_proration_changes(l_itr), 166);
3927:
3928: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
3929: (p_assignment_id
3930: ,l_proration_dates(l_itr)
3931: );
3932: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3928: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
3929: (p_assignment_id
3930: ,l_proration_dates(l_itr)
3931: );
3932: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3933: --
3934: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3935: AND
3936: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
3930: ,l_proration_dates(l_itr)
3931: );
3932: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3933: --
3934: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3935: AND
3936: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
3937:
3938: -- Fetch the previous set of attributes
3935: AND
3936: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
3937:
3938: -- Fetch the previous set of attributes
3939: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
3940: --
3941: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3942: AND
3943: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
3937:
3938: -- Fetch the previous set of attributes
3939: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
3940: --
3941: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3942: AND
3943: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
3944:
3945: debug('B4 checking if asg passes chk_has_tchr_elected_pension',170);
3973: )
3974: );
3975: debug('Leaver Date :'||to_char(l_leaver_date,'DD/MM/YYYY'), 185);
3976:
3977: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
3978: EXIT; -- quit loop -- no need to search for other events
3979:
3980: END IF; -- l_inclusion_flag = 'N' THEN
3981: --
3978: EXIT; -- quit loop -- no need to search for other events
3979:
3980: END IF; -- l_inclusion_flag = 'N' THEN
3981: --
3982: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3983: --
3984: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3985: --
3986: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
3980: END IF; -- l_inclusion_flag = 'N' THEN
3981: --
3982: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3983: --
3984: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3985: --
3986: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
3987: --
3988: debug('Assignment attributes have had changes',190);
3982: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3983: --
3984: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3985: --
3986: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
3987: --
3988: debug('Assignment attributes have had changes',190);
3989: --
3990: END IF; -- if this date <> last date to eliminate duplicates
4036:
4037: debug('Event date :'||to_char(l_proration_dates(l_itr),'DD/MM/YYYY'), 215);
4038: debug('Update Type :'||l_proration_changes(l_itr), 216);
4039:
4040: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4041: (p_assignment_id
4042: ,l_proration_dates(l_itr)
4043: );
4044: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4040: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4041: (p_assignment_id
4042: ,l_proration_dates(l_itr)
4043: );
4044: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4045: --
4046: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4047: AND
4048: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4042: ,l_proration_dates(l_itr)
4043: );
4044: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4045: --
4046: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4047: AND
4048: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4049:
4050: -- Fetch the previous set of attributes
4047: AND
4048: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4049:
4050: -- Fetch the previous set of attributes
4051: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4052: --
4053: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4054: AND
4055: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4049:
4050: -- Fetch the previous set of attributes
4051: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4052: --
4053: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4054: AND
4055: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4056:
4057: debug('This assignment HAS become a Non-Teacher.',220);
4063: )
4064: );
4065: debug('Leaver Date :'||to_char(l_leaver_date,'DD/MM/YYYY'), 225);
4066:
4067: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4068: EXIT; -- quit loop -- no need to search for other events
4069: --
4070: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4071: --
4066:
4067: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4068: EXIT; -- quit loop -- no need to search for other events
4069: --
4070: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4071: --
4072: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4073: --
4074: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4068: EXIT; -- quit loop -- no need to search for other events
4069: --
4070: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4071: --
4072: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4073: --
4074: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4075: --
4076: debug('Assignment attributes have had changes',230);
4070: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4071: --
4072: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4073: --
4074: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4075: --
4076: debug('Assignment attributes have had changes',230);
4077: --
4078: END IF; -- if this date <> last date to eliminate duplicates and ignore corrections
4124: l_no_of_events NUMBER(5);
4125: l_inclusion_flag VARCHAR2(1) := 'N';
4126:
4127: -- Rowtype Variable Declaration
4128: l_event_group_details pqp_gb_tp_pension_extracts.csr_event_group_details%ROWTYPE;
4129: l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
4130: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
4131: l_asg_details csr_asg_details_up%ROWTYPE;
4132: l_prev_asg_details csr_asg_details_up%ROWTYPE;
4129: l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
4130: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
4131: l_asg_details csr_asg_details_up%ROWTYPE;
4132: l_prev_asg_details csr_asg_details_up%ROWTYPE;
4133: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4134: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4135:
4136: l_temp_asg_details csr_asg_details_up%ROWTYPE;
4137: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
4130: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
4131: l_asg_details csr_asg_details_up%ROWTYPE;
4132: l_prev_asg_details csr_asg_details_up%ROWTYPE;
4133: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4134: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4135:
4136: l_temp_asg_details csr_asg_details_up%ROWTYPE;
4137: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
4138:
4221: AND
4222: l_asg_details.status_type = 'ACTIVE_ASSIGN'
4223: -- check if the location is a valid location
4224: AND
4225: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
4226: ) THEN
4227:
4228: debug('now checking chk_has_tchr_elected_pension',42);
4229: -- BUG : 3873376
4348: -- The new establishment must be a criteria establishment
4349: -- and the previous establishment must not be a criteria establishment
4350: IF (csr_asg_details_dn%FOUND
4351: AND -- the new asg location is a criteria location
4352: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
4353: AND -- the previous was not a criteria location
4354: (NOT pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
4355: )
4356: ) THEN
4350: IF (csr_asg_details_dn%FOUND
4351: AND -- the new asg location is a criteria location
4352: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
4353: AND -- the previous was not a criteria location
4354: (NOT pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
4355: )
4356: ) THEN
4357: debug('now checking chk_has_tchr_elected_pension',101);
4358: -- BUG : 3873376
4431: ( l_proration_dates(l_itr) <>
4432: l_proration_dates(l_proration_dates.PRIOR(l_itr))
4433: ) THEN
4434:
4435: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4436: (p_assignment_id
4437: ,l_proration_dates(l_itr)
4438: );
4439: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4435: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4436: (p_assignment_id
4437: ,l_proration_dates(l_itr)
4438: );
4439: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4440: --
4441: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4442: AND
4443: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
4437: ,l_proration_dates(l_itr)
4438: );
4439: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4440: --
4441: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4442: AND
4443: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
4444:
4445: -- Fetch the previous set of attributes
4442: AND
4443: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
4444:
4445: -- Fetch the previous set of attributes
4446: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4447: --
4448: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4449: AND
4450: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
4444:
4445: -- Fetch the previous set of attributes
4446: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4447: --
4448: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4449: AND
4450: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
4451:
4452: debug('now checking chk_has_tchr_elected_pension',121);
4472: l_restarter_date := LEAST(l_pqp_asg_attributes.effective_start_date
4473: ,nvl(l_restarter_date,l_pqp_asg_attributes.effective_start_date)
4474: );
4475:
4476: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4477: EXIT; -- quit loop -- no need to search for other events
4478: END IF ; --l_inclusion_flag = 'Y' THEN
4479:
4480: --
4477: EXIT; -- quit loop -- no need to search for other events
4478: END IF ; --l_inclusion_flag = 'Y' THEN
4479:
4480: --
4481: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4482: --
4483: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4484: --
4485: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4479:
4480: --
4481: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4482: --
4483: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4484: --
4485: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4486: --
4487: debug('Assignment attributes have had changes',130);
4481: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4482: --
4483: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4484: --
4485: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4486: --
4487: debug('Assignment attributes have had changes',130);
4488: --
4489: END IF; -- if this date <> last date to eliminate duplicates
4536: AND
4537: (l_proration_changes(l_itr) <> 'C' -- Not a Correction
4538: ) THEN
4539:
4540: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4541: (p_assignment_id
4542: ,l_proration_dates(l_itr)
4543: );
4544: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4540: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4541: (p_assignment_id
4542: ,l_proration_dates(l_itr)
4543: );
4544: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4545: --
4546: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4547: AND
4548: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4542: ,l_proration_dates(l_itr)
4543: );
4544: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4545: --
4546: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4547: AND
4548: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4549:
4550: -- Fetch the previous set of attributes
4547: AND
4548: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4549:
4550: -- Fetch the previous set of attributes
4551: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4552: --
4553: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4554: AND
4555: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4549:
4550: -- Fetch the previous set of attributes
4551: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4552: --
4553: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4554: AND
4555: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4556:
4557: debug('now checking chk_has_tchr_elected_pension',141);
4577: l_restarter_date := LEAST(l_pqp_asg_attributes.effective_start_date
4578: ,nvl(l_restarter_date,l_pqp_asg_attributes.effective_start_date)
4579: );
4580:
4581: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4582: EXIT; -- quit loop -- no need to search for other events
4583: END IF ; -- l_inclusion_flag = 'Y' THEN
4584: --
4585: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4581: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4582: EXIT; -- quit loop -- no need to search for other events
4583: END IF ; -- l_inclusion_flag = 'Y' THEN
4584: --
4585: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4586: --
4587: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4588: --
4589: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4583: END IF ; -- l_inclusion_flag = 'Y' THEN
4584: --
4585: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4586: --
4587: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4588: --
4589: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4590: --
4591: debug('Assignment attributes have had changes',150);
4585: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4586: --
4587: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4588: --
4589: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4590: --
4591: debug('Assignment attributes have had changes',150);
4592: --
4593: END IF; -- if this date <> last date to eliminate duplicates and ignore corrections
5032:
5033: debug('Storing values to globals',80);
5034:
5035: -- 1) Type 4 global collection g_ext_asg_details
5036: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5037: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5033: debug('Storing values to globals',80);
5034:
5035: -- 1) Type 4 global collection g_ext_asg_details
5036: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5037: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5034:
5035: -- 1) Type 4 global collection g_ext_asg_details
5036: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5037: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5035: -- 1) Type 4 global collection g_ext_asg_details
5036: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5037: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5036: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5037: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5044:
5037: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5044:
5045: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5038: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5044:
5045: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5046: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5039: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5040: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5044:
5045: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5046: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5047:
5041: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5044:
5045: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5046: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5047:
5048: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).tp_safeguarded_grade :=
5049: l_pqp_asg_attributes.tp_safeguarded_grade;
5042: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
5043: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
5044:
5045: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5046: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5047:
5048: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).tp_safeguarded_grade :=
5049: l_pqp_asg_attributes.tp_safeguarded_grade;
5050:
5044:
5045: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5046: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5047:
5048: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).tp_safeguarded_grade :=
5049: l_pqp_asg_attributes.tp_safeguarded_grade;
5050:
5051: -- 2) Type 1 global collection g_ext_asg_details
5052: g_teach_asg_count := g_teach_asg_count +1;
5060: -- supply location and full time
5061: warn_if_supply_tchr_is_ft
5062: (p_assignment_id => l_sec_asgs.assignment_id
5063: ,p_establishment_number =>
5064: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
5065: ,p_ext_emp_cat_code => l_asg_details.ext_emp_cat_cd
5066: );
5067:
5068: -- Bugfix 3073562:GAP9b
5066: );
5067:
5068: -- Bugfix 3073562:GAP9b
5069: -- Increment the supply asg count if this is a supply assignment
5070: IF pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number = '0966' THEN
5071: g_supply_asg_count := g_supply_asg_count + 1;
5072: debug('Incrementing supply teacher count',90);
5073: END IF;
5074:
5099: (NOT g_ext_asg_details.EXISTS(p_primary_assignment_id))
5100: THEN
5101:
5102: debug('First Time and Primary not being included',110);
5103: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_primary_assignment_id) :=
5104: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id);
5105:
5106: g_ext_asg_details(p_primary_assignment_id) := g_ext_asg_details(l_sec_asgs.assignment_id);
5107: g_ext_asg_details(p_primary_assignment_id).report_asg := 'N';
5100: THEN
5101:
5102: debug('First Time and Primary not being included',110);
5103: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_primary_assignment_id) :=
5104: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id);
5105:
5106: g_ext_asg_details(p_primary_assignment_id) := g_ext_asg_details(l_sec_asgs.assignment_id);
5107: g_ext_asg_details(p_primary_assignment_id).report_asg := 'N';
5108:
5108:
5109: -- Bugfix 3470242:GAP4 : We need to store the person id of the primary asg
5110: -- as we have to update the results data for this person id
5111: -- while creating new LOS.
5112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_primary_assignment_id).person_id := p_person_id;
5113: g_ext_asg_details(p_primary_assignment_id).person_id := p_person_id;
5114:
5115: g_ext_asg_details(p_primary_assignment_id).secondary_assignment_id :=
5116: l_sec_asgs.assignment_id;
5718: ELSE -- asg record FOUND
5719:
5720: IF l_asg_details.location_id IS NOT NULL
5721: AND
5722: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
5723:
5724:
5725: debug('Re-evaluating override asg details', 110);
5726: -- Setting the current ext_emp_cat_cd, location_id and estb_number
5727: g_ext_asg_details(g_override_ft_asg_id).ext_emp_cat_cd := 'F';
5728: debug('l_asg_details.location_id :'||to_char(l_asg_details.location_id), 111);
5729: g_ext_asg_details(g_override_ft_asg_id).location_id := l_asg_details.location_id;
5730: debug('g_ext_asg_details(g_override_ft_asg_id).estb_number :'||g_ext_asg_details(g_override_ft_asg_id).estb_number, 111);
5731: debug('Estb number in Global :'||pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 112);
5732: g_ext_asg_details(g_override_ft_asg_id).estb_number :=
5733: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5734:
5735: ELSE
5729: g_ext_asg_details(g_override_ft_asg_id).location_id := l_asg_details.location_id;
5730: debug('g_ext_asg_details(g_override_ft_asg_id).estb_number :'||g_ext_asg_details(g_override_ft_asg_id).estb_number, 111);
5731: debug('Estb number in Global :'||pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 112);
5732: g_ext_asg_details(g_override_ft_asg_id).estb_number :=
5733: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5734:
5735: ELSE
5736: debug('WARNING: This asg might hv multiple leaver events', 113);
5737: END IF;
6350: l_row_name_source_name pay_element_type_extra_info.eei_information19%TYPE;
6351:
6352:
6353: -- Added a new varaibale to hold the element details .
6354: l_element_dtl pqp_gb_tp_pension_extracts.t_allowance_eles;
6355: l_element_type_id NUMBER;
6356: l_count NUMBER := 0;
6357: l_error NUMBER;
6358:
6377: -- Get Element Attribution for the given rate type
6378: IF p_rate_type_name IS NULL THEN
6379: -- Retention elements are defined so we need to create the element set
6380: -- using the l_tab_ret_aln_eles
6381: debug('g_tab_ret_aln_eles.COUNT: '||to_char(pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles.COUNT),20);
6382:
6383: l_element_dtl := pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles;
6384:
6385: ELSE
6379: -- Retention elements are defined so we need to create the element set
6380: -- using the l_tab_ret_aln_eles
6381: debug('g_tab_ret_aln_eles.COUNT: '||to_char(pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles.COUNT),20);
6382:
6383: l_element_dtl := pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles;
6384:
6385: ELSE
6386: -- Create the element set from the rate type passed.
6387: OPEN csr_element_set (c_name => p_rate_type_name
9058: --CALC_PT_SAL_OPTIONS: BUG : 4135481
9059: -- check if calc_part_time_sal OR calc_part_time_sal_old is to be used
9060: /*
9061: IF g_calc_sal_new <> 'Y' AND
9062: (pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number <> '0966' OR
9063: g_date_work_mode <> 'Y') THEN -- switch is YES for old method
9064: */
9065: IF g_calc_sal_new <> 'Y' THEN
9066: debug(l_proc_name, 90);
9132: IF g_proration = 'Y'
9133: THEN
9134: debug('g_proration is set to Y',299);
9135: debug('g_ext_asg_details(p_assignment_id).location_id : ' || to_char(g_ext_asg_details(p_assignment_id).location_id),1212);
9136: debug('pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :'|| pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number, 300) ;
9137: debug('g_date_work_mode is set to ' || g_date_work_mode,299);
9138: -- IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number <> '0966' OR g_date_work_mode <> 'Y' THEN
9139: debug(l_proc_name, 150);
9140: -- get the pre payments here. (if any)
9134: debug('g_proration is set to Y',299);
9135: debug('g_ext_asg_details(p_assignment_id).location_id : ' || to_char(g_ext_asg_details(p_assignment_id).location_id),1212);
9136: debug('pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :'|| pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number, 300) ;
9137: debug('g_date_work_mode is set to ' || g_date_work_mode,299);
9138: -- IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number <> '0966' OR g_date_work_mode <> 'Y' THEN
9139: debug(l_proc_name, 150);
9140: -- get the pre payments here. (if any)
9141: -- the function returns the modified dates for the Full Pyroll Periods
9142: -- these will be used to get the Full Payroll Period payments.
9157: END IF;
9158: -- 4336613 : moved code to fetch defined balance id to global settings.
9159:
9160: l_count := 1;
9161: -- IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' and g_date_work_mode = 'Y' THEN
9162: --ELSE
9163: debug('Calling csr_get_asg_act_id_retro',302);
9164:
9165: OPEN csr_get_asg_act_id_retro
9927: IF g_supply_asg_count > 0 THEN
9928:
9929: debug (l_proc_name, 20);
9930: debug ('Establishment Number: '||
9931: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number,30
9932: );
9933:
9934: -- Check whether the establishment number = 0966
9935: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966'
9931: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number,30
9932: );
9933:
9934: -- Check whether the establishment number = 0966
9935: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966'
9936: THEN
9937: debug (l_proc_name, 40);
9938:
9939: -- Calculate the part time salary paid in the entire year
10765: IF (g_extract_type = 'TP2' ) THEN
10766: l_eff_start_date := pqp_gb_tp_type2_functions.g_effective_start_date;
10767: l_eff_end_date := pqp_gb_tp_type2_functions.g_effective_end_date;
10768: ELSIF (g_extract_type = 'TP4' ) THEN
10769: l_eff_start_date := pqp_gb_tp_pension_extracts.g_last_effective_date ;
10770: l_eff_end_date := pqp_gb_tp_pension_extracts.g_effective_run_date ;
10771: END IF;
10772:
10773: FOR l_multiper IN c_multiper
10766: l_eff_start_date := pqp_gb_tp_type2_functions.g_effective_start_date;
10767: l_eff_end_date := pqp_gb_tp_type2_functions.g_effective_end_date;
10768: ELSIF (g_extract_type = 'TP4' ) THEN
10769: l_eff_start_date := pqp_gb_tp_pension_extracts.g_last_effective_date ;
10770: l_eff_end_date := pqp_gb_tp_pension_extracts.g_effective_run_date ;
10771: END IF;
10772:
10773: FOR l_multiper IN c_multiper
10774: (p_person_id => l_person_id
10923: )
10924: VALUES
10925: ('X'
10926: ,ben_ext_thread.g_ext_dfn_id --ENH3
10927: ,pqp_gb_tp_pension_extracts.g_lea_number --ENH3
10928: ,p_business_group_id
10929: ,p_person_id
10930: ,p_national_identifier
10931: ,p_assignment_id
11247: -- Bugfix -- Bugfix 3671727: Performance enhancement
11248: -- If no location exists in the list of valid criteria
11249: -- establishments, then no point doing all checks
11250: -- Just warn once and skip every assignment
11251: IF pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0 THEN
11252:
11253: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11254: l_inclusion_flag := 'N';
11255:
11252:
11253: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11254: l_inclusion_flag := 'N';
11255:
11256: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11257: -- Call TP4 pkg proc to warning for no locations
11258: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11259: (p_assignment_id => p_assignment_id) ;
11260: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11254: l_inclusion_flag := 'N';
11255:
11256: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11257: -- Call TP4 pkg proc to warning for no locations
11258: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11259: (p_assignment_id => p_assignment_id) ;
11260: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11261:
11262: ELSE -- Valid locations EXIST
11256: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11257: -- Call TP4 pkg proc to warning for no locations
11258: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11259: (p_assignment_id => p_assignment_id) ;
11260: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11261:
11262: ELSE -- Valid locations EXIST
11263:
11264: -- Reset the supply assignment count
11435: debug('Storing values in globals',70);
11436: l_asg_details.leaver_date := l_leaver_date;
11437:
11438: -- 1) Type 4 global collection g_ext_asg_details
11439: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11440: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11436: l_asg_details.leaver_date := l_leaver_date;
11437:
11438: -- 1) Type 4 global collection g_ext_asg_details
11439: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11440: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11437:
11438: -- 1) Type 4 global collection g_ext_asg_details
11439: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11440: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11438: -- 1) Type 4 global collection g_ext_asg_details
11439: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11440: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11439: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11440: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11447:
11440: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11447:
11448: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11441: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11447:
11448: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11449: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11442: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11443: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11447:
11448: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11449: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11450:
11444: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11447:
11448: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11449: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11450:
11451: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11452: l_pqp_asg_attributes.tp_safeguarded_grade;
11445: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11446: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11447:
11448: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11449: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11450:
11451: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11452: l_pqp_asg_attributes.tp_safeguarded_grade;
11453:
11447:
11448: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11449: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11450:
11451: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11452: l_pqp_asg_attributes.tp_safeguarded_grade;
11453:
11454: -- 2) Type 1 global collection g_ext_asg_details
11455: g_ext_asg_details(p_assignment_id) := l_asg_details;
11463: -- supply location and full time
11464: warn_if_supply_tchr_is_ft
11465: (p_assignment_id => p_assignment_id
11466: ,p_establishment_number =>
11467: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
11468: ,p_ext_emp_cat_code => l_asg_details.ext_emp_cat_cd
11469: );
11470:
11471: -- Bugfix 3073562:GAP9b
11469: );
11470:
11471: -- Bugfix 3073562:GAP9b
11472: -- Increment the supply asg count if this is a supply assignment
11473: IF pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number = '0966' THEN
11474: g_supply_asg_count := g_supply_asg_count + 1;
11475: debug('Incrementing supply teacher count',75);
11476: END IF;
11477:
11502: debug('Primary Asg '|| to_char(p_assignment_id)||' is not terminated', 84) ;
11503: END IF; -- l_inclusion_flag = 'Y' Bug 5408932
11504: debug('g_asg_count: '|| to_char(g_asg_count), 85) ;
11505: debug('Number of TP1 teachers on periodic report :'||
11506: fnd_number.number_to_canonical(pqp_gb_tp_pension_extracts.g_ext_asg_details.COUNT));
11507:
11508: ELSE
11509: -- Bugfix 3073562:GAP6
11510: -- Continue looking for secondary asgs
11615: -- The following piece of code raises a warning if
11616: -- there exist more than one lea with the same lea Number within a BG.
11617: -- the warning is raised for the first valid assignment for a single Run.
11618: -- the flag for warning is set during the global setting through set_extract_globals.
11619: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11620: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
11621: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11622:
11623: -- The following proc raises a warning, if there is
11616: -- there exist more than one lea with the same lea Number within a BG.
11617: -- the warning is raised for the first valid assignment for a single Run.
11618: -- the flag for warning is set during the global setting through set_extract_globals.
11619: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11620: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
11621: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11622:
11623: -- The following proc raises a warning, if there is
11624: -- one FT teaching asg for the person
11617: -- the warning is raised for the first valid assignment for a single Run.
11618: -- the flag for warning is set during the global setting through set_extract_globals.
11619: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11620: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
11621: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11622:
11623: -- The following proc raises a warning, if there is
11624: -- one FT teaching asg for the person
11625: -- and there are multiple person records.
11636: );
11637: debug('g_asg_count: '|| to_char(g_asg_count), 258) ;
11638: END IF ; -- l_inclusion_flag = 'Y'
11639:
11640: END IF; -- pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0
11641:
11642: debug('l_inclusion_flag: '||l_inclusion_flag,260);
11643: debug_exit(l_proc_name);
11644:
11704:
11705:
11706: -- Bugifx 2848696 : Now using ben_ext_person.g_effective_date
11707: -- instead of p_effective_date
11708: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11709: g_reporting_mode := upper(pqp_gb_tp_pension_extracts.get_extract_udt_info
11710: ('Attribute Location Qualifier 1' -- column
11711: ,'Criteria' -- row
11712: ,ben_ext_person.g_effective_date -- p_effective_date
11705:
11706: -- Bugifx 2848696 : Now using ben_ext_person.g_effective_date
11707: -- instead of p_effective_date
11708: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11709: g_reporting_mode := upper(pqp_gb_tp_pension_extracts.get_extract_udt_info
11710: ('Attribute Location Qualifier 1' -- column
11711: ,'Criteria' -- row
11712: ,ben_ext_person.g_effective_date -- p_effective_date
11713: )
11711: ,'Criteria' -- row
11712: ,ben_ext_person.g_effective_date -- p_effective_date
11713: )
11714: );
11715: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11716:
11717: set_annual_run_dates;
11718:
11719: -- Fetch element ids from balance's
11726: -- Bugfix -- Bugfix 3671727: Performance enhancement
11727: -- If no location exists in the list of valid criteria
11728: -- establishments, then no point doing all checks
11729: -- Just warn once and skip every assignment
11730: IF pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0 THEN
11731:
11732: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11733: l_inclusion_flag := 'N';
11734:
11731:
11732: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11733: l_inclusion_flag := 'N';
11734:
11735: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11736: -- Call TP4 pkg proc to warning for no locations
11737: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11738: (p_assignment_id => p_assignment_id) ;
11739: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11733: l_inclusion_flag := 'N';
11734:
11735: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11736: -- Call TP4 pkg proc to warning for no locations
11737: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11738: (p_assignment_id => p_assignment_id) ;
11739: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11740:
11741: ELSE -- Valid locations EXIST
11735: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11736: -- Call TP4 pkg proc to warning for no locations
11737: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11738: (p_assignment_id => p_assignment_id) ;
11739: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11740:
11741: ELSE -- Valid locations EXIST
11742:
11743: debug('Reporting Mode for leavers is :'||g_reporting_mode,20);
11886:
11887: /* Replacing this with assignment statements for individual elements as
11888: there is a plan to extent the assignment cursor to include more cols.
11889:
11890: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id) := l_asg_details;
11891:
11892: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11893: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11894:
11888: there is a plan to extent the assignment cursor to include more cols.
11889:
11890: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id) := l_asg_details;
11891:
11892: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11893: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11894:
11895: -- already populated this in l_asg_details before received in this function
11896: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
11889:
11890: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id) := l_asg_details;
11891:
11892: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11893: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11894:
11895: -- already populated this in l_asg_details before received in this function
11896: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
11897:
11892: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11893: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11894:
11895: -- already populated this in l_asg_details before received in this function
11896: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
11897:
11898: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11899: l_pqp_asg_attributes.tp_safeguarded_grade;
11900: */
11894:
11895: -- already populated this in l_asg_details before received in this function
11896: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
11897:
11898: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11899: l_pqp_asg_attributes.tp_safeguarded_grade;
11900: */
11901:
11902: -- First assign the leaver date to the asg details rowtype variable
11909: debug('Storing values in globals',50);
11910:
11911: -- 1) Type 4 global collection g_ext_asg_details
11912:
11913: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11914: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11910:
11911: -- 1) Type 4 global collection g_ext_asg_details
11912:
11913: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11914: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11911: -- 1) Type 4 global collection g_ext_asg_details
11912:
11913: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11914: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11912:
11913: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11914: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11913: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11914: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11921:
11914: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11921:
11922: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11915: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11921:
11922: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11923: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11916: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
11917: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11921:
11922: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11923: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11924:
11918: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11921:
11922: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11923: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11924:
11925: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11926: l_pqp_asg_attributes.tp_safeguarded_grade;
11919: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11920: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11921:
11922: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11923: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11924:
11925: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11926: l_pqp_asg_attributes.tp_safeguarded_grade;
11927:
11921:
11922: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11923: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11924:
11925: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11926: l_pqp_asg_attributes.tp_safeguarded_grade;
11927:
11928: -- 2) Type 1 global collection g_ext_asg_details
11929: g_teach_asg_count :=g_teach_asg_count +1;
11937: -- supply location and full time
11938: warn_if_supply_tchr_is_ft
11939: (p_assignment_id => p_assignment_id
11940: ,p_establishment_number =>
11941: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
11942: ,p_ext_emp_cat_code => l_asg_details.ext_emp_cat_cd
11943: );
11944:
11945: -- Bugfix 3073562:GAP9b
11943: );
11944:
11945: -- Bugfix 3073562:GAP9b
11946: -- Increment the supply asg count if this is a supply assignment
11947: IF pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number = '0966' THEN
11948: g_supply_asg_count := g_supply_asg_count + 1;
11949: debug('Incrementing supply teacher count',55);
11950: END IF;
11951:
11970: ,p_must_be_a_leaver => FALSE
11971: );
11972: debug('g_asg_count: '|| to_char(g_asg_count), 65) ;
11973: debug('Number of TP1 teachers on annual report :'||
11974: fnd_number.number_to_canonical(pqp_gb_tp_pension_extracts.g_ext_asg_details.COUNT),70);
11975: --
11976: END IF; -- 2) l_inclusion_flag = 'Y' THEN
11977: --
11978: ELSE -- 1) inclusion flag is 'N'
12052: -- The following piece of code raises a warning if
12053: -- there exist more than one lea with the same lea Number within a BG.
12054: -- the warning is raised for the first valid assignment for a single Run.
12055: -- the flag for warning is set during the global setting through set_extract_globals.
12056: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
12057: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
12058: pqp_gb_tp_pension_extracts.g_nested_level := 0;
12059:
12060: -- The following proc raises a warning, if there is
12053: -- there exist more than one lea with the same lea Number within a BG.
12054: -- the warning is raised for the first valid assignment for a single Run.
12055: -- the flag for warning is set during the global setting through set_extract_globals.
12056: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
12057: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
12058: pqp_gb_tp_pension_extracts.g_nested_level := 0;
12059:
12060: -- The following proc raises a warning, if there is
12061: -- one FT teaching asg for the person
12054: -- the warning is raised for the first valid assignment for a single Run.
12055: -- the flag for warning is set during the global setting through set_extract_globals.
12056: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
12057: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
12058: pqp_gb_tp_pension_extracts.g_nested_level := 0;
12059:
12060: -- The following proc raises a warning, if there is
12061: -- one FT teaching asg for the person
12062: -- and there are multiple person records.
12076:
12077:
12078: END IF;
12079:
12080: END IF; -- pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0
12081:
12082: debug_exit(l_proc_name);
12083:
12084: RETURN l_inclusion_flag;
13928: ELSE -- asg record FOUND
13929:
13930: IF l_asg_details.location_id IS NOT NULL
13931: AND
13932: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
13933:
13934: -- Setting the current ext_emp_cat_cd, location_id and estb_number
13935: debug('Re-evaluating l_assignment_id details', 24);
13936:
13947:
13948: debug('Current estb number for l_assignment_id :'||
13949: g_ext_asg_details(l_assignment_id).estb_number, 26);
13950: debug('Estb number in Global :'||
13951: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 27);
13952: g_ext_asg_details(l_assignment_id).estb_number :=
13953: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
13954:
13955: ELSE
13949: g_ext_asg_details(l_assignment_id).estb_number, 26);
13950: debug('Estb number in Global :'||
13951: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 27);
13952: g_ext_asg_details(l_assignment_id).estb_number :=
13953: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
13954:
13955: ELSE
13956: debug('WARNING: This asg might hv multiple leaver events', 27);
13957: END IF;
13995: -- If override asg has been set, we need to
13996: -- refresh estb number from this asg
13997: debug('Refreshing from override asg', 34);
13998: p_dtl_rec.val_10 :=
13999: nvl(pqp_gb_tp_pension_extracts.g_criteria_estbs
14000: (g_ext_asg_details(l_assignment_id).location_id
14001: ).estb_number
14002: ,p_dtl_rec.val_10
14003: );
14038:
14039: -- Bugfix 3470242:BUG1 : Now using new_location_id, estb_number can
14040: -- always be sought using the location id
14041: -- p_dtl_rec.val_10 := nvl(p_event_details.new_estb_number, p_dtl_rec.val_10);
14042: p_dtl_rec.val_10 := nvl(pqp_gb_tp_pension_extracts.g_criteria_estbs
14043: (p_event_details.new_location_id).estb_number
14044: , p_dtl_rec.val_10
14045: );
14046:
14049: -- We now refresh unconditionally if none of the
14050: -- above conditions apply to fix the rehire problem
14051: debug('Refreshing from l_assignment_id', 40);
14052: p_dtl_rec.val_10
14053: := nvl(pqp_gb_tp_pension_extracts.g_criteria_estbs
14054: (g_ext_asg_details(l_assignment_id).location_id
14055: ).estb_number
14056: ,p_dtl_rec.val_10
14057: );
14100: CLOSE csr_pqp_asg_attributes_up;
14101:
14102: IF l_pqp_asg_attributes_up.tp_safeguarded_grade IS NOT NULL THEN
14103:
14104: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14105: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14106: g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14107: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14108:
14108:
14109: debug(l_proc_name, 80);
14110: IF l_report_primary_asg = 'N' THEN
14111: -- We need to keep the global record for primary asg updated as well
14112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade
14113: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14114: g_ext_asg_details(p_assignment_id).tp_safeguarded_grade
14115: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14116:
14125:
14126: debug(l_proc_name, 100);
14127:
14128: l_temp_date :=
14129: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date;
14130:
14131: -- Bugfix 3880543:GRD : We need to set the date in global
14132: -- collection for primary row as well, take a bakup
14133: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14131: -- Bugfix 3880543:GRD : We need to set the date in global
14132: -- collection for primary row as well, take a bakup
14133: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14134: l_temp_sfgrade :=
14135: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14136:
14137: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14138: := to_date(p_dtl_rec.val_13,'DDMMRR');
14139:
14133: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14134: l_temp_sfgrade :=
14135: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14136:
14137: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14138: := to_date(p_dtl_rec.val_13,'DDMMRR');
14139:
14140: -- Bugfix 3880543:GRD : We need to set the date in global
14141: -- collection for primary row as well
14140: -- Bugfix 3880543:GRD : We need to set the date in global
14141: -- collection for primary row as well
14142: g_ext_asg_details(p_assignment_id).teacher_start_date := to_date(p_dtl_rec.val_13,'DDMMRR');
14143:
14144: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14145: := NULL;
14146:
14147: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14148: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14143:
14144: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14145: := NULL;
14146:
14147: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14148: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14149: (p_assignment_id => p_assignment_id
14150: );
14151: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14144: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14145: := NULL;
14146:
14147: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14148: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14149: (p_assignment_id => p_assignment_id
14150: );
14151: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14152:
14147: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14148: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14149: (p_assignment_id => p_assignment_id
14150: );
14151: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14152:
14153: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date:=
14154: l_temp_date;
14155: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade:=
14149: (p_assignment_id => p_assignment_id
14150: );
14151: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14152:
14153: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date:=
14154: l_temp_date;
14155: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade:=
14156: l_temp_sfgrade;
14157:
14151: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14152:
14153: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date:=
14154: l_temp_date;
14155: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade:=
14156: l_temp_sfgrade;
14157:
14158: -- Bugfix 3880543:GRD : We need to set the date in global
14159: -- collection for primary row as well, now restoring the date
14261: -- Get the new grade value
14262: -- We need to fool the type 4 function so manipulate the global variables temporarily
14263:
14264: -- Make a copy of the original values
14265: l_temp_date := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date;
14266: -- Bugfix 3880543:GRD : We need to set the date in global
14267: -- collection for primary row as well, take a bakup
14268: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14269: l_temp_sfgrade := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14265: l_temp_date := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date;
14266: -- Bugfix 3880543:GRD : We need to set the date in global
14267: -- collection for primary row as well, take a bakup
14268: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14269: l_temp_sfgrade := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14270:
14271: -- Assign the effective date we need and make the sfgrade NULL
14272: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14273: := to_date(p_dtl_rec.val_13,'DDMMRR');
14268: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14269: l_temp_sfgrade := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14270:
14271: -- Assign the effective date we need and make the sfgrade NULL
14272: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14273: := to_date(p_dtl_rec.val_13,'DDMMRR');
14274:
14275: -- Bugfix 3880543:GRD : We need to set the date in global
14276: -- collection for primary row as well
14274:
14275: -- Bugfix 3880543:GRD : We need to set the date in global
14276: -- collection for primary row as well
14277: g_ext_asg_details(p_assignment_id).teacher_start_date := to_date(p_dtl_rec.val_13,'DDMMRR');
14278: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade := NULL;
14279:
14280: -- Bugfix 3470242:BUG1 : Location change has happened so we need to
14281: -- replace the location_id in tp4 global collection.
14282: -- Currently not replacing in tp1 collection as its not
14285: ,'PQP_GB_TP_ASG_LOCATION'
14286: ) > 0 THEN
14287:
14288: debug(l_proc_name, 130);
14289: -- l_temp_location_id := pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id;
14290: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id :=
14291: p_event_details.new_location_id;
14292: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14293: p_dtl_rec.val_10 <> '0966' THEN
14286: ) > 0 THEN
14287:
14288: debug(l_proc_name, 130);
14289: -- l_temp_location_id := pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id;
14290: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id :=
14291: p_event_details.new_location_id;
14292: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14293: p_dtl_rec.val_10 <> '0966' THEN
14294: g_supp_teacher := 'Y';
14288: debug(l_proc_name, 130);
14289: -- l_temp_location_id := pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id;
14290: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id :=
14291: p_event_details.new_location_id;
14292: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14293: p_dtl_rec.val_10 <> '0966' THEN
14294: g_supp_teacher := 'Y';
14295: END IF;
14296: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number := p_dtl_rec.val_10;
14292: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14293: p_dtl_rec.val_10 <> '0966' THEN
14294: g_supp_teacher := 'Y';
14295: END IF;
14296: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number := p_dtl_rec.val_10;
14297:
14298:
14299: END IF;
14300:
14297:
14298:
14299: END IF;
14300:
14301: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14302: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14303: (p_assignment_id => p_assignment_id
14304: );
14305: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14298:
14299: END IF;
14300:
14301: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14302: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14303: (p_assignment_id => p_assignment_id
14304: );
14305: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14306:
14301: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14302: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14303: (p_assignment_id => p_assignment_id
14304: );
14305: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14306:
14307: IF l_new_sfgrade = 'INVALID' THEN
14308:
14309: l_error := pqp_gb_tp_extract_functions.raise_extract_warning
14335:
14336: END IF;
14337:
14338: -- Assign back the original values to the global variables
14339: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date := l_temp_date;
14340: -- Bugfix 3880543:GRD : We need to set the date in global
14341: -- collection for primary row as well, now restoring the date
14342: g_ext_asg_details(p_assignment_id).teacher_start_date := l_temp_date_primary;
14343: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade := l_temp_sfgrade;
14339: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date := l_temp_date;
14340: -- Bugfix 3880543:GRD : We need to set the date in global
14341: -- collection for primary row as well, now restoring the date
14342: g_ext_asg_details(p_assignment_id).teacher_start_date := l_temp_date_primary;
14343: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade := l_temp_sfgrade;
14344:
14345: END IF; -- p_rec_type = 'NEW' for Salary Scale
14346: --
14347: debug('val_11 :'||p_dtl_rec.val_11, 140);
17123:
17124:
17125:
17126: -- Get UDT ID
17127: l_udt_id := pqp_gb_tp_pension_extracts.get_udt_id
17128: (p_udt_name => l_udt_name);
17129:
17130: -- Get the user rows information for this UDT
17131: --
17172: END IF;
17173:
17174: IF upper(l_user_value) in ('E','ELEMENT') THEN
17175:
17176: l_element_type_id := pqp_gb_tp_pension_extracts.get_allow_ele_info
17177: (p_assignment_id => p_assignment_id
17178: ,p_effective_date => p_effective_date
17179: ,p_table_name => l_udt_name
17180: ,p_row_name => l_user_row_name
17194: ELSIF upper(l_user_value) in ('R','RATE TYPE') THEN-- element type id is null
17195: -- Check for rate type
17196: debug(l_proc_name, 50);
17197:
17198: l_tab_mng_aln_eles := pqp_gb_tp_pension_extracts.get_allow_code_rt_ele_info
17199: (p_assignment_id => p_assignment_id
17200: ,p_effective_date => p_effective_date
17201: ,p_table_name => l_udt_name
17202: ,p_row_name => l_user_row_name
17255: END IF;
17256:
17257: IF upper(l_user_value) in ('E','ELEMENT') THEN
17258:
17259: l_element_type_id := pqp_gb_tp_pension_extracts.get_allow_ele_info
17260: (p_assignment_id => p_assignment_id
17261: ,p_effective_date => p_effective_date
17262: ,p_table_name => l_udt_name
17263: ,p_row_name => l_user_row_name
17277: ELSIF upper(l_user_value) in ('R','RATE TYPE') THEN-- element type id is null
17278: -- Check for rate type
17279: debug(l_proc_name, 50);
17280:
17281: l_tab_mng_aln_eles := pqp_gb_tp_pension_extracts.get_allow_code_rt_ele_info
17282: (p_assignment_id => p_assignment_id
17283: ,p_effective_date => p_effective_date
17284: ,p_table_name => l_udt_name
17285: ,p_row_name => l_user_row_name