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