1 package body IGS_PR_OU_UNIT_SET_PKG AS
2 /* $Header: IGSQI08B.pls 115.6 2003/02/25 09:09:24 anilk ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PR_OU_UNIT_SET_ALL%RowType;
6 new_references IGS_PR_OU_UNIT_SET_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
12 x_pra_sequence_number IN NUMBER DEFAULT NULL,
13 x_pro_sequence_number IN NUMBER DEFAULT NULL,
14 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
15 x_us_version_number IN NUMBER DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL ,
21 x_org_id IN NUMBER DEFAULT NULL
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_PR_OU_UNIT_SET_ALL
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 Close cur_old_ref_values;
41 App_Exception.Raise_Exception;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.progression_rule_cat := x_progression_rule_cat;
48 new_references.pra_sequence_number := x_pra_sequence_number;
49 new_references.pro_sequence_number := x_pro_sequence_number;
50 new_references.unit_set_cd := x_unit_set_cd;
51 new_references.us_version_number := x_us_version_number;
52 new_references.org_id := x_org_id;
53
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 PROCEDURE Check_Parent_Existance AS
67 BEGIN
68
69 IF (((old_references.progression_rule_cat = new_references.progression_rule_cat) AND
70 (old_references.pra_sequence_number = new_references.pra_sequence_number) AND
71 (old_references.pro_sequence_number = new_references.pro_sequence_number)) OR
72 ((new_references.progression_rule_cat IS NULL) OR
73 (new_references.pra_sequence_number IS NULL) OR
74 (new_references.pro_sequence_number IS NULL))) THEN
75 NULL;
76 ELSE
77 IF NOT IGS_PR_RU_OU_PKG.Get_PK_For_Validation (
78 new_references.progression_rule_cat,
79 new_references.pra_sequence_number,
80 new_references.pro_sequence_number
81 ) THEN
82 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
83 IGS_GE_MSG_STACK.ADD;
84 App_Exception.Raise_Exception;
85
86 END IF;
87 END IF;
88
89 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
90 (old_references.us_version_number = new_references.us_version_number)) OR
91 ((new_references.unit_set_cd IS NULL) OR
92 (new_references.us_version_number IS NULL))) THEN
93 NULL;
94 ELSE
95 IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
96 new_references.unit_set_cd,
97 new_references.us_version_number
98 ) THEN
99 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END IF;
103
104 END IF;
105
106 END Check_Parent_Existance;
107
108 FUNCTION Get_PK_For_Validation (
109 x_progression_rule_cat IN VARCHAR2,
110 x_pra_sequence_number IN NUMBER,
111 x_pro_sequence_number IN NUMBER,
112 x_unit_set_cd IN VARCHAR2,
113 x_us_version_number IN NUMBER
114 ) RETURN BOOLEAN AS
115
116 CURSOR cur_rowid IS
117 SELECT rowid
118 FROM IGS_PR_OU_UNIT_SET_ALL
119 WHERE progression_rule_cat = x_progression_rule_cat
120 AND pra_sequence_number = x_pra_sequence_number
121 AND pro_sequence_number = x_pro_sequence_number
122 AND unit_set_cd = x_unit_set_cd
123 AND us_version_number = x_us_version_number
124 FOR UPDATE NOWAIT;
125
126 lv_rowid cur_rowid%RowType;
127
128 BEGIN
129
130 Open cur_rowid;
131 Fetch cur_rowid INTO lv_rowid;
132 IF (cur_rowid%FOUND) THEN
133 Close cur_rowid;
134 Return (TRUE);
135 ELSE
136 Close cur_rowid;
137 Return (FALSE);
138 END IF;
139
140 END Get_PK_For_Validation;
141
142 PROCEDURE GET_FK_IGS_PR_RU_OU (
143 x_progression_rule_cat IN VARCHAR2,
144 x_pra_sequence_number IN NUMBER,
145 x_sequence_number IN NUMBER
146 ) AS
147
148 CURSOR cur_rowid IS
149 SELECT rowid
150 FROM IGS_PR_OU_UNIT_SET_ALL
151 WHERE progression_rule_cat = x_progression_rule_cat
152 AND pra_sequence_number = x_pra_sequence_number
153 AND pro_sequence_number = x_sequence_number ;
154
155 lv_rowid cur_rowid%RowType;
156
157 BEGIN
158
159 Open cur_rowid;
160 Fetch cur_rowid INTO lv_rowid;
161 IF (cur_rowid%FOUND) THEN
162 Fnd_Message.Set_Name ('IGS', 'IGS_PR_POUS_PRO_FK');
163 IGS_GE_MSG_STACK.ADD;
164 Close cur_rowid;
165 App_Exception.Raise_Exception;
166 Return;
167 END IF;
168 Close cur_rowid;
169
170 END GET_FK_IGS_PR_RU_OU;
171
172 PROCEDURE GET_FK_IGS_EN_UNIT_SET (
173 x_unit_set_cd IN VARCHAR2,
174 x_version_number IN NUMBER
175 ) AS
176
177 CURSOR cur_rowid IS
178 SELECT rowid
179 FROM IGS_PR_OU_UNIT_SET_ALL
180 WHERE unit_set_cd = x_unit_set_cd
181 AND us_version_number = x_version_number ;
182
183 lv_rowid cur_rowid%RowType;
184
185 BEGIN
186
187 Open cur_rowid;
188 Fetch cur_rowid INTO lv_rowid;
189 IF (cur_rowid%FOUND) THEN
190 Fnd_Message.Set_Name ('IGS', 'IGS_PR_POUS_US_FK');
191 IGS_GE_MSG_STACK.ADD;
192 Close cur_rowid;
193 App_Exception.Raise_Exception;
194 Return;
195 END IF;
196 Close cur_rowid;
197
198 END GET_FK_IGS_EN_UNIT_SET;
199
200 PROCEDURE BeforeInsertUpdate( p_action VARCHAR2 ) AS
201 /*
202 || Created By : anilk
203 || Created On : 25-FEB-2003
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209 CURSOR c_parent (
210 cp_progression_rule_cat IGS_PR_RU_OU.progression_rule_cat%TYPE,
211 cp_pra_sequence_number IGS_PR_RU_OU.pra_sequence_number%TYPE,
212 cp_sequence_number IGS_PR_RU_OU.sequence_number%TYPE ) IS
213 SELECT 1
214 FROM IGS_PR_RU_OU pro
215 WHERE pro.progression_rule_cat = cp_progression_rule_cat AND
216 pro.pra_sequence_number = cp_pra_sequence_number AND
217 pro.sequence_number = cp_sequence_number AND
218 pro.logical_delete_dt is NULL;
219
220 l_dummy NUMBER;
221
222 BEGIN
223
224 IF (p_action = 'INSERT') THEN
225 OPEN c_parent( new_references.progression_rule_cat, new_references.pra_sequence_number, new_references.pro_sequence_number );
226 FETCH c_parent INTO l_dummy;
227 IF c_parent%NOTFOUND THEN
228 CLOSE c_parent;
229 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
230 IGS_GE_MSG_STACK.ADD;
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 END IF;
233 CLOSE c_parent;
234 ELSIF(p_action = 'UPDATE') THEN
235 IF new_references.progression_rule_cat <> old_references.progression_rule_cat OR
236 new_references.pra_sequence_number <> old_references.pra_sequence_number OR
237 new_references.pro_sequence_number <> old_references.pro_sequence_number THEN
238 OPEN c_parent( new_references.progression_rule_cat, new_references.pra_sequence_number, new_references.pro_sequence_number );
239 FETCH c_parent INTO l_dummy;
240 IF c_parent%NOTFOUND THEN
241 CLOSE c_parent;
242 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
243 IGS_GE_MSG_STACK.ADD;
244 APP_EXCEPTION.RAISE_EXCEPTION;
245 END IF;
246 CLOSE c_parent;
247 END IF;
248 END IF;
249
250 END BeforeInsertUpdate;
251
252 PROCEDURE Before_DML (
253 p_action IN VARCHAR2,
254 x_rowid IN VARCHAR2 DEFAULT NULL,
255 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
256 x_pra_sequence_number IN NUMBER DEFAULT NULL,
257 x_pro_sequence_number IN NUMBER DEFAULT NULL,
258 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
259 x_us_version_number IN NUMBER DEFAULT NULL,
260 x_creation_date IN DATE DEFAULT NULL,
261 x_created_by IN NUMBER DEFAULT NULL,
262 x_last_update_date IN DATE DEFAULT NULL,
263 x_last_updated_by IN NUMBER DEFAULT NULL,
264 x_last_update_login IN NUMBER DEFAULT NULL,
265 x_org_id IN NUMBER DEFAULT NULL
266 ) AS
267 BEGIN
268
269 Set_Column_Values (
270 p_action,
271 x_rowid,
272 x_progression_rule_cat,
273 x_pra_sequence_number,
274 x_pro_sequence_number,
275 x_unit_set_cd,
276 x_us_version_number,
277 x_creation_date,
278 x_created_by,
279 x_last_update_date,
280 x_last_updated_by,
281 x_last_update_login ,
282 x_org_id
283 );
284
285 IF (p_action = 'INSERT') THEN
286 -- Call all the procedures related to Before Insert.
287 Check_Parent_Existance;
288 IF Get_PK_For_Validation (
289 new_references.progression_rule_cat,
290 new_references.pra_sequence_number,
291 new_references.pro_sequence_number,
292 new_references.unit_set_cd,
293 new_references.us_version_number) THEN
294 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297 END IF;
298 CHECK_CONSTRAINTS;
299
300 ELSIF (p_action = 'UPDATE') THEN
301 -- Call all the procedures related to Before Update.
302 CHECK_CONSTRAINTS;
303 Check_Parent_Existance;
304 ELSIF (p_action = 'VALIDATE_INSERT') THEN
305 IF Get_PK_For_Validation (
306 new_references.progression_rule_cat,
307 new_references.pra_sequence_number,
308 new_references.pro_sequence_number,
309 new_references.unit_set_cd,
310 new_references.us_version_number) THEN
311 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END IF;
315
316 CHECK_CONSTRAINTS;
317 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
318 CHECK_CONSTRAINTS;
319 END IF;
320
321 -- anilk, bug#2784198
322 BeforeInsertUpdate(p_action);
323
324 END Before_DML;
325
326 procedure INSERT_ROW (
327 X_ROWID in out NOCOPY VARCHAR2,
328 X_PROGRESSION_RULE_CAT in VARCHAR2,
329 X_PRA_SEQUENCE_NUMBER in NUMBER,
330 X_PRO_SEQUENCE_NUMBER in NUMBER,
331 X_UNIT_SET_CD in VARCHAR2,
332 X_US_VERSION_NUMBER in NUMBER,
333 X_MODE in VARCHAR2 default 'R',
334 X_ORG_ID IN NUMBER
335 ) AS
336 cursor C is select ROWID from IGS_PR_OU_UNIT_SET_ALL
337 where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
338 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
339 and PRO_SEQUENCE_NUMBER = X_PRO_SEQUENCE_NUMBER
340 and UNIT_SET_CD = X_UNIT_SET_CD
341 and US_VERSION_NUMBER = X_US_VERSION_NUMBER;
342 X_LAST_UPDATE_DATE DATE;
343 X_LAST_UPDATED_BY NUMBER;
344 X_LAST_UPDATE_LOGIN NUMBER;
345 begin
346 X_LAST_UPDATE_DATE := SYSDATE;
347 if(X_MODE = 'I') then
348 X_LAST_UPDATED_BY := 1;
349 X_LAST_UPDATE_LOGIN := 0;
350 elsif (X_MODE = 'R') then
351 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
352 if X_LAST_UPDATED_BY is NULL then
353 X_LAST_UPDATED_BY := -1;
354 end if;
355 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
356 if X_LAST_UPDATE_LOGIN is NULL then
357 X_LAST_UPDATE_LOGIN := -1;
358 end if;
359 else
360 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
361 IGS_GE_MSG_STACK.ADD;
362 app_exception.raise_exception;
363 end if;
364 Before_DML (
365 p_action => 'INSERT',
366 x_rowid => x_rowid ,
367 x_progression_rule_cat => x_progression_rule_cat ,
368 x_pra_sequence_number => x_pra_sequence_number ,
369 x_pro_sequence_number => x_pro_sequence_number ,
370 x_unit_set_cd => x_unit_set_cd ,
371 x_us_version_number => x_us_version_number ,
372 x_creation_date => x_last_update_date,
373 x_created_by => x_last_updated_by,
374 x_last_update_date => x_last_update_date ,
375 x_last_updated_by => x_last_updated_by ,
376 x_last_update_login => x_last_update_login ,
377 x_org_id => igs_ge_gen_003.get_org_id
378 );
379 insert into IGS_PR_OU_UNIT_SET_ALL (
380 PROGRESSION_RULE_CAT,
381 PRA_SEQUENCE_NUMBER,
382 PRO_SEQUENCE_NUMBER,
383 UNIT_SET_CD,
384 US_VERSION_NUMBER,
385 CREATION_DATE,
386 CREATED_BY,
387 LAST_UPDATE_DATE,
388 LAST_UPDATED_BY,
389 LAST_UPDATE_LOGIN,
390 ORG_ID
391 ) values (
392 NEW_REFERENCES.PROGRESSION_RULE_CAT,
393 NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
394 NEW_REFERENCES.PRO_SEQUENCE_NUMBER,
395 NEW_REFERENCES.UNIT_SET_CD,
396 NEW_REFERENCES.US_VERSION_NUMBER,
397 X_LAST_UPDATE_DATE,
398 X_LAST_UPDATED_BY,
399 X_LAST_UPDATE_DATE,
400 X_LAST_UPDATED_BY,
401 X_LAST_UPDATE_LOGIN,
402 NEW_REFERENCES.ORG_ID
403 );
404
405 open c;
406 fetch c into X_ROWID;
407 if (c%notfound) then
408 close c;
409 raise no_data_found;
410 end if;
411 close c;
412 end INSERT_ROW;
413
414 procedure LOCK_ROW (
415 X_ROWID in VARCHAR2,
416 X_PROGRESSION_RULE_CAT in VARCHAR2,
417 X_PRA_SEQUENCE_NUMBER in NUMBER,
418 X_PRO_SEQUENCE_NUMBER in NUMBER,
419 X_UNIT_SET_CD in VARCHAR2,
420 X_US_VERSION_NUMBER in NUMBER
421 ) AS
422 cursor c1 is select
423 rowid
424 from IGS_PR_OU_UNIT_SET_ALL
425 where ROWID = X_ROWID for update nowait;
426 tlinfo c1%rowtype;
427
428 begin
429 open c1;
430 fetch c1 into tlinfo;
431 if (c1%notfound) then
432 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
433 IGS_GE_MSG_STACK.ADD;
434 close c1;
435 app_exception.raise_exception;
436 return;
437 end if;
438 close c1;
439 return;
440 end LOCK_ROW;
441
442 procedure DELETE_ROW (
443 X_ROWID in VARCHAR2
444 ) is
445 begin
446 Before_DML (
447 p_action => 'DELETE',
448 x_rowid => X_ROWID
449 );
450
451 delete from IGS_PR_OU_UNIT_SET_ALL
452 where ROWID = X_ROWID;
453 if (sql%notfound) then
454 raise no_data_found;
455 end if;
456
457 end DELETE_ROW;
458
459 PROCEDURE Check_Constraints (
460 Column_Name IN VARCHAR2 DEFAULT NULL,
461 Column_Value IN VARCHAR2 DEFAULT NULL
462 ) AS
463 BEGIN
464
465 IF Column_Name is null THEN
466 NULL;
467 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CAT' THEN
468 new_references.PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
469
470 ELSIF upper(Column_name) = 'UNIT_SET_CD' THEN
471 new_references.UNIT_SET_CD:= COLUMN_VALUE ;
472
473 ELSIF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' THEN
474 new_references.PRA_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
475
476 ELSIF upper(Column_name) = 'PRO_SEQUENCE_NUMBER' THEN
477 new_references.PRO_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
478
479 END IF ;
480
481 IF upper(Column_name) = 'PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
482 IF new_references.PROGRESSION_RULE_CAT<> upper(new_references.PROGRESSION_RULE_CAT) then
483 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
484 IGS_GE_MSG_STACK.ADD;
485 App_Exception.Raise_Exception ;
486 END IF;
487
488 END IF ;
489
490 IF upper(Column_name) = 'UNIT_SET_CD' OR COLUMN_NAME IS NULL THEN
491 IF new_references.UNIT_SET_CD<> upper(new_references.UNIT_SET_CD) then
492 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
493 IGS_GE_MSG_STACK.ADD;
494 App_Exception.Raise_Exception ;
495 END IF;
496
497 END IF ;
498
499 IF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
500 IF new_references.PRA_SEQUENCE_NUMBER < 1 or new_references.PRA_SEQUENCE_NUMBER > 999999 then
501 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
502 IGS_GE_MSG_STACK.ADD;
503 App_Exception.Raise_Exception ;
504 END IF;
505
506 END IF ;
507
508 IF upper(Column_name) = 'PRO_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
509 IF new_references.PRO_SEQUENCE_NUMBER < 1 or new_references.PRO_SEQUENCE_NUMBER > 999999 then
510 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception ;
513 END IF;
514
515 END IF ;
516
517
518 END Check_Constraints;
519
520 end IGS_PR_OU_UNIT_SET_PKG;
[Home] [Help]