[Home] [Help]
PACKAGE BODY: APPS.DPP_COVEREDINVENTORY_PVT
Source
1 PACKAGE BODY DPP_COVEREDINVENTORY_PVT AS
2 /* $Header: dppvcovb.pls 120.16 2008/06/11 06:00:24 sanagar noship $ */
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 -- Debug Message
148 IF g_debug THEN
149 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
150 END IF;
151 -- Initialize API return status to sucess
152 l_return_status := FND_API.G_RET_STS_SUCCESS;
153 --
154 -- API body
155 --
156 IF l_hdr_rec.org_id IS NULL THEN
157 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
158 FND_MESSAGE.set_token('ID', 'Org ID');
159 FND_MSG_PUB.add;
160 RAISE FND_API.G_EXC_ERROR;
161 ELSIF l_hdr_rec.effective_start_date IS NULL THEN
162 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
163 FND_MESSAGE.set_token('ID', 'Effective Start Date');
164 FND_MSG_PUB.add;
165 RAISE FND_API.G_EXC_ERROR;
166 ELSIF l_hdr_rec.effective_end_date IS NULL THEN
167 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
168 FND_MESSAGE.set_token('ID', 'Effective End Date');
169 FND_MSG_PUB.add;
170 RAISE FND_API.G_EXC_ERROR;
171 ELSE
172 FOR i IN l_covered_inv_tbl.FIRST..l_covered_inv_tbl.LAST LOOP
173 IF l_covered_inv_tbl(i).Transaction_Line_Id IS NULL THEN
174 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
175 FND_MESSAGE.set_token('ID', 'Transaction Line ID');
176 FND_MSG_PUB.add;
177 RAISE FND_API.G_EXC_ERROR;
178 ELSIF l_covered_inv_tbl(i).inventory_item_id IS NULL THEN
179 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
180 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
181 FND_MSG_PUB.add;
182 RAISE FND_API.G_EXC_ERROR;
183 ELSE
184 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)
185 LOOP
186 l_covered_inv_tbl(i).covered_quantity := NVL(get_covered_inventory_rec.covered_qty,0);
187 l_covered_inv_tbl(i).onhand_quantity := NVL(get_covered_inventory_rec.onhand_qty,0);
188 l_covered_inv_tbl(i).uom_code := get_covered_inventory_rec.transaction_uom_code;
189 --IF covered inventory is negative then reassign it to 0
190 IF l_covered_inv_tbl(i).covered_quantity < 0 THEN
191 l_covered_inv_tbl(i).covered_quantity := 0;
192 END IF;
193 l_num_count := 0;
194 --Bug 7157230
195 l_covered_inv_wh_tbl.delete;
196 --Get the ware house level details only if the covered quantiy > 0
197 IF l_covered_inv_tbl(i).covered_quantity > 0 THEN
198 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)
199 LOOP
200 l_num_count := l_num_count + 1;
201 l_covered_inv_wh_tbl(l_num_count).warehouse_name := get_covered_inv_wh_rec.warehouse;
202 l_covered_inv_wh_tbl(l_num_count).warehouse_id := get_covered_inv_wh_rec.warehouse_id;
203 l_covered_inv_wh_tbl(l_num_count).covered_quantity := NVL(get_covered_inv_wh_rec.sum,0);
204 OPEN get_covered_inv_rct_csr(l_hdr_rec.org_id,
205 l_covered_inv_tbl(i).Inventory_ITem_ID,
206 get_covered_inv_wh_rec.warehouse_id);
207 LOOP
208 FETCH get_covered_inv_rct_csr BULK COLLECT INTO l_covered_inv_rct_tbl;
209 EXIT WHEN get_covered_inv_rct_csr%NOTFOUND;
210 END LOOP;
211 CLOSE get_covered_inv_rct_csr;
212 l_covered_inv_wh_tbl(l_num_count).rct_line_tbl := l_covered_inv_rct_tbl;
213 END LOOP;
214 END IF; --ware house level details only if the covered quantiy > 0
215 l_covered_inv_tbl(i).wh_line_tbl := l_covered_inv_wh_tbl;
216 END LOOP;
217 DPP_UTILITY_PVT.debug_message('On Hand Quantity: '||l_covered_inv_tbl(i).onhand_quantity);
218 DPP_UTILITY_PVT.debug_message('Covered Quantity: '||l_covered_inv_tbl(i).covered_quantity);
219 DPP_UTILITY_PVT.debug_message('inventory_item_id: '||l_covered_inv_tbl(i).inventory_item_id);
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 ('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 IF DPP_DEBUG_HIGH_ON THEN
245 DPP_UTILITY_pvt.debug_message ('select_coveredinventory(): x_return_status: ' || x_return_status);
246 END IF;
247
248 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 END IF;
253
254 -- Standard check for p_commit
255 IF FND_API.to_Boolean( p_commit )
256 THEN
257 COMMIT WORK;
258 END IF;
259 -- Debug Message
260 IF g_debug THEN
261 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
262 END IF;
263 -- Standard call to get message count and if count is 1, get message info.
264 FND_MSG_PUB.Count_And_Get
265 (p_count => x_msg_count,
266 p_data => x_msg_data
267 );
268
269 --Exception Handling
270 EXCEPTION
271
272 WHEN FND_API.G_EXC_ERROR THEN
273 ROLLBACK TO Select_CoveredInventory_PVT;
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 -- Standard call to get message count and if count=1, get the message
276 FND_MSG_PUB.Count_And_Get (
277 p_encoded => FND_API.G_FALSE,
278 p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281
282 IF x_msg_count > 1 THEN
283 FOR I IN 1..x_msg_count LOOP
284 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
285 END LOOP;
286 END IF;
287
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 ROLLBACK TO Select_CoveredInventory_PVT;
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291 -- Standard call to get message count and if count=1, get the message
292 FND_MSG_PUB.Count_And_Get (
293 p_encoded => FND_API.G_FALSE,
294 p_count => x_msg_count,
295 p_data => x_msg_data
296 );
297 IF x_msg_count > 1 THEN
298 FOR I IN 1..x_msg_count LOOP
299 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
300 END LOOP;
301 END IF;
302
303 WHEN OTHERS THEN
304 ROLLBACK TO Select_CoveredInventory_PVT;
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
307 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Select_CoveredInventory');
308 fnd_message.set_token('ERRNO', sqlcode);
309 fnd_message.set_token('REASON', sqlerrm);
310 FND_MSG_PUB.add;
311 -- Standard call to get message count and if count=1, get the message
312 FND_MSG_PUB.Count_And_Get (
313 p_encoded => FND_API.G_FALSE,
314 p_count => x_msg_count,
315 p_data => x_msg_data
316 );
317 IF x_msg_count > 1 THEN
318 FOR I IN 1..x_msg_count LOOP
319 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
320 END LOOP;
324 END Select_CoveredInventory;
321 END IF;
322
323
325
326
327 ---------------------------------------------------------------------
328 -- PROCEDURE
329 -- Populate_CoveredInventory
330 --
331 -- PURPOSE
332 -- Populate Covered Inventory
333 --
334 -- PARAMETERS
335 --
336 -- NOTES
337 -- 1.
338 -- 2.
339 ----------------------------------------------------------------------
340
341 PROCEDURE Populate_CoveredInventory(
342 p_api_version IN NUMBER
343 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
344 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
345 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
346 ,x_return_status OUT NOCOPY VARCHAR2
347 ,x_msg_count OUT NOCOPY NUMBER
348 ,x_msg_data OUT NOCOPY VARCHAR2
349 ,p_inv_hdr_rec IN dpp_inv_hdr_rec_type
350 ,p_covered_inv_tbl IN dpp_inv_cov_tbl_type
351 )
352 IS
353 l_api_name CONSTANT VARCHAR2(30) := 'Populate_CoveredInventory';
354 l_api_version CONSTANT NUMBER := 1.0;
355 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
356
357 l_return_status VARCHAR2(30);
358 l_msg_count NUMBER;
359 l_msg_data VARCHAR2(4000);
360
361 l_inv_hdr_rec DPP_COVEREDINVENTORY_PVT.dpp_inv_hdr_rec_type := p_inv_hdr_rec;
362 l_covered_inv_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_tbl_type := p_covered_inv_tbl;
363 l_covered_inv_wh_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_wh_tbl_type;
364 l_covered_inv_rct_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_rct_tbl_type;
365
366 l_inv_details_id NUMBER;
367 l_include_flag VARCHAR2(1);
368 l_days_out NUMBER;
369
370 BEGIN
371 ------------------------------------------
372 -- Initialization
373 ------------------------------------------
374
375 -- Standard begin of API savepoint
376 SAVEPOINT Populate_CoveredInventory_PVT;
377 -- Standard call to check for call compatibility.
378 IF NOT FND_API.Compatible_API_Call ( l_api_version,
379 p_api_version,
380 l_api_name,
381 G_PKG_NAME)
382 THEN
383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384 END IF;
385 -- Initialize message list if p_init_msg_list is set to TRUE.
386 IF FND_API.to_Boolean( p_init_msg_list )
387 THEN
388 FND_MSG_PUB.initialize;
389 END IF;
390
391 -- Debug Message
392 IF g_debug THEN
393 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
394 END IF;
395 -- Initialize API return status to sucess
396 l_return_status := FND_API.G_RET_STS_SUCCESS;
397 --
398 -- API body
399 --
400 BEGIN
401 UPDATE DPP_EXECUTION_DETAILS
402 SET execution_end_date = sysdate
403 ,execution_status = DECODE(l_return_status,FND_API.G_RET_STS_SUCCESS,'SUCCESS','WARNING')
404 ,last_update_date = sysdate
405 ,last_updated_by = l_inv_hdr_rec.Last_Updated_By
406 ,last_update_login = l_inv_hdr_rec.Last_Updated_By
407 ,provider_process_id = l_inv_hdr_rec.Provider_Process_Id
408 ,provider_process_instance_id = l_inv_hdr_rec.Provider_Process_Instance_id
409 ,output_xml = XMLTYPE(l_inv_hdr_rec.Output_XML)
410 WHERE execution_detail_id = l_inv_hdr_rec.Execution_Detail_ID;
411
412 IF SQL%ROWCOUNT = 0 THEN
413 -- Debug Message
414 IF g_debug THEN
415 DPP_UTILITY_PVT.debug_message('Invalid value for Execution Detail ID: ' || l_inv_hdr_rec.Execution_Detail_ID);
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418 END IF;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 DPP_UTILITY_PVT.debug_message(substr(('Error in Updating DPP_EXECUTION_DETAILS: ' || SQLERRM || ' from Populate Covered Inventory API'),1,4000));
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END ;
425
426 DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory(
427 p_api_version => l_api_version
428 ,p_init_msg_list => FND_API.G_FALSE
429 ,p_commit => FND_API.G_FALSE
430 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
431 ,x_return_status => l_return_status
432 ,x_msg_count => l_msg_count
433 ,x_msg_data => l_msg_data
434 ,p_inv_hdr_rec => l_inv_hdr_rec
435 ,p_covered_inv_tbl => l_covered_inv_tbl
436 );
437
438 x_return_status := l_return_status;
439
440 -- Standard check for p_commit
441 IF FND_API.to_Boolean( p_commit )
442 THEN
443 COMMIT WORK;
444 END IF;
445 -- Debug Message
446 IF g_debug THEN
447 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
448 END IF;
449 -- Standard call to get message count and if count is 1, get message info.
450 FND_MSG_PUB.Count_And_Get
451 (p_count => x_msg_count,
452 p_data => x_msg_data
453 );
454
455 --Exception Handling
456 EXCEPTION
457 WHEN DPP_UTILITY_PVT.resource_locked THEN
458 ROLLBACK TO Populate_CoveredInventory_PVT;
459 x_return_status := FND_API.g_ret_sts_error;
460 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
461 FND_MSG_PUB.Count_And_Get (
462 p_encoded => FND_API.G_FALSE,
463 p_count => x_msg_count,
464 p_data => x_msg_data
465 );
466 IF x_msg_count > 1 THEN
467 FOR I IN 1..x_msg_count LOOP
468 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
469 END LOOP;
470 END IF;
471
475 -- Standard call to get message count and if count=1, get the message
472 WHEN FND_API.G_EXC_ERROR THEN
473 ROLLBACK TO Populate_CoveredInventory_PVT;
474 x_return_status := FND_API.G_RET_STS_ERROR;
476 FND_MSG_PUB.Count_And_Get (
477 p_encoded => FND_API.G_FALSE,
478 p_count => x_msg_count,
479 p_data => x_msg_data
480 );
481 IF x_msg_count > 1 THEN
482 FOR I IN 1..x_msg_count LOOP
483 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
484 END LOOP;
485 END IF;
486
487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
488 ROLLBACK TO Populate_CoveredInventory_PVT;
489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
490 -- Standard call to get message count and if count=1, get the message
491 FND_MSG_PUB.Count_And_Get (
492 p_encoded => FND_API.G_FALSE,
493 p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496 IF x_msg_count > 1 THEN
497 FOR I IN 1..x_msg_count LOOP
498 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
499 END LOOP;
500 END IF;
501
502 WHEN OTHERS THEN
503 ROLLBACK TO Populate_CoveredInventory_PVT;
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
506 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Populate_CoveredInventory');
507 fnd_message.set_token('ERRNO', sqlcode);
508 fnd_message.set_token('REASON', sqlerrm);
509 FND_MSG_PUB.add;
510 -- Standard call to get message count and if count=1, get the message
511 FND_MSG_PUB.Count_And_Get (
512 p_encoded => FND_API.G_FALSE,
513 p_count => x_msg_count,
514 p_data => x_msg_data
515 );
516 IF x_msg_count > 1 THEN
517 FOR I IN 1..x_msg_count LOOP
518 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
519 END LOOP;
520 END IF;
521
522 END Populate_CoveredInventory;
523
524
525 PROCEDURE Update_CoveredInventory(
526 p_api_version IN NUMBER
527 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
528 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
529 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
530 ,x_return_status OUT NOCOPY VARCHAR2
531 ,x_msg_count OUT NOCOPY NUMBER
532 ,x_msg_data OUT NOCOPY VARCHAR2
533 ,p_inv_hdr_rec IN dpp_inv_hdr_rec_type
534 ,p_covered_inv_tbl IN dpp_inv_cov_tbl_type
535 )
536 IS
537 l_api_name CONSTANT VARCHAR2(30) := 'Update_CoveredInventory';
538 l_api_version CONSTANT NUMBER := 1.0;
539 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
540
541 l_return_status VARCHAR2(30);
542 l_msg_count NUMBER;
543 l_msg_data VARCHAR2(4000);
544
545 l_inv_hdr_rec DPP_COVEREDINVENTORY_PVT.dpp_inv_hdr_rec_type := p_inv_hdr_rec;
546 l_covered_inv_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_tbl_type := p_covered_inv_tbl;
547 l_covered_inv_wh_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_wh_tbl_type;
548 l_covered_inv_rct_tbl DPP_COVEREDINVENTORY_PVT.dpp_inv_cov_rct_tbl_type;
549 l_inv_details_id NUMBER;
550 l_txn_lines_tbl DPP_LOG_PVT.dpp_txn_line_tbl_type;
551 l_include_flag VARCHAR2(1);
552 l_flag VARCHAR2(1);
553 l_days_out NUMBER;
554 l_sysdate DATE := SYSDATE;
555
556 TYPE inventory_details_id_tbl IS TABLE OF dpp_inventory_details_all.inventory_details_id%TYPE
557 INDEX BY PLS_INTEGER;
558
559 inventory_details_ids inventory_details_id_tbl;
560
561 BEGIN
562 -- Standard begin of API savepoint
563 SAVEPOINT Update_CoveredInventory_PVT;
564 -- Standard call to check for call compatibility.
565 IF NOT FND_API.Compatible_API_Call ( l_api_version,
566 p_api_version,
567 l_api_name,
568 G_PKG_NAME)
569 THEN
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572 -- Initialize message list if p_init_msg_list is set to TRUE.
573 IF FND_API.to_Boolean( p_init_msg_list )
574 THEN
575 FND_MSG_PUB.initialize;
576 END IF;
577 -- Debug Message
578 IF g_debug THEN
579 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
580 END IF;
581 -- Initialize API return status to sucess
582 l_return_status := FND_API.G_RET_STS_SUCCESS;
583 --
584 -- API body
585 --
586 --Check whether the last updated by value is passed
587 IF l_inv_hdr_rec.Last_Updated_By IS NULL THEN
588 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
589 FND_MESSAGE.set_token('ID', 'Last Updated By');
590 FND_MSG_PUB.add;
591 RAISE FND_API.G_EXC_ERROR;
592 ELSIF l_inv_hdr_rec.effective_start_date IS NULL THEN
593 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
594 FND_MESSAGE.set_token('ID', 'Effective Start Date');
595 FND_MSG_PUB.add;
596 RAISE FND_API.G_EXC_ERROR;
597 ELSIF l_inv_hdr_rec.effective_end_date IS NULL THEN
598 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
599 FND_MESSAGE.set_token('ID', 'Effective End Date');
600 FND_MSG_PUB.add;
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 FOR i IN l_covered_inv_tbl.FIRST..l_covered_inv_tbl.LAST LOOP
605 IF l_covered_inv_tbl(i).Transaction_Line_Id IS NULL THEN
606 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
607 FND_MESSAGE.set_token('ID', 'Transaction Line ID');
608 FND_MSG_PUB.add;
609 RAISE FND_API.G_EXC_ERROR;
610 ELSIF
614 FND_MSG_PUB.add;
611 l_covered_inv_tbl(i).inventory_item_id IS NULL THEN
612 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
613 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
615 RAISE FND_API.G_EXC_ERROR;
616 ELSIF
617 l_covered_inv_tbl(i).UOM_Code IS NULL THEN
618 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
619 FND_MESSAGE.set_token('ID', 'UOM Code');
620 FND_MSG_PUB.add;
621 RAISE FND_API.G_EXC_ERROR;
622 ELSE
623 BEGIN
624 UPDATE DPP_TRANSACTION_LINES_ALL
625 SET covered_inventory = NVL(l_covered_inv_tbl(i).Covered_quantity,0),
626 onhand_inventory = NVL(l_covered_inv_tbl(i).Onhand_Quantity,0),
627 UOM = l_covered_inv_tbl(i).UOM_Code,
628 last_update_date = l_sysdate,
629 last_updated_by = l_inv_hdr_rec.Last_Updated_By,
630 last_calculated_by = l_inv_hdr_rec.Last_Updated_By,
631 last_update_login = FND_GLOBAL.LOGIN_ID,
632 last_calculated_date = l_sysdate
633 WHERE transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
634 IF SQL%ROWCOUNT = 0 THEN
635 -- Debug Message
636 DPP_UTILITY_PVT.debug_message('Invalid value for Transaction Line ID: ' || l_covered_inv_tbl(i).Transaction_Line_Id);
637
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640 EXCEPTION
641 WHEN OTHERS THEN
642 DPP_UTILITY_PVT.debug_message(substr(('Error in Updating DPP_TRANSACTION_LINES_ALL: ' || SQLERRM || ' from Update Covered Inventory API'),1,4000));
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 END ;
645 END IF;
646 -- Assign values to l_txn_lines_tbl for History
647 l_txn_lines_tbl(i).log_mode := 'U';
648 l_txn_lines_tbl(i).transaction_header_id := l_inv_hdr_rec.transaction_header_id;
649 l_txn_lines_tbl(i).transaction_line_id := l_covered_inv_tbl(i).Transaction_Line_Id;
650 l_txn_lines_tbl(i).covered_inventory := NVL(l_covered_inv_tbl(i).Covered_quantity,0);
651 l_txn_lines_tbl(i).org_id := l_inv_hdr_rec.org_id;
652 l_txn_lines_tbl(i).last_update_date := l_sysdate;
653 l_txn_lines_tbl(i).last_updated_by := l_inv_hdr_rec.Last_Updated_By;
654 l_txn_lines_tbl(i).creation_date := l_sysdate;
655 l_txn_lines_tbl(i).created_by := l_inv_hdr_rec.Last_Updated_By;
656 l_txn_lines_tbl(i).last_update_login := FND_GLOBAL.LOGIN_ID;
657 l_txn_lines_tbl(i).inventory_item_id := l_covered_inv_tbl(i).inventory_item_id;
658 l_txn_lines_tbl(i).last_calculated_by := l_inv_hdr_rec.Last_Updated_By;
659 l_txn_lines_tbl(i).last_calculated_date := l_sysdate;
660 l_txn_lines_tbl(i).onhand_inventory := NVL(l_covered_inv_tbl(i).Onhand_Quantity,0);
661
662 BEGIN
663 DPP_UTILITY_PVT.debug_message('Org Id: ' || l_inv_hdr_rec.org_id || 'transaction_line_id ' || l_covered_inv_tbl(i).Transaction_Line_Id);
664 -- Delete existing rows in DPP_INVENTORY_DETAILS_ADJ_ALL (if any)
665 SELECT
666 inventory_details_id
667 BULK COLLECT INTO
668 inventory_details_ids
669 FROM
670 dpp_inventory_details_all
671 WHERE
672 org_id = l_inv_hdr_rec.org_id
673 AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
674
675 FORALL indx IN inventory_details_ids.FIRST .. inventory_details_ids .LAST
676 DELETE
677 FROM DPP_INVENTORY_DETAILS_ADJ_ALL
678 WHERE INVENTORY_DETAILS_ID = inventory_details_ids(indx);
679
680 -- Delete existing rows in DPP_INVENTORY_DETAILS_ALL (if any)
681 DELETE
682 FROM DPP_INVENTORY_DETAILS_ALL
683 WHERE org_id = l_inv_hdr_rec.org_id
684 AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
685
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 NULL;
689 WHEN OTHERS THEN
690 IF g_debug THEN
691 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
692 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory - Delete rows');
693 fnd_message.set_token('ERRNO', sqlcode);
694 fnd_message.set_token('REASON', sqlerrm);
695 FND_MSG_PUB.add;
696 END IF;
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698 END;
699 --Insert child records if covered quantity <> 0
700 IF NVL(l_covered_inv_tbl(i).Covered_quantity,0) <> 0 THEN
701 FOR j IN l_covered_inv_tbl(i).wh_line_tbl.FIRST..l_covered_inv_tbl(i).wh_line_tbl.LAST LOOP
702 SELECT DPP_INVENTORY_DETAILS_SEQ.nextval
703 INTO l_inv_details_id
704 FROM DUAL;
705 l_flag := 'N';
706 INSERT INTO DPP_INVENTORY_DETAILS_ALL(
707 inventory_details_id,
708 transaction_line_id,
709 quantity,
710 uom,
711 include_flag,
712 creation_date,
713 created_by,
714 last_update_date,
715 last_updated_by,
716 last_update_login,
717 inventory_item_id,
718 org_id,
719 organization_id,
720 object_version_number)
721 VALUES( l_inv_details_id,
722 l_covered_inv_tbl(i).Transaction_Line_Id,
723 NVL(l_covered_inv_tbl(i).wh_line_tbl(j).Covered_quantity,0),
724 l_covered_inv_tbl(i).UOM_Code,
725 'N',
726 l_sysdate,
727 l_inv_hdr_rec.Last_Updated_By,
728 l_sysdate,
729 l_inv_hdr_rec.Last_Updated_By,
730 l_inv_hdr_rec.Last_Updated_By,
734 1);
731 l_covered_inv_tbl(i).inventory_item_id,
732 l_inv_hdr_rec.org_id,
733 l_covered_inv_tbl(i).wh_line_tbl(j).Warehouse_id,
735 DPP_UTILITY_PVT.debug_message('Inventory Details ID: '||l_inv_details_id);
736 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
737 BEGIN
738 IF ((l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received >= l_inv_hdr_rec.effective_start_date)
739 AND (l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received < l_inv_hdr_rec.effective_end_date)) THEN
740 l_include_flag := 'Y';
741 l_flag := 'Y';
742 l_days_out := 0;
743 ELSIF (l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received < l_inv_hdr_rec.effective_start_date) THEN
744 l_include_flag := 'N';
745 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);
746 l_days_out := floor(l_days_out);
747 ELSIF (l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received >= l_inv_hdr_rec.effective_end_date ) THEN
748 l_include_flag := 'N';
749 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;
750 l_days_out := ceil(l_days_out);
751 IF l_days_out = 0 THEN
752 l_days_out := 1;
753 END IF;
754 END IF;
755 END;
756
757 INSERT INTO DPP_INVENTORY_DETAILS_ADJ_ALL(
758 inv_details_adj_id,
759 inventory_details_id,
760 date_received,
761 days_out,
762 quantity,
763 uom,
764 comments,
765 include_flag,
766 creation_date,
767 created_by,
768 last_update_date,
769 last_updated_by,
770 last_update_login,
771 org_id,
772 object_version_number)
773 VALUES(dpp_inv_details_adj_id_seq.nextval,
774 l_inv_details_id,
775 l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received,
776 l_days_out,
777 NVL(l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).Onhand_quantity,0),
778 l_covered_inv_tbl(i).UOM_Code,
779 null,
780 l_include_flag,
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_inv_hdr_rec.org_id,
787 1
788 );
789
790 END LOOP; --l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl.FIRST..
791 IF l_flag = 'Y' THEN
792 UPDATE DPP_INVENTORY_DETAILS_ALL
793 SET include_flag = 'Y',
794 object_version_number = object_version_number + 1,
795 last_update_date = l_sysdate,
796 last_updated_by = l_inv_hdr_rec.Last_Updated_By,
797 last_update_login = l_inv_hdr_rec.Last_Updated_By
798 WHERE inventory_details_id = l_inv_details_id;
799 END IF;
800 END LOOP; --l_covered_inv_tbl(i).wh_line_tbl.FIRST
801 END IF; -- qty > 0
802
803 END LOOP;
804
805 --For insertion into Log table
806 DPP_LOG_PVT.Insert_LinesLog(p_api_version => 1.0
807 ,p_init_msg_list => FND_API.G_FALSE
808 ,p_commit => FND_API.G_FALSE
809 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
810 ,x_return_status => l_return_status
811 ,x_msg_count => l_msg_count
812 ,x_msg_data => l_msg_data
813 ,p_txn_lines_tbl => l_txn_lines_tbl
814 );
815 IF g_debug THEN
816 dpp_utility_pvt.debug_message('return status for Insert_LinesLog =>'||l_return_status);
817 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
818 dpp_utility_pvt.debug_message(substr(('Message dat for the DPP Insert_LinesLog API =>'||l_msg_data),1,4000));
819 END IF;
820 END IF;
821
822 x_return_status := l_return_status;
823 -- Standard check for p_commit
824 IF FND_API.to_Boolean(p_commit) THEN
825 COMMIT WORK;
826 END IF;
827 -- Debug Message
828 IF g_debug THEN
829 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
830 END IF;
831 -- Standard call to get message count and if count is 1, get message info.
832 FND_MSG_PUB.Count_And_Get
833 (p_count => x_msg_count,
834 p_data => x_msg_data
835 );
836
837 --Exception Handling
838 EXCEPTION
839 WHEN DPP_UTILITY_PVT.resource_locked THEN
840 ROLLBACK TO Update_CoveredInventory_PVT;
841 x_return_status := FND_API.g_ret_sts_error;
842 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
843 FND_MSG_PUB.Count_And_Get (
844 p_encoded => FND_API.G_FALSE,
845 p_count => x_msg_count,
846 p_data => x_msg_data
847 );
848 IF x_msg_count > 1 THEN
849 FOR I IN 1..x_msg_count LOOP
850 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
851 END LOOP;
852 END IF;
853
854 WHEN FND_API.G_EXC_ERROR THEN
855 ROLLBACK TO Update_CoveredInventory_PVT;
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 -- Standard call to get message count and if count=1, get the message
858 FND_MSG_PUB.Count_And_Get (
859 p_encoded => FND_API.G_FALSE,
863 IF x_msg_count > 1 THEN
860 p_count => x_msg_count,
861 p_data => x_msg_data
862 );
864 FOR I IN 1..x_msg_count LOOP
865 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
866 END LOOP;
867 END IF;
868
869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
870 ROLLBACK TO Update_CoveredInventory_PVT;
871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
872 -- Standard call to get message count and if count=1, get the message
873 FND_MSG_PUB.Count_And_Get (
874 p_encoded => FND_API.G_FALSE,
875 p_count => x_msg_count,
876 p_data => x_msg_data
877 );
878 IF x_msg_count > 1 THEN
879 FOR I IN 1..x_msg_count LOOP
880 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
881 END LOOP;
882 END IF;
883
884 WHEN OTHERS THEN
885 ROLLBACK TO Update_CoveredInventory_PVT;
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
888 fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory');
889 fnd_message.set_token('ERRNO', sqlcode);
890 fnd_message.set_token('REASON', sqlerrm);
891 FND_MSG_PUB.add;
892 -- Standard call to get message count and if count=1, get the message
893 FND_MSG_PUB.Count_And_Get (
894 p_encoded => FND_API.G_FALSE,
895 p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898 IF x_msg_count > 1 THEN
899 FOR I IN 1..x_msg_count LOOP
900 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
901 END LOOP;
902 END IF;
903
904
905 END Update_CoveredInventory;
906
907 END DPP_COVEREDINVENTORY_PVT;