[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_CONFIGURATION_VALUE
Source
1 PACKAGE BODY PQP_GB_CONFIGURATION_VALUE as
2 /* $Header: pqgbpcvp.pkb 120.0.12010000.4 2009/08/25 10:57:52 parusia ship $ */
3
4 g_debug boolean;
5 g_QTS_LOOKUP_NAME varchar2(10) := 'YES_NO';
6 g_QTS_ROUTE_LOOKUP_NAME varchar2(30) := 'PQP_GB_SWF_QTS_ROUTES';
7 g_REG_SPINE_LOOKUP_NAME varchar2(30) := 'PQP_GB_REGIONAL_SPINE_CODE';
8
9 procedure chk_pension_scheme_mapping(p_pcv_information2 in varchar2
10 ,p_pcv_information3 in varchar2
11 ) as
12 l_proc varchar2(53);
13 Begin
14 l_proc:='PQP_GB_CONFIGURATION_VALUE.chk_pension_scheme_mapping';
15
16 if g_debug then
17 hr_utility.set_location('Entering:'||l_proc,10);
18 hr_utility.trace('p_pcv_information2 '||p_pcv_information2);
19 hr_utility.trace('p_pcv_information3 '||p_pcv_information3);
20 end if;
21
22 -- pcv_information3(Partnership scheme) must have value if
23 -- pcv_information2 is PARTNER
24 if (
25 p_pcv_information2 ='PARTNER' and p_pcv_information3 is null
26 ) then
27
28 hr_utility.set_message(8303,'PQP_230236_ENTER_PARTNER_SCH');
29 hr_utility.raise_error;
30
31 --else
32 -- pcv_information2 is not PARTNER then ignore.
33 end if;
34
35
36 if g_debug then
37 hr_utility.set_location('Leaving:'||l_proc,20);
38 end if;
39
40 End chk_pension_scheme_mapping;
41
42
43 ------------
44 -- School Workforce Census- Teacher Number Validation
45 -- p_pcv_information1 => Source
46 -- p_pcv_information3 => Context Name
47 -- p_pcv_information4 => Segment Name
48 -----------
49 procedure chk_teacher_number(p_configuration_value_id in number
50 , p_pcv_information1 in varchar2
51 , p_pcv_information3 in varchar2
52 , p_pcv_information4 in varchar2
53 ) as
54 begin
55
56 -- If Person EIT or Person DFF is selected as source
57 -- then Context and Segment names are mandatory
58 if p_pcv_information1 = 'PER_PEOPLE' then
59 if p_pcv_information3 is null then
60 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
61 fnd_message.set_token('FIELD', 'Context Name');
62 fnd_message.set_token('SOURCE', 'Person DFF (Additional Personal Details Flexfield)');
63 fnd_message.raise_error;
64 elsif p_pcv_information4 is null then
65 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
66 fnd_message.set_token('FIELD', 'Segment Column');
67 fnd_message.set_token('SOURCE', 'Person DFF (Additional Personal Details Flexfield)');
68 fnd_message.raise_error;
69 end if ;
70 elsif p_pcv_information1 = 'Extra Person Info DDF' then
71 if p_pcv_information3 is null then
72 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
73 fnd_message.set_token('FIELD', 'Context Name');
74 fnd_message.set_token('SOURCE', 'Person EIT (Extra Person Information Flexfield)');
75 fnd_message.raise_error;
76 elsif p_pcv_information4 is null then
77 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
78 fnd_message.set_token('FIELD', 'Segment Column');
79 fnd_message.set_token('SOURCE', 'Person EIT (Extra Person Information Flexfield)');
80 fnd_message.raise_error;
81 end if ;
82 end if ;
83 --
84 end chk_teacher_number ;
85
86
87
88 ------------
89 -- School Workforce Census- Ethnic Origin Validation
90 -- p_pcv_information1 => User Ethnic Origin Value
91 -----------
92 procedure chk_ethnic_origin(p_configuration_value_id in number
93 , p_business_group_id in number
94 , p_pcv_information_category in varchar2
95 , p_pcv_information1 in varchar2
96 ) as
97 p_return boolean ;
98 begin
99 -- If the given user ethnic origin is already mapped
100 -- then user should not be able to map it again
101 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
102 , p_business_group_id => p_business_group_id
103 , p_pcv_information_category => p_pcv_information_category
104 , p_information_column => 'PCV_INFORMATION1'
105 , p_value => p_pcv_information1
106 , p_return => p_return);
107 if (p_return = false) then
108 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
109 fnd_message.set_token('VALUE', 'Ethnic Origin Value');
110 fnd_message.set_token('DCSF_CODE', 'DCSF Ethnic Code');
111 fnd_message.raise_error;
112 end if ;
113 --
114 end chk_ethnic_origin ;
115
116
117
118 ------------
119 -- School Workforce Census- QTS/QTS Route Mapping Validation
120 -- p_pcv_information1 => Lookup Name
121 -- p_pcv_information2 => Segment Start Value
122 -- p_pcv_information3 => Segment End Value
123 -- p_pcv_information4 => DCSF QTS/QTS Route Code
124 -----------
125 procedure chk_qts_mapping(p_configuration_value_id in number
126 , p_business_group_id in number
127 , p_pcv_information_category in varchar2
128 , p_pcv_information1 in varchar2
129 , p_pcv_information2 in varchar2
130 , p_pcv_information3 in varchar2
131 , p_pcv_information4 in varchar2
132 ) as
133 p_return boolean ;
134 p_seeded_lookup_name varchar(50);
135 l_field_name varchar2(50);
136 l_count number;
137 begin
138
139 -- 1) While creating multiple configuration records for this oconfiguration type
140 -- user should not be able to create records with different lookup names
141 pqp_gb_swf_validations.chk_unique_lookup_name(p_configuration_value_id => p_configuration_value_id
142 , p_business_group_id => p_business_group_id
143 , p_pcv_information_category => p_pcv_information_category
144 , p_information_column => 'PCV_INFORMATION1'
145 , p_value => p_pcv_information1
146 , p_return => p_return);
147 if (p_return = false) then
148 fnd_message.set_name('PQP', 'PQP_230243_USE_SAME_LOOKUP');
149 fnd_message.raise_error;
150 end if ;
151
152 -- 2) If the user has given seeded QTS/QTS Route lookup name
153 -- Then he should not be required to give the mapping
154 -- Else Segment Start Value and DCSF Code are mandatory
155
156 if p_pcv_information_category = 'PQP_GB_SWF_QTS_MAPPING' then
157 p_seeded_lookup_name := g_QTS_LOOKUP_NAME;
158 l_field_name := 'Qualified Teacher Status Value';
159 else
160 p_seeded_lookup_name := g_QTS_ROUTE_LOOKUP_NAME;
161 l_field_name := 'DCSF QTS Route Code';
162 end if ;
163
164 if p_pcv_information1 <> p_seeded_lookup_name then
165 if p_pcv_information2 is null or p_pcv_information4 is null then
166 fnd_message.set_name('PQP', 'PQP_230241_ENTER_MAPPING');
167 fnd_message.set_token('DCSF_CODE', l_field_name);
168 fnd_message.set_token('LOOKUP_NAME', p_seeded_lookup_name);
169 fnd_message.raise_error;
170 end if;
171 end if;
172
173 -- 3) If Segment End Value is provided
174 -- Then Segment Start Value should not be less than Segment End Value
175 if p_pcv_information1 <> p_seeded_lookup_name then
176 if p_pcv_information3 is not null then
177 if p_pcv_information3 < p_pcv_information2 then
178 fnd_message.set_name('PQP', 'PQP_230242_WRONG_END_VAL');
179 fnd_message.set_token('TYPE', 'Segment');
180 fnd_message.raise_error;
181 end if;
182 end if ;
183
184
185 -- 4) While creating multiple configuration records for this oconfiguration type
186 -- user should not be able to map same segment value twice
187 pqp_gb_swf_validations.chk_range_unique_mapping(p_configuration_value_id => p_configuration_value_id
188 , p_business_group_id => p_business_group_id
189 , p_pcv_information_category => p_pcv_information_category
190 , p_information_start_column => 'PCV_INFORMATION2'
191 , p_information_end_column => 'PCV_INFORMATION3'
192 , p_value_start => p_pcv_information2
193 , p_value_end => p_pcv_information3
194 , p_return => p_return);
195 if (p_return = false) then
196 fnd_message.set_name('PQP', 'PQP_230244_DUP_RANGE_MAP');
197 fnd_message.raise_error;
198 end if ;
199 else
200 -- seeded lookup used
201 -- check if there already exists a row with seeded lookup
202 -- then raise error saying mapping already defined
203 begin
204 select 1
205 into l_count
206 from pqp_configuration_values
207 where pcv_information_category = p_pcv_information_category
208 and business_group_id = p_business_group_id
209 and (p_configuration_value_id is null
210 or configuration_value_id <> p_configuration_value_id)
211 and pcv_information1 = p_pcv_information1 ;
212 exception
213 when others then
214 null ;
215 end;
216
217 if l_count is not null then
218 fnd_message.set_name('PQP', 'PQP_230240_DUPLICATE_MAPPING');
219 fnd_message.raise_error;
220 end if;
221 end if;
222 --
223 end chk_qts_mapping ;
224
225
226
227 ------------
228 -- School Workforce Census- HLTA Source Validation
229 -- p_pcv_information1 => Source ( Job KFF, Person DFF, Assignment DFF)
230 -- p_pcv_information3 => Job KFF Segment
231 -- p_pcv_information4 => Person/Assignment DFF Context
232 -- p_pcv_information5 => Person/Assignment DFF Segment
233 -----------
234 procedure chk_hlta_source(p_configuration_value_id in number
235 , p_business_group_id in number
236 , p_pcv_information_category in varchar2
237 , p_pcv_information1 in varchar2
238 , p_pcv_information3 in varchar2
239 , p_pcv_information4 in varchar2
240 , p_pcv_information5 in varchar2
241 )as
242 begin
243 -- 1) If Job Key Flexfield is selected as source
244 -- Then Job Key Flexfield segment must be entered
245 if p_pcv_information1 = 'JOB' and p_pcv_information3 is null then
246 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
247 fnd_message.set_token('FIELD', 'Job Key Flexfield Segment Column');
248 fnd_message.set_token('SOURCE', 'Job Key Flexfield');
249 fnd_message.raise_error;
250
251 end if ;
252
253 -- 2) If Person/Assignment DFF is selected as source
254 -- Then Person/Assignment Context and Segment must be provided
255 if p_pcv_information1 = 'PER_PEOPLE' then
256 if p_pcv_information4 is null then
257 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
258 fnd_message.set_token('FIELD', 'Person/Assignment Context Name');
259 fnd_message.set_token('SOURCE', 'Person DFF (Additional Personal Details Flexfield)');
260 fnd_message.raise_error;
261 elsif p_pcv_information5 is null then
262 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
266 end if;
263 fnd_message.set_token('FIELD', 'Person/Assignment Segment Column');
264 fnd_message.set_token('SOURCE', 'Person DFF (Additional Personal Details Flexfield)');
265 fnd_message.raise_error;
267 elsif p_pcv_information1 = 'PER_ASSIGNMENTS' then
268 if p_pcv_information4 is null then
269 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
270 fnd_message.set_token('FIELD', 'Person/Assignment Context Name');
271 fnd_message.set_token('SOURCE', 'Assignment DFF (Additional Assignment Details DFF)');
272 fnd_message.raise_error;
273 elsif p_pcv_information5 is null then
274 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
275 fnd_message.set_token('FIELD', 'Person/Assignment Segment Column');
276 fnd_message.set_token('SOURCE', 'Assignment DFF (Additional Assignment Details DFF)');
277 fnd_message.raise_error;
278 end if;
279 end if;
280 --
281 end chk_hlta_source;
282
283
284
285 ------------
286 -- School Workforce Census- HLTA Mapping Validation
287 -- p_pcv_information1 => Lookup Name
288 -- p_pcv_information2 => User Segment Value
289 -- p_pcv_information3 => HLTA Status Value
290 -----------
291 procedure chk_hlta_mapping(p_configuration_value_id in number
292 , p_business_group_id in number
293 , p_pcv_information_category in varchar2
294 , p_pcv_information1 in varchar2
295 , p_pcv_information2 in varchar2
296 , p_pcv_information3 in varchar2
297 )as
298 p_return boolean ;
299 begin
300 -- 1) While creating multiple configuration records for this oconfiguration type
301 -- user should not be able to create records with different lookup names
302 pqp_gb_swf_validations.chk_unique_lookup_name( p_configuration_value_id => p_configuration_value_id
303 , p_business_group_id => p_business_group_id
304 , p_pcv_information_category => p_pcv_information_category
305 , p_information_column => 'PCV_INFORMATION1'
306 , p_value => p_pcv_information1
307 , p_return => p_return);
308 if (p_return = false) then
309 fnd_message.set_name('PQP', 'PQP_230243_USE_SAME_LOOKUP');
310 fnd_message.raise_error;
311 end if ;
312
313 --
314 -- 2) If the given user segment value is already mapped
315 -- Then user should not be able to map it again
316 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
317 , p_business_group_id => p_business_group_id
318 , p_pcv_information_category => p_pcv_information_category
319 , p_information_column => 'PCV_INFORMATION2'
320 , p_value => p_pcv_information2
321 , p_return => p_return);
322 if (p_return = false) then
323 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
324 fnd_message.set_token('VALUE', 'User Segment Value');
325 fnd_message.set_token('DCSF_CODE', 'HLTA Status Value');
326 fnd_message.raise_error;
327 end if ;
328 --
329 end chk_hlta_mapping;
330
331
332
333 ------------
334 -- School Workforce Census- Arrival Date Validation
335 -- p_pcv_information1 => Source (Assignment Start Date / Calcuated)
336 -- p_pcv_information2 => User Formula
337 -----------
338 procedure chk_arrival_date(p_configuration_value_id in number
339 , p_business_group_id in number
340 , p_pcv_information_category in varchar2
341 , p_pcv_information1 in varchar2
342 , p_pcv_information2 in varchar2
343 ) as
344 begin
345 if p_pcv_information1 = 'CAL' and p_pcv_information2 is null then
346 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
347 fnd_message.set_token('FIELD', 'User Formula Name');
348 fnd_message.set_token('SOURCE', 'Calculated');
349 fnd_message.raise_error;
350 end if ;
351 --
352 end chk_arrival_date;
353
354 --
355 ------------
356 -- School Workforce Census- Contract Type Validation
357 -- p_pcv_information1 => Source (Assignment Category/ PQP Contract Type)
358 -- p_pcv_information3 => Assignment Category Value
359 -- p_pcv_information4 => PQP Contract Type Value
360 -- p_pcv_information5 => DCSF Contract Type Value
361 -----------
362 procedure chk_contract_type(p_configuration_value_id in number
363 , p_business_group_id in number
364 , p_pcv_information_category in varchar2
365 , p_pcv_information1 in varchar2
366 , p_pcv_information3 in varchar2
367 , p_pcv_information4 in varchar2
368 , p_pcv_information5 in varchar2
369 ) as
370 p_return boolean;
371 begin
372 -- 1) While creating multiple configuration records for this oconfiguration type
373 -- user should not be able to create records with different source names
374 pqp_gb_swf_validations.chk_unique_lookup_name(p_configuration_value_id => p_configuration_value_id
375 , p_business_group_id => p_business_group_id
376 , p_pcv_information_category => p_pcv_information_category
377 , p_information_column => 'PCV_INFORMATION1'
378 , p_value => p_pcv_information1
382 fnd_message.raise_error;
379 , p_return => p_return);
380 if (p_return = false) then
381 fnd_message.set_name('PQP', 'PQP_230245_USE_SAME_SOURCE');
383 end if ;
384
385
386 --
387 -- 2) Assignment Category Value must be provided if assignment category
388 -- is selected as source
389 if p_pcv_information1 = 'ASG_CAT' then
390 if p_pcv_information3 is null then
391 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
392 fnd_message.set_token('FIELD', 'Assignment Category Value');
393 fnd_message.set_token('SOURCE', 'Assignment Category');
394 fnd_message.raise_error;
395 end if ;
396 end if ;
397
398
399 --
400 -- 3) PQP Contract Type Value must be provided if PQP contract type
401 -- is selected as source
402 if p_pcv_information1 = 'CNTRCT_TYPE' then
403 if p_pcv_information4 is null then
404 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
405 fnd_message.set_token('FIELD', 'PQP Contract Type Value');
406 fnd_message.set_token('SOURCE', 'PQP Contract Type');
407 fnd_message.raise_error;
408 end if ;
409 end if ;
410
411
412 --
413 -- 4) If the given user value(Assignment Category or PQP contract type) is already mapped
414 -- Then user should not be able to map it again
415 if p_pcv_information1 = 'ASG_CAT' then
416 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
417 , p_business_group_id => p_business_group_id
418 , p_pcv_information_category => p_pcv_information_category
419 , p_information_column => 'PCV_INFORMATION3'
420 , p_value => p_pcv_information3
421 , p_return => p_return);
422 if (p_return = false) then
423 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
424 fnd_message.set_token('VALUE', 'Assignment Category');
425 fnd_message.set_token('DCSF_CODE', 'DCSF Contract Type Value');
426 fnd_message.raise_error;
427 end if ;
428 elsif p_pcv_information1 = 'CNTRCT_TYPE' then
429 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
430 , p_business_group_id => p_business_group_id
431 , p_pcv_information_category => p_pcv_information_category
432 , p_information_column => 'PCV_INFORMATION4'
433 , p_value => p_pcv_information4
434 , p_return => p_return);
435 if (p_return = false) then
436 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
437 fnd_message.set_token('VALUE', 'PQP Contract Type Value');
438 fnd_message.set_token('DCSF_CODE', 'DCSF Contract Type Value');
439 fnd_message.raise_error;
440 end if ;
441 end if ;
442
443
444 end chk_contract_type ;
445
446 --
447 ------------
448 -- School Workforce Census- Origin Mapping Validation
449 -- p_pcv_information1 => Lookup Name
450 -- p_pcv_information3 => Origin Value
451 -- p_pcv_information4 => DCSF Origin Code
452 -----------
453 procedure chk_origin_mapping(p_configuration_value_id in number
454 , p_business_group_id in number
455 , p_pcv_information_category in varchar2
456 , p_pcv_information1 in varchar2
457 , p_pcv_information2 in varchar2
458 , p_pcv_information3 in varchar2
459 ) as
460 p_return boolean;
461 begin
462
463 -- 1) While creating multiple configuration records for this oconfiguration type
464 -- user should not be able to create records with different lookup names
465 pqp_gb_swf_validations.chk_unique_lookup_name(p_configuration_value_id => p_configuration_value_id
466 , p_business_group_id => p_business_group_id
467 , p_pcv_information_category => p_pcv_information_category
468 , p_information_column => 'PCV_INFORMATION1'
469 , p_value => p_pcv_information1
470 , p_return => p_return);
471 if (p_return = false) then
472 fnd_message.set_name('PQP', 'PQP_230243_USE_SAME_LOOKUP');
473 fnd_message.raise_error;
474 end if ;
475
476 -- 2) If the given user origin value is already mapped
477 -- then user should not be able to map it again
478 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
479 , p_business_group_id => p_business_group_id
480 , p_pcv_information_category => p_pcv_information_category
481 , p_information_column => 'PCV_INFORMATION2'
482 , p_value => p_pcv_information2
483 , p_return => p_return);
484 if (p_return = false) then
485 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
486 fnd_message.set_token('VALUE', 'Origin Value');
487 fnd_message.set_token('DCSF_CODE', 'DCSF Origin Code');
488 fnd_message.raise_error;
489 end if ;
490 end chk_origin_mapping;
491
492 --
493 ------------
494 -- School Workforce Census- Destination Mapping Validation
495 -- p_pcv_information1 => Lookup Name
496 -- p_pcv_information3 => Destination Value
497 -- p_pcv_information4 => DCSF Destination Code
498 -----------
502 , p_pcv_information1 in varchar2
499 procedure chk_destination_mapping(p_configuration_value_id in number
500 , p_business_group_id in number
501 , p_pcv_information_category in varchar2
503 , p_pcv_information2 in varchar2
504 , p_pcv_information3 in varchar2
505 ) as
506 p_return boolean;
507 begin
508
509 -- 1) While creating multiple configuration records for this oconfiguration type
510 -- user should not be able to create records with different lookup names
511 pqp_gb_swf_validations.chk_unique_lookup_name(p_configuration_value_id => p_configuration_value_id
512 , p_business_group_id => p_business_group_id
513 , p_pcv_information_category => p_pcv_information_category
514 , p_information_column => 'PCV_INFORMATION1'
515 , p_value => p_pcv_information1
516 , p_return => p_return);
517 if (p_return = false) then
518 fnd_message.set_name('PQP', 'PQP_230243_USE_SAME_LOOKUP');
519 fnd_message.raise_error;
520 end if ;
521
522 -- 2) If the given user Destination value is already mapped
523 -- then user should not be able to map it again
524 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
525 , p_business_group_id => p_business_group_id
526 , p_pcv_information_category => p_pcv_information_category
527 , p_information_column => 'PCV_INFORMATION2'
528 , p_value => p_pcv_information2
529 , p_return => p_return);
530 if (p_return = false) then
531 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
532 fnd_message.set_token('VALUE', 'Destination Value');
533 fnd_message.set_token('DCSF_CODE', 'DCSF Destination Code');
534 fnd_message.raise_error;
535 end if ;
536 --
537 end chk_destination_mapping;
538
539 --
540 --
541 ------------
542 -- School Workforce Census- Role/Post Source Validation
543 -- p_pcv_information1 => Source(Job, Grade, Position KFF, Assignment Category)
544 -- p_pcv_information3 => Job/ Grade/ Position segment
545 -----------
546 procedure chk_role_post_source(p_configuration_value_id in number
547 , p_business_group_id in number
548 , p_pcv_information_category in varchar2
549 , p_pcv_information1 in varchar2
550 , p_pcv_information3 in varchar2
551 ) as
552 p_return boolean;
553 l_mapping_info_category varchar2(100);
554 l_mapping_lookup varchar2(100) ;
555 begin
556 -- hr_utility.trace_on(null,'SWFTRC');
557 hr_utility.trace('Inside Post source');
558 hr_utility.trace('p_pcv_information_category='||p_pcv_information_category);
559 hr_utility.trace('p_pcv_information1='||p_pcv_information1);
560 hr_utility.trace('p_pcv_information3='||p_pcv_information3);
561
562 -- Get role/post mapping
563 if p_pcv_information_category = 'PQP_GB_SWF_ROLE_SOURCE' then
564 l_mapping_info_category := 'PQP_GB_SWF_ROLE_MAPPING';
565 else
566 l_mapping_info_category := 'PQP_GB_SWF_POST_MAPPING';
567 end if ;
568
569 begin
570 select pcv_information1
571 into l_mapping_lookup
572 from pqp_configuration_values
573 where business_group_id = p_business_group_id
574 and pcv_information_category = l_mapping_info_category
575 and rownum<2 ;
576 exception
577 when no_data_found then
578 null ;
579 end ;
580
581 hr_utility.trace('l_mapping_lookup='||l_mapping_lookup);
582 hr_utility.trace('p_pcv_information1='||p_pcv_information1);
583
584 if p_pcv_information1 = 'EMP_CAT' and l_mapping_lookup <> 'EMP_CAT' then
585 hr_utility.trace('Raising error for EMP_CAT');
586 fnd_message.set_name('PQP', 'PQP_230246_INCORRECT_LOOKUP');
587 fnd_message.set_token('LOOKUP_NAME', 'EMP_CAT');
588 fnd_message.set_token('SOURCE', 'Assignment Category');
589 fnd_message.raise_error;
590 elsif p_pcv_information1 = 'EMPLOYEE_CATG' and l_mapping_lookup <> 'EMPLOYEE_CATG' then
591 hr_utility.trace('Raising error for EMPLOYEE_CATG');
592 fnd_message.set_name('PQP', 'PQP_230246_INCORRECT_LOOKUP');
593 fnd_message.set_token('LOOKUP_NAME', 'EMPLOYEE_CATG');
594 fnd_message.set_token('SOURCE', 'Employee Category');
595 fnd_message.raise_error;
596 end if;
597
598 hr_utility.trace('Outside error raising section');
599 --
600 -- 1) Segment Name must be provided if Job/Position/Grade KFF
601 -- is selected as source
602 if p_pcv_information1 = 'JOB' and p_pcv_information3 is null then
603 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
604 fnd_message.set_token('FIELD', 'Segment Column');
605 fnd_message.set_token('SOURCE', 'Job Key Flexfield');
606 fnd_message.raise_error;
607 elsif p_pcv_information1 = 'POS' and p_pcv_information3 is null then
608 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
609 fnd_message.set_token('FIELD', 'Segment Column');
610 fnd_message.set_token('SOURCE', 'Position Key Flexfield');
611 fnd_message.raise_error;
612 elsif p_pcv_information1 = 'GRD' and p_pcv_information3 is null then
613 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
614 fnd_message.set_token('FIELD', 'Segment Column');
618 hr_utility.trace('Leaving Post source checks');
615 fnd_message.set_token('SOURCE', 'Grade Key Flexfield');
616 fnd_message.raise_error;
617 end if ;
619 --
620 end chk_role_post_source;
621
622 --
623 --
624 --
625 ------------
626 -- School Workforce Census- Role/Post Mapping Validation
627 -- p_pcv_information1 => Lookup Name
628 -- p_pcv_information2 => Segment Start Value
629 -- p_pcv_information3 => Segment End Value
630 -- p_pcv_information4 => DCSF Role/Post Code
631 -----------
632 procedure chk_role_post_mapping(p_configuration_value_id in number
633 , p_business_group_id in number
634 , p_pcv_information_category in varchar2
635 , p_pcv_information1 in varchar2
636 , p_pcv_information2 in varchar2
637 , p_pcv_information3 in varchar2
638 , p_pcv_information4 in varchar2
639 ) as
640 p_return boolean;
641 l_source varchar2(50);
642 l_source_info_category varchar2(50);
643 begin
644
645 -- Get role/post source
646 if p_pcv_information_category = 'PQP_GB_SWF_ROLE_MAPPING' then
647 l_source_info_category := 'PQP_GB_SWF_ROLE_SOURCE';
648 else
649 l_source_info_category := 'PQP_GB_SWF_POST_SOURCE';
650 end if ;
651
652 begin
653 select pcv_information1
654 into l_source
655 from pqp_configuration_values
656 where business_group_id = p_business_group_id
657 and pcv_information_category = l_source_info_category ;
658 exception
659 when no_data_found then
660 null ;
661 end ;
662
663 --
664 -- 1) If source = Assignment/Employee Category, then lookup name must be 'EMP_CAT'/'EMPLOYEE_CATG'
665 if l_source = 'EMP_CAT' and p_pcv_information1 <> 'EMP_CAT' then
666 fnd_message.set_name('PQP', 'PQP_230246_INCORRECT_LOOKUP');
667 fnd_message.set_token('LOOKUP_NAME', 'EMP_CAT');
668 fnd_message.set_token('SOURCE', 'Assignment Category');
669 fnd_message.raise_error;
670 end if ;
671
672 if l_source = 'EMPLOYEE_CATG' and p_pcv_information1 <> 'EMPLOYEE_CATG' then
673 fnd_message.set_name('PQP', 'PQP_230246_INCORRECT_LOOKUP');
674 fnd_message.set_token('LOOKUP_NAME', 'EMPLOYEE_CATG');
675 fnd_message.set_token('SOURCE', 'Employee Category');
676 fnd_message.raise_error;
677 end if ;
678 --
679 -- 2) While creating multiple configuration records for this oconfiguration type
680 -- user should not be able to create records with different lookup names
681 pqp_gb_swf_validations.chk_unique_lookup_name(p_configuration_value_id => p_configuration_value_id
682 , p_business_group_id => p_business_group_id
683 , p_pcv_information_category => p_pcv_information_category
684 , p_information_column => 'PCV_INFORMATION1'
685 , p_value => p_pcv_information1
686 , p_return => p_return);
687 if (p_return = false) then
688 fnd_message.set_name('PQP', 'PQP_230243_USE_SAME_LOOKUP');
689 fnd_message.raise_error;
690 end if ;
691
692 --
693 -- 3) If source = Grade KFF, and Segment End Value is provided
694 -- Then Segment End Value should not be less than Segment Start Value
695 if l_source = 'GRD' and p_pcv_information3 is not null then
696 if p_pcv_information3 < p_pcv_information2 then
697 fnd_message.set_name('PQP', 'PQP_230242_WRONG_END_VAL');
698 fnd_message.set_token('TYPE', 'Segment');
699 fnd_message.raise_error;
700 end if ;
701 end if ;
702
703
704 -- 4) While creating multiple configuration records for this oconfiguration type
705 -- user should not be able to map same segment value twice
706 pqp_gb_swf_validations.chk_range_unique_mapping(
707 p_configuration_value_id => p_configuration_value_id
708 , p_business_group_id => p_business_group_id
709 , p_pcv_information_category => p_pcv_information_category
710 , p_information_start_column => 'PCV_INFORMATION2'
711 , p_information_end_column => 'PCV_INFORMATION3'
712 , p_value_start => p_pcv_information2
713 , p_value_end => p_pcv_information3
714 , p_return => p_return);
715 if (p_return = false) then
716 fnd_message.set_name('PQP', 'PQP_230244_DUP_RANGE_MAP');
717 fnd_message.raise_error;
718 end if ;
719 --
720 end chk_role_post_mapping;
721
722
723 --
724 ------------
725 -- School Workforce Census- Payscale Validation
726 -- p_pcv_information1 => User PayScale Value
727 -- p_pcv_information2 => DCSF PayScale Value
728 -----------
729 procedure chk_payscale_mapping(p_configuration_value_id in number
730 , p_business_group_id in number
731 , p_pcv_information_category in varchar2
732 , p_pcv_information1 in varchar2
733 , p_pcv_information2 in varchar2
734 ) as
735 p_return boolean;
736 begin
737 -- If the given user payscale is already mapped
738 -- then user should not be able to map it again
739 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
740 , p_business_group_id => p_business_group_id
744 , p_return => p_return);
741 , p_pcv_information_category => p_pcv_information_category
742 , p_information_column => 'PCV_INFORMATION1'
743 , p_value => p_pcv_information1
745 if (p_return = false) then
746 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
747 fnd_message.set_token('Value', 'Pay Scale Value');
748 fnd_message.set_token('DCSF_CODE', 'DCSF Pay Scale Code');
749 fnd_message.raise_error;
750 end if ;
751 --
752 end chk_payscale_mapping;
753
754
755 --
756 --
757 ------------
758 -- School Workforce Census- Regional Spine Source Validation
759 -- p_pcv_information1 => Source(Grade, PayScale or Spine Point, Default)
760 -- p_pcv_information2 => Grade Key Flexfield segment
761 -- p_pcv_information3 => Default Value
762 -----------
763 procedure chk_regional_spine_source(p_configuration_value_id in number
764 , p_business_group_id in number
765 , p_pcv_information_category in varchar2
766 , p_pcv_information1 in varchar2
767 , p_pcv_information2 in varchar2
768 , p_pcv_information3 in varchar2
769 ) as
770 p_return boolean;
771 begin
772 --
773 -- 1) Grade Flexfield Segment Name must be provided if Grade KFF
774 -- is selected as source
775 if p_pcv_information1 = 'GRD' and p_pcv_information2 is null then
776 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
777 fnd_message.set_token('Field', 'Grade Flexfield Segment Column');
778 fnd_message.set_token('SOURCE', 'Grade Key Flexfield');
779 fnd_message.raise_error;
780 end if ;
781
782 --
783 -- 2) Default Value must be provided if Default is selected as source
784 if p_pcv_information1 = 'DEFAULT' and p_pcv_information3 is null then
785 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
786 fnd_message.set_token('Field', 'Default Value');
787 fnd_message.set_token('SOURCE', 'Default');
788 fnd_message.raise_error;
789 end if ;
790 --
791 end chk_regional_spine_source;
792
793
794 ------------
795 -- School Workforce Census- Regional Spine mapping with Grade Validation
796 -- p_pcv_information1 => Lookup Name
797 -- p_pcv_information2 => Segment Start Value
798 -- p_pcv_information3 => Segment End Value
799 -- p_pcv_information4 => DCSF Regional Spine Code
800 -----------
801 procedure chk_regional_spine_map_grade(p_configuration_value_id in number
802 , p_business_group_id in number
803 , p_pcv_information_category in varchar2
804 , p_pcv_information1 in varchar2
805 , p_pcv_information2 in varchar2
806 , p_pcv_information3 in varchar2
807 , p_pcv_information4 in varchar2
808 ) as
809 p_return boolean ;
810 l_field_name varchar2(50);
811 l_count number;
812 begin
813 -- 1) While creating multiple configuration records for this oconfiguration type
814 -- user should not be able to create records with different lookup names
815 pqp_gb_swf_validations.chk_unique_lookup_name(
816 p_configuration_value_id => p_configuration_value_id
817 , p_business_group_id => p_business_group_id
818 , p_pcv_information_category => p_pcv_information_category
819 , p_information_column => 'PCV_INFORMATION1'
820 , p_value => p_pcv_information1
821 , p_return => p_return);
822 if (p_return = false) then
823 fnd_message.set_name('PQP', 'PQP_230243_USE_SAME_LOOKUP');
824 fnd_message.raise_error;
825 end if ;
826
827 -- 2) If the user has given seeded Regional Spine lookup name
828 -- Then he should not be required to give the mapping
829 -- Else Segment Start Value and DCSF Regional Spine Code are mandatory
830
831 if p_pcv_information1 <> g_REG_SPINE_LOOKUP_NAME then
832 if p_pcv_information2 is null or p_pcv_information4 is null then
833 fnd_message.set_name('PQP', 'PQP_230241_ENTER_MAPPING');
834 fnd_message.set_token('DCSF_CODE', 'DCSF Regional Spine Code');
835 fnd_message.set_token('LOOKUP_NAME', g_REG_SPINE_LOOKUP_NAME);
836 fnd_message.raise_error;
837 end if;
838 end if;
839
840 -- 3) If Segment End Value is provided
841 -- Then Segment Start Value should not be less than Segment End Value
842 if p_pcv_information1 <> g_REG_SPINE_LOOKUP_NAME then
843 if p_pcv_information3 is not null then
844 if p_pcv_information3 < p_pcv_information2 then
845 fnd_message.set_name('PQP', 'PQP_230242_WRONG_END_VAL');
846 fnd_message.set_token('TYPE', 'Segment');
847 fnd_message.raise_error;
848 end if;
849 end if ;
850
851
852 -- 4) While creating multiple configuration records for this oconfiguration type
853 -- user should not be able to map same segment value twice
854 pqp_gb_swf_validations.chk_range_unique_mapping(
855 p_configuration_value_id => p_configuration_value_id
856 , p_business_group_id => p_business_group_id
857 , p_pcv_information_category => p_pcv_information_category
858 , p_information_start_column => 'PCV_INFORMATION2'
859 , p_information_end_column => 'PCV_INFORMATION3'
863 if (p_return = false) then
860 , p_value_start => p_pcv_information2
861 , p_value_end => p_pcv_information3
862 , p_return => p_return);
864 fnd_message.set_name('PQP', 'PQP_230244_DUP_RANGE_MAP');
865 fnd_message.raise_error;
866 end if ;
867 else
868 -- seeded lookup used
869 -- check if there already exists a row with seeded lookup
870 -- then raise error saying mapping already defined
871 begin
872 select 1
873 into l_count
874 from pqp_configuration_values
875 where pcv_information_category = p_pcv_information_category
876 and business_group_id = p_business_group_id
877 and (p_configuration_value_id is null
878 or configuration_value_id <> p_configuration_value_id)
879 and pcv_information1 = p_pcv_information1 ;
880 exception
881 when others then
882 null ;
883 end;
884
885 if l_count is not null then
886 fnd_message.set_name('PQP', 'PQP_230240_DUPLICATE_MAPPING');
887 fnd_message.raise_error;
888 end if;
889 end if ;
890 --
891 end chk_regional_spine_map_grade ;
892
893
894 ------------
895 -- School Workforce Census- Regional Spine mapping with PayScale Validation
896 -- p_pcv_information1 => Pay Scale Name
897 -- p_pcv_information2 => Spine Point Start Value
898 -- p_pcv_information3 => Spine Point End Value
899 -- p_pcv_information4 => DCSF Regional Spine Code
900 -----------
901 procedure chk_regional_spine_map_pyscl(p_configuration_value_id in number
902 , p_business_group_id in number
903 , p_pcv_information_category in varchar2
904 , p_pcv_information1 in varchar2
905 , p_pcv_information2 in varchar2
906 , p_pcv_information3 in varchar2
907 , p_pcv_information4 in varchar2
908 ) as
909 p_return boolean ;
910 l_field_name varchar2(50);
911 begin
912 -- 1) If Spine Point End Value is provided
913 -- Then Spine Point Start Value should not be less than Spine Point End Value
914 if p_pcv_information3 is not null then
915 if p_pcv_information3 < p_pcv_information2 then
916 fnd_message.set_name('PQP', 'PQP_230242_WRONG_END_VAL');
917 fnd_message.set_token('TYPE', 'Spine Point');
918 fnd_message.raise_error;
919 end if;
920 end if ;
921
922 -- 2) While creating multiple configuration records for this oconfiguration type
923 -- user should not be able to map same spine point value twice
924 pqp_gb_swf_validations.chk_spine_pt_unique_mapping(
925 p_configuration_value_id => p_configuration_value_id
926 , p_business_group_id => p_business_group_id
927 , p_pcv_information_category => p_pcv_information_category
928 , p_payscale_column => 'PCV_INFORMATION1'
929 , p_information_start_column => 'PCV_INFORMATION2'
930 , p_information_end_column => 'PCV_INFORMATION3'
931 , p_payscale_value => p_pcv_information1
932 , p_value_start => p_pcv_information2
933 , p_value_end => p_pcv_information3
934 , p_return => p_return);
935
936 if (p_return = false) then
937 fnd_message.set_name('PQP', 'PQP_230249_DUP_SPINE_MAP');
938 fnd_message.raise_error;
939 end if ;
940 --
941 end chk_regional_spine_map_pyscl ;
942
943
944 ------------
945 -- School Workforce Census- Spine Point Validation
946 -- p_pcv_information1 => User Pay Scale
947 -- p_pcv_information2 => User Spine Point Value
948 -- p_pcv_information3 => DCSF Spine Point Value
949 -----------
950 procedure chk_spine_point_mapping(p_configuration_value_id in number
951 , p_business_group_id in number
952 , p_pcv_information_category in varchar2
953 , p_pcv_information1 in varchar2
954 , p_pcv_information2 in varchar2
955 , p_pcv_information3 in varchar2
956 ) as
957 p_return boolean;
958 begin
959 -- If the given user spine point is already mapped
960 -- then user should not be able to map it again
961 pqp_gb_swf_validations.chk_single_unique_mapping(
962 p_configuration_value_id => p_configuration_value_id
963 , p_business_group_id => p_business_group_id
964 , p_pcv_information_category => p_pcv_information_category
965 , p_information_column => 'PCV_INFORMATION2'
966 , p_value => p_pcv_information2
967 , p_return => p_return);
968 if (p_return = false) then
969 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
970 fnd_message.set_token('VALUE', 'Spine Point Value');
971 fnd_message.set_token('DCSF_CODE', 'DCSF Spine Point Code');
972 fnd_message.raise_error;
973 end if ;
974
975 --
976 end chk_spine_point_mapping ;
977
978
979 ------------
980 -- School Workforce Census- Absence Code Validation
981 -- p_pcv_information1 => Source ( Absence Type/category/reason)
982 -- p_pcv_information3 => Absence Category/Reason Value
983 -- p_pcv_information4 => Absence Type Value
987 , p_business_group_id in number
984 -- p_pcv_information5 => DCSF Absence Category
985 -----------
986 procedure chk_absence_code_mapping(p_configuration_value_id in number
988 , p_pcv_information_category in varchar2
989 , p_pcv_information1 in varchar2
990 , p_pcv_information3 in varchar2
991 , p_pcv_information4 in varchar2
992 , p_pcv_information5 in varchar2
993 ) as
994 p_return boolean;
995 l_information_column varchar2(50);
996 l_value varchar2(50);
997 l_message_token varchar2(50);
998 begin
999 -- 1) While creating multiple configuration records for this oconfiguration type
1000 -- user should not be able to create records with different source names
1001 pqp_gb_swf_validations.chk_unique_lookup_name(
1002 p_configuration_value_id => p_configuration_value_id
1003 , p_business_group_id => p_business_group_id
1004 , p_pcv_information_category => p_pcv_information_category
1005 , p_information_column => 'PCV_INFORMATION1'
1006 , p_value => p_pcv_information1
1007 , p_return => p_return);
1008 if (p_return = false) then
1009 fnd_message.set_name('PQP', 'PQP_230245_USE_SAME_SOURCE');
1010 fnd_message.raise_error;
1011 end if ;
1012
1013
1014 -- 2) If Absence Category/Reason is selected as source
1015 -- Then Absence Category/Reason Value must be provided
1016 if p_pcv_information1 = 'ABSENCE_CATEGORY' and p_pcv_information3 is null then
1017 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1018 fnd_message.set_token('FIELD', 'Absence Category/Reason Value');
1019 fnd_message.set_token('SOURCE', 'Absence Category');
1020 fnd_message.raise_error;
1021 elsif p_pcv_information1 = 'ABSENCE_REASON' and p_pcv_information3 is null then
1022 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1023 fnd_message.set_token('FIELD', 'Absence Category/Reason Value');
1024 fnd_message.set_token('SOURCE', 'Absence Reason');
1025 fnd_message.raise_error;
1026 end if ;
1027
1028 -- 3) If Absence Type is selected as source
1029 -- Then Absence Type Value must be provided
1030 if p_pcv_information1 = 'ABSENCE_TYPE' and p_pcv_information4 is null then
1031 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1032 fnd_message.set_token('FIELD', 'Absence Type Value');
1033 fnd_message.set_token('SOURCE', 'Absence Type');
1034 fnd_message.raise_error;
1035 end if ;
1036
1037 -- 4) If the given user Absence Category/Type/reason value is already mapped
1038 -- then user should not be able to map it again
1039 if p_pcv_information1 = 'ABSENCE_CATEGORY' then
1040 l_information_column := 'PCV_INFORMATION3' ;
1041 l_value := p_pcv_information3;
1042 l_message_token := 'Absence Category Value';
1043 elsif p_pcv_information1 = 'ABSENCE_REASON' then
1044 l_information_column := 'PCV_INFORMATION3' ;
1045 l_value := p_pcv_information3;
1046 l_message_token := 'Absence Reason Value';
1047 elsif p_pcv_information1 = 'ABSENCE_TYPE' then
1048 l_information_column := 'PCV_INFORMATION4' ;
1049 l_value := p_pcv_information4;
1050 l_message_token := 'Absence Type Value';
1051 end if ;
1052
1053 pqp_gb_swf_validations.chk_single_unique_mapping(
1054 p_configuration_value_id => p_configuration_value_id
1055 , p_business_group_id => p_business_group_id
1056 , p_pcv_information_category => p_pcv_information_category
1057 , p_information_column => l_information_column
1058 , p_value => l_value
1059 , p_return => p_return);
1060 if (p_return = false) then
1061 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
1062 fnd_message.set_token('VALUE', l_message_token);
1063 fnd_message.set_token('DCSF_CODE', 'DCSF Absence category');
1064 fnd_message.raise_error;
1065 end if ;
1066
1067 --
1068 end chk_absence_code_mapping ;
1069
1070 ------------
1071 -- School Workforce Census- Qualification Code Validation
1072 -- p_pcv_information1 => Source ( Qualification Type/category)
1073 -- p_pcv_information3 => Qualification Category Value
1074 -- p_pcv_information4 => Qualification Type Value
1075 -- p_pcv_information5 => DCSF Qualification Code
1076 -----------
1077 procedure chk_qualification_code_map(p_configuration_value_id in number
1078 , p_business_group_id in number
1079 , p_pcv_information_category in varchar2
1080 , p_pcv_information1 in varchar2
1081 , p_pcv_information3 in varchar2
1082 , p_pcv_information4 in varchar2
1083 , p_pcv_information5 in varchar2
1084 ) as
1085 p_return boolean;
1086 l_information_column varchar2(50);
1087 l_value varchar2(50);
1088 l_message_token varchar2(50);
1089 begin
1090 -- 1) While creating multiple configuration records for this oconfiguration type
1091 -- user should not be able to create records with different source names
1092 pqp_gb_swf_validations.chk_unique_lookup_name(
1093 p_configuration_value_id => p_configuration_value_id
1094 , p_business_group_id => p_business_group_id
1095 , p_pcv_information_category => p_pcv_information_category
1096 , p_information_column => 'PCV_INFORMATION1'
1100 fnd_message.set_name('PQP', 'PQP_230245_USE_SAME_SOURCE');
1097 , p_value => p_pcv_information1
1098 , p_return => p_return);
1099 if (p_return = false) then
1101 fnd_message.raise_error;
1102 end if ;
1103
1104
1105 -- 2) If Qualification Category is selected as source
1106 -- Then Qualification Category Value must be provided
1107 if p_pcv_information1 = 'QUALIFICATION_CATEGORY' and p_pcv_information3 is null then
1108 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1109 fnd_message.set_token('FIELD', 'Qualification Category Value');
1110 fnd_message.set_token('SOURCE', 'Qualification Category');
1111 fnd_message.raise_error;
1112 end if ;
1113
1114 -- 3) If Qualification Type is selected as source
1115 -- Then Qualification Type Value must be provided
1116 if p_pcv_information1 = 'QUALIFICATION_TYPE' and p_pcv_information4 is null then
1117 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1118 fnd_message.set_token('FIELD', 'Qualification Type Value');
1119 fnd_message.set_token('SOURCE', 'Qualification Type');
1120 fnd_message.raise_error;
1121 end if ;
1122
1123 -- 4) If the given user Qualification Category/Type value is already mapped
1124 -- then user should not be able to map it again
1125 if p_pcv_information1 = 'QUALIFICATION_CATEGORY' then
1126 l_information_column := 'PCV_INFORMATION3' ;
1127 l_value := p_pcv_information3;
1128 l_message_token := 'Qualification Category Value';
1129 elsif p_pcv_information1 = 'QUALIFICATION_TYPE' then
1130 l_information_column := 'PCV_INFORMATION4' ;
1131 l_value := p_pcv_information4;
1132 l_message_token := 'Qualification Type Value';
1133 end if ;
1134
1135 pqp_gb_swf_validations.chk_single_unique_mapping(
1136 p_configuration_value_id => p_configuration_value_id
1137 , p_business_group_id => p_business_group_id
1138 , p_pcv_information_category => p_pcv_information_category
1139 , p_information_column => l_information_column
1140 , p_value => l_value
1141 , p_return => p_return);
1142 if (p_return = false) then
1143 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
1144 fnd_message.set_token('VALUE', l_message_token);
1145 fnd_message.set_token('DCSF_CODE', 'DCSF Qualification Code');
1146 fnd_message.raise_error;
1147 end if ;
1148
1149 --
1150 end chk_qualification_code_map ;
1151
1152 ------------
1153 -- School Workforce Census- Subject Code Validation
1154 -- p_pcv_information1 => User Subject Value
1155 -- p_pcv_information2 => DCSF Subject Code
1156 -----------
1157 procedure chk_subject_code_map(p_configuration_value_id in number
1158 , p_business_group_id in number
1159 , p_pcv_information_category in varchar2
1160 , p_pcv_information1 in varchar2
1161 , p_pcv_information2 in varchar2
1162 ) as
1163 p_return boolean ;
1164 begin
1165 -- If the given user subject code is already mapped
1166 -- then user should not be able to map it again
1167 pqp_gb_swf_validations.chk_single_unique_mapping(p_configuration_value_id => p_configuration_value_id
1168 , p_business_group_id => p_business_group_id
1169 , p_pcv_information_category => p_pcv_information_category
1170 , p_information_column => 'PCV_INFORMATION1'
1171 , p_value => p_pcv_information1
1172 , p_return => p_return);
1173 if (p_return = false) then
1174 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
1175 fnd_message.set_token('VALUE', 'Subject Code');
1176 fnd_message.set_token('DCSF_CODE', 'DCSF Subject Code');
1177 fnd_message.raise_error;
1178 end if ;
1179 --
1180 end chk_subject_code_map ;
1181
1182 ------------
1183 -- School Workforce Census- Hours Validation
1184 -- p_pcv_information1 => Source (Assignemnt, PQP Contract Type)
1185 -- p_pcv_information2 => Contract Type
1186 -- p_pcv_information3 => Hours per Week column
1187 -- p_pcv_information4 => Weeks per Year Column
1188 -- p_pcv_information5 => Staff Category
1189 -- p_pcv_information6 => Default Weeks per Year
1190 -----------
1191 procedure chk_hours(p_configuration_value_id in number
1192 , p_business_group_id in number
1193 , p_pcv_information_category in varchar2
1194 , p_pcv_information1 in varchar2
1195 , p_pcv_information2 in varchar2
1196 , p_pcv_information3 in varchar2
1197 , p_pcv_information4 in varchar2
1198 , p_pcv_information5 in varchar2
1199 , p_pcv_information6 in varchar2
1200 , p_pcv_information7 in varchar2
1201 , p_pcv_information8 in varchar2
1202 ) as
1203 p_return boolean;
1204 begin
1205 -- 1) While creating multiple configuration records for this oconfiguration type
1206 -- user should not be able to create records with different source names
1207 pqp_gb_swf_validations.chk_unique_lookup_name(
1208 p_configuration_value_id => p_configuration_value_id
1209 , p_business_group_id => p_business_group_id
1210 , p_pcv_information_category => p_pcv_information_category
1211 , p_information_column => 'PCV_INFORMATION1'
1215 fnd_message.set_name('PQP', 'PQP_230245_USE_SAME_SOURCE');
1212 , p_value => p_pcv_information1
1213 , p_return => p_return);
1214 if (p_return = false) then
1216 fnd_message.raise_error;
1217 end if ;
1218
1219 -- 2) Check for mandatory columns as per selected source columns
1220 if p_pcv_information1 = 'CONTRACT_TYPE' then
1221 if p_pcv_information3 is null then
1222 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1223 fnd_message.set_token('FIELD', 'Hours Per Week formula name');
1224 fnd_message.set_token('SOURCE', 'PQP Contract Type');
1225 fnd_message.raise_error;
1226 elsif p_pcv_information4 is null then
1227 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1228 fnd_message.set_token('FIELD', 'Weeks Per Year source');
1229 fnd_message.set_token('SOURCE', 'PQP Contract Type');
1230 fnd_message.raise_error;
1231 end if;
1232
1233 if p_pcv_information4 = 'FORMULA' then
1234 if p_pcv_information5 is null then
1235 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1236 fnd_message.set_token('FIELD', 'Weeks Per Year formula name');
1237 fnd_message.set_token('SOURCE', 'PQP Contract Type');
1238 fnd_message.raise_error;
1239 end if;
1240 else
1241 if p_pcv_information6 is null then
1242 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1243 fnd_message.set_token('FIELD', 'Weeks Per Year column name');
1244 fnd_message.set_token('SOURCE', 'PQP Contract Type');
1245 fnd_message.raise_error;
1246 end if ;
1247 end if ;
1248 elsif p_pcv_information1 = 'ASG' then
1249 if p_pcv_information7 is null then
1250 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1251 fnd_message.set_token('FIELD', 'Staff Category');
1252 fnd_message.set_token('SOURCE', 'Assignment Hours');
1253 fnd_message.raise_error;
1254 elsif p_pcv_information8 is null then
1255 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1256 fnd_message.set_token('FIELD', 'Default Weeks Per Year');
1257 fnd_message.set_token('SOURCE', 'Assignment Hours');
1258 fnd_message.raise_error;
1259 end if;
1260 end if ;
1261
1262 -- 3) When source is PQP Contract Types, then user should not be able to map
1263 -- same contract type twice
1264 if p_pcv_information1 = 'CONTRACT_TYPE' then
1265 pqp_gb_swf_validations.chk_hours_cntrct_tp_unq_map(
1266 p_configuration_value_id => p_configuration_value_id
1267 , p_business_group_id => p_business_group_id
1268 , p_pcv_information_category => p_pcv_information_category
1269 , p_information_column => 'PCV_INFORMATION2'
1270 , p_value => p_pcv_information2
1271 , p_return => p_return);
1272 if (p_return = false) then
1273 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
1274 fnd_message.set_token('VALUE', 'Contract Type');
1275 fnd_message.set_token('DCSF_CODE', 'PQP_CONTRACT_TYPES UDT Column Name');
1276 fnd_message.raise_error;
1277 end if ;
1278 end if ;
1279
1280 -- 4) When source is Assignment Hours, then user should not be able to map
1281 -- same Staff Category twice
1282 pqp_gb_swf_validations.chk_single_unique_mapping(
1283 p_configuration_value_id => p_configuration_value_id
1284 , p_business_group_id => p_business_group_id
1285 , p_pcv_information_category => p_pcv_information_category
1286 , p_information_column => 'PCV_INFORMATION7'
1287 , p_value => p_pcv_information7
1288 , p_return => p_return);
1289 if (p_return = false) then
1290 fnd_message.set_name('PQP', 'PQP_230239_DUPLICATE_MAPPING');
1291 fnd_message.set_token('VALUE', 'Staff Category');
1292 fnd_message.set_token('DCSF_CODE', 'Default Weeks Per Year Value');
1293 fnd_message.raise_error;
1294 end if ;
1295 end chk_hours;
1296
1297
1298 ------------
1299 -- School Workforce Census- FTE Hours Validation
1300 -- p_pcv_information1 => Source (Assignment - Budget FTE Value, Calculated)
1301 -- p_pcv_information2 => User formula name
1302 -----------
1303 procedure chk_fte_hours(p_configuration_value_id in number
1304 , p_business_group_id in number
1305 , p_pcv_information_category in varchar2
1306 , p_pcv_information1 in varchar2
1307 , p_pcv_information2 in varchar2
1308 ) as
1309 begin
1310 if p_pcv_information1 = 'CAL' and p_pcv_information2 is null then
1311 fnd_message.set_name('PQP', 'PQP_230238_FIELD_MANDATORY');
1312 fnd_message.set_token('FIELD', 'User Formula Name');
1313 fnd_message.set_token('SOURCE', 'Calculated');
1314 fnd_message.raise_error;
1315 end if ;
1316 --
1317 end chk_fte_hours;
1318 --
1319
1320 PROCEDURE create_configuration_value_bp
1321 (p_configuration_value_id in number
1322 ,p_effective_date in date
1323 ,p_business_group_id in number
1324 ,p_legislation_code in varchar2
1325 ,p_pcv_information_category in varchar2
1326 ,p_pcv_information1 in varchar2
1330 ,p_pcv_information5 in varchar2
1327 ,p_pcv_information2 in varchar2
1328 ,p_pcv_information3 in varchar2
1329 ,p_pcv_information4 in varchar2
1331 ,p_pcv_information6 in varchar2
1332 ,p_pcv_information7 in varchar2
1333 ,p_pcv_information8 in varchar2
1334 ,p_pcv_information9 in varchar2
1335 ,p_pcv_information10 in varchar2
1336 ,p_pcv_information11 in varchar2
1337 ,p_pcv_information12 in varchar2
1338 ,p_pcv_information13 in varchar2
1339 ,p_pcv_information14 in varchar2
1340 ,p_pcv_information15 in varchar2
1341 ,p_pcv_information16 in varchar2
1342 ,p_pcv_information17 in varchar2
1343 ,p_pcv_information18 in varchar2
1344 ,p_pcv_information19 in varchar2
1345 ,p_pcv_information20 in varchar2
1346 ,p_configuration_name in varchar2
1347
1348 ) as
1349
1350 l_proc varchar2(56);
1351 p_return boolean ;
1352 l_count number;
1353 l_role_source varchar2(100);
1354 BEGIN
1355 l_proc:='PQP_GB_CONFIGURATION_VALUE.CREATE_CONFIGURATION_VALUE_BP';
1356
1357 if g_debug is null then
1358 g_debug := Hr_utility.debug_enabled;
1359 end if;
1360
1361 if g_debug then
1362 hr_utility.set_location('Entering:'||l_proc,10);
1363 end if;
1364
1365 /* Create private procedures to validate
1366 pcv_information_category you are interested in */
1367
1368 -- hr_utility.trace_on(null,'CNFTRC');
1369 case (p_pcv_information_category)
1370 when 'PQP_GB_PENSERV_SCHEME_MAP_INFO'then
1371 chk_pension_scheme_mapping(p_pcv_information2 =>p_pcv_information2 --Penserv Scheme
1372 ,p_pcv_information3 =>p_pcv_information3 --Partner Scheme
1373 );
1374 when 'PQP_GB_SWF_TEACHER_NUM' then
1375 chk_teacher_number(p_configuration_value_id => p_configuration_value_id
1376 , p_pcv_information1 => p_pcv_information1
1377 , p_pcv_information3 => p_pcv_information3
1378 , p_pcv_information4 => p_pcv_information4
1379 );
1380 when 'PQP_GB_SWF_ETHNIC_CODES' then
1381 chk_ethnic_origin(p_configuration_value_id => p_configuration_value_id
1382 , p_business_group_id => p_business_group_id
1383 , p_pcv_information_category => p_pcv_information_category
1384 , p_pcv_information1 => p_pcv_information1
1385 );
1386 when 'PQP_GB_SWF_QTS_MAPPING' then
1387 chk_qts_mapping(p_configuration_value_id => p_configuration_value_id
1388 , p_business_group_id => p_business_group_id
1389 , p_pcv_information_category => p_pcv_information_category
1390 , p_pcv_information1 => p_pcv_information1
1391 , p_pcv_information2 => p_pcv_information2
1392 , p_pcv_information3 => p_pcv_information3
1393 , p_pcv_information4 => p_pcv_information4
1394 );
1395 when 'PQP_GB_SWF_QTS_ROUTE_MAPPING' then
1396 chk_qts_mapping(p_configuration_value_id => p_configuration_value_id
1397 , p_business_group_id => p_business_group_id
1398 , p_pcv_information_category => p_pcv_information_category
1399 , p_pcv_information1 => p_pcv_information1
1400 , p_pcv_information2 => p_pcv_information2
1401 , p_pcv_information3 => p_pcv_information3
1402 , p_pcv_information4 => p_pcv_information4
1403 );
1404 when 'PQP_GB_SWF_HLTA_STATUS_SRC' then
1405 chk_hlta_source(p_configuration_value_id => p_configuration_value_id
1406 , p_business_group_id => p_business_group_id
1407 , p_pcv_information_category => p_pcv_information_category
1408 , p_pcv_information1 => p_pcv_information1
1409 , p_pcv_information3 => p_pcv_information3
1410 , p_pcv_information4 => p_pcv_information4
1411 , p_pcv_information5 => p_pcv_information5
1412 );
1413 when 'PQP_GB_SWF_HLTA_STATUS_MAPPING' then
1414 chk_hlta_mapping(p_configuration_value_id => p_configuration_value_id
1415 , p_business_group_id => p_business_group_id
1416 , p_pcv_information_category => p_pcv_information_category
1417 , p_pcv_information1 => p_pcv_information1
1418 , p_pcv_information2 => p_pcv_information2
1419 , p_pcv_information3 => p_pcv_information3
1420 );
1421 when 'PQP_GB_SWF_CNTRT_ARRIVAL_DATE' then
1422 chk_arrival_date(p_configuration_value_id => p_configuration_value_id
1423 , p_business_group_id => p_business_group_id
1424 , p_pcv_information_category => p_pcv_information_category
1425 , p_pcv_information1 => p_pcv_information1
1426 , p_pcv_information2 => p_pcv_information2
1427 );
1428 when 'PQP_GB_SWF_CONTRACT_TYPE' then
1432 , p_pcv_information1 => p_pcv_information1
1429 chk_contract_type(p_configuration_value_id => p_configuration_value_id
1430 , p_business_group_id => p_business_group_id
1431 , p_pcv_information_category => p_pcv_information_category
1433 , p_pcv_information3 => p_pcv_information3
1434 , p_pcv_information4 => p_pcv_information4
1435 , p_pcv_information5 => p_pcv_information5
1436 );
1437 when 'PQP_GB_SWF_ORIGIN_MAPPING' then
1438 chk_origin_mapping(p_configuration_value_id => p_configuration_value_id
1439 , p_business_group_id => p_business_group_id
1440 , p_pcv_information_category => p_pcv_information_category
1441 , p_pcv_information1 => p_pcv_information1
1442 , p_pcv_information2 => p_pcv_information2
1443 , p_pcv_information3 => p_pcv_information3
1444 );
1445 when 'PQP_GB_SWF_DESTINATION_MAPPING' then
1446 chk_destination_mapping(p_configuration_value_id => p_configuration_value_id
1447 , p_business_group_id => p_business_group_id
1448 , p_pcv_information_category => p_pcv_information_category
1449 , p_pcv_information1 => p_pcv_information1
1450 , p_pcv_information2 => p_pcv_information2
1451 , p_pcv_information3 => p_pcv_information3
1452 );
1453 when 'PQP_GB_SWF_ROLE_SOURCE' then
1454 chk_role_post_source(p_configuration_value_id => p_configuration_value_id
1455 , p_business_group_id => p_business_group_id
1456 , p_pcv_information_category => p_pcv_information_category
1457 , p_pcv_information1 => p_pcv_information1
1458 , p_pcv_information3 => p_pcv_information3
1459 );
1460 when 'PQP_GB_SWF_ROLE_MAPPING' then
1461 chk_role_post_mapping(p_configuration_value_id => p_configuration_value_id
1462 , p_business_group_id => p_business_group_id
1463 , p_pcv_information_category => p_pcv_information_category
1464 , p_pcv_information1 => p_pcv_information1
1465 , p_pcv_information2 => p_pcv_information2
1466 , p_pcv_information3 => p_pcv_information3
1467 , p_pcv_information4 => p_pcv_information4
1468 ) ;
1469 when 'PQP_GB_SWF_POST_SOURCE' then
1470 chk_role_post_source(p_configuration_value_id => p_configuration_value_id
1471 , p_business_group_id => p_business_group_id
1472 , p_pcv_information_category => p_pcv_information_category
1473 , p_pcv_information1 => p_pcv_information1
1474 , p_pcv_information3 => p_pcv_information3
1475 );
1476 when 'PQP_GB_SWF_POST_MAPPING' then
1477 chk_role_post_mapping(p_configuration_value_id => p_configuration_value_id
1478 , p_business_group_id => p_business_group_id
1479 , p_pcv_information_category => p_pcv_information_category
1480 , p_pcv_information1 => p_pcv_information1
1481 , p_pcv_information2 => p_pcv_information2
1482 , p_pcv_information3 => p_pcv_information3
1483 , p_pcv_information4 => p_pcv_information4
1484 ) ;
1485 when 'PQP_GB_SWF_HOURS' then
1486 chk_hours(p_configuration_value_id => p_configuration_value_id
1487 , p_business_group_id => p_business_group_id
1488 , p_pcv_information_category => p_pcv_information_category
1489 , p_pcv_information1 => p_pcv_information1
1490 , p_pcv_information2 => p_pcv_information2
1491 , p_pcv_information3 => p_pcv_information3
1492 , p_pcv_information4 => p_pcv_information4
1493 , p_pcv_information5 => p_pcv_information5
1494 , p_pcv_information6 => p_pcv_information6
1495 , p_pcv_information7 => p_pcv_information7
1496 , p_pcv_information8 => p_pcv_information8
1497 ) ;
1498 when 'PQP_GB_SWF_FTE_HOURS' then
1499 chk_fte_hours(p_configuration_value_id => p_configuration_value_id
1500 , p_business_group_id => p_business_group_id
1501 , p_pcv_information_category => p_pcv_information_category
1502 , p_pcv_information1 => p_pcv_information1
1503 , p_pcv_information2 => p_pcv_information2
1504 ) ;
1505 when 'PQP_GB_SWF_PAY_SCALE_MAPPING' then
1506 chk_payscale_mapping(p_configuration_value_id => p_configuration_value_id
1507 , p_business_group_id => p_business_group_id
1508 , p_pcv_information_category => p_pcv_information_category
1509 , p_pcv_information1 => p_pcv_information1
1510 , p_pcv_information2 => p_pcv_information2
1511 ) ;
1512 when 'PQP_GB_SWF_REG_SPINE_SRC' then
1513 chk_regional_spine_source(p_configuration_value_id => p_configuration_value_id
1514 , p_business_group_id => p_business_group_id
1515 , p_pcv_information_category => p_pcv_information_category
1516 , p_pcv_information1 => p_pcv_information1
1517 , p_pcv_information2 => p_pcv_information2
1521 chk_regional_spine_map_grade(p_configuration_value_id => p_configuration_value_id
1518 , p_pcv_information3 => p_pcv_information3
1519 ) ;
1520 when 'PQP_GB_SWF_REG_SPINE_MAP_GRD' then
1522 , p_business_group_id => p_business_group_id
1523 , p_pcv_information_category => p_pcv_information_category
1524 , p_pcv_information1 => p_pcv_information1
1525 , p_pcv_information2 => p_pcv_information2
1526 , p_pcv_information3 => p_pcv_information3
1527 , p_pcv_information4 => p_pcv_information4
1528 ) ;
1529 when 'PQP_GB_SWF_REG_SPINE_MAP_PYSCL' then
1530 chk_regional_spine_map_pyscl(p_configuration_value_id => p_configuration_value_id
1531 , p_business_group_id => p_business_group_id
1532 , p_pcv_information_category => p_pcv_information_category
1533 , p_pcv_information1 => p_pcv_information1
1534 , p_pcv_information2 => p_pcv_information2
1535 , p_pcv_information3 => p_pcv_information3
1536 , p_pcv_information4 => p_pcv_information4
1537 ) ;
1538 when 'PQP_GB_SWF_SPINE_POINT_MAPPING' then
1539 chk_spine_point_mapping(p_configuration_value_id => p_configuration_value_id
1540 , p_business_group_id => p_business_group_id
1541 , p_pcv_information_category => p_pcv_information_category
1542 , p_pcv_information1 => p_pcv_information1
1543 , p_pcv_information2 => p_pcv_information2
1544 , p_pcv_information3 => p_pcv_information3
1545 ) ;
1546 when 'PQP_GB_SWF_ABSENCE_CODE' then
1547 chk_absence_code_mapping(p_configuration_value_id => p_configuration_value_id
1548 , p_business_group_id => p_business_group_id
1549 , p_pcv_information_category => p_pcv_information_category
1550 , p_pcv_information1 => p_pcv_information1
1551 , p_pcv_information3 => p_pcv_information3
1552 , p_pcv_information4 => p_pcv_information4
1553 , p_pcv_information5 => p_pcv_information5
1554 ) ;
1555 when 'PQP_GB_SWF_QUAL_CODE_MAP' then
1556 chk_qualification_code_map(p_configuration_value_id => p_configuration_value_id
1557 , p_business_group_id => p_business_group_id
1558 , p_pcv_information_category => p_pcv_information_category
1559 , p_pcv_information1 => p_pcv_information1
1560 , p_pcv_information3 => p_pcv_information3
1561 , p_pcv_information4 => p_pcv_information4
1562 , p_pcv_information5 => p_pcv_information5
1563 ) ;
1564 when 'PQP_GB_SWF_QUAL_SUBJECT_MAP' then
1565 chk_subject_code_map(p_configuration_value_id => p_configuration_value_id
1566 , p_business_group_id => p_business_group_id
1567 , p_pcv_information_category => p_pcv_information_category
1568 , p_pcv_information1 => p_pcv_information1
1569 , p_pcv_information2 => p_pcv_information2
1570 ) ;
1571 else
1572 null;
1573 end case;
1574
1575
1576 if g_debug then
1577 hr_utility.set_location('Leaving:'||l_proc,20);
1578 end if;
1579
1580 END;
1581
1582 procedure update_configuration_value_bp
1583 (p_configuration_value_id in number
1584 ,p_effective_date in date
1585 ,p_business_group_id in number
1586 ,p_legislation_code in varchar2
1587 ,p_pcv_information_category in varchar2
1588 ,p_pcv_information1 in varchar2
1589 ,p_pcv_information2 in varchar2
1590 ,p_pcv_information3 in varchar2
1591 ,p_pcv_information4 in varchar2
1592 ,p_pcv_information5 in varchar2
1593 ,p_pcv_information6 in varchar2
1594 ,p_pcv_information7 in varchar2
1595 ,p_pcv_information8 in varchar2
1596 ,p_pcv_information9 in varchar2
1597 ,p_pcv_information10 in varchar2
1598 ,p_pcv_information11 in varchar2
1599 ,p_pcv_information12 in varchar2
1600 ,p_pcv_information13 in varchar2
1601 ,p_pcv_information14 in varchar2
1602 ,p_pcv_information15 in varchar2
1603 ,p_pcv_information16 in varchar2
1604 ,p_pcv_information17 in varchar2
1605 ,p_pcv_information18 in varchar2
1606 ,p_pcv_information19 in varchar2
1607 ,p_pcv_information20 in varchar2
1608 ,p_object_version_number in number
1609 ,p_configuration_name in varchar2
1610 )as
1611 l_proc varchar2(56);
1612 p_return boolean ;
1613 l_count number;
1614 l_role_source varchar2(100);
1615 BEGIN
1616 if g_debug is null then
1617 g_debug := Hr_utility.debug_enabled;
1618 end if;
1622 hr_utility.set_location('Entering:'||l_proc,10);
1619 l_proc:='PQP_GB_CONFIGURATION_VALUE.UPDATE_CONFIGURATION_VALUE_BP';
1620
1621 if g_debug then
1623 end if;
1624
1625
1626 /* Create private procedures to validate
1627 pcv_information_category you are interested in */
1628
1629 case (p_pcv_information_category)
1630 when 'PQP_GB_PENSERV_SCHEME_MAP_INFO'then
1631 chk_pension_scheme_mapping(p_pcv_information2 =>p_pcv_information2 --Penserv Scheme
1632 ,p_pcv_information3 =>p_pcv_information3 --Partner Scheme
1633 );
1634 when 'PQP_GB_SWF_TEACHER_NUM' then
1635 chk_teacher_number(p_configuration_value_id => p_configuration_value_id
1636 , p_pcv_information1 => p_pcv_information1
1637 , p_pcv_information3 => p_pcv_information3
1638 , p_pcv_information4 => p_pcv_information4
1639 );
1640 when 'PQP_GB_SWF_ETHNIC_CODES' then
1641 chk_ethnic_origin(p_configuration_value_id => p_configuration_value_id
1642 , p_business_group_id => p_business_group_id
1643 , p_pcv_information_category => p_pcv_information_category
1644 , p_pcv_information1 => p_pcv_information1
1645 );
1646 when 'PQP_GB_SWF_QTS_MAPPING' then
1647 chk_qts_mapping(p_configuration_value_id => p_configuration_value_id
1648 , p_business_group_id => p_business_group_id
1649 , p_pcv_information_category => p_pcv_information_category
1650 , p_pcv_information1 => p_pcv_information1
1651 , p_pcv_information2 => p_pcv_information2
1652 , p_pcv_information3 => p_pcv_information3
1653 , p_pcv_information4 => p_pcv_information4
1654 );
1655 when 'PQP_GB_SWF_QTS_ROUTE_MAPPING' then
1656 chk_qts_mapping(p_configuration_value_id => p_configuration_value_id
1657 , p_business_group_id => p_business_group_id
1658 , p_pcv_information_category => p_pcv_information_category
1659 , p_pcv_information1 => p_pcv_information1
1660 , p_pcv_information2 => p_pcv_information2
1661 , p_pcv_information3 => p_pcv_information3
1662 , p_pcv_information4 => p_pcv_information4
1663 );
1664 when 'PQP_GB_SWF_HLTA_STATUS_SRC' then
1665 chk_hlta_source(p_configuration_value_id => p_configuration_value_id
1666 , p_business_group_id => p_business_group_id
1667 , p_pcv_information_category => p_pcv_information_category
1668 , p_pcv_information1 => p_pcv_information1
1669 , p_pcv_information3 => p_pcv_information3
1670 , p_pcv_information4 => p_pcv_information4
1671 , p_pcv_information5 => p_pcv_information5
1672 );
1673 when 'PQP_GB_SWF_HLTA_STATUS_MAPPING' then
1674 chk_hlta_mapping(p_configuration_value_id => p_configuration_value_id
1675 , p_business_group_id => p_business_group_id
1676 , p_pcv_information_category => p_pcv_information_category
1677 , p_pcv_information1 => p_pcv_information1
1678 , p_pcv_information2 => p_pcv_information2
1679 , p_pcv_information3 => p_pcv_information3
1680 );
1681 when 'PQP_GB_SWF_CNTRT_ARRIVAL_DATE' then
1682 chk_arrival_date(p_configuration_value_id => p_configuration_value_id
1683 , p_business_group_id => p_business_group_id
1684 , p_pcv_information_category => p_pcv_information_category
1685 , p_pcv_information1 => p_pcv_information1
1686 , p_pcv_information2 => p_pcv_information2
1687 );
1688 when 'PQP_GB_SWF_CONTRACT_TYPE' then
1689 chk_contract_type(p_configuration_value_id => p_configuration_value_id
1690 , p_business_group_id => p_business_group_id
1691 , p_pcv_information_category => p_pcv_information_category
1692 , p_pcv_information1 => p_pcv_information1
1693 , p_pcv_information3 => p_pcv_information3
1694 , p_pcv_information4 => p_pcv_information4
1695 , p_pcv_information5 => p_pcv_information5
1696 );
1697 when 'PQP_GB_SWF_ORIGIN_MAPPING' then
1698 chk_origin_mapping(p_configuration_value_id => p_configuration_value_id
1699 , p_business_group_id => p_business_group_id
1700 , p_pcv_information_category => p_pcv_information_category
1701 , p_pcv_information1 => p_pcv_information1
1702 , p_pcv_information2 => p_pcv_information2
1703 , p_pcv_information3 => p_pcv_information3
1704 );
1705 when 'PQP_GB_SWF_DESTINATION_MAPPING' then
1706 chk_origin_mapping(p_configuration_value_id => p_configuration_value_id
1707 , p_business_group_id => p_business_group_id
1708 , p_pcv_information_category => p_pcv_information_category
1709 , p_pcv_information1 => p_pcv_information1
1710 , p_pcv_information2 => p_pcv_information2
1711 , p_pcv_information3 => p_pcv_information3
1712 );
1713 when 'PQP_GB_SWF_ROLE_SOURCE' then
1717 , p_pcv_information1 => p_pcv_information1
1714 chk_role_post_source(p_configuration_value_id => p_configuration_value_id
1715 , p_business_group_id => p_business_group_id
1716 , p_pcv_information_category => p_pcv_information_category
1718 , p_pcv_information3 => p_pcv_information3
1719 );
1720 when 'PQP_GB_SWF_ROLE_MAPPING' then
1721 chk_role_post_mapping(p_configuration_value_id => p_configuration_value_id
1722 , p_business_group_id => p_business_group_id
1723 , p_pcv_information_category => p_pcv_information_category
1724 , p_pcv_information1 => p_pcv_information1
1725 , p_pcv_information2 => p_pcv_information2
1726 , p_pcv_information3 => p_pcv_information3
1727 , p_pcv_information4 => p_pcv_information4
1728 ) ;
1729 when 'PQP_GB_SWF_POST_SOURCE' then
1730 chk_role_post_source(p_configuration_value_id => p_configuration_value_id
1731 , p_business_group_id => p_business_group_id
1732 , p_pcv_information_category => p_pcv_information_category
1733 , p_pcv_information1 => p_pcv_information1
1734 , p_pcv_information3 => p_pcv_information3
1735 );
1736 when 'PQP_GB_SWF_POST_MAPPING' then
1737 chk_role_post_mapping(p_configuration_value_id => p_configuration_value_id
1738 , p_business_group_id => p_business_group_id
1739 , p_pcv_information_category => p_pcv_information_category
1740 , p_pcv_information1 => p_pcv_information1
1741 , p_pcv_information2 => p_pcv_information2
1742 , p_pcv_information3 => p_pcv_information3
1743 , p_pcv_information4 => p_pcv_information4
1744 ) ;
1745 when 'PQP_GB_SWF_HOURS' then
1746 chk_hours(p_configuration_value_id => p_configuration_value_id
1747 , p_business_group_id => p_business_group_id
1748 , p_pcv_information_category => p_pcv_information_category
1749 , p_pcv_information1 => p_pcv_information1
1750 , p_pcv_information2 => p_pcv_information2
1751 , p_pcv_information3 => p_pcv_information3
1752 , p_pcv_information4 => p_pcv_information4
1753 , p_pcv_information5 => p_pcv_information5
1754 , p_pcv_information6 => p_pcv_information6
1755 , p_pcv_information7 => p_pcv_information7
1756 , p_pcv_information8 => p_pcv_information8
1757 ) ;
1758 when 'PQP_GB_SWF_FTE_HOURS' then
1759 chk_fte_hours(p_configuration_value_id => p_configuration_value_id
1760 , p_business_group_id => p_business_group_id
1761 , p_pcv_information_category => p_pcv_information_category
1762 , p_pcv_information1 => p_pcv_information1
1763 , p_pcv_information2 => p_pcv_information2
1764 ) ;
1765 when 'PQP_GB_SWF_PAY_SCALE_MAPPING' then
1766 chk_payscale_mapping(p_configuration_value_id => p_configuration_value_id
1767 , p_business_group_id => p_business_group_id
1768 , p_pcv_information_category => p_pcv_information_category
1769 , p_pcv_information1 => p_pcv_information1
1770 , p_pcv_information2 => p_pcv_information2
1771 ) ;
1772 when 'PQP_GB_SWF_REG_SPINE_SRC' then
1773 chk_regional_spine_source(p_configuration_value_id => p_configuration_value_id
1774 , p_business_group_id => p_business_group_id
1775 , p_pcv_information_category => p_pcv_information_category
1776 , p_pcv_information1 => p_pcv_information1
1777 , p_pcv_information2 => p_pcv_information2
1778 , p_pcv_information3 => p_pcv_information3
1779 ) ;
1780 when 'PQP_GB_SWF_REG_SPINE_MAP_GRD' then
1781 chk_regional_spine_map_grade(p_configuration_value_id => p_configuration_value_id
1782 , p_business_group_id => p_business_group_id
1783 , p_pcv_information_category => p_pcv_information_category
1784 , p_pcv_information1 => p_pcv_information1
1785 , p_pcv_information2 => p_pcv_information2
1786 , p_pcv_information3 => p_pcv_information3
1787 , p_pcv_information4 => p_pcv_information4
1788 ) ;
1789 when 'PQP_GB_SWF_REG_SPINE_MAP_PYSCL' then
1790 chk_regional_spine_map_pyscl(p_configuration_value_id => p_configuration_value_id
1791 , p_business_group_id => p_business_group_id
1792 , p_pcv_information_category => p_pcv_information_category
1793 , p_pcv_information1 => p_pcv_information1
1794 , p_pcv_information2 => p_pcv_information2
1795 , p_pcv_information3 => p_pcv_information3
1796 , p_pcv_information4 => p_pcv_information4
1797 ) ;
1798 when 'PQP_GB_SWF_SPINE_POINT_MAPPING' then
1799 chk_spine_point_mapping(p_configuration_value_id => p_configuration_value_id
1800 , p_business_group_id => p_business_group_id
1801 , p_pcv_information_category => p_pcv_information_category
1802 , p_pcv_information1 => p_pcv_information1
1806 when 'PQP_GB_SWF_ABSENCE_CODE' then
1803 , p_pcv_information2 => p_pcv_information2
1804 , p_pcv_information3 => p_pcv_information3
1805 ) ;
1807 chk_absence_code_mapping(p_configuration_value_id => p_configuration_value_id
1808 , p_business_group_id => p_business_group_id
1809 , p_pcv_information_category => p_pcv_information_category
1810 , p_pcv_information1 => p_pcv_information1
1811 , p_pcv_information3 => p_pcv_information3
1812 , p_pcv_information4 => p_pcv_information4
1813 , p_pcv_information5 => p_pcv_information5
1814 ) ;
1815 when 'PQP_GB_SWF_QUAL_CODE_MAP' then
1816 chk_qualification_code_map(p_configuration_value_id => p_configuration_value_id
1817 , p_business_group_id => p_business_group_id
1818 , p_pcv_information_category => p_pcv_information_category
1819 , p_pcv_information1 => p_pcv_information1
1820 , p_pcv_information3 => p_pcv_information3
1821 , p_pcv_information4 => p_pcv_information4
1822 , p_pcv_information5 => p_pcv_information5
1823 ) ;
1824 when 'PQP_GB_SWF_QUAL_SUBJECT_MAP' then
1825 chk_subject_code_map(p_configuration_value_id => p_configuration_value_id
1826 , p_business_group_id => p_business_group_id
1827 , p_pcv_information_category => p_pcv_information_category
1828 , p_pcv_information1 => p_pcv_information1
1829 , p_pcv_information2 => p_pcv_information2
1830 ) ;
1831 else
1832 null;
1833 end case;
1834
1835
1836 if g_debug then
1837 hr_utility.set_location('Leaving:'||l_proc,20);
1838 end if;
1839
1840 END;
1841 END PQP_GB_CONFIGURATION_VALUE;
1842