[Home] [Help]
PACKAGE BODY: APPS.DPP_CLAIMS_PVT
Source
1 PACKAGE BODY DPP_CLAIMS_PVT AS
2 /* $Header: dppvclab.pls 120.36.12010000.3 2008/12/22 10:47:17 anbbalas ship $ */
3
4 -- Package name : DPP_CLAIMS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_CLAIMS_PVT';
11 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
12 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvclab.pls';
13 ---------------------------------------------------------------------
14 -- PROCEDURE
15 -- Update_Executiondetails
16 --
17 -- PURPOSE
18 -- Update Executiondetails Table
19 --
20 -- PARAMETERS
21 --
22 -- NOTES
23 -- 1.
24 -- 2.
25 ----------------------------------------------------------------------
26
27 PROCEDURE Update_Executiondetails(p_status IN VARCHAR2
28 ,p_txn_hdr_rec IN dpp_txn_hdr_rec_type
29 ,p_output_xml IN CLOB
30 ,p_api_name IN VARCHAR2
31 )
32 IS
33 PRAGMA AUTONOMOUS_TRANSACTION;
34 BEGIN
35 --Update the execution details table
36 UPDATE DPP_EXECUTION_DETAILS
37 SET EXECUTION_END_DATE = sysdate,
38 OUTPUT_XML = XMLTYPE(p_output_xml),
39 EXECUTION_STATUS = p_status,
40 LAST_UPDATED_BY = p_txn_hdr_rec.Last_Updated_By,
41 LAST_UPDATE_DATE = sysdate,
42 PROVIDER_PROCESS_ID = p_txn_hdr_rec.Provider_Process_Id,
43 PROVIDER_PROCESS_INSTANCE_ID = p_txn_hdr_rec.Provider_Process_Instance_id,
44 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
45 WHERE EXECUTION_DETAIL_ID = p_txn_hdr_rec.Execution_Detail_ID
46 AND transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
47 --In case of updtate flow
48 IF p_status = 'WARNING' AND p_api_name = 'Update_Claims' THEN
49 UPDATE dpp_transaction_claims_all
50 SET approved_by_supplier = 'N',
51 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
52 last_updated_by = p_txn_hdr_rec.LAST_UPDATED_BY,
53 last_update_date = sysdate,
54 last_update_login = p_txn_hdr_rec.LAST_UPDATED_BY
55 WHERE claim_id = p_txn_hdr_rec.claim_id
56 AND transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
57 END IF;
58 COMMIT;
59 END Update_Executiondetails;
60 ---------------------------------------------------------------------
61 -- PROCEDURE
62 -- Create_Claims
63 --
64 -- PURPOSE
65 -- Create Claims
66 --
67 -- PARAMETERS
68 --
69 -- NOTES
70 -- 1.
71 -- 2.
72 ----------------------------------------------------------------------
73
74 PROCEDURE Create_Claims(
75 p_api_version IN NUMBER
76 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
77 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
78 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
79 ,x_return_status OUT NOCOPY VARCHAR2
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2
82 ,p_txn_hdr_rec IN dpp_txn_hdr_rec_type
83 ,p_txn_line_tbl IN dpp_txn_line_tbl_type
84 )
85 IS
86 l_api_name CONSTANT VARCHAR2(30) := 'Create_Claims';
87 l_api_version CONSTANT NUMBER := 1.0;
88 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
89
90 l_return_status VARCHAR2(30);
91 l_msg_count NUMBER;
92 l_msg_data VARCHAR2(4000);
93
94 l_txn_hdr_rec DPP_CLAIMS_PVT.dpp_txn_hdr_rec_type := p_txn_hdr_rec;
95 l_txn_line_mtbl DPP_CLAIMS_PVT.dpp_txn_line_tbl_type := p_txn_line_tbl;
96 l_txn_line_tbl DPP_CLAIMS_PVT.dpp_txn_line_tbl_type;
97 l_txn_line_pi_tbl DPP_CLAIMS_PVT.dpp_txn_line_tbl_type;
98 l_txn_line_pd_tbl DPP_CLAIMS_PVT.dpp_txn_line_tbl_type;
99
100 l_claim_pub_rec OZF_Claim_PUB.claim_rec_type;
101 l_claim_line_pub_tbl OZF_Claim_PUB.claim_line_tbl_type;
102 l_claim_line_pub_tbl_updt OZF_Claim_PUB.claim_line_tbl_type;
103
104 l_output_xml CLOB;
105 l_queryCtx dbms_xmlquery.ctxType;
106 l_status VARCHAR2(20);
107 l_transaction_number VARCHAR2(240);
108
109 l_approval_flag VARCHAR2(1);
110 l_approved_by_supplier VARCHAR(1);
111 l_x_claim_id NUMBER;
112
113 l_cust_account_id NUMBER;
114 l_count NUMBER;
115 l_object_version_number NUMBER ;
116 l_claim_number VARCHAR2(240);
117 l_item_description VARCHAR2(240);
118 l_item_type VARCHAR2(30) := 'PRODUCT';
119
120 l_price_increase_flag VARCHAR2(1);
121 l_claim_line_amount_flag VARCHAR2(1);
122 l_claim_type VARCHAR2(30);
123 l_pi_count NUMBER;
124 l_pd_count NUMBER;
125 i NUMBER;
126 j NUMBER;
127
128 CURSOR get_claim_hdr_amt_pd_csr IS
129 SELECT SUM(claim_line_amount) amount
130 FROM DPP_CUSTOMER_CLAIMS_GT
131 where claim_line_amount > 0;
132
133 CURSOR get_claim_hdr_amt_pi_csr IS
134 SELECT SUM(ABS(claim_line_amount)) amount
135 FROM DPP_CUSTOMER_CLAIMS_GT
136 where claim_line_amount < 0;
137
138 CURSOR get_claim_id_csr (p_line_id IN NUMBER) IS
139 SELECT claim_id
140 FROM DPP_CUSTOMER_CLAIMS_GT
141 WHERE Transaction_Line_ID = p_line_id;
142
143 CURSOR grpby_currency_csr IS
144 SELECT SUM(Claim_Line_Amount) amount,
145 Currency
146 FROM DPP_CUSTOMER_CLAIMS_GT
147 GROUP BY Currency;
148
149 CURSOR get_claim_lines_csr (p_currency IN VARCHAR2) IS
150 SELECT transaction_line_id,
151 CUST_ACCOUNT_ID,
152 claim_line_amount,
153 inventory_item_id,
154 claim_quantity,
155 item_description,
156 uom
157 FROM DPP_CUSTOMER_CLAIMS_GT
158 WHERE currency = p_currency;
159
160 CURSOR grpby_cur_cust_csr IS
161 SELECT SUM(Claim_Line_Amount) amount,
162 Currency,
163 customer_id,
164 cust_account_id
165 FROM DPP_CUSTOMER_CLAIMS_GT
166 GROUP BY Currency,
167 customer_id,
168 cust_account_id;
169
170 CURSOR get_cust_claim_lines_csr(p_currency IN VARCHAR2,
171 p_customer_id IN NUMBER,
172 p_cust_account_id IN NUMBER) IS
173 SELECT transaction_line_id,
174 customer_id,
175 claim_line_amount,
176 inventory_item_id,
177 claim_quantity,
178 item_description,
179 uom
180 FROM DPP_CUSTOMER_CLAIMS_GT
181 WHERE currency = p_currency
182 AND customer_id = p_customer_id
183 AND cust_account_id = p_cust_account_id;
184
185 CURSOR get_item_number_csr(p_item_id NUMBER) IS
186 SELECT DISTINCT concatenated_segments item_number,
187 description
188 FROM mtl_system_items_kfv
189 WHERE inventory_item_id = p_item_id;
190
191 CURSOR get_claim_number_csr(p_claim_id NUMBER) IS
192 SELECT claim_number
193 FROM ozf_claims_all
194 WHERE claim_id = p_claim_id;
195
196 CURSOR get_customer_dtl_csr(p_cust_account_id NUMBER) IS
197 SELECT account_name,
198 party_id
199 FROM hz_cust_accounts
200 WHERE cust_account_id = p_cust_account_id;
201
202 BEGIN
203 -- Standard begin of API savepoint
204 SAVEPOINT CREATE_CLAIMS_PVT;
205
206 -- Standard call to check for call compatibility.
207 IF NOT FND_API.Compatible_API_Call ( l_api_version,
208 p_api_version,
209 l_api_name,
210 G_PKG_NAME)
211 THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214 -- Initialize message list if p_init_msg_list is set to TRUE.
215 IF FND_API.to_Boolean( p_init_msg_list )
216 THEN
217 FND_MSG_PUB.initialize;
218 END IF;
219 -- Debug Message
220 IF g_debug THEN
221 DPP_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'start');
222 END IF;
223 -- Initialize API return status to success
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 IF g_debug THEN
227 DPP_UTILITY_PVT.debug_message('Transaction Number: ' || l_txn_hdr_rec.Transaction_number);
228 DPP_UTILITY_PVT.debug_message('Claim Type Flag: ' || l_txn_hdr_rec.claim_type_flag);
229 END IF;
230 l_transaction_number := ''''||l_txn_hdr_rec.Transaction_number||'''';
231
232 MO_GLOBAL.set_policy_context('S',l_txn_hdr_rec.org_id);
233
234 --Get the Cust account id for the supplier
235 BEGIN
236 SELECT pre_approval_flag,
237 cust_account_id
238 INTO l_approval_flag,
239 l_cust_account_id
240 FROM ozf_supp_trd_prfls_all
241 WHERE supplier_id = l_txn_hdr_rec.Vendor_ID
242 AND supplier_site_id = l_txn_hdr_rec.Vendor_site_ID
243 AND org_id = l_txn_hdr_rec.org_id;
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
247 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
248 fnd_message.set_token('ERRNO', sqlcode);
249 fnd_message.set_token('REASON', 'NO CUST ACCOUNT ID RETRIEVED');
250 FND_MSG_PUB.add;
251 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
252 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
253 END IF;
254 RAISE FND_API.G_EXC_ERROR;
255 WHEN OTHERS THEN
256 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
257 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
258 fnd_message.set_token('ERRNO', sqlcode);
259 fnd_message.set_token('REASON', sqlerrm);
260 FND_MSG_PUB.add;
261 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
262 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_TRD_PROFILE');
263 fnd_message.set_token('VENDORID', l_txn_hdr_rec.Vendor_ID);
264 FND_MSG_PUB.add;
265 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
266 END IF;
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
268 END;
269 IF g_debug THEN
270 DPP_UTILITY_PVT.debug_message('Pre Approval Required : ' || l_approval_flag);
271 DPP_UTILITY_PVT.debug_message('Customer Account ID : ' || l_cust_account_id);
272 END IF;
273
274 --Added code for DPP Price Increase Enhancement
275 --Segregate the lines according to the claim_amount
276 l_pi_count := 0;
277 l_pd_count := 0;
278
279 IF l_txn_line_mtbl.COUNT > 0 THEN
280 FOR i in l_txn_line_mtbl.FIRST..l_txn_line_mtbl.LAST LOOP
281 IF l_txn_line_mtbl(i).Claim_Line_Amount < 0 THEN --Price Increase Lines
282 l_pi_count := l_pi_count + 1;
283 l_txn_line_pi_tbl(l_pi_count) := l_txn_line_mtbl(i);
284
285 ELSIF l_txn_line_mtbl(i).Claim_Line_Amount > 0 THEN --Price Decrease Lines
286 l_pd_count := l_pd_count + 1;
287 l_txn_line_pd_tbl(l_pd_count) := l_txn_line_mtbl(i);
288 END IF;
289 END LOOP;
290 END IF;
291
292 IF l_txn_line_pi_tbl.COUNT > 0 AND l_txn_line_pd_tbl.COUNT > 0 THEN
293 j := 2;
294 ELSE
295 j := 1;
296 END IF;
297
298 -- Delete the existing lines if any from the DPP_CUSTOMER_CLAIMS_GT temporary table
299 DELETE FROM DPP_CUSTOMER_CLAIMS_GT;
300
301 l_price_increase_flag := NULL;
302 FOR k in 1..j LOOP
303 IF j = 2 THEN --When transaction has both price increase and price decrease lines
304 IF k = 1 THEN --Price Decrease Lines
305 l_txn_line_tbl := l_txn_line_pd_tbl;
306 l_price_increase_flag := 'N';
307 ELSIF k = 2 THEN --Price Increase Lines
308 l_txn_line_tbl := l_txn_line_pi_tbl;
309 l_price_increase_flag := 'Y';
310 END IF;
311 ELSIF j = 1 THEN --When transaction has either price increase or price decrease lines
312 IF l_txn_line_pi_tbl.COUNT > 0 THEN --Price Increase Lines
313 l_txn_line_tbl := l_txn_line_pi_tbl;
314 l_price_increase_flag := 'Y';
315 ELSE --Price Decrease Lines
316 l_txn_line_tbl := l_txn_line_pd_tbl;
317 l_price_increase_flag := 'N';
318 END IF;
319 END IF;
320
321 -- Delete the existing lines if any from the DPP_CUSTOMER_CLAIMS_GT temporary table
322 --DELETE FROM DPP_CUSTOMER_CLAIMS_GT;
323
324 --Insert the lines into the global temp table
325 FOR i in l_txn_line_tbl.FIRST..l_txn_line_tbl.LAST LOOP
326 BEGIN
327 Insert into DPP_CUSTOMER_CLAIMS_GT(Transaction_Line_ID,
328 Inventory_Item_Id,
329 cust_account_id,
330 Claim_Line_Amount,
331 Currency,
332 Claim_Quantity,
333 UOM,
334 claim_id)
335 values(l_txn_line_tbl(i).Transaction_Line_ID,
336 l_txn_line_tbl(i).Inventory_Item_Id,
337 l_txn_line_tbl(i).cust_account_id,
338 l_txn_line_tbl(i).Claim_Line_Amount,
339 l_txn_line_tbl(i).Currency,
340 l_txn_line_tbl(i).Claim_Quantity,
341 l_txn_line_tbl(i).UOM,
342 null
343 );
344 EXCEPTION
345 WHEN OTHERS THEN
346 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
347 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
348 fnd_message.set_token('ERRNO', sqlcode);
349 fnd_message.set_token('REASON', sqlerrm);
350 FND_MSG_PUB.add;
351 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
352 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
353 END IF;
354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
355 END;
356 --Get the item number
357 IF l_txn_line_tbl(i).inventory_item_id IS NOT NULL THEN
358 FOR get_item_number_rec IN get_item_number_csr(l_txn_line_tbl(i).inventory_item_id) LOOP
359 UPDATE DPP_CUSTOMER_CLAIMS_GT
360 SET item_number = get_item_number_rec.item_number,
364 DPP_UTILITY_PVT.debug_message('Unable to Update the column item_number in DPP_CUSTOMER_CLAIMS_GT Table');
361 item_description = get_item_number_rec.description
362 WHERE transaction_line_id = l_txn_line_tbl(i).transaction_line_id;
363 IF SQL%ROWCOUNT = 0 THEN
365 END IF;
366 END LOOP;
367 ELSE
368 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
369 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
370 FND_MSG_PUB.add;
371 RAISE FND_API.G_EXC_ERROR;
372 END IF;
373 --Get the customer name
374 IF l_txn_hdr_rec.claim_type_flag = 'CUST_CL' THEN
375 IF l_txn_line_tbl(i).cust_account_id IS NOT NULL THEN
376 FOR get_customer_dtl_rec IN get_customer_dtl_csr(l_txn_line_tbl(i).cust_account_id) LOOP
377 UPDATE DPP_CUSTOMER_CLAIMS_GT
378 SET customer_name = get_customer_dtl_rec.account_name,
379 customer_id = get_customer_dtl_rec.party_id
380 WHERE cust_account_id = l_txn_line_tbl(i).cust_account_id;
381 IF SQL%ROWCOUNT = 0 THEN
382 DPP_UTILITY_PVT.debug_message('Unable to Update the column customer_name in DPP_CUSTOMER_CLAIMS_GT Table');
383 END IF;
384 END LOOP;
385 ELSE
386 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
387 FND_MESSAGE.set_token('ID', 'Customer Account ID');
388 FND_MSG_PUB.add;
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391 END IF;
392 END LOOP; --Checked for all the lines in the table type variable.
393
394 --Check for vendor claims
395 IF l_txn_hdr_rec.claim_type_flag = 'SUPP_DSTR_CL' THEN
396 --Clear the existing records from the table type variable..
397 l_claim_line_pub_tbl.delete();
398 l_claim_pub_rec := NULL;
399 IF l_approval_flag = 'Y' THEN
400 l_approved_by_supplier := 'N';
401 l_claim_pub_rec.status_code := 'PENDING_APPROVAL';
402 l_claim_pub_rec.user_status_id := 2008; --Pending_Approval status
403 ELSIF l_approval_flag = 'N' THEN
404 l_approved_by_supplier := 'Y';
405 l_claim_pub_rec.user_status_id := 2001; --OPEN status
406 l_claim_pub_rec.status_code := 'OPEN';
407 END IF;
408 --Header record
409 IF l_price_increase_flag = 'Y' THEN
410 FOR get_claim_hdr_amt_rec IN get_claim_hdr_amt_pi_csr LOOP
411 l_claim_pub_rec.amount := get_claim_hdr_amt_rec.amount;
412 END LOOP;
413 ELSIF l_price_increase_flag = 'N' THEN
414 FOR get_claim_hdr_amt_rec IN get_claim_hdr_amt_pd_csr LOOP
415 l_claim_pub_rec.amount := get_claim_hdr_amt_rec.amount;
416 END LOOP;
417 END IF;
418
419 -- FOR get_claim_hdr_amt_rec IN get_claim_hdr_amt_csr LOOP
420 -- l_claim_pub_rec.amount := get_claim_hdr_amt_rec.amount;
421 -- END LOOP;
422 l_claim_pub_rec.cust_account_id := l_cust_account_id;
423 l_claim_pub_rec.claim_class := 'CLAIM';
424
425 IF g_debug THEN
426 DPP_UTILITY_PVT.debug_message('l_txn_hdr_rec.claim_amount : ' || l_txn_hdr_rec.claim_amount);
427 END IF;
428
429 l_claim_pub_rec.currency_code := l_txn_hdr_rec.currency_code;
430 l_claim_pub_rec.vendor_id := l_txn_hdr_rec.Vendor_ID;
431 l_claim_pub_rec.vendor_site_id := l_txn_hdr_rec.Vendor_site_ID;
432 l_claim_pub_rec.custom_setup_id := 300;
433
434 l_claim_line_amount_flag := NULL;
435 --Line records
436 FOR i in l_txn_line_tbl.FIRST..l_txn_line_tbl.LAST LOOP
437 BEGIN
438 IF l_claim_line_amount_flag IS NULL THEN
439 IF l_txn_line_tbl(i).Claim_Line_Amount < 0 THEN -- Price Increase Lines
440 l_claim_pub_rec.source_object_class := 'PPINCVENDOR';
441 l_claim_pub_rec.payment_method := 'AP_DEFAULT';
442 l_claim_line_amount_flag := 'Y';
443 ELSIF l_txn_line_tbl(i).Claim_Line_Amount > 0 THEN -- Price Decrease Lines
444 l_claim_pub_rec.source_object_class := 'PPVENDOR';
445 l_claim_pub_rec.payment_method := 'AP_DEBIT';
446 l_claim_line_amount_flag := 'N';
447 END IF;
448 END IF;
449 --Get the item description
450 SELECT DISTINCT description
451 INTO l_item_description
452 FROM mtl_system_items_kfv
453 WHERE inventory_item_id = l_txn_line_tbl(i).Inventory_Item_Id;
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 ROLLBACK TO CREATE_CLAIMS_PVT;
457 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
458 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
459 fnd_message.set_token('ERRNO', sqlcode);
460 fnd_message.set_token('REASON', 'NO ITEM DESC RETRIEVED');
461 FND_MSG_PUB.add;
462 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
463 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
464 END IF;
465 RAISE FND_API.G_EXC_ERROR;
466 WHEN OTHERS THEN
467 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
468 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
469 fnd_message.set_token('ERRNO', sqlcode);
470 fnd_message.set_token('REASON', sqlerrm);
471 FND_MSG_PUB.add;
475 FND_MSG_PUB.add;
472 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
473 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
474 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
476 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
477 END IF;
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END;
480 IF l_price_increase_flag = 'Y' THEN
481 l_claim_line_pub_tbl(i).source_object_class := 'PPINCVENDOR';
482 l_claim_line_pub_tbl(i).claim_currency_amount := -1 * l_txn_line_tbl(i).Claim_Line_Amount;
483 ELSIF l_price_increase_flag = 'N' THEN
484 l_claim_line_pub_tbl(i).source_object_class := 'PPVENDOR';
485 l_claim_line_pub_tbl(i).claim_currency_amount := l_txn_line_tbl(i).Claim_Line_Amount;
486 END IF;
487
488 IF g_debug THEN
489 DPP_UTILITY_PVT.debug_message('l_claim_line_pub_tbl(i).claim_currency_amount: ' || l_claim_line_pub_tbl(i).claim_currency_amount);
490 END IF;
491
492 l_claim_line_pub_tbl(i).item_description := l_item_description;
493 l_claim_line_pub_tbl(i).item_type := l_item_type;
494 l_claim_line_pub_tbl(i).item_id := l_txn_line_tbl(i).Inventory_Item_Id;
495 l_claim_line_pub_tbl(i).currency_code := l_txn_line_tbl(i).Currency;
496 l_claim_line_pub_tbl(i).quantity := l_txn_line_tbl(i).Claim_Quantity;
497 l_claim_line_pub_tbl(i).quantity_uom := l_txn_line_tbl(i).UOM;
498 END LOOP;
499
500 --Invoke the standard API with the above defined parameters.
501 OZF_Claim_PUB.Create_Claim(p_api_version_number => 1.0,
502 p_init_msg_list => FND_API.G_TRUE,
503 p_commit => FND_API.G_FALSE,
504 p_validation_level => p_validation_level,
505 x_return_status => l_return_status,
506 x_msg_count => l_msg_count,
507 x_msg_data => l_msg_data,
508 p_claim_rec => l_claim_pub_rec,
509 p_claim_line_tbl => l_claim_line_pub_tbl,
510 x_claim_id => l_x_claim_id
511 );
512 IF g_debug THEN
513 dpp_utility_pvt.debug_message('return status for Create_Claim =>'||l_return_status);
514 dpp_utility_pvt.debug_message (substr(('Message data from OZF Create Claim =>'||l_msg_data),1,4000));
515 END IF;
516 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
517 x_return_status := l_return_status;
518 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
519 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
520 fnd_message.set_token('ERRNO', sqlcode);
521 fnd_message.set_token('REASON', 'Error while Creating Claim in Trade Management');
522 FND_MSG_PUB.add;
523 --Update the GT table with the reason for failure
524 UPDATE DPP_CUSTOMER_CLAIMS_GT
525 SET reason_for_failure = nvl(substr(l_msg_data,1,4000),'Error while Creating Claim in Trade Management');
526 IF SQL%ROWCOUNT = 0 THEN
527 DPP_UTILITY_PVT.debug_message('Unable to Update DPP_CUSTOMER_CLAIMS_GT Table');
528 END IF;
529 ELSE
530 --Assign the claim id to the header record to call the update claim api
531 l_txn_hdr_rec.claim_id := l_x_claim_id ;
532 --Get the claim number corresponding to the claim id
533 FOR get_claim_number_rec IN get_claim_number_csr(l_x_claim_id) LOOP
534 l_claim_number := get_claim_number_rec.claim_number;
535 END LOOP;
536 --Insert the claim id into the dpp_transaction_claims_all table
537 BEGIN
538 --Derive the claim type based on the change type i.e. price increase or price decrease
539 IF l_price_increase_flag = 'Y' THEN
540 l_claim_type := 'SUPP_DSTR_INC_CL';
541 ELSIF l_price_increase_flag = 'N' THEN
542 l_claim_type := 'SUPP_DSTR_CL';
543 END IF;
544 INSERT INTO dpp_transaction_claims_all(CLAIM_ID,
545 TRANSACTION_HEADER_ID,
546 OBJECT_VERSION_NUMBER,
547 CLAIM_TYPE,
548 CREATION_DATE,
549 CREATED_BY,
550 LAST_UPDATE_DATE,
551 LAST_UPDATED_BY,
552 LAST_UPDATE_LOGIN,
553 ORG_ID,
554 APPROVED_BY_SUPPLIER)
555 VALUES(to_char(l_x_claim_id),
556 l_txn_hdr_rec.transaction_header_id,
557 1,
558 l_claim_type,
559 sysdate,
560 l_txn_hdr_rec.LAST_UPDATED_BY,
561 sysdate,
562 l_txn_hdr_rec.LAST_UPDATED_BY,
563 l_txn_hdr_rec.LAST_UPDATED_BY,
564 to_char(l_txn_hdr_rec.ORG_ID),
565 l_approved_by_supplier);
566 EXCEPTION
567 WHEN OTHERS THEN
568 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
572 FND_MSG_PUB.add;
569 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
570 fnd_message.set_token('ERRNO', sqlcode);
571 fnd_message.set_token('REASON', sqlerrm);
573 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
574 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
575 END IF;
576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
577 END;
578 --Insert the claim id into the global temp table..
579 IF l_price_increase_flag = 'Y' THEN
580 UPDATE DPP_CUSTOMER_CLAIMS_GT
581 SET claim_id = l_x_claim_id,
582 claim_number = l_claim_number
583 where claim_line_amount < 0;
584 ELSIF l_price_increase_flag = 'N' THEN
585 UPDATE DPP_CUSTOMER_CLAIMS_GT
586 SET claim_id = l_x_claim_id,
587 claim_number = l_claim_number
588 where claim_line_amount > 0;
589 END IF;
590
591 -- UPDATE DPP_CUSTOMER_CLAIMS_GT
592 -- SET claim_id = l_x_claim_id,
593 -- claim_number = l_claim_number;
594 IF SQL%ROWCOUNT = 0 THEN
595 DPP_UTILITY_PVT.debug_message('Unable to Update the column claim_id in DPP_CUSTOMER_CLAIMS_GT Table');
596 END IF;
597 --Assign the claim id to the corresponding lines
598 FOR i IN l_txn_line_tbl.FIRST..l_txn_line_tbl.LAST LOOP
599 --Insert into the output xml table to generate the error log
600 FOR get_claim_id_rec IN get_claim_id_csr (l_txn_line_tbl(i).transaction_line_id) LOOP
601 UPDATE DPP_TRANSACTION_LINES_ALL
602 SET supp_dist_claim_id = to_char(get_claim_id_rec.claim_id),
603 supp_dist_claim_status = 'Y',
604 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
605 last_updated_by = l_txn_hdr_rec.LAST_UPDATED_BY,
606 last_update_date = sysdate,
607 last_update_login = l_txn_hdr_rec.LAST_UPDATED_BY
608 WHERE transaction_line_id = l_txn_line_tbl(i).transaction_line_id;
609
610 IF SQL%ROWCOUNT = 0 THEN
611 DPP_UTILITY_PVT.debug_message('Unable to Update the column supp_dist_claim_id in DPP_TRANSACTION_LINES_ALL Table');
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614 END LOOP; --getting the claim id loop
615 END LOOP; --lines table loop
616
617 --Check if pre approval is required and update the claim
618 IF l_approval_flag = 'N' THEN
619 --Call the Update_Claims Procedure to flip the status of the claim to Pending Close
620 --Clear the existing records from the table type and record type variable..
621 l_claim_line_pub_tbl.delete();
622 l_claim_pub_rec := NULL;
623 l_claim_pub_rec.claim_id := l_x_claim_id;
624 l_claim_pub_rec.user_status_id := 2003; --For pending Close Status
625 l_claim_pub_rec.status_code := 'PENDING_CLOSE';
626
627 IF l_price_increase_flag = 'Y' THEN
628 l_claim_pub_rec.payment_method := 'AP_DEFAULT';
629 ELSIF l_price_increase_flag = 'N' THEN
630 l_claim_pub_rec.payment_method := 'AP_DEBIT';
631 END IF;
632
633 --Retrieve the object version number
634 BEGIN
635 l_object_version_number := NULL;
636
637 SELECT Object_version_number
638 INTO l_object_version_number
639 FROM ozf_claims
640 WHERE claim_id = l_x_claim_id;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
644 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
645 fnd_message.set_token('ERRNO', sqlcode);
646 fnd_message.set_token('REASON', 'OBJECT VERSION NUMBER NOT FOUND');
647 FND_MSG_PUB.add;
648 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
649 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
650 END IF;
651 RAISE FND_API.G_EXC_ERROR;
652 WHEN OTHERS THEN
653 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
654 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
655 fnd_message.set_token('ERRNO', sqlcode);
656 fnd_message.set_token('REASON', sqlerrm);
657 FND_MSG_PUB.add;
658 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
659 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_OBJ_VER_NUM');
660 fnd_message.set_token('CLAIM_ID', l_x_claim_id);
661 FND_MSG_PUB.add;
662 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
663 END IF;
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 END;
666 l_claim_pub_rec.object_version_number := l_object_version_number;
667 --Invoke the standard API with the above defined parameters.
668 OZF_CLAIM_PUB.Update_Claim (p_api_version_number => l_api_version
669 ,p_init_msg_list => FND_API.G_FALSE
670 ,p_commit => FND_API.G_FALSE
671 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
672 ,x_return_status => l_return_status
676 ,p_claim_line_tbl => l_claim_line_pub_tbl
673 ,x_msg_count => l_msg_count
674 ,x_msg_data => l_msg_data
675 ,p_claim_rec => l_claim_pub_rec
677 ,x_object_version_number => l_object_version_number
678 );
679 IF g_debug THEN
680 dpp_utility_pvt.debug_message('return status for OZF Update_Claims =>'||l_return_status);
681 dpp_utility_pvt.debug_message(substr(('Message data for OZF Update_Claims =>'||l_msg_data),1,4000));
682 END IF;
683 --If the claim updation process failed
684 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
685 x_return_status := l_return_status;
686 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
687 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
688 fnd_message.set_token('ERRNO', sqlcode);
689 fnd_message.set_token('REASON', 'Error while Updating Claim in Trade Management');
690 FND_MSG_PUB.add;
691 --Delete the claim numbers from the GT table
692 UPDATE DPP_CUSTOMER_CLAIMS_GT
693 SET claim_id = null,
694 claim_number = null;
695 IF SQL%ROWCOUNT = 0 THEN
696 DPP_UTILITY_PVT.debug_message('Unable to Update DPP_CUSTOMER_CLAIMS_GT Table');
697 END IF;
698 END IF;
699 END IF; --Pre approval is not required
700 END IF;
701
702 --Check for vendor claims for customer
703 ELSIF l_txn_hdr_rec.claim_type_flag = 'SUPP_CUST_CL' AND l_price_increase_flag = 'N' THEN
704 FOR grpby_currency_rec IN grpby_currency_csr LOOP
705 --Clear the existing records from the table type variable..
706 l_claim_line_pub_tbl.delete();
707 l_claim_pub_rec := null;
708 --Header Record
709 l_claim_pub_rec.cust_account_id := l_cust_account_id;
710 l_claim_pub_rec.claim_class := 'CLAIM';
711 l_claim_pub_rec.source_object_class := 'PPVENDOR';
712 l_claim_pub_rec.amount := grpby_currency_rec.amount;
713 l_claim_pub_rec.currency_code := grpby_currency_rec.Currency;
714 l_claim_pub_rec.vendor_id := l_txn_hdr_rec.Vendor_ID;
715 l_claim_pub_rec.vendor_site_id := l_txn_hdr_rec.Vendor_site_ID;
716 l_claim_pub_rec.status_code := 'OPEN';
717 l_claim_pub_rec.user_status_id := 2001; --OPEN status
718 l_claim_pub_rec.custom_setup_id := 300;
719 --Line records
720 l_count := 1;
721 FOR get_claim_lines_rec IN get_claim_lines_csr(grpby_currency_rec.Currency) LOOP
722 l_claim_line_pub_tbl(l_count).claim_currency_amount := get_claim_lines_rec.claim_line_amount;
723 l_claim_line_pub_tbl(l_count).item_id := get_claim_lines_rec.Inventory_Item_Id;
724 l_claim_line_pub_tbl(l_count).item_description := get_claim_lines_rec.item_description;
725 l_claim_line_pub_tbl(l_count).item_type := l_item_type;
726 l_claim_line_pub_tbl(l_count).source_object_class := 'PPVENDOR';
727 l_claim_line_pub_tbl(l_count).dpp_cust_account_id := get_claim_lines_rec.CUST_ACCOUNT_ID;
728 l_claim_line_pub_tbl(l_count).currency_code := grpby_currency_rec.Currency;
729 l_claim_line_pub_tbl(l_count).quantity := get_claim_lines_rec.Claim_Quantity;
730 l_claim_line_pub_tbl(l_count).quantity_uom := get_claim_lines_rec.uom;
731 l_count := l_count + 1;
732 END LOOP;
733
734 --Invoke the standard API with the above defined parameters.
735 OZF_Claim_PUB.Create_Claim(p_api_version_number => 1.0,
736 p_init_msg_list => FND_API.G_TRUE,
737 p_commit => FND_API.G_FALSE,
738 p_validation_level => p_validation_level,
739 x_return_status => l_return_status,
740 x_msg_count => l_msg_count,
741 x_msg_data => l_msg_data,
742 p_claim_rec => l_claim_pub_rec,
743 p_claim_line_tbl => l_claim_line_pub_tbl,
744 x_claim_id => l_x_claim_id
745 );
746 IF g_debug THEN
747 dpp_utility_pvt.debug_message('return status for Create_Claim =>'||l_return_status);
748 dpp_utility_pvt.debug_message(substr(('Message Data for OZF Create_Claim =>'||l_msg_data),1,4000));
749 END IF;
750
751 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
752 x_return_status := l_return_status;
753 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
754 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
755 fnd_message.set_token('ERRNO', sqlcode);
756 fnd_message.set_token('REASON', 'Error while Creating Claim in Trade Management');
757 FND_MSG_PUB.add;
758 --Update the claim id into the global temp table..
759 UPDATE DPP_CUSTOMER_CLAIMS_GT
760 SET reason_for_failure = nvl(substr(l_msg_data,1,4000),'Error while Creating Claim in Trade Management')
761 WHERE currency = grpby_currency_rec.Currency;
762
763 IF SQL%ROWCOUNT = 0 THEN
764 DPP_UTILITY_PVT.debug_message('Unable to Update the column claim_id in DPP_CUSTOMER_CLAIMS_GT Table');
765 END IF;
766 ELSE
767 --Update the claim to pending close status
768 --Call the update_claims procedure to update the claim status to open
769 l_claim_pub_rec.claim_id := l_x_claim_id;
773 --Retrieve the object version number
770 l_claim_pub_rec.user_status_id := 2003; -- For pending close status
771 l_claim_pub_rec.payment_method := 'AP_DEBIT';
772 l_claim_pub_rec.status_code := 'PENDING_CLOSE';
774 BEGIN
775 SELECT Object_version_number
776 INTO l_object_version_number
777 FROM ozf_claims
778 WHERE claim_id = l_x_claim_id;
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
782 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
783 fnd_message.set_token('ERRNO', sqlcode);
784 fnd_message.set_token('REASON', 'OBJECT VERSION NUMBER NOT FOUND');
785 FND_MSG_PUB.add;
786 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
787 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
788 END IF;
789 RAISE FND_API.G_EXC_ERROR;
790 WHEN OTHERS THEN
791 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
792 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
793 fnd_message.set_token('ERRNO', sqlcode);
794 fnd_message.set_token('REASON', sqlerrm);
795 FND_MSG_PUB.add;
796 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
797 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_OBJ_VER_NUM');
798 fnd_message.set_token('CLAIM_ID', l_x_claim_id);
799 FND_MSG_PUB.add;
800 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
801 END IF;
802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
803 END;
804 l_claim_pub_rec.object_version_number := l_object_version_number;
805 --Invoke the standard API with the above defined parameters.
806 OZF_CLAIM_PUB.Update_Claim (p_api_version_number => l_api_version
807 ,p_init_msg_list => FND_API.G_FALSE
808 ,p_commit => FND_API.G_FALSE
809 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
810 ,x_return_status => l_return_status
811 ,x_msg_count => l_msg_count
812 ,x_msg_data => l_msg_data
813 ,p_claim_rec => l_claim_pub_rec
814 ,p_claim_line_tbl => l_claim_line_pub_tbl_updt
815 ,x_object_version_number => l_object_version_number
816 );
817 IF g_debug THEN
818 dpp_utility_pvt.debug_message('return status for Update_Claims =>'||l_return_status);
819 dpp_utility_pvt.debug_message(substr(('Message data for OZF Update_Claims =>'||l_msg_data),1,4000));
820 END IF;
821 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
822 x_return_status := l_return_status;
823 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
824 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
825 fnd_message.set_token('ERRNO', sqlcode);
826 fnd_message.set_token('REASON', 'Error while Updating Claim in Trade Management');
827 FND_MSG_PUB.add;
828 ELSE
829 --Get the claim number corresponding to the claim id
830 FOR get_claim_number_rec IN get_claim_number_csr(l_x_claim_id) LOOP
831 l_claim_number := get_claim_number_rec.claim_number;
832 END LOOP;
833 --Insert the claim id into the dpp_transaction_claims_all table
834 BEGIN
835 INSERT INTO dpp_transaction_claims_all(CLAIM_ID,
836 TRANSACTION_HEADER_ID,
837 OBJECT_VERSION_NUMBER,
838 CLAIM_TYPE,
839 CREATION_DATE,
840 CREATED_BY,
841 LAST_UPDATE_DATE,
842 LAST_UPDATED_BY,
843 LAST_UPDATE_LOGIN,
844 ORG_ID,
845 APPROVED_BY_SUPPLIER)
846 VALUES(to_char(l_x_claim_id),
847 l_txn_hdr_rec.transaction_header_id,
848 1,
849 l_txn_hdr_rec.claim_type_flag,
850 sysdate,
851 l_txn_hdr_rec.LAST_UPDATED_BY,
852 sysdate,
853 l_txn_hdr_rec.LAST_UPDATED_BY,
854 l_txn_hdr_rec.LAST_UPDATED_BY,
855 to_char(l_txn_hdr_rec.ORG_ID),
856 'Y');
857 EXCEPTION
858 WHEN OTHERS THEN
859 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
860 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
861 fnd_message.set_token('ERRNO', sqlcode);
862 fnd_message.set_token('REASON', sqlerrm);
866 END IF;
863 FND_MSG_PUB.add;
864 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
865 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868 END;
869 --Update the claim id into the global temp table..
870 UPDATE DPP_CUSTOMER_CLAIMS_GT
871 SET claim_id = l_x_claim_id,
872 claim_number = l_claim_number
873 WHERE currency = grpby_currency_rec.Currency;
874
875 IF SQL%ROWCOUNT = 0 THEN
876 DPP_UTILITY_PVT.debug_message('Unable to Update the column claim_id in DPP_CUSTOMER_CLAIMS_GT Table');
877 END IF;
878 END IF; --Update claim call success
879 END IF; --Create claim success
880 END LOOP;
881 --Assign the claim id to the corresponding lines
882 FOR i IN l_txn_line_tbl.FIRST..l_txn_line_tbl.LAST LOOP
883 --Insert into the output xml table to generate the error log
884 FOR get_claim_id_rec IN get_claim_id_csr (l_txn_line_tbl(i).transaction_line_id) LOOP
885 IF get_claim_id_rec.claim_id IS NOT NULL THEN
886 UPDATE DPP_CUSTOMER_CLAIMS_ALL
887 SET SUPP_CUST_CLAIM_ID = to_char(get_claim_id_rec.claim_id),
888 supplier_claim_created = 'Y',
889 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
890 last_updated_by = l_txn_hdr_rec.LAST_UPDATED_BY,
891 last_update_date = sysdate,
892 last_update_login = l_txn_hdr_rec.LAST_UPDATED_BY
893 WHERE CUSTOMER_INV_LINE_ID = l_txn_line_tbl(i).transaction_line_id;
894
895 IF SQL%ROWCOUNT = 0 THEN
896 DPP_UTILITY_PVT.debug_message('Unable to Update the column SUPP_CUST_CLAIM_ID in DPP_CUSTOMER_CLAIMS_ALL Table');
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899 ELSE
900 UPDATE DPP_CUSTOMER_CLAIMS_ALL
901 SET supplier_claim_created = 'N',
902 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
903 last_updated_by = l_txn_hdr_rec.LAST_UPDATED_BY,
904 last_update_date = sysdate,
905 last_update_login = l_txn_hdr_rec.LAST_UPDATED_BY
906 WHERE CUSTOMER_INV_LINE_ID = l_txn_line_tbl(i).transaction_line_id;
907
908 IF SQL%ROWCOUNT = 0 THEN
909 DPP_UTILITY_PVT.debug_message('Unable to Update the column supplier_claim_created in DPP_CUSTOMER_CLAIMS_ALL Table');
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912 END IF;
913 END LOOP;
914 END LOOP;
915 --Check for vendor claims for customer
916 ELSIF l_txn_hdr_rec.claim_type_flag = 'CUST_CL' AND l_price_increase_flag = 'N' THEN
917 FOR grpby_cur_cust_rec IN grpby_cur_cust_csr LOOP
918 --Clear the existing records from the table type variable..
919 l_claim_line_pub_tbl.delete();
920 l_claim_pub_rec := null;
921 --Header Records
922 l_claim_pub_rec.cust_account_id := grpby_cur_cust_rec.cust_account_id;
923 l_claim_pub_rec.claim_class := 'CLAIM';
924 l_claim_pub_rec.source_object_class := 'PPCUSTOMER';
925 l_claim_pub_rec.amount := grpby_cur_cust_rec.amount;
926 l_claim_pub_rec.currency_code := grpby_cur_cust_rec.Currency;
927 l_claim_pub_rec.status_code := 'OPEN';
928 l_claim_pub_rec.user_status_id := 2001; --OPEN status
929 l_claim_pub_rec.custom_setup_id := 300;
930 --Line records
931 l_count := 1;
932 FOR get_cust_claim_lines_rec IN get_cust_claim_lines_csr(grpby_cur_cust_rec.Currency,
933 grpby_cur_cust_rec.customer_id,
934 grpby_cur_cust_rec.cust_account_id) LOOP
935 l_claim_line_pub_tbl(l_count).claim_currency_amount := get_cust_claim_lines_rec.claim_line_amount;
936 l_claim_line_pub_tbl(l_count).item_id := get_cust_claim_lines_rec.inventory_item_id;
937 l_claim_line_pub_tbl(l_count).item_description := get_cust_claim_lines_rec.item_description;
938 l_claim_line_pub_tbl(l_count).item_type := l_item_type;
939 l_claim_line_pub_tbl(l_count).source_object_class := 'PPCUSTOMER';
940 l_claim_line_pub_tbl(l_count).currency_code := grpby_cur_cust_rec.Currency;
941 l_claim_line_pub_tbl(l_count).quantity := get_cust_claim_lines_rec.Claim_Quantity;
942 l_claim_line_pub_tbl(l_count).quantity_uom := get_cust_claim_lines_rec.uom;
943 l_count := l_count + 1;
944 END LOOP;
945
946 --Invoke the standard API with the above defined parameters.
947 OZF_Claim_PUB.Create_Claim(p_api_version_number => 1.0,
948 p_init_msg_list => FND_API.G_TRUE,
949 p_commit => FND_API.G_FALSE,
950 p_validation_level => p_validation_level,
951 x_return_status => l_return_status,
952 x_msg_count => l_msg_count,
953 x_msg_data => l_msg_data,
954 p_claim_rec => l_claim_pub_rec,
955 p_claim_line_tbl => l_claim_line_pub_tbl,
956 x_claim_id => l_x_claim_id
957 );
958 IF g_debug THEN
959 dpp_utility_pvt.debug_message('return status for Create_Claim =>'||l_return_status);
963 x_return_status := l_return_status;
960 dpp_utility_pvt.debug_message(substr(('Message data for OZF Create_Claim =>'||l_msg_data),1,4000));
961 END IF;
962 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
964 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
965 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
966 fnd_message.set_token('ERRNO', sqlcode);
967 fnd_message.set_token('REASON', 'Error while Creating Claim in Trade Management');
968 FND_MSG_PUB.add;
969 --Insert the claim id into the global temp table..
970 UPDATE DPP_CUSTOMER_CLAIMS_GT
971 SET reason_for_failure = nvl(substr(l_msg_data,1,4000),'Error while Creating Claim in Trade Management')
972 WHERE currency = grpby_cur_cust_rec.Currency
973 AND customer_id = grpby_cur_cust_rec.customer_id
974 AND cust_account_id = grpby_cur_cust_rec.cust_account_id;
975
976 IF SQL%ROWCOUNT = 0 THEN
977 DPP_UTILITY_PVT.debug_message('Unable to Update the column claim_id in DPP_CUSTOMER_CLAIMS_GT Table');
978 END IF;
979 ELSE
980 --Update the claim to Pending Close status
981 l_claim_pub_rec.claim_id := l_x_claim_id;
982 l_claim_pub_rec.user_status_id := 2003; -- For pending close status
983 l_claim_pub_rec.payment_method := 'CREDIT_MEMO';
984 l_claim_pub_rec.status_code := 'PENDING_CLOSE';
985 --Retrieve the object version number
986 BEGIN
987 SELECT Object_version_number
988 INTO l_object_version_number
989 FROM ozf_claims
990 WHERE claim_id = l_x_claim_id;
991 EXCEPTION
992 WHEN NO_DATA_FOUND THEN
993 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
994 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
995 fnd_message.set_token('ERRNO', sqlcode);
996 fnd_message.set_token('REASON', 'OBJECT VERSION NUMBER NOT FOUND');
997 FND_MSG_PUB.add;
998 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
999 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1000 END IF;
1001 RAISE FND_API.G_EXC_ERROR;
1002 WHEN OTHERS THEN
1003 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1004 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1005 fnd_message.set_token('ERRNO', sqlcode);
1006 fnd_message.set_token('REASON', sqlerrm);
1007 FND_MSG_PUB.add;
1008 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1009 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_OBJ_VER_NUM');
1010 fnd_message.set_token('CLAIM_ID', l_x_claim_id);
1011 FND_MSG_PUB.add;
1012 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1013 END IF;
1014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1015 END;
1016 l_claim_pub_rec.object_version_number := l_object_version_number;
1017 --Invoke the standard API with the above defined parameters.
1018 OZF_CLAIM_PUB.Update_Claim (p_api_version_number => l_api_version
1019 ,p_init_msg_list => FND_API.G_FALSE
1020 ,p_commit => FND_API.G_FALSE
1021 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1022 ,x_return_status => l_return_status
1023 ,x_msg_count => l_msg_count
1024 ,x_msg_data => l_msg_data
1025 ,p_claim_rec => l_claim_pub_rec
1026 ,p_claim_line_tbl => l_claim_line_pub_tbl_updt
1027 ,x_object_version_number => l_object_version_number
1028 );
1029 IF g_debug THEN
1030 dpp_utility_pvt.debug_message('return status for Update_Claims =>'||l_return_status);
1031 dpp_utility_pvt.debug_message(substr(('Message Data for OZF Update_Claims =>'||l_msg_data),1,4000));
1032 END IF;
1033 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1034 x_return_status := l_return_status;
1035 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1036 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1037 fnd_message.set_token('ERRNO', sqlcode);
1038 fnd_message.set_token('REASON', 'Error while Creating Claim in Trade Management');
1039 FND_MSG_PUB.add;
1040 ELSE
1041 --Get the claim number corresponding to the claim id
1042 FOR get_claim_number_rec IN get_claim_number_csr(l_x_claim_id) LOOP
1043 l_claim_number := get_claim_number_rec.claim_number;
1044 END LOOP;
1045 --Insert the claim id into the dpp_transaction_claims_all table
1046 BEGIN
1047 INSERT INTO dpp_transaction_claims_all(CLAIM_ID,
1048 TRANSACTION_HEADER_ID,
1049 OBJECT_VERSION_NUMBER,
1050 CLAIM_TYPE,
1051 CREATION_DATE,
1052 CREATED_BY,
1053 LAST_UPDATE_DATE,
1054 LAST_UPDATED_BY,
1055 LAST_UPDATE_LOGIN,
1056 ORG_ID,
1060 1,
1057 APPROVED_BY_SUPPLIER)
1058 VALUES(to_char(l_x_claim_id),
1059 l_txn_hdr_rec.transaction_header_id,
1061 l_txn_hdr_rec.claim_type_flag,
1062 sysdate,
1063 l_txn_hdr_rec.LAST_UPDATED_BY,
1064 sysdate,
1065 l_txn_hdr_rec.LAST_UPDATED_BY,
1066 l_txn_hdr_rec.LAST_UPDATED_BY,
1067 to_char(l_txn_hdr_rec.ORG_ID),
1068 'Y');
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1072 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1073 fnd_message.set_token('ERRNO', sqlcode);
1074 fnd_message.set_token('REASON', sqlerrm);
1075 FND_MSG_PUB.add;
1076 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1077 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1078 END IF;
1079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1080 END;
1081 --Insert the claim id into the global temp table..
1082 UPDATE DPP_CUSTOMER_CLAIMS_GT
1083 SET claim_id = l_x_claim_id,
1084 claim_number = l_claim_number,
1085 reason_for_failure = nvl(substr(l_msg_data,1,4000),'Error while Updating Claim in Trade Management')
1086 WHERE currency = grpby_cur_cust_rec.Currency
1087 AND customer_id = grpby_cur_cust_rec.customer_id
1088 AND cust_account_id = grpby_cur_cust_rec.cust_account_id;
1089 IF SQL%ROWCOUNT = 0 THEN
1090 DPP_UTILITY_PVT.debug_message('Unable to Update the column claim_id in DPP_CUSTOMER_CLAIMS_GT Table');
1091 END IF;
1092 END IF;
1093 END IF;
1094 END LOOP;
1095
1096 --Assign the claim id to the corresponding lines
1097 FOR i IN l_txn_line_tbl.FIRST..l_txn_line_tbl.LAST LOOP
1098 FOR get_claim_id_rec IN get_claim_id_csr (l_txn_line_tbl(i).transaction_line_id) LOOP
1099 IF get_claim_id_rec.claim_id IS NOT NULL THEN
1100 UPDATE DPP_CUSTOMER_CLAIMS_ALL
1101 SET CUSTOMER_CLAIM_ID = to_char(get_claim_id_rec.claim_id),
1102 customer_claim_created = 'Y',
1103 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1104 last_updated_by = l_txn_hdr_rec.LAST_UPDATED_BY,
1105 last_update_date = sysdate,
1106 last_update_login = l_txn_hdr_rec.LAST_UPDATED_BY
1107 WHERE CUSTOMER_INV_LINE_ID = l_txn_line_tbl(i).transaction_line_id;
1108
1109 IF SQL%ROWCOUNT = 0 THEN
1110 DPP_UTILITY_PVT.debug_message('Unable to Update the column CUSTOMER_CLAIM_ID in DPP_CUSTOMER_CLAIMS_ALL Table');
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 END IF;
1113 ELSE
1114 UPDATE DPP_CUSTOMER_CLAIMS_ALL
1115 SET customer_claim_created = 'N',
1116 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1117 last_updated_by = l_txn_hdr_rec.LAST_UPDATED_BY,
1118 last_update_date = sysdate,
1119 last_update_login = l_txn_hdr_rec.LAST_UPDATED_BY
1120 WHERE CUSTOMER_INV_LINE_ID = l_txn_line_tbl(i).transaction_line_id;
1121
1122 IF SQL%ROWCOUNT = 0 THEN
1123 DPP_UTILITY_PVT.debug_message('Unable to Update the column customer_claim_created in DPP_CUSTOMER_CLAIMS_ALL Table');
1124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1125 END IF;
1126 END IF;
1127 END LOOP;
1128 END LOOP;
1129 ELSE
1130 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1131 FND_MESSAGE.set_token('ID', 'Claim Type Flag');
1132 FND_MSG_PUB.add;
1133 RAISE FND_API.G_EXC_ERROR;
1134 END IF;
1135 END LOOP; -- End Loop for DPP Price Increase Enhancement
1136
1137 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1138 x_return_status := l_return_status;
1139 END IF;
1140 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1141 l_status := 'SUCCESS';
1142 --Output XML Generation Code.
1143 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_transaction_number||'Txnnumber,
1144 CURSOR (SELECT DISTINCT claim_number claimnumber
1145 FROM DPP_CUSTOMER_CLAIMS_GT) TRANSACTION
1146 FROM dual');
1147 ELSE
1148 l_status := 'WARNING';
1149 --Output XML Generation Code.
1150 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_transaction_number||'Txnnumber,
1151 CURSOR(SELECT claim_number claimnumber,
1152 customer_name customername,
1153 currency,
1154 item_number itemnumber ,
1155 reason_for_failure reason
1159 ,'ROOT'
1156 FROM DPP_CUSTOMER_CLAIMS_GT) transaction FROM dual');
1157 END IF;
1158 dbms_xmlquery.setRowTag(l_queryCtx
1160 );
1161 l_output_xml := dbms_xmlquery.getXml(l_queryCtx);
1162 dbms_xmlquery.closeContext(l_queryCtx);
1163 --Call the Update_Executiondetails Procedure to update the execution details table and commit the transaction
1164 Update_Executiondetails(l_status,
1165 l_txn_hdr_rec,
1166 l_output_xml,
1167 l_api_name
1168 );
1169 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1170 RAISE FND_API.G_EXC_ERROR;
1171 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1173 END IF;
1174 /*--Update the execution details table
1175 UPDATE DPP_EXECUTION_DETAILS
1176 SET EXECUTION_END_DATE = sysdate,
1177 OUTPUT_XML = XMLTYPE(l_output_xml),
1178 EXECUTION_STATUS = l_status,
1179 LAST_UPDATED_BY = l_txn_hdr_rec.Last_Updated_By,
1180 LAST_UPDATE_DATE = sysdate,
1181 PROVIDER_PROCESS_ID = l_txn_hdr_rec.Provider_Process_Id,
1182 PROVIDER_PROCESS_INSTANCE_ID = l_txn_hdr_rec.Provider_Process_Instance_id,
1183 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
1184 WHERE EXECUTION_DETAIL_ID = l_txn_hdr_rec.Execution_Detail_ID
1185 AND transaction_header_id = l_txn_hdr_rec.Transaction_Header_ID;
1186
1187 IF SQL%ROWCOUNT = 0 THEN
1188 DPP_UTILITY_PVT.debug_message('Unable to Update DPP_EXECUTION_DETAILS Table');
1189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1190 END IF;
1191 */
1192
1193
1194 -- Debug Message
1195 IF g_debug THEN
1196 DPP_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'end');
1197 END IF;
1198 -- Standard check for p_commit
1199 IF FND_API.to_Boolean( p_commit )
1200 THEN
1201 COMMIT;
1202 END IF;
1203 -- Standard call to get message count and if count is 1, get message info.
1204 FND_MSG_PUB.Count_And_Get
1205 (p_count => x_msg_count,
1206 p_data => x_msg_data
1207 );
1208 --Exception Handling
1209 EXCEPTION
1210 WHEN DPP_UTILITY_PVT.resource_locked THEN
1211 ROLLBACK TO CREATE_CLAIMS_PVT;
1212 x_return_status := FND_API.g_ret_sts_error;
1213 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
1214 FND_MSG_PUB.Count_And_Get (
1215 p_encoded => FND_API.G_FALSE,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219 IF x_msg_count > 1 THEN
1220 FOR I IN 1..x_msg_count LOOP
1221 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1222 END LOOP;
1223 END IF;
1224 WHEN FND_API.G_EXC_ERROR THEN
1225 ROLLBACK TO CREATE_CLAIMS_PVT;
1226 x_return_status := FND_API.G_RET_STS_ERROR;
1227 -- Standard call to get message count and if count=1, get the message
1228 FND_MSG_PUB.Count_And_Get (
1229 p_encoded => FND_API.G_FALSE,
1230 p_count => x_msg_count,
1231 p_data => x_msg_data
1232 );
1233 IF x_msg_count > 1 THEN
1234 FOR I IN 1..x_msg_count LOOP
1235 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1236 END LOOP;
1237 END IF;
1238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1239 ROLLBACK TO CREATE_CLAIMS_PVT;
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1241 -- Standard call to get message count and if count=1, get the message
1242 FND_MSG_PUB.Count_And_Get (
1243 p_encoded => FND_API.G_FALSE,
1244 p_count => x_msg_count,
1245 p_data => x_msg_data
1246 );
1247 IF x_msg_count > 1 THEN
1248 FOR I IN 1..x_msg_count LOOP
1249 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1250 END LOOP;
1251 END IF;
1252 WHEN OTHERS THEN
1253 ROLLBACK TO CREATE_CLAIMS_PVT;
1254 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1255 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1256 fnd_message.set_token('ERRNO', sqlcode);
1257 fnd_message.set_token('REASON', sqlerrm);
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1259 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1260 THEN
1261 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1262 END IF;
1263 -- Standard call to get message count and if count=1, get the message
1264 FND_MSG_PUB.Count_And_Get (
1265 p_encoded => FND_API.G_FALSE,
1266 p_count => x_msg_count,
1267 p_data => x_msg_data
1268 );
1269 IF x_msg_count > 1 THEN
1270 FOR I IN 1..x_msg_count LOOP
1271 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1272 END LOOP;
1273 END IF;
1274
1275 END Create_Claims;
1276
1277 ---------------------------------------------------------------------
1278 -- PROCEDURE
1279 -- Update_Claims
1280 --
1281 -- PURPOSE
1282 -- Update Claims
1283 --
1284 -- PARAMETERS
1285 --
1286 -- NOTES
1287 -- 1.
1288 -- 2.
1289 ----------------------------------------------------------------------
1290 PROCEDURE Update_Claims(
1291 p_api_version IN NUMBER
1295 ,x_return_status OUT NOCOPY VARCHAR2
1292 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1293 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1294 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1296 ,x_msg_count OUT NOCOPY NUMBER
1297 ,x_msg_data OUT NOCOPY VARCHAR2
1298 ,p_txn_hdr_rec IN OUT NOCOPY dpp_txn_hdr_rec_type
1299 ,p_txn_line_tbl IN OUT NOCOPY dpp_txn_line_tbl_type
1300 )
1301 IS
1302 l_api_name CONSTANT VARCHAR2(30) := 'Update_Claims';
1303 l_api_version CONSTANT NUMBER := 1.0;
1304 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1305
1306 l_return_status VARCHAR2(30);
1307 l_msg_count NUMBER;
1308 l_msg_data VARCHAR2(4000);
1309
1310 l_txn_hdr_rec DPP_CLAIMS_PVT.dpp_txn_hdr_rec_type := p_txn_hdr_rec;
1311 l_txn_line_tbl DPP_CLAIMS_PVT.dpp_txn_line_tbl_type := p_txn_line_tbl;
1312
1313 l_claim_pub_rec OZF_Claim_PUB.claim_rec_type;
1314 l_claim_line_pub_tbl OZF_Claim_PUB.claim_line_tbl_type;
1315
1316 l_output_xml CLOB;
1317 l_queryCtx dbms_xmlquery.ctxType;
1318
1319 l_object_version_number NUMBER := 1.0;
1320 l_claim_line_id NUMBER;
1321 l_claim_line_number NUMBER;
1322 l_set_of_books_id NUMBER;
1323 l_valid_flag VARCHAR2(1);
1324 l_user_status_id NUMBER := 2003; --Pending_close status: 2003
1325 l_cust_account_id NUMBER;
1326 l_status VARCHAR2(240);
1327 l_transaction_number VARCHAR2(240);
1328 l_reason VARCHAR2(4000);
1329 L_CLAIM_LINE_OBJ_VER NUMBER;
1330
1331 BEGIN
1332 -- Standard begin of API savepoint
1333 SAVEPOINT Update_Claims_PVT;
1334 -- Standard call to check for call compatibility.
1335 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1336 p_api_version,
1337 l_api_name,
1338 G_PKG_NAME)
1339 THEN
1340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1341 END IF;
1342 -- Initialize message list if p_init_msg_list is set to TRUE.
1343 IF FND_API.to_Boolean( p_init_msg_list )
1344 THEN
1345 FND_MSG_PUB.initialize;
1346 END IF;
1347
1348 -- Debug Message
1349 IF g_debug THEN
1350 DPP_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'start');
1351 END IF;
1352 -- Initialize API return status to sucess
1353 l_return_status := FND_API.G_RET_STS_SUCCESS;
1354
1355 IF l_txn_hdr_rec.Transaction_number IS NULL THEN
1356 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1357 FND_MESSAGE.set_token('ID', 'Transaction Number');
1358 FND_MSG_PUB.add;
1359 RAISE FND_API.G_EXC_ERROR;
1360 ELSIF l_txn_hdr_rec.claim_id IS NULL THEN
1361 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1362 FND_MESSAGE.set_token('ID', 'Claim ID');
1363 FND_MSG_PUB.add;
1364 RAISE FND_API.G_EXC_ERROR;
1365 ELSIF l_txn_hdr_rec.Transaction_Header_ID IS NULL THEN
1366 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1367 FND_MESSAGE.set_token('ID', 'Transaction Header ID');
1368 FND_MSG_PUB.add;
1369 RAISE FND_API.G_EXC_ERROR;
1370 ELSE
1371 l_transaction_number := ''''||l_txn_hdr_rec.Transaction_number||'''';
1372 IF g_debug THEN
1373 DPP_UTILITY_PVT.debug_message('Transaction Number: ' || l_txn_hdr_rec.Transaction_number);
1374 DPP_UTILITY_PVT.debug_message('Claim Id to be updated : ' || l_txn_hdr_rec.claim_id);
1375 END IF;
1376 END IF;
1377 --
1378 -- API body
1379 --
1380 MO_GLOBAL.set_policy_context('S',l_txn_hdr_rec.org_id);
1381 --Object Version Number and the cust account id
1382 BEGIN
1383 SELECT Object_version_number,
1384 cust_account_id
1385 INTO l_object_version_number,
1386 l_cust_account_id
1387 FROM ozf_claims_all
1388 WHERE claim_id = l_txn_hdr_rec.claim_id;
1389 EXCEPTION
1390 WHEN NO_DATA_FOUND THEN
1391 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1392 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1393 fnd_message.set_token('ERRNO', sqlcode);
1394 fnd_message.set_token('REASON', 'OBJECT VERSION NUMBER NOT FOUND');
1395 FND_MSG_PUB.add;
1396 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1397 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1398 END IF;
1399 RAISE FND_API.G_EXC_ERROR;
1400 WHEN OTHERS THEN
1401 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1402 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1403 fnd_message.set_token('ERRNO', sqlcode);
1404 fnd_message.set_token('REASON', sqlerrm);
1405 FND_MSG_PUB.add;
1406 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1407 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_OBJ_VER_NUM');
1408 fnd_message.set_token('CLAIM_ID', l_txn_hdr_rec.claim_id);
1409 FND_MSG_PUB.add;
1410 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1411 END IF;
1412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1413 END;
1414 --Clear the existing records from the table type variable..
1415 l_claim_line_pub_tbl.delete();
1419
1416 l_claim_pub_rec := NULL;
1417 --Header record
1418 l_claim_pub_rec.claim_id := l_txn_hdr_rec.claim_id;
1420 IF l_txn_hdr_rec.claim_amount < 0 THEN --ANBBALAS: For Price Increase enhancement
1421 l_claim_pub_rec.amount := -1 * l_txn_hdr_rec.claim_amount;
1422 ELSE
1423 l_claim_pub_rec.amount := l_txn_hdr_rec.claim_amount;
1424 END IF;
1425
1426 l_claim_pub_rec.currency_code := l_txn_hdr_rec.currency_code;
1427 l_claim_pub_rec.status_code := 'OPEN';
1428 l_claim_pub_rec.cust_account_id := l_cust_account_id;
1429 l_claim_pub_rec.object_version_number := l_object_version_number;
1430 l_claim_pub_rec.user_status_id := 2001; --For OPEN status
1431 --l_claim_pub_rec.payment_method := 'AP_DEBIT'; --ANBBALAS: For Price Increase enhancement
1432 l_claim_pub_rec.custom_setup_id := 300;
1433 --Line records
1434 IF l_txn_line_tbl.COUNT >0 THEN
1435 FOR i in l_txn_line_tbl.FIRST..l_txn_line_tbl.LAST LOOP
1436 BEGIN
1437 SELECT claim_line_id,
1438 line_number,
1439 set_of_books_id,
1440 valid_flag,
1441 object_version_number
1442 INTO l_claim_line_id,
1443 l_claim_line_number,
1444 l_set_of_books_id,
1445 l_valid_flag,
1446 L_CLAIM_LINE_OBJ_VER
1447 FROM ozf_claim_lines_all
1448 WHERE claim_id = l_txn_hdr_rec.claim_id
1449 AND item_id = l_txn_line_tbl(i).Inventory_Item_Id;
1450
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1454 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1455 fnd_message.set_token('ERRNO', sqlcode);
1456 fnd_message.set_token('REASON', 'CLAIM LINE DETAILS NOT FOUND');
1457 FND_MSG_PUB.add;
1458 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1459 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1460 END IF;
1461 RAISE FND_API.G_EXC_ERROR;
1462 WHEN OTHERS THEN
1463 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1464 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1465 fnd_message.set_token('ERRNO', sqlcode);
1466 fnd_message.set_token('REASON', sqlerrm);
1467 FND_MSG_PUB.add;
1468 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1469 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_LINE_DETAILS');
1470 fnd_message.set_token('CLAIM_ID', l_txn_hdr_rec.claim_id);
1471 fnd_message.set_token('ITEM_ID', l_txn_line_tbl(i).Inventory_Item_Id);
1472 FND_MSG_PUB.add;
1473 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1474 END IF;
1475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1476 END;
1477 l_claim_line_pub_tbl(i).claim_id := l_txn_hdr_rec.claim_id;
1478 l_claim_line_pub_tbl(i).claim_line_id := l_claim_line_id;
1479 --l_claim_line_pub_tbl(i).claim_currency_amount := l_txn_line_tbl(i).Claim_Line_Amount; --ANBBALAS: For Price Increase enhancement
1480 l_claim_line_pub_tbl(i).quantity := l_txn_line_tbl(i).Claim_Quantity;
1481 l_claim_line_pub_tbl(i).currency_code := l_txn_line_tbl(i).Currency;
1482 l_claim_line_pub_tbl(i).item_id := l_txn_line_tbl(i).inventory_item_id;
1483 l_claim_line_pub_tbl(i).line_number :=l_claim_line_number;
1484 l_claim_line_pub_tbl(i).set_of_books_id := l_set_of_books_id;
1485 l_claim_line_pub_tbl(i).valid_flag := l_valid_flag;
1486 l_claim_line_pub_tbl(i).object_version_number := l_claim_line_obj_ver;
1487 --l_claim_line_pub_tbl(i).amount := l_txn_line_tbl(i).Claim_Line_Amount; --ANBBALAS: For Price Increase enhancement
1488
1489 IF l_txn_line_tbl(i).Claim_Line_Amount < 0 THEN --ANBBALAS: For Price Increase enhancement
1490 l_claim_line_pub_tbl(i).claim_currency_amount := -1 * l_txn_line_tbl(i).Claim_Line_Amount;
1491 l_claim_line_pub_tbl(i).amount := -1 * l_txn_line_tbl(i).Claim_Line_Amount;
1492 ELSE
1493 l_claim_line_pub_tbl(i).claim_currency_amount := l_txn_line_tbl(i).Claim_Line_Amount;
1494 l_claim_line_pub_tbl(i).amount := l_txn_line_tbl(i).Claim_Line_Amount;
1495 END IF;
1496
1497 END LOOP;
1498 END IF;
1499
1500 --Invoke the standard API with the above defined parameters.
1501 OZF_CLAIM_PUB.Update_Claim (p_api_version_number => l_api_version
1502 ,p_init_msg_list => FND_API.G_FALSE
1503 ,p_commit => FND_API.G_False
1504 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1505 ,x_return_status => l_return_status
1506 ,x_msg_count => l_msg_count
1507 ,x_msg_data => l_msg_data
1508 ,p_claim_rec => l_claim_pub_rec
1509 ,p_claim_line_tbl => l_claim_line_pub_tbl
1510 ,x_object_version_number => l_object_version_number
1511 );
1512 IF g_debug THEN
1513 dpp_utility_pvt.debug_message('return status for Update_Claims =>'||l_return_status);
1514 dpp_utility_pvt.debug_message(substr(('Message Data for OZF Update_Claims =>'||l_msg_data),1,4000));
1515 END IF;
1516 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1517 IF g_debug THEN
1518 dpp_utility_pvt.debug_message('return status for Update_Claims =>'||l_return_status);
1519 dpp_utility_pvt.debug_message('Unable to Update the claim to OPEN status');
1520 END IF;
1521 ELSE
1522 --Bug#6928445
1523 IF l_txn_hdr_rec.claim_amount <> 0 THEN --ANBBALAS: For Price Increase enhancement
1524 --Update the Claim to PENDING CLOSE status
1525 l_claim_pub_rec := NULL;
1526 l_claim_line_pub_tbl.delete();
1527 l_claim_pub_rec.claim_id := l_txn_hdr_rec.claim_id;
1528 l_claim_pub_rec.user_status_id := 2003; --For PENDING_CLOSE status
1529 l_claim_pub_rec.status_code := l_txn_hdr_rec.claim_status_code;
1530 --l_claim_pub_rec.payment_method := 'AP_DEBIT'; --ANBBALAS: For Price Increase enhancement
1531 l_claim_pub_rec.custom_setup_id := 300;
1532
1533 --Retrieve the object version number
1534 BEGIN
1535 SELECT Object_version_number
1536 INTO l_object_version_number
1537 FROM ozf_claims
1538 WHERE claim_id = l_txn_hdr_rec.claim_id;
1539 EXCEPTION
1540 WHEN NO_DATA_FOUND THEN
1541 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1542 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1543 fnd_message.set_token('ERRNO', sqlcode);
1544 fnd_message.set_token('REASON', 'OBJECT VERSION NUMBER NOT FOUND');
1545 FND_MSG_PUB.add;
1546 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1547 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1548 END IF;
1549 RAISE FND_API.G_EXC_ERROR;
1550 WHEN OTHERS THEN
1551 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1552 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1553 fnd_message.set_token('ERRNO', sqlcode);
1554 fnd_message.set_token('REASON', sqlerrm);
1555 FND_MSG_PUB.add;
1556 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1557 FND_MESSAGE.set_name('DPP', 'DPP_CLAIM_INVALID_OBJ_VER_NUM');
1558 fnd_message.set_token('CLAIM_ID', l_txn_hdr_rec.claim_id);
1559 FND_MSG_PUB.add;
1560 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1561 END IF;
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END;
1564 l_claim_pub_rec.object_version_number := l_object_version_number;
1565 --Invoke the standard API with the above defined parameters.
1566 OZF_CLAIM_PUB.Update_Claim (p_api_version_number => l_api_version
1567 ,p_init_msg_list => FND_API.G_FALSE
1568 ,p_commit => FND_API.G_FALSE
1569 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1570 ,x_return_status => l_return_status
1571 ,x_msg_count => l_msg_count
1572 ,x_msg_data => l_msg_data
1573 ,p_claim_rec => l_claim_pub_rec
1574 ,p_claim_line_tbl => l_claim_line_pub_tbl
1575 ,x_object_version_number => l_object_version_number
1576 );
1577 IF g_debug THEN
1578 dpp_utility_pvt.debug_message('return status for Update_Claims =>'||l_return_status);
1579 dpp_utility_pvt.debug_message(substr(('Message data for OZF Update_Claims =>'||l_msg_data),1,4000));
1580 END IF;
1581 END IF; --IF l_txn_hdr_rec.claim_amount > 0 THEN
1582 END IF;
1583 l_reason := ''''||substr(l_msg_data,1,3990)||'''';
1584 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1585 l_status := 'SUCCESS';
1586 ELSE
1587 l_status := 'WARNING';
1588 END IF;
1589 --Output XML Generation Code
1590 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_transaction_number||'txnnumber,
1591 claim_number claimnumber,'
1592 ||l_reason||'reason
1593 FROM ozf_claims_all
1594 WHERE claim_id = '||l_txn_hdr_rec.claim_id);
1595 dbms_xmlquery.setRowTag(l_queryCtx
1596 ,'ROOT'
1597 );
1598 l_output_xml := dbms_xmlquery.getXml(l_queryCtx);
1599 dbms_xmlquery.closeContext(l_queryCtx);
1600 --Call the Update_Executiondetails Procedure to update the execution details table and commit the transaction
1601 Update_Executiondetails(l_status,
1602 l_txn_hdr_rec,
1603 l_output_xml,
1604 l_api_name
1605 );
1606 x_return_status := l_return_status;
1607 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1608 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1609 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1610 fnd_message.set_token('ERRNO', sqlcode);
1611 fnd_message.set_token('REASON', 'Error while Updating claim in Trade Management');
1612 FND_MSG_PUB.add;
1613 RAISE FND_API.G_EXC_ERROR;
1614 END IF;
1615
1616 -- Debug Message
1617 IF g_debug THEN
1618 DPP_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'end');
1619 END IF;
1620 -- Standard check for p_commit
1621 IF FND_API.to_Boolean( p_commit )
1622 THEN
1623 COMMIT;
1624 END IF;
1625 -- Standard call to get message count and if count is 1, get message info.
1626 FND_MSG_PUB.Count_And_Get
1627 (p_count => x_msg_count,
1628 p_data => x_msg_data
1629 );
1630 --Exception Handling
1631 EXCEPTION
1632 WHEN DPP_UTILITY_PVT.resource_locked THEN
1633 ROLLBACK TO UPDATE_CLAIMS_PVT;
1634 x_return_status := FND_API.g_ret_sts_error;
1635 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
1636 FND_MSG_PUB.Count_And_Get (
1637 p_encoded => FND_API.G_FALSE,
1638 p_count => x_msg_count,
1639 p_data => x_msg_data
1640 );
1641 IF x_msg_count > 1 THEN
1642 FOR I IN 1..x_msg_count LOOP
1643 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1644 END LOOP;
1645 END IF;
1646 WHEN FND_API.G_EXC_ERROR THEN
1647 ROLLBACK TO UPDATE_CLAIMS_PVT;
1648 x_return_status := FND_API.G_RET_STS_ERROR;
1649 -- Standard call to get message count and if count=1, get the message
1650 FND_MSG_PUB.Count_And_Get (
1651 p_encoded => FND_API.G_FALSE,
1652 p_count => x_msg_count,
1653 p_data => x_msg_data
1654 );
1655 IF x_msg_count > 1 THEN
1656 FOR I IN 1..x_msg_count LOOP
1657 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1658 END LOOP;
1659 END IF;
1660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1661 ROLLBACK TO UPDATE_CLAIMS_PVT;
1662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1663 -- Standard call to get message count and if count=1, get the message
1664 FND_MSG_PUB.Count_And_Get (
1665 p_encoded => FND_API.G_FALSE,
1666 p_count => x_msg_count,
1667 p_data => x_msg_data
1668 );
1669 IF x_msg_count > 1 THEN
1670 FOR I IN 1..x_msg_count LOOP
1671 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1672 END LOOP;
1673 END IF;
1674 WHEN OTHERS THEN
1675 ROLLBACK TO UPDATE_CLAIMS_PVT;
1676 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1677 fnd_message.set_token('ROUTINE', 'DPP_CLAIMS_PVT');
1678 fnd_message.set_token('ERRNO', sqlcode);
1679 fnd_message.set_token('REASON', sqlerrm);
1680 FND_MSG_PUB.add;
1681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1682 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1683 THEN
1684 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1685 END IF;
1686 -- Standard call to get message count and if count=1, get the message
1687 FND_MSG_PUB.Count_And_Get (
1688 p_encoded => FND_API.G_FALSE,
1689 p_count => x_msg_count,
1690 p_data => x_msg_data
1691 );
1692 IF x_msg_count > 1 THEN
1693 FOR I IN 1..x_msg_count LOOP
1694 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1695 END LOOP;
1696 END IF;
1697
1698 END Update_Claims;
1699 END DPP_CLAIMS_PVT;