1 PACKAGE BODY igs_or_org_inst_type_pkg AS
2 /* $Header: IGSOI19B.pls 120.0 2005/06/01 16:49:51 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_or_org_inst_type_all%RowType;
5 new_references igs_or_org_inst_type_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_institution_type IN VARCHAR2 DEFAULT NULL,
11 x_description IN VARCHAR2 DEFAULT NULL,
12 x_SYSTEM_INST_TYPE VARCHAR2,
13 x_close_ind IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL ,
19 X_ORG_ID in NUMBER DEFAULT NULL
20 ) AS
21
22 /*************************************************************
23 Created By : ssahai
24 Date Created By : 11/05/2000
25 Purpose : Populating the new_references columns to be used by other functions.
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igs_or_org_inst_type_all
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.institution_type := x_institution_type;
57 new_references.description := x_description;
58 new_references.system_inst_type := x_system_inst_type;
59 new_references.close_ind := x_close_ind;
60 new_references.org_id := x_org_id;
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END Set_Column_Values;
73
74 PROCEDURE Check_Constraints (
75 Column_Name IN VARCHAR2 DEFAULT NULL,
76 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
77 /*************************************************************
78 Created By : ssahai
79 Date Created By : 11/5/2000
80 Purpose : To check for the existance of Parent values before inserting into foreign key columns.
81 Know limitations, enhancements or remarks
82 Change History
83 Who When What
84
85 (reverse chronological order - newest change first)
86 ***************************************************************/
87
88 BEGIN
89
90 IF column_name IS NULL THEN
91 NULL;
92 ELSIF UPPER(column_name) = 'CLOSE_IND' THEN
93 new_references.close_ind := column_value;
94 NULL;
95 END IF;
96
97
98
99 -- The following code checks for check constraints on the Columns.
100 IF Upper(Column_Name) = 'CLOSE_IND' OR
101 Column_Name IS NULL THEN
102 IF NOT (new_references.close_ind IN ('Y','N')) THEN
103 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 END IF;
108
109
110 END Check_Constraints;
111
112 PROCEDURE Check_Child_Existance IS
113 /*************************************************************
114 Created By : ssahai
115 Date Created By : 11/5/2000
116 Purpose : To check for the existance of of child records before deleting the records from this table
117 Know limitations, enhancements or remarks
118 Change History
119 Who When What
120 Aiyer 04-Feb-2003 Modified for the bug 2664699
121 Replaced call to IGS_AD_I_ENTRY_STATS_PKG.GET_FK_FOR_VALIDATION
122 with IGS_RC_I_ENT_STATS_PKG.GET_FK_FOR_VALIDATION
123 Gmaheswa 18-Mar-2005 Modified for bug 4207144. Deleted the reference to IGS_RC_I_ENT_STATS_PKG
124 as it is obsoleted.
125 (reverse chronological order - newest change first)
126 ***************************************************************/
127
128 BEGIN
129
130 Igs_Or_Institution_Pkg.Get_FK_Igs_Or_Org_Inst_Type (
131 old_references.institution_type
132 );
133
134 END Check_Child_Existance;
135
136 FUNCTION Get_PK_For_Validation (
137 x_institution_type IN VARCHAR2
138 ) RETURN BOOLEAN AS
139
140 /*************************************************************
141 Created By : ssahai
142 Date Created By : 11/5/2000
143 Purpose : This function is used by other TBH's in their check_parent_existance to validate their their Foreign key values to this PK.
144 Know limitations, enhancements or remarks
145 Change History
146 Who When What
147
148 (reverse chronological order - newest change first)
149 ***************************************************************/
150
151 CURSOR cur_rowid IS
152 SELECT rowid
153 FROM igs_or_org_inst_type_all
154 WHERE institution_type = x_institution_type
155 FOR UPDATE NOWAIT;
156
157 lv_rowid cur_rowid%RowType;
158
159 BEGIN
160
161 Open cur_rowid;
162 Fetch cur_rowid INTO lv_rowid;
163 IF (cur_rowid%FOUND) THEN
164 Close cur_rowid;
165 Return(TRUE);
166 ELSE
167 Close cur_rowid;
168 Return(FALSE);
169 END IF;
170 END Get_PK_For_Validation;
171
172 PROCEDURE Before_DML (
173 p_action IN VARCHAR2,
174 x_rowid IN VARCHAR2 DEFAULT NULL,
175 x_institution_type IN VARCHAR2 DEFAULT NULL,
176 x_description IN VARCHAR2 DEFAULT NULL,
177 x_SYSTEM_INST_TYPE VARCHAR2 DEFAULT NULL,
178 x_close_ind IN VARCHAR2 DEFAULT NULL,
179 x_creation_date IN DATE DEFAULT NULL,
180 x_created_by IN NUMBER DEFAULT NULL,
181 x_last_update_date IN DATE DEFAULT NULL,
182 x_last_updated_by IN NUMBER DEFAULT NULL,
183 x_last_update_login IN NUMBER DEFAULT NULL,
184 X_ORG_ID in NUMBER DEFAULT NULL
185 ) AS
186 /*************************************************************
187 Created By : ssahai
188 Date Created By : 11/5/2000
189 Purpose : This procedure is called before any DML operation as a parameter.
190 This is a function which is called from other functions like insert_row/ add_row etc.
191 Know limitations, enhancements or remarks
192 Change History
193 Who When What
194
195 (reverse chronological order - newest change first)
196 ***************************************************************/
197
198 BEGIN
199
200 Set_Column_Values (
201 p_action,
202 x_rowid,
203 x_institution_type,
204 x_description,
205 x_SYSTEM_INST_TYPE,
206 x_close_ind,
207 x_creation_date,
208 x_created_by,
209 x_last_update_date,
210 x_last_updated_by,
211 x_last_update_login ,
212 x_org_id
213 );
214
215 IF (p_action = 'INSERT') THEN
216 -- Call all the procedures related to Before Insert.
217 Null;
218 IF Get_Pk_For_Validation(
219 new_references.institution_type) THEN
220 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF;
224 Check_Constraints;
225 ELSIF (p_action = 'UPDATE') THEN
226 -- Call all the procedures related to Before Update.
227 Null;
228 Check_Constraints;
229 ELSIF (p_action = 'DELETE') THEN
230 -- Call all the procedures related to Before Delete.
231 Null;
232 Check_Child_Existance;
233 ELSIF (p_action = 'VALIDATE_INSERT') THEN
234 -- Call all the procedures related to Before Insert.
235 IF Get_PK_For_Validation (
236 new_references.institution_type) THEN
237 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
238 IGS_GE_MSG_STACK.ADD;
239 App_Exception.Raise_Exception;
240 END IF;
241 Check_Constraints;
242 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
243 Check_Constraints;
244 ELSIF (p_action = 'VALIDATE_DELETE') THEN
245 Check_Child_Existance;
246 END IF;
247
248 END Before_DML;
249
250 PROCEDURE After_DML (
251 p_action IN VARCHAR2,
252 x_rowid IN VARCHAR2
253 ) IS
254 /*************************************************************
255 Created By : ssahai
256 Date Created By : 11/5/2000
257 Purpose : In case there are any after dml operations functions are to be performed, those can come in here.
258 Know limitations, enhancements or remarks
259 Change History
260 Who When What
261
262 (reverse chronological order - newest change first)
263 ***************************************************************/
264
265 BEGIN
266
267 l_rowid := x_rowid;
268
269 IF (p_action = 'INSERT') THEN
270 -- Call all the procedures related to After Insert.
271 Null;
272 ELSIF (p_action = 'UPDATE') THEN
273 -- Call all the procedures related to After Update.
274 Null;
275 ELSIF (p_action = 'DELETE') THEN
276 -- Call all the procedures related to After Delete.
277 Null;
278 END IF;
279
280 END After_DML;
281
282 procedure INSERT_ROW (
283 X_ROWID in out NOCOPY VARCHAR2,
284 x_INSTITUTION_TYPE IN VARCHAR2,
285 x_DESCRIPTION IN VARCHAR2,
286 x_SYSTEM_INST_TYPE VARCHAR2,
287 x_CLOSE_IND IN VARCHAR2,
288 X_MODE in VARCHAR2 default 'R' ,
289 X_ORG_ID in NUMBER
290 ) AS
291 /*************************************************************
292 Created By : ssahai
293 Date Created By : 11/5/2000
294 Purpose : This procedure is called from forms during an insert_row (ON_INSERT) operation.
295 This in turn calls before_dml.
296 Know limitations, enhancements or remarks
297 Change History
298 Who When What
299
300 (reverse chronological order - newest change first)
301 ***************************************************************/
302
303 cursor C is select ROWID from igs_or_org_inst_type_all
304 where INSTITUTION_TYPE= X_INSTITUTION_TYPE
305 ;
306 X_LAST_UPDATE_DATE DATE ;
307 X_LAST_UPDATED_BY NUMBER ;
308 X_LAST_UPDATE_LOGIN NUMBER ;
309 begin
310 X_LAST_UPDATE_DATE := SYSDATE;
311 if(X_MODE = 'I') then
312 X_LAST_UPDATED_BY := 1;
313 X_LAST_UPDATE_LOGIN := 0;
314 elsif (X_MODE = 'R') then
315 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
316 if X_LAST_UPDATED_BY is NULL then
317 X_LAST_UPDATED_BY := -1;
318 end if;
319 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
320 if X_LAST_UPDATE_LOGIN is NULL then
321 X_LAST_UPDATE_LOGIN := -1;
322 end if;
323 else
324 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
325 IGS_GE_MSG_STACK.ADD;
326 app_exception.raise_exception;
327 end if;
328 Before_DML(
329 p_action=>'INSERT',
330 x_rowid=>X_ROWID,
331 x_institution_type=>X_INSTITUTION_TYPE,
332 x_description=>X_DESCRIPTION,
333 x_SYSTEM_INST_TYPE=>x_system_inst_type,
334 x_close_ind=>X_CLOSE_IND,
335 x_creation_date=>X_LAST_UPDATE_DATE,
336 x_created_by=>X_LAST_UPDATED_BY,
337 x_last_update_date=>X_LAST_UPDATE_DATE,
338 x_last_updated_by=>X_LAST_UPDATED_BY,
339 x_last_update_login=>X_LAST_UPDATE_LOGIN,
340 x_org_id=>igs_ge_gen_003.get_org_id);
341
342 insert into igs_or_org_inst_type_all (
343 INSTITUTION_TYPE
344 ,DESCRIPTION
345 ,SYSTEM_INST_TYPE
346 ,CLOSE_IND
347 ,CREATION_DATE
348 ,CREATED_BY
349 ,LAST_UPDATE_DATE
350 ,LAST_UPDATED_BY
351 ,LAST_UPDATE_LOGIN
352 ,ORG_ID
353 ) values (
354 NEW_REFERENCES.INSTITUTION_TYPE
355 ,NEW_REFERENCES.DESCRIPTION
356 ,NEW_REFERENCES.SYSTEM_INST_TYPE
357 ,NEW_REFERENCES.CLOSE_IND
358 ,X_LAST_UPDATE_DATE
359 ,X_LAST_UPDATED_BY
360 ,X_LAST_UPDATE_DATE
361 ,X_LAST_UPDATED_BY
362 ,X_LAST_UPDATE_LOGIN,
363 NEW_REFERENCES.ORG_ID
364 );
365 open c;
366 fetch c into X_ROWID;
367 if (c%notfound) then
368 close c;
369 raise no_data_found;
370 end if;
371 close c;
372 After_DML (
373 p_action => 'INSERT' ,
374 x_rowid => X_ROWID );
375 end INSERT_ROW;
376
377 procedure LOCK_ROW (
378 X_ROWID in VARCHAR2,
379 x_INSTITUTION_TYPE IN VARCHAR2,
380 x_DESCRIPTION IN VARCHAR2,
381 x_SYSTEM_INST_TYPE VARCHAR2,
382 x_CLOSE_IND IN VARCHAR2 ) AS
383 /*************************************************************
384 Created By : ssahai
385 Date Created By : 11/5/2000
386 Purpose : This procedure is called from forms during lock_row (ON_LOCK) operation
387 Know limitations, enhancements or remarks
388 Change History
389 Who When What
390
391 (reverse chronological order - newest change first)
392 ***************************************************************/
393
394 cursor c1 is select
395 DESCRIPTION
396 , CLOSE_IND
397 , SYSTEM_INST_TYPE
398 from igs_or_org_inst_type_all
399 where ROWID = X_ROWID
400 for update nowait;
401 tlinfo c1%rowtype;
402 begin
403 open c1;
404 fetch c1 into tlinfo;
405 if (c1%notfound) then
406 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
407 IGS_GE_MSG_STACK.ADD;
408 close c1;
409 app_exception.raise_exception;
410 return;
411 end if;
412 close c1;
413 if ( ( tlinfo.DESCRIPTION = X_DESCRIPTION) AND (tlinfo.system_inst_type = x_system_inst_type)
414 AND ((tlinfo.CLOSE_IND = X_CLOSE_IND)
415 OR ((tlinfo.CLOSE_IND is null)
416 AND (X_CLOSE_IND is null)))
417
418 ) then
419 null;
420 else
421 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
422 IGS_GE_MSG_STACK.ADD;
423 app_exception.raise_exception;
424 end if;
425 return;
426 end LOCK_ROW;
427 Procedure UPDATE_ROW (
428 X_ROWID in VARCHAR2,
429 x_INSTITUTION_TYPE IN VARCHAR2,
430 x_DESCRIPTION IN VARCHAR2,
431 x_SYSTEM_INST_TYPE VARCHAR2,
432 x_CLOSE_IND IN VARCHAR2,
433 X_MODE in VARCHAR2 default 'R'
434 ) AS
435 /*************************************************************
436 Created By : ssahai
437 Date Created By : 11/5/2000
438 Purpose : This procedure is used to update a row in case the PK of the row being updated is present. It is called from ADD_ROW.
439 Know limitations, enhancements or remarks
440 Change History
441 Who When What
442
443 (reverse chronological order - newest change first)
444 ***************************************************************/
445
446 X_LAST_UPDATE_DATE DATE ;
447 X_LAST_UPDATED_BY NUMBER ;
448 X_LAST_UPDATE_LOGIN NUMBER ;
449 begin
450 X_LAST_UPDATE_DATE := SYSDATE;
451 if(X_MODE = 'I') then
452 X_LAST_UPDATED_BY := 1;
453 X_LAST_UPDATE_LOGIN := 0;
454 elsif (X_MODE = 'R') then
455 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
456 if X_LAST_UPDATED_BY is NULL then
457 X_LAST_UPDATED_BY := -1;
458 end if;
459 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
460 if X_LAST_UPDATE_LOGIN is NULL then
461 X_LAST_UPDATE_LOGIN := -1;
462 end if;
463 else
464 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
465 IGS_GE_MSG_STACK.ADD;
466 app_exception.raise_exception;
467 end if;
468 Before_DML(
469 p_action=>'UPDATE',
470 x_rowid=>X_ROWID,
471 x_institution_type=>X_INSTITUTION_TYPE,
472 x_description=>X_DESCRIPTION,
473 x_SYSTEM_INST_TYPE => X_SYSTEM_INST_TYPE,
474 x_close_ind=>X_CLOSE_IND,
475 x_creation_date=>X_LAST_UPDATE_DATE,
476 x_created_by=>X_LAST_UPDATED_BY,
477 x_last_update_date=>X_LAST_UPDATE_DATE,
478 x_last_updated_by=>X_LAST_UPDATED_BY,
479 x_last_update_login=>X_LAST_UPDATE_LOGIN);
480 update igs_or_org_inst_type_all set
481 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
482 SYSTEM_INST_TYPE = NEW_REFERENCES.SYSTEM_INST_TYPE,
483 CLOSE_IND = NEW_REFERENCES.CLOSE_IND,
484 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
485 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
486 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
487 where ROWID = X_ROWID;
488 if (sql%notfound) then
489 raise no_data_found;
490 end if;
491
492 After_DML (
493 p_action => 'UPDATE' ,
494 x_rowid => X_ROWID
495 );
496 end UPDATE_ROW;
497 procedure ADD_ROW (
498 X_ROWID in out NOCOPY VARCHAR2,
499 x_INSTITUTION_TYPE IN VARCHAR2,
500 x_DESCRIPTION IN VARCHAR2,
501 x_SYSTEM_INST_TYPE VARCHAR2,
502 x_CLOSE_IND IN VARCHAR2,
503 X_MODE in VARCHAR2 default 'R' ,
504 X_ORG_ID in NUMBER
505 ) AS
506 /*************************************************************
507 Created By : ssahai
508 Date Created By : 11/5/2000
509 Purpose : This procedure is called from forms during an insert_row (INSERT_ROW) - It checks if there is a
510 row for the given PK and if there isn't then it inserts it as a new row. If there is a existing Pk then it
511 uses update_row to update the same row.
512 Know limitations, enhancements or remarks
513 Change History
514 Who When What
515
516 (reverse chronological order - newest change first)
517 ***************************************************************/
518
519 cursor c1 is select ROWID from igs_or_org_inst_type_all
520 where INSTITUTION_TYPE= X_INSTITUTION_TYPE
521 ;
522 begin
523 open c1;
524 fetch c1 into X_ROWID;
525 if (c1%notfound) then
526 close c1;
527 INSERT_ROW (
528 X_ROWID,
529 X_INSTITUTION_TYPE,
530 X_DESCRIPTION,
531 x_SYSTEM_INST_TYPE,
532 X_CLOSE_IND,
533 X_MODE ,
534 x_org_id);
535 return;
536 end if;
537 close c1;
538 UPDATE_ROW (
539 X_ROWID,
540 X_INSTITUTION_TYPE,
541 X_DESCRIPTION,
542 x_SYSTEM_INST_TYPE,
543 X_CLOSE_IND,
544 X_MODE );
545 end ADD_ROW;
546
547 procedure DELETE_ROW (
548 X_ROWID in VARCHAR2
549 ) AS
550 /*************************************************************
551 Created By : ssahai
552 Date Created By : 11/5/2000
553 Purpose : This procedure is called from forms during an delete_row (ON_DELETE) operation.
554 Know limitations, enhancements or remarks
555 Change History
556 Who When What
557
558 (reverse chronological order - newest change first)
559 ***************************************************************/
560
561 begin
562 Before_DML (
563 p_action => 'DELETE',
564 x_rowid => X_ROWID
565 );
566 delete from igs_or_org_inst_type_all
567 where ROWID = X_ROWID;
568 if (sql%notfound) then
569 raise no_data_found;
570 end if;
571 After_DML (
572 p_action => 'DELETE',
573 x_rowid => X_ROWID
574 );
575 end DELETE_ROW;
576 END igs_or_org_inst_type_pkg;
[Home] [Help]