1 PACKAGE PQP_GB_PSI_SALARY_HISTORY AS
2 -- /* $Header: pqpgbpssal.pkh 120.0.12000000.3 2007/03/07 13:15:02 kkarri noship $ */
3 --
4 -- Debug Variables.
5 --
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7 g_package VARCHAR2(30) := 'PQP_GB_PSI_SALARY_HISTORY';
8 c_application_id CONSTANT NUMBER := 8303;
9 c_highest_date CONSTANT DATE := hr_api.g_eot;
10 g_person_id per_all_people_f.person_id%type;
11 g_assignment_id NUMBER;
12 g_business_group_id per_all_people_f.business_group_id%TYPE;
13 g_legislation_code VARCHAR2(4);
14 g_current_run varchar2(20);
15 g_pay_proc_evt_tab ben_ext_person.t_detailed_output_table;
16 g_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
17 g_notional_pay NUMBER; -- used while calculating actual pay
18 g_effective_date DATE;-- for cutover run this will be the cutover date
19 -- for periodic changes this will be set to event eff date.
20
21 --g_penserver_contract_type VARCHAR2(1);
22 --g_contract_type_effective_date DATE;
23 --g_contract_type VARCHAR2(30);
24
25 g_basic_sal_rate_name VARCHAR2(30); -- the rate type used for Salary.
26
27 g_user_rate_function VARCHAR2(200);
28
29 g_sal_ele_fte_attr VARCHAR2(10); -- added in 115.12.
30
31 g_salary_start_date DATE; --
32
33 g_salary_end_date DATE;--
34
35 g_curr_person_dtls per_all_people_f%ROWTYPE;
36 -- this contains the person details on effective date
37
38 g_curr_assg_dtls per_all_assignments_f%ROWTYPE;
39 -- this contains the person details on effective date
40 g_paypoint VARCHAR2(30);
41 g_cutover_date DATE;
42 g_ext_dfn_id NUMBER;
43
44 -- uniformed grade override flag configuration values.
45 g_unigrade_source VARCHAR2(30);
46 g_assignment_context VARCHAR2(80);
47 g_assignment_column VARCHAR2(30);
48 g_people_group_column VARCHAR2(30);
49 ----------
50
51 g_grade_chg_date DATE := hr_api.g_eot;
52 g_todays_grade_code VARCHAR2(80);
53
54 CURSOR csr_assignment_status
55 (
56 p_assignment_status_type_id NUMBER
57 )
58 IS
59 SELECT DECODE(pay_system_status,'D','DO NOT PROCESS','P','PROCESS')
60 ,per_system_status
61 FROM per_assignment_status_types
62 WHERE ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
63 AND primary_flag = 'P';
64
65 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
66
67 TYPE t_varchar2 IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
68
69 g_grade_codes t_varchar2;
70
71 /*CURSOR csr_get_contract_type
72 (
73 p_effective_date DATE
74 )
75 IS
76 select CONTRACT_TYPE
77 from pqp_assignment_attributes_f
78 where business_group_id = g_business_group_id
79 and assignment_id = g_assignment_id
80 and p_effective_date between effective_start_date
81 and effective_end_date;*/
82
83 CURSOR csr_get_grade_extra_info
84 (
85 p_grade_id NUMBER
86 )
87 IS
88 select INFORMATION2 GRADE_CODE,INFORMATION5 UNIFORM_GRADE_FLAG
89 from PER_GRADES
90 where INFORMATION_CATEGORY = 'GB_PQP_PENSERV_GRADE_INFO'
91 and grade_id = p_grade_id
92 and business_group_id = g_business_group_id
93 and g_effective_date between date_from
94 and nvl(date_to,c_highest_date);
95
96 -- cursor to check if the change is on FTE
97 CURSOR csr_is_fte_abv
98 (
99 p_assignment_budget_value_id NUMBER
100 )
101 IS
102 SELECT 'Y'
103 FROM PER_ASSIGNMENT_BUDGET_VALUES_F
104 WHERE assignment_budget_value_id = p_assignment_budget_value_id
105 AND UNIT = 'FTE'
106 AND ROWNUM = 1 ;
107
108
109 -- cursor to fetch all the FTE values of an assignment
110 CURSOR csr_get_asg_fte_values
111 IS
112 SELECT assignment_budget_value_id
113 ,effective_start_date
114 ,effective_end_date
115 ,value
116 FROM PER_ASSIGNMENT_BUDGET_VALUES_F
117 WHERE UNIT = 'FTE'
118 AND assignment_id = g_assignment_id
119 and business_group_id = g_business_group_id;
120
121 -- Debug
122 PROCEDURE DEBUG (
123 p_trace_message IN VARCHAR2
124 ,p_trace_location IN NUMBER DEFAULT NULL
125 );
126
127 -- Debug_Enter
128 PROCEDURE debug_enter (
129 p_proc_name IN VARCHAR2
130 ,p_trace_on IN VARCHAR2 DEFAULT NULL
131 );
132
133 -- Debug_Exit
134 PROCEDURE debug_exit (
135 p_proc_name IN VARCHAR2
136 ,p_trace_off IN VARCHAR2 DEFAULT NULL
137 );
138
139 -- Debug Others
140 PROCEDURE debug_others (
141 p_proc_name IN VARCHAR2
142 ,p_proc_step IN NUMBER DEFAULT NULL
143 );
144 ---
145 -- ----------------------------------------------------------------------------
146 -- |----------------------< set_salary_history_globals >---------------------|
147 -- Description: This procedure is to obtain set the extract level globals.
148 -- ----------------------------------------------------------------------------
149 PROCEDURE set_salary_history_globals
150 (
151 p_business_group_id IN NUMBER
152 ,p_assignment_id IN NUMBER
153 ,p_effective_date IN DATE
154 );
155
156 -- ----------------------------------------------------------------------------
157 -- |----------------------< set_assignment_globals >--------------------------|
158 -- Description: This procedure is to set the assignment level globals.
159 -- ----------------------------------------------------------------------------
160 PROCEDURE set_assignment_globals
161 (
162 p_assignment_id IN NUMBER
163 ,p_effective_date IN DATE
164 );
165
166 PROCEDURE reset_salary_history_globals;
167
168 -- ----------------------------------------------------------------------------
169 -- |---------------------< salary_cutover_ext_criteria >---------------------|
170 -- Description: Cutover extract criteria.
171 -- ----------------------------------------------------------------------------
172 FUNCTION salary_cutover_ext_criteria
173 (
174 p_business_group_id IN NUMBER
175 ,p_assignment_id IN NUMBER
176 ,p_effective_date IN DATE
177 )RETURN VARCHAR2;
178 -- ----------------------------------------------------------------------------
179 -- |---------------------< salary_periodic_ext_criteria >---------------------|
180 -- Description: Periodic Changes extract criteria.
181 -- ----------------------------------------------------------------------------
182 FUNCTION salary_periodic_ext_criteria
183 (
184 p_business_group_id IN NUMBER
185 ,p_assignment_id IN NUMBER
186 ,p_effective_date IN DATE
187 )RETURN VARCHAR2;
188
189 -- ----------------------------------------------------------------------------
190 -- |---------------------< salary_data_element_value >-----------------------|
191 -- Description: This is a common function used by all the data elements to fetch
192 -- thier respective values. Depending the parameter p_ext_user_value
193 -- this procedure decides which value to be returned.
194 -- ----------------------------------------------------------------------------
195 FUNCTION salary_data_element_value
196 (
197 p_ext_user_value IN VARCHAR2
198 ,p_output_value OUT NOCOPY VARCHAR2
199 )
200 RETURN NUMBER;
201
202 -- ----------------------------------------------------------------------------
203 -- |----------------------< salary_post_processing >--------------------------|
204 -- Description: This is the post-processing rule for the Salary History.
205 -- ----------------------------------------------------------------------------
206 FUNCTION salary_post_processing RETURN VARCHAR2;
207
208 END PQP_GB_PSI_SALARY_HISTORY;
[Home] [Help]