[Home] [Help]
PACKAGE BODY: APPS.DPP_COVEREDINVENTORY_PVT
Source
1 PACKAGE BODY DPP_COVEREDINVENTORY_PVT AS
2 /* $Header: dppvcovb.pls 120.16.12010000.5 2010/04/26 07:08:11 pvaramba ship $ */
3
4 -- Package name : DPP_COVEREDINVENTORY_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :Contains Procedures - Select Covered Inventory from INV, Populate Covered Inventory in DPP
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_COVEREDINVENTORY_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) := 'dppvcovb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- Select_CoveredInventory
17 --
18 -- PURPOSE
19 -- Select Covered Inventory
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
27
28 PROCEDURE Select_CoveredInventory
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_Inv_hdr_rec IN DPP_INV_HDR_REC_TYPE,
37 p_Covered_Inv_Tbl IN OUT NOCOPY DPP_INV_COV_TBL_TYPE)
38 IS
39 l_api_name CONSTANT VARCHAR2(30) := 'Select_CoveredInventory';
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
42
43 l_return_status VARCHAR2(30);
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(4000);
46
47 l_hdr_rec dpp_inv_hdr_rec_type:= p_inv_hdr_rec;
48 l_covered_inv_tbl dpp_inv_cov_tbl_type := p_covered_inv_tbl;
49 l_covered_inv_wh_tbl dpp_inv_cov_wh_tbl_type;
50 l_covered_inv_rct_tbl dpp_inv_cov_rct_tbl_type;
51 l_num_count NUMBER;
52 l_primary_uom_code VARCHAR2(3);
53
54 CURSOR get_covered_inventory_csr (p_org_id IN NUMBER,
55 p_effective_start_date DATE,
56 p_effective_end_date DATE,
57 p_inventory_item_id IN NUMBER)
58 IS
59 SELECT sum(case when ( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date
60 AND NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date))
61 --BETWEEN p_effective_start_date and p_effective_end_date)
62 then moqd.transaction_quantity else 0 end) covered_qty,
63 sum(moqd.transaction_quantity) onhand_qty,
64 moqd.transaction_uom_code
65 FROM mtl_onhand_quantities_detail moqd,
66 org_organization_definitions ood,
67 mtl_parameters mp
68 WHERE moqd.organization_id = ood.organization_id
69 AND moqd.inventory_item_id = p_inventory_item_id
70 AND mp.organization_id = ood.organization_id
71 AND NVL(ood.disable_date,SYSDATE + 1) > SYSDATE
72 AND ood.operating_unit = p_org_id
73 AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
74 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
75 AND moqd.PLANNING_TP_TYPE = 2
76 AND moqd.OWNING_TP_TYPE = 2
77 AND moqd.IS_CONSIGNED = 2
78 GROUP BY moqd.transaction_uom_code;
79
80 CURSOR get_covered_inv_wh_csr(p_org_id IN NUMBER,
81 p_effective_start_date DATE,
82 p_effective_end_date DATE,
83 p_inventory_item_id IN NUMBER)
84 IS
85 SELECT
86 SUM(moqd.transaction_quantity) sum,
87 ood.organization_name warehouse,
88 ood.organization_id warehouse_id
89 FROM
90 mtl_onhand_quantities_detail moqd,
91 org_organization_definitions ood,
92 mtl_parameters mp
93 WHERE moqd.organization_id = ood.organization_id
94 AND moqd.inventory_item_id = p_inventory_item_id
95 AND ood.operating_unit = p_org_id
96 AND mp.organization_id = ood.organization_id
97 AND NVL(ood.disable_date,SYSDATE + 1) > SYSDATE
98 AND (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date
99 AND NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date)
100 --BETWEEN p_effective_start_date and p_effective_end_date
101 AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
102 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
103 AND moqd.PLANNING_TP_TYPE = 2
104 AND moqd.OWNING_TP_TYPE = 2
105 AND moqd.IS_CONSIGNED = 2
106 GROUP BY ood.organization_name,ood.organization_id;
107
108 cursor get_covered_inv_rct_csr(p_org_id IN NUMBER, p_inventory_item_id IN NUMBER, p_warehouse_id IN NUMBER) is
109 SELECT
110 (NVL(moqd.orig_date_received,moqd.date_received)) date_received,
111 SUM(moqd.transaction_quantity) sum
112 FROM
113 mtl_onhand_quantities_detail moqd,
114 org_organization_definitions ood,
115 mtl_parameters mp
116 WHERE
117 moqd.organization_id = ood.organization_id AND
118 moqd.inventory_item_id = p_inventory_item_id AND
119 ood.operating_unit = p_org_id AND
120 mp.organization_id = ood.organization_id AND
121 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
122 moqd.organization_id = p_warehouse_id AND
123 moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
124 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
125 AND moqd.PLANNING_TP_TYPE = 2
126 AND moqd.OWNING_TP_TYPE = 2
127 AND moqd.IS_CONSIGNED = 2
128 GROUP BY (NVL(moqd.orig_date_received,moqd.date_received));
129
130 BEGIN
131
132 -- Standard begin of API savepoint
133 SAVEPOINT Select_CoveredInventory_PVT;
134 -- Standard call to check for call compatibility.
135 IF NOT FND_API.Compatible_API_Call ( l_api_version,
136 p_api_version,
137 l_api_name,
138 G_PKG_NAME)
139 THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END IF;
142 -- Initialize message list if p_init_msg_list is set to TRUE.
143 IF FND_API.to_Boolean( p_init_msg_list )
144 THEN
145 FND_MSG_PUB.initialize;
146 END IF;
147
148 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, 'dpp.plsql.' || L_FULL_NAME, 'Private API: ' || l_api_name || 'start');
149
150 -- Initialize API return status to sucess
151 l_return_status := FND_API.G_RET_STS_SUCCESS;
152 --
153 -- API body
154 --
155 IF l_hdr_rec.org_id IS NULL THEN
156 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
157 FND_MESSAGE.set_token('ID', 'Org ID');
158 FND_MSG_PUB.add;
159 RAISE FND_API.G_EXC_ERROR;
160 ELSIF l_hdr_rec.effective_start_date IS NULL THEN
161 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
162 FND_MESSAGE.set_token('ID', 'Effective Start Date');
163 FND_MSG_PUB.add;
164 RAISE FND_API.G_EXC_ERROR;
165 ELSIF l_hdr_rec.effective_end_date IS NULL THEN
166 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
167 FND_MESSAGE.set_token('ID', 'Effective End Date');
168 FND_MSG_PUB.add;
169 RAISE FND_API.G_EXC_ERROR;
170 ELSE
171 FOR i IN l_covered_inv_tbl.FIRST..l_covered_inv_tbl.LAST LOOP
172 IF l_covered_inv_tbl(i).Transaction_Line_Id IS NULL THEN
173 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
174 FND_MESSAGE.set_token('ID', 'Transaction Line ID');
175 FND_MSG_PUB.add;
176 RAISE FND_API.G_EXC_ERROR;
177 ELSIF l_covered_inv_tbl(i).inventory_item_id IS NULL THEN
178 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
179 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
180 FND_MSG_PUB.add;
181 RAISE FND_API.G_EXC_ERROR;
182 ELSE
183 FOR get_covered_inventory_rec IN get_covered_inventory_csr(l_hdr_rec.org_id, l_hdr_rec.effective_start_date, l_hdr_rec.effective_end_date, l_covered_inv_tbl(i).Inventory_ITem_ID)
184 LOOP
185 l_covered_inv_tbl(i).covered_quantity := NVL(get_covered_inventory_rec.covered_qty,0);
186 l_covered_inv_tbl(i).onhand_quantity := NVL(get_covered_inventory_rec.onhand_qty,0);
187 l_covered_inv_tbl(i).uom_code := get_covered_inventory_rec.transaction_uom_code;
188 --IF covered inventory is negative then reassign it to 0
189 IF l_covered_inv_tbl(i).covered_quantity < 0 THEN
190 l_covered_inv_tbl(i).covered_quantity := 0;
191 END IF;
192 l_num_count := 0;
193 --Bug 7157230
194 l_covered_inv_wh_tbl.delete;
195 --Get the ware house level details only if the covered quantiy > 0
196 IF l_covered_inv_tbl(i).covered_quantity > 0 THEN
197 FOR get_covered_inv_wh_rec IN get_covered_inv_wh_csr(l_hdr_rec.org_id, l_hdr_rec.effective_start_date, l_hdr_rec.effective_end_date, l_covered_inv_tbl(i).Inventory_ITem_ID)
198 LOOP
199 l_num_count := l_num_count + 1;
200 l_covered_inv_wh_tbl(l_num_count).warehouse_name := get_covered_inv_wh_rec.warehouse;
201 l_covered_inv_wh_tbl(l_num_count).warehouse_id := get_covered_inv_wh_rec.warehouse_id;
202 l_covered_inv_wh_tbl(l_num_count).covered_quantity := NVL(get_covered_inv_wh_rec.sum,0);
203 OPEN get_covered_inv_rct_csr(l_hdr_rec.org_id,
204 l_covered_inv_tbl(i).Inventory_ITem_ID,
205 get_covered_inv_wh_rec.warehouse_id);
206 LOOP
207 FETCH get_covered_inv_rct_csr BULK COLLECT INTO l_covered_inv_rct_tbl;
208 EXIT WHEN get_covered_inv_rct_csr%NOTFOUND;
209 END LOOP;
210 CLOSE get_covered_inv_rct_csr;
211 l_covered_inv_wh_tbl(l_num_count).rct_line_tbl := l_covered_inv_rct_tbl;
212 END LOOP;
213 END IF; --ware house level details only if the covered quantiy > 0
214 l_covered_inv_tbl(i).wh_line_tbl := l_covered_inv_wh_tbl;
215 END LOOP;
216 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'On Hand Quantity: '||l_covered_inv_tbl(i).onhand_quantity);
217 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Covered Quantity: '||l_covered_inv_tbl(i).covered_quantity);
218 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'inventory_item_id: '||l_covered_inv_tbl(i).inventory_item_id);
219
220 IF l_covered_inv_tbl(i).onhand_quantity IS NULL THEN
221 l_covered_inv_tbl(i).covered_quantity := 0;
222 l_covered_inv_tbl(i).onhand_quantity := 0;
223 BEGIN
224 SELECT primary_uom_code
225 INTO l_primary_uom_code
226 FROM mtl_system_items msi,
227 mtl_parameters mp
228 WHERE inventory_item_id = l_covered_inv_tbl(i).inventory_item_id
229 AND mp.organization_id = msi.organization_id
230 AND mp.organization_id = mp.master_organization_id
231 AND rownum = 1;
232 EXCEPTION
233 WHEN OTHERS THEN
234 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, 'Error in fetching primary UOM: ' || SQLERRM);
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 END;
237 l_covered_inv_tbl(i).uom_code := l_primary_uom_code; -- Default to Primary UOM
238 END IF; -- onhand qty null if
239 END IF; -- txn line id null if
240 END LOOP;
241 END IF;
242 p_covered_inv_tbl := l_covered_inv_tbl;
243 x_return_status := l_return_status;
244 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'select_coveredinventory(): x_return_status: ' || x_return_status);
245
246 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END IF;
251
252 -- Standard check for p_commit
253 IF FND_API.to_Boolean( p_commit )
254 THEN
255 COMMIT WORK;
256 END IF;
257
258 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Private API: ' || l_api_name || 'end');
259
260 -- Standard call to get message count and if count is 1, get message info.
261 FND_MSG_PUB.Count_And_Get
262 (p_count => x_msg_count,
263 p_data => x_msg_data
264 );
265
266 --Exception Handling
267 EXCEPTION
268
269 WHEN FND_API.G_EXC_ERROR THEN
270 ROLLBACK TO Select_CoveredInventory_PVT;
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 -- Standard call to get message count and if count=1, get the message
273 FND_MSG_PUB.Count_And_Get (
274 p_encoded => FND_API.G_FALSE,
275 p_count => x_msg_count,
276 p_data => x_msg_data
277 );
278
279 IF x_msg_count > 1 THEN
280 FOR I IN 1..x_msg_count LOOP
281 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
282 END LOOP;
283 END IF;
284
285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
286 ROLLBACK TO Select_CoveredInventory_PVT;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 -- Standard call to get message count and if count=1, get the message
289 FND_MSG_PUB.Count_And_Get (
290 p_encoded => FND_API.G_FALSE,
291 p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294 IF x_msg_count > 1 THEN
295 FOR I IN 1..x_msg_count LOOP
296 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
297 END LOOP;
298 END IF;
299
300 WHEN OTHERS THEN
301 ROLLBACK TO Select_CoveredInventory_PVT;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
304 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Select_CoveredInventory');
305 fnd_message.set_token('ERRNO', sqlcode);
306 fnd_message.set_token('REASON', sqlerrm);
307 FND_MSG_PUB.add;
308 -- Standard call to get message count and if count=1, get the message
309 FND_MSG_PUB.Count_And_Get (
310 p_encoded => FND_API.G_FALSE,
311 p_count => x_msg_count,
312 p_data => x_msg_data
313 );
314 IF x_msg_count > 1 THEN
315 FOR I IN 1..x_msg_count LOOP
316 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
317 END LOOP;
318 END IF;
319
320
321 END Select_CoveredInventory;
322
323
324 ---------------------------------------------------------------------
325 -- PROCEDURE
326 -- Populate_CoveredInventory
327 --
328 -- PURPOSE
329 -- Populate Covered Inventory
330 --
331 -- PARAMETERS
332 --
333 -- NOTES
334 -- 1.
335 -- 2.
336 ----------------------------------------------------------------------
337
338 PROCEDURE Populate_CoveredInventory(
339 p_api_version IN NUMBER
340 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
341 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
342 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
343 ,x_return_status OUT NOCOPY VARCHAR2
344 ,x_msg_count OUT NOCOPY NUMBER
345 ,x_msg_data OUT NOCOPY VARCHAR2
346 ,p_inv_hdr_rec IN dpp_inv_hdr_rec_type
347 ,p_covered_inv_tbl IN dpp_inv_cov_tbl_type
348 )
349 IS
350 l_api_name CONSTANT VARCHAR2(30) := 'Populate_CoveredInventory';
351 l_api_version CONSTANT NUMBER := 1.0;
352 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
353
354 l_return_status VARCHAR2(30);
355 l_msg_count NUMBER;
356 l_msg_data VARCHAR2(4000);
357
358 l_inv_hdr_rec DPP_COVEREDINVENTORY_PVT.dpp_inv_hdr_rec_type := p_inv_hdr_rec;
359 l_covered_inv_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_tbl_type := p_covered_inv_tbl;
360 l_covered_inv_wh_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_wh_tbl_type;
361 l_covered_inv_rct_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_rct_tbl_type;
362
363 l_inv_details_id NUMBER;
364 l_include_flag VARCHAR2(1);
365 l_days_out NUMBER;
366
367 BEGIN
368 ------------------------------------------
369 -- Initialization
370 ------------------------------------------
371
372 -- Standard begin of API savepoint
373 SAVEPOINT Populate_CoveredInventory_PVT;
374 -- Standard call to check for call compatibility.
375 IF NOT FND_API.Compatible_API_Call ( l_api_version,
376 p_api_version,
377 l_api_name,
378 G_PKG_NAME)
379 THEN
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END IF;
382 -- Initialize message list if p_init_msg_list is set to TRUE.
383 IF FND_API.to_Boolean( p_init_msg_list )
384 THEN
385 FND_MSG_PUB.initialize;
386 END IF;
387
388
389 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, 'dpp.plsql.' || L_FULL_NAME, 'Private API: ' || l_api_name || 'start');
390
391 -- Initialize API return status to sucess
392 l_return_status := FND_API.G_RET_STS_SUCCESS;
393 --
394 -- API body
395 --
396 BEGIN
397 UPDATE DPP_EXECUTION_DETAILS
398 SET execution_end_date = sysdate
399 ,execution_status = DECODE(l_return_status,FND_API.G_RET_STS_SUCCESS,'SUCCESS','WARNING')
400 ,last_update_date = sysdate
401 ,last_updated_by = l_inv_hdr_rec.Last_Updated_By
402 ,last_update_login = l_inv_hdr_rec.Last_Updated_By
403 ,provider_process_id = l_inv_hdr_rec.Provider_Process_Id
404 ,provider_process_instance_id = l_inv_hdr_rec.Provider_Process_Instance_id
405 ,output_xml = XMLTYPE(l_inv_hdr_rec.Output_XML)
406 WHERE execution_detail_id = l_inv_hdr_rec.Execution_Detail_ID;
407
408 IF SQL%ROWCOUNT = 0 THEN
409
410 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Invalid value for Execution Detail ID: ' || l_inv_hdr_rec.Execution_Detail_ID);
411 RAISE FND_API.G_EXC_ERROR;
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, substr(('Error in Updating DPP_EXECUTION_DETAILS: ' || SQLERRM || ' from Populate Covered Inventory API'),1,4000));
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END ;
419
420 DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory(
421 p_api_version => l_api_version
422 ,p_init_msg_list => FND_API.G_FALSE
423 ,p_commit => FND_API.G_FALSE
424 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
425 ,x_return_status => l_return_status
426 ,x_msg_count => l_msg_count
427 ,x_msg_data => l_msg_data
428 ,p_inv_hdr_rec => l_inv_hdr_rec
429 ,p_covered_inv_tbl => l_covered_inv_tbl
430 );
431
432 x_return_status := l_return_status;
433
434 -- Standard check for p_commit
435 IF FND_API.to_Boolean( p_commit )
436 THEN
437 COMMIT WORK;
438 END IF;
439
440 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Private API: ' || l_api_name || 'end');
441 -- Standard call to get message count and if count is 1, get message info.
442 FND_MSG_PUB.Count_And_Get
443 (p_count => x_msg_count,
444 p_data => x_msg_data
445 );
446
447 --Exception Handling
448 EXCEPTION
449 WHEN DPP_UTILITY_PVT.resource_locked THEN
450 ROLLBACK TO Populate_CoveredInventory_PVT;
451 x_return_status := FND_API.g_ret_sts_error;
452 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
453 FND_MSG_PUB.Count_And_Get (
454 p_encoded => FND_API.G_FALSE,
455 p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458 IF x_msg_count > 1 THEN
459 FOR I IN 1..x_msg_count LOOP
460 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
461 END LOOP;
462 END IF;
463
464 WHEN FND_API.G_EXC_ERROR THEN
465 ROLLBACK TO Populate_CoveredInventory_PVT;
466 x_return_status := FND_API.G_RET_STS_ERROR;
467 -- Standard call to get message count and if count=1, get the message
468 FND_MSG_PUB.Count_And_Get (
469 p_encoded => FND_API.G_FALSE,
470 p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473 IF x_msg_count > 1 THEN
474 FOR I IN 1..x_msg_count LOOP
475 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
476 END LOOP;
477 END IF;
478
479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
480 ROLLBACK TO Populate_CoveredInventory_PVT;
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482 -- Standard call to get message count and if count=1, get the message
483 FND_MSG_PUB.Count_And_Get (
484 p_encoded => FND_API.G_FALSE,
485 p_count => x_msg_count,
486 p_data => x_msg_data
487 );
488 IF x_msg_count > 1 THEN
489 FOR I IN 1..x_msg_count LOOP
490 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
491 END LOOP;
492 END IF;
493
494 WHEN OTHERS THEN
495 ROLLBACK TO Populate_CoveredInventory_PVT;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
498 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Populate_CoveredInventory');
499 fnd_message.set_token('ERRNO', sqlcode);
500 fnd_message.set_token('REASON', sqlerrm);
501 FND_MSG_PUB.add;
502 -- Standard call to get message count and if count=1, get the message
503 FND_MSG_PUB.Count_And_Get (
504 p_encoded => FND_API.G_FALSE,
505 p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508 IF x_msg_count > 1 THEN
509 FOR I IN 1..x_msg_count LOOP
510 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
511 END LOOP;
512 END IF;
513
514 END Populate_CoveredInventory;
515
516
517 PROCEDURE Update_CoveredInventory(
518 p_api_version IN NUMBER
519 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
520 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
521 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
522 ,x_return_status OUT NOCOPY VARCHAR2
523 ,x_msg_count OUT NOCOPY NUMBER
524 ,x_msg_data OUT NOCOPY VARCHAR2
525 ,p_inv_hdr_rec IN dpp_inv_hdr_rec_type
526 ,p_covered_inv_tbl IN dpp_inv_cov_tbl_type
527 )
528 IS
529 l_api_name CONSTANT VARCHAR2(30) := 'Update_CoveredInventory';
530 l_api_version CONSTANT NUMBER := 1.0;
531 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
532
533 l_return_status VARCHAR2(30);
534 l_msg_count NUMBER;
535 l_msg_data VARCHAR2(4000);
536
537 l_inv_hdr_rec DPP_COVEREDINVENTORY_PVT.dpp_inv_hdr_rec_type := p_inv_hdr_rec;
538 l_covered_inv_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_tbl_type := p_covered_inv_tbl;
539 l_covered_inv_wh_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_wh_tbl_type;
540 l_covered_inv_rct_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_rct_tbl_type;
541 l_inv_details_id NUMBER;
542 l_txn_lines_tbl DPP_LOG_PVT.dpp_txn_line_tbl_type;
543 l_include_flag VARCHAR2(1);
544 l_flag VARCHAR2(1);
545 l_days_out NUMBER;
546 l_sysdate DATE := SYSDATE;
547
548 l_price_change_flag VARCHAR2(20);
549 l_user_id NUMBER :=FND_PROFILE.VALUE('USER_ID');
550
551 TYPE inventory_details_id_tbl IS TABLE OF dpp_inventory_details_all.inventory_details_id%TYPE
552 INDEX BY PLS_INTEGER;
553
554 inventory_details_ids inventory_details_id_tbl;
555
556 BEGIN
557 -- Standard begin of API savepoint
558 SAVEPOINT Update_CoveredInventory_PVT;
559 -- Standard call to check for call compatibility.
560 IF NOT FND_API.Compatible_API_Call ( l_api_version,
561 p_api_version,
562 l_api_name,
563 G_PKG_NAME)
564 THEN
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566 END IF;
567 -- Initialize message list if p_init_msg_list is set to TRUE.
568 IF FND_API.to_Boolean( p_init_msg_list )
569 THEN
570 FND_MSG_PUB.initialize;
571 END IF;
572
573 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, 'dpp.plsql.' || L_FULL_NAME, 'Private API: ' || l_api_name || 'start');
574
575 -- Initialize API return status to sucess
576 l_return_status := FND_API.G_RET_STS_SUCCESS;
577 --
578 -- API body
579 --
580 --Check whether the last updated by value is passed
581 IF l_inv_hdr_rec.Last_Updated_By IS NULL THEN
582 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
583 FND_MESSAGE.set_token('ID', 'Last Updated By');
584 FND_MSG_PUB.add;
585 RAISE FND_API.G_EXC_ERROR;
586 ELSIF l_inv_hdr_rec.effective_start_date IS NULL THEN
587 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
588 FND_MESSAGE.set_token('ID', 'Effective Start Date');
589 FND_MSG_PUB.add;
590 RAISE FND_API.G_EXC_ERROR;
591 ELSIF l_inv_hdr_rec.effective_end_date IS NULL THEN
592 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
593 FND_MESSAGE.set_token('ID', 'Effective End Date');
594 FND_MSG_PUB.add;
595 RAISE FND_API.G_EXC_ERROR;
596 END IF;
597
598 FOR i IN l_covered_inv_tbl.FIRST..l_covered_inv_tbl.LAST LOOP
599 IF l_covered_inv_tbl(i).Transaction_Line_Id IS NULL THEN
600 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
601 FND_MESSAGE.set_token('ID', 'Transaction Line ID');
602 FND_MSG_PUB.add;
603 RAISE FND_API.G_EXC_ERROR;
604 ELSIF
605 l_covered_inv_tbl(i).inventory_item_id IS NULL THEN
606 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
607 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
608 FND_MSG_PUB.add;
609 RAISE FND_API.G_EXC_ERROR;
610 ELSIF
611 l_covered_inv_tbl(i).UOM_Code IS NULL THEN
612 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
613 FND_MESSAGE.set_token('ID', 'UOM Code');
614 FND_MSG_PUB.add;
615 RAISE FND_API.G_EXC_ERROR;
616 ELSE
617 BEGIN
618 UPDATE DPP_TRANSACTION_LINES_ALL
619 SET covered_inventory = NVL(l_covered_inv_tbl(i).Covered_quantity,0),
620 approved_inventory = NVL(l_covered_inv_tbl(i).Covered_quantity,0),
621 onhand_inventory = NVL(l_covered_inv_tbl(i).Onhand_Quantity,0),
622 UOM = l_covered_inv_tbl(i).UOM_Code,
623 last_update_date = l_sysdate,
624 last_updated_by = l_inv_hdr_rec.Last_Updated_By,
625 last_calculated_by = l_inv_hdr_rec.Last_Updated_By,
626 last_update_login = FND_GLOBAL.LOGIN_ID,
627 last_calculated_date = l_sysdate
628 WHERE transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
629 IF SQL%ROWCOUNT = 0 THEN
630
631 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Invalid value for Transaction Line ID: ' || l_covered_inv_tbl(i).Transaction_Line_Id);
632
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635 EXCEPTION
636 WHEN OTHERS THEN
637 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, substr(('Error in Updating DPP_TRANSACTION_LINES_ALL: ' || SQLERRM || ' from Update Covered Inventory API'),1,4000));
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END ;
640
641 --Get the supplier trade profile value to include price increase value for claim or not
642 BEGIN
643 SELECT nvl(create_claim_price_increase,'N')
644 INTO l_price_change_flag
645 FROM ozf_supp_trd_prfls_all ostp,
646 dpp_transaction_headers_all dtha
647 WHERE ostp.supplier_id = to_number(dtha.vendor_id)
648 AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
649 AND ostp.org_id = to_number(dtha.org_id)
650 AND dtha.transaction_header_id = l_inv_hdr_rec.transaction_header_id;
651 EXCEPTION
652 WHEN NO_DATA_FOUND THEN
653 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
654 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
655 fnd_message.set_token('ERRNO', sqlcode);
656 fnd_message.set_token('REASON', 'SUPPLIER TRADE PROFILE IS NOT FOUND'); --To be modified
657 FND_MSG_PUB.add;
658 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
659 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
660 END IF;
661 RAISE FND_API.g_exc_error;
662 WHEN OTHERS THEN
663 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
664 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
665 fnd_message.set_token('ERRNO', sqlcode);
666 fnd_message.set_token('REASON', sqlerrm);
667 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
668 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_EXE_DET_ID'); --To be modified
669 fnd_message.set_token('SEQ_NAME', 'DPP_EXECUTION_DETAIL_ID_SEQ'); --To be modified
670 FND_MSG_PUB.add;
671 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
672 END IF;
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END;
675
676 IF (l_price_change_flag = 'N') THEN -- Only Price Decrease
677 UPDATE dpp_transaction_lines_all dtla
678 SET dtla.claim_amount = dtla.approved_inventory * price_change,
679 dtla.object_version_number = dtla.object_version_number +1,
680 dtla.last_updated_by = nvl(l_user_id,0),
681 dtla.last_update_login = nvl(l_user_id,0),
682 dtla.last_update_date = sysdate
683 WHERE dtla.transaction_header_id = l_inv_hdr_rec.transaction_header_id
684 AND dtla.transaction_line_id = l_covered_inv_tbl(i).transaction_line_id
685 AND dtla.price_change > 0;
686 ELSE -- Both Price Increase and Price Decrease
687 UPDATE dpp_transaction_lines_all dtla
688 SET dtla.claim_amount = dtla.approved_inventory * price_change,
689 dtla.object_version_number = dtla.object_version_number +1,
690 dtla.last_updated_by = nvl(l_user_id,0),
691 dtla.last_update_login = nvl(l_user_id,0),
692 dtla.last_update_date = sysdate
693 WHERE dtla.transaction_header_id = l_inv_hdr_rec.transaction_header_id
694 AND dtla.transaction_line_id = l_covered_inv_tbl(i).transaction_line_id
695 AND dtla.price_change <> 0;
696 END IF;
697
698 END IF;
699 -- Assign values to l_txn_lines_tbl for History
700 l_txn_lines_tbl(i).log_mode := 'U';
701 l_txn_lines_tbl(i).transaction_header_id := l_inv_hdr_rec.transaction_header_id;
702 l_txn_lines_tbl(i).transaction_line_id := l_covered_inv_tbl(i).Transaction_Line_Id;
703 l_txn_lines_tbl(i).covered_inventory := NVL(l_covered_inv_tbl(i).Covered_quantity,0);
704 l_txn_lines_tbl(i).org_id := l_inv_hdr_rec.org_id;
705 l_txn_lines_tbl(i).last_update_date := l_sysdate;
706 l_txn_lines_tbl(i).last_updated_by := l_inv_hdr_rec.Last_Updated_By;
707 l_txn_lines_tbl(i).creation_date := l_sysdate;
708 l_txn_lines_tbl(i).created_by := l_inv_hdr_rec.Last_Updated_By;
709 l_txn_lines_tbl(i).last_update_login := FND_GLOBAL.LOGIN_ID;
710 l_txn_lines_tbl(i).inventory_item_id := l_covered_inv_tbl(i).inventory_item_id;
711 l_txn_lines_tbl(i).last_calculated_by := l_inv_hdr_rec.Last_Updated_By;
712 l_txn_lines_tbl(i).last_calculated_date := l_sysdate;
713 l_txn_lines_tbl(i).onhand_inventory := NVL(l_covered_inv_tbl(i).Onhand_Quantity,0);
714
715 BEGIN
716
717 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Org Id: ' || l_inv_hdr_rec.org_id || 'transaction_line_id ' || l_covered_inv_tbl(i).Transaction_Line_Id);
718
719 -- Delete existing rows in DPP_INVENTORY_DETAILS_ADJ_ALL (if any)
720 SELECT
721 inventory_details_id
722 BULK COLLECT INTO
723 inventory_details_ids
724 FROM
725 dpp_inventory_details_all
726 WHERE
727 org_id = l_inv_hdr_rec.org_id
728 AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
729
730 FORALL indx IN inventory_details_ids.FIRST .. inventory_details_ids .LAST
731 DELETE
732 FROM DPP_INVENTORY_DETAILS_ADJ_ALL
733 WHERE INVENTORY_DETAILS_ID = inventory_details_ids(indx);
734
735 -- Delete existing rows in DPP_INVENTORY_DETAILS_ALL (if any)
736 DELETE
737 FROM DPP_INVENTORY_DETAILS_ALL
738 WHERE org_id = l_inv_hdr_rec.org_id
739 AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
740
741 EXCEPTION
742 WHEN NO_DATA_FOUND THEN
743 NULL;
744 WHEN OTHERS THEN
745 IF g_debug THEN
746 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
747 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory - Delete rows');
748 fnd_message.set_token('ERRNO', sqlcode);
749 fnd_message.set_token('REASON', sqlerrm);
750 FND_MSG_PUB.add;
751 END IF;
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 END;
754 --Insert child records if covered quantity <> 0
755 IF NVL(l_covered_inv_tbl(i).Covered_quantity,0) <> 0 THEN
756 FOR j IN l_covered_inv_tbl(i).wh_line_tbl.FIRST..l_covered_inv_tbl(i).wh_line_tbl.LAST LOOP
757 SELECT DPP_INVENTORY_DETAILS_SEQ.nextval
758 INTO l_inv_details_id
759 FROM DUAL;
760 l_flag := 'N';
761 INSERT INTO DPP_INVENTORY_DETAILS_ALL(
762 inventory_details_id,
763 transaction_line_id,
764 quantity,
765 uom,
766 include_flag,
767 creation_date,
768 created_by,
769 last_update_date,
770 last_updated_by,
771 last_update_login,
772 inventory_item_id,
773 org_id,
774 organization_id,
775 object_version_number)
776 VALUES( l_inv_details_id,
777 l_covered_inv_tbl(i).Transaction_Line_Id,
778 NVL(l_covered_inv_tbl(i).wh_line_tbl(j).Covered_quantity,0),
779 l_covered_inv_tbl(i).UOM_Code,
780 'N',
781 l_sysdate,
782 l_inv_hdr_rec.Last_Updated_By,
783 l_sysdate,
784 l_inv_hdr_rec.Last_Updated_By,
785 l_inv_hdr_rec.Last_Updated_By,
786 l_covered_inv_tbl(i).inventory_item_id,
787 l_inv_hdr_rec.org_id,
788 l_covered_inv_tbl(i).wh_line_tbl(j).Warehouse_id,
789 1);
790
791 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Inventory Details ID: '||l_inv_details_id);
792
793 FOR k IN l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl.FIRST..l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl.LAST LOOP
794 BEGIN
795 IF ((l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received >= l_inv_hdr_rec.effective_start_date)
796 AND (l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received < l_inv_hdr_rec.effective_end_date)) THEN
797 l_include_flag := 'Y';
798 l_flag := 'Y';
799 l_days_out := 0;
800 ELSIF (l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received < l_inv_hdr_rec.effective_start_date) THEN
801 l_include_flag := 'N';
802 l_days_out := -(l_inv_hdr_rec.effective_start_date - l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received);
803 l_days_out := floor(l_days_out);
804 ELSIF (l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received >= l_inv_hdr_rec.effective_end_date ) THEN
805 l_include_flag := 'N';
806 l_days_out := l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received - l_inv_hdr_rec.effective_end_date;
807 l_days_out := ceil(l_days_out);
808 IF l_days_out = 0 THEN
809 l_days_out := 1;
810 END IF;
811 END IF;
812 END;
813
814 INSERT INTO DPP_INVENTORY_DETAILS_ADJ_ALL(
815 inv_details_adj_id,
816 inventory_details_id,
817 date_received,
818 days_out,
819 quantity,
820 uom,
821 comments,
822 include_flag,
823 creation_date,
824 created_by,
825 last_update_date,
826 last_updated_by,
827 last_update_login,
828 org_id,
829 object_version_number)
830 VALUES(dpp_inv_details_adj_id_seq.nextval,
831 l_inv_details_id,
832 l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received,
833 l_days_out,
834 NVL(l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).Onhand_quantity,0),
835 l_covered_inv_tbl(i).UOM_Code,
836 null,
837 l_include_flag,
838 l_sysdate,
839 l_inv_hdr_rec.Last_Updated_By,
840 l_sysdate,
841 l_inv_hdr_rec.Last_Updated_By,
842 l_inv_hdr_rec.Last_Updated_By,
843 l_inv_hdr_rec.org_id,
844 1
845 );
846
847 END LOOP; --l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl.FIRST..
848 IF l_flag = 'Y' THEN
849 UPDATE DPP_INVENTORY_DETAILS_ALL
850 SET include_flag = 'Y',
851 object_version_number = object_version_number + 1,
852 last_update_date = l_sysdate,
853 last_updated_by = l_inv_hdr_rec.Last_Updated_By,
854 last_update_login = l_inv_hdr_rec.Last_Updated_By
855 WHERE inventory_details_id = l_inv_details_id;
856 END IF;
857 END LOOP; --l_covered_inv_tbl(i).wh_line_tbl.FIRST
858 END IF; -- qty > 0
859
860 END LOOP;
861
862 --For insertion into Log table
863 DPP_LOG_PVT.Insert_LinesLog(p_api_version => 1.0
864 ,p_init_msg_list => FND_API.G_FALSE
865 ,p_commit => FND_API.G_FALSE
866 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
867 ,x_return_status => l_return_status
868 ,x_msg_count => l_msg_count
869 ,x_msg_data => l_msg_data
870 ,p_txn_lines_tbl => l_txn_lines_tbl
871 );
872
873 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'return status for Insert_LinesLog =>'||l_return_status);
874 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
875 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, substr(('Message dat for the DPP Insert_LinesLog API =>'||l_msg_data),1,4000));
876 END IF;
877
878
879 x_return_status := l_return_status;
880 -- Standard check for p_commit
881 IF FND_API.to_Boolean(p_commit) THEN
882 COMMIT WORK;
883 END IF;
884
885 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Private API: ' || l_api_name || 'end');
886
887 -- Standard call to get message count and if count is 1, get message info.
888 FND_MSG_PUB.Count_And_Get
889 (p_count => x_msg_count,
890 p_data => x_msg_data
891 );
892
893 --Exception Handling
894 EXCEPTION
895 WHEN DPP_UTILITY_PVT.resource_locked THEN
896 ROLLBACK TO Update_CoveredInventory_PVT;
897 x_return_status := FND_API.g_ret_sts_error;
898 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
899 FND_MSG_PUB.Count_And_Get (
900 p_encoded => FND_API.G_FALSE,
901 p_count => x_msg_count,
902 p_data => x_msg_data
903 );
904 IF x_msg_count > 1 THEN
905 FOR I IN 1..x_msg_count LOOP
906 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
907 END LOOP;
908 END IF;
909
910 WHEN FND_API.G_EXC_ERROR THEN
911 ROLLBACK TO Update_CoveredInventory_PVT;
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 -- Standard call to get message count and if count=1, get the message
914 FND_MSG_PUB.Count_And_Get (
915 p_encoded => FND_API.G_FALSE,
916 p_count => x_msg_count,
917 p_data => x_msg_data
918 );
919 IF x_msg_count > 1 THEN
920 FOR I IN 1..x_msg_count LOOP
921 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
922 END LOOP;
923 END IF;
924
925 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
926 ROLLBACK TO Update_CoveredInventory_PVT;
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 -- Standard call to get message count and if count=1, get the message
929 FND_MSG_PUB.Count_And_Get (
930 p_encoded => FND_API.G_FALSE,
931 p_count => x_msg_count,
932 p_data => x_msg_data
933 );
934 IF x_msg_count > 1 THEN
935 FOR I IN 1..x_msg_count LOOP
936 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
937 END LOOP;
938 END IF;
939
940 WHEN OTHERS THEN
941 ROLLBACK TO Update_CoveredInventory_PVT;
942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
943 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
944 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory');
945 fnd_message.set_token('ERRNO', sqlcode);
946 fnd_message.set_token('REASON', sqlerrm);
947 FND_MSG_PUB.add;
948 -- Standard call to get message count and if count=1, get the message
949 FND_MSG_PUB.Count_And_Get (
950 p_encoded => FND_API.G_FALSE,
951 p_count => x_msg_count,
952 p_data => x_msg_data
953 );
954 IF x_msg_count > 1 THEN
955 FOR I IN 1..x_msg_count LOOP
956 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
957 END LOOP;
958 END IF;
959
960
961 END Update_CoveredInventory;
962
963 END DPP_COVEREDINVENTORY_PVT;