[Home] [Help]
PACKAGE BODY: APPS.HZ_WORK_CLASS_PKG
Source
1 PACKAGE BODY HZ_WORK_CLASS_PKG as
2 /* $Header: ARHPWCTB.pls 120.8 2005/10/30 04:22:59 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_WORK_CLASS_ID IN OUT NOCOPY NUMBER,
7 x_LEVEL_OF_EXPERIENCE IN VARCHAR2,
8 x_WORK_CLASS_NAME IN VARCHAR2,
9 x_EMPLOYMENT_HISTORY_ID IN NUMBER,
10 x_STATUS IN VARCHAR2,
11 x_OBJECT_VERSION_NUMBER IN NUMBER,
12 x_CREATED_BY_MODULE IN VARCHAR2,
13 x_application_id IN NUMBER
14 ) IS
15
16 l_success VARCHAR2(1) := 'N';
17
18 BEGIN
19
20 WHILE l_success = 'N' LOOP
21 BEGIN
22 INSERT INTO HZ_WORK_CLASS(
23 WORK_CLASS_ID,
24 LEVEL_OF_EXPERIENCE,
25 WORK_CLASS_NAME,
26 CREATED_BY,
27 EMPLOYMENT_HISTORY_ID,
28 CREATION_DATE,
29 LAST_UPDATE_LOGIN,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 REQUEST_ID,
33 PROGRAM_APPLICATION_ID,
34 PROGRAM_ID,
35 PROGRAM_UPDATE_DATE,
36 STATUS,
37 OBJECT_VERSION_NUMBER,
38 CREATED_BY_MODULE,
39 APPLICATION_ID
40 ) VALUES (
41 DECODE( x_WORK_CLASS_ID, FND_API.G_MISS_NUM, HZ_WORK_CLASS_S.NEXTVAL, NULL, HZ_WORK_CLASS_S.NEXTVAL, X_WORK_CLASS_ID ),
42 decode( x_LEVEL_OF_EXPERIENCE, FND_API.G_MISS_CHAR, NULL,x_LEVEL_OF_EXPERIENCE),
43 decode( x_WORK_CLASS_NAME, FND_API.G_MISS_CHAR, NULL,x_WORK_CLASS_NAME),
44 HZ_UTILITY_V2PUB.CREATED_BY,
45 decode( x_EMPLOYMENT_HISTORY_ID, FND_API.G_MISS_NUM, NULL,x_EMPLOYMENT_HISTORY_ID),
46 HZ_UTILITY_V2PUB.CREATION_DATE,
47 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
48 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
49 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
50 HZ_UTILITY_V2PUB.REQUEST_ID,
51 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
52 HZ_UTILITY_V2PUB.PROGRAM_ID,
53 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
54 decode( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
55 decode( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
56 decode( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
57 HZ_UTILITY_V2PUB.APPLICATION_ID
58 ) RETURNING
59 WORK_CLASS_ID
60 INTO
61 X_WORK_CLASS_ID;
62
63 l_success := 'Y';
64
65 EXCEPTION
66 WHEN DUP_VAL_ON_INDEX THEN
67 IF INSTRB( SQLERRM, 'HZ_WORK_CLASS_U1' ) <> 0 OR
68 INSTRB( SQLERRM, 'HZ_WORK_CLASS_PK' ) <> 0
69 THEN
70 DECLARE
71 l_count NUMBER;
72 l_dummy VARCHAR2(1);
73 BEGIN
74 l_count := 1;
75 WHILE l_count > 0 LOOP
76 SELECT HZ_WORK_CLASS_S.NEXTVAL
77 INTO X_WORK_CLASS_ID FROM dual;
78 BEGIN
79 SELECT 'Y' INTO l_dummy
80 FROM HZ_WORK_CLASS
81 WHERE WORK_CLASS_ID = X_WORK_CLASS_ID;
82 l_count := 1;
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 l_count := 0;
86 END;
87 END LOOP;
88 END;
89 ELSE
90 RAISE;
91 END IF;
92
93 END;
94 END LOOP;
95
96 END Insert_Row;
97
98
99
100
101 PROCEDURE Delete_Row( x_WORK_CLASS_ID NUMBER
102 ) IS
103 BEGIN
104 DELETE FROM HZ_WORK_CLASS
105 WHERE WORK_CLASS_ID = x_WORK_CLASS_ID;
106 If (SQL%NOTFOUND) then
107 RAISE NO_DATA_FOUND;
108 End If;
109 END Delete_Row;
110
111
112
113 PROCEDURE Update_Row(
114 x_Rowid IN OUT NOCOPY VARCHAR2,
115 x_WORK_CLASS_ID IN NUMBER,
116 x_LEVEL_OF_EXPERIENCE IN VARCHAR2,
117 x_WORK_CLASS_NAME IN VARCHAR2,
118 x_EMPLOYMENT_HISTORY_ID IN NUMBER,
119 x_STATUS IN VARCHAR2,
120 x_OBJECT_VERSION_NUMBER IN NUMBER,
121 x_CREATED_BY_MODULE IN VARCHAR2,
122 x_application_id IN NUMBER
123 ) IS
124 BEGIN
125 Update HZ_WORK_CLASS
126 SET
127
128 WORK_CLASS_ID = decode( x_WORK_CLASS_ID, NULL, WORK_CLASS_ID, FND_API.G_MISS_NUM, NULL, x_WORK_CLASS_ID),
129 LEVEL_OF_EXPERIENCE = decode( x_LEVEL_OF_EXPERIENCE, NULL, LEVEL_OF_EXPERIENCE, FND_API.G_MISS_CHAR, NULL, x_LEVEL_OF_EXPERIENCE),
130 WORK_CLASS_NAME = decode( x_WORK_CLASS_NAME, NULL, WORK_CLASS_NAME, FND_API.G_MISS_CHAR, NULL, x_WORK_CLASS_NAME),
131 -- Bug 3032780
132 -- CREATED_BY = HZ_UTILITY_V2PUB.CREATED_BY,
133 EMPLOYMENT_HISTORY_ID = decode( x_EMPLOYMENT_HISTORY_ID, NULL, EMPLOYMENT_HISTORY_ID, FND_API.G_MISS_NUM,NULL, x_EMPLOYMENT_HISTORY_ID),
134 -- Bug 3032780
135 -- CREATION_DATE = HZ_UTILITY_V2PUB.CREATION_DATE,
136 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
137 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
138 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
139 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
140 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
141 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
142 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
143 STATUS =decode(x_STATUS, NULL,STATUS, FND_API.G_MISS_CHAR,NULL,x_STATUS),
144 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
145 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
146 APPLICATION_ID=HZ_UTILITY_V2PUB.APPLICATION_ID
147 where rowid = X_RowId;
148
149 If (SQL%NOTFOUND) then
150 RAISE NO_DATA_FOUND;
151 End If;
152 END Update_Row;
153
154
155
156 PROCEDURE Lock_Row(
157 x_Rowid VARCHAR2,
158 x_WORK_CLASS_ID NUMBER,
159 x_LEVEL_OF_EXPERIENCE VARCHAR2,
160 x_WORK_CLASS_NAME VARCHAR2,
161 x_CREATED_BY NUMBER,
162 x_EMPLOYMENT_HISTORY_ID NUMBER,
163 x_CREATION_DATE DATE,
164 x_LAST_UPDATE_LOGIN NUMBER,
165 x_LAST_UPDATE_DATE DATE,
166 x_LAST_UPDATED_BY NUMBER,
167 x_REQUEST_ID NUMBER,
168 x_PROGRAM_APPLICATION_ID NUMBER,
169 x_PROGRAM_ID NUMBER,
170 x_PROGRAM_UPDATE_DATE DATE,
171 x_STATUS VARCHAR2
172 ) IS
173 CURSOR C IS
174 SELECT *
175 FROM HZ_WORK_CLASS
176 WHERE rowid = x_Rowid
177 FOR UPDATE of WORK_CLASS_ID NOWAIT;
178 Recinfo C%ROWTYPE;
179 BEGIN
180 OPEN C;
181 FETCH C INTO Recinfo;
182 If (C%NOTFOUND) then
183 CLOSE C;
184 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 End If;
187 CLOSE C;
188 if (
189 ( ( Recinfo.WORK_CLASS_ID = x_WORK_CLASS_ID)
190 OR ( ( Recinfo.WORK_CLASS_ID = NULL )
191 AND ( x_WORK_CLASS_ID = NULL )))
192 AND ( ( Recinfo.LEVEL_OF_EXPERIENCE = x_LEVEL_OF_EXPERIENCE)
193 OR ( ( Recinfo.LEVEL_OF_EXPERIENCE = NULL )
194 AND ( x_LEVEL_OF_EXPERIENCE = NULL )))
195 AND ( ( Recinfo.WORK_CLASS_NAME = x_WORK_CLASS_NAME)
196 OR ( ( Recinfo.WORK_CLASS_NAME = NULL )
197 AND ( x_WORK_CLASS_NAME = NULL )))
198 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
199 OR ( ( Recinfo.CREATED_BY = NULL )
200 AND ( x_CREATED_BY = NULL )))
201 AND ( ( Recinfo.EMPLOYMENT_HISTORY_ID = x_EMPLOYMENT_HISTORY_ID)
202 OR ( ( Recinfo.EMPLOYMENT_HISTORY_ID = NULL )
203 AND ( x_EMPLOYMENT_HISTORY_ID = NULL )))
204 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
205 OR ( ( Recinfo.CREATION_DATE = NULL )
206 AND ( x_CREATION_DATE = NULL )))
207 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
208 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
209 AND ( x_LAST_UPDATE_LOGIN = NULL )))
210 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
211 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
212 AND ( x_LAST_UPDATE_DATE = NULL )))
213 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
214 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
215 AND ( x_LAST_UPDATED_BY = NULL )))
216 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
217 OR ( ( Recinfo.REQUEST_ID = NULL )
218 AND ( x_REQUEST_ID = NULL )))
219 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
220 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
221 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
222 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
223 OR ( ( Recinfo.PROGRAM_ID = NULL )
224 AND ( x_PROGRAM_ID = NULL )))
225 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
226 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
227 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
228 AND ( ( Recinfo.STATUS = x_STATUS)
229 OR ( ( Recinfo.STATUS = NULL )
230 AND ( x_STATUS = NULL )))
231 ) then
232 return;
233 else
234 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
235 APP_EXCEPTION.RAISE_EXCEPTION;
236 End If;
237 END Lock_Row;
238
239 PROCEDURE Select_Row (
240 x_work_class_id IN OUT NOCOPY NUMBER,
241 x_level_of_experience OUT NOCOPY VARCHAR2,
242 x_work_class_name OUT NOCOPY VARCHAR2,
243 x_employment_history_id OUT NOCOPY NUMBER,
244 x_status OUT NOCOPY VARCHAR2,
245 x_application_id OUT NOCOPY NUMBER,
246 x_created_by_module OUT NOCOPY VARCHAR2
247 ) IS
248 BEGIN
249
250 SELECT
251 NVL(work_class_id, FND_API.G_MISS_NUM),
252 NVL(level_of_experience, FND_API.G_MISS_CHAR),
253 NVL(work_class_name, FND_API.G_MISS_CHAR),
254 NVL(employment_history_id, FND_API.G_MISS_NUM),
255 NVL(status, FND_API.G_MISS_CHAR),
256 NVL(application_id, FND_API.G_MISS_NUM),
257 NVL(created_by_module, FND_API.G_MISS_CHAR)
258 INTO
259 x_work_class_id,
260 x_level_of_experience,
261 x_work_class_name,
262 x_employment_history_id,
263 x_status,
264 x_application_id,
265 x_created_by_module
266 FROM HZ_WORK_CLASS
267 WHERE work_class_id = x_work_class_id;
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
272 --2890664, Changed this token
273 FND_MESSAGE.SET_TOKEN('RECORD', 'WORK_CLASS_REC');
274 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_work_class_id));
275 FND_MSG_PUB.ADD;
276 RAISE FND_API.G_EXC_ERROR;
277
278 END Select_Row;
279
280 END HZ_WORK_CLASS_PKG;