[Home] [Help]
124: and p_effective_date between pgr.effective_start_date
125: and pgr.effective_end_date;
126: --
127: begin
128: hr_utility.set_location('Entering:'|| l_proc, 10);
129: --
130: -- Check mandatory parameters have been set
131: --
132: hr_api.mandatory_arg_error
158: ,p_argument => 'effective_end_date'
159: ,p_argument_value => p_effective_end_date
160: );
161: --
162: hr_utility.set_location(l_proc, 20);
163: --
164: -- Check that if the rule is for a grade, this grade
165: -- is a valid grade on per_grades, otherwise the rule
166: -- must be defined for a spinal point in which case
166: -- must be defined for a spinal point in which case
167: -- the spinal point id should exist on per_spinal_points
168: --
169: if p_rate_type = 'G' then
170: hr_utility.set_location(l_proc, 30);
171: open csr_valid_grade_id;
172: fetch csr_valid_grade_id into l_business_group_id;
173: if csr_valid_grade_id%notfound then
174: close csr_valid_grade_id;
171: open csr_valid_grade_id;
172: fetch csr_valid_grade_id into l_business_group_id;
173: if csr_valid_grade_id%notfound then
174: close csr_valid_grade_id;
175: hr_utility.set_message(801, 'HR_7311_GRR_INVALID_GRADE');
176: hr_utility.raise_error;
177: end if;
178: close csr_valid_grade_id;
179: hr_utility.set_location(l_proc, 35);
172: fetch csr_valid_grade_id into l_business_group_id;
173: if csr_valid_grade_id%notfound then
174: close csr_valid_grade_id;
175: hr_utility.set_message(801, 'HR_7311_GRR_INVALID_GRADE');
176: hr_utility.raise_error;
177: end if;
178: close csr_valid_grade_id;
179: hr_utility.set_location(l_proc, 35);
180: --
175: hr_utility.set_message(801, 'HR_7311_GRR_INVALID_GRADE');
176: hr_utility.raise_error;
177: end if;
178: close csr_valid_grade_id;
179: hr_utility.set_location(l_proc, 35);
180: --
181: -- Check that the grade is in the same business group as the
182: -- grade rule
183: --
182: -- grade rule
183: --
184: If p_business_group_id <> l_business_group_id then
185: --
186: hr_utility.set_message(801, 'HR_51299_GRR_INV_G_BG ');
187: hr_utility.raise_error;
188: --
189: end if;
190: hr_utility.set_location(l_proc, 36);
183: --
184: If p_business_group_id <> l_business_group_id then
185: --
186: hr_utility.set_message(801, 'HR_51299_GRR_INV_G_BG ');
187: hr_utility.raise_error;
188: --
189: end if;
190: hr_utility.set_location(l_proc, 36);
191: elsif p_rate_type = 'SP' then
186: hr_utility.set_message(801, 'HR_51299_GRR_INV_G_BG ');
187: hr_utility.raise_error;
188: --
189: end if;
190: hr_utility.set_location(l_proc, 36);
191: elsif p_rate_type = 'SP' then
192: hr_utility.set_location(l_proc, 40);
193: open csr_valid_spinal_point;
194: fetch csr_valid_spinal_point into l_business_group_id;
188: --
189: end if;
190: hr_utility.set_location(l_proc, 36);
191: elsif p_rate_type = 'SP' then
192: hr_utility.set_location(l_proc, 40);
193: open csr_valid_spinal_point;
194: fetch csr_valid_spinal_point into l_business_group_id;
195: if csr_valid_spinal_point%notfound then
196: close csr_valid_spinal_point;
193: open csr_valid_spinal_point;
194: fetch csr_valid_spinal_point into l_business_group_id;
195: if csr_valid_spinal_point%notfound then
196: close csr_valid_spinal_point;
197: hr_utility.set_message(801, 'HR_7312_GRR_INVALID_SPNL_POINT');
198: hr_utility.raise_error;
199: end if;
200: close csr_valid_spinal_point;
201: hr_utility.set_location(l_proc, 50);
194: fetch csr_valid_spinal_point into l_business_group_id;
195: if csr_valid_spinal_point%notfound then
196: close csr_valid_spinal_point;
197: hr_utility.set_message(801, 'HR_7312_GRR_INVALID_SPNL_POINT');
198: hr_utility.raise_error;
199: end if;
200: close csr_valid_spinal_point;
201: hr_utility.set_location(l_proc, 50);
202: --
197: hr_utility.set_message(801, 'HR_7312_GRR_INVALID_SPNL_POINT');
198: hr_utility.raise_error;
199: end if;
200: close csr_valid_spinal_point;
201: hr_utility.set_location(l_proc, 50);
202: --
203: -- Check that the spinal point is in the same business group as the
204: -- grade rule
205: --
204: -- grade rule
205: --
206: If p_business_group_id <> l_business_group_id then
207: --
208: hr_utility.set_message(801, 'HR_51298_GRR_INV_SP_BG ');
209: hr_utility.raise_error;
210: --
211: end if;
212: hr_utility.set_location(l_proc, 60);
205: --
206: If p_business_group_id <> l_business_group_id then
207: --
208: hr_utility.set_message(801, 'HR_51298_GRR_INV_SP_BG ');
209: hr_utility.raise_error;
210: --
211: end if;
212: hr_utility.set_location(l_proc, 60);
213: end if;
208: hr_utility.set_message(801, 'HR_51298_GRR_INV_SP_BG ');
209: hr_utility.raise_error;
210: --
211: end if;
212: hr_utility.set_location(l_proc, 60);
213: end if;
214: --
215: hr_utility.set_location(l_proc, 3);
216: --
211: end if;
212: hr_utility.set_location(l_proc, 60);
213: end if;
214: --
215: hr_utility.set_location(l_proc, 3);
216: --
217: -- Checks that the combination of p_grade_or_spinal_point_id,
218: -- p_rate_id, p_rate_type, p_business_group_id is unique
219: -- betwen effective_start_date and effective_end_date on
222: open csr_unique_rate;
223: fetch csr_unique_rate into l_exists;
224: if csr_unique_rate%found then
225: close csr_unique_rate;
226: hr_utility.set_message(801, 'HR_7313_GRR_GRADE_RULE_COMB_EX');
227: hr_utility.raise_error;
228: end if;
229: close csr_unique_rate;
230: --
223: fetch csr_unique_rate into l_exists;
224: if csr_unique_rate%found then
225: close csr_unique_rate;
226: hr_utility.set_message(801, 'HR_7313_GRR_GRADE_RULE_COMB_EX');
227: hr_utility.raise_error;
228: end if;
229: close csr_unique_rate;
230: --
231: hr_utility.set_location(' Leaving:'|| l_proc, 4);
227: hr_utility.raise_error;
228: end if;
229: close csr_unique_rate;
230: --
231: hr_utility.set_location(' Leaving:'|| l_proc, 4);
232: end chk_grade_or_spinal_point_id;
233: --
234: -- ---------------------------------------------------------------------------
235: -- |---------------------------< chk_rate_id >-------------------------------|
282: from pay_rates pr
283: where pr.rate_id = p_rate_id;
284: --
285: begin
286: hr_utility.set_location('Entering:'|| l_proc, 10);
287: --
288: -- Check mandatory parameters have been set
289: --
290: hr_api.mandatory_arg_error
292: ,p_argument => 'rate_id'
293: ,p_argument_value => p_rate_id
294: );
295: --
296: hr_utility.set_location(l_proc, 20);
297: --
298: -- Check that p_rate_id exists on table
299: -- pay_rates
300: --
301: open csr_rate_exists;
302: fetch csr_rate_exists into l_business_group_id;
303: if csr_rate_exists%notfound then
304: close csr_rate_exists;
305: hr_utility.set_message(801, 'HR_7314_GRR_NON_EXIST_PAY_RATE');
306: hr_utility.raise_error;
307: end if;
308: close csr_rate_exists;
309: hr_utility.set_location(l_proc, 25);
302: fetch csr_rate_exists into l_business_group_id;
303: if csr_rate_exists%notfound then
304: close csr_rate_exists;
305: hr_utility.set_message(801, 'HR_7314_GRR_NON_EXIST_PAY_RATE');
306: hr_utility.raise_error;
307: end if;
308: close csr_rate_exists;
309: hr_utility.set_location(l_proc, 25);
310: --
305: hr_utility.set_message(801, 'HR_7314_GRR_NON_EXIST_PAY_RATE');
306: hr_utility.raise_error;
307: end if;
308: close csr_rate_exists;
309: hr_utility.set_location(l_proc, 25);
310: --
311: -- Check that the rate is in the same business group as the
312: -- grade rule
313: --
312: -- grade rule
313: --
314: If p_business_group_id <> l_business_group_id then
315: --
316: hr_utility.set_message(801, 'HR_51301_GRR_INV_RA_BG ');
317: hr_utility.raise_error;
318: --
319: end if;
320: hr_utility.set_location(' Leaving:'|| l_proc, 30);
313: --
314: If p_business_group_id <> l_business_group_id then
315: --
316: hr_utility.set_message(801, 'HR_51301_GRR_INV_RA_BG ');
317: hr_utility.raise_error;
318: --
319: end if;
320: hr_utility.set_location(' Leaving:'|| l_proc, 30);
321: end chk_rate_id;
316: hr_utility.set_message(801, 'HR_51301_GRR_INV_RA_BG ');
317: hr_utility.raise_error;
318: --
319: end if;
320: hr_utility.set_location(' Leaving:'|| l_proc, 30);
321: end chk_rate_id;
322: --
323: -- ---------------------------------------------------------------------------
324: -- |--------------------------< chk_rate_type >------------------------------|
372: where pr.rate_id = p_rate_id
373: and pr.rate_type = p_rate_type;
374: --
375: begin
376: hr_utility.set_location('Entering:'|| l_proc, 1);
377: --
378: -- Check mandatory parameters have been set
379: --
380: hr_api.mandatory_arg_error
388: ,p_argument => 'p_rate_type'
389: ,p_argument_value => p_rate_type
390: );
391: --
392: hr_utility.set_location(l_proc, 2);
393: --
394: -- Check rate type in 'G' or 'SP'
395: --
396: if p_rate_type not in ('G','SP') then
397: pay_grr_shd.constraint_error(
398: p_constraint_name => 'PAY_GRL_RATE_TYPE_CHK');
399: end if;
400: --
401: hr_utility.set_location(l_proc, 3);
402: --
403: -- Check that the combination of rate id and
404: -- rate_type is valid on the table pay_rates
405: --
406: open csr_valid_comb;
407: fetch csr_valid_comb into l_exists;
408: if csr_valid_comb%notfound then
409: close csr_valid_comb;
410: hr_utility.set_message(801, 'HR_7315_GRR_INVA_PAY_RATE_COMB');
411: hr_utility.raise_error;
412: end if;
413: close csr_valid_comb;
414: hr_utility.set_location(' Leaving:'|| l_proc, 4);
407: fetch csr_valid_comb into l_exists;
408: if csr_valid_comb%notfound then
409: close csr_valid_comb;
410: hr_utility.set_message(801, 'HR_7315_GRR_INVA_PAY_RATE_COMB');
411: hr_utility.raise_error;
412: end if;
413: close csr_valid_comb;
414: hr_utility.set_location(' Leaving:'|| l_proc, 4);
415: end chk_rate_type;
410: hr_utility.set_message(801, 'HR_7315_GRR_INVA_PAY_RATE_COMB');
411: hr_utility.raise_error;
412: end if;
413: close csr_valid_comb;
414: hr_utility.set_location(' Leaving:'|| l_proc, 4);
415: end chk_rate_type;
416: --
417: -- ---------------------------------------------------------------------------
418: -- |---------------------------< chk_maximum >-------------------------------|
479: where pr.rate_id = p_rate_id
480: and pbg.business_group_id = pr.business_group_id;
481: --
482: begin
483: hr_utility.set_location('Entering:'|| l_proc, 1);
484: --
485: -- p_value and p_maximum reassigned to l_value
486: -- and l_maximum for use within the checkformat
487: -- procedure call as in/out arguments
521: if ((l_api_updating and
522: nvl(pay_grr_shd.g_old_rec.maximum, hr_api.g_varchar2) <>
523: nvl(l_maximum, hr_api.g_varchar2)) or
524: (NOT l_api_updating)) then
525: hr_utility.set_location(l_proc, 2);
526: --
527: if p_rate_type = 'G' then
528: open csel1;
529: fetch csel1 into l_uom, l_currency_code;
532: --
533: -- If no currency exists for a particular
534: -- business group then flag an error
535: --
536: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
537: hr_utility.set_message_token('PROCEDURE', l_proc);
538: hr_utility.set_message_token('STEP', '5');
539: end if;
540: close csel1;
533: -- If no currency exists for a particular
534: -- business group then flag an error
535: --
536: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
537: hr_utility.set_message_token('PROCEDURE', l_proc);
538: hr_utility.set_message_token('STEP', '5');
539: end if;
540: close csel1;
541: --
534: -- business group then flag an error
535: --
536: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
537: hr_utility.set_message_token('PROCEDURE', l_proc);
538: hr_utility.set_message_token('STEP', '5');
539: end if;
540: close csel1;
541: --
542: -- Check 1 : maximum > minimum
551: rgeflg => l_min_max_status,
552: curcode => l_currency_code);
553: --
554: if (l_min_max_status = 'F') then
555: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
556: hr_utility.raise_error;
557: end if;
558: --
559: -- Check 2 : value < maximum
552: curcode => l_currency_code);
553: --
554: if (l_min_max_status = 'F') then
555: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
556: hr_utility.raise_error;
557: end if;
558: --
559: -- Check 2 : value < maximum
560: --
568: rgeflg => l_min_max_status,
569: curcode => l_currency_code);
570: --
571: if (l_min_max_status = 'F') then
572: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
573: hr_utility.raise_error;
574: end if;
575: --
576: -- 40.2 change start.
569: curcode => l_currency_code);
570: --
571: if (l_min_max_status = 'F') then
572: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
573: hr_utility.raise_error;
574: end if;
575: --
576: -- 40.2 change start.
577: --
576: -- 40.2 change start.
577: --
578: elsif p_rate_type = 'SP' then
579: if l_maximum is not null then
580: hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
581: hr_utility.raise_error;
582: end if;
583: --
584: -- 40.2 change end.
577: --
578: elsif p_rate_type = 'SP' then
579: if l_maximum is not null then
580: hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
581: hr_utility.raise_error;
582: end if;
583: --
584: -- 40.2 change end.
585: --
585: --
586: end if;
587: end if;
588: --
589: hr_utility.set_location(' Leaving:'|| l_proc, 3);
590: end ;
591: --
592: -- ---------------------------------------------------------------------------
593: -- |--------------------------< chk_mid_value >------------------------------|
653: where pr.rate_id = p_rate_id
654: and pbg.business_group_id = pr.business_group_id;
655: --
656: begin
657: hr_utility.set_location('Entering:'|| l_proc, 1);
658: --
659: -- p_mid_value reassigned to l_mid_value for use within
660: -- the checkformat procedure call as in/out argument
661: --
693: if ((l_api_updating and
694: nvl(pay_grr_shd.g_old_rec.mid_value, hr_api.g_varchar2) <>
695: nvl(l_mid_value, hr_api.g_varchar2)) or
696: (NOT l_api_updating)) then
697: hr_utility.set_location(l_proc, 2);
698: --
699: if p_rate_type = 'G' then
700: open csel1;
701: fetch csel1 into l_uom, l_currency_code;
700: open csel1;
701: fetch csel1 into l_uom, l_currency_code;
702: if csel1%notfound then
703: close csel1;
704: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
705: hr_utility.raise_error;
706: end if;
707: close csel1;
708: --
701: fetch csel1 into l_uom, l_currency_code;
702: if csel1%notfound then
703: close csel1;
704: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
705: hr_utility.raise_error;
706: end if;
707: close csel1;
708: --
709: -- Check 1 : minimum < mid_value
718: rgeflg => l_min_max_status,
719: curcode => l_currency_code);
720: --
721: if (l_min_max_status = 'F') then
722: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
723: hr_utility.raise_error;
724: end if;
725: --
726: -- Check 2 : mid_value < maximum
719: curcode => l_currency_code);
720: --
721: if (l_min_max_status = 'F') then
722: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
723: hr_utility.raise_error;
724: end if;
725: --
726: -- Check 2 : mid_value < maximum
727: ---
735: rgeflg => l_min_max_status,
736: curcode => l_currency_code);
737: --
738: if (l_min_max_status = 'F') then
739: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
740: hr_utility.raise_error;
741: end if;
742: --
743: -- Added check for not null value of mid_value for rate type
736: curcode => l_currency_code);
737: --
738: if (l_min_max_status = 'F') then
739: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
740: hr_utility.raise_error;
741: end if;
742: --
743: -- Added check for not null value of mid_value for rate type
744: -- of SP - AForte 14/6/96
743: -- Added check for not null value of mid_value for rate type
744: -- of SP - AForte 14/6/96
745: elsif p_rate_type = 'SP' then
746: if l_mid_value is not null then
747: hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
748: hr_utility.raise_error;
749: end if;
750: end if;
751: end if;
744: -- of SP - AForte 14/6/96
745: elsif p_rate_type = 'SP' then
746: if l_mid_value is not null then
747: hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
748: hr_utility.raise_error;
749: end if;
750: end if;
751: end if;
752: --
749: end if;
750: end if;
751: end if;
752: --
753: hr_utility.set_location(' Leaving:'|| l_proc, 3);
754: end ;
755: --
756: -- ---------------------------------------------------------------------------
757: -- |---------------------------< chk_minimum >-------------------------------|
818: where pr.rate_id = p_rate_id
819: and pbg.business_group_id = pr.business_group_id;
820: --
821: begin
822: hr_utility.set_location('Entering:'|| l_proc, 1);
823: --
824: -- p_minimum and p_value reassigned to l_minimum
825: -- and l_value for use within the checkformat
826: -- procedure call as in/out arguments
860: if ((l_api_updating and
861: nvl(pay_grr_shd.g_old_rec.minimum, hr_api.g_varchar2) <>
862: nvl(l_minimum, hr_api.g_varchar2)) or
863: (NOT l_api_updating)) then
864: hr_utility.set_location(l_proc, 2);
865: --
866: if p_rate_type = 'G' then
867: open csel1;
868: fetch csel1 into l_uom, l_currency_code;
871: --
872: -- If no currency exists for a particular
873: -- business group then flag an error
874: --
875: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
876: hr_utility.set_message_token('PROCEDURE', l_proc);
877: hr_utility.set_message_token('STEP', '5');
878: end if;
879: close csel1;
872: -- If no currency exists for a particular
873: -- business group then flag an error
874: --
875: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
876: hr_utility.set_message_token('PROCEDURE', l_proc);
877: hr_utility.set_message_token('STEP', '5');
878: end if;
879: close csel1;
880: --
873: -- business group then flag an error
874: --
875: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
876: hr_utility.set_message_token('PROCEDURE', l_proc);
877: hr_utility.set_message_token('STEP', '5');
878: end if;
879: close csel1;
880: --
881: -- Check 1 : minimum < maximum
890: rgeflg => l_min_max_status,
891: curcode => l_currency_code);
892: --
893: if (l_min_max_status = 'F') then
894: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
895: hr_utility.raise_error;
896: end if;
897: --
898: -- Check 2 : value > minimum
891: curcode => l_currency_code);
892: --
893: if (l_min_max_status = 'F') then
894: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
895: hr_utility.raise_error;
896: end if;
897: --
898: -- Check 2 : value > minimum
899: --
907: rgeflg => l_min_max_status,
908: curcode => l_currency_code);
909: --
910: if (l_min_max_status = 'F') then
911: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
912: hr_utility.raise_error;
913: end if;
914: --
915: -- Added check for not null value of minimum for rate type
908: curcode => l_currency_code);
909: --
910: if (l_min_max_status = 'F') then
911: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
912: hr_utility.raise_error;
913: end if;
914: --
915: -- Added check for not null value of minimum for rate type
916: -- of SP - AForte 14/6/96
916: -- of SP - AForte 14/6/96
917:
918: elsif p_rate_type = 'SP' then
919: if l_minimum is not null then
920: hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
921: hr_utility.raise_error;
922: end if;
923: end if;
924: end if;
917:
918: elsif p_rate_type = 'SP' then
919: if l_minimum is not null then
920: hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
921: hr_utility.raise_error;
922: end if;
923: end if;
924: end if;
925: --
922: end if;
923: end if;
924: end if;
925: --
926: hr_utility.set_location(' Leaving:'|| l_proc, 3);
927: end ;
928: --
929: -- ---------------------------------------------------------------------------
930: -- |---------------------------< chk_sequence >------------------------------|
990: where psp.spinal_point_id = p_grade_or_spinal_point_id
991: and psp.sequence = p_sequence;
992: --
993: begin
994: hr_utility.set_location('Entering:'|| l_proc, 1);
995: --
996: -- Check mandatory parameters have been set
997: --
998: hr_api.mandatory_arg_error
1024: ,p_argument => 'effective_end_date'
1025: ,p_argument_value => p_effective_end_date
1026: );
1027: --
1028: hr_utility.set_location(l_proc, 2);
1029: --
1030: -- Check that combination of Sequence, Business Group,
1031: -- Rate ID and Rate Type is unique on pay_grade_rules_f
1032: --
1039: open csr_valid_grade_seq;
1040: fetch csr_valid_grade_seq into l_exists;
1041: if csr_valid_grade_seq%notfound then
1042: close csr_valid_grade_seq;
1043: hr_utility.set_message(801, 'HR_7318_GRR_INV_GRADE_SEQUENCE');
1044: hr_utility.raise_error;
1045: end if;
1046: close csr_valid_grade_seq;
1047: elsif p_rate_type = 'SP' then
1040: fetch csr_valid_grade_seq into l_exists;
1041: if csr_valid_grade_seq%notfound then
1042: close csr_valid_grade_seq;
1043: hr_utility.set_message(801, 'HR_7318_GRR_INV_GRADE_SEQUENCE');
1044: hr_utility.raise_error;
1045: end if;
1046: close csr_valid_grade_seq;
1047: elsif p_rate_type = 'SP' then
1048: open csr_valid_sp_seq;
1048: open csr_valid_sp_seq;
1049: fetch csr_valid_sp_seq into l_exists;
1050: if csr_valid_sp_seq%notfound then
1051: close csr_valid_sp_seq;
1052: hr_utility.set_message(801, 'HR_7319_GRR_INV_SPIN_POINT_SEQ');
1053: hr_utility.raise_error;
1054: end if;
1055: close csr_valid_sp_seq;
1056: end if;
1049: fetch csr_valid_sp_seq into l_exists;
1050: if csr_valid_sp_seq%notfound then
1051: close csr_valid_sp_seq;
1052: hr_utility.set_message(801, 'HR_7319_GRR_INV_SPIN_POINT_SEQ');
1053: hr_utility.raise_error;
1054: end if;
1055: close csr_valid_sp_seq;
1056: end if;
1057: end if;
1055: close csr_valid_sp_seq;
1056: end if;
1057: end if;
1058: --
1059: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1060: end chk_sequence;
1061: --
1062: -- ---------------------------------------------------------------------------
1063: -- |---------------------------< chk_value >---------------------------------|
1122: where pr.rate_id = p_rate_id
1123: and pbg.business_group_id = pr.business_group_id;
1124: --
1125: begin
1126: hr_utility.set_location('Entering:'|| l_proc, 1);
1127: --
1128: -- p_value reassigned to l_value for use within
1129: -- the checkformat procedure call as in/out argument
1130: --
1162: if ((l_api_updating and
1163: nvl(pay_grr_shd.g_old_rec.value, hr_api.g_varchar2) <>
1164: nvl(l_value, hr_api.g_varchar2)) or
1165: (NOT l_api_updating)) then
1166: hr_utility.set_location(l_proc, 2);
1167: --
1168: -- Check that value for Value is >= Minimum and <= Maximum
1169: -- (if Minimum, Maximum NOT NULL)
1170: --
1176: --
1177: -- If no currency exists for a particular
1178: -- business group then flag an error
1179: --
1180: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1181: hr_utility.set_message_token('PROCEDURE', l_proc);
1182: hr_utility.set_message_token('STEP', '5');
1183: end if;
1184: close csel1;
1177: -- If no currency exists for a particular
1178: -- business group then flag an error
1179: --
1180: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1181: hr_utility.set_message_token('PROCEDURE', l_proc);
1182: hr_utility.set_message_token('STEP', '5');
1183: end if;
1184: close csel1;
1185: --
1178: -- business group then flag an error
1179: --
1180: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1181: hr_utility.set_message_token('PROCEDURE', l_proc);
1182: hr_utility.set_message_token('STEP', '5');
1183: end if;
1184: close csel1;
1185: --
1186: -- Check 1 : value > minimum
1195: rgeflg => l_min_max_status,
1196: curcode => l_currency_code);
1197: --
1198: if (l_min_max_status = 'F') then
1199: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
1200: hr_utility.raise_error;
1201: end if;
1202: --
1203: -- Check 2 : value < maximum
1196: curcode => l_currency_code);
1197: --
1198: if (l_min_max_status = 'F') then
1199: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
1200: hr_utility.raise_error;
1201: end if;
1202: --
1203: -- Check 2 : value < maximum
1204: --
1212: rgeflg => l_min_max_status,
1213: curcode => l_currency_code);
1214: --
1215: if (l_min_max_status = 'F') then
1216: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
1217: hr_utility.raise_error;
1218: end if;
1219: end if;
1220: end if;
1213: curcode => l_currency_code);
1214: --
1215: if (l_min_max_status = 'F') then
1216: hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
1217: hr_utility.raise_error;
1218: end if;
1219: end if;
1220: end if;
1221: --
1218: end if;
1219: end if;
1220: end if;
1221: --
1222: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1223: end ;
1224: --
1225: -- ----------------------------------------------------------------------------
1226: -- |----------------------< check_non_updateable_args >-----------------------|
1259: l_error exception;
1260: l_argument varchar2(30);
1261: --
1262: Begin
1263: hr_utility.set_location('Entering:'||l_proc, 5);
1264: --
1265: -- Only proceed with validation if a row exists for
1266: -- the current record in the HR Schema
1267: --
1269: (p_grade_rule_id => p_rec.grade_rule_id
1270: ,p_object_version_number => p_rec.object_version_number
1271: ,p_effective_date => p_effective_date
1272: ) then
1273: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1274: hr_utility.set_message_token('PROCEDURE', l_proc);
1275: hr_utility.set_message_token('STEP', '5');
1276: end if;
1277: --
1270: ,p_object_version_number => p_rec.object_version_number
1271: ,p_effective_date => p_effective_date
1272: ) then
1273: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1274: hr_utility.set_message_token('PROCEDURE', l_proc);
1275: hr_utility.set_message_token('STEP', '5');
1276: end if;
1277: --
1278: hr_utility.set_location(l_proc, 6);
1271: ,p_effective_date => p_effective_date
1272: ) then
1273: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1274: hr_utility.set_message_token('PROCEDURE', l_proc);
1275: hr_utility.set_message_token('STEP', '5');
1276: end if;
1277: --
1278: hr_utility.set_location(l_proc, 6);
1279: --
1274: hr_utility.set_message_token('PROCEDURE', l_proc);
1275: hr_utility.set_message_token('STEP', '5');
1276: end if;
1277: --
1278: hr_utility.set_location(l_proc, 6);
1279: --
1280: if nvl(p_rec.business_group_id, hr_api.g_number) <>
1281: nvl(pay_grr_shd.g_old_rec.business_group_id, hr_api.g_number) then
1282: l_argument := 'business_group_id';
1281: nvl(pay_grr_shd.g_old_rec.business_group_id, hr_api.g_number) then
1282: l_argument := 'business_group_id';
1283: raise l_error;
1284: end if;
1285: hr_utility.set_location(l_proc, 7);
1286: --
1287: if p_rec.grade_rule_id <> pay_grr_shd.g_old_rec.grade_rule_id then
1288: l_argument := 'grade_rule_id';
1289: raise l_error;
1287: if p_rec.grade_rule_id <> pay_grr_shd.g_old_rec.grade_rule_id then
1288: l_argument := 'grade_rule_id';
1289: raise l_error;
1290: end if;
1291: hr_utility.set_location(l_proc, 8);
1292: --
1293: if nvl(p_rec.grade_or_spinal_point_id, hr_api.g_number) <>
1294: nvl(pay_grr_shd.g_old_rec.grade_or_spinal_point_id, hr_api.g_number) then
1295: l_argument := 'grade_or_spinal_point_id';
1294: nvl(pay_grr_shd.g_old_rec.grade_or_spinal_point_id, hr_api.g_number) then
1295: l_argument := 'grade_or_spinal_point_id';
1296: raise l_error;
1297: end if;
1298: hr_utility.set_location(l_proc, 9);
1299: --
1300: if nvl(p_rec.rate_id, hr_api.g_number) <>
1301: nvl(pay_grr_shd.g_old_rec.rate_id, hr_api.g_number) then
1302: l_argument := 'rate_id';
1301: nvl(pay_grr_shd.g_old_rec.rate_id, hr_api.g_number) then
1302: l_argument := 'rate_id';
1303: raise l_error;
1304: end if;
1305: hr_utility.set_location(l_proc, 10);
1306: --
1307: if nvl(p_rec.sequence, hr_api.g_number) <>
1308: nvl(pay_grr_shd.g_old_rec.sequence, hr_api.g_number) then
1309: l_argument := 'sequence';
1308: nvl(pay_grr_shd.g_old_rec.sequence, hr_api.g_number) then
1309: l_argument := 'sequence';
1310: raise l_error;
1311: end if;
1312: hr_utility.set_location(l_proc, 11);
1313: --
1314: if nvl(p_rec.rate_type, hr_api.g_varchar2) <>
1315: nvl(pay_grr_shd.g_old_rec.rate_type, hr_api.g_varchar2) then
1316: l_argument := 'rate_type';
1315: nvl(pay_grr_shd.g_old_rec.rate_type, hr_api.g_varchar2) then
1316: l_argument := 'rate_type';
1317: raise l_error;
1318: end if;
1319: hr_utility.set_location(l_proc, 12);
1320: exception
1321: when l_error then
1322: hr_api.argument_changed_error
1323: (p_api_name => l_proc
1323: (p_api_name => l_proc
1324: ,p_argument => l_argument);
1325: when others then
1326: raise;
1327: hr_utility.set_location(' Leaving:'||l_proc, 12);
1328: end check_non_updateable_args;
1329: --
1330: -- ----------------------------------------------------------------------------
1331: -- |--------------------------< dt_update_validate >--------------------------|
1366: l_integrity_error Exception;
1367: l_table_name all_tables.table_name%TYPE;
1368: --
1369: Begin
1370: hr_utility.set_location('Entering:'||l_proc, 5);
1371: --
1372: -- Ensure that the p_datetrack_mode argument is not null
1373: --
1374: hr_api.mandatory_arg_error
1396: --
1397: --
1398: End If;
1399: --
1400: hr_utility.set_location(' Leaving:'||l_proc, 10);
1401: Exception
1402: When l_integrity_error Then
1403: --
1404: -- A referential integrity check was violated therefore
1403: --
1404: -- A referential integrity check was violated therefore
1405: -- we must error
1406: --
1407: hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1408: hr_utility.set_message_token('TABLE_NAME', l_table_name);
1409: hr_utility.raise_error;
1410: When Others Then
1411: --
1404: -- A referential integrity check was violated therefore
1405: -- we must error
1406: --
1407: hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1408: hr_utility.set_message_token('TABLE_NAME', l_table_name);
1409: hr_utility.raise_error;
1410: When Others Then
1411: --
1412: -- An unhandled or unexpected error has occurred which
1405: -- we must error
1406: --
1407: hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1408: hr_utility.set_message_token('TABLE_NAME', l_table_name);
1409: hr_utility.raise_error;
1410: When Others Then
1411: --
1412: -- An unhandled or unexpected error has occurred which
1413: -- we must report
1411: --
1412: -- An unhandled or unexpected error has occurred which
1413: -- we must report
1414: --
1415: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1416: hr_utility.set_message_token('PROCEDURE', l_proc);
1417: hr_utility.set_message_token('STEP','15');
1418: hr_utility.raise_error;
1419: End dt_update_validate;
1412: -- An unhandled or unexpected error has occurred which
1413: -- we must report
1414: --
1415: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1416: hr_utility.set_message_token('PROCEDURE', l_proc);
1417: hr_utility.set_message_token('STEP','15');
1418: hr_utility.raise_error;
1419: End dt_update_validate;
1420: --
1413: -- we must report
1414: --
1415: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1416: hr_utility.set_message_token('PROCEDURE', l_proc);
1417: hr_utility.set_message_token('STEP','15');
1418: hr_utility.raise_error;
1419: End dt_update_validate;
1420: --
1421: -- ----------------------------------------------------------------------------
1414: --
1415: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1416: hr_utility.set_message_token('PROCEDURE', l_proc);
1417: hr_utility.set_message_token('STEP','15');
1418: hr_utility.raise_error;
1419: End dt_update_validate;
1420: --
1421: -- ----------------------------------------------------------------------------
1422: -- |--------------------------< dt_delete_validate >--------------------------|
1463: l_rows_exist Exception;
1464: l_table_name all_tables.table_name%TYPE;
1465: --
1466: Begin
1467: hr_utility.set_location('Entering:'||l_proc, 5);
1468: --
1469: -- Ensure that the p_datetrack_mode argument is not null
1470: --
1471: hr_api.mandatory_arg_error
1500: --
1501: --
1502: End If;
1503: --
1504: hr_utility.set_location(' Leaving:'||l_proc, 10);
1505: Exception
1506: When l_rows_exist Then
1507: --
1508: -- A referential integrity check was violated therefore
1507: --
1508: -- A referential integrity check was violated therefore
1509: -- we must error
1510: --
1511: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1512: hr_utility.set_message_token('TABLE_NAME', l_table_name);
1513: hr_utility.raise_error;
1514: When Others Then
1515: --
1508: -- A referential integrity check was violated therefore
1509: -- we must error
1510: --
1511: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1512: hr_utility.set_message_token('TABLE_NAME', l_table_name);
1513: hr_utility.raise_error;
1514: When Others Then
1515: --
1516: -- An unhandled or unexpected error has occurred which
1509: -- we must error
1510: --
1511: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1512: hr_utility.set_message_token('TABLE_NAME', l_table_name);
1513: hr_utility.raise_error;
1514: When Others Then
1515: --
1516: -- An unhandled or unexpected error has occurred which
1517: -- we must report
1515: --
1516: -- An unhandled or unexpected error has occurred which
1517: -- we must report
1518: --
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','15');
1522: hr_utility.raise_error;
1523: End dt_delete_validate;
1516: -- An unhandled or unexpected error has occurred which
1517: -- we must report
1518: --
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','15');
1522: hr_utility.raise_error;
1523: End dt_delete_validate;
1524: --
1517: -- we must report
1518: --
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','15');
1522: hr_utility.raise_error;
1523: End dt_delete_validate;
1524: --
1525: -- ----------------------------------------------------------------------------
1518: --
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','15');
1522: hr_utility.raise_error;
1523: End dt_delete_validate;
1524: --
1525: -- ----------------------------------------------------------------------------
1526: -- |---------------------------< insert_validate >----------------------------|
1534: --
1535: l_proc varchar2(72) := g_package||'insert_validate';
1536: --
1537: Begin
1538: hr_utility.set_location('Entering:'||l_proc, 5);
1539: --
1540: -- Call all supporting business operations
1541: --
1542: --
1547: -- Rule CHK_BUSINESS_GROUP_ID / a,c
1548: --
1549: hr_api.validate_bus_grp_id(p_rec.business_group_id);
1550: --
1551: hr_utility.set_location(l_proc, 6);
1552: --
1553: -- Validate grade or spinal point id
1554: --
1555: -- Business Rule Mapping
1566: ,p_rate_type => p_rec.rate_type
1567: ,p_rate_id => p_rec.rate_id
1568: );
1569: --
1570: hr_utility.set_location(l_proc, 8);
1571: --
1572: -- Validate rate id
1573: --
1574: chk_rate_id
1575: (p_rate_id => p_rec.rate_id,
1576: p_business_group_id => p_rec.business_group_id
1577: );
1578: --
1579: hr_utility.set_location(l_proc, 9);
1580: --
1581: -- Validate rate type
1582: --
1583: -- Business Rule Mapping
1588: (p_rate_id => p_rec.rate_id
1589: ,p_rate_type => p_rec.rate_type
1590: );
1591: --
1592: hr_utility.set_location(l_proc, 10);
1593: --
1594: -- Validate maximum
1595: --
1596: -- Business Rule Mapping
1607: ,p_effective_date => p_effective_date
1608: ,p_object_version_number => p_rec.object_version_number
1609: );
1610: --
1611: hr_utility.set_location(l_proc, 11);
1612: --
1613: -- Validate mid value
1614: --
1615: -- Business Rule Mapping
1626: ,p_effective_date => p_effective_date
1627: ,p_object_version_number => p_rec.object_version_number
1628: );
1629: --
1630: hr_utility.set_location(l_proc, 12);
1631: --
1632: -- Validate minimum
1633: --
1634: -- Business Rule Mapping
1645: ,p_effective_date => p_effective_date
1646: ,p_object_version_number => p_rec.object_version_number
1647: );
1648: --
1649: hr_utility.set_location(l_proc, 13);
1650: --
1651: -- Validate sequence
1652: --
1653: -- Business Rule Mapping
1663: ,p_effective_start_date => p_validation_start_date
1664: ,p_effective_end_date => p_validation_end_date
1665: );
1666: --
1667: hr_utility.set_location(l_proc, 14);
1668: --
1669: -- Validate value
1670: --
1671: -- Business Rule Mapping
1682: ,p_effective_date => p_effective_date
1683: ,p_object_version_number => p_rec.object_version_number
1684: );
1685: --
1686: hr_utility.set_location(' Leaving:'||l_proc, 15);
1687: End insert_validate;
1688: --
1689: -- ----------------------------------------------------------------------------
1690: -- |---------------------------< update_validate >----------------------------|
1698: --
1699: l_proc varchar2(72) := g_package||'update_validate';
1700: --
1701: Begin
1702: hr_utility.set_location('Entering:'||l_proc, 5);
1703: --
1704: -- Call all supporting business operations. Mapping to the
1705: -- appropriate Business Rules in pergrr.bru is provided.
1706: --
1717: --
1718: check_non_updateable_args(p_rec => p_rec
1719: ,p_effective_date => p_effective_date);
1720: --
1721: hr_utility.set_location(l_proc, 6);
1722: -- Validate maximum
1723: --
1724: -- Business Rule Mapping
1725: -- =====================
1735: ,p_effective_date => p_effective_date
1736: ,p_object_version_number => p_rec.object_version_number
1737: );
1738: --
1739: hr_utility.set_location(l_proc, 7);
1740: --
1741: -- Validate mid value
1742: --
1743: -- Business Rule Mapping
1754: ,p_effective_date => p_effective_date
1755: ,p_object_version_number => p_rec.object_version_number
1756: );
1757: --
1758: hr_utility.set_location(l_proc, 8);
1759: --
1760: -- Validate minimum
1761: --
1762: -- Business Rule Mapping
1773: ,p_effective_date => p_effective_date
1774: ,p_object_version_number => p_rec.object_version_number
1775: );
1776: --
1777: hr_utility.set_location(l_proc, 9);
1778: --
1779: -- Validate value
1780: --
1781: -- Business Rule Mapping
1792: ,p_effective_date => p_effective_date
1793: ,p_object_version_number => p_rec.object_version_number
1794: );
1795: --
1796: hr_utility.set_location(l_proc, 10);
1797: --
1798: -- Call the datetrack update integrity operation
1799: --
1800: dt_update_validate
1802: p_datetrack_mode => p_datetrack_mode,
1803: p_validation_start_date => p_validation_start_date,
1804: p_validation_end_date => p_validation_end_date);
1805: --
1806: hr_utility.set_location(' Leaving:'||l_proc, 11);
1807: End update_validate;
1808: --
1809: -- ----------------------------------------------------------------------------
1810: -- |---------------------------< delete_validate >----------------------------|
1818: --
1819: l_proc varchar2(72) := g_package||'delete_validate';
1820: --
1821: Begin
1822: hr_utility.set_location('Entering:'||l_proc, 5);
1823: --
1824: -- Call all supporting business operations
1825: --
1826: dt_delete_validate
1828: p_validation_start_date => p_validation_start_date,
1829: p_validation_end_date => p_validation_end_date,
1830: p_grade_rule_id => p_rec.grade_rule_id);
1831: --
1832: hr_utility.set_location(' Leaving:'||l_proc, 10);
1833: End delete_validate;
1834: --
1835: --
1836: -- ---------------------------------------------------------------------------
1855: --
1856: l_legislation_code varchar2(150);
1857: l_proc varchar2(72) := g_package||'return_legislation_code';
1858: begin
1859: hr_utility.set_location('Entering:'|| l_proc, 10);
1860: --
1861: -- Ensure that all the mandatory parameter are not null
1862: --
1863: hr_api.mandatory_arg_error(p_api_name => l_proc,
1870: -- call to this function. Just return the value in the global
1871: -- variable.
1872: --
1873: l_legislation_code := g_legislation_code;
1874: hr_utility.set_location(l_proc, 20);
1875: else
1876: --
1877: -- The ID is different to the last call to this function
1878: -- or this is the first call to this function.
1883: close csr_leg_code;
1884: --
1885: -- The primary key is invalid therefore we must error
1886: --
1887: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1888: hr_utility.raise_error;
1889: end if;
1890: --
1891: -- Set the global variables so the values are
1884: --
1885: -- The primary key is invalid therefore we must error
1886: --
1887: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1888: hr_utility.raise_error;
1889: end if;
1890: --
1891: -- Set the global variables so the values are
1892: -- available for the next call to this function
1894: close csr_leg_code;
1895: g_grade_rule_id := p_grade_rule_id;
1896: g_legislation_code := l_legislation_code;
1897: end if;
1898: hr_utility.set_location(' Leaving:'|| l_proc, 20);
1899: --
1900: return l_legislation_code;
1901: end return_legislation_code;
1902: --