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