[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_LINE_PVT
Source
1 PACKAGE BODY OZF_CLAIM_LINE_PVT AS
2 /* $Header: ozfvclnb.pls 120.45.12020000.4 2013/03/05 06:14:55 apyadav ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='OZF_Claim_Line_PVT';
5
6 g_delete CONSTANT VARCHAR2(30):='DELETE';
7
8 G_FUNCTIONAL_CURRENCY VARCHAR2(15) := NULL;
9 G_OFFER_CURRENCY VARCHAR2(15) := NULL;
10 G_CLAIM_SET_OF_BOOKS_ID NUMBER := NULL;
11 G_CLAIM_CURRENCY VARCHAR2(15) := NULL;
12 G_CLAIM_EXC_TYPE VARCHAR2(30) := NULL;
13 G_CLAIM_EXC_DATE DATE := NULL;
14 G_CLAIM_EXC_RATE NUMBER := NULL;
15
16 -- object_type
17 G_CLAIM_OBJECT_TYPE CONSTANT VARCHAR2(30) := 'CLAM';
18
19 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
20 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
21
22
23
24 ---------------------------------------------------------------------
25 -- FUNCTION
26 -- Compare_Line_Items
27 --
28 -- NOTES
29 -- 1. Compare_Line_Items function will return FND_API.g_true/false;
30 -- 2. Return FND_API.g_true: Compare is the same;
31 -- 3. Return FND_API.g_false: Compare is not the same;
32 --
33 -- HISTORY
34 -- 02/01/2001 mchang Created.
35 -- 07/02/2001 mchang Incorporated with AMS_COLUMN_RULES to look up history
36 -- rule setting by user.
37 -- 07/26/2001 mchang Add Tax_Code as a comparing column.
38 ---------------------------------------------------------------------
39 FUNCTION Compare_Line_Items(
40 p_old_rec IN claim_line_rec_type
41 , p_new_rec IN claim_line_rec_type
42 , p_object_attribute IN VARCHAR2
43 )
44 RETURN VARCHAR2
45 IS
46 CURSOR csr_user_hist_cols(cv_object_attribute IN VARCHAR2) IS
47 SELECT db_column_name
48 --, ak_attribute_code
49 FROM ams_column_rules
50 WHERE db_table_name = 'OZF_CLAIM_LINES_ALL'
51 AND rule_type = 'HISTORY'
52 AND object_type = 'CLAM'
53 AND object_attribute = cv_object_attribute;
54
55 l_line_hist_col VARCHAR2(80);
56 l_return VARCHAR2(1) := FND_API.g_true;
57
58 BEGIN
59 OPEN csr_user_hist_cols(p_object_attribute);
60 LOOP
61 FETCH csr_user_hist_cols INTO l_line_hist_col;
62 EXIT WHEN csr_user_hist_cols%NOTFOUND;
63 EXIT WHEN l_return = FND_API.g_false;
64 IF l_line_hist_col = 'VALID_FLAG' AND
65 p_old_rec.valid_flag <> p_new_rec.valid_flag THEN
66 l_return := FND_API.g_false;
67 ELSIF l_line_hist_col = 'PERFORMANCE_COMPLETE_FLAG' AND
68 p_old_rec.performance_complete_flag <> p_new_rec.performance_complete_flag THEN
69 l_return := FND_API.g_false;
70 ELSIF l_line_hist_col = 'PERFORMANCE_ATTACHED_FLAG' AND
71 p_old_rec.performance_attached_flag <> p_new_rec.performance_attached_flag THEN
72 l_return := FND_API.g_false;
73 ELSIF l_line_hist_col = 'EARNINGS_ASSOCIATED_FLAG' AND
74 p_old_rec.earnings_associated_flag <> p_new_rec.earnings_associated_flag THEN
75 l_return := FND_API.g_false;
76 ELSIF l_line_hist_col = 'CLAIM_CURRENCY_AMOUNT' AND
77 p_old_rec.claim_currency_amount <> p_new_rec.claim_currency_amount THEN
78 l_return := FND_API.g_false;
79 ELSIF l_line_hist_col = 'ACTIVITY_TYPE' AND
80 p_old_rec.activity_type <> p_new_rec.activity_type THEN
81 l_return := FND_API.g_false;
82 ELSIF l_line_hist_col = 'ACTIVITY_ID' AND
83 p_old_rec.activity_id <> p_new_rec.activity_id THEN
84 l_return := FND_API.g_false;
85 ELSIF l_line_hist_col = 'PLAN_ID' AND
86 p_old_rec.plan_id <> p_new_rec.plan_id THEN
87 l_return := FND_API.g_false;
88 ELSIF l_line_hist_col = 'OFFER_ID' AND
89 p_old_rec.offer_id <> p_new_rec.offer_id THEN
90 l_return := FND_API.g_false;
91 ELSIF l_line_hist_col = 'SOURCE_OBJECT_ID' AND
92 p_old_rec.source_object_id <> p_new_rec.source_object_id THEN
93 l_return := FND_API.g_false;
94 ELSIF l_line_hist_col = 'SOURCE_OBJECT_LINE_ID' AND
95 p_old_rec.source_object_line_id <> p_new_rec.source_object_line_id THEN
96 l_return := FND_API.g_false;
97 ELSIF l_line_hist_col = 'SOURCE_OBJECT_CLASS' AND
98 p_old_rec.source_object_class <> p_new_rec.source_object_class THEN
99 l_return := FND_API.g_false;
100 ELSIF l_line_hist_col = 'QUANTITY' AND
101 p_old_rec.quantity <> p_new_rec.quantity THEN
102 l_return := FND_API.g_false;
103 ELSIF l_line_hist_col = 'RATE' AND
104 p_old_rec.rate <> p_new_rec.rate THEN
105 l_return := FND_API.g_false;
106 ELSIF l_line_hist_col = 'QUANTITY_UOM' AND
107 p_old_rec.quantity_uom <> p_new_rec.quantity_uom THEN
108 l_return := FND_API.g_false;
109 ELSIF l_line_hist_col = 'ITEM_ID' AND
110 p_old_rec.item_id <> p_new_rec.item_id THEN
111 l_return := FND_API.g_false;
112 ELSIF l_line_hist_col = 'ITEM_DESCRIPTION' AND
113 p_old_rec.item_description <> p_new_rec.item_description THEN
114 l_return := FND_API.g_false;
115 ELSIF l_line_hist_col = 'COMMENTS' AND
116 p_old_rec.comments <> p_new_rec.comments THEN
117 l_return := FND_API.g_false;
118 ELSIF l_line_hist_col = 'TAX_CODE' AND
119 p_old_rec.tax_code <> p_new_rec.tax_code THEN
120 l_return := FND_API.g_false;
121 ELSIF l_line_hist_col = 'CREDIT_TO' AND
122 p_old_rec.credit_to <> p_new_rec.credit_to THEN
123 l_return := FND_API.g_false;
124 -- ELSE
125 -- l_return := FND_API.g_true;
126 END IF;
127 END LOOP;
128 CLOSE csr_user_hist_cols;
129
130 RETURN l_return;
131
132 END Compare_Line_Items;
133
134 ---------------------------------------------------------------------
135 -- FUNCTION
136 -- get_default_product_uom
137 --
138 -- PURPOSE
139 -- This returns default uom for a product
140 --
141 -- PARAMETERS
142 -- p_reason_code_id
143 -- p_org_id
144 --
145 -- NOTES
146 ---------------------------------------------------------------------
147 FUNCTION Get_Default_Product_UOM(p_product_id in NUMBER
148 , p_org_id in NUMBER
149 )
150 RETURN VARCHAR2
151 IS
152
153 CURSOR default_uom_csr (p_id in number, p_orgid in number) is
154 select primary_uom_code
155 from mtl_system_items
156 where inventory_item_id = p_id
157 and organization_id = p_orgid;
158
159 l_default_uom varchar2(30);
160 BEGIN
161
162 If ((p_product_id is not null and p_product_id <> FND_API.G_MISS_NUM)
163 AND (p_org_id is not null and p_org_id <> FND_API.G_MISS_NUM))
164 THEN
165 OPEN default_uom_csr(p_product_id, p_org_id);
166 FETCH default_uom_csr into l_default_uom;
167 CLOSE default_uom_csr;
168
169 ELSE
170 l_default_uom := null;
171 END IF;
172 return l_default_uom;
173
174 END Get_Default_Product_UOM;
175
176
177 ---------------------------------------------------------------------
178 -- PROCEDURE
179 -- Check_Create_Line_Hist
180 --
181 -- NOTES
182 -- 1. p_mode should be JTF_PLSQL_API.g_create/update, or g_delete
183 -- 2. x_create_hist_flag will be set to 'Y' or 'N'
184 -- 3. p_object_attribute could be 'LINE' or 'LNDT'.
185 --
186 -- HISTORY
187 -- 02/01/2001 mchang Create.
188 -- 07/26/2001 mchang Add Tax_Code as a history rule checking column.
189 -- 08/13/2001 mchang Add p_object_attribute as passing in parameter.
190 ---------------------------------------------------------------------
191 PROCEDURE Check_Create_Line_Hist(
192 p_api_version IN NUMBER
193 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
194
195 ,x_return_status OUT NOCOPY VARCHAR2
196 ,x_msg_count OUT NOCOPY NUMBER
197 ,x_msg_data OUT NOCOPY VARCHAR2
198
199 ,p_mode IN VARCHAR2
200 ,p_claim_line_rec IN claim_line_rec_type
201 ,p_object_attribute IN VARCHAR2
202 ,x_create_hist_flag OUT NOCOPY VARCHAR2
203 )
204 IS
205 l_api_version CONSTANT NUMBER := 1.0;
206 l_api_name CONSTANT VARCHAR2(30) := 'Check_Create_Line_Hist';
207 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
208
209 l_return_status VARCHAR2(1);
210 l_new_line_rec claim_line_rec_type;
211 l_old_line_rec claim_line_rec_type;
212 l_compare_result VARCHAR2(1);
213
214 CURSOR c_claim_old_line(cv_claim_line_id IN NUMBER) IS
215 SELECT claim_id
216 , valid_flag
217 , performance_complete_flag
218 , performance_attached_flag
219 , earnings_associated_flag
220 , set_of_books_id
221 , claim_currency_amount
222 , amount
223 , acctd_amount
224 , activity_type
225 , activity_id
226 , plan_id
227 , offer_id
228 , source_object_id
229 , source_object_line_id
230 , source_object_class
231 , quantity
232 , rate
233 , quantity_uom
234 , item_id
235 , item_description
236 , comments
237 , tax_code
238 , credit_to
239 FROM ozf_claim_lines_hist
240 WHERE claim_line_id = cv_claim_line_id
241 order by claim_line_history_id desc;
242
243 BEGIN
244 --------------------- initialize -----------------------
245 SAVEPOINT Check_Create_Line_Hist;
246
247 IF OZF_DEBUG_HIGH_ON THEN
248 OZF_Utility_PVT.debug_message(l_full_name||': start');
249 END IF;
250
251 IF FND_API.to_boolean(p_init_msg_list) THEN
252 FND_MSG_PUB.initialize;
253 END IF;
254
255 IF NOT FND_API.compatible_api_call(
256 l_api_version,
257 p_api_version,
258 l_api_name,
259 g_pkg_name
260 ) THEN
261 RAISE FND_API.g_exc_unexpected_error;
262 END IF;
263
264 IF p_mode <> 'CREATE' THEN
265 -- replace g_miss_char/num/date with current column values
266 Complete_Claim_Line_Rec(
267 p_claim_line_rec => p_claim_line_rec
268 ,x_complete_rec => l_new_line_rec
269 );
270 ELSE
271 l_new_line_rec := p_claim_line_rec;
272 END IF;
273
274
275 x_return_status := FND_API.g_ret_sts_success;
276
277 ------------------------ start -------------------------
278 -- fetch out old claim line record
279 IF p_mode = JTF_PLSQL_API.g_create THEN
280 l_old_line_rec := null;
281 ELSE
282 IF p_claim_line_rec.claim_line_id <> FND_API.g_miss_num
283 AND p_claim_line_rec.claim_line_id IS NOT NULL THEN
284 OPEN c_claim_old_line(p_claim_line_rec.claim_line_id);
285 FETCH c_claim_old_line INTO l_old_line_rec.claim_id
286 , l_old_line_rec.valid_flag
287 , l_old_line_rec.performance_complete_flag
288 , l_old_line_rec.performance_attached_flag
289 , l_old_line_rec.earnings_associated_flag
290 , l_old_line_rec.set_of_books_id
291 , l_old_line_rec.claim_currency_amount
292 , l_old_line_rec.amount
293 , l_old_line_rec.acctd_amount
294 , l_old_line_rec.activity_type
295 , l_old_line_rec.activity_id
296 , l_old_line_rec.plan_id
297 , l_old_line_rec.offer_id
298 , l_old_line_rec.source_object_id
299 , l_old_line_rec.source_object_line_id
300 , l_old_line_rec.source_object_class
301 , l_old_line_rec.quantity
302 , l_old_line_rec.rate
303 , l_old_line_rec.quantity_uom
304 , l_old_line_rec.item_id
305 , l_old_line_rec.item_description
306 , l_old_line_rec.comments
307 , l_old_line_rec.tax_code
308 , l_old_line_rec.credit_to;
309 CLOSE c_claim_old_line;
310 END IF;
311 END IF;
312 ------------------------ comparison -------------------------
313 IF p_mode = JTF_PLSQL_API.g_create
314 THEN
315 x_create_hist_flag := 'Y';
316
317 --In create mode always create a history record.
318
319 -- Init_Claim_Line_Rec(
320 -- x_claim_line_rec => l_old_line_rec
321 -- );
322 -- l_compare_result := Compare_Line_Items(
323 -- p_old_rec => l_old_line_rec
324 -- ,p_new_rec => l_new_line_rec
325 -- ,p_object_attribute => p_object_attribute
326 -- );
327 -- IF l_compare_result = FND_API.g_true THEN
328 -- x_create_hist_flag := 'N';
329 -- ELSE
330 -- x_create_hist_flag := 'Y';
331 -- END IF;
332
333
334 ELSIF p_mode = g_delete THEN
335 --x_create_hist_flag := 'Y';
336 l_compare_result := Compare_Line_Items(
337 p_old_rec => l_old_line_rec
338 ,p_new_rec => l_new_line_rec
339 ,p_object_attribute => p_object_attribute
340 );
341 IF l_compare_result = FND_API.g_true THEN
342 x_create_hist_flag := 'N';
343 ELSE
344 x_create_hist_flag := 'Y';
345 END IF;
346 ELSIF p_mode = JTF_PLSQL_API.g_update THEN
347 l_compare_result := Compare_Line_Items(
348 p_old_rec => l_old_line_rec
349 ,p_new_rec => l_new_line_rec
350 ,p_object_attribute => p_object_attribute
351 );
352 IF l_compare_result = FND_API.g_true THEN
353 x_create_hist_flag := 'N';
354 ELSE
355 x_create_hist_flag := 'Y';
356 END IF;
357 ELSE
358 IF OZF_DEBUG_HIGH_ON THEN
359 OZF_Utility_PVT.debug_message(l_full_name||': p_mode should be CREATE, UPDATE, or DELETE');
360 END IF;
361 RAISE FND_API.g_exc_error;
362 END IF;
363
364 ------------------------- finish -------------------------------
365 FND_MSG_PUB.count_and_get(
366 p_encoded => FND_API.g_false,
367 p_count => x_msg_count,
368 p_data => x_msg_data
369 );
370
371 IF OZF_DEBUG_HIGH_ON THEN
372 OZF_Utility_PVT.debug_message(l_full_name ||': end');
373 END IF;
374
375 EXCEPTION
376 WHEN FND_API.g_exc_error THEN
377 ROLLBACK TO Check_Create_Line_Hist;
378 x_return_status := FND_API.g_ret_sts_error;
379 FND_MSG_PUB.count_and_get (
380 p_encoded => FND_API.g_false
381 ,p_count => x_msg_count
382 ,p_data => x_msg_data
383 );
384
385 WHEN FND_API.g_exc_unexpected_error THEN
386 ROLLBACK TO Check_Create_Line_Hist;
387 x_return_status := FND_API.g_ret_sts_unexp_error ;
388 FND_MSG_PUB.count_and_get (
389 p_encoded => FND_API.g_false
390 ,p_count => x_msg_count
391 ,p_data => x_msg_data
392 );
393
394 WHEN OTHERS THEN
395 ROLLBACK TO Check_Create_Line_Hist;
396 x_return_status := FND_API.g_ret_sts_unexp_error ;
397 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
398 THEN
399 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
400 END IF;
401 FND_MSG_PUB.count_and_get(
402 p_encoded => FND_API.g_false
403 ,p_count => x_msg_count
404 ,p_data => x_msg_data
405 );
406
407 END Check_Create_Line_Hist;
408
409 ---------------------------------------------------------------------
410 -- PROCEDURE
411 -- Convert_Line_Amount
412 --
413 -- HISTORY
414 -- 29-AUG-2001 mchang Create.
415 -- 06-AUG-2001 mchang Updated : Convert Line Amount from Acctd_Amount
416 ---------------------------------------------------------------------
417 PROCEDURE Convert_Line_Amount(
418 p_claim_line_rec IN claim_line_rec_type
419 ,x_claim_line_rec OUT NOCOPY claim_line_rec_type
420 ,x_return_status OUT NOCOPY VARCHAR2
421 ) IS
422 l_api_name CONSTANT VARCHAR2(30) := 'Convert_Line_Amount';
423 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
424
425 -- fix for bug 5042046
426 CURSOR csr_function_currency IS
427 SELECT gs.currency_code
428 FROM gl_sets_of_books gs
429 , ozf_sys_parameters org
430 WHERE org.set_of_books_id = gs.set_of_books_id
431 AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
432
433 CURSOR csr_claim_currency(cv_claim_id IN NUMBER) IS
434 SELECT currency_code
435 FROM ozf_claims
436 WHERE claim_id = cv_claim_id;
437
438 CURSOR csr_order_default_exc(cv_doc_id IN NUMBER) IS
439 SELECT transactional_curr_code
440 , conversion_type_code
441 , conversion_rate
442 , conversion_rate_date
443 FROM oe_order_headers
444 WHERE header_id = cv_doc_id;
445
446 CURSOR csr_invoice_default_exc(cv_doc_id IN NUMBER) IS
447 SELECT invoice_currency_code
448 , exchange_rate_type
449 , exchange_rate
450 , exchange_date
451 FROM ra_customer_trx
452 WHERE customer_trx_id = cv_doc_id;
453 /*
454 CURSOR csr_invline_default_exc(cv_doc_id IN NUMBER) IS
455 SELECT h.invoice_currency_code
456 , h.exchange_rate_type
457 , h.exchange_rate
458 , h.exchange_date
459 FROM ra_customer_trx h, ra_customer_trx_lines ln
460 WHERE h.customer_trx_id = ln.customer_trx_id
461 AND ln.customer_trx_line_id = cv_doc_id;
462 */
463 CURSOR csr_pcho_default_exc(cv_doc_id IN NUMBER) IS
464 SELECT currency_code
465 , rate_type
466 , rate
467 , rate_date
468 FROM po_headers
469 WHERE po_header_id = cv_doc_id;
470
471 l_return_status VARCHAR2(1);
472 l_claim_currency VARCHAR2(15);
473 l_function_currency VARCHAR2(15);
474 l_default_currency VARCHAR2(15) := NULL;
475 l_default_exc_type VARCHAR2(30);
476 l_default_exc_date DATE;
477 l_default_exc_rate NUMBER;
478 l_line_amount NUMBER;
479 l_rate NUMBER;
480 l_tri_denominator NUMBER;
481 l_tri_numerator NUMBER;
482
483 l_claim_line_rec claim_line_rec_type := p_claim_line_rec;
484
485 BEGIN
486 x_return_status := FND_API.g_ret_sts_success;
487 -- Added check for Credit Memo for bug # 12369100
488 IF p_claim_line_rec.source_object_id IS NOT NULL THEN
489 IF p_claim_line_rec.source_object_class IN ('INVOICE', 'CB', 'DM','CM') THEN --//Fix for Bug 16301455 - Added Credit Memo -CM
490 OPEN csr_invoice_default_exc(p_claim_line_rec.source_object_id);
491 FETCH csr_invoice_default_exc INTO l_default_currency,
492 l_default_exc_type,
493 l_default_exc_rate,
494 l_default_exc_date;
495 CLOSE csr_invoice_default_exc;
496 ELSIF p_claim_line_rec.source_object_class = 'ORDER' THEN
497 OPEN csr_order_default_exc(p_claim_line_rec.source_object_id);
498 FETCH csr_order_default_exc INTO l_default_currency,
499 l_default_exc_type,
500 l_default_exc_rate,
501 l_default_exc_date;
502 CLOSE csr_order_default_exc;
503 ELSIF p_claim_line_rec.source_object_class = 'PCHO' THEN
504 OPEN csr_pcho_default_exc(p_claim_line_rec.source_object_id);
505 FETCH csr_pcho_default_exc INTO l_default_currency,
506 l_default_exc_type,
507 l_default_exc_rate,
508 l_default_exc_date;
509 CLOSE csr_pcho_default_exc;
510 END IF;
511 END IF;
512
513 IF l_default_currency IS NOT NULL THEN
514 OPEN csr_claim_currency(p_claim_line_rec.claim_id);
515 FETCH csr_claim_currency INTO l_claim_currency;
516 CLOSE csr_claim_currency;
517
518 /*
519 BEGIN
520 GL_CURRENCY_API.get_triangulation_rate (
521 x_from_currency => l_default_currency,
522 x_to_currency => l_claim_currency,
523 x_conversion_date => l_default_exc_date,
524 x_conversion_type => l_default_exc_type,
525 x_denominator => l_tri_denominator,
526 x_numerator => l_tri_numerator,
527 x_rate => l_rate
528 );
529 EXCEPTION
530 WHEN OTHERS THEN
531 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
532 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_TRIANG_API_ERR');
533 FND_MSG_PUB.add;
534 END IF;
535 RAISE FND_API.g_exc_error;
536 END;
537
538 l_line_amount := p_claim_line_rec.claim_currency_amount * l_rate;
539 */
540 IF l_default_currency = l_claim_currency THEN
541 l_claim_line_rec.amount := l_claim_line_rec.claim_currency_amount;
542 l_claim_line_rec.currency_code := l_default_currency;
543 l_claim_line_rec.exchange_rate_type := l_default_exc_type;
544 l_claim_line_rec.exchange_rate_date := l_default_exc_date;
545 l_claim_line_rec.exchange_rate := l_default_exc_rate;
546 ELSE
547 OPEN csr_function_currency;
548 FETCH csr_function_currency INTO l_function_currency;
549 CLOSE csr_function_currency;
550 OZF_UTILITY_PVT.Convert_Currency(
551 p_from_currency => l_function_currency
552 ,p_to_currency => l_default_currency
553 ,p_conv_type => l_default_exc_type
554 ,p_conv_rate => 1/l_default_exc_rate -- Bug4437696
555 ,p_conv_date => l_default_exc_date
556 ,p_from_amount => p_claim_line_rec.acctd_amount
557 ,x_return_status => l_return_status
558 ,x_to_amount => l_line_amount
559 ,x_rate => l_rate
560 );
561 IF l_return_status = FND_API.g_ret_sts_error THEN
562 RAISE FND_API.g_exc_error;
563 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
564 RAISE FND_API.g_exc_unexpected_error;
565 END IF;
566
567 l_claim_line_rec.currency_code := l_default_currency;
568 l_claim_line_rec.exchange_rate_type := l_default_exc_type;
569 l_claim_line_rec.exchange_rate_date := l_default_exc_date;
570 l_claim_line_rec.exchange_rate := 1/l_rate; -- Bug4437696
571 l_claim_line_rec.amount := l_line_amount;
572 END IF;
573 END IF;
574
575 x_claim_line_rec := l_claim_line_rec;
576
577 EXCEPTION
578 WHEN FND_API.g_exc_error THEN
579 x_return_status := FND_API.g_ret_sts_error;
580
581 WHEN FND_API.g_exc_unexpected_error THEN
582 x_return_status := FND_API.g_ret_sts_unexp_error ;
583
584 WHEN OTHERS THEN
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586 IF OZF_DEBUG_LOW_ON THEN
587 FND_MESSAGE.set_name('OZF', 'OZF_API_DEBUG_MESSAGE');
588 FND_MESSAGE.set_token('TEXT', l_full_name||': An error happened while converting line amount');
589 FND_MSG_PUB.add;
590 END IF;
591
592 END Convert_Line_Amount;
593
594
595 ---------------------------------------------------------------------
596 -- PROCEDURE
597 -- Update_Line_Fm_Claim
598 --
599 -- HISTORY
600 -- 07/26/2001 mchang Create.
601 ---------------------------------------------------------------------
602 PROCEDURE Update_Line_Fm_Claim(
603 p_api_version IN NUMBER
604 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
605 ,p_commit IN VARCHAR2 := FND_API.g_false
606 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
607
608 ,x_return_status OUT NOCOPY VARCHAR2
609 ,x_msg_data OUT NOCOPY VARCHAR2
610 ,x_msg_count OUT NOCOPY NUMBER
611
612 ,p_new_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
613 )
614 IS
615 l_api_version CONSTANT NUMBER := 1.0;
616 l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Fm_Claim';
617 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
618
619 l_return_status VARCHAR2(1);
620 l_msg_count NUMBER;
621 l_msg_data VARCHAR2(4000);
622
623 l_old_claim_rec OZF_CLAIM_PVT.claim_rec_type;
624 l_upd_line_flag VARCHAR2(1) := FND_API.g_false;
625 l_remove_tax_code VARCHAR2(1) := FND_API.g_false;
626 l_remove_related_cust VARCHAR2(1) := FND_API.g_false;
627 l_amount_change_flag VARCHAR2(1) := FND_API.g_false; --Bug:2781186
628
629 l_claim_line_tbl claim_line_tbl_type;
630 l_line_counter NUMBER := 1;
631 l_object_version NUMBER;
632 l_claim_line_count NUMBER := 0; --Bug:2781186
633 l_claim_line_amount NUMBER := 0; --Bug:2781186
634
635
636 CURSOR csr_claim_old_rec(cv_claim_id IN NUMBER) IS
637 SELECT currency_code
638 , exchange_rate_type
639 , exchange_rate_date
640 , exchange_rate
641 , cust_account_id
642 , payment_method
643 , tax_code
644 , set_of_books_id
645 , amount -- Bug:2781186
646 FROM ozf_claims
647 WHERE claim_id = cv_claim_id;
648
649 CURSOR csr_line_rec_upd(cv_claim_id IN NUMBER) IS
650 SELECT claim_line_id
651 , object_version_number
652 , claim_currency_amount
653 , tax_code
654 , earnings_associated_flag --Bug:2781186
655 FROM ozf_claim_lines
656 WHERE claim_id = cv_claim_id;
657
658
659 --Start:Bug:2781186
660 -- get how many claim lines for the given claim. If count is greate than 1 means
661 -- user has modified the line amount and he must be investigating this claim.
662 CURSOR csr_claim_line_count(cv_claim_id IN NUMBER) IS
663 SELECT count(*)
664 FROM ozf_claim_lines
665 WHERE claim_id = cv_claim_id;
666
667 CURSOR csr_get_line_amount(cv_claim_id IN NUMBER) IS
668 SELECT claim_currency_amount
669 FROM ozf_claim_lines
670 WHERE claim_id = cv_claim_id;
671 --End:Bug:2781186
672
673
674 BEGIN
675 --------------------- initialize -----------------------
676 SAVEPOINT Update_Line_Fm_Claim;
677
678 IF OZF_DEBUG_HIGH_ON THEN
679 OZF_Utility_PVT.debug_message(l_full_name||': start');
680 END IF;
681
682 IF FND_API.to_boolean(p_init_msg_list) THEN
683 FND_MSG_PUB.initialize;
684 END IF;
685
686 IF NOT FND_API.compatible_api_call(
687 l_api_version,
688 p_api_version,
689 l_api_name,
690 g_pkg_name
691 ) THEN
692 RAISE FND_API.g_exc_unexpected_error;
693 END IF;
694
695 x_return_status := FND_API.g_ret_sts_success;
696
697 -------------------------- start ----------------------------
698 OPEN csr_claim_old_rec(p_new_claim_rec.claim_id);
699 FETCH csr_claim_old_rec INTO l_old_claim_rec.currency_code
700 , l_old_claim_rec.exchange_rate_type
701 , l_old_claim_rec.exchange_rate_date
702 , l_old_claim_rec.exchange_rate
703 , l_old_claim_rec.cust_account_id
704 , l_old_claim_rec.payment_method
705 , l_old_claim_rec.tax_code
706 , G_CLAIM_SET_OF_BOOKS_ID
707 , l_old_claim_rec.amount; --Bug:2781186
708 CLOSE csr_claim_old_rec;
709
710 -- change in currency_code and exchange_rates
711 IF ( p_new_claim_rec.currency_code <> FND_API.g_miss_char
712 AND
713 p_new_claim_rec.currency_code <> l_old_claim_rec.currency_code )
714 THEN
715 l_upd_line_flag := FND_API.g_true;
716 G_CLAIM_CURRENCY := p_new_claim_rec.currency_code;
717 ELSE
718 G_CLAIM_CURRENCY := l_old_claim_rec.currency_code;
719 END IF;
720
721
722 IF ( p_new_claim_rec.exchange_rate_type <> FND_API.g_miss_char
723 AND
724 NVL(p_new_claim_rec.exchange_rate_type,FND_API.g_miss_char) <> NVL(l_old_claim_rec.exchange_rate_type,FND_API.g_miss_char))
725 THEN
726 l_upd_line_flag := FND_API.g_true;
727 G_CLAIM_EXC_TYPE := p_new_claim_rec.exchange_rate_type;
728 ELSE
729 G_CLAIM_EXC_TYPE := l_old_claim_rec.exchange_rate_type;
730 END IF;
731
732 IF ( p_new_claim_rec.exchange_rate_date <> FND_API.g_miss_date
733 AND
734 NVL(p_new_claim_rec.exchange_rate_date,FND_API.g_miss_date) <> NVL(l_old_claim_rec.exchange_rate_date,FND_API.g_miss_date))
735 THEN
736 l_upd_line_flag := FND_API.g_true;
737 G_CLAIM_EXC_DATE := p_new_claim_rec.exchange_rate_date;
738 ELSE
739 G_CLAIM_EXC_DATE := l_old_claim_rec.exchange_rate_date;
740 END IF;
741
742 IF ( p_new_claim_rec.exchange_rate <> FND_API.g_miss_num
743 AND
744 NVL(p_new_claim_rec.exchange_rate,FND_API.g_miss_num) <> NVL(l_old_claim_rec.exchange_rate,FND_API.g_miss_num))
745 THEN
746 l_upd_line_flag := FND_API.g_true;
747 G_CLAIM_EXC_RATE := p_new_claim_rec.exchange_rate;
748 ELSE
749 G_CLAIM_EXC_RATE := l_old_claim_rec.exchange_rate;
750 END IF;
751
752
753 -- change in cust_account_id
754 IF ( p_new_claim_rec.cust_account_id <> FND_API.g_miss_num
755 AND p_new_claim_rec.cust_account_id <> l_old_claim_rec.cust_account_id) THEN
756 l_upd_line_flag := FND_API.g_true;
757 l_remove_related_cust := FND_API.g_true;
758 END IF;
759
760 -- change in settlement method
761 IF ( p_new_claim_rec.payment_method <> FND_API.g_miss_char
762 AND
763 NVL(p_new_claim_rec.payment_method,FND_API.g_miss_char) <> NVL(l_old_claim_rec.payment_method,FND_API.g_miss_char))
764 THEN
765 l_upd_line_flag := FND_API.g_true;
766 l_remove_tax_code := FND_API.g_true;
767 END IF;
768
769 -- change in tax_code
770 IF ( p_new_claim_rec.tax_code <> FND_API.g_miss_char
771 AND
772 NVL(p_new_claim_rec.tax_code,FND_API.g_miss_char) <> NVL(l_old_claim_rec.tax_code,FND_API.g_miss_char))
773 THEN
774 l_upd_line_flag := FND_API.g_true;
775 END IF;
776
777
778 -- Bug4489415: Pass tax_action to claim line
779 IF (p_new_claim_rec.tax_action is not null AND p_new_claim_rec.tax_action <> FND_API.g_miss_char) THEN
780 l_upd_line_flag := FND_API.g_true;
781 END IF;
782
783 -- ----------------------------------------------------------------------------
784 -- Bug : 2781186
785 -- Changed by : Uday Poluri Date: 03-Jun-2003
786 -- Comments : Check for change in deduction amount
787 -- ----------------------------------------------------------------------------
788 IF (p_new_claim_rec.amount is not null AND p_new_claim_rec.amount <> FND_API.g_miss_num)
789 AND p_new_claim_rec.amount <> l_old_claim_rec.amount THEN
790
791 --Count how many records are present in claim_lines
792 OPEN csr_claim_line_count(p_new_claim_rec.claim_id);
793 FETCH csr_claim_line_count INTO l_claim_line_count;
794 CLOSE csr_claim_line_count;
795
796 -- if l_claim_line_count is 1 means claim user has not touch this line.so it is ok to update.
797 IF l_claim_line_count = 1 THEN
798 --Get line Amount1
799 OPEN csr_get_line_amount(p_new_claim_rec.claim_id);
800 FETCH csr_get_line_amount INTO l_claim_line_amount;
801 CLOSe csr_get_line_amount;
802
803 --Compare new deduction amount with line amount if it is less then only update.
804 --IF p_new_claim_rec.amount < l_claim_line_amount THEN
805 --IF p_new_claim_rec.amount_remaining <> l_claim_line_amount THEN --Changed on 11-Mar-03
806 IF p_new_claim_rec.amount <> l_claim_line_amount THEN --Changed on 12-Mar-03
807 l_upd_line_flag := FND_API.g_true;
808 l_amount_change_flag := FND_API.g_true;
809 END IF;
810
811 END IF;
812 END IF;
813 -- End Bug: 2781186 -----------------------------------------------------------
814
815
816 -------------------- Update Claim Line ----------------------
817 IF l_upd_line_flag = FND_API.g_true THEN
818 OPEN csr_line_rec_upd(p_new_claim_rec.claim_id);
819 LOOP
820 Init_Claim_Line_Rec(
821 x_claim_line_rec => l_claim_line_tbl(l_line_counter)
822 );
823 FETCH csr_line_rec_upd INTO l_claim_line_tbl(l_line_counter).claim_line_id
824 , l_claim_line_tbl(l_line_counter).object_version_number
825 , l_claim_line_tbl(l_line_counter).claim_currency_amount
826 , l_claim_line_tbl(l_line_counter).tax_code
827 , l_claim_line_tbl(l_line_counter).earnings_associated_flag; --Bug:2781186
828 IF OZF_DEBUG_HIGH_ON THEN
829 OZF_Utility_PVT.debug_message('ULFC:STEP 4:'||'line_tbl('||l_line_counter||')'||
830 l_claim_line_tbl(l_line_counter).claim_currency_amount);
831 END IF;
832 l_line_counter := l_line_counter + 1;
833 EXIT WHEN csr_line_rec_upd%NOTFOUND;
834 END LOOP;
835 CLOSE csr_line_rec_upd;
836
837 -- assign new value in claim line rec
838 FOR i IN l_claim_line_tbl.FIRST..l_claim_line_tbl.LAST LOOP
839
840 IF l_claim_line_tbl(i).claim_line_id IS NOT NULL AND
841 l_claim_line_tbl(i).claim_line_id <> FND_API.g_miss_num THEN
842
843 -- Modified for Bug4489415
844 IF l_remove_tax_code = FND_API.g_true THEN
845 l_claim_line_tbl(i).tax_code := NVL(p_new_claim_rec.tax_code,FND_API.g_miss_char);
846 l_claim_line_tbl(i).tax_amount := FND_API.g_miss_num;
847 l_claim_line_tbl(i).acctd_tax_amount := FND_API.g_miss_num;
848 l_claim_line_tbl(i).claim_curr_tax_amount := FND_API.g_miss_num;
849 ELSE
850 IF l_claim_line_tbl(i).tax_code = l_old_claim_rec.tax_code
851 OR l_claim_line_tbl(i).tax_code IS NULL THEN
852 l_claim_line_tbl(i).tax_code := p_new_claim_rec.tax_code;
853 END IF;
854 END IF;
855
856
857
858 l_claim_line_tbl(i).tax_action := p_new_claim_rec.tax_action; -- Bug4489415
859
860 IF l_remove_related_cust = FND_API.g_true THEN
861 l_claim_line_tbl(i).earnings_associated_flag := FND_API.g_false;
862 l_claim_line_tbl(i).relationship_type := FND_API.g_miss_char;
863 l_claim_line_tbl(i).related_cust_account_id := FND_API.g_miss_num;
864 END IF;
865
866 -- ----------------------------------------------------------------------------
867 -- Bug : 2781186
868 -- Comments : Check for amount_change_flag
869 -- ----------------------------------------------------------------------------
870 IF l_amount_change_flag = FND_API.g_true THEN
871 l_claim_line_tbl(i).claim_currency_amount := p_new_claim_rec.amount;
872 IF l_claim_line_tbl(i).earnings_associated_flag = 'T' THEN
873 --Delete associated earnings lines.
874 l_claim_line_tbl(i).earnings_associated_flag := FND_API.g_false;
875 END IF;
876 END IF;
877 -- End Bug: 2781186 -----------------------------------------------------------
878
879
880 l_claim_line_tbl(i).update_from_tbl_flag := FND_API.g_true;
881
882 -- Call the update_claim_line API
883 Update_Claim_Line(
884 p_api_version => 1.0
885 , p_init_msg_list => FND_API.g_false
886 , p_commit => FND_API.g_false
887 , p_validation_level => p_validation_level
888 , x_return_status => l_return_status
889 , x_msg_data => x_msg_data
890 , x_msg_count => x_msg_count
891 , p_claim_line_rec => l_claim_line_tbl(i)
892 , x_object_version => l_object_version
893 );
894 IF l_return_status = fnd_api.g_ret_sts_error THEN
895 RAISE FND_API.g_exc_error;
896 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
897 RAISE FND_API.g_exc_unexpected_error;
898 END IF;
899 END IF;
900 END LOOP;
901 END IF;
902
903 ------------------------- finish -------------------------------
904 IF FND_API.to_boolean(p_commit) THEN
905 COMMIT;
906 END IF;
907
908 FND_MSG_PUB.count_and_get(
909 p_encoded => FND_API.g_false,
910 p_count => x_msg_count,
911 p_data => x_msg_data
912 );
913
914 IF OZF_DEBUG_HIGH_ON THEN
915 OZF_Utility_PVT.debug_message(l_full_name ||': end');
916 END IF;
917
918 EXCEPTION
919 WHEN FND_API.g_exc_error THEN
920 ROLLBACK TO Update_Line_Fm_Claim;
921 x_return_status := FND_API.g_ret_sts_error;
922 FND_MSG_PUB.count_and_get (
923 p_encoded => FND_API.g_false
924 ,p_count => x_msg_count
925 ,p_data => x_msg_data
926 );
927
928 WHEN FND_API.g_exc_unexpected_error THEN
929 ROLLBACK TO Update_Line_Fm_Claim;
930 x_return_status := FND_API.g_ret_sts_unexp_error ;
931 FND_MSG_PUB.count_and_get (
932 p_encoded => FND_API.g_false
933 ,p_count => x_msg_count
934 ,p_data => x_msg_data
935 );
936
937 WHEN OTHERS THEN
938 ROLLBACK TO Update_Line_Fm_Claim;
939 x_return_status := FND_API.g_ret_sts_unexp_error ;
940 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
941 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
942 END IF;
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
949 END Update_Line_Fm_Claim;
950
951
952 ---------------------------------------------------------------------
953 -- PROCEDURE
954 -- Update_Line_Fm_Claim
955 --
956 -- HISTORY
957 -- 03/14/2011 kdass Created. This API will be called for New Claim UI for Claim Line
958 -- update based on change of claim header columns
959 ---------------------------------------------------------------------
960 PROCEDURE Update_Line_Fm_Claim(
961 p_api_version IN NUMBER
962 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
963 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
964 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
965
966 ,x_return_status OUT NOCOPY VARCHAR2
967 ,x_msg_data OUT NOCOPY VARCHAR2
968 ,x_msg_count OUT NOCOPY NUMBER
969
970 ,p_claim_id IN NUMBER
971 ,p_tax_action IN VARCHAR2
972 ,p_remove_tax_code IN VARCHAR2 := FND_API.G_FALSE
973 ,p_remove_related_cust IN VARCHAR2 := FND_API.G_FALSE
974 ,p_amount_change_flag IN VARCHAR2 := FND_API.G_FALSE
975 )
976 IS
977 l_api_version CONSTANT NUMBER := 1.0;
978 l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Fm_Claim';
979 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
980
981 l_remove_tax_code VARCHAR2(1) := p_remove_tax_code;
982 l_remove_related_cust VARCHAR2(1) := p_remove_related_cust;
983 l_amount_change_flag VARCHAR2(1) := p_amount_change_flag;
984
985 l_claim_line_tbl claim_line_tbl_type;
986 l_line_counter NUMBER := 1;
987 l_object_version NUMBER;
988 l_tax_code VARCHAR2(50);
989 l_amount NUMBER;
990 l_payment_method VARCHAR2(30);
991 l_vendor_id NUMBER;
992 l_vendor_site_id NUMBER;
993
994 CURSOR csr_claim_rec IS
995 SELECT currency_code
996 , exchange_rate_type
997 , exchange_rate_date
998 , exchange_rate
999 , tax_code
1000 , amount
1001 , payment_method
1002 , vendor_id
1003 , vendor_site_id
1004 FROM ozf_claims_all
1005 WHERE claim_id = p_claim_id;
1006
1007 CURSOR csr_line_rec_upd IS
1008 SELECT claim_line_id
1009 , object_version_number
1010 , claim_currency_amount
1011 , tax_code
1012 , earnings_associated_flag
1013 FROM ozf_claim_lines_all
1014 WHERE claim_id = p_claim_id;
1015
1016 BEGIN
1017 --------------------- initialize -----------------------
1018 SAVEPOINT Update_Line_Fm_Claim;
1019
1020 IF OZF_DEBUG_HIGH_ON THEN
1021 OZF_Utility_PVT.debug_message(l_full_name||': start');
1022 END IF;
1023
1024 IF FND_API.to_boolean(p_init_msg_list) THEN
1025 FND_MSG_PUB.initialize;
1026 END IF;
1027
1028 IF NOT FND_API.compatible_api_call(
1029 l_api_version,
1030 p_api_version,
1031 l_api_name,
1032 g_pkg_name
1033 ) THEN
1034 RAISE FND_API.g_exc_unexpected_error;
1035 END IF;
1036
1037 x_return_status := FND_API.g_ret_sts_success;
1038
1039 -------------------------- start ----------------------------
1040 OPEN csr_claim_rec;
1041 FETCH csr_claim_rec INTO G_CLAIM_CURRENCY
1042 ,G_CLAIM_EXC_TYPE
1043 ,G_CLAIM_EXC_DATE
1044 ,G_CLAIM_EXC_RATE
1045 ,l_tax_code
1046 ,l_amount
1047 ,l_payment_method
1048 , l_vendor_id
1049 , l_vendor_site_id;
1050 CLOSE csr_claim_rec;
1051
1052 -- Fix for Bug 12373880 : Throwing error if vendor information is missing
1053 IF (p_tax_action IS NOT NULL AND p_tax_action <> FND_API.G_MISS_CHAR) THEN
1054 IF l_payment_method IN ( 'EFT','WIRE','CHECK','AP_DEBIT', 'AP_DEFAULT') AND
1055 l_vendor_id IS NULL AND l_vendor_site_id IS NULL THEN
1056 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1057 FND_MESSAGE.set_name('OZF', 'OZF_VENDOR_INFO_MISSING');
1058 FND_MSG_PUB.add;
1059 END IF;
1060 RAISE FND_API.g_exc_error;
1061 END IF;
1062 END IF;
1063
1064 -------------------- Update Claim Line ----------------------
1065 OPEN csr_line_rec_upd;
1066 LOOP
1067
1068 FETCH csr_line_rec_upd INTO l_claim_line_tbl(l_line_counter).claim_line_id
1069 , l_claim_line_tbl(l_line_counter).object_version_number
1070 , l_claim_line_tbl(l_line_counter).claim_currency_amount
1071 , l_claim_line_tbl(l_line_counter).tax_code
1072 , l_claim_line_tbl(l_line_counter).earnings_associated_flag;
1073 EXIT WHEN csr_line_rec_upd%NOTFOUND;
1074
1075 IF OZF_DEBUG_HIGH_ON THEN
1076 OZF_Utility_PVT.debug_message('line_tbl('||l_line_counter||')'||
1077 l_claim_line_tbl(l_line_counter).claim_currency_amount);
1078 END IF;
1079
1080 l_line_counter := l_line_counter + 1;
1081
1082 END LOOP;
1083 CLOSE csr_line_rec_upd;
1084
1085 FOR i IN l_claim_line_tbl.FIRST..l_claim_line_tbl.LAST LOOP
1086
1087 IF l_claim_line_tbl(i).claim_line_id IS NOT NULL AND
1088 l_claim_line_tbl(i).claim_line_id <> FND_API.g_miss_num THEN
1089
1090 IF l_remove_tax_code = FND_API.g_true THEN
1091 l_claim_line_tbl(i).tax_code := NVL(l_claim_line_tbl(i).tax_code, NVL(l_tax_code,FND_API.g_miss_char));
1092 l_claim_line_tbl(i).tax_amount := FND_API.g_miss_num;
1093 l_claim_line_tbl(i).acctd_tax_amount := FND_API.g_miss_num;
1094 l_claim_line_tbl(i).claim_curr_tax_amount := FND_API.g_miss_num;
1095 ELSE
1096 IF (l_tax_code <> NULL AND l_tax_code <> FND_API.g_miss_char
1097 AND l_claim_line_tbl(i).tax_code <> l_tax_code)
1098 OR l_claim_line_tbl(i).tax_code IS NULL THEN
1099 l_claim_line_tbl(i).tax_code := l_tax_code;
1100 END IF;
1101 END IF;
1102
1103 l_claim_line_tbl(i).tax_action := p_tax_action;
1104
1105 IF l_remove_related_cust = FND_API.g_true THEN
1106 l_claim_line_tbl(i).earnings_associated_flag := FND_API.g_false;
1107 l_claim_line_tbl(i).relationship_type := FND_API.g_miss_char;
1108 l_claim_line_tbl(i).related_cust_account_id := FND_API.g_miss_num;
1109 END IF;
1110
1111 IF l_amount_change_flag = FND_API.g_true THEN
1112 l_claim_line_tbl(i).claim_currency_amount := l_amount;
1113 IF l_claim_line_tbl(i).earnings_associated_flag = 'T' THEN
1114 --Delete associated earnings lines.
1115 l_claim_line_tbl(i).earnings_associated_flag := FND_API.g_false;
1116 END IF;
1117 END IF;
1118
1119 l_claim_line_tbl(i).update_from_tbl_flag := FND_API.g_true;
1120
1121 --Call the update_claim_line API
1122 Update_Claim_Line(
1123 p_api_version => 1.0
1124 , p_init_msg_list => FND_API.g_false
1125 , p_commit => FND_API.g_false
1126 , p_validation_level => p_validation_level
1127 , x_return_status => x_return_status
1128 , x_msg_data => x_msg_data
1129 , x_msg_count => x_msg_count
1130 , p_claim_line_rec => l_claim_line_tbl(i)
1131 , x_object_version => l_object_version
1132 );
1133 IF x_return_status = fnd_api.g_ret_sts_error THEN
1134 RAISE FND_API.g_exc_error;
1135 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1136 RAISE FND_API.g_exc_unexpected_error;
1137 END IF;
1138 END IF;
1139
1140 END LOOP;
1141
1142 ------------------------- finish -------------------------------
1143 IF FND_API.to_boolean(p_commit) THEN
1144 COMMIT;
1145 END IF;
1146
1147 FND_MSG_PUB.count_and_get(
1148 p_encoded => FND_API.g_false,
1149 p_count => x_msg_count,
1150 p_data => x_msg_data
1151 );
1152
1153 IF OZF_DEBUG_HIGH_ON THEN
1154 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1155 END IF;
1156
1157 EXCEPTION
1158 WHEN FND_API.g_exc_error THEN
1159 ROLLBACK TO Update_Line_Fm_Claim;
1160 x_return_status := FND_API.g_ret_sts_error;
1161 FND_MSG_PUB.count_and_get (
1162 p_encoded => FND_API.g_false
1163 ,p_count => x_msg_count
1164 ,p_data => x_msg_data
1165 );
1166
1167 WHEN FND_API.g_exc_unexpected_error THEN
1168 ROLLBACK TO Update_Line_Fm_Claim;
1169 x_return_status := FND_API.g_ret_sts_unexp_error ;
1170 FND_MSG_PUB.count_and_get (
1171 p_encoded => FND_API.g_false
1172 ,p_count => x_msg_count
1173 ,p_data => x_msg_data
1174 );
1175
1176 WHEN OTHERS THEN
1177 ROLLBACK TO Update_Line_Fm_Claim;
1178 x_return_status := FND_API.g_ret_sts_unexp_error ;
1179 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1180 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1181 END IF;
1182 FND_MSG_PUB.count_and_get(
1183 p_encoded => FND_API.g_false
1184 ,p_count => x_msg_count
1185 ,p_data => x_msg_data
1186 );
1187
1188 END Update_Line_Fm_Claim;
1189
1190 ---------------------------------------------------------------------
1191 -- PROCEDURE
1192 -- Create_Claim_Line_Tbl
1193 --
1194 -- HISTORY
1195 -- 02/02/2001 mchang Create.
1196 -- 03/28/2001 mchang add passing_in parameter: p_utiz_obj_ver_tbl
1197 -- 04/30/2001 mchang remove passing_in parameter: p_utiz_obj_ver_tbl
1198 -- 23/01/2002 slkrishn modified amount checking conditions
1199 -- 07/22/2002 yizhang add p_mode for security check
1200 -- 21/02/2011 kpatro made record type claim_line_tbl_type as IN OUT
1201 ---------------------------------------------------------------------
1202 PROCEDURE Create_Claim_Line_Tbl(
1203 p_api_version IN NUMBER
1204 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1205 ,p_commit IN VARCHAR2 := FND_API.g_false
1206 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1207
1208 ,x_return_status OUT NOCOPY VARCHAR2
1209 ,x_msg_data OUT NOCOPY VARCHAR2
1210 ,x_msg_count OUT NOCOPY NUMBER
1211
1212 ,p_claim_line_tbl IN OUT NOCOPY claim_line_tbl_type
1213 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
1214
1215 ,x_error_index OUT NOCOPY NUMBER
1216 ) IS
1217 l_api_version CONSTANT NUMBER := 1.0;
1218 l_api_name CONSTANT VARCHAR2(30) := 'Create_Claim_Line_Tbl';
1219 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1220
1221 l_return_status VARCHAR2(1);
1222 l_msg_count NUMBER;
1223 l_msg_data VARCHAR2(4000);
1224
1225 l_claim_line_rec claim_line_rec_type;
1226 l_claim_line_tbl claim_line_tbl_type;
1227 l_claim_line_id NUMBER;
1228 l_utiz_obj_ver NUMBER;
1229 l_create_total_amt NUMBER := 0;
1230 l_exist_total_amt NUMBER;
1231 l_claim_amount NUMBER;
1232 l_claim_class VARCHAR2(30);
1233 l_claim_id NUMBER;
1234 l_currency_code VARCHAR2(15);
1235 l_access VARCHAR2(1) := 'N';
1236
1237 l_inv_quantity NUMBER := 0;
1238 l_tot_quantity NUMBER := 0;
1239 l_clmline_quantity NUMBER := 0;
1240
1241 -- Cursor to get claim amount
1242 CURSOR c_claim_amount(cv_claim_id IN NUMBER) IS
1243 SELECT amount_remaining, claim_class
1244 FROM ozf_claims
1245 WHERE claim_id = cv_claim_id;
1246
1247 -- Cursor to get claim amount
1248 CURSOR c_exist_total_line_amt(cv_claim_id IN NUMBER) IS
1249 SELECT NVL(SUM(claim_currency_amount), 0)
1250 FROM ozf_claim_lines
1251 WHERE claim_id = cv_claim_id;
1252
1253 CURSOR c_claim(cv_claim_id IN NUMBER) IS
1254 SELECT currency_code
1255 FROM ozf_claims
1256 WHERE claim_id = cv_claim_id;
1257
1258 -- Bug#9957893 (+)
1259 CURSOR c_old_line_qty(cv_src_obj_line_id IN NUMBER) IS
1260 SELECT quantity_invoiced
1261 FROM ra_customer_trx_lines_all
1262 WHERE customer_trx_line_id = cv_src_obj_line_id;
1263
1264 CURSOR c_tot_qty(cv_claim_id IN NUMBER, cv_source_object_line_id IN NUMBER) IS
1265 select NVL(sum(quantity),0) from ozf_claim_lines_all
1266 WHERE claim_id = cv_claim_id
1267 and source_object_line_id = cv_source_object_line_id;
1268
1269 -- Bug#9957893 (-)
1270
1271 BEGIN
1272 --------------------- initialize -----------------------
1273 SAVEPOINT Create_Claim_Line_Tbl;
1274
1275 IF OZF_DEBUG_HIGH_ON THEN
1276 OZF_Utility_PVT.debug_message(l_full_name||': start');
1277 END IF;
1278
1279 IF FND_API.to_boolean(p_init_msg_list) THEN
1280 FND_MSG_PUB.initialize;
1281 END IF;
1282
1283 IF NOT FND_API.compatible_api_call(
1284 l_api_version,
1285 p_api_version,
1286 l_api_name,
1287 g_pkg_name
1288 )
1289 THEN
1290 RAISE FND_API.g_exc_unexpected_error;
1291 END IF;
1292
1293 x_return_status := FND_API.g_ret_sts_success;
1294
1295 l_claim_line_tbl := p_claim_line_tbl;
1296
1297 ---------------------- check claim access ------------------------
1298 IF p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode AND p_claim_line_tbl.count > 0 THEN
1299 FOR j IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
1300 IF p_claim_line_tbl.EXISTS(j) THEN
1301 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
1302 P_Api_Version_Number => 1.0
1303 , P_Init_Msg_List => FND_API.G_FALSE
1304 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1305 , P_Commit => FND_API.G_FALSE
1306 , P_object_id => p_claim_line_tbl(j).claim_id
1307 , P_object_type => G_CLAIM_OBJECT_TYPE
1308 , P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
1309 , X_Return_Status => l_return_status
1310 , X_Msg_Count => l_msg_count
1311 , X_Msg_Data => l_msg_data
1312 , X_access => l_access);
1313
1314 IF l_access = 'N' THEN
1315 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1316 FND_MESSAGE.set_name('OZF','OZF_CLAIM_NO_ACCESS');
1317 FND_MSG_PUB.add;
1318 END IF;
1319 RAISE FND_API.G_EXC_ERROR;
1320 END IF;
1321 EXIT;
1322 END IF;
1323 END LOOP;
1324 END IF;
1325
1326 ------------ Default Claim Line by Settlement Method ----------------
1327 OZF_CLAIM_SETTLEMENT_VAL_PVT.Default_Claim_Line_Tbl(
1328 p_api_version => l_api_version
1329 ,p_init_msg_list => FND_API.g_false
1330 ,p_validation_level => p_validation_level
1331 ,x_return_status => l_return_status
1332 ,x_msg_data => x_msg_data
1333 ,x_msg_count => x_msg_count
1334 ,p_x_claim_line_tbl => l_claim_line_tbl
1335 );
1336 IF l_return_status = fnd_api.g_ret_sts_error THEN
1337 RAISE FND_API.g_exc_error;
1338 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1339 RAISE FND_API.g_exc_unexpected_error;
1340 END IF;
1341
1342 --------------------- Amount Checking -----------------------
1343 IF l_claim_line_tbl.count > 0 THEN
1344 OPEN c_claim(l_claim_line_tbl(1).claim_id);
1345 FETCH c_claim INTO l_currency_code;
1346 CLOSE c_claim;
1347
1348 FOR j IN l_claim_line_tbl.FIRST..l_claim_line_tbl.LAST LOOP
1349 IF l_claim_line_tbl.EXISTS(j) THEN
1350 -- calculate claim currency amount from qty and rate if they exist
1351 -- added by slkrishn
1352
1353 -- Bug#9957893 (+)
1354 -- Fix for Bug 10383102: Added a check for source_object_line_id
1355 IF l_claim_line_tbl(j).source_object_class = 'INVOICE' AND
1356 l_claim_line_tbl(j).source_object_line_id IS NOT NULL AND
1357 l_claim_line_tbl(j).source_object_line_id <> FND_API.G_MISS_NUM THEN
1358
1359 l_tot_quantity := l_claim_line_tbl(j).quantity;
1360
1361 OPEN c_tot_qty(l_claim_line_tbl(j).claim_id, l_claim_line_tbl(j).source_object_line_id);
1362 FETCH c_tot_qty INTO l_clmline_quantity;
1363 CLOSE c_tot_qty;
1364
1365 l_tot_quantity := l_clmline_quantity + l_tot_quantity;
1366
1367 FOR m IN j+1..l_claim_line_tbl.LAST LOOP
1368 IF l_claim_line_tbl(j).source_object_line_id = l_claim_line_tbl(m).source_object_line_id THEN
1369 l_tot_quantity := l_tot_quantity + l_claim_line_tbl(m).quantity;
1370 END IF;
1371 END LOOP;
1372
1373 OPEN c_old_line_qty(l_claim_line_tbl(j).source_object_line_id);
1374 FETCH c_old_line_qty INTO l_inv_quantity;
1375 CLOSE c_old_line_qty;
1376
1377 IF OZF_DEBUG_HIGH_ON THEN
1378 OZF_Utility_PVT.debug_message('Old Quantity from ra_customer_trx_lines_all is ' || l_inv_quantity);
1379 OZF_Utility_PVT.debug_message('Current Claim Line Quantity is ' || l_claim_line_tbl(j).quantity);
1380 OZF_Utility_PVT.debug_message('Total Claim Line Quantity for source_object_line_id '|| l_claim_line_tbl(j).source_object_line_id || 'is ' || l_tot_quantity);
1381 END IF;
1382
1383 IF l_inv_quantity < l_tot_quantity THEN
1384 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1385 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_INV_QTY');
1386 FND_MSG_PUB.add;
1387 END IF;
1388 x_error_index := j;
1389 RAISE FND_API.g_exc_error;
1390 END IF;
1391 END IF;
1392 -- Bug#9957893 (-)
1393
1394
1395 IF l_claim_line_tbl(j).quantity IS NOT NULL AND
1396 l_claim_line_tbl(j).rate IS NOT NULL
1397 THEN
1398 l_claim_line_tbl(j).claim_currency_amount :=
1399 l_claim_line_tbl(j).quantity * l_claim_line_tbl(j).rate;
1400 END IF;
1401
1402 -- raise error if claim currency amount is null
1403 -- added by slkrishn
1404 IF l_claim_line_tbl(j).claim_currency_amount IS NULL THEN
1405 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1406 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_NULL');
1407 FND_MSG_PUB.add;
1408 END IF;
1409 x_error_index := j;
1410 RAISE FND_API.g_exc_error;
1411 ELSE
1412 IF OZF_DEBUG_HIGH_ON THEN
1413 OZF_Utility_PVT.debug_message('Before CurrRound :: claim line currency_currency_amount = '||l_claim_line_tbl(j).claim_currency_amount);
1414 OZF_Utility_PVT.debug_message('Before CurrRound :: claim currency code = '||l_currency_code);
1415 END IF;
1416 l_claim_line_tbl(j).claim_currency_amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_tbl(j).claim_currency_amount, l_currency_code);
1417 END IF;
1418
1419 l_create_total_amt := l_create_total_amt + l_claim_line_tbl(j).claim_currency_amount;
1420 l_claim_id := p_claim_line_tbl(j).claim_id;
1421 END IF;
1422 END LOOP;
1423
1424 OPEN c_claim_amount(l_claim_id);
1425 FETCH c_claim_amount INTO l_claim_amount, l_claim_class;
1426 CLOSE c_claim_amount;
1427
1428 OPEN c_exist_total_line_amt(l_claim_id);
1429 FETCH c_exist_total_line_amt INTO l_exist_total_amt;
1430 CLOSE c_exist_total_line_amt;
1431
1432
1433 IF OZF_DEBUG_HIGH_ON THEN
1434 OZF_Utility_PVT.debug_message('exist_total_amt:' || l_exist_total_amt );
1435 OZF_Utility_PVT.debug_message('create_total_amt:' || l_create_total_amt );
1436 OZF_Utility_PVT.debug_message('claim_amount:' || l_claim_amount );
1437 END IF;
1438 --Check for the sum of line amount sign. It should be same as that claims remaining amount.
1439 --Skip the check in case of Subsequent Receipt Application.
1440 IF l_claim_class <> 'GROUP' THEN
1441 IF sign(l_exist_total_amt + l_create_total_amt) <> sign(l_claim_amount)
1442 THEN
1443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1444 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_SIGN_ERR');
1445 FND_MSG_PUB.add;
1446 END IF;
1447 RAISE FND_API.g_exc_error;
1448 END IF;
1449 END IF;
1450
1451
1452 IF ABS((l_exist_total_amt + l_create_total_amt)) > ABS(l_claim_amount) AND
1453 l_claim_class <> 'GROUP' THEN
1454 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1455 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
1456 FND_MSG_PUB.add;
1457 END IF;
1458 RAISE FND_API.g_exc_error;
1459 END IF;
1460 END IF;
1461
1462 --------------------- Create Claim Line Table -----------------------
1463 IF p_claim_line_tbl.count > 0 THEN
1464 FOR i IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
1465 IF p_claim_line_tbl.EXISTS(i) THEN
1466
1467 l_claim_line_rec := l_claim_line_tbl(i);
1468
1469 --If all values in line overview is null, line record will not be created.
1470 IF l_claim_line_rec.activity_type IS NOT NULL OR
1471 l_claim_line_rec.source_object_class IS NOT NULL OR
1472 l_claim_line_rec.source_object_id IS NOT NULL OR
1473 l_claim_line_rec.source_object_line_id IS NOT NULL OR
1474 l_claim_line_rec.quantity_uom IS NOT NULL OR
1475 l_claim_line_rec.claim_currency_amount IS NOT NULL OR
1476 l_claim_line_rec.quantity IS NOT NULL OR
1477 l_claim_line_rec.rate IS NOT NULL OR
1478 l_claim_line_rec.tax_code IS NOT NULL OR
1479 l_claim_line_rec.item_description IS NOT NULL THEN
1480 l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
1481
1482 -- Call the create claim line API
1483 Create_Claim_Line(
1484 p_api_version => 1.0
1485 , p_init_msg_list => FND_API.g_false
1486 , p_commit => FND_API.g_false
1487 , p_validation_level => p_validation_level
1488 , x_return_status => l_return_status
1489 , x_msg_data => x_msg_data
1490 , x_msg_count => x_msg_count
1491 , p_claim_line_rec => l_claim_line_rec
1492 , p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
1493 , x_claim_line_id => l_claim_line_id
1494 );
1495 IF l_return_status = fnd_api.g_ret_sts_error THEN
1496 x_error_index := i;
1497 RAISE FND_API.g_exc_error;
1498 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1499 x_error_index := i;
1500 RAISE FND_API.g_exc_unexpected_error;
1501 END IF;
1502 -- Fix for Bug 11793070
1503 p_claim_line_tbl(i).claim_line_id := l_claim_line_id;
1504 END IF;
1505 END IF;
1506 END LOOP;
1507 END IF;
1508 ------------------------- finish -------------------------------
1509 IF FND_API.to_boolean(p_commit) THEN
1510 COMMIT;
1511 END IF;
1512
1513 FND_MSG_PUB.count_and_get(
1514 p_encoded => FND_API.g_false,
1515 p_count => x_msg_count,
1516 p_data => x_msg_data
1517 );
1518
1519 IF OZF_DEBUG_HIGH_ON THEN
1520 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1521 END IF;
1522
1523 EXCEPTION
1524 WHEN FND_API.g_exc_error THEN
1525 ROLLBACK TO Create_Claim_Line_Tbl;
1526 x_return_status := FND_API.g_ret_sts_error;
1527 FND_MSG_PUB.count_and_get (
1528 p_encoded => FND_API.g_false
1529 ,p_count => x_msg_count
1530 ,p_data => x_msg_data
1531 );
1532
1533 WHEN FND_API.g_exc_unexpected_error THEN
1534 ROLLBACK TO Create_Claim_Line_Tbl;
1535 x_return_status := FND_API.g_ret_sts_unexp_error ;
1536 FND_MSG_PUB.count_and_get (
1537 p_encoded => FND_API.g_false
1538 ,p_count => x_msg_count
1539 ,p_data => x_msg_data
1540 );
1541
1542 WHEN OTHERS THEN
1543 ROLLBACK TO Create_Claim_Line_Tbl;
1544 x_return_status := FND_API.g_ret_sts_unexp_error ;
1545 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1546 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1547 END IF;
1548 FND_MSG_PUB.count_and_get(
1549 p_encoded => FND_API.g_false
1550 ,p_count => x_msg_count
1551 ,p_data => x_msg_data
1552 );
1553
1554 END Create_Claim_Line_Tbl;
1555
1556
1557 ---------------------------------------------------------------------
1558 -- PROCEDURE
1559 -- Create_Claim_Line
1560 --
1561 -- HISTORY
1562 -- 07/11/2000 mchang Create.
1563 -- 07/31/2000 mchang Add amount checking: Claim amount >= Existing Lines Total + New Line amount.
1564 -- If lines does not exist, Claim amount >= New Line amount.
1565 -- 02/02/2001 mchang Remove passing in parameters - p_claim_amount and p_claim_version.
1566 -- 03/20/2001 mchang Remove claim amount checking; Add currency conversion
1567 -- 03/28/2001 mchang add passing_in parameter: p_utiz_obj_ver
1568 -- 04/30/2001 mchang remove passing_in parameter: p_utiz_obj_ver
1569 -- conver acctd_amount and amount.
1570 -- 08/06-2001 mchang Updated: convert line amount from acctd_amount
1571 -- 23/01/2002 slkrishn modified amount checking conditions
1572 -- 07/22/2002 yizhang add p_mode for security check
1573 --
1574 -----------------------------------------------------------------------------------------
1575 PROCEDURE Create_Claim_Line(
1576 p_api_version IN NUMBER
1577 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1578 ,p_commit IN VARCHAR2 := FND_API.g_false
1579 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
1580
1581 ,x_return_status OUT NOCOPY VARCHAR2
1582 ,x_msg_count OUT NOCOPY NUMBER
1583 ,x_msg_data OUT NOCOPY VARCHAR2
1584
1585 ,p_claim_line_rec IN claim_line_rec_type
1586 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
1587 ,x_claim_line_id OUT NOCOPY NUMBER
1588 )
1589 IS
1590 -- Cursor to get the sequence for claim_line_id
1591 CURSOR c_claim_line_seq IS
1592 SELECT ozf_claim_lines_all_s.NEXTVAL
1593 FROM DUAL;
1594
1595 -- Cursor to validate the uniqueness of the claim_line_id
1596 CURSOR c_claim_line_count(cv_claim_line_id IN NUMBER) IS
1597 SELECT COUNT(claim_line_id)
1598 FROM ozf_claim_lines
1599 WHERE claim_line_id = cv_claim_line_id;
1600
1601 -- Cursor to check the maximum of line_number in order to set line_number value
1602 CURSOR c_line_number(cv_claim_id IN NUMBER) IS
1603 SELECT MAX(line_number)
1604 FROM ozf_claim_lines
1605 WHERE claim_id = cv_claim_id;
1606
1607 -- Cursor to get set_of_books_id, and tax_code from Claim
1608 CURSOR csr_default_fm_claim(cv_claim_id IN NUMBER) IS
1609 SELECT set_of_books_id, tax_code, org_id
1610 FROM ozf_claims
1611 WHERE CLAIM_ID = cv_claim_id;
1612
1613 -- Cursor to get claim amount
1614 CURSOR c_claim_amount(cv_claim_id IN NUMBER) IS
1615 SELECT amount_remaining
1616 , claim_class
1617 FROM ozf_claims
1618 WHERE claim_id = cv_claim_id;
1619
1620 -- Cursor to sum of claim line amount
1621 CURSOR c_line_sum_amt(cv_claim_id IN NUMBER) IS
1622 SELECT SUM(claim_currency_amount)
1623 FROM ozf_claim_lines
1624 WHERE claim_id = cv_claim_id;
1625
1626 -- Cursor to get default exchange_rate data from claim
1627 CURSOR c_claim_default_exc(cv_claim_id IN NUMBER) IS
1628 SELECT currency_code
1629 , exchange_rate_type
1630 , exchange_rate_date
1631 , exchange_rate
1632 FROM ozf_claims
1633 WHERE claim_id = cv_claim_id;
1634
1635 -- fix for bug 5042046
1636 -- Cursor to get functional currency
1637 CURSOR csr_function_currency IS
1638 SELECT gs.currency_code
1639 FROM gl_sets_of_books gs
1640 , ozf_sys_parameters org
1641 WHERE org.set_of_books_id = gs.set_of_books_id
1642 AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
1643
1644
1645 l_api_version CONSTANT NUMBER := 1.0;
1646 l_api_name CONSTANT VARCHAR2(30) := 'Create_Claim_Line';
1647 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1648
1649 l_created_by NUMBER;
1650 l_updated_by NUMBER;
1651 l_last_update_login NUMBER;
1652 l_org_id NUMBER;
1653 l_valid_flag VARCHAR2(1);
1654
1655 l_return_status VARCHAR2(1);
1656
1657 l_claim_line_rec claim_line_rec_type := p_claim_line_rec;
1658 l_x_claim_line_rec claim_line_rec_type;
1659 l_object_version_number NUMBER := 1;
1660 l_line_number NUMBER := 1;
1661
1662 l_claim_line_count NUMBER;
1663 l_claim_amount NUMBER;
1664 l_claim_class VARCHAR2(30);
1665 l_line_sum_amt NUMBER;
1666 l_claim_currency_amount NUMBER;
1667
1668 l_set_of_books_id NUMBER;
1669 l_tax_code VARCHAR2(50);
1670
1671 l_claim_currency VARCHAR2(15);
1672 l_claim_exc_rate NUMBER;
1673 l_claim_exc_type VARCHAR2(30);
1674 l_claim_exc_date DATE;
1675 l_functional_currency VARCHAR2(15);
1676
1677 l_access VARCHAR2(1) := 'N';
1678
1679 /*
1680 --Bug# 7319828 fixed by ateotia(+)
1681 l_batch_type VARCHAR2(30);
1682 l_convert_acctd_amount VARCHAR2(1) := 'T';
1683
1684 CURSOR csr_batch_type (p_claim_id IN NUMBER) IS
1685 SELECT
1686 orb.batch_type
1687 FROM
1688 ozf_resale_batches_all orb,
1689 ozf_claims_all oc
1690 WHERE
1691 oc.batch_id = orb.resale_batch_id
1692 AND oc.claim_id = p_claim_id;
1693 --Bug# 7319828 fixed by ateotia(-)
1694 */
1695 BEGIN
1696 --------------------- initialize -----------------------
1697 SAVEPOINT Create_Claim_Line;
1698
1699 IF OZF_DEBUG_HIGH_ON THEN
1700 OZF_Utility_PVT.debug_message(l_full_name||': start');
1701 END IF;
1702
1703 IF FND_API.to_boolean(p_init_msg_list) THEN
1704 FND_MSG_PUB.initialize;
1705 END IF;
1706
1707 IF NOT FND_API.compatible_api_call(
1708 l_api_version,
1709 p_api_version,
1710 l_api_name,
1711 g_pkg_name
1712 ) THEN
1713 RAISE FND_API.g_exc_unexpected_error;
1714 END IF;
1715
1716 x_return_status := FND_API.g_ret_sts_success;
1717
1718 l_claim_line_rec := p_claim_line_rec;
1719
1720 ----------------- check claim access -------------------
1721 IF p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode THEN
1722 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
1723 P_Api_Version_Number => 1.0
1724 , P_Init_Msg_List => FND_API.G_FALSE
1725 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1726 , P_Commit => FND_API.G_FALSE
1727 , P_object_id => l_claim_line_rec.claim_id
1728 , P_object_type => G_CLAIM_OBJECT_TYPE
1729 , P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
1730 , X_Return_Status => l_return_status
1731 , X_Msg_Count => x_msg_count
1732 , X_Msg_Data => x_msg_data
1733 , X_access => l_access);
1734
1735 IF l_access = 'N' THEN
1736 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1737 FND_MESSAGE.set_name('OZF','OZF_CLAIM_NO_ACCESS');
1738 FND_MSG_PUB.add;
1739 END IF;
1740 RAISE FND_API.G_EXC_ERROR;
1741 END IF;
1742 END IF;
1743
1744 /*-------------------------------------------------------*
1745 | set default value for claim line |
1746 *-------------------------------------------------------*/
1747 l_created_by := NVL(FND_GLOBAL.user_id,-1);
1748 l_updated_by := NVL(FND_GLOBAL.user_id,-1);
1749 l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
1750 l_valid_flag := NVL(l_claim_line_rec.valid_flag, FND_API.g_false);
1751
1752 -- get org_id, set_of_books_id and tax_code from claim
1753 OPEN csr_default_fm_claim(l_claim_line_rec.claim_id);
1754 FETCH csr_default_fm_claim INTO l_set_of_books_id
1755 , l_tax_code
1756 , l_org_id;
1757 CLOSE csr_default_fm_claim;
1758
1759 -- generate the value of line_number
1760 IF l_claim_line_rec.line_number is NULL THEN
1761 -- get existing max line_number
1762 OPEN c_line_number(l_claim_line_rec.claim_id);
1763 FETCH c_line_number INTO l_line_number;
1764 CLOSE c_line_number;
1765
1766 IF (l_line_number IS NOT NULL) THEN
1767 l_line_number := l_line_number + 1;
1768 ELSE
1769 l_line_number := 1;
1770 END IF;
1771
1772 l_claim_line_rec.line_number := l_line_number;
1773 END IF;
1774
1775 -- Default UOM for product.
1776 IF l_claim_line_rec.item_type = 'PRODUCT'
1777 AND (l_claim_line_rec.item_id is not null
1778 AND l_claim_line_rec.item_id <> FND_API.G_MISS_NUM )
1779 AND (l_claim_line_rec.quantity_uom is null
1780 OR l_claim_line_rec.quantity_uom = FND_API.G_MISS_CHAR)
1781 THEN
1782 --Bugfix 5182181
1783 l_claim_line_rec.quantity_uom := Get_Default_Product_UOM
1784 ( p_product_id => l_claim_line_rec.item_id
1785 , p_org_id => FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
1786 );
1787 END IF;
1788
1789
1790
1791 -- default set_of_books_id
1792 IF l_claim_line_rec.set_of_books_id IS NULL THEN
1793 l_claim_line_rec.set_of_books_id := l_set_of_books_id;
1794 END IF;
1795
1796
1797 -- default tax_code
1798 IF l_claim_line_rec.tax_code IS NULL THEN
1799 l_claim_line_rec.tax_code := l_tax_code;
1800 END IF;
1801
1802 -- default claim line by settlement method
1803 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
1804 OZF_CLAIM_SETTLEMENT_VAL_PVT.Default_Claim_Line(
1805 p_api_version => l_api_version
1806 ,p_init_msg_list => FND_API.g_false
1807 ,p_validation_level => FND_API.g_valid_level_full
1808 ,x_return_status => l_return_status
1809 ,x_msg_data => x_msg_data
1810 ,x_msg_count => x_msg_count
1811 ,p_x_claim_line_rec => l_claim_line_rec
1812 );
1813 IF l_return_status = FND_API.g_ret_sts_error THEN
1814 RAISE FND_API.g_exc_error;
1815 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1816 RAISE FND_API.g_exc_unexpected_error;
1817 END IF;
1818 END IF;
1819
1820 -- get functional currency
1821 OPEN csr_function_currency;
1822 FETCH csr_function_currency INTO l_functional_currency;
1823 CLOSE csr_function_currency;
1824
1825 ------------------ checking quantity * rate -------------------
1826 -- added by slkrishn since the condition below is moved to tbl api
1827 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
1828 IF l_claim_line_rec.quantity IS NOT NULL AND
1829 l_claim_line_rec.rate IS NOT NULL THEN
1830 l_claim_line_rec.claim_currency_amount := l_claim_line_rec.quantity * l_claim_line_rec.rate;
1831 END IF;
1832 END IF;
1833
1834 ------------------ convert currency --------------------
1835 IF l_claim_line_rec.claim_currency_amount IS NOT NULL THEN
1836 OPEN c_claim_default_exc(l_claim_line_rec.claim_id);
1837 FETCH c_claim_default_exc INTO l_claim_currency
1838 , l_claim_exc_type
1839 , l_claim_exc_date
1840 , l_claim_exc_rate;
1841 CLOSE c_claim_default_exc;
1842
1843 /*
1844 --Bug# 7319828 fixed by ateotia(+)
1845 IF l_claim_line_rec.acctd_amount IS NOT NULL THEN
1846 OPEN csr_batch_type (l_claim_line_rec.claim_id);
1847 FETCH csr_batch_type INTO l_batch_type;
1848 CLOSE csr_batch_type;
1849 IF (l_claim_line_rec.activity_type = 'PRIC' AND l_batch_type = 'CHARGEBACK') THEN
1850 l_convert_acctd_amount := 'F';
1851 END IF;
1852 END IF;
1853 IF (l_convert_acctd_amount = 'T') THEN
1854 */
1855 -- Convert ACCTD_AMOUNT
1856 OZF_UTILITY_PVT.Convert_Currency(
1857 P_SET_OF_BOOKS_ID => l_claim_line_rec.set_of_books_id,
1858 P_FROM_CURRENCY => l_claim_currency,
1859 P_CONVERSION_DATE => l_claim_exc_date,
1860 P_CONVERSION_TYPE => l_claim_exc_type,
1861 P_CONVERSION_RATE => l_claim_exc_rate,
1862 P_AMOUNT => l_claim_line_rec.claim_currency_amount,
1863 X_RETURN_STATUS => l_return_status,
1864 X_ACC_AMOUNT => l_claim_line_rec.acctd_amount,
1865 X_RATE => l_claim_line_rec.exchange_rate
1866 );
1867 IF l_return_status = FND_API.g_ret_sts_error THEN
1868 RAISE FND_API.g_exc_error;
1869 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1870 RAISE FND_API.g_exc_unexpected_error;
1871 END IF;
1872 -- END IF;
1873 --Bug# 7319828 fixed by ateotia(-)
1874
1875 -- Convert Line AMOUNT
1876 -- bug fix for #2528435
1877 --IF p_claim_line_rec.source_object_class IS NULL AND
1878 -- Bugfix 7811671
1879 IF l_claim_line_rec.source_object_id IS NULL OR l_claim_line_rec.source_object_class = 'SD_SUPPLIER' THEN
1880 l_claim_line_rec.currency_code := l_claim_currency;
1881 l_claim_line_rec.exchange_rate := l_claim_exc_rate;
1882 l_claim_line_rec.exchange_rate_type := l_claim_exc_type;
1883 l_claim_line_rec.exchange_rate_date := l_claim_exc_date;
1884 l_claim_line_rec.amount := l_claim_line_rec.claim_currency_amount;
1885 ELSE
1886 Convert_Line_Amount(
1887 p_claim_line_rec => l_claim_line_rec
1888 ,x_claim_line_rec => l_x_claim_line_rec
1889 ,x_return_status => l_return_status
1890 );
1891 IF l_return_status = FND_API.g_ret_sts_error THEN
1892 RAISE FND_API.g_exc_error;
1893 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1894 RAISE FND_API.g_exc_unexpected_error;
1895 END IF;
1896 l_claim_line_rec := l_x_claim_line_rec;
1897 END IF;
1898 -- raise error if claim currency amount is null
1899 ELSE
1900 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1901 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_NULL');
1902 FND_MSG_PUB.add;
1903 END IF;
1904 RAISE FND_API.g_exc_error;
1905 END IF;
1906
1907 /*-------------------------------------------------------*
1908 | validate |
1909 *-------------------------------------------------------*/
1910 IF OZF_DEBUG_HIGH_ON THEN
1911 OZF_Utility_PVT.debug_message(l_full_name ||': validate');
1912 END IF;
1913
1914 Validate_Claim_Line(
1915 p_api_version => l_api_version,
1916 p_init_msg_list => p_init_msg_list,
1917 p_validation_level => p_validation_level,
1918 x_return_status => l_return_status,
1919 x_msg_count => x_msg_count,
1920 x_msg_data => x_msg_data,
1921 p_claim_line_rec => l_claim_line_rec
1922 );
1923 IF l_return_status = FND_API.g_ret_sts_error THEN
1924 RAISE FND_API.g_exc_error;
1925 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1926 RAISE FND_API.g_exc_unexpected_error;
1927 END IF;
1928
1929 ------------------- amount rounding --------------------
1930 IF l_claim_line_rec.claim_currency_amount IS NOT NULL THEN
1931 l_claim_line_rec.claim_currency_amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_rec.claim_currency_amount, l_claim_currency);
1932 END IF;
1933
1934 IF l_claim_line_rec.amount IS NOT NULL THEN
1935 l_claim_line_rec.amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_rec.amount, l_claim_line_rec.currency_code);
1936 END IF;
1937
1938 --Bug# 7319828 fixed by ateotia(+)
1939 --IF (l_convert_acctd_amount = 'T') THEN
1940 IF l_claim_line_rec.acctd_amount IS NOT NULL THEN
1941 l_claim_line_rec.acctd_amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_rec.acctd_amount, l_functional_currency);
1942 END IF;
1943 --END IF;
1944 --Bug# 7319828 fixed by ateotia(-)
1945
1946 ------------------- amount checking --------------------
1947 IF l_claim_line_rec.claim_currency_amount IS NOT NULL THEN
1948 -- Get the claim amount from database (amount_remaining)
1949 OPEN c_claim_amount(l_claim_line_rec.claim_id);
1950 FETCH c_claim_amount INTO l_claim_amount, l_claim_class;
1951 CLOSE c_claim_amount;
1952
1953 -- Sign of claim_currency_amount should be the same as claim amount_remaining
1954 -- 20-APR-04 Commenting the sign check for the claim amounts, as for a claim negative line amount
1955 -- can be specified to associate negative accruals. Similarily valid for DED/OPM.
1956 --IF SIGN(l_claim_line_rec.claim_currency_amount) <> SIGN(l_claim_amount) THEN
1957 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1958 -- FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_SIGN_ERR');
1959 -- FND_MSG_PUB.add;
1960 -- END IF;
1961 -- RAISE FND_API.g_exc_error;
1962 --END IF;
1963
1964 -- skip amount comparison if it's updating from tbl.
1965 -- update_from_tbl_flag condition commented by slkrishn
1966 -- amount condition not working at table
1967 -- mchnag: open the checking again to fix BUG#2242664
1968 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false AND
1969 l_claim_class <> 'GROUP' THEN
1970 -- get total of existing line amount (sum of claim_currency_amount)
1971 OPEN c_line_sum_amt(l_claim_line_rec.claim_id);
1972 FETCH c_line_sum_amt INTO l_line_sum_amt;
1973 CLOSE c_line_sum_amt;
1974
1975 -- comparison of claim amount and line amount (claim_currency_amount)
1976 IF l_line_sum_amt IS NOT NULL THEN
1977 IF ABS((l_line_sum_amt + l_claim_line_rec.claim_currency_amount)) > ABS(l_claim_amount) THEN
1978 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1979 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
1980 FND_MSG_PUB.add;
1981 END IF;
1982 RAISE FND_API.g_exc_error;
1983 END IF;
1984 ELSIF ABS(l_claim_line_rec.claim_currency_amount) > ABS(l_claim_amount) THEN
1985 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1986 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
1987 FND_MSG_PUB.add;
1988 END IF;
1989 RAISE FND_API.g_exc_error;
1990 END IF;
1991 END IF;
1992 END IF;
1993
1994 OZF_CLAIM_SETTLEMENT_VAL_PVT.Validate_Claim_Line(
1995 p_api_version => l_api_version
1996 ,p_init_msg_list => FND_API.g_false
1997 ,p_validation_level => FND_API.g_valid_level_full
1998 ,x_return_status => l_return_status
1999 ,x_msg_data => x_msg_data
2000 ,x_msg_count => x_msg_count
2001 ,p_claim_line_rec => l_claim_line_rec
2002 );
2003 IF l_return_status = FND_API.g_ret_sts_error THEN
2004 RAISE FND_API.g_exc_error;
2005 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2006 RAISE FND_API.g_exc_unexpected_error;
2007 END IF;
2008
2009
2010 IF l_claim_line_rec.claim_line_id IS NULL THEN
2011 LOOP
2012 -- Get the identifier
2013 OPEN c_claim_line_seq;
2014 FETCH c_claim_line_seq INTO l_claim_line_rec.claim_line_id;
2015 CLOSE c_claim_line_seq;
2016 -- Check the uniqueness of the identifier
2017 OPEN c_claim_line_count(l_claim_line_rec.claim_line_id);
2018 FETCH c_claim_line_count INTO l_claim_line_count;
2019 CLOSE c_claim_line_count;
2020 -- Exit when the identifier uniqueness is established
2021 EXIT WHEN l_claim_line_count = 0;
2022 END LOOP;
2023 END IF;
2024
2025 -- Bug4489415: Make the Tax Call
2026 IF l_claim_line_rec.tax_action IS NOT NULL AND
2027 l_claim_line_rec.amount IS NOT NULL THEN
2028
2029 OZF_CLAIM_TAX_PVT.Calculate_Claim_Line_Tax(
2030 p_api_version => l_api_version
2031 ,p_init_msg_list => FND_API.g_false
2032 ,p_validation_level => FND_API.g_valid_level_full
2033 ,x_return_status => l_return_status
2034 ,x_msg_data => x_msg_data
2035 ,x_msg_count => x_msg_count
2036 ,p_x_claim_line_rec => l_claim_line_rec
2037 );
2038 IF l_return_status = FND_API.g_ret_sts_error THEN
2039 RAISE FND_API.g_exc_error;
2040 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2041 RAISE FND_API.g_exc_unexpected_error;
2042 END IF;
2043
2044 END IF;
2045
2046 /*-------------------------------------------------------*
2047 | insert |
2048 *-------------------------------------------------------*/
2049 IF OZF_DEBUG_HIGH_ON THEN
2050 OZF_Utility_PVT.debug_message(l_full_name ||': insert');
2051 OZF_Utility_PVT.debug_message('l_claim_line_rec.claim_line_id before insert :' || l_claim_line_rec.claim_line_id);
2052 END IF;
2053
2054
2055 INSERT INTO ozf_claim_lines_all (
2056 claim_line_id,
2057 object_version_number,
2058 last_update_date,
2059 last_updated_by,
2060 creation_date,
2061 created_by,
2062 last_update_login,
2063 request_id,
2064 program_application_id,
2065 program_update_date,
2066 program_id,
2067 created_from,
2068 claim_id,
2069 line_number,
2070 split_from_claim_line_id,
2071 amount,
2072 claim_currency_amount,
2073 acctd_amount,
2074 currency_code,
2075 exchange_rate_type,
2076 exchange_rate_date,
2077 exchange_rate,
2078 set_of_books_id,
2079 valid_flag,
2080 source_object_id,
2081 source_object_line_id,
2082 source_object_class,
2083 source_object_type_id,
2084 plan_id,
2085 offer_id,
2086 utilization_id,
2087 payment_method,
2088 payment_reference_id,
2089 payment_reference_number,
2090 payment_reference_date,
2091 voucher_id,
2092 voucher_number,
2093 payment_status,
2094 approved_flag,
2095 approved_date,
2096 approved_by,
2097 settled_date,
2098 settled_by,
2099 performance_complete_flag,
2100 performance_attached_flag,
2101 select_cust_children_flag,
2102 item_id,
2103 item_description,
2104 quantity,
2105 quantity_uom,
2106 rate,
2107 activity_type,
2108 activity_id,
2109 related_cust_account_id,
2110 buy_group_cust_account_id,
2111 relationship_type,
2112 earnings_associated_flag,
2113 comments,
2114 tax_code,
2115 credit_to,
2116 attribute_category,
2117 attribute1,
2118 attribute2,
2119 attribute3,
2120 attribute4,
2121 attribute5,
2122 attribute6,
2123 attribute7,
2124 attribute8,
2125 attribute9,
2126 attribute10,
2127 attribute11,
2128 attribute12,
2129 attribute13,
2130 attribute14,
2131 attribute15,
2132 org_id,
2133 sale_date,
2134 item_type,
2135 tax_amount,
2136 claim_curr_tax_amount,
2137 acctd_tax_amount,
2138 activity_line_id,
2139 offer_type,
2140 prorate_earnings_flag,
2141 earnings_end_date,
2142 buy_group_party_id,
2143 dpp_cust_account_id, --12.1 Enhancement : Price Protection
2144 batch_line_id , --Bugfix : 7811671
2145 activity_name --Hbandi added code for resolving the bug 10164003
2146
2147 )
2148 VALUES (
2149 l_claim_line_rec.claim_line_id,
2150 l_object_version_number, -- OBJECT_VERSION_NUMBER
2151 SYSDATE, -- LAST_UPDATE_DATE
2152 l_updated_by, -- LAST_UPDATED_BY
2153 SYSDATE, -- CREATION_DATE
2154 l_created_by, -- CREATED_BY
2155 l_last_update_login, -- LAST_UPDATE_LOGIN
2156 FND_GLOBAL.CONC_REQUEST_ID, -- REQUEST_ID
2157 FND_GLOBAL.PROG_APPL_ID, -- PROGRAM_APPLICATION_ID
2158 SYSDATE, -- PROGRAM_UPDATE_DATE
2159 FND_GLOBAL.CONC_PROGRAM_ID, -- PROGRAM_ID
2160 l_claim_line_rec.created_from, -- CREATED_FROM
2161 l_claim_line_rec.claim_id,
2162 l_claim_line_rec.line_number,
2163 l_claim_line_rec.split_from_claim_line_id,
2164 l_claim_line_rec.amount,
2165 l_claim_line_rec.claim_currency_amount,
2166 l_claim_line_rec.acctd_amount,
2167 l_claim_line_rec.currency_code,
2168 l_claim_line_rec.exchange_rate_type,
2169 l_claim_line_rec.exchange_rate_date,
2170 l_claim_line_rec.exchange_rate,
2171 l_claim_line_rec.set_of_books_id,
2172 l_valid_flag,
2173 l_claim_line_rec.source_object_id,
2174 l_claim_line_rec.source_object_line_id,
2175 l_claim_line_rec.source_object_class,
2176 l_claim_line_rec.source_object_type_id,
2177 l_claim_line_rec.plan_id,
2178 l_claim_line_rec.offer_id,
2179 l_claim_line_rec.utilization_id,
2180 l_claim_line_rec.payment_method,
2181 l_claim_line_rec.payment_reference_id,
2182 l_claim_line_rec.payment_reference_number,
2183 l_claim_line_rec.payment_reference_date,
2184 l_claim_line_rec.voucher_id,
2185 l_claim_line_rec.voucher_number,
2186 l_claim_line_rec.payment_status,
2187 l_claim_line_rec.approved_flag,
2188 l_claim_line_rec.approved_date,
2189 l_claim_line_rec.approved_by,
2190 l_claim_line_rec.settled_date,
2191 l_claim_line_rec.settled_by,
2192 l_claim_line_rec.performance_complete_flag,
2193 l_claim_line_rec.performance_attached_flag,
2194 l_claim_line_rec.select_cust_children_flag,
2195 l_claim_line_rec.item_id,
2196 l_claim_line_rec.item_description,
2197 l_claim_line_rec.quantity,
2198 l_claim_line_rec.quantity_uom,
2199 l_claim_line_rec.rate,
2200 l_claim_line_rec.activity_type,
2201 l_claim_line_rec.activity_id,
2202 l_claim_line_rec.related_cust_account_id,
2203 l_claim_line_rec.buy_group_cust_account_id,
2204 l_claim_line_rec.relationship_type,
2205 l_claim_line_rec.earnings_associated_flag,
2206 l_claim_line_rec.comments,
2207 l_claim_line_rec.tax_code,
2208 l_claim_line_rec.credit_to,
2209 l_claim_line_rec.attribute_category,
2210 l_claim_line_rec.attribute1,
2211 l_claim_line_rec.attribute2,
2212 l_claim_line_rec.attribute3,
2213 l_claim_line_rec.attribute4,
2214 l_claim_line_rec.attribute5,
2215 l_claim_line_rec.attribute6,
2216 l_claim_line_rec.attribute7,
2217 l_claim_line_rec.attribute8,
2218 l_claim_line_rec.attribute9,
2219 l_claim_line_rec.attribute10,
2220 l_claim_line_rec.attribute11,
2221 l_claim_line_rec.attribute12,
2222 l_claim_line_rec.attribute13,
2223 l_claim_line_rec.attribute14,
2224 l_claim_line_rec.attribute15,
2225 l_org_id, -- ORG_ID
2226 l_claim_line_rec.sale_date,
2227 l_claim_line_rec.item_type,
2228 l_claim_line_rec.tax_amount,
2229 l_claim_line_rec.claim_curr_tax_amount,
2230 l_claim_line_rec.acctd_tax_amount, --Bug4489415
2231 l_claim_line_rec.activity_line_id,
2232 l_claim_line_rec.offer_type,
2233 l_claim_line_rec.prorate_earnings_flag,
2234 l_claim_line_rec.earnings_end_date,
2235 l_claim_line_rec.buy_group_party_id,
2236 l_claim_line_rec.dpp_cust_account_id, --12.1 Enhancement : Price Protection
2237 l_claim_line_rec.batch_line_id , --Bugfix : 7811671
2238 l_claim_line_rec.activity_name --Hbandi added code for resolving the bug 10164003
2239 );
2240
2241 ------------------------- finish -------------------------------
2242 x_claim_line_id := l_claim_line_rec.claim_line_id;
2243
2244 -- Check for commit
2245 IF FND_API.to_boolean(p_commit) THEN
2246 COMMIT;
2247 END IF;
2248
2249 FND_MSG_PUB.count_and_get(
2250 p_encoded => FND_API.g_false,
2251 p_count => x_msg_count,
2252 p_data => x_msg_data
2253 );
2254
2255 IF OZF_DEBUG_HIGH_ON THEN
2256 OZF_Utility_PVT.debug_message(l_full_name ||': end');
2257 END IF;
2258
2259 EXCEPTION
2260 WHEN FND_API.g_exc_error THEN
2261 ROLLBACK TO Create_Claim_Line;
2262 x_return_status := FND_API.g_ret_sts_error;
2263 FND_MSG_PUB.count_and_get (
2264 p_encoded => FND_API.g_false
2265 ,p_count => x_msg_count
2266 ,p_data => x_msg_data
2267 );
2268
2269 WHEN FND_API.g_exc_unexpected_error THEN
2270 ROLLBACK TO Create_Claim_Line;
2271 x_return_status := FND_API.g_ret_sts_unexp_error ;
2272 FND_MSG_PUB.count_and_get (
2273 p_encoded => FND_API.g_false
2274 ,p_count => x_msg_count
2275 ,p_data => x_msg_data
2276 );
2277
2278 WHEN OTHERS THEN
2279 ROLLBACK TO Create_Claim_Line;
2280 x_return_status := FND_API.g_ret_sts_unexp_error ;
2281 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2282 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2283 END IF;
2284 FND_MSG_PUB.count_and_get(
2285 p_encoded => FND_API.g_false
2286 ,p_count => x_msg_count
2287 ,p_data => x_msg_data
2288 );
2289
2290 END Create_Claim_Line;
2291
2292
2293 ---------------------------------------------------------------------
2294 -- PROCEDURE
2295 -- Delete_Claim_Line_Tbl
2296 --
2297 -- HISTORY
2298 -- 02/02/2001 mchang Create.
2299 -- 03/28/2001 mchang add passing_in parameter: p_utiz_obj_ver_tbl
2300 -- 04/30/2001 mchang remove passing_in parameter: p_utiz_obj_ver_tbl
2301 -- 07/22/2002 yizhang add p_mode for security check
2302 ---------------------------------------------------------------------
2303 PROCEDURE Delete_Claim_Line_Tbl(
2304 p_api_version IN NUMBER
2305 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
2306 ,p_commit IN VARCHAR2 := FND_API.g_false
2307 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
2308
2309 ,x_return_status OUT NOCOPY VARCHAR2
2310 ,x_msg_data OUT NOCOPY VARCHAR2
2311 ,x_msg_count OUT NOCOPY NUMBER
2312
2313 ,p_claim_line_tbl IN claim_line_tbl_type
2314 ,p_change_object_version IN VARCHAR2 := FND_API.g_false
2315 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
2316 ,x_error_index OUT NOCOPY NUMBER
2317 ) IS
2318 l_api_version CONSTANT NUMBER := 1.0;
2319 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Claim_Line_Tbl';
2320 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2321
2322 l_return_status VARCHAR2(1);
2323 l_msg_count NUMBER;
2324 l_msg_data VARCHAR2(4000);
2325
2326 l_claim_line_id NUMBER;
2327 l_object_version NUMBER;
2328
2329 l_access VARCHAR2(1) := 'N';
2330
2331 CURSOR csr_line_obj_ver(cv_claim_line_id IN NUMBER) IS
2332 SELECT object_version_number
2333 FROM ozf_claim_lines
2334 WHERE claim_line_id = cv_claim_line_id;
2335
2336
2337 --(Addded by Uday) For Amount check
2338 l_claim_amount NUMBER;
2339 l_exist_total_line_amount NUMBER;
2340 l_effective_line_amount NUMBER;
2341 l_del_total_line_amount NUMBER := 0;
2342 l_claim_class VARCHAR2(30);
2343
2344
2345 CURSOR csr_claim_amount(cv_claim_id IN NUMBER) IS
2346 SELECT amount_remaining, claim_class
2347 FROM ozf_claims
2348 WHERE claim_id = cv_claim_id;
2349
2350 CURSOR csr_exist_total_line_amt(cv_claim_id IN NUMBER) IS
2351 SELECT nvl(sum(claim_currency_amount), 0)
2352 FROM ozf_claim_lines
2353 WHERE claim_id = cv_claim_id;
2354
2355 --End of Amount check declarations
2356
2357 BEGIN
2358 --------------------- initialize -----------------------
2359 SAVEPOINT Delete_Claim_Line_Tbl;
2360
2361 IF OZF_DEBUG_HIGH_ON THEN
2362 OZF_Utility_PVT.debug_message(l_full_name||': start');
2363 END IF;
2364
2365 IF FND_API.to_boolean(p_init_msg_list) THEN
2366 FND_MSG_PUB.initialize;
2367 END IF;
2368
2369 IF NOT FND_API.compatible_api_call(
2370 l_api_version,
2371 p_api_version,
2372 l_api_name,
2373 g_pkg_name
2374 ) THEN
2375 RAISE FND_API.g_exc_unexpected_error;
2376 END IF;
2377
2378 x_return_status := FND_API.g_ret_sts_success;
2379
2380 ---------------------- check claim access ------------------------
2381 IF p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode AND p_claim_line_tbl.count > 0 THEN
2382 FOR j IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
2383 IF p_claim_line_tbl.EXISTS(j) THEN
2384 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
2385 P_Api_Version_Number => 1.0
2386 , P_Init_Msg_List => FND_API.G_FALSE
2387 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2388 , P_Commit => FND_API.G_FALSE
2389 , P_object_id => p_claim_line_tbl(j).claim_id
2390 , P_object_type => G_CLAIM_OBJECT_TYPE
2391 , P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
2392 , X_Return_Status => l_return_status
2393 , X_Msg_Count => l_msg_count
2394 , X_Msg_Data => l_msg_data
2395 , X_access => l_access);
2396
2397 IF l_access = 'N' THEN
2398 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2399 FND_MESSAGE.set_name('OZF','OZF_CLAIM_NO_ACCESS');
2400 FND_MSG_PUB.add;
2401 END IF;
2402 RAISE FND_API.G_EXC_ERROR;
2403 END IF;
2404 EXIT;
2405 END IF;
2406 END LOOP;
2407 END IF;
2408
2409 --------------------- Amount Checking -----------------------
2410 IF p_claim_line_tbl.count > 0 THEN
2411 --Get the claim amount.
2412 OPEN csr_claim_amount(p_claim_line_tbl(1).claim_id);
2413 FETCH csr_claim_amount INTO l_claim_amount, l_claim_class;
2414 CLOSE csr_claim_amount;
2415
2416 --Get the existing line amount
2417 OPEN csr_exist_total_line_amt(p_claim_line_tbl(1).claim_id);
2418 FETCH csr_exist_total_line_amt INTO l_exist_total_line_amount;
2419 CLOSE csr_exist_total_line_amt;
2420
2421 --Get the sum of line amounts to be deleted.
2422 FOR j IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
2423 IF p_claim_line_tbl.EXISTS(j) THEN
2424 l_del_total_line_amount := l_del_total_line_amount + nvl(p_claim_line_tbl(j).claim_currency_amount, 0);
2425 END IF;
2426 END LOOP;
2427
2428 l_effective_line_amount := l_exist_total_line_amount - l_del_total_line_amount;
2429
2430 IF l_claim_class <> 'GROUP' THEN
2431 IF (sign(l_effective_line_amount) <> sign(l_claim_amount))
2432 AND l_effective_line_amount <> 0
2433 THEN
2434 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2435 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_SIGN_ERR');
2436 FND_MSG_PUB.add;
2437 END IF;
2438 RAISE FND_API.g_exc_error;
2439 END IF;
2440
2441 IF ABS(l_effective_line_amount) > ABS(l_claim_amount)
2442 THEN
2443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2444 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
2445 FND_MSG_PUB.add;
2446 END IF;
2447 RAISE FND_API.g_exc_error;
2448 END IF;
2449 END IF;
2450 END IF;
2451 --------------------- End of Amount Checking -----------------------
2452
2453 --------------------- Delete Claim Line Table -----------------------
2454 IF p_claim_line_tbl.count > 0 THEN
2455 FOR i IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
2456 IF p_claim_line_tbl.EXISTS(i) THEN
2457 l_claim_line_id := p_claim_line_tbl(i).claim_line_id;
2458 IF p_change_object_version = FND_API.g_true THEN
2459 --l_object_version := p_claim_line_tbl(i).object_version_number + 1;
2460 OPEN csr_line_obj_ver(p_claim_line_tbl(i).claim_line_id);
2461 FETCH csr_line_obj_ver INTO l_object_version;
2462 CLOSE csr_line_obj_ver;
2463 IF OZF_DEBUG_HIGH_ON THEN
2464 OZF_Utility_PVT.debug_message('l_object_version11:' || l_object_version);
2465 END IF;
2466 ELSE
2467 l_object_version := p_claim_line_tbl(i).object_version_number;
2468 IF OZF_DEBUG_HIGH_ON THEN
2469 OZF_Utility_PVT.debug_message('l_object_version22:' || l_object_version);
2470 END IF;
2471 END IF;
2472
2473 IF OZF_DEBUG_HIGH_ON THEN
2474 OZF_Utility_PVT.debug_message('l_object_version33:' || l_object_version);
2475 END IF;
2476 -- Call the delete claim line API
2477 Delete_Claim_Line(
2478 p_api_version => 1.0
2479 , p_init_msg_list => FND_API.g_false
2480 , p_commit => FND_API.g_false
2481 , x_return_status => l_return_status
2482 , x_msg_data => x_msg_data
2483 , x_msg_count => x_msg_count
2484 , p_claim_line_id => l_claim_line_id
2485 , p_object_version => l_object_version
2486 , p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
2487 );
2488 IF l_return_status = fnd_api.g_ret_sts_error THEN
2489 x_error_index := i;
2490 RAISE FND_API.g_exc_error;
2491 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2492 x_error_index := i;
2493 RAISE FND_API.g_exc_unexpected_error;
2494 END IF;
2495 END IF;
2496 END LOOP;
2497 END IF;
2498
2499 ------------------------- finish -------------------------------
2500 IF FND_API.to_boolean(p_commit) THEN
2501 COMMIT;
2502 END IF;
2503
2504 FND_MSG_PUB.count_and_get(
2505 p_encoded => FND_API.g_false,
2506 p_count => x_msg_count,
2507 p_data => x_msg_data
2508 );
2509
2510 IF OZF_DEBUG_HIGH_ON THEN
2511 OZF_Utility_PVT.debug_message(l_full_name ||': end');
2512 END IF;
2513
2514 EXCEPTION
2515 WHEN FND_API.g_exc_error THEN
2516 ROLLBACK TO Delete_Claim_Line_Tbl;
2517 x_return_status := FND_API.g_ret_sts_error;
2518 FND_MSG_PUB.count_and_get (
2519 p_encoded => FND_API.g_false
2520 ,p_count => x_msg_count
2521 ,p_data => x_msg_data
2522 );
2523
2524 WHEN FND_API.g_exc_unexpected_error THEN
2525 ROLLBACK TO Delete_Claim_Line_Tbl;
2526 x_return_status := FND_API.g_ret_sts_unexp_error ;
2527 FND_MSG_PUB.count_and_get (
2528 p_encoded => FND_API.g_false
2529 ,p_count => x_msg_count
2530 ,p_data => x_msg_data
2531 );
2532
2533 WHEN OTHERS THEN
2534 ROLLBACK TO Delete_Claim_Line_Tbl;
2535 x_return_status := FND_API.g_ret_sts_unexp_error ;
2536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2537 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2538 END IF;
2539 FND_MSG_PUB.count_and_get(
2540 p_encoded => FND_API.g_false
2541 ,p_count => x_msg_count
2542 ,p_data => x_msg_data
2543 );
2544
2545 END Delete_Claim_Line_Tbl;
2546
2547
2548 ---------------------------------------------------------------
2549 -- PROCEDURE
2550 -- Delete_Claim_Line
2551 --
2552 -- HISTORY
2553 -- 07/11/2000 mchang Create.
2554 -- 07/31/2000 mchang Add amount checking: Claim Amount >= (Existing Lines Total - to be Deleted Line Amount).
2555 -- If Claim amount is different from DB, update Claim amount to new amount.
2556 -- 02/02/2001 mchang Remove passing in parameters - p_claim_amount and p_claim_version.
2557 -- 03/28/2001 mchang add passing_in parameter: p_utiz_obj_ver
2558 -- 04/30/2001 mchang remove passing_in parameter: p_utiz_obj_ver
2559 -- 08/06/2001 mchang remove associate earnings by calling OZF_Claim_Accrual_PVT.Delete_Line_Util_Tbl
2560 -- 07/22/2002 yizhang add p_mode for security check
2561 ---------------------------------------------------------------
2562 PROCEDURE Delete_Claim_Line(
2563 p_api_version IN NUMBER
2564 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
2565 ,p_commit IN VARCHAR2 := FND_API.g_false
2566
2567 ,x_return_status OUT NOCOPY VARCHAR2
2568 ,x_msg_count OUT NOCOPY NUMBER
2569 ,x_msg_data OUT NOCOPY VARCHAR2
2570
2571 ,p_claim_line_id IN NUMBER
2572 ,p_object_version IN NUMBER
2573 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
2574 )
2575 IS
2576 l_api_version CONSTANT NUMBER := 1.0;
2577 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Claim_Line';
2578 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2579 l_return_status VARCHAR2(1);
2580 l_access VARCHAR2(1) := 'N';
2581
2582 -- Cursor to get claim_id
2583 CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
2584 SELECT claim_id
2585 FROM ozf_claim_lines
2586 WHERE claim_line_id = cv_claim_line_id;
2587
2588 -- Cursor to get claim amount
2589 CURSOR c_claim_amount(cv_claim_id IN NUMBER) IS
2590 SELECT amount_remaining
2591 FROM ozf_claims
2592 WHERE claim_id = cv_claim_id;
2593
2594 -- Cursor to sum of claim line amount
2595 CURSOR c_line_sum_amt(cv_claim_id IN NUMBER, cv_line_id IN NUMBER) IS
2596 SELECT SUM(claim_currency_amount)
2597 FROM ozf_claim_lines
2598 WHERE claim_id = cv_claim_id
2599 AND claim_line_id <> cv_line_id;
2600
2601 -- Cursor to get earnings associated with this line
2602 CURSOR csr_get_lines_util(cv_claim_line_id IN NUMBER) IS
2603 SELECT claim_line_util_id
2604 , object_version_number
2605 , currency_code
2606 FROM ozf_claim_lines_util
2607 WHERE claim_line_id = cv_claim_line_id;
2608
2609 l_claim_id NUMBER;
2610 l_claim_amount NUMBER;
2611 l_line_sum_amt NUMBER;
2612 l_line_util_tbl OZF_Claim_Accrual_PVT.line_util_tbl_type;
2613 l_counter NUMBER := 1;
2614 l_error_index NUMBER;
2615
2616 BEGIN
2617 --------------------- initialize -----------------------
2618 SAVEPOINT Delete_Claim_Line;
2619
2620 IF OZF_DEBUG_HIGH_ON THEN
2621 OZF_Utility_PVT.debug_message(l_full_name||': start');
2622 END IF;
2623
2624 IF FND_API.to_boolean(p_init_msg_list) THEN
2625 FND_MSG_PUB.initialize;
2626 END IF;
2627
2628 IF NOT FND_API.compatible_api_call(
2629 l_api_version,
2630 p_api_version,
2631 l_api_name,
2632 g_pkg_name
2633 ) THEN
2634 RAISE FND_API.g_exc_unexpected_error;
2635 END IF;
2636
2637 x_return_status := FND_API.G_RET_STS_SUCCESS;
2638
2639 OPEN csr_claim_id(p_claim_line_id);
2640 FETCH csr_claim_id INTO l_claim_id;
2641 CLOSE csr_claim_id;
2642
2643 ---------------------- check claim access ------------------------
2644 IF p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode THEN
2645 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
2646 P_Api_Version_Number => 1.0
2647 , P_Init_Msg_List => FND_API.G_FALSE
2648 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2649 , P_Commit => FND_API.G_FALSE
2650 , P_object_id => l_claim_id
2651 , P_object_type => G_CLAIM_OBJECT_TYPE
2652 , P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
2653 , X_Return_Status => l_return_status
2654 , X_Msg_Count => x_msg_count
2655 , X_Msg_Data => x_msg_data
2656 , X_access => l_access);
2657
2658 IF l_access = 'N' THEN
2659 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2660 FND_MESSAGE.set_name('OZF','OZF_CLAIM_NO_ACCESS');
2661 FND_MSG_PUB.add;
2662 END IF;
2663 RAISE FND_API.G_EXC_ERROR;
2664 END IF;
2665 END IF;
2666
2667 --------------------- claim amount checking -----------------------
2668 -- get claim amount from database (amount_remaining)
2669 --OPEN c_claim_amount(l_claim_id);
2670 --FETCH c_claim_amount INTO l_claim_amount;
2671 --CLOSE c_claim_amount;
2672
2673 -- get total of existing line amount (sum of claim_currency_amount)
2674 --OPEN c_line_sum_amt(l_claim_id, p_claim_line_id);
2675 --FETCH c_line_sum_amt INTO l_line_sum_amt;
2676 --CLOSE c_line_sum_amt;
2677
2678 -- comparison of claim amount and line amount (claim_currency_amount)
2679 --IF l_line_sum_amt IS NOT NULL THEN
2680 --IF ABS(l_line_sum_amt) > ABS(l_claim_amount) THEN
2681 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2682 -- FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
2683 --FND_MSG_PUB.add;
2684 --END IF;
2685 --RAISE FND_API.g_exc_error;
2686 --END IF;
2687 --END IF;
2688
2689 ------------------ Remove Associate Earnings ----------------
2690 OPEN csr_get_lines_util(p_claim_line_id);
2691 LOOP
2692 FETCH csr_get_lines_util INTO l_line_util_tbl(l_counter).claim_line_util_id
2693 , l_line_util_tbl(l_counter).object_version_number
2694 , l_line_util_tbl(l_counter).currency_code;
2695 EXIT WHEN csr_get_lines_util%NOTFOUND;
2696 l_line_util_tbl(l_counter).claim_line_id := p_claim_line_id;
2697 l_counter := l_counter + 1;
2698 END LOOP;
2699 CLOSE csr_get_lines_util;
2700
2701 IF l_counter > 1 THEN
2702 OZF_Claim_Accrual_PVT.Delete_Line_Util_Tbl(
2703 p_api_version => l_api_version
2704 ,p_init_msg_list => FND_API.g_false
2705 ,p_commit => FND_API.g_false
2706 ,p_validation_level => FND_API.g_valid_level_full
2707 ,x_return_status => l_return_status
2708 ,x_msg_data => x_msg_data
2709 ,x_msg_count => x_msg_count
2710 ,p_line_util_tbl => l_line_util_tbl
2711 ,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
2712 ,x_error_index => l_error_index
2713 );
2714 IF l_return_status = fnd_api.g_ret_sts_error THEN
2715 RAISE FND_API.g_exc_error;
2716 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2717 RAISE FND_API.g_exc_unexpected_error;
2718 END IF;
2719 END IF;
2720
2721 ------------------------ Delete ------------------------
2722 IF OZF_DEBUG_HIGH_ON THEN
2723 OZF_Utility_PVT.debug_message(l_full_name ||': delete');
2724 OZF_Utility_PVT.debug_message(p_claim_line_id ||': delete : p_claim_line_id');
2725 OZF_Utility_PVT.debug_message(p_object_version ||': delete : p_object_version');
2726 END IF;
2727
2728 DELETE FROM ozf_claim_lines_all
2729 WHERE claim_line_id = p_claim_line_id
2730 AND object_version_number = p_object_version;
2731
2732 IF (SQL%NOTFOUND) THEN
2733 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2734 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2735 FND_MSG_PUB.add;
2736 END IF;
2737 RAISE FND_API.g_exc_error;
2738 END IF;
2739
2740 -------------------- finish --------------------------
2741 IF FND_API.to_boolean(p_commit) THEN
2742 COMMIT;
2743 END IF;
2744
2745 FND_MSG_PUB.count_and_get(
2746 p_encoded => FND_API.g_false,
2747 p_count => x_msg_count,
2748 p_data => x_msg_data
2749 );
2750
2751 IF OZF_DEBUG_HIGH_ON THEN
2752 OZF_Utility_PVT.debug_message(l_full_name ||': end');
2753 END IF;
2754
2755 EXCEPTION
2756 WHEN FND_API.g_exc_error THEN
2757 ROLLBACK TO Delete_Claim_Line;
2758 x_return_status := FND_API.g_ret_sts_error;
2759 FND_MSG_PUB.count_and_get(
2760 p_encoded => FND_API.g_false,
2761 p_count => x_msg_count,
2762 p_data => x_msg_data
2763 );
2764
2765 WHEN FND_API.g_exc_unexpected_error THEN
2766 ROLLBACK TO Delete_Claim_Line;
2767 x_return_status := FND_API.g_ret_sts_unexp_error ;
2768 FND_MSG_PUB.count_and_get(
2769 p_encoded => FND_API.g_false,
2770 p_count => x_msg_count,
2771 p_data => x_msg_data
2772 );
2773
2774 WHEN OTHERS THEN
2775 ROLLBACK TO Delete_Claim_Line;
2776 x_return_status := FND_API.g_ret_sts_unexp_error ;
2777 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2778 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2779 END IF;
2780 FND_MSG_PUB.count_and_get(
2781 p_encoded => FND_API.g_false,
2782 p_count => x_msg_count,
2783 p_data => x_msg_data
2784 );
2785
2786 END Delete_Claim_Line;
2787
2788
2789 -------------------------------------------------------------------
2790 -- PROCEDURE
2791 -- Lock_Claim_Line
2792 --
2793 -- HISTORY
2794 -- 07/11/2000 mchang Create.
2795 --------------------------------------------------------------------
2796 PROCEDURE Lock_Claim_Line(
2797 p_api_version IN NUMBER
2798 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
2799
2800 ,x_return_status OUT NOCOPY VARCHAR2
2801 ,x_msg_count OUT NOCOPY NUMBER
2802 ,x_msg_data OUT NOCOPY VARCHAR2
2803
2804 ,p_claim_line_id IN NUMBER
2805 ,p_object_version IN NUMBER
2806 )
2807 IS
2808 l_api_version CONSTANT NUMBER := 1.0;
2809 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Claim_Line';
2810 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2811
2812 l_claim_line_id NUMBER;
2813
2814 CURSOR c_claim_line IS
2815 SELECT claim_line_id
2816 FROM ozf_claim_lines_all
2817 WHERE claim_line_id = p_claim_line_id
2818 AND object_version_number = p_object_version
2819 FOR UPDATE OF claim_line_id NOWAIT;
2820
2821 BEGIN
2822 -------------------- initialize ------------------------
2823 IF OZF_DEBUG_HIGH_ON THEN
2824 OZF_Utility_PVT.debug_message(l_full_name||': start');
2825 END IF;
2826
2827 IF FND_API.to_boolean(p_init_msg_list) THEN
2828 FND_MSG_PUB.initialize;
2829 END IF;
2830
2831 IF NOT FND_API.compatible_api_call(
2832 l_api_version,
2833 p_api_version,
2834 l_api_name,
2835 g_pkg_name
2836 ) THEN
2837 RAISE FND_API.g_exc_unexpected_error;
2838 END IF;
2839
2840 x_return_status := FND_API.g_ret_sts_success;
2841
2842 ------------------------ lock -------------------------
2843 IF OZF_DEBUG_HIGH_ON THEN
2844 OZF_Utility_PVT.debug_message(l_full_name||': lock');
2845 END IF;
2846
2847 OPEN c_claim_line;
2848 FETCH c_claim_line INTO l_claim_line_id;
2849 IF (c_claim_line%NOTFOUND) THEN
2850 CLOSE c_claim_line;
2851 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2852 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2853 FND_MSG_PUB.add;
2854 END IF;
2855 RAISE FND_API.g_exc_error;
2856 END IF;
2857 CLOSE c_claim_line;
2858
2859 -------------------- finish --------------------------
2860 FND_MSG_PUB.count_and_get(
2861 p_encoded => FND_API.g_false,
2862 p_count => x_msg_count,
2863 p_data => x_msg_data
2864 );
2865
2866 IF OZF_DEBUG_HIGH_ON THEN
2867 OZF_Utility_PVT.debug_message(l_full_name ||': end');
2868 END IF;
2869
2870 EXCEPTION
2871 WHEN OZF_Utility_PVT.resource_locked THEN
2872 x_return_status := FND_API.g_ret_sts_error;
2873 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2874 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
2875 FND_MSG_PUB.add;
2876 END IF;
2877 FND_MSG_PUB.count_and_get(
2878 p_encoded => FND_API.g_false,
2879 p_count => x_msg_count,
2880 p_data => x_msg_data
2881 );
2882
2883 WHEN FND_API.g_exc_error THEN
2884 x_return_status := FND_API.g_ret_sts_error;
2885 FND_MSG_PUB.count_and_get(
2886 p_encoded => FND_API.g_false,
2887 p_count => x_msg_count,
2888 p_data => x_msg_data
2889 );
2890
2891 WHEN FND_API.g_exc_unexpected_error THEN
2892 x_return_status := FND_API.g_ret_sts_unexp_error ;
2893 FND_MSG_PUB.count_and_get(
2894 p_encoded => FND_API.g_false,
2895 p_count => x_msg_count,
2896 p_data => x_msg_data
2897 );
2898
2899 WHEN OTHERS THEN
2900 x_return_status := FND_API.g_ret_sts_unexp_error ;
2901 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2902 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2903 END IF;
2904 FND_MSG_PUB.count_and_get(
2905 p_encoded => FND_API.g_false,
2906 p_count => x_msg_count,
2907 p_data => x_msg_data
2908 );
2909
2910 END Lock_Claim_Line;
2911
2912
2913 ---------------------------------------------------------------------
2914 -- PROCEDURE
2915 -- Check_Claim_Line_Lock_Items
2916 --
2917 -- HISTORY
2918 -- 04/27/2002 yizhang Create.
2919 -- 05/30/2005 kdhulipa fix for 4400825
2920 -- 01/16/2006 kdhulipa fix for 4954996
2921 ---------------------------------------------------------------------
2922 PROCEDURE Check_Claim_Line_Lock_Items(
2923 p_claim_line_rec IN claim_line_rec_type
2924 ,x_return_status OUT NOCOPY VARCHAR2
2925 )
2926 IS
2927 l_source_object_id NUMBER := NULL;
2928 l_source_object_class VARCHAR2(15);
2929 l_source_object_type_id NUMBER := NULL;
2930 l_plan_id NUMBER := NULL;
2931 l_item_id NUMBER := NULL;
2932 l_item_description VARCHAR2(240);
2933 l_activity_type VARCHAR2(30);
2934 l_activity_id NUMBER := NULL;
2935 l_earnings_associated_flag VARCHAR2(1);
2936
2937 CURSOR csr_line_lock_items(cv_claim_line_id IN NUMBER) IS
2938 SELECT NVL(source_object_id, FND_API.g_miss_num)
2939 , NVL(source_object_class, FND_API.g_miss_char)
2940 , NVL(source_object_type_id, FND_API.g_miss_num)
2941 , NVL(plan_id, FND_API.g_miss_num)
2942 , NVL(item_id, FND_API.g_miss_num)
2943 , NVL(item_description, FND_API.g_miss_char)
2944 , NVL(activity_type, FND_API.g_miss_char)
2945 , NVL(activity_id, FND_API.g_miss_num)
2946 , earnings_associated_flag
2947 FROM ozf_claim_lines
2948 WHERE claim_line_id = cv_claim_line_id;
2949 BEGIN
2950 x_return_status := FND_API.g_ret_sts_success;
2951
2952 -- get old claim line values
2953 OPEN csr_line_lock_items(p_claim_line_rec.claim_line_id);
2954 FETCH csr_line_lock_items INTO l_source_object_id
2955 , l_source_object_class
2956 , l_source_object_type_id
2957 , l_plan_id
2958 , l_item_id
2959 , l_item_description
2960 , l_activity_type
2961 , l_activity_id
2962 , l_earnings_associated_flag;
2963 CLOSE csr_line_lock_items;
2964
2965 -------------- Lock items when earnings is associated --------------
2966 IF l_earnings_associated_flag = FND_API.g_true THEN
2967 IF NVL(p_claim_line_rec.source_object_id,FND_API.g_miss_num) <> l_source_object_id THEN
2968 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2969 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
2970 FND_MESSAGE.set_token('LOCK', 'Name OR Line / Product');
2971 FND_MSG_PUB.add;
2972 END IF;
2973 x_return_status := FND_API.g_ret_sts_error;
2974 RETURN;
2975 END IF;
2976 IF NVL(p_claim_line_rec.source_object_class,FND_API.g_miss_char) <> l_source_object_class THEN
2977 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2978 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
2979 FND_MESSAGE.set_token('LOCK', 'Type');
2980 FND_MSG_PUB.add;
2981 END IF;
2982 x_return_status := FND_API.g_ret_sts_error;
2983 RETURN;
2984 END IF;
2985 IF NVL(p_claim_line_rec.source_object_type_id,FND_API.g_miss_num) <> l_source_object_type_id THEN
2986 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2987 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
2988 FND_MESSAGE.set_token('LOCK', 'Type');
2989 FND_MSG_PUB.add;
2990 END IF;
2991 x_return_status := FND_API.g_ret_sts_error;
2992 RETURN;
2993 END IF;
2994 IF NVL(p_claim_line_rec.plan_id,FND_API.g_miss_num) <> l_plan_id THEN
2995 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2996 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
2997 FND_MESSAGE.set_token('LOCK', 'Type');
2998 FND_MSG_PUB.add;
2999 END IF;
3000 x_return_status := FND_API.g_ret_sts_error;
3001 RETURN;
3002 END IF;
3003 IF NVL(p_claim_line_rec.item_id,FND_API.g_miss_num) <> l_item_id THEN
3004 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3005 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
3006 FND_MESSAGE.set_token('LOCK', 'Line / Product');
3007 FND_MSG_PUB.add;
3008 END IF;
3009 x_return_status := FND_API.g_ret_sts_error;
3010 RETURN;
3011 END IF;
3012 /*
3013 IF NVL(p_claim_line_rec.item_description,FND_API.g_miss_char) <> l_item_description THEN
3014 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3015 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
3016 FND_MESSAGE.set_token('LOCK', 'ITEM_DESCRIPTION');
3017 FND_MSG_PUB.add;
3018 END IF;
3019 x_return_status := FND_API.g_ret_sts_error;
3020 RETURN;
3021 END IF;
3022 */
3023 IF NVL(p_claim_line_rec.activity_type,FND_API.g_miss_char) <> l_activity_type THEN
3024 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3025 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
3026 FND_MESSAGE.set_token('LOCK', 'Name');
3027 FND_MSG_PUB.add;
3028 END IF;
3029 x_return_status := FND_API.g_ret_sts_error;
3030 RETURN;
3031 END IF;
3032 IF NVL(p_claim_line_rec.activity_id,FND_API.g_miss_num) <> l_activity_id THEN
3033 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3034 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_LOCK');
3035 FND_MESSAGE.set_token('LOCK', 'Name');
3036 FND_MSG_PUB.add;
3037 END IF;
3038 x_return_status := FND_API.g_ret_sts_error;
3039 RETURN;
3040 END IF;
3041 END IF;
3042
3043 END Check_Claim_Line_Lock_Items;
3044
3045
3046 ---------------------------------------------------------------------
3047 -- PROCEDURE
3048 -- Update_Claim_Line_Tbl
3049 --
3050 -- HISTORY
3051 -- 02/02/2001 mchang Create.
3052 -- 03/28/2001 mchang add passing_in parameter: p_utiz_obj_ver_tbl
3053 -- 04/30/2001 mchang remove passing_in parameter: p_utiz_obj_ver_tbl
3054 -- 23/01/2002 slkrishn modified amount checking conditions
3055 ---------------------------------------------------------------------
3056 PROCEDURE Update_Claim_Line_Tbl(
3057 p_api_version IN NUMBER
3058 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
3059 ,p_commit IN VARCHAR2 := FND_API.g_false
3060 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
3061
3062 ,x_return_status OUT NOCOPY VARCHAR2
3063 ,x_msg_data OUT NOCOPY VARCHAR2
3064 ,x_msg_count OUT NOCOPY NUMBER
3065
3066 ,p_claim_line_tbl IN claim_line_tbl_type
3067 ,p_change_object_version IN VARCHAR2 := FND_API.g_false
3068 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
3069
3070 ,x_error_index OUT NOCOPY NUMBER
3071 ) IS
3072 l_api_version CONSTANT NUMBER := 1.0;
3073 l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Line_Tbl';
3074 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3075
3076 l_return_status VARCHAR2(1);
3077 l_msg_count NUMBER;
3078 l_msg_data VARCHAR2(4000);
3079
3080 l_claim_line_rec claim_line_rec_type;
3081 l_claim_line_tbl claim_line_tbl_type;
3082 l_object_version NUMBER;
3083
3084 l_old_upd_total_amt NUMBER := 0;
3085 l_new_upd_total_amt NUMBER := 0;
3086 l_exist_total_amt NUMBER;
3087 l_old_line_amt NUMBER;
3088 l_claim_amount NUMBER;
3089 l_claim_class VARCHAR2(30);
3090 l_claim_id NUMBER;
3091 l_currency_code VARCHAR2(15);
3092 l_access VARCHAR2(1) := 'N';
3093 l_offer_id NUMBER;
3094
3095 l_tot_quantity NUMBER := 0;
3096 l_inv_quantity NUMBER := 0;
3097
3098 CURSOR csr_line_obj_ver(cv_claim_line_id IN NUMBER) IS
3099 SELECT object_version_number
3100 FROM ozf_claim_lines
3101 WHERE claim_line_id = cv_claim_line_id;
3102
3103 --ER#9453443
3104 CURSOR c_claim(cv_claim_id IN NUMBER) IS
3105 SELECT amount_remaining
3106 , set_of_books_id
3107 , currency_code
3108 , exchange_rate_type
3109 , exchange_rate_date
3110 , exchange_rate
3111 , claim_class
3112 , offer_id
3113 FROM ozf_claims
3114 WHERE claim_id = cv_claim_id;
3115
3116 -- Bug#9957893 (+)
3117 CURSOR c_old_line_qty(cv_src_obj_line_id IN NUMBER) IS
3118 SELECT quantity_invoiced
3119 FROM ra_customer_trx_lines_all
3120 WHERE customer_trx_line_id = cv_src_obj_line_id;
3121 -- Bug#9957893 (-)
3122
3123
3124 CURSOR c_old_line_amt(cv_claim_line_id IN NUMBER) IS
3125 SELECT claim_currency_amount, currency_code
3126 FROM ozf_claim_lines
3127 WHERE claim_line_id = cv_claim_line_id;
3128
3129 CURSOR c_exist_total_line_amt(cv_claim_id IN NUMBER) IS
3130 SELECT NVL(SUM(claim_currency_amount), 0)
3131 FROM ozf_claim_lines
3132 WHERE claim_id = cv_claim_id;
3133
3134 BEGIN
3135 --------------------- initialize -----------------------
3136 SAVEPOINT Update_Claim_Line_Tbl;
3137
3138 IF OZF_DEBUG_HIGH_ON THEN
3139 OZF_Utility_PVT.debug_message(l_full_name||': start');
3140 END IF;
3141
3142 IF FND_API.to_boolean(p_init_msg_list) THEN
3143 FND_MSG_PUB.initialize;
3144 END IF;
3145
3146 IF NOT FND_API.compatible_api_call(
3147 l_api_version,
3148 p_api_version,
3149 l_api_name,
3150 g_pkg_name
3151 ) THEN
3152 RAISE FND_API.g_exc_unexpected_error;
3153 END IF;
3154
3155 x_return_status := FND_API.g_ret_sts_success;
3156
3157 l_claim_line_tbl := p_claim_line_tbl;
3158
3159 ---------------------- check claim access ------------------------
3160 IF p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode AND p_claim_line_tbl.count > 0 THEN
3161 FOR j IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
3162 IF p_claim_line_tbl.EXISTS(j) THEN
3163 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
3164 P_Api_Version_Number => 1.0
3165 , P_Init_Msg_List => FND_API.G_FALSE
3166 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3167 , P_Commit => FND_API.G_FALSE
3168 , P_object_id => p_claim_line_tbl(j).claim_id
3169 , P_object_type => G_CLAIM_OBJECT_TYPE
3170 , P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
3171 , X_Return_Status => l_return_status
3172 , X_Msg_Count => l_msg_count
3173 , X_Msg_Data => l_msg_data
3174 , X_access => l_access);
3175
3176 IF l_access = 'N' THEN
3177 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3178 FND_MESSAGE.set_name('OZF','OZF_CLAIM_NO_ACCESS');
3179 FND_MSG_PUB.add;
3180 END IF;
3181 RAISE FND_API.G_EXC_ERROR;
3182 END IF;
3183 EXIT;
3184 END IF;
3185 END LOOP;
3186 END IF;
3187
3188 ------------ Default Claim Line by Settlement Method ----------------
3189 OZF_CLAIM_SETTLEMENT_VAL_PVT.Default_Claim_Line_Tbl(
3190 p_api_version => l_api_version
3191 ,p_init_msg_list => FND_API.g_false
3192 ,p_validation_level => p_validation_level
3193 ,x_return_status => l_return_status
3194 ,x_msg_data => x_msg_data
3195 ,x_msg_count => x_msg_count
3196 ,p_x_claim_line_tbl => l_claim_line_tbl
3197 );
3198 IF l_return_status = fnd_api.g_ret_sts_error THEN
3199 RAISE FND_API.g_exc_error;
3200 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3201 RAISE FND_API.g_exc_unexpected_error;
3202 END IF;
3203
3204 --------------------- Amount Checking -----------------------
3205
3206 IF l_claim_line_tbl.count > 0 THEN
3207
3208 --ER#9453443
3209 OPEN c_claim(l_claim_line_tbl(1).claim_id);
3210 FETCH c_claim INTO l_claim_amount
3211 , G_CLAIM_SET_OF_BOOKS_ID
3212 , G_CLAIM_CURRENCY
3213 , G_CLAIM_EXC_TYPE
3214 , G_CLAIM_EXC_DATE
3215 , G_CLAIM_EXC_RATE
3216 , l_claim_class
3217 , l_offer_id;
3218 CLOSE c_claim;
3219
3220
3221 OPEN c_exist_total_line_amt(l_claim_line_tbl(1).claim_id);
3222 FETCH c_exist_total_line_amt INTO l_exist_total_amt;
3223 CLOSE c_exist_total_line_amt;
3224
3225 FOR j IN l_claim_line_tbl.FIRST..l_claim_line_tbl.LAST LOOP
3226 IF l_claim_line_tbl.EXISTS(j) THEN
3227 OPEN c_old_line_amt(l_claim_line_tbl(j).claim_line_id);
3228 FETCH c_old_line_amt INTO l_old_line_amt, l_currency_code;
3229 CLOSE c_old_line_amt;
3230
3231 -- Bug#9957893 (+)
3232 -- Fix for Bug 10383102: Added a check for source_object_line_id
3233 IF l_claim_line_tbl(j).source_object_class = 'INVOICE' AND
3234 l_claim_line_tbl(j).source_object_line_id IS NOT NULL AND
3235 l_claim_line_tbl(j).source_object_line_id <> FND_API.G_MISS_NUM THEN
3236
3237 l_tot_quantity := l_claim_line_tbl(j).quantity;
3238
3239 FOR m IN j+1..l_claim_line_tbl.LAST LOOP
3240 IF l_claim_line_tbl(j).source_object_line_id = l_claim_line_tbl(m).source_object_line_id THEN
3241 l_tot_quantity := l_tot_quantity + l_claim_line_tbl(m).quantity;
3242 END IF;
3243 END LOOP;
3244
3245 OPEN c_old_line_qty(l_claim_line_tbl(j).source_object_line_id);
3246 FETCH c_old_line_qty INTO l_inv_quantity;
3247 CLOSE c_old_line_qty;
3248
3249 IF OZF_DEBUG_HIGH_ON THEN
3250 OZF_Utility_PVT.debug_message('Old Quantity from ra_customer_trx_lines_all is ' || l_inv_quantity);
3251 OZF_Utility_PVT.debug_message('Current Claim Line Quantity is ' || l_claim_line_tbl(j).quantity);
3252 OZF_Utility_PVT.debug_message('Total Quantity is ' || l_tot_quantity);
3253 END IF;
3254
3255 IF l_inv_quantity < l_tot_quantity THEN
3256 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3257 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_INV_QTY');
3258 FND_MSG_PUB.add;
3259 END IF;
3260 x_error_index := j;
3261 RAISE FND_API.g_exc_error;
3262 END IF;
3263 END IF;
3264 -- Bug#9957893 (-)
3265
3266 -- added by slkrishn
3267 IF (l_claim_line_tbl(j).quantity IS NOT NULL AND
3268 l_claim_line_tbl(j).quantity <> FND_API.g_miss_num)
3269 AND (l_claim_line_tbl(j).rate IS NOT NULL AND
3270 l_claim_line_tbl(j).rate <> FND_API.g_miss_num)
3271 THEN
3272 l_claim_line_tbl(j).claim_currency_amount :=
3273 l_claim_line_tbl(j).quantity * l_claim_line_tbl(j).rate;
3274 END IF;
3275
3276 -------- raise error if claim currency amount is null ----------
3277 IF l_claim_line_tbl(j).claim_currency_amount IS NULL THEN
3278 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3279 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_NULL');
3280 FND_MSG_PUB.add;
3281 END IF;
3282 x_error_index := j;
3283 RAISE FND_API.g_exc_error;
3284 ELSE
3285 IF OZF_DEBUG_HIGH_ON THEN
3286 OZF_Utility_PVT.debug_message('Before CurrRound :: claim line currency_currency_amount = '||l_claim_line_tbl(j).claim_currency_amount);
3287 OZF_Utility_PVT.debug_message('Before CurrRound :: claim currency code = '||l_currency_code);
3288 END IF;
3289 l_claim_line_tbl(j).claim_currency_amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_tbl(j).claim_currency_amount, l_currency_code);
3290 END IF;
3291
3292 --l_new_upd_total_amt := l_new_upd_total_amt + OZF_UTILITY_PVT.CurrRound(l_claim_line_tbl(j).claim_currency_amount, l_currency_code);
3293 l_new_upd_total_amt := l_new_upd_total_amt + l_claim_line_tbl(j).claim_currency_amount;
3294
3295 l_old_upd_total_amt := l_old_upd_total_amt + l_old_line_amt;
3296 --l_claim_id := p_claim_line_tbl(j).claim_id;
3297 END IF;
3298 END LOOP;
3299
3300
3301 IF OZF_DEBUG_HIGH_ON THEN
3302 OZF_Utility_PVT.debug_message('Sign check :: exist_total_amt:' || l_exist_total_amt );
3303 OZF_Utility_PVT.debug_message('Sign check :: old_upd_total_amt:' || l_old_upd_total_amt );
3304 OZF_Utility_PVT.debug_message('Sign check :: new_upd_total_amt:' || l_new_upd_total_amt );
3305 OZF_Utility_PVT.debug_message('Sign check :: claim_amount:' || l_claim_amount );
3306 OZF_Utility_PVT.debug_message('l_offer_id:' || l_offer_id );
3307 END IF;
3308 --Check for the sum of line amount sign. It should be same as that claims remaining amount.
3309 --Skip the check in case of Subsequent Receipt Application.
3310 --ER#9453443 : Added the Offer ID check
3311 IF (l_claim_class <> 'GROUP' AND l_offer_id IS NULL) THEN
3312 IF sign(l_exist_total_amt - l_old_upd_total_amt + l_new_upd_total_amt) <> sign(l_claim_amount)
3313 THEN
3314 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3315 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_SIGN_ERR');
3316 FND_MSG_PUB.add;
3317 END IF;
3318 RAISE FND_API.g_exc_error;
3319 END IF;
3320
3321 END IF;
3322
3323 -- Skip this check if the Claim line is updated from Request.
3324 --ER#9453443 : Added the Offer ID check
3325 IF ((p_mode <> OZF_CLAIM_UTILITY_PVT.g_request_mode OR
3326 l_claim_class <> 'GROUP') AND l_offer_id IS NULL) THEN
3327 IF ABS(l_exist_total_amt - l_old_upd_total_amt + l_new_upd_total_amt) > ABS(l_claim_amount) THEN
3328 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3329 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
3330 FND_MSG_PUB.add;
3331 END IF;
3332 RAISE FND_API.g_exc_error;
3333 END IF;
3334 END IF;
3335
3336 END IF;
3337
3338
3339 --------------------- Update Claim Line Table -----------------------
3340 IF l_claim_line_tbl.count > 0 THEN
3341 FOR i IN l_claim_line_tbl.FIRST..l_claim_line_tbl.LAST LOOP
3342 IF l_claim_line_tbl.EXISTS(i) THEN
3343 l_claim_line_rec := l_claim_line_tbl(i);
3344 IF p_change_object_version = FND_API.g_true THEN
3345 --l_claim_line_rec.object_version_number := l_claim_line_rec.object_version_number + 1;
3346 OPEN csr_line_obj_ver(l_claim_line_tbl(i).claim_line_id);
3347 FETCH csr_line_obj_ver INTO l_claim_line_rec.object_version_number;
3348 CLOSE csr_line_obj_ver;
3349 END IF;
3350 l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
3351
3352 -- Call the update claim line API
3353 Update_Claim_Line(
3354 p_api_version => 1.0
3355 , p_init_msg_list => FND_API.g_false
3356 , p_commit => FND_API.g_false
3357 , p_validation_level => p_validation_level
3358 , x_return_status => l_return_status
3359 , x_msg_data => x_msg_data
3360 , x_msg_count => x_msg_count
3361 , p_claim_line_rec => l_claim_line_rec
3362 , p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
3363 , x_object_version => l_object_version
3364 );
3365 IF l_return_status = fnd_api.g_ret_sts_error THEN
3366 x_error_index := i;
3367 RAISE FND_API.g_exc_error;
3368 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3369 x_error_index := i;
3370 RAISE FND_API.g_exc_unexpected_error;
3371 END IF;
3372 END IF;
3373 END LOOP;
3374 END IF;
3375
3376 ------------------------- finish -------------------------------
3377 IF FND_API.to_boolean(p_commit) THEN
3378 COMMIT;
3379 END IF;
3380
3381 FND_MSG_PUB.count_and_get(
3382 p_encoded => FND_API.g_false,
3383 p_count => x_msg_count,
3384 p_data => x_msg_data
3385 );
3386
3387 IF OZF_DEBUG_HIGH_ON THEN
3388 OZF_Utility_PVT.debug_message(l_full_name ||': end');
3389 END IF;
3390
3391 EXCEPTION
3392 WHEN FND_API.g_exc_error THEN
3393 ROLLBACK TO Update_Claim_Line_Tbl;
3394 x_return_status := FND_API.g_ret_sts_error;
3395 FND_MSG_PUB.count_and_get (
3396 p_encoded => FND_API.g_false
3397 ,p_count => x_msg_count
3398 ,p_data => x_msg_data
3399 );
3400
3401 WHEN FND_API.g_exc_unexpected_error THEN
3402 ROLLBACK TO Update_Claim_Line_Tbl;
3403 x_return_status := FND_API.g_ret_sts_unexp_error ;
3404 FND_MSG_PUB.count_and_get (
3405 p_encoded => FND_API.g_false
3406 ,p_count => x_msg_count
3407 ,p_data => x_msg_data
3408 );
3409
3410 WHEN OTHERS THEN
3411 ROLLBACK TO Update_Claim_Line_Tbl;
3412 x_return_status := FND_API.g_ret_sts_unexp_error ;
3413 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3414 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3415 END IF;
3416 FND_MSG_PUB.count_and_get(
3417 p_encoded => FND_API.g_false
3418 ,p_count => x_msg_count
3419 ,p_data => x_msg_data
3420 );
3421
3422 END Update_Claim_Line_Tbl;
3423
3424
3425 ---------------------------------------------------------------------
3426 -- PROCEDURE
3427 -- Update_Claim_Line
3428 --
3429 -- HISTORY
3430 -- 07/11/2000 mchang Create.
3431 -- 07/31/2000 mchang Add amount checking: Claim amount >= (Existing Lines total-claim line updated)+updated line amount.
3432 -- If Claim amount is different from DB, update Claim amount to new amount.
3433 -- 02/02/2001 mchang Remove passing in parameters - p_claim_amount and p_claim_version.
3434 -- 03/20/2001 mchang Add claim_currency_amount conversion
3435 -- 03/28/2001 mchang add passing_in parameter: p_utiz_obj_ver
3436 -- 04/30/2001 mchang remove passing_in parameter: p_utiz_obj_ver
3437 -- 08/06/2001 mchang convert acctd_amount from claim_currency_amount
3438 -- remove associate earnings by calling OZF_Claim_Accrual_PVT.Delete_Line_Util_Tbl
3439 -- 23/01/2002 slkrishn modified amount checking conditions
3440 -- 07/22/2002 yizhang add p_mode for security check
3441 ----------------------------------------------------------------------
3442 PROCEDURE Update_Claim_Line(
3443 p_api_version IN NUMBER
3444 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
3445 ,p_commit IN VARCHAR2 := FND_API.g_false
3446 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
3447
3448 ,x_return_status OUT NOCOPY VARCHAR2
3449 ,x_msg_count OUT NOCOPY NUMBER
3450 ,x_msg_data OUT NOCOPY VARCHAR2
3451
3452 ,p_claim_line_rec IN claim_line_rec_type
3453 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
3454
3455 ,x_object_version OUT NOCOPY NUMBER
3456 )
3457 IS
3458 -- Cursor to get claim amount
3459 -- ER#9453443
3460 CURSOR c_claim_amount(cv_claim_id IN NUMBER) IS
3461 SELECT amount_remaining
3462 , claim_class, offer_id
3463 FROM ozf_claims
3464 WHERE claim_id = cv_claim_id;
3465
3466 -- cursor to get payment_method
3467 CURSOR c_claim_payment_method(cv_claim_id IN NUMBER) IS
3468 SELECT payment_method
3469 FROM ozf_claims
3470 WHERE claim_id = cv_claim_id;
3471
3472 -- Cursor to sum of line util associations
3473 -- Chage the cursor for bug 7658894
3474 CURSOR csr_claim_line_util_amt(cv_claim_line_id IN NUMBER) IS
3475 SELECT nvl(SUM(acctd_amount),0), nvl(SUM(amount),0)
3476 FROM ozf_claim_lines_util
3477 WHERE claim_line_id = cv_claim_line_id;
3478
3479 -- Cursor to sum of claim line amount
3480 CURSOR c_line_sum_amt(cv_claim_id IN NUMBER, cv_line_id IN NUMBER) IS
3481 SELECT SUM(claim_currency_amount)
3482 FROM ozf_claim_lines
3483 WHERE claim_id = cv_claim_id
3484 AND claim_line_id <> cv_line_id;
3485
3486 -- Cursor to get default exchange_rate data from claim
3487 CURSOR c_claim_default_exc(cv_claim_id IN NUMBER) IS
3488 SELECT set_of_books_id
3489 , currency_code
3490 , exchange_rate_type
3491 , exchange_rate_date
3492 , exchange_rate
3493 FROM ozf_claims
3494 WHERE claim_id = cv_claim_id;
3495
3496 -- fix for bug 5042046
3497 CURSOR csr_function_currency IS
3498 SELECT gs.currency_code
3499 FROM gl_sets_of_books gs
3500 , ozf_sys_parameters org
3501 WHERE org.set_of_books_id = gs.set_of_books_id
3502 AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
3503
3504 -- Cursor to get earnings associated with this line
3505 CURSOR csr_get_lines_util(cv_claim_line_id IN NUMBER) IS
3506 SELECT claim_line_util_id
3507 , object_version_number
3508 , currency_code
3509 FROM ozf_claim_lines_util
3510 WHERE claim_line_id = cv_claim_line_id;
3511
3512 -- Fix for bug 7658894
3513 l_line_util_sum NUMBER :=0;
3514
3515 l_api_version CONSTANT NUMBER := 1.0;
3516 l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Line';
3517 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3518
3519 l_last_updated_by NUMBER;
3520 l_last_update_login NUMBER;
3521
3522 l_claim_line_rec claim_line_rec_type;
3523 l_x_claim_line_rec claim_line_rec_type;
3524 l_return_status VARCHAR2(1);
3525 l_mode VARCHAR2(30);
3526 l_object_version_number NUMBER;
3527 l_old_utilization_id NUMBER;
3528 l_old_line_amount NUMBER;
3529 l_line_util_acc_amt NUMBER;
3530
3531 l_claim_amount NUMBER;
3532 l_claim_class VARCHAR2(30);
3533 l_line_sum_amt NUMBER;
3534 l_claim_currency_amount NUMBER;
3535 l_payment_method VARCHAR2(30);
3536
3537 l_rate NUMBER;
3538
3539 l_tri_denominator NUMBER;
3540 l_tri_numerator NUMBER;
3541 l_tri_rate NUMBER;
3542 l_function_currency VARCHAR2(15);
3543
3544 l_line_util_tbl OZF_Claim_Accrual_PVT.line_util_tbl_type;
3545 l_counter NUMBER :=1;
3546 l_error_index NUMBER;
3547
3548 l_access VARCHAR2(1) := 'N';
3549 -- Added for Rule Based Settlement
3550 l_offer_id NUMBER :=0;
3551
3552 BEGIN
3553 -------------------- initialize -------------------------
3554 SAVEPOINT Update_Claim_Line;
3555
3556 IF OZF_DEBUG_HIGH_ON THEN
3557 OZF_Utility_PVT.debug_message(l_full_name||': start');
3558 END IF;
3559
3560 IF FND_API.to_boolean(p_init_msg_list) THEN
3561 FND_MSG_PUB.initialize;
3562 END IF;
3563
3564 IF NOT FND_API.compatible_api_call(
3565 l_api_version,
3566 p_api_version,
3567 l_api_name,
3568 g_pkg_name
3569 ) THEN
3570 RAISE FND_API.g_exc_unexpected_error;
3571 END IF;
3572
3573 x_return_status := FND_API.g_ret_sts_success;
3574
3575 ---------------------- check claim access ------------------------
3576 IF p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode THEN
3577 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
3578 P_Api_Version_Number => 1.0
3579 , P_Init_Msg_List => FND_API.G_FALSE
3580 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3581 , P_Commit => FND_API.G_FALSE
3582 , P_object_id => p_claim_line_rec.claim_id
3583 , P_object_type => G_CLAIM_OBJECT_TYPE
3584 , P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
3585 , X_Return_Status => l_return_status
3586 , X_Msg_Count => x_msg_count
3587 , X_Msg_Data => x_msg_data
3588 , X_access => l_access);
3589
3590 IF l_access = 'N' THEN
3591 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3592 FND_MESSAGE.set_name('OZF','OZF_CLAIM_NO_ACCESS');
3593 FND_MSG_PUB.add;
3594 END IF;
3595 RAISE FND_API.G_EXC_ERROR;
3596 END IF;
3597 END IF;
3598
3599 l_object_version_number := p_claim_line_rec.object_version_number + 1;
3600 l_last_updated_by := NVL(FND_GLOBAL.user_id,-1);
3601 l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
3602
3603
3604 -- Default UOM for product.
3605 IF l_claim_line_rec.item_type = 'PRODUCT'
3606 AND (l_claim_line_rec.item_id is not null
3607 AND l_claim_line_rec.item_id <> FND_API.G_MISS_NUM )
3608 AND (l_claim_line_rec.quantity_uom is null
3609 OR l_claim_line_rec.quantity_uom = FND_API.G_MISS_CHAR)
3610 THEN
3611 -- Bugfix 5182181
3612 l_claim_line_rec.quantity_uom := Get_Default_Product_UOM
3613 ( p_product_id => l_claim_line_rec.item_id
3614 , p_org_id => FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
3615 );
3616
3617 END IF;
3618
3619
3620 -- item level validation
3621 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3622 Check_Claim_Line_Items(
3623 p_claim_line_rec => l_claim_line_rec,
3624 p_validation_mode => JTF_PLSQL_API.g_update,
3625 x_return_status => l_return_status
3626 );
3627
3628 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3629 RAISE FND_API.g_exc_unexpected_error;
3630 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
3631 RAISE FND_API.g_exc_error;
3632 END IF;
3633 END IF;
3634
3635 -- replace g_miss_char/num/date with current column values
3636 Complete_Claim_Line_Rec(
3637 p_claim_line_rec => p_claim_line_rec
3638 ,x_complete_rec => l_claim_line_rec
3639 );
3640
3641 -- default claim line based on claim settlement method
3642 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
3643 OZF_CLAIM_SETTLEMENT_VAL_PVT.Default_Claim_Line(
3644 p_api_version => l_api_version
3645 ,p_init_msg_list => FND_API.g_false
3646 ,p_validation_level => FND_API.g_valid_level_full
3647 ,x_return_status => l_return_status
3648 ,x_msg_data => x_msg_data
3649 ,x_msg_count => x_msg_count
3650 ,p_x_claim_line_rec => l_claim_line_rec
3651 );
3652 IF l_return_status = FND_API.g_ret_sts_error THEN
3653 RAISE FND_API.g_exc_error;
3654 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3655 RAISE FND_API.g_exc_unexpected_error;
3656 END IF;
3657 END IF;
3658
3659 -- lock line items when earning is associated
3660 Check_Claim_Line_Lock_Items(
3661 p_claim_line_rec => l_claim_line_rec
3662 ,x_return_status => l_return_status
3663 );
3664
3665 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3666 RAISE FND_API.g_exc_unexpected_error;
3667 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
3668 RAISE FND_API.g_exc_error;
3669 END IF;
3670
3671 -- record level validation
3672 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
3673 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
3674 Check_Claim_Line_Record(
3675 p_claim_line_rec => p_claim_line_rec,
3676 p_complete_rec => l_claim_line_rec,
3677 x_return_status => l_return_status
3678 );
3679 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3680 RAISE FND_API.g_exc_unexpected_error;
3681 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
3682 RAISE FND_API.g_exc_error;
3683 END IF;
3684 END IF;
3685 END IF;
3686
3687 -- skip qty*rate calc if it's updating from tbl.
3688 -- updated by slkrishn
3689 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
3690 ------------------ checking quantity * rate -------------------
3691 IF (p_claim_line_rec.quantity IS NOT NULL AND
3692 p_claim_line_rec.quantity <> FND_API.g_miss_num) AND
3693 (p_claim_line_rec.rate IS NOT NULL
3694 AND p_claim_line_rec.rate <> FND_API.g_miss_num)
3695 THEN
3696 l_claim_line_rec.claim_currency_amount := l_claim_line_rec.quantity * l_claim_line_rec.rate;
3697 END IF;
3698
3699 -------- raise error if claim currency amount is null ----------
3700 IF l_claim_line_rec.claim_currency_amount IS NULL THEN
3701 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3702 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_NULL');
3703 FND_MSG_PUB.add;
3704 END IF;
3705 RAISE FND_API.g_exc_error;
3706 END IF;
3707 END IF;
3708
3709 ---------------- get claim exchange data -------------------
3710 -- No need to set global claim exc info if it's updating from tbl
3711 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
3712 OPEN c_claim_default_exc(l_claim_line_rec.claim_id);
3713 FETCH c_claim_default_exc INTO G_CLAIM_SET_OF_BOOKS_ID
3714 , G_CLAIM_CURRENCY
3715 , G_CLAIM_EXC_TYPE
3716 , G_CLAIM_EXC_DATE
3717 , G_CLAIM_EXC_RATE;
3718 CLOSE c_claim_default_exc;
3719 END IF;
3720
3721 OPEN csr_function_currency;
3722 FETCH csr_function_currency INTO l_function_currency;
3723 CLOSE csr_function_currency;
3724
3725 -------------------- convert currency --------------------------------------
3726 -- Note1: Accounted Amount is recalculated at every update to allow for change
3727 -- of amount/exchange rate at the claim header
3728 -- Note2: Amount is recalculated at every update to allow for change of
3729 -- amount/exchange rate at the claim header and change of source object
3730 -- information.
3731 -- Modified for Bug4437696
3732
3733 IF OZF_DEBUG_HIGH_ON THEN
3734 OZF_Utility_PVT.debug_message('l_claim_line_rec.claim_line_id =' ||l_claim_line_rec.claim_line_id);
3735 END IF;
3736
3737 OPEN csr_claim_line_util_amt(l_claim_line_rec.claim_line_id);
3738 FETCH csr_claim_line_util_amt INTO l_line_util_acc_amt,l_line_util_sum;
3739 CLOSE csr_claim_line_util_amt;
3740
3741 IF OZF_DEBUG_HIGH_ON THEN
3742 OZF_Utility_PVT.debug_message('l_line_util_acc_amt =' ||l_line_util_acc_amt);
3743 OZF_Utility_PVT.debug_message('l_line_util_sum =' ||l_line_util_sum);
3744 OZF_Utility_PVT.debug_message('l_claim_line_rec.claim_currency_amount =' ||l_claim_line_rec.claim_currency_amount);
3745 END IF;
3746
3747 IF (p_claim_line_rec.acctd_amount IS NULL OR p_claim_line_rec.acctd_amount = FND_API.g_miss_num) THEN
3748 IF l_claim_line_rec.claim_currency_amount IS NULL THEN
3749 l_claim_line_rec.acctd_amount := NULL;
3750 l_claim_line_rec.amount := NULL;
3751 ELSE
3752
3753 -- fix for bug 7658894
3754 IF (l_line_util_sum = l_claim_line_rec.claim_currency_amount) THEN
3755 l_claim_line_rec.acctd_amount := l_line_util_acc_amt;
3756
3757 ELSE
3758
3759 -- Convert ACCTD_AMOUNT
3760 OZF_UTILITY_PVT.Convert_Currency(
3761 P_SET_OF_BOOKS_ID => G_CLAIM_SET_OF_BOOKS_ID,
3762 P_FROM_CURRENCY => G_CLAIM_CURRENCY,
3763 P_CONVERSION_DATE => G_CLAIM_EXC_DATE,
3764 P_CONVERSION_TYPE => G_CLAIM_EXC_TYPE,
3765 P_CONVERSION_RATE => G_CLAIM_EXC_RATE,
3766 P_AMOUNT => l_claim_line_rec.claim_currency_amount,
3767 X_RETURN_STATUS => l_return_status,
3768 X_ACC_AMOUNT => l_claim_line_rec.acctd_amount,
3769 X_RATE => l_rate
3770 );
3771 IF l_return_status = FND_API.g_ret_sts_error THEN
3772 RAISE FND_API.g_exc_error;
3773 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3774 RAISE FND_API.g_exc_unexpected_error;
3775 END IF;
3776 END IF;
3777
3778 -- Convert Line Amount
3779 -- For Price Protection
3780 -- Fix for 7443072
3781 -- Bugfix 7811671
3782 IF ((l_claim_line_rec.source_object_class IS NULL OR l_claim_line_rec.source_object_class IN('PPCUSTOMER','PPVENDOR','PPINCVENDOR','SD_SUPPLIER'))
3783 AND l_claim_line_rec.source_object_id IS NULL) THEN
3784 IF (p_claim_line_rec.claim_currency_amount <> FND_API.g_miss_num
3785 AND p_claim_line_rec.claim_currency_amount IS NOT NULL ) THEN
3786 l_claim_line_rec.currency_code := G_CLAIM_CURRENCY;
3787 l_claim_line_rec.exchange_rate_type := G_CLAIM_EXC_TYPE;
3788 l_claim_line_rec.exchange_rate_date := G_CLAIM_EXC_DATE;
3789 l_claim_line_rec.exchange_rate := G_CLAIM_EXC_RATE;
3790 l_claim_line_rec.amount := l_claim_line_rec.claim_currency_amount;
3791 END IF;
3792 --Fix for Bug 14808223 - Added ELSIF conditon
3793 ELSIF (l_claim_line_rec.source_object_class IS NOT NULL AND l_claim_line_rec.source_object_id IS NULL) THEN
3794 IF (p_claim_line_rec.claim_currency_amount <> FND_API.g_miss_num
3795 AND p_claim_line_rec.claim_currency_amount IS NOT NULL ) THEN
3796 l_claim_line_rec.currency_code := G_CLAIM_CURRENCY;
3797 l_claim_line_rec.exchange_rate_type := G_CLAIM_EXC_TYPE;
3798 l_claim_line_rec.exchange_rate_date := G_CLAIM_EXC_DATE;
3799 l_claim_line_rec.exchange_rate := G_CLAIM_EXC_RATE;
3800 l_claim_line_rec.amount := l_claim_line_rec.claim_currency_amount;
3801 END IF;
3802 ELSE
3803 Convert_Line_Amount(
3804 p_claim_line_rec => l_claim_line_rec
3805 ,x_claim_line_rec => l_x_claim_line_rec
3806 ,x_return_status => l_return_status
3807 );
3808 IF l_return_status = FND_API.g_ret_sts_error THEN
3809 RAISE FND_API.g_exc_error;
3810 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3811 RAISE FND_API.g_exc_unexpected_error;
3812 END IF;
3813 l_claim_line_rec := l_x_claim_line_rec;
3814 END IF;
3815 END IF;
3816
3817 END IF;
3818
3819 ------------------- amount rounding --------------------
3820 IF l_claim_line_rec.claim_currency_amount IS NOT NULL THEN
3821 l_claim_line_rec.claim_currency_amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_rec.claim_currency_amount, G_CLAIM_CURRENCY);
3822 END IF;
3823
3824 IF l_claim_line_rec.amount IS NOT NULL THEN
3825 l_claim_line_rec.amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_rec.amount, l_claim_line_rec.currency_code);
3826 END IF;
3827
3828 IF l_claim_line_rec.acctd_amount IS NOT NULL THEN
3829 l_claim_line_rec.acctd_amount := OZF_UTILITY_PVT.CurrRound(l_claim_line_rec.acctd_amount, l_function_currency);
3830 END IF;
3831
3832 -------------------- amount checking --------------------------------
3833 IF l_claim_line_rec.claim_currency_amount IS NOT NULL THEN
3834 -- get claim amount from database (amount_remaining)
3835 OPEN c_claim_amount(l_claim_line_rec.claim_id);
3836 FETCH c_claim_amount INTO l_claim_amount, l_claim_class,l_offer_id;
3837 CLOSE c_claim_amount;
3838
3839 -- -------------------------------------------------------------------------------------------
3840 -- Bug : 2781186
3841 -- Changed by : (uday poluri) Date: 03-Jun-2003
3842 -- Comments : Following if condition (IF l_claim_line_rec.claim_currency_amount <> 0 THEN)
3843 -- is added, because subsequent application can update the claim amount as Zero
3844 -- so as claim_line_amount.
3845 -- -------------------------------------------------------------------------------------------
3846 -- 20-APR-04 Commenting the sign check for the claim amounts, as for a claim negative line amount
3847 -- can be specified to associate negative accruals. Similarily valid for DED/OPM.
3848 --IF l_claim_line_rec.claim_currency_amount <> 0 THEN --Bug:2781186
3849 -- Sign of claim_currency_amount should be the same as claim amount_remaining
3850 --DATE : 03-jun-2003 [Changed for Sign erro in case of partial application for claim investigation.]
3851 -- IF l_claim_amount <> 0 THEN
3852 -- Sign of claim_currency_amount should be the same as claim amount_remaining
3853 -- IF SIGN(l_claim_line_rec.claim_currency_amount) <> SIGN(l_claim_amount) THEN
3854 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3855 -- FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_AMT_SIGN_ERR');
3856 -- FND_MSG_PUB.add;
3857 -- END IF;
3858 -- RAISE FND_API.g_exc_error;
3859 -- END IF;
3860 --END IF;
3861 --END IF; --Bug:2781186
3862
3863 -- skip amount comparison if it's updating from tbl.
3864 -- commenting update_from_tbl_flag since checking does not happen
3865 -- updated by skrishn
3866 -- mchang: open the cursor again to fix BUG#2242644
3867 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
3868 -- get total of existing line amount (sum of claim_currency_amount)
3869 OPEN c_line_sum_amt(l_claim_line_rec.claim_id, l_claim_line_rec.claim_line_id);
3870 FETCH c_line_sum_amt INTO l_line_sum_amt;
3871 CLOSE c_line_sum_amt;
3872
3873 -- -------------------------------------------------------------------------------------------
3874 -- Bug : 2781186
3875 -- Changed by : (Uday Poluri) Date: 03-Jun-2003
3876 -- Comments : Add p_mode check, If it is AUTO then allow amount change on claim.
3877 -- PLEASE check with Michelle about this
3878 -- -------------------------------------------------------------------------------------------
3879
3880 IF OZF_DEBUG_HIGH_ON THEN
3881 OZF_Utility_PVT.debug_message('l_offer_id =' ||l_offer_id);
3882 END IF;
3883 -- ER#9453443 : Added the offer id check
3884 IF p_mode <> OZF_claim_Utility_pvt.G_AUTO_MODE
3885 AND p_mode <> OZF_claim_Utility_pvt.G_REQUEST_MODE
3886 AND l_claim_class <> 'GROUP' AND l_offer_id IS NULL
3887 THEN --Bug:2781186
3888 -- comparison of claim amount and line amount
3889 IF l_line_sum_amt IS NOT NULL THEN
3890 IF ABS(l_line_sum_amt + l_claim_line_rec.claim_currency_amount) > ABS(l_claim_amount) THEN
3891 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3892 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
3893 FND_MSG_PUB.add;
3894 END IF;
3895 RAISE FND_API.g_exc_error;
3896 END IF;
3897 ELSIF ABS(l_claim_line_rec.claim_currency_amount) > ABS(l_claim_amount) THEN
3898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3899 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_EXCESS_AMOUNT');
3900 FND_MSG_PUB.add;
3901 END IF;
3902 RAISE FND_API.g_exc_error;
3903 END IF;
3904 END IF;
3905 END IF;
3906 END IF;
3907
3908 ---------------- check associate earnings flag ------------------------
3909 IF p_claim_line_rec.earnings_associated_flag = FND_API.g_false THEN
3910 -- remove associated earnings
3911 OPEN csr_get_lines_util(l_claim_line_rec.claim_line_id);
3912 LOOP
3913 FETCH csr_get_lines_util INTO l_line_util_tbl(l_counter).claim_line_util_id
3914 , l_line_util_tbl(l_counter).object_version_number
3915 , l_line_util_tbl(l_counter).currency_code;
3916 EXIT WHEN csr_get_lines_util%NOTFOUND;
3917 l_line_util_tbl(l_counter).claim_line_id := l_claim_line_rec.claim_line_id;
3918 l_counter := l_counter + 1;
3919 END LOOP;
3920 CLOSE csr_get_lines_util;
3921
3922 IF l_counter > 1 THEN
3923 OZF_Claim_Accrual_PVT.Delete_Line_Util_Tbl(
3924 p_api_version => l_api_version
3925 ,p_init_msg_list => FND_API.g_false
3926 ,p_commit => FND_API.g_false
3927 ,p_validation_level => FND_API.g_valid_level_full
3928 ,x_return_status => l_return_status
3929 ,x_msg_data => x_msg_data
3930 ,x_msg_count => x_msg_count
3931 ,p_line_util_tbl => l_line_util_tbl
3932 ,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
3933 ,x_error_index => l_error_index
3934 );
3935 IF l_return_status = fnd_api.g_ret_sts_error THEN
3936 RAISE FND_API.g_exc_error;
3937 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3938 RAISE FND_API.g_exc_unexpected_error;
3939 END IF;
3940 END IF;
3941
3942 ELSE
3943 ------- check line acctd amount is more then earnings acctd amount -------
3944
3945 IF ABS(nvl(l_claim_line_rec.acctd_amount,0)) < ABS(l_line_util_acc_amt) THEN
3946 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3947 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_EXCESS_LINE_AMT');
3948 FND_MSG_PUB.add;
3949 END IF;
3950 RAISE FND_API.g_exc_error;
3951 END IF;
3952
3953 END IF;
3954
3955 -- validate claim line record based on claim settlement method
3956 IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
3957 OZF_CLAIM_SETTLEMENT_VAL_PVT.Validate_Claim_Line(
3958 p_api_version => l_api_version
3959 ,p_init_msg_list => FND_API.g_false
3960 ,p_validation_level => FND_API.g_valid_level_full
3961 ,x_return_status => l_return_status
3962 ,x_msg_data => x_msg_data
3963 ,x_msg_count => x_msg_count
3964 ,p_claim_line_rec => l_claim_line_rec
3965 );
3966 IF l_return_status = FND_API.g_ret_sts_error THEN
3967 RAISE FND_API.g_exc_error;
3968 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3969 RAISE FND_API.g_exc_unexpected_error;
3970 END IF;
3971 END IF;
3972
3973
3974
3975 -- Bug4489415: Make the Tax Call
3976 IF l_claim_line_rec.tax_action IS NOT NULL AND
3977 l_claim_line_rec.amount IS NOT NULL THEN
3978
3979 OPEN c_claim_payment_method(l_claim_line_rec.claim_id);
3980 FETCH c_claim_payment_method INTO l_payment_method;
3981 CLOSE c_claim_payment_method;
3982
3983 IF OZF_DEBUG_HIGH_ON THEN
3984 OZF_Utility_PVT.debug_message(' Tax Action =' ||l_claim_line_rec.tax_action);
3985 OZF_Utility_PVT.debug_message(' Payment Method =' ||l_payment_method);
3986 END IF;
3987
3988 IF l_payment_method IS NOT NULL THEN
3989
3990 OZF_CLAIM_TAX_PVT.Calculate_Claim_Line_Tax(
3991 p_api_version => l_api_version
3992 ,p_init_msg_list => FND_API.g_false
3993 ,p_validation_level => FND_API.g_valid_level_full
3994 ,x_return_status => l_return_status
3995 ,x_msg_data => x_msg_data
3996 ,x_msg_count => x_msg_count
3997 ,p_x_claim_line_rec => l_claim_line_rec
3998 );
3999 IF l_return_status = FND_API.g_ret_sts_error THEN
4000 RAISE FND_API.g_exc_error;
4001 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4002 RAISE FND_API.g_exc_unexpected_error;
4003 END IF;
4004
4005 END IF;
4006
4007 END IF;
4008 --//Bugfix: 8829808
4009 l_object_version_number := l_claim_line_rec.object_version_number + 1;
4010 -------------------------- update --------------------
4011 IF OZF_DEBUG_HIGH_ON THEN
4012 OZF_Utility_PVT.debug_message(l_full_name ||': update');
4013 END IF;
4014
4015 UPDATE ozf_claim_lines_all SET
4016 object_version_number = l_object_version_number,
4017 last_update_date = SYSDATE,
4018 last_updated_by = l_last_updated_by,
4019 last_update_login = l_last_update_login,
4020 request_id = FND_GLOBAL.CONC_REQUEST_ID,
4021 program_application_id = FND_GLOBAL.PROG_APPL_ID,
4022 program_update_date = SYSDATE,
4023 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
4024 created_from = l_claim_line_rec.created_from,
4025 claim_id = l_claim_line_rec.claim_id,
4026 line_number = l_claim_line_rec.line_number,
4027 split_from_claim_line_id = l_claim_line_rec.split_from_claim_line_id,
4028 amount = l_claim_line_rec.amount,
4029 claim_currency_amount = l_claim_line_rec.claim_currency_amount,
4030 acctd_amount = l_claim_line_rec.acctd_amount,
4031 currency_code = l_claim_line_rec.currency_code,
4032 exchange_rate_type = l_claim_line_rec.exchange_rate_type,
4033 exchange_rate_date = l_claim_line_rec.exchange_rate_date,
4034 exchange_rate = l_claim_line_rec.exchange_rate,
4035 set_of_books_id = l_claim_line_rec.set_of_books_id,
4036 valid_flag = l_claim_line_rec.valid_flag,
4037 source_object_id = l_claim_line_rec.source_object_id,
4038 source_object_line_id = l_claim_line_rec.source_object_line_id,
4039 source_object_class = l_claim_line_rec.source_object_class,
4040 source_object_type_id = l_claim_line_rec.source_object_type_id,
4041 plan_id = l_claim_line_rec.plan_id,
4042 offer_id = l_claim_line_rec.offer_id,
4043 utilization_id = l_claim_line_rec.utilization_id,
4044 payment_method = l_claim_line_rec.payment_method,
4045 payment_reference_id = l_claim_line_rec.payment_reference_id,
4046 payment_reference_number = l_claim_line_rec.payment_reference_number,
4047 payment_reference_date = l_claim_line_rec.payment_reference_date,
4048 voucher_id = l_claim_line_rec.voucher_id,
4049 voucher_number = l_claim_line_rec.voucher_number,
4050 payment_status = l_claim_line_rec.payment_status,
4051 approved_flag = l_claim_line_rec.approved_flag,
4052 approved_date = l_claim_line_rec.approved_date,
4053 approved_by = l_claim_line_rec.approved_by,
4054 settled_date = l_claim_line_rec.settled_date,
4055 settled_by = l_claim_line_rec.settled_by,
4056 performance_complete_flag = l_claim_line_rec.performance_complete_flag,
4057 performance_attached_flag = l_claim_line_rec.performance_attached_flag,
4058 select_cust_children_flag = l_claim_line_rec.select_cust_children_flag,
4059 item_id = l_claim_line_rec.item_id,
4060 item_description = l_claim_line_rec.item_description,
4061 quantity = l_claim_line_rec.quantity,
4062 quantity_uom = l_claim_line_rec.quantity_uom,
4063 rate = l_claim_line_rec.rate,
4064 activity_type = l_claim_line_rec.activity_type,
4065 activity_id = l_claim_line_rec.activity_id,
4066 related_cust_account_id = l_claim_line_rec.related_cust_account_id,
4067 buy_group_cust_account_id = l_claim_line_rec.buy_group_cust_account_id,
4068 relationship_type = l_claim_line_rec.relationship_type,
4069 earnings_associated_flag = l_claim_line_rec.earnings_associated_flag,
4070 comments = l_claim_line_rec.comments,
4071 tax_code = l_claim_line_rec.tax_code,
4072 credit_to = l_claim_line_rec.credit_to,
4073 attribute_category = l_claim_line_rec.attribute_category,
4074 attribute1 = l_claim_line_rec.attribute1,
4075 attribute2 = l_claim_line_rec.attribute2,
4076 attribute3 = l_claim_line_rec.attribute3,
4077 attribute4 = l_claim_line_rec.attribute4,
4078 attribute5 = l_claim_line_rec.attribute5,
4079 attribute6 = l_claim_line_rec.attribute6,
4080 attribute7 = l_claim_line_rec.attribute7,
4081 attribute8 = l_claim_line_rec.attribute8,
4082 attribute9 = l_claim_line_rec.attribute9,
4083 attribute10 = l_claim_line_rec.attribute10,
4084 attribute11 = l_claim_line_rec.attribute11,
4085 attribute12 = l_claim_line_rec.attribute12,
4086 attribute13 = l_claim_line_rec.attribute13,
4087 attribute14 = l_claim_line_rec.attribute14,
4088 attribute15 = l_claim_line_rec.attribute15,
4089 sale_date = l_claim_line_rec.sale_date,
4090 item_type = l_claim_line_rec.item_type,
4091 tax_amount = l_claim_line_rec.tax_amount,
4092 acctd_tax_amount = l_claim_line_rec.acctd_tax_amount,
4093 claim_curr_tax_amount = l_claim_line_rec.claim_curr_tax_amount,
4094 activity_line_id = l_claim_line_rec.activity_line_id,
4095 offer_type = l_claim_line_rec.offer_type,
4096 prorate_earnings_flag = l_claim_line_rec.prorate_earnings_flag,
4097 earnings_end_date = l_claim_line_rec.earnings_end_date,
4098 buy_group_party_id = l_claim_line_rec.buy_group_party_id,
4099 --12.1 Enhancement : Price Protection
4100 dpp_cust_account_id = l_claim_line_rec.dpp_cust_account_id,
4101 activity_name = l_claim_line_rec.activity_name-- Hbandi added code for resolving the bug 10164003
4102
4103 WHERE claim_line_id = l_claim_line_rec.claim_line_id
4104 AND object_version_number = l_claim_line_rec.object_version_number;
4105
4106 IF (SQL%NOTFOUND) THEN
4107 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4108 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
4109 FND_MSG_PUB.add;
4110 END IF;
4111 RAISE FND_API.g_exc_error;
4112 END IF;
4113
4114 -------------------- finish --------------------------
4115 x_object_version := l_object_version_number;
4116
4117 -- Check for commit
4118 IF FND_API.to_boolean(p_commit) THEN
4119 COMMIT;
4120 END IF;
4121
4122 FND_MSG_PUB.count_and_get(
4123 p_encoded => FND_API.g_false,
4124 p_count => x_msg_count,
4125 p_data => x_msg_data
4126 );
4127
4128 IF OZF_DEBUG_HIGH_ON THEN
4129 OZF_Utility_PVT.debug_message(l_full_name ||': end');
4130 END IF;
4131
4132 EXCEPTION
4133 WHEN FND_API.g_exc_error THEN
4134 ROLLBACK TO Update_Claim_Line;
4135 x_return_status := FND_API.g_ret_sts_error;
4136 FND_MSG_PUB.count_and_get(
4137 p_encoded => FND_API.g_false,
4138 p_count => x_msg_count,
4139 p_data => x_msg_data
4140 );
4141
4142 WHEN FND_API.g_exc_unexpected_error THEN
4143 ROLLBACK TO Update_Claim_Line;
4144 x_return_status := FND_API.g_ret_sts_unexp_error ;
4145 FND_MSG_PUB.count_and_get(
4146 p_encoded => FND_API.g_false,
4147 p_count => x_msg_count,
4148 p_data => x_msg_data
4149 );
4150
4151 WHEN OTHERS THEN
4152 ROLLBACK TO Update_Claim_Line;
4153 x_return_status := FND_API.g_ret_sts_unexp_error ;
4154 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4155 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4156 END IF;
4157 FND_MSG_PUB.count_and_get(
4158 p_encoded => FND_API.g_false,
4159 p_count => x_msg_count,
4160 p_data => x_msg_data
4161 );
4162
4163 END Update_Claim_Line;
4164
4165
4166 --------------------------------------------------------------------
4167 -- PROCEDURE
4168 -- Validate_Claim_Line
4169 --
4170 -- HISTORY
4171 -- 07/11/2000 mchang Create.
4172 --------------------------------------------------------------------
4173 PROCEDURE Validate_Claim_Line(
4174 p_api_version IN NUMBER
4175 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
4176 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
4177
4178 ,x_return_status OUT NOCOPY VARCHAR2
4179 ,x_msg_count OUT NOCOPY NUMBER
4180 ,x_msg_data OUT NOCOPY VARCHAR2
4181
4182 ,p_claim_line_rec IN claim_line_rec_type
4183 )
4184 IS
4185 l_api_version CONSTANT NUMBER := 1.0;
4186 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Claim_Line';
4187 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4188
4189 l_return_status VARCHAR2(1);
4190
4191 BEGIN
4192 ----------------------- initialize --------------------
4193 IF OZF_DEBUG_HIGH_ON THEN
4194 OZF_Utility_PVT.debug_message(l_full_name||': start');
4195 END IF;
4196
4197 IF FND_API.to_boolean(p_init_msg_list) THEN
4198 FND_MSG_PUB.initialize;
4199 END IF;
4200
4201 IF NOT FND_API.compatible_api_call(
4202 l_api_version,
4203 p_api_version,
4204 l_api_name,
4205 g_pkg_name
4206 ) THEN
4207 RAISE FND_API.g_exc_unexpected_error;
4208 END IF;
4209
4210 x_return_status := FND_API.g_ret_sts_success;
4211
4212 ---------------------- validate ------------------------
4213 IF OZF_DEBUG_HIGH_ON THEN
4214 OZF_Utility_PVT.debug_message(l_full_name||': check items');
4215 END IF;
4216
4217 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
4218 Check_Claim_Line_Items(
4219 p_claim_line_rec => p_claim_line_rec,
4220 p_validation_mode => JTF_PLSQL_API.g_create,
4221 x_return_status => l_return_status
4222 );
4223
4224 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4225 RAISE FND_API.g_exc_unexpected_error;
4226 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
4227 RAISE FND_API.g_exc_error;
4228 END IF;
4229 END IF;
4230
4231 IF OZF_DEBUG_HIGH_ON THEN
4232 OZF_Utility_PVT.debug_message(l_full_name||': check record');
4233 END IF;
4234
4235 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
4236 Check_Claim_Line_Record(
4237 p_claim_line_rec => p_claim_line_rec,
4238 p_complete_rec => NULL,
4239 x_return_status => l_return_status
4240 );
4241
4242 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4243 RAISE FND_API.g_exc_unexpected_error;
4244 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
4245 RAISE FND_API.g_exc_error;
4246 END IF;
4247 END IF;
4248
4249 -------------------- finish --------------------------
4250 FND_MSG_PUB.count_and_get(
4251 p_encoded => FND_API.g_false,
4252 p_count => x_msg_count,
4253 p_data => x_msg_data
4254 );
4255
4256 IF OZF_DEBUG_HIGH_ON THEN
4257 OZF_Utility_PVT.debug_message(l_full_name ||': end');
4258 END IF;
4259
4260 EXCEPTION
4261 WHEN FND_API.g_exc_error THEN
4262 x_return_status := FND_API.g_ret_sts_error;
4263 FND_MSG_PUB.count_and_get(
4264 p_encoded => FND_API.g_false,
4265 p_count => x_msg_count,
4266 p_data => x_msg_data
4267 );
4268
4269 WHEN FND_API.g_exc_unexpected_error THEN
4270 x_return_status := FND_API.g_ret_sts_unexp_error ;
4271 FND_MSG_PUB.count_and_get(
4272 p_encoded => FND_API.g_false,
4273 p_count => x_msg_count,
4274 p_data => x_msg_data
4275 );
4276
4277 WHEN OTHERS THEN
4278 x_return_status := FND_API.g_ret_sts_unexp_error;
4279 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4280 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
4281 END IF;
4282 FND_MSG_PUB.count_and_get(
4283 p_encoded => FND_API.g_false,
4284 p_count => x_msg_count,
4285 p_data => x_msg_data
4286 );
4287
4288 END Validate_Claim_Line;
4289
4290
4291 ---------------------------------------------------------------------
4292 -- PROCEDURE
4293 -- Check_Claim_Line_Req_Items
4294 --
4295 -- HISTORY
4296 -- 07/11/2000 mchang Create.
4297 ---------------------------------------------------------------------
4298 PROCEDURE Check_Claim_Line_Req_Items(
4299 p_claim_line_rec IN claim_line_rec_type
4300 ,x_return_status OUT NOCOPY VARCHAR2
4301 )
4302 IS
4303 BEGIN
4304
4305 x_return_status := FND_API.g_ret_sts_success;
4306
4307 ------------------------ CLAIM_ID -------------------------------
4308 IF p_claim_line_rec.claim_id <> FND_API.g_miss_num AND
4309 p_claim_line_rec.claim_id IS NULL THEN
4310 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4311 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_MISSING_COLUMN');
4312 FND_MESSAGE.set_token('COLUMN', 'CLAIM_ID');
4313 FND_MSG_PUB.add;
4314 END IF;
4315
4316 x_return_status := FND_API.g_ret_sts_error;
4317 RETURN;
4318 ------------------------ LINE_NUMBER -------------------------------
4319 ELSIF p_claim_line_rec.line_number <> FND_API.g_miss_num AND
4320 p_claim_line_rec.line_number IS NULL THEN
4321 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4322 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_MISSING_COLUMN');
4323 FND_MESSAGE.set_token('COLUMN', 'LINE_NUMBER');
4324 FND_MSG_PUB.add;
4325 END IF;
4326
4327 x_return_status := FND_API.g_ret_sts_error;
4328 RETURN;
4329 ------------------------ SET_OF_BOOKS_ID -------------------------------
4330 ELSIF p_claim_line_rec.set_of_books_id <> FND_API.g_miss_num AND
4331 p_claim_line_rec.set_of_books_id IS NULL THEN
4332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4333 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_MISSING_COLUMN');
4334 FND_MESSAGE.set_token('COLUMN', 'SET_OF_BOOKS_ID');
4335 FND_MSG_PUB.add;
4336 END IF;
4337
4338 x_return_status := FND_API.g_ret_sts_error;
4339 RETURN;
4340 END IF;
4341
4342 END Check_Claim_Line_Req_Items;
4343
4344
4345 ---------------------------------------------------------------------
4346 -- PROCEDURE
4347 -- Check_Claim_Line_Uk_Items
4348 --
4349 -- HISTORY
4350 -- 07/11/2000 mchang Create.
4351 ---------------------------------------------------------------------
4352 PROCEDURE Check_Claim_Line_Uk_Items(
4353 p_claim_line_rec IN claim_line_rec_type
4354 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
4355 ,x_return_status OUT NOCOPY VARCHAR2
4356 )
4357 IS
4358 l_valid_flag VARCHAR2(1);
4359
4360 BEGIN
4361 x_return_status := FND_API.g_ret_sts_success;
4362
4363 -- For Create_Claim_Line, when claim_line_id is passed in, we need to
4364 -- check if this claim_line_id is unique.
4365 IF p_validation_mode = JTF_PLSQL_API.g_create AND
4366 p_claim_line_rec.claim_line_id IS NOT NULL THEN
4367 IF OZF_Utility_PVT.check_uniqueness(
4368 'ozf_claim_lines',
4369 'claim_line_id = ' || p_claim_line_rec.claim_line_id
4370 ) = FND_API.g_false THEN
4371 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4372 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_DUPLICATE_VALUE');
4373 FND_MESSAGE.set_token('COLLUMN', 'CLAIM_LINE_ID');
4374 FND_MSG_PUB.add;
4375 END IF;
4376 x_return_status := FND_API.g_ret_sts_error;
4377 RETURN;
4378 END IF;
4379 END IF;
4380
4381 -- check other unique items
4382
4383 END Check_Claim_Line_Uk_Items;
4384
4385
4386 ---------------------------------------------------------------------
4387 -- PROCEDURE
4388 -- Check_Claim_Line_Fk_Items
4389 --
4390 -- HISTORY
4391 -- 06/12/2000 mchang Create.
4392 ---------------------------------------------------------------------
4393 PROCEDURE Check_Claim_Line_Fk_Items(
4394 p_claim_line_rec IN claim_line_rec_type
4395 ,x_return_status OUT NOCOPY VARCHAR2
4396 )
4397 IS
4398 BEGIN
4399
4400 x_return_status := FND_API.g_ret_sts_success;
4401
4402 -- check other fk items
4403
4404 END Check_Claim_Line_Fk_Items;
4405
4406
4407 ---------------------------------------------------------------------
4408 -- PROCEDURE
4409 -- Check_Claim_Line_Lookup_Items
4410 --
4411 -- HISTORY
4412 -- 04/25/2000 mchang Create.
4413 ---------------------------------------------------------------------
4414 PROCEDURE Check_Claim_Line_Lookup_Items(
4415 p_claim_line_rec IN claim_line_rec_type
4416 ,x_return_status OUT NOCOPY VARCHAR2
4417 )
4418 IS
4419 BEGIN
4420
4421 x_return_status := FND_API.g_ret_sts_success;
4422
4423 -- check other lookup codes
4424
4425 END Check_Claim_Line_Lookup_Items;
4426
4427
4428 ---------------------------------------------------------------------
4429 -- PROCEDURE
4430 -- Check_Claim_Line_Flag_Items
4431 --
4432 -- HISTORY
4433 -- 06/12/2000 mchang Create.
4434 ---------------------------------------------------------------------
4435 PROCEDURE Check_Claim_Line_Flag_Items(
4436 p_claim_line_rec IN claim_line_rec_type
4437 ,x_return_status OUT NOCOPY VARCHAR2
4438 )
4439 IS
4440 BEGIN
4441 x_return_status := FND_API.g_ret_sts_success;
4442
4443 ----------------------- VALID_FLAG ------------------------
4444 IF p_claim_line_rec.valid_flag <> FND_API.g_miss_char AND
4445 p_claim_line_rec.valid_flag IS NOT NULL THEN
4446 IF p_claim_line_rec.valid_flag <> FND_API.g_true AND
4447 p_claim_line_rec.valid_flag <> FND_API.g_false THEN
4448 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4449 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BAD_FLAG');
4450 FND_MESSAGE.set_token('FLAG', 'VALID_FLAG');
4451 FND_MSG_PUB.add;
4452 END IF;
4453 x_return_status := FND_API.g_ret_sts_error;
4454 RETURN;
4455 END IF;
4456 END IF;
4457
4458 ----------------------- APPROVED_FLAG ------------------------
4459 IF p_claim_line_rec.approved_flag <> FND_API.g_miss_char AND
4460 p_claim_line_rec.approved_flag IS NOT NULL THEN
4461 IF p_claim_line_rec.approved_flag <> FND_API.g_true AND
4462 p_claim_line_rec.approved_flag <> FND_API.g_false THEN
4463 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4464 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BAD_FLAG');
4465 FND_MESSAGE.set_token('FLAG', 'APPROVED_FLAG');
4466 FND_MSG_PUB.add;
4467 END IF;
4468 x_return_status := FND_API.g_ret_sts_error;
4469 RETURN;
4470 END IF;
4471 END IF;
4472
4473 ----------------------- PERFORMANCE_COMPLETE_FLAG ------------------------
4474 IF p_claim_line_rec.performance_complete_flag <> FND_API.g_miss_char AND
4475 p_claim_line_rec.performance_complete_flag IS NOT NULL THEN
4476 IF p_claim_line_rec.performance_complete_flag <> FND_API.g_true AND
4477 p_claim_line_rec.performance_complete_flag <> FND_API.g_false THEN
4478 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4479 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BAD_FLAG');
4480 FND_MESSAGE.set_token('FLAG', 'PERFORMANCE_COMPLETE_FLAG');
4481 FND_MSG_PUB.add;
4482 END IF;
4483 x_return_status := FND_API.g_ret_sts_error;
4484 RETURN;
4485 END IF;
4486 END IF;
4487
4488 ----------------------- PERFORMANCE_ATTACHED_FLAG ------------------------
4489 IF p_claim_line_rec.performance_attached_flag <> FND_API.g_miss_char AND
4490 p_claim_line_rec.performance_attached_flag IS NOT NULL THEN
4491 IF p_claim_line_rec.performance_attached_flag <> FND_API.g_true AND
4492 p_claim_line_rec.performance_attached_flag <> FND_API.g_false THEN
4493 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4494 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BAD_FLAG');
4495 FND_MESSAGE.set_token('FLAG', 'PERFORMANCE_ATTACHED_FLAG');
4496 FND_MSG_PUB.add;
4497 END IF;
4498 x_return_status := FND_API.g_ret_sts_error;
4499 RETURN;
4500 END IF;
4501 END IF;
4502
4503 ----------------------- EARNINGS_ASSOCIATED_FLAG ------------------------
4504 IF p_claim_line_rec.earnings_associated_flag <> FND_API.g_miss_char AND
4505 p_claim_line_rec.earnings_associated_flag IS NOT NULL THEN
4506 IF p_claim_line_rec.earnings_associated_flag <> FND_API.g_true AND
4507 p_claim_line_rec.earnings_associated_flag <> FND_API.g_false THEN
4508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4509 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_BAD_FLAG');
4510 FND_MESSAGE.set_token('FLAG', 'EARNINGS_ASSOCIATED_FLAG');
4511 FND_MSG_PUB.add;
4512 END IF;
4513 x_return_status := FND_API.g_ret_sts_error;
4514 RETURN;
4515 END IF;
4516 END IF;
4517
4518 -- check other flags
4519
4520 END Check_Claim_Line_Flag_Items;
4521
4522 ---------------------------------------------------------------------
4523 -- PROCEDURE
4524 -- Check_Claim_Line_Items
4525 --
4526 -- HISTORY
4527 -- 07/11/2000 mchang Create.
4528 ---------------------------------------------------------------------
4529 PROCEDURE Check_Claim_Line_Items(
4530 p_claim_line_rec IN claim_line_rec_type
4531 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
4532 ,x_return_status OUT NOCOPY VARCHAR2
4533 )
4534 IS
4535 BEGIN
4536 Check_Claim_Line_Req_Items(
4537 p_claim_line_rec => p_claim_line_rec
4538 ,x_return_status => x_return_status
4539 );
4540
4541 IF x_return_status <> FND_API.g_ret_sts_success THEN
4542 RETURN;
4543 END IF;
4544
4545 Check_Claim_Line_Uk_Items(
4546 p_claim_line_rec => p_claim_line_rec
4547 ,p_validation_mode => p_validation_mode
4548 ,x_return_status => x_return_status
4549 );
4550
4551 IF x_return_status <> FND_API.g_ret_sts_success THEN
4552 RETURN;
4553 END IF;
4554
4555 Check_Claim_Line_Fk_Items(
4556 p_claim_line_rec => p_claim_line_rec
4557 ,x_return_status => x_return_status
4558 );
4559
4560 IF x_return_status <> FND_API.g_ret_sts_success THEN
4561 RETURN;
4562 END IF;
4563
4564 Check_Claim_Line_Lookup_Items(
4565 p_claim_line_rec => p_claim_line_rec
4566 ,x_return_status => x_return_status
4567 );
4568
4569 IF x_return_status <> FND_API.g_ret_sts_success THEN
4570 RETURN;
4571 END IF;
4572
4573 Check_Claim_Line_Flag_Items(
4574 p_claim_line_rec => p_claim_line_rec
4575 ,x_return_status => x_return_status
4576 );
4577
4578 IF x_return_status <> FND_API.g_ret_sts_success THEN
4579 RETURN;
4580 END IF;
4581
4582 END Check_Claim_Line_Items;
4583
4584
4585
4586 ---------------------------------------------------------------------
4587 -- PROCEDURE
4588 -- Check_Claim_Line_Record
4589 --
4590 -- HISTORY
4591 -- 07/11/2000 mchang Create.
4592 -- 03/20/2001 mchang Remove utilization amount checking.
4593 ---------------------------------------------------------------------
4594 PROCEDURE Check_Claim_Line_Record(
4595 p_claim_line_rec IN claim_line_rec_type
4596 ,p_complete_rec IN claim_line_rec_type := NULL
4597 ,x_return_status OUT NOCOPY VARCHAR2
4598 )
4599 IS
4600 l_tax_for VARCHAR2(15);
4601 l_tax_code VARCHAR2(30);
4602 l_source_object_id NUMBER;
4603
4604 CURSOR csr_claim_settle_method(cv_claim_id IN NUMBER) IS
4605 SELECT ocs.tax_for
4606 FROM ozf_claim_sttlmnt_methods_all ocs,
4607 ozf_claims_all oc
4608 WHERE claim_id = cv_claim_id
4609 AND ocs.claim_class = oc.claim_class
4610 AND NVL(ocs.source_object_class,'NULL') = NVL(oc.source_object_class,'NULL')
4611 AND ocs.settlement_method = oc.payment_method ;
4612
4613 CURSOR csr_ap_tax_code(cv_tax_code IN VARCHAR2) IS
4614 SELECT lookup_code
4615 FROM fnd_lookups lkp
4616 WHERE lkp.lookup_type in ( 'ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS' )
4617 AND lkp.enabled_flag = 'Y' ;
4618
4619 CURSOR csr_ar_tax_code(cv_tax_code IN VARCHAR2) IS
4620 SELECT lookup_code
4621 FROM fnd_lookups lkp
4622 WHERE lkp.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
4623 AND lkp.enabled_flag = 'Y' ;
4624
4625
4626 CURSOR csr_customer_trx_id(cv_customer_trx_line_id IN NUMBER) IS
4627 SELECT customer_trx_id
4628 FROM ra_customer_trx_lines
4629 WHERE customer_trx_line_id = cv_customer_trx_line_id;
4630
4631 BEGIN
4632 x_return_status := FND_API.g_ret_sts_success;
4633
4634 -- check if tax code belonging to the right settlement method
4635 IF p_complete_rec.tax_code IS NOT NULL THEN
4636 OPEN csr_claim_settle_method(p_complete_rec.claim_id);
4637 FETCH csr_claim_settle_method INTO l_tax_for;
4638 CLOSE csr_claim_settle_method;
4639
4640 IF l_tax_for = 'AP' THEN
4641 OPEN csr_ap_tax_code(p_complete_rec.tax_code);
4642 FETCH csr_ap_tax_code INTO l_tax_code;
4643 CLOSE csr_ap_tax_code;
4644
4645 IF l_tax_code IS NULL THEN
4646 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4647 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_TAX_CODE_ERR');
4648 FND_MSG_PUB.add;
4649 END IF;
4650 x_return_status := FND_API.g_ret_sts_error;
4651 RETURN;
4652 END IF;
4653 ELSIF l_tax_for = 'AR' THEN
4654 OPEN csr_ar_tax_code(p_complete_rec.tax_code);
4655 FETCH csr_ar_tax_code INTO l_tax_code;
4656 CLOSE csr_ar_tax_code;
4657
4658 IF l_tax_code IS NULL THEN
4659 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4660 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_TAX_CODE_ERR');
4661 FND_MSG_PUB.add;
4662 END IF;
4663 x_return_status := FND_API.g_ret_sts_error;
4664 RETURN;
4665 END IF;
4666 ELSIF l_tax_for IS NULL THEN
4667 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4668 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_LINE_TAX_CODE_ERR');
4669 FND_MSG_PUB.add;
4670 END IF;
4671 x_return_status := FND_API.g_ret_sts_error;
4672 RETURN;
4673 END IF;
4674 END IF;
4675
4676 -- check if source_object_line_id belongs to source_object_id
4677 IF p_complete_rec.source_object_line_id IS NOT NULL THEN
4678 IF p_complete_rec.source_object_class = 'INVOICE' AND
4679 p_complete_rec.source_object_id IS NOT NULL THEN
4680 OPEN csr_customer_trx_id(p_complete_rec.source_object_line_id);
4681 FETCH csr_customer_trx_id INTO l_source_object_id;
4682 CLOSE csr_customer_trx_id;
4683
4684 IF l_source_object_id <> p_complete_rec.source_object_id THEN
4685 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4686 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INVLINE_NOT_IN_INV');
4687 FND_MSG_PUB.add;
4688 END IF;
4689 x_return_status := FND_API.g_ret_sts_error;
4690 RETURN;
4691 END IF;
4692 END IF;
4693 END IF;
4694
4695 END Check_Claim_Line_Record;
4696
4697
4698 ---------------------------------------------------------------------
4699 -- PROCEDURE
4700 -- Init_Claim_Line_Rec
4701 --
4702 -- HISTORY
4703 -- 06/12/2000 mchang Create.
4704 ---------------------------------------------------------------------
4705 PROCEDURE Init_Claim_Line_Rec(
4706 x_claim_line_rec OUT NOCOPY claim_line_rec_type
4707 )
4708 IS
4709 BEGIN
4710
4711
4712 RETURN;
4713 END Init_Claim_Line_Rec;
4714
4715
4716 ---------------------------------------------------------------------
4717 -- PROCEDURE
4718 -- Complete_Claim_Line_Rec
4719 --
4720 -- HISTORY
4721 -- 07/11/2000 mchang Create.
4722 ---------------------------------------------------------------------
4723 PROCEDURE Complete_Claim_Line_Rec(
4724 p_claim_line_rec IN claim_line_rec_type
4725 ,x_complete_rec OUT NOCOPY claim_line_rec_type
4726 )
4727 IS
4728 CURSOR c_claim_line(cv_claim_line_id IN NUMBER) IS
4729 SELECT object_version_number,
4730 claim_id,
4731 line_number,
4732 split_from_claim_line_id,
4733 amount,
4734 claim_currency_amount,
4735 acctd_amount,
4736 currency_code,
4737 exchange_rate_type,
4738 exchange_rate_date,
4739 exchange_rate,
4740 set_of_books_id,
4741 valid_flag,
4742 source_object_id,
4743 source_object_line_id,
4744 source_object_class,
4745 source_object_type_id,
4746 plan_id,
4747 offer_id,
4748 utilization_id,
4749 payment_method,
4750 payment_reference_id,
4751 payment_reference_number,
4752 payment_reference_date,
4753 voucher_id,
4754 voucher_number,
4755 payment_status,
4756 approved_flag,
4757 approved_date,
4758 approved_by,
4759 settled_date,
4760 settled_by,
4761 performance_complete_flag,
4762 performance_attached_flag,
4763 select_cust_children_flag,
4764 item_id,
4765 item_description,
4766 quantity,
4767 quantity_uom,
4768 rate,
4769 activity_type,
4770 activity_id,
4771 related_cust_account_id,
4772 buy_group_cust_account_id,
4773 relationship_type,
4774 earnings_associated_flag,
4775 comments,
4776 tax_code,
4777 credit_to,
4778 attribute_category,
4779 attribute1,
4780 attribute2,
4781 attribute3,
4782 attribute4,
4783 attribute5,
4784 attribute6,
4785 attribute7,
4786 attribute8,
4787 attribute9,
4788 attribute10,
4789 attribute11,
4790 attribute12,
4791 attribute13,
4792 attribute14,
4793 attribute15,
4794 sale_date,
4795 item_type,
4796 tax_amount,
4797 claim_curr_tax_amount,
4798 acctd_tax_amount, -- Bug4489415
4799 activity_line_id,
4800 offer_type,
4801 prorate_earnings_flag,
4802 earnings_end_date,
4803 buy_group_party_id,
4804 dpp_cust_account_id --12.1 Enhancement : Price Protection
4805 FROM ozf_claim_lines
4806 WHERE claim_line_id = cv_claim_line_id;
4807
4808 l_claim_line_rec c_claim_line%ROWTYPE;
4809
4810 BEGIN
4811
4812 x_complete_rec := p_claim_line_rec;
4813
4814 OPEN c_claim_line(p_claim_line_rec.claim_line_id);
4815 FETCH c_claim_line INTO l_claim_line_rec;
4816 IF c_claim_line%NOTFOUND THEN
4817 CLOSE c_claim_line;
4818 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4819 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
4820 FND_MSG_PUB.add;
4821 END IF;
4822 RAISE FND_API.g_exc_error;
4823 END IF;
4824 CLOSE c_claim_line;
4825
4826 IF p_claim_line_rec.object_version_number = FND_API.G_MISS_NUM THEN
4827 x_complete_rec.object_version_number := NULL;
4828 END IF;
4829 IF p_claim_line_rec.object_version_number IS NULL THEN
4830 x_complete_rec.object_version_number := l_claim_line_rec.object_version_number;
4831 END IF;
4832
4833 IF p_claim_line_rec.claim_id = FND_API.G_MISS_NUM THEN
4834 x_complete_rec.claim_id := NULL;
4835 END IF;
4836 IF p_claim_line_rec.claim_id IS NULL THEN
4837 x_complete_rec.claim_id := l_claim_line_rec.claim_id;
4838 END IF;
4839
4840 IF p_claim_line_rec.line_number = FND_API.G_MISS_NUM THEN
4841 x_complete_rec.line_number := NULL;
4842 END IF;
4843 IF p_claim_line_rec.line_number IS NULL THEN
4844 x_complete_rec.line_number := l_claim_line_rec.line_number;
4845 END IF;
4846
4847 IF p_claim_line_rec.split_from_claim_line_id = FND_API.G_MISS_NUM THEN
4848 x_complete_rec.split_from_claim_line_id := NULL;
4849 END IF;
4850 IF p_claim_line_rec.split_from_claim_line_id IS NULL THEN
4851 x_complete_rec.split_from_claim_line_id := l_claim_line_rec.split_from_claim_line_id;
4852 END IF;
4853
4854 IF p_claim_line_rec.amount = FND_API.G_MISS_NUM THEN
4855 x_complete_rec.amount := NULL;
4856 END IF;
4857 IF p_claim_line_rec.amount IS NULL THEN
4858 x_complete_rec.amount := l_claim_line_rec.amount;
4859 END IF;
4860
4861 IF p_claim_line_rec.claim_currency_amount = FND_API.G_MISS_NUM THEN
4862 x_complete_rec.claim_currency_amount := NULL;
4863 END IF;
4864 IF p_claim_line_rec.claim_currency_amount IS NULL THEN
4865 x_complete_rec.claim_currency_amount := l_claim_line_rec.claim_currency_amount;
4866 END IF;
4867
4868 IF p_claim_line_rec.acctd_amount = FND_API.G_MISS_NUM THEN
4869 x_complete_rec.acctd_amount := NULL;
4870 END IF;
4871 IF p_claim_line_rec.acctd_amount IS NULL THEN
4872 x_complete_rec.acctd_amount := l_claim_line_rec.acctd_amount;
4873 END IF;
4874
4875 IF p_claim_line_rec.currency_code = FND_API.G_MISS_CHAR THEN
4876 x_complete_rec.currency_code := NULL;
4877 END IF;
4878 IF p_claim_line_rec.currency_code IS NULL THEN
4879 x_complete_rec.currency_code := l_claim_line_rec.currency_code;
4880 END IF;
4881
4882 IF p_claim_line_rec.exchange_rate_type = FND_API.G_MISS_CHAR THEN
4883 x_complete_rec.exchange_rate_type := NULL;
4884 END IF;
4885 IF p_claim_line_rec.exchange_rate_type IS NULL THEN
4886 x_complete_rec.exchange_rate_type := l_claim_line_rec.exchange_rate_type;
4887 END IF;
4888
4889 IF p_claim_line_rec.exchange_rate_date = FND_API.G_MISS_DATE THEN
4890 x_complete_rec.exchange_rate_date := NULL;
4891 END IF;
4892 IF p_claim_line_rec.exchange_rate_date IS NULL THEN
4893 x_complete_rec.exchange_rate_date := l_claim_line_rec.exchange_rate_date;
4894 END IF;
4895
4896 IF p_claim_line_rec.exchange_rate = FND_API.G_MISS_NUM THEN
4897 x_complete_rec.exchange_rate := NULL;
4898 END IF;
4899 IF p_claim_line_rec.exchange_rate IS NULL THEN
4900 x_complete_rec.exchange_rate := l_claim_line_rec.exchange_rate;
4901 END IF;
4902
4903 IF p_claim_line_rec.set_of_books_id = FND_API.G_MISS_NUM THEN
4904 x_complete_rec.set_of_books_id := NULL;
4905 END IF;
4906 IF p_claim_line_rec.set_of_books_id IS NULL THEN
4907 x_complete_rec.set_of_books_id := l_claim_line_rec.set_of_books_id;
4908 END IF;
4909
4910 IF p_claim_line_rec.valid_flag = FND_API.G_MISS_CHAR THEN
4911 x_complete_rec.valid_flag := NULL;
4912 END IF;
4913 IF p_claim_line_rec.valid_flag IS NULL THEN
4914 x_complete_rec.valid_flag := l_claim_line_rec.valid_flag;
4915 END IF;
4916
4917 IF p_claim_line_rec.source_object_id = FND_API.G_MISS_NUM THEN
4918 x_complete_rec.source_object_id := NULL;
4919 END IF;
4920 IF p_claim_line_rec.source_object_id IS NULL THEN
4921 x_complete_rec.source_object_id := l_claim_line_rec.source_object_id;
4922 END IF;
4923
4924 IF p_claim_line_rec.source_object_line_id = FND_API.G_MISS_NUM THEN
4925 x_complete_rec.source_object_line_id := NULL;
4926 END IF;
4927 IF p_claim_line_rec.source_object_line_id IS NULL THEN
4928 x_complete_rec.source_object_line_id := l_claim_line_rec.source_object_line_id;
4929 END IF;
4930
4931 IF p_claim_line_rec.source_object_class = FND_API.G_MISS_CHAR THEN
4932 x_complete_rec.source_object_class := NULL;
4933 END IF;
4934 IF p_claim_line_rec.source_object_class IS NULL THEN
4935 x_complete_rec.source_object_class := l_claim_line_rec.source_object_class;
4936 END IF;
4937
4938 IF p_claim_line_rec.source_object_type_id = FND_API.G_MISS_NUM THEN
4939 x_complete_rec.source_object_type_id := NULL;
4940 END IF;
4941 IF p_claim_line_rec.source_object_type_id IS NULL THEN
4942 x_complete_rec.source_object_type_id := l_claim_line_rec.source_object_type_id;
4943 END IF;
4944
4945 IF p_claim_line_rec.plan_id = FND_API.G_MISS_NUM THEN
4946 x_complete_rec.plan_id := NULL;
4947 END IF;
4948 IF p_claim_line_rec.plan_id IS NULL THEN
4949 x_complete_rec.plan_id := l_claim_line_rec.plan_id;
4950 END IF;
4951
4952 IF p_claim_line_rec.offer_id = FND_API.G_MISS_NUM THEN
4953 x_complete_rec.offer_id := NULL;
4954 END IF;
4955 IF p_claim_line_rec.offer_id IS NULL THEN
4956 x_complete_rec.offer_id := l_claim_line_rec.offer_id;
4957 END IF;
4958
4959 IF p_claim_line_rec.utilization_id = FND_API.G_MISS_NUM THEN
4960 x_complete_rec.utilization_id := NULL;
4961 END IF;
4962 IF p_claim_line_rec.utilization_id IS NULL THEN
4963 x_complete_rec.utilization_id := l_claim_line_rec.utilization_id;
4964 END IF;
4965
4966 IF p_claim_line_rec.payment_method = FND_API.G_MISS_CHAR THEN
4967 x_complete_rec.payment_method := NULL;
4968 END IF;
4969 IF p_claim_line_rec.payment_method IS NULL THEN
4970 x_complete_rec.payment_method := l_claim_line_rec.payment_method;
4971 END IF;
4972
4973 IF p_claim_line_rec.payment_reference_id = FND_API.G_MISS_NUM THEN
4974 x_complete_rec.payment_reference_id := NULL;
4975 END IF;
4976 IF p_claim_line_rec.payment_reference_id IS NULL THEN
4977 x_complete_rec.payment_reference_id := l_claim_line_rec.payment_reference_id;
4978 END IF;
4979
4980 IF p_claim_line_rec.payment_reference_number = FND_API.G_MISS_CHAR THEN
4981 x_complete_rec.payment_reference_number := NULL;
4982 END IF;
4983 IF p_claim_line_rec.payment_reference_number IS NULL THEN
4984 x_complete_rec.payment_reference_number := l_claim_line_rec.payment_reference_number;
4985 END IF;
4986
4987 IF p_claim_line_rec.payment_reference_date = FND_API.G_MISS_DATE THEN
4988 x_complete_rec.payment_reference_date := NULL;
4989 END IF;
4990 IF p_claim_line_rec.payment_reference_date IS NULL THEN
4991 x_complete_rec.payment_reference_date := l_claim_line_rec.payment_reference_date;
4992 END IF;
4993
4994 IF p_claim_line_rec.voucher_id = FND_API.G_MISS_NUM THEN
4995 x_complete_rec.voucher_id := NULL;
4996 END IF;
4997 IF p_claim_line_rec.voucher_id IS NULL THEN
4998 x_complete_rec.voucher_id := l_claim_line_rec.voucher_id;
4999 END IF;
5000
5001 IF p_claim_line_rec.voucher_number = FND_API.G_MISS_CHAR THEN
5002 x_complete_rec.voucher_number := NULL;
5003 END IF;
5004 IF p_claim_line_rec.voucher_number IS NULL THEN
5005 x_complete_rec.voucher_number := l_claim_line_rec.voucher_number;
5006 END IF;
5007
5008 IF p_claim_line_rec.payment_status = FND_API.G_MISS_CHAR THEN
5009 x_complete_rec.payment_status := NULL;
5010 END IF;
5011 IF p_claim_line_rec.payment_status IS NULL THEN
5012 x_complete_rec.payment_status := l_claim_line_rec.payment_status;
5013 END IF;
5014
5015 IF p_claim_line_rec.approved_flag = FND_API.G_MISS_CHAR THEN
5016 x_complete_rec.approved_flag := NULL;
5017 END IF;
5018 IF p_claim_line_rec.approved_flag IS NULL THEN
5019 x_complete_rec.approved_flag := l_claim_line_rec.approved_flag;
5020 END IF;
5021
5022 IF p_claim_line_rec.approved_date = FND_API.G_MISS_DATE THEN
5023 x_complete_rec.approved_date := NULL;
5024 END IF;
5025 IF p_claim_line_rec.approved_date IS NULL THEN
5026 x_complete_rec.approved_date := l_claim_line_rec.approved_date;
5027 END IF;
5028
5029 IF p_claim_line_rec.approved_by = FND_API.G_MISS_NUM THEN
5030 x_complete_rec.approved_by := NULL;
5031 END IF;
5032 IF p_claim_line_rec.approved_by IS NULL THEN
5033 x_complete_rec.approved_by := l_claim_line_rec.approved_by;
5034 END IF;
5035
5036 IF p_claim_line_rec.settled_date = FND_API.G_MISS_DATE THEN
5037 x_complete_rec.settled_date := NULL;
5038 END IF;
5039 IF p_claim_line_rec.settled_date IS NULL THEN
5040 x_complete_rec.settled_date := l_claim_line_rec.settled_date;
5041 END IF;
5042
5043 IF p_claim_line_rec.settled_by = FND_API.G_MISS_NUM THEN
5044 x_complete_rec.settled_by := NULL;
5045 END IF;
5046 IF p_claim_line_rec.settled_by IS NULL THEN
5047 x_complete_rec.settled_by := l_claim_line_rec.settled_by;
5048 END IF;
5049
5050 IF p_claim_line_rec.performance_complete_flag = FND_API.G_MISS_CHAR THEN
5051 x_complete_rec.performance_complete_flag := NULL;
5052 END IF;
5053 IF p_claim_line_rec.performance_complete_flag IS NULL THEN
5054 x_complete_rec.performance_complete_flag := l_claim_line_rec.performance_complete_flag;
5055 END IF;
5056
5057 IF p_claim_line_rec.performance_attached_flag = FND_API.G_MISS_CHAR THEN
5058 x_complete_rec.performance_attached_flag := NULL;
5059 END IF;
5060 IF p_claim_line_rec.performance_attached_flag IS NULL THEN
5061 x_complete_rec.performance_attached_flag := l_claim_line_rec.performance_attached_flag;
5062 END IF;
5063
5064 IF p_claim_line_rec.select_cust_children_flag = FND_API.G_MISS_CHAR THEN
5065 x_complete_rec.select_cust_children_flag := NULL;
5066 END IF;
5067 IF p_claim_line_rec.select_cust_children_flag IS NULL THEN
5068 x_complete_rec.select_cust_children_flag := l_claim_line_rec.select_cust_children_flag;
5069 END IF;
5070
5071 IF p_claim_line_rec.attribute_category = FND_API.G_MISS_CHAR THEN
5072 x_complete_rec.attribute_category := NULL;
5073 END IF;
5074 IF p_claim_line_rec.attribute_category IS NULL THEN
5075 x_complete_rec.attribute_category := l_claim_line_rec.attribute_category;
5076 END IF;
5077
5078 IF p_claim_line_rec.item_id = FND_API.G_MISS_NUM THEN
5079 x_complete_rec.item_id := NULL;
5080 END IF;
5081 IF p_claim_line_rec.item_id IS NULL THEN
5082 x_complete_rec.item_id := l_claim_line_rec.item_id;
5083 END IF;
5084
5085 IF p_claim_line_rec.item_description = FND_API.G_MISS_CHAR THEN
5086 x_complete_rec.item_description := NULL;
5087 END IF;
5088 IF p_claim_line_rec.item_description IS NULL THEN
5089 x_complete_rec.item_description := l_claim_line_rec.item_description;
5090 END IF;
5091
5092 IF p_claim_line_rec.quantity = FND_API.G_MISS_NUM THEN
5093 x_complete_rec.quantity := NULL;
5094 END IF;
5095 IF p_claim_line_rec.quantity IS NULL THEN
5096 x_complete_rec.quantity := l_claim_line_rec.quantity;
5097 END IF;
5098
5099 IF p_claim_line_rec.quantity_uom = FND_API.G_MISS_CHAR THEN
5100 x_complete_rec.quantity_uom := NULL;
5101 END IF;
5102 IF p_claim_line_rec.quantity_uom IS NULL THEN
5103 x_complete_rec.quantity_uom := l_claim_line_rec.quantity_uom;
5104 END IF;
5105
5106 IF p_claim_line_rec.rate = FND_API.G_MISS_NUM THEN
5107 x_complete_rec.rate := NULL;
5108 END IF;
5109 IF p_claim_line_rec.rate IS NULL THEN
5110 x_complete_rec.rate := l_claim_line_rec.rate;
5111 END IF;
5112
5113 IF p_claim_line_rec.activity_type = FND_API.G_MISS_CHAR THEN
5114 x_complete_rec.activity_type := NULL;
5115 END IF;
5116 IF p_claim_line_rec.activity_type IS NULL THEN
5117 x_complete_rec.activity_type := l_claim_line_rec.activity_type;
5118 END IF;
5119
5120 IF p_claim_line_rec.activity_id = FND_API.G_MISS_NUM THEN
5121 x_complete_rec.activity_id := NULL;
5122 END IF;
5123 IF p_claim_line_rec.activity_id IS NULL THEN
5124 x_complete_rec.activity_id := l_claim_line_rec.activity_id;
5125 END IF;
5126
5127 IF p_claim_line_rec.related_cust_account_id = FND_API.G_MISS_NUM THEN
5128 x_complete_rec.related_cust_account_id := NULL;
5129 END IF;
5130 IF p_claim_line_rec.related_cust_account_id IS NULL THEN
5131 x_complete_rec.related_cust_account_id := l_claim_line_rec.related_cust_account_id;
5132 END IF;
5133
5134 IF p_claim_line_rec.buy_group_cust_account_id = FND_API.G_MISS_NUM THEN
5135 x_complete_rec.buy_group_cust_account_id := NULL;
5136 END IF;
5137 IF p_claim_line_rec.buy_group_cust_account_id IS NULL THEN
5138 x_complete_rec.buy_group_cust_account_id := l_claim_line_rec.buy_group_cust_account_id;
5139 END IF;
5140
5141 IF p_claim_line_rec.relationship_type = FND_API.G_MISS_CHAR THEN
5142 x_complete_rec.relationship_type := NULL;
5143 END IF;
5144 IF p_claim_line_rec.relationship_type IS NULL THEN
5145 x_complete_rec.relationship_type := l_claim_line_rec.relationship_type;
5146 END IF;
5147
5148 IF p_claim_line_rec.earnings_associated_flag = FND_API.G_MISS_CHAR THEN
5149 x_complete_rec.earnings_associated_flag := NULL;
5150 END IF;
5151 IF p_claim_line_rec.earnings_associated_flag IS NULL THEN
5152 x_complete_rec.earnings_associated_flag := l_claim_line_rec.earnings_associated_flag;
5153 END IF;
5154
5155 IF p_claim_line_rec.comments = FND_API.G_MISS_CHAR THEN
5156 x_complete_rec.comments := NULL;
5157 END IF;
5158 IF p_claim_line_rec.comments IS NULL THEN
5159 x_complete_rec.comments := l_claim_line_rec.comments;
5160 END IF;
5161
5162 IF p_claim_line_rec.tax_code = FND_API.G_MISS_CHAR THEN
5163 x_complete_rec.tax_code := NULL;
5164 END IF;
5165 IF p_claim_line_rec.tax_code IS NULL THEN
5166 x_complete_rec.tax_code := l_claim_line_rec.tax_code;
5167 END IF;
5168
5169 IF p_claim_line_rec.credit_to = FND_API.G_MISS_CHAR THEN
5170 x_complete_rec.credit_to := NULL;
5171 END IF;
5172 IF p_claim_line_rec.credit_to IS NULL THEN
5173 x_complete_rec.credit_to := l_claim_line_rec.credit_to;
5174 END IF;
5175
5176 IF p_claim_line_rec.attribute1 = FND_API.G_MISS_CHAR THEN
5177 x_complete_rec.attribute1 := NULL;
5178 END IF;
5179 IF p_claim_line_rec.attribute1 IS NULL THEN
5180 x_complete_rec.attribute1 := l_claim_line_rec.attribute1;
5181 END IF;
5182
5183 IF p_claim_line_rec.attribute2 = FND_API.G_MISS_CHAR THEN
5184 x_complete_rec.attribute2 := NULL;
5185 END IF;
5186 IF p_claim_line_rec.attribute2 IS NULL THEN
5187 x_complete_rec.attribute2 := l_claim_line_rec.attribute2;
5188 END IF;
5189
5190 IF p_claim_line_rec.attribute3 = FND_API.G_MISS_CHAR THEN
5191 x_complete_rec.attribute3 := NULL;
5192 END IF;
5193 IF p_claim_line_rec.attribute3 IS NULL THEN
5194 x_complete_rec.attribute3 := l_claim_line_rec.attribute3;
5195 END IF;
5196
5197 IF p_claim_line_rec.attribute4 = FND_API.G_MISS_CHAR THEN
5198 x_complete_rec.attribute4 := NULL;
5199 END IF;
5200 IF p_claim_line_rec.attribute4 IS NULL THEN
5201 x_complete_rec.attribute4 := l_claim_line_rec.attribute4;
5202 END IF;
5203
5204 IF p_claim_line_rec.attribute5 = FND_API.G_MISS_CHAR THEN
5205 x_complete_rec.attribute5 := NULL;
5206 END IF;
5207 IF p_claim_line_rec.attribute5 IS NULL THEN
5208 x_complete_rec.attribute5 := l_claim_line_rec.attribute5;
5209 END IF;
5210
5211 IF p_claim_line_rec.attribute6 = FND_API.G_MISS_CHAR THEN
5212 x_complete_rec.attribute6 := NULL;
5213 END IF;
5214 IF p_claim_line_rec.attribute6 IS NULL THEN
5215 x_complete_rec.attribute6 := l_claim_line_rec.attribute6;
5216 END IF;
5217
5218 IF p_claim_line_rec.attribute7 = FND_API.G_MISS_CHAR THEN
5219 x_complete_rec.attribute7 := NULL;
5220 END IF;
5221 IF p_claim_line_rec.attribute7 IS NULL THEN
5222 x_complete_rec.attribute7 := l_claim_line_rec.attribute7;
5223 END IF;
5224
5225 IF p_claim_line_rec.attribute8 = FND_API.G_MISS_CHAR THEN
5226 x_complete_rec.attribute8 := NULL;
5227 END IF;
5228 IF p_claim_line_rec.attribute8 IS NULL THEN
5229 x_complete_rec.attribute8 := l_claim_line_rec.attribute8;
5230 END IF;
5231
5232 IF p_claim_line_rec.attribute9 = FND_API.G_MISS_CHAR THEN
5233 x_complete_rec.attribute9 := NULL;
5234 END IF;
5235 IF p_claim_line_rec.attribute9 IS NULL THEN
5236 x_complete_rec.attribute9 := l_claim_line_rec.attribute9;
5237 END IF;
5238
5239 IF p_claim_line_rec.attribute10 = FND_API.G_MISS_CHAR THEN
5240 x_complete_rec.attribute10 := NULL;
5241 END IF;
5242 IF p_claim_line_rec.attribute10 IS NULL THEN
5243 x_complete_rec.attribute10 := l_claim_line_rec.attribute10;
5244 END IF;
5245
5246 IF p_claim_line_rec.attribute11 = FND_API.G_MISS_CHAR THEN
5247 x_complete_rec.attribute11 := NULL;
5248 END IF;
5249 IF p_claim_line_rec.attribute11 IS NULL THEN
5250 x_complete_rec.attribute11 := l_claim_line_rec.attribute11;
5251 END IF;
5252
5253 IF p_claim_line_rec.attribute12 = FND_API.G_MISS_CHAR THEN
5254 x_complete_rec.attribute12 := NULL;
5255 END IF;
5256 IF p_claim_line_rec.attribute12 IS NULL THEN
5257 x_complete_rec.attribute12 := l_claim_line_rec.attribute12;
5258 END IF;
5259
5260 IF p_claim_line_rec.attribute13 = FND_API.G_MISS_CHAR THEN
5261 x_complete_rec.attribute13 := NULL;
5262 END IF;
5263 IF p_claim_line_rec.attribute13 IS NULL THEN
5264 x_complete_rec.attribute13 := l_claim_line_rec.attribute13;
5265 END IF;
5266
5267 IF p_claim_line_rec.attribute14 = FND_API.G_MISS_CHAR THEN
5268 x_complete_rec.attribute14 := NULL;
5269 END IF;
5270 IF p_claim_line_rec.attribute14 IS NULL THEN
5271 x_complete_rec.attribute14 := l_claim_line_rec.attribute14;
5272 END IF;
5273
5274 IF p_claim_line_rec.attribute15 = FND_API.G_MISS_CHAR THEN
5275 x_complete_rec.attribute15 := NULL;
5276 END IF;
5277 IF p_claim_line_rec.attribute15 IS NULL THEN
5278 x_complete_rec.attribute15 := l_claim_line_rec.attribute15;
5279 END IF;
5280
5281 IF p_claim_line_rec.update_from_tbl_flag = FND_API.G_MISS_CHAR THEN
5282 x_complete_rec.update_from_tbl_flag := NULL;
5283 END IF;
5284 IF p_claim_line_rec.update_from_tbl_flag IS NULL THEN
5285 x_complete_rec.update_from_tbl_flag := FND_API.g_false;
5286 END IF;
5287
5288 IF p_claim_line_rec.sale_date = FND_API.G_MISS_DATE THEN
5289 x_complete_rec.sale_date := NULL;
5290 END IF;
5291 IF p_claim_line_rec.sale_date IS NULL THEN
5292 x_complete_rec.sale_date := l_claim_line_rec.sale_date;
5293 END IF;
5294
5295 IF p_claim_line_rec.item_type = FND_API.G_MISS_CHAR THEN
5296 x_complete_rec.item_type := NULL;
5297 END IF;
5298 IF p_claim_line_rec.item_type IS NULL THEN
5299 x_complete_rec.item_type := l_claim_line_rec.item_type;
5300 END IF;
5301
5302 IF p_claim_line_rec.tax_amount = FND_API.G_MISS_NUM THEN
5303 x_complete_rec.tax_amount := NULL;
5304 END IF;
5305 IF p_claim_line_rec.tax_amount IS NULL THEN
5306 x_complete_rec.tax_amount := l_claim_line_rec.tax_amount;
5307 END IF;
5308
5309 IF p_claim_line_rec.claim_curr_tax_amount = FND_API.G_MISS_NUM THEN
5310 x_complete_rec.claim_curr_tax_amount := NULL;
5311 END IF;
5312 IF p_claim_line_rec.claim_curr_tax_amount IS NULL THEN
5313 x_complete_rec.claim_curr_tax_amount := l_claim_line_rec.claim_curr_tax_amount;
5314 END IF;
5315
5316 -- Added for Bug4489415
5317 IF p_claim_line_rec.acctd_tax_amount = FND_API.G_MISS_NUM THEN
5318 x_complete_rec.acctd_tax_amount := NULL;
5319 END IF;
5320 IF p_claim_line_rec.acctd_tax_amount IS NULL THEN
5321 x_complete_rec.acctd_tax_amount := l_claim_line_rec.acctd_tax_amount;
5322 END IF;
5323
5324 IF p_claim_line_rec.activity_line_id = FND_API.G_MISS_NUM THEN
5325 x_complete_rec.activity_line_id := NULL;
5326 END IF;
5327 IF p_claim_line_rec.activity_line_id IS NULL THEN
5328 x_complete_rec.activity_line_id := l_claim_line_rec.activity_line_id;
5329 END IF;
5330
5331 IF p_claim_line_rec.offer_type = FND_API.G_MISS_CHAR THEN
5332 x_complete_rec.offer_type := NULL;
5333 END IF;
5334 IF p_claim_line_rec.offer_type IS NULL THEN
5335 x_complete_rec.offer_type := l_claim_line_rec.offer_type;
5336 END IF;
5337
5338 IF p_claim_line_rec.prorate_earnings_flag = FND_API.G_MISS_CHAR THEN
5339 x_complete_rec.prorate_earnings_flag := NULL;
5340 END IF;
5341 IF p_claim_line_rec.prorate_earnings_flag IS NULL THEN
5342 x_complete_rec.prorate_earnings_flag := l_claim_line_rec.prorate_earnings_flag;
5343 END IF;
5344
5345
5346 IF p_claim_line_rec.earnings_end_date = FND_API.G_MISS_DATE THEN
5347 x_complete_rec.earnings_end_date := NULL;
5348 END IF;
5349 IF p_claim_line_rec.earnings_end_date IS NULL THEN
5350 x_complete_rec.earnings_end_date := l_claim_line_rec.earnings_end_date;
5351 END IF;
5352
5353 IF p_claim_line_rec.buy_group_party_id = FND_API.G_MISS_NUM THEN
5354 x_complete_rec.buy_group_party_id := NULL;
5355 END IF;
5356 IF p_claim_line_rec.buy_group_party_id IS NULL THEN
5357 x_complete_rec.buy_group_party_id := l_claim_line_rec.buy_group_party_id;
5358 END IF;
5359
5360 -- Bug4489415
5361 IF p_claim_line_rec.tax_action = FND_API.G_MISS_CHAR THEN
5362 x_complete_rec.tax_action := NULL;
5363 END IF;
5364
5365 --12.1 Enhancement : Price Protection
5366 IF p_claim_line_rec.dpp_cust_account_id IS NULL THEN
5367 x_complete_rec.dpp_cust_account_id := l_claim_line_rec.dpp_cust_account_id;
5368 END IF;
5369 END Complete_Claim_Line_Rec;
5370
5371 ---------------------------------------------------------------------
5372 -- PROCEDURE
5373 -- Split_Claim_Lines
5374 --
5375 -- PURPOSE
5376 -- Split claim lines so as to associate each claim line with
5377 -- earnings from only one offer-product combination.
5378 --
5379 --
5380 -- Date UID Description
5381 -- 29-Jul-2005 Sahana Created for Bug4348163
5382 -- 03-Mar-06 azahmed bugfix 5075837 line number increment
5383 ---------------------------------------------------------------------
5384 PROCEDURE split_claim_line(
5385 p_api_version IN NUMBER
5386 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5387 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5388 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5389
5390 ,p_claim_line_id IN NUMBER
5391 ,x_return_status OUT NOCOPY VARCHAR2
5392 ,x_msg_count OUT NOCOPY NUMBER
5393 ,x_msg_data OUT NOCOPY VARCHAR2
5394 )
5395 IS
5396
5397 l_api_version CONSTANT NUMBER := 1.0;
5398 l_api_name CONSTANT VARCHAR2(30) := 'split_claim_line';
5399 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5400
5401
5402 CURSOR csr_line(cv_line_id IN NUMBER) IS
5403 SELECT object_version_number,
5404 claim_id,
5405 line_number,
5406 split_from_claim_line_id,
5407 amount,
5408 claim_currency_amount,
5409 acctd_amount,
5410 currency_code,
5411 exchange_rate_type,
5412 exchange_rate_date,
5413 exchange_rate,
5414 set_of_books_id,
5415 valid_flag,
5416 source_object_id,
5417 source_object_line_id,
5418 source_object_class,
5419 source_object_type_id,
5420 plan_id,
5421 offer_id,
5422 utilization_id,
5423 payment_method,
5424 payment_reference_id,
5425 payment_reference_number,
5426 payment_reference_date,
5427 voucher_id,
5428 voucher_number,
5429 payment_status,
5430 approved_flag,
5431 approved_date,
5432 approved_by,
5433 settled_date,
5434 settled_by,
5435 performance_complete_flag,
5436 performance_attached_flag,
5437 select_cust_children_flag,
5438 item_id,
5439 item_description,
5440 quantity,
5441 quantity_uom,
5442 rate,
5443 activity_type,
5444 activity_id,
5445 related_cust_account_id,
5446 buy_group_cust_account_id,
5447 relationship_type,
5448 earnings_associated_flag,
5449 comments,
5450 tax_code,
5451 credit_to,
5452 attribute_category,
5453 attribute1,
5454 attribute2,
5455 attribute3,
5456 attribute4,
5457 attribute5,
5458 attribute6,
5459 attribute7,
5460 attribute8,
5461 attribute9,
5462 attribute10,
5463 attribute11,
5464 attribute12,
5465 attribute13,
5466 attribute14,
5467 attribute15,
5468 sale_date,
5469 item_type,
5470 tax_amount,
5471 claim_curr_tax_amount,
5472 activity_line_id,
5473 offer_type,
5474 prorate_earnings_flag,
5475 earnings_end_date,
5476 buy_group_party_id,
5477 org_id
5478 FROM ozf_claim_lines
5479 WHERE claim_line_id = cv_line_id;
5480
5481
5482 -- Cursor modified for Bug4997509
5483 -- Selected activity_type from fu
5484 CURSOR csr_line_utils(cv_line_id IN NUMBER) IS
5485 SELECT offer_id,
5486 activity_type,
5487 product_level_type,
5488 product_id,
5489 SUM(amount),
5490 SUM(acctd_amount),
5491 SUM(util_curr_amount)
5492 FROM
5493 (SELECT fu.plan_id offer_id,
5494 fu.plan_type activity_type,
5495 fu.product_level_type product_level_type,
5496 fu.product_id product_id,
5497 utl.amount amount,
5498 utl.acctd_amount acctd_amount,
5499 utl.util_curr_amount util_curr_amount
5500 FROM ozf_funds_utilized_all_b fu, ozf_claim_lines_util utl
5501 WHERE utl.utilization_id = fu.utilization_id
5502 AND utl.claim_line_id = cv_line_id
5503 AND utl.utilization_id <> -1
5504 UNION ALL
5505 SELECT act.act_product_used_by_id offer_id,
5506 'OFFR' activity_type,
5507 act.level_type_code product_level_type,
5508 NVL(act.inventory_item_id, act.category_id) product_id,
5509 utl.amount amount,
5510 utl.acctd_amount acctd_amount,
5511 NVL(utl.util_curr_amount,utl.amount) util_curr_amount
5512 FROM ams_act_products act, ozf_claim_lines_util utl
5513 WHERE utl.activity_product_id = act.activity_product_id
5514 AND utl.claim_line_id = cv_line_id
5515 AND utl.utilization_id = -1)
5516 GROUP BY offer_id,activity_type, product_level_type, product_id;
5517
5518
5519 CURSOR csr_claim_status(cv_claim_id IN NUMBER) IS
5520 SELECT status_code
5521 FROM ozf_claims_all
5522 WHERE claim_id = cv_claim_id;
5523
5524 -- added for bugfix 5075837 to get max line number
5525 CURSOR c_line_number(cv_claim_id IN NUMBER) IS
5526 SELECT MAX(line_number)
5527 FROM ozf_claim_lines
5528 WHERE claim_id = cv_claim_id;
5529
5530 -- Fix for bug 7658894
5531 CURSOR csr_claim_line_util_sum(cv_claim_line_id IN NUMBER) IS
5532 SELECT sum(amount), sum(acctd_amount)
5533 FROM ozf_claim_lines_util_all
5534 WHERE claim_line_id = cv_claim_line_id;
5535
5536 CURSOR csr_claim_line_sum(cv_claim_line_id IN NUMBER) IS
5537 SELECT nvl(amount,0), nvl(acctd_amount,0)
5538 FROM ozf_claim_lines_all
5539 WHERE claim_line_id = cv_claim_line_id;
5540
5541 l_sum_util_amount NUMBER :=0;
5542 l_sum_util_acctd_amount NUMBER :=0;
5543 l_line_amount NUMBER :=0;
5544 l_line_acctd_amount NUMBER :=0;
5545
5546 l_old_line_rec csr_line%ROWTYPE;
5547 l_claim_line_rec OZF_CLAIM_LINE_PVT.claim_line_rec_type;
5548
5549 l_status_code VARCHAR2(30);
5550 l_offer_id NUMBER;
5551 l_util_product_id NUMBER;
5552 l_util_product_level VARCHAR2(30);
5553 l_claim_line_amount NUMBER;
5554 l_acctd_amount NUMBER;
5555 l_currency_amount NUMBER;
5556 l_line_return_status VARCHAR2(1);
5557 l_claim_line_id NUMBER;
5558 l_activity_product_id NUMBER;
5559 l_uom_code VARCHAR2(30);
5560 l_activity_type VARCHAR2(30);
5561 l_max_line_number NUMBER;
5562
5563
5564
5565 BEGIN
5566
5567
5568 -------------------- initialize -------------------------
5569 SAVEPOINT split_claim_line;
5570
5571 IF OZF_DEBUG_HIGH_ON THEN
5572 OZF_Utility_PVT.debug_message(l_full_name||': start');
5573 END IF;
5574
5575 IF FND_API.to_boolean(p_init_msg_list) THEN
5576 FND_MSG_PUB.initialize;
5577 END IF;
5578
5579 IF NOT FND_API.compatible_api_call(
5580 l_api_version,
5581 p_api_version,
5582 l_api_name,
5583 g_pkg_name
5584 ) THEN
5585 RAISE FND_API.g_exc_unexpected_error;
5586 END IF;
5587
5588 x_return_status := FND_API.g_ret_sts_success;
5589
5590
5591
5592 -- Get the line being processed
5593 OPEN csr_line(p_claim_line_id);
5594 FETCH csr_line INTO l_old_line_rec;
5595 CLOSE csr_line;
5596
5597 OPEN csr_claim_status(l_old_line_rec.claim_id);
5598 FETCH csr_claim_status INTO l_status_code;
5599 CLOSE csr_claim_status;
5600
5601 -- No need to process if prorate flag is N
5602 IF (NVL(l_old_line_rec.prorate_earnings_flag,'F') = 'F' OR
5603 l_status_code IN ('PENDING_CLOSE','CLOSED') ) THEN
5604 RETURN;
5605 END IF;
5606
5607
5608 -- Get all the utilz for the line
5609 OPEN csr_line_utils(p_claim_line_id);
5610 LOOP
5611 FETCH csr_line_utils INTO l_offer_id, l_activity_type,
5612 l_util_product_level, l_util_product_id,
5613 l_claim_line_amount , l_acctd_amount,
5614 l_currency_amount;
5615 EXIT WHEN csr_line_utils%NOTFOUND;
5616
5617
5618 IF l_util_product_level = 'PRODUCT' THEN
5619 --Bugfix 5182181
5620 l_uom_code := Get_Default_Product_UOM
5621 ( p_product_id => l_util_product_id
5622 , p_org_id => FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
5623 );
5624 END IF;
5625
5626
5627 IF csr_line_utils%ROWCOUNT = 1 THEN -- bugfix 4953092
5628
5629 -- Update original line with offer-product information
5630 -- Leave the utilizations with the original line
5631 UPDATE ozf_claim_lines
5632 SET item_id = l_util_product_id,
5633 item_type = l_util_product_level,
5634 activity_id = NVL(l_old_line_rec.activity_id,l_offer_id),
5635 activity_type = NVL(l_old_line_rec.activity_type,l_activity_type),
5636 offer_id = NVL(l_old_line_rec.offer_id,l_offer_id),
5637 quantity_uom = l_uom_code
5638 WHERE claim_line_id = p_claim_line_id;
5639 l_old_line_rec.item_id := l_util_product_id;
5640 l_old_line_rec.item_type := l_util_product_level;
5641 l_old_line_rec.offer_id := l_offer_id;
5642
5643
5644 ELSE
5645 --Create a new claim line and move the earnings
5646 -- Modified: reduce claim currency amount by l_claim_line_amount
5647 UPDATE ozf_claim_lines
5648 SET amount = amount - l_claim_line_amount,
5649 acctd_amount = acctd_amount - l_acctd_amount,
5650 claim_currency_amount= claim_currency_amount - l_claim_line_amount
5651 WHERE claim_line_id = p_claim_line_id;
5652
5653 -- get existing max line_number bugfix 5075837
5654 OPEN c_line_number(l_old_line_rec.claim_id);
5655 FETCH c_line_number INTO l_max_line_number;
5656 CLOSE c_line_number;
5657
5658 l_claim_line_rec.claim_id := l_old_line_rec.claim_id;
5659 l_claim_line_rec.line_number := l_max_line_number + 1; --bugfix 5075837
5660 l_claim_line_rec.split_from_claim_line_id := l_old_line_rec.split_from_claim_line_id;
5661 l_claim_line_rec.currency_code := l_old_line_rec.currency_code;
5662 l_claim_line_rec.exchange_rate_type := l_old_line_rec.exchange_rate_type;
5663 l_claim_line_rec.exchange_rate_date := l_old_line_rec.exchange_rate_date;
5664 l_claim_line_rec.exchange_rate := l_old_line_rec.exchange_rate;
5665 l_claim_line_rec.set_of_books_id := l_old_line_rec.set_of_books_id;
5666 l_claim_line_rec.valid_flag := l_old_line_rec.valid_flag;
5667 l_claim_line_rec.source_object_id := l_old_line_rec.source_object_id;
5668 l_claim_line_rec.source_object_line_id := l_old_line_rec.source_object_line_id;
5669 l_claim_line_rec.source_object_class := l_old_line_rec.source_object_class;
5670 l_claim_line_rec.source_object_type_id := l_old_line_rec.source_object_type_id;
5671 l_claim_line_rec.plan_id := l_old_line_rec.plan_id;
5672 l_claim_line_rec.offer_id := l_offer_id;
5673 l_claim_line_rec.utilization_id := l_old_line_rec.utilization_id;
5674 l_claim_line_rec.payment_method := l_old_line_rec.payment_method;
5675 l_claim_line_rec.payment_reference_id := l_old_line_rec.payment_reference_id;
5676 l_claim_line_rec.payment_reference_number := l_old_line_rec.payment_reference_number;
5677 l_claim_line_rec.payment_reference_date := l_old_line_rec.payment_reference_date;
5678 l_claim_line_rec.voucher_id := l_old_line_rec.voucher_id;
5679 l_claim_line_rec.voucher_number := l_old_line_rec.voucher_number;
5680 l_claim_line_rec.payment_status := l_old_line_rec.payment_status;
5681 l_claim_line_rec.approved_flag := l_old_line_rec.approved_flag;
5682 l_claim_line_rec.approved_date := l_old_line_rec.approved_date;
5683 l_claim_line_rec.approved_by := l_old_line_rec.approved_by;
5684 l_claim_line_rec.settled_date := l_old_line_rec.settled_date;
5685 l_claim_line_rec.settled_by := l_old_line_rec.settled_by;
5686 l_claim_line_rec.performance_complete_flag := l_old_line_rec.performance_complete_flag;
5687 l_claim_line_rec.performance_attached_flag := l_old_line_rec.performance_attached_flag;
5688 l_claim_line_rec.select_cust_children_flag := l_old_line_rec.select_cust_children_flag;
5689 l_claim_line_rec.related_cust_account_id := l_old_line_rec.related_cust_account_id;
5690 l_claim_line_rec.buy_group_cust_account_id := l_old_line_rec.buy_group_cust_account_id;
5691 l_claim_line_rec.relationship_type := l_old_line_rec.relationship_type;
5692 l_claim_line_rec.earnings_associated_flag := l_old_line_rec.earnings_associated_flag;
5693 l_claim_line_rec.comments := l_old_line_rec.comments;
5694 l_claim_line_rec.tax_code := l_old_line_rec.tax_code;
5695 l_claim_line_rec.credit_to := l_old_line_rec.credit_to;
5696 l_claim_line_rec.attribute_category := l_old_line_rec.attribute_category;
5697 l_claim_line_rec.attribute1 := l_old_line_rec.attribute1;
5698 l_claim_line_rec.attribute2 := l_old_line_rec.attribute2;
5699 l_claim_line_rec.attribute3 := l_old_line_rec.attribute3;
5700 l_claim_line_rec.attribute4 := l_old_line_rec.attribute4;
5701 l_claim_line_rec.attribute5 := l_old_line_rec.attribute5;
5702 l_claim_line_rec.attribute6 := l_old_line_rec.attribute6;
5703 l_claim_line_rec.attribute7 := l_old_line_rec.attribute7;
5704 l_claim_line_rec.attribute8 := l_old_line_rec.attribute8;
5705 l_claim_line_rec.attribute9 := l_old_line_rec.attribute9;
5706 l_claim_line_rec.attribute10 := l_old_line_rec.attribute10;
5707 l_claim_line_rec.attribute11 := l_old_line_rec.attribute11;
5708 l_claim_line_rec.attribute12 := l_old_line_rec.attribute12;
5709 l_claim_line_rec.attribute13 := l_old_line_rec.attribute13;
5710 l_claim_line_rec.attribute14 := l_old_line_rec.attribute14;
5711 l_claim_line_rec.attribute15 := l_old_line_rec.attribute15;
5712 l_claim_line_rec.sale_date := l_old_line_rec.sale_date;
5713 l_claim_line_rec.item_type := l_old_line_rec.item_type;
5714 l_claim_line_rec.tax_amount := l_old_line_rec.tax_amount;
5715 l_claim_line_rec.prorate_earnings_flag := l_old_line_rec.prorate_earnings_flag;
5716 l_claim_line_rec.earnings_end_date := l_old_line_rec.earnings_end_date;
5717 l_claim_line_rec.buy_group_party_id := l_old_line_rec.buy_group_party_id;
5718 l_claim_line_rec.item_id := l_util_product_id;
5719 l_claim_line_rec.item_type := l_util_product_level;
5720 l_claim_line_rec.amount := l_claim_line_amount;
5721 l_claim_line_rec.acctd_amount := l_acctd_amount;
5722 l_claim_line_rec.claim_currency_amount := l_claim_line_amount; -- Modified
5723 l_claim_line_rec.activity_id := NVL(l_old_line_rec.activity_id, l_offer_id);
5724 l_claim_line_rec.activity_type := NVL(l_old_line_rec.activity_type, 'OFFR');
5725 l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
5726
5727
5728 -- Call API to create new claim line
5729 ozf_claim_line_pvt.Create_Claim_Line(
5730 p_api_version => 1.0
5731 , p_init_msg_list => FND_API.g_false
5732 , p_commit => FND_API.g_false
5733 , p_validation_level => p_validation_level
5734 , x_return_status => x_return_status
5735 , x_msg_data => x_msg_data
5736 , x_msg_count => x_msg_count
5737 , p_claim_line_rec => l_claim_line_rec
5738 , p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
5739 , x_claim_line_id => l_claim_line_id
5740 );
5741 IF x_return_status = fnd_api.g_ret_sts_error THEN
5742 RAISE FND_API.g_exc_error;
5743 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5744 RAISE FND_API.g_exc_unexpected_error;
5745 END IF;
5746
5747 -- Point the utilizations to the new claim line
5748 IF l_util_product_id IS NULL AND l_util_product_level IS NULL THEN
5749 -- These are adjustment records without product info
5750 UPDATE ozf_claim_lines_util_all
5751 SET claim_line_id = l_claim_line_id
5752 WHERE claim_line_util_id IN ( SELECT util.claim_line_util_id
5753 FROM ozf_funds_utilized_all_b fu, ozf_claim_lines_util_all util
5754 WHERE fu.utilization_id = util.utilization_id
5755 AND fu.product_id IS NULL
5756 AND util.claim_line_id = p_claim_line_id
5757 AND fu.product_level_type IS NULL
5758 AND fu.plan_id = l_offer_id
5759 AND util.utilization_id > -1);
5760 ELSE
5761 UPDATE ozf_claim_lines_util_all
5762 SET claim_line_id = l_claim_line_id
5763 WHERE claim_line_util_id in ( SELECT util.claim_line_util_id
5764 FROM ozf_funds_utilized_all_b fu, ozf_claim_lines_util_all util
5765 WHERE fu.utilization_id = util.utilization_id
5766 AND fu.product_id = l_util_product_id
5767 AND util.claim_line_id = p_claim_line_id
5768 AND fu.product_level_type = l_util_product_level
5769 AND fu.plan_id = l_offer_id
5770 AND util.utilization_id > -1);
5771
5772 UPDATE ozf_claim_lines_util_all
5773 SET claim_line_id = l_claim_line_id
5774 WHERE claim_line_util_id IN ( SELECT utl.claim_line_util_id
5775 FROM ams_act_products act, ozf_claim_lines_util_all utl
5776 WHERE utl.activity_product_id = act.activity_product_id
5777 AND utl.claim_line_id = p_claim_line_id
5778 AND act.level_type_code = l_util_product_level
5779 AND NVL(act.inventory_item_id, act.category_id) = l_util_product_id
5780 AND act.act_product_used_by_id = l_offer_id
5781 AND utilization_id = -1);
5782 END IF; -- product id is null
5783
5784 IF OZF_DEBUG_LOW_ON THEN
5785 OZF_Utility_PVT.debug_message('l_claim_line_id'||l_claim_line_id);
5786 END IF;
5787
5788 -- Fix for bug 7658894
5789 OPEN csr_claim_line_util_sum(l_claim_line_id);
5790 FETCH csr_claim_line_util_sum INTO l_sum_util_amount,l_sum_util_acctd_amount;
5791 CLOSE csr_claim_line_util_sum;
5792
5793 IF OZF_DEBUG_LOW_ON THEN
5794 OZF_Utility_PVT.debug_message('l_sum_util_amount'||l_sum_util_amount);
5795 OZF_Utility_PVT.debug_message('l_sum_util_acctd_amount'||l_sum_util_acctd_amount);
5796 END IF;
5797
5798 OPEN csr_claim_line_sum(l_claim_line_id);
5799 FETCH csr_claim_line_sum INTO l_line_amount,l_line_acctd_amount;
5800 CLOSE csr_claim_line_sum;
5801
5802 IF OZF_DEBUG_LOW_ON THEN
5803 OZF_Utility_PVT.debug_message('l_line_amount'||l_line_amount);
5804 OZF_Utility_PVT.debug_message('l_sum_util_amount'||l_sum_util_amount);
5805 END IF;
5806
5807 IF (l_line_amount = l_sum_util_amount) THEN
5808 update ozf_claim_lines_all
5809 set acctd_amount = l_sum_util_acctd_amount
5810 where claim_line_id = l_claim_line_id;
5811 END IF;
5812 END IF; -- update/create claim line
5813 END LOOP;
5814 CLOSE csr_line_utils;
5815
5816
5817 -- Check for commit
5818 IF FND_API.to_boolean(p_commit) THEN
5819 COMMIT;
5820 END IF;
5821
5822 FND_MSG_PUB.count_and_get(
5823 p_encoded => FND_API.g_false,
5824 p_count => x_msg_count,
5825 p_data => x_msg_data
5826 );
5827
5828 IF OZF_DEBUG_HIGH_ON THEN
5829 OZF_Utility_PVT.debug_message(l_full_name ||': end');
5830 END IF;
5831
5832 EXCEPTION
5833 WHEN FND_API.g_exc_error THEN
5834 ROLLBACK TO split_claim_line;
5835 x_return_status := FND_API.g_ret_sts_error;
5836 FND_MSG_PUB.count_and_get(
5837 p_encoded => FND_API.g_false,
5838 p_count => x_msg_count,
5839 p_data => x_msg_data
5840 );
5841
5842 WHEN FND_API.g_exc_unexpected_error THEN
5843 ROLLBACK TO split_claim_line;
5844 x_return_status := FND_API.g_ret_sts_unexp_error ;
5845 FND_MSG_PUB.count_and_get(
5846 p_encoded => FND_API.g_false,
5847 p_count => x_msg_count,
5848 p_data => x_msg_data
5849 );
5850
5851 WHEN OTHERS THEN
5852 ROLLBACK TO split_claim_line;
5853 x_return_status := FND_API.g_ret_sts_unexp_error ;
5854 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5855 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
5856 END IF;
5857 FND_MSG_PUB.count_and_get(
5858 p_encoded => FND_API.g_false,
5859 p_count => x_msg_count,
5860 p_data => x_msg_data
5861 );
5862
5863 END split_claim_line;
5864
5865
5866 ---------------------------------------------------------------------
5867 -- PROCEDURE
5868 -- Delete_All_Association_AssoUI
5869 --
5870 -- PURPOSE
5871 -- Delete the Association from the OA UI
5872 --
5873 --
5874 -- Date UID Description
5875 -- 14-MAR-2011 KPATRO Created
5876 ---------------------------------------------------------------------
5877 PROCEDURE Delete_All_Association_AssoUI(
5878 p_api_version IN NUMBER
5879 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5880 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5881 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5882
5883 ,p_claim_id IN NUMBER
5884 ,x_return_status OUT NOCOPY VARCHAR2
5885 ,x_msg_count OUT NOCOPY NUMBER
5886 ,x_msg_data OUT NOCOPY VARCHAR2
5887 )
5888 IS
5889
5890 l_api_version CONSTANT NUMBER := 1.0;
5891 l_api_name CONSTANT VARCHAR2(30) := 'Delete_All_Association_AssoUI';
5892 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5893
5894 l_return_status VARCHAR2(1);
5895 l_msg_count NUMBER;
5896 l_msg_data VARCHAR2(20000);
5897 l_org_id NUMBER;
5898 l_error_index NUMBER;
5899 l_line_counter NUMBER := 1;
5900 l_claim_id NUMBER;
5901 l_claim_line_id NUMBER;
5902 l_object_version_number_line NUMBER;
5903 l_claim_line_tbl OZF_Claim_Line_PVT.claim_line_tbl_type;
5904
5905
5906 --Cursor to fetch the claim line detail
5907 CURSOR claim_line_id_csr(p_id in number) IS
5908 SELECT distinct cln.claim_line_id, cln.object_version_number
5909 FROM ozf_claim_lines_all cln, ozf_claim_lines_util_all clu
5910 WHERE clu.claim_line_id = cln.claim_line_id
5911 AND claim_id = p_id;
5912
5913
5914 BEGIN
5915
5916
5917 -------------------- initialize -------------------------
5918 SAVEPOINT delete_all_association;
5919
5920 IF OZF_DEBUG_HIGH_ON THEN
5921 OZF_Utility_PVT.debug_message(l_full_name||': start');
5922 END IF;
5923
5924 IF FND_API.to_boolean(p_init_msg_list) THEN
5925 FND_MSG_PUB.initialize;
5926 END IF;
5927
5928 IF NOT FND_API.compatible_api_call(
5929 l_api_version,
5930 p_api_version,
5931 l_api_name,
5932 g_pkg_name
5933 ) THEN
5934 RAISE FND_API.g_exc_unexpected_error;
5935 END IF;
5936
5937 x_return_status := FND_API.g_ret_sts_success;
5938
5939 OPEN claim_line_id_csr(p_claim_id);
5940 LOOP
5941 FETCH claim_line_id_csr into l_claim_line_id, l_object_version_number_line;
5942 EXIT when claim_line_id_csr%NOTFOUND;
5943 l_claim_line_tbl(l_line_counter).claim_line_id := l_claim_line_id;
5944 l_claim_line_tbl(l_line_counter).object_version_number := l_object_version_number_line;
5945 l_line_counter := l_line_counter +1;
5946 END LOOP;
5947 CLOSE claim_line_id_csr;
5948 -- API call to Delete the claim Association and claim line.
5949 IF(l_claim_line_tbl.COUNT > 0 ) THEN
5950 OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
5951 p_api_version => l_api_version
5952 ,p_init_msg_list => FND_API.g_false
5953 ,p_commit => FND_API.g_false
5954 ,p_validation_level => FND_API.g_valid_level_full
5955 ,x_return_status => l_return_status
5956 ,x_msg_count => l_msg_count
5957 ,x_msg_data => l_msg_data
5958 ,p_claim_line_tbl => l_claim_line_tbl
5959 ,p_change_object_version => FND_API.g_false
5960 ,x_error_index => l_error_index
5961 );
5962 IF l_return_status = FND_API.g_ret_sts_error THEN
5963 RAISE FND_API.g_exc_unexpected_error;
5964 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5965 RAISE FND_API.g_exc_unexpected_error;
5966 END IF;
5967 END IF;
5968
5969
5970 -- Check for commit
5971 IF FND_API.to_boolean(p_commit) THEN
5972 COMMIT;
5973 END IF;
5974
5975 FND_MSG_PUB.count_and_get(
5976 p_encoded => FND_API.g_false,
5977 p_count => x_msg_count,
5978 p_data => x_msg_data
5979 );
5980
5981 IF OZF_DEBUG_HIGH_ON THEN
5982 OZF_Utility_PVT.debug_message(l_full_name ||': end');
5983 END IF;
5984
5985 EXCEPTION
5986 WHEN FND_API.g_exc_error THEN
5987 ROLLBACK TO delete_all_association;
5988 x_return_status := FND_API.g_ret_sts_error;
5989 FND_MSG_PUB.count_and_get(
5990 p_encoded => FND_API.g_false,
5991 p_count => x_msg_count,
5992 p_data => x_msg_data
5993 );
5994
5995 WHEN FND_API.g_exc_unexpected_error THEN
5996 ROLLBACK TO delete_all_association;
5997 x_return_status := FND_API.g_ret_sts_unexp_error ;
5998 FND_MSG_PUB.count_and_get(
5999 p_encoded => FND_API.g_false,
6000 p_count => x_msg_count,
6001 p_data => x_msg_data
6002 );
6003
6004 WHEN OTHERS THEN
6005 ROLLBACK TO delete_all_association;
6006 x_return_status := FND_API.g_ret_sts_unexp_error ;
6007 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
6008 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
6009 END IF;
6010 FND_MSG_PUB.count_and_get(
6011 p_encoded => FND_API.g_false,
6012 p_count => x_msg_count,
6013 p_data => x_msg_data
6014 );
6015
6016 END Delete_All_Association_AssoUI;
6017
6018 END OZF_Claim_Line_PVT;