[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GVT_SPSHT_CTL_PKG
Source
1 package body IGS_ST_GVT_SPSHT_CTL_PKG as
2 /* $Header: IGSVI08B.pls 115.6 2002/11/29 04:32:37 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_ST_GVT_SPSHT_CTL_ALL%RowType;
5 new_references IGS_ST_GVT_SPSHT_CTL_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_submission_yr IN NUMBER DEFAULT NULL,
11 x_submission_number IN NUMBER DEFAULT NULL,
12 x_ess_snapshot_dt_time IN DATE DEFAULT NULL,
13 x_completion_dt IN DATE DEFAULT NULL,
14 x_org_id IN NUMBER DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_ST_GVT_SPSHT_CTL_ALL
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37 IGS_GE_MSG_STACK.ADD;
38 App_Exception.Raise_Exception;
39 Close cur_old_ref_values;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.submission_yr := x_submission_yr;
46 new_references.submission_number := x_submission_number;
47 new_references.ess_snapshot_dt_time := x_ess_snapshot_dt_time;
48 new_references.completion_dt := x_completion_dt;
49 new_references.org_id := x_org_id;
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 AfterRowInsertUpdate1(
64 p_inserting IN BOOLEAN DEFAULT FALSE,
65 p_updating IN BOOLEAN DEFAULT FALSE,
66 p_deleting IN BOOLEAN DEFAULT FALSE
67 ) AS
68 v_message_name VARCHAR2(30);
69 BEGIN
70 IF NVL(old_references.ess_snapshot_dt_time, IGS_GE_DATE.igsdate('1900/01/01')) <>
71 NVL(new_references.ess_snapshot_dt_time,IGS_GE_DATE.igsdate('1900/01/01')) THEN
72
73 IF IGS_ST_VAL_GSC.stap_val_gsc_sdt (
74 new_references.submission_yr,
75 new_references.ess_snapshot_dt_time,
76 v_message_name) = FALSE THEN
77 FND_MESSAGE.SET_NAME('IGS',v_message_name);
78 IGS_GE_MSG_STACK.ADD;
79 APP_EXCEPTION.RAISE_EXCEPTION;
80 END IF;
81 IF IGS_ST_VAL_GSC.stap_val_gsc_sdt_upd (
82 new_references.submission_yr,
83 new_references.submission_number,
84 v_message_name) = FALSE THEN
85 FND_MESSAGE.SET_NAME('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 APP_EXCEPTION.RAISE_EXCEPTION;
88 END IF;
89 END IF;
90 END AfterRowInsertUpdate1;
91
92
93 PROCEDURE Check_Parent_Existance AS
94 BEGIN
95
96 IF (((old_references.ess_snapshot_dt_time = new_references.ess_snapshot_dt_time)) OR
97 ((new_references.ess_snapshot_dt_time IS NULL))) THEN
98 NULL;
99 ELSE
100 IF NOT IGS_EN_ST_SPSHT_CTL_PKG.Get_PK_For_Validation (
101 new_references.ess_snapshot_dt_time
102 )THEN
103 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
104 IGS_GE_MSG_STACK.ADD;
105 APP_EXCEPTION.RAISE_EXCEPTION;
106 END IF;
107 END IF;
108
109 END Check_Parent_Existance;
110
111 PROCEDURE Check_Child_Existance AS
112 BEGIN
113
114 IGS_ST_GOVT_SEMESTER_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
115 old_references.submission_yr,
116 old_references.submission_number
117 );
118
119 IGS_ST_GVT_SPSHT_CHG_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
120 old_references.submission_yr,
121 old_references.submission_number
122 );
123
124 IGS_ST_GOVT_STDNT_EN_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
125 old_references.submission_yr,
126 old_references.submission_number
127 );
128
129 IGS_AD_SBMAO_FN_AMTT_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
130 old_references.submission_yr,
131 old_references.submission_number
132 );
133
134 IGS_AD_SBMAO_FN_CTTT_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
135 old_references.submission_yr,
136 old_references.submission_number
137 );
138
139 IGS_AD_SBM_AOU_FNDTT_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
140 old_references.submission_yr,
141 old_references.submission_number
142 );
143
144 IGS_AD_SBMAO_FN_UITT_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
145 old_references.submission_yr,
146 old_references.submission_number
147 );
148
149 IGS_AD_SBM_PS_FNTRGT_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
150 old_references.submission_yr,
151 old_references.submission_number
152 );
153
154 IGS_AD_SBMINTAK_TRGT_PKG.GET_FK_IGS_ST_GVT_SPSHT_CTL (
155 old_references.submission_yr,
156 old_references.submission_number
157 );
158
159 END Check_Child_Existance;
160
161 FUNCTION Get_PK_For_Validation (
162 x_submission_yr IN NUMBER,
163 x_submission_number IN NUMBER
164 )
165 RETURN BOOLEAN AS
166
167 CURSOR cur_rowid IS
168 SELECT rowid
169 FROM IGS_ST_GVT_SPSHT_CTL_ALL
170 WHERE submission_yr = x_submission_yr
171 AND submission_number = x_submission_number
172 FOR UPDATE NOWAIT;
173
174 lv_rowid cur_rowid%RowType;
175
176 BEGIN
177
178 Open cur_rowid;
179 Fetch cur_rowid INTO lv_rowid;
180 IF (cur_rowid%FOUND) THEN
181 Close cur_rowid;
182 Return TRUE;
183 ELSE
184 Close cur_rowid;
185 Return FALSE;
186 END IF;
187
188 END Get_PK_For_Validation;
189
190 PROCEDURE GET_FK_IGS_EN_ST_SPSHT_CTL (
191 x_ess_snapshot_dt_time IN DATE
192 ) AS
193
194 CURSOR cur_rowid IS
195 SELECT rowid
196 FROM IGS_ST_GVT_SPSHT_CTL_ALL
197 WHERE ess_snapshot_dt_time = x_ess_snapshot_dt_time;
198
199 lv_rowid cur_rowid%RowType;
200
201 BEGIN
202
203 Open cur_rowid;
204 Fetch cur_rowid INTO lv_rowid;
205 IF (cur_rowid%FOUND) THEN
206 Close cur_rowid;
207 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSC_ESSC_FK');
208 IGS_GE_MSG_STACK.ADD;
209 App_Exception.Raise_Exception;
210 Return;
211 END IF;
212 Close cur_rowid;
213
214 END GET_FK_IGS_EN_ST_SPSHT_CTL;
215
216 -- procedure to check constraints
217 PROCEDURE CHECK_CONSTRAINTS(
218 column_name IN VARCHAR2 DEFAULT NULL,
219 column_value IN VARCHAR2 DEFAULT NULL
220 ) AS
221 BEGIN
222 IF column_name is null THEN
223 NULL;
224 ELSIF upper(column_name) = 'SUBMISSION_YR' THEN
225 new_references.submission_yr := IGS_GE_NUMBER.to_num(column_value);
226 ELSIF upper(column_name) = 'SUBMISSION_NUMBER' THEN
227 new_references.submission_number := IGS_GE_NUMBER.to_num(column_value);
228 END IF;
229
230 IF upper(column_name) = 'SUBMISSION_YR' OR column_name IS NULL THEN
231 IF new_references.submission_yr < 0 OR new_references.submission_yr > 9999 THEN
232 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
233 IGS_GE_MSG_STACK.ADD;
234 APP_EXCEPTION.RAISE_EXCEPTION;
235 END IF;
236 END IF;
237 IF upper(column_name) = 'SUBMISSION_NUMBER' OR column_name IS NULL THEN
238 IF new_references.submission_number < 1 OR new_references.submission_number > 3 THEN
239 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
240 IGS_GE_MSG_STACK.ADD;
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 END IF;
243 END IF;
244
245 END CHECK_CONSTRAINTS;
246
247
248 PROCEDURE Before_DML (
249 p_action IN VARCHAR2,
250 x_rowid IN VARCHAR2 DEFAULT NULL,
251 x_submission_yr IN NUMBER DEFAULT NULL,
252 x_submission_number IN NUMBER DEFAULT NULL,
253 x_ess_snapshot_dt_time IN DATE DEFAULT NULL,
254 x_completion_dt IN DATE DEFAULT NULL,
255 x_org_id IN NUMBER DEFAULT NULL,
256 x_creation_date IN DATE DEFAULT NULL,
257 x_created_by IN NUMBER DEFAULT NULL,
258 x_last_update_date IN DATE DEFAULT NULL,
259 x_last_updated_by IN NUMBER DEFAULT NULL,
260 x_last_update_login IN NUMBER DEFAULT NULL
261 ) AS
262 BEGIN
263
264 Set_Column_Values (
265 p_action,
266 x_rowid,
267 x_submission_yr,
268 x_submission_number,
269 x_ess_snapshot_dt_time,
270 x_completion_dt,
271 x_org_id,
272 x_creation_date,
273 x_created_by,
274 x_last_update_date,
275 x_last_updated_by,
276 x_last_update_login
277 );
278
279 IF (p_action = 'INSERT') THEN
280 -- Call all the procedures related to Before Insert.
281 Null;
282 --Check_Unique (x_rowid);
283 IF GET_PK_FOR_VALIDATION(
284 new_references.submission_yr,
285 new_references.submission_number
286 )THEN
287 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
288 IGS_GE_MSG_STACK.ADD;
289 APP_EXCEPTION.RAISE_EXCEPTION;
290 END IF;
291 Check_Constraints;
292 Check_Parent_Existance;
293 ELSIF (p_action = 'UPDATE') THEN
294 -- Call all the procedures related to Before Update.
295 Null;
296 --Check_Unique (x_rowid);
297 Check_Constraints;
298 Check_Parent_Existance;
299 ELSIF (p_action = 'DELETE') THEN
300 -- Call all the procedures related to Before Delete.
301 Null;
302 Check_Child_Existance;
303 ELSIF (p_action = 'VALIDATE_INSERT') THEN
304 -- Call all the procedures related to Before Delete.
305 IF GET_PK_FOR_VALIDATION(
306 new_references.submission_yr,
307 new_references.submission_number
308 )THEN
309 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
310 IGS_GE_MSG_STACK.ADD;
311 APP_EXCEPTION.RAISE_EXCEPTION;
312 END IF;
313 Check_Constraints;
314 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
315 -- Call all the procedures related to Before Delete.
316 check_constraints;
317 ELSIF (p_action = 'VALIDATE_DELETE') THEN
318 -- Call all the procedures related to Before Delete.
319 Check_Child_Existance;
320 END IF;
321
322 END Before_DML;
323
324 PROCEDURE After_DML (
325 p_action IN VARCHAR2,
326 x_rowid IN VARCHAR2
327 ) AS
328 BEGIN
329
330 l_rowid := x_rowid;
331
332 IF (p_action = 'INSERT') THEN
333 -- Call all the procedures related to After Insert.
334 AfterRowInsertUpdate1 ( p_inserting => TRUE );
335 ELSIF (p_action = 'UPDATE') THEN
336 -- Call all the procedures related to After Update.
337 AfterRowInsertUpdate1 ( p_updating => TRUE );
338 END IF;
339
340 END After_DML;
341
342
343 procedure INSERT_ROW (
344 X_ROWID in out NOCOPY VARCHAR2,
345 X_SUBMISSION_YR in NUMBER,
346 X_SUBMISSION_NUMBER in NUMBER,
347 X_ESS_SNAPSHOT_DT_TIME in DATE,
348 X_COMPLETION_DT in DATE,
349 x_org_id IN NUMBER,
350 X_MODE in VARCHAR2 default 'R'
351 ) AS
352 cursor C is select ROWID from IGS_ST_GVT_SPSHT_CTL_ALL
353 where SUBMISSION_YR = X_SUBMISSION_YR
354 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER;
355 X_LAST_UPDATE_DATE DATE;
356 X_LAST_UPDATED_BY NUMBER;
357 X_LAST_UPDATE_LOGIN NUMBER;
358 X_REQUEST_ID NUMBER;
359 X_PROGRAM_ID NUMBER;
360 X_PROGRAM_APPLICATION_ID NUMBER;
361 X_PROGRAM_UPDATE_DATE DATE;
362 begin
363 X_LAST_UPDATE_DATE := SYSDATE;
364 if(X_MODE = 'I') then
365 X_LAST_UPDATED_BY := 1;
366 X_LAST_UPDATE_LOGIN := 0;
367 elsif (X_MODE = 'R') then
368 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
369 if X_LAST_UPDATED_BY is NULL then
370 X_LAST_UPDATED_BY := -1;
371 end if;
372 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
373 if X_LAST_UPDATE_LOGIN is NULL then
374 X_LAST_UPDATE_LOGIN := -1;
375 end if;
376 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
377 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
378 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
379 if (X_REQUEST_ID = -1) then
380 X_REQUEST_ID := NULL;
381 X_PROGRAM_ID := NULL;
382 X_PROGRAM_APPLICATION_ID := NULL;
383 X_PROGRAM_UPDATE_DATE := NULL;
384 else
385 X_PROGRAM_UPDATE_DATE := SYSDATE;
386 end if;
387 else
388 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
389 IGS_GE_MSG_STACK.ADD;
390 app_exception.raise_exception;
391 end if;
392
393 Before_DML (
394 p_action =>'INSERT',
395 x_rowid =>X_ROWID,
396 x_submission_yr => X_SUBMISSION_YR,
397 x_submission_number => X_SUBMISSION_NUMBER,
398 x_ess_snapshot_dt_time => X_ESS_SNAPSHOT_DT_TIME,
399 x_completion_dt => X_COMPLETION_DT,
400 x_org_id => igs_ge_gen_003.get_org_id,
401 x_creation_date =>X_LAST_UPDATE_DATE,
402 x_created_by =>X_LAST_UPDATED_BY,
403 x_last_update_date =>X_LAST_UPDATE_DATE,
404 x_last_updated_by =>X_LAST_UPDATED_BY,
405 x_last_update_login =>X_LAST_UPDATE_LOGIN
406 );
407
408 insert into IGS_ST_GVT_SPSHT_CTL_ALL (
409 SUBMISSION_YR,
410 SUBMISSION_NUMBER,
411 ESS_SNAPSHOT_DT_TIME,
412 COMPLETION_DT,
413 ORG_ID,
414 CREATION_DATE,
415 CREATED_BY,
416 LAST_UPDATE_DATE,
417 LAST_UPDATED_BY,
418 LAST_UPDATE_LOGIN,
419 REQUEST_ID,
420 PROGRAM_ID,
421 PROGRAM_APPLICATION_ID,
422 PROGRAM_UPDATE_DATE
423 ) values (
424 NEW_REFERENCES.SUBMISSION_YR,
425 NEW_REFERENCES.SUBMISSION_NUMBER,
426 NEW_REFERENCES.ESS_SNAPSHOT_DT_TIME,
427 NEW_REFERENCES.COMPLETION_DT,
428 NEW_REFERENCES.ORG_ID,
429 X_LAST_UPDATE_DATE,
430 X_LAST_UPDATED_BY,
431 X_LAST_UPDATE_DATE,
432 X_LAST_UPDATED_BY,
433 X_LAST_UPDATE_LOGIN,
434 X_REQUEST_ID,
435 X_PROGRAM_ID,
436 X_PROGRAM_APPLICATION_ID,
437 X_PROGRAM_UPDATE_DATE
438 );
439
440 open c;
441 fetch c into X_ROWID;
442 if (c%notfound) then
443 close c;
444 raise no_data_found;
445 end if;
446 close c;
447
448 After_DML(
449 p_action =>'INSERT',
450 x_rowid => X_ROWID
451 );
452
453 end INSERT_ROW;
454
455 procedure LOCK_ROW (
456 X_ROWID in VARCHAR2,
457 X_SUBMISSION_YR in NUMBER,
458 X_SUBMISSION_NUMBER in NUMBER,
459 X_ESS_SNAPSHOT_DT_TIME in DATE,
460 X_COMPLETION_DT in DATE
461 ) AS
462 cursor c1 is select
463 ESS_SNAPSHOT_DT_TIME,
464 COMPLETION_DT
465 from IGS_ST_GVT_SPSHT_CTL_ALL
466 where ROWID = X_ROWID
467 for update nowait;
468 tlinfo c1%rowtype;
469
470 begin
471 open c1;
472 fetch c1 into tlinfo;
473 if (c1%notfound) then
474 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 close c1;
478 return;
479 end if;
480 close c1;
481
482 if ( ((tlinfo.ESS_SNAPSHOT_DT_TIME = X_ESS_SNAPSHOT_DT_TIME)
483 OR ((tlinfo.ESS_SNAPSHOT_DT_TIME is null)
484 AND (X_ESS_SNAPSHOT_DT_TIME is null)))
485 AND ((tlinfo.COMPLETION_DT = X_COMPLETION_DT)
486 OR ((tlinfo.COMPLETION_DT is null)
487 AND (X_COMPLETION_DT is null)))
488 ) then
489 null;
490 else
491 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
492 IGS_GE_MSG_STACK.ADD;
493 app_exception.raise_exception;
494 end if;
495 return;
496 end LOCK_ROW;
497
498 procedure UPDATE_ROW (
499 X_ROWID in VARCHAR2,
500 X_SUBMISSION_YR in NUMBER,
501 X_SUBMISSION_NUMBER in NUMBER,
502 X_ESS_SNAPSHOT_DT_TIME in DATE,
503 X_COMPLETION_DT in DATE,
504 X_MODE in VARCHAR2 default 'R'
505 ) AS
506 X_LAST_UPDATE_DATE DATE;
507 X_LAST_UPDATED_BY NUMBER;
508 X_LAST_UPDATE_LOGIN NUMBER;
509 X_REQUEST_ID NUMBER;
510 X_PROGRAM_ID NUMBER;
511 X_PROGRAM_APPLICATION_ID NUMBER;
512 X_PROGRAM_UPDATE_DATE DATE;
513 begin
514 X_LAST_UPDATE_DATE := SYSDATE;
515 if(X_MODE = 'I') then
516 X_LAST_UPDATED_BY := 1;
517 X_LAST_UPDATE_LOGIN := 0;
518 elsif (X_MODE = 'R') then
519 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
520 if X_LAST_UPDATED_BY is NULL then
521 X_LAST_UPDATED_BY := -1;
522 end if;
523 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
524 if X_LAST_UPDATE_LOGIN is NULL then
525 X_LAST_UPDATE_LOGIN := -1;
526 end if;
527 else
528 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
529 IGS_GE_MSG_STACK.ADD;
530 app_exception.raise_exception;
531 end if;
532
533 Before_DML (
534 p_action =>'UPDATE',
535 x_rowid =>X_ROWID,
536 x_submission_yr => X_SUBMISSION_YR,
537 x_submission_number => X_SUBMISSION_NUMBER,
538 x_ess_snapshot_dt_time => X_ESS_SNAPSHOT_DT_TIME,
539 x_completion_dt => X_COMPLETION_DT,
540 x_creation_date =>X_LAST_UPDATE_DATE,
541 x_created_by =>X_LAST_UPDATED_BY,
542 x_last_update_date =>X_LAST_UPDATE_DATE,
543 x_last_updated_by =>X_LAST_UPDATED_BY,
544 x_last_update_login =>X_LAST_UPDATE_LOGIN
545 );
546
547
548 if (X_MODE = 'R') then
549 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
550 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
551 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
552 if (X_REQUEST_ID = -1) then
553 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
554 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
555 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
556 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
557 else
558 X_PROGRAM_UPDATE_DATE := SYSDATE;
559 end if;
560 end if;
561 update IGS_ST_GVT_SPSHT_CTL_ALL set
562 ESS_SNAPSHOT_DT_TIME = NEW_REFERENCES.ESS_SNAPSHOT_DT_TIME,
563 COMPLETION_DT = NEW_REFERENCES.COMPLETION_DT,
564 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
565 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
566 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
567 REQUEST_ID = X_REQUEST_ID,
568 PROGRAM_ID = X_PROGRAM_ID,
569 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
570 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
571 where ROWID = X_ROWID
572 ;
573 if (sql%notfound) then
574 raise no_data_found;
575 end if;
576
577 After_DML(
578 p_action =>'UPDATE',
579 x_rowid => X_ROWID
580 );
581
582 end UPDATE_ROW;
583
584 procedure ADD_ROW (
585 X_ROWID in out NOCOPY VARCHAR2,
586 X_SUBMISSION_YR in NUMBER,
587 X_SUBMISSION_NUMBER in NUMBER,
588 X_ESS_SNAPSHOT_DT_TIME in DATE,
589 X_COMPLETION_DT in DATE,
590 X_ORG_ID in NUMBER,
591 X_MODE in VARCHAR2 default 'R'
592 ) AS
593 cursor c1 is select rowid from IGS_ST_GVT_SPSHT_CTL_ALL
594 where SUBMISSION_YR = X_SUBMISSION_YR
595 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
596 ;
597 begin
598 open c1;
599 fetch c1 into X_ROWID;
600 if (c1%notfound) then
601 close c1;
602 INSERT_ROW (
603 X_ROWID,
604 X_SUBMISSION_YR,
605 X_SUBMISSION_NUMBER,
606 X_ESS_SNAPSHOT_DT_TIME,
607 X_COMPLETION_DT,
608 X_ORG_ID,
609 X_MODE);
610 return;
611 end if;
612 close c1;
613 UPDATE_ROW (
614 X_ROWID,
615 X_SUBMISSION_YR,
616 X_SUBMISSION_NUMBER,
617 X_ESS_SNAPSHOT_DT_TIME,
618 X_COMPLETION_DT,
619 X_MODE);
620 end ADD_ROW;
621
622 procedure DELETE_ROW (
623 X_ROWID in VARCHAR2
624 ) AS
625 begin
626
627 Before_DML(
628 p_action =>'DELETE',
629 x_rowid => X_ROWID
630 );
631
632 delete from IGS_ST_GVT_SPSHT_CTL_ALL
633 where ROWID = X_ROWID;
634 if (sql%notfound) then
635 raise no_data_found;
636 end if;
637
638 After_DML(
639 p_action =>'DELETE',
640 x_rowid => X_ROWID
641 );
642
643 end DELETE_ROW;
644
645 end IGS_ST_GVT_SPSHT_CTL_PKG;