1 PACKAGE BODY igs_uc_sys_decision_pkg AS
2 /* $Header: IGSXI44B.pls 115.6 2003/12/07 15:32:48 pmarada noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_sys_decision%ROWTYPE;
6 new_references igs_uc_sys_decision%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_system_code IN VARCHAR2,
12 x_decision_code IN VARCHAR2,
13 x_s_adm_outcome_status IN VARCHAR2,
14 x_closed_ind IN VARCHAR2,
15 x_decision_type IN VARCHAR2
16 ) AS
17 /*
18 || Created By : [email protected]
19 || Created On : 17-SEP-2002
20 || Purpose : Initialises the Old and New references for the columns of the table.
21 || Known limitations, enhancements or remarks :
22 || Change History :
23 || Who When What
24 || pmarada 05-dec-03 Added decision_type as per UC205FD,bug 2669224
25 || (reverse chronological order - newest change first)
26 */
27
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM igs_uc_sys_decision
31 WHERE rowid = x_rowid;
32
33 BEGIN
34
35 l_rowid := x_rowid;
36
37 -- Code for setting the Old and New Reference Values.
38 -- Populate Old Values.
39 OPEN cur_old_ref_values;
40 FETCH cur_old_ref_values INTO old_references;
41 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
42 CLOSE cur_old_ref_values;
43 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
44 igs_ge_msg_stack.add;
45 app_exception.raise_exception;
46 RETURN;
47 END IF;
48 CLOSE cur_old_ref_values;
49
50 -- Populate New Values.
51 new_references.system_code := x_system_code;
52 new_references.decision_code := x_decision_code;
53 new_references.s_adm_outcome_status := x_s_adm_outcome_status;
54 new_references.closed_ind := x_closed_ind;
55 new_references.decision_type := x_decision_type;
56
57 END set_column_values;
58
59
60 PROCEDURE check_child_existance AS
61 /*
62 || Created By : [email protected]
63 || Created On : 17-SEP-2002
64 || Purpose : Checks for the existance of Child records.
65 || Known limitations, enhancements or remarks :
66 || Change History :
67 || Who When What
68 || (reverse chronological order - newest change first)
69 */
70 BEGIN
71
72 igs_uc_map_off_resp_pkg.get_fk_igs_uc_sys_decision (
73 old_references.system_code,
74 old_references.decision_code
75 );
76
77 igs_uc_map_out_stat_pkg.get_fk_igs_uc_sys_decision (
78 old_references.system_code,
79 old_references.decision_code
80 );
81
82 END check_child_existance;
83
84 FUNCTION get_pk_for_validation (
85 x_system_code IN VARCHAR2,
86 x_decision_code IN VARCHAR2
87 ) RETURN BOOLEAN AS
88 /*
89 || Created By : [email protected]
90 || Created On : 17-SEP-2002
91 || Purpose : Validates the Primary Key of the table.
92 || Known limitations, enhancements or remarks :
93 || Change History :
94 || Who When What
95 || (reverse chronological order - newest change first)
96 */
97 CURSOR cur_rowid IS
98 SELECT rowid
99 FROM igs_uc_sys_decision
100 WHERE system_code = x_system_code
101 AND decision_code = x_decision_code ;
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
120 PROCEDURE before_dml (
121 p_action IN VARCHAR2,
122 x_rowid IN VARCHAR2,
123 x_system_code IN VARCHAR2,
124 x_decision_code IN VARCHAR2,
125 x_s_adm_outcome_status IN VARCHAR2,
126 x_closed_ind IN VARCHAR2,
127 x_decision_type IN VARCHAR2
128 ) AS
129 /*
130 || Created By : [email protected]
131 || Created On : 17-SEP-2002
132 || Purpose : Initialises the columns, Checks Constraints, Calls the
133 || Trigger Handlers for the table, before any DML operation.
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 */
139 BEGIN
140
141 set_column_values (
142 p_action,
143 x_rowid,
144 x_system_code,
145 x_decision_code,
146 x_s_adm_outcome_status,
147 x_closed_ind ,
148 x_decision_type
149 );
150
151 IF (p_action = 'INSERT') THEN
152 -- Call all the procedures related to Before Insert.
153 IF ( get_pk_for_validation(
154 new_references.system_code,
155 new_references.decision_code
156 )
157 ) THEN
158 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
159 igs_ge_msg_stack.add;
160 app_exception.raise_exception;
161 END IF;
162
163 ELSIF (p_action = 'UPDATE') THEN
164 -- Call all the procedures related to Before Update.
165 NULL;
166
167 ELSIF (p_action = 'DELETE') THEN
168 -- Call all the procedures related to Before Delete.
169 check_child_existance;
170 ELSIF (p_action = 'VALIDATE_INSERT') THEN
171 -- Call all the procedures related to Before Insert.
172 IF ( get_pk_for_validation (
173 new_references.system_code,
174 new_references.decision_code
175 )
176 ) THEN
177 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END IF;
181
182 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
183 NULL;
184 ELSIF (p_action = 'VALIDATE_DELETE') THEN
185 check_child_existance;
186 END IF;
187
188 END before_dml;
189
190
191 PROCEDURE insert_row (
192 x_rowid IN OUT NOCOPY VARCHAR2,
193 x_system_code IN VARCHAR2,
194 x_decision_code IN VARCHAR2,
195 x_s_adm_outcome_status IN VARCHAR2,
196 x_closed_ind IN VARCHAR2,
197 x_decision_type IN VARCHAR2,
198 x_mode IN VARCHAR2
199 ) AS
200 /*
201 || Created By : [email protected]
202 || Created On : 17-SEP-2002
203 || Purpose : Handles the INSERT DML logic for the table.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209
210 x_last_update_date DATE;
211 x_last_updated_by NUMBER;
212 x_last_update_login NUMBER;
213
214 BEGIN
215
216 x_last_update_date := SYSDATE;
217 IF (x_mode = 'I') THEN
218 x_last_updated_by := 1;
219 x_last_update_login := 0;
220 ELSIF (x_mode = 'R') THEN
221 x_last_updated_by := fnd_global.user_id;
222 IF (x_last_updated_by IS NULL) THEN
223 x_last_updated_by := -1;
224 END IF;
225 x_last_update_login := fnd_global.login_id;
226 IF (x_last_update_login IS NULL) THEN
227 x_last_update_login := -1;
228 END IF;
229 ELSE
230 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
231 igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 END IF;
234
235 before_dml(
236 p_action => 'INSERT',
237 x_rowid => x_rowid,
238 x_system_code => x_system_code,
239 x_decision_code => x_decision_code,
240 x_s_adm_outcome_status => x_s_adm_outcome_status,
241 x_closed_ind => x_closed_ind ,
242 x_decision_type => x_decision_type
243 );
244
245 INSERT INTO igs_uc_sys_decision (
246 system_code,
247 decision_code,
248 s_adm_outcome_status,
249 closed_ind ,
250 decision_type
251 ) VALUES (
252 new_references.system_code,
253 new_references.decision_code,
254 new_references.s_adm_outcome_status,
255 new_references.closed_ind ,
256 new_references.decision_type
257 ) RETURNING ROWID INTO x_rowid;
258
259 END insert_row;
260
261
262 PROCEDURE lock_row (
263 x_rowid IN VARCHAR2,
264 x_system_code IN VARCHAR2,
265 x_decision_code IN VARCHAR2,
266 x_s_adm_outcome_status IN VARCHAR2,
267 x_closed_ind IN VARCHAR2,
268 x_decision_type IN VARCHAR2
269 ) AS
270 /*
271 || Created By : [email protected]
272 || Created On : 17-SEP-2002
273 || Purpose : Handles the LOCK mechanism for the table.
274 || Known limitations, enhancements or remarks :
275 || Change History :
276 || Who When What
277 || (reverse chronological order - newest change first)
278 */
279 CURSOR c1 IS
280 SELECT
281 s_adm_outcome_status,
282 closed_ind,
283 decision_type
284 FROM igs_uc_sys_decision
285 WHERE rowid = x_rowid
286 FOR UPDATE NOWAIT;
287
288 tlinfo c1%ROWTYPE;
289
290 BEGIN
291
292 OPEN c1;
293 FETCH c1 INTO tlinfo;
294 IF (c1%notfound) THEN
295 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
296 igs_ge_msg_stack.add;
297 CLOSE c1;
298 app_exception.raise_exception;
299 RETURN;
300 END IF;
301 CLOSE c1;
302
303 IF (
304 (tlinfo.s_adm_outcome_status = x_s_adm_outcome_status)
305 AND (tlinfo.closed_ind = x_closed_ind)
306 AND (tlinfo.decision_type = x_decision_type)
307 ) THEN
308 NULL;
309 ELSE
310 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
311 igs_ge_msg_stack.add;
312 app_exception.raise_exception;
313 END IF;
314
315 RETURN;
316
317 END lock_row;
318
319
320 PROCEDURE update_row (
321 x_rowid IN VARCHAR2,
322 x_system_code IN VARCHAR2,
323 x_decision_code IN VARCHAR2,
324 x_s_adm_outcome_status IN VARCHAR2,
325 x_closed_ind IN VARCHAR2,
326 x_decision_type IN VARCHAR2,
327 x_mode IN VARCHAR2
328 ) AS
329 /*
330 || Created By : [email protected]
331 || Created On : 17-SEP-2002
332 || Purpose : Handles the UPDATE DML logic for the table.
333 || Known limitations, enhancements or remarks :
334 || Change History :
335 || Who When What
336 || (reverse chronological order - newest change first)
337 */
338 x_last_update_date DATE ;
339 x_last_updated_by NUMBER;
340 x_last_update_login NUMBER;
341
342 BEGIN
343
344 x_last_update_date := SYSDATE;
345 IF (X_MODE = 'I') THEN
346 x_last_updated_by := 1;
347 x_last_update_login := 0;
348 ELSIF (x_mode = 'R') THEN
349 x_last_updated_by := fnd_global.user_id;
350 IF x_last_updated_by IS NULL THEN
351 x_last_updated_by := -1;
352 END IF;
353 x_last_update_login := fnd_global.login_id;
354 IF (x_last_update_login IS NULL) THEN
355 x_last_update_login := -1;
356 END IF;
357 ELSE
358 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
359 igs_ge_msg_stack.add;
360 app_exception.raise_exception;
361 END IF;
362
363 before_dml(
364 p_action => 'UPDATE',
365 x_rowid => x_rowid,
366 x_system_code => x_system_code,
367 x_decision_code => x_decision_code,
368 x_s_adm_outcome_status => x_s_adm_outcome_status,
369 x_closed_ind => x_closed_ind,
370 x_decision_type => x_decision_type
371 );
372
373 UPDATE igs_uc_sys_decision
374 SET
375 s_adm_outcome_status = new_references.s_adm_outcome_status,
376 closed_ind = new_references.closed_ind ,
377 decision_type = new_references.decision_type
378 WHERE rowid = x_rowid;
379
380 IF (SQL%NOTFOUND) THEN
381 RAISE NO_DATA_FOUND;
382 END IF;
383
384 END update_row;
385
386
387 PROCEDURE add_row (
388 x_rowid IN OUT NOCOPY VARCHAR2,
389 x_system_code IN VARCHAR2,
390 x_decision_code IN VARCHAR2,
391 x_s_adm_outcome_status IN VARCHAR2,
392 x_closed_ind IN VARCHAR2,
393 x_decision_type IN VARCHAR2,
394 x_mode IN VARCHAR2
395 ) AS
396 /*
397 || Created By : [email protected]
398 || Created On : 17-SEP-2002
399 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
400 || Known limitations, enhancements or remarks :
401 || Change History :
402 || Who When What
403 || (reverse chronological order - newest change first)
404 */
405 CURSOR c1 IS
406 SELECT rowid
407 FROM igs_uc_sys_decision
408 WHERE system_code = x_system_code
409 AND decision_code = x_decision_code;
410
411 BEGIN
412
413 OPEN c1;
414 FETCH c1 INTO x_rowid;
415 IF (c1%NOTFOUND) THEN
416 CLOSE c1;
417
418 insert_row (
419 x_rowid,
420 x_system_code,
421 x_decision_code,
422 x_s_adm_outcome_status,
423 x_closed_ind,
424 x_decision_type,
425 x_mode
426 );
427 RETURN;
428 END IF;
429 CLOSE c1;
430
431 update_row (
432 x_rowid,
433 x_system_code,
434 x_decision_code,
435 x_s_adm_outcome_status,
436 x_closed_ind,
437 x_decision_type,
438 x_mode
439 );
440
441 END add_row;
442
443
444 PROCEDURE delete_row (
445 x_rowid IN VARCHAR2
446 ) AS
447 /*
448 || Created By : [email protected]
449 || Created On : 17-SEP-2002
450 || Purpose : Handles the DELETE DML logic for the table.
451 || Known limitations, enhancements or remarks :
452 || Change History :
453 || Who When What
454 || (reverse chronological order - newest change first)
455 */
456 BEGIN
457
458 before_dml (
459 p_action => 'DELETE',
460 x_rowid => x_rowid
461 );
462
463 DELETE FROM igs_uc_sys_decision
464 WHERE rowid = x_rowid;
465
466 IF (SQL%NOTFOUND) THEN
467 RAISE NO_DATA_FOUND;
468 END IF;
469
470 END delete_row;
471
472
473 END igs_uc_sys_decision_pkg;
[Home] [Help]