1 PACKAGE BODY igs_ps_dic_occ_titls_pkg AS
2 /* $Header: IGSPI0EB.pls 115.6 2002/11/29 01:56:01 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_dic_occ_titls%RowType;
5 new_references igs_ps_dic_occ_titls%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_occupational_title_code IN VARCHAR2 DEFAULT NULL,
11 x_occupational_title IN VARCHAR2 DEFAULT NULL,
12 x_alternate_title IN VARCHAR2 DEFAULT NULL,
13 x_creation_date IN DATE DEFAULT NULL,
14 x_created_by IN NUMBER DEFAULT NULL,
15 x_last_update_date IN DATE DEFAULT NULL,
16 x_last_updated_by IN NUMBER DEFAULT NULL,
17 x_last_update_login IN NUMBER DEFAULT NULL
18 ) AS
19
20 /*************************************************************
21 Created By : jdeekoll
22 Date Created By : 11-May-2000
23 Purpose :
24 Know limitations, enhancements or remarks
25 Change History
26 Who When What
27
28 (reverse chronological order - newest change first)
29 ***************************************************************/
30
31
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_PS_DIC_OCC_TITLS
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.occupational_title_code := x_occupational_title_code;
57 new_references.occupational_title := x_occupational_title;
58 new_references.alternate_title := x_alternate_title;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 PROCEDURE Check_Constraints (
73 Column_Name IN VARCHAR2 DEFAULT NULL,
74 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
75
76 /*************************************************************
77 Created By : jdeekoll
78 Date Created By : 11-May-2000
79 Purpose :
80 Know limitations, enhancements or remarks
81 Change History
82 Who When What
83
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86
87
88
89 BEGIN
90
91 IF column_name IS NULL THEN
92 NULL;
93 NULL;
94 END IF;
95
96
97
98
99 END Check_Constraints;
100
101 PROCEDURE Check_Child_Existance IS
102 /*************************************************************
103 Created By : jdeekoll
104 Date Created By : 11-May-2000
105 Purpose :
106 Know limitations, enhancements or remarks
107 Change History
108 Who When What
109
110 (reverse chronological order - newest change first)
111 ***************************************************************/
112
113 BEGIN
114
115 Igs_Ps_Occup_Titles_Pkg.Get_FK_Igs_Ps_Dic_Occ_Titls (
116 old_references.occupational_title_code
117 );
118
119 END Check_Child_Existance;
120
121
122
123
124 FUNCTION Get_PK_For_Validation (
125 x_occupational_title_code IN VARCHAR2
126 ) RETURN BOOLEAN AS
127
128 CURSOR cur_rowid IS
129 SELECT rowid
130 FROM igs_ps_dic_occ_titls
131 WHERE occupational_title_code = x_occupational_title_code
132 FOR UPDATE NOWAIT;
133
134 lv_rowid cur_rowid%RowType;
135
136 BEGIN
137
138 Open cur_rowid;
139 Fetch cur_rowid INTO lv_rowid;
140 IF (cur_rowid%FOUND) THEN
141 Close cur_rowid;
142 Return(TRUE);
143 ELSE
144 Close cur_rowid;
145 Return(FALSE);
146 END IF;
147 END Get_PK_For_Validation;
148
149 PROCEDURE Before_DML (
150 p_action IN VARCHAR2,
151 x_rowid IN VARCHAR2 DEFAULT NULL,
152 x_occupational_title_code IN VARCHAR2 DEFAULT NULL,
153 x_occupational_title IN VARCHAR2 DEFAULT NULL,
154 x_alternate_title IN VARCHAR2 DEFAULT NULL,
155 x_creation_date IN DATE DEFAULT NULL,
156 x_created_by IN NUMBER DEFAULT NULL,
157 x_last_update_date IN DATE DEFAULT NULL,
158 x_last_updated_by IN NUMBER DEFAULT NULL,
159 x_last_update_login IN NUMBER DEFAULT NULL
160 ) AS
161
162
163 /*************************************************************
164 Created By : jdeekoll
165 Date Created By : 11-May-2000
166 Purpose :
167 Know limitations, enhancements or remarks
168 Change History
169 Who When What
170
171 (reverse chronological order - newest change first)
172 ***************************************************************/
173
174 BEGIN
175
176 Set_Column_Values (
177 p_action,
178 x_rowid,
179 x_occupational_title_code,
180 x_occupational_title,
181 x_alternate_title,
182 x_creation_date,
183 x_created_by,
184 x_last_update_date,
185 x_last_updated_by,
186 x_last_update_login
187 );
188
189 IF (p_action = 'INSERT') THEN
190 -- Call all the procedures related to Before Insert.
191 Null;
192 IF Get_Pk_For_Validation(
193 new_references.occupational_title_code) THEN
194 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198 Check_Constraints;
199 ELSIF (p_action = 'UPDATE') THEN
200 -- Call all the procedures related to Before Update.
201 Null;
202 Check_Constraints;
203 ELSIF (p_action = 'DELETE') THEN
204 -- Call all the procedures related to Before Delete.
205 Null;
206 Check_Child_Existance;
207 ELSIF (p_action = 'VALIDATE_INSERT') THEN
208 -- Call all the procedures related to Before Insert.
209 IF Get_PK_For_Validation (
210 new_references.occupational_title_code) THEN
211 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214 END IF;
215 Check_Constraints;
216 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
217 Check_Constraints;
218 ELSIF (p_action = 'VALIDATE_DELETE') THEN
219 Null;
220 Check_Child_Existance;
221 END IF;
222
223 END Before_DML;
224
225 PROCEDURE After_DML (
226 p_action IN VARCHAR2,
227 x_rowid IN VARCHAR2
228 ) AS
229
230 /*************************************************************
231 Created By : jdeekoll
232 Date Created By : 11-May-2000
233 Purpose :
234 Know limitations, enhancements or remarks
235 Change History
236 Who When What
237
238 (reverse chronological order - newest change first)
239 ***************************************************************/
240
241
242 BEGIN
243
244 l_rowid := x_rowid;
245
246 IF (p_action = 'INSERT') THEN
247 -- Call all the procedures related to After Insert.
248 Null;
249 ELSIF (p_action = 'UPDATE') THEN
250 -- Call all the procedures related to After Update.
251 Null;
252 ELSIF (p_action = 'DELETE') THEN
253 -- Call all the procedures related to After Delete.
254 Null;
255 END IF;
256
257 END After_DML;
258
259 procedure INSERT_ROW (
260 X_ROWID in out NOCOPY VARCHAR2,
261 x_OCCUPATIONAL_TITLE_CODE IN VARCHAR2,
262 x_OCCUPATIONAL_TITLE IN VARCHAR2,
263 x_ALTERNATE_TITLE IN VARCHAR2,
264 X_MODE in VARCHAR2 default 'R'
265 ) AS
266
267 /*************************************************************
268 Created By : jdeekoll
269 Date Created By : 11-May-2000
270 Purpose :
271 Know limitations, enhancements or remarks
272 Change History
273 Who When What
274
275 (reverse chronological order - newest change first)
276 ***************************************************************/
277
278
279
280 cursor C is select ROWID from IGS_PS_DIC_OCC_TITLS
281 where OCCUPATIONAL_TITLE_CODE= X_OCCUPATIONAL_TITLE_CODE
282 ;
283 X_LAST_UPDATE_DATE DATE ;
284 X_LAST_UPDATED_BY NUMBER ;
285 X_LAST_UPDATE_LOGIN NUMBER ;
286 begin
287 X_LAST_UPDATE_DATE := SYSDATE;
288 if(X_MODE = 'I') then
289 X_LAST_UPDATED_BY := 1;
290 X_LAST_UPDATE_LOGIN := 0;
291 elsif (X_MODE = 'R') then
292 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
293 if X_LAST_UPDATED_BY is NULL then
294 X_LAST_UPDATED_BY := -1;
295 end if;
296 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
297 if X_LAST_UPDATE_LOGIN is NULL then
298 X_LAST_UPDATE_LOGIN := -1;
299 end if;
300 else
301 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
302 IGS_GE_MSG_STACK.ADD;
303 app_exception.raise_exception;
304 end if;
305 Before_DML(
306 p_action=>'INSERT',
307 x_rowid=>X_ROWID,
308 x_occupational_title_code=>X_OCCUPATIONAL_TITLE_CODE,
309 x_occupational_title=>X_OCCUPATIONAL_TITLE,
310 x_alternate_title=>X_ALTERNATE_TITLE,
311 x_creation_date=>X_LAST_UPDATE_DATE,
312 x_created_by=>X_LAST_UPDATED_BY,
313 x_last_update_date=>X_LAST_UPDATE_DATE,
314 x_last_updated_by=>X_LAST_UPDATED_BY,
315 x_last_update_login=>X_LAST_UPDATE_LOGIN);
316 insert into IGS_PS_DIC_OCC_TITLS (
317 OCCUPATIONAL_TITLE_CODE
318 ,OCCUPATIONAL_TITLE
319 ,ALTERNATE_TITLE
320 ,CREATION_DATE
321 ,CREATED_BY
322 ,LAST_UPDATE_DATE
323 ,LAST_UPDATED_BY
324 ,LAST_UPDATE_LOGIN
325 ) values (
326 NEW_REFERENCES.OCCUPATIONAL_TITLE_CODE
327 ,NEW_REFERENCES.OCCUPATIONAL_TITLE
328 ,NEW_REFERENCES.ALTERNATE_TITLE
329 ,X_LAST_UPDATE_DATE
330 ,X_LAST_UPDATED_BY
331 ,X_LAST_UPDATE_DATE
332 ,X_LAST_UPDATED_BY
333 ,X_LAST_UPDATE_LOGIN
334 );
335 open c;
336 fetch c into X_ROWID;
337 if (c%notfound) then
338 close c;
339 raise no_data_found;
340 end if;
341 close c;
342 After_DML (
343 p_action => 'INSERT' ,
344 x_rowid => X_ROWID );
345 end INSERT_ROW;
346 procedure LOCK_ROW (
347 X_ROWID in VARCHAR2,
348 x_OCCUPATIONAL_TITLE_CODE IN VARCHAR2,
349 x_OCCUPATIONAL_TITLE IN VARCHAR2,
350 x_ALTERNATE_TITLE IN VARCHAR2 ) AS
351
352 /*************************************************************
353 Created By : jdeekoll
354 Date Created By : 11-May-2000
355 Purpose :
356 Know limitations, enhancements or remarks
357 Change History
358 Who When What
359
360 (reverse chronological order - newest change first)
361 ***************************************************************/
362
363
364 cursor c1 is select
365 OCCUPATIONAL_TITLE
366 , ALTERNATE_TITLE
367 from IGS_PS_DIC_OCC_TITLS
368 where ROWID = X_ROWID
369 for update nowait;
370 tlinfo c1%rowtype;
371 begin
372 open c1;
373 fetch c1 into tlinfo;
374 if (c1%notfound) then
375 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
376 IGS_GE_MSG_STACK.ADD;
377 close c1;
378 app_exception.raise_exception;
379 return;
380 end if;
381 close c1;
382 if ( ( tlinfo.OCCUPATIONAL_TITLE = X_OCCUPATIONAL_TITLE)
383 AND ((tlinfo.ALTERNATE_TITLE = X_ALTERNATE_TITLE)
384 OR ((tlinfo.ALTERNATE_TITLE is null)
385 AND (X_ALTERNATE_TITLE is null)))
386 ) then
387 null;
388 else
389 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
390 IGS_GE_MSG_STACK.ADD;
391 app_exception.raise_exception;
392 end if;
393 return;
394 end LOCK_ROW;
395 Procedure UPDATE_ROW (
396 X_ROWID in VARCHAR2,
397 x_OCCUPATIONAL_TITLE_CODE IN VARCHAR2,
398 x_OCCUPATIONAL_TITLE IN VARCHAR2,
399 x_ALTERNATE_TITLE IN VARCHAR2,
400 X_MODE in VARCHAR2 default 'R'
401 ) AS
402
403 /*************************************************************
404 Created By : jdeekoll
405 Date Created By : 11-May-2000
406 Purpose :
407 Know limitations, enhancements or remarks
408 Change History
409 Who When What
410
411 (reverse chronological order - newest change first)
412 ***************************************************************/
413
414
415 X_LAST_UPDATE_DATE DATE ;
416 X_LAST_UPDATED_BY NUMBER ;
417 X_LAST_UPDATE_LOGIN NUMBER ;
418 begin
419 X_LAST_UPDATE_DATE := SYSDATE;
420 if(X_MODE = 'I') then
421 X_LAST_UPDATED_BY := 1;
422 X_LAST_UPDATE_LOGIN := 0;
423 elsif (X_MODE = 'R') then
424 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
425 if X_LAST_UPDATED_BY is NULL then
426 X_LAST_UPDATED_BY := -1;
427 end if;
428 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
429 if X_LAST_UPDATE_LOGIN is NULL then
430 X_LAST_UPDATE_LOGIN := -1;
431 end if;
432 else
433 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
434 IGS_GE_MSG_STACK.ADD;
435 app_exception.raise_exception;
436 end if;
437 Before_DML(
438 p_action=>'UPDATE',
439 x_rowid=>X_ROWID,
440 x_occupational_title_code=>X_OCCUPATIONAL_TITLE_CODE,
441 x_occupational_title=>X_OCCUPATIONAL_TITLE,
442 x_alternate_title=>X_ALTERNATE_TITLE,
443 x_creation_date=>X_LAST_UPDATE_DATE,
444 x_created_by=>X_LAST_UPDATED_BY,
445 x_last_update_date=>X_LAST_UPDATE_DATE,
446 x_last_updated_by=>X_LAST_UPDATED_BY,
447 x_last_update_login=>X_LAST_UPDATE_LOGIN);
448 update IGS_PS_DIC_OCC_TITLS set
449 OCCUPATIONAL_TITLE = NEW_REFERENCES.OCCUPATIONAL_TITLE,
450 ALTERNATE_TITLE = NEW_REFERENCES.ALTERNATE_TITLE,
451 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
452 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
453 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
454 where ROWID = X_ROWID;
455 if (sql%notfound) then
456 raise no_data_found;
457 end if;
458
459 After_DML (
460 p_action => 'UPDATE' ,
461 x_rowid => X_ROWID
462 );
463 end UPDATE_ROW;
464 procedure ADD_ROW (
465 X_ROWID in out NOCOPY VARCHAR2,
466 x_OCCUPATIONAL_TITLE_CODE IN VARCHAR2,
467 x_OCCUPATIONAL_TITLE IN VARCHAR2,
468 x_ALTERNATE_TITLE IN VARCHAR2,
469 X_MODE in VARCHAR2 default 'R'
470 ) AS
471
472
473 /*************************************************************
474 Created By : jdeekoll
475 Date Created By : 11-May-2000
476 Purpose :
477 Know limitations, enhancements or remarks
478 Change History
479 Who When What
480
481 (reverse chronological order - newest change first)
482 ***************************************************************/
483
484
485 cursor c1 is select ROWID from IGS_PS_DIC_OCC_TITLS
486 where OCCUPATIONAL_TITLE_CODE= X_OCCUPATIONAL_TITLE_CODE
487 ;
488 begin
489 open c1;
490 fetch c1 into X_ROWID;
491 if (c1%notfound) then
492 close c1;
493 INSERT_ROW (
494 X_ROWID,
495 X_OCCUPATIONAL_TITLE_CODE,
496 X_OCCUPATIONAL_TITLE,
497 X_ALTERNATE_TITLE,
498 X_MODE );
499 return;
500 end if;
501 close c1;
502 UPDATE_ROW (
503 X_ROWID,
504 X_OCCUPATIONAL_TITLE_CODE,
505 X_OCCUPATIONAL_TITLE,
506 X_ALTERNATE_TITLE,
507 X_MODE );
508 end ADD_ROW;
509 procedure DELETE_ROW (
510 X_ROWID in VARCHAR2
511 ) AS
512
513 /*************************************************************
514 Created By : jdeekoll
515 Date Created By : 11-May-2000
516 Purpose :
517 Know limitations, enhancements or remarks
518 Change History
519 Who When What
520
521 (reverse chronological order - newest change first)
522 ***************************************************************/
523
524
525
526 begin
527 Before_DML (
528 p_action => 'DELETE',
529 x_rowid => X_ROWID
530 );
531 delete from IGS_PS_DIC_OCC_TITLS
532 where ROWID = X_ROWID;
533 if (sql%notfound) then
534 raise no_data_found;
535 end if;
536 After_DML (
537 p_action => 'DELETE',
538 x_rowid => X_ROWID
539 );
540 end DELETE_ROW;
541 END igs_ps_dic_occ_titls_pkg;
[Home] [Help]