[Home] [Help]
PACKAGE BODY: APPS.DPP_CUSTOMERCLAIMS_PVT
Source
4 -- Package name : DPP_CUSTOMERCLAIMS_PVT
1 PACKAGE BODY DPP_CUSTOMERCLAIMS_PVT AS
2 /* $Header: dppvcusb.pls 120.22 2011/06/12 13:37:57 rvkondur ship $ */
3
5 -- Purpose :
6 -- History :
7 -- NOTE : Contains Procedures - Select Data for Customer Claims Tab Prepopulation, Populate data in DPP
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_CUSTOMERCLAIMS_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) := 'dppvcusb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- Select_CustomerPrice
17 --
18 -- PURPOSE
19 -- Select Customer Price
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
27
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
28 PROCEDURE Select_CustomerPrice(
29 p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
31 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
32 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ,p_cust_hdr_rec IN dpp_cust_hdr_rec_type
37 ,p_customer_tbl IN OUT NOCOPY dpp_customer_tbl_type
38 )
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Select_CustomerPrice';
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
43
44 l_result number;
45 l_count number;
46
47
48 l_return_status varchar2(30);
49 l_msg_count number;
50 l_msg_data varchar2(4000);
51
52 l_cust_hdr_rec DPP_CUSTOMERCLAIMS_PVT.dpp_cust_hdr_rec_type := p_cust_hdr_rec;
53 l_customer_tbl DPP_CUSTOMERCLAIMS_PVT.dpp_customer_tbl_type := p_customer_tbl;
54 l_customer_price_tbl DPP_CUSTOMERCLAIMS_PVT.dpp_customer_price_tbl_type;
55 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_CUSTOMERCLAIMS_PVT.SELECT_CUSTOMERPRICE';
56
57 CURSOR get_customer_csr (p_org_id IN NUMBER,
58 p_inventory_item_id IN NUMBER,
59 p_start_date IN DATE,
60 p_end_date IN DATE) IS
61 SELECT oola.sold_to_org_id customer_id
62 FROM oe_order_headers_all ooha,
63 oe_order_lines_all oola,
64 hz_cust_accounts hca
65 WHERE ooha.header_id = oola.header_id
66 AND ooha.org_id = oola.org_id
67 AND ooha.org_id = p_org_id
68 AND oola.inventory_item_id = p_inventory_item_id
69 AND (actual_shipment_date >= p_start_date AND actual_shipment_date < p_end_date)
70 --BETWEEN p_start_date AND p_end_date
71 AND hca.cust_account_id = oola.sold_to_org_id
72 AND hca.status = 'A'
73 GROUP BY oola.sold_to_org_id;
74
75 CURSOR get_last_price_csr (p_org_id IN NUMBER,
76 p_inventory_item_id IN NUMBER,
77 p_customer_id IN NUMBER,
78 p_uom_code IN VARCHAR2) IS
79 SELECT
80 rct.sold_to_customer_id cust_account_id,
81 unit_selling_price last_price,
82 rct.invoice_currency_code
83 FROM
84 ra_customer_trx_lines_all rctl,
85 ra_customer_trx_all rct,
86 ra_cust_trx_types_all rctt
87 WHERE
88 line_type = 'LINE' AND
89 inventory_item_id = p_inventory_item_id AND
90 uom_code = p_uom_code AND
91 rct.customer_trx_id = rctl.customer_trx_id AND
92 rct.org_id = p_org_id AND
93 rctt.cust_trx_type_id = rct.cust_trx_type_id AND
94 rct.org_id = rctt.org_id AND
95 rctt.name = 'Invoice' AND
96 rct.org_id = rctl.org_id AND
97 rct.sold_to_customer_id = p_customer_id AND
98 rct.complete_flag = 'Y' AND
99 rctl.customer_trx_line_id = (
100 SELECT
101 MAX(rctl1.customer_trx_line_id)
102 FROM
103 ra_customer_trx_lines_all rctl1,
104 ra_customer_trx_all rct1,
105 ra_cust_trx_types_all rctt1
106 WHERE
107 line_type = 'LINE' AND
108 inventory_item_id = p_inventory_item_id AND
109 uom_code = p_uom_code AND
110 rct1.customer_trx_id = rctl1.customer_trx_id AND
111 rct1.org_id = p_org_id AND
112 rctt1.cust_trx_type_id = rct1.cust_trx_type_id AND
113 rct1.org_id = rctt1.org_id AND
114 rctt1.name = 'Invoice' AND
115 rct1.org_id = rctl1.org_id AND
116 rct1.sold_to_customer_id = p_customer_id AND
117 rct1.complete_flag = 'Y');
118
119 BEGIN
120
121
122 -- Standard begin of API savepoint
123 SAVEPOINT Select_CustomerPrice_PVT;
124 -- Standard call to check for call compatibility.
125 IF NOT FND_API.Compatible_API_Call ( l_api_version,
126 p_api_version,
127 l_api_name,
128 G_PKG_NAME)
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132 -- Initialize message list if p_init_msg_list is set to TRUE.
133 IF FND_API.to_Boolean( p_init_msg_list )
134 THEN
135 FND_MSG_PUB.initialize;
136 END IF;
137
138 -- Debug Message
139
140 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'start');
141
142 -- Initialize API return status to sucess
143 l_return_status := FND_API.G_RET_STS_SUCCESS;
144 --
145 -- API body
146 --
147 IF l_cust_hdr_rec.org_id IS NULL THEN
148 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
149 FND_MESSAGE.set_token('ID', 'Org ID');
150 FND_MSG_PUB.add;
151 RAISE FND_API.G_EXC_ERROR;
152 ELSIF l_cust_hdr_rec.Effective_Start_Date IS NULL THEN
153 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
154 FND_MESSAGE.set_token('ID', 'Effective Start Date');
155 FND_MSG_PUB.add;
156 RAISE FND_API.G_EXC_ERROR;
157 ELSIF l_cust_hdr_rec.Effective_End_Date IS NULL THEN
158 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
159 FND_MESSAGE.set_token('ID', 'Effective End Date');
160 FND_MSG_PUB.add;
161 RAISE FND_API.G_EXC_ERROR;
162 ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
166 RAISE FND_API.G_EXC_ERROR;
163 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
164 FND_MESSAGE.set_token('ID', 'Currency Code');
165 FND_MSG_PUB.add;
167 ELSE
168 IF l_customer_tbl.EXISTS(1) THEN
169 FOR i IN l_customer_tbl.FIRST..l_customer_tbl.LAST LOOP
170 IF l_customer_tbl(i).inventory_item_id IS NULL THEN
171 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
172 FND_MESSAGE.set_token('ID', 'Inventory Item Id');
173 FND_MSG_PUB.add;
174 RAISE FND_API.G_EXC_ERROR;
175 ELSIF l_customer_tbl(i).uom_code IS NULL THEN
176 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
177 FND_MESSAGE.set_token('ID', 'UOM Code');
178 FND_MSG_PUB.add;
179 RAISE FND_API.G_EXC_ERROR;
180 ELSE --Inventory item id null
181 l_customer_price_tbl.delete();
182 l_count :=0;
183 FOR get_customer_rec IN get_customer_csr(to_number(l_cust_hdr_rec.org_id),
184 to_number(l_customer_tbl(i).inventory_item_id),
185 l_cust_hdr_rec.Effective_Start_Date,
186 l_cust_hdr_rec.Effective_End_Date) LOOP
187 l_count := l_count + 1;
188 l_customer_price_tbl(l_count).cust_account_id := get_customer_rec.customer_id;
189 -- Debug Message
190
191 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Start Date: ' || l_cust_hdr_rec.Effective_Start_Date );
192 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'End Date: ' || l_cust_hdr_rec.Effective_End_Date );
193 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Formatted Start Date: ' || to_char(l_cust_hdr_rec.Effective_Start_Date,'DD-MON-YYYY') );
194 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Formatted End Date: ' || to_char(l_cust_hdr_rec.Effective_End_Date,'DD-MON-YYYY') );
195
196 FOR get_last_price_rec IN get_last_price_csr(to_number(l_cust_hdr_rec.org_id),
197 to_number(l_customer_tbl(i).inventory_item_id),
198 get_customer_rec.customer_id,
199 l_customer_tbl(i).uom_code) LOOP
200 l_customer_price_tbl(l_count).last_price := nvl(get_last_price_rec.last_price,0);
201 l_customer_price_tbl(l_count).invoice_currency_code := nvl(get_last_price_rec.invoice_currency_code,l_cust_hdr_rec.Currency_code);
202 END LOOP;
203 IF l_customer_price_tbl(l_count).last_price IS NULL THEN
204 l_customer_price_tbl(l_count).last_price := 0 ;
205 l_customer_price_tbl(l_count).invoice_currency_code := l_cust_hdr_rec.currency_code;
206 END IF;
207 END LOOP;
208 IF l_customer_price_tbl.COUNT = 0 THEN
209 l_customer_price_tbl(1).cust_account_id := NULL;
210 l_customer_price_tbl(1).last_price := NULL;
211 l_customer_price_tbl(1).invoice_currency_code := NULL;
212 END IF;
213 l_customer_tbl(i).customer_price_tbl := l_customer_price_tbl;
214 END IF; --Inventory item id null
215 END LOOP;
216 ELSE --No line details available
217 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
218 FND_MESSAGE.set_token('ID', 'Line Details');
219 FND_MSG_PUB.add;
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222
223 p_customer_tbl := l_customer_tbl;
224 x_return_status := l_return_status;
225 END IF;
226 -- Standard check for p_commit
227 IF FND_API.to_Boolean( p_commit )
228 THEN
229 COMMIT WORK;
230 END IF;
231 -- Debug Message
232
233 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'end');
234
235 -- Standard call to get message count and if count is 1, get message info.
236 FND_MSG_PUB.Count_And_Get
237 (p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240
241
242 --Exception Handling
243 EXCEPTION
244
245 WHEN FND_API.G_EXC_ERROR THEN
246 ROLLBACK TO Select_CustomerPrice_PVT;
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 -- Standard call to get message count and if count=1, get the message
249 FND_MSG_PUB.Count_And_Get (
250 p_encoded => FND_API.G_FALSE,
251 p_count => x_msg_count,
252 p_data => x_msg_data
253 );
254 IF x_msg_count > 1 THEN
255 FOR I IN 1..x_msg_count LOOP
256 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
257 END LOOP;
258 END IF;
259
260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261 ROLLBACK TO Select_CustomerPrice_PVT;
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 -- Standard call to get message count and if count=1, get the message
264 FND_MSG_PUB.Count_And_Get (
265 p_encoded => FND_API.G_FALSE,
266 p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269 IF x_msg_count > 1 THEN
270 FOR I IN 1..x_msg_count LOOP
271 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
272 END LOOP;
273 END IF;
274
275 WHEN OTHERS THEN
276 ROLLBACK TO Select_CustomerPrice_PVT;
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
279 fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Select_CustomerPrice');
280 fnd_message.set_token('ERRNO', sqlcode);
281 fnd_message.set_token('REASON', sqlerrm);
285 p_encoded => FND_API.G_FALSE,
282 FND_MSG_PUB.add;
283 -- Standard call to get message count and if count=1, get the message
284 FND_MSG_PUB.Count_And_Get (
286 p_count => x_msg_count,
287 p_data => x_msg_data
288 );
289 IF x_msg_count > 1 THEN
290 FOR I IN 1..x_msg_count LOOP
291 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
292 END LOOP;
293 END IF;
294
295 END Select_CustomerPrice;
296
297
298 ---------------------------------------------------------------------
299 -- PROCEDURE
300 -- Populate_CustomerPrice
301 --
302 -- PURPOSE
303 -- Populate Customer and Price
304 --
305 -- PARAMETERS
306 --
307 -- NOTES
308 -- 1.
309 -- 2.
310 ----------------------------------------------------------------------
311
312 PROCEDURE Populate_CustomerPrice(
313 p_api_version IN NUMBER
314 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
315 ,p_commit IN VARCHAR2 := FND_API.G_TRUE
316 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
317 ,x_return_status OUT NOCOPY VARCHAR2
318 ,x_msg_count OUT NOCOPY NUMBER
319 ,x_msg_data OUT NOCOPY VARCHAR2
320 ,p_cust_hdr_rec IN dpp_cust_hdr_rec_type
321 ,p_customer_tbl IN dpp_customer_tbl_type
322 )
323 IS
324 l_api_name CONSTANT VARCHAR2(30) := 'Populate_CustomerPrice';
325 l_api_version CONSTANT NUMBER := 1.0;
326 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
327
328 l_return_status varchar2(30);
329 l_msg_count number;
330 l_msg_data varchar2(4000);
331
332 l_cust_hdr_rec dpp_cust_hdr_rec_type := p_cust_hdr_rec;
333 l_customer_tbl dpp_customer_tbl_type := p_customer_tbl;
334 l_customer_price_tbl dpp_customer_price_tbl_type;
335 l_claim_lines_tbl DPP_LOG_PVT.dpp_claim_line_tbl_type;
336 l_hdr_rec DPP_UTILITY_PVT.dpp_inv_hdr_rec_type;
337 l_cust_inv_tbl DPP_UTILITY_PVT.dpp_cust_inv_tbl_type;
338
339 --l_lastprice_tbl DPP_UTILITY_PVT.dpp_cust_price_tbl_type;
340 l_result NUMBER;
341 l_cust_inv_line_id NUMBER;
342 l_line_number NUMBER := 0;
343 l_sysdate DATE := SYSDATE;
344 l_supp_new_price NUMBER := 0;
345 l_conv_supp_new_price NUMBER := 0;
346 l_rnd_supp_new_price NUMBER := 0;
347 l_conv_cust_new_price NUMBER := 0;
348 l_rnd_cust_new_price NUMBER := 0;
349 l_price_change NUMBER := 0;
350 l_conv_price_change NUMBER := 0;
351 l_rnd_price_change NUMBER := 0;
352 l_reported_inventory NUMBER := 0;
353 l_exchange_rate NUMBER;
354 l_rec_count NUMBER;
355 l_supp_claim_amt NUMBER;
356 l_cust_claim_amt NUMBER;
357 l_last_price NUMBER;
358 l_rounding NUMBER := fnd_profile.VALUE('DPP_NEW_PRICE_DECIMAL_PRECISION');
359 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_CUSTOMERCLAIMS_PVT.POPULATE_CUSTOMERPRICE';
360 l_profile_option_value VARCHAR2(20);
361
362 BEGIN
363 -- Standard begin of API savepoint
364 SAVEPOINT Populate_CustomerPrice_PVT;
365 -- Standard call to check for call compatibility.
366 IF NOT FND_API.Compatible_API_Call ( l_api_version,
367 p_api_version,
368 l_api_name,
369 G_PKG_NAME)
370 THEN
371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
372 END IF;
373 -- Initialize message list if p_init_msg_list is set to TRUE.
374 IF FND_API.to_Boolean( p_init_msg_list )
375 THEN
376 FND_MSG_PUB.initialize;
377 END IF;
378
379 -- Debug Message
380
381 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'start');
382
383 -- Initialize API return status to sucess
384 l_return_status := FND_API.G_RET_STS_SUCCESS;
385
386 l_profile_option_value := fnd_profile.VALUE('DPP_DEF_CUST_CLAIM_LINE_INFO');
387 --
388 -- API body
389 --
390 --Assign ) to thr rounding value
391 IF l_rounding IS NULL THEN
392 l_rounding := 4;
393 END IF;
394
395 IF l_cust_hdr_rec.org_id IS NULL THEN
396 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
397 FND_MESSAGE.set_token('ID', 'Org Id');
398 FND_MSG_PUB.add;
399 RAISE FND_API.G_EXC_ERROR;
400 ELSIF l_cust_hdr_rec.effective_start_date IS NULL THEN
401 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
402 FND_MESSAGE.set_token('ID', 'Effective Start Date');
403 FND_MSG_PUB.add;
404 RAISE FND_API.G_EXC_ERROR;
405 ELSIF l_cust_hdr_rec.effective_end_date IS NULL THEN
406 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
407 FND_MESSAGE.set_token('ID', 'Effective End Date');
408 FND_MSG_PUB.add;
409 RAISE FND_API.G_EXC_ERROR;
410 ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
411 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
412 FND_MESSAGE.set_token('ID', 'Currency Code');
413 FND_MSG_PUB.add;
414 RAISE FND_API.G_EXC_ERROR;
415 ELSE
416 IF l_customer_tbl.EXISTS(1) THEN
417 IF l_profile_option_value = 'Y' OR l_profile_option_value IS NULL THEN
418 FOR i IN l_customer_tbl.FIRST..l_customer_tbl.LAST LOOP
419 l_supp_new_price := 0;
420 IF l_customer_tbl(i).inventory_item_id IS NULL THEN
421 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
422 FND_MESSAGE.set_token('ID', 'Inventory Item Id');
423 FND_MSG_PUB.add;
424 RAISE FND_API.G_EXC_ERROR;
425 ELSE
426 BEGIN
427 SELECT SUPPLIER_NEW_PRICE, PRICE_CHANGE
431 EXCEPTION
428 INTO l_supp_new_price, l_price_change
429 FROM DPP_TRANSACTION_LINES_ALL
430 WHERE transaction_line_id = l_customer_tbl(i).transaction_line_id;
432 WHEN NO_DATA_FOUND THEN
433 l_supp_new_price := 0;
434 END;
435 -- Debug Message
436
437 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Supp Price: '||l_supp_new_price);
438
439 IF l_customer_tbl(i).customer_price_tbl.EXISTS(1) THEN
440 FOR j IN l_customer_tbl(i).customer_price_tbl.FIRST..l_customer_tbl(i).customer_price_tbl.LAST LOOP
441 l_conv_supp_new_price := 0;
442 l_rnd_supp_new_price := 0;
443 l_conv_price_change := 0;
444 l_rnd_price_change := 0;
445 l_reported_inventory := 0;
446 IF l_customer_tbl(i).customer_price_tbl(j).cust_account_id IS NOT NULL THEN
447 SELECT DPP_CUST_INV_LINE_ID_SEQ.nextval
448 INTO l_cust_inv_line_id
449 FROM DUAL;
450 l_line_number := l_line_number + 1;
451 l_hdr_rec.org_id := l_cust_hdr_rec.org_id;
452 l_hdr_rec.effective_start_date := l_cust_hdr_rec.effective_start_date;
453 l_hdr_rec.effective_end_date := l_cust_hdr_rec.effective_end_date;
454
455 l_cust_inv_tbl.delete();
456 l_cust_inv_tbl(1).inventory_item_id := to_number(l_customer_tbl(i).inventory_item_id);
457 l_cust_inv_tbl(1).customer_id := l_customer_tbl(i).customer_price_tbl(j).cust_account_id;
458
459 DPP_UTILITY_PVT.Get_CustomerInventory(p_hdr_rec => l_hdr_rec
460 ,p_cust_inv_tbl => l_cust_inv_tbl
461 ,x_rec_count => l_rec_count
462 ,x_return_status => l_return_status
463 );
464 --Debug Message
465
466 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Return status for Get_CustomerInventory: '||l_return_status);
467 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Cust O/H: '||l_cust_inv_tbl(1).onhand_quantity);
468 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Customer ID: '||l_customer_tbl(i).customer_price_tbl(j).cust_account_id);
469
470 --Convert supp new price
471 DPP_UTILITY_PVT.convert_currency(p_from_currency => l_cust_hdr_rec.currency_code
472 ,p_to_currency => l_customer_tbl(i).customer_price_tbl(j).invoice_currency_code
473 ,p_conv_type => FND_API.G_MISS_CHAR
474 ,p_conv_rate => FND_API.G_MISS_NUM
475 ,p_conv_date => trunc(SYSDATE)
476 ,p_from_amount => l_supp_new_price
477 ,x_return_status => l_return_status
478 ,x_to_amount => l_conv_supp_new_price
479 ,x_rate => l_exchange_rate);
480 l_rnd_supp_new_price := ROUND(l_conv_supp_new_price,l_rounding);
481 IF l_price_change <> 0 THEN
482 -- convert price change
483 DPP_UTILITY_PVT.convert_currency(p_from_currency => l_cust_hdr_rec.currency_code
484 ,p_to_currency => l_customer_tbl(i).customer_price_tbl(j).invoice_currency_code
485 ,p_conv_type => FND_API.G_MISS_CHAR
486 ,p_conv_rate => FND_API.G_MISS_NUM
487 ,p_conv_date => trunc(SYSDATE)
488 ,p_from_amount => l_price_change
489 ,x_return_status => l_return_status
490 ,x_to_amount => l_conv_price_change
491 ,x_rate => l_exchange_rate);
492 l_rnd_price_change := ROUND(l_conv_price_change,l_rounding);
493 ELSE
494 l_conv_price_change := 0;
495 l_rnd_price_change :=0;
496 END IF;
497 -- if last invoice price is not available, default customer new price to 0.
498 IF NVL(l_customer_tbl(i).customer_price_tbl(j).last_price,0) > 0 THEN
499 l_conv_cust_new_price := NVL(l_customer_tbl(i).customer_price_tbl(j).last_price,0) - l_rnd_price_change;
500 l_rnd_cust_new_price := ROUND(l_conv_cust_new_price,l_rounding);
501 IF l_rnd_cust_new_price < 0 THEN
502 l_rnd_cust_new_price := 0;
503 END IF;
504 ELSE
505 l_conv_cust_new_price := 0;
506 l_rnd_cust_new_price := 0;
507 END IF;
508 -- If calculated inventory is -ve, then reported inventory should be 0.
509 IF NVL(l_cust_inv_tbl(1).onhand_quantity,0) < 0 THEN
510 l_reported_inventory := 0;
511 ELSE
515 --prior price = last_price
512 l_reported_inventory := NVL(l_cust_inv_tbl(1).onhand_quantity,0);
513 END IF;
514 --Calculate the supplier and the customer claim amount
516 --price change := converted price change from get_last price api
517 l_last_price := ROUND(NVL(l_customer_tbl(i).customer_price_tbl(j).last_price,0),l_rounding);
518 l_cust_claim_amt := (l_reported_inventory * (l_last_price - l_rnd_cust_new_price));
519 l_supp_claim_amt := (l_reported_inventory * l_rnd_price_change);
520
521 BEGIN
522 INSERT INTO DPP_CUSTOMER_CLAIMS_ALL(TRANSACTION_HEADER_ID,
523 CUSTOMER_INV_LINE_ID,
524 LINE_NUMBER,
525 LAST_PRICE,
526 SUPPLIER_NEW_PRICE,
527 CUSTOMER_NEW_PRICE,
528 TRX_CURRENCY,
529 REPORTED_INVENTORY,
530 CALCULATED_INVENTORY,
531 UOM,
532 CREATION_DATE,
533 CREATED_BY,
534 LAST_UPDATE_DATE,
535 LAST_UPDATED_BY,
536 LAST_UPDATE_LOGIN,
537 INVENTORY_ITEM_ID,
538 CUST_ACCOUNT_ID,
539 ORG_ID,
540 OBJECT_VERSION_NUMBER,
541 SUPPLIER_PRICE_DROP,
542 CUST_CLAIM_AMT,
543 SUPP_CLAIM_AMT,
544 CUSTOMER_CLAIM_CREATED,
545 SUPPLIER_CLAIM_CREATED)
546 VALUES(l_cust_hdr_rec.transaction_header_id,
547 l_cust_inv_line_id,
548 l_line_number,
549 l_last_price,
550 l_rnd_supp_new_price,
551 l_rnd_cust_new_price,
552 nvl(l_customer_tbl(i).customer_price_tbl(j).invoice_currency_code,l_cust_hdr_rec.currency_code),
553 l_reported_inventory,
554 NVL(l_cust_inv_tbl(1).onhand_quantity,0),
555 NVL(l_cust_inv_tbl(1).uom_code, l_customer_tbl(i).uom_code),
556 l_sysdate,
557 l_cust_hdr_rec.Last_Updated_By,
558 l_sysdate,
559 l_cust_hdr_rec.Last_Updated_By,
560 FND_GLOBAL.login_ID,
561 l_customer_tbl(i).inventory_item_id,
562 l_customer_tbl(i).customer_price_tbl(j).cust_account_id,
563 l_cust_hdr_rec.org_id,
564 1,
565 l_rnd_price_change,
566 l_cust_claim_amt,
567 l_supp_claim_amt,
568 'N',
569 'N');
570 EXCEPTION
571 WHEN OTHERS THEN
572 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
573 fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
574 fnd_message.set_token('ERRNO', sqlcode);
575 fnd_message.set_token('REASON', sqlerrm);
576 fnd_msg_pub.add;
577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578 END;
579 -- Debug Message
580
581 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Insertion Done in table DPP_CUSTOMER_CLAIMS_ALL');
582
583 --Assign values to l_claim_lines_tbl for insertion into Log table
584 l_claim_lines_tbl(i).log_mode := 'I'; -- Insert
585 l_claim_lines_tbl(i).transaction_header_id := l_cust_hdr_rec.transaction_header_id;
586 l_claim_lines_tbl(i).customer_inv_line_id := l_cust_inv_line_id;
587 l_claim_lines_tbl(i).line_number := l_line_number;
588 l_claim_lines_tbl(i).last_price := l_customer_tbl(i).customer_price_tbl(j).last_price;
589 l_claim_lines_tbl(i).supplier_new_price := l_supp_new_price;
593 l_claim_lines_tbl(i).last_update_date := l_sysdate;
590 l_claim_lines_tbl(i).calculated_inventory := l_cust_inv_tbl(1).onhand_quantity;
591 l_claim_lines_tbl(i).creation_date := l_sysdate;
592 l_claim_lines_tbl(i).created_by := l_cust_hdr_rec.Last_Updated_By;
594 l_claim_lines_tbl(i).last_updated_by := l_cust_hdr_rec.Last_Updated_By;
595 l_claim_lines_tbl(i).last_update_login := FND_GLOBAL.login_ID;
596 l_claim_lines_tbl(i).inventory_item_id := l_customer_tbl(i).inventory_item_id;
597 l_claim_lines_tbl(i).cust_account_id := l_customer_tbl(i).customer_price_tbl(j).cust_account_id;
598 l_claim_lines_tbl(i).org_id := l_cust_hdr_rec.org_id;
599 END IF; --If cust account id is not null
600 END LOOP; --customer_price_tbl loop
601 ELSE --customer_price_tbl exists
602 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
603 FND_MESSAGE.set_token('ID', 'Line Details');
604 FND_MSG_PUB.add;
605 RAISE FND_API.G_EXC_ERROR;
606 END IF; --customer_price_tbl exists
607 END IF; --inventory_item_id is not null
608 END LOOP; --l_customer_tbl loop
609 -- Call the procedure to insert history record
610 DPP_LOG_PVT.Insert_ClaimsLog(p_api_version => l_api_version
611 ,p_init_msg_list => FND_API.G_FALSE
612 ,p_commit => FND_API.G_FALSE
613 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
614 ,x_return_status => l_return_status
615 ,x_msg_count => l_msg_count
616 ,x_msg_data => l_msg_data
617 ,p_claim_lines_tbl => l_claim_lines_tbl
618 );
619 -- Debug Message
620
621 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Insertion Done in table DPP_CUSTOMER_CLAIMS_LOG');
622 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Return Status from DPP_LOG_PVT.Insert_ClaimsLog: '|| l_return_status);
623
624 END IF; --IF l_profile_option_value = 'Y' OR l_profile_option_value IS NULL THEN
625
626 UPDATE DPP_EXECUTION_DETAILS
627 SET execution_end_date = sysdate
628 ,execution_status = DECODE(l_return_status,'S','SUCCESS','WARNING')
629 ,last_update_date = sysdate
630 ,last_updated_by = l_cust_hdr_rec.Last_Updated_By
631 ,last_update_login = l_cust_hdr_rec.Last_Updated_By
632 ,provider_process_id = l_cust_hdr_rec.Provider_Process_Id
633 ,provider_process_instance_id = l_cust_hdr_rec.Provider_Process_Instance_id
634 ,output_xml = XMLType(l_cust_hdr_rec.Output_XML)
635 ,object_version_number = nvl(object_version_number,0) + 1
636 WHERE execution_detail_id = l_cust_hdr_rec.Execution_Detail_ID;
637 -- Debug Message
638
639 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name||': Exe Detail ID: '||l_cust_hdr_rec.Execution_Detail_ID);
640 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name|| ': '||SQL%ROWCOUNT ||' row(s) updated in DPP_EXECUTION_DETAILS.');
641
642 ELSE
643 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
644 FND_MESSAGE.set_token('ID', 'Line Details');
645 FND_MSG_PUB.add;
646 RAISE FND_API.G_EXC_ERROR;
647 END IF; --l_customer_tbl.EXISTS(1)
648 END IF; --l_cust_hdr_rec.org_id IS NULL
649
650 x_return_status := l_return_status;
651 -- Standard check for p_commit
652 IF FND_API.to_Boolean( p_commit )
653 THEN
654 COMMIT WORK;
655 END IF;
656 -- Debug Message
657
658 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'end');
659
660 -- Standard call to get message count and if count is 1, get message info.
661 FND_MSG_PUB.Count_And_Get
662 (p_count => x_msg_count,
663 p_data => x_msg_data
664 );
665
666 IF x_msg_count > 1 THEN
667 FOR I IN 1..x_msg_count LOOP
668 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
669 END LOOP;
670 END IF;
671
672 --Exception Handling
673 EXCEPTION
674 WHEN DPP_UTILITY_PVT.resource_locked THEN
675 ROLLBACK TO Populate_CustomerPrice_PVT;
676 x_return_status := FND_API.g_ret_sts_error;
677 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
678 FND_MSG_PUB.Count_And_Get (
679 p_encoded => FND_API.G_FALSE,
680 p_count => x_msg_count,
681 p_data => x_msg_data
682 );
683 IF x_msg_count > 1 THEN
684 FOR I IN 1..x_msg_count LOOP
685 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
686 END LOOP;
687 END IF;
688
689 WHEN FND_API.G_EXC_ERROR THEN
690 ROLLBACK TO Populate_CustomerPrice_PVT;
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 -- Standard call to get message count and if count=1, get the message
693 FND_MSG_PUB.Count_And_Get (
694 p_encoded => FND_API.G_FALSE,
695 p_count => x_msg_count,
696 p_data => x_msg_data
697 );
698 IF x_msg_count > 1 THEN
699 FOR I IN 1..x_msg_count LOOP
700 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
701 END LOOP;
702 END IF;
703
704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
705 ROLLBACK TO Populate_CustomerPrice_PVT;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 -- Standard call to get message count and if count=1, get the message
708 FND_MSG_PUB.Count_And_Get (
709 p_encoded => FND_API.G_FALSE,
710 p_count => x_msg_count,
711 p_data => x_msg_data
712 );
713 IF x_msg_count > 1 THEN
714 FOR I IN 1..x_msg_count LOOP
715 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
716 END LOOP;
717 END IF;
718
719 WHEN OTHERS THEN
720 ROLLBACK TO Populate_CustomerPrice_PVT;
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
723 fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
724 fnd_message.set_token('ERRNO', sqlcode);
725 fnd_message.set_token('REASON', sqlerrm);
726 FND_MSG_PUB.ADD;
727 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in inserting into DPP_CUSTOMER_CLAIMS_ALL: '||SQLERRM);
728
729 -- Standard call to get message count and if count=1, get the message
730 FND_MSG_PUB.Count_And_Get (
731 p_encoded => FND_API.G_FALSE,
732 p_count => x_msg_count,
733 p_data => x_msg_data
734 );
735 IF x_msg_count > 1 THEN
736 FOR I IN 1..x_msg_count LOOP
737 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
738 END LOOP;
739 END IF;
740
741
742 END Populate_CustomerPrice;
743
744 END DPP_CUSTOMERCLAIMS_PVT;