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