[Home] [Help]
PACKAGE: APPS.PQP_GB_PSI_SERVICE_HISTORY
Source
1 PACKAGE pqp_gb_psi_service_history
2 -- /* $Header: pqpgbpsiser.pkh 120.2.12010000.1 2008/07/28 11:17:37 appldev ship $ */
3 AUTHID CURRENT_USER AS
4 --
5 -- Debug Variables.
6 --
7 g_proc_name VARCHAR2(61) := 'pqp_gb_psi_service_history.';
8 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
9 g_debug BOOLEAN := hr_utility.debug_enabled;
10 g_business_group_id NUMBER;
11 g_effective_date DATE;
12 g_extract_type VARCHAR2(100);
13 g_cutover_date DATE;
14 g_ext_dfn_id NUMBER;
15 g_paypoint VARCHAR2(10);
16 g_start_reason pqp_configuration_values.pcv_information1%TYPE;
17 g_scheme_category pqp_configuration_values.pcv_information1%TYPE;
18 g_scheme_status pqp_configuration_values.pcv_information1%TYPE;
19 g_ser_start_date DATE;
20 g_opt_in VARCHAR2(10);
21 g_opt_out VARCHAR2(10);
22 g_active_asg_sts_id NUMBER;
23 g_terminate_asg_sts_id NUMBER;
24 g_event_counter NUMBER;
25 g_assignment_id NUMBER;
26 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
27 g_person_id NUMBER;
28 g_min_effective_date DATE;
29 g_min_eff_date_exists VARCHAR2(10);
30 g_effective_start_date DATE;
31 g_effective_end_date DATE;
32
33 -- Leaving reason variable
34 g_leaving_reason pqp_configuration_values.pcv_information1%TYPE;
35
36 -- TYPE r_config_values IS RECORD (
37 -- pcv_information1 pqp_configuration_values.pcv_information1%TYPE,
38 -- pcv_information2 pqp_configuration_values.pcv_information2%TYPE,
39 -- pcv_information3 pqp_configuration_values.pcv_information3%TYPE,
40 -- pcv_information4 pqp_configuration_values.pcv_information4%TYPE,
41 -- pcv_information5 pqp_configuration_values.pcv_information5%TYPE,
42 -- pcv_information6 pqp_configuration_values.pcv_information6%TYPE,
43 -- pcv_information7 pqp_configuration_values.pcv_information7%TYPE,
44 -- pcv_information8 pqp_configuration_values.pcv_information8%TYPE,
45 -- pcv_information9 pqp_configuration_values.pcv_information9%TYPE,
46 -- pcv_information10 pqp_configuration_values.pcv_information10%TYPE,
47 -- pcv_information11 pqp_configuration_values.pcv_information11%TYPE,
48 -- pcv_information12 pqp_configuration_values.pcv_information12%TYPE,
49 -- pcv_information13 pqp_configuration_values.pcv_information13%TYPE,
50 -- pcv_information14 pqp_configuration_values.pcv_information14%TYPE,
51 -- pcv_information15 pqp_configuration_values.pcv_information15%TYPE,
52 -- pcv_information16 pqp_configuration_values.pcv_information16%TYPE,
53 -- pcv_information17 pqp_configuration_values.pcv_information17%TYPE,
54 -- pcv_information18 pqp_configuration_values.pcv_information18%TYPE,
55 -- pcv_information19 pqp_configuration_values.pcv_information19%TYPE,
56 -- pcv_information20 pqp_configuration_values.pcv_information20%TYPE
57 -- );
58
59 TYPE r_element_details IS RECORD(
60 element_type_id NUMBER
61 ,input_value_name pay_input_values_f.NAME%TYPE
62 ,input_value_id NUMBER
63 );
64
65 TYPE r_ele_ent_details IS RECORD(
66 element_entry_id NUMBER
67 ,effective_start_date DATE
68 ,effective_end_date DATE
69 ,element_type_id NUMBER
70 );
71
72 TYPE r_asg_details IS RECORD(
73 person_id NUMBER
74 ,effective_start_date DATE
75 ,effective_end_date DATE
76 ,assignment_number per_all_assignments_f.assignment_number%TYPE
77 ,primary_flag per_all_assignments_f.primary_flag%TYPE
78 ,normal_hours per_all_assignments_f.normal_hours%TYPE
79 ,assignment_status_type_id NUMBER
80 ,employment_category per_all_assignments_f.employment_category%TYPE
81 );
82
83 TYPE r_lookup_code IS RECORD(
84 lookup_code hr_lookups.lookup_code%TYPE
85 ,meaning hr_lookups.meaning%TYPE
86 );
87
88 -- cursor to fetch dated table info
89 CURSOR csr_get_dated_table_info(c_table_name VARCHAR2)
90 IS
91 SELECT dated_table_id, table_name, surrogate_key_name
92 FROM pay_dated_tables
93 WHERE table_name = c_table_name;
94
95 TYPE t_dated_table IS TABLE OF csr_get_dated_table_info%ROWTYPE
96 INDEX BY BINARY_INTEGER;
97
98 -- Cursor to get event group info
99 CURSOR csr_get_event_group_info(c_event_group VARCHAR2)
100 IS
101 SELECT event_group_id, event_group_name, event_group_type
102 FROM pay_event_groups
103 WHERE event_group_name = c_event_group
104 AND (
105 (business_group_id = g_business_group_id)
106 OR (
107 business_group_id IS NULL
108 AND (
109 legislation_code IS NULL
110 OR legislation_code = g_legislation_code
111 )
112 )
113 );
114
115 TYPE t_event_group IS TABLE OF csr_get_event_group_info%ROWTYPE
116 INDEX BY BINARY_INTEGER;
117
118 -- TYPE t_config_values IS TABLE OF r_config_values
119 -- INDEX BY BINARY_INTEGER;
120
121 TYPE t_number IS TABLE OF NUMBER
122 INDEX BY BINARY_INTEGER;
123
124 TYPE t_element_details IS TABLE OF r_element_details
125 INDEX BY BINARY_INTEGER;
126
127 TYPE t_varchar2 IS TABLE OF VARCHAR2(150)
128 INDEX BY BINARY_INTEGER;
129
130 TYPE t_lookups IS TABLE OF r_lookup_code
131 INDEX BY BINARY_INTEGER;
132
133 g_tab_event_map_cv pqp_utilities.t_config_values;
134 g_tab_pen_sch_map_cv pqp_utilities.t_config_values;
135 g_tab_emp_typ_map_cv pqp_utilities.t_config_values;
136 g_tab_abs_types t_number;
137 g_tab_asg_status t_number;
138 g_tab_pen_ele_ids t_element_details;
139 g_tab_prs_dfn_cv pqp_utilities.t_config_values;
140 g_tab_event_desc_lov t_lookups;
141 g_person_dtl per_all_people_f%ROWTYPE;
142 g_assignment_dtl per_all_assignments_f%ROWTYPE;
143 g_tab_dated_table t_dated_table;
144 g_tab_pay_proc_evnts ben_ext_person.t_detailed_output_table;
145 g_tab_event_group t_event_group;
146 g_prev_pay_proc_evnts ben_ext_person.t_detailed_output_tab_rec;
147 g_tab_lvrsn_map_cv pqp_utilities.t_config_values;
148
149 -- Cursor to fetch sickness pay transition from
150 -- summary tables
151 CURSOR csr_chk_pay_trans(
152 c_assignment_id NUMBER
153 ,c_effective_date DATE
154 ,c_absence_attendance_id NUMBER
155 )
156 IS
157 SELECT glds.gap_absence_plan_id, glds.gap_level, glds.date_start
158 ,glds.date_end
159 FROM pqp_gap_absence_plans gap, pqp_gap_duration_summary glds
160 WHERE glds.gap_absence_plan_id = gap.gap_absence_plan_id
161 AND glds.assignment_id = c_assignment_id
162 AND glds.summary_type = 'PAY'
163 AND gap.absence_attendance_id = c_absence_attendance_id
164 AND (
165 (c_effective_date BETWEEN glds.date_start AND glds.date_end)
166 OR glds.date_start < c_effective_date
167 )
168 ORDER BY glds.date_start DESC;
169
170 -- cursor to fetch the asg status type id details
171 CURSOR csr_get_asg_sts_dtls(c_per_system_status VARCHAR2)
172 IS
173 SELECT assignment_status_type_id, default_flag, active_flag, primary_flag
174 ,user_status, pay_system_status, per_system_status
175 FROM per_assignment_status_types
176 WHERE per_system_status = c_per_system_status
177 AND (
178 (business_group_id = g_business_group_id)
179 OR (
180 business_group_id IS NULL
181 AND (
182 legislation_code IS NULL
183 OR legislation_code = g_legislation_code
184 )
185 )
186 );
187
188 -- Debug
189 PROCEDURE DEBUG(
190 p_trace_message IN VARCHAR2
191 ,p_trace_location IN NUMBER DEFAULT NULL
192 );
193
194 -- Debug_Enter
195 PROCEDURE debug_enter(
196 p_proc_name IN VARCHAR2
197 ,p_trace_on IN VARCHAR2 DEFAULT NULL
198 );
199
200 -- Debug_Exit
201 PROCEDURE debug_exit(
202 p_proc_name IN VARCHAR2
203 ,p_trace_off IN VARCHAR2 DEFAULT NULL
204 );
205
206 -- Debug Others
207 PROCEDURE debug_others(
208 p_proc_name IN VARCHAR2
209 ,p_proc_step IN NUMBER DEFAULT NULL
210 );
211
212 -- Service History Criteria
213 FUNCTION chk_service_history_criteria(
214 p_business_group_id IN NUMBER
215 ,p_effective_date IN DATE
216 ,p_assignment_id IN NUMBER
217 )
218 RETURN VARCHAR2;
219
220 -- Service History Data
221 FUNCTION get_service_history_data(
222 p_business_group_id IN NUMBER
223 ,p_effective_date IN DATE
224 ,p_assignment_id IN NUMBER
225 ,p_rule_parameter IN VARCHAR2
226 )
227 RETURN VARCHAR2;
228
229 -- Service History Post Process
230 FUNCTION service_history_post_process(p_ext_rslt_id IN NUMBER)
231 RETURN VARCHAR2;
232 END pqp_gb_psi_service_history;