[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_WORK_SET_PKG
Source
1 package body IGS_RU_WORK_SET_PKG as
2 /* $Header: IGSUI15B.pls 115.7 2002/11/29 04:28:55 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_WORK_SET%RowType;
6 new_references IGS_RU_WORK_SET%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_WORK_SET
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
34 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_WORK_SET : P_ACTION INSERT, VALIDATE_INSERT : IGSUI15B.PLS');
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
69 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
70 IF new_references.sequence_number < 0 OR new_references.sequence_number > 999999 THEN
71 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
72 IGS_GE_MSG_STACK.ADD;
73 App_Exception.Raise_Exception;
74 END IF;
75 END IF;
76
77 END Check_Constraints;
78
79 PROCEDURE Check_Child_Existance as
80 BEGIN
81
82 IGS_RU_WORK_SET_MBR_PKG.GET_FK_IGS_RU_WORK_SET (
83 old_references.sequence_number
84 );
85
86 END Check_Child_Existance;
87
88 FUNCTION Get_PK_For_Validation (
89 x_sequence_number IN NUMBER
90 )return BOOLEAN as
91
92 CURSOR cur_rowid IS
93 SELECT rowid
94 FROM IGS_RU_WORK_SET
95 WHERE sequence_number = x_sequence_number
96 FOR UPDATE NOWAIT;
97
98 lv_rowid cur_rowid%RowType;
99
100 BEGIN
101
102 Open cur_rowid;
103 Fetch cur_rowid INTO lv_rowid;
104 IF (cur_rowid%FOUND) THEN
105 Close cur_rowid;
106 Return(TRUE);
107 ELSE
108 Close cur_rowid;
109 Return(FALSE);
110 END IF;
111
112 END Get_PK_For_Validation;
113
114
115 PROCEDURE Before_DML (
116 p_action IN VARCHAR2,
117 x_rowid IN VARCHAR2 ,
118 x_sequence_number IN NUMBER ,
119 x_creation_date IN DATE ,
120 x_created_by IN NUMBER ,
121 x_last_update_date IN DATE ,
122 x_last_updated_by IN NUMBER ,
123 x_last_update_login IN NUMBER
124 ) as
125 BEGIN
126 Set_Column_Values (
127 p_action,
128 x_rowid,
129 x_sequence_number,
130 x_creation_date,
131 x_created_by,
132 x_last_update_date,
133 x_last_updated_by,
134 x_last_update_login
135 );
136
137 IF (p_action = 'INSERT') THEN
138
139 IF Get_PK_For_Validation (
140 new_references.sequence_number
141 ) THEN
142 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 Check_Constraints;
147 ELSIF (p_action = 'UPDATE') THEN
148 Check_Constraints;
149 ELSIF (p_action = 'DELETE') THEN
150 Check_Child_Existance;
151 ELSIF (p_action = 'VALIDATE_INSERT') THEN
152 IF Get_PK_For_Validation (
153 new_references.sequence_number
154 ) THEN
155 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END IF;
159 Check_Constraints;
160 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
161 Check_Constraints;
162 ELSIF (p_action = 'VALIDATE_DELETE') THEN
163 Check_Child_Existance;
164 END IF;
165 END Before_DML;
166
167 PROCEDURE After_DML (
168 p_action IN VARCHAR2,
169 x_rowid IN VARCHAR2
170 ) as
171 BEGIN
172
173 l_rowid := x_rowid;
174
175 END After_DML;
176
177 procedure INSERT_ROW (
178 X_ROWID in out NOCOPY VARCHAR2,
179 X_SEQUENCE_NUMBER in NUMBER,
180 X_MODE in VARCHAR2
181 ) as
182 cursor C is select ROWID from IGS_RU_WORK_SET
183 where SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
184 X_LAST_UPDATE_DATE DATE;
185 X_LAST_UPDATED_BY NUMBER;
186 X_LAST_UPDATE_LOGIN NUMBER;
187 X_REQUEST_ID NUMBER;
188 X_PROGRAM_ID NUMBER;
189 X_PROGRAM_APPLICATION_ID NUMBER;
190 X_PROGRAM_UPDATE_DATE DATE;
191 begin
192 X_LAST_UPDATE_DATE := SYSDATE;
193 if(X_MODE = 'I') then
194 X_LAST_UPDATED_BY := 1;
195 X_LAST_UPDATE_LOGIN := 0;
196 elsif (X_MODE = 'R') then
197 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
198 if X_LAST_UPDATED_BY is NULL then
199 X_LAST_UPDATED_BY := -1;
200 end if;
201 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
202 if X_LAST_UPDATE_LOGIN is NULL then
203 X_LAST_UPDATE_LOGIN := -1;
204 end if;
205 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
206 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
207 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
208 if (X_REQUEST_ID = -1) then
209 X_REQUEST_ID := NULL;
210 X_PROGRAM_ID := NULL;
211 X_PROGRAM_APPLICATION_ID := NULL;
212 X_PROGRAM_UPDATE_DATE := NULL;
213 else
214 X_PROGRAM_UPDATE_DATE := SYSDATE;
215 end if;
216 else
217 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
218 IGS_GE_MSG_STACK.ADD;
219 app_exception.raise_exception;
220 end if;
221
222 Before_DML(
223 p_action=>'INSERT',
224 x_rowid=>X_ROWID,
225 x_sequence_number=>X_SEQUENCE_NUMBER,
226 x_creation_date=>X_LAST_UPDATE_DATE,
227 x_created_by=>X_LAST_UPDATED_BY,
228 x_last_update_date=>X_LAST_UPDATE_DATE,
229 x_last_updated_by=>X_LAST_UPDATED_BY,
230 x_last_update_login=>X_LAST_UPDATE_LOGIN
231 );
232
233 insert into IGS_RU_WORK_SET (
234 SEQUENCE_NUMBER,
235 CREATION_DATE,
236 CREATED_BY,
237 LAST_UPDATE_DATE,
238 LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN,
240 REQUEST_ID,
241 PROGRAM_ID,
242 PROGRAM_APPLICATION_ID,
243 PROGRAM_UPDATE_DATE
244 ) values (
245 NEW_REFERENCES.SEQUENCE_NUMBER,
246 X_LAST_UPDATE_DATE,
247 X_LAST_UPDATED_BY,
248 X_LAST_UPDATE_DATE,
249 X_LAST_UPDATED_BY,
250 X_LAST_UPDATE_LOGIN,
251 X_REQUEST_ID,
252 X_PROGRAM_ID,
253 X_PROGRAM_APPLICATION_ID,
254 X_PROGRAM_UPDATE_DATE
255 );
256
257 open c;
258 fetch c into X_ROWID;
259 if (c%notfound) then
260 close c;
261 raise no_data_found;
262 end if;
263 close c;
264
265 After_DML (
266 p_action => 'INSERT',
267 x_rowid => X_ROWID);
268
269 end INSERT_ROW;
270
271 procedure LOCK_ROW (
272 X_ROWID in VARCHAR2,
273 X_SEQUENCE_NUMBER in NUMBER
274 ) as
275 cursor c1 is select ROWID
276 from IGS_RU_WORK_SET
277 where ROWID = X_ROWID for update nowait;
278 tlinfo c1%rowtype;
279
280 begin
281 open c1;
282 fetch c1 into tlinfo;
283 if (c1%notfound) then
284 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
285 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_WORK_SET : P_ACTION LOCK_ROW : IGSUI15B.PLS');
286 IGS_GE_MSG_STACK.ADD;
287 app_exception.raise_exception;
288 close c1;
289 return;
290 end if;
291 close c1;
292
293 return;
294 end LOCK_ROW;
295
296 procedure DELETE_ROW (
297 X_ROWID in VARCHAR2
298 ) as
299 begin
300
301 Before_DML (
302 p_action => 'DELETE',
303 x_rowid => X_ROWID);
304
305 delete from IGS_RU_WORK_SET
306 where ROWID = X_ROWID;
307 if (sql%notfound) then
308 raise no_data_found;
309 end if;
310
311 After_DML (
312 p_action => 'DELETE',
313 x_rowid => X_ROWID);
314
315 end DELETE_ROW;
316
317 end IGS_RU_WORK_SET_PKG;