[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_DSCP_PKG
Source
1 package body IGS_PS_UNIT_DSCP_PKG as
2 /* $Header: IGSPI76B.pls 115.6 2002/11/29 02:37:33 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_UNIT_DSCP%RowType;
6 new_references IGS_PS_UNIT_DSCP%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_unit_cd IN VARCHAR2 ,
12 x_version_number IN NUMBER ,
13 x_discipline_group_cd IN VARCHAR2 ,
14 x_percentage IN NUMBER ,
15 x_creation_date IN DATE ,
16 x_created_by IN NUMBER ,
17 x_last_update_date IN DATE ,
18 x_last_updated_by IN NUMBER ,
19 x_last_update_login IN NUMBER
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_PS_UNIT_DSCP
25 WHERE rowid = x_rowid;
26
27 BEGIN
28
29 l_rowid := x_rowid;
30
31 -- Code for setting the Old and New Reference Values.
32 -- Populate Old Values.
33 Open cur_old_ref_values;
34 Fetch cur_old_ref_values INTO old_references;
35 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
36 Close cur_old_ref_values;
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.unit_cd := x_unit_cd;
46 new_references.version_number := x_version_number;
47 new_references.discipline_group_cd := x_discipline_group_cd;
48 new_references.percentage := x_percentage;
49 IF (p_action = 'UPDATE') THEN
50 new_references.creation_date := old_references.creation_date;
51 new_references.created_by := old_references.created_by;
52 ELSE
53 new_references.creation_date := x_creation_date;
54 new_references.created_by := x_created_by;
55 END IF;
56 new_references.last_update_date := x_last_update_date;
57 new_references.last_updated_by := x_last_updated_by;
58 new_references.last_update_login := x_last_update_login;
59
60 END Set_Column_Values;
61
62 PROCEDURE BeforeRowInsertUpdateDelete1(
63 p_inserting IN BOOLEAN ,
64 p_updating IN BOOLEAN ,
65 p_deleting IN BOOLEAN
66 ) AS
67 v_unit_cd IGS_PS_UNIT_DSCP.unit_cd%TYPE;
68 v_version_number IGS_PS_UNIT_DSCP.version_number%TYPE;
69 v_percentage IGS_PS_UNIT_DSCP.percentage%TYPE;
70 v_message_name Varchar2(30);
71 BEGIN
72 -- Set variables.
73 IF p_deleting THEN
74 v_unit_cd := old_references.unit_cd;
75 v_version_number := old_references.version_number;
76 ELSE -- p_inserting or p_updating
77 v_unit_cd := new_references.unit_cd;
78 v_version_number := new_references.version_number;
79 END IF;
80 -- Validate the insert/update/delete.
81 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
82 v_unit_cd,
83 v_version_number,
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 -- Validate IGS_PS_DSCP group code. IGS_PS_DSCP group code is not updateable.
90 IF p_inserting THEN
91 IF IGS_PS_VAL_UD.crsp_val_ud_dg_cd (
92 new_references.discipline_group_cd,
93 v_message_name) = FALSE THEN
94 Fnd_Message.Set_Name('IGS',v_message_name);
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END IF;
98 END IF;
99 -- Create history record.
100 IF p_updating THEN
101 IF old_references.percentage <> new_references.percentage THEN
102 SELECT DECODE (old_references.percentage,new_references.percentage,NULL,old_references.percentage)
103 INTO v_percentage
104 FROM dual;
105 IGS_PS_GEN_005.CRSP_INS_UD_HIST(
106 old_references.unit_cd,
107 old_references.version_number,
108 old_references.discipline_group_cd,
109 old_references.last_update_date,
110 new_references.last_update_date,
111 old_references.last_updated_by,
112 v_percentage);
113 END IF;
114 END IF;
115 -- Create history record on delete
116 IF p_deleting THEN
117 IGS_PS_GEN_005.CRSP_INS_UD_HIST(
118 old_references.unit_cd,
119 old_references.version_number,
120 old_references.discipline_group_cd,
121 old_references.last_update_date,
122 SYSDATE,
123 old_references.last_updated_by,
124 old_references.percentage);
125 END IF;
126
127
128 END BeforeRowInsertUpdateDelete1;
129
130 PROCEDURE Check_Constraints(
131 Column_Name IN VARCHAR2,
132 Column_Value IN VARCHAR2)
133 AS
134 BEGIN
135
136 IF Column_Name IS NULL Then
137 NULL;
138 ELSIF Upper(Column_Name)='PERCENTAGE' Then
139 New_References.Percentage := igs_ge_number.to_num(Column_Value);
140 ELSIF Upper(Column_Name)='DISCIPLINE_GROUP_CD' Then
141 New_References.Discipline_Group_Cd := Column_Value;
142 ELSIF Upper(Column_Name)='UNIT_CD' Then
143 New_References.Unit_Cd := Column_Value;
144 END IF;
145
146 IF Upper(Column_Name)='PERCENTAGE' OR Column_Name IS NULL Then
147 IF New_References.Percentage <= 0 OR New_References.Percentage > 100 Then
148 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153
154 IF Upper(Column_Name)='DISCIPLINE_GROUP_CD' OR Column_Name IS NULL Then
155 IF New_References.Discipline_Group_Cd <> UPPER(New_References.Discipline_Group_Cd) Then
156 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161
162 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
163 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 END Check_Constraints;
171
172 PROCEDURE Check_Parent_Existance AS
173 BEGIN
174
175 IF (((old_references.discipline_group_cd = new_references.discipline_group_cd)) OR
176 ((new_references.discipline_group_cd IS NULL))) THEN
177 NULL;
178 ELSE
179 IF NOT IGS_PS_DSCP_PKG.Get_PK_For_Validation (
180 new_references.discipline_group_cd
181 ) THEN
182
183 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186 END IF;
187
188 END IF;
189
190 IF (((old_references.unit_cd = new_references.unit_cd) AND
191 (old_references.version_number = new_references.version_number)) OR
192 ((new_references.unit_cd IS NULL) OR
193 (new_references.version_number IS NULL))) THEN
194 NULL;
195 ELSE
196 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
197 new_references.unit_cd,
198 new_references.version_number
199 ) THEN
200
201 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception;
204 END IF;
205
206 END IF;
207
208 END Check_Parent_Existance;
209
210 FUNCTION Get_PK_For_Validation (
211 x_unit_cd IN VARCHAR2,
212 x_version_number IN NUMBER,
213 x_discipline_group_cd IN VARCHAR2
214 ) RETURN BOOLEAN AS
215
216 CURSOR cur_rowid IS
217 SELECT rowid
218 FROM IGS_PS_UNIT_DSCP
219 WHERE unit_cd = x_unit_cd
220 AND version_number = x_version_number
221 AND discipline_group_cd = x_discipline_group_cd
222 FOR UPDATE NOWAIT;
223
224 lv_rowid cur_rowid%RowType;
225
226 BEGIN
227
228 Open cur_rowid;
229 Fetch cur_rowid INTO lv_rowid;
230
231 IF (cur_rowid%FOUND) THEN
232 Close cur_rowid;
233 Return(TRUE);
234 ELSE
235 Close cur_rowid;
236 Return(FALSE);
237 END IF;
238
239 END Get_PK_For_Validation;
240
241 PROCEDURE GET_FK_IGS_PS_DSCP (
242 x_discipline_group_cd IN VARCHAR2
243 ) AS
244
245 CURSOR cur_rowid IS
246 SELECT rowid
247 FROM IGS_PS_UNIT_DSCP
248 WHERE discipline_group_cd = x_discipline_group_cd ;
249
250 lv_rowid cur_rowid%RowType;
251
252 BEGIN
253
254 Open cur_rowid;
255 Fetch cur_rowid INTO lv_rowid;
256 IF (cur_rowid%FOUND) THEN
257 Close cur_rowid;
258 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UD_DI_FK');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 Return;
262 END IF;
263 Close cur_rowid;
264
265 END GET_FK_IGS_PS_DSCP;
266
267 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
268 x_unit_cd IN VARCHAR2,
269 x_version_number IN NUMBER
270 ) AS
271
272 CURSOR cur_rowid IS
273 SELECT rowid
274 FROM IGS_PS_UNIT_DSCP
275 WHERE unit_cd = x_unit_cd
276 AND version_number = x_version_number ;
277
278 lv_rowid cur_rowid%RowType;
279
280 BEGIN
281
282 Open cur_rowid;
283 Fetch cur_rowid INTO lv_rowid;
284 IF (cur_rowid%FOUND) THEN
285 Close cur_rowid;
286 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UD_UV_FK');
287 IGS_GE_MSG_STACK.ADD;
288 App_Exception.Raise_Exception;
289 Return;
290 END IF;
291 Close cur_rowid;
292
293 END GET_FK_IGS_PS_UNIT_VER;
294
295 PROCEDURE Before_DML (
296 p_action IN VARCHAR2,
297 x_rowid IN VARCHAR2 ,
298 x_unit_cd IN VARCHAR2 ,
299 x_version_number IN NUMBER ,
300 x_discipline_group_cd IN VARCHAR2 ,
301 x_percentage IN NUMBER ,
302 x_creation_date IN DATE ,
303 x_created_by IN NUMBER ,
304 x_last_update_date IN DATE ,
305 x_last_updated_by IN NUMBER ,
306 x_last_update_login IN NUMBER
307 ) AS
308 BEGIN
309
310 Set_Column_Values (
311 p_action,
312 x_rowid,
313 x_unit_cd,
314 x_version_number,
315 x_discipline_group_cd,
316 x_percentage,
317 x_creation_date,
318 x_created_by,
319 x_last_update_date,
320 x_last_updated_by,
321 x_last_update_login
322 );
323
324 IF (p_action = 'INSERT') THEN
325 -- Call all the procedures related to Before Insert.
326 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE );
327 IF Get_PK_For_Validation (New_References.unit_cd,
328 New_References.version_number,
329 New_References.discipline_group_cd) THEN
330 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
331 IGS_GE_MSG_STACK.ADD;
332 App_Exception.Raise_Exception;
333 END IF;
334 Check_Constraints;
335 Check_Parent_Existance;
336 ELSIF (p_action = 'UPDATE') THEN
337 -- Call all the procedures related to Before Update.
338 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE, p_inserting => FALSE, p_deleting => FALSE );
339 Check_Constraints;
340 Check_Parent_Existance;
341 ELSIF (p_action = 'DELETE') THEN
342 -- Call all the procedures related to Before Delete.
343 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE, p_inserting => FALSE, p_updating => FALSE );
344 ELSIF (p_action = 'VALIDATE_INSERT') THEN
345 IF Get_PK_For_Validation (New_References.unit_cd,
346 New_References.version_number,
347 New_References.discipline_group_cd) THEN
348 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
349 IGS_GE_MSG_STACK.ADD;
350 App_Exception.Raise_Exception;
351 END IF;
352 Check_Constraints;
353 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
354 Check_Constraints;
355
356 END IF;
357
358 END Before_DML;
359
360 PROCEDURE After_DML (
361 p_action IN VARCHAR2,
362 x_rowid IN VARCHAR2
363 ) AS
364 BEGIN
365
366 l_rowid := x_rowid;
367
368
369 END After_DML;
370
371 procedure INSERT_ROW (
372 X_ROWID in out NOCOPY VARCHAR2,
373 X_UNIT_CD in VARCHAR2,
374 X_VERSION_NUMBER in NUMBER,
375 X_DISCIPLINE_GROUP_CD in VARCHAR2,
376 X_PERCENTAGE in NUMBER,
377 X_MODE in VARCHAR2
378 ) AS
379 cursor C is select ROWID from IGS_PS_UNIT_DSCP
380 where UNIT_CD = X_UNIT_CD
381 and VERSION_NUMBER = X_VERSION_NUMBER
382 and DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD;
383 X_LAST_UPDATE_DATE DATE;
384 X_LAST_UPDATED_BY NUMBER;
385 X_LAST_UPDATE_LOGIN NUMBER;
386 begin
387 X_LAST_UPDATE_DATE := SYSDATE;
388 if(X_MODE = 'I') then
389 X_LAST_UPDATED_BY := 1;
390 X_LAST_UPDATE_LOGIN := 0;
391 elsif (X_MODE = 'R') then
392 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
393 if X_LAST_UPDATED_BY is NULL then
394 X_LAST_UPDATED_BY := -1;
395 end if;
396 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
397 if X_LAST_UPDATE_LOGIN is NULL then
398 X_LAST_UPDATE_LOGIN := -1;
399 end if;
400 else
401 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
402 IGS_GE_MSG_STACK.ADD;
403 app_exception.raise_exception;
404 end if;
405
406 Before_DML(
407 p_action => 'INSERT',
408 x_rowid => X_ROWID,
409 x_unit_cd => X_UNIT_CD,
410 x_version_number => X_VERSION_NUMBER,
411 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
412 x_percentage => X_PERCENTAGE,
413 x_creation_date => X_LAST_UPDATE_DATE,
414 x_created_by => X_LAST_UPDATED_BY,
415 x_last_update_date => X_LAST_UPDATE_DATE,
416 x_last_updated_by => X_LAST_UPDATED_BY,
417 x_last_update_login => X_LAST_UPDATE_LOGIN
418 );
419
420 insert into IGS_PS_UNIT_DSCP (
421 UNIT_CD,
422 VERSION_NUMBER,
423 DISCIPLINE_GROUP_CD,
424 PERCENTAGE,
425 CREATION_DATE,
426 CREATED_BY,
427 LAST_UPDATE_DATE,
428 LAST_UPDATED_BY,
429 LAST_UPDATE_LOGIN
430 ) values (
431 NEW_REFERENCES.UNIT_CD,
432 NEW_REFERENCES.VERSION_NUMBER,
433 NEW_REFERENCES.DISCIPLINE_GROUP_CD,
434 NEW_REFERENCES.PERCENTAGE,
435 X_LAST_UPDATE_DATE,
436 X_LAST_UPDATED_BY,
437 X_LAST_UPDATE_DATE,
438 X_LAST_UPDATED_BY,
439 X_LAST_UPDATE_LOGIN
440 );
441
442 open c;
443 fetch c into X_ROWID;
444 if (c%notfound) then
445 close c;
446 raise no_data_found;
447 end if;
448 close c;
449 After_DML (
450 p_action => 'INSERT',
451 x_rowid => X_ROWID
452 );
453 end INSERT_ROW;
454
455 procedure LOCK_ROW (
456 X_ROWID IN VARCHAR2,
457 X_UNIT_CD in VARCHAR2,
458 X_VERSION_NUMBER in NUMBER,
459 X_DISCIPLINE_GROUP_CD in VARCHAR2,
460 X_PERCENTAGE in NUMBER
461 ) AS
462 cursor c1 is select
463 PERCENTAGE
464 from IGS_PS_UNIT_DSCP
465 where ROWID = X_ROWID
466 for update nowait;
467 tlinfo c1%rowtype;
468
469 begin
470 open c1;
471 fetch c1 into tlinfo;
472 if (c1%notfound) then
473 close c1;
474 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 return;
478 end if;
479 close c1;
480
481 if ( (tlinfo.PERCENTAGE = X_PERCENTAGE)
482 ) then
483 null;
484 else
485 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
486 IGS_GE_MSG_STACK.ADD;
487 app_exception.raise_exception;
488 end if;
489 return;
490 end LOCK_ROW;
491
492 procedure UPDATE_ROW (
493 X_ROWID IN VARCHAR2,
494 X_UNIT_CD in VARCHAR2,
495 X_VERSION_NUMBER in NUMBER,
496 X_DISCIPLINE_GROUP_CD in VARCHAR2,
497 X_PERCENTAGE in NUMBER,
498 X_MODE in VARCHAR2
499 ) AS
500 X_LAST_UPDATE_DATE DATE;
501 X_LAST_UPDATED_BY NUMBER;
502 X_LAST_UPDATE_LOGIN NUMBER;
503 begin
504 X_LAST_UPDATE_DATE := SYSDATE;
505 if(X_MODE = 'I') then
506 X_LAST_UPDATED_BY := 1;
507 X_LAST_UPDATE_LOGIN := 0;
508 elsif (X_MODE = 'R') then
509 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
510 if X_LAST_UPDATED_BY is NULL then
511 X_LAST_UPDATED_BY := -1;
512 end if;
513 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
514 if X_LAST_UPDATE_LOGIN is NULL then
515 X_LAST_UPDATE_LOGIN := -1;
516 end if;
517 else
518 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
519 IGS_GE_MSG_STACK.ADD;
520 app_exception.raise_exception;
521 end if;
522
523 Before_DML(
524 p_action => 'UPDATE',
525 x_rowid => X_ROWID,
526 x_unit_cd => X_UNIT_CD,
527 x_version_number => X_VERSION_NUMBER,
528 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
529 x_percentage => X_PERCENTAGE,
530 x_creation_date => X_LAST_UPDATE_DATE,
531 x_created_by => X_LAST_UPDATED_BY,
532 x_last_update_date => X_LAST_UPDATE_DATE,
533 x_last_updated_by => X_LAST_UPDATED_BY,
534 x_last_update_login => X_LAST_UPDATE_LOGIN
535 );
536 update IGS_PS_UNIT_DSCP set
537 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
538 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
539 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
540 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
541 where ROWID = X_ROWID
542 ;
543 if (sql%notfound) then
544 raise no_data_found;
545 end if;
546 After_DML (
547 p_action => 'UPDATE',
548 x_rowid => X_ROWID
549 );
550
551 end UPDATE_ROW;
552
553 procedure ADD_ROW (
554 X_ROWID in out NOCOPY VARCHAR2,
555 X_UNIT_CD in VARCHAR2,
556 X_VERSION_NUMBER in NUMBER,
557 X_DISCIPLINE_GROUP_CD in VARCHAR2,
558 X_PERCENTAGE in NUMBER,
559 X_MODE in VARCHAR2
560 ) AS
561 cursor c1 is select rowid from IGS_PS_UNIT_DSCP
562 where UNIT_CD = X_UNIT_CD
563 and VERSION_NUMBER = X_VERSION_NUMBER
564 and DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD
565 ;
566 begin
567 open c1;
568 fetch c1 into X_ROWID;
569 if (c1%notfound) then
570 close c1;
571 INSERT_ROW (
572 X_ROWID,
573 X_UNIT_CD,
574 X_VERSION_NUMBER,
575 X_DISCIPLINE_GROUP_CD,
576 X_PERCENTAGE,
577 X_MODE);
578 return;
579 end if;
580 close c1;
581 UPDATE_ROW (
582 X_ROWID,
583 X_UNIT_CD,
584 X_VERSION_NUMBER,
585 X_DISCIPLINE_GROUP_CD,
586 X_PERCENTAGE,
587 X_MODE);
588 end ADD_ROW;
589
590 procedure DELETE_ROW (
591 X_ROWID in VARCHAR2
592 ) AS
593 begin
594 Before_DML(
595 p_action => 'DELETE',
596 x_rowid => X_ROWID
597 );
598 delete from IGS_PS_UNIT_DSCP
599 where ROWID = X_ROWID;
600 if (sql%notfound) then
601 raise no_data_found;
602 end if;
603 After_DML(
604 p_action => 'DELETE',
605 x_rowid => X_ROWID
606 );
607 end DELETE_ROW;
608
609 end IGS_PS_UNIT_DSCP_PKG;