[Home] [Help]
36: l_grade varchar2(30);
37: --
38: begin
39: --
40: hr_utility.set_location('Entering:'|| l_proc, 10);
41: --
42: -- Ensure that all the mandatory parameter are not null
43: --
44: --
46: fetch csr_cpd_grade into l_grade;
47: --
48: if csr_cpd_grade%found then
49: close csr_cpd_grade;
50: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_GRADE');
51: hr_utility.raise_error;
52: else
53: close csr_cpd_grade;
54: end if;
47: --
48: if csr_cpd_grade%found then
49: close csr_cpd_grade;
50: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_GRADE');
51: hr_utility.raise_error;
52: else
53: close csr_cpd_grade;
54: end if;
55: --
52: else
53: close csr_cpd_grade;
54: end if;
55: --
56: hr_utility.set_location(' Leaving:'|| l_proc, 20);
57: --
58: end chk_corps_grades_dup;
59: --
60: -- ---------------------------------------------------------------------------
80: l_org varchar2(30);
81: --
82: begin
83: --
84: hr_utility.set_location('Entering:'|| l_proc, 10);
85: --
86: -- Ensure that all the mandatory parameter are not null
87: --
88: --
90: fetch csr_cpd_org into l_org;
91: --
92: if csr_cpd_org%found then
93: close csr_cpd_org;
94: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_ORG');
95: hr_utility.raise_error;
96: else
97: close csr_cpd_org;
98: end if;
91: --
92: if csr_cpd_org%found then
93: close csr_cpd_org;
94: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_ORG');
95: hr_utility.raise_error;
96: else
97: close csr_cpd_org;
98: end if;
99: --
96: else
97: close csr_cpd_org;
98: end if;
99: --
100: hr_utility.set_location(' Leaving:'|| l_proc, 20);
101: --
102: end chk_corps_org;
103: --
104: -- ---------------------------------------------------------------------------
117: l_information7 number;
118: --
119: begin
120: --
121: hr_utility.set_location('Entering:'|| l_proc, 10);
122: if p_rec.information_type ='GRADE' then
123: --
124: l_information4 := to_number(p_rec.information4);
125: l_information5 := to_number(p_rec.information5);
126: l_information6 := to_number(p_rec.information6);
127: l_information7 := to_number(p_rec.information7);
128: --
129: if l_information4 not between 0 and 100 then
130: hr_utility.set_message(8302, 'PQH_CORPS_GRADE_QUOTA');
131: hr_utility.raise_error;
132: end if;
133: if l_information5 not between 0 and 100 then
134: hr_utility.set_message(8302, 'PQH_CORPS_MIN_QUOTA');
127: l_information7 := to_number(p_rec.information7);
128: --
129: if l_information4 not between 0 and 100 then
130: hr_utility.set_message(8302, 'PQH_CORPS_GRADE_QUOTA');
131: hr_utility.raise_error;
132: end if;
133: if l_information5 not between 0 and 100 then
134: hr_utility.set_message(8302, 'PQH_CORPS_MIN_QUOTA');
135: hr_utility.raise_error;
130: hr_utility.set_message(8302, 'PQH_CORPS_GRADE_QUOTA');
131: hr_utility.raise_error;
132: end if;
133: if l_information5 not between 0 and 100 then
134: hr_utility.set_message(8302, 'PQH_CORPS_MIN_QUOTA');
135: hr_utility.raise_error;
136: end if;
137: if l_information6 not between 0 and 100 then
138: hr_utility.set_message(8302, 'PQH_CORPS_AVG_QUOTA');
131: hr_utility.raise_error;
132: end if;
133: if l_information5 not between 0 and 100 then
134: hr_utility.set_message(8302, 'PQH_CORPS_MIN_QUOTA');
135: hr_utility.raise_error;
136: end if;
137: if l_information6 not between 0 and 100 then
138: hr_utility.set_message(8302, 'PQH_CORPS_AVG_QUOTA');
139: hr_utility.raise_error;
134: hr_utility.set_message(8302, 'PQH_CORPS_MIN_QUOTA');
135: hr_utility.raise_error;
136: end if;
137: if l_information6 not between 0 and 100 then
138: hr_utility.set_message(8302, 'PQH_CORPS_AVG_QUOTA');
139: hr_utility.raise_error;
140: end if;
141: if l_information7 not between 0 and 100 then
142: hr_utility.set_message(8302, 'PQH_CORPS_MAX_QUOTA');
135: hr_utility.raise_error;
136: end if;
137: if l_information6 not between 0 and 100 then
138: hr_utility.set_message(8302, 'PQH_CORPS_AVG_QUOTA');
139: hr_utility.raise_error;
140: end if;
141: if l_information7 not between 0 and 100 then
142: hr_utility.set_message(8302, 'PQH_CORPS_MAX_QUOTA');
143: hr_utility.raise_error;
138: hr_utility.set_message(8302, 'PQH_CORPS_AVG_QUOTA');
139: hr_utility.raise_error;
140: end if;
141: if l_information7 not between 0 and 100 then
142: hr_utility.set_message(8302, 'PQH_CORPS_MAX_QUOTA');
143: hr_utility.raise_error;
144: end if;
145: l_sum_quota := nvl(l_information5,0)+nvl(l_information6,0)+nvl(l_information7,0);
146: if l_sum_quota > 100 then
139: hr_utility.raise_error;
140: end if;
141: if l_information7 not between 0 and 100 then
142: hr_utility.set_message(8302, 'PQH_CORPS_MAX_QUOTA');
143: hr_utility.raise_error;
144: end if;
145: l_sum_quota := nvl(l_information5,0)+nvl(l_information6,0)+nvl(l_information7,0);
146: if l_sum_quota > 100 then
147: hr_utility.set_message(8302, 'PQH_CORPS_TOTAL_QUOTA');
143: hr_utility.raise_error;
144: end if;
145: l_sum_quota := nvl(l_information5,0)+nvl(l_information6,0)+nvl(l_information7,0);
146: if l_sum_quota > 100 then
147: hr_utility.set_message(8302, 'PQH_CORPS_TOTAL_QUOTA');
148: hr_utility.raise_error;
149: end if;
150: select sum(information4)
151: into l_grade_quota
144: end if;
145: l_sum_quota := nvl(l_information5,0)+nvl(l_information6,0)+nvl(l_information7,0);
146: if l_sum_quota > 100 then
147: hr_utility.set_message(8302, 'PQH_CORPS_TOTAL_QUOTA');
148: hr_utility.raise_error;
149: end if;
150: select sum(information4)
151: into l_grade_quota
152: from pqh_corps_extra_info
153: where corps_definition_id = p_rec.corps_definition_id
154: and information_type ='GRADE'
155: and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id,corps_extra_info_id);
156: if l_grade_quota + l_information4 > 100 then
157: hr_utility.set_message(8302, 'PQH_CORPS_OCCU_QUOTA');
158: hr_utility.raise_error;
159: end if;
160: end if;
161: --
154: and information_type ='GRADE'
155: and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id,corps_extra_info_id);
156: if l_grade_quota + l_information4 > 100 then
157: hr_utility.set_message(8302, 'PQH_CORPS_OCCU_QUOTA');
158: hr_utility.raise_error;
159: end if;
160: end if;
161: --
162: hr_utility.set_location(' Leaving:'|| l_proc, 20);
158: hr_utility.raise_error;
159: end if;
160: end if;
161: --
162: hr_utility.set_location(' Leaving:'|| l_proc, 20);
163: --
164: end chk_corps_grades;
165: --
166: -- ---------------------------------------------------------------------------
191: l_training varchar2(30);
192: --
193: begin
194: --
195: hr_utility.set_location('Entering:'|| l_proc, 10);
196: --
197: -- Ensure that all the mandatory parameter are not null
198: --
199: --
201: fetch csr_cpd_training into l_training;
202: --
203: if csr_cpd_training%found then
204: close csr_cpd_training;
205: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_TRAINING');
206: hr_utility.raise_error;
207: else
208: close csr_cpd_training;
209: end if;
202: --
203: if csr_cpd_training%found then
204: close csr_cpd_training;
205: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_TRAINING');
206: hr_utility.raise_error;
207: else
208: close csr_cpd_training;
209: end if;
210: --
207: else
208: close csr_cpd_training;
209: end if;
210: --
211: hr_utility.set_location(' Leaving:'|| l_proc, 20);
212: --
213: end chk_corps_training;
214: --
215: -- ---------------------------------------------------------------------------
239: l_exam varchar2(30);
240: --
241: begin
242: --
243: hr_utility.set_location('Entering:'|| l_proc, 10);
244: --
245: -- Ensure that all the mandatory parameter are not null
246: --
247: --
249: fetch csr_cpd_exam into l_exam;
250: --
251: if csr_cpd_exam%found then
252: close csr_cpd_exam;
253: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_EXAM');
254: hr_utility.raise_error;
255: else
256: close csr_cpd_exam;
257: end if;
250: --
251: if csr_cpd_exam%found then
252: close csr_cpd_exam;
253: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_EXAM');
254: hr_utility.raise_error;
255: else
256: close csr_cpd_exam;
257: end if;
258: --
255: else
256: close csr_cpd_exam;
257: end if;
258: --
259: hr_utility.set_location(' Leaving:'|| l_proc, 20);
260: --
261: end chk_corps_exam;
262: --
263: -- ---------------------------------------------------------------------------
288: l_rules varchar2(30);
289: --
290: begin
291: --
292: hr_utility.set_location('Entering:'|| l_proc, 10);
293: --
294: -- Ensure that all the mandatory parameter are not null
295: --
296: --
298: fetch csr_cpd_rules into l_rules;
299: --
300: if csr_cpd_rules%found then
301: close csr_cpd_rules;
302: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_RULES');
303: hr_utility.raise_error;
304: else
305: close csr_cpd_rules;
306: end if;
299: --
300: if csr_cpd_rules%found then
301: close csr_cpd_rules;
302: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_RULES');
303: hr_utility.raise_error;
304: else
305: close csr_cpd_rules;
306: end if;
307: if p_rec.information_type ='RULES' then
308: if p_rec.information4 ='DIPLOMA' then
309: null;
310: elsif p_rec.information4 ='MAX_AGE' then
311: if p_rec.information5 is null then
312: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_NULL');
313: hr_utility.raise_error;
314: end if;
315: if p_rec.information5 not between 0 and 80 then
316: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MAX_AGE');
309: null;
310: elsif p_rec.information4 ='MAX_AGE' then
311: if p_rec.information5 is null then
312: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_NULL');
313: hr_utility.raise_error;
314: end if;
315: if p_rec.information5 not between 0 and 80 then
316: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MAX_AGE');
317: hr_utility.raise_error;
312: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_NULL');
313: hr_utility.raise_error;
314: end if;
315: if p_rec.information5 not between 0 and 80 then
316: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MAX_AGE');
317: hr_utility.raise_error;
318: end if;
319: if nvl(p_rec.information6,'Y') <> 'Y' then
320: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_YEARS');
313: hr_utility.raise_error;
314: end if;
315: if p_rec.information5 not between 0 and 80 then
316: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MAX_AGE');
317: hr_utility.raise_error;
318: end if;
319: if nvl(p_rec.information6,'Y') <> 'Y' then
320: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_YEARS');
321: hr_utility.raise_error;
316: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MAX_AGE');
317: hr_utility.raise_error;
318: end if;
319: if nvl(p_rec.information6,'Y') <> 'Y' then
320: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_YEARS');
321: hr_utility.raise_error;
322: end if;
323: elsif p_rec.information4 ='MIN_AGE' then
324: if p_rec.information5 is null then
317: hr_utility.raise_error;
318: end if;
319: if nvl(p_rec.information6,'Y') <> 'Y' then
320: hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_YEARS');
321: hr_utility.raise_error;
322: end if;
323: elsif p_rec.information4 ='MIN_AGE' then
324: if p_rec.information5 is null then
325: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_NULL');
321: hr_utility.raise_error;
322: end if;
323: elsif p_rec.information4 ='MIN_AGE' then
324: if p_rec.information5 is null then
325: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_NULL');
326: hr_utility.raise_error;
327: end if;
328: if p_rec.information5 not between 0 and 80 then
329: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MIN_AGE');
322: end if;
323: elsif p_rec.information4 ='MIN_AGE' then
324: if p_rec.information5 is null then
325: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_NULL');
326: hr_utility.raise_error;
327: end if;
328: if p_rec.information5 not between 0 and 80 then
329: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MIN_AGE');
330: hr_utility.raise_error;
325: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_NULL');
326: hr_utility.raise_error;
327: end if;
328: if p_rec.information5 not between 0 and 80 then
329: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MIN_AGE');
330: hr_utility.raise_error;
331: end if;
332: if nvl(p_rec.information6,'Y') <> 'Y' then
333: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_YEARS');
326: hr_utility.raise_error;
327: end if;
328: if p_rec.information5 not between 0 and 80 then
329: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MIN_AGE');
330: hr_utility.raise_error;
331: end if;
332: if nvl(p_rec.information6,'Y') <> 'Y' then
333: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_YEARS');
334: hr_utility.raise_error;
329: hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MIN_AGE');
330: hr_utility.raise_error;
331: end if;
332: if nvl(p_rec.information6,'Y') <> 'Y' then
333: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_YEARS');
334: hr_utility.raise_error;
335: end if;
336: elsif p_rec.information4 ='NATIONAL' then
337: null;
330: hr_utility.raise_error;
331: end if;
332: if nvl(p_rec.information6,'Y') <> 'Y' then
333: hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_YEARS');
334: hr_utility.raise_error;
335: end if;
336: elsif p_rec.information4 ='NATIONAL' then
337: null;
338: elsif p_rec.information4 ='PROB_PERIOD' then
336: elsif p_rec.information4 ='NATIONAL' then
337: null;
338: elsif p_rec.information4 ='PROB_PERIOD' then
339: if p_rec.information6 is null then
340: hr_utility.set_message(8302, 'PQH_CORPS_PROB_PRD_UOM_NULL');
341: hr_utility.raise_error;
342: end if;
343: elsif p_rec.information4 ='SERVICE_LEN' then
344: null;
337: null;
338: elsif p_rec.information4 ='PROB_PERIOD' then
339: if p_rec.information6 is null then
340: hr_utility.set_message(8302, 'PQH_CORPS_PROB_PRD_UOM_NULL');
341: hr_utility.raise_error;
342: end if;
343: elsif p_rec.information4 ='SERVICE_LEN' then
344: null;
345: elsif p_rec.information4 ='START_STEP' then
346: null;
347: end if;
348: end if;
349: --
350: hr_utility.set_location(' Leaving:'|| l_proc, 20);
351: --
352: end chk_corps_rules;
353: --
354: -- ---------------------------------------------------------------------------
378: l_doc varchar2(30);
379: --
380: begin
381: --
382: hr_utility.set_location('Entering:'|| l_proc, 10);
383: --
384: -- Ensure that all the mandatory parameter are not null
385: --
386: --
388: fetch csr_cpd_doc into l_doc;
389: --
390: if csr_cpd_doc%found then
391: close csr_cpd_doc;
392: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_DOC');
393: hr_utility.raise_error;
394: else
395: close csr_cpd_doc;
396: end if;
389: --
390: if csr_cpd_doc%found then
391: close csr_cpd_doc;
392: hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_DOC');
393: hr_utility.raise_error;
394: else
395: close csr_cpd_doc;
396: end if;
397: --
394: else
395: close csr_cpd_doc;
396: end if;
397: --
398: hr_utility.set_location(' Leaving:'|| l_proc, 20);
399: --
400: end chk_corps_doc;
401: --
402: -- ---------------------------------------------------------------------------
429: l_proc varchar2(72) := g_package||'set_security_group_id';
430: --
431: begin
432: --
433: hr_utility.set_location('Entering:'|| l_proc, 10);
434: --
435: -- Ensure that all the mandatory parameter are not null
436: --
437: hr_api.mandatory_arg_error
464: (p_security_group_id => l_security_group_id
465: );
466: end if;
467: --
468: hr_utility.set_location(' Leaving:'|| l_proc, 20);
469: --
470: end set_security_group_id;
471: --
472: -- ---------------------------------------------------------------------------
499: l_proc varchar2(72) := g_package||'return_legislation_code';
500: --
501: Begin
502: --
503: hr_utility.set_location('Entering:'|| l_proc, 10);
504: --
505: -- Ensure that all the mandatory parameter are not null
506: --
507: hr_api.mandatory_arg_error
517: -- call to this function. Just return the value in the global
518: -- variable.
519: --
520: l_legislation_code := pqh_cei_bus.g_legislation_code;
521: hr_utility.set_location(l_proc, 20);
522: else
523: --
524: -- The ID is different to the last call to this function
525: -- or this is the first call to this function.
534: close csr_leg_code;
535: fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
536: fnd_message.raise_error;
537: end if;
538: hr_utility.set_location(l_proc,30);
539: --
540: -- Set the global variables so the values are
541: -- available for the next call to this function.
542: --
543: close csr_leg_code;
544: pqh_cei_bus.g_corps_extra_info_id := p_corps_extra_info_id;
545: pqh_cei_bus.g_legislation_code := l_legislation_code;
546: end if;
547: hr_utility.set_location(' Leaving:'|| l_proc, 40);
548: return l_legislation_code;
549: end return_legislation_code;
550: --
551: -- ----------------------------------------------------------------------------
582: --
583: l_proc varchar2(72) := g_package || 'chk_ddf';
584: --
585: begin
586: hr_utility.set_location('Entering:'||l_proc,10);
587: --
588: if ((p_rec.corps_extra_info_id is not null) and (
589: nvl(pqh_cei_shd.g_old_rec.information_type, hr_api.g_varchar2) <>
590: nvl(p_rec.information_type, hr_api.g_varchar2) or
728: */
729: null;
730: end if;
731: --
732: hr_utility.set_location(' Leaving:'||l_proc,20);
733: end chk_ddf;
734: --
735: -- ----------------------------------------------------------------------------
736: -- |------------------------------< chk_df >----------------------------------|
766: --
767: l_proc varchar2(72) := g_package || 'chk_df';
768: --
769: begin
770: hr_utility.set_location('Entering:'||l_proc,10);
771: --
772: if ((p_rec.corps_extra_info_id is not null) and (
773: nvl(pqh_cei_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
774: nvl(p_rec.attribute1, hr_api.g_varchar2) or
908: */
909: null;
910: end if;
911: --
912: hr_utility.set_location(' Leaving:'||l_proc,20);
913: end chk_df;
914: --
915: -- ----------------------------------------------------------------------------
916: -- |-----------------------< chk_non_updateable_args >------------------------|
976: --
977: l_proc varchar2(72) := g_package||'insert_validate';
978: --
979: Begin
980: hr_utility.set_location('Entering:'||l_proc, 5);
981: --
982: -- Call all supporting business operations
983: --
984: --
1009: -- pqh_cei_bus.chk_ddf(p_rec);
1010: --
1011: -- pqh_cei_bus.chk_df(p_rec);
1012: --
1013: hr_utility.set_location(' Leaving:'||l_proc, 10);
1014: End insert_validate;
1015: --
1016: -- ----------------------------------------------------------------------------
1017: -- |---------------------------< update_validate >----------------------------|
1023: --
1024: l_proc varchar2(72) := g_package||'update_validate';
1025: --
1026: Begin
1027: hr_utility.set_location('Entering:'||l_proc, 5);
1028: --
1029: -- Call all supporting business operations
1030: --
1031: --
1059: -- pqh_cei_bus.chk_ddf(p_rec);
1060: --
1061: -- pqh_cei_bus.chk_df(p_rec);
1062: --
1063: hr_utility.set_location(' Leaving:'||l_proc, 10);
1064: End update_validate;
1065: --
1066: -- ----------------------------------------------------------------------------
1067: -- |---------------------------< delete_validate >----------------------------|
1072: --
1073: l_proc varchar2(72) := g_package||'delete_validate';
1074: --
1075: Begin
1076: hr_utility.set_location('Entering:'||l_proc, 5);
1077: --
1078: -- Call all supporting business operations
1079: --
1080: hr_utility.set_location(' Leaving:'||l_proc, 10);
1076: hr_utility.set_location('Entering:'||l_proc, 5);
1077: --
1078: -- Call all supporting business operations
1079: --
1080: hr_utility.set_location(' Leaving:'||l_proc, 10);
1081: End delete_validate;
1082: --
1083: end pqh_cei_bus;