[Home] [Help]
PACKAGE BODY: APPS.PQH_COMP_SURVEY
Source
1 package body PQH_COMP_SURVEY as
2 /* $Header: pqhsuadi.pkb 120.1 2005/10/05 15:07:26 rthiagar noship $ */
3
4 -- This procedure is used within Web ADI for Compensation Survey to support
5 -- creation of new salary survey lines and to update existing survey lines
6 -- that are downloaded to a spread sheet.
7 procedure import_row
8 ( P_survey_name VARCHAR2 default null
9 , P_identifier VARCHAR2 default null
10 , P_survey_company VARCHAR2 default null
11 , P_survey_type VARCHAR2 default null
12 , P_base_region VARCHAR2 default null
13 , P_SALARY_SURVEY_ID NUMBER default null
14 , P_SALARY_SURVEY_LINE_ID NUMBER default null
15 , P_OBJECT_VERSION_NUMBER NUMBER default null
16 , P_SURVEY_JOB_NAME_CODE VARCHAR2 default null
17 , P_SURVEY_REGION_CODE VARCHAR2 default null
18 , P_SURVEY_SENIORITY_CODE VARCHAR2 default null
19 , P_COMPANY_SIZE_CODE VARCHAR2 default null
20 , P_INDUSTRY_CODE VARCHAR2 default null
21 , P_SURVEY_AGE_CODE VARCHAR2 default null
22 , P_CURRENCY_CODE VARCHAR2 default null
23 , P_STOCK_DISPLAY_TYPE_CODE VARCHAR2 default null
24 , P_SURVEY_JOB_NAME VARCHAR2 default null
25 , P_SURVEY_REGION VARCHAR2 default null
26 , P_SURVEY_SENIORITY VARCHAR2 default null
27 , P_COMPANY_SIZE VARCHAR2 default null
28 , P_INDUSTRY VARCHAR2 default null
29 , P_SURVEY_AGE VARCHAR2 default null
30 , P_CURRENCY VARCHAR2 default null
31 , P_START_DATE DATE default null
32 , P_END_DATE DATE default null
33 , P_DIFFERENTIAL NUMBER default null
34 , P_MINIMUM_PAY NUMBER default null
35 , P_MEAN_PAY NUMBER default null
36 , P_MAXIMUM_PAY NUMBER default null
37 , P_GRADUATE_PAY NUMBER default null
38 , P_STARTING_PAY NUMBER default null
39 , P_PERCENTAGE_CHANGE NUMBER default null
40 , P_JOB_FIRST_QUARTILE NUMBER default null
41 , P_JOB_MEDIAN_QUARTILE NUMBER default null
42 , P_JOB_THIRD_QUARTILE NUMBER default null
43 , P_JOB_FOURTH_QUARTILE NUMBER default null
44 , P_MINIMUM_TOTAL_COMPENSATION NUMBER default null
45 , P_MEAN_TOTAL_COMPENSATION NUMBER default null
46 , P_MAXIMUM_TOTAL_COMPENSATION NUMBER default null
47 , P_COMPNSTN_FIRST_QUARTILE NUMBER default null
48 , P_COMPNSTN_MEDIAN_QUARTILE NUMBER default null
49 , P_COMPNSTN_THIRD_QUARTILE NUMBER default null
50 , P_COMPNSTN_FOURTH_QUARTILE NUMBER default null
51 , P_TENTH_PERCENTILE NUMBER default null
52 , P_TWENTY_FIFTH_PERCENTILE NUMBER default null
53 , P_FIFTIETH_PERCENTILE NUMBER default null
54 , P_SEVENTY_FIFTH_PERCENTILE NUMBER default null
55 , P_NINETIETH_PERCENTILE NUMBER default null
56 , P_MINIMUM_BONUS NUMBER default null
57 , P_MEAN_BONUS NUMBER default null
58 , P_MAXIMUM_BONUS NUMBER default null
59 , P_MINIMUM_SALARY_INCREASE NUMBER default null
60 , P_MEAN_SALARY_INCREASE NUMBER default null
61 , P_MAXIMUM_SALARY_INCREASE NUMBER default null
62 , P_MIN_VARIABLE_COMPENSATION NUMBER default null
63 , P_MEAN_VARIABLE_COMPENSATION NUMBER default null
64 , P_MAX_VARIABLE_COMPENSATION NUMBER default null
65 , P_MINIMUM_STOCK NUMBER default null
66 , P_MEAN_STOCK NUMBER default null
67 , P_MAXIMUM_STOCK NUMBER default null
68 , P_STOCK_DISPLAY_TYPE VARCHAR2 default null
69 , P_ATTRIBUTE_CATEGORY VARCHAR2 default null
70 , P_ATTRIBUTE1 VARCHAR2 default null
71 , P_ATTRIBUTE2 VARCHAR2 default null
72 , P_ATTRIBUTE3 VARCHAR2 default null
73 , P_ATTRIBUTE4 VARCHAR2 default null
74 , P_ATTRIBUTE5 VARCHAR2 default null
75 , P_ATTRIBUTE6 VARCHAR2 default null
76 , P_ATTRIBUTE7 VARCHAR2 default null
77 , P_ATTRIBUTE8 VARCHAR2 default null
78 , P_ATTRIBUTE9 VARCHAR2 default null
79 , P_ATTRIBUTE10 VARCHAR2 default null
80 , P_ATTRIBUTE11 VARCHAR2 default null
81 , P_ATTRIBUTE12 VARCHAR2 default null
82 , P_ATTRIBUTE13 VARCHAR2 default null
83 , P_ATTRIBUTE14 VARCHAR2 default null
84 , P_ATTRIBUTE15 VARCHAR2 default null
85 , P_ATTRIBUTE16 VARCHAR2 default null
86 , P_ATTRIBUTE17 VARCHAR2 default null
87 , P_ATTRIBUTE18 VARCHAR2 default null
88 , P_ATTRIBUTE19 VARCHAR2 default null
89 , P_ATTRIBUTE20 VARCHAR2 default null
90 , P_ATTRIBUTE21 VARCHAR2 default null
91 , P_ATTRIBUTE22 VARCHAR2 default null
92 , P_ATTRIBUTE23 VARCHAR2 default null
93 , P_ATTRIBUTE24 VARCHAR2 default null
94 , P_ATTRIBUTE25 VARCHAR2 default null
95 , P_ATTRIBUTE26 VARCHAR2 default null
96 , P_ATTRIBUTE27 VARCHAR2 default null
97 , P_ATTRIBUTE28 VARCHAR2 default null
98 , P_ATTRIBUTE29 VARCHAR2 default null
99 , P_ATTRIBUTE30 VARCHAR2 default null
100 , P_LAST_UPDATE_DATE DATE default null
101 , P_LAST_UPDATED_BY NUMBER default null
102 , P_LAST_UPDATE_LOGIN NUMBER default null
103 , P_CREATED_BY NUMBER default null
104 , P_CREATION_DATE DATE default null
105 ) is
106
107 --
108 l_salary_survey_line_id per_salary_survey_lines.salary_survey_line_id%TYPE;
109 l_ssl_object_version_number per_salary_survey_lines.object_version_number%TYPE;
110 --
111 l_overlap_warning boolean := false;
112 --
113 -- This cursor gets the latest object version number to determine the row to be -- updated.
114 cursor c_object_version is
115 select max(object_version_number)
116 from per_salary_survey_lines
117 where salary_survey_line_id = p_salary_survey_line_id
118 and salary_survey_id = p_salary_survey_id;
119
120 begin
121
122 -- If survey line id is null then a new row needs to be created for the survey
123 -- line and hence the api for creating a new survey line is called.
124 if P_SALARY_SURVEY_LINE_ID is null then
125
126 hr_salary_survey_line_api.create_salary_survey_line
127 (p_validate => FALSE,
128 p_salary_survey_id => p_salary_survey_id,
129 p_survey_job_name_code => p_survey_job_name_code,
130 p_survey_region_code => p_survey_region_code,
131 p_survey_seniority_code => p_survey_seniority_code,
132 p_company_size_code => p_company_size_code,
133 p_industry_code => p_industry_code,
134 p_survey_age_code => p_survey_age_code,
135 p_start_date => p_start_date,
136 p_end_date => p_end_date,
137 p_currency_code => p_currency_code,
138 p_differential => p_differential,
139 p_minimum_pay => p_minimum_pay,
140 p_mean_pay => p_mean_pay,
141 p_maximum_pay => p_maximum_pay,
142 p_graduate_pay => p_graduate_pay,
143 p_starting_pay => p_starting_pay,
144 p_percentage_change => p_percentage_change,
145 p_job_first_quartile => p_job_first_quartile,
146 p_job_median_quartile => p_job_median_quartile,
147 p_job_third_quartile => p_job_third_quartile,
148 p_job_fourth_quartile => p_job_fourth_quartile,
149 p_minimum_total_compensation => p_minimum_total_compensation,
150 p_mean_total_compensation => p_mean_total_compensation,
151 p_maximum_total_compensation => p_maximum_total_compensation,
152 p_compnstn_first_quartile => p_compnstn_first_quartile,
153 p_compnstn_median_quartile => p_compnstn_median_quartile,
154 p_compnstn_third_quartile => p_compnstn_third_quartile,
155 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
156 p_tenth_percentile => p_tenth_percentile,
157 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
158 p_fiftieth_percentile => p_fiftieth_percentile,
159 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
160 p_ninetieth_percentile => p_ninetieth_percentile,
161 p_minimum_bonus => p_minimum_bonus,
162 p_mean_bonus => p_mean_bonus,
163 p_maximum_bonus => p_maximum_bonus,
164 p_minimum_salary_increase => p_minimum_salary_increase,
165 p_mean_salary_increase => p_mean_salary_increase,
166 p_maximum_salary_increase => p_maximum_salary_increase,
167 p_min_variable_compensation => p_min_variable_compensation,
168 p_mean_variable_compensation => p_mean_variable_compensation,
169 p_max_variable_compensation => p_max_variable_compensation,
170 p_stock_display_type => p_stock_display_type_code,
171 p_minimum_stock => p_minimum_stock,
172 p_mean_stock => p_mean_stock,
173 p_maximum_stock => p_maximum_stock
174 ,p_effective_date => sysdate --l_effective_date
175 ,p_attribute_category => p_attribute_category
176 ,p_attribute1 => p_attribute1
177 ,p_attribute2 => p_attribute2
178 ,p_attribute3 => p_attribute3
179 ,p_attribute4 => p_attribute4
180 ,p_attribute5 => p_attribute5
181 ,p_attribute6 => p_attribute6
182 ,p_attribute7 => p_attribute7
183 ,p_attribute8 => p_attribute8
184 ,p_attribute9 => p_attribute9
185 ,p_attribute10 => p_attribute10
186 ,p_attribute11 => p_attribute11
187 ,p_attribute12 => p_attribute12
188 ,p_attribute13 => p_attribute13
189 ,p_attribute14 => p_attribute14
190 ,p_attribute15 => p_attribute15
191 ,p_attribute16 => p_attribute16
192 ,p_attribute17 => p_attribute17
193 ,p_attribute18 => p_attribute18
194 ,p_attribute19 => p_attribute19
195 ,p_attribute20 => p_attribute20
196 ,p_attribute21 => p_attribute21
197 ,p_attribute22 => p_attribute22
198 ,p_attribute23 => p_attribute23
199 ,p_attribute24 => p_attribute24
200 ,p_attribute25 => p_attribute25
201 ,p_attribute26 => p_attribute26
202 ,p_attribute27 => p_attribute27
203 ,p_attribute28 => p_attribute28
204 ,p_attribute29 => p_attribute29
205 ,p_attribute30 => p_attribute30
206 ,p_salary_survey_line_id => l_salary_survey_line_id,
207 p_ssl_object_version_number => l_ssl_object_version_number,
208 p_overlap_warning => l_overlap_warning
209 );
210
211 else -- P_SALARY_SURVEY_LINE_ID is not null
212 -- If survey line id is not null then we get the object version number of the
213 -- survey line to be updated and issue a call to the survey line update api.
214
215 open c_object_version;
216 fetch c_object_version into l_ssl_object_version_number;
217 close c_object_version;
218
219 hr_salary_survey_line_api.update_salary_survey_line
220 (p_validate => FALSE,
221 p_survey_job_name_code => p_survey_job_name_code,
222 p_survey_region_code => p_survey_region_code,
223 p_survey_seniority_code => p_survey_seniority_code,
224 p_company_size_code => p_company_size_code,
225 p_industry_code => p_industry_code,
226 p_survey_age_code => p_survey_age_code,
227 p_start_date => p_start_date,
228 p_end_date => p_end_date,
229 p_currency_code => p_currency_code,
230 p_differential => p_differential,
231 p_minimum_pay => p_minimum_pay,
232 p_mean_pay => p_mean_pay,
233 p_maximum_pay => p_maximum_pay,
234 p_graduate_pay => p_graduate_pay,
235 p_starting_pay => p_starting_pay,
236 p_percentage_change => p_percentage_change,
237 p_job_first_quartile => p_job_first_quartile,
238 p_job_median_quartile => p_job_median_quartile,
239 p_job_third_quartile => p_job_third_quartile,
240 p_job_fourth_quartile => p_job_fourth_quartile,
241 p_minimum_total_compensation => p_minimum_total_compensation,
242 p_mean_total_compensation => p_mean_total_compensation,
243 p_maximum_total_compensation => p_maximum_total_compensation,
244 p_compnstn_first_quartile => p_compnstn_first_quartile,
245 p_compnstn_median_quartile => p_compnstn_median_quartile,
246 p_compnstn_third_quartile => p_compnstn_third_quartile,
247 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
248 p_tenth_percentile => p_tenth_percentile,
249 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
250 p_fiftieth_percentile => p_fiftieth_percentile,
251 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
252 p_ninetieth_percentile => p_ninetieth_percentile,
253 p_minimum_bonus => p_minimum_bonus,
254 p_mean_bonus => p_mean_bonus,
255 p_maximum_bonus => p_maximum_bonus,
256 p_minimum_salary_increase => p_minimum_salary_increase,
257 p_mean_salary_increase => p_mean_salary_increase,
258 p_maximum_salary_increase => p_maximum_salary_increase,
259 p_min_variable_compensation => p_min_variable_compensation,
260 p_mean_variable_compensation => p_mean_variable_compensation,
261 p_max_variable_compensation => p_max_variable_compensation,
262 p_stock_display_type => p_stock_display_type_code,
263 p_minimum_stock => p_minimum_stock,
264 p_mean_stock => p_mean_stock,
265 p_maximum_stock => p_maximum_stock
266 ,p_effective_date => sysdate --l_effective_date
267 ,p_attribute_category => p_attribute_category
268 ,p_attribute1 => p_attribute1
269 ,p_attribute2 => p_attribute2
270 ,p_attribute3 => p_attribute3
271 ,p_attribute4 => p_attribute4
272 ,p_attribute5 => p_attribute5
273 ,p_attribute6 => p_attribute6
274 ,p_attribute7 => p_attribute7
275 ,p_attribute8 => p_attribute8
276 ,p_attribute9 => p_attribute9
277 ,p_attribute10 => p_attribute10
278 ,p_attribute11 => p_attribute11
279 ,p_attribute12 => p_attribute12
280 ,p_attribute13 => p_attribute13
281 ,p_attribute14 => p_attribute14
282 ,p_attribute15 => p_attribute15
283 ,p_attribute16 => p_attribute16
284 ,p_attribute17 => p_attribute17
285 ,p_attribute18 => p_attribute18
286 ,p_attribute19 => p_attribute19
287 ,p_attribute20 => p_attribute20
288 ,p_attribute21 => p_attribute21
289 ,p_attribute22 => p_attribute22
290 ,p_attribute23 => p_attribute23
291 ,p_attribute24 => p_attribute24
292 ,p_attribute25 => p_attribute25
293 ,p_attribute26 => p_attribute26
294 ,p_attribute27 => p_attribute27
295 ,p_attribute28 => p_attribute28
296 ,p_attribute29 => p_attribute29
297 ,p_attribute30 => p_attribute30
298 ,p_salary_survey_line_id => p_salary_survey_line_id
299 ,p_ssl_object_version_number => l_ssl_object_version_number
300 );
301
302 end if; -- P_SALARY_SURVEY_LINE_ID is null
303
304 end import_row;
305
306 end PQH_COMP_SURVEY;