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