[Home] [Help]
66: ,p_trace_on IN VARCHAR2
67: )
68: IS
69:
70: l_extract_attributes pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes%ROWTYPE;
71: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
72:
73: BEGIN
74:
76:
77: -- Set the trace flag, but only the first time around
78: IF g_trace IS NULL THEN
79:
80: OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
81: FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
82: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
83:
84: l_business_group_id := fnd_global.per_business_group_id;
77: -- Set the trace flag, but only the first time around
78: IF g_trace IS NULL THEN
79:
80: OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
81: FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
82: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
83:
84: l_business_group_id := fnd_global.per_business_group_id;
85:
78: IF g_trace IS NULL THEN
79:
80: OPEN pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
81: FETCH pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes INTO l_extract_attributes;
82: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_extract_attributes;
83:
84: l_business_group_id := fnd_global.per_business_group_id;
85:
86: BEGIN
2464: BEGIN -- store_cross_bg_details
2465:
2466: debug_enter(l_proc_name);
2467:
2468: IF pqp_gb_tp_pension_extracts.g_parent_request_id <> -1 THEN
2469:
2470: -- We are running the TPEP, get the master bg id
2471: OPEN csr_multiproc_data(p_record_type => 'M');
2472: FETCH csr_multiproc_data INTO l_master_bg_dets;
2498: -- Answer is NO
2499: g_crossbg_enabled := 'N';
2500:
2501: -- Also change the flag in Type 4 pkg
2502: pqp_gb_tp_pension_extracts.g_crossbg_enabled := 'N';
2503:
2504: END IF;
2505:
2506: debug('MasterBG :'||to_char(g_master_bg_id)||
2520:
2521: debug(l_proc_name, 30);
2522:
2523: -- Store all criteria establishments from this BG
2524: FOR l_estb_details IN pqp_gb_tp_pension_extracts.csr_estb_details
2525: (p_business_group_id => l_BG_dets.business_group_id
2526: ,p_lea_estb_yn => 'Y')
2527: LOOP -- 2
2528:
2525: (p_business_group_id => l_BG_dets.business_group_id
2526: ,p_lea_estb_yn => 'Y')
2527: LOOP -- 2
2528:
2529: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
2530:
2531: debug('Establishment Details...', 40);
2532: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2533: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2528:
2529: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
2530:
2531: debug('Establishment Details...', 40);
2532: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2533: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2534: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2535: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2536: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2529: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id):= l_estb_details;
2530:
2531: debug('Establishment Details...', 40);
2532: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2533: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2534: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2535: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2536: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2537: debug('...Establishment Details', 50);
2530:
2531: debug('Establishment Details...', 40);
2532: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2533: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2534: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2535: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2536: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2537: debug('...Establishment Details', 50);
2538:
2531: debug('Establishment Details...', 40);
2532: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2533: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2534: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2535: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2536: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2537: debug('...Establishment Details', 50);
2538:
2539: END LOOP; -- 2
2532: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).location_id);
2533: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).lea_estb_yn);
2534: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_number);
2535: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_name);
2536: debug(pqp_gb_tp_pension_extracts.g_criteria_estbs(l_estb_details.location_id).estb_type);
2537: debug('...Establishment Details', 50);
2538:
2539: END LOOP; -- 2
2540: --
2577: FETCH csr_request_dets INTO l_req_dets;
2578: CLOSE csr_request_dets;
2579: */
2580:
2581: IF pqp_gb_tp_pension_extracts.g_parent_request_id = -1 THEN
2582:
2583: debug(l_proc_name, 20);
2584: UPDATE pqp_ext_cross_person_records
2585: SET processing_status = 'U'
2632: debug(p_effective_date);
2633: g_effective_date := p_effective_date;
2634:
2635: -- Call the Type 4 function to set globals in Type 4 package
2636: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2637: pqp_gb_tp_pension_extracts.set_extract_globals
2638: (p_business_group_id
2639: ,p_effective_date
2640: ,p_assignment_id
2633: g_effective_date := p_effective_date;
2634:
2635: -- Call the Type 4 function to set globals in Type 4 package
2636: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2637: pqp_gb_tp_pension_extracts.set_extract_globals
2638: (p_business_group_id
2639: ,p_effective_date
2640: ,p_assignment_id
2641: );
2638: (p_business_group_id
2639: ,p_effective_date
2640: ,p_assignment_id
2641: );
2642: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2643:
2644: -- Set the globals in this package before exiting this function
2645: -- ********* Variables ***************
2646: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2642: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2643:
2644: -- Set the globals in this package before exiting this function
2645: -- ********* Variables ***************
2646: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2647: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2643:
2644: -- Set the globals in this package before exiting this function
2645: -- ********* Variables ***************
2646: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2647: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2644: -- Set the globals in this package before exiting this function
2645: -- ********* Variables ***************
2646: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2647: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2652: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2645: -- ********* Variables ***************
2646: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2647: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2652: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2653: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2646: g_extract_type := pqp_gb_tp_pension_extracts.g_extract_type;
2647: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2652: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2653: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2654:
2647: g_extract_udt_name := pqp_gb_tp_pension_extracts.g_extract_udt_name;
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2652: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2653: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2654:
2655: /************ Collections ***********
2648: g_criteria_location_code := pqp_gb_tp_pension_extracts.g_criteria_location_code;
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2652: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2653: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2654:
2655: /************ Collections ***********
2656: Sh we duplicate or just use the one in Type 4??
2649: g_lea_number := pqp_gb_tp_pension_extracts.g_lea_number;
2650: g_crossbg_enabled := pqp_gb_tp_pension_extracts.g_crossbg_enabled;
2651: g_estb_number := pqp_gb_tp_pension_extracts.g_estb_number;
2652: g_originators_title := pqp_gb_tp_pension_extracts.g_originators_title;
2653: g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2654:
2655: /************ Collections ***********
2656: Sh we duplicate or just use the one in Type 4??
2657: Current thought is reuse from Type 4
2886:
2887: debug_enter(l_proc_name);
2888:
2889: -- Call the Type 4 function to set run dates in Type 4 package
2890: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2891: pqp_gb_tp_pension_extracts.set_run_effective_dates;
2892: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2893:
2894: -- Set the globals in this package before exiting this function
2887: debug_enter(l_proc_name);
2888:
2889: -- Call the Type 4 function to set run dates in Type 4 package
2890: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2891: pqp_gb_tp_pension_extracts.set_run_effective_dates;
2892: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2893:
2894: -- Set the globals in this package before exiting this function
2895: -- ********* Variables ****************
2888:
2889: -- Call the Type 4 function to set run dates in Type 4 package
2890: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
2891: pqp_gb_tp_pension_extracts.set_run_effective_dates;
2892: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2893:
2894: -- Set the globals in this package before exiting this function
2895: -- ********* Variables ****************
2896: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2892: pqp_gb_tp_pension_extracts.g_nested_level := 0;
2893:
2894: -- Set the globals in this package before exiting this function
2895: -- ********* Variables ****************
2896: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2897: g_next_effective_date := pqp_gb_tp_pension_extracts.g_next_effective_date;
2898: g_effective_run_date := pqp_gb_tp_pension_extracts.g_effective_run_date;
2899:
2900: -- For the sake of periodic report only
2893:
2894: -- Set the globals in this package before exiting this function
2895: -- ********* Variables ****************
2896: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2897: g_next_effective_date := pqp_gb_tp_pension_extracts.g_next_effective_date;
2898: g_effective_run_date := pqp_gb_tp_pension_extracts.g_effective_run_date;
2899:
2900: -- For the sake of periodic report only
2901: -- pension year end date is being set as the run end date.
2894: -- Set the globals in this package before exiting this function
2895: -- ********* Variables ****************
2896: g_last_effective_date := pqp_gb_tp_pension_extracts.g_last_effective_date;
2897: g_next_effective_date := pqp_gb_tp_pension_extracts.g_next_effective_date;
2898: g_effective_run_date := pqp_gb_tp_pension_extracts.g_effective_run_date;
2899:
2900: -- For the sake of periodic report only
2901: -- pension year end date is being set as the run end date.
2902: g_pension_year_end_date := g_effective_run_date;
2902: g_pension_year_end_date := g_effective_run_date;
2903:
2904: -- Changed by Raju T. on 08/05/2002 as a development bugfix
2905: -- Commented out as this is now being created differently for Type 1
2906: -- g_header_system_element := pqp_gb_tp_pension_extracts.g_header_system_element;
2907:
2908: -- Set the start date of the pension year, i.e. 01-04-YYYY
2909: SELECT TO_DATE('01-04-'||
2910: DECODE
2922: g_last_effective_date := GREATEST(g_last_effective_date
2923: ,g_pension_year_start_date
2924: );
2925:
2926: IF g_last_effective_date <> pqp_gb_tp_pension_extracts.g_last_effective_date THEN
2927:
2928: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2929:
2930: END IF;
2924: );
2925:
2926: IF g_last_effective_date <> pqp_gb_tp_pension_extracts.g_last_effective_date THEN
2927:
2928: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
2929:
2930: END IF;
2931:
2932: g_header_system_element:=
2935: fnd_date.date_to_canonical(g_effective_run_date) ||':'||
2936: fnd_date.date_to_canonical(g_next_effective_date)||':';
2937:
2938: -- Assign the newly created system element to the type4 global
2939: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
2940:
2941: debug('New Type 1 Header System Element :'||g_header_system_element);
2942: -- Changes END here
2943:
2995: g_next_effective_date := g_effective_date;
2996: g_effective_run_date := g_pension_year_end_date;
2997:
2998: -- Now set the Type 4 globals
2999: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
3000: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
3001: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
3002: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
3003:
2996: g_effective_run_date := g_pension_year_end_date;
2997:
2998: -- Now set the Type 4 globals
2999: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
3000: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
3001: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
3002: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
3003:
3004: debug('Header System Element :'||g_header_system_element);
2997:
2998: -- Now set the Type 4 globals
2999: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
3000: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
3001: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
3002: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
3003:
3004: debug('Header System Element :'||g_header_system_element);
3005:
2998: -- Now set the Type 4 globals
2999: pqp_gb_tp_pension_extracts.g_last_effective_date := g_last_effective_date;
3000: pqp_gb_tp_pension_extracts.g_next_effective_date := g_next_effective_date;
3001: pqp_gb_tp_pension_extracts.g_effective_run_date := g_effective_run_date;
3002: pqp_gb_tp_pension_extracts.g_header_system_element := g_header_system_element;
3003:
3004: debug('Header System Element :'||g_header_system_element);
3005:
3006: debug_exit(l_proc_name);
3181: -- Variable Declaration
3182: l_no_of_events NUMBER;
3183:
3184: -- Rowtype Variable Declaration
3185: l_event_group_details pqp_gb_tp_pension_extracts.csr_event_group_details%ROWTYPE;
3186: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3187: l_assignment_action_id number;
3188: l_proc_name VARCHAR2(61):=
3189: g_proc_name||'get_events';
3403: -- which we are reporting for, check the next record
3404: -- Also, if the start date is outside our date range, not look at this record.
3405: IF l_asg_details.start_date <= g_effective_run_date
3406: AND
3407: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
3408: AND -- Bugfix 3873376:SUSP Ignore suspended assignments
3409: l_asg_details.status_type <> 'SUSP_ASSIGN'
3410: AND -- Bugfix 4336613 :IGNR_TERMASG_3A Ignore terminated assignments
3411: l_asg_details.status_type <> 'TERM_ASSIGN' THEN
3436: -- Include otherwise
3437:
3438: IF NOT
3439: (-- To be excluded
3440: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3441: = '0966'
3442: OR
3443: (l_asg_details.ext_emp_cat_cd = 'P'
3444: -- Bugfix 3641851:ENH7 : Added this AND clause to INCLUDE
3443: (l_asg_details.ext_emp_cat_cd = 'P'
3444: -- Bugfix 3641851:ENH7 : Added this AND clause to INCLUDE
3445: -- part-time asgs at LEA establishment only
3446: AND
3447: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_type
3448: <> 'LEA_ESTB'
3449: )
3450: OR
3451: (l_asg_details.ext_emp_cat_cd = 'F'
3449: )
3450: OR
3451: (l_asg_details.ext_emp_cat_cd = 'F'
3452: AND
3453: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_type
3454: = 'IND_ESTB'
3455: )
3456: ) THEN
3457:
3573: l_inclusion_flag VARCHAR2(1) := 'Y';
3574: l_new_event_itr NUMBER(5);
3575:
3576: -- Rowtype Variable Declaration
3577: l_event_group_details pqp_gb_tp_pension_extracts.csr_event_group_details%ROWTYPE;
3578: l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
3579: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3580: l_asg_details csr_asg_details_up%ROWTYPE;
3581: l_prev_asg_details csr_asg_details_up%ROWTYPE;
3579: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3580: l_asg_details csr_asg_details_up%ROWTYPE;
3581: l_prev_asg_details csr_asg_details_up%ROWTYPE;
3582: l_next_asg_details csr_asg_details_up%ROWTYPE;
3583: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3584: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3585:
3586: l_temp_asg_details csr_asg_details_up%ROWTYPE;
3587: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
3580: l_asg_details csr_asg_details_up%ROWTYPE;
3581: l_prev_asg_details csr_asg_details_up%ROWTYPE;
3582: l_next_asg_details csr_asg_details_up%ROWTYPE;
3583: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3584: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
3585:
3586: l_temp_asg_details csr_asg_details_up%ROWTYPE;
3587: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
3588:
3834: debug('Event worth considering',100);
3835:
3836: -- Check if the locaiton change is a valid one to report as leaver
3837: -- The new establishment is not a criteria establishment
3838: IF NOT pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
3839:
3840: -- The assignment has had a location change such that it has become a leaver
3841: l_leaver := 'Y';
3842: l_leaver_date := LEAST((l_asg_details.start_date - 1)
3874: -- The current and new Establishment nos. are different
3875: -- This chk is to ensure that the current location
3876: -- was not corrected after doing a datetrack update
3877: (-- Bugfix 3641851:CBF4 : Added EXISTS just to avoid exception
3878: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3879: AND
3880: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3881: <>
3882: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3876: -- was not corrected after doing a datetrack update
3877: (-- Bugfix 3641851:CBF4 : Added EXISTS just to avoid exception
3878: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3879: AND
3880: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3881: <>
3882: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3883: ) THEN
3884:
3878: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
3879: AND
3880: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
3881: <>
3882: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_prev_asg_details.location_id).estb_number
3883: ) THEN
3884:
3885: debug('Location change is a new line of service event, storing', 130);
3886:
3893:
3894: -- Bugfix 3470242:BUG1 : Now using new_location_id, estb_number can
3895: -- always be sought using the location id
3896: --g_asg_events(l_new_event_itr).new_estb_number :=
3897: -- pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
3898: g_asg_events(l_new_event_itr).new_location_id := l_asg_details.location_id;
3899:
3900: debug('New Location Id :'||to_char(l_asg_details.location_id), 135);
3901:
3952: debug('Inside IF for Elected pension changes check',160);
3953: debug('Event date :'||to_char(l_proration_dates(l_itr),'DD/MM/YYYY'), 165);
3954: debug('Update Type :'||l_proration_changes(l_itr), 166);
3955:
3956: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
3957: (p_assignment_id
3958: ,l_proration_dates(l_itr)
3959: );
3960: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3956: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
3957: (p_assignment_id
3958: ,l_proration_dates(l_itr)
3959: );
3960: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3961: --
3962: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3963: AND
3964: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
3958: ,l_proration_dates(l_itr)
3959: );
3960: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
3961: --
3962: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3963: AND
3964: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
3965:
3966: -- Fetch the previous set of attributes
3963: AND
3964: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
3965:
3966: -- Fetch the previous set of attributes
3967: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
3968: --
3969: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3970: AND
3971: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
3965:
3966: -- Fetch the previous set of attributes
3967: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
3968: --
3969: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
3970: AND
3971: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
3972:
3973: debug('B4 checking if asg passes chk_has_tchr_elected_pension',170);
4001: )
4002: );
4003: debug('Leaver Date :'||to_char(l_leaver_date,'DD/MM/YYYY'), 185);
4004:
4005: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4006: EXIT; -- quit loop -- no need to search for other events
4007:
4008: END IF; -- l_inclusion_flag = 'N' THEN
4009: --
4006: EXIT; -- quit loop -- no need to search for other events
4007:
4008: END IF; -- l_inclusion_flag = 'N' THEN
4009: --
4010: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4011: --
4012: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4013: --
4014: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4008: END IF; -- l_inclusion_flag = 'N' THEN
4009: --
4010: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4011: --
4012: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4013: --
4014: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4015: --
4016: debug('Assignment attributes have had changes',190);
4010: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4011: --
4012: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4013: --
4014: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4015: --
4016: debug('Assignment attributes have had changes',190);
4017: --
4018: END IF; -- if this date <> last date to eliminate duplicates
4064:
4065: debug('Event date :'||to_char(l_proration_dates(l_itr),'DD/MM/YYYY'), 215);
4066: debug('Update Type :'||l_proration_changes(l_itr), 216);
4067:
4068: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4069: (p_assignment_id
4070: ,l_proration_dates(l_itr)
4071: );
4072: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4068: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4069: (p_assignment_id
4070: ,l_proration_dates(l_itr)
4071: );
4072: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4073: --
4074: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4075: AND
4076: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4070: ,l_proration_dates(l_itr)
4071: );
4072: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4073: --
4074: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4075: AND
4076: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4077:
4078: -- Fetch the previous set of attributes
4075: AND
4076: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4077:
4078: -- Fetch the previous set of attributes
4079: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4080: --
4081: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4082: AND
4083: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4077:
4078: -- Fetch the previous set of attributes
4079: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4080: --
4081: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4082: AND
4083: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4084:
4085: debug('This assignment HAS become a Non-Teacher.',220);
4091: )
4092: );
4093: debug('Leaver Date :'||to_char(l_leaver_date,'DD/MM/YYYY'), 225);
4094:
4095: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4096: EXIT; -- quit loop -- no need to search for other events
4097: --
4098: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4099: --
4094:
4095: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4096: EXIT; -- quit loop -- no need to search for other events
4097: --
4098: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4099: --
4100: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4101: --
4102: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4096: EXIT; -- quit loop -- no need to search for other events
4097: --
4098: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4099: --
4100: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4101: --
4102: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4103: --
4104: debug('Assignment attributes have had changes',230);
4098: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4099: --
4100: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4101: --
4102: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4103: --
4104: debug('Assignment attributes have had changes',230);
4105: --
4106: END IF; -- if this date <> last date to eliminate duplicates and ignore corrections
4152: l_no_of_events NUMBER(5);
4153: l_inclusion_flag VARCHAR2(1) := 'N';
4154:
4155: -- Rowtype Variable Declaration
4156: l_event_group_details pqp_gb_tp_pension_extracts.csr_event_group_details%ROWTYPE;
4157: l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
4158: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
4159: l_asg_details csr_asg_details_up%ROWTYPE;
4160: l_prev_asg_details csr_asg_details_up%ROWTYPE;
4157: l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
4158: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
4159: l_asg_details csr_asg_details_up%ROWTYPE;
4160: l_prev_asg_details csr_asg_details_up%ROWTYPE;
4161: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4162: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4163:
4164: l_temp_asg_details csr_asg_details_up%ROWTYPE;
4165: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
4158: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
4159: l_asg_details csr_asg_details_up%ROWTYPE;
4160: l_prev_asg_details csr_asg_details_up%ROWTYPE;
4161: l_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4162: l_last_pqp_asg_attributes pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%ROWTYPE;
4163:
4164: l_temp_asg_details csr_asg_details_up%ROWTYPE;
4165: l_temp_aat_details csr_pqp_asg_attributes_up%ROWTYPE;
4166:
4249: AND
4250: l_asg_details.status_type = 'ACTIVE_ASSIGN'
4251: -- check if the location is a valid location
4252: AND
4253: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
4254: ) THEN
4255:
4256: debug('now checking chk_has_tchr_elected_pension',42);
4257: -- BUG : 3873376
4376: -- The new establishment must be a criteria establishment
4377: -- and the previous establishment must not be a criteria establishment
4378: IF (csr_asg_details_dn%FOUND
4379: AND -- the new asg location is a criteria location
4380: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
4381: AND -- the previous was not a criteria location
4382: (NOT pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
4383: )
4384: ) THEN
4378: IF (csr_asg_details_dn%FOUND
4379: AND -- the new asg location is a criteria location
4380: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id)
4381: AND -- the previous was not a criteria location
4382: (NOT pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_prev_asg_details.location_id)
4383: )
4384: ) THEN
4385: debug('now checking chk_has_tchr_elected_pension',101);
4386: -- BUG : 3873376
4459: ( l_proration_dates(l_itr) <>
4460: l_proration_dates(l_proration_dates.PRIOR(l_itr))
4461: ) THEN
4462:
4463: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4464: (p_assignment_id
4465: ,l_proration_dates(l_itr)
4466: );
4467: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4463: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4464: (p_assignment_id
4465: ,l_proration_dates(l_itr)
4466: );
4467: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4468: --
4469: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4470: AND
4471: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
4465: ,l_proration_dates(l_itr)
4466: );
4467: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4468: --
4469: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4470: AND
4471: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
4472:
4473: -- Fetch the previous set of attributes
4470: AND
4471: NVL(l_pqp_asg_attributes.tp_elected_pension,'N') = 'Y' THEN
4472:
4473: -- Fetch the previous set of attributes
4474: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4475: --
4476: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4477: AND
4478: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
4472:
4473: -- Fetch the previous set of attributes
4474: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4475: --
4476: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4477: AND
4478: nvl(l_last_pqp_asg_attributes.tp_elected_pension,'N') = 'N' THEN
4479:
4480: debug('now checking chk_has_tchr_elected_pension',121);
4500: l_restarter_date := LEAST(l_pqp_asg_attributes.effective_start_date
4501: ,nvl(l_restarter_date,l_pqp_asg_attributes.effective_start_date)
4502: );
4503:
4504: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4505: EXIT; -- quit loop -- no need to search for other events
4506: END IF ; --l_inclusion_flag = 'Y' THEN
4507:
4508: --
4505: EXIT; -- quit loop -- no need to search for other events
4506: END IF ; --l_inclusion_flag = 'Y' THEN
4507:
4508: --
4509: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4510: --
4511: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4512: --
4513: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4507:
4508: --
4509: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4510: --
4511: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4512: --
4513: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4514: --
4515: debug('Assignment attributes have had changes',130);
4509: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4510: --
4511: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4512: --
4513: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4514: --
4515: debug('Assignment attributes have had changes',130);
4516: --
4517: END IF; -- if this date <> last date to eliminate duplicates
4564: AND
4565: (l_proration_changes(l_itr) <> 'C' -- Not a Correction
4566: ) THEN
4567:
4568: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4569: (p_assignment_id
4570: ,l_proration_dates(l_itr)
4571: );
4572: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4568: OPEN pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn
4569: (p_assignment_id
4570: ,l_proration_dates(l_itr)
4571: );
4572: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4573: --
4574: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4575: AND
4576: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4570: ,l_proration_dates(l_itr)
4571: );
4572: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_pqp_asg_attributes;
4573: --
4574: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4575: AND
4576: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4577:
4578: -- Fetch the previous set of attributes
4575: AND
4576: NVL(l_pqp_asg_attributes.tp_is_teacher,'NONT') IN ('TCHR','TTR6') THEN
4577:
4578: -- Fetch the previous set of attributes
4579: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4580: --
4581: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4582: AND
4583: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4577:
4578: -- Fetch the previous set of attributes
4579: FETCH pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn INTO l_last_pqp_asg_attributes;
4580: --
4581: IF pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4582: AND
4583: nvl(l_last_pqp_asg_attributes.tp_is_teacher,'NONT') = 'NONT' THEN
4584:
4585: debug('now checking chk_has_tchr_elected_pension',141);
4605: l_restarter_date := LEAST(l_pqp_asg_attributes.effective_start_date
4606: ,nvl(l_restarter_date,l_pqp_asg_attributes.effective_start_date)
4607: );
4608:
4609: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4610: EXIT; -- quit loop -- no need to search for other events
4611: END IF ; -- l_inclusion_flag = 'Y' THEN
4612: --
4613: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4609: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4610: EXIT; -- quit loop -- no need to search for other events
4611: END IF ; -- l_inclusion_flag = 'Y' THEN
4612: --
4613: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4614: --
4615: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4616: --
4617: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4611: END IF ; -- l_inclusion_flag = 'Y' THEN
4612: --
4613: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4614: --
4615: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4616: --
4617: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4618: --
4619: debug('Assignment attributes have had changes',150);
4613: END IF; -- 2 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4614: --
4615: END IF; -- 1 pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn%FOUND
4616: --
4617: CLOSE pqp_gb_tp_pension_extracts.csr_pqp_asg_attributes_dn;
4618: --
4619: debug('Assignment attributes have had changes',150);
4620: --
4621: END IF; -- if this date <> last date to eliminate duplicates and ignore corrections
5060:
5061: debug('Storing values to globals',80);
5062:
5063: -- 1) Type 4 global collection g_ext_asg_details
5064: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5065: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5061: debug('Storing values to globals',80);
5062:
5063: -- 1) Type 4 global collection g_ext_asg_details
5064: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5065: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5062:
5063: -- 1) Type 4 global collection g_ext_asg_details
5064: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5065: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5063: -- 1) Type 4 global collection g_ext_asg_details
5064: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5065: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5071: 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;
5064: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).person_id := l_asg_details.person_id;
5065: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5071: 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;
5072:
5065: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).assignment_id := l_asg_details.assignment_id;
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5071: 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;
5072:
5073: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5066: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).start_date := l_asg_details.start_date;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5071: 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;
5072:
5073: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5074: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5067: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).effective_end_date := l_asg_details.effective_end_date;
5068: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).creation_date := l_asg_details.creation_date;
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5071: 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;
5072:
5073: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5074: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5075:
5069: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).location_id := l_asg_details.location_id;
5070: 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;
5071: 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;
5072:
5073: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5074: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5075:
5076: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).tp_safeguarded_grade :=
5077: l_pqp_asg_attributes.tp_safeguarded_grade;
5070: 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;
5071: 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;
5072:
5073: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5074: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5075:
5076: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).tp_safeguarded_grade :=
5077: l_pqp_asg_attributes.tp_safeguarded_grade;
5078:
5072:
5073: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).estb_number :=
5074: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5075:
5076: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id).tp_safeguarded_grade :=
5077: l_pqp_asg_attributes.tp_safeguarded_grade;
5078:
5079: -- 2) Type 1 global collection g_ext_asg_details
5080: g_teach_asg_count := g_teach_asg_count +1;
5088: -- supply location and full time
5089: warn_if_supply_tchr_is_ft
5090: (p_assignment_id => l_sec_asgs.assignment_id
5091: ,p_establishment_number =>
5092: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
5093: ,p_ext_emp_cat_code => l_asg_details.ext_emp_cat_cd
5094: );
5095:
5096: -- Bugfix 3073562:GAP9b
5094: );
5095:
5096: -- Bugfix 3073562:GAP9b
5097: -- Increment the supply asg count if this is a supply assignment
5098: IF pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number = '0966' THEN
5099: g_supply_asg_count := g_supply_asg_count + 1;
5100: debug('Incrementing supply teacher count',90);
5101: END IF;
5102:
5127: (NOT g_ext_asg_details.EXISTS(p_primary_assignment_id))
5128: THEN
5129:
5130: debug('First Time and Primary not being included',110);
5131: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_primary_assignment_id) :=
5132: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id);
5133:
5134: g_ext_asg_details(p_primary_assignment_id) := g_ext_asg_details(l_sec_asgs.assignment_id);
5135: g_ext_asg_details(p_primary_assignment_id).report_asg := 'N';
5128: THEN
5129:
5130: debug('First Time and Primary not being included',110);
5131: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_primary_assignment_id) :=
5132: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_sec_asgs.assignment_id);
5133:
5134: g_ext_asg_details(p_primary_assignment_id) := g_ext_asg_details(l_sec_asgs.assignment_id);
5135: g_ext_asg_details(p_primary_assignment_id).report_asg := 'N';
5136:
5136:
5137: -- Bugfix 3470242:GAP4 : We need to store the person id of the primary asg
5138: -- as we have to update the results data for this person id
5139: -- while creating new LOS.
5140: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_primary_assignment_id).person_id := p_person_id;
5141: g_ext_asg_details(p_primary_assignment_id).person_id := p_person_id;
5142:
5143: g_ext_asg_details(p_primary_assignment_id).secondary_assignment_id :=
5144: l_sec_asgs.assignment_id;
5746: ELSE -- asg record FOUND
5747:
5748: IF l_asg_details.location_id IS NOT NULL
5749: AND
5750: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
5751:
5752:
5753: debug('Re-evaluating override asg details', 110);
5754: -- Setting the current ext_emp_cat_cd, location_id and estb_number
5755: g_ext_asg_details(g_override_ft_asg_id).ext_emp_cat_cd := 'F';
5756: debug('l_asg_details.location_id :'||to_char(l_asg_details.location_id), 111);
5757: g_ext_asg_details(g_override_ft_asg_id).location_id := l_asg_details.location_id;
5758: debug('g_ext_asg_details(g_override_ft_asg_id).estb_number :'||g_ext_asg_details(g_override_ft_asg_id).estb_number, 111);
5759: debug('Estb number in Global :'||pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 112);
5760: g_ext_asg_details(g_override_ft_asg_id).estb_number :=
5761: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5762:
5763: ELSE
5757: g_ext_asg_details(g_override_ft_asg_id).location_id := l_asg_details.location_id;
5758: debug('g_ext_asg_details(g_override_ft_asg_id).estb_number :'||g_ext_asg_details(g_override_ft_asg_id).estb_number, 111);
5759: debug('Estb number in Global :'||pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 112);
5760: g_ext_asg_details(g_override_ft_asg_id).estb_number :=
5761: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
5762:
5763: ELSE
5764: debug('WARNING: This asg might hv multiple leaver events', 113);
5765: END IF;
6378: l_row_name_source_name pay_element_type_extra_info.eei_information19%TYPE;
6379:
6380:
6381: -- Added a new varaibale to hold the element details .
6382: l_element_dtl pqp_gb_tp_pension_extracts.t_allowance_eles;
6383: l_element_type_id NUMBER;
6384: l_count NUMBER := 0;
6385: l_error NUMBER;
6386:
6405: -- Get Element Attribution for the given rate type
6406: IF p_rate_type_name IS NULL THEN
6407: -- Retention elements are defined so we need to create the element set
6408: -- using the l_tab_ret_aln_eles
6409: debug('g_tab_ret_aln_eles.COUNT: '||to_char(pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles.COUNT),20);
6410:
6411: l_element_dtl := pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles;
6412:
6413: ELSE
6407: -- Retention elements are defined so we need to create the element set
6408: -- using the l_tab_ret_aln_eles
6409: debug('g_tab_ret_aln_eles.COUNT: '||to_char(pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles.COUNT),20);
6410:
6411: l_element_dtl := pqp_gb_tp_pension_extracts.g_tab_ret_aln_eles;
6412:
6413: ELSE
6414: -- Create the element set from the rate type passed.
6415: OPEN csr_element_set (c_name => p_rate_type_name
9109: --CALC_PT_SAL_OPTIONS: BUG : 4135481
9110: -- check if calc_part_time_sal OR calc_part_time_sal_old is to be used
9111: /*
9112: IF g_calc_sal_new <> 'Y' AND
9113: (pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number <> '0966' OR
9114: g_date_work_mode <> 'Y') THEN -- switch is YES for old method
9115: */
9116: IF g_calc_sal_new <> 'Y' THEN
9117: debug(l_proc_name, 90);
9183: IF g_proration = 'Y'
9184: THEN
9185: debug('g_proration is set to Y',299);
9186: debug('g_ext_asg_details(p_assignment_id).location_id : ' || to_char(g_ext_asg_details(p_assignment_id).location_id),1212);
9187: 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) ;
9188: debug('g_date_work_mode is set to ' || g_date_work_mode,299);
9189: -- IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number <> '0966' OR g_date_work_mode <> 'Y' THEN
9190: debug(l_proc_name, 150);
9191: -- get the pre payments here. (if any)
9185: debug('g_proration is set to Y',299);
9186: debug('g_ext_asg_details(p_assignment_id).location_id : ' || to_char(g_ext_asg_details(p_assignment_id).location_id),1212);
9187: 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) ;
9188: debug('g_date_work_mode is set to ' || g_date_work_mode,299);
9189: -- IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number <> '0966' OR g_date_work_mode <> 'Y' THEN
9190: debug(l_proc_name, 150);
9191: -- get the pre payments here. (if any)
9192: -- the function returns the modified dates for the Full Pyroll Periods
9193: -- these will be used to get the Full Payroll Period payments.
9208: END IF;
9209: -- 4336613 : moved code to fetch defined balance id to global settings.
9210:
9211: l_count := 1;
9212: -- IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' and g_date_work_mode = 'Y' THEN
9213: --ELSE
9214: debug('Calling csr_get_asg_act_id_retro',302);
9215:
9216: OPEN csr_get_asg_act_id_retro
10047: IF g_supply_asg_count > 0 THEN
10048:
10049: debug (l_proc_name, 20);
10050: debug ('Establishment Number: '||
10051: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number,30
10052: );
10053:
10054: -- Check whether the establishment number = 0966
10055: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966'
10051: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number,30
10052: );
10053:
10054: -- Check whether the establishment number = 0966
10055: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966'
10056: THEN
10057: debug (l_proc_name, 40);
10058:
10059: -- Calculate the part time salary paid in the entire year
10144: ELSIF (g_ext_asg_details(p_assignment_id).ext_emp_cat_cd ='P' ) THEN
10145:
10146: debug ('Entered new Part Time logic');
10147: debug (l_proc_name, 20);
10148: debug ('Establishment Number: '||pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number,30);
10149:
10150: debug (l_proc_name, 40);
10151:
10152: -- Calculate the part time salary paid in the entire year
10962: IF (g_extract_type = 'TP2' ) THEN
10963: l_eff_start_date := pqp_gb_tp_type2_functions.g_effective_start_date;
10964: l_eff_end_date := pqp_gb_tp_type2_functions.g_effective_end_date;
10965: ELSIF (g_extract_type = 'TP4' ) THEN
10966: l_eff_start_date := pqp_gb_tp_pension_extracts.g_last_effective_date ;
10967: l_eff_end_date := pqp_gb_tp_pension_extracts.g_effective_run_date ;
10968: END IF;
10969:
10970: FOR l_multiper IN c_multiper
10963: l_eff_start_date := pqp_gb_tp_type2_functions.g_effective_start_date;
10964: l_eff_end_date := pqp_gb_tp_type2_functions.g_effective_end_date;
10965: ELSIF (g_extract_type = 'TP4' ) THEN
10966: l_eff_start_date := pqp_gb_tp_pension_extracts.g_last_effective_date ;
10967: l_eff_end_date := pqp_gb_tp_pension_extracts.g_effective_run_date ;
10968: END IF;
10969:
10970: FOR l_multiper IN c_multiper
10971: (p_person_id => l_person_id
11120: )
11121: VALUES
11122: ('X'
11123: ,ben_ext_thread.g_ext_dfn_id --ENH3
11124: ,pqp_gb_tp_pension_extracts.g_lea_number --ENH3
11125: ,p_business_group_id
11126: ,p_person_id
11127: ,p_national_identifier
11128: ,p_assignment_id
11444: -- Bugfix -- Bugfix 3671727: Performance enhancement
11445: -- If no location exists in the list of valid criteria
11446: -- establishments, then no point doing all checks
11447: -- Just warn once and skip every assignment
11448: IF pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0 THEN
11449:
11450: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11451: l_inclusion_flag := 'N';
11452:
11449:
11450: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11451: l_inclusion_flag := 'N';
11452:
11453: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11454: -- Call TP4 pkg proc to warning for no locations
11455: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11456: (p_assignment_id => p_assignment_id) ;
11457: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11451: l_inclusion_flag := 'N';
11452:
11453: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11454: -- Call TP4 pkg proc to warning for no locations
11455: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11456: (p_assignment_id => p_assignment_id) ;
11457: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11458:
11459: ELSE -- Valid locations EXIST
11453: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11454: -- Call TP4 pkg proc to warning for no locations
11455: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11456: (p_assignment_id => p_assignment_id) ;
11457: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11458:
11459: ELSE -- Valid locations EXIST
11460:
11461: -- Reset the supply assignment count
11632: debug('Storing values in globals',70);
11633: l_asg_details.leaver_date := l_leaver_date;
11634:
11635: -- 1) Type 4 global collection g_ext_asg_details
11636: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11637: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11633: l_asg_details.leaver_date := l_leaver_date;
11634:
11635: -- 1) Type 4 global collection g_ext_asg_details
11636: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11637: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11634:
11635: -- 1) Type 4 global collection g_ext_asg_details
11636: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11637: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11635: -- 1) Type 4 global collection g_ext_asg_details
11636: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11637: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11636: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
11637: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11644:
11637: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11644:
11645: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11638: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11644:
11645: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11646: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11639: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date:= l_asg_details.effective_end_date;
11640: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11644:
11645: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11646: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11647:
11641: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11644:
11645: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11646: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11647:
11648: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11649: l_pqp_asg_attributes.tp_safeguarded_grade;
11642: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
11643: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
11644:
11645: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11646: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11647:
11648: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11649: l_pqp_asg_attributes.tp_safeguarded_grade;
11650:
11644:
11645: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
11646: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
11647:
11648: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
11649: l_pqp_asg_attributes.tp_safeguarded_grade;
11650:
11651: -- 2) Type 1 global collection g_ext_asg_details
11652: g_ext_asg_details(p_assignment_id) := l_asg_details;
11660: -- supply location and full time
11661: warn_if_supply_tchr_is_ft
11662: (p_assignment_id => p_assignment_id
11663: ,p_establishment_number =>
11664: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
11665: ,p_ext_emp_cat_code => l_asg_details.ext_emp_cat_cd
11666: );
11667:
11668: -- Bugfix 3073562:GAP9b
11666: );
11667:
11668: -- Bugfix 3073562:GAP9b
11669: -- Increment the supply asg count if this is a supply assignment
11670: IF pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number = '0966' THEN
11671: g_supply_asg_count := g_supply_asg_count + 1;
11672: debug('Incrementing supply teacher count',75);
11673: END IF;
11674:
11699: debug('Primary Asg '|| to_char(p_assignment_id)||' is not terminated', 84) ;
11700: END IF; -- l_inclusion_flag = 'Y' Bug 5408932
11701: debug('g_asg_count: '|| to_char(g_asg_count), 85) ;
11702: debug('Number of TP1 teachers on periodic report :'||
11703: fnd_number.number_to_canonical(pqp_gb_tp_pension_extracts.g_ext_asg_details.COUNT));
11704:
11705: ELSE
11706: -- Bugfix 3073562:GAP6
11707: -- Continue looking for secondary asgs
11813: -- The following piece of code raises a warning if
11814: -- there exist more than one lea with the same lea Number within a BG.
11815: -- the warning is raised for the first valid assignment for a single Run.
11816: -- the flag for warning is set during the global setting through set_extract_globals.
11817: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11818: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
11819: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11820:
11821: -- The following proc raises a warning, if there is
11814: -- there exist more than one lea with the same lea Number within a BG.
11815: -- the warning is raised for the first valid assignment for a single Run.
11816: -- the flag for warning is set during the global setting through set_extract_globals.
11817: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11818: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
11819: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11820:
11821: -- The following proc raises a warning, if there is
11822: -- one FT teaching asg for the person
11815: -- the warning is raised for the first valid assignment for a single Run.
11816: -- the flag for warning is set during the global setting through set_extract_globals.
11817: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11818: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
11819: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11820:
11821: -- The following proc raises a warning, if there is
11822: -- one FT teaching asg for the person
11823: -- and there are multiple person records.
11834: );
11835: debug('g_asg_count: '|| to_char(g_asg_count), 258) ;
11836: END IF ; -- l_inclusion_flag = 'Y'
11837:
11838: END IF; -- pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0
11839:
11840: debug('l_inclusion_flag: '||l_inclusion_flag,260);
11841: debug_exit(l_proc_name);
11842:
11902:
11903:
11904: -- Bugifx 2848696 : Now using ben_ext_person.g_effective_date
11905: -- instead of p_effective_date
11906: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11907: g_reporting_mode := upper(pqp_gb_tp_pension_extracts.get_extract_udt_info
11908: ('Attribute Location Qualifier 1' -- column
11909: ,'Criteria' -- row
11910: ,ben_ext_person.g_effective_date -- p_effective_date
11903:
11904: -- Bugifx 2848696 : Now using ben_ext_person.g_effective_date
11905: -- instead of p_effective_date
11906: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11907: g_reporting_mode := upper(pqp_gb_tp_pension_extracts.get_extract_udt_info
11908: ('Attribute Location Qualifier 1' -- column
11909: ,'Criteria' -- row
11910: ,ben_ext_person.g_effective_date -- p_effective_date
11911: )
11909: ,'Criteria' -- row
11910: ,ben_ext_person.g_effective_date -- p_effective_date
11911: )
11912: );
11913: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11914:
11915: set_annual_run_dates;
11916:
11917: -- Fetch element ids from balance's
11924: -- Bugfix -- Bugfix 3671727: Performance enhancement
11925: -- If no location exists in the list of valid criteria
11926: -- establishments, then no point doing all checks
11927: -- Just warn once and skip every assignment
11928: IF pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0 THEN
11929:
11930: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11931: l_inclusion_flag := 'N';
11932:
11929:
11930: debug('Setting inclusion flag to N as no locations EXIST.', 15);
11931: l_inclusion_flag := 'N';
11932:
11933: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11934: -- Call TP4 pkg proc to warning for no locations
11935: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11936: (p_assignment_id => p_assignment_id) ;
11937: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11931: l_inclusion_flag := 'N';
11932:
11933: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11934: -- Call TP4 pkg proc to warning for no locations
11935: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11936: (p_assignment_id => p_assignment_id) ;
11937: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11938:
11939: ELSE -- Valid locations EXIST
11933: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
11934: -- Call TP4 pkg proc to warning for no locations
11935: pqp_gb_tp_pension_extracts.warn_if_no_loc_exist
11936: (p_assignment_id => p_assignment_id) ;
11937: pqp_gb_tp_pension_extracts.g_nested_level := 0;
11938:
11939: ELSE -- Valid locations EXIST
11940:
11941: debug('Reporting Mode for leavers is :'||g_reporting_mode,20);
12084:
12085: /* Replacing this with assignment statements for individual elements as
12086: there is a plan to extent the assignment cursor to include more cols.
12087:
12088: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id) := l_asg_details;
12089:
12090: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12091: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12092:
12086: there is a plan to extent the assignment cursor to include more cols.
12087:
12088: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id) := l_asg_details;
12089:
12090: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12091: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12092:
12093: -- already populated this in l_asg_details before received in this function
12094: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
12087:
12088: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id) := l_asg_details;
12089:
12090: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12091: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12092:
12093: -- already populated this in l_asg_details before received in this function
12094: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
12095:
12090: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12091: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12092:
12093: -- already populated this in l_asg_details before received in this function
12094: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
12095:
12096: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
12097: l_pqp_asg_attributes.tp_safeguarded_grade;
12098: */
12092:
12093: -- already populated this in l_asg_details before received in this function
12094: -- pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_pension_start_date;
12095:
12096: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
12097: l_pqp_asg_attributes.tp_safeguarded_grade;
12098: */
12099:
12100: -- First assign the leaver date to the asg details rowtype variable
12107: debug('Storing values in globals',50);
12108:
12109: -- 1) Type 4 global collection g_ext_asg_details
12110:
12111: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
12112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12108:
12109: -- 1) Type 4 global collection g_ext_asg_details
12110:
12111: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
12112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12109: -- 1) Type 4 global collection g_ext_asg_details
12110:
12111: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
12112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12110:
12111: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
12112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12111: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).person_id := l_asg_details.person_id;
12112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12119:
12112: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).assignment_id := l_asg_details.assignment_id;
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12119:
12120: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12113: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).start_date := l_asg_details.start_date;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12119:
12120: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12121: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12114: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).effective_end_date := l_asg_details.effective_end_date;
12115: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).creation_date := l_asg_details.creation_date;
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12119:
12120: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12121: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12122:
12116: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id := l_asg_details.location_id;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12119:
12120: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12121: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12122:
12123: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
12124: l_pqp_asg_attributes.tp_safeguarded_grade;
12117: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).asg_emp_cat_cd := l_asg_details.asg_emp_cat_cd;
12118: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).ext_emp_cat_cd := l_asg_details.ext_emp_cat_cd;
12119:
12120: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12121: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12122:
12123: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
12124: l_pqp_asg_attributes.tp_safeguarded_grade;
12125:
12119:
12120: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number :=
12121: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
12122:
12123: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade :=
12124: l_pqp_asg_attributes.tp_safeguarded_grade;
12125:
12126: -- 2) Type 1 global collection g_ext_asg_details
12127: g_teach_asg_count :=g_teach_asg_count +1;
12135: -- supply location and full time
12136: warn_if_supply_tchr_is_ft
12137: (p_assignment_id => p_assignment_id
12138: ,p_establishment_number =>
12139: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number
12140: ,p_ext_emp_cat_code => l_asg_details.ext_emp_cat_cd
12141: );
12142:
12143: -- Bugfix 3073562:GAP9b
12141: );
12142:
12143: -- Bugfix 3073562:GAP9b
12144: -- Increment the supply asg count if this is a supply assignment
12145: IF pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number = '0966' THEN
12146: g_supply_asg_count := g_supply_asg_count + 1;
12147: debug('Incrementing supply teacher count',55);
12148: END IF;
12149:
12168: ,p_must_be_a_leaver => FALSE
12169: );
12170: debug('g_asg_count: '|| to_char(g_asg_count), 65) ;
12171: debug('Number of TP1 teachers on annual report :'||
12172: fnd_number.number_to_canonical(pqp_gb_tp_pension_extracts.g_ext_asg_details.COUNT),70);
12173: --
12174: END IF; -- 2) l_inclusion_flag = 'Y' THEN
12175: --
12176: ELSE -- 1) inclusion flag is 'N'
12250: -- The following piece of code raises a warning if
12251: -- there exist more than one lea with the same lea Number within a BG.
12252: -- the warning is raised for the first valid assignment for a single Run.
12253: -- the flag for warning is set during the global setting through set_extract_globals.
12254: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
12255: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
12256: pqp_gb_tp_pension_extracts.g_nested_level := 0;
12257:
12258: -- The following proc raises a warning, if there is
12251: -- there exist more than one lea with the same lea Number within a BG.
12252: -- the warning is raised for the first valid assignment for a single Run.
12253: -- the flag for warning is set during the global setting through set_extract_globals.
12254: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
12255: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
12256: pqp_gb_tp_pension_extracts.g_nested_level := 0;
12257:
12258: -- The following proc raises a warning, if there is
12259: -- one FT teaching asg for the person
12252: -- the warning is raised for the first valid assignment for a single Run.
12253: -- the flag for warning is set during the global setting through set_extract_globals.
12254: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
12255: pqp_gb_tp_pension_extracts.warn_if_multi_lea_exist (p_assignment_id => p_assignment_id);
12256: pqp_gb_tp_pension_extracts.g_nested_level := 0;
12257:
12258: -- The following proc raises a warning, if there is
12259: -- one FT teaching asg for the person
12260: -- and there are multiple person records.
12274:
12275:
12276: END IF;
12277:
12278: END IF; -- pqp_gb_tp_pension_extracts.g_criteria_estbs.COUNT = 0
12279:
12280: debug_exit(l_proc_name);
12281:
12282: RETURN l_inclusion_flag;
14127: ELSE -- asg record FOUND
14128:
14129: IF l_asg_details.location_id IS NOT NULL
14130: AND
14131: pqp_gb_tp_pension_extracts.g_criteria_estbs.EXISTS(l_asg_details.location_id) THEN
14132:
14133: -- Setting the current ext_emp_cat_cd, location_id and estb_number
14134: debug('Re-evaluating l_assignment_id details', 24);
14135:
14146:
14147: debug('Current estb number for l_assignment_id :'||
14148: g_ext_asg_details(l_assignment_id).estb_number, 26);
14149: debug('Estb number in Global :'||
14150: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 27);
14151: g_ext_asg_details(l_assignment_id).estb_number :=
14152: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
14153:
14154: ELSE
14148: g_ext_asg_details(l_assignment_id).estb_number, 26);
14149: debug('Estb number in Global :'||
14150: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number, 27);
14151: g_ext_asg_details(l_assignment_id).estb_number :=
14152: pqp_gb_tp_pension_extracts.g_criteria_estbs(l_asg_details.location_id).estb_number;
14153:
14154: ELSE
14155: debug('WARNING: This asg might hv multiple leaver events', 27);
14156: END IF;
14194: -- If override asg has been set, we need to
14195: -- refresh estb number from this asg
14196: debug('Refreshing from override asg', 34);
14197: p_dtl_rec.val_10 :=
14198: nvl(pqp_gb_tp_pension_extracts.g_criteria_estbs
14199: (g_ext_asg_details(l_assignment_id).location_id
14200: ).estb_number
14201: ,p_dtl_rec.val_10
14202: );
14237:
14238: -- Bugfix 3470242:BUG1 : Now using new_location_id, estb_number can
14239: -- always be sought using the location id
14240: -- p_dtl_rec.val_10 := nvl(p_event_details.new_estb_number, p_dtl_rec.val_10);
14241: p_dtl_rec.val_10 := nvl(pqp_gb_tp_pension_extracts.g_criteria_estbs
14242: (p_event_details.new_location_id).estb_number
14243: , p_dtl_rec.val_10
14244: );
14245:
14248: -- We now refresh unconditionally if none of the
14249: -- above conditions apply to fix the rehire problem
14250: debug('Refreshing from l_assignment_id', 40);
14251: p_dtl_rec.val_10
14252: := nvl(pqp_gb_tp_pension_extracts.g_criteria_estbs
14253: (g_ext_asg_details(l_assignment_id).location_id
14254: ).estb_number
14255: ,p_dtl_rec.val_10
14256: );
14299: CLOSE csr_pqp_asg_attributes_up;
14300:
14301: IF l_pqp_asg_attributes_up.tp_safeguarded_grade IS NOT NULL THEN
14302:
14303: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14304: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14305: g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14306: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14307:
14307:
14308: debug(l_proc_name, 80);
14309: IF l_report_primary_asg = 'N' THEN
14310: -- We need to keep the global record for primary asg updated as well
14311: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).tp_safeguarded_grade
14312: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14313: g_ext_asg_details(p_assignment_id).tp_safeguarded_grade
14314: := l_pqp_asg_attributes_up.tp_safeguarded_grade;
14315:
14324:
14325: debug(l_proc_name, 100);
14326:
14327: l_temp_date :=
14328: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date;
14329:
14330: -- Bugfix 3880543:GRD : We need to set the date in global
14331: -- collection for primary row as well, take a bakup
14332: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14330: -- Bugfix 3880543:GRD : We need to set the date in global
14331: -- collection for primary row as well, take a bakup
14332: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14333: l_temp_sfgrade :=
14334: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14335:
14336: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14337: := to_date(p_dtl_rec.val_13,'DDMMRR');
14338:
14332: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14333: l_temp_sfgrade :=
14334: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14335:
14336: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14337: := to_date(p_dtl_rec.val_13,'DDMMRR');
14338:
14339: -- Bugfix 3880543:GRD : We need to set the date in global
14340: -- collection for primary row as well
14339: -- Bugfix 3880543:GRD : We need to set the date in global
14340: -- collection for primary row as well
14341: g_ext_asg_details(p_assignment_id).teacher_start_date := to_date(p_dtl_rec.val_13,'DDMMRR');
14342:
14343: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14344: := NULL;
14345:
14346: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14347: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14342:
14343: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14344: := NULL;
14345:
14346: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14347: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14348: (p_assignment_id => p_assignment_id
14349: );
14350: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14343: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade
14344: := NULL;
14345:
14346: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14347: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14348: (p_assignment_id => p_assignment_id
14349: );
14350: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14351:
14346: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14347: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14348: (p_assignment_id => p_assignment_id
14349: );
14350: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14351:
14352: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date:=
14353: l_temp_date;
14354: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade:=
14348: (p_assignment_id => p_assignment_id
14349: );
14350: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14351:
14352: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date:=
14353: l_temp_date;
14354: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade:=
14355: l_temp_sfgrade;
14356:
14350: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14351:
14352: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date:=
14353: l_temp_date;
14354: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade:=
14355: l_temp_sfgrade;
14356:
14357: -- Bugfix 3880543:GRD : We need to set the date in global
14358: -- collection for primary row as well, now restoring the date
14460: -- Get the new grade value
14461: -- We need to fool the type 4 function so manipulate the global variables temporarily
14462:
14463: -- Make a copy of the original values
14464: l_temp_date := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date;
14465: -- Bugfix 3880543:GRD : We need to set the date in global
14466: -- collection for primary row as well, take a bakup
14467: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14468: l_temp_sfgrade := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14464: l_temp_date := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date;
14465: -- Bugfix 3880543:GRD : We need to set the date in global
14466: -- collection for primary row as well, take a bakup
14467: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14468: l_temp_sfgrade := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14469:
14470: -- Assign the effective date we need and make the sfgrade NULL
14471: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14472: := to_date(p_dtl_rec.val_13,'DDMMRR');
14467: l_temp_date_primary := g_ext_asg_details(p_assignment_id).teacher_start_date;
14468: l_temp_sfgrade := pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade;
14469:
14470: -- Assign the effective date we need and make the sfgrade NULL
14471: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date
14472: := to_date(p_dtl_rec.val_13,'DDMMRR');
14473:
14474: -- Bugfix 3880543:GRD : We need to set the date in global
14475: -- collection for primary row as well
14473:
14474: -- Bugfix 3880543:GRD : We need to set the date in global
14475: -- collection for primary row as well
14476: g_ext_asg_details(p_assignment_id).teacher_start_date := to_date(p_dtl_rec.val_13,'DDMMRR');
14477: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade := NULL;
14478:
14479: -- Bugfix 3470242:BUG1 : Location change has happened so we need to
14480: -- replace the location_id in tp4 global collection.
14481: -- Currently not replacing in tp1 collection as its not
14484: ,'PQP_GB_TP_ASG_LOCATION'
14485: ) > 0 THEN
14486:
14487: debug(l_proc_name, 130);
14488: -- l_temp_location_id := pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id;
14489: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id :=
14490: p_event_details.new_location_id;
14491: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14492: p_dtl_rec.val_10 <> '0966' THEN
14485: ) > 0 THEN
14486:
14487: debug(l_proc_name, 130);
14488: -- l_temp_location_id := pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id;
14489: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id :=
14490: p_event_details.new_location_id;
14491: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14492: p_dtl_rec.val_10 <> '0966' THEN
14493: g_supp_teacher := 'Y';
14487: debug(l_proc_name, 130);
14488: -- l_temp_location_id := pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id;
14489: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).location_id :=
14490: p_event_details.new_location_id;
14491: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14492: p_dtl_rec.val_10 <> '0966' THEN
14493: g_supp_teacher := 'Y';
14494: END IF;
14495: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number := p_dtl_rec.val_10;
14491: IF pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number = '0966' AND
14492: p_dtl_rec.val_10 <> '0966' THEN
14493: g_supp_teacher := 'Y';
14494: END IF;
14495: pqp_gb_tp_pension_extracts.g_ext_asg_details(p_assignment_id).estb_number := p_dtl_rec.val_10;
14496:
14497:
14498: END IF;
14499:
14496:
14497:
14498: END IF;
14499:
14500: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14501: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14502: (p_assignment_id => p_assignment_id
14503: );
14504: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14497:
14498: END IF;
14499:
14500: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14501: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14502: (p_assignment_id => p_assignment_id
14503: );
14504: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14505:
14500: pqp_gb_tp_pension_extracts.g_nested_level := g_nested_level;
14501: l_new_sfgrade := pqp_gb_tp_pension_extracts.get_tp4_salary_scale
14502: (p_assignment_id => p_assignment_id
14503: );
14504: pqp_gb_tp_pension_extracts.g_nested_level := 0;
14505:
14506: IF l_new_sfgrade = 'INVALID' THEN
14507:
14508: l_error := pqp_gb_tp_extract_functions.raise_extract_warning
14534:
14535: END IF;
14536:
14537: -- Assign back the original values to the global variables
14538: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date := l_temp_date;
14539: -- Bugfix 3880543:GRD : We need to set the date in global
14540: -- collection for primary row as well, now restoring the date
14541: g_ext_asg_details(p_assignment_id).teacher_start_date := l_temp_date_primary;
14542: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade := l_temp_sfgrade;
14538: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).start_date := l_temp_date;
14539: -- Bugfix 3880543:GRD : We need to set the date in global
14540: -- collection for primary row as well, now restoring the date
14541: g_ext_asg_details(p_assignment_id).teacher_start_date := l_temp_date_primary;
14542: pqp_gb_tp_pension_extracts.g_ext_asg_details(l_assignment_id).tp_safeguarded_grade := l_temp_sfgrade;
14543:
14544: END IF; -- p_rec_type = 'NEW' for Salary Scale
14545: --
14546: debug('val_11 :'||p_dtl_rec.val_11, 140);
17492:
17493:
17494:
17495: -- Get UDT ID
17496: l_udt_id := pqp_gb_tp_pension_extracts.get_udt_id
17497: (p_udt_name => l_udt_name);
17498:
17499: -- Get the user rows information for this UDT
17500: --
17541: END IF;
17542:
17543: IF upper(l_user_value) in ('E','ELEMENT') THEN
17544:
17545: l_element_type_id := pqp_gb_tp_pension_extracts.get_allow_ele_info
17546: (p_assignment_id => p_assignment_id
17547: ,p_effective_date => p_effective_date
17548: ,p_table_name => l_udt_name
17549: ,p_row_name => l_user_row_name
17563: ELSIF upper(l_user_value) in ('R','RATE TYPE') THEN-- element type id is null
17564: -- Check for rate type
17565: debug(l_proc_name, 50);
17566:
17567: l_tab_mng_aln_eles := pqp_gb_tp_pension_extracts.get_allow_code_rt_ele_info
17568: (p_assignment_id => p_assignment_id
17569: ,p_effective_date => p_effective_date
17570: ,p_table_name => l_udt_name
17571: ,p_row_name => l_user_row_name
17624: END IF;
17625:
17626: IF upper(l_user_value) in ('E','ELEMENT') THEN
17627:
17628: l_element_type_id := pqp_gb_tp_pension_extracts.get_allow_ele_info
17629: (p_assignment_id => p_assignment_id
17630: ,p_effective_date => p_effective_date
17631: ,p_table_name => l_udt_name
17632: ,p_row_name => l_user_row_name
17646: ELSIF upper(l_user_value) in ('R','RATE TYPE') THEN-- element type id is null
17647: -- Check for rate type
17648: debug(l_proc_name, 50);
17649:
17650: l_tab_mng_aln_eles := pqp_gb_tp_pension_extracts.get_allow_code_rt_ele_info
17651: (p_assignment_id => p_assignment_id
17652: ,p_effective_date => p_effective_date
17653: ,p_table_name => l_udt_name
17654: ,p_row_name => l_user_row_name