PACKAGE BODY: APPS.PAY_US_JOB_WC_USAGES_PKG

Source

1 PACKAGE BODY pay_us_job_wc_usages_pkg as
2 /* $Header: pyuswcdf.pkb 115.0 99/07/17 06:48:16 porting ship $ */
3 
4 PROCEDURE run (errbuf              	OUT 	VARCHAR2,
5   		            retcode              	OUT 	NUMBER,
6   		            p_business_group_id 	IN      NUMBER,
7   		            p_state_code              	IN      VARCHAR2,
8 			    p_default_wc_code		IN  	NUMBER) IS
9 
10 --
11 -- Local Variables :
12 --
13 l_state_code          VARCHAR2(50) := p_state_code;
14 l_Business_group_id   NUMBER := p_business_group_id;
15 l_default_code        NUMBER := p_default_wc_code;
16 l_exists              NUMBER := 0;
17 l_valid               NUMBER := 0;
18 --
19 CURSOR csr_jobs( l_business_group_id NUMBER) IS
20 SELECT job_id
21 FROM per_jobs
22 WHERE business_group_id = l_business_group_id
23 ORDER BY name;
24 --
25 CURSOR csr_valid_job_code(p_job_code NUMBER, l_state_code VARCHAR2) IS
26 SELECT 1
27 FROM pay_wc_funds wcf, pay_wc_rates wcr
28 WHERE wcr.wc_code = p_job_code         /* reference */
29   AND wcr.fund_id = wcf.fund_id        /* for this fund */
30   AND wcf.state_code = l_state_code; /* in this state */
31 --
32 CURSOR csr_records_exist(l_state_code VARCHAR2, l_business_group_id NUMBER) IS
33 SELECT 1
34 FROM pay_job_wc_code_usages
35 WHERE state_code = l_state_code
36   AND business_group_id = l_business_group_id;
37 --
38 BEGIN /* Main program */
39 --
40 /*Check for existing records*/
41 OPEN csr_records_exist(l_state_code, l_business_group_id);
42 FETCH csr_records_exist INTO l_exists;
43 IF csr_records_exist%FOUND THEN
44  hr_utility.set_message(801,'PAY_51838_JWC_JOB_ALREADY_ASS');
45  RAISE hr_utility.hr_error;
46 END IF;
47 CLOSE csr_records_exist;
48 
49 
50 --
51 /* Check that a valid job code has been defined */
52 OPEN csr_valid_job_code(l_default_code, l_state_code);
53 FETCH csr_valid_job_code INTO l_valid;
54 IF csr_valid_job_code%NOTFOUND THEN
55  hr_utility.set_message(801,'PAY_51838_JWC_JOB_CD_INVALID');
56  RAISE hr_utility.hr_error;
57 END IF;
58 CLOSE csr_valid_job_code;
59 --
60 --
61   FOR cur_rec IN csr_jobs(l_business_group_id) LOOP
62 --
63     INSERT INTO pay_job_wc_code_usages
64           (state_code,
65            business_group_id,
66            job_id,
67            wc_code)
68     VALUES(l_state_code,
69            l_business_group_id,
70            cur_rec.job_id,
71            l_default_code);
72 --
73 --
74   END LOOP;
75 --
76 EXCEPTION
77    --
78    WHEN hr_utility.hr_error THEN
79      --
80      -- Set up error message and error return code.
81      --
82      errbuf  := hr_utility.get_message;
83      retcode := 2;
84      --
85 --
86 WHEN others THEN
87 --
88      -- Set up error message and return code.
89      --
90      errbuf  := sqlerrm;
91      retcode := 2;
92 --
93 END run;
94 
95 END pay_us_job_wc_usages_pkg;
96 --
97