[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_ADM_UNIT_STAT_PKG
Source
1 package body IGS_AD_ADM_UNIT_STAT_PKG AS
2 /* $Header: IGSAI01B.pls 115.12 2003/10/30 13:09:45 akadam ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_ADM_UNIT_STAT_ALL%RowType;
5 new_references IGS_AD_ADM_UNIT_STAT_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
12 x_unit_attempt_status IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_show_on_offic_ntfctn_ind IN VARCHAR2 DEFAULT NULL,
15 x_effective_progression_ind IN VARCHAR2 DEFAULT NULL,
16 x_effective_time_elapsed_ind IN VARCHAR2 DEFAULT NULL,
17 x_closed_ind IN VARCHAR2 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 ) AS
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_AD_ADM_UNIT_STAT_ALL
27 WHERE rowid = x_rowid;
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.org_id := x_org_id;
47 new_references.administrative_unit_status := x_administrative_unit_status;
48 new_references.unit_attempt_status := x_unit_attempt_status;
49 new_references.description := x_description;
50 new_references.show_on_offic_ntfctn_ind := x_show_on_offic_ntfctn_ind;
51 new_references.effective_progression_ind := x_effective_progression_ind;
52 new_references.effective_time_elapsed_ind := x_effective_time_elapsed_ind;
53 new_references.closed_ind := x_closed_ind;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66
67 PROCEDURE BeforeRowInsertUpdate1(
68 p_inserting IN BOOLEAN DEFAULT FALSE,
69 p_updating IN BOOLEAN DEFAULT FALSE,
70 p_deleting IN BOOLEAN DEFAULT FALSE
71 ) AS
72 v_message_name VARCHAR2(30);
73 BEGIN
74 -- Validate that inserts/updates are allowed
75 IF p_inserting OR p_updating THEN
76 IF new_references.unit_attempt_status <> 'DISCONTIN' THEN
77 IF IGS_EN_VAL_AUS.ENRP_VAL_AUS_AUSG(new_references.administrative_unit_status
78 ,v_message_name) = FALSE THEN
79 Fnd_Message.Set_Name('IGS',v_message_name);
80 IGS_GE_MSG_STACK.ADD;
81 App_Exception.Raise_Exception;
82 END IF;
83 END IF;
84 END IF;
85
86
87 END BeforeRowInsertUpdate1;
88
89 PROCEDURE Check_Parent_Existance AS
90 BEGIN
91
92 IF (((old_references.unit_attempt_status = new_references.unit_attempt_status)) OR
93 ((new_references.unit_attempt_status IS NULL))) THEN
94 NULL;
95 ELSE
96 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
97 'UNIT_ATTEMPT_STATUS', new_references.unit_attempt_status ) THEN
98 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
99 IGS_GE_MSG_STACK.ADD;
100 App_Exception.Raise_Exception;
101 END IF;
102 END IF;
103
104 END Check_Parent_Existance;
105
106 PROCEDURE Check_Constraints (
107 Column_Name IN VARCHAR2 DEFAULT NULL,
108 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
109
110 BEGIN
111
112 IF Column_Name IS NULL THEN
113 NULL;
114 ELSIF upper(Column_Name) = 'EFFECTIVE_PROGRESSION_IND' THEN
115 new_references.effective_progression_ind := column_value ;
116 ELSIF upper(Column_Name) = 'CLOSED_IND' THEN
117 new_references.closed_ind := column_value ;
118 ELSIF upper(Column_Name) = 'ADMINISTRATIVE_UNIT_STATUS' THEN
119 new_references.administrative_unit_status := column_value ;
120 ELSIF upper(Column_Name) = 'EFFECTIVE_TIME_ELAPSED_IND' THEN
121 new_references.effective_time_elapsed_ind := column_value ;
122 ELSIF upper(Column_Name) = 'SHOW_ON_OFFIC_NTFCTN_IND' THEN
123 new_references.show_on_offic_ntfctn_ind := column_value ;
124 ELSIF upper(Column_Name) = 'UNIT_ATTEMPT_STATUS' THEN
125 new_references.unit_attempt_status := column_value ;
126 END IF;
127
128 IF upper(column_name) = 'EFFECTIVE_PROGRESSION_IND' OR
129 column_name IS NULL THEN
130 IF new_references.effective_progression_ind NOT IN ( 'Y' , 'N' ) THEN
131 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136 IF upper(column_name) = 'CLOSED_IND' OR
137 column_name IS NULL THEN
138 IF new_references.closed_ind NOT IN ( 'Y' , 'N' ) THEN
139 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
140 IGS_GE_MSG_STACK.ADD;
141 App_Exception.Raise_Exception;
142 END IF;
143 END IF;
144 IF upper(column_name) = 'ADMINISTRATIVE_UNIT_STATUS' OR
145 column_name IS NULL THEN
146 IF new_references.administrative_unit_status <> UPPER(new_references.administrative_unit_status) THEN
147 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152
153 IF upper(column_name) = 'EFFECTIVE_TIME_ELAPSED_IND' OR
154 column_name IS NULL THEN
155 IF new_references.effective_time_elapsed_ind NOT IN ( 'Y' , 'N' ) THEN
156 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161 IF upper(column_Name) = 'SHOW_ON_OFFIC_NTFCTN_IND' OR
162 column_name IS NULL THEN
163 IF new_references.show_on_offic_ntfctn_ind NOT IN ( 'Y' , 'N' ) THEN
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169 IF upper(Column_Name) = 'UNIT_ATTEMPT_STATUS' OR
170 column_name IS NULL THEN
171 IF new_references.unit_attempt_status <> upper(new_references.unit_attempt_status) THEN
172 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177
178 END Check_Constraints;
179
180 FUNCTION Get_PK_For_Validation (
181 x_administrative_unit_status IN VARCHAR2,
182 x_closed_ind IN VARCHAR2
183 ) RETURN BOOLEAN AS
184
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM IGS_AD_ADM_UNIT_STAT_ALL
188 WHERE administrative_unit_status = x_administrative_unit_status AND
189 closed_ind = NVL(x_closed_ind,closed_ind);
190
191
192 lv_rowid cur_rowid%RowType;
193
194 BEGIN
195
196 Open cur_rowid;
197 Fetch cur_rowid INTO lv_rowid;
198
199 IF (cur_rowid%FOUND) THEN
200 Close cur_rowid;
201 Return True;
202 ELSE
203 Close cur_rowid;
204 Return False;
205 END IF;
206
207 END Get_PK_For_Validation;
208
209 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
210 x_unit_attempt_status IN VARCHAR2
211 ) AS
212
213 CURSOR cur_rowid IS
214 SELECT rowid
215 FROM IGS_AD_ADM_UNIT_STAT_ALL
216 WHERE unit_attempt_status = x_unit_attempt_status ;
217
218 lv_rowid cur_rowid%RowType;
219
220 BEGIN
221
222 Open cur_rowid;
223 Fetch cur_rowid INTO lv_rowid;
224 IF (cur_rowid%FOUND) THEN
225 Close cur_rowid;
226 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUS_LKUPV_FK');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 Return;
230 END IF;
231 Close cur_rowid;
232
233 END GET_FK_IGS_LOOKUPS_VIEW;
234
235 PROCEDURE Before_DML (
236 p_action IN VARCHAR2,
237 x_rowid IN VARCHAR2 DEFAULT NULL,
238 x_org_id IN NUMBER DEFAULT NULL,
239 x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
240 x_unit_attempt_status IN VARCHAR2 DEFAULT NULL,
241 x_description IN VARCHAR2 DEFAULT NULL,
242 x_show_on_offic_ntfctn_ind IN VARCHAR2 DEFAULT NULL,
243 x_effective_progression_ind IN VARCHAR2 DEFAULT NULL,
244 x_effective_time_elapsed_ind IN VARCHAR2 DEFAULT NULL,
245 x_closed_ind IN VARCHAR2 DEFAULT NULL,
246 x_creation_date IN DATE DEFAULT NULL,
247 x_created_by IN NUMBER DEFAULT NULL,
248 x_last_update_date IN DATE DEFAULT NULL,
249 x_last_updated_by IN NUMBER DEFAULT NULL,
250 x_last_update_login IN NUMBER DEFAULT NULL
251 ) AS
252 BEGIN
253
254 Set_Column_Values (
255 p_action,
256 x_rowid,
257 x_org_id,
258 x_administrative_unit_status,
259 x_unit_attempt_status,
260 x_description,
261 x_show_on_offic_ntfctn_ind,
262 x_effective_progression_ind,
263 x_effective_time_elapsed_ind,
264 x_closed_ind,
265 x_creation_date,
266 x_created_by,
267 x_last_update_date,
268 x_last_updated_by,
269 x_last_update_login
270 );
271
272 IF (p_action = 'INSERT') THEN
273 -- Call all the procedures related to Before Insert.
274 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
275 IF Get_PK_For_Validation (new_references.administrative_unit_status ) THEN
276 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280 Check_Constraints ;
281 Check_Parent_Existance;
282 ELSIF (p_action = 'UPDATE') THEN
283 -- Call all the procedures related to Before Update.
284 BeforeRowInsertUpdate1 ( p_updating => TRUE );
285 Check_Constraints;
286 Check_Parent_Existance;
287 ELSIF (p_action = 'VALIDATE_INSERT') THEN
288 IF Get_PK_For_Validation (new_references.administrative_unit_status ) 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 END After_DML;
308
309 procedure INSERT_ROW (
310 X_ROWID in out NOCOPY VARCHAR2,
311 X_ORG_ID in NUMBER,
312 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
313 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
314 X_DESCRIPTION in VARCHAR2,
315 X_SHOW_ON_OFFIC_NTFCTN_IND in VARCHAR2,
316 X_EFFECTIVE_PROGRESSION_IND in VARCHAR2,
317 X_EFFECTIVE_TIME_ELAPSED_IND in VARCHAR2,
318 X_CLOSED_IND in VARCHAR2,
319 X_MODE in VARCHAR2 default 'R'
320 ) AS
321 cursor C is select ROWID from IGS_AD_ADM_UNIT_STAT_ALL
322 where ADMINISTRATIVE_UNIT_STATUS = X_ADMINISTRATIVE_UNIT_STATUS;
323 X_LAST_UPDATE_DATE DATE;
324 X_LAST_UPDATED_BY NUMBER;
325 X_LAST_UPDATE_LOGIN NUMBER;
326 begin
327 X_LAST_UPDATE_DATE := SYSDATE;
328 if(X_MODE = 'I') then
329 X_LAST_UPDATED_BY := 1;
330 X_LAST_UPDATE_LOGIN := 0;
331 elsif (X_MODE = 'R') then
332 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
333 if X_LAST_UPDATED_BY is NULL then
334 X_LAST_UPDATED_BY := -1;
335 end if;
336 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
337 if X_LAST_UPDATE_LOGIN is NULL then
338 X_LAST_UPDATE_LOGIN := -1;
339 end if;
340 else
341 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
342 IGS_GE_MSG_STACK.ADD;
343 app_exception.raise_exception;
344 end if;
345
346 Before_DML (
347 p_action => 'INSERT',
348 x_rowid => X_ROWID,
349 x_org_id => igs_ge_gen_003.get_org_id,
350 x_administrative_unit_status => X_ADMINISTRATIVE_UNIT_STATUS,
351 x_unit_attempt_status => X_UNIT_ATTEMPT_STATUS,
352 x_description => X_DESCRIPTION,
353 x_show_on_offic_ntfctn_ind => Nvl(X_SHOW_ON_OFFIC_NTFCTN_IND, 'Y'),
354 x_effective_progression_ind => Nvl(X_EFFECTIVE_PROGRESSION_IND, 'Y'),
355 x_effective_time_elapsed_ind => Nvl(X_EFFECTIVE_TIME_ELAPSED_IND, 'Y'),
356 x_closed_ind => Nvl(X_CLOSED_IND, 'N'),
357 x_creation_date => X_LAST_UPDATE_DATE,
358 x_created_by => X_LAST_UPDATED_BY,
359 x_last_update_date =>X_LAST_UPDATE_DATE,
360 x_last_updated_by =>X_LAST_UPDATED_BY,
361 x_last_update_login =>X_LAST_UPDATE_LOGIN
362 );
363
364 insert into IGS_AD_ADM_UNIT_STAT_ALL (
365 ORG_ID,
366 ADMINISTRATIVE_UNIT_STATUS,
367 UNIT_ATTEMPT_STATUS,
368 DESCRIPTION,
369 SHOW_ON_OFFIC_NTFCTN_IND,
370 EFFECTIVE_PROGRESSION_IND,
371 EFFECTIVE_TIME_ELAPSED_IND,
372 CLOSED_IND,
373 CREATION_DATE,
374 CREATED_BY,
375 LAST_UPDATE_DATE,
376 LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN
378 ) values (
379 NEW_REFERENCES.ORG_ID,
380 NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
381 NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
382 NEW_REFERENCES.DESCRIPTION,
383 NEW_REFERENCES.SHOW_ON_OFFIC_NTFCTN_IND,
384 NEW_REFERENCES.EFFECTIVE_PROGRESSION_IND,
385 NEW_REFERENCES.EFFECTIVE_TIME_ELAPSED_IND,
386 NEW_REFERENCES.CLOSED_IND,
387 X_LAST_UPDATE_DATE,
388 X_LAST_UPDATED_BY,
389 X_LAST_UPDATE_DATE,
390 X_LAST_UPDATED_BY,
391 X_LAST_UPDATE_LOGIN
392 );
393
394 open c;
395 fetch c into X_ROWID;
396 if (c%notfound) then
397 close c;
398 raise no_data_found;
399 end if;
400 close c;
401 After_DML (
402 p_action => 'INSERT',
403 x_rowid => X_ROWID
404 );
405 end INSERT_ROW;
406
407 procedure LOCK_ROW (
408 X_ROWID in VARCHAR2,
409 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
410 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
411 X_DESCRIPTION in VARCHAR2,
412 X_SHOW_ON_OFFIC_NTFCTN_IND in VARCHAR2,
416 ) AS
413 X_EFFECTIVE_PROGRESSION_IND in VARCHAR2,
414 X_EFFECTIVE_TIME_ELAPSED_IND in VARCHAR2,
415 X_CLOSED_IND in VARCHAR2
417 cursor c1 is select
418 UNIT_ATTEMPT_STATUS,
419 DESCRIPTION,
420 SHOW_ON_OFFIC_NTFCTN_IND,
421 EFFECTIVE_PROGRESSION_IND,
422 EFFECTIVE_TIME_ELAPSED_IND,
423 CLOSED_IND
424 from IGS_AD_ADM_UNIT_STAT_ALL
425 where ROWID = X_ROWID
426 for update nowait;
427 tlinfo c1%rowtype;
428
429 begin
430 open c1;
431 fetch c1 into tlinfo;
432 if (c1%notfound) then
433 close c1;
434 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
435 app_exception.raise_exception;
436 return;
437 end if;
438 close c1;
439
440 if ( (tlinfo.UNIT_ATTEMPT_STATUS = X_UNIT_ATTEMPT_STATUS)
441 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
442 AND (tlinfo.SHOW_ON_OFFIC_NTFCTN_IND = X_SHOW_ON_OFFIC_NTFCTN_IND)
443 AND (tlinfo.EFFECTIVE_PROGRESSION_IND = X_EFFECTIVE_PROGRESSION_IND)
444 AND (tlinfo.EFFECTIVE_TIME_ELAPSED_IND = X_EFFECTIVE_TIME_ELAPSED_IND)
445 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
446 ) then
447 null;
448 else
449 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
450 app_exception.raise_exception;
451 end if;
452 return;
453 end LOCK_ROW;
454
455 procedure UPDATE_ROW (
456 X_ROWID in VARCHAR2,
457 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
458 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
459 X_DESCRIPTION in VARCHAR2,
460 X_SHOW_ON_OFFIC_NTFCTN_IND in VARCHAR2,
461 X_EFFECTIVE_PROGRESSION_IND in VARCHAR2,
462 X_EFFECTIVE_TIME_ELAPSED_IND in VARCHAR2,
463 X_CLOSED_IND in VARCHAR2,
464 X_MODE in VARCHAR2 default 'R'
465 ) AS
466 X_LAST_UPDATE_DATE DATE;
467 X_LAST_UPDATED_BY NUMBER;
468 X_LAST_UPDATE_LOGIN NUMBER;
469 begin
470 X_LAST_UPDATE_DATE := SYSDATE;
471 if(X_MODE = 'I') then
472 X_LAST_UPDATED_BY := 1;
473 X_LAST_UPDATE_LOGIN := 0;
474 elsif (X_MODE = 'R') then
475 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
476 if X_LAST_UPDATED_BY is NULL then
477 X_LAST_UPDATED_BY := -1;
478 end if;
479 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
480 if X_LAST_UPDATE_LOGIN is NULL then
481 X_LAST_UPDATE_LOGIN := -1;
482 end if;
483 else
484 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
485 IGS_GE_MSG_STACK.ADD;
486 app_exception.raise_exception;
487 end if;
488
489 Before_DML (
490 p_action => 'UPDATE',
491 x_rowid => X_ROWID,
492 x_administrative_unit_status => X_ADMINISTRATIVE_UNIT_STATUS,
493 x_unit_attempt_status => X_UNIT_ATTEMPT_STATUS,
494 x_description => X_DESCRIPTION,
495 x_show_on_offic_ntfctn_ind => X_SHOW_ON_OFFIC_NTFCTN_IND,
496 x_effective_progression_ind => X_EFFECTIVE_PROGRESSION_IND,
497 x_effective_time_elapsed_ind => X_EFFECTIVE_TIME_ELAPSED_IND,
498 x_closed_ind => X_CLOSED_IND,
499 x_creation_date => X_LAST_UPDATE_DATE,
500 x_created_by => X_LAST_UPDATED_BY,
501 x_last_update_date =>X_LAST_UPDATE_DATE,
502 x_last_updated_by =>X_LAST_UPDATED_BY,
503 x_last_update_login =>X_LAST_UPDATE_LOGIN
504 );
505
506 update IGS_AD_ADM_UNIT_STAT_ALL set
507 UNIT_ATTEMPT_STATUS = NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
508 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
509 SHOW_ON_OFFIC_NTFCTN_IND = NEW_REFERENCES.SHOW_ON_OFFIC_NTFCTN_IND,
510 EFFECTIVE_PROGRESSION_IND = NEW_REFERENCES.EFFECTIVE_PROGRESSION_IND,
511 EFFECTIVE_TIME_ELAPSED_IND = NEW_REFERENCES.EFFECTIVE_TIME_ELAPSED_IND,
512 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
513 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
514 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
515 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
516 where ROWID = X_ROWID ;
517 if (sql%notfound) then
518 raise no_data_found;
519 end if;
520
521 After_DML (
522 p_action => 'UPDATE',
523 x_rowid => X_ROWID
524 );
525 end UPDATE_ROW;
526
527 procedure ADD_ROW (
528 X_ROWID in out NOCOPY VARCHAR2,
529 X_ORG_ID in NUMBER,
530 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
531 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
532 X_DESCRIPTION in VARCHAR2,
533 X_SHOW_ON_OFFIC_NTFCTN_IND in VARCHAR2,
534 X_EFFECTIVE_PROGRESSION_IND in VARCHAR2,
535 X_EFFECTIVE_TIME_ELAPSED_IND in VARCHAR2,
536 X_CLOSED_IND in VARCHAR2,
537 X_MODE in VARCHAR2 default 'R'
538 ) AS
539 cursor c1 is select rowid from IGS_AD_ADM_UNIT_STAT_ALL
540 where ADMINISTRATIVE_UNIT_STATUS = X_ADMINISTRATIVE_UNIT_STATUS
541 ;
542 begin
543 open c1;
544 fetch c1 into X_ROWID;
545 if (c1%notfound) then
546 close c1;
547 INSERT_ROW (
548 X_ROWID,
549 X_ORG_ID,
550 X_ADMINISTRATIVE_UNIT_STATUS,
551 X_UNIT_ATTEMPT_STATUS,
552 X_DESCRIPTION,
553 X_SHOW_ON_OFFIC_NTFCTN_IND,
554 X_EFFECTIVE_PROGRESSION_IND,
555 X_EFFECTIVE_TIME_ELAPSED_IND,
556 X_CLOSED_IND,
557 X_MODE);
558 return;
559 end if;
560 close c1;
561 UPDATE_ROW (
562 X_ROWID,
563 X_ADMINISTRATIVE_UNIT_STATUS,
567 X_EFFECTIVE_PROGRESSION_IND,
564 X_UNIT_ATTEMPT_STATUS,
565 X_DESCRIPTION,
566 X_SHOW_ON_OFFIC_NTFCTN_IND,
568 X_EFFECTIVE_TIME_ELAPSED_IND,
569 X_CLOSED_IND,
570 X_MODE);
571 end ADD_ROW;
572 end IGS_AD_ADM_UNIT_STAT_PKG;