1 PACKAGE PQP_GB_PSI_ALLOWANCE_HISTORY AUTHID CURRENT_USER AS
2 -- /* $Header: pqpgbpsiall.pkh 120.0.12010000.3 2008/08/05 14:06:26 ubhat ship $ */
3
4 --
5 -- Debug Variables.
6 --
7
8 g_proc_name VARCHAR2(61):= 'PQP_GB_PSI_ALLOWANCE_HISTORY.';
9
10 g_debug BOOLEAN := hr_utility.debug_enabled;
11 c_application_id CONSTANT NUMBER := 8303;
12 c_highest_date CONSTANT DATE := hr_api.g_eot;
13 g_person_id NUMBER := NULL;
14 g_business_group_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
15 g_assignment_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
16 g_person_dtl per_all_people_f%rowtype;
17 g_assignment_dtl per_all_assignments_f%rowtype;
18 g_altkey VARCHAR2(12):= NULL;
19 g_current_run varchar2(20) := NULL;
20 g_current_layout varchar2(20) := NULL;
21 g_notional_pay NUMBER; -- used while calculating actual pay
22 g_effective_date DATE;-- for cutover run this will be the cutover date
23 g_extract_type VARCHAR2(100);
24 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
25
26 g_notional_rate NUMBER;
27 g_allowance_actual_pay NUMBER;
28 g_allowance_code VARCHAR2(10);
29
30 -- for include_events
31 g_pay_proc_evt_tab ben_ext_person.t_detailed_output_table;
32
33 -- globals set by set_shared_globals
34 g_paypoint VARCHAR2(5) := NULL;
35 g_cutover_date DATE;
36 g_ext_dfn_id NUMBER;
37 g_is_spread_bonus_yn VARCHAR2(1) := NULL;
38 g_allowance_end_dated_today VARCHAR2(1) := NULL;
39 g_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
40 g_assg_start_date DATE;
41 g_user_rate_function VARCHAR2(200) := NULL;
42 g_claim_date VARCHAR2(60);
43
44
45 --g_penserver_contract_type VARCHAR2(1);
46 --g_contract_type_effective_date DATE;
47 --g_contract_type VARCHAR2(30);
48
49 g_basic_sal_rate_name VARCHAR2(30); -- the rate type used for Salary.
50
51 g_salary_start_date DATE; --
52
53 g_salary_end_date DATE;--
54
55 g_salary_ele_end_date DATE := c_highest_date;
56 -- this is used to makr that current event date has a salary element end event
57 g_non_salary_ele_end_date DATE := c_highest_date;
58 -- this is used to mark that there is no salary element end event on current event date
59
60 g_sal_chg_event_exists VARCHAR2(1);
61 -- this is used to mark that there is a salary change event on current date.
62
63 g_salary_ended VARCHAR2(1);
64 -- this is mark that the salary has ended and no further event wud be processed
65
66 g_salary_started VARCHAR2(1);
67 -- this is mark that the salary has started and further events will be processed
68
69 g_curr_person_dtls per_all_people_f%ROWTYPE;
70 -- this contains the person details on effective date
71
72 g_curr_assg_dtls per_all_assignments_f%ROWTYPE;
73
74 --For Bug 7149468
75 g_leaver_event varchar2(2);
76
77 --For Bug 7229852
78 g_act_term_date DATE;
79
80
81 TYPE t_varchar30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
82 g_dated_tables t_varchar30;
83
84 CURSOR csr_assignment_status
85 (
86 p_assignment_status_type_id NUMBER
87 )
88 IS
89 SELECT DECODE(pay_system_status,'D','DO NOT PROCESS','P','PROCESS')
90 ,per_system_status
91 FROM per_assignment_status_types
92 WHERE ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
93 AND primary_flag = 'P';
94
95 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
96
97 /*CURSOR csr_get_contract_type
98 (
99 p_effective_date DATE
100 )
101 IS
102 select CONTRACT_TYPE
103 from pqp_assignment_attributes_f
104 where business_group_id = g_business_group_id
105 and assignment_id = g_assignment_id
106 and p_effective_date between effective_start_date
107 and effective_end_date;*/
108
109 CURSOR csr_get_grade_extra_info
110 (
111 p_grade_id NUMBER
112 )
113 IS
114 select INFORMATION2 GRADE_CODE,INFORMATION5 UNIFORM_GRADE_FLAG
115 from PER_GRADES
116 where INFORMATION_CATEGORY = 'GB_PQP_PENSERV_GRADE_INFO'
117 and grade_id = p_grade_id
118 and business_group_id = g_business_group_id
119 and g_effective_date between date_from
120 and nvl(date_to,c_highest_date);
121
122 -- cursor to check if the change is on FTE
123 CURSOR csr_is_fte_abv
124 (
125 p_assignment_budget_value_id NUMBER
126 )
127 IS
128 SELECT 'Y'
129 FROM PER_ASSIGNMENT_BUDGET_VALUES_F
130 WHERE assignment_budget_value_id = p_assignment_budget_value_id
131 AND UNIT = 'FTE'
132 AND ROWNUM = 1 ;
133
134
135 TYPE t_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
136 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
137
138 TYPE r_asg_fte_values IS RECORD
139 (
140 t_assignment_budget_value_id t_num_type
141 ,t_effective_start_date t_date_type
142 ,t_effective_end_date t_date_type
143 ,t_value t_num_type
144 );
145 g_asg_fte_values r_asg_fte_values;
146
147 -- cursor to fetch all the FTE values of an assignment
148 CURSOR csr_get_asg_fte_values
149 IS
150 SELECT assignment_budget_value_id
151 ,effective_start_date
152 ,effective_end_date
153 ,value
154 FROM PER_ASSIGNMENT_BUDGET_VALUES_F
155 WHERE UNIT = 'FTE'
156 AND assignment_id = g_assignment_id
157 and business_group_id = g_business_group_id;
158
159
160 CURSOR csr_get_entry_value
161 (c_effective_date DATE
162 ,c_element_entry_id NUMBER
163 ,c_input_value IN VARCHAR2 DEFAULT 'PAY VALUE'
164 )
165 IS
166 select peev.screen_entry_value
167 from
168 PAY_ELEMENT_ENTRY_VALUES_F peev
169 ,pay_input_values_f piv
170 where peev.element_entry_id = c_element_entry_id
171 AND peev.input_value_id = piv.input_value_id
172 and UPPER(piv.NAME) = UPPER(c_input_value)
173 and c_effective_date
174 between peev.effective_start_date
175 and peev.effective_end_date;
176
177 CURSOR csr_get_start_date_cut
178 (p_element_entry_id IN NUMBER
179 )
180 IS
181 SELECT effective_start_date FROM pay_element_entries_f
182 WHERE element_entry_id = p_element_entry_id
183 AND rownum = 1
184 ORDER BY effective_start_date;
185
186 -- Debug
187 PROCEDURE DEBUG (
188 p_trace_message IN VARCHAR2
189 ,p_trace_location IN NUMBER DEFAULT NULL
190 );
191
192 -- Debug_Enter
193 PROCEDURE debug_enter (
194 p_proc_name IN VARCHAR2
195 ,p_trace_on IN VARCHAR2 DEFAULT NULL
196 );
197
198 -- Debug_Exit
199 PROCEDURE debug_exit (
200 p_proc_name IN VARCHAR2
201 ,p_trace_off IN VARCHAR2 DEFAULT NULL
202 );
203
204 -- Debug Others
205 PROCEDURE debug_others (
206 p_proc_name IN VARCHAR2
207 ,p_proc_step IN NUMBER DEFAULT NULL
208 );
209 ---
210
211 -- ----------------------------------------------------------------------------
212 -- |------------------------< Function Definitions >---------------------------|
213 -- ----------------------------------------------------------------------------
214
215 --
216 -- Allowance history main function
217 --
218 FUNCTION allowance_history_main
219 (p_business_group_id IN NUMBER -- context
220 ,p_effective_date IN DATE -- context
221 ,p_assignment_id IN NUMBER -- context
222 ,p_rule_parameter IN VARCHAR2 -- parameter
223 ,p_output OUT NOCOPY VARCHAR2
224 )
225 RETURN number;
226
227
228 -- ----------------------------------------------------------------------------
229 -- |---------------------< all_cutover_ext_criteria >---------------------|
230 -- Description: Cutover extract criteria.
231 -- ----------------------------------------------------------------------------
232 FUNCTION all_cutover_ext_criteria
233 (
234 p_business_group_id IN NUMBER
235 ,p_assignment_id IN NUMBER
236 ,p_effective_date IN DATE
237 )RETURN VARCHAR2;
238 -- ----------------------------------------------------------------------------
239 -- |---------------------< salary_periodic_ext_criteria >---------------------|
240 -- Description: Periodic Changes extract criteria.
241 -- ----------------------------------------------------------------------------
242 FUNCTION all_periodic_ext_criteria
243 (
244 p_business_group_id IN NUMBER
245 ,p_assignment_id IN NUMBER
246 ,p_effective_date IN DATE
247 )RETURN VARCHAR2;
248
249 FUNCTION allowance_post_processing RETURN VARCHAR2;
250
251
252 END PQP_GB_PSI_ALLOWANCE_HISTORY;
[Home] [Help]