[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_TCH_RSOV_HIST_PKG
Source
1 package body IGS_PS_TCH_RSOV_HIST_PKG as
2 /* $Header: IGSPI72B.pls 115.6 2002/12/23 04:54:15 smvk ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_TCH_RSOV_HIST_ALL%RowType;
5 new_references IGS_PS_TCH_RSOV_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_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_location_cd IN VARCHAR2 DEFAULT NULL,
15 x_unit_class IN VARCHAR2 DEFAULT NULL,
16 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
17 x_ou_start_dt IN DATE DEFAULT NULL,
18 x_hist_start_dt IN DATE DEFAULT NULL,
19 x_hist_end_dt IN DATE DEFAULT NULL,
20 x_hist_who IN NUMBER DEFAULT NULL,
21 x_percentage IN NUMBER DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL ,
27 x_org_id IN NUMBER DEFAULT NULL
28 ) AS
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_PS_TCH_RSOV_HIST_ALL
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 Close cur_old_ref_values;
45 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 Return;
49 END IF;
50 Close cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.unit_cd := x_unit_cd;
54 new_references.version_number := x_version_number;
55 new_references.cal_type := x_cal_type;
56 new_references.ci_sequence_number := x_ci_sequence_number;
57 new_references.location_cd := x_location_cd;
58 new_references.unit_class := x_unit_class;
59 new_references.org_unit_cd := x_org_unit_cd;
60 new_references.ou_start_dt := x_ou_start_dt;
61 new_references.hist_start_dt := x_hist_start_dt;
62 new_references.hist_end_dt := x_hist_end_dt;
63 new_references.hist_who := x_hist_who;
64 new_references.percentage := x_percentage;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75 new_references.org_id := x_org_id;
76
77 END Set_Column_Values;
78
79 PROCEDURE Check_Constraints (
80 Column_Name IN VARCHAR2 DEFAULT NULL,
81 Column_Value IN VARCHAR2 DEFAULT NULL
82 )
83 AS
84 BEGIN
85
86 IF column_name is null then
87 NULL;
88 ELSIF upper(Column_name) = 'PERCENTAGE' then
89 new_references.percentage := IGS_GE_NUMBER.TO_NUM(column_value);
90 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
91 new_references.ci_sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
92 ELSIF upper(Column_name) = 'CAL_TYPE' then
93 new_references.cal_type := column_value;
94 ELSIF upper(Column_name) = 'LOCATION_CD' then
95 new_references.location_cd := column_value;
96 ELSIF upper(Column_name) = 'UNIT_CLASS' then
97 new_references.unit_class:= column_value;
98 ELSIF upper(Column_name) = 'UNIT_CD' then
99 new_references.unit_cd:= column_value;
100 END IF;
101
102 IF upper(column_name) = 'PERCENTAGE' OR
103 column_name is null Then
104 IF new_references.percentage < 000.01 OR new_references.percentage > 100.00 Then
105 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109 END IF;
110
111 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
112 column_name is null Then
113 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 Then
114 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 END IF;
119
120 IF upper(column_name) = 'CAL_TYPE' OR
121 column_name is null Then
122 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
123 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127 END IF;
128
129 IF upper(column_name) = 'LOCATION_CD' OR
130 column_name is null Then
131 IF new_references.location_cd <> UPPER(new_references.location_cd) Then
132 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
133 IGS_GE_MSG_STACK.ADD;
134 App_Exception.Raise_Exception;
135 END IF;
136 END IF;
137
138 IF upper(column_name) = 'UNIT_CLASS' OR
139 column_name is null Then
140 IF new_references.unit_class <> UPPER(new_references.unit_class) Then
141 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146
147 IF upper(column_name) = 'UNIT_CD' OR
148 column_name is null Then
149 IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
150 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END IF;
154 END IF;
155
156 END check_constraints;
157
158 PROCEDURE Check_Parent_Existance AS
159 BEGIN
160
161 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
162 (old_references.ou_start_dt = new_references.ou_start_dt)) OR
163 ((new_references.org_unit_cd IS NULL) OR
164 (new_references.ou_start_dt IS NULL))) THEN
165 NULL;
166 ELSE
167 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
168 new_references.org_unit_cd,
169 new_references.ou_start_dt
170 ) THEN
171 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174 END IF;
175 END IF;
176
177 IF (((old_references.unit_cd = new_references.unit_cd) AND
178 (old_references.version_number = new_references.version_number) AND
179 (old_references.cal_type = new_references.cal_type) AND
180 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
181 (old_references.location_cd = new_references.location_cd) AND
182 (old_references.unit_class = new_references.unit_class)) OR
183 ((new_references.unit_cd IS NULL) OR
184 (new_references.version_number IS NULL) OR
185 (new_references.cal_type IS NULL) OR
186 (new_references.ci_sequence_number IS NULL) OR
187 (new_references.location_cd IS NULL) OR
188 (new_references.unit_class IS NULL))) THEN
189 NULL;
190 ELSE
191 IF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_PK_For_Validation (
192 new_references.unit_cd,
193 new_references.version_number,
194 new_references.cal_type,
195 new_references.ci_sequence_number,
196 new_references.location_cd,
197 new_references.unit_class
198 ) THEN
199 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203 END IF;
204
205 END Check_Parent_Existance;
206
207 FUNCTION Get_PK_For_Validation (
208 x_unit_cd IN VARCHAR2,
209 x_version_number IN NUMBER,
210 x_cal_type IN VARCHAR2,
211 x_ci_sequence_number IN NUMBER,
212 x_location_cd IN VARCHAR2,
213 x_unit_class IN VARCHAR2,
214 x_org_unit_cd IN VARCHAR2,
215 x_ou_start_dt IN DATE,
216 x_hist_start_dt IN DATE
217 ) RETURN BOOLEAN AS
218
219 CURSOR cur_rowid IS
220 SELECT rowid
221 FROM IGS_PS_TCH_RSOV_HIST_ALL
222 WHERE unit_cd = x_unit_cd
223 AND version_number = x_version_number
224 AND cal_type = x_cal_type
225 AND ci_sequence_number = x_ci_sequence_number
226 AND location_cd = x_location_cd
227 AND unit_class = x_unit_class
228 AND org_unit_cd = x_org_unit_cd
229 AND ou_start_dt = x_ou_start_dt
230 AND hist_start_dt = x_hist_start_dt
231 FOR UPDATE NOWAIT;
232
233 lv_rowid cur_rowid%RowType;
234
235 BEGIN
236
237 Open cur_rowid;
238 Fetch cur_rowid INTO lv_rowid;
239 IF (cur_rowid%FOUND) THEN
240 Close cur_rowid;
241 Return (TRUE);
242 ELSE
243 Close cur_rowid;
244 Return (FALSE);
245 END IF;
246 END Get_PK_For_Validation;
247
248 PROCEDURE GET_FK_IGS_OR_UNIT (
249 x_org_unit_cd IN VARCHAR2,
250 x_start_dt IN VARCHAR2
251 ) AS
252
253 CURSOR cur_rowid IS
254 SELECT rowid
255 FROM IGS_PS_TCH_RSOV_HIST_ALL
256 WHERE org_unit_cd = x_org_unit_cd
257 AND ou_start_dt = x_start_dt ;
258
259 lv_rowid cur_rowid%RowType;
260
261 BEGIN
262
263 Open cur_rowid;
264 Fetch cur_rowid INTO lv_rowid;
265 IF (cur_rowid%FOUND) THEN
266 Close cur_rowid;
267 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TROH_OU_FK');
268 IGS_GE_MSG_STACK.ADD;
269 App_Exception.Raise_Exception;
270 Return;
271 END IF;
272 Close cur_rowid;
273
274 END GET_FK_IGS_OR_UNIT;
275
276 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_OPT (
277 x_unit_cd IN VARCHAR2,
278 x_version_number IN NUMBER,
279 x_cal_type IN VARCHAR2,
280 x_ci_sequence_number IN NUMBER,
281 x_location_cd IN VARCHAR2,
282 x_unit_class IN VARCHAR2
283 ) AS
284
285 CURSOR cur_rowid IS
286 SELECT rowid
287 FROM IGS_PS_TCH_RSOV_HIST_ALL
288 WHERE unit_cd = x_unit_cd
289 AND version_number = x_version_number
290 AND cal_type = x_cal_type
291 AND ci_sequence_number = x_ci_sequence_number
292 AND location_cd = x_location_cd
293 AND unit_class = x_unit_class ;
294
295 lv_rowid cur_rowid%RowType;
296
297 BEGIN
298
299 Open cur_rowid;
300 Fetch cur_rowid INTO lv_rowid;
301 IF (cur_rowid%FOUND) THEN
302 Close cur_rowid;
303 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TROH_UOO_FK');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 Return;
307 END IF;
308 Close cur_rowid;
309
310 END GET_FK_IGS_PS_UNIT_OFR_OPT;
311
312 PROCEDURE Before_DML (
313 p_action IN VARCHAR2,
314 x_rowid IN VARCHAR2 DEFAULT NULL,
315 x_unit_cd IN VARCHAR2 DEFAULT NULL,
316 x_version_number IN NUMBER DEFAULT NULL,
317 x_cal_type IN VARCHAR2 DEFAULT NULL,
318 x_ci_sequence_number IN NUMBER DEFAULT NULL,
319 x_location_cd IN VARCHAR2 DEFAULT NULL,
320 x_unit_class IN VARCHAR2 DEFAULT NULL,
321 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
322 x_ou_start_dt IN DATE DEFAULT NULL,
323 x_hist_start_dt IN DATE DEFAULT NULL,
324 x_hist_end_dt IN DATE DEFAULT NULL,
325 x_hist_who IN NUMBER DEFAULT NULL,
326 x_percentage IN NUMBER DEFAULT NULL,
327 x_creation_date IN DATE DEFAULT NULL,
328 x_created_by IN NUMBER DEFAULT NULL,
329 x_last_update_date IN DATE DEFAULT NULL,
330 x_last_updated_by IN NUMBER DEFAULT NULL,
331 x_last_update_login IN NUMBER DEFAULT NULL,
332 x_org_id IN NUMBER DEFAULT NULL
333 ) AS
334 BEGIN
335
336 Set_Column_Values (
337 p_action,
338 x_rowid,
339 x_unit_cd,
340 x_version_number,
341 x_cal_type,
342 x_ci_sequence_number,
343 x_location_cd,
344 x_unit_class,
345 x_org_unit_cd,
346 x_ou_start_dt,
347 x_hist_start_dt,
348 x_hist_end_dt,
349 x_hist_who,
350 x_percentage,
351 x_creation_date,
352 x_created_by,
353 x_last_update_date,
354 x_last_updated_by,
355 x_last_update_login ,
356 x_org_id
357 );
358
359 IF (p_action = 'INSERT') THEN
360 -- Call all the procedures related to Before Insert.
361
362 IF Get_PK_For_Validation (
363 new_references.unit_cd,
364 new_references.version_number,
365 new_references.cal_type,
366 new_references.ci_sequence_number,
367 new_references.location_cd,
368 new_references.unit_class,
369 new_references.org_unit_cd,
370 new_references.ou_start_dt,
371 new_references.hist_start_dt
372 ) THEN
373 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 Check_Constraints;
378 Check_Parent_Existance;
379 ELSIF (p_action = 'UPDATE') THEN
380 -- Call all the procedures related to Before Update.
381
382 Check_Constraints;
383 Check_Parent_Existance;
384
385 ELSIF (p_action = 'VALIDATE_INSERT') THEN
386 IF Get_PK_For_Validation (
387 new_references.unit_cd,
388 new_references.version_number,
389 new_references.cal_type,
390 new_references.ci_sequence_number,
391 new_references.location_cd,
392 new_references.unit_class,
393 new_references.org_unit_cd,
394 new_references.ou_start_dt,
395 new_references.hist_start_dt
396 ) THEN
397 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
398 IGS_GE_MSG_STACK.ADD;
399 App_Exception.Raise_Exception;
400 END IF;
401 Check_Constraints;
402 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
403 Check_Constraints;
404 END IF;
405
406 END Before_DML;
407
408 PROCEDURE After_DML (
409 p_action IN VARCHAR2,
410 x_rowid IN VARCHAR2
411 ) AS
412 BEGIN
413
414 l_rowid := x_rowid;
415
416
417 END After_DML;
418
419 procedure INSERT_ROW (
420 X_ROWID in out NOCOPY VARCHAR2,
421 X_UNIT_CD in VARCHAR2,
422 X_CAL_TYPE in VARCHAR2,
423 X_CI_SEQUENCE_NUMBER in NUMBER,
424 X_VERSION_NUMBER in NUMBER,
425 X_LOCATION_CD in VARCHAR2,
426 X_ORG_UNIT_CD in VARCHAR2,
427 X_HIST_START_DT in DATE,
428 X_OU_START_DT in DATE,
429 X_UNIT_CLASS in VARCHAR2,
430 X_HIST_END_DT in DATE,
431 X_HIST_WHO in NUMBER,
432 X_PERCENTAGE in NUMBER,
433 X_MODE in VARCHAR2 default 'R',
434 X_ORG_ID in NUMBER
435 ) as
436 cursor C is select ROWID from IGS_PS_TCH_RSOV_HIST_ALL
437 where UNIT_CD = X_UNIT_CD
438 and CAL_TYPE = X_CAL_TYPE
439 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
440 and VERSION_NUMBER = X_VERSION_NUMBER
441 and LOCATION_CD = X_LOCATION_CD
442 and ORG_UNIT_CD = X_ORG_UNIT_CD
443 and HIST_START_DT = X_HIST_START_DT
444 and OU_START_DT = X_OU_START_DT
445 and UNIT_CLASS = X_UNIT_CLASS;
446 X_LAST_UPDATE_DATE DATE;
447 X_LAST_UPDATED_BY NUMBER;
448 X_LAST_UPDATE_LOGIN NUMBER;
449 begin
450 X_LAST_UPDATE_DATE := SYSDATE;
451 if(X_MODE = 'I') then
452 X_LAST_UPDATED_BY := 1;
453 X_LAST_UPDATE_LOGIN := 0;
454 elsif (X_MODE = 'R') then
455 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
456 if X_LAST_UPDATED_BY is NULL then
457 X_LAST_UPDATED_BY := -1;
458 end if;
459 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
460 if X_LAST_UPDATE_LOGIN is NULL then
461 X_LAST_UPDATE_LOGIN := -1;
462 end if;
463 else
464 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
465 IGS_GE_MSG_STACK.ADD;
466 app_exception.raise_exception;
467 end if;
468
469 Before_DML(
470 p_action => 'INSERT',
471 x_rowid => X_ROWID,
472 x_unit_cd => X_UNIT_CD,
473 x_version_number => X_VERSION_NUMBER,
474 x_cal_type => X_CAL_TYPE,
475 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
476 x_location_cd => X_LOCATION_CD,
477 x_unit_class => X_UNIT_CLASS,
478 x_org_unit_cd => X_ORG_UNIT_CD,
479 x_hist_start_dt => X_HIST_START_DT,
480 x_ou_start_dt => X_OU_START_DT,
481 x_hist_end_dt => X_HIST_END_DT,
482 x_hist_who => X_HIST_WHO,
483 x_percentage => NVL(X_PERCENTAGE,100),
484 x_creation_date => X_LAST_UPDATE_DATE,
485 x_created_by => X_LAST_UPDATED_BY,
486 x_last_update_date => X_LAST_UPDATE_DATE,
487 x_last_updated_by => X_LAST_UPDATED_BY,
488 x_last_update_login => X_LAST_UPDATE_LOGIN,
489 x_org_id => igs_ge_gen_003.get_org_id
490 );
491
492 insert into IGS_PS_TCH_RSOV_HIST_ALL (
493 UNIT_CD,
494 VERSION_NUMBER,
495 CAL_TYPE,
496 CI_SEQUENCE_NUMBER,
497 LOCATION_CD,
498 UNIT_CLASS,
499 ORG_UNIT_CD,
500 OU_START_DT,
501 HIST_START_DT,
502 HIST_END_DT,
503 HIST_WHO,
504 PERCENTAGE,
505 CREATION_DATE,
506 CREATED_BY,
507 LAST_UPDATE_DATE,
508 LAST_UPDATED_BY,
509 LAST_UPDATE_LOGIN,
510 ORG_ID
511 ) values (
512 NEW_REFERENCES.UNIT_CD,
513 NEW_REFERENCES.VERSION_NUMBER,
514 NEW_REFERENCES.CAL_TYPE,
515 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
516 NEW_REFERENCES.LOCATION_CD,
517 NEW_REFERENCES.UNIT_CLASS,
518 NEW_REFERENCES.ORG_UNIT_CD,
519 NEW_REFERENCES.OU_START_DT,
520 NEW_REFERENCES.HIST_START_DT,
521 NEW_REFERENCES.HIST_END_DT,
522 NEW_REFERENCES.HIST_WHO,
523 NEW_REFERENCES.PERCENTAGE,
524 X_LAST_UPDATE_DATE,
525 X_LAST_UPDATED_BY,
526 X_LAST_UPDATE_DATE,
527 X_LAST_UPDATED_BY,
528 X_LAST_UPDATE_LOGIN,
529 NEW_REFERENCES.ORG_ID
530 );
531
532 open c;
533 fetch c into X_ROWID;
534 if (c%notfound) then
535 close c;
536 raise no_data_found;
537 end if;
538 close c;
539 After_DML (
540 p_action => 'INSERT',
541 x_rowid => X_ROWID
542 );
543
544 end INSERT_ROW;
545
546 procedure LOCK_ROW (
547 X_ROWID IN VARCHAR2,
548 X_UNIT_CD in VARCHAR2,
549 X_CAL_TYPE in VARCHAR2,
550 X_CI_SEQUENCE_NUMBER in NUMBER,
551 X_VERSION_NUMBER in NUMBER,
552 X_LOCATION_CD in VARCHAR2,
553 X_ORG_UNIT_CD in VARCHAR2,
554 X_HIST_START_DT in DATE,
555 X_OU_START_DT in DATE,
556 X_UNIT_CLASS in VARCHAR2,
557 X_HIST_END_DT in DATE,
558 X_HIST_WHO in NUMBER,
559 X_PERCENTAGE in NUMBER
560 ) as
561 cursor c1 is select
562 HIST_END_DT,
563 HIST_WHO,
564 PERCENTAGE
565 from IGS_PS_TCH_RSOV_HIST_ALL
566 where ROWID = X_ROWID
567 for update nowait;
568 tlinfo c1%rowtype;
569
570 begin
571 open c1;
572 fetch c1 into tlinfo;
573 if (c1%notfound) then
574 close c1;
575 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
576 IGS_GE_MSG_STACK.ADD;
577 app_exception.raise_exception;
578 return;
579 end if;
580 close c1;
581
582 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
583 AND (tlinfo.HIST_WHO = X_HIST_WHO)
584 AND (tlinfo.PERCENTAGE = X_PERCENTAGE)
585 ) then
586 null;
587 else
588 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
589 IGS_GE_MSG_STACK.ADD;
590 app_exception.raise_exception;
591 end if;
592 return;
593 end LOCK_ROW;
594
595 procedure UPDATE_ROW (
596 X_ROWID IN VARCHAR2,
597 X_UNIT_CD in VARCHAR2,
598 X_CAL_TYPE in VARCHAR2,
599 X_CI_SEQUENCE_NUMBER in NUMBER,
600 X_VERSION_NUMBER in NUMBER,
601 X_LOCATION_CD in VARCHAR2,
602 X_ORG_UNIT_CD in VARCHAR2,
603 X_HIST_START_DT in DATE,
604 X_OU_START_DT in DATE,
605 X_UNIT_CLASS in VARCHAR2,
606 X_HIST_END_DT in DATE,
607 X_HIST_WHO in NUMBER,
608 X_PERCENTAGE in NUMBER,
609 X_MODE in VARCHAR2 default 'R'
610 ) as
611 X_LAST_UPDATE_DATE DATE;
612 X_LAST_UPDATED_BY NUMBER;
613 X_LAST_UPDATE_LOGIN NUMBER;
614 begin
615 X_LAST_UPDATE_DATE := SYSDATE;
616 if(X_MODE = 'I') then
617 X_LAST_UPDATED_BY := 1;
618 X_LAST_UPDATE_LOGIN := 0;
619 elsif (X_MODE = 'R') then
620 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
621 if X_LAST_UPDATED_BY is NULL then
622 X_LAST_UPDATED_BY := -1;
623 end if;
624 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
625 if X_LAST_UPDATE_LOGIN is NULL then
626 X_LAST_UPDATE_LOGIN := -1;
627 end if;
628 else
629 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
630 IGS_GE_MSG_STACK.ADD;
631 app_exception.raise_exception;
632 end if;
633 Before_DML(
634 p_action => 'UPDATE',
635 x_rowid => X_ROWID,
636 x_unit_cd => X_UNIT_CD,
637 x_version_number => X_VERSION_NUMBER,
638 x_cal_type => X_CAL_TYPE,
639 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
640 x_location_cd => X_LOCATION_CD,
641 x_unit_class => X_UNIT_CLASS,
642 x_org_unit_cd => X_ORG_UNIT_CD,
643 x_hist_start_dt => X_HIST_START_DT,
644 x_ou_start_dt => X_OU_START_DT,
645 x_hist_end_dt => X_HIST_END_DT,
646 x_hist_who => X_HIST_WHO,
647 x_percentage => X_PERCENTAGE,
648 x_creation_date => X_LAST_UPDATE_DATE,
649 x_created_by => X_LAST_UPDATED_BY,
650 x_last_update_date => X_LAST_UPDATE_DATE,
651 x_last_updated_by => X_LAST_UPDATED_BY,
652 x_last_update_login => X_LAST_UPDATE_LOGIN
653 );
654
655 update IGS_PS_TCH_RSOV_HIST_ALL set
656 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
657 HIST_WHO = NEW_REFERENCES.HIST_WHO,
658 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
659 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
660 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
661 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
662 where ROWID = X_ROWID
663 ;
664 if (sql%notfound) then
665 raise no_data_found;
666 end if;
667 After_DML (
668 p_action => 'UPDATE',
669 x_rowid => X_ROWID
670 );
671
672 end UPDATE_ROW;
673
674 procedure ADD_ROW (
675 X_ROWID in out NOCOPY VARCHAR2,
676 X_UNIT_CD in VARCHAR2,
677 X_CAL_TYPE in VARCHAR2,
678 X_CI_SEQUENCE_NUMBER in NUMBER,
679 X_VERSION_NUMBER in NUMBER,
680 X_LOCATION_CD in VARCHAR2,
681 X_ORG_UNIT_CD in VARCHAR2,
682 X_HIST_START_DT in DATE,
683 X_OU_START_DT in DATE,
684 X_UNIT_CLASS in VARCHAR2,
685 X_HIST_END_DT in DATE,
686 X_HIST_WHO in NUMBER,
687 X_PERCENTAGE in NUMBER,
688 X_MODE in VARCHAR2 default 'R',
689 X_ORG_ID in NUMBER
690 ) as
691 cursor c1 is select rowid from IGS_PS_TCH_RSOV_HIST_ALL
692 where UNIT_CD = X_UNIT_CD
693 and CAL_TYPE = X_CAL_TYPE
694 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
695 and VERSION_NUMBER = X_VERSION_NUMBER
696 and LOCATION_CD = X_LOCATION_CD
697 and ORG_UNIT_CD = X_ORG_UNIT_CD
698 and HIST_START_DT = X_HIST_START_DT
699 and OU_START_DT = X_OU_START_DT
700 and UNIT_CLASS = X_UNIT_CLASS
701 ;
702
703 begin
704 open c1;
705 fetch c1 into X_ROWID;
706 if (c1%notfound) then
707 close c1;
708 INSERT_ROW (
709 X_ROWID,
710 X_UNIT_CD,
711 X_CAL_TYPE,
712 X_CI_SEQUENCE_NUMBER,
713 X_VERSION_NUMBER,
714 X_LOCATION_CD,
715 X_ORG_UNIT_CD,
716 X_HIST_START_DT,
717 X_OU_START_DT,
718 X_UNIT_CLASS,
719 X_HIST_END_DT,
720 X_HIST_WHO,
721 X_PERCENTAGE,
722 X_MODE,
723 X_ORG_ID);
724 return;
725 end if;
726 close c1;
727 UPDATE_ROW (
728 X_ROWID,
729 X_UNIT_CD,
730 X_CAL_TYPE,
731 X_CI_SEQUENCE_NUMBER,
732 X_VERSION_NUMBER,
733 X_LOCATION_CD,
734 X_ORG_UNIT_CD,
735 X_HIST_START_DT,
736 X_OU_START_DT,
737 X_UNIT_CLASS,
738 X_HIST_END_DT,
739 X_HIST_WHO,
740 X_PERCENTAGE,
741 X_MODE
742 );
743 end ADD_ROW;
744
745 procedure DELETE_ROW (
746 X_ROWID in VARCHAR2
747 ) as
748 begin
749 Before_DML(
750 p_action => 'DELETE',
751 x_rowid => X_ROWID
752 );
753 delete from IGS_PS_TCH_RSOV_HIST_ALL
754 where ROWID = X_ROWID;
755 if (sql%notfound) then
756 raise no_data_found;
757 end if;
758 After_DML(
759 p_action => 'DELETE',
760 x_rowid => X_ROWID
761 );
762
763 end DELETE_ROW;
764
765 end IGS_PS_TCH_RSOV_HIST_PKG;