[Home] [Help]
PACKAGE BODY: APPS.IGS_TR_GROUP_MEMBER_PKG
Source
1 PACKAGE BODY igs_tr_group_member_pkg AS
2 /* $Header: IGSTI10B.pls 115.5 2002/11/29 04:16:27 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_tr_group_member%ROWTYPE;
6 new_references igs_tr_group_member%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_tracking_id IN NUMBER 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 ) AS
19
20 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM igs_tr_group_member
23 WHERE ROWID = x_rowid;
24
25 BEGIN
26
27 l_rowid := x_rowid;
28
29 -- Code for setting the Old and New Reference Values.
30 -- Populate Old Values.
31 OPEN cur_old_ref_values;
32 FETCH cur_old_ref_values INTO old_references;
33 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
34 CLOSE cur_old_ref_values;
35 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
36 igs_ge_msg_stack.add;
37 app_exception.raise_exception;
38 RETURN;
39 END IF;
40 CLOSE cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.tracking_group_id := x_tracking_group_id;
44 new_references.tracking_id := x_tracking_id;
45
46 IF (p_action = 'UPDATE') THEN
47 new_references.creation_date := old_references.creation_date;
48 new_references.created_by := old_references.created_by;
49 ELSE
50 new_references.creation_date := x_creation_date;
51 new_references.created_by := x_created_by;
52 END IF;
53
54 new_references.last_update_date := x_last_update_date;
55 new_references.last_updated_by := x_last_updated_by;
56 new_references.last_update_login := x_last_update_login;
57
58 END set_column_values;
59
60 PROCEDURE check_parent_existance AS
61 BEGIN
62
63 IF (((old_references.tracking_group_id = new_references.tracking_group_id)) OR
64 ((new_references.tracking_group_id IS NULL))) THEN
65 NULL;
66
67 ELSE
68 IF NOT igs_tr_group_pkg.get_pk_for_validation ( new_references.tracking_group_id ) THEN
69 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
70 igs_ge_msg_stack.add;
71 app_exception.raise_exception;
72 END IF;
73 END IF;
74
75 IF (((old_references.tracking_id = new_references.tracking_id)) OR
76 ((new_references.tracking_id IS NULL))) THEN
77 NULL;
78 ELSE
79
80 IF NOT igs_tr_item_pkg.get_pk_for_validation ( new_references.tracking_id ) THEN
81 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
82 igs_ge_msg_stack.add;
83 app_exception.raise_exception;
84 END IF;
85 END IF;
86
87 END check_parent_existance;
88
89 FUNCTION get_pk_for_validation (
90 x_tracking_group_id IN NUMBER,
91 x_tracking_id IN NUMBER
92 )
93 RETURN BOOLEAN AS
94
95 CURSOR cur_rowid IS
96 SELECT ROWID
97 FROM igs_tr_group_member
98 WHERE tracking_group_id = x_tracking_group_id
99 AND tracking_id = x_tracking_id
100 FOR UPDATE NOWAIT;
101
102 lv_rowid cur_rowid%ROWTYPE;
103
104 BEGIN
105
106 OPEN cur_rowid;
107 FETCH cur_rowid INTO lv_rowid;
108 IF (cur_rowid%FOUND) THEN
109 CLOSE cur_rowid;
110 RETURN (TRUE);
111 ELSE
112 CLOSE cur_rowid;
113 RETURN (FALSE);
114 END IF;
115
116 END get_pk_for_validation;
117
118 PROCEDURE get_fk_igs_tr_group (
119 x_tracking_group_id IN NUMBER
120 ) AS
121
122 CURSOR cur_rowid IS
123 SELECT ROWID
124 FROM igs_tr_group_member
125 WHERE tracking_group_id = x_tracking_group_id ;
126
127 lv_rowid cur_rowid%ROWTYPE;
128
129 BEGIN
130
131 OPEN cur_rowid;
132 FETCH cur_rowid INTO lv_rowid;
133 IF (cur_rowid%FOUND) THEN
134 CLOSE cur_rowid;
135 fnd_message.set_name ('IGS', 'IGS_TR_TGM_TG_FK');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 RETURN;
139 END IF;
140 CLOSE cur_rowid;
141
142 END get_fk_igs_tr_group;
143
144 PROCEDURE get_fk_igs_tr_item (
145 x_tracking_id IN NUMBER
146 ) AS
147
148 CURSOR cur_rowid IS
149 SELECT ROWID
150 FROM igs_tr_group_member
151 WHERE tracking_id = x_tracking_id ;
152
153 lv_rowid cur_rowid%ROWTYPE;
154
155 BEGIN
156
157 OPEN cur_rowid;
158 FETCH cur_rowid INTO lv_rowid;
159 IF (cur_rowid%FOUND) THEN
160 CLOSE cur_rowid;
161 fnd_message.set_name ('IGS', 'IGS_TR_TGM_TRI_FK');
162 igs_ge_msg_stack.add;
163 app_exception.raise_exception;
164 RETURN;
165 END IF;
166 CLOSE cur_rowid;
167
168 END get_fk_igs_tr_item;
169
170 PROCEDURE before_dml (
171 p_action IN VARCHAR2,
172 x_rowid IN VARCHAR2 DEFAULT NULL,
173 x_tracking_group_id IN NUMBER DEFAULT NULL,
174 x_tracking_id IN NUMBER DEFAULT NULL,
175 x_creation_date IN DATE DEFAULT NULL,
176 x_created_by IN NUMBER DEFAULT NULL,
177 x_last_update_date IN DATE DEFAULT NULL,
178 x_last_updated_by IN NUMBER DEFAULT NULL,
179 x_last_update_login IN NUMBER DEFAULT NULL
180 ) AS
181 BEGIN
182
183 set_column_values (
184 p_action,
185 x_rowid,
186 x_tracking_group_id,
187 x_tracking_id,
188 x_creation_date,
189 x_created_by,
190 x_last_update_date,
191 x_last_updated_by,
192 x_last_update_login
193 );
194
195 IF (p_action = 'INSERT') THEN
196 IF get_pk_for_validation (
197 new_references.tracking_group_id,
198 new_references.tracking_id
199 ) THEN
200 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
201 igs_ge_msg_stack.add;
202 app_exception.raise_exception;
203 END IF;
204 check_parent_existance;
205
206 ELSIF (p_action = 'UPDATE') THEN
207 check_parent_existance;
208
209 ELSIF (p_action = 'VALIDATE_INSERT') THEN
210 IF get_pk_for_validation (
211 new_references.tracking_group_id,
212 new_references.tracking_id
213 ) THEN
214 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
215 igs_ge_msg_stack.add;
216 app_exception.raise_exception;
217 END IF;
218 END IF;
219
220 END before_dml;
221
222 PROCEDURE after_dml (
223 p_action IN VARCHAR2,
224 x_rowid IN VARCHAR2
225 ) AS
226 BEGIN
227
228 l_rowid := x_rowid;
229
230 END after_dml;
231
232 PROCEDURE insert_row (
233 x_rowid IN OUT NOCOPY VARCHAR2,
234 x_tracking_group_id IN NUMBER,
235 x_tracking_id IN NUMBER,
236 x_mode IN VARCHAR2 DEFAULT 'R'
237 ) AS
238
239 CURSOR c IS
240 SELECT ROWID
241 FROM igs_tr_group_member
242 WHERE tracking_group_id = x_tracking_group_id
243 AND tracking_id = x_tracking_id;
244
245 x_last_update_date DATE;
246 x_last_updated_by NUMBER;
247 x_last_update_login NUMBER;
248
249 BEGIN
250
251 x_last_update_date := SYSDATE;
252
253 IF(x_mode = 'I') THEN
254 x_last_updated_by := 1;
255 x_last_update_login := 0;
256
257 ELSIF (x_mode = 'R') THEN
258 x_last_updated_by := fnd_global.user_id;
259 IF x_last_updated_by IS NULL THEN
260 x_last_updated_by := -1;
261 END IF;
262
263 x_last_update_login :=fnd_global.login_id;
264 IF x_last_update_login IS NULL THEN
265 x_last_update_login := -1;
266 END IF;
267
268 ELSE
269 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
270 igs_ge_msg_stack.add;
271 app_exception.raise_exception;
272 END IF;
273
274 before_dml(p_action =>'INSERT',
275 x_rowid =>x_rowid,
276 x_tracking_group_id => x_tracking_group_id,
277 x_tracking_id => x_tracking_id,
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_tr_group_member (
286 tracking_group_id,
287 tracking_id,
288 creation_date,
289 created_by,
290 last_update_date,
291 last_updated_by,
292 last_update_login
293 ) VALUES (
294 new_references.tracking_group_id,
295 new_references.tracking_id,
296 x_last_update_date,
297 x_last_updated_by,
298 x_last_update_date,
299 x_last_updated_by,
300 x_last_update_login
301 );
302
303 OPEN c;
304 FETCH c INTO x_rowid;
305 IF (c%NOTFOUND) THEN
306 CLOSE c;
307 RAISE no_data_found;
308 END IF;
309 CLOSE c;
310
311 after_dml(
312 p_action =>'INSERT',
313 x_rowid => x_rowid
314 );
315
316 END insert_row;
317
318 PROCEDURE lock_row (
319 x_rowid IN VARCHAR2,
320 x_tracking_group_id IN NUMBER,
321 x_tracking_id IN NUMBER
322 ) AS
323
324 CURSOR c1 IS
325 SELECT ROWID
326 FROM igs_tr_group_member
327 WHERE ROWID = x_rowid
328 FOR UPDATE NOWAIT;
329
330 tlinfo c1%ROWTYPE;
331
332 BEGIN
333
334 OPEN c1;
335 FETCH c1 INTO tlinfo;
336 IF (c1%NOTFOUND) THEN
337 CLOSE c1;
338 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
339 igs_ge_msg_stack.add;
340 app_exception.raise_exception;
341 RETURN;
342 END IF;
343 CLOSE c1;
344
345 RETURN;
346
347 END lock_row;
348
349 PROCEDURE delete_row (
350 x_rowid IN VARCHAR2
351 ) AS
352 BEGIN
353
354 before_dml(
355 p_action =>'DELETE',
356 x_rowid => x_rowid
357 );
358
359 DELETE FROM igs_tr_group_member WHERE ROWID = x_rowid;
360 IF (SQL%NOTFOUND) THEN
361 RAISE no_data_found;
362 END IF;
363 after_dml(
364 p_action =>'DELETE',
365 x_rowid => x_rowid
366 );
367
368 END delete_row;
369
370 END igs_tr_group_member_pkg;