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