[Home] [Help]
PACKAGE BODY: APPS.IGR_I_PKG_ITEM_PKG
Source
1 package body IGR_I_PKG_ITEM_PKG AS
2 /* $Header: IGSRH03B.pls 120.0 2005/06/01 16:02:39 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGR_I_PKG_ITEM%RowType;
6 new_references IGR_I_PKG_ITEM%RowType;
7
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_package_item_id IN NUMBER DEFAULT NULL,
13 x_publish_ss_ind in VARCHAR2 DEFAULT NULL ,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM IGR_I_PKG_ITEM
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 Close cur_old_ref_values;
38 App_Exception.Raise_Exception;
39 Return;
40 END IF;
41 Close cur_old_ref_values;
42
43 -- Populate New Values.
44 new_references.package_item_id := x_package_item_id;
45 new_references.publish_ss_ind := x_publish_ss_ind;
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 new_references.last_update_date := x_last_update_date;
54 new_references.last_updated_by := x_last_updated_by;
55 new_references.last_update_login := x_last_update_login;
56
57 END Set_Column_Values;
58
59
60
61 PROCEDURE check_constraints (
62 Column_Name IN VARCHAR2 DEFAULT NULL,
63 Column_Value IN VARCHAR2 DEFAULT NULL
64 ) as
65
66 BEGIN
67
68 -- The following code checks for check constraints on the Columns.
69
70 IF column_name is NULL THEN
71 NULL;
72 ELSIF UPPER(column_name) = 'PACKAGE_ITEM_ID' THEN
73 new_references.package_item_id := column_value;
74 ELSIF UPPER(column_name) = 'PUBLISH_SS_IND' THEN
75 new_references.publish_ss_ind := column_value;
76 END IF;
77
78
79 IF ((UPPER (column_name) = 'PACKAGE_ITEM_ID') OR (column_name IS NULL)) THEN
80 IF (new_references.package_item_id <> UPPER (new_references.package_item_id)) THEN
81 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception;
84 END IF;
85 END IF;
86
87 IF ((UPPER (column_name) = 'PUBLISH_SS_IND') OR (column_name IS NULL)) THEN
88 IF new_references.publish_ss_ind NOT IN ( 'Y' , 'N' ) THEN
89 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception;
92 END IF;
93 END IF;
94
95
96 END check_constraints;
97
98 PROCEDURE check_parent_existance AS
99 BEGIN
100 DECLARE
101
102 CURSOR c_ams_deliverables (p_package_item_id igr_i_pkg_item.package_item_id%TYPE) IS
103 SELECT deliverable_id
104 FROM ams_deliverables_all_b
105 WHERE deliverable_id = p_package_item_id;
106 l_deliverable_id AMS_DELIVERABLES_ALL_B.deliverable_id%TYPE;
107 BEGIN
108
109 IF (((old_references.package_item_id = new_references.package_item_id)) OR
110 ((new_references.package_item_id IS NULL))) THEN
111 NULL;
112 ELSE
113 OPEN c_ams_deliverables(new_references.package_item_id);
114 FETCH c_ams_deliverables INTO l_deliverable_id;
115 CLOSE c_ams_deliverables;
116 IF c_ams_deliverables%NOTFOUND THEN
117 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122 END;
123 END check_parent_existance;
124
125 PROCEDURE Check_Child_Existance AS
126 /*
127 || Created By : [email protected]
128 || Created On : 06-MAR-2005
129 || Purpose : Checks for the existance of Child records.
130 || Known limitations, enhancements or remarks :
131 || Change History :
132 || Who When What
133 || (reverse chronological order - newest change first)
134 */
135 BEGIN
136 igr_i_inquiry_types_pkg.get_fk_igr_i_pkg_item (old_references.package_item_id);
137 igr_i_pkgitm_assign_pkg.get_fk_igr_i_pkg_item (old_references.package_item_id);
138
139 END Check_Child_Existance;
140
141 FUNCTION get_pk_for_validation (
142 x_package_item_id IN NUMBER DEFAULT NULL
143 ) RETURN BOOLEAN AS
144
145 CURSOR cur_rowid IS
146 SELECT rowid
147 FROM IGR_I_PKG_ITEM
148 WHERE package_item_id = x_package_item_id;
149
150 lv_rowid cur_rowid%RowType;
151
152 BEGIN
153
154 Open cur_rowid;
155 Fetch cur_rowid INTO lv_rowid;
156
157 IF (cur_rowid%FOUND) THEN
158 Close cur_rowid;
159 Return(TRUE);
160 ELSE
161 Close cur_rowid;
162 Return(FALSE);
163 END IF;
164
165 END get_pk_for_validation;
166
167 PROCEDURE get_fk_ams_deliverable_all_b (
168 x_package_item_id IN NUMBER DEFAULT NULL
169 ) AS
170
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM IGR_I_PKG_ITEM
174 WHERE package_item_id = x_package_item_id ;
175
176 lv_rowid cur_rowid%RowType;
177
178 BEGIN
179
180 Open cur_rowid;
181 Fetch cur_rowid INTO lv_rowid;
182 IF (cur_rowid%FOUND) THEN
183 Fnd_Message.Set_Name ('IGS', 'IGS_IN_EPI_AM_FK');
184 IGS_GE_MSG_STACK.ADD;
185 Close cur_rowid;
186 App_Exception.Raise_Exception;
187 Return;
188 END IF;
189 Close cur_rowid;
190
191 END get_fk_ams_deliverable_all_b;
192
193 PROCEDURE Before_DML (
194 p_action IN VARCHAR2,
195 x_rowid IN VARCHAR2 DEFAULT NULL,
196 x_package_item_id IN NUMBER DEFAULT NULL,
197 x_publish_ss_ind in VARCHAR2 DEFAULT NULL ,
198 x_creation_date IN DATE DEFAULT NULL,
199 x_created_by IN NUMBER DEFAULT NULL,
200 x_last_update_date IN DATE DEFAULT NULL,
201 x_last_updated_by IN NUMBER DEFAULT NULL,
202 x_last_update_login IN NUMBER DEFAULT NULL
203 ) AS
204 BEGIN
205 Set_Column_Values (
206 p_action,
207 x_rowid,
208 x_package_item_id,
209 x_publish_ss_ind,
210 x_creation_date,
211 x_created_by,
212 x_last_update_date,
213 x_last_updated_by,
214 x_last_update_login
215 ) ;
216
217 IF (p_action = 'INSERT') THEN
218 -- Call all the procedures related to Before Insert.
219 IF get_pk_for_validation(new_references.package_item_id) THEN
220 Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF;
224
225 Check_Constraints;
226 ELSIF (p_action = 'UPDATE') THEN
227 -- Call all the procedures related to Before Update.
228 Check_Constraints;
229 Check_Parent_Existance;
230 ELSIF (p_action = 'DELETE') THEN
231 -- Call all the procedures related to Before Delete.
232 Check_Child_Existance;
233 ELSIF (p_action = 'VALIDATE_INSERT') THEN
234 IF get_pk_for_validation (new_references.package_item_id) THEN
235 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 check_constraints;
240 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
241 check_constraints;
242 ELSIF (p_action = 'VALIDATE_DELETE') THEN
243 Check_Child_Existance;
244 END IF;
245 END Before_DML;
246
247 PROCEDURE insert_row (
248 x_rowid IN OUT NOCOPY VARCHAR2,
249 x_package_item_id IN NUMBER DEFAULT NULL,
250 x_publish_ss_ind IN VARCHAR2 DEFAULT NULL,
251 x_mode IN VARCHAR2 DEFAULT 'R'
252 ) AS
253 CURSOR C IS
254 SELECT rowid FROM IGR_I_PKG_ITEM
255 WHERE package_item_id = x_package_item_id;
256 x_last_update_date DATE;
257 x_last_updated_by NUMBER;
258 x_last_update_login NUMBER;
259 begin
260 x_last_update_date := SYSDATE;
261 if(x_mode = 'I') then
262 x_last_updated_by := 1;
263 x_last_update_login := 0;
264 elsif (x_mode = 'R') then
265 x_last_updated_by := FND_GLOBAL.USER_ID;
266 if x_last_updated_by is NULL then
267 x_last_updated_by := -1;
268 end if;
269 x_last_update_login :=FND_GLOBAL.LOGIN_ID;
270 if x_last_update_login is NULL then
271 x_last_update_login := -1;
272 end if;
273 else
274 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
275 IGS_GE_MSG_STACK.ADD;
276 app_exception.raise_exception;
277 end if;
278
279 Before_DML(
280 p_action=>'INSERT',
281 x_rowid=>X_ROWID,
282 x_publish_ss_ind => x_publish_ss_ind ,
283 x_package_item_id => x_package_item_id,
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 insert into IGR_I_PKG_ITEM (
291 package_item_id,
292 publish_ss_ind,
293 creation_date,
294 created_by,
295 last_update_date,
296 last_updated_by,
297 last_update_login
298 ) values (
299 new_references.package_item_id,
300 new_references.publish_ss_ind ,
301 x_last_update_date,
302 x_last_updated_by,
303 x_last_update_date,
304 x_last_updated_by,
305 x_last_update_login
306 );
307
308 OPEN c;
309 FETCH c INTO x_rowid;
310 IF (c%NOTFOUND) THEN
311 CLOSE c;
312 raise no_data_found;
313 END IF;
314 CLOSE c;
315 END insert_row;
316
317
318 PROCEDURE lock_row (
319 x_rowid IN VARCHAR2,
320 x_package_item_id IN NUMBER DEFAULT NULL,
321 x_publish_ss_ind IN VARCHAR2 DEFAULT NULL
322 ) AS
323 CURSOR c1 IS
324 SELECT package_item_id,
325 publish_ss_ind
326 FROM igr_i_pkg_item
327 WHERE rowid = x_rowid;
328 tlinfo c1%rowtype;
329
330 BEGIN
331 OPEN c1;
332 FETCH c1 INTO tlinfo;
333 IF (c1%NOTFOUND) THEN
334 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
335 IGS_GE_MSG_STACK.ADD;
336 CLOSE c1;
337 app_exception.raise_exception;
338 RETURN;
339 END IF;
340 CLOSE c1;
341
342 IF ( (tlinfo.PACKAGE_ITEM_ID = X_PACKAGE_ITEM_ID)
343 AND ((tlinfo.PUBLISH_SS_IND = X_PUBLISH_SS_IND)
344 OR ((tlinfo.PUBLISH_SS_IND is null)
345 AND (X_PUBLISH_SS_IND is null)))
346 ) THEN
347 NULL;
348 ELSE
349 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
350 IGS_GE_MSG_STACK.ADD;
351 app_exception.raise_exception;
352 END IF;
353 RETURN;
354 END lock_row;
355
356 PROCEDURE update_row (
357 x_rowid IN VARCHAR2,
358 x_package_item_id IN NUMBER DEFAULT NULL,
359 x_publish_ss_ind IN VARCHAR2 DEFAULT NULL,
360 x_mode IN VARCHAR2 DEFAULT 'R'
361 ) AS
362 x_last_update_date DATE;
363 x_last_updated_by NUMBER;
364 x_last_update_login NUMBER;
365 BEGIN
366 x_last_update_date := SYSDATE;
367 IF(x_mode = 'I') THEN
368 x_last_updated_by := 1;
369 x_last_update_login := 0;
370 ELSIF (x_mode = 'R') THEN
371 x_last_updated_by := FND_GLOBAL.USER_ID;
372 IF x_last_updated_by is NULL THEN
373 x_last_updated_by := -1;
374 END IF;
375 x_last_update_login :=FND_GLOBAL.LOGIN_ID;
376 IF x_last_update_login IS NULL THEN
377 x_last_update_login := -1;
378 END IF;
379 ELSE
380 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
381 IGS_GE_MSG_STACK.ADD;
382 app_exception.raise_exception;
383 END IF;
384
385 Before_DML(
386 p_action=>'UPDATE',
387 x_rowid=>X_ROWID,
388 x_publish_ss_ind => X_PUBLISH_SS_IND ,
389 x_package_item_id => X_PACKAGE_ITEM_ID,
390 x_creation_date=>X_LAST_UPDATE_DATE,
391 x_created_by=>X_LAST_UPDATED_BY,
392 x_last_update_date=>X_LAST_UPDATE_DATE,
393 x_last_updated_by=>X_LAST_UPDATED_BY,
394 x_last_update_login=>X_LAST_UPDATE_LOGIN
395 );
396 update IGR_I_PKG_ITEM set
397 package_item_id = new_references.package_item_id,
398 publish_ss_ind = new_references.publish_ss_ind,
399 last_update_date = x_last_update_date,
400 last_updated_by = x_last_updated_by,
401 last_update_login = x_last_update_login
402 WHERE rowid = x_rowid ;
403 IF (sql%NOTFOUND) THEN
404 raise no_data_found;
405 END IF;
406 END update_row;
407
408 PROCEDURE add_row (
409 x_rowid IN OUT NOCOPY VARCHAR2,
410 x_package_item_id IN NUMBER DEFAULT NULL,
411 x_publish_ss_ind IN VARCHAR2 DEFAULT NULL,
412 x_mode IN VARCHAR2 DEFAULT 'R'
413 ) AS
414 CURSOR c1 IS
415 SELECT rowid FROM IGR_I_PKG_ITEM
416 WHERE package_item_id = x_package_item_id;
417 BEGIN
418 OPEN c1;
419 FETCH c1 INTO x_rowid;
420 IF (c1%NOTFOUND) THEN
421 CLOSE c1;
422 insert_row (
423 x_rowid,
424 x_package_item_id,
425 x_publish_ss_ind,
426 x_mode);
427 RETURN;
428 END IF;
429 CLOSE c1;
430 update_row (
431 x_rowid,
432 x_package_item_id,
433 x_publish_ss_ind,
434 x_mode);
435 END add_row;
436
437 PROCEDURE delete_row (
438 x_rowid IN VARCHAR2
439 ) AS
440 BEGIN
441 before_dml(
442 p_action => 'DELETE',
443 x_rowid => x_rowid
444 );
445 DELETE FROM IGR_I_PKG_ITEM
446 WHERE rowid = x_rowid;
447 IF (sql%NOTFOUND) THEN
448 raise no_data_found;
449 END IF;
450
451 END delete_row;
452
453
454 END IGR_I_PKG_ITEM_PKG;