[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_WORK_SET_MBR_PKG
Source
1 package body IGS_RU_WORK_SET_MBR_PKG as
2 /* $Header: IGSUI16B.pls 115.6 2002/11/29 04:29:12 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_WORK_SET_MBR%RowType;
6 new_references IGS_RU_WORK_SET_MBR%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_rws_sequence_number IN NUMBER ,
12 x_unit_cd IN VARCHAR2 ,
13 x_version_number IN VARCHAR2 ,
14 x_cal_type IN VARCHAR2 ,
15 x_ci_sequence_number IN VARCHAR2 ,
16 x_creation_date IN DATE ,
17 x_created_by IN NUMBER ,
18 x_last_update_date IN DATE ,
19 x_last_updated_by IN NUMBER ,
20 x_last_update_login IN NUMBER
21 ) as
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_RU_WORK_SET_MBR
26 WHERE rowid = x_rowid;
27
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_WORK_SET : P_ACTION INSERT, VALIDATE_INSERT : IGSUI16B.PLS');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Close cur_old_ref_values;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.rws_sequence_number := x_rws_sequence_number;
48 new_references.unit_cd := x_unit_cd;
49 new_references.version_number := x_version_number;
50 new_references.cal_type := x_cal_type;
51 new_references.ci_sequence_number := x_ci_sequence_number;
52 IF (p_action = 'UPDATE') THEN
53 new_references.creation_date := old_references.creation_date;
54 new_references.created_by := old_references.created_by;
55 ELSE
56 new_references.creation_date := x_creation_date;
57 new_references.created_by := x_created_by;
58 END IF;
59 new_references.last_update_date := x_last_update_date;
60 new_references.last_updated_by := x_last_updated_by;
61 new_references.last_update_login := x_last_update_login;
62
63 END Set_Column_Values;
64
65 PROCEDURE Check_Parent_Existance as
66 BEGIN
67
68
69 IF (((old_references.rws_sequence_number = new_references.rws_sequence_number)) OR
70 ((new_references.rws_sequence_number IS NULL))) THEN
71 NULL;
72 ELSE
73 IF NOT IGS_RU_WORK_SET_PKG.Get_PK_For_Validation (
74 new_references.rws_sequence_number
75 ) THEN
76 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
77 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_WORK_SET : P_ACTION Check_Parent_Existance rws_sequence_number: IGSUI16B.PLS');
78 IGS_GE_MSG_STACK.ADD;
79 App_Exception.Raise_Exception;
80 END IF;
81 END IF;
82
83 END Check_Parent_Existance;
84
85 FUNCTION Get_PK_For_Validation (
86 x_rws_sequence_number IN NUMBER,
87 x_unit_cd IN VARCHAR2,
88 x_version_number IN VARCHAR2,
89 x_cal_type IN VARCHAR2,
90 x_ci_sequence_number IN VARCHAR2
91 )return BOOLEAN as
92
93 CURSOR cur_rowid IS
94 SELECT rowid
95 FROM IGS_RU_WORK_SET_MBR
96 WHERE rws_sequence_number = x_rws_sequence_number
97 AND unit_cd = x_unit_cd
98 AND version_number = x_version_number
99 AND cal_type = x_cal_type
100 AND ci_sequence_number = x_ci_sequence_number
101 FOR UPDATE NOWAIT;
102
103 lv_rowid cur_rowid%RowType;
104
105 BEGIN
106
107 Open cur_rowid;
108 Fetch cur_rowid INTO lv_rowid;
109 IF (cur_rowid%FOUND) THEN
110 Close cur_rowid;
111 Return(TRUE);
112 ELSE
113 Close cur_rowid;
114 Return(FALSE);
115 END IF;
116
117 END Get_PK_For_Validation;
118
119 PROCEDURE GET_FK_IGS_RU_WORK_SET (
120 x_sequence_number IN NUMBER
121 ) as
122
123 CURSOR cur_rowid IS
124 SELECT rowid
125 FROM IGS_RU_WORK_SET_MBR
126 WHERE rws_sequence_number = x_sequence_number ;
127
128 lv_rowid cur_rowid%RowType;
129
130 BEGIN
131
132 Open cur_rowid;
133 Fetch cur_rowid INTO lv_rowid;
134 IF (cur_rowid%FOUND) THEN
135 Close cur_rowid;
136 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RWSM_RWS_FK');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 Return;
140 END IF;
141 Close cur_rowid;
142
143 END GET_FK_IGS_RU_WORK_SET;
144
145 PROCEDURE Before_DML (
146 p_action IN VARCHAR2,
147 x_rowid IN VARCHAR2,
148 x_rws_sequence_number IN NUMBER ,
149 x_unit_cd IN VARCHAR2 ,
150 x_version_number IN VARCHAR2 ,
151 x_cal_type IN VARCHAR2 ,
152 x_ci_sequence_number IN VARCHAR2 ,
153 x_creation_date IN DATE ,
154 x_created_by IN NUMBER ,
155 x_last_update_date IN DATE ,
156 x_last_updated_by IN NUMBER ,
157 x_last_update_login IN NUMBER
158 ) as
159 BEGIN
160
161 Set_Column_Values (
162 p_action,
163 x_rowid,
164 x_rws_sequence_number,
165 x_unit_cd,
166 x_version_number,
167 x_cal_type,
168 x_ci_sequence_number,
169 x_creation_date,
170 x_created_by,
171 x_last_update_date,
172 x_last_updated_by,
173 x_last_update_login
174 );
175
176 IF (p_action = 'INSERT') THEN
177 IF Get_PK_For_Validation (
178 new_references.rws_sequence_number,
179 new_references.unit_cd,
180 new_references.version_number,
181 new_references.cal_type,
182 new_references.ci_sequence_number
183 ) THEN
184 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188 Check_Parent_Existance;
189 ELSIF (p_action = 'UPDATE') THEN
190 Check_Parent_Existance;
191 ELSIF (p_action = 'VALIDATE_INSERT') THEN
192 IF Get_PK_For_Validation (
193 new_references.rws_sequence_number,
194 new_references.unit_cd,
195 new_references.version_number,
196 new_references.cal_type,
197 new_references.ci_sequence_number
198 ) THEN
199 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203 END IF;
204 END Before_DML;
205
206 PROCEDURE After_DML (
207 p_action IN VARCHAR2,
208 x_rowid IN VARCHAR2
209 ) as
210 BEGIN
211
212 l_rowid := x_rowid;
213
214 END After_DML;
215
216
217 procedure INSERT_ROW (
218 X_ROWID in out NOCOPY VARCHAR2,
219 X_RWS_SEQUENCE_NUMBER in NUMBER,
220 X_UNIT_CD in VARCHAR2,
221 X_VERSION_NUMBER in VARCHAR2,
222 X_CAL_TYPE in VARCHAR2,
223 X_CI_SEQUENCE_NUMBER in VARCHAR2,
224 X_MODE in VARCHAR2
225 ) as
226 cursor C is select ROWID from IGS_RU_WORK_SET_MBR
227 where RWS_SEQUENCE_NUMBER = X_RWS_SEQUENCE_NUMBER
228 and UNIT_CD = X_UNIT_CD
229 and VERSION_NUMBER = X_VERSION_NUMBER
230 and CAL_TYPE = X_CAL_TYPE
231 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER;
232 X_LAST_UPDATE_DATE DATE;
233 X_LAST_UPDATED_BY NUMBER;
234 X_LAST_UPDATE_LOGIN NUMBER;
235 X_REQUEST_ID NUMBER;
236 X_PROGRAM_ID NUMBER;
237 X_PROGRAM_APPLICATION_ID NUMBER;
238 X_PROGRAM_UPDATE_DATE DATE;
239 begin
240 X_LAST_UPDATE_DATE := SYSDATE;
241 if(X_MODE = 'I') then
242 X_LAST_UPDATED_BY := 1;
243 X_LAST_UPDATE_LOGIN := 0;
244 elsif (X_MODE = 'R') then
245 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
246 if X_LAST_UPDATED_BY is NULL then
247 X_LAST_UPDATED_BY := -1;
248 end if;
249 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
250 if X_LAST_UPDATE_LOGIN is NULL then
251 X_LAST_UPDATE_LOGIN := -1;
252 end if;
253 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
254 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
255 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
256 if (X_REQUEST_ID = -1) then
257 X_REQUEST_ID := NULL;
258 X_PROGRAM_ID := NULL;
259 X_PROGRAM_APPLICATION_ID := NULL;
260 X_PROGRAM_UPDATE_DATE := NULL;
261 else
262 X_PROGRAM_UPDATE_DATE := SYSDATE;
263 end if;
264 else
265 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
266 IGS_GE_MSG_STACK.ADD;
267 app_exception.raise_exception;
268 end if;
269
270 Before_DML(
271 p_action=>'INSERT',
272 x_rowid=>X_ROWID,
273 x_cal_type=>X_CAL_TYPE,
274 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
275 x_rws_sequence_number=>X_RWS_SEQUENCE_NUMBER,
276 x_unit_cd=>X_UNIT_CD,
277 x_version_number=>X_VERSION_NUMBER,
278 x_creation_date=>X_LAST_UPDATE_DATE,
279 x_created_by=>X_LAST_UPDATED_BY,
280 x_last_update_date=>X_LAST_UPDATE_DATE,
281 x_last_updated_by=>X_LAST_UPDATED_BY,
282 x_last_update_login=>X_LAST_UPDATE_LOGIN
283 );
284
285 insert into IGS_RU_WORK_SET_MBR (
286 RWS_SEQUENCE_NUMBER,
287 UNIT_CD,
288 VERSION_NUMBER,
289 CAL_TYPE,
290 CI_SEQUENCE_NUMBER,
291 CREATION_DATE,
292 CREATED_BY,
293 LAST_UPDATE_DATE,
294 LAST_UPDATED_BY,
295 LAST_UPDATE_LOGIN,
296 REQUEST_ID,
297 PROGRAM_ID,
298 PROGRAM_APPLICATION_ID,
299 PROGRAM_UPDATE_DATE
300 ) values (
301 NEW_REFERENCES.RWS_SEQUENCE_NUMBER,
302 NEW_REFERENCES.UNIT_CD,
303 NEW_REFERENCES.VERSION_NUMBER,
304 NEW_REFERENCES.CAL_TYPE,
305 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
306 X_LAST_UPDATE_DATE,
307 X_LAST_UPDATED_BY,
308 X_LAST_UPDATE_DATE,
309 X_LAST_UPDATED_BY,
310 X_LAST_UPDATE_LOGIN,
311 X_REQUEST_ID,
312 X_PROGRAM_ID,
313 X_PROGRAM_APPLICATION_ID,
314 X_PROGRAM_UPDATE_DATE
315 );
316
317 open c;
318 fetch c into X_ROWID;
319 if (c%notfound) then
320 close c;
321 raise no_data_found;
322 end if;
323 close c;
324
325 After_DML (
326 p_action => 'UPDATE',
327 x_rowid => X_ROWID);
328
329 end INSERT_ROW;
330
331 procedure LOCK_ROW (
332 X_ROWID in VARCHAR2,
333 X_RWS_SEQUENCE_NUMBER in NUMBER,
334 X_UNIT_CD in VARCHAR2,
335 X_VERSION_NUMBER in VARCHAR2,
336 X_CAL_TYPE in VARCHAR2,
337 X_CI_SEQUENCE_NUMBER in VARCHAR2
338 ) as
339 cursor c1 is select ROWID
340 from IGS_RU_WORK_SET_MBR
341 where ROWID = X_ROWID for update nowait;
342 tlinfo c1%rowtype;
343
344 begin
345 open c1;
346 fetch c1 into tlinfo;
347 if (c1%notfound) then
348 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
349 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_WORK_SET : P_ACTION LOCK_ROW : IGSUI16B.PLS');
350 IGS_GE_MSG_STACK.ADD;
351 app_exception.raise_exception;
352 close c1;
353 return;
354
355 end if;
356 close c1;
357
358 return;
359 end LOCK_ROW;
360
361 procedure DELETE_ROW (
362 X_ROWID in VARCHAR2
363 ) as
364 begin
365
366
367 Before_DML (
368 p_action => 'DELETE',
369 x_rowid => X_ROWID);
370
371 delete from IGS_RU_WORK_SET_MBR
372 where ROWID = X_ROWID;
373 if (sql%notfound) then
374 raise no_data_found;
375 end if;
376
377 After_DML (
378 p_action => 'DELETE',
379 x_rowid => X_ROWID);
380
381 end DELETE_ROW;
382
383 end IGS_RU_WORK_SET_MBR_PKG;