[Home] [Help]
Skip to content
PACKAGE: APPS.PQP_GB_PSI_EARNINGS_HISTORY
Source
1 PACKAGE PQP_GB_PSI_EARNINGS_HISTORY
2 -- /* $Header: pqpgbpsiern.pkh 120.12.12020000.2 2012/07/05 12:10:54 amnaraya ship $ */
3 AS
4 --
5 -- Debug Variables.
6 --
7 g_proc_name VARCHAR2(61) := 'pqp_gb_psi_earnings_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_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
17 g_assignment_id NUMBER;
18 g_ni_ele_type_id NUMBER;
19 g_ni_category_iv_id NUMBER;
20 g_ni_pension_iv_id NUMBER;
21 g_ni_euel_bal_type_id NUMBER;
22 g_ni_euel_ptd_bal_id NUMBER;
23 g_ni_eet_bal_type_id NUMBER;
24 g_ni_eet_ptd_bal_id NUMBER;
25 --Bug 7312374: Added globals for NI UAP
26 g_ni_euap_bal_type_id NUMBER;
27 g_ni_euap_ptd_bal_id NUMBER;
28
29 -- For bug 7297812
30 g_asst_action_id NUMBER;
31 g_prev_asst_action_id NUMBER;
32 g_prev_assg_id NUMBER := -1;
33
34 -- For Bug 8425023
35
36 g_check_balance VARCHAR2(10) := 'Y';
37
38
39 -- Commenting the below variables as they are now replaced by g_tot_ayr_fb_cont_bal_id and g_tot_ayr_fb_ptd_bal_id
40 /* g_tot_byb_cont_bal_id NUMBER;
41 g_tot_byb_ptd_bal_id NUMBER; */
42
43 g_tot_ayr_cont_bal_id NUMBER;
44 g_tot_ayr_ptd_bal_id NUMBER;
45 -- For 115.9
46 g_tot_ayr_ytd_bal_id NUMBER;
47
48 g_tot_ayr_fb_cont_bal_id NUMBER;
49 g_tot_ayr_fb_ptd_bal_id NUMBER;
50 -- For 115.9
51 g_tot_ayr_fb_ytd_bal_id NUMBER;
52
53 /* Begin for Nuvos */
54
55 g_tot_apavc_cont_bal_id NUMBER;
56 g_tot_apavc_ptd_bal_id NUMBER;
57 -- For 115.9
58 g_tot_apavc_ytd_bal_id NUMBER;
59
60 g_tot_apavcm_cont_bal_id NUMBER;
61 g_tot_apavcm_ptd_bal_id NUMBER;
62 -- For 115.9
63 g_tot_apavcm_ytd_bal_id NUMBER;
64
65 /* END for Nuvos */
66
67 g_effective_start_date DATE;
68 g_effective_end_date DATE;
69 g_procptd_dimension_id NUMBER;
70 -- For 115.9
71 g_penytd_dimension_id NUMBER;
72 g_tdptd_dimension_id NUMBER;
73 g_ayfwd_bal_conts NUMBER;
74 g_ni_e_cat_exists VARCHAR2(10);
75 g_member VARCHAR2(10);
76
77 g_ayfb_bal_conts NUMBER;
78
79 --For Bug 5941475
80 g_ern_term_exclude_flag VARCHAR2(10) := 'Y';
81
82 -- TYPE r_config_values IS RECORD (
83 -- pcv_information1 pqp_configuration_values.pcv_information1%TYPE,
84 -- pcv_information2 pqp_configuration_values.pcv_information2%TYPE,
85 -- pcv_information3 pqp_configuration_values.pcv_information3%TYPE,
86 -- pcv_information4 pqp_configuration_values.pcv_information4%TYPE,
87 -- pcv_information5 pqp_configuration_values.pcv_information5%TYPE,
88 -- pcv_information6 pqp_configuration_values.pcv_information6%TYPE,
89 -- pcv_information7 pqp_configuration_values.pcv_information7%TYPE,
90 -- pcv_information8 pqp_configuration_values.pcv_information8%TYPE,
91 -- pcv_information9 pqp_configuration_values.pcv_information9%TYPE,
92 -- pcv_information10 pqp_configuration_values.pcv_information10%TYPE,
93 -- pcv_information11 pqp_configuration_values.pcv_information11%TYPE,
94 -- pcv_information12 pqp_configuration_values.pcv_information12%TYPE,
95 -- pcv_information13 pqp_configuration_values.pcv_information13%TYPE,
96 -- pcv_information14 pqp_configuration_values.pcv_information14%TYPE,
97 -- pcv_information15 pqp_configuration_values.pcv_information15%TYPE,
98 -- pcv_information16 pqp_configuration_values.pcv_information16%TYPE,
99 -- pcv_information17 pqp_configuration_values.pcv_information17%TYPE,
100 -- pcv_information18 pqp_configuration_values.pcv_information18%TYPE,
101 -- pcv_information19 pqp_configuration_values.pcv_information19%TYPE,
102 -- pcv_information20 pqp_configuration_values.pcv_information20%TYPE
103 -- );
104
105 TYPE r_element_details IS RECORD(
106 element_type_id NUMBER
107 ,element_name pay_element_types_f.element_name%TYPE
108 ,input_value_name pay_input_values_f.NAME%TYPE
109 ,input_value_id NUMBER
110 );
111
112 TYPE r_ele_ent_details IS RECORD(
113 element_entry_id NUMBER
114 ,effective_start_date DATE
115 ,effective_end_date DATE
116 ,element_type_id NUMBER
117 );
118
119 TYPE r_asg_details IS RECORD(
120 person_id NUMBER
121 ,effective_start_date DATE
122 ,effective_end_date DATE
123 ,assignment_number per_all_assignments_f.assignment_number%TYPE
124 ,primary_flag per_all_assignments_f.primary_flag%TYPE
125 ,normal_hours per_all_assignments_f.normal_hours%TYPE
126 ,assignment_status_type_id NUMBER
127 ,employment_category per_all_assignments_f.employment_category%TYPE
128 );
129
130 TYPE r_lookup_code IS RECORD(
131 lookup_code hr_lookups.lookup_code%TYPE
132 ,meaning hr_lookups.meaning%TYPE
133 );
134
135 TYPE r_pen_bal_dtls IS RECORD(
136 element_type_id NUMBER
137 ,ees_balance_name pay_balance_types.balance_name%TYPE
138 ,ees_bal_type_id NUMBER
139 ,ees_ptd_bal_id NUMBER
140 -- For 115.9
141 ,ees_ytd_bal_id NUMBER
142 ,ers_balance_name pay_balance_types.balance_name%TYPE
143 ,ers_bal_type_id NUMBER
144 ,ers_ptd_bal_id NUMBER
145 -- For 115.9
146 ,ers_ytd_bal_id NUMBER
147 -- Commenting the below variables as they are not used
148 /* ,add_balance_name pay_balance_types.balance_name%TYPE
149 ,add_bal_type_id NUMBER
150 ,add_ptd_bal_id NUMBER
151 ,ayr_balance_name pay_balance_types.balance_name%TYPE
152 ,ayr_bal_type_id NUMBER
153 ,ayr_ptd_bal_id NUMBER
154 ,fwd_balance_name pay_balance_types.balance_name%TYPE
155 ,fwd_bal_type_id NUMBER
156 ,fwd_ptd_bal_id NUMBER */
157 ,ayfwd_balance_name pay_balance_types.balance_name%TYPE
158 ,ayfwd_bal_type_id NUMBER
159 ,ayfwd_ptd_bal_id NUMBER
160 -- For 115.9
161 ,ayfwd_ytd_bal_id NUMBER
162 --For Bug 6082532 (Added Years Family Benefit)
163 /* ,ayfb_balance_name pay_balance_types.balance_name%TYPE
164 ,ayfb_bal_type_id NUMBER
165 ,ayfb_ptd_bal_id NUMBER */
166 --For Nuvos
167 ,nuvos_sa_balance_name pay_balance_types.balance_name%TYPE
168 ,nuvos_sa_bal_type_id NUMBER
169 ,nuvos_sa_ptd_bal_id NUMBER
170 -- For 115.9
171 ,nuvos_sa_ytd_bal_id NUMBER
172
173 -- For Classic Extra Contributions
174 ,extra_contrib_balance_name pay_balance_types.balance_name%TYPE
175 ,extra_contrib_bal_type_id NUMBER
176 ,extra_contrib_ptd_bal_id NUMBER
177 ,extra_contrib_ytd_bal_id NUMBER
178
179 );
180
181 TYPE r_ele_bal_dtls IS RECORD(
182 balance_name pay_balance_types.balance_name%TYPE
183 ,balance_type_id NUMBER
184 ,defined_balance_id NUMBER
185 ,pen_defined_balance_id NUMBER
186 );
187
188 -- TYPE t_config_values IS TABLE OF r_config_values
189 -- INDEX BY BINARY_INTEGER;
190
191 TYPE t_number IS TABLE OF NUMBER
192 INDEX BY BINARY_INTEGER;
193
194 TYPE t_element_details IS TABLE OF r_element_details
195 INDEX BY BINARY_INTEGER;
196
197 TYPE t_varchar2 IS TABLE OF VARCHAR2(150)
198 INDEX BY BINARY_INTEGER;
199
200 TYPE t_lookups IS TABLE OF r_lookup_code
201 INDEX BY BINARY_INTEGER;
202
203 TYPE t_eei_info IS TABLE OF pay_element_type_extra_info%ROWTYPE
204 INDEX BY BINARY_INTEGER;
205
206 TYPE t_ele_bal_dtls IS TABLE OF r_ele_bal_dtls
207 INDEX BY BINARY_INTEGER;
208
209 TYPE t_pen_bal_dtls IS TABLE OF r_pen_bal_dtls
210 INDEX BY BINARY_INTEGER;
211
212 g_tab_pen_sch_map_cv pqp_utilities.t_config_values;
213 g_tab_pen_ele_ids t_element_details;
214 g_tab_prs_dfn_cv pqp_utilities.t_config_values;
215 g_person_dtl per_all_people_f%ROWTYPE;
216 g_assignment_dtl per_all_assignments_f%ROWTYPE;
217 g_tab_eei_info t_eei_info;
218 g_tab_clas_pen_bal_dtls t_pen_bal_dtls;
219 g_tab_clap_pen_bal_dtls t_pen_bal_dtls;
220 g_tab_prem_pen_bal_dtls t_pen_bal_dtls;
221 g_tab_part_pen_bal_dtls t_pen_bal_dtls;
222 g_tab_avc_pen_bal_dtls t_ele_bal_dtls;
223 g_ni_ele_ent_details r_ele_ent_details;
224 g_tab_ni_cont_out_bals t_varchar2;
225
226 -- For Nuvos
227 g_tab_nuvos_pen_bal_dtls t_pen_bal_dtls;
228
229 -- Debug
230 PROCEDURE DEBUG(
231 p_trace_message IN VARCHAR2
232 ,p_trace_location IN NUMBER DEFAULT NULL
233 );
234
235 -- Debug_Enter
236 PROCEDURE debug_enter(
237 p_proc_name IN VARCHAR2
238 ,p_trace_on IN VARCHAR2 DEFAULT NULL
239 );
240
241 -- Debug_Exit
242 PROCEDURE debug_exit(
243 p_proc_name IN VARCHAR2
244 ,p_trace_off IN VARCHAR2 DEFAULT NULL
245 );
246
247 -- Debug Others
248 PROCEDURE debug_others(
249 p_proc_name IN VARCHAR2
250 ,p_proc_step IN NUMBER DEFAULT NULL
251 );
252
253 -- Earnings History Criteria
254 FUNCTION chk_earnings_history_criteria(
255 p_business_group_id IN NUMBER
256 ,p_effective_date IN DATE
257 ,p_assignment_id IN NUMBER
258 )
259 RETURN VARCHAR2;
260
261 -- Earnings History Data
262 FUNCTION get_earnings_history_data(
263 p_business_group_id IN NUMBER
264 ,p_effective_date IN DATE
265 ,p_assignment_id IN NUMBER
266 ,p_rule_parameter IN VARCHAR2
267 )
268 RETURN VARCHAR2;
269
270 -- Earnings History Post Process
271 FUNCTION earnings_history_post_process(p_ext_rslt_id IN NUMBER)
272 RETURN VARCHAR2;
273 END pqp_gb_psi_earnings_history;