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