1 PACKAGE BODY HRWCDCR_PKG AS
2 -- $Header: pywcdcr.pkb 115.3 99/08/24 11:55:22 porting ship $
3 --
4 --
5 --
6 --
7 PROCEDURE INSERT_ROW( X_ROWID IN OUT VARCHAR2,
8 X_FUND_ID IN OUT NUMBER,
9 X_BUSINESS_GROUP_ID NUMBER,
10 X_CARRIER_ID NUMBER,
11 X_LOCATION_ID NUMBER,
12 X_STATE_CODE VARCHAR2,
13 X_CALCULATION_METHOD VARCHAR2,
14 X_CALCULATION_METHOD2 VARCHAR2,
15 X_CALCULATION_METHOD3 VARCHAR2) IS
16 BEGIN
17 hr_utility.set_location('pywcdcr.insert_row', 0);
18 --
19 SELECT PAY_WC_FUNDS_S.NEXTVAL
20 INTO X_FUND_ID
21 FROM DUAL;
22 --
23 hr_utility.set_location('pywcdcr.insert_row', 1);
24 --
25 IF (SQL%NOTFOUND) THEN
26 RAISE NO_DATA_FOUND;
27 END IF;
28 --
29 hr_utility.set_location('pywcdcr.insert_row', 2);
30 --
31 INSERT INTO PAY_WC_FUNDS
32 (FUND_ID, BUSINESS_GROUP_ID, CARRIER_ID, LOCATION_ID,
33 STATE_CODE, CALCULATION_METHOD, CALCULATION_METHOD2, CALCULATION_METHOD3)
34 VALUES
35 (X_FUND_ID, X_BUSINESS_GROUP_ID, X_CARRIER_ID, X_LOCATION_ID,
36 X_STATE_CODE, X_CALCULATION_METHOD, X_CALCULATION_METHOD2, X_CALCULATION_METHOD3);
37 --
38 hr_utility.set_location('pywcdcr.insert_row', 3);
39 --
40 SELECT ROWID
41 INTO X_ROWID
42 FROM PAY_WC_FUNDS
43 WHERE FUND_ID = X_FUND_ID;
44 --
45 hr_utility.set_location('pywcdcr.insert_row', 4);
46 --
47 IF (SQL%NOTFOUND) THEN
48 RAISE NO_DATA_FOUND;
49 END IF;
50 --
51 END INSERT_ROW;
52 --
53 --
54 --
55 PROCEDURE UPDATE_ROW( X_ROWID VARCHAR2,
56 X_FUND_ID NUMBER,
57 X_BUSINESS_GROUP_ID NUMBER,
58 X_CARRIER_ID NUMBER,
59 X_LOCATION_ID NUMBER,
60 X_STATE_CODE VARCHAR2,
61 X_CALCULATION_METHOD VARCHAR2,
62 X_CALCULATION_METHOD2 VARCHAR2,
63 X_CALCULATION_METHOD3 VARCHAR2) IS
64 BEGIN
65 hr_utility.set_location('pywcdcr.update_row', 0);
66 --
67 UPDATE PAY_WC_FUNDS
68 SET FUND_ID = X_FUND_ID
69 , BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
70 , CARRIER_ID = X_CARRIER_ID
71 , LOCATION_ID = X_LOCATION_ID
72 , STATE_CODE = X_STATE_CODE
73 , CALCULATION_METHOD = X_CALCULATION_METHOD
74 , CALCULATION_METHOD2 = X_CALCULATION_METHOD2
75 , CALCULATION_METHOD3 = X_CALCULATION_METHOD3
76 WHERE ROWID = X_ROWID;
77 --
78 hr_utility.set_location('pywcdcr.update_row', 1);
79 --
80 IF (SQL%NOTFOUND) THEN
81 RAISE NO_DATA_FOUND;
82 END IF;
83 --
84 hr_utility.set_location('pywcdcr.update_row', 2);
85 --
86 END UPDATE_ROW;
87 --
88 --
89 --
90 PROCEDURE DELETE_ROW( X_ROWID VARCHAR2,
91 X_FUND_ID NUMBER,
92 X_BUSINESS_GROUP_ID NUMBER,
93 X_CARRIER_ID NUMBER,
94 X_LOCATION_ID NUMBER,
95 X_STATE_CODE VARCHAR2,
96 X_CALCULATION_METHOD VARCHAR2,
97 X_CALCULATION_METHOD2 VARCHAR2,
98 X_CALCULATION_METHOD3 VARCHAR2) IS
99 BEGIN
100 hr_utility.set_location('pywcdcr.delete_row', 0);
101 --
102 DELETE FROM PAY_WC_FUNDS
103 WHERE ROWID = X_ROWID;
104 --
105 hr_utility.set_location('pywcdcr.delete_row', 1);
106 --
107 IF (SQL%NOTFOUND) THEN
108 RAISE NO_DATA_FOUND;
109 END IF;
110 --
111 hr_utility.set_location('pywcdcr.delete_row', 2);
112 --
113 END DELETE_ROW;
114 --
115 --
116 --
117 PROCEDURE LOCK_ROW( X_ROWID VARCHAR2,
118 X_FUND_ID NUMBER,
119 X_BUSINESS_GROUP_ID NUMBER,
120 X_CARRIER_ID NUMBER,
121 X_LOCATION_ID NUMBER,
122 X_STATE_CODE VARCHAR2,
123 X_CALCULATION_METHOD VARCHAR2,
124 X_CALCULATION_METHOD2 VARCHAR2,
125 X_CALCULATION_METHOD3 VARCHAR2) IS
126 --
127 CURSOR C IS
128 SELECT *
129 FROM PAY_WC_FUNDS
130 WHERE ROWID = X_ROWID
131 FOR UPDATE OF FUND_ID NOWAIT;
132 --
133 RECINFO C%ROWTYPE;
134 --
135 BEGIN
136
137 hr_utility.set_location('pywcdcr.lock_row', 0);
138 --
139 OPEN C;
140 FETCH C INTO RECINFO;
141 IF (C%NOTFOUND) THEN
142 CLOSE C;
143 RAISE NO_DATA_FOUND;
144 END IF;
145 CLOSE C;
146 --
147 hr_utility.set_location('pywcdcr.lock_row', 1);
148 --
149 --
150 -- rtrim char fields
151 --
152 Recinfo.state_code := RTRIM(Recinfo.state_code);
153 --
154 hr_utility.set_location('pywcdcr.lock_row', 2);
155 --
156 IF( ( ( RECINFO.FUND_ID = X_FUND_ID)
157 OR ( RECINFO.FUND_ID IS NULL AND X_FUND_ID IS NULL))
158 AND
159 ( ( RECINFO.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
160 OR ( RECINFO.BUSINESS_GROUP_ID IS NULL AND X_BUSINESS_GROUP_ID IS NULL))
161 AND
162 ( ( RECINFO.CARRIER_ID = X_CARRIER_ID)
163 OR ( RECINFO.CARRIER_ID IS NULL AND X_CARRIER_ID IS NULL))
164 AND
165 ( ( RECINFO.LOCATION_ID = X_LOCATION_ID)
166 OR ( RECINFO.LOCATION_ID IS NULL AND X_LOCATION_ID IS NULL))
167 AND
168 ( ( RECINFO.STATE_CODE = X_STATE_CODE)
169 OR ( RECINFO.STATE_CODE IS NULL AND X_STATE_CODE IS NULL))
170 AND
171 ( ( RECINFO.CALCULATION_METHOD = X_CALCULATION_METHOD)
172 OR ( RECINFO.CALCULATION_METHOD IS NULL AND X_CALCULATION_METHOD IS NULL))
173 AND
174 ( ( RECINFO.CALCULATION_METHOD2 = X_CALCULATION_METHOD2)
175 OR ( RECINFO.CALCULATION_METHOD2 IS NULL AND X_CALCULATION_METHOD2 IS NULL))
176 AND
177 ( ( RECINFO.CALCULATION_METHOD3 = X_CALCULATION_METHOD3)
178 OR ( RECINFO.CALCULATION_METHOD3 IS NULL AND X_CALCULATION_METHOD3 IS NULL))
179 ) THEN
180 --
181 hr_utility.set_location('pywcdcr.lock_row', 3);
182 --
183 RETURN;
184 ELSE
185 --
186 hr_utility.set_location('pywcdcr.lock_row', 4);
187 --
188 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
189 hr_utility.raise_error;
190 END IF;
191 --
192 END LOCK_ROW;
193 --
194 --
195 PROCEDURE CARRIER_STATE_LOC_UNIQUE( P_ROWID VARCHAR2,
196 P_CARRIER_ID NUMBER,
197 P_STATE_CODE VARCHAR2,
198 P_LOCATION_ID NUMBER) IS
199 --
200 --
201 l_comb_exists VARCHAR2(2);
202 --
203 CURSOR DUP_REC IS
204 SELECT 'Y'
205 FROM PAY_WC_FUNDS
206 WHERE CARRIER_ID = P_CARRIER_ID
207 AND STATE_CODE = P_STATE_CODE
208 AND ((LOCATION_ID = P_LOCATION_ID
209 AND P_LOCATION_ID IS NOT NULL)
210 OR (LOCATION_ID IS NULL
211 AND P_LOCATION_ID IS NULL))
212 AND ((ROWID <> P_ROWID
213 AND P_ROWID IS NOT NULL)
214 OR
215 (P_ROWID IS NULL));
216 --
217 --
218 BEGIN
219 --
220 hr_utility.set_location('pywcdcr.carrier_state_loc_unique', 0);
221 --
222 --
223 -- initialise variable
224 l_comb_exists := 'N';
225 --
226 -- open fetch and close the cursor - if a record is found then the local
227 -- variable will be set to 'Y', otherwise it will remain 'N'
228 --
229 OPEN DUP_REC;
230 FETCH DUP_REC INTO l_comb_exists;
231 CLOSE DUP_REC;
232 --
233 hr_utility.set_location('pywcdcr.carrier_state_loc_unique', 1);
234 --
235 -- go ahead and check the value of the local variable - if it's 'Y' then this
236 -- record is duplicated
237 --
238 IF (l_comb_exists = 'Y')
239 THEN
240 hr_utility.set_message(801, 'HR_13104_WC_FUND_STATE_LOC_DUP');
241 hr_utility.raise_error;
242 END IF;
243 --
244 hr_utility.set_location('pywcdcr.carrier_state_loc_unique', 2);
245 --
246 --
247 END CARRIER_STATE_LOC_UNIQUE;
248 --
249 --
250 PROCEDURE FUND_WC_CODE_UNIQUE( P_ROWID VARCHAR2,
251 P_FUND_ID NUMBER,
252 P_WC_CODE NUMBER) IS
253 --
254 --
255 l_comb_exists VARCHAR2(2);
256 --
257 CURSOR DUP_REC IS
258 SELECT 'Y'
259 FROM PAY_WC_RATES
260 WHERE FUND_ID = P_FUND_ID
261 AND WC_CODE = P_WC_CODE
262 AND ((ROWID <> P_ROWID
263 AND P_ROWID IS NOT NULL)
264 OR
265 (P_ROWID IS NULL));
266 --
267 --
268 BEGIN
269 --
270 hr_utility.set_location('pywcdcr.fund_wc_code_unique', 0);
271 --
272 -- initialise variable
273 l_comb_exists := 'N';
274 --
275 -- open fetch and close the cursor - if a record is found then the local
276 -- variable will be set to 'Y', otherwise it will remain 'N'
277 --
278 OPEN DUP_REC;
279 FETCH DUP_REC INTO l_comb_exists;
280 CLOSE DUP_REC;
281 --
282 hr_utility.set_location('pywcdcr.fund_wc_code_unique', 1);
283 --
284 -- go ahead and check the value of the local variable - if it's 'Y' then this
285 -- record is duplicated
286 --
287 IF (l_comb_exists = 'Y')
288 THEN
289 hr_utility.set_message(801, 'HR_13105_WC_DUP_WC_CODE');
290 hr_utility.raise_error;
291 END IF;
292 --
293 hr_utility.set_location('pywcdcr.fund_wc_code_unique', 2);
294 --
295 --
296 END FUND_WC_CODE_UNIQUE;
297 --
298 --
299 PROCEDURE CODES_AND_RATES_EXIST( P_FUND_ID NUMBER) IS
300 --
301 --
302 l_rec_exists VARCHAR2(2);
303 --
304 CURSOR REC_EXISTS IS
305 SELECT 'Y'
306 FROM PAY_WC_RATES
307 WHERE FUND_ID = P_FUND_ID;
308 --
309 --
310 BEGIN
311 --
312 hr_utility.set_location('pywcdcr.codes_and_rates_exist', 0);
313 --
314 -- initialise variable
315 l_rec_exists := 'N';
316 --
317 -- open fetch and close the cursor - if a record is found then the local
318 -- variable will be set to 'Y', otherwise it will remain 'N'
319 --
320 OPEN REC_EXISTS;
321 FETCH REC_EXISTS INTO l_rec_exists;
322 CLOSE REC_EXISTS;
323 --
324 hr_utility.set_location('pywcdcr.codes_and_rates_exist', 1);
325 --
326 -- go ahead and check the value of the local variable - if it's 'Y' then this
327 -- record is duplicated
328 --
329 IF (l_rec_exists = 'Y')
330 THEN
331 hr_utility.set_message(801, 'HR_13106_WC_CODES_RATES_EXIST');
332 hr_utility.raise_error;
333 END IF;
334 --
335 hr_utility.set_location('pywcdcr.codes_and_rates_exist', 2);
336 --
337 --
338 END CODES_AND_RATES_EXIST;
339 --
340 --
341 PROCEDURE CODE_IN_USE ( P_FUND_ID NUMBER,
342 P_STATE_CODE VARCHAR2,
343 P_ROWID VARCHAR2,
344 P_WC_CODE NUMBER,
345 P_BUSINESS_GROUP_ID NUMBER,
346 P_CARRIER_ID NUMBER,
347 P_LOCATION_ID NUMBER) IS
348 --
349 -- Overview
350 --
351 -- This validation ensures that a WC code is not deleted if there are either
352 --
353 -- a) any jobs using this code (for the specified state code)
354 --
355 -- b) if there are any WC overrides using the code
356 --
357 -- Case b) (above) is not entirely straightforward: WC codes/rates are
358 -- defined either for a specific location (ie a location_id is present) or
359 -- the user can leave the location null. When the location is left null then
360 -- that code/rate is treated as the default. There can be many instances of
361 -- the same code being defined more than once for a given fund - eg the
362 -- California State Fund may have a rate set up for San Francisco, another
363 -- for Los Angeles, another for Sacremento and a rate which is used for all
364 -- other locations in California: all of these rates use the same WC code.
365 -- When determining whether it is possible to delete a WC code/rate record
366 -- you must ensure any assignment's override is valid. For example, if the
367 -- user attempted to delete the code 1234 which has been set up for Los
368 -- Angeles, then the validation must ensure that there is a default code/rate
369 -- which can be used (ie the location code is null); similarly, if the
370 -- user wishes to delete the default rates then the validation must ensure
371 -- that each override is valid (ie using the above example all assignments
372 -- which use an override WC code must have a location of either San
373 -- Francisco, Los Angeles or Sacremento).
374 --
375 -- When attempting to validate b) there are three possible scenarios -
376 --
377 -- i) there is only one occurrance of the WC code defined for the state:
378 -- therefore if any overrides are using this code then disallow the delete
379 --
380 -- ii) there are multiple occurrences of the WC code defined for the state:
381 -- in this case check if there are any overrides using this code -
382 --
383 -- if there are any then ensure that these overrides are still valid
384 -- (ie it is still possible to find a rate based on the location, SUI
385 -- state and organization)
386 --
387 -- if there are no overrides using this code then go ahead and do the
388 -- delete
389 --
390 --
391 -- The validation is split into two, depending on how many occurrences of
392 -- the code exist for the state -
393 --
394 -- a) if there is only one occurrence of this code (ie the record being
395 -- deleted is the only one) then disallow the delete if there are any
396 -- jobs using the WC code, or if there are any overrides using this code
397 --
398 -- b) there are multiple occurrences of this code then you do not need to
399 -- check the job wc code usages table to see if a job is using this code;
400 -- you will need to ensure that any overrides using this code are still
401 -- valid.
402 --
403 --
404 --
405 --
406 -- OK, here's the code...
407 --
408 l_rec_exists VARCHAR2(2);
409 --
410 --
411 -- determines whether there are any other funds for the same state using the
412 -- same WC code as the one about to be deleted
413 --
414 CURSOR SAME_CODE_EXISTS IS
415 SELECT 'Y'
416 FROM PAY_WC_FUNDS WCF
417 WHERE WCF.STATE_CODE = P_STATE_CODE
418 AND WCF.ROWID <> P_ROWID
419 AND EXISTS (
420 SELECT 1
421 FROM PAY_WC_RATES WCR
422 WHERE WCR.FUND_ID = WCF.FUND_ID
423 AND WCR.WC_CODE = P_WC_CODE);
424 --
425 --
426 -- determines whether any jobs are using the WC code about to be deleted
427 -- (need only check for records in the same US state)
428 --
429 CURSOR CODE_USED_BY_JOB IS
430 SELECT 'Y'
431 FROM PAY_JOB_WC_CODE_USAGES
432 WHERE STATE_CODE = P_STATE_CODE
433 AND WC_CODE = P_WC_CODE
434 AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID;
435 --
436 --
437 -- determines whether there are any assignment SCLs using the
438 -- soon-to-be-deleted WC code override
439 --
440 -- note the convoluted access path -> in English, the query must find all
441 -- assignment SCL where the override WC code = the one about to be deleted;
442 -- this search has a context of US state, so we must restrict the assignments
443 -- by the WC Fund's state (ie we are deleting a Californian code, so we
444 -- should only check assignments with a SUI state for California) - the SUI
445 -- state is held on yet more SCL, but hidden by a view (PAY_EMP_FED_TAX_V1);
446 -- finally, we restrict the search to assignments which use the carrier as
447 -- defined by the fund - yep, you've got it - more SCL - an assignment's SCL
448 -- holds the tax unit and the tax unit holds the WC carrier id in (drum role)
449 -- more SCL.
450 --
451 -- It's all very simple, really.
452 --
453 CURSOR ONE_CODE_AND_OVERRIDES_EXIST IS
454 SELECT 'Y'
455 FROM HR_ORGANIZATION_INFORMATION ORG
456 , PAY_US_EMP_FED_TAX_RULES_F EFT
457 , PER_ALL_ASSIGNMENTS_F ASS
458 , HR_SOFT_CODING_KEYFLEX SCF
459 , FND_ID_FLEX_STRUCTURES_VL IFS
460 WHERE ORG.ORG_INFORMATION8 = P_CARRIER_ID
461 AND ORG.ORG_INFORMATION1 = P_STATE_CODE
462 AND ORG.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
463 AND ORG.ORGANIZATION_ID = SCF.SEGMENT1
464 AND EFT.SUI_STATE_CODE = P_STATE_CODE
465 AND EFT.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
466 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
467 AND ASS.SOFT_CODING_KEYFLEX_ID = SCF.SOFT_CODING_KEYFLEX_ID
468 AND SCF.SEGMENT8 = TO_CHAR(P_WC_CODE)
469 AND SCF.ID_FLEX_NUM = IFS.ID_FLEX_NUM
470 AND IFS.ID_FLEX_STRUCTURE_NAME = 'GREs and other data';
471 --
472 --
473 CURSOR MANY_CODES_AND_OVERRIDES_EXIST IS
474 SELECT 'Y'
475 FROM HR_ORGANIZATION_INFORMATION ORG
476 , PAY_US_EMP_FED_TAX_RULES_F EFT
477 , PER_ALL_ASSIGNMENTS_F ASS
478 , HR_SOFT_CODING_KEYFLEX SCF
479 , FND_ID_FLEX_STRUCTURES_VL IFS
480 WHERE ORG.ORG_INFORMATION8 = P_CARRIER_ID
481 AND ORG.ORG_INFORMATION1 = P_STATE_CODE
482 AND ORG.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
483 AND ORG.ORGANIZATION_ID = SCF.SEGMENT1
484 AND EFT.SUI_STATE_CODE = P_STATE_CODE
485 AND EFT.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
486 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
487 AND ASS.SOFT_CODING_KEYFLEX_ID = SCF.SOFT_CODING_KEYFLEX_ID
488 AND SCF.SEGMENT8 = TO_CHAR(P_WC_CODE)
489 AND SCF.ID_FLEX_NUM = IFS.ID_FLEX_NUM
490 AND IFS.ID_FLEX_STRUCTURE_NAME = 'GREs and other data'
491 AND NOT EXISTS (
492 SELECT 1
493 FROM PAY_WC_FUNDS WCF
494 WHERE WCF.CARRIER_ID = P_CARRIER_ID
495 AND WCF.STATE_CODE = P_STATE_CODE
496 AND WCF.ROWID <> P_ROWID
497 AND ((WCF.LOCATION_ID IS NULL)
498 OR (WCF.LOCATION_ID = P_LOCATION_ID
499 AND WCF.LOCATION_ID IS NOT NULL
500 AND P_LOCATION_ID IS NOT NULL)));
501 --
502 --
503 BEGIN
504 hr_utility.set_location('pywcdcr.code_in_use', 0);
505 --
506 -- initialise variable
507 --
508 l_rec_exists := 'N';
509 --
510 -- open fetch and close the cursor - if a record is found then the local
511 -- variable will be set to 'Y', otherwise it will remain 'N'
512 --
513 OPEN SAME_CODE_EXISTS;
514 FETCH SAME_CODE_EXISTS INTO l_rec_exists;
515 CLOSE SAME_CODE_EXISTS;
516 --
517 hr_utility.set_location('pywcdcr.code_in_use', 1);
518 --
519 -- go ahead and check the value of the local variable - if it's 'Y' then
520 -- another fund is using this code; so we can just go ahead and do the delete
521 --
522 IF (l_rec_exists = 'Y') THEN
523 --
524 hr_utility.set_location('pywcdcr.code_in_use', 2);
525 --
526 -- re-initialise variable
527 --
528 l_rec_exists := 'N';
529 --
530 -- see if there any any overrides which would be invalid if this code
531 -- was deleted
532 --
533 OPEN MANY_CODES_AND_OVERRIDES_EXIST;
534 FETCH MANY_CODES_AND_OVERRIDES_EXIST INTO l_rec_exists;
535 CLOSE MANY_CODES_AND_OVERRIDES_EXIST;
536 --
537 hr_utility.set_location('pywcdcr.code_in_use', 3);
538 --
539 -- if a record exists then raise error and tell user that the code is
540 -- in use
541 --
542 IF (l_rec_exists = 'Y')
543 THEN
544 hr_utility.set_message(801, 'HR_13129_WC_CODE_IS_OVERRIDE');
545 hr_utility.raise_error;
546 END IF;
547 hr_utility.set_location('pywcdcr.code_in_use', 4);
548 --
549 ELSE
550 --
551 hr_utility.set_location('pywcdcr.code_in_use', 5);
552 --
553 -- this WC code is not defined for this state anywhere else; check to see
554 -- if this WC code is associated with any jobs
555 --
556 --
557 -- re-initialise variable
558 --
559 l_rec_exists := 'N';
560 --
561 -- see if any jobs are using this code
562 --
563 OPEN CODE_USED_BY_JOB;
564 FETCH CODE_USED_BY_JOB INTO l_rec_exists;
565 CLOSE CODE_USED_BY_JOB;
566 --
567 hr_utility.set_location('pywcdcr.code_in_use', 6);
568 --
569 -- if a record exists then raise error and tell user that the code is
570 -- in use
571 --
572 IF (l_rec_exists = 'Y')
573 THEN
574 hr_utility.set_message(801, 'HR_13128_WC_CODE_USED_BY_JOB');
575 hr_utility.raise_error;
576 END IF;
577 --
578 hr_utility.set_location('pywcdcr.code_in_use', 7);
579 --
580 --
581 -- if we've got this far then we need to see if this code is being used
582 -- as an override on the assignment SCL!!!
583 --
584 --
585 -- re-initialise variable
586 --
587 l_rec_exists := 'N';
588 --
589 -- now see if any assignments are using this code
590 --
591 OPEN ONE_CODE_AND_OVERRIDES_EXIST;
592 FETCH ONE_CODE_AND_OVERRIDES_EXIST INTO l_rec_exists;
593 CLOSE ONE_CODE_AND_OVERRIDES_EXIST;
594 --
595 hr_utility.set_location('pywcdcr.code_in_use', 8);
596 --
597 -- if a record exists then raise error and tell user that the code is
598 -- in use
599 --
600 IF (l_rec_exists = 'Y')
601 THEN
602 hr_utility.set_message(801, 'HR_13129_WC_CODE_IS_OVERRIDE');
603 hr_utility.raise_error;
604 END IF;
605 --
606 hr_utility.set_location('pywcdcr.code_in_use', 9);
607 --
608 END IF;
609 --
610 hr_utility.set_location('pywcdcr.code_in_use', 10);
611 --
612 END CODE_IN_USE;
613 --
614 --
615 --
616 --
617 --
618 END HRWCDCR_PKG;