[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_TURIN_FNC_PKG
Source
1 package body IGS_RU_TURIN_FNC_PKG as
2 /* $Header: IGSUI14B.pls 120.1 2005/09/16 06:18:30 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_TURIN_FNC%RowType;
6 new_references IGS_RU_TURIN_FNC%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_s_turin_function IN VARCHAR2 ,
12 x_rud_sequence_number IN NUMBER ,
13 x_parenthesis_ind IN VARCHAR2 ,
14 x_creation_date IN DATE ,
15 x_created_by IN NUMBER ,
16 x_last_update_date IN DATE ,
17 x_last_updated_by IN NUMBER ,
18 x_last_update_login IN NUMBER
19 ) as
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM IGS_RU_TURIN_FNC
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_TURIN_FNC : P_ACTION INSERT, VALIDATE_INSERT : IGSUI14B.PLS');
37 IGS_GE_MSG_STACK.ADD;
38 App_Exception.Raise_Exception;
39 Close cur_old_ref_values;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.s_turin_function := x_s_turin_function;
46 new_references.rud_sequence_number := x_rud_sequence_number;
47 new_references.parenthesis_ind := x_parenthesis_ind;
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
62 PROCEDURE Check_Constraints (
63 Column_Name IN VARCHAR2 ,
64 Column_Value IN VARCHAR2
65 )
66 as
67 BEGIN
68 IF column_name is null then
69 NULL;
70 ELSIF upper(Column_Name) = 'PARENTHESIS_IND' then
71 new_references.parenthesis_ind := column_value;
72 ELSIF upper(Column_name) = 'RUD_SEQUENCE_NUMBER' Then
73 new_references.rud_sequence_number := igs_ge_number.to_num(column_value);
74 END IF;
75 IF upper(Column_Name) = 'PARENTHESIS_IND' OR Column_Name IS NULL THEN
76 IF new_references.parenthesis_ind NOT IN ('Y','N') THEN
77 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
78 IGS_GE_MSG_STACK.ADD;
79 App_Exception.Raise_Exception;
80 END IF;
81 END IF;
82 IF upper(Column_Name) = 'RUD_SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
83 IF new_references.rud_sequence_number < 1 OR new_references.rud_sequence_number > 999999 THEN
84 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
85 IGS_GE_MSG_STACK.ADD;
86 App_Exception.Raise_Exception;
87 END IF;
88 END IF;
89
90 END Check_Constraints;
91
92 PROCEDURE Check_Parent_Existance as
93 BEGIN
94
95 IF (((old_references.rud_sequence_number = new_references.rud_sequence_number)) OR
96 ((new_references.rud_sequence_number IS NULL))) THEN
97 NULL;
98 ELSE
99 IF NOT IGS_RU_DESCRIPTION_PKG.Get_PK_For_Validation (
100 new_references.rud_sequence_number
101 ) THEN
102 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
103 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_DESCRIPTION : P_ACTION Check_Parent_Existance rud_sequence_number : IGSUI14B.PLS');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 END IF;
108
109 END Check_Parent_Existance;
110
111 PROCEDURE Check_Child_Existance as
112 BEGIN
113
114 IGS_RU_DESCRIPTION_PKG.GET_FK_IGS_RU_TURIN_FNC (
115 old_references.s_turin_function
116 );
117
118 IGS_RU_ITEM_PKG.GET_FK_IGS_RU_TURIN_FNC (
119 old_references.s_turin_function
120 );
121
122 END Check_Child_Existance;
123
124 FUNCTION Get_PK_For_Validation (
125 x_s_turin_function IN VARCHAR2
126 )return BOOLEAN as
127
128 CURSOR cur_rowid IS
129 SELECT rowid
130 FROM IGS_RU_TURIN_FNC
131 WHERE s_turin_function = x_s_turin_function
132 FOR UPDATE;
133
134 lv_rowid cur_rowid%RowType;
135
136 BEGIN
137
138 Open cur_rowid;
139 Fetch cur_rowid INTO lv_rowid;
140 IF (cur_rowid%FOUND) THEN
141 Close cur_rowid;
142 Return(TRUE);
143 ELSE
144 Close cur_rowid;
145 Return(FALSE);
146 END IF;
147
148 END Get_PK_For_Validation;
149
150 PROCEDURE GET_FK_IGS_RU_DESCRIPTION (
151 x_sequence_number IN NUMBER
152 ) as
153
154 CURSOR cur_rowid IS
155 SELECT rowid
156 FROM IGS_RU_TURIN_FNC
157 WHERE rud_sequence_number = x_sequence_number ;
158
159 lv_rowid cur_rowid%RowType;
160
161 BEGIN
162
163 Open cur_rowid;
164 Fetch cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 Close cur_rowid;
167 Fnd_Message.Set_Name ('IGS', 'IGS_RU_STF_RUD_FK');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 Return;
171 END IF;
172 Close cur_rowid;
173
174 END GET_FK_IGS_RU_DESCRIPTION;
175
176 PROCEDURE Before_DML (
177 p_action IN VARCHAR2,
178 x_rowid IN VARCHAR2 ,
179 x_s_turin_function IN VARCHAR2 ,
180 x_rud_sequence_number IN NUMBER ,
181 x_parenthesis_ind IN VARCHAR2 ,
182 x_creation_date IN DATE ,
183 x_created_by IN NUMBER ,
184 x_last_update_date IN DATE ,
185 x_last_updated_by IN NUMBER ,
186 x_last_update_login IN NUMBER
187 ) as
188 BEGIN
189 Set_Column_Values (
190 p_action,
191 x_rowid,
192 x_s_turin_function,
193 x_rud_sequence_number,
194 x_parenthesis_ind,
195 x_creation_date,
196 x_created_by,
197 x_last_update_date,
198 x_last_updated_by,
199 x_last_update_login
200 );
201
202 IF (p_action = 'INSERT') THEN
203
204 IF Get_PK_For_Validation (
205 new_references.s_turin_function
206 ) THEN
207 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
208 IGS_GE_MSG_STACK.ADD;
209 App_Exception.Raise_Exception;
210 END IF;
211 Check_Constraints;
212 Check_Parent_Existance;
213 ELSIF (p_action = 'UPDATE') THEN
214 Check_Constraints;
215 Check_Parent_Existance;
216 ELSIF (p_action = 'DELETE') THEN
217 Check_Child_Existance;
218 ELSIF (p_action = 'VALIDATE_INSERT') THEN
219 IF Get_PK_For_Validation (
220 new_references.s_turin_function
221 ) THEN
222 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 END IF;
226 Check_Constraints;
227 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
228 Check_Constraints;
229 ELSIF (p_action = 'VALIDATE_DELETE') THEN
230 Check_Child_Existance;
231 END IF;
232
233 END Before_DML;
234
235 PROCEDURE After_DML (
236 p_action IN VARCHAR2,
237 x_rowid IN VARCHAR2
238 ) as
239 BEGIN
240
241 l_rowid := x_rowid;
242
243 END After_DML;
244
245
246 procedure INSERT_ROW (
247 X_ROWID in out NOCOPY VARCHAR2,
248 X_S_TURIN_FUNCTION in VARCHAR2,
249 X_RUD_SEQUENCE_NUMBER in NUMBER,
250 X_PARENTHESIS_IND in VARCHAR2,
251 X_MODE in VARCHAR2
252 ) as
253 cursor C is select ROWID from IGS_RU_TURIN_FNC
254 where S_TURIN_FUNCTION = X_S_TURIN_FUNCTION;
255 X_LAST_UPDATE_DATE DATE;
256 X_LAST_UPDATED_BY NUMBER;
257 X_LAST_UPDATE_LOGIN NUMBER;
258 begin
259 X_LAST_UPDATE_DATE := SYSDATE;
260 if(X_MODE = 'I') then
261 X_LAST_UPDATED_BY := 1;
262 X_LAST_UPDATE_LOGIN := 0;
263 elsif (X_MODE = 'R') then
264 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
265 if X_LAST_UPDATED_BY is NULL then
266 X_LAST_UPDATED_BY := -1;
267 end if;
268 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
269 if X_LAST_UPDATE_LOGIN is NULL then
270 X_LAST_UPDATE_LOGIN := -1;
271 end if;
272 else
273 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
274 IGS_GE_MSG_STACK.ADD;
275 app_exception.raise_exception;
276 end if;
277
278 Before_DML(
279 p_action=>'INSERT',
280 x_rowid=>X_ROWID,
281 x_parenthesis_ind=>X_PARENTHESIS_IND,
282 x_rud_sequence_number=>X_RUD_SEQUENCE_NUMBER,
283 x_s_turin_function=>X_S_TURIN_FUNCTION,
284 x_creation_date=>X_LAST_UPDATE_DATE,
285 x_created_by=>X_LAST_UPDATED_BY,
286 x_last_update_date=>X_LAST_UPDATE_DATE,
287 x_last_updated_by=>X_LAST_UPDATED_BY,
288 x_last_update_login=>X_LAST_UPDATE_LOGIN
289 );
290
291 insert into IGS_RU_TURIN_FNC (
292 S_TURIN_FUNCTION,
293 RUD_SEQUENCE_NUMBER,
294 PARENTHESIS_IND,
295 CREATION_DATE,
296 CREATED_BY,
297 LAST_UPDATE_DATE,
298 LAST_UPDATED_BY,
299 LAST_UPDATE_LOGIN
300 ) values (
301 NEW_REFERENCES.S_TURIN_FUNCTION,
302 NEW_REFERENCES.RUD_SEQUENCE_NUMBER,
303 NEW_REFERENCES.PARENTHESIS_IND,
304 X_LAST_UPDATE_DATE,
305 X_LAST_UPDATED_BY,
306 X_LAST_UPDATE_DATE,
307 X_LAST_UPDATED_BY,
308 X_LAST_UPDATE_LOGIN
309 );
310
311 open c;
312 fetch c into X_ROWID;
313 if (c%notfound) then
314 close c;
315 raise no_data_found;
316 end if;
317 close c;
318
319 After_DML (
320 p_action => 'INSERT',
321 x_rowid => X_ROWID);
322
323 end INSERT_ROW;
324
325 procedure LOCK_ROW (
326 X_ROWID in VARCHAR2,
327 X_S_TURIN_FUNCTION in VARCHAR2,
328 X_RUD_SEQUENCE_NUMBER in NUMBER,
329 X_PARENTHESIS_IND in VARCHAR2
330 ) as
331 cursor c1 is select
332 RUD_SEQUENCE_NUMBER,
333 PARENTHESIS_IND
334 from IGS_RU_TURIN_FNC
335 where ROWID = X_ROWID for update nowait;
336 tlinfo c1%rowtype;
337
338 begin
339 open c1;
340 fetch c1 into tlinfo;
341 if (c1%notfound) then
342 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
343 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_TURIN_FNC : P_ACTION LOCK_ROW : IGSUI14B.PLS');
344 IGS_GE_MSG_STACK.ADD;
345 app_exception.raise_exception;
346 close c1;
347 return;
348 end if;
349 close c1;
350
351 if ( (tlinfo.RUD_SEQUENCE_NUMBER = X_RUD_SEQUENCE_NUMBER)
352 AND ((tlinfo.PARENTHESIS_IND = X_PARENTHESIS_IND)
353 OR ((tlinfo.PARENTHESIS_IND is null)
354 AND (X_PARENTHESIS_IND is null)))
355 ) then
356 null;
357 else
358 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
359 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_TURIN_FNC : P_ACTION LOCK_ROW FORM_RECORD_CHANGED : IGSUI14B.PLS');
360 IGS_GE_MSG_STACK.ADD;
361 app_exception.raise_exception;
362 end if;
363 return;
364 end LOCK_ROW;
365
366 procedure UPDATE_ROW (
367 X_ROWID in VARCHAR2,
368 X_S_TURIN_FUNCTION in VARCHAR2,
369 X_RUD_SEQUENCE_NUMBER in NUMBER,
370 X_PARENTHESIS_IND in VARCHAR2,
371 X_MODE in VARCHAR2
372 ) as
373 X_LAST_UPDATE_DATE DATE;
374 X_LAST_UPDATED_BY NUMBER;
375 X_LAST_UPDATE_LOGIN NUMBER;
376 begin
377 X_LAST_UPDATE_DATE := SYSDATE;
378 if(X_MODE = 'I') then
379 X_LAST_UPDATED_BY := 1;
380 X_LAST_UPDATE_LOGIN := 0;
381 elsif (X_MODE = 'R') then
382 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
383 if X_LAST_UPDATED_BY is NULL then
384 X_LAST_UPDATED_BY := -1;
385 end if;
386 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
387 if X_LAST_UPDATE_LOGIN is NULL then
388 X_LAST_UPDATE_LOGIN := -1;
389 end if;
390 else
391 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
392 IGS_GE_MSG_STACK.ADD;
393 app_exception.raise_exception;
394 end if;
395
396 Before_DML(
397 p_action=>'UPDATE',
398 x_rowid=>X_ROWID,
399 x_parenthesis_ind=>X_PARENTHESIS_IND,
400 x_rud_sequence_number=>X_RUD_SEQUENCE_NUMBER,
401 x_s_turin_function=>X_S_TURIN_FUNCTION,
402 x_creation_date=>X_LAST_UPDATE_DATE,
403 x_created_by=>X_LAST_UPDATED_BY,
404 x_last_update_date=>X_LAST_UPDATE_DATE,
405 x_last_updated_by=>X_LAST_UPDATED_BY,
406 x_last_update_login=>X_LAST_UPDATE_LOGIN
407 );
408
409 update IGS_RU_TURIN_FNC set
410 RUD_SEQUENCE_NUMBER = NEW_REFERENCES.RUD_SEQUENCE_NUMBER,
411 PARENTHESIS_IND = NEW_REFERENCES.PARENTHESIS_IND,
412 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
413 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
414 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
415 where ROWID = X_ROWID
416 ;
417 if (sql%notfound) then
418 raise no_data_found;
419 end if;
420
421 After_DML (
422 p_action => 'UPDATE',
423 x_rowid => X_ROWID);
424
425 end UPDATE_ROW;
426
427 procedure ADD_ROW (
428 X_ROWID in out NOCOPY VARCHAR2,
429 X_S_TURIN_FUNCTION in VARCHAR2,
430 X_RUD_SEQUENCE_NUMBER in NUMBER,
431 X_PARENTHESIS_IND in VARCHAR2,
432 X_MODE in VARCHAR2
433 ) as
434 cursor c1 is select rowid from IGS_RU_TURIN_FNC
435 where S_TURIN_FUNCTION = X_S_TURIN_FUNCTION
436 ;
437 begin
438 open c1;
439 fetch c1 into X_ROWID;
440 if (c1%notfound) then
441 close c1;
442 INSERT_ROW (
443 X_ROWID,
444 X_S_TURIN_FUNCTION,
445 X_RUD_SEQUENCE_NUMBER,
446 X_PARENTHESIS_IND,
447 X_MODE);
448 return;
449 end if;
450 close c1;
451 UPDATE_ROW (
452 X_ROWID,
453 X_S_TURIN_FUNCTION,
454 X_RUD_SEQUENCE_NUMBER,
455 X_PARENTHESIS_IND,
456 X_MODE);
457 end ADD_ROW;
458
459 procedure DELETE_ROW (
460 X_ROWID in VARCHAR2
461 ) as
462 begin
463
464 Before_DML (
465 p_action => 'DELETE',
466 x_rowid => X_ROWID);
467
468 delete from IGS_RU_TURIN_FNC
469 where ROWID = X_ROWID;
470 if (sql%notfound) then
471 raise no_data_found;
472 end if;
473
474 After_DML (
475 p_action => 'DELETE',
476 x_rowid => X_ROWID);
477
478 end DELETE_ROW;
479
480 end IGS_RU_TURIN_FNC_PKG;