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