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