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