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