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