[Home] [Help]
PACKAGE BODY: APPS.HRWCDJC_PKG
Source
1 PACKAGE BODY HRWCDJC_PKG AS
2 /* $Header: pywcdjc.pkb 115.1 99/07/17 06:50:10 porting ship $ */
3 --
4 --
5 --
6 --
7 PROCEDURE INSERT_ROW( X_ROWID IN OUT VARCHAR2,
8 X_STATE_CODE VARCHAR2,
9 X_BUSINESS_GROUP_ID NUMBER,
10 X_JOB_ID NUMBER,
11 X_WC_CODE NUMBER) IS
12 BEGIN
13 --
14 INSERT INTO PAY_JOB_WC_CODE_USAGES
15 (STATE_CODE, BUSINESS_GROUP_ID, JOB_ID, WC_CODE)
16 VALUES
17 (X_STATE_CODE, X_BUSINESS_GROUP_ID, X_JOB_ID, X_WC_CODE);
18 --
19 SELECT ROWID
20 INTO X_ROWID
21 FROM PAY_JOB_WC_CODE_USAGES
22 WHERE STATE_CODE = X_STATE_CODE
23 AND JOB_ID = X_JOB_ID;
24 --
25 IF (SQL%NOTFOUND) THEN
26 RAISE NO_DATA_FOUND;
27 END IF;
28 --
29 END INSERT_ROW;
30 --
31 --
32 --
33 PROCEDURE UPDATE_ROW( X_ROWID VARCHAR2,
34 X_STATE_CODE VARCHAR2,
35 X_BUSINESS_GROUP_ID NUMBER,
36 X_JOB_ID NUMBER,
37 X_WC_CODE NUMBER) IS
38 BEGIN
39 --
40 UPDATE PAY_JOB_WC_CODE_USAGES
41 SET STATE_CODE = X_STATE_CODE
42 , BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
43 , JOB_ID = X_JOB_ID
44 , WC_CODE = X_WC_CODE
45 WHERE ROWID = X_ROWID;
46 --
47 IF (SQL%NOTFOUND) THEN
48 RAISE NO_DATA_FOUND;
49 END IF;
50 --
51 END UPDATE_ROW;
52 --
53 --
54 --
55 PROCEDURE DELETE_ROW( X_ROWID VARCHAR2,
56 X_STATE_CODE VARCHAR2,
57 X_BUSINESS_GROUP_ID NUMBER,
58 X_JOB_ID NUMBER,
59 X_WC_CODE NUMBER) IS
60 BEGIN
61 --
62 DELETE FROM PAY_JOB_WC_CODE_USAGES
63 WHERE ROWID = X_ROWID;
64 --
65 IF (SQL%NOTFOUND) THEN
66 RAISE NO_DATA_FOUND;
67 END IF;
68 --
69 END DELETE_ROW;
70 --
71 --
72 --
73 PROCEDURE LOCK_ROW( X_ROWID VARCHAR2,
74 X_STATE_CODE VARCHAR2,
75 X_BUSINESS_GROUP_ID NUMBER,
76 X_JOB_ID NUMBER,
77 X_WC_CODE NUMBER) IS
78 --
79 CURSOR C IS
80 SELECT *
81 FROM PAY_JOB_WC_CODE_USAGES
82 WHERE ROWID = X_ROWID
83 FOR UPDATE OF STATE_CODE NOWAIT;
84 --
85 RECINFO C%ROWTYPE;
86 --
87 BEGIN
88 --
89 OPEN C;
90 FETCH C INTO RECINFO;
91 IF (C%NOTFOUND) THEN
92 CLOSE C;
93 RAISE NO_DATA_FOUND;
94 END IF;
95 CLOSE C;
96 --
97 -- rtrim char columns
98 --
99 Recinfo.state_code := RTRIM(Recinfo.state_code);
100 --
101 IF( ( ( RECINFO.STATE_CODE = X_STATE_CODE)
102 OR ( RECINFO.STATE_CODE IS NULL AND X_STATE_CODE IS NULL))
103 AND
104 ( ( RECINFO.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
105 OR ( RECINFO.BUSINESS_GROUP_ID IS NULL AND X_BUSINESS_GROUP_ID IS NULL))
106 AND
107 ( ( RECINFO.JOB_ID = X_JOB_ID)
108 OR ( RECINFO.JOB_ID IS NULL AND X_JOB_ID IS NULL))
109 AND
110 ( ( RECINFO.WC_CODE = X_WC_CODE)
111 OR ( RECINFO.WC_CODE IS NULL AND X_WC_CODE IS NULL))
112 ) THEN
113 RETURN;
114 ELSE
115 -- FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
116 -- APP_EXCEPTION.RAISE_EXCEPTION;
117 hr_utility.set_message(0, 'FORM_RECORD_CHANGED');
118 hr_utility.raise_error;
119 END IF;
120 --
121 END LOCK_ROW;
122 --
123 --
124 PROCEDURE JOB_STATE_UNIQUE( P_ROWID VARCHAR2,
125 P_STATE_CODE VARCHAR2,
126 P_JOB_ID NUMBER) IS
127 --
128 --
129 l_job_exists VARCHAR2(2);
130 --
131 CURSOR DUP_JOB IS
132 SELECT 'Y'
133 FROM PAY_JOB_WC_CODE_USAGES
134 WHERE STATE_CODE = P_STATE_CODE
135 AND JOB_ID = P_JOB_ID
136 AND ((ROWID <> P_ROWID
137 AND P_ROWID IS NOT NULL)
138 OR
139 (P_ROWID IS NULL));
140 --
141 --
142 BEGIN
143 --
144 --
145 -- initialise variable
146 l_job_exists := 'N';
147 --
148 -- open fetch and close the cursor - if a record is found then the local
149 -- variable will be set to 'Y', otherwise it will remain 'N'
150 --
151 OPEN DUP_JOB;
152 FETCH DUP_JOB INTO l_job_exists;
153 CLOSE DUP_JOB;
154 --
155 -- go ahead and check the value of the local variable - if it's 'Y' then this
156 -- job has already got a WC code for the state
157 --
158 IF (l_job_exists = 'Y')
159 THEN
160 hr_utility.set_message(801, 'HR_13102_WC_ONE_WCCODE_PER_JOB');
161 hr_utility.raise_error;
162 END IF;
163 --
164 --
165 END JOB_STATE_UNIQUE;
166 --
167 --
168 --
169 --
170 END HRWCDJC_PKG;