1 PACKAGE BODY igs_or_org_note_type_pkg AS
2 /* $Header: IGSOI17B.pls 120.0 2005/06/01 12:57:41 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_or_org_note_type%RowType;
5 new_references igs_or_org_note_type%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_notes_type IN VARCHAR2 DEFAULT NULL,
11 x_note_type_description IN VARCHAR2 DEFAULT NULL,
12 x_inst_flag IN VARCHAR2 DEFAULT NULL,
13 x_unit_flag IN VARCHAR2 DEFAULT NULL,
14 x_location_flag IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 /*************************************************************
23 Created By : hchauhan
24 Date Created By : 2000/05/12
25 Purpose :
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_NOTE_TYPE
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.org_notes_type := x_org_notes_type;
57 new_references.note_type_description := x_note_type_description;
58 new_references.inst_flag := x_inst_flag;
59 new_references.unit_flag := x_unit_flag;
60 new_references.location_flag := x_location_flag;
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 : hchauhan
79 Date Created By : 2000/05/12
80 Purpose :
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) = 'UNIT_FLAG' THEN
93 new_references.unit_flag := column_value;
94 ELSIF UPPER(column_name) = 'LOCATION_FLAG' THEN
95 new_references.location_flag := column_value;
96 ELSIF UPPER(column_name) = 'INST_FLAG' THEN
97 new_references.inst_flag := column_value;
98 NULL;
99 END IF;
100
101
102
103 -- The following code checks for check constraints on the Columns.
104 IF Upper(Column_Name) = 'UNIT_FLAG' OR
105 Column_Name IS NULL THEN
106 IF NOT (new_references.unit_flag IN ('Y', 'N')) THEN
107 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END IF;
111 END IF;
112
113 -- The following code checks for check constraints on the Columns.
114 IF Upper(Column_Name) = 'LOCATION_FLAG' OR
115 Column_Name IS NULL THEN
116 IF NOT (new_references.location_flag IN ('Y', 'N')) THEN
117 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122
123 -- The following code checks for check constraints on the Columns.
124 IF Upper(Column_Name) = 'INST_FLAG' OR
125 Column_Name IS NULL THEN
126 IF NOT (new_references.inst_flag IN ('Y', 'N')) THEN
127 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131 END IF;
132
133
134
135 END Check_Constraints;
136
137 PROCEDURE Check_Child_Existance IS
138 /*************************************************************
139 Created By : hchauhan
140 Date Created By : 2000/05/12
141 Purpose :
142 Know limitations, enhancements or remarks
143 Change History
144 Who When What
145
146 (reverse chronological order - newest change first)
147 ***************************************************************/
148
149 BEGIN
150
151 Igs_Or_Org_Notes_Pkg.Get_FK_Igs_Or_Org_Note_Type (
152 old_references.org_notes_type
153 );
154
155 END Check_Child_Existance;
156
157 FUNCTION Get_PK_For_Validation (
158 x_org_notes_type IN VARCHAR2
159 ) RETURN BOOLEAN AS
160
161 /*************************************************************
162 Created By : hchauhan
163 Date Created By : 2000/05/12
164 Purpose :
165 Know limitations, enhancements or remarks
166 Change History
167 Who When What
168
169 (reverse chronological order - newest change first)
170 ***************************************************************/
171
172 CURSOR cur_rowid IS
173 SELECT rowid
174 FROM igs_or_org_note_type
175 WHERE org_notes_type = x_org_notes_type
176 FOR UPDATE NOWAIT;
177
178 lv_rowid cur_rowid%RowType;
179
180 BEGIN
181
182 Open cur_rowid;
183 Fetch cur_rowid INTO lv_rowid;
184 IF (cur_rowid%FOUND) THEN
185 Close cur_rowid;
186 Return(TRUE);
187 ELSE
188 Close cur_rowid;
189 Return(FALSE);
190 END IF;
191 END Get_PK_For_Validation;
192
193 PROCEDURE Before_DML (
194 p_action IN VARCHAR2,
195 x_rowid IN VARCHAR2 DEFAULT NULL,
196 x_org_notes_type IN VARCHAR2 DEFAULT NULL,
197 x_note_type_description IN VARCHAR2 DEFAULT NULL,
198 x_inst_flag IN VARCHAR2 DEFAULT NULL,
199 x_unit_flag IN VARCHAR2 DEFAULT NULL,
200 x_location_flag IN VARCHAR2 DEFAULT NULL,
201 x_creation_date IN DATE DEFAULT NULL,
202 x_created_by IN NUMBER DEFAULT NULL,
203 x_last_update_date IN DATE DEFAULT NULL,
204 x_last_updated_by IN NUMBER DEFAULT NULL,
205 x_last_update_login IN NUMBER DEFAULT NULL
206 ) AS
207 /*************************************************************
208 Created By : hchauhan
209 Date Created By : 2000/05/12
210 Purpose :
211 Know limitations, enhancements or remarks
212 Change History
213 Who When What
214
215 (reverse chronological order - newest change first)
216 ***************************************************************/
217
218 BEGIN
219
220 Set_Column_Values (
221 p_action,
222 x_rowid,
223 x_org_notes_type,
224 x_note_type_description,
225 x_inst_flag,
226 x_unit_flag,
227 x_location_flag,
228 x_creation_date,
229 x_created_by,
230 x_last_update_date,
231 x_last_updated_by,
232 x_last_update_login
233 );
234
235 IF (p_action = 'INSERT') THEN
236 -- Call all the procedures related to Before Insert.
237 Null;
238 IF Get_Pk_For_Validation(
239 new_references.org_notes_type) THEN
240 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244 Check_Constraints;
245 ELSIF (p_action = 'UPDATE') THEN
246 -- Call all the procedures related to Before Update.
247 Null;
248 Check_Constraints;
249 ELSIF (p_action = 'DELETE') THEN
250 -- Call all the procedures related to Before Delete.
251 Null;
252 Check_Child_Existance;
253 ELSIF (p_action = 'VALIDATE_INSERT') THEN
254 -- Call all the procedures related to Before Insert.
255 IF Get_PK_For_Validation (
256 new_references.org_notes_type) THEN
257 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END IF;
261 Check_Constraints;
262 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
263 Check_Constraints;
264 ELSIF (p_action = 'VALIDATE_DELETE') THEN
265 Check_Child_Existance;
266 END IF;
267
268 END Before_DML;
269
270 PROCEDURE After_DML (
271 p_action IN VARCHAR2,
272 x_rowid IN VARCHAR2
273 ) IS
274 /*************************************************************
275 Created By : hchauhan
276 Date Created By : 2000/05/12
277 Purpose :
278 Know limitations, enhancements or remarks
279 Change History
280 Who When What
281
282 (reverse chronological order - newest change first)
283 ***************************************************************/
284
285 BEGIN
286
287 l_rowid := x_rowid;
288
289 IF (p_action = 'INSERT') THEN
290 -- Call all the procedures related to After Insert.
291 Null;
292 ELSIF (p_action = 'UPDATE') THEN
293 -- Call all the procedures related to After Update.
294 Null;
295 ELSIF (p_action = 'DELETE') THEN
296 -- Call all the procedures related to After Delete.
297 Null;
298 END IF;
299
300 END After_DML;
301
302 procedure INSERT_ROW (
303 X_ROWID in out NOCOPY VARCHAR2,
304 x_ORG_NOTES_TYPE IN VARCHAR2,
305 x_NOTE_TYPE_DESCRIPTION IN VARCHAR2,
306 x_INST_FLAG IN VARCHAR2,
307 x_UNIT_FLAG IN VARCHAR2,
308 x_LOCATION_FLAG IN VARCHAR2,
309 X_MODE in VARCHAR2 default 'R'
310 ) AS
311 /*************************************************************
312 Created By : hchauhan
313 Date Created By : 2000/05/12
314 Purpose :
315 Know limitations, enhancements or remarks
316 Change History
317 Who When What
318
319 (reverse chronological order - newest change first)
320 ***************************************************************/
321
322 cursor C is select ROWID from IGS_OR_ORG_NOTE_TYPE
323 where ORG_NOTES_TYPE= X_ORG_NOTES_TYPE
324 ;
325 X_LAST_UPDATE_DATE DATE ;
326 X_LAST_UPDATED_BY NUMBER ;
327 X_LAST_UPDATE_LOGIN NUMBER ;
328 begin
329 X_LAST_UPDATE_DATE := SYSDATE;
330 if(X_MODE = 'I') then
331 X_LAST_UPDATED_BY := 1;
332 X_LAST_UPDATE_LOGIN := 0;
333 elsif (X_MODE = 'R') then
334 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
335 if X_LAST_UPDATED_BY is NULL then
336 X_LAST_UPDATED_BY := -1;
337 end if;
338 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
339 if X_LAST_UPDATE_LOGIN is NULL then
340 X_LAST_UPDATE_LOGIN := -1;
341 end if;
342 else
343 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
344 IGS_GE_MSG_STACK.ADD;
345 app_exception.raise_exception;
346 end if;
347 Before_DML(
348 p_action=>'INSERT',
349 x_rowid=>X_ROWID,
350 x_org_notes_type=>X_ORG_NOTES_TYPE,
351 x_note_type_description=>X_NOTE_TYPE_DESCRIPTION,
352 x_inst_flag=>X_INST_FLAG,
353 x_unit_flag=>X_UNIT_FLAG,
354 x_location_flag=>X_LOCATION_FLAG,
355 x_creation_date=>X_LAST_UPDATE_DATE,
356 x_created_by=>X_LAST_UPDATED_BY,
357 x_last_update_date=>X_LAST_UPDATE_DATE,
358 x_last_updated_by=>X_LAST_UPDATED_BY,
359 x_last_update_login=>X_LAST_UPDATE_LOGIN);
360 insert into IGS_OR_ORG_NOTE_TYPE (
361 ORG_NOTES_TYPE
362 ,NOTE_TYPE_DESCRIPTION
363 ,INST_FLAG
364 ,UNIT_FLAG
365 ,LOCATION_FLAG
366 ,CREATION_DATE
367 ,CREATED_BY
368 ,LAST_UPDATE_DATE
369 ,LAST_UPDATED_BY
370 ,LAST_UPDATE_LOGIN
371 ) values (
372 NEW_REFERENCES.ORG_NOTES_TYPE
373 ,NEW_REFERENCES.NOTE_TYPE_DESCRIPTION
374 ,NEW_REFERENCES.INST_FLAG
375 ,NEW_REFERENCES.UNIT_FLAG
376 ,NEW_REFERENCES.LOCATION_FLAG
377 ,X_LAST_UPDATE_DATE
378 ,X_LAST_UPDATED_BY
379 ,X_LAST_UPDATE_DATE
380 ,X_LAST_UPDATED_BY
381 ,X_LAST_UPDATE_LOGIN
382 );
383 open c;
384 fetch c into X_ROWID;
385 if (c%notfound) then
386 close c;
387 raise no_data_found;
388 end if;
389 close c;
390 After_DML (
391 p_action => 'INSERT' ,
392 x_rowid => X_ROWID );
393 end INSERT_ROW;
394 procedure LOCK_ROW (
395 X_ROWID in VARCHAR2,
396 x_ORG_NOTES_TYPE IN VARCHAR2,
397 x_NOTE_TYPE_DESCRIPTION IN VARCHAR2,
398 x_INST_FLAG IN VARCHAR2,
399 x_UNIT_FLAG IN VARCHAR2,
400 x_LOCATION_FLAG IN VARCHAR2 ) AS
401 /*************************************************************
402 Created By : hchauhan
403 Date Created By : 2000/05/12
404 Purpose :
405 Know limitations, enhancements or remarks
406 Change History
407 Who When What
408
409 (reverse chronological order - newest change first)
410 ***************************************************************/
411
412 cursor c1 is select
413 NOTE_TYPE_DESCRIPTION
414 , INST_FLAG
415 , UNIT_FLAG
416 , LOCATION_FLAG
417 from IGS_OR_ORG_NOTE_TYPE
418 where ROWID = X_ROWID
419 for update nowait;
420 tlinfo c1%rowtype;
421 begin
422 open c1;
423 fetch c1 into tlinfo;
424 if (c1%notfound) then
425 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
426 IGS_GE_MSG_STACK.ADD;
427 close c1;
428 app_exception.raise_exception;
429 return;
430 end if;
431 close c1;
432 if ( ( (tlinfo.NOTE_TYPE_DESCRIPTION = X_NOTE_TYPE_DESCRIPTION)
433 OR ((tlinfo.NOTE_TYPE_DESCRIPTION is null)
434 AND (X_NOTE_TYPE_DESCRIPTION is null)))
435 AND ((tlinfo.INST_FLAG = X_INST_FLAG)
436 OR ((tlinfo.INST_FLAG is null)
437 AND (X_INST_FLAG is null)))
438 AND ((tlinfo.UNIT_FLAG = X_UNIT_FLAG)
439 OR ((tlinfo.UNIT_FLAG is null)
440 AND (X_UNIT_FLAG is null)))
441 AND ((tlinfo.LOCATION_FLAG = X_LOCATION_FLAG)
442 OR ((tlinfo.LOCATION_FLAG is null)
443 AND (X_LOCATION_FLAG is null)))
444 ) then
445 null;
446 else
447 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
448 IGS_GE_MSG_STACK.ADD;
449 app_exception.raise_exception;
450 end if;
451 return;
452 end LOCK_ROW;
453 Procedure UPDATE_ROW (
454 X_ROWID in VARCHAR2,
455 x_ORG_NOTES_TYPE IN VARCHAR2,
456 x_NOTE_TYPE_DESCRIPTION IN VARCHAR2,
457 x_INST_FLAG IN VARCHAR2,
458 x_UNIT_FLAG IN VARCHAR2,
459 x_LOCATION_FLAG IN VARCHAR2,
460 X_MODE in VARCHAR2 default 'R'
461 ) AS
462 /*************************************************************
463 Created By : hchauhan
464 Date Created By : 2000/05/12
465 Purpose :
466 Know limitations, enhancements or remarks
467 Change History
468 Who When What
469
470 (reverse chronological order - newest change first)
471 ***************************************************************/
472
473 X_LAST_UPDATE_DATE DATE ;
474 X_LAST_UPDATED_BY NUMBER ;
475 X_LAST_UPDATE_LOGIN NUMBER ;
476 begin
477 X_LAST_UPDATE_DATE := SYSDATE;
478 if(X_MODE = 'I') then
479 X_LAST_UPDATED_BY := 1;
480 X_LAST_UPDATE_LOGIN := 0;
481 elsif (X_MODE = 'R') then
482 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
483 if X_LAST_UPDATED_BY is NULL then
484 X_LAST_UPDATED_BY := -1;
485 end if;
486 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
487 if X_LAST_UPDATE_LOGIN is NULL then
488 X_LAST_UPDATE_LOGIN := -1;
489 end if;
490 else
491 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
492 IGS_GE_MSG_STACK.ADD;
493 app_exception.raise_exception;
494 end if;
495 Before_DML(
496 p_action=>'UPDATE',
497 x_rowid=>X_ROWID,
498 x_org_notes_type=>X_ORG_NOTES_TYPE,
499 x_note_type_description=>X_NOTE_TYPE_DESCRIPTION,
500 x_inst_flag=>X_INST_FLAG,
501 x_unit_flag=>X_UNIT_FLAG,
502 x_location_flag=>X_LOCATION_FLAG,
503 x_creation_date=>X_LAST_UPDATE_DATE,
504 x_created_by=>X_LAST_UPDATED_BY,
505 x_last_update_date=>X_LAST_UPDATE_DATE,
506 x_last_updated_by=>X_LAST_UPDATED_BY,
507 x_last_update_login=>X_LAST_UPDATE_LOGIN);
508 update IGS_OR_ORG_NOTE_TYPE set
509 NOTE_TYPE_DESCRIPTION = NEW_REFERENCES.NOTE_TYPE_DESCRIPTION,
510 INST_FLAG = NEW_REFERENCES.INST_FLAG,
511 UNIT_FLAG = NEW_REFERENCES.UNIT_FLAG,
512 LOCATION_FLAG = NEW_REFERENCES.LOCATION_FLAG,
513 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
514 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
515 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
516 where ROWID = X_ROWID;
517 if (sql%notfound) then
518 raise no_data_found;
519 end if;
520
521 After_DML (
522 p_action => 'UPDATE' ,
523 x_rowid => X_ROWID
524 );
525 end UPDATE_ROW;
526 procedure ADD_ROW (
527 X_ROWID in out NOCOPY VARCHAR2,
528 x_ORG_NOTES_TYPE IN VARCHAR2,
529 x_NOTE_TYPE_DESCRIPTION IN VARCHAR2,
530 x_INST_FLAG IN VARCHAR2,
531 x_UNIT_FLAG IN VARCHAR2,
532 x_LOCATION_FLAG IN VARCHAR2,
533 X_MODE in VARCHAR2 default 'R'
534 ) AS
535 /*************************************************************
536 Created By : hchauhan
537 Date Created By : 2000/05/12
538 Purpose :
539 Know limitations, enhancements or remarks
540 Change History
541 Who When What
542
543 (reverse chronological order - newest change first)
544 ***************************************************************/
545
546 cursor c1 is select ROWID from IGS_OR_ORG_NOTE_TYPE
547 where ORG_NOTES_TYPE= X_ORG_NOTES_TYPE
548 ;
549 begin
550 open c1;
551 fetch c1 into X_ROWID;
552 if (c1%notfound) then
553 close c1;
554 INSERT_ROW (
555 X_ROWID,
556 X_ORG_NOTES_TYPE,
557 X_NOTE_TYPE_DESCRIPTION,
558 X_INST_FLAG,
559 X_UNIT_FLAG,
560 X_LOCATION_FLAG,
561 X_MODE );
562 return;
563 end if;
564 close c1;
565 UPDATE_ROW (
566 X_ROWID,
567 X_ORG_NOTES_TYPE,
568 X_NOTE_TYPE_DESCRIPTION,
569 X_INST_FLAG,
570 X_UNIT_FLAG,
571 X_LOCATION_FLAG,
572 X_MODE );
573 end ADD_ROW;
574 procedure DELETE_ROW (
575 X_ROWID in VARCHAR2
576 ) AS
577 /*************************************************************
578 Created By : hchauhan
579 Date Created By : 2000/05/12
580 Purpose :
581 Know limitations, enhancements or remarks
582 Change History
583 Who When What
584
585 (reverse chronological order - newest change first)
586 ***************************************************************/
587
588 begin
589 Before_DML (
590 p_action => 'DELETE',
591 x_rowid => X_ROWID
592 );
593 delete from IGS_OR_ORG_NOTE_TYPE
594 where ROWID = X_ROWID;
595 if (sql%notfound) then
596 raise no_data_found;
597 end if;
598 After_DML (
599 p_action => 'DELETE',
600 x_rowid => X_ROWID
601 );
602 end DELETE_ROW;
603 END igs_or_org_note_type_pkg;
[Home] [Help]