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