[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_SETTLEMENT_VAL_PVT
Source
1 PACKAGE BODY OZF_CLAIM_SETTLEMENT_VAL_PVT AS
2 /* $Header: ozfvcsvb.pls 120.10.12020000.3 2012/12/10 09:20:10 kpatro ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_CLAIM_SETTLEMENT_VAL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvcsvb.pls';
6
7 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
9
10 /*=======================================================================*
11 | PROCEDURE
12 | Default_Claim_Line
13 |
14 | NOTES
15 | This API default claim line recored against different settlement method.
16 |
17 | HISTORY
18 | 14-NOV-2002 mchang Create.
19 *=======================================================================*/
20 PROCEDURE Default_Claim_Line(
21 p_api_version IN NUMBER
22 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
23 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
24
25 ,x_return_status OUT NOCOPY VARCHAR2
26 ,x_msg_data OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28
29 ,p_x_claim_line_rec IN OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_rec_type
30 ,p_def_from_tbl_flag IN VARCHAR2
31 )
32 IS
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'Default_Claim_Line';
35 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
36 l_return_status VARCHAR2(1);
37
38 CURSOR csr_settlement_method(cv_claim_id IN NUMBER) IS
39 SELECT payment_method
40 FROM ozf_claims
41 WHERE claim_id = cv_claim_id;
42
43 l_settlement_method VARCHAR2(30);
44
45 BEGIN
46 IF OZF_DEBUG_HIGH_ON THEN
47 OZF_Utility_PVT.debug_message(l_full_name||' : start');
48 END IF;
49
50 -- Initialize API return status to sucess
51 x_return_status := FND_API.g_ret_sts_success;
52
53 ------------------------------------------------
54 IF p_x_claim_line_rec.source_object_class IS NULL THEN
55 p_x_claim_line_rec.source_object_id := NULL;
56 END IF;
57
58 IF p_x_claim_line_rec.source_object_id IS NULL THEN
59 p_x_claim_line_rec.source_object_line_id := NULL;
60 END IF;
61
62 --
63 OPEN csr_settlement_method(p_x_claim_line_rec.claim_id);
64 FETCH csr_settlement_method INTO l_settlement_method;
65 CLOSE csr_settlement_method;
66
67 IF p_def_from_tbl_flag = FND_API.g_false THEN
68 IF l_settlement_method = 'RMA' THEN
69 OZF_OM_VALIDATION_PVT.Default_Claim_Line(
70 p_api_version => l_api_version
71 ,p_init_msg_list => p_init_msg_list
72 ,p_validation_level => p_validation_level
73 ,x_return_status => l_return_status
74 ,x_msg_data => x_msg_data
75 ,x_msg_count => x_msg_count
76 ,p_x_claim_line_rec => p_x_claim_line_rec
77 );
78 IF l_return_status = FND_API.g_ret_sts_error THEN
79 RAISE FND_API.g_exc_error;
80 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
81 RAISE FND_API.g_exc_unexpected_error;
82 END IF;
83 END IF;
84 END IF;
85
86 IF OZF_DEBUG_HIGH_ON THEN
87 OZF_Utility_PVT.debug_message(l_full_name||' : end');
88 END IF;
89 EXCEPTION
90 WHEN FND_API.G_EXC_ERROR THEN
91 x_return_status := FND_API.G_RET_STS_ERROR;
92 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94 WHEN OTHERS THEN
95 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
96 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
97 END IF;
98 x_return_status := FND_API.g_ret_sts_unexp_error;
99
100 END Default_Claim_Line;
101
102
103 /*=======================================================================*
104 | PROCEDURE
105 | Default_Claim_Line_Tbl
106 |
107 | NOTES
108 | This API default claim line table for RMA settlement method.
109 |
110 | HISTORY
111 | 14-NOV-2002 mchang Create.
112 *=======================================================================*/
113 PROCEDURE Default_Claim_Line_Tbl(
114 p_api_version IN NUMBER
115 ,p_init_msg_list IN VARCHAR2
116 ,p_validation_level IN NUMBER
117
118 ,x_return_status OUT NOCOPY VARCHAR2
119 ,x_msg_data OUT NOCOPY VARCHAR2
120 ,x_msg_count OUT NOCOPY NUMBER
121
122 ,p_x_claim_line_tbl IN OUT NOCOPY OZF_CLAIM_LINE_PVT.claim_line_tbl_type
123 )
124 IS
125 l_api_version CONSTANT NUMBER := 1.0;
126 l_api_name CONSTANT VARCHAR2(30) := 'Default_Claim_Line_Tbl';
127 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
128 l_return_status VARCHAR2(1);
129
130 CURSOR csr_settlement_method(cv_claim_id IN NUMBER) IS
131 SELECT payment_method
132 FROM ozf_claims
133 WHERE claim_id = cv_claim_id;
134
135 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
136 l_settlement_method VARCHAR2(30);
137 i NUMBER;
138
139 BEGIN
140 IF OZF_DEBUG_HIGH_ON THEN
141 OZF_Utility_PVT.debug_message(l_full_name||' : start');
142 END IF;
143
144 -- Initialize API return status to sucess
145 x_return_status := FND_API.g_ret_sts_success;
146
147 i := p_x_claim_line_tbl.FIRST;
148 IF i IS NOT NULL THEN
149 LOOP
150 l_claim_line_rec := p_x_claim_line_tbl(i);
151 Default_Claim_Line(
152 p_api_version => l_api_version
153 ,p_init_msg_list => p_init_msg_list
154 ,p_validation_level => p_validation_level
155 ,x_return_status => l_return_status
156 ,x_msg_data => x_msg_data
157 ,x_msg_count => x_msg_count
158 ,p_x_claim_line_rec => l_claim_line_rec
159 ,p_def_from_tbl_flag => FND_API.g_true
160 );
161 p_x_claim_line_tbl(i) := l_claim_line_rec;
162 IF l_return_status = FND_API.g_ret_sts_error THEN
163 RAISE FND_API.g_exc_error;
164 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
165 RAISE FND_API.g_exc_unexpected_error;
166 END IF;
167 EXIT WHEN i = p_x_claim_line_tbl.LAST;
168 i := p_x_claim_line_tbl.NEXT(i);
169 END LOOP;
170
171 OPEN csr_settlement_method(p_x_claim_line_tbl(1).claim_id);
172 FETCH csr_settlement_method INTO l_settlement_method;
173 CLOSE csr_settlement_method;
174
175 IF l_settlement_method = 'RMA' THEN
176 OZF_OM_VALIDATION_PVT.Default_Claim_Line_Tbl(
177 p_api_version => l_api_version
178 ,p_init_msg_list => p_init_msg_list
179 ,p_validation_level => p_validation_level
180 ,x_return_status => l_return_status
181 ,x_msg_data => x_msg_data
182 ,x_msg_count => x_msg_count
183 ,p_x_claim_line_tbl => p_x_claim_line_tbl
184 );
185 IF l_return_status = FND_API.g_ret_sts_error THEN
186 RAISE FND_API.g_exc_error;
187 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
188 RAISE FND_API.g_exc_unexpected_error;
189 END IF;
190 END IF;
191 END IF;
192
193 IF OZF_DEBUG_HIGH_ON THEN
194 OZF_Utility_PVT.debug_message(l_full_name||' : end');
195 END IF;
196 EXCEPTION
197 WHEN FND_API.G_EXC_ERROR THEN
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 WHEN OTHERS THEN
202 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
203 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
204 END IF;
205 x_return_status := FND_API.g_ret_sts_unexp_error;
206
207 END Default_Claim_Line_Tbl;
208
209
210 /*=======================================================================*
211 | PROCEDURE
212 | Validate_Claim_Line
213 |
214 | NOTES
215 | This API validate claim line recored against RMA settlement.
216 |
217 | HISTORY
218 | 30-NOV-2002 mchang Create.
219 *=======================================================================*/
220 PROCEDURE Validate_Claim_Line(
221 p_api_version IN NUMBER
222 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
223 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
224
225 ,x_return_status OUT NOCOPY VARCHAR2
226 ,x_msg_data OUT NOCOPY VARCHAR2
227 ,x_msg_count OUT NOCOPY NUMBER
228
229 ,p_claim_line_rec IN OZF_CLAIM_LINE_PVT.claim_line_rec_type
230 ,p_val_from_tbl_flag IN VARCHAR2
231 )
232 IS
233 l_api_version CONSTANT NUMBER := 1.0;
234 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line';
235 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
236 l_return_status VARCHAR2(1);
237
238 CURSOR csr_settlement_method(cv_claim_id IN NUMBER) IS
239 SELECT payment_method
240 FROM ozf_claims
241 WHERE claim_id = cv_claim_id;
242
243 l_error BOOLEAN := FALSE;
244 l_settlement_method VARCHAR2(30);
245
246 BEGIN
247 IF OZF_DEBUG_HIGH_ON THEN
248 OZF_Utility_PVT.debug_message(l_full_name||' : start');
249 END IF;
250
251 -- Initialize API return status to sucess
252 x_return_status := FND_API.g_ret_sts_success;
253
254 OPEN csr_settlement_method(p_claim_line_rec.claim_id);
255 FETCH csr_settlement_method INTO l_settlement_method;
256 CLOSE csr_settlement_method;
257
258 IF p_val_from_tbl_flag = FND_API.g_false THEN
259 IF l_settlement_method = 'RMA' THEN
260 OZF_OM_VALIDATION_PVT.Validate_Claim_Line(
261 p_api_version => l_api_version
262 ,p_init_msg_list => p_init_msg_list
263 ,p_validation_level => p_validation_level
264 ,x_return_status => l_return_status
265 ,x_msg_data => x_msg_data
266 ,x_msg_count => x_msg_count
267 ,p_claim_line_rec => p_claim_line_rec
268 );
269 IF l_return_status = FND_API.g_ret_sts_error THEN
270 RAISE FND_API.g_exc_error;
271 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
272 RAISE FND_API.g_exc_unexpected_error;
273 END IF;
274 END IF;
275 END IF;
276
277 IF OZF_DEBUG_HIGH_ON THEN
278 OZF_Utility_PVT.debug_message(l_full_name||' : end');
279 END IF;
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 WHEN OTHERS THEN
286 IF OZF_DEBUG_LOW_ON THEN
287 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
288 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
289 FND_MSG_PUB.Add;
290 END IF;
291 x_return_status := FND_API.g_ret_sts_unexp_error;
292
293 END Validate_Claim_Line;
294
295
296 /*=======================================================================*
297 | PROCEDURE
298 | Validate_Claim_Line_Tbl
299 |
300 | NOTES
301 | This API validate claim line recored against RMA settlement.
302 |
303 | HISTORY
304 | 30-NOV-2002 mchang Create.
305 *=======================================================================*/
306 PROCEDURE Validate_Claim_Line_Tbl(
307 p_api_version IN NUMBER
308 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
309 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
310
311 ,x_return_status OUT NOCOPY VARCHAR2
312 ,x_msg_data OUT NOCOPY VARCHAR2
313 ,x_msg_count OUT NOCOPY NUMBER
314
315 ,p_claim_line_tbl IN OZF_CLAIM_LINE_PVT.claim_line_tbl_type
316 )
317 IS
318 l_api_version CONSTANT NUMBER := 1.0;
319 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line_Tbl';
320 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
321 l_return_status VARCHAR2(1);
322
323 CURSOR csr_settlement_method(cv_claim_id IN NUMBER) IS
324 SELECT payment_method
325 FROM ozf_claims
326 WHERE claim_id = cv_claim_id;
327
328 i NUMBER;
329 l_error BOOLEAN := FALSE;
330 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
331 l_settlement_method VARCHAR2(15);
332
333
334 BEGIN
335 IF OZF_DEBUG_HIGH_ON THEN
336 OZF_Utility_PVT.debug_message(l_full_name||' : start');
337 END IF;
338
339 -- Initialize API return status to sucess
340 x_return_status := FND_API.g_ret_sts_success;
341
342 i := p_claim_line_tbl.FIRST ;
343 IF i IS NOT NULL THEN
344 LOOP
345 l_claim_line_rec := p_claim_line_tbl(i);
346 Validate_Claim_Line(
347 p_api_version => l_api_version
348 ,p_init_msg_list => p_init_msg_list
349 ,p_validation_level => p_validation_level
350 ,x_return_status => l_return_status
351 ,x_msg_data => x_msg_data
352 ,x_msg_count => x_msg_count
353 ,p_claim_line_rec => l_claim_line_rec
354 ,p_val_from_tbl_flag => FND_API.g_true
355 );
356 IF l_return_status = FND_API.g_ret_sts_error THEN
357 RAISE FND_API.g_exc_error;
358 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
359 RAISE FND_API.g_exc_unexpected_error;
360 END IF;
361 EXIT WHEN i = p_claim_line_tbl.LAST;
362 i := p_claim_line_tbl.NEXT(i);
363 END LOOP;
364
365 OPEN csr_settlement_method(p_claim_line_tbl(1).claim_id);
366 FETCH csr_settlement_method INTO l_settlement_method;
367 CLOSE csr_settlement_method;
368
369 IF l_settlement_method = 'RMA' THEN
370 OZF_OM_VALIDATION_PVT.Validate_Claim_Line_Tbl(
371 p_api_version => l_api_version
372 ,p_init_msg_list => p_init_msg_list
373 ,p_validation_level => p_validation_level
374 ,x_return_status => l_return_status
375 ,x_msg_data => x_msg_data
376 ,x_msg_count => x_msg_count
377 ,p_claim_line_tbl => p_claim_line_tbl
378 );
379 IF l_return_status = FND_API.g_ret_sts_error THEN
380 RAISE FND_API.g_exc_error;
381 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
382 RAISE FND_API.g_exc_unexpected_error;
383 END IF;
384 END IF;
385 END IF;
386
387 IF l_error THEN
388 RAISE FND_API.G_EXC_ERROR;
389 END IF;
390
391
392 IF OZF_DEBUG_HIGH_ON THEN
393 OZF_Utility_PVT.debug_message(l_full_name||' : end');
394 END IF;
395 EXCEPTION
396 WHEN FND_API.G_EXC_ERROR THEN
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 WHEN OTHERS THEN
401 IF OZF_DEBUG_LOW_ON THEN
402 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
403 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
404 FND_MSG_PUB.Add;
405 END IF;
406 x_return_status := FND_API.g_ret_sts_unexp_error;
407
408 END Validate_Claim_Line_Tbl;
409
410
411 FUNCTION gl_date_in_open(
412 p_application_id IN NUMBER
413 ,p_claim_id IN NUMBER
414 ,p_gl_date IN DATE DEFAULT NULL
415 )
416 RETURN BOOLEAN
417 IS
418
419 -- Fix for Bug 7717206
420 -- Fix for Bug#14550700
421 CURSOR csr_gl_date_val_no_date( cv_app_id IN NUMBER
422 , cv_claim_id IN NUMBER
423 ) IS
424 SELECT DECODE(MAX(gl.period_name), '', 0, 1)
425 FROM gl_period_statuses gl
426 , ozf_claims c
427 WHERE gl.application_id = cv_app_id
428 AND c.claim_id= cv_claim_id
429 AND gl.set_of_books_id = c.set_of_books_id
430 AND gl.adjustment_period_flag = 'N'
431 AND trunc(c.gl_date) BETWEEN gl.start_date AND gl.end_date
432 AND gl.closing_status IN ('O', 'F');
433
434
435 CURSOR csr_gl_date_validate( cv_app_id IN NUMBER
436 , cv_claim_id IN NUMBER
437 , cv_gl_date IN DATE
438 ) IS
439 SELECT DECODE(MAX(gl.period_name), '', 0, 1)
440 FROM gl_period_statuses gl
441 , ozf_claims c
442 WHERE gl.application_id = cv_app_id
443 AND c.claim_id= cv_claim_id
444 AND gl.set_of_books_id = c.set_of_books_id
445 AND gl.adjustment_period_flag = 'N'
446 AND trunc(cv_gl_date) BETWEEN gl.start_date AND gl.end_date
447 AND gl.closing_status IN ('O', 'F');
448
449 l_gl_date_count NUMBER := 1;
450
451 BEGIN
452
453 -- Fix for Bug#14550700
454 IF (p_gl_date IS NOT NULL) THEN
455
456 OPEN csr_gl_date_validate(p_application_id, p_claim_id,p_gl_date);
457 FETCH csr_gl_date_validate INTO l_gl_date_count;
458 CLOSE csr_gl_date_validate;
459
460 ELSE
461
462 OPEN csr_gl_date_val_no_date(p_application_id, p_claim_id);
463 FETCH csr_gl_date_val_no_date INTO l_gl_date_count;
464 CLOSE csr_gl_date_val_no_date;
465
466 END IF;
467
468 IF l_gl_date_count = 0 THEN
469 RETURN FALSE;
470 ELSE
471 RETURN TRUE;
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 IF OZF_DEBUG_LOW_ON THEN
477 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
478 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
479 FND_MSG_PUB.Add;
480 END IF;
481 RETURN FALSE;
482 END gl_date_in_open;
483
484
485 /*=======================================================================*
486 | Procedure
487 | Complete_Claim_Validation
488 |
489 | Return
490 |
491 | NOTES
492 |
493 | HISTORY
494 | 24-OCT-2002 mchang Create.
495 *=======================================================================*/
496 PROCEDURE Complete_Claim_Validation(
497 p_api_version IN NUMBER
498 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
499 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
500
501 ,x_return_status OUT NOCOPY VARCHAR2
502 ,x_msg_data OUT NOCOPY VARCHAR2
503 ,x_msg_count OUT NOCOPY NUMBER
504
505 ,p_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
506 ,x_claim_rec OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
507 )
508 IS
509 l_api_version CONSTANT NUMBER := 1.0;
510 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Claim_Validation';
511 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
512 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
513
514 --Added claim_currency_amount check for bug 6828924.
515 CURSOR csr_claim_line(cv_claim_id IN NUMBER) IS
516 SELECT claim_line_id
517 , currency_code
518 , amount
519 , claim_currency_amount
520 , acctd_amount
521 , earnings_associated_flag
522 , performance_complete_flag
523 , source_object_class
524 , source_object_id
525 , source_object_line_id
526 , item_id
527 FROM ozf_claim_lines
528 WHERE claim_id = cv_claim_id
529 AND claim_currency_amount <> 0;
530
531 CURSOR csr_asso_offr_performance(cv_claim_line_id IN NUMBER) IS
532 SELECT op.offer_performance_id
533 FROM ozf_claim_lines_util lu
534 , ozf_funds_utilized fu
535 , ozf_offer_performances op
536 WHERE lu.utilization_id = fu.utilization_id
537 AND fu.component_type = 'OFFR'
538 AND fu.component_id = op.list_header_id
539 AND (op.requirement_type IS NULL OR op.requirement_type NOT IN ('AMOUNT', 'VOLUME'))
540 AND op.required_flag = 'Y'
541 AND lu.claim_line_id = cv_claim_line_id;
542
543 CURSOR csr_line_offr_performance(cv_claim_line_id IN NUMBER) IS
544 SELECT op.offer_performance_id
545 FROM ozf_claim_lines ln
546 , ozf_offer_performances op
547 WHERE ln.activity_type = 'OFFR'
548 AND ln.activity_id = op.list_header_id
549 AND (op.requirement_type IS NULL OR op.requirement_type NOT IN ('AMOUNT', 'VOLUME'))
550 AND op.required_flag = 'Y'
551 AND ln.claim_line_id = cv_claim_line_id;
552
553 -- Modified for FXGL Enhancement
554 -- Need to compare line amount with associated util_amounts
555 CURSOR csr_sum_util_amt(cv_claim_line_id IN NUMBER) IS
556 SELECT NVL(SUM(amount), 0)
557 FROM ozf_claim_lines_util
558 WHERE claim_line_id = cv_claim_line_id;
559
560
561 --//Bugfix : 13069412
562 /*
563 CURSOR csr_sum_line_amt(cv_claim_id IN NUMBER) IS
564 SELECT NVL(SUM(claim_currency_amount), 0)
565 FROM ozf_claim_lines
566 WHERE claim_id = cv_claim_id;
567 */
568 CURSOR csr_sum_line_amt(cv_claim_id IN NUMBER) IS
569 SELECT NVL(SUM(claim_currency_amount), 0),NVL(SUM(claim_curr_tax_amount), 0)
570 FROM ozf_claim_lines
571 WHERE claim_id = cv_claim_id;
572
573 --Added claim_currency_amount check for bug 6828924.
574 CURSOR csr_line_prom_distinct_chk(cv_claim_id IN NUMBER) IS
575 SELECT distinct earnings_associated_flag
576 FROM ozf_claim_lines
577 WHERE claim_id = cv_claim_id
578 AND claim_currency_amount <> 0;
579
580 CURSOR csr_gl_date_validate(cv_app_id IN NUMBER, cv_set_of_books_id IN NUMBER) IS
581 SELECT DECODE(MAX(period_name), '', 0, 1)
582 FROM gl_period_statuses
583 WHERE application_id = cv_app_id
584 AND set_of_books_id = cv_set_of_books_id
585 AND adjustment_period_flag = 'N'
586 AND SYSDATE BETWEEN start_date AND end_date
587 AND closing_status IN ('O', 'F');
588
589 CURSOR csr_sysparam_defaults IS
590 SELECT post_to_gl
591 , payables_source
592 , batch_source_id
593 , gl_id_ded_clearing
594 , gl_rec_clearing_account
595 , gl_date_type
596 FROM ozf_sys_parameters;
597
598 CURSOR csr_inv_line(cv_invoice_line_id IN NUMBER) IS
599 SELECT inventory_item_id
600 , uom_code
601 , quantity_invoiced
602 FROM ra_customer_trx_lines
603 WHERE customer_trx_line_id = cv_invoice_line_id;
604
605 CURSOR csr_inv_line_exist(cv_invoice_id IN NUMBER, cv_invoice_line_id IN NUMBER) IS
606 SELECT customer_trx_line_id
607 FROM ra_customer_trx_lines
608 WHERE customer_trx_id = cv_invoice_id
609 AND customer_trx_line_id = cv_invoice_line_id;
610
611 l_claim_line_id NUMBER;
612 l_line_src_obj_class VARCHAR2(15);
613 l_line_src_obj_id NUMBER;
614 l_line_src_obj_line_id NUMBER;
615 l_line_item_id NUMBER;
616 l_line_currency VARCHAR2(15);
617 l_line_amount NUMBER;
618 l_line_claim_curr_amt NUMBER;
619 l_line_acctd_amount NUMBER;
620 l_line_earnings_flag VARCHAR2(1);
621 l_line_perf_comp_flag VARCHAR2(1);
622 l_line_sum_curr_amt NUMBER;
623 l_sum_line_util_amt NUMBER;
624 l_asso_earnings_exist BOOLEAN := FALSE;
625 l_promo_distinct_err BOOLEAN := FALSE;
626 l_line_amount_err BOOLEAN := FALSE;
627 l_line_util_err BOOLEAN := FALSE;
628 l_offr_perf_flag_err BOOLEAN := FALSE;
629 l_error BOOLEAN := FALSE;
630 l_asso_offr_perf_id NUMBER;
631 l_gl_count NUMBER;
632 l_gl_acc_checking VARCHAR2(1);
633 l_batch_source_id NUMBER;
634 l_payables_source VARCHAR2(30);
635 l_vendor_in_sys NUMBER;
636 l_rec_clr_in_sys NUMBER;
637 l_inv_item_id RA_CUSTOMER_TRX_LINES.inventory_item_id%TYPE;
638 l_inv_uom_code RA_CUSTOMER_TRX_LINES.uom_code%TYPE;
639 l_inv_quantity RA_CUSTOMER_TRX_LINES.quantity_invoiced%TYPE;
640 l_inv_line_id RA_CUSTOMER_TRX_LINES.customer_trx_line_id%TYPE;
641 l_gl_date_type VARCHAR2(30);
642 l_line_mix_amt_sign BOOLEAN := FALSE;
643
644 l_cline_sum_curr_amt NUMBER := 0;
645 l_cline_sum_curr_tax_amt NUMBER := 0;
646 l_sum_total_amt NUMBER := 0;
647
648 -- R12: Earnings are supported for custom settlement
649 CURSOR csr_payment_type(cv_claim_id IN NUMBER) IS
650 SELECT ocs.seeded_flag
651 FROM ozf_claim_sttlmnt_methods_all ocs,
652 ozf_claims_all oc
653 WHERE claim_id = cv_claim_id
654 AND ocs.claim_class = oc.claim_class
655 AND NVL(ocs.source_object_class,'NULL') = NVL(oc.source_object_class,'NULL')
656 AND ocs.settlement_method = oc.payment_method ;
657 l_seeded_flag VARCHAR2(1);
658
659 -- Fix for Bug#14550700
660 l_sys_gl_date_type VARCHAR2(30);
661 l_gl_date DATE;
662
663 CURSOR csr_get_gl_date_type(cv_set_of_books_id IN NUMBER) IS
664 SELECT gl_date_type
665 FROM ozf_sys_parameters
666 WHERE set_of_books_id = cv_set_of_books_id;
667
668
669 --Added cursor for Bug 14757221 to check if both vendor and vendor site is active
670 CURSOR csr_active_vendor(cv_vendor_id IN NUMBER, cv_vendor_site_id IN NUMBER) IS
671 SELECT 1 FROM po_vendors pv, po_vendor_sites pvs
672 WHERE pv.vendor_id = pvs.vendor_id
673 AND (sysdate between nvl(pv.start_date_active, sysdate-1) and nvl(pv.end_date_active, sysdate+1))
674 AND (pvs.inactive_date is null or pvs.inactive_date > trunc(sysdate))
675 AND pv.vendor_id = cv_vendor_id
676 AND pvs.vendor_site_id = cv_vendor_site_id;
677
678 l_active_vendor_num NUMBER ;
679
680 BEGIN
681 IF OZF_DEBUG_HIGH_ON THEN
682 OZF_Utility_PVT.debug_message(l_full_name||' : start');
683 END IF;
684
685 -- Initialize API return status to sucess
686 x_return_status := FND_API.g_ret_sts_success;
687
688 x_claim_rec := p_claim_rec;
689
690 ---------------------------
691 -- System Parameters
692 ---------------------------
693 OPEN csr_sysparam_defaults;
694 FETCH csr_sysparam_defaults INTO l_gl_acc_checking
695 , l_payables_source
696 , l_batch_source_id
697 , l_vendor_in_sys
698 , l_rec_clr_in_sys
699 , l_gl_date_type;
700 CLOSE csr_sysparam_defaults;
701
702 ---------------------------
703 -- Settlement Method: Required
704 ---------------------------
705
706 IF OZF_DEBUG_HIGH_ON THEN
707 OZF_Utility_PVT.debug_message('p_claim_rec.payment_method:' || p_claim_rec.payment_method);
708 END IF;
709
710 IF (p_claim_rec.payment_method IS NULL OR p_claim_rec.payment_method = FND_API.G_MISS_CHAR
711 OR p_claim_rec.payment_method = '' ) THEN
712 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
713 FND_MESSAGE.set_name('OZF', 'OZF_SETL_PAYMETHOD_REQ');
714 FND_MSG_PUB.add;
715 END IF;
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718
719 -- Query Claim Line records
720 OPEN csr_claim_line(p_claim_rec.claim_id);
721 LOOP
722 FETCH csr_claim_line INTO l_claim_line_id
723 , l_line_currency
724 , l_line_amount
725 , l_line_claim_curr_amt
726 , l_line_acctd_amount
727 , l_line_earnings_flag
728 , l_line_perf_comp_flag
729 , l_line_src_obj_class
730 , l_line_src_obj_id
731 , l_line_src_obj_line_id
732 , l_line_item_id;
733 EXIT WHEN csr_claim_line%NOTFOUND;
734
735 IF l_line_earnings_flag = FND_API.g_true THEN
736 l_asso_earnings_exist :=TRUE;
737
738 IF NOT l_offr_perf_flag_err THEN
739 -- offer performance checking (1)
740 OPEN csr_asso_offr_performance(l_claim_line_id);
741 LOOP
742 FETCH csr_asso_offr_performance INTO l_asso_offr_perf_id;
743 EXIT WHEN csr_asso_offr_performance%NOTFOUND;
744 IF l_line_perf_comp_flag <> FND_API.g_true THEN
745 l_offr_perf_flag_err := TRUE;
746 EXIT;
747 END IF;
748 END LOOP;
749 CLOSE csr_asso_offr_performance;
750 END IF;
751 END IF;
752
753 -- offer performance checking (2)
754 IF NOT l_offr_perf_flag_err THEN
755 OPEN csr_line_offr_performance(l_claim_line_id);
756 LOOP
757 FETCH csr_line_offr_performance INTO l_asso_offr_perf_id;
758 EXIT WHEN csr_line_offr_performance%NOTFOUND;
759 IF l_line_perf_comp_flag <> FND_API.g_true THEN
760 l_offr_perf_flag_err := TRUE;
761 EXIT;
762 END IF;
763 END LOOP;
764 CLOSE csr_line_offr_performance;
765 END IF;
766
767 ------------------------------------
768 -- If earnings are associated with Claim,
769 -- claim line acctd Amount has to equal to associated earnings
770 ------------------------------------
771 IF l_asso_earnings_exist THEN
772 OPEN csr_sum_util_amt(l_claim_line_id);
773 FETCH csr_sum_util_amt INTO l_sum_line_util_amt;
774 CLOSE csr_sum_util_amt;
775
776 -- Modified for FXGL Enhancement
777 -- Need to compare line amount with associated util amounts
778 IF l_line_amount <> l_sum_line_util_amt THEN
779 l_line_util_err := TRUE;
780 EXIT;
781 END IF;
782 END IF;
783
784 IF l_line_currency = p_claim_rec.currency_code THEN
785 IF l_line_amount <> l_line_claim_curr_amt THEN
786 l_line_amount_err := TRUE;
787 END IF;
788 END IF;
789
790 -- Invoice/Order Line and Product should be the same.
791 IF l_line_src_obj_class = 'INVOICE' AND
792 l_line_src_obj_id IS NOT NULL THEN
793 IF l_line_src_obj_line_id IS NOT NULL THEN
794 OPEN csr_inv_line(l_line_src_obj_line_id);
795 FETCH csr_inv_line INTO l_inv_item_id
796 , l_inv_uom_code
797 , l_inv_quantity;
798 CLOSE csr_inv_line;
799
800 OPEN csr_inv_line_exist(l_line_src_obj_id, l_line_src_obj_line_id);
801 FETCH csr_inv_line_exist INTO l_inv_line_id;
802 CLOSE csr_inv_line_exist;
803
804 IF l_inv_line_id IS NULL THEN
805 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
806 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_INV_LINE_ERR');
807 FND_MSG_PUB.add;
808 END IF;
809 l_error := TRUE;
810 END IF;
811
812 IF l_line_item_id IS NOT NULL AND
813 l_line_item_id <> l_inv_item_id THEN
814 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
815 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VAL_LINE_ITEM_DIFF');
816 FND_MSG_PUB.add;
817 END IF;
818 l_error := TRUE;
819 END IF;
820 END IF;
821 END IF;
822
823 ------------------------------------
824 -- Only promotional claims are allowed to
825 -- have both positive and negative lines.
826 ------------------------------------
827 IF NOT l_asso_earnings_exist AND
828 SIGN(p_claim_rec.amount_remaining) <> SIGN(l_line_claim_curr_amt) THEN
829 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
830 FND_MESSAGE.set_name('OZF', 'OZF_SETL_LINE_AMT_SIGN_ERR');
831 FND_MSG_PUB.add;
832 END IF;
833 l_error := TRUE;
834 END IF;
835
836 END LOOP;
837 CLOSE csr_claim_line;
838
839 IF l_error THEN
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842
843 ---------------------- Validation -----------------------------
844 -----------------------------------------------------
845 -- If earnings are associated with Claim, Claim can only be settled by
846 -- Check
847 -- Credit Memo-On Account
848 -- Debit Memo (for Charge claim class)
849 -- Contra Charge
850 -- Bug4241187: Support RMA, REG_CREDIT_MEMO, PREV CM/DM if
851 -- post_to_gl flag is N.
852 -- R12: Support EFT, WIRE, AP_DEBIT, PREV_OPEN_CREDIT
853 -- and PREV_OPEN_DEBIT
854 --- R12: Support earnings for custom settlement methods
855 -----------------------------------------------------
856 OPEN csr_payment_type(p_claim_rec.claim_id);
857 FETCH csr_payment_type INTO l_seeded_flag;
858 CLOSE csr_payment_type;
859
860 IF l_asso_earnings_exist AND NVL(l_seeded_flag, 'N') = 'N' THEN
861 NULL;
862 ELSIF l_asso_earnings_exist AND NVL(l_seeded_flag, 'N') = 'Y' THEN
863 IF p_claim_rec.payment_method IN ( 'CREDIT_MEMO'
864 , 'CHECK'
865 , 'DEBIT_MEMO'
866 , 'CONTRA_CHARGE'
867 , 'EFT'
868 , 'WIRE'
869 , 'AP_DEBIT'
870 , 'AP_DEFAULT'
871 , 'ACCOUNTING_ONLY' --For R12.1 Enhancement
872 ) THEN
873 NULL;
874 ELSIF p_claim_rec.payment_method IN ( 'REG_CREDIT_MEMO'
875 , 'RMA'
876 , 'PREV_OPEN_CREDIT'
877 , 'PREV_OPEN_DEBIT'
878 ) THEN
879 IF NVL(l_gl_acc_checking,'F') = 'T' THEN
880 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
881 FND_MESSAGE.set_name('OZF', 'OZF_POST_ASSO_PAYMETHOD_ERR');
882 FND_MSG_PUB.add;
883 END IF;
884 RAISE FND_API.G_EXC_ERROR;
885 END IF;
886 ELSE
887 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
888 FND_MESSAGE.set_name('OZF', 'OZF_SETL_ASSO_PAYMETHOD_ERR');
889 FND_MSG_PUB.add;
890 END IF;
891 RAISE FND_API.G_EXC_ERROR;
892 END IF;
893 END IF;
894
895 ---------------------------
896 -- Claim Line Validation
897 ---------------------------
898 -- raise error when line amount not equal to utilizations associated
899 IF l_line_util_err THEN
900 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
901 FND_MESSAGE.set_name('OZF', 'OZF_SETL_COMP_LINE_UTIL_ERR');
902 FND_MSG_PUB.add;
903 END IF;
904 RAISE FND_API.G_EXC_ERROR;
905 END IF;
906
907 -- raise error if performance complete is required.
908 IF l_offr_perf_flag_err THEN
909 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
910 FND_MESSAGE.set_name('OZF', 'OZF_SETL_COMP_LINE_PERF_ERR');
911 FND_MSG_PUB.add;
912 END IF;
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915
916 --//Bugfix : 13069412
917 OPEN csr_sum_line_amt(p_claim_rec.claim_id);
918 FETCH csr_sum_line_amt INTO l_cline_sum_curr_amt,l_cline_sum_curr_tax_amt;
919 CLOSE csr_sum_line_amt;
920
921 IF p_claim_rec.payment_method = 'REG_CREDIT_MEMO' THEN
922 l_line_sum_curr_amt := l_cline_sum_curr_amt + l_cline_sum_curr_tax_amt;
923 ELSE
924 l_line_sum_curr_amt := l_cline_sum_curr_amt;
925 END IF;
926
927 -- line amount should be equal to claim_currency_amount
928 -- if curreny code is the same as claim.
929 IF l_line_amount_err THEN
930 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
931 FND_MESSAGE.set_name('OZF', 'OZF_SETL_LINE_AMT_ERR');
932 FND_MSG_PUB.add;
933 END IF;
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 -- For payment_method other then credit memo or debit memo without specifying payment_reference,
938 -- sum of line.claim_currency_amount should be equal to amount_remaining in claim.
939 -- sum of line.claim_currency_amount could not be equal to amount_remaining in claim
940 -- in case of 1. CREDIT_MEMO settlement
941 -- 2. DEBIT_MEMO settlement
942 -- 3. CHECK settlement
943 -- 4. RMA settlement
944 --- R12: Also EFT, WIRE, AP_DEBIT
945 IF l_line_sum_curr_amt IS NULL OR
946 l_line_sum_curr_amt = 0 THEN
947 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
948 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SETL_AMOUNT_ERR');
949 FND_MSG_PUB.add;
950 END IF;
951 RAISE FND_API.G_EXC_ERROR;
952 ELSIF l_line_sum_curr_amt <> p_claim_rec.amount_remaining THEN
953 IF p_claim_rec.payment_method IN ('CREDIT_MEMO',
954 'DEBIT_MEMO',
955 'RMA',
956 'CHECK',
957 'EFT',
958 'WIRE',
959 'AP_DEBIT',
960 'AP_DEFAULT',
961 'ACCOUNTING_ONLY') THEN --R12.1 Enhancement
962 NULL;
963 ELSE
964 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
965 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SETL_AMOUNT_ERR');
966 FND_MSG_PUB.add;
967 END IF;
968 RAISE FND_API.G_EXC_ERROR;
969 END IF;
970 END IF;
971
972 ---------------------------
973 -- Fully Promotional Line Check:
974 -- either promotional or non-promontial check
975 ---------------------------
976 OPEN csr_line_prom_distinct_chk(p_claim_rec.claim_id);
977 LOOP
978 FETCH csr_line_prom_distinct_chk INTO l_line_earnings_flag;
979 EXIT WHEN csr_line_prom_distinct_chk%NOTFOUND;
980 IF csr_line_prom_distinct_chk%ROWCOUNT > 1 THEN
981 l_promo_distinct_err := TRUE;
982 EXIT;
983 END IF;
984 END LOOP;
985 CLOSE csr_line_prom_distinct_chk;
986
987 -- raise error if earning associated flag is not distinct.
988 IF l_promo_distinct_err THEN
989 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
990 FND_MESSAGE.set_name('OZF', 'OZF_SETL_COMP_LINE_PROM_ERR');
991 FND_MSG_PUB.add;
992 END IF;
993 RAISE FND_API.G_EXC_ERROR;
994 END IF;
995
996
997 ---------------------------
998 -- Owner Id: Required
999 ---------------------------
1000 IF p_claim_rec.owner_id IS NULL THEN
1001 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1002 FND_MESSAGE.set_name('OZF', 'OZF_SETL_OWNER_REQ');
1003 FND_MSG_PUB.add;
1004 END IF;
1005 RAISE FND_API.G_EXC_ERROR;
1006 END IF;
1007
1008 ---------------------------
1009 -- GL Date: should fall into an open period if entered.
1010 ---------------------------
1011 -- Fix for Bug#14550700
1012 IF p_claim_rec.gl_date IS NULL THEN
1013 OPEN csr_get_gl_date_type(p_claim_rec.set_of_books_id);
1014 FETCH csr_get_gl_date_type INTO l_sys_gl_date_type;
1015 CLOSE csr_get_gl_date_type;
1016
1017 IF l_gl_date_type = 'CLAIM_DATE' THEN
1018 l_gl_date := p_claim_rec.claim_date;
1019 ELSIF l_gl_date_type = 'SYSTEM_DATE' THEN
1020 l_gl_date := SYSDATE;
1021 END IF;
1022 ELSE
1023 l_gl_date := p_claim_rec.gl_date;
1024 END IF;
1025
1026 IF l_gl_date IS NOT NULL THEN
1027 IF p_claim_rec.payment_method IN ( 'CREDIT_MEMO'
1028 , 'DEBIT_MEMO'
1029 , 'CHARGEBACK'
1030 , 'REG_CREDIT_MEMO'
1031 , 'ON_ACCT_CREDIT'
1032 , 'WRITE_OFF'
1033 , 'CONTRA_CHARGE'
1034 , 'ACCOUNTING_ONLY' -- R12.1 Enhancement: For validating GL date
1035 ) THEN
1036 IF NOT gl_date_in_open(
1037 p_application_id => 222
1038 , p_claim_id => p_claim_rec.claim_id
1039 , p_gl_date => l_gl_date
1040 ) THEN
1041 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1042 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
1043 FND_MSG_PUB.add;
1044 END IF;
1045 RAISE FND_API.G_EXC_ERROR;
1046 END IF;
1047
1048 ELSIF p_claim_rec.payment_method IN ('CHECK', 'CONTRA_CHARGE','EFT','WIRE','AP_DEBIT','AP_DEFAULT') THEN
1049 IF NOT gl_date_in_open(
1050 p_application_id => 200
1051 , p_claim_id => p_claim_rec.claim_id
1052 , p_gl_date => l_gl_date
1053 ) THEN
1054 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1055 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
1056 FND_MSG_PUB.add;
1057 END IF;
1058 RAISE FND_API.G_EXC_ERROR;
1059 END IF;
1060 END IF;
1061 ELSE
1062
1063 IF l_sys_gl_date_type IS NULL THEN
1064 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1065 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_DRIVE_ERR');
1066 FND_MSG_PUB.add;
1067 END IF;
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070 END IF;
1071
1072
1073 /*------------------------------------------------------------------------------*
1074 | AR Validation |
1075 *------------------------------------------------------------------------------*/
1076 IF p_claim_rec.payment_method IN ( 'CREDIT_MEMO'
1077 , 'DEBIT_MEMO'
1078 , 'CHARGEBACK'
1079 , 'REG_CREDIT_MEMO'
1080 , 'ON_ACCT_CREDIT'
1081 , 'WRITE_OFF'
1082 , 'PREV_OPEN_CREDIT'
1083 , 'PREV_OPEN_DEBIT'
1084 ) THEN
1085
1086 OZF_AR_VALIDATION_PVT.Complete_AR_Validation(
1087 p_api_version => l_api_version
1088 ,p_init_msg_list => FND_API.g_false
1089 ,p_commit => FND_API.g_false
1090 ,p_validation_level => FND_API.g_valid_level_full
1091 ,x_return_status => l_return_status
1092 ,x_msg_data => x_msg_data
1093 ,x_msg_count => x_msg_count
1094 ,p_claim_rec => p_claim_rec
1095 );
1096 IF l_return_status = FND_API.g_ret_sts_error THEN
1097 RAISE FND_API.g_exc_error;
1098 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1099 RAISE FND_API.g_exc_unexpected_error;
1100 END IF;
1101
1102 /*------------------------------------------------------------------------------*
1103 | RMA Validation |
1104 *------------------------------------------------------------------------------*/
1105 ELSIF p_claim_rec.payment_method = 'RMA' THEN
1106 OZF_AR_VALIDATION_PVT.Complete_AR_Validation(
1107 p_api_version => l_api_version
1108 ,p_init_msg_list => FND_API.g_false
1109 ,p_commit => FND_API.g_false
1110 ,p_validation_level => FND_API.g_valid_level_full
1111 ,x_return_status => l_return_status
1112 ,x_msg_data => x_msg_data
1113 ,x_msg_count => x_msg_count
1114 ,p_claim_rec => p_claim_rec
1115 );
1116 IF l_return_status = FND_API.g_ret_sts_error THEN
1117 RAISE FND_API.g_exc_error;
1118 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1119 RAISE FND_API.g_exc_unexpected_error;
1120 END IF;
1121
1122 OZF_OM_VALIDATION_PVT.Complete_RMA_Validation(
1123 p_api_version => l_api_version
1124 ,p_init_msg_list => FND_API.g_false
1125 ,p_validation_level => FND_API.g_valid_level_full
1126 ,x_return_status => l_return_status
1127 ,x_msg_data => x_msg_data
1128 ,x_msg_count => x_msg_count
1129 ,p_claim_rec => p_claim_rec
1130 ,x_claim_rec => x_claim_rec
1131 );
1132 IF l_return_status = FND_API.g_ret_sts_error THEN
1133 RAISE FND_API.g_exc_error;
1134 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1135 RAISE FND_API.g_exc_unexpected_error;
1136 END IF;
1137
1138 /*------------------------------------------------------------------------------*
1139 | AP Validation (CHECK,EFT,WIRE,AP_DEBIT,AP_DEFAULT |
1140 *------------------------------------------------------------------------------*/
1141 ELSIF p_claim_rec.payment_method IN ( 'CHECK', 'EFT','WIRE','AP_DEBIT','AP_DEFAULT') THEN
1142 -----------------------------------------------------
1143 -- Payable Source
1144 -----------------------------------------------------
1145 IF l_payables_source IS NULL THEN
1146 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1147 FND_MESSAGE.set_name('OZF', 'OZF_SETL_PAYABLES_SOURCE_NULL');
1148 FND_MSG_PUB.add;
1149 END IF;
1150 RAISE FND_API.G_EXC_ERROR;
1151 END IF;
1152
1153 -----------------------------------------------------
1154 -- Vendor_Id
1155 -----------------------------------------------------
1156 IF p_claim_rec.vendor_id IS NULL THEN
1157 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1158 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VENDOR_REQ');
1159 FND_MESSAGE.set_token('PAY_METHOD', OZF_Utility_Pvt.get_lookup_meaning('OZF_PAYMENT_METHOD', p_claim_rec.payment_method));
1160 FND_MSG_PUB.add;
1161 END IF;
1162 RAISE FND_API.G_EXC_ERROR;
1163 END IF;
1164
1165 -----------------------------------------------------
1166 -- Vendor_Site_Id
1167 -----------------------------------------------------
1168 IF p_claim_rec.vendor_site_id IS NULL THEN
1169 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1170 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VENDOR_SITE_REQ');
1171 FND_MESSAGE.set_token('PAY_METHOD', OZF_Utility_Pvt.get_lookup_meaning('OZF_PAYMENT_METHOD', p_claim_rec.payment_method));
1172 FND_MSG_PUB.add;
1173 END IF;
1174 RAISE FND_API.G_EXC_ERROR;
1175 END IF;
1176
1177 -------------------------------------------------------
1178 --Check for active vendor and vendor site(Bug 14236524 )
1179 -------------------------------------------------------
1180 IF p_claim_rec.vendor_id IS NOT NULL
1181 AND p_claim_rec.vendor_site_id IS NOT NULL THEN
1182 OPEN csr_active_vendor(p_claim_rec.vendor_id, p_claim_rec.vendor_site_id);
1183 FETCH csr_active_vendor INTO l_active_vendor_num;
1184 CLOSE csr_active_vendor;
1185 IF l_active_vendor_num is null THEN
1186 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1187 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SETL_VENDOR_INACTIVE');
1188 FND_MSG_PUB.add;
1189 END IF;
1190 RAISE FND_API.G_EXC_ERROR;
1191 END IF;
1192 END IF;
1193 -----------------------------------------------------
1194 -- Vendor Clearing Account
1195 -----------------------------------------------------
1196 -- vendor clearing account must exist in system parameter
1197 IF l_gl_acc_checking = FND_API.g_true THEN
1198 IF l_vendor_in_sys IS NULL THEN
1199 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1200 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VENCLRACC_REQ');
1201 FND_MSG_PUB.add;
1202 END IF;
1203 RAISE FND_API.G_EXC_ERROR;
1204 END IF;
1205 END IF;
1206
1207 /*------------------------------------------------------------------------------*
1208 | CONTRA_CHARGE |
1209 *------------------------------------------------------------------------------*/
1210 ELSIF p_claim_rec.payment_method = 'CONTRA_CHARGE' THEN
1211
1212 -- check vendor_id
1213 IF p_claim_rec.vendor_id IS NULL THEN
1214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1215 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VENDOR_REQ');
1216 FND_MESSAGE.set_token('PAY_METHOD', OZF_Utility_Pvt.get_lookup_meaning('OZF_PAYMENT_METHOD', p_claim_rec.payment_method));
1217 FND_MSG_PUB.add;
1218 END IF;
1219 RAISE FND_API.G_EXC_ERROR;
1220 END IF;
1221
1222 -- check vendor_site_id
1223 IF p_claim_rec.vendor_site_id IS NULL THEN
1224 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1225 FND_MESSAGE.set_name('OZF', 'OZF_SETL_VENDOR_SITE_REQ');
1226 FND_MESSAGE.set_token('PAY_METHOD', OZF_Utility_Pvt.get_lookup_meaning('OZF_PAYMENT_METHOD', p_claim_rec.payment_method));
1227 FND_MSG_PUB.add;
1228 END IF;
1229 RAISE FND_API.G_EXC_ERROR;
1230 END IF;
1231
1232
1233 /*------------------------------------------------------------------------------*
1234 | NON-SEEDED PAYMENT METHOD |
1235 *------------------------------------------------------------------------------*/
1236 ELSE
1237
1238 -- R12: Validate depending on AR or AP Settlement
1239 NULL;
1240
1241 END IF;
1242
1243 IF OZF_DEBUG_HIGH_ON THEN
1244 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1245 END IF;
1246 EXCEPTION
1247 WHEN FND_API.G_EXC_ERROR THEN
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 WHEN OTHERS THEN
1252 IF OZF_DEBUG_LOW_ON THEN
1253 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1254 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1255 FND_MSG_PUB.Add;
1256 END IF;
1257 x_return_status := FND_API.g_ret_sts_unexp_error;
1258
1259 END Complete_Claim_Validation;
1260
1261 /*=======================================================================*
1262 | Procedure
1263 | Complete_Claim
1264 |
1265 | Return
1266 |
1267 | NOTES
1268 |
1269 | HISTORY
1270 | 16-JAN-2003 mchang Create.
1271 *=======================================================================*/
1272 PROCEDURE Complete_Claim(
1273 p_api_version IN NUMBER
1274 ,p_init_msg_list IN VARCHAR2
1275 ,p_validation_level IN NUMBER
1276
1277 ,x_return_status OUT NOCOPY VARCHAR2
1278 ,x_msg_data OUT NOCOPY VARCHAR2
1279 ,x_msg_count OUT NOCOPY NUMBER
1280
1281 ,p_x_claim_rec IN OUT NOCOPY OZF_CLAIM_PVT.claim_rec_type
1282 )
1283 IS
1284 l_api_version CONSTANT NUMBER := 1.0;
1285 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Claim';
1286 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1287 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
1288
1289 BEGIN
1290 IF OZF_DEBUG_HIGH_ON THEN
1291 OZF_Utility_PVT.debug_message(l_full_name||' : start');
1292 END IF;
1293
1294 -- Initialize API return status to sucess
1295 x_return_status := FND_API.g_ret_sts_success;
1296
1297 IF p_x_claim_rec.payment_method = 'RMA' THEN
1298 IF p_x_claim_rec.order_type_id IS NULL THEN
1299 OZF_OM_VALIDATION_PVT.Get_Default_Order_Type(
1300 p_api_version => l_api_version
1301 ,p_init_msg_list => FND_API.g_false
1302 ,p_validation_level => FND_API.g_valid_level_full
1303 ,x_return_status => l_return_status
1304 ,x_msg_data => x_msg_data
1305 ,x_msg_count => x_msg_count
1306 ,p_reason_code_id => p_x_claim_rec.reason_code_id
1307 ,p_claim_type_id => p_x_claim_rec.claim_type_id
1308 ,p_set_of_books_id => p_x_claim_rec.set_of_books_id
1309 ,x_order_type_id => p_x_claim_rec.order_type_id
1310 );
1311 IF l_return_status = FND_API.g_ret_sts_error THEN
1312 RAISE FND_API.g_exc_error;
1313 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1314 RAISE FND_API.g_exc_unexpected_error;
1315 END IF;
1316 END IF;
1317 ELSE
1318 -- erase RMA transaction type if not for RMA settlement.
1319 p_x_claim_rec.order_type_id := NULL;
1320 END IF;
1321
1322 IF p_x_claim_rec.payment_method NOT IN ('PREV_OPEN_CREDIT', 'PREV_OPEN_DEBIT', 'CHECK', 'RMA','EFT',
1323 'AP_DEFAULT','WIRE','AP_DEBIT') THEN
1324 p_x_claim_rec.payment_reference_id := NULL;
1325 p_x_claim_rec.payment_reference_number := NULL;
1326 END IF;
1327
1328 IF OZF_DEBUG_HIGH_ON THEN
1329 OZF_Utility_PVT.debug_message(l_full_name||' : end');
1330 END IF;
1331
1332 EXCEPTION
1333 WHEN FND_API.G_EXC_ERROR THEN
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1337 WHEN OTHERS THEN
1338 IF OZF_DEBUG_LOW_ON THEN
1339 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1340 FND_MESSAGE.Set_Token('TEXT',l_full_name||' : Error');
1341 FND_MSG_PUB.Add;
1342 END IF;
1343 x_return_status := FND_API.g_ret_sts_unexp_error;
1344
1345 END Complete_Claim;
1346
1347
1348 END OZF_CLAIM_SETTLEMENT_VAL_PVT;