1 package body IGS_GR_CRM_ROUND_PRD_PKG as
2 /* $Header: IGSGI10B.pls 120.0 2005/07/05 11:33:41 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_GR_CRM_ROUND_PRD%RowType;
5 new_references IGS_GR_CRM_ROUND_PRD%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_completion_year IN NUMBER DEFAULT NULL,
13 x_completion_period IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM IGS_GR_CRM_ROUND_PRD
24 WHERE rowid = x_rowid;
25
26 BEGIN
27
28 l_rowid := x_rowid;
29
30 -- Code for setting the Old and New Reference Values.
31 -- Populate Old Values.
32 Open cur_old_ref_values;
33 Fetch cur_old_ref_values INTO old_references;
34 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
35 Close cur_old_ref_values;
36 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37 IGS_GE_MSG_STACK.ADD;
38 App_Exception.Raise_Exception;
39 Return;
40 END IF;
41 Close cur_old_ref_values;
42
43 -- Populate New Values.
44 new_references.grd_cal_type := x_grd_cal_type;
45 new_references.grd_ci_sequence_number := x_grd_ci_sequence_number;
46 new_references.completion_year := x_completion_year;
47 new_references.completion_period := x_completion_period;
48 IF (p_action = 'UPDATE') THEN
49 new_references.creation_date := old_references.creation_date;
50 new_references.created_by := old_references.created_by;
51 ELSE
52 new_references.creation_date := x_creation_date;
53 new_references.created_by := x_created_by;
54 END IF;
55 new_references.last_update_date := x_last_update_date;
56 new_references.last_updated_by := x_last_updated_by;
57 new_references.last_update_login := x_last_update_login;
58
59 END Set_Column_Values;
60
61 -- Trigger description :-
62 -- "OSS_TST".trg_crdp_br_iu
63 -- BEFORE INSERT OR UPDATE
64 -- ON IGS_GR_CRM_ROUND_PRD
65 -- FOR EACH ROW
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 the graduation cal instance is of the correct category and status
75 IF p_inserting OR p_updating THEN
76 IF IGS_GR_VAL_CRDP.grdp_val_crdp_iud(
77 new_references.grd_cal_type,
78 new_references.grd_ci_sequence_number,
79 v_message_name) = FALSE THEN
80 Fnd_Message.Set_Name('IGS', v_message_name);
81 IGS_GE_MSG_STACK.ADD;
82 App_Exception.Raise_Exception;
83 END IF;
84 END IF;
85
86
87 END BeforeRowInsertUpdate1;
88
89 PROCEDURE Check_Parent_Existance AS
90 --smaddali added a parent check for bug#2237194 ARCR043 ccr
91 -- as new foreign key has been added with table igs_en_nom_cmpl_prd
92
93 BEGIN
94
95 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
96 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
97 ((new_references.grd_cal_type IS NULL) OR
98 (new_references.grd_ci_sequence_number IS NULL))) THEN
99 NULL;
100 ELSE
101 IF NOT IGS_GR_CRMN_ROUND_PKG.Get_PK_For_Validation (
102 new_references.grd_cal_type,
103 new_references.grd_ci_sequence_number
104 ) THEN
105 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
106 IGS_GE_MSG_STACK.ADD;
107 APP_EXCEPTION.RAISE_EXCEPTION;
108 END IF;
109
110 END IF;
111
112 --smaddali added this check for bug#2237194 ARCR043 ccr
113 -- as new foreign key has been added with table igs_en_nom_cmpl_prd
114 IF (old_references.completion_period = new_references.completion_period) OR
115 (new_references.completion_period IS NULL) THEN
116 NULL;
117 ELSE
118 IF NOT IGS_EN_NOM_CMPL_PRD_PKG.Get_PK_For_Validation (
119 new_references.completion_period
120 ) THEN
121 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
122 IGS_GE_MSG_STACK.ADD;
123 APP_EXCEPTION.RAISE_EXCEPTION;
124 END IF;
125
126 END IF;
127
128 END Check_Parent_Existance;
129
130 PROCEDURE CHECK_CONSTRAINTS(
131 Column_Name IN VARCHAR2 DEFAULT NULL,
132 Column_Value IN VARCHAR2 DEFAULT NULL
133 ) AS
134 --smaddali removed a constraint for item completion_period to check if
135 -- values are in list ('E','S','M') for bug # 2237194 ARCR043 ccr
136
137 BEGIN
138
139 IF Column_Name is null THEN
140 NULL;
141 ELSIF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' THEN
142 new_references.GRD_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
143
144 ELSIF upper(Column_name) = 'COMPLETION_YEAR' THEN
145 new_references.COMPLETION_YEAR:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
146
147 ELSIF upper(Column_name) = 'COMPLETION_PERIOD' THEN
148 new_references.COMPLETION_PERIOD:= COLUMN_VALUE ;
149
150 ELSIF upper(Column_name) = 'GRD_CAL_TYPE' THEN
151 new_references.GRD_CAL_TYPE:= COLUMN_VALUE ;
152
153 ELSIF upper(Column_name) = 'COMPLETION_PERIOD' THEN
154 new_references.COMPLETION_PERIOD:= COLUMN_VALUE ;
155
156 END IF ;
157
158 IF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
159 IF new_references.GRD_CI_SEQUENCE_NUMBER < 1 OR new_references.GRD_CI_SEQUENCE_NUMBER > 999999 then
160 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception ;
163 END IF;
164 END IF ;
165
166 IF upper(Column_name) = 'COMPLETION_YEAR' OR COLUMN_NAME IS NULL THEN
167 IF new_references.COMPLETION_YEAR < 1000 OR new_references.COMPLETION_YEAR > 9999 then
168 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception ;
171 END IF;
172 END IF ;
173
174 IF upper(Column_name) = 'COMPLETION_PERIOD' OR COLUMN_NAME IS NULL THEN
175 --smaddali removed a constraint to check values in list ('E','S','M')
176 --for bug # 2237194 ARCR043 ccr
177 IF new_references.COMPLETION_PERIOD<> upper(NEW_REFERENCES.COMPLETION_PERIOD) 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) = 'GRD_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
185 IF new_references.GRD_CAL_TYPE<> upper(NEW_REFERENCES.GRD_CAL_TYPE) then
186 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception ;
189 END IF;
190
191 END IF ;
192 END;
193
194 FUNCTION Get_PK_For_Validation (
195 x_grd_cal_type IN VARCHAR2,
196 x_grd_ci_sequence_number IN NUMBER,
197 x_completion_year IN NUMBER,
198 x_completion_period IN VARCHAR2
199 )RETURN BOOLEAN AS
200
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM IGS_GR_CRM_ROUND_PRD
204 WHERE grd_cal_type = x_grd_cal_type
205 AND grd_ci_sequence_number = x_grd_ci_sequence_number
206 AND completion_year = x_completion_year
207 AND completion_period = x_completion_period
208 FOR UPDATE NOWAIT;
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 Open cur_rowid;
215 Fetch cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 Close cur_rowid;
218 Return (TRUE);
219 ELSE
220 Close cur_rowid;
221 Return (FALSE);
222 END IF;
223
224 END Get_PK_For_Validation;
225
226 PROCEDURE GET_FK_IGS_GR_CRMN_ROUND (
227 x_grd_cal_type IN VARCHAR2,
228 x_grd_ci_sequence_number IN NUMBER
229 ) AS
230
231 CURSOR cur_rowid IS
232 SELECT rowid
233 FROM IGS_GR_CRM_ROUND_PRD
234 WHERE grd_cal_type = x_grd_cal_type
235 AND grd_ci_sequence_number = x_grd_ci_sequence_number ;
236
237 lv_rowid cur_rowid%RowType;
238
239 BEGIN
240
241 Open cur_rowid;
242 Fetch cur_rowid INTO lv_rowid;
243 IF (cur_rowid%FOUND) THEN
244 Close cur_rowid;
245 Fnd_Message.Set_Name ('IGS', 'IGS_GR_CRDP_CRD_FK');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 Return;
249 END IF;
250 Close cur_rowid;
251
252 END GET_FK_IGS_GR_CRMN_ROUND;
253
254
255
256 PROCEDURE Before_DML (
257 p_action IN VARCHAR2,
258 x_rowid IN VARCHAR2 DEFAULT NULL,
259 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
260 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
261 x_completion_year IN NUMBER DEFAULT NULL,
262 x_completion_period IN VARCHAR2 DEFAULT NULL,
263 x_creation_date IN DATE DEFAULT NULL,
264 x_created_by IN NUMBER DEFAULT NULL,
265 x_last_update_date IN DATE DEFAULT NULL,
266 x_last_updated_by IN NUMBER DEFAULT NULL,
267 x_last_update_login IN NUMBER DEFAULT NULL
268 ) AS
269 BEGIN
270
271 Set_Column_Values (
272 p_action,
273 x_rowid,
274 x_grd_cal_type,
275 x_grd_ci_sequence_number,
276 x_completion_year,
277 x_completion_period,
278 x_creation_date,
279 x_created_by,
280 x_last_update_date,
281 x_last_updated_by,
282 x_last_update_login
283 );
284
285 IF (p_action = 'INSERT') THEN
286 -- Call all the procedures related to Before Insert.
287 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
288 IF GET_PK_FOR_VALIDATION(
289 NEW_REFERENCES.grd_cal_type,
290 NEW_REFERENCES.grd_ci_sequence_number,
291 NEW_REFERENCES.completion_year,
292 NEW_REFERENCES.completion_period
293 ) THEN
294 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297 END IF;
298
299 check_constraints;
300 Check_Parent_Existance;
301 ELSIF (p_action = 'UPDATE') THEN
302 -- Call all the procedures related to Before Update.
303 BeforeRowInsertUpdate1 ( p_updating => TRUE );
304
305 check_constraints;
306 Check_Parent_Existance;
307 ELSIF (p_action = 'VALIDATE_INSERT') THEN
308 IF GET_PK_FOR_VALIDATION(
309 NEW_REFERENCES.grd_cal_type,
310 NEW_REFERENCES.grd_ci_sequence_number,
311 NEW_REFERENCES.completion_year,
312 NEW_REFERENCES.completion_period
313 ) THEN
314 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 END IF;
318 check_constraints;
319 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
320 check_constraints;
321 END IF;
322
323 END Before_DML;
324
325 procedure INSERT_ROW (
326 X_ROWID in out NOCOPY VARCHAR2,
327 X_GRD_CAL_TYPE in VARCHAR2,
328 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
329 X_COMPLETION_YEAR in NUMBER,
330 X_COMPLETION_PERIOD in out NOCOPY VARCHAR2,
331 X_MODE in VARCHAR2 default 'R'
332 ) AS
333 cursor C is select ROWID from IGS_GR_CRM_ROUND_PRD
334 where GRD_CAL_TYPE = X_GRD_CAL_TYPE
335 and GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER
336 and COMPLETION_YEAR = X_COMPLETION_YEAR
337 and COMPLETION_PERIOD = NEW_REFERENCES.COMPLETION_PERIOD;
338 X_LAST_UPDATE_DATE DATE;
339 X_LAST_UPDATED_BY NUMBER;
340 X_LAST_UPDATE_LOGIN NUMBER;
341 begin
342 X_LAST_UPDATE_DATE := SYSDATE;
343 if(X_MODE = 'I') then
344 X_LAST_UPDATED_BY := 1;
345 X_LAST_UPDATE_LOGIN := 0;
346 elsif (X_MODE = 'R') then
347 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
348 if X_LAST_UPDATED_BY is NULL then
349 X_LAST_UPDATED_BY := -1;
350 end if;
351 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
352 if X_LAST_UPDATE_LOGIN is NULL then
353 X_LAST_UPDATE_LOGIN := -1;
354 end if;
355 else
356 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
357 IGS_GE_MSG_STACK.ADD;
358 app_exception.raise_exception;
359 end if;
360
361 Before_DML (
362 p_action => 'INSERT',
363 x_rowid => X_ROWID,
364 x_grd_cal_type => X_GRD_CAL_TYPE,
365 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
366 x_completion_year => X_COMPLETION_YEAR,
367 x_completion_period => NVL(X_COMPLETION_PERIOD, 'E'),
368 x_creation_date => X_LAST_UPDATE_DATE,
369 x_created_by => X_LAST_UPDATED_BY,
370 x_last_update_date => X_LAST_UPDATE_DATE,
371 x_last_updated_by => X_LAST_UPDATED_BY,
372 x_last_update_login => X_LAST_UPDATE_LOGIN
373 );
374
375 insert into IGS_GR_CRM_ROUND_PRD (
376 GRD_CAL_TYPE,
377 GRD_CI_SEQUENCE_NUMBER,
378 COMPLETION_YEAR,
379 COMPLETION_PERIOD,
380 CREATION_DATE,
381 CREATED_BY,
382 LAST_UPDATE_DATE,
383 LAST_UPDATED_BY,
384 LAST_UPDATE_LOGIN
385 ) values (
386 NEW_REFERENCES.GRD_CAL_TYPE,
387 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
388 NEW_REFERENCES.COMPLETION_YEAR,
389 NEW_REFERENCES.COMPLETION_PERIOD,
390 X_LAST_UPDATE_DATE,
391 X_LAST_UPDATED_BY,
392 X_LAST_UPDATE_DATE,
393 X_LAST_UPDATED_BY,
394 X_LAST_UPDATE_LOGIN
395 );
396
397 open c;
398 fetch c into X_ROWID;
399 if (c%notfound) then
400 close c;
401 raise no_data_found;
402 end if;
403 close c;
404
405 end INSERT_ROW;
406
407 procedure LOCK_ROW (
408 X_ROWID in VARCHAR2,
409 X_GRD_CAL_TYPE in VARCHAR2,
410 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
411 X_COMPLETION_YEAR in NUMBER,
412 X_COMPLETION_PERIOD in VARCHAR2
413 ) AS
414 cursor c1 is select
415 rowid
416 from IGS_GR_CRM_ROUND_PRD
417 where ROWID = X_ROWID for update nowait;
418 tlinfo c1%rowtype;
419
420 begin
421 open c1;
422 fetch c1 into tlinfo;
423 if (c1%notfound) then
424 close c1;
425 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
426 app_exception.raise_exception;
427 return;
428 end if;
429 close c1;
430
431 return;
432 end LOCK_ROW;
433
434 procedure DELETE_ROW (
435 X_ROWID in VARCHAR2
436 ) AS
437 begin
438
439 delete from IGS_GR_CRM_ROUND_PRD
440 where ROWID = X_ROWID;
441 if (sql%notfound) then
442 raise no_data_found;
443 end if;
444 end;
445
446 end IGS_GR_CRM_ROUND_PRD_PKG;
[Home] [Help]