[Home] [Help]
PACKAGE BODY: APPS.OZF_SPLIT_CLAIM_PVT
Source
1 PACKAGE BODY OZF_split_claim_PVT AS
2 /* $Header: ozfvspcb.pls 120.4.12010000.2 2008/11/24 11:26:50 bkunjan ship $ */
3
4 ----------- Define private data type -----------------------------
5 TYPE Simple_line_type IS RECORD (
6 claim_line_id NUMBER,
7 object_version_number NUMBER
8 );
9
10 TYPE Simple_line_tbl_type IS TABLE of Simple_line_type
11 INDEX BY BINARY_INTEGER;
12
13 TYPE Child_Claim_int_type IS RECORD (
14 claim_id NUMBER,
15 object_version_number NUMBER,
16 claim_type_id NUMBER,
17 amount NUMBER,
18 line_amount_sum NUMBER,
19 reason_code_id NUMBER,
20 parent_claim_id NUMBER
21 );
22
23
24 ----------- Define Private Constant -----------------------------
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_split_claim_PVT';
26 G_OPEN_STATUS CONSTANT VARCHAR2(30) := 'OPEN';
27 G_CLOSE_STATUS CONSTANT VARCHAR2(30) := 'CLOSED';
28 G_PENDING_CLOSE_STATUS CONSTANT VARCHAR2(30) := 'PENDING_CLOSE';
29 G_CLAIM_STATUS CONSTANT VARCHAR2(30) := 'OZF_CLAIM_STATUS';
30 G_NO_CHANGE_EVENT CONSTANT VARCHAR2(30) := 'NOCHANGE';
31 G_SPLIT_EVENT CONSTANT VARCHAR2(30) := 'SPLIT';
32 G_UPDATE_EVENT CONSTANT VARCHAR2(30) := 'UPDATE';
33 G_CLAIM_OBJECT_TYPE CONSTANT VARCHAR2(30) := 'CLAM';
34
35 ----------- Define session related variables --------------------
36 g_history_created boolean := false;
37
38 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
39 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
40
41 ---------------------------------------------------------------------
42 -- PROCEDURE
43 -- create_child_claim
44 --
45 -- PURPOSE
46 -- Split a child claim
47 --
48 -- PARAMETERS
49 -- p_claim : the new claim to be created.
50 -- p_line_tbl : the table of lines associated with this new claim if any.
51 --
52 -- NOTES
53 ----------------------------------------------------------------------
54 PROCEDURE create_child_claim (
55 p_claim IN child_claim_int_type
56 ,p_line_tbl IN Simple_line_tbl_type
57 ,x_return_status OUT NOCOPY VARCHAR2
58 )
59 IS
60 l_api_name CONSTANT VARCHAR2(30) := 'Create_Child_Claim';
61 l_api_version CONSTANT NUMBER := 1.0;
62 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
63
64 l_return_status VARCHAR2(30);
65 l_msg_data VARCHAR2(2000);
66 l_msg_count NUMBER;
67 l_claim_id NUMBER;
68 l_child_claim OZF_CLAIM_PVT.claim_rec_type;
69 l_claim_line OZF_CLAIM_LINE_PVT.claim_line_rec_type;
70 l_line_obj_num NUMBER;
71 l_open_status_id NUMBER;
72 l_split_status_type VARCHAR2(30);
73
74 CURSOR parent_claim_csr(p_id in number) IS
75 SELECT *
76 FROM OZF_CLAIMS_ALL
77 WHERE claim_id = p_id;
78 l_parent_claim parent_claim_csr%ROWTYPE;
79
80 -- [BEGIN OF BUG 3473501 FIXING]
81 Type access_list_tbl_type IS TABLE OF AMS_ACCESS_PVT.ACCESS_REC_TYPE
82 INDEX BY BINARY_INTEGER;
83 l_access_list_tbl access_list_tbl_type;
84 l_access_id NUMBER;
85 i NUMBER := 1;
86
87 CURSOR parent_claim_act_access( pv_parent_claim_id IN NUMBER
88 , pv_child_claim_id IN NUMBER
89 ) IS
90 SELECT user_or_role_id
91 , arc_user_or_role_type
92 , admin_flag
93 , owner_flag
94 FROM ams_act_access
95 WHERE arc_act_access_to_object = 'CLAM'
96 AND act_access_to_object_id = pv_parent_claim_id
97 MINUS
98 SELECT user_or_role_id
99 , arc_user_or_role_type
100 , admin_flag
101 , owner_flag
102 FROM ams_act_access
103 WHERE arc_act_access_to_object = 'CLAM'
104 AND act_access_to_object_id = pv_child_claim_id;
105 -- [END OF BUG 3473501 FIXING]
106
107 BEGIN
108 -- Debug Message
109 IF OZF_DEBUG_LOW_ON THEN
110 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
111 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
112 FND_MSG_PUB.Add;
113 END IF;
114
115 -- Initialize API return status to sucess
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 -- Start Creation.
119 -- setup the data for claim and then call create_claim package.
120 -- get the parent claim record
121
122 OPEN parent_claim_csr(p_claim.parent_claim_id);
123 FETCH parent_claim_csr INTO l_parent_claim;
124 CLOSE parent_claim_csr;
125
126 -- assign the value of the parent to the child claim.
127 IF p_claim.claim_type_id is NULL THEN
128 l_child_claim.claim_type_id := l_parent_claim.claim_type_id;
129 ELSE
130 l_child_claim.claim_type_id := p_claim.claim_type_id;
131 END IF;
132 l_child_claim.split_from_claim_id := p_claim.parent_claim_id;
133 l_child_claim.duplicate_claim_id := null;
134 l_child_claim.split_date := sysdate;
135 l_child_claim.amount := p_claim.amount;
136 IF p_claim.reason_code_id is NULL THEN
137 l_child_claim.reason_code_id := l_parent_claim.reason_code_id;
138 ELSE
139 l_child_claim.reason_code_id := p_claim.reason_code_id;
140 END IF;
141
142 --//Bugfix: 7584669
143 l_split_status_type := NVL(FND_PROFILE.value('OZF_DEFAULT_CHILD_CLAIM_STATUS'),'PARENT');
144
145 IF l_split_status_type = 'PARENT' THEN
146 l_open_status_id := l_parent_claim.open_status_id;
147 ELSE
148 l_open_status_id := to_number( ozf_utility_pvt.GET_DEFAULT_USER_STATUS(
149 P_STATUS_TYPE=> G_CLAIM_STATUS,
150 P_STATUS_CODE=> G_OPEN_STATUS));
151 END IF;
152 --//End
153
154 l_child_claim.user_status_id := l_open_status_id;
155 l_child_claim.open_status_id := l_open_status_id;
156
157 l_child_claim.batch_id := l_parent_claim.batch_id;
158 l_child_claim.claim_class := l_parent_claim.claim_class;
159 l_child_claim.claim_date := l_parent_claim.claim_date;
160 l_child_claim.due_date := l_parent_claim.due_date;
161 l_child_claim.owner_id := l_parent_claim.owner_id;
162 l_child_claim.root_claim_id := l_parent_claim.root_claim_id;
163 -- l_child_claim.tax_amount := l_parent_claim.tax_amount;
164 l_child_claim.tax_code := l_parent_claim.tax_code;
165 l_child_claim.order_type_id := l_parent_claim.order_type_id; -- fixed for 4946978
166 -- l_child_claim.tax_calculation_flag := l_parent_claim.tax_calculation_flag;
167 l_child_claim.currency_code := l_parent_claim.currency_code;
168 l_child_claim.exchange_rate_type := l_parent_claim.exchange_rate_type;
169 l_child_claim.exchange_rate_date := l_parent_claim.exchange_rate_date;
170 l_child_claim.exchange_rate := l_parent_claim.exchange_rate;
171 l_child_claim.set_of_books_id := l_parent_claim.set_of_books_id;
172 l_child_claim.original_claim_date := l_parent_claim.claim_date;
173 l_child_claim.source_object_id := l_parent_claim.source_object_id;
174 l_child_claim.source_object_class := l_parent_claim.source_object_class;
175 l_child_claim.source_object_type_id := l_parent_claim.source_object_type_id;
176 l_child_claim.source_object_number:= l_parent_claim.source_object_number;
177 l_child_claim.cust_account_id := l_parent_claim.cust_account_id;
178 l_child_claim.cust_billto_acct_site_id := l_parent_claim.cust_billto_acct_site_id;
179 l_child_claim.cust_shipto_acct_site_id := l_parent_claim.cust_shipto_acct_site_id;
180 l_child_claim.location_id := l_parent_claim.location_id;
181 l_child_claim.pay_related_account_flag := l_parent_claim.pay_related_account_flag;
182 l_child_claim.related_cust_account_id:= l_parent_claim.related_cust_account_id;
183 l_child_claim.related_site_use_id := l_parent_claim.related_site_use_id;
184 l_child_claim.relationship_type := l_parent_claim.relationship_type;
185 l_child_claim.vendor_id := l_parent_claim.vendor_id;
186 l_child_claim.vendor_site_id := l_parent_claim.vendor_site_id;
187 l_child_claim.reason_type := l_parent_claim.reason_type;
188 l_child_claim.sales_rep_id := l_parent_claim.sales_rep_id;
189 l_child_claim.collector_id := l_parent_claim.collector_id;
190 l_child_claim.contact_id := l_parent_claim.contact_id;
191 l_child_claim.broker_id := l_parent_claim.broker_id;
192 l_child_claim.territory_id := l_parent_claim.territory_id;
193 l_child_claim.customer_ref_date := l_parent_claim.customer_ref_date;
194 l_child_claim.customer_ref_number := l_parent_claim.customer_ref_number;
195 l_child_claim.assigned_to := l_parent_claim.assigned_to;
196 l_child_claim.receipt_id := l_parent_claim.receipt_id;
197 l_child_claim.receipt_number := l_parent_claim.receipt_number;
198 l_child_claim.doc_sequence_id := l_parent_claim.doc_sequence_id;
199 l_child_claim.doc_sequence_value := l_parent_claim.doc_sequence_value;
200 -- l_child_claim.gl_date := l_parent_claim.gl_date;
201 l_child_claim.payment_method := l_parent_claim.payment_method;
202 l_child_claim.voucher_id := l_parent_claim.voucher_id;
203 l_child_claim.voucher_number := l_parent_claim.voucher_number;
204 l_child_claim.payment_reference_id:= l_parent_claim.payment_reference_id;
205 l_child_claim.payment_reference_number:= l_parent_claim.payment_reference_number;
206 l_child_claim.payment_reference_date := l_parent_claim.payment_reference_date;
207 -- l_child_claim.payment_status := l_parent_claim.payment_status;
208 -- l_child_claim.approved_flag := l_parent_claim.approved_flag;
209 -- l_child_claim.approved_date := l_parent_claim.approved_date;
210 -- l_child_claim.approved_by := l_parent_claim.approved_by;
211 -- l_child_claim.settled_date := l_parent_claim.settled_date;
212 -- l_child_claim.settled_by := l_parent_claim.settled_by;
213 l_child_claim.effective_date := l_parent_claim.effective_date;
214 l_child_claim.custom_setup_id := l_parent_claim.custom_setup_id;
215 l_child_claim.task_id := l_parent_claim.task_id;
216 l_child_claim.country_id := l_parent_claim.country_id;
217 l_child_claim.comments := l_parent_claim.comments;
218 l_child_claim.attribute_category := l_parent_claim.attribute_category;
219 l_child_claim.attribute1 := l_parent_claim.attribute1;
220 l_child_claim.attribute2 := l_parent_claim.attribute2;
221 l_child_claim.attribute3 := l_parent_claim.attribute3;
222 l_child_claim.attribute4 := l_parent_claim.attribute4;
223 l_child_claim.attribute5 := l_parent_claim.attribute5;
224 l_child_claim.attribute6 := l_parent_claim.attribute6;
225 l_child_claim.attribute7 := l_parent_claim.attribute7;
226 l_child_claim.attribute8 := l_parent_claim.attribute8;
227 l_child_claim.attribute9 := l_parent_claim.attribute9;
228 l_child_claim.attribute10 := l_parent_claim.attribute10;
229 l_child_claim.attribute11 := l_parent_claim.attribute11;
230 l_child_claim.attribute12 := l_parent_claim.attribute12;
231 l_child_claim.attribute13 := l_parent_claim.attribute13;
232 l_child_claim.attribute14 := l_parent_claim.attribute14;
233 l_child_claim.attribute15 := l_parent_claim.attribute15;
234 l_child_claim.deduction_attribute_category := l_parent_claim.deduction_attribute_category;
235 l_child_claim.deduction_attribute1 := l_parent_claim.deduction_attribute1;
236 l_child_claim.deduction_attribute2 := l_parent_claim.deduction_attribute2;
237 l_child_claim.deduction_attribute3 := l_parent_claim.deduction_attribute3;
238 l_child_claim.deduction_attribute4 := l_parent_claim.deduction_attribute4;
239 l_child_claim.deduction_attribute5 := l_parent_claim.deduction_attribute5;
240 l_child_claim.deduction_attribute6 := l_parent_claim.deduction_attribute6;
241 l_child_claim.deduction_attribute7 := l_parent_claim.deduction_attribute7;
242 l_child_claim.deduction_attribute8 := l_parent_claim.deduction_attribute8;
243 l_child_claim.deduction_attribute9 := l_parent_claim.deduction_attribute9;
244 l_child_claim.deduction_attribute10 := l_parent_claim.deduction_attribute10;
245 l_child_claim.deduction_attribute11 := l_parent_claim.deduction_attribute11;
246 l_child_claim.deduction_attribute12 := l_parent_claim.deduction_attribute12;
247 l_child_claim.deduction_attribute13 := l_parent_claim.deduction_attribute13;
248 l_child_claim.deduction_attribute14 := l_parent_claim.deduction_attribute14;
249 l_child_claim.deduction_attribute15 := l_parent_claim.deduction_attribute15;
250 l_child_claim.org_id := l_parent_claim.org_id;
251
252
253 OZF_claim_PVT.Create_Claim(
254 P_Api_Version => 1.0,
255 P_Init_Msg_List => FND_API.G_FALSE,
256 P_Commit => FND_API.G_FALSE,
257 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
258 X_Return_Status => l_return_status,
259 X_Msg_Count => l_msg_count,
260 X_Msg_Data => l_msg_data,
261 P_claim => l_child_claim,
262 X_CLAIM_ID => l_claim_id
263 );
264
265 IF l_return_status = FND_API.g_ret_sts_error THEN
266 RAISE FND_API.g_exc_error;
267 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
268 RAISE FND_API.g_exc_unexpected_error;
269 END IF;
270
271 IF OZF_DEBUG_HIGH_ON THEN
272 ozf_utility_PVT.debug_message('new claim id '|| l_claim_id);
273 END IF;
274
275 -- For each claim_line_id, update the claim_line
276 FOR i in 1..p_line_tbl.COUNT LOOP
277 -- build a record to update in claim lines table.
278 OZF_CLAIM_LINE_PVT.Init_Claim_Line_Rec(x_claim_line_rec => l_claim_line);
279 l_claim_line.claim_line_id := p_line_tbl(i).claim_line_id;
280 l_claim_line.object_version_number := p_line_tbl(i).object_version_number;
281 l_claim_line.claim_id := l_claim_id;
282
283 OZF_CLAIM_LINE_PVT.Update_Claim_Line(
284 p_api_version => l_api_version
285 ,p_init_msg_list => FND_API.g_false
286 ,p_commit => FND_API.g_false
287 ,p_validation_level => FND_API.g_valid_level_full
288 ,x_return_status => l_return_status
289 ,x_msg_count => l_msg_count
290 ,x_msg_data => l_msg_data
291 ,p_claim_line_rec => l_claim_line
292 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
293 ,x_object_version => l_line_obj_num
294 );
295
296 IF l_return_status = FND_API.g_ret_sts_error THEN
297 RAISE FND_API.g_exc_error;
298 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
299 RAISE FND_API.g_exc_unexpected_error;
300 END IF;
301 END LOOP;
302
303
304 -- [BEGIN OF BUG 3473501 FIXING]
305 OPEN parent_claim_act_access(p_claim.parent_claim_id, l_claim_id);
306 LOOP
307 FETCH parent_claim_act_access INTO l_access_list_tbl(i).user_or_role_id
308 , l_access_list_tbl(i).arc_user_or_role_type
309 , l_access_list_tbl(i).admin_flag
310 , l_access_list_tbl(i).owner_flag;
311 EXIT WHEN parent_claim_act_access%NOTFOUND;
312 l_access_list_tbl(i).arc_act_access_to_object := 'CLAM';
313 l_access_list_tbl(i).act_access_to_object_id := l_claim_id;
314 i := i + 1;
315 END LOOP;
316 CLOSE parent_claim_act_access;
317
318 IF l_access_list_tbl.COUNT > 0 THEN
319 FOR i IN 1..l_access_list_tbl.LAST LOOP
320 AMS_ACCESS_PVT.Create_Access(
321 p_api_version => l_api_version
322 ,p_init_msg_list => FND_API.G_FALSE
323 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
324 ,x_return_status => l_return_status
325 ,x_msg_count => l_msg_count
326 ,x_msg_data => l_msg_data
327 ,p_commit => FND_API.G_FALSE
328 ,p_access_rec => l_access_list_tbl(i)
329 ,x_access_id => l_access_id
330 );
331 IF l_return_status = fnd_api.g_ret_sts_error THEN
332 RAISE fnd_api.g_exc_error;
333 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
334 RAISE fnd_api.g_exc_unexpected_error;
335 END IF;
336 END LOOP;
337 END IF;
338 -- [END OF BUG 3473501 FIXING]
339
340
341 -- Debug Message
342 IF OZF_DEBUG_LOW_ON THEN
343 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
344 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
345 FND_MSG_PUB.Add;
346 END IF;
347 EXCEPTION
348 WHEN FND_API.G_EXC_ERROR THEN
349 x_return_status := FND_API.G_RET_STS_ERROR;
350
351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353
354 WHEN OTHERS THEN
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
357 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
358 END IF;
359 END create_child_claim;
360
361 ---------------------------------------------------------------------
362 -- PROCEDURE
363 -- update_child_claim
364 --
365 -- PURPOSE
366 -- Update a child claim
367 --
368 -- PARAMETERS
369 -- p_claim : the claim to be update.
370 -- p_line_tbl : the table of lines associated with this claim if any.
371 --
372 -- NOTES
373 ----------------------------------------------------------------------
374 PROCEDURE update_child_claim (
375 p_claim IN child_claim_int_type
376 ,p_line_tbl IN Simple_line_tbl_type
377 ,x_return_status OUT NOCOPY VARCHAR2
378 ,x_new_claim_amount OUT NOCOPY NUMBER
379 )
380 IS
381 l_api_name CONSTANT VARCHAR2(30) := 'Update_Child_Claim';
382 l_api_version CONSTANT NUMBER := 1.0;
383 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
384
385 l_return_status VARCHAR2(30);
386 l_msg_data VARCHAR2(2000);
387 l_msg_count NUMBER;
388 l_child_claim OZF_CLAIM_PVT.claim_rec_type;
389 l_claim_line OZF_CLAIM_LINE_PVT.claim_line_rec_type;
390 l_line_obj_num NUMBER;
391
392 l_object_version_number NUMBER;
393
394 CURSOR version_csr (p_id in number) IS
395 SELECT object_version_number
396 FROM ozf_claims_all
397 WHERE claim_id = p_id;
398
399 CURSOR status_code_csr(p_id in number) IS
400 SELECT status_code
401 FROM ozf_claims_all
402 WHERE claim_id = p_id;
403 l_status_code VARCHAR(30);
404
405 CURSOR line_amount_sum_csr(p_id in number) IS
406 /* BEGIN FIX BUG : split amount becomes null after updating split claim.*/
407 --SELECT SUM(claim_currency_amount)
408 SELECT NVL(SUM(claim_currency_amount), 0)
409 /* END FIX BUG : while calculating claim amount, line_sume_amount should set to 0 if it is null.*/
410 FROM ozf_claim_lines_all
411 WHERE claim_id = p_id;
412 l_line_amount_sum number;
413 l_diff_amount number;
414
415 BEGIN
416 -- Debug Message
417 IF OZF_DEBUG_LOW_ON THEN
418 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
419 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
420 FND_MSG_PUB.Add;
421 END IF;
422 -- Initialize API return status to sucess
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 OPEN version_csr(p_claim.claim_id);
426 FETCH version_csr INTO l_object_version_number;
427 CLOSE version_csr;
428
429 IF p_claim.object_version_number = l_object_version_number THEN
430 BEGIN
431 -- We will do some checking before updating the claim
432 OPEN status_code_csr(p_claim.claim_id);
433 FETCH status_code_csr INTO l_status_code;
434 CLOSE status_code_csr;
435
436 IF l_status_code = G_OPEN_STATUS THEN
437 -- Get the line amount before update
438 OPEN line_amount_sum_csr(p_claim.claim_id);
439 FETCH line_amount_sum_csr into l_line_amount_sum;
440 CLOSE line_amount_sum_csr;
441
442 -- To get the new amount, I first get the difference between claim_amount and sum( old line amount)
443 -- Then, I add this difference to the sum(new line amount), which is obtained from the screen.
444 -- Here p_claim.amount is the old amount, since we don't update it on screen.
445 l_diff_amount := p_claim.amount - l_line_amount_sum;
446 l_child_claim.claim_id := p_claim.claim_id;
447 l_child_claim.object_version_number := p_claim.object_version_number;
448 IF p_claim.line_amount_sum is not null THEN
449 l_child_claim.amount := l_diff_amount + p_claim.line_amount_sum;
450 ELSE
451 l_child_claim.amount := l_diff_amount;
452 END IF;
453
454 OZF_claim_PVT.Update_Claim (
455 p_api_version => l_api_version
456 ,p_init_msg_list => FND_API.G_FALSE
457 ,p_commit => FND_API.G_FALSE
458 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
459 ,x_return_status => l_return_status
460 ,x_msg_data => l_msg_data
461 ,x_msg_count => l_msg_count
462 ,p_claim => l_child_claim
463 ,p_event => G_UPDATE_EVENT
464 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
465 ,x_object_version_number => l_object_version_number
466 );
467 IF l_return_status = FND_API.g_ret_sts_error THEN
468 RAISE FND_API.g_exc_error;
469 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
470 RAISE FND_API.g_exc_unexpected_error;
471 END IF;
472 x_new_claim_amount := l_child_claim.amount;
473
474 -- Now update the lines.
475 -- For each claim_line_id, update the claim_line
476 FOR i in 1..p_line_tbl.COUNT LOOP
477 -- build a record to update in claim lines table.
478 OZF_CLAIM_LINE_PVT.Init_Claim_Line_Rec(x_claim_line_rec => l_claim_line);
479 l_claim_line.claim_line_id := p_line_tbl(i).claim_line_id;
480 l_claim_line.object_version_number := p_line_tbl(i).object_version_number;
481 l_claim_line.claim_id := p_claim.claim_id;
482
483 OZF_CLAIM_LINE_PVT.Update_Claim_Line(
484 p_api_version => l_api_version
485 ,p_init_msg_list => FND_API.g_false
486 ,p_commit => FND_API.g_false
487 ,p_validation_level => FND_API.g_valid_level_full
488 ,x_return_status => l_return_status
489 ,x_msg_count => l_msg_count
490 ,x_msg_data => l_msg_data
491 ,p_claim_line_rec => l_claim_line
492 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
493 ,x_object_version => l_line_obj_num
494 );
495
496 IF l_return_status = FND_API.g_ret_sts_error THEN
497 RAISE FND_API.g_exc_error;
498 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
499 RAISE FND_API.g_exc_unexpected_error;
500 END IF;
501 END LOOP;
502 ELSE
503 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
504 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_WRONG_SPLIT_STATUS');
505 FND_MSG_PUB.ADD;
506 END IF;
507 RAISE FND_API.g_exc_unexpected_error;
508 END IF;
509
510 EXCEPTION
511 WHEN OTHERS THEN
512 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
513 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
514 FND_MSG_PUB.add;
515 END IF;
516 RAISE FND_API.g_exc_error;
517 END;
518 ELSE
519 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
520 FND_MESSAGE.set_name('OZF', 'OZF_REC_VERSION_CHANGED');
521 FND_MSG_PUB.add;
522 END IF;
523 RAISE FND_API.g_exc_error;
524 END IF;
525 -- Debug Message
526 IF OZF_DEBUG_LOW_ON THEN
527 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
528 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
529 FND_MSG_PUB.Add;
530 END IF;
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 WHEN OTHERS THEN
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
539 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
540 END IF;
541 END update_child_claim;
542
543 ---------------------------------------------------------------------
544 -- PROCEDURE
545 -- update_parent_claim
546 --
547 -- PURPOSE
548 -- update parent claim
549 --
550 -- PARAMETERS
551 -- p_claim : the parent claim to be updated.
552 -- p_mode : mode of the opreation. It's to indicate whether the caller is from UI or API.
553 -- p_line_tbl : the table of lines associated with the parent claim if any.
554 --
555 -- NOTES
556 ----------------------------------------------------------------------
557 PROCEDURE update_parent_claim (
558 p_claim IN parent_claim_type
559 ,p_mode IN VARCHAR2
560 ,x_return_status OUT NOCOPY VARCHAR2
561 )
562 IS
563 l_api_name CONSTANT VARCHAR2(30) := 'Update_Parent_Claim';
564 l_api_version CONSTANT NUMBER := 1.0;
565 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
566
567 l_return_status VARCHAR2(30);
568 l_msg_data VARCHAR2(2000);
569 l_msg_count NUMBER;
570
571 l_claim OZF_CLAIM_PVT.claim_rec_type;
572 l_claim_line OZF_CLAIM_LINE_PVT.claim_line_rec_type;
573 l_line_obj_num NUMBER;
574
575 CURSOR claim_info_csr (p_id in number) IS
576 SELECT object_version_number, status_code, amount_adjusted, amount_remaining, currency_code, reason_code_id
577 FROM ozf_claims_all
578 WHERE claim_id = p_id;
579 l_object_version_number NUMBER;
580 l_status_code varchar2(30);
581 l_amount_adjusted number;
582 l_amount_remaining number;
583 l_currency_code varchar2(15);
584 l_reason_code_id number;
585
586
587 BEGIN
588 -- Debug Message
589 IF OZF_DEBUG_LOW_ON THEN
590 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
591 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
592 FND_MSG_PUB.Add;
593 END IF;
594 IF OZF_DEBUG_HIGH_ON THEN
595 ozf_utility_pvt.debug_message('start update parent');
596 END IF;
597
598 -- Initialize API return status to sucess
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600
601 OPEN claim_info_csr(p_claim.claim_id);
602 FETCH claim_info_csr INTO l_object_version_number, l_status_code
603 , l_amount_adjusted, l_amount_remaining, l_currency_code, l_reason_code_id;
604 CLOSE claim_info_csr;
605
606 IF l_amount_adjusted is null THEN
607 l_amount_adjusted := 0;
608 END IF;
609
610 IF p_claim.object_version_number = l_object_version_number THEN
611 -- A claim has to be in the open status or close stasut with amount_remaining not zero to be split.
612 IF ((l_status_code = G_OPEN_STATUS) OR
613 (l_status_code = G_PENDING_CLOSE_STATUS AND
614 ABS(l_amount_remaining) > 0 AND
615 p_mode = OZF_claim_Utility_pvt.G_AUTO_MODE))THEN
616
617
618 -- build the record to call update_claim
619 l_claim.claim_id := p_claim.claim_id;
620 l_claim.object_version_number := p_claim.object_version_number;
621 l_claim.reason_code_id := l_reason_code_id;
622
623 IF p_claim.amount_adjusted is not null THEN
624 l_claim.amount_adjusted := l_amount_adjusted + p_claim.amount_adjusted;
625 ELSE
626 l_claim.amount_adjusted := l_amount_adjusted;
627 END IF;
628
629 IF ((l_amount_remaining > 0) and
630 (l_amount_remaining -OZF_UTILITY_PVT.CurrRound(p_claim.amount_adjusted, l_currency_code)< 0)) THEN
631 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
632 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_SPLT_NG_NOPM');
633 FND_MSG_PUB.Add;
634 END IF;
635 RAISE FND_API.G_EXC_ERROR;
636 END IF;
637
638 IF ((l_amount_remaining < 0) and
639 (l_amount_remaining - OZF_UTILITY_PVT.CurrRound(p_claim.amount_adjusted, l_currency_code) > 0)) THEN
640 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
641 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_SPLIT_PO_OPM');
642 FND_MSG_PUB.Add;
643 END IF;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647 OZF_claim_PVT.Update_Claim (
648 p_api_version => l_api_version
649 ,p_init_msg_list => FND_API.G_FALSE
650 ,p_commit => FND_API.G_FALSE
651 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
652 ,x_return_status => l_return_status
653 ,x_msg_data => l_msg_data
654 ,x_msg_count => l_msg_count
655 ,p_claim => l_claim
656 ,p_event => G_NO_CHANGE_EVENT
657 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
658 ,x_object_version_number => l_object_version_number
659 );
660
661 IF l_return_status = FND_API.g_ret_sts_error THEN
662 RAISE FND_API.g_exc_error;
663 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
664 RAISE FND_API.g_exc_unexpected_error;
665 END IF;
666 ELSE
667 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
668 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_WRONG_SPLIT_STATUS');
669 FND_MSG_PUB.ADD;
670 END IF;
671 RAISE FND_API.g_exc_unexpected_error;
672 END IF;
673 ELSE
674 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
675 FND_MESSAGE.set_name('OZF', 'OZF_REC_VERSION_CHANGED');
676 FND_MSG_PUB.add;
677 END IF;
678 RAISE FND_API.g_exc_error;
679 END IF;
680
681 -- Debug Message
682 IF OZF_DEBUG_LOW_ON THEN
683 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
684 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
685 FND_MSG_PUB.Add;
686 END IF;
687 EXCEPTION
688 WHEN FND_API.G_EXC_ERROR THEN
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 WHEN OTHERS THEN
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
695 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
696 END IF;
697 END update_parent_claim;
698
699 ---------------------------------------------------------------------
700 -- PROCEDURE
701 -- Check_Split_Condition
702 --
703 -- PURPOSE
704 -- Check_Split_Condition
705 --
706 -- PARAMETERS
707 -- p_claim_id : the parent claim to be updated.
708 --
709 -- NOTES
710 -- This procedure check whether the parent claim's amount condition still
711 -- holds after all the child claims are created.
712 ----------------------------------------------------------------------
713 PROCEDURE Check_Split_Condition (
714 p_claim_id IN NUMBER
715 ,x_return_status OUT NOCOPY VARCHAR2
716 )
717 IS
718 l_api_name CONSTANT VARCHAR2(30) := 'Check_Split_Condition';
719 l_api_version CONSTANT NUMBER := 1.0;
720 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
721
722 l_return_status VARCHAR2(30);
723 l_msg_data VARCHAR2(2000);
724 l_msg_count NUMBER;
725
726 CURSOR amount_csr(p_id in NUMBER) IS
727 SELECT amount_remaining
728 FROM ozf_claims_all
729 WHERE claim_id = p_id;
730 l_parent_amount_remaining NUMBER:=0;
731
732 CURSOR line_amt_sum_csr( p_id in NUMBER) IS
733 SELECT NVL(SUM(claim_currency_amount), 0)
734 FROM ozf_claim_lines_all
735 WHERE claim_id = p_id;
736 l_parent_line_sum NUMBER:=0;
737 l_child_line_sum NUMBER:=0;
738 l_child_line_total NUMBER:=0;
739
740 CURSOR amount_sum_csr(p_id in NUMBER) IS
741 SELECT NVL(SUM(amount), 0)
742 FROM ozf_claims_all
743 WHERE split_from_claim_id = p_id;
744 l_child_amount_sum NUMBER:=0;
745
746 CURSOR child_claim_id_csr(p_id in NUMBER) IS
747 SELECT claim_id
748 FROM ozf_claims_all
749 WHERE split_from_claim_id = p_id;
750
751 TYPE child_claim_id_Tbl_Type IS TABLE OF child_claim_id_csr%rowtype
752 INDEX BY BINARY_INTEGER;
753 l_claim_id_tbl child_claim_id_Tbl_Type;
754 i number := 1;
755 BEGIN
756
757 -- Initialize API return status to sucess
758 x_return_status := FND_API.G_RET_STS_SUCCESS;
759 -- Debug Message
760 IF OZF_DEBUG_LOW_ON THEN
761 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
762 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
763 FND_MSG_PUB.Add;
764 END IF;
765
766
767 OPEN amount_csr (p_claim_id);
768 FETCH amount_csr INTO l_parent_amount_remaining;
769 CLOSE amount_csr;
770
771 OPEN line_amt_sum_csr(p_claim_id);
772 FETCH line_amt_sum_csr INTO l_parent_line_sum;
773 CLOSE line_amt_sum_csr;
774
775 OPEN amount_sum_csr(p_claim_id);
776 FETCH amount_sum_csr INTO l_child_amount_sum;
777 CLOSE amount_sum_csr;
778
779 OPEN child_claim_id_csr(p_claim_id);
780 LOOP
781 EXIT WHEN child_claim_id_csr%NOTFOUND;
782 FETCH child_claim_id_csr into l_claim_id_tbl(i);
783 i := i +1;
784 END LOOP;
785 CLOSE child_claim_id_csr;
786
787 FOR i in 1..l_claim_id_tbl.COUNT LOOP
788 OPEN line_amt_sum_csr(l_claim_id_tbl(i).claim_id);
789 FETCH line_amt_sum_csr INTO l_child_line_sum;
790 CLOSE line_amt_sum_csr;
791 l_child_line_total := l_child_line_total + l_child_line_sum;
792 END LOOP;
793
794 -- We want l_child_amount_sum - l_child_line_total <= l_parent_amount_remaining - l_parent_line_sum
795 /* BEGIN FIX BUG -- by mchang 07/13/2001 */
796 --IF (l_child_amount_sum - l_child_line_total) >= (l_parent_amount_remaining - l_parent_line_sum) THEN
797 IF (l_child_amount_sum - l_child_line_total) > (l_parent_amount_remaining - l_parent_line_sum) THEN
798 /* END FIG BUG */
799 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
800 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_SPLIT_TOO_BIG');
801 FND_MSG_PUB.ADD;
802 END IF;
803 RAISE FND_API.g_exc_error;
804 END IF;
805
806 -- Debug Message
807 IF OZF_DEBUG_LOW_ON THEN
808 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
809 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
810 FND_MSG_PUB.Add;
811 END IF;
812
813 EXCEPTION
814 WHEN FND_API.G_EXC_ERROR THEN
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818 WHEN OTHERS THEN
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
821 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
822 END IF;
823 END Check_Split_Condition;
824
825 ---------------------------------------------------------------------
826 -- PROCEDURE
827 -- get_line_table
828 --
829 -- PURPOSE
830 -- convert a comma dilimited string to a simple_line_tble
831 -- The string is pairs of xxx,xxx
832 --
833 -- PARAMETERS
834 -- p_line_string
835 -- x_line_table
836 -- x_return_status
837 --
838 -- NOTES
839 ---------------------------------------------------------------------
840 PROCEDURE get_line_table(p_line_string in varchar2,
841 x_line_table OUT NOCOPY simple_line_tbl_type,
842 x_return_status OUT NOCOPY varchar2)
843 IS
844 l_return_status varchar2(3);
845 l_line_string varchar2(4000):= p_line_string; --Bugfix 5305296
846 l_index number;
847 l_temp VARCHAR2(20);
848 l_temp_index NUMBER;
849 i number:=1; -- line table index start from 1
850 BEGIN
851 -- Initialize API return status to sucess
852 x_return_status := FND_API.G_RET_STS_SUCCESS;
853
854 -- split (claim_line_id, object_version_number) pair
855 l_index := INSTR(l_line_string, ',', 1, 2);
856 WHILE (l_index >0)LOOP
857
858 -- get the pair string
859 l_temp := SUBSTR(l_line_string, 1, l_index-1);
860 l_temp_index := INSTR(l_temp, ',',1,1);
861 x_line_table(i).claim_line_id := TO_NUMBER(SUBSTR(l_temp,1, l_temp_index -1));
862 x_line_table(i).object_version_number := TO_NUMBER(SUBSTR(l_temp, l_temp_index+1));
863
864 -- get new string and change index
865 l_line_string := SUBSTR(l_line_string, l_index +1);
866 l_index := INSTR(l_line_string, ',', 1, 2);
867 i:=i+1;
868 END LOOP;
869
870 -- Get the last pair
871 l_temp_index := INSTR(l_line_string, ',',1,1);
872 IF l_temp_index = 0 THEN
873 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
874 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SPLT_LINE_STR_WRG');
875 FND_MSG_PUB.add;
876 END IF;
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878 ELSE
879 x_line_table(i).claim_line_id := TO_NUMBER(SUBSTR(l_line_string,1, l_temp_index -1));
880 x_line_table(i).object_version_number := TO_NUMBER(SUBSTR(l_line_string, l_temp_index +1));
881 END IF;
882
883 EXCEPTION
884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886 WHEN OTHERS THEN
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
889 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SPLT_LINE_TBL_ERR');
890 FND_MSG_PUB.add;
891 END IF;
892 END get_line_table;
893
894 ---------------------------------------------------------------------
895 -- PROCEDURE
896 -- autosplit_line
897 --
898 -- PURPOSE
899 -- split a claim line of a parent based the child claim information
900 --
901 -- PARAMETERS
902 -- p_child_claim_tbl
903 -- x_child_claim_tbl
904 -- x_return_status
905 --
906 -- NOTES
907 ---------------------------------------------------------------------
908 PROCEDURE autosplit_line(p_child_claim_tbl in Child_Claim_tbl_type,
909 x_child_claim_tbl OUT NOCOPY Child_Claim_tbl_type,
910 x_return_status OUT NOCOPY varchar2)
911 IS
912 l_return_status varchar2(3);
913 l_msg_data VARCHAR2(2000);
914 l_msg_count NUMBER;
915 l_child_claim_tbl Child_Claim_tbl_type := p_child_claim_tbl;
916
917 CURSOR parent_amount_rem_csr(p_id in NUMBER) IS
918 SELECT amount_remaining
919 FROM ozf_claims_all
920 WHERE claim_id = p_id;
921 l_parent_amount_remaining NUMBER:=0;
922
923 CURSOR line_amount_sum_csr(p_id in number) IS
924 SELECT NVL(SUM(claim_currency_amount), 0)
925 FROM ozf_claim_lines_all
926 WHERE claim_id = p_id;
927 l_current_sum number;
928
929 CURSOR line_count_csr(p_id in number) IS
930 SELECT Count(claim_line_id)
931 FROM ozf_claim_lines_all
932 WHERE claim_id = p_id;
933 l_line_count number:=0;
934
935 CURSOR line_associate_csr(p_id in number) IS
936 SELECT earnings_associated_flag
937 FROM ozf_claim_lines_all
938 WHERE claim_id = p_id;
939 l_earning_flag varchar2(1);
940
941 l_no_line_found boolean := true;
942
943 CURSOR line_info_csr(p_id in number) IS
944 SELECT *
945 FROM ozf_claim_lines_all
946 where claim_id = p_id;
947
948 l_claim_line line_info_csr%rowtype;
949 l_pvt_claim_line OZF_CLAIM_LINE_PVT.claim_line_rec_type ;
950 l_parent_line OZF_CLAIM_LINE_PVT.claim_line_rec_type;
951 l_claim_line_id number;
952
953 l_new_split_amount number :=0;
954 l_line_obj_num number;
955 BEGIN
956
957 -- Initialize API return status to sucess
958 x_return_status := FND_API.G_RET_STS_SUCCESS;
959
960 -- Under the following condition, I will create lines with the child amount for the parents.
961 -- These line information will be stored in the line_table for the child claim.
962 -- These new lines will be moved to the child claims in a later stage
963
964 -- Get the sum of the line amount for the parent claim
965 -- If the sum of the line amount = claim amount and
966 -- there is only one line and
967 -- there is no utlization and
968 -- there is no line_table for any of the child claim
969 -- for this line, we will create new claim line for the parent claim based on the child claim amount.
970 OPEN parent_amount_rem_csr(l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id);
971 FETCH parent_amount_rem_csr into l_parent_amount_remaining;
972 CLOSE parent_amount_rem_csr;
973
974 OPEN line_amount_sum_csr(l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id);
975 FETCH line_amount_sum_csr into l_current_sum;
976 CLOSE line_amount_sum_csr;
977
978 OPEN line_count_csr(l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id);
979 FETCH line_count_csr into l_line_count;
980 CLOSE line_count_csr;
981
982 OPEN line_associate_csr(l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id);
983 FETCH line_associate_csr into l_earning_flag;
984 CLOSE line_associate_csr;
985
986 -- If there is no claim line moved between parent and child
987 For i in 1..l_child_claim_tbl.count LOOP
988 l_no_line_found :=l_child_claim_tbl(i).line_table is null;
989 exit when not l_no_line_found;
990 END LOOP;
991
992
993 IF l_current_sum = l_parent_amount_remaining AND
994 l_line_count = 1 AND
995 (l_earning_flag = 'F' or l_earning_flag is null) AND
996 l_no_line_found THEN
997
998 -- get the amount to be split
999 For i in 1..l_child_claim_tbl.count LOOP
1000 l_new_split_amount := l_new_split_amount + l_child_claim_tbl(i).amount;
1001 END LOOP;
1002
1003 -- store the line information in a local variable
1004 OPEN line_info_csr(l_child_claim_tbl(1).parent_claim_id);
1005 FETCH line_info_csr into l_claim_line;
1006 CLOSE line_info_csr;
1007
1008 IF l_new_split_amount = l_parent_amount_remaining THEN
1009 IF OZF_DEBUG_HIGH_ON THEN
1010 ozf_utility_PVT.debug_message('delete claim line amount ' || l_claim_line.claim_line_id);
1011 END IF;
1012
1013 delete from ozf_claim_lines_all
1014 where claim_line_id = l_claim_line.claim_line_id;
1015 ELSE
1016
1017 IF OZF_DEBUG_HIGH_ON THEN
1018 ozf_utility_PVT.debug_message('update parent claim amount ' ||l_claim_line.claim_line_id);
1019 END IF;
1020
1021 OZF_CLAIM_LINE_PVT.Init_Claim_Line_Rec(x_claim_line_rec => l_parent_line);
1022 l_parent_line.claim_line_id := l_claim_line.claim_line_id;
1023 l_parent_line.object_version_number := l_claim_line.object_version_number;
1024 l_parent_line.claim_id := l_claim_line.claim_id;
1025 l_parent_line.claim_currency_amount := l_parent_amount_remaining - l_new_split_amount;
1026
1027
1028 --Bug Fix 3405910
1029 IF (l_claim_line.quantity is not null
1030 AND l_claim_line.quantity <> FND_API.G_MISS_NUM)
1031 AND
1032 (l_claim_line.rate is not null
1033 AND l_claim_line.rate <> FND_API.G_MISS_NUM)
1034 THEN
1035 IF mod(l_parent_line.claim_currency_amount , l_claim_line.rate) = 0 THEN
1036 l_parent_line.quantity := l_parent_line.claim_currency_amount/l_claim_line.rate;
1037 ELSE
1038 --If not a whole number, then clear the parent lines values.
1039 l_parent_line.quantity := fnd_api.g_miss_num;
1040 l_parent_line.rate := fnd_api.g_miss_num;
1041 l_parent_line.quantity_uom := fnd_api.g_miss_char;
1042 END IF;
1043 END IF;
1044 --End of Bug Fix 3405910
1045
1046
1047 OZF_CLAIM_LINE_PVT.Update_Claim_Line(
1048 p_api_version => 1.0
1049 ,p_init_msg_list => FND_API.g_false
1050 ,p_commit => FND_API.g_false
1051 ,p_validation_level => FND_API.g_valid_level_full
1052 ,x_return_status => l_return_status
1053 ,x_msg_count => l_msg_count
1054 ,x_msg_data => l_msg_data
1055 ,p_claim_line_rec => l_parent_line
1056 ,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
1057 ,x_object_version => l_line_obj_num
1058 );
1059
1060 IF l_return_status = FND_API.g_ret_sts_error THEN
1061 RAISE FND_API.g_exc_error;
1062 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1063 RAISE FND_API.g_exc_unexpected_error;
1064 END IF;
1065 /*
1066 update ozf_claim_lines_all
1067 set claim_currency_amount = l_parent_amount_remaining - l_new_split_amount,
1068 quantity = null
1069 where claim_line_id = l_claim_line.claim_line_id;
1070 */
1071 END IF;
1072
1073 For i in 1..l_child_claim_tbl.count LOOP
1074 -- construct the new line record based on the old line record
1075 -- keep the rate info, but nullify the quantity
1076 -- assign claim_currency_amount := l_child_claim_tbl(i).amount;
1077 l_pvt_claim_line.claim_id := l_claim_line.claim_id;
1078 l_pvt_claim_line.split_from_claim_line_id := l_claim_line.split_from_claim_line_id;
1079
1080 -- assign claim_currency_amount := l_child_claim_tbl(i).amount;
1081 l_pvt_claim_line.claim_currency_amount := l_child_claim_tbl(i).amount;
1082 /* l_pvt_claim_line.acctd_amount := l_claim_line.acctd_amount;
1083 l_pvt_claim_line.currency_code := l_claim_line.currency_code ;
1084 l_pvt_claim_line.exchange_rate_type := l_claim_line.exchange_rate_type ;
1085 l_pvt_claim_line.exchange_rate_date := l_claim_line.exchange_rate_date;
1086 l_pvt_claim_line.exchange_rate := l_claim_line.exchange_rate;
1087 */
1088 l_pvt_claim_line.set_of_books_id := l_claim_line.set_of_books_id;
1089 l_pvt_claim_line.valid_flag := l_claim_line.valid_flag;
1090 l_pvt_claim_line.source_object_id := l_claim_line.source_object_id;
1091 l_pvt_claim_line.source_object_class := l_claim_line.source_object_class;
1092 l_pvt_claim_line.source_object_type_id := l_claim_line.source_object_type_id;
1093 l_pvt_claim_line.plan_id := l_claim_line.plan_id;
1094 l_pvt_claim_line.offer_id := l_claim_line.offer_id;
1095 l_pvt_claim_line.utilization_id := l_claim_line.utilization_id;
1096 l_pvt_claim_line.payment_method := l_claim_line.payment_method;
1097 l_pvt_claim_line.payment_reference_id := l_claim_line.payment_reference_id;
1098 l_pvt_claim_line.payment_reference_number := l_claim_line.payment_reference_number;
1099 l_pvt_claim_line.payment_reference_date := l_claim_line.payment_reference_date;
1100 l_pvt_claim_line.voucher_id := l_claim_line.voucher_id;
1101 l_pvt_claim_line.voucher_number := l_claim_line.voucher_number;
1102 l_pvt_claim_line.payment_status := l_claim_line.payment_status;
1103 l_pvt_claim_line.approved_flag := l_claim_line.approved_flag ;
1104 l_pvt_claim_line.approved_date := l_claim_line.approved_date;
1105 l_pvt_claim_line.approved_by := l_claim_line.approved_by ;
1106 l_pvt_claim_line.settled_date := l_claim_line.settled_date;
1107 l_pvt_claim_line.settled_by := l_claim_line.settled_by;
1108 l_pvt_claim_line.performance_complete_flag := l_claim_line.performance_complete_flag;
1109 l_pvt_claim_line.performance_attached_flag := l_claim_line.performance_attached_flag;
1110 l_pvt_claim_line.item_id := l_claim_line.item_id;
1111 l_pvt_claim_line.item_description := l_claim_line.item_description ;
1112 l_pvt_claim_line.quantity := null;
1113 l_pvt_claim_line.quantity_uom := l_claim_line.quantity_uom;
1114 l_pvt_claim_line.rate := l_claim_line.rate;
1115 l_pvt_claim_line.activity_type := l_claim_line.activity_type;
1116 l_pvt_claim_line.activity_id := l_claim_line.activity_id;
1117 l_pvt_claim_line.related_cust_account_id := l_claim_line.related_cust_account_id;
1118 l_pvt_claim_line.relationship_type := l_claim_line.relationship_type;
1119 l_pvt_claim_line.earnings_associated_flag := l_claim_line.earnings_associated_flag;
1120 l_pvt_claim_line.comments := l_claim_line.comments;
1121 l_pvt_claim_line.tax_code := l_claim_line.tax_code;
1122 l_pvt_claim_line.attribute_category := l_claim_line.attribute_category;
1123 l_pvt_claim_line.attribute1 := l_claim_line.attribute1;
1124 l_pvt_claim_line.attribute2 := l_claim_line.attribute2;
1125 l_pvt_claim_line.attribute3 := l_claim_line.attribute3;
1126 l_pvt_claim_line.attribute4 := l_claim_line.attribute4;
1127 l_pvt_claim_line.attribute5 := l_claim_line.attribute5;
1128 l_pvt_claim_line.attribute6 := l_claim_line.attribute6;
1129 l_pvt_claim_line.attribute7 := l_claim_line.attribute7;
1130 l_pvt_claim_line.attribute8 := l_claim_line.attribute8;
1131 l_pvt_claim_line.attribute9 := l_claim_line.attribute9;
1132 l_pvt_claim_line.attribute10 := l_claim_line.attribute10;
1133 l_pvt_claim_line.attribute11 := l_claim_line.attribute11;
1134 l_pvt_claim_line.attribute12 := l_claim_line.attribute12;
1135 l_pvt_claim_line.attribute13 := l_claim_line.attribute13;
1136 l_pvt_claim_line.attribute14 := l_claim_line.attribute14;
1137 l_pvt_claim_line.attribute15 := l_claim_line.attribute15;
1138 l_pvt_claim_line.org_id := l_claim_line.org_id ;
1139 -- bugfix 4921610
1140 l_pvt_claim_line.item_type := l_claim_line.item_type;
1141
1142
1143 Ozf_Claim_Line_Pvt.Create_Claim_Line(
1144 p_api_version => 1.0
1145 , p_init_msg_list => FND_API.g_false
1146 , p_commit => FND_API.g_false
1147 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1148 , x_return_status => l_return_status
1149 , x_msg_data => l_msg_data
1150 , x_msg_count => l_msg_count
1151 , p_claim_line_rec => l_pvt_claim_line
1152 , p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
1153 , x_claim_line_id => l_claim_line_id
1154 );
1155 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1156 RAISE FND_API.g_exc_unexpected_error;
1157 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1158 RAISE FND_API.g_exc_unexpected_error;
1159 END IF;
1160
1161 --update the l_child_claim_tbl record
1162 -- line_amount_sum := amount
1163 -- line_table := l_claim_line_id || ',1';
1164 l_child_claim_tbl(i).line_amount_sum := l_child_claim_tbl(i).amount;
1165 l_child_claim_tbl(i).line_table := l_claim_line_id || ',1';
1166
1167 END LOOP;
1168 ELSE
1169 -- I will not construct the line information for these child claims
1170 null;
1171 END IF;
1172 x_child_claim_tbl := l_child_claim_tbl;
1173 EXCEPTION
1174 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1176 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1177 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AUTSPLT_LINE_ERR');
1178 FND_MSG_PUB.add;
1179 END IF;
1180 WHEN OTHERS THEN
1181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1182 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1183 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_AUTSPLT_LINE_ERR');
1184 FND_MSG_PUB.add;
1185 END IF;
1186 END autosplit_line;
1187
1188 ---------------------------------------------------------------------
1189 -- PROCEDURE
1190 -- create_child_claim_tbl
1191 --
1192 -- PURPOSE
1193 -- Split a child claim
1194 --
1195 -- PARAMETERS
1196 -- p_claim : the new claim to be created.
1197 -- p_line_tbl : the table of lines associated with this new claim if any.
1198 --
1199 -- NOTES
1200 ----------------------------------------------------------------------
1201 PROCEDURE create_child_claim_tbl (
1202 p_api_version IN NUMBER
1203 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1204 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1205 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1206 ,x_return_status OUT NOCOPY VARCHAR2
1207 ,x_msg_data OUT NOCOPY VARCHAR2
1208 ,x_msg_count OUT NOCOPY NUMBER
1209 ,p_child_claim_tbl IN Child_Claim_tbl_type
1210 ,p_mode IN VARCHAR2
1211 )
1212 IS
1213 l_api_name CONSTANT VARCHAR2(30) := 'Create_Child_Claim_tbl';
1214 l_api_version CONSTANT NUMBER := 1.0;
1215 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1216
1217 l_return_status VARCHAR2(30);
1218 l_msg_data VARCHAR2(2000);
1219 l_msg_count NUMBER;
1220 l_child_claim_tbl Child_Claim_tbl_type := p_child_claim_tbl;
1221 l_x_child_claim_tbl Child_Claim_tbl_type;
1222 l_child_claim child_claim_int_type;
1223 l_parent_claim Parent_Claim_Type;
1224 l_line_tbl Simple_line_tbl_type;
1225 l_amount_adjusted number := 0;
1226 l_new_split_amount number := 0;
1227
1228 l_temp_claim_rec ozf_claim_pvt.claim_rec_type;
1229 l_temp_need_to_create VARCHAR2(20);
1230 l_temp_clm_history_id NUMBER;
1231 l_access varchar2(1) := 'N';
1232 l_claim_class varchar2(30);
1233
1234
1235 CURSOR claim_class_csr(p_claim_id in number) IS
1236 SELECT claim_class
1237 FROM ozf_claims_all
1238 WHERE claim_id = p_claim_id;
1239
1240 BEGIN
1241 -- Standard begin of API savepoint
1242 SAVEPOINT Create_Child_Tbl_PVT;
1243
1244 -- Standard call to check for call compatibility.
1245 IF NOT FND_API.Compatible_API_Call (
1246 l_api_version,
1247 p_api_version,
1248 l_api_name,
1249 G_PKG_NAME)
1250 THEN
1251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1252 END IF;
1253 -- Debug Message
1254 IF OZF_DEBUG_LOW_ON THEN
1255 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1256 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
1257 FND_MSG_PUB.Add;
1258 END IF;
1259 --Initialize message list if p_init_msg_list is TRUE.
1260 IF FND_API.To_Boolean (p_init_msg_list) THEN
1261 FND_MSG_PUB.initialize;
1262 END IF;
1263
1264
1265 -- Initialize API return status to sucess
1266 x_return_status := FND_API.G_RET_STS_SUCCESS;
1267 IF p_mode = 'MANU' THEN
1268 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
1269 P_Api_Version_Number => 1.0,
1270 P_Init_Msg_List => FND_API.G_FALSE,
1271 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1272 P_Commit => FND_API.G_FALSE,
1273 P_object_id => l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id,
1274 P_object_type => G_CLAIM_OBJECT_TYPE,
1275 P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1)),
1276 X_Return_Status => l_return_status,
1277 X_Msg_Count => l_msg_count,
1278 X_Msg_Data => l_msg_data,
1279 X_access => l_access);
1280
1281 IF l_access = 'N' THEN
1282 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1283 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_NO_ACCESS');
1284 FND_MSG_PUB.Add;
1285 END IF;
1286 RAISE FND_API.G_EXC_ERROR;
1287 END IF;
1288 END IF;
1289
1290
1291 --Get the parent claim class.
1292 OPEN claim_class_csr(l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id);
1293 FETCH claim_class_csr INTO l_claim_class;
1294 CLOSE claim_class_csr;
1295 --End of Claim amount check.
1296
1297 FOR i IN 1..l_child_claim_tbl.count LOOP
1298 --Check for the claim amount, if it is invalid amount throw an exception.
1299 IF l_claim_class = 'CLAIM'
1300 OR l_claim_class = 'DEDUCTION'
1301 THEN
1302 IF l_child_claim_tbl(i).amount <= 0
1303 THEN
1304 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1305 THEN
1306 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SPLIT_POS_AMT_ERR');
1307 FND_MSG_PUB.add;
1308 END IF;
1309 RAISE FND_API.g_exc_error;
1310 END IF;
1311 ELSIF l_claim_class = 'CHARGE'
1312 OR l_claim_class = 'OVERPAYMENT'
1313 THEN
1314 IF l_child_claim_tbl(i).amount >= 0
1315 THEN
1316 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1317 THEN
1318 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_SPLIT_NEG_AMT_ERR');
1319 FND_MSG_PUB.add;
1320 END IF;
1321 RAISE FND_API.g_exc_error;
1322 END IF;
1323 END IF;
1324 --End of claim amount check.
1325 END LOOP;
1326
1327
1328 -- automatically split a claim line for the parent, if needed, based on the child information
1329 autosplit_line(p_child_claim_tbl => l_child_claim_tbl,
1330 x_child_claim_tbl => l_x_child_claim_tbl,
1331 x_return_status => l_return_status);
1332 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1333 RAISE FND_API.g_exc_unexpected_error;
1334 END IF;
1335 l_child_claim_tbl := l_x_child_claim_tbl;
1336
1337 l_new_split_amount := 0;
1338 For i in 1..l_child_claim_tbl.count LOOP
1339 l_child_claim.claim_id := l_child_claim_tbl(i).claim_id;
1340 l_child_claim.object_version_number := l_child_claim_tbl(i).object_version_number;
1341 l_child_claim.claim_type_id := l_child_claim_tbl(i).claim_type_id;
1342 l_child_claim.amount := l_child_claim_tbl(i).amount;
1343 l_child_claim.line_amount_sum := l_child_claim_tbl(i).line_amount_sum;
1344 l_child_claim.reason_code_id := l_child_claim_tbl(i).reason_code_id;
1345 l_child_claim.parent_claim_id := l_child_claim_tbl(i).parent_claim_id;
1346
1347 IF l_child_claim_tbl(i).line_table is not null AND
1348 l_child_claim_tbl(i).line_table <> FND_API.G_MISS_CHAR THEN
1349 get_line_table (p_line_string => l_child_claim_tbl(i).line_table,
1350 x_line_table => l_line_tbl,
1351 x_return_status => l_return_status
1352 );
1353 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1354 RAISE FND_API.g_exc_unexpected_error;
1355 END IF;
1356 END IF;
1357
1358 create_child_claim (
1359 p_claim => l_child_claim
1360 ,p_line_tbl => l_line_tbl
1361 ,x_return_status => l_return_status
1362 );
1363 IF l_return_status = FND_API.g_ret_sts_error THEN
1364 RAISE FND_API.g_exc_error;
1365 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1366 RAISE FND_API.g_exc_unexpected_error;
1367 END IF;
1368
1369 -- create_child_claim sucessful.
1370 -- Reinitialize line table is needed.
1371 IF l_line_tbl.count >0 THEN
1372 l_line_tbl.delete;
1373 END IF;
1374 l_new_split_amount := l_new_split_amount + l_child_claim_tbl(i).amount;
1375 END LOOP;
1376
1377 l_parent_claim.claim_id := l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id;
1378 l_parent_claim.object_version_number := l_child_claim_tbl(l_child_claim_tbl.count).parent_object_ver_num;
1379
1380 -- When creating child claims, amount_adjusted = claim amount of all new claims.
1381 -- The l_old_split_amount is the amount that has been split. It should be 0 during child creation.
1382 -- change this line: l_parent_claim.amount_adjusted := l_new_split_amount - l_old_split_amount;
1383 l_parent_claim.amount_adjusted := l_new_split_amount;
1384
1385
1386
1387 update_parent_claim (
1388 p_claim => l_parent_claim
1389 ,p_mode => p_mode
1390 ,x_return_status => l_return_status
1391 );
1392 IF l_return_status = FND_API.g_ret_sts_error THEN
1393 RAISE FND_API.g_exc_error;
1394 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1395 RAISE FND_API.g_exc_unexpected_error;
1396 END IF;
1397
1398
1399 --bug 335704
1400 -- create claim history if needed
1401 IF (not g_history_created ) THEN
1402 l_temp_claim_rec.claim_id := l_child_claim_tbl(l_child_claim_tbl.count).parent_claim_id;
1403 l_temp_claim_rec.object_version_number := l_child_claim_tbl(l_child_claim_tbl.count).parent_object_ver_num;
1404
1405 OZF_CLAIM_PVT.Create_Claim_History (
1406 p_api_version => l_api_version
1407 ,p_init_msg_list => FND_API.G_FALSE
1408 ,p_commit => FND_API.G_FALSE
1409 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1410 ,x_return_status => l_return_status
1411 ,x_msg_data => l_msg_data
1412 ,x_msg_count => l_msg_count
1413 ,p_claim => l_temp_claim_rec
1414 ,p_event => G_SPLIT_EVENT
1415 ,x_need_to_create => l_temp_need_to_create
1416 ,x_claim_history_id => l_temp_clm_history_id
1417 );
1418 IF l_return_status = FND_API.g_ret_sts_error THEN
1419 RAISE FND_API.g_exc_error;
1420 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1421 RAISE FND_API.g_exc_unexpected_error;
1422 END IF;
1423 ELSE
1424 g_history_created := false;
1425 END IF;
1426 --Standard check of commit
1427 IF FND_API.To_Boolean ( p_commit ) THEN
1428 COMMIT WORK;
1429 END IF;
1430
1431 -- Debug Message
1432 IF OZF_DEBUG_LOW_ON THEN
1433 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1434 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
1435 FND_MSG_PUB.Add;
1436 END IF;
1437
1438 --Standard call to get message count and if count=1, get the message
1439 FND_MSG_PUB.Count_And_Get (
1440 p_encoded => FND_API.G_FALSE,
1441 p_count => x_msg_count,
1442 p_data => x_msg_data
1443 );
1444 EXCEPTION
1445 WHEN FND_API.G_EXC_ERROR THEN
1446 ROLLBACK TO Create_Child_Tbl_PVT;
1447 x_return_status := FND_API.G_RET_STS_ERROR;
1448 -- Standard call to get message count and if count=1, get the message
1449 FND_MSG_PUB.Count_And_Get (
1450 p_encoded => FND_API.G_FALSE,
1451 p_count => x_msg_count,
1452 p_data => x_msg_data
1453 );
1454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1455 ROLLBACK TO Create_Child_Tbl_PVT;
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 -- Standard call to get message count and if count=1, get the message
1458 FND_MSG_PUB.Count_And_Get (
1459 p_encoded => FND_API.G_FALSE,
1460 p_count => x_msg_count,
1461 p_data => x_msg_data
1462 );
1463 WHEN OTHERS THEN
1464 ROLLBACK TO Create_Child_Tbl_PVT;
1465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1467 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1468 END IF;
1469 -- Standard call to get message count and if count=1, get the message
1470 FND_MSG_PUB.Count_And_Get (
1471 p_encoded => FND_API.G_FALSE,
1472 p_count => x_msg_count,
1473 p_data => x_msg_data
1474 );
1475 END create_child_claim_tbl;
1476
1477 ---------------------------------------------------------------------
1478 -- PROCEDURE
1479 -- update_child_claim_tbl
1480 --
1481 -- PURPOSE
1482 -- Update a child claim
1483 --
1484 -- PARAMETERS
1485 -- p_claim : the new claim to be created.
1486 -- p_line_tbl : the table of lines associated with this new claim if any.
1487 --
1488 -- NOTES
1489 ----------------------------------------------------------------------
1490 PROCEDURE update_child_claim_tbl (
1491 p_api_version IN NUMBER
1492 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1493 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1494 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1495 ,x_return_status OUT NOCOPY VARCHAR2
1496 ,x_msg_data OUT NOCOPY VARCHAR2
1497 ,x_msg_count OUT NOCOPY NUMBER
1498 ,p_child_claim_tbl IN Child_Claim_tbl_type
1499 ,p_mode IN VARCHAR2
1500 )
1501 IS
1502 l_api_name CONSTANT VARCHAR2(30) := 'Update_Child_Claim_tbl';
1503 l_api_version CONSTANT NUMBER := 1.0;
1504 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1505
1506 l_return_status VARCHAR2(30);
1507 l_msg_data VARCHAR2(2000);
1508 l_msg_count NUMBER;
1509 l_child_claim child_claim_int_type;
1510 l_parent_claim parent_Claim_type;
1511 l_line_tbl Simple_line_tbl_type;
1512 l_amount_adjusted number := 0;
1513 l_new_split_amount number := 0;
1514 l_old_split_amount number := 0;
1515 l_new_child_claim_amount number := 0;
1516
1517 l_temp_claim_rec ozf_claim_pvt.claim_rec_type;
1518 l_temp_need_to_create VARCHAR2(20);
1519 l_temp_clm_history_id NUMBER;
1520 l_access varchar2(1) := 'N';
1521 BEGIN
1522 -- Standard begin of API savepoint
1523 SAVEPOINT Update_Child_Tbl_PVT;
1524
1525 -- Standard call to check for call compatibility.
1526 IF NOT FND_API.Compatible_API_Call (
1527 l_api_version,
1528 p_api_version,
1529 l_api_name,
1530 G_PKG_NAME)
1531 THEN
1532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1533 END IF;
1534 -- Debug Message
1535 IF OZF_DEBUG_LOW_ON THEN
1536 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1537 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
1538 FND_MSG_PUB.Add;
1539 END IF;
1540 --Initialize message list if p_init_msg_list is TRUE.
1541 IF FND_API.To_Boolean (p_init_msg_list) THEN
1542 FND_MSG_PUB.initialize;
1543 END IF;
1544
1545 -- Initialize API return status to sucess
1546 x_return_status := FND_API.G_RET_STS_SUCCESS;
1547
1548 IF p_mode = 'MANU' THEN
1549 OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
1550 P_Api_Version_Number => 1.0,
1551 P_Init_Msg_List => FND_API.G_FALSE,
1552 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1553 P_Commit => FND_API.G_FALSE,
1554 P_object_id => p_child_claim_tbl(p_child_claim_tbl.count).parent_claim_id,
1555 P_object_type => G_CLAIM_OBJECT_TYPE,
1556 P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1)),
1557 X_Return_Status => l_return_status,
1558 X_Msg_Count => l_msg_count,
1559 X_Msg_Data => l_msg_data,
1560 X_access => l_access);
1561
1562 IF l_access = 'N' THEN
1563 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1564 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_NO_ACCESS');
1565 FND_MSG_PUB.Add;
1566 END IF;
1567 RAISE FND_API.G_EXC_ERROR;
1568 END IF;
1569 END IF;
1570
1571
1572 For i in 1..p_child_claim_tbl.count LOOP
1573 l_child_claim.claim_id := p_child_claim_tbl(i).claim_id;
1574 l_child_claim.object_version_number := p_child_claim_tbl(i).object_version_number;
1575 l_child_claim.claim_type_id := p_child_claim_tbl(i).claim_type_id;
1576 l_child_claim.amount := p_child_claim_tbl(i).amount;
1577 l_child_claim.line_amount_sum := p_child_claim_tbl(i).line_amount_sum;
1578 l_child_claim.reason_code_id := p_child_claim_tbl(i).reason_code_id;
1579 l_child_claim.parent_claim_id := p_child_claim_tbl(i).parent_claim_id;
1580
1581 IF p_child_claim_tbl(i).line_table is not null AND
1582 p_child_claim_tbl(i).line_table <> FND_API.G_MISS_CHAR THEN
1583 get_line_table (p_line_string => p_child_claim_tbl(i).line_table,
1584 x_line_table => l_line_tbl,
1585 x_return_status => l_return_status
1586 );
1587 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1588 RAISE FND_API.g_exc_unexpected_error;
1589 END IF;
1590 END IF;
1591 update_child_claim (
1592 p_claim => l_child_claim
1593 ,p_line_tbl => l_line_tbl
1594 ,x_return_status => l_return_status
1595 ,x_new_claim_amount => l_new_child_claim_amount
1596 );
1597 IF l_return_status = FND_API.g_ret_sts_error THEN
1598 RAISE FND_API.g_exc_error;
1599 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1600 RAISE FND_API.g_exc_unexpected_error;
1601 END IF;
1602
1603 -- create_child_claim sucessful.
1604
1605 -- Reinitialize line table is needed.
1606 IF l_line_tbl.count >0 THEN
1607 l_line_tbl.delete;
1608 END IF;
1609
1610 l_old_split_amount := l_old_split_amount + p_child_claim_tbl(i).amount;
1611 l_new_split_amount := l_new_split_amount + l_new_child_claim_amount;
1612 END LOOP;
1613
1614 l_parent_claim.claim_id := p_child_claim_tbl(p_child_claim_tbl.count).parent_claim_id;
1615 l_parent_claim.object_version_number := p_child_claim_tbl(p_child_claim_tbl.count).parent_object_ver_num;
1616
1617 -- When creating child claims, amount_adjusted = sum(new child claim amount) - sum(old child claim amount)
1618 l_parent_claim.amount_adjusted := l_new_split_amount - l_old_split_amount;
1619 update_parent_claim (
1620 p_claim => l_parent_claim
1621 ,p_mode => p_mode
1622 ,x_return_status => l_return_status
1623 );
1624 IF l_return_status = FND_API.g_ret_sts_error THEN
1625 RAISE FND_API.g_exc_error;
1626 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1627 RAISE FND_API.g_exc_unexpected_error;
1628 END IF;
1629
1630 --bug 3357204
1631 -- create claim history if needed
1632 if (g_history_created = false) THEN
1633 l_temp_claim_rec.claim_id := p_child_claim_tbl(p_child_claim_tbl.count).parent_claim_id;
1634 l_temp_claim_rec.object_version_number := p_child_claim_tbl(p_child_claim_tbl.count).parent_object_ver_num;
1635
1636 OZF_CLAIM_PVT.Create_Claim_History (
1637 p_api_version => l_api_version
1638 ,p_init_msg_list => FND_API.G_FALSE
1639 ,p_commit => FND_API.G_FALSE
1640 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1641 ,x_return_status => l_return_status
1642 ,x_msg_data => l_msg_data
1643 ,x_msg_count => l_msg_count
1644 ,p_claim => l_temp_claim_rec
1645 ,p_event => G_SPLIT_EVENT
1646 ,x_need_to_create => l_temp_need_to_create
1647 ,x_claim_history_id => l_temp_clm_history_id
1648 );
1649 g_history_created := true;
1650 END IF;
1651
1652 --Standard check of commit
1653 IF FND_API.To_Boolean ( p_commit ) THEN
1654 COMMIT WORK;
1655 END IF;
1656
1657 -- Debug Message
1658 IF OZF_DEBUG_LOW_ON THEN
1659 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1660 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
1661 FND_MSG_PUB.Add;
1662 END IF;
1663
1664 --Standard call to get message count and if count=1, get the message
1665 FND_MSG_PUB.Count_And_Get (
1666 p_encoded => FND_API.G_FALSE,
1667 p_count => x_msg_count,
1668 p_data => x_msg_data
1669 );
1670 EXCEPTION
1671 WHEN FND_API.G_EXC_ERROR THEN
1672 ROLLBACK TO Update_Child_Tbl_PVT;
1673 x_return_status := FND_API.G_RET_STS_ERROR;
1674 -- Standard call to get message count and if count=1, get the message
1675 FND_MSG_PUB.Count_And_Get (
1676 p_encoded => FND_API.G_FALSE,
1677 p_count => x_msg_count,
1678 p_data => x_msg_data
1679 );
1680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1681 ROLLBACK TO Update_Child_Tbl_PVT;
1682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1683 -- Standard call to get message count and if count=1, get the message
1684 FND_MSG_PUB.Count_And_Get (
1685 p_encoded => FND_API.G_FALSE,
1686 p_count => x_msg_count,
1687 p_data => x_msg_data
1688 );
1689 WHEN OTHERS THEN
1690 ROLLBACK TO Update_Child_Tbl_PVT;
1691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1692 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1693 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1694 END IF;
1695 -- Standard call to get message count and if count=1, get the message
1696 FND_MSG_PUB.Count_And_Get (
1697 p_encoded => FND_API.G_FALSE,
1698 p_count => x_msg_count,
1699 p_data => x_msg_data
1700 );
1701 END update_child_claim_tbl;
1702
1703 END;