[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_DOC_FEE_PMNT_PKG
Source
1 PACKAGE BODY Igs_As_Doc_Fee_Pmnt_Pkg AS
2 /* $Header: IGSDI72B.pls 115.3 2002/11/28 23:29:39 nsidana noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_as_doc_fee_pmnt%ROWTYPE;
5 new_references igs_as_doc_fee_pmnt%ROWTYPE;
6 FUNCTION check_unique_calseq (p_person_id IN NUMBER,
7 p_plan_id IN NUMBER,
8 p_cal_type IN VARCHAR2,
9 p_seq_num IN NUMBER)
10 RETURN boolean;
11
12 PROCEDURE set_column_values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2,
15 x_person_id IN NUMBER ,
16 x_fee_paid_date IN DATE ,
17 x_fee_amount IN NUMBER ,
18 x_fee_recorded_date IN DATE ,
19 x_fee_recorded_by IN NUMBER ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER ,
25 x_plan_id IN NUMBER ,
26 x_invoice_id IN NUMBER ,
27 x_plan_discon_from IN DATE ,
28 x_plan_discon_by IN NUMBER ,
29 x_num_of_copies IN NUMBER ,
30 x_prev_paid_plan IN VARCHAR2,
31 x_cal_type IN VARCHAR2,
32 x_ci_sequence_number IN NUMBER ,
33 x_program_on_file IN VARCHAR2
34 ) AS
35 /*
36 || Created By : [email protected]
37 || Created On : 07-FEB-2002
38 || Purpose : Initialises the Old and New references for the columns of the table.
39 || Known limitations, enhancements or remarks :
40 || Change History :
41 || Who When What
42 || (reverse chronological order - newest change first)
43 */
44 CURSOR cur_old_ref_values IS
45 SELECT *
46 FROM igs_as_doc_fee_pmnt
47 WHERE ROWID = x_rowid;
48 BEGIN
49 l_rowid := x_rowid;
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 -- Populate New Values.
63 new_references.person_id := x_person_id;
64 new_references.fee_paid_date := x_fee_paid_date;
65 new_references.fee_amount := x_fee_amount;
66 new_references.fee_recorded_date := x_fee_recorded_date;
67 new_references.fee_recorded_by := x_fee_recorded_by;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
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 new_references.plan_id := x_plan_id;
79 new_references.invoice_id := x_invoice_id;
80 new_references.plan_discon_from := x_plan_discon_from;
81 IF x_plan_discon_from IS NULL OR x_plan_discon_from = '' THEN
82 new_references.plan_discon_by := NULL;
83 ELSE
84 new_references.plan_discon_by := x_plan_discon_by;
85 END IF;
86 new_references.num_of_copies := x_num_of_copies;
87 new_references.prev_paid_plan := x_prev_paid_plan;
88 new_references.cal_type := x_cal_type;
89 new_references.ci_sequence_number := x_ci_sequence_number;
90 new_references.program_on_file := x_program_on_file;
91 END set_column_values;
92
93 PROCEDURE before_dml (
94 p_action IN VARCHAR2,
95 x_rowid IN VARCHAR2,
96 x_person_id IN NUMBER ,
97 x_fee_paid_date IN DATE ,
98 x_fee_amount IN NUMBER ,
99 x_fee_recorded_date IN DATE ,
100 x_fee_recorded_by IN NUMBER ,
101 x_creation_date IN DATE ,
102 x_created_by IN NUMBER ,
103 x_last_update_date IN DATE ,
104 x_last_updated_by IN NUMBER ,
105 x_last_update_login IN NUMBER ,
106 x_plan_id IN NUMBER ,
107 x_invoice_id IN NUMBER ,
108 x_plan_discon_from IN DATE ,
109 x_plan_discon_by IN NUMBER ,
110 x_num_of_copies IN NUMBER ,
111 x_prev_paid_plan IN VARCHAR2,
112 x_cal_type IN VARCHAR2,
113 x_ci_sequence_number IN NUMBER ,
114 x_program_on_file IN VARCHAR2
115 ) AS
116 /*
117 || Created By : [email protected]
118 || Created On : 07-FEB-2002
119 || Purpose : Initialises the columns, Checks Constraints, Calls the
120 || Trigger Handlers for the table, before any DML operation.
121 || Known limitations, enhancements or remarks :
122 || Change History :
123 || Who When What
124 || (reverse chronological order - newest change first)
125 */
126 BEGIN
127 set_column_values (
128 p_action,
129 x_rowid,
130 x_person_id,
131 x_fee_paid_date,
132 x_fee_amount,
133 x_fee_recorded_date,
134 x_fee_recorded_by,
135 x_creation_date,
136 x_created_by,
137 x_last_update_date,
138 x_last_updated_by,
139 x_last_update_login,
140 x_plan_id ,
141 x_invoice_id ,
142 x_plan_discon_from ,
143 x_plan_discon_by ,
144 x_num_of_copies ,
145 x_prev_paid_plan ,
146 x_cal_type ,
147 x_ci_sequence_number,
148 x_program_on_file
149 );
150 IF (p_action = 'INSERT') THEN
151 -- Check uniqueness if cal type and seq num are not null
152 IF new_references.cal_type IS NOT NULL AND
153 new_references.ci_sequence_number IS NOT NULL THEN
154 IF ( check_unique_calseq(
155 new_references.person_id,
156 new_references.plan_id,
157 new_references.cal_type,
158 new_references.ci_sequence_number
159 )
160 ) THEN
161 fnd_message.set_name('IGS','IGS_AS_PLAN_EXISTS_CAL');
162 FND_MSG_PUB.ADD;
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165 END IF;
166 END IF;
167 END before_dml;
168
169 PROCEDURE insert_row (
170 x_rowid IN OUT NOCOPY VARCHAR2,
171 x_person_id IN NUMBER,
172 x_fee_paid_date IN DATE,
173 x_fee_amount IN NUMBER,
174 x_fee_recorded_date IN DATE,
175 x_fee_recorded_by IN NUMBER,
176 x_mode IN VARCHAR2,
177 x_plan_id IN NUMBER ,
178 x_invoice_id IN NUMBER ,
179 x_plan_discon_from IN DATE ,
180 x_plan_discon_by IN NUMBER ,
181 x_num_of_copies IN NUMBER ,
182 x_prev_paid_plan IN VARCHAR2,
183 x_cal_type IN VARCHAR2,
184 x_ci_sequence_number IN NUMBER ,
185 x_program_on_file IN VARCHAR2,
186 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
187 X_MSG_DATA OUT NOCOPY VARCHAR2,
188 X_MSG_COUNT OUT NOCOPY NUMBER
189 ) AS
190 /*
191 || Created By : [email protected]
192 || Created On : 07-FEB-2002
193 || Purpose : Handles the INSERT DML logic for the table.
194 || Known limitations, enhancements or remarks :
195 || Change History :
196 || Who When What
197 || (reverse chronological order - newest change first)
198 */
199 CURSOR c IS
200 SELECT ROWID
201 FROM igs_as_doc_fee_pmnt
202 WHERE person_id = x_person_id;
203 x_last_update_date DATE;
204 x_last_updated_by NUMBER;
205 x_last_update_login NUMBER;
206 BEGIN
207 FND_MSG_PUB.initialize;
208 x_last_update_date := SYSDATE;
209 IF (x_mode = 'I') THEN
210 x_last_updated_by := 1;
211 x_last_update_login := 0;
212 ELSIF (x_mode = 'R') THEN
213 x_last_updated_by := fnd_global.user_id;
214 IF (x_last_updated_by IS NULL) THEN
215 x_last_updated_by := -1;
216 END IF;
217 x_last_update_login := fnd_global.login_id;
218 IF (x_last_update_login IS NULL) THEN
219 x_last_update_login := -1;
220 END IF;
221 ELSE
222 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
223 igs_ge_msg_stack.ADD;
224 app_exception.raise_exception;
225 END IF;
226 before_dml(
227 p_action => 'INSERT',
228 x_rowid => x_rowid,
229 x_person_id => x_person_id,
230 x_fee_paid_date => x_fee_paid_date,
231 x_fee_amount => x_fee_amount,
232 x_fee_recorded_date => x_fee_recorded_date,
233 x_fee_recorded_by => x_fee_recorded_by,
234 x_creation_date => x_last_update_date,
235 x_created_by => x_last_updated_by,
236 x_last_update_date => x_last_update_date,
237 x_last_updated_by => x_last_updated_by,
238 x_last_update_login => x_last_update_login ,
239 x_plan_id => x_plan_id ,
240 x_invoice_id => x_invoice_id ,
241 x_plan_discon_from => x_plan_discon_from ,
242 x_plan_discon_by => x_plan_discon_by ,
243 x_num_of_copies => x_num_of_copies ,
244 x_prev_paid_plan => x_prev_paid_plan ,
245 x_cal_type => x_cal_type ,
246 x_ci_sequence_number => x_ci_sequence_number,
247 x_program_on_file => x_program_on_file
248
249 );
250 INSERT INTO igs_as_doc_fee_pmnt (
251 person_id,
252 fee_paid_date,
253 fee_amount,
254 fee_recorded_date,
255 fee_recorded_by,
256 creation_date,
257 created_by,
258 last_update_date,
259 last_updated_by,
260 last_update_login,
261 plan_id ,
262 invoice_id ,
263 plan_discon_from ,
264 plan_discon_by ,
265 num_of_copies ,
266 prev_paid_plan ,
267 cal_type ,
268 ci_sequence_number,
269 program_on_file
270 ) VALUES (
271 new_references.person_id,
272 new_references.fee_paid_date,
273 new_references.fee_amount,
274 new_references.fee_recorded_date,
275 new_references.fee_recorded_by,
276 x_last_update_date,
277 x_last_updated_by,
278 x_last_update_date,
279 x_last_updated_by,
280 x_last_update_login,
281 new_references.plan_id ,
282 new_references.invoice_id ,
283 new_references.plan_discon_from ,
284 new_references.plan_discon_by ,
285 new_references.num_of_copies ,
286 new_references.prev_paid_plan ,
287 new_references.cal_type ,
288 new_references.ci_sequence_number,
289 new_references.program_on_file
290 );
291
292 OPEN c;
293 FETCH c INTO x_rowid;
294 IF (c%NOTFOUND) THEN
295 CLOSE c;
296 RAISE NO_DATA_FOUND;
297 END IF;
298 CLOSE c;
299
300 -- Initialize API return status to success.
301 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
302 -- Standard call to get message count and if count is 1, get message
303 -- info.
304 FND_MSG_PUB.Count_And_Get(
305 p_encoded => FND_API.G_FALSE,
306 p_count => x_MSG_COUNT,
307 p_data => X_MSG_DATA);
308
309 EXCEPTION
310 WHEN FND_API.G_EXC_ERROR THEN
311 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
312 FND_MSG_PUB.Count_And_Get(
313 p_encoded => FND_API.G_FALSE,
314 p_count => x_MSG_COUNT,
315 p_data => X_MSG_DATA);
316 RETURN;
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
319 FND_MSG_PUB.Count_And_Get(
320 p_encoded => FND_API.G_FALSE,
321 p_count => x_MSG_COUNT,
322 p_data => X_MSG_DATA);
323 RETURN;
324 WHEN OTHERS THEN
325 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
326 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
327 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
328 FND_MSG_PUB.ADD;
329 FND_MSG_PUB.Count_And_Get(
330 p_encoded => FND_API.G_FALSE,
331 p_count => x_MSG_COUNT,
332 p_data => X_MSG_DATA);
333 RETURN;
334
335 END insert_row;
336
337 PROCEDURE lock_row (
338 x_rowid IN VARCHAR2,
339 x_person_id IN NUMBER,
340 x_fee_paid_date IN DATE,
341 x_fee_amount IN NUMBER,
342 x_fee_recorded_date IN DATE,
343 x_fee_recorded_by IN NUMBER,
344 x_plan_id IN NUMBER ,
345 x_invoice_id IN NUMBER ,
346 x_plan_discon_from IN DATE ,
347 x_plan_discon_by IN NUMBER ,
348 x_num_of_copies IN NUMBER ,
349 x_prev_paid_plan IN VARCHAR2,
350 x_cal_type IN VARCHAR2,
351 x_ci_sequence_number IN NUMBER ,
352 x_program_on_file IN VARCHAR2,
353 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
354 X_MSG_DATA OUT NOCOPY VARCHAR2,
355 X_MSG_COUNT OUT NOCOPY NUMBER
356 ) AS
357 /*
358 || Created By : [email protected]
359 || Created On : 07-FEB-2002
360 || Purpose : Handles the LOCK mechanism for the table.
361 || Known limitations, enhancements or remarks :
362 || Change History :
363 || Who When What
364 || (reverse chronological order - newest change first)
365 */
366 CURSOR c1 IS
367 SELECT
368 person_id,
369 fee_paid_date,
370 fee_amount,
371 fee_recorded_date,
372 fee_recorded_by,
373 plan_id ,
374 invoice_id ,
375 plan_discon_from ,
376 plan_discon_by ,
377 num_of_copies ,
378 prev_paid_plan ,
379 cal_type ,
380 ci_sequence_number,
381 program_on_file
382 FROM igs_as_doc_fee_pmnt
383 WHERE ROWID = x_rowid
384 FOR UPDATE NOWAIT;
385 tlinfo c1%ROWTYPE;
386 BEGIN
387 FND_MSG_PUB.initialize;
388 OPEN c1;
389 FETCH c1 INTO tlinfo;
390 IF (c1%NOTFOUND) THEN
391 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
392 igs_ge_msg_stack.ADD;
393 CLOSE c1;
397 CLOSE c1;
394 app_exception.raise_exception;
395 RETURN;
396 END IF;
398 IF (
399 (tlinfo.person_id = x_person_id)
400 AND ((trunc(tlinfo.fee_paid_date) = trunc(x_fee_paid_date)) OR ((tlinfo.fee_paid_date IS NULL) AND (X_fee_paid_date IS NULL)))
401 AND ((tlinfo.fee_amount = x_fee_amount) OR ((tlinfo.fee_amount IS NULL) AND (X_fee_amount IS NULL)))
402 AND ((trunc(tlinfo.fee_recorded_date) = trunc(x_fee_recorded_date)) OR ((tlinfo.fee_recorded_date IS NULL) AND (X_fee_recorded_date IS NULL)))
403 AND ((tlinfo.fee_recorded_by = x_fee_recorded_by) OR ((tlinfo.fee_recorded_by IS NULL) AND (X_fee_recorded_by IS NULL)))
404 AND ((tlinfo.plan_id = x_plan_id) OR ((tlinfo.plan_id IS NULL) AND (X_plan_id IS NULL)))
405 AND ((tlinfo.invoice_id = x_invoice_id) OR ((tlinfo.invoice_id IS NULL) AND (X_invoice_id IS NULL)))
406 AND ((trunc(tlinfo.plan_discon_from) = trunc(x_plan_discon_from)) OR ((tlinfo.plan_discon_from IS NULL) AND (X_plan_discon_from IS NULL)))
407 AND ((tlinfo.plan_discon_by = x_plan_discon_by) OR ((tlinfo.plan_discon_by IS NULL) AND (X_plan_discon_by IS NULL)))
408 AND ((tlinfo.num_of_copies = x_num_of_copies) OR ((tlinfo.num_of_copies IS NULL) AND (X_num_of_copies IS NULL)))
409 AND ((tlinfo.prev_paid_plan = x_prev_paid_plan) OR ((tlinfo.prev_paid_plan IS NULL) AND (X_prev_paid_plan IS NULL)))
410 AND ((tlinfo.cal_type = x_cal_type) OR ((tlinfo.cal_type IS NULL) AND (X_cal_type IS NULL)))
411 AND ((tlinfo.ci_sequence_number = x_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
412 AND ((tlinfo.program_on_file = x_program_on_file) OR ((tlinfo.program_on_file IS NULL) AND (X_program_on_file IS NULL)))
413
414 ) THEN
415
416 NULL;
417 ELSE
418 fnd_message.set_name('FND', '*'||x_rowid||'*');
419 FND_MSG_PUB.ADD;
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422
423
424 -- Initialize API return status to success.
425 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
426 -- Standard call to get message count and if count is 1, get message
427 -- info.
428 FND_MSG_PUB.Count_And_Get(
429 p_encoded => FND_API.G_FALSE,
430 p_count => x_MSG_COUNT,
431 p_data => X_MSG_DATA);
432 RETURN;
433 EXCEPTION
434 WHEN FND_API.G_EXC_ERROR THEN
435 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
436 FND_MSG_PUB.Count_And_Get(
437 p_encoded => FND_API.G_FALSE,
438 p_count => x_MSG_COUNT,
439 p_data => X_MSG_DATA);
440 RETURN;
441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
442 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
443 FND_MSG_PUB.Count_And_Get(
444 p_encoded => FND_API.G_FALSE,
445 p_count => x_MSG_COUNT,
446 p_data => X_MSG_DATA);
447 RETURN;
448 WHEN OTHERS THEN
449 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
450 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
451 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
452 FND_MSG_PUB.ADD;
453 FND_MSG_PUB.Count_And_Get(
454 p_encoded => FND_API.G_FALSE,
455 p_count => x_MSG_COUNT,
456 p_data => X_MSG_DATA);
457 RETURN;
458
459 END lock_row;
460 PROCEDURE update_row (
461 x_rowid IN VARCHAR2,
462 x_person_id IN NUMBER,
463 x_fee_paid_date IN DATE,
464 x_fee_amount IN NUMBER,
465 x_fee_recorded_date IN DATE,
466 x_fee_recorded_by IN NUMBER,
467 x_mode IN VARCHAR2,
468 x_plan_id IN NUMBER ,
469 x_invoice_id IN NUMBER ,
470 x_plan_discon_from IN DATE ,
471 x_plan_discon_by IN NUMBER ,
472 x_num_of_copies IN NUMBER ,
473 x_prev_paid_plan IN VARCHAR2,
474 x_cal_type IN VARCHAR2,
475 x_ci_sequence_number IN NUMBER ,
476 x_program_on_file IN VARCHAR2,
477 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
478 X_MSG_DATA OUT NOCOPY VARCHAR2,
479 X_MSG_COUNT OUT NOCOPY NUMBER
480 ) AS
481 /*
482 || Created By : [email protected]
483 || Created On : 07-FEB-2002
484 || Purpose : Handles the UPDATE DML logic for the table.
485 || Known limitations, enhancements or remarks :
486 || Change History :
487 || Who When What
488 || (reverse chronological order - newest change first)
489 */
490 x_last_update_date DATE ;
491 x_last_updated_by NUMBER;
492 x_last_update_login NUMBER;
493 BEGIN
494 FND_MSG_PUB.initialize;
495 x_last_update_date := SYSDATE;
496 IF (X_MODE = 'I') THEN
497 x_last_updated_by := 1;
498 x_last_update_login := 0;
499 ELSIF (x_mode = 'R') THEN
500 x_last_updated_by := fnd_global.user_id;
501 IF x_last_updated_by IS NULL THEN
502 x_last_updated_by := -1;
503 END IF;
504 x_last_update_login := fnd_global.login_id;
505 IF (x_last_update_login IS NULL) THEN
506 x_last_update_login := -1;
507 END IF;
508 ELSE
509 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
513 before_dml(
510 igs_ge_msg_stack.ADD;
511 app_exception.raise_exception;
512 END IF;
514 p_action => 'UPDATE',
515 x_rowid => x_rowid,
516 x_person_id => x_person_id,
517 x_fee_paid_date => x_fee_paid_date,
518 x_fee_amount => x_fee_amount,
519 x_fee_recorded_date => x_fee_recorded_date,
520 x_fee_recorded_by => x_fee_recorded_by,
521 x_creation_date => x_last_update_date,
522 x_created_by => x_last_updated_by,
523 x_last_update_date => x_last_update_date,
524 x_last_updated_by => x_last_updated_by,
525 x_last_update_login => x_last_update_login,
526 x_plan_id => x_plan_id ,
527 x_invoice_id => x_invoice_id ,
528 x_plan_discon_from => x_plan_discon_from ,
529 x_plan_discon_by => x_plan_discon_by ,
530 x_num_of_copies => x_num_of_copies ,
531 x_prev_paid_plan => x_prev_paid_plan ,
532 x_cal_type => x_cal_type ,
533 x_ci_sequence_number => x_ci_sequence_number,
534 x_program_on_file => x_program_on_file
535 );
536 UPDATE igs_as_doc_fee_pmnt
537 SET
538 fee_paid_date = new_references.fee_paid_date,
539 fee_amount = new_references.fee_amount,
540 fee_recorded_date = new_references.fee_recorded_date,
541 fee_recorded_by = new_references.fee_recorded_by,
542 last_update_date = x_last_update_date,
543 last_updated_by = x_last_updated_by,
544 last_update_login = x_last_update_login ,
545 plan_id = new_references.plan_id,
546 invoice_id = new_references.invoice_id ,
547 plan_discon_from = new_references.plan_discon_from ,
548 plan_discon_by = new_references.plan_discon_by ,
549 num_of_copies = new_references.num_of_copies ,
550 prev_paid_plan = new_references.prev_paid_plan ,
551 cal_type = new_references.cal_type ,
552 ci_sequence_number = new_references.ci_sequence_number,
553 program_on_file = new_references.program_on_file
554 WHERE ROWID = x_rowid;
555 IF (SQL%NOTFOUND) THEN
556 RAISE NO_DATA_FOUND;
557 END IF;
558 --
559 -- call the api IGS_AS_SS_DOC_REQUEST.RE_CALC_DOC_FESS
560 -- to recalculate the fee if the plan is unsubscribed
561 --
562 DECLARE
563 l_orders_recalc VARCHAR2(2000);
564 BEGIN
565 IF old_references.plan_discon_by IS NULL AND new_references.plan_discon_by IS NOT NULL THEN
566 IGS_AS_SS_DOC_REQUEST.RE_CALC_DOC_FEES (
567 p_person_id => x_person_id,
568 p_plan_id => new_references.plan_id,
569 p_subs_unsubs => 'U',
570 p_admin_person_id => new_references.plan_discon_by,
571 p_orders_recalc => l_orders_recalc);
572 END IF;
573 EXCEPTION
574 WHEN OTHERS THEN
575 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_TRNS_RECLC_ERR');
576 FND_MSG_PUB.ADD;
577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578 END;
579
580
581 -- Initialize API return status to success.
582 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
583 -- Standard call to get message count and if count is 1, get message
584 -- info.
585 FND_MSG_PUB.Count_And_Get(
586 p_encoded => FND_API.G_FALSE,
587 p_count => x_MSG_COUNT,
588 p_data => X_MSG_DATA);
589
590 EXCEPTION
591 WHEN FND_API.G_EXC_ERROR THEN
592 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
593 FND_MSG_PUB.Count_And_Get(
594 p_encoded => FND_API.G_FALSE,
595 p_count => x_MSG_COUNT,
596 p_data => X_MSG_DATA);
597 RETURN;
598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
599 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
600 FND_MSG_PUB.Count_And_Get(
601 p_encoded => FND_API.G_FALSE,
602 p_count => x_MSG_COUNT,
603 p_data => X_MSG_DATA);
604 RETURN;
605 WHEN OTHERS THEN
606 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
607 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
608 FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
609 FND_MSG_PUB.ADD;
610 FND_MSG_PUB.Count_And_Get(
611 p_encoded => FND_API.G_FALSE,
612 p_count => x_MSG_COUNT,
613 p_data => X_MSG_DATA);
614 RETURN;
615
616
617 END update_row;
618
619 PROCEDURE add_row (
620 x_rowid IN OUT NOCOPY VARCHAR2,
621 x_person_id IN NUMBER,
622 x_fee_paid_date IN DATE,
623 x_fee_amount IN NUMBER,
624 x_fee_recorded_date IN DATE,
625 x_fee_recorded_by IN NUMBER,
626 x_mode IN VARCHAR2,
627 x_plan_id IN NUMBER ,
631 x_num_of_copies IN NUMBER ,
628 x_invoice_id IN NUMBER ,
629 x_plan_discon_from IN DATE ,
630 x_plan_discon_by IN NUMBER ,
632 x_prev_paid_plan IN VARCHAR2,
633 x_cal_type IN VARCHAR2,
634 x_ci_sequence_number IN NUMBER ,
635 x_program_on_file IN VARCHAR2
636 ) AS
637 /*
638 || Created By : [email protected]
639 || Created On : 07-FEB-2002
640 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
641 || Known limitations, enhancements or remarks :
642 || Change History :
643 || Who When What
644 || (reverse chronological order - newest change first)
645 */
646 CURSOR c1 IS
647 SELECT ROWID
648 FROM igs_as_doc_fee_pmnt
649 WHERE person_id = x_person_id;
650
651 L_RETURN_STATUS VARCHAR2(10);
652 L_MSG_DATA VARCHAR2(2000);
653 L_MSG_COUNT NUMBER(10);
654 BEGIN
655 OPEN c1;
656 FETCH c1 INTO x_rowid;
657 IF (c1%NOTFOUND) THEN
658 CLOSE c1;
659 insert_row (
660 x_rowid,
661 x_person_id,
662 x_fee_paid_date,
663 x_fee_amount,
664 x_fee_recorded_date,
665 x_fee_recorded_by,
666 x_mode ,
667 x_plan_id ,
668 x_invoice_id ,
669 x_plan_discon_from ,
670 x_plan_discon_by ,
671 x_num_of_copies ,
672 x_prev_paid_plan ,
673 x_cal_type ,
674 x_ci_sequence_number,
675 x_program_on_file,
676 L_RETURN_STATUS ,
677 L_MSG_DATA ,
678 L_MSG_COUNT
679 );
680 RETURN;
681 END IF;
682 CLOSE c1;
683 update_row (
684 x_rowid,
685 x_person_id,
686 x_fee_paid_date,
687 x_fee_amount,
688 x_fee_recorded_date,
689 x_fee_recorded_by,
690 x_mode ,
691 x_plan_id ,
692 x_invoice_id ,
693 x_plan_discon_from ,
694 x_plan_discon_by ,
695 x_num_of_copies ,
696 x_prev_paid_plan ,
697 x_cal_type ,
698 x_ci_sequence_number,
699 x_program_on_file ,
700 L_RETURN_STATUS ,
701 L_MSG_DATA ,
702 L_MSG_COUNT
703 );
704 END add_row;
705
706 PROCEDURE delete_row (
707 x_rowid IN VARCHAR2 ,
708 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
709 X_MSG_DATA OUT NOCOPY VARCHAR2,
710 X_MSG_COUNT OUT NOCOPY NUMBER
711 ) AS
712 /*
713 || Created By : [email protected]
714 || Created On : 07-FEB-2002
715 || Purpose : Handles the DELETE DML logic for the table.
716 || Known limitations, enhancements or remarks :
717 || Change History :
718 || Who When What
719 || (reverse chronological order - newest change first)
720 */
721 BEGIN
722 FND_MSG_PUB.initialize;
723 before_dml (
724 p_action => 'DELETE',
725 x_rowid => x_rowid
726 );
727 DELETE FROM igs_as_doc_fee_pmnt
728 WHERE ROWID = x_rowid;
729 IF (SQL%NOTFOUND) THEN
730 RAISE NO_DATA_FOUND;
731 END IF;
732 -- Initialize API return status to success.
733 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
734 -- Standard call to get message count and if count is 1, get message
735 -- info.
736 FND_MSG_PUB.Count_And_Get(
737 p_encoded => FND_API.G_FALSE,
738 p_count => x_MSG_COUNT,
739 p_data => X_MSG_DATA);
740
741 EXCEPTION
742 WHEN FND_API.G_EXC_ERROR THEN
743 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
744 FND_MSG_PUB.Count_And_Get(
745 p_encoded => FND_API.G_FALSE,
746 p_count => x_MSG_COUNT,
747 p_data => X_MSG_DATA);
748 RETURN;
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
751 FND_MSG_PUB.Count_And_Get(
752 p_encoded => FND_API.G_FALSE,
753 p_count => x_MSG_COUNT,
754 p_data => X_MSG_DATA);
755 RETURN;
756 WHEN OTHERS THEN
757 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
758 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
759 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
760 FND_MSG_PUB.ADD;
761 FND_MSG_PUB.Count_And_Get(
762 p_encoded => FND_API.G_FALSE,
763 p_count => x_MSG_COUNT,
764 p_data => X_MSG_DATA);
765 RETURN;
766
767
768 END delete_row;
769
770 FUNCTION check_unique_calseq (p_person_id IN NUMBER,
771 p_plan_id IN NUMBER,
772 p_cal_type IN VARCHAR2,
773 p_seq_num IN NUMBER)
774 RETURN boolean
775 AS
776 CURSOR c_unique (cp_person_id IN NUMBER,
777 cp_plan_id IN NUMBER,
778 cp_cal_type IN VARCHAR2,
779 cp_seq_num IN NUMBER) IS
780 SELECT 'Y'
781 FROM igs_as_doc_fee_pmnt
782 WHERE person_id = cp_person_id AND
783 plan_id = cp_plan_id AND
784 cal_type = cp_cal_type AND
785 ci_sequence_number = cp_seq_num ;
786 l_exists VARCHAR2(1) := 'N';
787
788 BEGIN
789 OPEN c_unique (p_person_id,
790 p_plan_id ,
791 p_cal_type ,
792 p_seq_num );
793 FETCH c_unique INTO l_exists;
794 CLOSE c_unique;
795 IF l_exists = 'Y' THEN
796 RETURN (TRUE);
797 ELSE
798 RETURN (FALSE);
799 END IF;
800 END check_unique_calseq;
801
802 END Igs_As_Doc_Fee_Pmnt_Pkg;