[Home] [Help]
PACKAGE BODY: APPS.IGS_DA_REQ_WIF_PKG
Source
1 PACKAGE BODY igs_da_req_wif_pkg AS
2 /* $Header: IGSKI43B.pls 115.1 2003/04/16 05:40:10 smanglm noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_da_req_wif%ROWTYPE;
6 new_references igs_da_req_wif%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_batch_id IN NUMBER,
12 x_wif_id IN NUMBER,
13 x_program_code IN VARCHAR2,
14 x_catalog_cal_type IN VARCHAR2,
15 x_catalog_ci_seq_num IN NUMBER,
16 x_major_unit_set_cd1 IN VARCHAR2,
17 x_major_unit_set_cd2 IN VARCHAR2,
18 x_major_unit_set_cd3 IN VARCHAR2,
19 x_minor_unit_set_cd1 IN VARCHAR2,
20 x_minor_unit_set_cd2 IN VARCHAR2,
21 x_minor_unit_set_cd3 IN VARCHAR2,
22 x_track_unit_set_cd1 IN VARCHAR2,
23 x_track_unit_set_cd2 IN VARCHAR2,
24 x_track_unit_set_cd3 IN VARCHAR2,
25 x_creation_date IN DATE,
26 x_created_by IN NUMBER,
27 x_last_update_date IN DATE,
28 x_last_updated_by IN NUMBER,
29 x_last_update_login IN NUMBER
30 ) AS
31 /*
32 || Created By :
33 || Created On : 27-MAR-2003
34 || Purpose : Initialises the Old and New references for the columns of the table.
35 || Known limitations, enhancements or remarks :
36 || Change History :
37 || Who When What
38 || (reverse chronological order - newest change first)
39 */
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM igs_da_req_wif
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 OPEN cur_old_ref_values;
53 FETCH cur_old_ref_values INTO old_references;
54 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55 CLOSE cur_old_ref_values;
56 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57 igs_ge_msg_stack.add;
58 app_exception.raise_exception;
59 RETURN;
60 END IF;
61 CLOSE cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.batch_id := x_batch_id;
65 new_references.wif_id := x_wif_id;
66 new_references.program_code := x_program_code;
67 new_references.catalog_cal_type := x_catalog_cal_type;
68 new_references.catalog_ci_seq_num := x_catalog_ci_seq_num;
69 new_references.major_unit_set_cd1 := x_major_unit_set_cd1;
70 new_references.major_unit_set_cd2 := x_major_unit_set_cd2;
71 new_references.major_unit_set_cd3 := x_major_unit_set_cd3;
72 new_references.minor_unit_set_cd1 := x_minor_unit_set_cd1;
73 new_references.minor_unit_set_cd2 := x_minor_unit_set_cd2;
74 new_references.minor_unit_set_cd3 := x_minor_unit_set_cd3;
75 new_references.track_unit_set_cd1 := x_track_unit_set_cd1;
76 new_references.track_unit_set_cd2 := x_track_unit_set_cd2;
77 new_references.track_unit_set_cd3 := x_track_unit_set_cd3;
78
79 IF (p_action = 'UPDATE') THEN
80 new_references.creation_date := old_references.creation_date;
81 new_references.created_by := old_references.created_by;
82 ELSE
83 new_references.creation_date := x_creation_date;
84 new_references.created_by := x_created_by;
85 END IF;
86
87 new_references.last_update_date := x_last_update_date;
88 new_references.last_updated_by := x_last_updated_by;
89 new_references.last_update_login := x_last_update_login;
90
91 END set_column_values;
92
93
94 PROCEDURE check_parent_existance AS
95 /*
96 || Created By :
97 || Created On : 27-MAR-2003
98 || Purpose : Checks for the existance of Parent records.
99 || Known limitations, enhancements or remarks :
100 || Change History :
101 || Who When What
102 || (reverse chronological order - newest change first)
103 */
104 BEGIN
105 IF (((old_references.batch_id = new_references.batch_id)) OR
106 ((new_references.batch_id IS NULL))) THEN
107 NULL;
108 ELSIF NOT igs_da_rqst_pkg.get_pk_for_validation (
109 new_references.batch_id
110 ) THEN
111 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
112 igs_ge_msg_stack.add;
113 app_exception.raise_exception;
114 END IF;
115 IF (((old_references.program_code = new_references.program_code)) OR
116 ((new_references.program_code IS NULL))) THEN
117 NULL;
118 ELSIF NOT igs_ps_course_pkg.get_pk_for_validation (
119 new_references.program_code
120 ) THEN
121 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
122 igs_ge_msg_stack.add;
123 app_exception.raise_exception;
124 END IF;
125 IF (((old_references.catalog_cal_type = new_references.catalog_cal_type) AND
126 (old_references.catalog_ci_seq_num = new_references.catalog_ci_seq_num)) OR
127 ((new_references.catalog_cal_type IS NULL) OR
128 (new_references.catalog_ci_seq_num IS NULL))) THEN
129 NULL;
130 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
131 new_references.catalog_cal_type,
132 new_references.catalog_ci_seq_num
133 ) THEN
134 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
135 igs_ge_msg_stack.add;
136 app_exception.raise_exception;
137 END IF;
138
139 END check_parent_existance;
140
141
142 PROCEDURE check_child_existance AS
143 /*
144 || Created By :
145 || Created On : 27-MAR-2003
146 || Purpose : Checks for the existance of Child records.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 BEGIN
153
154 igs_da_req_stdnts_pkg.get_fk_igs_da_req_wif (
155 old_references.batch_id,
156 old_references.wif_id
157 );
158
159 END check_child_existance;
160
161
162 FUNCTION get_pk_for_validation (
163 x_batch_id IN NUMBER,
164 x_wif_id IN NUMBER
165 ) RETURN BOOLEAN AS
166 /*
167 || Created By :
168 || Created On : 27-MAR-2003
169 || Purpose : Validates the Primary Key of the table.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 CURSOR cur_rowid IS
176 SELECT rowid
177 FROM igs_da_req_wif
178 WHERE batch_id = x_batch_id
179 AND wif_id = x_wif_id
180 FOR UPDATE NOWAIT;
181
182 lv_rowid cur_rowid%RowType;
183
184 BEGIN
185
186 OPEN cur_rowid;
187 FETCH cur_rowid INTO lv_rowid;
188 IF (cur_rowid%FOUND) THEN
189 CLOSE cur_rowid;
190 RETURN(TRUE);
191 ELSE
192 CLOSE cur_rowid;
193 RETURN(FALSE);
194 END IF;
195
196 END get_pk_for_validation;
197
198
199 PROCEDURE get_fk_igs_da_rqst (
200 x_batch_id IN NUMBER
201 ) AS
202 /*
203 || Created By :
204 || Created On : 27-MAR-2003
205 || Purpose : Validates the Foreign Keys for the table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || (reverse chronological order - newest change first)
210 */
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM igs_da_req_wif
214 WHERE ((batch_id = x_batch_id));
215
216 lv_rowid cur_rowid%RowType;
217
218 BEGIN
219
220 OPEN cur_rowid;
221 FETCH cur_rowid INTO lv_rowid;
222 IF (cur_rowid%FOUND) THEN
223 CLOSE cur_rowid;
224 fnd_message.set_name ('IGS', 'IGS_DA_RQST_WIF_FK');
225 igs_ge_msg_stack.add;
226 app_exception.raise_exception;
227 RETURN;
228 END IF;
229 CLOSE cur_rowid;
230
231 END get_fk_igs_da_rqst;
232
233
234 PROCEDURE get_fk_igs_ps_course (
235 x_course_cd IN VARCHAR2
236 ) AS
237 /*
238 || Created By :
239 || Created On : 27-MAR-2003
240 || Purpose : Validates the Foreign Keys for the table.
241 || Known limitations, enhancements or remarks :
242 || Change History :
243 || Who When What
244 || (reverse chronological order - newest change first)
245 */
246 CURSOR cur_rowid IS
247 SELECT rowid
248 FROM igs_da_req_wif
249 WHERE ((program_code = x_course_cd));
250
251 lv_rowid cur_rowid%RowType;
252
253 BEGIN
254
255 OPEN cur_rowid;
256 FETCH cur_rowid INTO lv_rowid;
257 IF (cur_rowid%FOUND) THEN
258 CLOSE cur_rowid;
259 fnd_message.set_name ('IGS', 'IGS_DA_PS_WIF_FK');
260 igs_ge_msg_stack.add;
261 app_exception.raise_exception;
262 RETURN;
263 END IF;
264 CLOSE cur_rowid;
265
266 END get_fk_igs_ps_course;
267
268
269 PROCEDURE get_fk_igs_ca_inst (
270 x_cal_type IN VARCHAR2,
271 x_sequence_number IN NUMBER
272 ) AS
273 /*
274 || Created By :
275 || Created On : 27-MAR-2003
276 || Purpose : Validates the Foreign Keys 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 cur_rowid IS
283 SELECT rowid
284 FROM igs_da_req_wif
285 WHERE ((catalog_cal_type = x_cal_type) AND
286 (catalog_ci_seq_num = x_sequence_number));
287
288 lv_rowid cur_rowid%RowType;
289
290 BEGIN
291
292 OPEN cur_rowid;
293 FETCH cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 CLOSE cur_rowid;
296 fnd_message.set_name ('IGS', 'IGS_DA_CA_WIF_FK');
297 igs_ge_msg_stack.add;
298 app_exception.raise_exception;
299 RETURN;
300 END IF;
301 CLOSE cur_rowid;
302
303 END get_fk_igs_ca_inst;
304
305
306 PROCEDURE before_dml (
307 p_action IN VARCHAR2,
308 x_rowid IN VARCHAR2,
309 x_batch_id IN NUMBER,
310 x_wif_id IN NUMBER,
311 x_program_code IN VARCHAR2,
312 x_catalog_cal_type IN VARCHAR2,
313 x_catalog_ci_seq_num IN NUMBER,
314 x_major_unit_set_cd1 IN VARCHAR2,
315 x_major_unit_set_cd2 IN VARCHAR2,
316 x_major_unit_set_cd3 IN VARCHAR2,
317 x_minor_unit_set_cd1 IN VARCHAR2,
318 x_minor_unit_set_cd2 IN VARCHAR2,
319 x_minor_unit_set_cd3 IN VARCHAR2,
320 x_track_unit_set_cd1 IN VARCHAR2,
321 x_track_unit_set_cd2 IN VARCHAR2,
322 x_track_unit_set_cd3 IN VARCHAR2,
323 x_creation_date IN DATE,
324 x_created_by IN NUMBER,
325 x_last_update_date IN DATE,
326 x_last_updated_by IN NUMBER,
327 x_last_update_login IN NUMBER
328 ) AS
329 /*
330 || Created By :
331 || Created On : 27-MAR-2003
332 || Purpose : Initialises the columns, Checks Constraints, Calls the
333 || Trigger Handlers for the table, before any DML operation.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339 BEGIN
340 set_column_values (
341 p_action,
342 x_rowid,
343 x_batch_id,
344 x_wif_id,
345 x_program_code,
346 x_catalog_cal_type,
347 x_catalog_ci_seq_num,
348 x_major_unit_set_cd1,
349 x_major_unit_set_cd2,
350 x_major_unit_set_cd3,
351 x_minor_unit_set_cd1,
352 x_minor_unit_set_cd2,
353 x_minor_unit_set_cd3,
354 x_track_unit_set_cd1,
355 x_track_unit_set_cd2,
356 x_track_unit_set_cd3,
357 x_creation_date,
358 x_created_by,
359 x_last_update_date,
360 x_last_updated_by,
361 x_last_update_login
362 );
363 IF (p_action = 'INSERT') THEN
364 -- Call all the procedures related to Before Insert.
365 IF ( get_pk_for_validation(
366 new_references.batch_id,
367 new_references.wif_id
368 )
369 ) THEN
370 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374 check_parent_existance;
375 ELSIF (p_action = 'UPDATE') THEN
376 -- Call all the procedures related to Before Update.
377 check_parent_existance;
378 ELSIF (p_action = 'DELETE') THEN
379 -- Call all the procedures related to Before Delete.
380 check_child_existance;
381 ELSIF (p_action = 'VALIDATE_INSERT') THEN
382 -- Call all the procedures related to Before Insert.
383 IF ( get_pk_for_validation (
384 new_references.batch_id,
385 new_references.wif_id
386 )
387 ) THEN
388 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
389 igs_ge_msg_stack.add;
390 app_exception.raise_exception;
391 END IF;
392 ELSIF (p_action = 'VALIDATE_DELETE') THEN
393 check_child_existance;
394 END IF;
395
396 END before_dml;
397
398
399 PROCEDURE insert_row (
400 x_rowid IN OUT NOCOPY VARCHAR2,
401 x_batch_id IN NUMBER,
402 x_wif_id IN NUMBER,
403 x_program_code IN VARCHAR2,
404 x_catalog_cal_type IN VARCHAR2,
405 x_catalog_ci_seq_num IN NUMBER,
406 x_major_unit_set_cd1 IN VARCHAR2,
407 x_major_unit_set_cd2 IN VARCHAR2,
408 x_major_unit_set_cd3 IN VARCHAR2,
409 x_minor_unit_set_cd1 IN VARCHAR2,
410 x_minor_unit_set_cd2 IN VARCHAR2,
411 x_minor_unit_set_cd3 IN VARCHAR2,
412 x_track_unit_set_cd1 IN VARCHAR2,
413 x_track_unit_set_cd2 IN VARCHAR2,
414 x_track_unit_set_cd3 IN VARCHAR2,
415 x_mode IN VARCHAR2,
416 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
417 X_MSG_DATA OUT NOCOPY VARCHAR2,
418 X_MSG_COUNT OUT NOCOPY NUMBER
419 ) AS
420 /*
421 || Created By :
422 || Created On : 27-MAR-2003
423 || Purpose : Handles the INSERT DML logic for the table.
424 || Known limitations, enhancements or remarks :
425 || Change History :
426 || Who When What
427 || (reverse chronological order - newest change first)
428 */
429
430 x_last_update_date DATE;
431 x_last_updated_by NUMBER;
432 x_last_update_login NUMBER;
433
434 BEGIN
435 FND_MSG_PUB.initialize;
436 x_last_update_date := SYSDATE;
437 IF (x_mode = 'I') THEN
438 x_last_updated_by := 1;
439 x_last_update_login := 0;
440 ELSIF (x_mode = 'R') THEN
441 x_last_updated_by := fnd_global.user_id;
442 IF (x_last_updated_by IS NULL) THEN
443 x_last_updated_by := -1;
444 END IF;
445 x_last_update_login := fnd_global.login_id;
446 IF (x_last_update_login IS NULL) THEN
447 x_last_update_login := -1;
448 END IF;
449 ELSE
450 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
451 fnd_message.set_token ('ROUTINE', 'IGS_DA_REQ_WIF_PKG.INSERT_ROW');
452 igs_ge_msg_stack.add;
453 app_exception.raise_exception;
454 END IF;
455 before_dml(
456 p_action => 'INSERT',
457 x_rowid => x_rowid,
458 x_batch_id => x_batch_id,
459 x_wif_id => x_wif_id,
460 x_program_code => x_program_code,
461 x_catalog_cal_type => x_catalog_cal_type,
462 x_catalog_ci_seq_num => x_catalog_ci_seq_num,
463 x_major_unit_set_cd1 => x_major_unit_set_cd1,
464 x_major_unit_set_cd2 => x_major_unit_set_cd2,
465 x_major_unit_set_cd3 => x_major_unit_set_cd3,
466 x_minor_unit_set_cd1 => x_minor_unit_set_cd1,
467 x_minor_unit_set_cd2 => x_minor_unit_set_cd2,
468 x_minor_unit_set_cd3 => x_minor_unit_set_cd3,
469 x_track_unit_set_cd1 => x_track_unit_set_cd1,
470 x_track_unit_set_cd2 => x_track_unit_set_cd2,
471 x_track_unit_set_cd3 => x_track_unit_set_cd3,
472 x_creation_date => x_last_update_date,
473 x_created_by => x_last_updated_by,
474 x_last_update_date => x_last_update_date,
475 x_last_updated_by => x_last_updated_by,
476 x_last_update_login => x_last_update_login
477 );
478 INSERT INTO igs_da_req_wif (
479 batch_id,
480 wif_id,
481 program_code,
482 catalog_cal_type,
483 catalog_ci_seq_num,
484 major_unit_set_cd1,
485 major_unit_set_cd2,
486 major_unit_set_cd3,
487 minor_unit_set_cd1,
488 minor_unit_set_cd2,
489 minor_unit_set_cd3,
490 track_unit_set_cd1,
491 track_unit_set_cd2,
492 track_unit_set_cd3,
493 creation_date,
494 created_by,
495 last_update_date,
496 last_updated_by,
497 last_update_login
498 ) VALUES (
499 new_references.batch_id,
500 new_references.wif_id,
501 new_references.program_code,
502 new_references.catalog_cal_type,
503 new_references.catalog_ci_seq_num,
504 new_references.major_unit_set_cd1,
505 new_references.major_unit_set_cd2,
506 new_references.major_unit_set_cd3,
507 new_references.minor_unit_set_cd1,
508 new_references.minor_unit_set_cd2,
509 new_references.minor_unit_set_cd3,
510 new_references.track_unit_set_cd1,
511 new_references.track_unit_set_cd2,
512 new_references.track_unit_set_cd3,
513 x_last_update_date,
514 x_last_updated_by,
515 x_last_update_date,
516 x_last_updated_by,
517 x_last_update_login
518 ) RETURNING ROWID INTO x_rowid;
519 -- Initialize API return status to success.
520 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
521 -- Standard call to get message count and if count is 1, get message
522 -- info.
523 FND_MSG_PUB.Count_And_Get(
524 p_encoded => FND_API.G_FALSE,
525 p_count => x_MSG_COUNT,
526 p_data => X_MSG_DATA);
527
528 EXCEPTION
529 WHEN FND_API.G_EXC_ERROR THEN
530 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
531 FND_MSG_PUB.Count_And_Get(
532 p_encoded => FND_API.G_FALSE,
533 p_count => x_MSG_COUNT,
534 p_data => X_MSG_DATA);
535 RETURN;
536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
537 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
538 FND_MSG_PUB.Count_And_Get(
539 p_encoded => FND_API.G_FALSE,
540 p_count => x_MSG_COUNT,
541 p_data => X_MSG_DATA);
542 RETURN;
543 WHEN OTHERS THEN
544 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
545 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
546 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
547 FND_MSG_PUB.ADD;
548 FND_MSG_PUB.Count_And_Get(
549 p_encoded => FND_API.G_FALSE,
550 p_count => x_MSG_COUNT,
551 p_data => X_MSG_DATA);
552 RETURN;
553
554 END insert_row;
555
556
557 PROCEDURE lock_row (
558 x_rowid IN VARCHAR2,
559 x_batch_id IN NUMBER,
560 x_wif_id IN NUMBER,
561 x_program_code IN VARCHAR2,
562 x_catalog_cal_type IN VARCHAR2,
563 x_catalog_ci_seq_num IN NUMBER,
564 x_major_unit_set_cd1 IN VARCHAR2,
565 x_major_unit_set_cd2 IN VARCHAR2,
566 x_major_unit_set_cd3 IN VARCHAR2,
567 x_minor_unit_set_cd1 IN VARCHAR2,
568 x_minor_unit_set_cd2 IN VARCHAR2,
569 x_minor_unit_set_cd3 IN VARCHAR2,
570 x_track_unit_set_cd1 IN VARCHAR2,
571 x_track_unit_set_cd2 IN VARCHAR2,
572 x_track_unit_set_cd3 IN VARCHAR2,
573 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
574 X_MSG_DATA OUT NOCOPY VARCHAR2,
575 X_MSG_COUNT OUT NOCOPY NUMBER
576 ) AS
577 /*
578 || Created By :
579 || Created On : 27-MAR-2003
580 || Purpose : Handles the LOCK mechanism for the table.
581 || Known limitations, enhancements or remarks :
582 || Change History :
583 || Who When What
584 || (reverse chronological order - newest change first)
585 */
586 CURSOR c1 IS
587 SELECT
588 program_code,
589 catalog_cal_type,
590 catalog_ci_seq_num,
591 major_unit_set_cd1,
592 major_unit_set_cd2,
593 major_unit_set_cd3,
594 minor_unit_set_cd1,
595 minor_unit_set_cd2,
596 minor_unit_set_cd3,
597 track_unit_set_cd1,
598 track_unit_set_cd2,
599 track_unit_set_cd3
600 FROM igs_da_req_wif
601 WHERE rowid = x_rowid
602 FOR UPDATE NOWAIT;
603
604 tlinfo c1%ROWTYPE;
605
606 BEGIN
607 FND_MSG_PUB.initialize;
608 OPEN c1;
609 FETCH c1 INTO tlinfo;
610 IF (c1%notfound) THEN
611 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
612 igs_ge_msg_stack.add;
613 CLOSE c1;
614 app_exception.raise_exception;
615 RETURN;
616 END IF;
617 CLOSE c1;
618
619 IF (
620 (tlinfo.program_code = x_program_code)
621 AND ((tlinfo.catalog_cal_type = x_catalog_cal_type) OR ((tlinfo.catalog_cal_type IS NULL) AND (X_catalog_cal_type IS NULL)))
622 AND ((tlinfo.catalog_ci_seq_num = x_catalog_ci_seq_num) OR ((tlinfo.catalog_ci_seq_num IS NULL) AND (X_catalog_ci_seq_num IS NULL)))
623 AND ((tlinfo.major_unit_set_cd1 = x_major_unit_set_cd1) OR ((tlinfo.major_unit_set_cd1 IS NULL) AND (X_major_unit_set_cd1 IS NULL)))
624 AND ((tlinfo.major_unit_set_cd2 = x_major_unit_set_cd2) OR ((tlinfo.major_unit_set_cd2 IS NULL) AND (X_major_unit_set_cd2 IS NULL)))
625 AND ((tlinfo.major_unit_set_cd3 = x_major_unit_set_cd3) OR ((tlinfo.major_unit_set_cd3 IS NULL) AND (X_major_unit_set_cd3 IS NULL)))
626 AND ((tlinfo.minor_unit_set_cd1 = x_minor_unit_set_cd1) OR ((tlinfo.minor_unit_set_cd1 IS NULL) AND (X_minor_unit_set_cd1 IS NULL)))
627 AND ((tlinfo.minor_unit_set_cd2 = x_minor_unit_set_cd2) OR ((tlinfo.minor_unit_set_cd2 IS NULL) AND (X_minor_unit_set_cd2 IS NULL)))
628 AND ((tlinfo.minor_unit_set_cd3 = x_minor_unit_set_cd3) OR ((tlinfo.minor_unit_set_cd3 IS NULL) AND (X_minor_unit_set_cd3 IS NULL)))
629 AND ((tlinfo.track_unit_set_cd1 = x_track_unit_set_cd1) OR ((tlinfo.track_unit_set_cd1 IS NULL) AND (X_track_unit_set_cd1 IS NULL)))
630 AND ((tlinfo.track_unit_set_cd2 = x_track_unit_set_cd2) OR ((tlinfo.track_unit_set_cd2 IS NULL) AND (X_track_unit_set_cd2 IS NULL)))
631 AND ((tlinfo.track_unit_set_cd3 = x_track_unit_set_cd3) OR ((tlinfo.track_unit_set_cd3 IS NULL) AND (X_track_unit_set_cd3 IS NULL)))
632 ) THEN
633 NULL;
634 ELSE
635 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
636 igs_ge_msg_stack.add;
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639
640 -- Initialize API return status to success.
641 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
642 -- Standard call to get message count and if count is 1, get message
643 -- info.
644 FND_MSG_PUB.Count_And_Get(
645 p_encoded => FND_API.G_FALSE,
646 p_count => x_MSG_COUNT,
647 p_data => X_MSG_DATA);
648 RETURN;
649 EXCEPTION
650 WHEN FND_API.G_EXC_ERROR THEN
651 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
652 FND_MSG_PUB.Count_And_Get(
653 p_encoded => FND_API.G_FALSE,
654 p_count => x_MSG_COUNT,
655 p_data => X_MSG_DATA);
656 RETURN;
657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
658 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
659 FND_MSG_PUB.Count_And_Get(
660 p_encoded => FND_API.G_FALSE,
661 p_count => x_MSG_COUNT,
662 p_data => X_MSG_DATA);
663 RETURN;
664 WHEN OTHERS THEN
665 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
666 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
667 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
668 FND_MSG_PUB.ADD;
669 FND_MSG_PUB.Count_And_Get(
670 p_encoded => FND_API.G_FALSE,
671 p_count => x_MSG_COUNT,
672 p_data => X_MSG_DATA);
673 RETURN;
674
675
676 END lock_row;
677
678
679 PROCEDURE update_row (
680 x_rowid IN VARCHAR2,
681 x_batch_id IN NUMBER,
682 x_wif_id IN NUMBER,
683 x_program_code IN VARCHAR2,
684 x_catalog_cal_type IN VARCHAR2,
685 x_catalog_ci_seq_num IN NUMBER,
686 x_major_unit_set_cd1 IN VARCHAR2,
687 x_major_unit_set_cd2 IN VARCHAR2,
688 x_major_unit_set_cd3 IN VARCHAR2,
689 x_minor_unit_set_cd1 IN VARCHAR2,
690 x_minor_unit_set_cd2 IN VARCHAR2,
691 x_minor_unit_set_cd3 IN VARCHAR2,
692 x_track_unit_set_cd1 IN VARCHAR2,
693 x_track_unit_set_cd2 IN VARCHAR2,
694 x_track_unit_set_cd3 IN VARCHAR2,
695 x_mode IN VARCHAR2,
696 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
697 X_MSG_DATA OUT NOCOPY VARCHAR2,
698 X_MSG_COUNT OUT NOCOPY NUMBER
699 ) AS
700 /*
701 || Created By :
702 || Created On : 27-MAR-2003
703 || Purpose : Handles the UPDATE DML logic for the table.
704 || Known limitations, enhancements or remarks :
705 || Change History :
706 || Who When What
707 || (reverse chronological order - newest change first)
708 */
709 x_last_update_date DATE ;
710 x_last_updated_by NUMBER;
711 x_last_update_login NUMBER;
712
713 BEGIN
714 FND_MSG_PUB.initialize;
715 x_last_update_date := SYSDATE;
716 IF (X_MODE = 'I') THEN
717 x_last_updated_by := 1;
718 x_last_update_login := 0;
719 ELSIF (x_mode = 'R') THEN
720 x_last_updated_by := fnd_global.user_id;
721 IF x_last_updated_by IS NULL THEN
722 x_last_updated_by := -1;
723 END IF;
724 x_last_update_login := fnd_global.login_id;
725 IF (x_last_update_login IS NULL) THEN
726 x_last_update_login := -1;
727 END IF;
728 ELSE
729 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
730 fnd_message.set_token ('ROUTINE', 'IGS_DA_REQ_WIF_PKG.UPDATE_ROW');
731 igs_ge_msg_stack.add;
732 app_exception.raise_exception;
733 END IF;
734
735 before_dml(
736 p_action => 'UPDATE',
737 x_rowid => x_rowid,
738 x_batch_id => x_batch_id,
739 x_wif_id => x_wif_id,
740 x_program_code => x_program_code,
741 x_catalog_cal_type => x_catalog_cal_type,
742 x_catalog_ci_seq_num => x_catalog_ci_seq_num,
743 x_major_unit_set_cd1 => x_major_unit_set_cd1,
744 x_major_unit_set_cd2 => x_major_unit_set_cd2,
745 x_major_unit_set_cd3 => x_major_unit_set_cd3,
746 x_minor_unit_set_cd1 => x_minor_unit_set_cd1,
747 x_minor_unit_set_cd2 => x_minor_unit_set_cd2,
748 x_minor_unit_set_cd3 => x_minor_unit_set_cd3,
749 x_track_unit_set_cd1 => x_track_unit_set_cd1,
750 x_track_unit_set_cd2 => x_track_unit_set_cd2,
751 x_track_unit_set_cd3 => x_track_unit_set_cd3,
752 x_creation_date => x_last_update_date,
753 x_created_by => x_last_updated_by,
754 x_last_update_date => x_last_update_date,
755 x_last_updated_by => x_last_updated_by,
756 x_last_update_login => x_last_update_login
757 );
758
759 UPDATE igs_da_req_wif
760 SET
761 program_code = new_references.program_code,
762 catalog_cal_type = new_references.catalog_cal_type,
763 catalog_ci_seq_num = new_references.catalog_ci_seq_num,
764 major_unit_set_cd1 = new_references.major_unit_set_cd1,
765 major_unit_set_cd2 = new_references.major_unit_set_cd2,
766 major_unit_set_cd3 = new_references.major_unit_set_cd3,
767 minor_unit_set_cd1 = new_references.minor_unit_set_cd1,
768 minor_unit_set_cd2 = new_references.minor_unit_set_cd2,
769 minor_unit_set_cd3 = new_references.minor_unit_set_cd3,
770 track_unit_set_cd1 = new_references.track_unit_set_cd1,
771 track_unit_set_cd2 = new_references.track_unit_set_cd2,
772 track_unit_set_cd3 = new_references.track_unit_set_cd3,
773 last_update_date = x_last_update_date,
774 last_updated_by = x_last_updated_by,
775 last_update_login = x_last_update_login
776 WHERE rowid = x_rowid;
777
778 IF (SQL%NOTFOUND) THEN
779 RAISE NO_DATA_FOUND;
780 END IF;
781 -- Initialize API return status to success.
782 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
783 -- Standard call to get message count and if count is 1, get message
784 -- info.
785 FND_MSG_PUB.Count_And_Get(
786 p_encoded => FND_API.G_FALSE,
787 p_count => x_MSG_COUNT,
788 p_data => X_MSG_DATA);
789
790 EXCEPTION
791 WHEN FND_API.G_EXC_ERROR THEN
792 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
793 FND_MSG_PUB.Count_And_Get(
794 p_encoded => FND_API.G_FALSE,
795 p_count => x_MSG_COUNT,
796 p_data => X_MSG_DATA);
797 RETURN;
798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
799 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
800 FND_MSG_PUB.Count_And_Get(
801 p_encoded => FND_API.G_FALSE,
802 p_count => x_MSG_COUNT,
803 p_data => X_MSG_DATA);
804 RETURN;
805 WHEN OTHERS THEN
806 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
807 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
808 FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
809 FND_MSG_PUB.ADD;
810 FND_MSG_PUB.Count_And_Get(
811 p_encoded => FND_API.G_FALSE,
812 p_count => x_MSG_COUNT,
813 p_data => X_MSG_DATA);
814 RETURN;
815
816 END update_row;
817
818
819 PROCEDURE add_row (
820 x_rowid IN OUT NOCOPY VARCHAR2,
821 x_batch_id IN NUMBER,
822 x_wif_id IN NUMBER,
823 x_program_code IN VARCHAR2,
824 x_catalog_cal_type IN VARCHAR2,
825 x_catalog_ci_seq_num IN NUMBER,
826 x_major_unit_set_cd1 IN VARCHAR2,
827 x_major_unit_set_cd2 IN VARCHAR2,
828 x_major_unit_set_cd3 IN VARCHAR2,
829 x_minor_unit_set_cd1 IN VARCHAR2,
830 x_minor_unit_set_cd2 IN VARCHAR2,
831 x_minor_unit_set_cd3 IN VARCHAR2,
832 x_track_unit_set_cd1 IN VARCHAR2,
833 x_track_unit_set_cd2 IN VARCHAR2,
834 x_track_unit_set_cd3 IN VARCHAR2,
835 x_mode IN VARCHAR2
836 ) AS
837 /*
838 || Created By :
839 || Created On : 27-MAR-2003
840 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
841 || Known limitations, enhancements or remarks :
842 || Change History :
843 || Who When What
844 || (reverse chronological order - newest change first)
845 */
846 CURSOR c1 IS
847 SELECT rowid
848 FROM igs_da_req_wif
849 WHERE batch_id = x_batch_id
850 AND wif_id = x_wif_id;
851 L_RETURN_STATUS VARCHAR2(10);
852 L_MSG_DATA VARCHAR2(2000);
853 L_MSG_COUNT NUMBER(10);
854 BEGIN
855
856 OPEN c1;
857 FETCH c1 INTO x_rowid;
858 IF (c1%NOTFOUND) THEN
859 CLOSE c1;
860
861 insert_row (
862 x_rowid,
863 x_batch_id,
864 x_wif_id,
865 x_program_code,
866 x_catalog_cal_type,
867 x_catalog_ci_seq_num,
868 x_major_unit_set_cd1,
869 x_major_unit_set_cd2,
870 x_major_unit_set_cd3,
871 x_minor_unit_set_cd1,
872 x_minor_unit_set_cd2,
873 x_minor_unit_set_cd3,
874 x_track_unit_set_cd1,
875 x_track_unit_set_cd2,
876 x_track_unit_set_cd3,
877 x_mode ,
878 L_RETURN_STATUS,
879 L_MSG_DATA ,
880 L_MSG_COUNT
881 );
882 RETURN;
883 END IF;
884 CLOSE c1;
885
886 update_row (
887 x_rowid,
888 x_batch_id,
889 x_wif_id,
890 x_program_code,
891 x_catalog_cal_type,
892 x_catalog_ci_seq_num,
893 x_major_unit_set_cd1,
894 x_major_unit_set_cd2,
895 x_major_unit_set_cd3,
896 x_minor_unit_set_cd1,
897 x_minor_unit_set_cd2,
898 x_minor_unit_set_cd3,
899 x_track_unit_set_cd1,
900 x_track_unit_set_cd2,
901 x_track_unit_set_cd3,
902 x_mode ,
903 L_RETURN_STATUS,
904 L_MSG_DATA ,
905 L_MSG_COUNT
906 );
907
908 END add_row;
909
910
911 PROCEDURE delete_row (
912 x_rowid IN VARCHAR2 ,
913 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
914 X_MSG_DATA OUT NOCOPY VARCHAR2,
915 X_MSG_COUNT OUT NOCOPY NUMBER
916 ) AS
917 /*
918 || Created By :
919 || Created On : 27-MAR-2003
920 || Purpose : Handles the DELETE DML logic for the table.
921 || Known limitations, enhancements or remarks :
922 || Change History :
923 || Who When What
924 || (reverse chronological order - newest change first)
925 */
926 BEGIN
927 FND_MSG_PUB.initialize;
928 before_dml (
929 p_action => 'DELETE',
930 x_rowid => x_rowid
931 );
932
933 DELETE FROM igs_da_req_wif
934 WHERE rowid = x_rowid;
935
936 IF (SQL%NOTFOUND) THEN
937 RAISE NO_DATA_FOUND;
938 END IF;
939 -- Initialize API return status to success.
940 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
941 -- Standard call to get message count and if count is 1, get message
942 -- info.
943 FND_MSG_PUB.Count_And_Get(
944 p_encoded => FND_API.G_FALSE,
945 p_count => x_MSG_COUNT,
946 p_data => X_MSG_DATA);
947
948 EXCEPTION
949 WHEN FND_API.G_EXC_ERROR THEN
950 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
951 FND_MSG_PUB.Count_And_Get(
952 p_encoded => FND_API.G_FALSE,
953 p_count => x_MSG_COUNT,
954 p_data => X_MSG_DATA);
955 RETURN;
956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
957 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
958 FND_MSG_PUB.Count_And_Get(
959 p_encoded => FND_API.G_FALSE,
960 p_count => x_MSG_COUNT,
961 p_data => X_MSG_DATA);
962 RETURN;
963 WHEN OTHERS THEN
964 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
965 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
966 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
967 FND_MSG_PUB.ADD;
968 FND_MSG_PUB.Count_And_Get(
969 p_encoded => FND_API.G_FALSE,
970 p_count => x_MSG_COUNT,
971 p_data => X_MSG_DATA);
972 RETURN;
973
974
975 END delete_row;
976
977
978 END igs_da_req_wif_pkg;