1 package body IGS_RU_RULE_PKG as
2 /* $Header: IGSUI11B.pls 115.12 2002/11/29 04:27:46 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_RULE%RowType;
6 new_references IGS_RU_RULE%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_sequence_number IN NUMBER ,
12 x_creation_date IN DATE ,
13 x_created_by IN NUMBER ,
14 x_last_update_date IN DATE ,
15 x_last_updated_by IN NUMBER ,
16 x_last_update_login IN NUMBER
17 ) as
18
19 CURSOR cur_old_ref_values IS
20 SELECT *
21 FROM IGS_RU_RULE
22 WHERE rowid = x_rowid;
23
24 BEGIN
25
26 l_rowid := x_rowid;
27
28 -- Code for setting the Old and New Reference Values.
29 -- Populate Old Values.
30 Open cur_old_ref_values;
31 Fetch cur_old_ref_values INTO old_references;
32 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
33 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_RULE : P_ACTION INSERT VALIDATE_INSERT : IGSUI11B.PLS');
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 Close cur_old_ref_values;
38 Return;
39 END IF;
40 Close cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.sequence_number := x_sequence_number;
44 IF (p_action = 'UPDATE') THEN
45 new_references.creation_date := old_references.creation_date;
46 new_references.created_by := old_references.created_by;
47 ELSE
48 new_references.creation_date := x_creation_date;
49 new_references.created_by := x_created_by;
50 END IF;
51 new_references.last_update_date := x_last_update_date;
52 new_references.last_updated_by := x_last_updated_by;
53 new_references.last_update_login := x_last_update_login;
54
55 END Set_Column_Values;
56
57 PROCEDURE Check_Constraints (
58 Column_Name IN VARCHAR2 ,
59 Column_Value IN VARCHAR2
60 )
61 as
62 BEGIN
63 IF column_name is null then
64 NULL;
65 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' Then
66 new_references.sequence_number := igs_ge_number.to_num(COLUMN_VALUE);
67 END IF;
68 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
69 IF new_references.sequence_number < 0 OR new_references.sequence_number > 999999 THEN
70 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
71 IGS_GE_MSG_STACK.ADD;
72 App_Exception.Raise_Exception;
73 END IF;
74 END IF;
75
76 END Check_Constraints;
77
78
79 FUNCTION Get_PK_For_Validation (
80 x_sequence_number IN NUMBER
81 )return BOOLEAN as
82
83 CURSOR cur_rowid IS
84 SELECT rowid
85 FROM IGS_RU_RULE
86 WHERE sequence_number = x_sequence_number
87 FOR UPDATE NOWAIT;
88
89 lv_rowid cur_rowid%RowType;
90
91 BEGIN
92
93 Open cur_rowid;
94 Fetch cur_rowid INTO lv_rowid;
95 IF (cur_rowid%FOUND) THEN
96 Close cur_rowid;
97 Return(TRUE);
98 ELSE
99 Close cur_rowid;
100 Return(FALSE);
101 END IF;
102
103 END Get_PK_For_Validation;
104
105 PROCEDURE Before_DML (
106 p_action IN VARCHAR2,
107 x_rowid IN VARCHAR2 ,
108 x_sequence_number IN NUMBER ,
109 x_creation_date IN DATE ,
110 x_created_by IN NUMBER ,
111 x_last_update_date IN DATE ,
112 x_last_updated_by IN NUMBER ,
113 x_last_update_login IN NUMBER
114 ) as
115 BEGIN
116
117 Set_Column_Values (
118 p_action,
119 x_rowid,
120 x_sequence_number,
121 x_creation_date,
122 x_created_by,
123 x_last_update_date,
124 x_last_updated_by,
125 x_last_update_login
126 );
127
128
129 IF (p_action = 'INSERT') THEN
130
131 IF Get_PK_For_Validation (
132 new_references.sequence_number
133 ) THEN
134 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 Check_Constraints;
139 ELSIF (p_action = 'UPDATE') THEN
140 Check_Constraints;
141 ELSIF (p_action = 'DELETE') THEN
142 --
143 -- svenkata - This table handler is released as part of IGS specific forms in SEED . As a consequence ,
144 -- the procedure Check_Child_Existance originally a part of this package , has been moved to Igs_Ru_Gen_005 .
145 -- This was done 'cos Check_Child_Existance makes calls to other procedures which are not being shipped !
146 -- Check_Child_Existance is called only when the user is not DATAMERGE . Hence , the proc.
147 -- Check_Child_Existance_ru_rule is being called using execute immediate only if the user is not DATAMERGE .
148 -- Bug # 2233951
149 --
150 IF (fnd_global.user_id <> 1) THEN
151 -- do execute immediate
152 EXECUTE IMMEDIATE 'BEGIN Igs_Ru_Gen_005.Check_Child_Existance_ru_rule(:1); END;'
153 USING old_references.sequence_number;
154 END IF;
155 ELSIF (p_action = 'VALIDATE_INSERT') THEN
156 IF Get_PK_For_Validation (
157 new_references.sequence_number
158 ) THEN
159 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 Check_Constraints;
164 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
165 Check_Constraints;
166 ELSIF (p_action = 'VALIDATE_DELETE') THEN
167 --
168 -- svenkata - This table handler is released as part of IGS specific forms in SEED . As a consequence ,
169 -- the procedure Check_Child_Existance originally a part of this package , has been moved to Igs_Ru_Gen_005 .
170 -- This was done 'cos Check_Child_Existance makes calls to other procedures which are not being shipped !
171 -- Check_Child_Existance is called only when the user is not DATAMERGE . Hence , the proc.
172 -- Check_Child_Existance_ru_rule is being called using execute immediate only if the user is not DATAMERGE .
173 -- Bug # 2233951
174 --
175 IF (fnd_global.user_id <> 1) THEN
176 -- do execute immediate
177 EXECUTE IMMEDIATE 'BEGIN Igs_Ru_Gen_005.Check_Child_Existance_ru_rule(:1); END;'
178 USING old_references.sequence_number;
179 END IF;
180 END IF;
181
182 END Before_DML;
183
184 PROCEDURE After_DML (
185 p_action IN VARCHAR2,
186 x_rowid IN VARCHAR2
187 ) as
188 BEGIN
189
190 l_rowid := x_rowid;
191
192 END After_DML;
193
194 procedure INSERT_ROW (
195 X_ROWID in out NOCOPY VARCHAR2,
196 X_SEQUENCE_NUMBER in NUMBER,
197 X_MODE in VARCHAR2
198 ) as
199
200 --svenkata - The cursor C is being modified and cursor cur_max_plus_one is being created . This is to ensure that
201 -- when a user defined rule is created , it picks up a sequence number more than 50000 . Bug # 2233951
202 l_sequence_number NUMBER;
203 cursor C is select ROWID from IGS_RU_RULE
204 where SEQUENCE_NUMBER = L_SEQUENCE_NUMBER;
205
206
207 -- this cursor has been modified to lock the record so that parallel processing is avoided
208 -- rnirwani - 15.mar.02- 2233951
209 CURSOR cur_max_plus_one IS
210 SELECT (sequence_number + 1) sequence_number
211 FROM IGS_RU_RULE
212 WHERE sequence_number = (SELECT MAX(sequence_number) + 1 FROM igs_ru_rule
213 where sequence_number < 499999 ) FOR UPDATE OF sequence_number NOWAIT;
214
215 X_LAST_UPDATE_DATE DATE;
216 X_LAST_UPDATED_BY NUMBER;
217 X_LAST_UPDATE_LOGIN NUMBER;
218 begin
219 X_LAST_UPDATE_DATE := SYSDATE;
220 if(X_MODE = 'I') then
221 X_LAST_UPDATED_BY := 1;
222 X_LAST_UPDATE_LOGIN := 0;
223 elsif (X_MODE = 'R') then
224 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
225 if X_LAST_UPDATED_BY is NULL then
226 X_LAST_UPDATED_BY := -1;
227 end if;
228 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
229 if X_LAST_UPDATE_LOGIN is NULL then
230 X_LAST_UPDATE_LOGIN := -1;
231 end if;
232 else
233 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
234 IGS_GE_MSG_STACK.ADD;
235 app_exception.raise_exception;
236 end if;
237
238 Before_DML(
239 p_action=>'INSERT',
240 x_rowid=>X_ROWID,
241 x_sequence_number=>X_SEQUENCE_NUMBER,
242 x_creation_date=>X_LAST_UPDATE_DATE,
243 x_created_by=>X_LAST_UPDATED_BY,
244 x_last_update_date=>X_LAST_UPDATE_DATE,
245 x_last_updated_by=>X_LAST_UPDATED_BY,
246 x_last_update_login=>X_LAST_UPDATE_LOGIN
247 );
248 --
249 -- If the sequence number is passed as a NULL value then generate it.
250 --
251 IF (fnd_global.user_id = 1) THEN
252 --
253 -- If the sequence number is passed as a NULL value then generate it.
254 -- If the User creating this record is DATAMERGE (id = 1) then
255 -- Get the sequence as the existing maximum value + 1
256 --
257 IF (x_sequence_number IS NULL) THEN
258 OPEN cur_max_plus_one;
259 FETCH cur_max_plus_one INTO l_sequence_number;
260 CLOSE cur_max_plus_one;
261 ELSE
262 l_sequence_number := x_sequence_number;
263 END IF;
264 --
265 -- Seeded Sequences can go upto 499999 only else raise an error
266 --
267 IF (l_sequence_number > 499999) THEN
268 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception ;
271 END IF;
272 ELSE
273 l_sequence_number := NEW_REFERENCES.SEQUENCE_NUMBER;
274 END IF;
275
276 insert into IGS_RU_RULE (
277 SEQUENCE_NUMBER,
278 CREATION_DATE,
279 CREATED_BY,
280 LAST_UPDATE_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_LOGIN
283 ) values (
284 NEW_REFERENCES.SEQUENCE_NUMBER,
285 X_LAST_UPDATE_DATE,
286 X_LAST_UPDATED_BY,
287 X_LAST_UPDATE_DATE,
288 X_LAST_UPDATED_BY,
289 X_LAST_UPDATE_LOGIN
290 );
291
292 open c;
293 fetch c into X_ROWID;
294 if (c%notfound) then
295 close c;
296 raise no_data_found;
297 end if;
298 close c;
299
300 After_DML (
301 p_action => 'INSERT',
302 x_rowid => X_ROWID);
303
304 end INSERT_ROW;
305
306 procedure LOCK_ROW (
307 X_ROWID in VARCHAR2,
308 X_SEQUENCE_NUMBER in NUMBER
309 ) as
310 cursor c1 is select ROWID
311 from IGS_RU_RULE
312 where ROWID = X_ROWID for update nowait;
313 tlinfo c1%rowtype;
314
315 begin
316 open c1;
317 fetch c1 into tlinfo;
318 if (c1%notfound) then
319 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
320 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_RULE : P_ACTION LOCK_ROW : IGSUI11B.PLS');
321 IGS_GE_MSG_STACK.ADD;
322 app_exception.raise_exception;
323 close c1;
324 return;
325 end if;
326 close c1;
327
328 return;
329 end LOCK_ROW;
330
331 procedure DELETE_ROW (
332 X_ROWID in VARCHAR2
333 ) as
334 begin
335
336 Before_DML (
337 p_action => 'DELETE',
338 x_rowid => X_ROWID);
339
340 delete from IGS_RU_RULE
341 where ROWID = X_ROWID;
342 if (sql%notfound) then
343 raise no_data_found;
344 end if;
345
346 After_DML (
347 p_action => 'DELETE',
348 x_rowid => X_ROWID);
349
350 end DELETE_ROW;
351
352 end IGS_RU_RULE_PKG;
[Home] [Help]