[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_DCNT_REASONCD_PKG
Source
1 package body IGS_EN_DCNT_REASONCD_PKG AS
2 /* $Header: IGSEI20B.pls 115.8 2003/02/20 08:55:14 prraj ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_DCNT_REASONCD_ALL%RowType;
5 new_references IGS_EN_DCNT_REASONCD_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_discontinuation_reason_cd IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_dflt_ind IN VARCHAR2 DEFAULT NULL,
14 x_DCNT_PROGRAM_ind IN VARCHAR2 DEFAULT NULL,
15 x_DCNT_UNIT_ind IN VARCHAR2 DEFAULT NULL,
16 x_s_dcnt_reason_type IN VARCHAR2 DEFAULT NULL,
17 x_sys_dflt_ind IN VARCHAR2 DEFAULT NULL,
18 x_closed_ind IN VARCHAR2 DEFAULT NULL,
19 x_comments IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_EN_DCNT_REASONCD_ALL
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 Close cur_old_ref_values;
42 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
43 IGS_GE_MSG_STACK.ADD;
44 App_Exception.Raise_Exception;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.org_id := x_org_id;
51 new_references.discontinuation_reason_cd := x_discontinuation_reason_cd;
52 new_references.description := x_description;
53 new_references.dflt_ind := x_dflt_ind;
54 new_references.DCNT_PROGRAM_IND := x_DCNT_PROGRAM_IND;
55 new_references.DCNT_UNIT_IND := x_DCNT_UNIT_IND;
56 new_references.s_discontinuation_reason_type := x_s_dcnt_reason_type;
57 new_references.sys_dflt_ind := x_sys_dflt_ind;
58 new_references.closed_ind := x_closed_ind;
59 new_references.comments := x_comments;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END Set_Column_Values;
72
73 -- Trigger description :-
74 -- "OSS_TST".trg_dr_br_iu
75 -- BEFORE INSERT OR UPDATE
76 -- ON IGS_EN_DCNT_REASONCD
77 -- FOR EACH ROW
78
79 PROCEDURE BeforeRowInsertUpdate1(
80 p_inserting IN BOOLEAN DEFAULT FALSE,
81 p_updating IN BOOLEAN DEFAULT FALSE,
82 p_deleting IN BOOLEAN DEFAULT FALSE
83 ) AS
84 v_message_name varchar2(30);
85 BEGIN
86 -- Cannot update or insert to a closed s_discontinuation_reason_type
87 IF (p_inserting OR
88 (p_updating AND
89 old_references.s_discontinuation_reason_type <>
90 new_references.s_discontinuation_reason_type)) THEN
91 IF IGS_EN_VAL_DR.enrp_val_sdrt_closed(
92 new_references.s_discontinuation_reason_type,
93 v_message_name) = FALSE THEN
94 fnd_message.set_name('IGS',v_message_name);
95 IGS_GE_MSG_STACK.ADD;
96 app_exception.raise_exception;
97 END IF;
98 END IF;
99 -- Can only set sys_dflt_ind to 'Y' if s_discontinuation_reason_type is set
100 IF ((p_inserting OR
101 p_updating) AND
102 new_references.sys_dflt_ind = 'Y') THEN
103 IF IGS_EN_VAL_DR.enrp_val_dr_sysdflt(
104 new_references.s_discontinuation_reason_type,
105 new_references.sys_dflt_ind,
106 v_message_name) = FALSE THEN
107 fnd_message.set_name('IGS',v_message_name);
108 IGS_GE_MSG_STACK.ADD;
109 app_exception.raise_exception;
110 END IF;
111 END IF;
112
113
114 END BeforeRowInsertUpdate1;
115
116 procedure Check_constraints(
117 column_name IN VARCHAR2 DEFAULT NULL,
118 column_value IN VARCHAR2 DEFAULT NULL
119 ) AS
120 begin
121 IF column_name is null then
122 NULL;
123 ELSIF upper(column_name) = 'SYS_DFLT_IND' then
124 new_references.sys_dflt_ind := column_value;
125 ELSIF upper(column_name) = 'CLOSED_IND' then
126 new_references.closed_ind := column_value;
127 ELSIF upper(column_name) = 'DFLT_IND' then
128 new_references.dflt_ind := column_value;
129 ELSIF upper(column_name) = 'DCNT_UNIT_IND' then
130 new_references.DCNT_UNIT_IND := column_value;
131 ELSIF upper(column_name) = 'DCNT_PROGRAM_IND' then
132 new_references.DCNT_PROGRAM_IND := column_value;
133 ELSIF upper(column_name) = 'DISCONTINUATION_REASON_CD' then
134 new_references.discontinuation_reason_cd := column_value;
135 ELSIF upper(column_name) = 'S_DISCONTINUATION_REASON_TYPE' then
136 new_references.s_discontinuation_reason_type := column_value;
137 END IF;
138
139 IF upper(column_name) = 'SYS_DFLT_IND' OR
140 column_name is NULL then
141 if new_references.sys_dflt_ind NOT IN ('Y','N') OR
142 new_references.sys_dflt_ind <>upper(new_references.sys_dflt_ind) then
143 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
144 IGS_GE_MSG_STACK.ADD;
145 App_Exception.Raise_Exception;
146 end if;
147 end if;
148 IF upper(column_name) = 'CLOSED_IND' OR
149 column_name is NULL then
150 if new_references.closed_ind NOT IN ('Y','N') OR
151 new_references.closed_ind <> upper(new_references.closed_ind) then
152 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 end if;
156 end if;
157 IF upper(column_name) = 'DFLT_IND' OR
158 column_name is NULL then
159 if new_references.dflt_ind NOT IN ('Y','N') OR
160 new_references.dflt_ind <> upper(new_references.dflt_ind) then
161 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
162 IGS_GE_MSG_STACK.ADD;
163 App_Exception.Raise_Exception;
164 end if;
165 end if;
166 IF upper(column_name) = 'DCNT_PROGRAM_IND' OR
167 column_name is NULL then
168 if new_references.DCNT_PROGRAM_IND NOT IN ('Y','N') OR
169 new_references.DCNT_PROGRAM_IND <> upper(new_references.DCNT_PROGRAM_IND) then
170 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 end if;
174 end if;
175 IF upper(column_name) = 'DCNT_UNIT_IND' OR
176 column_name is NULL then
177 if new_references.DCNT_UNIT_IND NOT IN ('Y','N') OR
178 new_references.DCNT_UNIT_IND <> upper(new_references.DCNT_UNIT_IND) then
179 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 end if;
183 end if;
184
185 IF upper(column_name) = 'DISCONTINUATION_REASON_CD' OR
186 column_name is NULL then
187 if new_references.discontinuation_reason_cd <>
188 upper(new_references.discontinuation_reason_cd) then
189 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
190 IGS_GE_MSG_STACK.ADD;
191 App_Exception.Raise_Exception;
192 end if;
193 end if;
194 IF upper(column_name) = 'S_DISCONTINUATION_REASON_TYPE' OR
195 column_name is NULL then
196 if new_references.s_discontinuation_reason_type <>
197 upper(new_references.s_discontinuation_reason_type) then
198 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 end if;
202 end if;
203
204 END check_constraints;
205
206 FUNCTION Get_PK_For_Validation (
207 x_dcnt_reason_cd IN VARCHAR2
208 ) RETURN BOOLEAN AS
209
210 CURSOR cur_rowid IS
211 SELECT rowid
212 FROM IGS_EN_DCNT_REASONCD_ALL
213 WHERE discontinuation_reason_cd = x_dcnt_reason_cd;
214
215 lv_rowid cur_rowid%RowType;
216
217 BEGIN
218
219 Open cur_rowid;
220 Fetch cur_rowid INTO lv_rowid;
221 IF (cur_rowid%FOUND) THEN
222 Close cur_rowid;
223 return(TRUE);
224 else
225 Close cur_rowid;
226 Return(FALSE);
227 END IF;
228
229 END Get_PK_For_Validation;
230
231 PROCEDURE Check_Parent_Existance AS
232 BEGIN
233
234 IF (((old_references.s_discontinuation_reason_type = new_references.s_discontinuation_reason_type)) OR
235 ((new_references.s_discontinuation_reason_type IS NULL))) THEN
236 NULL;
237 ELSE
238 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
239 'DISCONTINUATION_REASON_TYPE',
240 new_references.s_discontinuation_reason_type
241 )THEN
242 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 end if;
246 END IF;
247
248 END Check_Parent_Existance;
249
250
251 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
252 x_s_dcnt_reason_type IN VARCHAR2
253 ) AS
254
255 CURSOR cur_rowid IS
256 SELECT rowid
257 FROM IGS_EN_DCNT_REASONCD_ALL
258 WHERE s_discontinuation_reason_type = x_s_dcnt_reason_type ;
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 Open cur_rowid;
265 Fetch cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 Close cur_rowid;
268 Fnd_Message.Set_Name ('IGS', 'IGS_EN_DR_LKUPV_FK');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 Return;
272 END IF;
273 Close cur_rowid;
274
275 END GET_FK_IGS_LOOKUPS_VIEW;
276
277 PROCEDURE Before_DML (
278 p_action IN VARCHAR2,
279 x_rowid IN VARCHAR2 DEFAULT NULL,
280 x_org_id IN NUMBER DEFAULT NULL,
281 x_discontinuation_reason_cd IN VARCHAR2 DEFAULT NULL,
282 x_description IN VARCHAR2 DEFAULT NULL,
283 x_dflt_ind IN VARCHAR2 DEFAULT NULL,
284 x_dcnt_program_ind IN VARCHAR2 DEFAULT NULL,
285 x_dcnt_unit_ind IN VARCHAR2 DEFAULT NULL,
286 x_s_dcnt_reason_type IN VARCHAR2 DEFAULT NULL,
287 x_sys_dflt_ind IN VARCHAR2 DEFAULT NULL,
288 x_closed_ind IN VARCHAR2 DEFAULT NULL,
289 x_comments IN VARCHAR2 DEFAULT NULL,
290 x_creation_date IN DATE DEFAULT NULL,
291 x_created_by IN NUMBER DEFAULT NULL,
292 x_last_update_date IN DATE DEFAULT NULL,
293 x_last_updated_by IN NUMBER DEFAULT NULL,
294 x_last_update_login IN NUMBER DEFAULT NULL
295 ) AS
296 BEGIN
297
298 Set_Column_Values (
299 p_action,
300 x_rowid,
301 x_org_id,
302 x_discontinuation_reason_cd,
303 x_description,
304 x_dflt_ind,
305 x_dcnt_program_ind,
306 x_dcnt_unit_ind,
307 x_s_dcnt_reason_type,
308 x_sys_dflt_ind,
309 x_closed_ind,
310 x_comments,
311 x_creation_date,
312 x_created_by,
313 x_last_update_date,
314 x_last_updated_by,
315 x_last_update_login
316 );
317
318 IF (p_action = 'INSERT') THEN
319 -- Call all the procedures related to Before Insert.
320 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
321 If Get_PK_For_Validation(
322 new_references.DISCONTINUATION_REASON_CD
323 ) then
324 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 end if;
328 Check_constraints;
329 Check_Parent_Existance;
330 ELSIF (p_action = 'UPDATE') THEN
331 -- Call all the procedures related to Before Update.
332 BeforeRowInsertUpdate1 ( p_updating => TRUE );
333 Check_constraints;
334 Check_Parent_Existance;
335
336 ELSIF (p_action = 'VALIDATE_INSERT') then
337 If Get_PK_For_Validation(
338 new_references.DISCONTINUATION_REASON_CD
339 ) then
340 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
341 IGS_GE_MSG_STACK.ADD;
342 App_Exception.Raise_Exception;
343 end if;
344 Check_constraints;
345 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
346 Check_constraints;
347
348 END IF;
349
350 END Before_DML;
351
352 PROCEDURE After_DML (
353 p_action IN VARCHAR2,
354 x_rowid IN VARCHAR2
355 ) AS
356 BEGIN
357
358 l_rowid := x_rowid;
359
360 IF (p_action = 'INSERT') THEN
361 -- Call all the procedures related to After Insert.
362 Null;
363 ELSIF (p_action = 'UPDATE') THEN
364 -- Call all the procedures related to After Update.
365 Null;
366 END IF;
367
368 END After_DML;
369
370 procedure INSERT_ROW (
371 X_ROWID in out NOCOPY VARCHAR2,
372 X_ORG_ID in NUMBER,
373 X_DISCONTINUATION_REASON_CD in VARCHAR2,
374 X_DESCRIPTION in VARCHAR2,
375 X_DFLT_IND in VARCHAR2,
376 X_DCNT_PROGRAM_IND in VARCHAR2,
377 X_DCNT_UNIT_IND in VARCHAR2,
378 X_S_Dcnt_REASON_TYP in VARCHAR2,
379 X_SYS_DFLT_IND in VARCHAR2,
380 X_CLOSED_IND in VARCHAR2,
381 X_COMMENTS in VARCHAR2,
382 X_MODE in VARCHAR2 default 'R'
383 ) AS
384 cursor C is select ROWID from IGS_EN_DCNT_REASONCD_ALL
385 where DISCONTINUATION_REASON_CD = X_DISCONTINUATION_REASON_CD;
386 X_LAST_UPDATE_DATE DATE;
387 X_LAST_UPDATED_BY NUMBER;
388 X_LAST_UPDATE_LOGIN NUMBER;
389 begin
390 X_LAST_UPDATE_DATE := SYSDATE;
391 if(X_MODE = 'I') then
392 X_LAST_UPDATED_BY := 1;
393 X_LAST_UPDATE_LOGIN := 0;
394 elsif (X_MODE = 'R') then
395 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
396 if X_LAST_UPDATED_BY is NULL then
397 X_LAST_UPDATED_BY := -1;
398 end if;
399 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
400 if X_LAST_UPDATE_LOGIN is NULL then
401 X_LAST_UPDATE_LOGIN := -1;
402 end if;
403 else
404 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
405 IGS_GE_MSG_STACK.ADD;
406 app_exception.raise_exception;
407 end if;
408 Before_DML (
409 p_action => 'INSERT',
410 x_rowid => X_ROWID,
411 x_org_id => igs_ge_gen_003.get_org_id,
412 x_discontinuation_reason_cd => X_DISCONTINUATION_REASON_CD,
413 X_DESCRIPTION => X_DESCRIPTION,
414 X_DFLT_IND => NVL(X_DFLT_IND,'N'),
415 X_DCNT_PROGRAM_IND => NVL(X_DCNT_PROGRAM_IND,'N'),
416 X_DCNT_UNIT_IND => NVL(X_DCNT_UNIT_IND,'N'),
417 x_s_dcnt_reason_type => X_S_dcnt_REASON_TYP,
418 x_sys_dflt_ind => NVL(X_SYS_DFLT_IND,'N'),
419 x_closed_ind => NVL(X_CLOSED_IND,'N'),
420 x_comments => X_COMMENTS,
421 x_creation_date => X_LAST_UPDATE_DATE,
422 x_created_by => X_LAST_UPDATED_BY,
423 x_last_update_date => X_LAST_UPDATE_DATE,
424 x_last_updated_by => X_LAST_UPDATED_BY,
425 x_last_update_login => X_LAST_UPDATE_LOGIN
426 );
427 insert into IGS_EN_DCNT_REASONCD_ALL (
428 org_id,
429 DISCONTINUATION_REASON_CD,
430 DESCRIPTION,
431 DFLT_IND,
432 DCNT_PROGRAM_IND,
433 DCNT_UNIT_IND,
434 S_DISCONTINUATION_REASON_TYPE,
435 SYS_DFLT_IND,
436 CLOSED_IND,
437 COMMENTS,
438 CREATION_DATE,
439 CREATED_BY,
440 LAST_UPDATE_DATE,
441 LAST_UPDATED_BY,
442 LAST_UPDATE_LOGIN
443 ) values (
444 NEW_REFERENCES.ORG_ID,
445 NEW_REFERENCES.DISCONTINUATION_REASON_CD,
446 NEW_REFERENCES.DESCRIPTION,
447 NEW_REFERENCES.DFLT_IND,
448 NEW_REFERENCES.DCNT_PROGRAM_IND,
449 NEW_REFERENCES.DCNT_UNIT_IND,
450 NEW_REFERENCES.S_DISCONTINUATION_REASON_TYPE,
451 NEW_REFERENCES.SYS_DFLT_IND,
452 NEW_REFERENCES.CLOSED_IND,
453 NEW_REFERENCES.COMMENTS,
454 X_LAST_UPDATE_DATE,
455 X_LAST_UPDATED_BY,
456 X_LAST_UPDATE_DATE,
457 X_LAST_UPDATED_BY,
458 X_LAST_UPDATE_LOGIN
459 );
460
461 open c;
462 fetch c into X_ROWID;
463 if (c%notfound) then
464 close c;
465 raise no_data_found;
466 end if;
467 close c;
468
469 After_DML (
470 p_action => 'INSERT',
471 x_rowid => X_ROWID
472 );
473 end INSERT_ROW;
474
475
476 procedure LOCK_ROW (
477 X_ROWID in VARCHAR2,
478 X_DISCONTINUATION_REASON_CD in VARCHAR2,
479 X_DESCRIPTION in VARCHAR2,
480 X_DFLT_IND in VARCHAR2,
481 X_DCNT_PROGRAM_IND in VARCHAR2,
482 X_DCNT_UNIT_IND in VARCHAR2,
483 X_S_Dcnt_REASON_TYP in VARCHAR2,
484 X_SYS_DFLT_IND in VARCHAR2,
485 X_CLOSED_IND in VARCHAR2,
486 X_COMMENTS in VARCHAR2
487 ) AS
488 cursor c1 is select
489
490 DESCRIPTION,
491 DFLT_IND,
492 DCNT_PROGRAM_IND,
493 DCNT_UNIT_IND,
494 S_DISCONTINUATION_REASON_TYPE,
495 SYS_DFLT_IND,
496 CLOSED_IND,
497 COMMENTS
498 from IGS_EN_DCNT_REASONCD_ALL
499 where ROWID = X_ROWID for update nowait;
500 tlinfo c1%rowtype;
501
502 begin
503 open c1;
504 fetch c1 into tlinfo;
505 if (c1%notfound) then
506 close c1;
507 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
508 IGS_GE_MSG_STACK.ADD;
509 app_exception.raise_exception;
510 return;
511 end if;
512 close c1;
513
514 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
515 AND (tlinfo.DFLT_IND = X_DFLT_IND)
516 AND ((tlinfo.DCNT_UNIT_IND = X_DCNT_UNIT_IND)
517 OR ((tlinfo.DCNT_UNIT_IND is null)
518 AND (X_DCNT_UNIT_IND is null)))
519 AND ((tlinfo.DCNT_PROGRAM_IND = X_DCNT_PROGRAM_IND)
520 OR ((tlinfo.DCNT_PROGRAM_IND is null)
521 AND (X_DCNT_PROGRAM_IND is null)))
522 AND ((tlinfo.S_DISCONTINUATION_REASON_TYPE = X_S_Dcnt_REASON_TYP)
523 OR ((tlinfo.S_DISCONTINUATION_REASON_TYPE is null)
524 AND (X_S_Dcnt_REASON_TYP is null)))
525 AND (tlinfo.SYS_DFLT_IND = X_SYS_DFLT_IND)
526 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
527 AND ((tlinfo.COMMENTS = X_COMMENTS)
528 OR ((tlinfo.COMMENTS is null)
529 AND (X_COMMENTS is null)))
530 ) then
531 null;
532 else
533 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
534 IGS_GE_MSG_STACK.ADD;
535 app_exception.raise_exception;
536 end if;
537 return;
538 end LOCK_ROW;
539
540 procedure UPDATE_ROW (
541 X_ROWID in VARCHAR2,
542 X_DISCONTINUATION_REASON_CD in VARCHAR2,
543 X_DESCRIPTION in VARCHAR2,
544 X_DFLT_IND in VARCHAR2,
545 X_DCNT_PROGRAM_IND in VARCHAR2,
546 X_DCNT_UNIT_IND in VARCHAR2,
547 X_S_Dcnt_REASON_TYP in VARCHAR2,
548 X_SYS_DFLT_IND in VARCHAR2,
549 X_CLOSED_IND in VARCHAR2,
550 X_COMMENTS in VARCHAR2,
551 X_MODE in VARCHAR2 default 'R'
552 ) AS
553 X_LAST_UPDATE_DATE DATE;
554 X_LAST_UPDATED_BY NUMBER;
555 X_LAST_UPDATE_LOGIN NUMBER;
556 begin
557 X_LAST_UPDATE_DATE := SYSDATE;
558 if(X_MODE = 'I') then
559 X_LAST_UPDATED_BY := 1;
560 X_LAST_UPDATE_LOGIN := 0;
561 elsif (X_MODE = 'R') then
562 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
563 if X_LAST_UPDATED_BY is NULL then
564 X_LAST_UPDATED_BY := -1;
565 end if;
566 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
567 if X_LAST_UPDATE_LOGIN is NULL then
568 X_LAST_UPDATE_LOGIN := -1;
569 end if;
570 else
571 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
572 IGS_GE_MSG_STACK.ADD;
573 app_exception.raise_exception;
574 end if;
575 Before_DML (
576 p_action => 'UPDATE',
577 x_rowid => X_ROWID,
578 x_discontinuation_reason_cd => X_DISCONTINUATION_REASON_CD,
579 X_DESCRIPTION => X_DESCRIPTION,
580 X_DFLT_IND => X_DFLT_IND,
581 X_DCNT_PROGRAM_IND => X_DCNT_PROGRAM_IND,
582 X_DCNT_UNIT_IND => X_DCNT_UNIT_IND,
583 x_s_dcnt_reason_type => X_S_Dcnt_REASON_TYP,
584 x_sys_dflt_ind => X_SYS_DFLT_IND,
585 x_closed_ind => X_CLOSED_IND,
586 x_comments => X_COMMENTS,
587 x_creation_date => X_LAST_UPDATE_DATE,
588 x_created_by => X_LAST_UPDATED_BY,
589 x_last_update_date => X_LAST_UPDATE_DATE,
590 x_last_updated_by => X_LAST_UPDATED_BY,
591 x_last_update_login => X_LAST_UPDATE_LOGIN
592 );
593 update IGS_EN_DCNT_REASONCD_ALL set
594 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
595 DFLT_IND = NEW_REFERENCES.DFLT_IND,
596 DCNT_PROGRAM_IND = NEW_REFERENCES.DCNT_PROGRAM_IND,
597 DCNT_UNIT_IND = NEW_REFERENCES.DCNT_UNIT_IND,
598 S_DISCONTINUATION_REASON_TYPE = NEW_REFERENCES.S_DISCONTINUATION_REASON_TYPE,
599 SYS_DFLT_IND = NEW_REFERENCES.SYS_DFLT_IND,
600 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
601 COMMENTS = NEW_REFERENCES.COMMENTS,
602 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
603 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
604 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
605 where ROWID = X_ROWID;
606
607 if (sql%notfound) then
608 raise no_data_found;
609 end if;
610 After_DML (
611 p_action => 'UPDATE',
612 x_rowid => X_ROWID
613 );
614 end UPDATE_ROW;
615
616 procedure ADD_ROW (
617 X_ROWID in out NOCOPY VARCHAR2,
618 X_ORG_ID in NUMBER,
619 X_DISCONTINUATION_REASON_CD in VARCHAR2,
620 X_DESCRIPTION in VARCHAR2,
621 X_DFLT_IND in VARCHAR2,
622 X_DCNT_PROGRAM_IND in VARCHAR2,
623 X_DCNT_UNIT_IND in VARCHAR2,
624 X_S_Dcnt_REASON_TYP in VARCHAR2,
625 X_SYS_DFLT_IND in VARCHAR2,
626 X_CLOSED_IND in VARCHAR2,
627 X_COMMENTS in VARCHAR2,
628 X_MODE in VARCHAR2 default 'R'
629 ) AS
630 cursor c1 is select rowid from IGS_EN_DCNT_REASONCD_ALL
631 where DISCONTINUATION_REASON_CD = X_DISCONTINUATION_REASON_CD;
632
633 begin
634 open c1;
635 fetch c1 into X_ROWID;
636 if (c1%notfound) then
637 close c1;
638 INSERT_ROW (
639 X_ROWID,
640 x_org_id,
641 X_DISCONTINUATION_REASON_CD,
642 X_DESCRIPTION,
643 X_DFLT_IND,
644 X_DCNT_PROGRAM_IND,
645 X_DCNT_UNIT_IND,
646 X_S_Dcnt_REASON_TYP,
647 X_SYS_DFLT_IND,
648 X_CLOSED_IND,
649 X_COMMENTS,
650 X_MODE);
651 return;
652 end if;
653 close c1;
654 UPDATE_ROW (
655 X_ROWID,
656 X_DISCONTINUATION_REASON_CD,
657 X_DESCRIPTION,
658 X_DFLT_IND,
659 X_DCNT_PROGRAM_IND,
660 X_DCNT_UNIT_IND,
661 X_S_Dcnt_REASON_TYP,
662 X_SYS_DFLT_IND,
663 X_CLOSED_IND,
664 X_COMMENTS,
665 X_MODE);
666 end ADD_ROW;
667
668
669 END IGS_EN_DCNT_REASONCD_PKG;