[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_DSCP_PKG
Source
1 package body IGS_PS_DSCP_PKG as
2 /* $Header: IGSPI52B.pls 115.6 2002/11/29 02:30:30 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_DSCP_ALL%RowType;
6 new_references IGS_PS_DSCP_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_funding_index_1 IN NUMBER DEFAULT NULL,
14 x_funding_index_2 IN NUMBER DEFAULT NULL,
15 x_funding_index_3 IN NUMBER DEFAULT NULL,
16 x_govt_discipline_group_cd 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 x_org_id IN NUMBER DEFAULT NULL
24 ) AS
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_PS_DSCP_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.discipline_group_cd := x_discipline_group_cd;
50 new_references.description := x_description;
51 new_references.funding_index_1 := x_funding_index_1;
52 new_references.funding_index_2 := x_funding_index_2;
53 new_references.funding_index_3 := x_funding_index_3;
54 new_references.govt_discipline_group_cd := x_govt_discipline_group_cd;
55 new_references.closed_ind := x_closed_ind;
56 new_references.org_id:=x_org_id;
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
68 END Set_Column_Values;
69
70 PROCEDURE BeforeRowInsertUpdateDelete1(
71 p_inserting IN BOOLEAN DEFAULT FALSE,
72 p_updating IN BOOLEAN DEFAULT FALSE,
73 p_deleting IN BOOLEAN DEFAULT FALSE
74 ) AS
75 v_message_name VARCHAR2(30);
76 v_description IGS_PS_DSCP_ALL.description%TYPE DEFAULT NULL;
77 v_funding_index_1 IGS_PS_DSCP_ALL.funding_index_1%TYPE DEFAULT NULL;
78 v_funding_index_2 IGS_PS_DSCP_ALL.funding_index_2%TYPE DEFAULT NULL;
79 v_funding_index_3 IGS_PS_DSCP_ALL.funding_index_3%TYPE DEFAULT NULL;
80 v_govt_discipline_group_cd
81 IGS_PS_DSCP_ALL.govt_discipline_group_cd%TYPE DEFAULT NULL;
82 v_closed_ind IGS_PS_DSCP_ALL.closed_ind%TYPE DEFAULT NULL;
83
84 x_rowid varchar2(25);
85 l_org_id NUMBER(15);
86 CURSOR SPDH_CUR IS
87 SELECT Rowid
88 FROM IGS_PS_DSCP_HIST
89 WHERE discipline_group_cd = old_references.discipline_group_cd;
90
91 BEGIN
92 -- Validate Govt IGS_PS_DSCP group code. Also validate if the closed
93 -- indicator has been updated from closed to open to
94 -- verify that the Govt IGS_PS_DSCP group code is not closed.
95 IF p_inserting OR
96 (old_references.govt_discipline_group_cd <> new_references.govt_discipline_group_cd) OR
97 ((old_references.closed_ind = 'N') AND
98 ( new_references.closed_ind = 'Y')) THEN
99 IF IGS_PS_VAL_DI.crsp_val_di_govt_dg (
100 new_references.govt_discipline_group_cd,
101 v_message_name) = FALSE THEN
102 FND_MESSAGE.SET_NAME('IGS',v_message_name);
103 IGS_GE_MSG_STACK.ADD;
104 APP_EXCEPTION.RAISE_EXCEPTION;
105 END IF;
106 END IF;
107 IF p_updating THEN
108 IF old_references.description <> new_references.description OR
109 nvl(old_references.funding_index_1,999999) <> nvl(new_references.funding_index_1,999999) OR
110 nvl(old_references.funding_index_2,999999) <> nvl(new_references.funding_index_2,999999) OR
111 nvl(old_references.funding_index_3,999999) <> nvl(new_references.funding_index_3,999999) OR
112 old_references.govt_discipline_group_cd <> new_references.govt_discipline_group_cd OR
113 old_references.closed_ind <> new_references.closed_ind THEN
114 IF old_references.description <> new_references.description THEN
115 v_description := old_references.description;
116 END IF;
117 IF nvl(old_references.funding_index_1,999999) <> nvl(new_references.funding_index_1,999999) THEN
118 v_funding_index_1 := old_references.funding_index_1;
119 END IF;
120 IF nvl(old_references.funding_index_2,999999) <> nvl(new_references.funding_index_2,999999) THEN
121 v_funding_index_2 := old_references.funding_index_2;
122 END IF;
123 IF nvl(old_references.funding_index_3,999999) <> nvl(new_references.funding_index_3,999999) THEN
124 v_funding_index_3 := old_references.funding_index_3;
125 END IF;
126 IF old_references.govt_discipline_group_cd <> new_references.govt_discipline_group_cd THEN
127 v_govt_discipline_group_cd := old_references.govt_discipline_group_cd;
128 END IF;
129 IF old_references.closed_ind <> new_references.closed_ind THEN
130 v_closed_ind := old_references.closed_ind;
131 END IF;
132
133
134 BEGIN
135 IGS_PS_DSCP_HIST_PKG.Insert_Row(
136 X_ROWID => x_rowid,
137 X_DISCIPLINE_GROUP_CD => old_references.discipline_group_cd,
138 X_HIST_START_DT => old_references.last_update_date,
139 X_HIST_END_DT => new_references.last_update_date,
140 X_HIST_WHO => old_references.last_updated_by,
141 X_DESCRIPTION => v_description,
142 X_FUNDING_INDEX_1 => v_funding_index_1,
143 X_FUNDING_INDEX_2 => v_funding_index_2,
144 X_FUNDING_INDEX_3 => v_funding_index_3,
145 X_GOVT_DISCIPLINE_GROUP_CD => v_govt_discipline_group_cd,
146 X_CLOSED_IND => v_closed_ind,
147 X_MODE => 'R',
148 X_ORG_ID => old_references.org_id);
149 END ;
150 END IF;
151 END IF;
152 IF p_deleting THEN
153 -- Delete IGS_PS_DSCP_HIST records if the IGS_PS_DSCP is deleted.
154 BEGIN
155
156 FOR SPDH_Rec IN SPDH_CUR
157 Loop
158 IGS_PS_DSCP_HIST_PKG.Delete_Row(X_ROWID => SPDH_Rec.Rowid);
159 End Loop;
160
161 END;
162
163 END IF;
164
165
166 END BeforeRowInsertUpdateDelete1;
167
168 PROCEDURE Check_Constraints (
169 Column_Name IN VARCHAR2 DEFAULT NULL,
170 Column_Value IN VARCHAR2 DEFAULT NULL
171 )
172 AS
173 BEGIN
174 IF column_name is null then
175 NULL;
176 ELSIF upper(Column_name) = 'FUNDING_INDEX_1' then
177 new_references.funding_index_1 := IGS_GE_NUMBER.TO_NUM(column_value);
178 ELSIF upper(Column_name) = 'FUNDING_INDEX_2' then
179 new_references.funding_index_2 := IGS_GE_NUMBER.TO_NUM(column_value);
180 ELSIF upper(Column_name) = 'FUNDING_INDEX_3' then
181 new_references.funding_index_3 := IGS_GE_NUMBER.TO_NUM(column_value);
182 ELSIF upper(Column_name) = 'CLOSED_IND' then
183 new_references.closed_ind := column_value;
184 ELSIF upper(Column_name) = 'DISCIPLINE_GROUP_CD' then
185 new_references.discipline_group_cd := column_value;
186 ELSIF upper(Column_name) = 'GOVT_DISCIPLINE_GROUP_CD' then
187 new_references.govt_discipline_group_cd := column_value;
188 END IF;
189
190 IF upper(column_name) = 'FUNDING_INDEX_2' OR
191 column_name is null Then
192 IF new_references.funding_index_2 < 0 OR new_references.funding_index_2 > 1.70 Then
193 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198
199 IF upper(column_name) = 'FUNDING_INDEX_3' OR
200 column_name is null Then
201 IF new_references.funding_index_3 < 0 OR new_references.funding_index_3 > 1.70 Then
202 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END IF;
206 END IF;
207
208 IF upper(column_name) = 'CLOSED_IND' OR
209 column_name is null Then
210 IF new_references.closed_ind NOT IN ('Y','N') THEN
211 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214 END IF;
215 END IF;
216
217 IF upper(column_name) = 'FUNDING_INDEX_1' OR
218 column_name is null Then
219 IF new_references.funding_index_1 < 0 OR new_references.funding_index_1 > 1.70 Then
220 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF;
224 END IF;
225
226 IF upper(column_name) = 'DISCIPLINE_GROUP_CD' OR
227 column_name is null Then
228 IF new_references.discipline_group_cd <> UPPER(new_references.discipline_group_cd) Then
229 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception;
232 END IF;
233 END IF;
234
235 IF upper(column_name) = 'GOVT_DISCIPLINE_GROUP_CD' OR
236 column_name is null Then
237 IF new_references.govt_discipline_group_cd <> UPPER(new_references.govt_discipline_group_cd) Then
238 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 END IF;
242 END IF;
243 END check_constraints;
244
245
246 PROCEDURE Check_Parent_Existance AS
247 BEGIN
248
249 IF (((old_references.govt_discipline_group_cd = new_references.govt_discipline_group_cd)) OR
250 ((new_references.govt_discipline_group_cd IS NULL))) THEN
251 NULL;
252 ELSE
253 IF NOT IGS_PS_GOVT_DSCP_PKG.Get_PK_For_Validation (
254 new_references.govt_discipline_group_cd
255 ) THEN
256 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260 END IF;
261
262 END Check_Parent_Existance;
263
264 PROCEDURE Check_Child_Existance AS
265 BEGIN
266
267 IGS_AD_TER_ED_UNI_AT_PKG.GET_FK_IGS_PS_DSCP (
268 old_references.discipline_group_cd
269 );
270
271 IGS_PS_UNIT_DSCP_PKG.GET_FK_IGS_PS_DSCP (
272 old_references.discipline_group_cd
273 );
274
275 IGS_PS_UNT_DSCP_HIST_PKG.GET_FK_IGS_PS_DSCP (
276 old_references.discipline_group_cd
277 );
278
279 END Check_Child_Existance;
280
281 FUNCTION Get_PK_For_Validation (
282 x_discipline_group_cd IN VARCHAR2
283 ) RETURN BOOLEAN AS
284
285 CURSOR cur_rowid IS
286 SELECT rowid
287 FROM IGS_PS_DSCP_ALL
288 WHERE discipline_group_cd = x_discipline_group_cd
289 FOR UPDATE NOWAIT;
290
291 lv_rowid cur_rowid%RowType;
292
293 BEGIN
294
295 Open cur_rowid;
296 Fetch cur_rowid INTO lv_rowid;
297 IF (cur_rowid%FOUND) THEN
298 Close cur_rowid;
299 Return (TRUE);
300 ELSE
301 Close cur_rowid;
302 Return (FALSE);
303 END IF;
304 END Get_PK_For_Validation;
305
306 PROCEDURE GET_FK_IGS_PS_GOVT_DSCP (
307 x_govt_discipline_group_cd IN VARCHAR2
308 ) AS
309
310 CURSOR cur_rowid IS
311 SELECT rowid
312 FROM IGS_PS_DSCP_ALL
313 WHERE govt_discipline_group_cd = x_govt_discipline_group_cd ;
314
315 lv_rowid cur_rowid%RowType;
316
317 BEGIN
318
319 Open cur_rowid;
320 Fetch cur_rowid INTO lv_rowid;
321 IF (cur_rowid%FOUND) THEN
322 Close cur_rowid;
323 Fnd_Message.Set_Name ('IGS', 'IGS_PS_DI_GD_FK');
324 IGS_GE_MSG_STACK.ADD;
325 App_Exception.Raise_Exception;
326 Return;
327 END IF;
328 Close cur_rowid;
329
330 END GET_FK_IGS_PS_GOVT_DSCP;
331
332 PROCEDURE Before_DML (
333 p_action IN VARCHAR2,
334 x_rowid IN VARCHAR2 DEFAULT NULL,
335 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
336 x_description IN VARCHAR2 DEFAULT NULL,
337 x_funding_index_1 IN NUMBER DEFAULT NULL,
338 x_funding_index_2 IN NUMBER DEFAULT NULL,
339 x_funding_index_3 IN NUMBER DEFAULT NULL,
340 x_govt_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
341 x_closed_ind IN VARCHAR2 DEFAULT NULL,
342 x_creation_date IN DATE DEFAULT NULL,
343 x_created_by IN NUMBER DEFAULT NULL,
344 x_last_update_date IN DATE DEFAULT NULL,
345 x_last_updated_by IN NUMBER DEFAULT NULL,
346 x_last_update_login IN NUMBER DEFAULT NULL,
347 x_org_id IN NUMBER DEFAULT NULL
348 ) AS
349 BEGIN
350
351 Set_Column_Values (
352 p_action,
353 x_rowid,
354 x_discipline_group_cd,
355 x_description,
356 x_funding_index_1,
357 x_funding_index_2,
358 x_funding_index_3,
359 x_govt_discipline_group_cd,
360 x_closed_ind,
361 x_creation_date,
362 x_created_by,
363 x_last_update_date,
364 x_last_updated_by,
365 x_last_update_login,
366 x_org_id
367 );
368
369
370 IF (p_action = 'INSERT') THEN
371 -- Call all the procedures related to Before Insert.
372 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
373 IF Get_PK_For_Validation (
374 new_references.discipline_group_cd
375 ) THEN
376 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 END IF;
380 Check_Constraints;
381 Check_Parent_Existance;
382 ELSIF (p_action = 'UPDATE') THEN
383 -- Call all the procedures related to Before Update.
384 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
385 Check_Constraints;
386 Check_Parent_Existance;
387 ELSIF (p_action = 'DELETE') THEN
388 -- Call all the procedures related to Before Delete.
389 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
390 Check_Child_Existance;
391 ELSIF (p_action = 'VALIDATE_INSERT') THEN
392 IF Get_PK_For_Validation (
393 new_references.discipline_group_cd
394 ) THEN
395 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398 END IF;
399 Check_Constraints;
400 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
401 Check_Constraints;
402 ELSIF (p_action = 'VALIDATE_DELETE') THEN
403 Check_Child_Existance;
404 END IF;
405 END Before_DML;
406
407 PROCEDURE After_DML (
408 p_action IN VARCHAR2,
409 x_rowid IN VARCHAR2
410 ) AS
411 BEGIN
412
413 l_rowid := x_rowid;
414
415
416 END After_DML;
417
418 procedure INSERT_ROW (
419 X_ROWID in out NOCOPY VARCHAR2,
420 X_DISCIPLINE_GROUP_CD in VARCHAR2,
421 X_DESCRIPTION in VARCHAR2,
422 X_FUNDING_INDEX_1 in NUMBER,
423 X_FUNDING_INDEX_2 in NUMBER,
424 X_FUNDING_INDEX_3 in NUMBER,
425 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
426 X_CLOSED_IND in VARCHAR2,
427 X_MODE in VARCHAR2 default 'R',
428 x_org_id IN NUMBER
429 ) as
430 /****************************************************************************
431 sbaliga 13-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id
432 in call to before_dml as part of SWCR006 build.
433 ****************************************************************************/
434 cursor C is select ROWID from IGS_PS_DSCP_ALL
435 where DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD;
436 X_LAST_UPDATE_DATE DATE;
437 X_LAST_UPDATED_BY NUMBER;
438 X_LAST_UPDATE_LOGIN NUMBER;
439 begin
440 X_LAST_UPDATE_DATE := SYSDATE;
441 if(X_MODE = 'I') then
442 X_LAST_UPDATED_BY := 1;
443 X_LAST_UPDATE_LOGIN := 0;
444 elsif (X_MODE = 'R') then
445 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
446 if X_LAST_UPDATED_BY is NULL then
447 X_LAST_UPDATED_BY := -1;
448 end if;
449 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
450 if X_LAST_UPDATE_LOGIN is NULL then
451 X_LAST_UPDATE_LOGIN := -1;
452 end if;
453 else
454 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
455 IGS_GE_MSG_STACK.ADD;
456 app_exception.raise_exception;
457 end if;
458
459 Before_DML( p_action => 'INSERT',
460 x_rowid => X_ROWID,
461 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
462 x_description => X_DESCRIPTION,
463 x_funding_index_1 => X_FUNDING_INDEX_1,
464 x_funding_index_2 => X_FUNDING_INDEX_2,
465 x_funding_index_3 => X_FUNDING_INDEX_3,
466 x_govt_discipline_group_cd => X_GOVT_DISCIPLINE_GROUP_CD,
467 x_closed_ind => NVL(X_CLOSED_IND,'N'),
468 x_creation_date => X_LAST_UPDATE_DATE,
469 x_created_by => X_LAST_UPDATED_BY,
470 x_last_update_date => X_LAST_UPDATE_DATE,
471 x_last_updated_by => X_LAST_UPDATED_BY,
472 x_last_update_login => X_LAST_UPDATE_LOGIN,
473 x_org_id => igs_ge_gen_003.get_org_id
474 );
475 insert into IGS_PS_DSCP_ALL (
476 DISCIPLINE_GROUP_CD,
477 DESCRIPTION,
478 FUNDING_INDEX_1,
479 FUNDING_INDEX_2,
480 FUNDING_INDEX_3,
481 GOVT_DISCIPLINE_GROUP_CD,
482 CLOSED_IND,
483 CREATION_DATE,
484 CREATED_BY,
485 LAST_UPDATE_DATE,
486 LAST_UPDATED_BY,
487 LAST_UPDATE_LOGIN,
488 ORG_ID
489 ) values (
490 NEW_REFERENCES.DISCIPLINE_GROUP_CD,
491 NEW_REFERENCES.DESCRIPTION,
492 NEW_REFERENCES.FUNDING_INDEX_1,
493 NEW_REFERENCES.FUNDING_INDEX_2,
494 NEW_REFERENCES.FUNDING_INDEX_3,
495 NEW_REFERENCES.GOVT_DISCIPLINE_GROUP_CD,
496 NEW_REFERENCES.CLOSED_IND,
497 X_LAST_UPDATE_DATE,
498 X_LAST_UPDATED_BY,
499 X_LAST_UPDATE_DATE,
500 X_LAST_UPDATED_BY,
501 X_LAST_UPDATE_LOGIN,
502 NEW_REFERENCES.ORG_ID
503 );
504
505 open c;
506 fetch c into X_ROWID;
507 if (c%notfound) then
508 close c;
509 raise no_data_found;
510 end if;
511 close c;
512 After_DML(
513 p_action => 'INSERT',
514 x_rowid => X_ROWID
515 );
516
517 end INSERT_ROW;
518
519 procedure LOCK_ROW (
520 X_ROWID in VARCHAR2,
521 X_DISCIPLINE_GROUP_CD in VARCHAR2,
522 X_DESCRIPTION in VARCHAR2,
523 X_FUNDING_INDEX_1 in NUMBER,
524 X_FUNDING_INDEX_2 in NUMBER,
525 X_FUNDING_INDEX_3 in NUMBER,
526 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
527 X_CLOSED_IND in VARCHAR2
528
529 ) as
530 cursor c1 is select
531 DESCRIPTION,
532 FUNDING_INDEX_1,
533 FUNDING_INDEX_2,
534 FUNDING_INDEX_3,
535 GOVT_DISCIPLINE_GROUP_CD,
536 CLOSED_IND,
537 ORG_ID
538 from IGS_PS_DSCP_ALL
539 where ROWID = X_ROWID for update nowait;
540 tlinfo c1%rowtype;
541
542 begin
543 open c1;
544 fetch c1 into tlinfo;
545 if (c1%notfound) then
546 close c1;
547 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
548 IGS_GE_MSG_STACK.ADD;
549 app_exception.raise_exception;
550 return;
551 end if;
552 close c1;
553
554 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
555 AND ((tlinfo.FUNDING_INDEX_1 = X_FUNDING_INDEX_1)
556 OR ((tlinfo.FUNDING_INDEX_1 is null)
557 AND (X_FUNDING_INDEX_1 is null)))
558 AND ((tlinfo.FUNDING_INDEX_2 = X_FUNDING_INDEX_2)
559 OR ((tlinfo.FUNDING_INDEX_2 is null)
560 AND (X_FUNDING_INDEX_2 is null)))
561 AND ((tlinfo.FUNDING_INDEX_3 = X_FUNDING_INDEX_3)
562 OR ((tlinfo.FUNDING_INDEX_3 is null)
563 AND (X_FUNDING_INDEX_3 is null)))
564 AND (tlinfo.GOVT_DISCIPLINE_GROUP_CD = X_GOVT_DISCIPLINE_GROUP_CD)
565 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
566
567 ) then
568 null;
569 else
570 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
571 IGS_GE_MSG_STACK.ADD;
572 app_exception.raise_exception;
573 end if;
574 return;
575 end LOCK_ROW;
576
577 procedure UPDATE_ROW (
578 X_ROWID in VARCHAR2,
579 X_DISCIPLINE_GROUP_CD in VARCHAR2,
580 X_DESCRIPTION in VARCHAR2,
581 X_FUNDING_INDEX_1 in NUMBER,
582 X_FUNDING_INDEX_2 in NUMBER,
583 X_FUNDING_INDEX_3 in NUMBER,
584 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
585 X_CLOSED_IND in VARCHAR2,
586 X_MODE in VARCHAR2 default 'R'
587 ) as
588 X_LAST_UPDATE_DATE DATE;
589 X_LAST_UPDATED_BY NUMBER;
590 X_LAST_UPDATE_LOGIN NUMBER;
591 begin
592 X_LAST_UPDATE_DATE := SYSDATE;
593 if(X_MODE = 'I') then
594 X_LAST_UPDATED_BY := 1;
595 X_LAST_UPDATE_LOGIN := 0;
596 elsif (X_MODE = 'R') then
597 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
598 if X_LAST_UPDATED_BY is NULL then
599 X_LAST_UPDATED_BY := -1;
600 end if;
601 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
602 if X_LAST_UPDATE_LOGIN is NULL then
603 X_LAST_UPDATE_LOGIN := -1;
604 end if;
605 else
606 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
607 IGS_GE_MSG_STACK.ADD;
608 app_exception.raise_exception;
609 end if;
610
611 Before_DML( p_action => 'UPDATE',
612 x_rowid => X_ROWID,
613 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
614 x_description => X_DESCRIPTION,
615 x_funding_index_1 => X_FUNDING_INDEX_1,
616 x_funding_index_2 => X_FUNDING_INDEX_2,
617 x_funding_index_3 => X_FUNDING_INDEX_3,
618 x_govt_discipline_group_cd => X_GOVT_DISCIPLINE_GROUP_CD,
619 x_closed_ind => X_CLOSED_IND,
620 x_creation_date => X_LAST_UPDATE_DATE,
621 x_created_by => X_LAST_UPDATED_BY,
622 x_last_update_date => X_LAST_UPDATE_DATE,
623 x_last_updated_by => X_LAST_UPDATED_BY,
624 x_last_update_login => X_LAST_UPDATE_LOGIN
625 );
626 update IGS_PS_DSCP_ALL set
627 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
628 FUNDING_INDEX_1 = NEW_REFERENCES.FUNDING_INDEX_1,
629 FUNDING_INDEX_2 = NEW_REFERENCES.FUNDING_INDEX_2,
630 FUNDING_INDEX_3 = NEW_REFERENCES.FUNDING_INDEX_3,
631 GOVT_DISCIPLINE_GROUP_CD = NEW_REFERENCES.GOVT_DISCIPLINE_GROUP_CD,
632 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
633 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
634 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
635 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
636 where ROWID = X_ROWID
637 ;
638 if (sql%notfound) then
639 raise no_data_found;
640 end if;
641 After_DML(
642 p_action => 'UPDATE',
643 x_rowid => X_ROWID
644 );
645 end UPDATE_ROW;
646
647 procedure ADD_ROW (
648 X_ROWID in out NOCOPY VARCHAR2,
649 X_DISCIPLINE_GROUP_CD in VARCHAR2,
650 X_DESCRIPTION in VARCHAR2,
651 X_FUNDING_INDEX_1 in NUMBER,
652 X_FUNDING_INDEX_2 in NUMBER,
653 X_FUNDING_INDEX_3 in NUMBER,
654 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
655 X_CLOSED_IND in VARCHAR2,
656 X_MODE in VARCHAR2 default 'R',
657 X_ORG_ID IN NUMBER
658 ) as
659 cursor c1 is select rowid from IGS_PS_DSCP_ALL
660 where DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD
661 ;
662 begin
663 open c1;
664 fetch c1 into X_ROWID;
665 if (c1%notfound) then
666 close c1;
667 INSERT_ROW (
668 X_ROWID,
669 X_DISCIPLINE_GROUP_CD,
670 X_DESCRIPTION,
671 X_FUNDING_INDEX_1,
672 X_FUNDING_INDEX_2,
673 X_FUNDING_INDEX_3,
674 X_GOVT_DISCIPLINE_GROUP_CD,
675 X_CLOSED_IND,
676 X_MODE,
677 X_ORG_ID);
678 return;
679 end if;
680 close c1;
681 UPDATE_ROW (
682 X_ROWID,
683 X_DISCIPLINE_GROUP_CD,
684 X_DESCRIPTION,
685 X_FUNDING_INDEX_1,
686 X_FUNDING_INDEX_2,
687 X_FUNDING_INDEX_3,
688 X_GOVT_DISCIPLINE_GROUP_CD,
689 X_CLOSED_IND,
690 X_MODE
691 );
692 end ADD_ROW;
693
694 procedure DELETE_ROW (
695 X_ROWID in VARCHAR2
696 ) as
697 begin
698 Before_DML( p_action => 'DELETE',
699 x_rowid => X_ROWID
700 );
701 delete from IGS_PS_DSCP_ALL
702 where ROWID = X_ROWID;
703 if (sql%notfound) then
704 raise no_data_found;
705 end if;
706 After_DML(
707 p_action => 'DELETE',
708 x_rowid => X_ROWID
709 );
710 end DELETE_ROW;
711
712 end IGS_PS_DSCP_PKG;