[Home] [Help]
PACKAGE BODY: APPS.IGS_TR_GROUP_PKG
Source
1 PACKAGE BODY igs_tr_group_pkg AS
2 /* $Header: IGSTI09B.pls 120.1 2005/09/08 15:41:58 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_tr_group_all%ROWTYPE;
6 new_references igs_tr_group_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_tracking_group_id IN NUMBER DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_creation_date IN DATE DEFAULT NULL,
14 x_created_by IN NUMBER DEFAULT NULL,
15 x_last_update_date IN DATE DEFAULT NULL,
16 x_last_updated_by IN NUMBER DEFAULT NULL,
17 x_last_update_login IN NUMBER DEFAULT NULL,
18 x_org_id IN NUMBER DEFAULT NULL
19 ) AS
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM igs_tr_group_all
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_ge_msg_stack.add;
37 app_exception.raise_exception;
38 CLOSE cur_old_ref_values;
39 RETURN;
40 END IF;
41 CLOSE cur_old_ref_values;
42
43 -- Populate New Values.
44 new_references.tracking_group_id := x_tracking_group_id;
45 new_references.description := x_description;
46 new_references.org_id := x_org_id;
47
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 DEFAULT NULL,
64 column_value IN VARCHAR2 DEFAULT NULL
65 )AS
66 BEGIN
67
68 IF column_name IS NULL THEN
69 NULL;
70
71 ELSIF UPPER(column_name) = 'TRACKING_GROUP_ID' THEN
72 new_references.tracking_group_id:= igs_ge_number.to_num(column_value) ;
73 END IF ;
74
75 IF new_references.tracking_group_id < 1 OR new_references.tracking_group_id > 999999999 THEN
76 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
77 igs_ge_msg_stack.add;
78 app_exception.raise_exception ;
79 END IF;
80
81 END check_constraints;
82
83 PROCEDURE check_child_existance AS
84 BEGIN
85
86 igs_tr_group_member_pkg.get_fk_igs_tr_group (
87 old_references.tracking_group_id
88 );
89
90 igs_tr_group_note_pkg.get_fk_igs_tr_group (
91 old_references.tracking_group_id
92 );
93
94 END check_child_existance;
95
96 FUNCTION get_pk_for_validation (
97 x_tracking_group_id IN NUMBER
98 ) RETURN BOOLEAN AS
99
100 CURSOR cur_rowid IS
101 SELECT ROWID
102 FROM igs_tr_group_all
103 WHERE tracking_group_id = x_tracking_group_id
104 FOR UPDATE NOWAIT;
105
106 lv_rowid cur_rowid%ROWTYPE;
107
108 BEGIN
109
110 OPEN cur_rowid;
111 FETCH cur_rowid INTO lv_rowid;
112 IF (cur_rowid%FOUND) THEN
113 CLOSE cur_rowid;
114 RETURN (TRUE);
115 ELSE
116 CLOSE cur_rowid;
117 RETURN (FALSE);
118 END IF;
119
120 END get_pk_for_validation;
121
122 PROCEDURE before_dml (
123 p_action IN VARCHAR2,
124 x_rowid IN VARCHAR2 DEFAULT NULL,
125 x_tracking_group_id IN NUMBER DEFAULT NULL,
126 x_description IN VARCHAR2 DEFAULT NULL,
127 x_creation_date IN DATE DEFAULT NULL,
128 x_created_by IN NUMBER DEFAULT NULL,
129 x_last_update_date IN DATE DEFAULT NULL,
130 x_last_updated_by IN NUMBER DEFAULT NULL,
131 x_last_update_login IN NUMBER DEFAULT NULL,
132 x_org_id IN NUMBER DEFAULT NULL
133 ) AS
134 BEGIN
135
136 set_column_values (
137 p_action,
138 x_rowid,
139 x_tracking_group_id,
140 x_description,
141 x_creation_date,
142 x_created_by,
143 x_last_update_date,
144 x_last_updated_by,
145 x_last_update_login,
146 x_org_id
147 );
148
149 IF (p_action = 'INSERT') THEN
150 IF get_pk_for_validation ( new_references.tracking_group_id ) THEN
151 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
152 igs_ge_msg_stack.add;
153 app_exception.raise_exception;
154 END IF;
155 check_constraints;
156
157 ELSIF (p_action = 'UPDATE') THEN
158 check_constraints;
159
160 ELSIF (p_action = 'DELETE') THEN
161 check_child_existance;
162
163 ELSIF (p_action = 'VALIDATE_INSERT') THEN
164 IF get_pk_for_validation ( new_references.tracking_group_id ) THEN
165 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
166 igs_ge_msg_stack.add;
167 app_exception.raise_exception;
168 END IF;
169 check_constraints;
170
171 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
172 check_constraints;
173
174 ELSIF (p_action = 'VALIDATE_DELETE') THEN
175 check_child_existance;
176 END IF;
177
178 END before_dml;
179
180 PROCEDURE after_dml (
181 p_action IN VARCHAR2,
182 x_rowid IN VARCHAR2
183 ) AS
184 BEGIN
185
186 l_rowid := x_rowid;
187
188 END after_dml;
189
190 PROCEDURE insert_row (
191 x_rowid IN OUT NOCOPY VARCHAR2,
192 x_tracking_group_id IN NUMBER,
193 x_description IN VARCHAR2,
194 x_mode IN VARCHAR2 DEFAULT 'R',
195 x_org_id IN NUMBER
196 ) AS
197
198 CURSOR c IS
199 SELECT ROWID
200 FROM igs_tr_group_all
201 WHERE tracking_group_id = x_tracking_group_id;
202
203 x_last_update_date DATE;
204 x_last_updated_by NUMBER;
205 x_last_update_login NUMBER;
206
207 BEGIN
208
209 x_last_update_date := SYSDATE;
210
211 IF(x_mode = 'I') THEN
212 x_last_updated_by := 1;
213 x_last_update_login := 0;
214
215 ELSIF (x_mode = 'R') THEN
216 x_last_updated_by := fnd_global.user_id;
217 IF x_last_updated_by IS NULL THEN
218 x_last_updated_by := -1;
219 END IF;
220 x_last_update_login :=fnd_global.login_id;
221 IF x_last_update_login IS NULL THEN
222 x_last_update_login := -1;
223 END IF;
224
225 ELSE
226 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
227 igs_ge_msg_stack.add;
228 app_exception.raise_exception;
229 END IF;
230
231 before_dml(p_action =>'INSERT',
232 x_rowid =>x_rowid,
233 x_tracking_group_id => x_tracking_group_id,
234 x_description => x_description,
235 x_creation_date => x_last_update_date,
236 x_created_by => x_last_updated_by,
237 x_last_update_date => x_last_update_date,
238 x_last_updated_by => x_last_updated_by,
239 x_last_update_login => x_last_update_login,
240 x_org_id => igs_ge_gen_003.get_org_id
241 );
242
243 INSERT INTO igs_tr_group_all (
244 tracking_group_id,
245 description,
246 org_id,
247 creation_date,
248 created_by,
249 last_update_date,
250 last_updated_by,
251 last_update_login
252 ) VALUES (
253 new_references.tracking_group_id,
254 new_references.description,
255 new_references.org_id,
256 x_last_update_date,
257 x_last_updated_by,
258 x_last_update_date,
259 x_last_updated_by,
260 x_last_update_login
261 );
262
263 OPEN c;
264 FETCH c INTO x_rowid;
265 IF (c%NOTFOUND) THEN
266 CLOSE c;
267 RAISE no_data_found;
268 END IF;
269 CLOSE c;
270
271 after_dml( p_action =>'INSERT', x_rowid => x_rowid);
272
273 END insert_row;
274
275 PROCEDURE lock_row (
276 x_rowid IN VARCHAR2,
277 x_tracking_group_id IN NUMBER,
278 x_description IN VARCHAR2
279 ) AS
280
281 CURSOR c1 IS
282 SELECT description
283 FROM igs_tr_group_all
284 WHERE ROWID = x_rowid
285 FOR UPDATE NOWAIT;
286
287 tlinfo c1%ROWTYPE;
288
289 BEGIN
290
291 OPEN c1;
292 FETCH c1 INTO tlinfo;
293 IF (c1%NOTFOUND) THEN
294 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
295 igs_ge_msg_stack.add;
296 app_exception.raise_exception;
297 CLOSE c1;
298 RETURN;
299 END IF;
300 CLOSE c1;
301
302 IF ( (tlinfo.description = x_description) ) THEN
303 NULL;
304 ELSE
305 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
306 igs_ge_msg_stack.add;
307 app_exception.raise_exception;
308 END IF;
309 RETURN;
310
311 END lock_row;
312
313 PROCEDURE update_row (
314 x_rowid IN VARCHAR2,
315 x_tracking_group_id IN NUMBER,
316 x_description IN VARCHAR2,
317 x_mode IN VARCHAR2 DEFAULT 'R'
318 ) AS
319
320 x_last_update_date DATE;
321 x_last_updated_by NUMBER;
322 x_last_update_login NUMBER;
323
324 BEGIN
325
326 x_last_update_date := SYSDATE;
327 IF(x_mode = 'I') THEN
328 x_last_updated_by := 1;
329 x_last_update_login := 0;
330
331 ELSIF (x_mode = 'R') THEN
332 x_last_updated_by := fnd_global.user_id;
333 IF x_last_updated_by IS NULL THEN
334 x_last_updated_by := -1;
335 END IF;
336 x_last_update_login :=fnd_global.login_id;
337 IF x_last_update_login IS NULL THEN
338 x_last_update_login := -1;
339 END IF;
340
341 ELSE
342 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
343 igs_ge_msg_stack.add;
344 app_exception.raise_exception;
345 END IF;
346
347 before_dml(p_action =>'UPDATE',
348 x_rowid =>x_rowid,
349 x_tracking_group_id => x_tracking_group_id,
350 x_description => x_description,
351 x_creation_date => x_last_update_date,
352 x_created_by => x_last_updated_by,
353 x_last_update_date => x_last_update_date,
354 x_last_updated_by => x_last_updated_by,
355 x_last_update_login => x_last_update_login
356 );
357
358 UPDATE igs_tr_group_all SET
359 description = new_references.description,
360 last_update_date = x_last_update_date,
361 last_updated_by = x_last_updated_by,
362 last_update_login = x_last_update_login
363 WHERE ROWID = x_rowid;
364
365 IF (SQL%NOTFOUND) THEN
366 RAISE no_data_found;
367 END IF;
368
369 after_dml(
370 p_action =>'UPDATE',
371 x_rowid => x_rowid
372 );
373
374 END update_row;
375
376 PROCEDURE add_row (
377 x_rowid IN OUT NOCOPY VARCHAR2,
378 x_tracking_group_id IN NUMBER,
379 x_description IN VARCHAR2,
380 x_mode IN VARCHAR2 DEFAULT 'R',
381 x_org_id IN NUMBER
382 ) AS
383
384 CURSOR c1 IS
385 SELECT ROWID
386 FROM igs_tr_group_all
387 WHERE tracking_group_id = x_tracking_group_id;
388
389 BEGIN
390
391 OPEN c1;
392 FETCH c1 INTO x_rowid;
393 IF (c1%NOTFOUND) THEN
394 CLOSE c1;
395 insert_row (
396 x_rowid,
397 x_tracking_group_id,
398 x_description,
399 x_mode,
400 x_org_id
401 );
402 RETURN;
403 END IF;
404 CLOSE c1;
405 update_row (
406 x_rowid,
407 x_tracking_group_id,
408 x_description,
409 x_mode);
410
411 END add_row;
412
413 PROCEDURE delete_row (
414 x_rowid IN VARCHAR2
415 ) AS
416 BEGIN
417
418 before_dml(
419 p_action =>'DELETE',
420 x_rowid => x_rowid
421 );
422
423 DELETE FROM igs_tr_group_all WHERE ROWID = x_rowid;
424 IF (SQL%NOTFOUND) THEN
425 RAISE no_data_found;
426 END IF;
427
428 after_dml(
429 p_action =>'DELETE',
430 x_rowid => x_rowid
431 );
432 END delete_row;
433
434 END igs_tr_group_pkg;