[Home] [Help]
PACKAGE BODY: APPS.HZ_EDUCATION_PKG
Source
1 PACKAGE BODY HZ_EDUCATION_PKG as
2 /* $Header: ARHPEDTB.pls 120.6 2005/10/30 04:22:09 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_EDUCATION_ID IN OUT NOCOPY NUMBER,
7 x_COURSE_MAJOR IN VARCHAR2,
8 x_PARTY_ID IN NUMBER,
9 x_DEGREE_RECEIVED IN VARCHAR2,
10 x_LAST_DATE_ATTENDED IN DATE,
11 x_SCHOOL_ATTENDED_NAME IN VARCHAR2,
12 x_TYPE_OF_SCHOOL IN VARCHAR2,
13 x_START_DATE_ATTENDED IN DATE,
14 x_STATUS IN VARCHAR2,
15 x_SCHOOL_PARTY_ID IN NUMBER,
16 x_OBJECT_VERSION_NUMBER IN NUMBER,
17 x_CREATED_BY_MODULE IN VARCHAR2,
18 x_APPLICATION_ID IN NUMBER
19
20 )IS
21
22 l_success VARCHAR2(1) := 'N';
23
24 BEGIN
25 WHILE l_success = 'N' LOOP
26 BEGIN
27 INSERT INTO HZ_EDUCATION(
28 EDUCATION_ID,
29 COURSE_MAJOR,
30 PARTY_ID,
31 SCHOOL_PARTY_ID,
32 DEGREE_RECEIVED,
33 LAST_DATE_ATTENDED,
34 SCHOOL_ATTENDED_NAME,
35 TYPE_OF_SCHOOL,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATE_LOGIN,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 REQUEST_ID,
42 PROGRAM_APPLICATION_ID,
43 PROGRAM_ID,
44 PROGRAM_UPDATE_DATE,
45 START_DATE_ATTENDED,
46 STATUS,
47 OBJECT_VERSION_NUMBER,
48 CREATED_BY_MODULE,
49 APPLICATION_ID
50
51 ) VALUES (
52 decode( x_EDUCATION_ID, FND_API.G_MISS_NUM, HZ_EDUCATION_S.NEXTVAL, NULL, HZ_EDUCATION_S.NEXTVAL, X_EDUCATION_ID ),
53 decode( x_COURSE_MAJOR, FND_API.G_MISS_CHAR, NULL,x_COURSE_MAJOR),
54 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
55 decode( x_SCHOOL_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_SCHOOL_PARTY_ID),
56 decode( x_DEGREE_RECEIVED, FND_API.G_MISS_CHAR, NULL,x_DEGREE_RECEIVED),
57 decode( x_LAST_DATE_ATTENDED, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_DATE_ATTENDED),
58 decode( x_SCHOOL_ATTENDED_NAME, FND_API.G_MISS_CHAR, NULL,x_SCHOOL_ATTENDED_NAME),
59 decode( x_TYPE_OF_SCHOOL, FND_API.G_MISS_CHAR, NULL,x_TYPE_OF_SCHOOL),
60 HZ_UTILITY_V2PUB.CREATED_BY,
61 HZ_UTILITY_V2PUB.CREATION_DATE,
62 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
63 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
64 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
65 HZ_UTILITY_V2PUB.REQUEST_ID,
66 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
67 HZ_UTILITY_V2PUB.PROGRAM_ID,
68 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
69 decode( x_START_DATE_ATTENDED, FND_API.G_MISS_DATE, TO_DATE(NULL),x_START_DATE_ATTENDED),
70 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
71 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
72 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
73 HZ_UTILITY_V2PUB.APPLICATION_ID
74
75 )
76 RETURNING
77 EDUCATION_ID
78 INTO
79 X_EDUCATION_ID;
80
81 l_success := 'Y';
82
83 EXCEPTION
84 WHEN DUP_VAL_ON_INDEX THEN
85 IF INSTRB( SQLERRM, 'HZ_EDUCATION_U1' ) <> 0 OR
86 INSTRB( SQLERRM, 'HZ_EDUCATION_PK' ) <> 0
87 THEN
88 DECLARE
89 l_count NUMBER;
90 l_dummy VARCHAR2(1);
91 BEGIN
92 l_count := 1;
93 WHILE l_count > 0 LOOP
94 SELECT HZ_EDUCATION_S.NEXTVAL
95 INTO X_EDUCATION_ID FROM dual;
96 BEGIN
97 SELECT 'Y' INTO l_dummy
98 FROM HZ_EDUCATION
99 WHERE EDUCATION_ID = EDUCATION_ID;
100 l_count := 1;
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 l_count := 0;
104 END;
105 END LOOP;
106 END;
107 ELSE
108 RAISE;
109 END IF;
110
111 END;
112 END LOOP;
113
114 END Insert_Row;
115
116
117
118
119 PROCEDURE Delete_Row( x_EDUCATION_ID NUMBER
120 ) IS
121 BEGIN
122 DELETE FROM HZ_EDUCATION
123 WHERE EDUCATION_ID = x_EDUCATION_ID;
124 If (SQL%NOTFOUND) then
125 RAISE NO_DATA_FOUND;
126 End If;
127 END Delete_Row;
128
129
130
131 PROCEDURE Update_Row(
132 x_Rowid IN OUT NOCOPY VARCHAR2,
133 x_EDUCATION_ID IN OUT NOCOPY NUMBER,
134 x_COURSE_MAJOR IN VARCHAR2,
135 x_PARTY_ID IN NUMBER,
136 x_SCHOOL_PARTY_ID IN NUMBER,
137 x_DEGREE_RECEIVED IN VARCHAR2,
138 x_LAST_DATE_ATTENDED IN DATE,
139 x_SCHOOL_ATTENDED_NAME IN VARCHAR2,
140 x_TYPE_OF_SCHOOL IN VARCHAR2,
141 x_START_DATE_ATTENDED IN DATE,
142 x_STATUS IN VARCHAR2,
143 x_OBJECT_VERSION_NUMBER IN NUMBER,
144 x_CREATED_BY_MODULE IN VARCHAR2,
145 x_APPLICATION_ID IN NUMBER
146
147 ) IS
148 BEGIN
149 Update HZ_EDUCATION
150 SET
151 EDUCATION_ID = decode( x_EDUCATION_ID, NULL, EDUCATION_ID, FND_API.G_MISS_NUM, NULL, x_EDUCATION_ID),
152 COURSE_MAJOR = decode( x_COURSE_MAJOR, NULL, COURSE_MAJOR, FND_API.G_MISS_CHAR,NULL, x_COURSE_MAJOR),
153 PARTY_ID = decode( x_PARTY_ID, NULL, PARTY_ID, FND_API.G_MISS_NUM, NULL, x_PARTY_ID),
154 SCHOOL_PARTY_ID = decode( x_SCHOOL_PARTY_ID, NULL, SCHOOL_PARTY_ID, FND_API.G_MISS_NUM, NULL, x_SCHOOL_PARTY_ID),
155 DEGREE_RECEIVED = decode( x_DEGREE_RECEIVED, NULL, DEGREE_RECEIVED, FND_API.G_MISS_CHAR, NULL, x_DEGREE_RECEIVED),
156 LAST_DATE_ATTENDED = decode( x_LAST_DATE_ATTENDED, NULL, LAST_DATE_ATTENDED, FND_API.G_MISS_DATE, NULL, x_LAST_DATE_ATTENDED),
157 SCHOOL_ATTENDED_NAME = decode( x_SCHOOL_ATTENDED_NAME, NULL, SCHOOL_ATTENDED_NAME, FND_API.G_MISS_CHAR, NULL, x_SCHOOL_ATTENDED_NAME),
158 TYPE_OF_SCHOOL = decode( x_TYPE_OF_SCHOOL, NULL, TYPE_OF_SCHOOL, FND_API.G_MISS_CHAR, NULL, x_TYPE_OF_SCHOOL),
159 -- Bug 3032780
160 -- CREATED_BY = HZ_UTILITY_V2PUB.CREATED_BY,
161 -- CREATION_DATE = HZ_UTILITY_V2PUB.CREATION_DATE,
162 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
163 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
165 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
166 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
167 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
168 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
169 START_DATE_ATTENDED = decode( x_START_DATE_ATTENDED, NULL, START_DATE_ATTENDED, FND_API.G_MISS_DATE, NULL, x_START_DATE_ATTENDED),
170 STATUS =decode(x_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, x_STATUS),
171 OBJECT_VERSION_NUMBER = DECODE( x_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
172 CREATED_BY_MODULE = DECODE( x_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
173 APPLICATION_ID = DECODE( x_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
174 where rowid = X_RowId;
175
176 If (SQL%NOTFOUND) then
177 RAISE NO_DATA_FOUND;
178 End If;
179 END Update_Row;
180
181
182 PROCEDURE Lock_Row(
183 x_Rowid VARCHAR2,
184 x_EDUCATION_ID NUMBER,
185 x_COURSE_MAJOR VARCHAR2,
186 x_PARTY_ID NUMBER,
187 x_SCHOOL_PARTY_ID NUMBER,
188 x_DEGREE_RECEIVED VARCHAR2,
189 x_LAST_DATE_ATTENDED DATE,
190 x_SCHOOL_ATTENDED_NAME VARCHAR2,
191 x_TYPE_OF_SCHOOL VARCHAR2,
192 x_CREATED_BY NUMBER,
193 x_CREATION_DATE DATE,
194 x_LAST_UPDATE_LOGIN NUMBER,
195 x_LAST_UPDATE_DATE DATE,
196 x_LAST_UPDATED_BY NUMBER,
197 x_REQUEST_ID NUMBER,
198 x_PROGRAM_APPLICATION_ID NUMBER,
199 x_PROGRAM_ID NUMBER,
200 x_PROGRAM_UPDATE_DATE DATE,
201 x_WH_UPDATE_DATE DATE,
202 x_START_DATE_ATTENDED DATE,
203 x_STATUS VARCHAR2
204
205 ) IS
206 CURSOR C IS
207 SELECT *
208 FROM HZ_EDUCATION
209 WHERE rowid = x_Rowid
210 FOR UPDATE of EDUCATION_ID NOWAIT;
211 Recinfo C%ROWTYPE;
212 BEGIN
213 OPEN C;
214 FETCH C INTO Recinfo;
215 If (C%NOTFOUND) then
216 CLOSE C;
217 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
218 APP_EXCEPTION.RAISE_EXCEPTION;
219 End If;
220 CLOSE C;
221 if (
222 ( ( Recinfo.EDUCATION_ID = x_EDUCATION_ID)
223 OR ( ( Recinfo.EDUCATION_ID = NULL )
224 AND ( x_EDUCATION_ID = NULL )))
225 AND ( ( Recinfo.COURSE_MAJOR = x_COURSE_MAJOR)
226 OR ( ( Recinfo.COURSE_MAJOR = NULL )
227 AND ( x_COURSE_MAJOR = NULL )))
228 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
229 OR ( ( Recinfo.PARTY_ID = NULL )
230 AND ( x_PARTY_ID = NULL )))
231 AND ( ( Recinfo.SCHOOL_PARTY_ID = x_SCHOOL_PARTY_ID)
232 OR ( ( Recinfo.SCHOOL_PARTY_ID = NULL )
233 AND ( x_SCHOOL_PARTY_ID = NULL )))
234 AND ( ( Recinfo.DEGREE_RECEIVED = x_DEGREE_RECEIVED)
235 OR ( ( Recinfo.DEGREE_RECEIVED = NULL )
236 AND ( x_DEGREE_RECEIVED = NULL )))
237 AND ( ( Recinfo.LAST_DATE_ATTENDED = x_LAST_DATE_ATTENDED)
238 OR ( ( Recinfo.LAST_DATE_ATTENDED = NULL )
239 AND ( x_LAST_DATE_ATTENDED = NULL )))
240 AND ( ( Recinfo.SCHOOL_ATTENDED_NAME = x_SCHOOL_ATTENDED_NAME)
241 OR ( ( Recinfo.SCHOOL_ATTENDED_NAME = NULL )
242 AND ( x_SCHOOL_ATTENDED_NAME = NULL )))
243 AND ( ( Recinfo.TYPE_OF_SCHOOL = x_TYPE_OF_SCHOOL)
244 OR ( ( Recinfo.TYPE_OF_SCHOOL = NULL )
245 AND ( x_TYPE_OF_SCHOOL = NULL )))
246 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
247 OR ( ( Recinfo.CREATED_BY = NULL )
248 AND ( x_CREATED_BY = NULL )))
249 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
250 OR ( ( Recinfo.CREATION_DATE = NULL )
251 AND ( x_CREATION_DATE = NULL )))
252 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
253 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
254 AND ( x_LAST_UPDATE_LOGIN = NULL )))
255 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
256 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
257 AND ( x_LAST_UPDATE_DATE = NULL )))
258 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
259 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
260 AND ( x_LAST_UPDATED_BY = NULL )))
261 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
262 OR ( ( Recinfo.REQUEST_ID = NULL )
263 AND ( x_REQUEST_ID = NULL )))
264 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
265 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
266 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
267 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
268 OR ( ( Recinfo.PROGRAM_ID = NULL )
269 AND ( x_PROGRAM_ID = NULL )))
270 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
271 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
272 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
273 AND ( ( Recinfo.WH_UPDATE_DATE = x_WH_UPDATE_DATE)
274 OR ( ( Recinfo.WH_UPDATE_DATE = NULL )
275 AND ( x_WH_UPDATE_DATE = NULL )))
276 AND ( ( Recinfo.START_DATE_ATTENDED = x_START_DATE_ATTENDED)
277 OR ( ( Recinfo.START_DATE_ATTENDED = NULL )
278 AND ( x_START_DATE_ATTENDED = NULL )))
279
280 AND ( ( Recinfo.STATUS = x_STATUS)
281 OR ( ( Recinfo.STATUS = NULL )
282 AND ( x_STATUS = NULL )))
283 ) then
284 return;
285 else
286 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
287 APP_EXCEPTION.RAISE_EXCEPTION;
288 End If;
289 END Lock_Row;
290
291
292 PROCEDURE Select_Row (
293 x_education_id IN OUT NOCOPY NUMBER,
294 x_course_major OUT NOCOPY VARCHAR2,
295 x_party_id OUT NOCOPY NUMBER,
296 x_school_party_id OUT NOCOPY NUMBER,
297 x_degree_received OUT NOCOPY VARCHAR2,
298 x_last_date_attended OUT NOCOPY DATE,
299 x_start_date_attended OUT NOCOPY DATE,
300 x_school_attended_name OUT NOCOPY VARCHAR2,
301 x_type_of_school OUT NOCOPY VARCHAR2,
302 x_status OUT NOCOPY VARCHAR2,
303 x_application_id OUT NOCOPY NUMBER,
304 x_created_by_module OUT NOCOPY VARCHAR2
305 ) IS
306 BEGIN
307
308 SELECT
309 NVL(education_id, FND_API.G_MISS_NUM),
310 NVL(course_major, FND_API.G_MISS_CHAR),
311 NVL(party_id, FND_API.G_MISS_NUM),
312 NVL(school_party_id, FND_API.G_MISS_NUM),
313 NVL(degree_received, FND_API.G_MISS_CHAR),
314 NVL(last_date_attended, FND_API.G_MISS_DATE),
315 NVL(start_date_attended, FND_API.G_MISS_DATE),
316 NVL(school_attended_name, FND_API.G_MISS_CHAR),
317 NVL(type_of_school, FND_API.G_MISS_CHAR),
318 NVL(status, FND_API.G_MISS_CHAR),
319 NVL(application_id, FND_API.G_MISS_NUM),
320 NVL(created_by_module, FND_API.G_MISS_CHAR)
321 INTO
322 x_education_id,
323 x_course_major,
324 x_party_id,
325 x_school_party_id,
326 x_degree_received,
327 x_last_date_attended,
328 x_start_date_attended,
329 x_school_attended_name,
330 x_type_of_school,
331 x_status,
332 x_application_id,
333 x_created_by_module
334 FROM HZ_EDUCATION
335 WHERE education_id = x_education_id;
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
340 FND_MESSAGE.SET_TOKEN('RECORD', 'education_rec');
341 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_education_id));
342 FND_MSG_PUB.ADD;
343 RAISE FND_API.G_EXC_ERROR;
344
345 END Select_Row;
346
347
348 END HZ_EDUCATION_PKG;