[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_TCH_RESP_PKG
Source
1 package body IGS_PS_TCH_RESP_PKG as
2 /* $Header: IGSPI69B.pls 120.0 2005/06/01 21:45:01 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_TCH_RESP%RowType;
5 new_references IGS_PS_TCH_RESP%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_unit_cd IN VARCHAR2 ,
11 x_version_number IN NUMBER ,
12 x_org_unit_cd IN VARCHAR2,
13 x_ou_start_dt IN DATE,
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_TCH_RESP
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.org_unit_cd := x_org_unit_cd;
48 new_references.ou_start_dt := x_ou_start_dt;
49 new_references.percentage := x_percentage;
50 IF (p_action = 'UPDATE') THEN
51 new_references.creation_date := old_references.creation_date;
52 new_references.created_by := old_references.created_by;
53 ELSE
54 new_references.creation_date := x_creation_date;
55 new_references.created_by := x_created_by;
56 END IF;
57 new_references.last_update_date := x_last_update_date;
58 new_references.last_updated_by := x_last_updated_by;
59 new_references.last_update_login := x_last_update_login;
60
61 END Set_Column_Values;
62
63 PROCEDURE Check_Child_Existance AS
64 /*
65 || Created By : [email protected]
66 || Created On : 12-JAN-2005
67 || Purpose : Checks for the existance of Child records.
68 || Known limitations, enhancements or remarks :
69 || Change History :
70 || Who When What
71 || (reverse chronological order - newest change first)
72 */
73 BEGIN
74
75 IGS_HE_UNT_OU_CC_PKG.get_fk_igs_ps_tch_resp (
76 old_references.unit_cd,
77 old_references.version_number,
78 old_references.org_unit_cd
79 );
80
81 END check_child_existance;
82
83 PROCEDURE BeforeRowInsertUpdateDelete1(
84 p_inserting IN BOOLEAN,
85 p_updating IN BOOLEAN,
86 p_deleting IN BOOLEAN
87 ) AS
88 v_unit_cd IGS_PS_TCH_RESP.unit_cd%TYPE;
89 v_version_number IGS_PS_TCH_RESP.version_number%TYPE;
90 v_percentage IGS_PS_TCH_RESP.percentage%TYPE;
91 v_message_name VARCHAR2(30);
92 BEGIN
93 -- Set variables.
94 IF p_deleting THEN
95 v_unit_cd := old_references.unit_cd;
96 v_version_number := old_references.version_number;
97 ELSE -- p_inserting or p_updating
98 v_unit_cd := new_references.unit_cd;
99 v_version_number := new_references.version_number;
100 END IF;
101 -- Validate the insert/update/delete.
102 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
103 v_unit_cd,
104 v_version_number,
105 v_message_name) = FALSE THEN
106 Fnd_Message.Set_Name('IGS', v_message_name);
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END IF;
110 -- Validate the org IGS_PS_UNIT. Org IGS_PS_UNIT is not updateable.
111 IF p_inserting THEN
112 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_TR.crsp_val_ou_sys_sts
113 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts (
114 new_references.org_unit_cd,
115 new_references.ou_start_dt,
116 v_message_name) = FALSE THEN
117 Fnd_Message.Set_Name('IGS', v_message_name);
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122 -- Create history record.
123 IF p_updating THEN
124 IF old_references.percentage <> new_references.percentage THEN
125 SELECT DECODE (old_references.percentage,new_references.percentage,NULL,old_references.percentage)
126 INTO v_percentage
127 FROM dual;
128 IGS_PS_GEN_005.CRSP_INS_TR_HIST(
129 old_references.unit_cd,
130 old_references.version_number,
131 old_references.org_unit_cd,
132 old_references.ou_start_dt,
133 old_references.last_update_date,
134 new_references.last_update_date,
135 old_references.last_updated_by,
136 v_percentage);
137 END IF;
138 END IF;
139 -- Create history record on delete
140 IF p_deleting THEN
141
142 -- Added for HE355 - Org Unit Cost Center Link to check for child existance before deleting record
143 Check_Child_Existance;
144
145 IGS_PS_GEN_005.CRSP_INS_TR_HIST(
146 old_references.unit_cd,
147 old_references.version_number,
148 old_references.org_unit_cd,
149 old_references.ou_start_dt,
150 old_references.last_update_date,
151 SYSDATE,
152 old_references.last_updated_by,
153 old_references.percentage);
154
155 END IF;
156
157
158 END BeforeRowInsertUpdateDelete1;
159
160 PROCEDURE Check_Constraints (
161 Column_Name IN VARCHAR2,
162 Column_Value IN VARCHAR2
163 )
164 AS
165 BEGIN
166
167 IF column_name is null then
168 NULL;
169 ELSIF upper(Column_name) = 'UNIT_CD' then
170 new_references.unit_cd:= column_value;
171 ELSIF upper(Column_name) = 'PERCENTAGE' then
172 new_references.percentage :=IGS_GE_NUMBER.TO_NUM(column_value);
173 END IF;
174
175 IF upper(column_name) = 'UNIT_CD' OR
176 column_name is null Then
177 IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182 END IF;
183
184 IF upper(column_name) = 'PERCENTAGE' OR
185 column_name is null Then
186 IF new_references.percentage <= 0 OR new_references.percentage > 100 Then
187 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192 END check_constraints;
193
194
195 PROCEDURE Check_Parent_Existance AS
196 BEGIN
197
198 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
199 (old_references.ou_start_dt = new_references.ou_start_dt)) OR
200 ((new_references.org_unit_cd IS NULL) OR
201 (new_references.ou_start_dt IS NULL))) THEN
202 NULL;
203 ELSE
204 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
205 new_references.org_unit_cd,
206 new_references.ou_start_dt
207 ) THEN
208 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213
214 IF (((old_references.unit_cd = new_references.unit_cd) AND
215 (old_references.version_number = new_references.version_number)) OR
216 ((new_references.unit_cd IS NULL) OR
217 (new_references.version_number IS NULL))) THEN
218 NULL;
219 ELSE
220 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
221 new_references.unit_cd,
222 new_references.version_number
223 ) THEN
224 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229 END Check_Parent_Existance;
230
231 FUNCTION Get_PK_For_Validation (
232 x_unit_cd IN VARCHAR2,
233 x_version_number IN NUMBER,
234 x_org_unit_cd IN VARCHAR2,
235 x_ou_start_dt IN DATE
236 ) RETURN BOOLEAN AS
237
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM IGS_PS_TCH_RESP
241 WHERE unit_cd = x_unit_cd
242 AND version_number = x_version_number
243 AND org_unit_cd = x_org_unit_cd
244 AND ou_start_dt = x_ou_start_dt
245 FOR UPDATE NOWAIT;
246
247 lv_rowid cur_rowid%RowType;
248
249 BEGIN
250
251 Open cur_rowid;
252 Fetch cur_rowid INTO lv_rowid;
253 IF (cur_rowid%FOUND) THEN
254 Close cur_rowid;
255 Return (TRUE);
256 ELSE
257 Close cur_rowid;
258 Return (FALSE);
259 END IF;
260 END Get_PK_For_Validation;
261
262 FUNCTION Get_PK_For_Validation (
263 x_unit_cd IN VARCHAR2,
264 x_version_number IN NUMBER,
265 x_org_unit_cd IN VARCHAR2
266 ) RETURN BOOLEAN AS
267 /*
268 || Created By : [email protected]
269 || Created On : 26-JAN-2005
270 || Purpose : Validates the Primary Keys of the table.
271 || Known limitations, enhancements or remarks :
272 || Change History :
273 || Who When What
274 || (reverse chronological order - newest change first)
275 */
276 CURSOR cur_rowid IS
277 SELECT rowid
278 FROM IGS_PS_TCH_RESP
279 WHERE unit_cd = x_unit_cd
280 AND version_number = x_version_number
281 AND org_unit_cd = x_org_unit_cd
282 FOR UPDATE NOWAIT;
283
284 lv_rowid cur_rowid%RowType;
285
286 BEGIN
287
288 Open cur_rowid;
289 Fetch cur_rowid INTO lv_rowid;
290 IF (cur_rowid%FOUND) THEN
291 Close cur_rowid;
292 Return (TRUE);
293 ELSE
294 Close cur_rowid;
295 Return (FALSE);
296 END IF;
297 END Get_PK_For_Validation;
298
299 PROCEDURE GET_FK_IGS_OR_UNIT (
300 x_org_unit_cd IN VARCHAR2,
301 x_start_dt IN VARCHAR2
302 ) AS
303
304 CURSOR cur_rowid IS
305 SELECT rowid
306 FROM IGS_PS_TCH_RESP
307 WHERE org_unit_cd = x_org_unit_cd
308 AND ou_start_dt = x_start_dt ;
309
310 lv_rowid cur_rowid%RowType;
311
312 BEGIN
313
314 Open cur_rowid;
315 Fetch cur_rowid INTO lv_rowid;
316 IF (cur_rowid%FOUND) THEN
317 Close cur_rowid;
318 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TR_OU_FK');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 Return;
322 END IF;
323 Close cur_rowid;
324
325 END GET_FK_IGS_OR_UNIT;
326
327 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
328 x_unit_cd IN VARCHAR2,
329 x_version_number IN NUMBER
330 ) AS
331
332 CURSOR cur_rowid IS
333 SELECT rowid
334 FROM IGS_PS_TCH_RESP
335 WHERE unit_cd = x_unit_cd
336 AND version_number = x_version_number ;
337
338 lv_rowid cur_rowid%RowType;
339
340 BEGIN
341
342 Open cur_rowid;
343 Fetch cur_rowid INTO lv_rowid;
344 IF (cur_rowid%FOUND) THEN
345 Close cur_rowid;
346 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TR_UV_FK');
347 IGS_GE_MSG_STACK.ADD;
348 App_Exception.Raise_Exception;
349 Return;
350 END IF;
351 Close cur_rowid;
352
353 END GET_FK_IGS_PS_UNIT_VER;
354
355 PROCEDURE Before_DML (
356 p_action IN VARCHAR2,
357 x_rowid IN VARCHAR2 ,
358 x_unit_cd IN VARCHAR2 ,
359 x_version_number IN NUMBER ,
360 x_org_unit_cd IN VARCHAR2 ,
361 x_ou_start_dt IN DATE ,
362 x_percentage IN NUMBER ,
363 x_creation_date IN DATE ,
364 x_created_by IN NUMBER ,
365 x_last_update_date IN DATE ,
366 x_last_updated_by IN NUMBER ,
367 x_last_update_login IN NUMBER
368 ) AS
369 BEGIN
370
371 Set_Column_Values (
372 p_action,
373 x_rowid,
374 x_unit_cd,
375 x_version_number,
376 x_org_unit_cd,
377 x_ou_start_dt,
378 x_percentage,
379 x_creation_date,
380 x_created_by,
381 x_last_update_date,
382 x_last_updated_by,
383 x_last_update_login
384 );
385
386 IF (p_action = 'INSERT') THEN
387 -- Call all the procedures related to Before Insert.
388 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE);
389 IF Get_PK_For_Validation (
390 new_references.unit_cd,
391 new_references.version_number,
392 new_references.org_unit_cd,
393 new_references.ou_start_dt
394 ) THEN
395 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398 END IF;
399 Check_Constraints;
400 Check_Parent_Existance;
401 ELSIF (p_action = 'UPDATE') THEN
402 -- Call all the procedures related to Before Update.
403 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE );
404 Check_Constraints;
405 Check_Parent_Existance;
406 ELSIF (p_action = 'DELETE') THEN
407 -- Call all the procedures related to Before Delete.
408 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE );
409 ELSIF (p_action = 'VALIDATE_INSERT') THEN
410 IF Get_PK_For_Validation (
411 new_references.unit_cd,
412 new_references.version_number,
413 new_references.org_unit_cd,
414 new_references.ou_start_dt
415 ) THEN
416 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception;
419 END IF;
420 Check_Constraints;
421 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
422 Check_Constraints;
423 ELSIF (p_action = 'VALIDATE_DELETE') THEN
424 -- Added for HE355 - Org Unit Cost Center Link to check for child existance before deleting record
425 Check_Child_Existance;
426 END IF;
427
428 END Before_DML;
429
430 PROCEDURE After_DML (
431 p_action IN VARCHAR2,
432 x_rowid IN VARCHAR2
433 ) AS
434 BEGIN
435
436 l_rowid := x_rowid;
437
438
439 END After_DML;
440
441 procedure INSERT_ROW (
442 X_ROWID in out NOCOPY VARCHAR2,
443 X_UNIT_CD in VARCHAR2,
444 X_VERSION_NUMBER in NUMBER,
445 X_OU_START_DT in DATE,
446 X_ORG_UNIT_CD in VARCHAR2,
447 X_PERCENTAGE in NUMBER,
448 X_MODE in VARCHAR2
449 ) as
450 cursor C is select ROWID from IGS_PS_TCH_RESP
451 where UNIT_CD = X_UNIT_CD
452 and VERSION_NUMBER = X_VERSION_NUMBER
453 and OU_START_DT = X_OU_START_DT
454 and ORG_UNIT_CD = X_ORG_UNIT_CD;
455 X_LAST_UPDATE_DATE DATE;
456 X_LAST_UPDATED_BY NUMBER;
457 X_LAST_UPDATE_LOGIN NUMBER;
458 begin
459 X_LAST_UPDATE_DATE := SYSDATE;
460 if(X_MODE = 'I') then
461 X_LAST_UPDATED_BY := 1;
462 X_LAST_UPDATE_LOGIN := 0;
463 elsif (X_MODE = 'R') then
464 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
465 if X_LAST_UPDATED_BY is NULL then
466 X_LAST_UPDATED_BY := -1;
467 end if;
468 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
469 if X_LAST_UPDATE_LOGIN is NULL then
470 X_LAST_UPDATE_LOGIN := -1;
471 end if;
472 else
473 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
474 IGS_GE_MSG_STACK.ADD;
475 app_exception.raise_exception;
476 end if;
477
478 Before_DML (
479 p_action => 'INSERT',
480 x_rowid => X_ROWID,
481 x_unit_cd => X_UNIT_CD,
482 x_version_number => X_VERSION_NUMBER,
483 x_org_unit_cd => X_ORG_UNIT_CD,
484 x_ou_start_dt => X_OU_START_DT,
485 x_percentage => X_PERCENTAGE,
486 x_creation_date => X_LAST_UPDATE_DATE,
487 x_created_by => X_LAST_UPDATED_BY,
488 x_last_update_date => X_LAST_UPDATE_DATE,
489 x_last_updated_by => X_LAST_UPDATED_BY,
490 x_last_update_login => X_LAST_UPDATE_LOGIN
491 );
492
493 insert into IGS_PS_TCH_RESP (
494 UNIT_CD,
495 VERSION_NUMBER,
496 ORG_UNIT_CD,
497 OU_START_DT,
498 PERCENTAGE,
499 CREATION_DATE,
500 CREATED_BY,
501 LAST_UPDATE_DATE,
502 LAST_UPDATED_BY,
503 LAST_UPDATE_LOGIN
504 ) values (
505 NEW_REFERENCES.UNIT_CD,
506 NEW_REFERENCES.VERSION_NUMBER,
507 NEW_REFERENCES.ORG_UNIT_CD,
508 NEW_REFERENCES.OU_START_DT,
509 NEW_REFERENCES.PERCENTAGE,
510 X_LAST_UPDATE_DATE,
511 X_LAST_UPDATED_BY,
512 X_LAST_UPDATE_DATE,
513 X_LAST_UPDATED_BY,
514 X_LAST_UPDATE_LOGIN
515 );
516
517 open c;
518 fetch c into X_ROWID;
519 if (c%notfound) then
520 close c;
521 raise no_data_found;
522 end if;
523 close c;
524 After_DML (
525 p_action => 'INSERT',
526 x_rowid => X_ROWID
527 );
528 end INSERT_ROW;
529
530 procedure LOCK_ROW (
531 X_ROWID IN VARCHAR2,
532 X_UNIT_CD in VARCHAR2,
533 X_VERSION_NUMBER in NUMBER,
534 X_OU_START_DT in DATE,
535 X_ORG_UNIT_CD in VARCHAR2,
536 X_PERCENTAGE in NUMBER
537 ) as
538 cursor c1 is select
539 PERCENTAGE
540 from IGS_PS_TCH_RESP
541 where ROWID = X_ROWID
542 for update nowait;
543 tlinfo c1%rowtype;
544
545 begin
546 open c1;
547 fetch c1 into tlinfo;
548 if (c1%notfound) then
549 close c1;
550 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
551 IGS_GE_MSG_STACK.ADD;
552 app_exception.raise_exception;
553 return;
554 end if;
555 close c1;
556
557 if ( (tlinfo.PERCENTAGE = X_PERCENTAGE)
558 ) then
559 null;
560 else
561 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
562 IGS_GE_MSG_STACK.ADD;
563 app_exception.raise_exception;
564 end if;
565 return;
566 end LOCK_ROW;
567
568 procedure UPDATE_ROW (
569 X_ROWID IN VARCHAR2,
570 X_UNIT_CD in VARCHAR2,
571 X_VERSION_NUMBER in NUMBER,
572 X_OU_START_DT in DATE,
573 X_ORG_UNIT_CD in VARCHAR2,
574 X_PERCENTAGE in NUMBER,
575 X_MODE in VARCHAR2
576 ) as
577 X_LAST_UPDATE_DATE DATE;
578 X_LAST_UPDATED_BY NUMBER;
579 X_LAST_UPDATE_LOGIN NUMBER;
580 begin
581 X_LAST_UPDATE_DATE := SYSDATE;
582 if(X_MODE = 'I') then
583 X_LAST_UPDATED_BY := 1;
584 X_LAST_UPDATE_LOGIN := 0;
585 elsif (X_MODE = 'R') then
586 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
587 if X_LAST_UPDATED_BY is NULL then
588 X_LAST_UPDATED_BY := -1;
589 end if;
590 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
591 if X_LAST_UPDATE_LOGIN is NULL then
592 X_LAST_UPDATE_LOGIN := -1;
593 end if;
594 else
595 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
596 IGS_GE_MSG_STACK.ADD;
597 app_exception.raise_exception;
598 end if;
599 Before_DML (
600 p_action => 'UPDATE',
601 x_rowid => X_ROWID,
602 x_unit_cd => X_UNIT_CD,
603 x_version_number => X_VERSION_NUMBER,
604 x_org_unit_cd => X_ORG_UNIT_CD,
605 x_ou_start_dt => X_OU_START_DT,
606 x_percentage => X_PERCENTAGE,
607 x_creation_date => X_LAST_UPDATE_DATE,
608 x_created_by => X_LAST_UPDATED_BY,
609 x_last_update_date => X_LAST_UPDATE_DATE,
610 x_last_updated_by => X_LAST_UPDATED_BY,
611 x_last_update_login => X_LAST_UPDATE_LOGIN
612 );
613
614 update IGS_PS_TCH_RESP set
615 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
616 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
617 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
618 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
619 where ROWID = X_ROWID
620 ;
621 if (sql%notfound) then
622 raise no_data_found;
623 end if;
624 After_DML (
625 p_action => 'UPDATE',
626 x_rowid => X_ROWID
627 );
628 end UPDATE_ROW;
629
630 procedure ADD_ROW (
631 X_ROWID in out NOCOPY VARCHAR2,
632 X_UNIT_CD in VARCHAR2,
633 X_VERSION_NUMBER in NUMBER,
634 X_OU_START_DT in DATE,
635 X_ORG_UNIT_CD in VARCHAR2,
636 X_PERCENTAGE in NUMBER,
637 X_MODE in VARCHAR2
638 ) as
639 cursor c1 is select rowid from IGS_PS_TCH_RESP
640 where UNIT_CD = X_UNIT_CD
641 and VERSION_NUMBER = X_VERSION_NUMBER
642 and OU_START_DT = X_OU_START_DT
643 and ORG_UNIT_CD = X_ORG_UNIT_CD
644 ;
645 begin
646 open c1;
647 fetch c1 into X_ROWID;
648 if (c1%notfound) then
649 close c1;
650 INSERT_ROW (
651 X_ROWID,
652 X_UNIT_CD,
653 X_VERSION_NUMBER,
654 X_OU_START_DT,
655 X_ORG_UNIT_CD,
656 X_PERCENTAGE,
657 X_MODE);
658 return;
659 end if;
660 close c1;
661 UPDATE_ROW (
662 X_ROWID,
663 X_UNIT_CD,
664 X_VERSION_NUMBER,
665 X_OU_START_DT,
666 X_ORG_UNIT_CD,
667 X_PERCENTAGE,
668 X_MODE);
669 end ADD_ROW;
670
671 procedure DELETE_ROW (
672 X_ROWID in VARCHAR2
673 ) as
674 begin
675 Before_DML(
676 p_action => 'DELETE',
677 x_rowid => X_ROWID
678 );
679 delete from IGS_PS_TCH_RESP
680 where ROWID = X_ROWID;
681 if (sql%notfound) then
682 raise no_data_found;
683 end if;
684 After_DML(
685 p_action => 'DELETE',
686 x_rowid => X_ROWID
687 );
688 end DELETE_ROW;
689
690 end IGS_PS_TCH_RESP_PKG;