1 PACKAGE BODY igi_iac_cal_idx_values_pkg AS
2 /* $Header: igiicpvb.pls 120.3.12000000.1 2007/08/01 16:20:47 npandya ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igi_iac_cal_idx_values%ROWTYPE;
6 new_references igi_iac_cal_idx_values%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_cal_price_index_link_id IN NUMBER ,
12 x_date_from IN DATE ,
13 x_date_to IN DATE ,
14 x_original_price_index_value IN NUMBER ,
15 x_current_price_index_value IN NUMBER ,
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 || Created By : [email protected]
24 || Created On : 12-APR-2002
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || (reverse chronological order - newest change first)
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM igi_iac_cal_idx_values
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.cal_price_index_link_id := x_cal_price_index_link_id;
55 new_references.date_from := x_date_from;
56 new_references.date_to := x_date_to;
57 new_references.original_price_index_value := x_original_price_index_value;
58 new_references.current_price_index_value := x_current_price_index_value;
59
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END set_column_values;
73
74
75 PROCEDURE check_parent_existance AS
76 /*
77 || Created By : [email protected]
78 || Created On : 12-APR-2002
79 || Purpose : Checks for the existance of Parent records.
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84 */
85 BEGIN
86
87 IF (((old_references.cal_price_index_link_id = new_references.cal_price_index_link_id)) OR
88 ((new_references.cal_price_index_link_id IS NULL))) THEN
89 NULL;
90 ELSIF NOT igi_iac_cal_price_indexes_pkg.get_pk_for_validation (
91 new_references.cal_price_index_link_id
92 ) THEN
93 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
94 app_exception.raise_exception;
95 END IF;
96
97 END check_parent_existance;
98
99
100 FUNCTION get_pk_for_validation (
101 x_cal_price_index_link_id IN NUMBER,
102 x_date_from IN DATE,
103 x_date_to IN DATE
104 ) RETURN BOOLEAN AS
105 /*
106 || Created By : [email protected]
107 || Created On : 12-APR-2002
108 || Purpose : Validates the Primary Key of the table.
109 || Known limitations, enhancements or remarks :
110 || Change History :
111 || Who When What
112 || (reverse chronological order - newest change first)
113 */
114 CURSOR cur_rowid IS
115 SELECT rowid
116 FROM igi_iac_cal_idx_values
117 WHERE cal_price_index_link_id = x_cal_price_index_link_id
118 AND date_from = x_date_from
119 AND date_to = x_date_to
120 FOR UPDATE NOWAIT;
121
122 lv_rowid cur_rowid%RowType;
123
124 BEGIN
125
126 OPEN cur_rowid;
127 FETCH cur_rowid INTO lv_rowid;
128 IF (cur_rowid%FOUND) THEN
129 CLOSE cur_rowid;
130 RETURN(TRUE);
131 ELSE
132 CLOSE cur_rowid;
133 RETURN(FALSE);
134 END IF;
135
136 END get_pk_for_validation;
137
138
139 PROCEDURE get_fk_igi_iac_cal_price_idx (
140 x_cal_price_index_link_id IN NUMBER
141 ) AS
142 /*
143 || Created By : [email protected]
144 || Created On : 12-APR-2002
145 || Purpose : Validates the Foreign Keys for the table.
146 || Known limitations, enhancements or remarks :
147 || Change History :
148 || Who When What
149 || (reverse chronological order - newest change first)
150 */
151 CURSOR cur_rowid IS
152 SELECT rowid
153 FROM igi_iac_cal_idx_values
154 WHERE ((cal_price_index_link_id = x_cal_price_index_link_id));
155
156 lv_rowid cur_rowid%RowType;
157
158 BEGIN
159
160 OPEN cur_rowid;
161 FETCH cur_rowid INTO lv_rowid;
162 IF (cur_rowid%FOUND) THEN
163 CLOSE cur_rowid;
164 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
165 app_exception.raise_exception;
166 RETURN;
167 END IF;
168 CLOSE cur_rowid;
169
170 END get_fk_igi_iac_cal_price_idx;
171
172
173 PROCEDURE before_dml (
174 p_action IN VARCHAR2,
175 x_rowid IN VARCHAR2 ,
176 x_cal_price_index_link_id IN NUMBER ,
177 x_date_from IN DATE ,
178 x_date_to IN DATE ,
179 x_original_price_index_value IN NUMBER ,
180 x_current_price_index_value IN NUMBER ,
181 x_creation_date IN DATE ,
182 x_created_by IN NUMBER ,
183 x_last_update_date IN DATE ,
184 x_last_updated_by IN NUMBER ,
185 x_last_update_login IN NUMBER
186 ) AS
187 /*
188 || Created By : [email protected]
189 || Created On : 12-APR-2002
190 || Purpose : Initialises the columns, Checks Constraints, Calls the
191 || Trigger Handlers for the table, before any DML operation.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 BEGIN
198
199 set_column_values (
200 p_action,
201 x_rowid,
202 x_cal_price_index_link_id,
203 x_date_from,
204 x_date_to,
205 x_original_price_index_value,
206 x_current_price_index_value,
207 x_creation_date,
208 x_created_by,
209 x_last_update_date,
210 x_last_updated_by,
211 x_last_update_login
212 );
213
214 IF (p_action = 'INSERT') THEN
215 -- Call all the procedures related to Before Insert.
216 IF ( get_pk_for_validation(
217 new_references.cal_price_index_link_id,
218 new_references.date_from,
219 new_references.date_to
220 )
221 ) THEN
222 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
223 app_exception.raise_exception;
224 END IF;
225 check_parent_existance;
226 ELSIF (p_action = 'UPDATE') THEN
227 -- Call all the procedures related to Before Update.
228 check_parent_existance;
229 ELSIF (p_action = 'VALIDATE_INSERT') THEN
230 -- Call all the procedures related to Before Insert.
231 IF ( get_pk_for_validation (
232 new_references.cal_price_index_link_id,
233 new_references.date_from,
234 new_references.date_to
235 )
236 ) THEN
237 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
238 app_exception.raise_exception;
239 END IF;
240 END IF;
241
242 END before_dml;
243
244
245 PROCEDURE insert_row (
246 x_rowid IN OUT NOCOPY VARCHAR2,
247 x_cal_price_index_link_id IN NUMBER,
248 x_date_from IN DATE,
249 x_date_to IN DATE,
250 x_original_price_index_value IN NUMBER,
251 x_current_price_index_value IN NUMBER,
252 x_mode IN VARCHAR2
253 ) AS
254 /*
255 || Created By : [email protected]
256 || Created On : 12-APR-2002
257 || Purpose : Handles the INSERT DML logic for the table.
258 || Known limitations, enhancements or remarks :
259 || Change History :
260 || Who When What
261 || (reverse chronological order - newest change first)
262 */
263 CURSOR c IS
264 SELECT rowid
265 FROM igi_iac_cal_idx_values
266 WHERE cal_price_index_link_id = x_cal_price_index_link_id
267 AND date_from = x_date_from
268 AND date_to = x_date_to;
269
270 x_last_update_date DATE;
271 x_last_updated_by NUMBER;
272 x_last_update_login NUMBER;
273
274 BEGIN
275
276 x_last_update_date := SYSDATE;
277 IF (x_mode = 'I') THEN
278 x_last_updated_by := 1;
279 x_last_update_login := 0;
280 ELSIF (x_mode = 'R') THEN
281 x_last_updated_by := fnd_global.user_id;
282 IF (x_last_updated_by IS NULL) THEN
283 x_last_updated_by := -1;
284 END IF;
285 x_last_update_login := fnd_global.login_id;
286 IF (x_last_update_login IS NULL) THEN
287 x_last_update_login := -1;
288 END IF;
289 ELSE
290 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
291 app_exception.raise_exception;
292 END IF;
293
294 before_dml(
295 p_action => 'INSERT',
296 x_rowid => x_rowid,
297 x_cal_price_index_link_id => x_cal_price_index_link_id,
298 x_date_from => x_date_from,
299 x_date_to => x_date_to,
300 x_original_price_index_value => x_original_price_index_value,
301 x_current_price_index_value => x_current_price_index_value,
302 x_creation_date => x_last_update_date,
303 x_created_by => x_last_updated_by,
304 x_last_update_date => x_last_update_date,
305 x_last_updated_by => x_last_updated_by,
306 x_last_update_login => x_last_update_login
307 );
308
309 INSERT INTO igi_iac_cal_idx_values (
310 cal_price_index_link_id,
311 date_from,
312 date_to,
313 original_price_index_value,
314 current_price_index_value,
315 creation_date,
316 created_by,
317 last_update_date,
318 last_updated_by,
319 last_update_login
320 ) VALUES (
321 new_references.cal_price_index_link_id,
322 new_references.date_from,
323 new_references.date_to,
324 new_references.original_price_index_value,
325 new_references.current_price_index_value,
326 x_last_update_date,
327 x_last_updated_by,
328 x_last_update_date,
329 x_last_updated_by,
330 x_last_update_login
331 );
332
333 OPEN c;
334 FETCH c INTO x_rowid;
335 IF (c%NOTFOUND) THEN
336 CLOSE c;
337 RAISE NO_DATA_FOUND;
338 END IF;
339 CLOSE c;
340
341 END insert_row;
342
343
344 PROCEDURE lock_row (
345 x_rowid IN VARCHAR2,
346 x_cal_price_index_link_id IN NUMBER,
347 x_date_from IN DATE,
348 x_date_to IN DATE,
349 x_original_price_index_value IN NUMBER,
350 x_current_price_index_value IN NUMBER
351 ) AS
352 /*
353 || Created By : [email protected]
354 || Created On : 12-APR-2002
355 || Purpose : Handles the LOCK mechanism for the table.
356 || Known limitations, enhancements or remarks :
357 || Change History :
358 || Who When What
359 || (reverse chronological order - newest change first)
360 */
361 CURSOR c1 IS
362 SELECT
363 original_price_index_value,
364 current_price_index_value
365 FROM igi_iac_cal_idx_values
366 WHERE rowid = x_rowid
367 FOR UPDATE NOWAIT;
368
369 tlinfo c1%ROWTYPE;
370
371 BEGIN
372
373 OPEN c1;
374 FETCH c1 INTO tlinfo;
375 IF (c1%notfound) THEN
376 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
377 CLOSE c1;
378 app_exception.raise_exception;
379 RETURN;
380 END IF;
381 CLOSE c1;
382
383 IF (
384 ((tlinfo.original_price_index_value = x_original_price_index_value) OR ((tlinfo.original_price_index_value IS NULL) AND (X_original_price_index_value IS NULL)))
385 AND (tlinfo.current_price_index_value = x_current_price_index_value)
386 ) THEN
387 NULL;
388 ELSE
389 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
390 app_exception.raise_exception;
391 END IF;
392
393 RETURN;
394
395 END lock_row;
396
397
398 PROCEDURE update_row (
399 x_rowid IN VARCHAR2,
400 x_cal_price_index_link_id IN NUMBER,
401 x_date_from IN DATE,
402 x_date_to IN DATE,
403 x_original_price_index_value IN NUMBER,
404 x_current_price_index_value IN NUMBER,
405 x_mode IN VARCHAR2
406 ) AS
407 /*
408 || Created By : [email protected]
409 || Created On : 12-APR-2002
410 || Purpose : Handles the UPDATE DML logic for the table.
411 || Known limitations, enhancements or remarks :
412 || Change History :
413 || Who When What
414 || (reverse chronological order - newest change first)
415 */
416 x_last_update_date DATE ;
417 x_last_updated_by NUMBER;
418 x_last_update_login NUMBER;
419
420 BEGIN
421
422 x_last_update_date := SYSDATE;
423 IF (X_MODE = 'I') THEN
424 x_last_updated_by := 1;
425 x_last_update_login := 0;
426 ELSIF (x_mode = 'R') THEN
427 x_last_updated_by := fnd_global.user_id;
428 IF x_last_updated_by IS NULL THEN
429 x_last_updated_by := -1;
430 END IF;
431 x_last_update_login := fnd_global.login_id;
432 IF (x_last_update_login IS NULL) THEN
433 x_last_update_login := -1;
434 END IF;
435 ELSE
436 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
437 app_exception.raise_exception;
438 END IF;
439
440 before_dml(
441 p_action => 'UPDATE',
442 x_rowid => x_rowid,
443 x_cal_price_index_link_id => x_cal_price_index_link_id,
444 x_date_from => x_date_from,
445 x_date_to => x_date_to,
446 x_original_price_index_value => x_original_price_index_value,
447 x_current_price_index_value => x_current_price_index_value,
448 x_creation_date => x_last_update_date,
449 x_created_by => x_last_updated_by,
450 x_last_update_date => x_last_update_date,
451 x_last_updated_by => x_last_updated_by,
452 x_last_update_login => x_last_update_login
453 );
454
455 UPDATE igi_iac_cal_idx_values
456 SET
457 original_price_index_value = new_references.original_price_index_value,
458 current_price_index_value = new_references.current_price_index_value,
459 last_update_date = x_last_update_date,
460 last_updated_by = x_last_updated_by,
461 last_update_login = x_last_update_login
462 WHERE rowid = x_rowid;
463
464 IF (SQL%NOTFOUND) THEN
465 RAISE NO_DATA_FOUND;
466 END IF;
467
468 END update_row;
469
470
471 PROCEDURE add_row (
472 x_rowid IN OUT NOCOPY VARCHAR2,
473 x_cal_price_index_link_id IN NUMBER,
474 x_date_from IN DATE,
475 x_date_to IN DATE,
476 x_original_price_index_value IN NUMBER,
477 x_current_price_index_value IN NUMBER,
478 x_mode IN VARCHAR2
479 ) AS
480 /*
481 || Created By : [email protected]
482 || Created On : 12-APR-2002
483 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
484 || Known limitations, enhancements or remarks :
485 || Change History :
486 || Who When What
487 || (reverse chronological order - newest change first)
488 */
489 CURSOR c1 IS
490 SELECT rowid
491 FROM igi_iac_cal_idx_values
492 WHERE cal_price_index_link_id = x_cal_price_index_link_id
493 AND date_from = x_date_from
494 AND date_to = x_date_to;
495
496 BEGIN
497
498 OPEN c1;
499 FETCH c1 INTO x_rowid;
500 IF (c1%NOTFOUND) THEN
501 CLOSE c1;
502
503 insert_row (
504 x_rowid,
505 x_cal_price_index_link_id,
506 x_date_from,
507 x_date_to,
508 x_original_price_index_value,
509 x_current_price_index_value,
510 x_mode
511 );
512 RETURN;
513 END IF;
514 CLOSE c1;
515
516 update_row (
517 x_rowid,
518 x_cal_price_index_link_id,
519 x_date_from,
520 x_date_to,
521 x_original_price_index_value,
522 x_current_price_index_value,
523 x_mode
524 );
525
526 END add_row;
527
528
529 PROCEDURE delete_row (
530 x_rowid IN VARCHAR2
531 ) AS
532 /*
533 || Created By : [email protected]
534 || Created On : 12-APR-2002
535 || Purpose : Handles the DELETE DML logic for the table.
536 || Known limitations, enhancements or remarks :
537 || Change History :
538 || Who When What
539 || (reverse chronological order - newest change first)
540 */
541 BEGIN
542
543 before_dml (
544 p_action => 'DELETE',
545 x_rowid => x_rowid
546 );
547
548 DELETE FROM igi_iac_cal_idx_values
549 WHERE rowid = x_rowid;
550
551 IF (SQL%NOTFOUND) THEN
552 RAISE NO_DATA_FOUND;
553 END IF;
554
555 END delete_row;
556
557
558 END igi_iac_cal_idx_values_pkg;
[Home] [Help]