[Home] [Help]
PACKAGE BODY: APPS.DPP_LISTPRICE_PVT
Source
1 PACKAGE BODY DPP_LISTPRICE_PVT AS
2 /* $Header: dppvlprb.pls 120.19.12010000.2 2010/04/21 11:33:45 anbbalas ship $ */
3
4 -- Package name : DPP_LISTPRICE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
11 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_LISTPRICE_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvlprb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- Update_ListPrice
17 --
18 -- PURPOSE
19 -- Update list price.
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
27
28 PROCEDURE Update_ListPrice(
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_txn_hdr_rec IN dpp_txn_hdr_rec_type
37 ,p_item_cost_tbl IN dpp_txn_line_tbl_type
38 )
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Update_ListPrice';
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
43 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_LISTPRICE_PVT.UPDATE_LISTPRICE';
44
45 l_return_status VARCHAR2(30);
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(4000);
48
49 l_txn_hdr_rec DPP_LISTPRICE_PVT.dpp_txn_hdr_rec_type := p_txn_hdr_rec;
50 l_item_cost_tbl DPP_LISTPRICE_PVT.dpp_txn_line_tbl_type := p_item_cost_tbl;
51 l_exe_update_rec DPP_ExecutionDetails_PVT.DPP_EXE_UPDATE_REC_TYPE;
52 l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
53
54 l_to_amount NUMBER := 0;
55 l_set_of_books_id NUMBER;
56 l_mrc_sob_type_code VARCHAR2(1);
57 l_fc_currency_code VARCHAR2(15);
58 l_exchange_rate_type VARCHAR2(30);
59 l_exchange_rate NUMBER;
60 l_execution_status VARCHAR2(10);
61
62 l_item_rec INV_ITEM_GRP.Item_rec_type;
63 l_x_item_rec INV_ITEM_GRP.Item_rec_type;
64 l_error_tbl INV_ITEM_GRP.Error_tbl_type;
65 l_revision_rec INV_ITEM_GRP.Item_Revision_Rec_Type;
66
67 l_output_xml CLOB;
68 l_queryCtx dbms_xmlquery.ctxType;
69 l_Transaction_Number CLOB;
70 l_control_level NUMBER;
71 l_count NUMBER;
72 l_reason fnd_new_messages.message_text%TYPE;
73 l_item_number VARCHAR2(240);
74
75 CURSOR Item_cur(p_inventory_item_id IN NUMBER,p_org_id IN NUMBER, p_control_level IN NUMBER)
76 IS
77 SELECT DECODE( p_control_level, 1, mp.master_organization_id, 2, mp.organization_id) organization_id,
78 msi.concatenated_segments item_number
79 FROM mtl_parameters mp,
80 financials_system_params_all fspa,
81 mtl_system_items_kfv msi
82 WHERE mp.organization_id = fspa.inventory_organization_id and
83 mp.organization_id = msi.organization_id and
84 msi.inventory_item_id = p_inventory_item_id and
85 fspa.org_id = p_org_id;
86
87 BEGIN
88 -- Standard begin of API savepoint
89 SAVEPOINT Update_ListPrice_PVT;
90 -- Standard call to check for call compatibility.
91 IF NOT FND_API.Compatible_API_Call ( l_api_version,
92 p_api_version,
93 l_api_name,
94 G_PKG_NAME)
95 THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98 -- Initialize message list if p_init_msg_list is set to TRUE.
99 IF FND_API.to_Boolean( p_init_msg_list )
100 THEN
101 FND_MSG_PUB.initialize;
102 END IF;
103 -- Debug Message
104 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'start');
105
106 -- Initialize API return status to sucess
107 l_return_status := FND_API.G_RET_STS_SUCCESS;
108 x_return_status := l_return_status;
109 --
110 -- API body
111 --
112 IF l_txn_hdr_rec.org_id IS NULL THEN
113 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
114 FND_MESSAGE.set_token('ID', 'Org ID');
115 FND_MSG_PUB.add;
116 RAISE FND_API.G_EXC_ERROR;
117 ELSIF l_txn_hdr_rec.Transaction_Number IS NULL THEN
118 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
119 FND_MESSAGE.set_token('ID', 'Transaction Number');
120 FND_MSG_PUB.add;
121 RAISE FND_API.G_EXC_ERROR;
122 ELSIF l_txn_hdr_rec.Transaction_Header_ID IS NULL THEN
123 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
124 FND_MESSAGE.set_token('ID', 'Transaction Header ID');
125 FND_MSG_PUB.add;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128 --Assign
129 l_Transaction_Number := ''''||l_txn_hdr_rec.Transaction_Number||'''';
130
131 --Control Level:1 - Master level, 2 - Org level
132 BEGIN
133 SELECT control_level
134 INTO l_control_level
135 FROM mtl_item_attributes_v miav,
136 mtl_item_attr_appl_inst_v miaaiv
137 WHERE status_control_code IS NULL
138 AND miaaiv.attribute_name = miav.attribute_name
139 AND miaaiv.attribute_name = 'MTL_SYSTEM_ITEMS.LIST_PRICE_PER_UNIT';
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN --
142 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_CONTROL_LEVEL');
143 FND_MSG_PUB.add;
144 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
148 WHEN OTHERS THEN
145 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
146 END IF;
147 RAISE FND_API.G_EXC_ERROR;
149 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
150 fnd_message.set_token('ROUTINE', 'DPP_LISTPRICE_PVT');
151 fnd_message.set_token('ERRNO', sqlcode);
152 fnd_message.set_token('REASON', sqlerrm);
153 FND_MSG_PUB.add;
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END;
156 IF l_item_cost_tbl.EXISTS(1) THEN
157 FOR i IN l_item_cost_tbl.FIRST..l_item_cost_tbl.LAST LOOP
158 l_to_amount := 0;
159 DPP_UTILITY_PVT.calculate_functional_curr(p_from_amount => l_item_cost_tbl(i).new_price
160 ,p_conv_date => SYSDATE
161 ,p_tc_currency_code => l_item_cost_tbl(i).currency
162 ,p_org_id => l_txn_hdr_rec.org_id
163 ,x_to_amount => l_to_amount
164 ,x_set_of_books_id => l_set_of_books_id
165 ,x_mrc_sob_type_code => l_mrc_sob_type_code
166 ,x_fc_currency_code => l_fc_currency_code
167 ,x_exchange_rate_type => l_exchange_rate_type
168 ,x_exchange_rate => l_exchange_rate
169 ,x_return_status => l_return_status);
170 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 IF l_item_cost_tbl(i).inventory_item_id IS NULL THEN
175 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
176 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
177 FND_MSG_PUB.add;
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180 l_count := 0;
181 FOR Item_rec IN Item_cur(l_item_cost_tbl(i).inventory_item_id,l_txn_hdr_rec.org_id, l_control_level) LOOP
182 l_item_cost_tbl(i).item_number := Item_rec.item_number;
183 l_item_rec.inventory_item_id := l_item_cost_tbl(i).inventory_item_id;
184 l_item_rec.organization_id := Item_rec.organization_id;
185 l_item_rec.list_price_per_unit := NVL(l_to_amount,l_item_cost_tbl(i).new_price);
186 l_count := l_count + 1;
187
188 inv_item_grp.Update_Item(p_commit => fnd_api.g_FALSE
189 , p_lock_rows => fnd_api.g_TRUE
190 , p_validation_level => fnd_api.g_VALID_LEVEL_FULL
191 , p_Item_rec => l_item_rec
192 , x_Item_rec => l_x_item_rec
193 , x_return_status => l_return_status
194 , x_Error_tbl => l_error_tbl
195 , p_Template_Id => NULL
196 , p_Template_Name => NULL
197 , p_Revision_rec => l_revision_rec
198 );
199 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
200 l_item_cost_tbl(i).update_status := 'Y';
201 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
202 NewPrice,
203 Currency,
204 Reason_For_Failure)
205 VALUES(l_item_cost_tbl(i).item_number,
206 l_item_rec.list_price_per_unit,
207 l_item_cost_tbl(i).Currency,
208 NULL);
209 ELSE
210 l_item_cost_tbl(i).update_status := 'N';
211 l_return_status := FND_API.G_RET_STS_ERROR;
212 FOR j IN l_error_tbl.FIRST..l_error_tbl.LAST LOOP
213 l_item_cost_tbl(i).Reason_For_Failure := NVL(l_item_cost_tbl(i).Reason_For_Failure,' ')
214 ||'Org ID: '||Item_rec.organization_id
215 ||' Error: '||l_error_tbl(j).MESSAGE_TEXT;
216
217 INSERT INTO DPP_OUTPUT_XML_GT(
218 Item_Number,NewPrice,
219 Currency,Reason_For_Failure)
220 VALUES(
221 l_item_cost_tbl(i).item_number,l_item_rec.list_price_per_unit,
222 l_item_cost_tbl(i).Currency,l_item_cost_tbl(i).Reason_For_Failure);
223 END LOOP;
224 END IF;
225 IF x_return_status NOT IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
226 x_return_status := l_return_status;
227 END IF;
228 END LOOP;
229 --Check if the item belongs to that inventory organization
230 IF l_count = 0 THEN
231 l_item_cost_tbl(i).update_status := 'N';
232 l_return_status := FND_API.G_RET_STS_ERROR;
233 SELECT fnd_message.get_string('DPP','DPP_INVALID_ITEM')
234 INTO l_reason
235 FROM dual;
236
237 SELECT DISTINCT(concatenated_segments)
238 INTO l_item_number
239 FROM mtl_system_items_kfv
240 WHERE inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
241
242 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
243 NewPrice,
247 NVL(l_to_amount,l_item_cost_tbl(i).new_price),
244 Currency,
245 Reason_For_Failure)
246 VALUES(l_item_number,
248 l_item_cost_tbl(i).Currency,
249 l_reason);
250 END IF;
251 IF x_return_status NOT IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
252 x_return_status := l_return_status;
253 END IF;
254 l_status_Update_tbl(i).transaction_line_id := l_item_cost_tbl(i).transaction_line_id;
255 l_status_Update_tbl(i).update_status := l_item_cost_tbl(i).update_status;
256 END LOOP;
257 ELSE
258 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
259 FND_MESSAGE.set_token('ID', 'Line Details');
260 FND_MSG_PUB.add;
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263
264 BEGIN
265 IF x_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
266 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
267 CURSOR (Select Item_Number ITEMNUMBER,
268 NewPrice NEWPRICE,
269 Currency CURRENCY,
270 Reason_For_Failure REASON
271 from DPP_OUTPUT_XML_GT
272 where Reason_For_Failure IS NOT NULL) TRANSACTION from dual');
273 ELSE
274 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER from dual');
275 END IF;
276 dbms_xmlquery.setRowTag(l_queryCtx
277 ,'ROOT'
278 );
279 l_output_xml := dbms_xmlquery.getXml(l_queryCtx);
280 dbms_xmlquery.closeContext(l_queryCtx);
281 EXCEPTION
282 WHEN OTHERS THEN
283 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
285 fnd_message.set_token('ROUTINE', 'DPP_LISTPRICE_PVT.Update_ListPrice');
286 fnd_message.set_token('ERRNO', sqlcode);
287 fnd_message.set_token('REASON', sqlerrm);
288 fnd_msg_pub.add;
289 END;
290 IF x_return_status NOT IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
291 x_return_status := l_return_status;
292 END IF;
293
294 SELECT DECODE(x_return_status,'S','SUCCESS','WARNING')
295 INTO l_execution_status
296 FROM DUAL;
297
298 l_exe_update_rec.Transaction_Header_ID := l_txn_hdr_rec.Transaction_Header_ID;
299 l_exe_update_rec.Org_ID := l_txn_hdr_rec.Org_ID;
300 l_exe_update_rec.Execution_Detail_ID := l_txn_hdr_rec.Execution_Detail_ID;
301 l_exe_update_rec.Output_XML := l_output_xml;
302 l_exe_update_rec.EXECUTION_Status := l_execution_status;
303 l_exe_update_rec.Execution_End_Date := SYSDATE;
304 l_exe_update_rec.Provider_Process_Id := l_txn_hdr_rec.Provider_Process_Id;
305 l_exe_update_rec.Provider_Process_Instance_id := l_txn_hdr_rec.Provider_Process_Instance_id;
306 l_exe_update_rec.Last_Updated_By := l_txn_hdr_rec.Last_Updated_By;
307
308 DPP_ExecutionDetails_PVT.Update_ExecutionDetails(
309 p_api_version => l_api_version
310 ,p_init_msg_list => FND_API.G_FALSE
311 ,p_commit => FND_API.G_FALSE
312 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
313 ,x_return_status => l_return_status
314 ,x_msg_count => l_msg_count
315 ,x_msg_data => l_msg_data
316 ,p_EXE_UPDATE_rec => l_exe_update_rec
317 ,p_status_Update_tbl => l_status_Update_tbl
318 );
319
320 IF x_return_status NOT IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
321 x_return_status := l_return_status;
322 END IF;
323
324 -- Standard check for p_commit
325 IF FND_API.to_Boolean( p_commit )
326 THEN
327 COMMIT WORK;
328 END IF;
329
330 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
331 FND_MESSAGE.set_name('DPP', 'DPP_UPDATE_ITEM_ERR');
332 x_msg_data := fnd_message.get();
333 END IF;
334
335 -- Debug Message
336 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'end');
337
338 -- Standard call to get message count and if count is 1, get message info.
339 /* FND_MSG_PUB.Count_And_Get
340 (p_count => x_msg_count,
341 p_data => x_msg_data
342 );
343 IF x_msg_count > 1 THEN
344 FOR I IN 1..x_msg_count LOOP
345 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
346 END LOOP;
347 END IF;*/
348
349
350 --Exception Handling
351 EXCEPTION
352 WHEN DPP_UTILITY_PVT.resource_locked THEN
353 ROLLBACK TO UPDATE_LISTPRICE_PVT;
354 x_return_status := FND_API.g_ret_sts_error;
355 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
356 FND_MSG_PUB.Count_And_Get (
357 p_encoded => FND_API.G_FALSE,
358 p_count => x_msg_count,
359 p_data => x_msg_data
360 );
361
362 IF x_msg_count > 1 THEN
363 FOR I IN 1..x_msg_count LOOP
364 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
365 END LOOP;
366 END IF;
367
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK TO UPDATE_LISTPRICE_PVT;
370 x_return_status := FND_API.G_RET_STS_ERROR;
374 p_count => x_msg_count,
371 -- Standard call to get message count and if count=1, get the message
372 FND_MSG_PUB.Count_And_Get (
373 p_encoded => FND_API.G_FALSE,
375 p_data => x_msg_data
376 );
377 IF x_msg_count > 1 THEN
378 FOR I IN 1..x_msg_count LOOP
379 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
380 END LOOP;
381 END IF;
382
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 ROLLBACK TO UPDATE_LISTPRICE_PVT;
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 -- Standard call to get message count and if count=1, get the message
387 FND_MSG_PUB.Count_And_Get (
388 p_encoded => FND_API.G_FALSE,
389 p_count => x_msg_count,
390 p_data => x_msg_data
391 );
392 IF x_msg_count > 1 THEN
393 FOR I IN 1..x_msg_count LOOP
394 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
395 END LOOP;
396 END IF;
397
398 WHEN OTHERS THEN
399 ROLLBACK TO UPDATE_LISTPRICE_PVT;
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
402 fnd_message.set_token('ROUTINE', l_full_name);
403 fnd_message.set_token('ERRNO', sqlcode);
404 fnd_message.set_token('REASON', sqlerrm);
405 fnd_msg_pub.add;
406 -- Standard call to get message count and if count=1, get the message
407 FND_MSG_PUB.Count_And_Get (
408 p_encoded => FND_API.G_FALSE,
409 p_count => x_msg_count,
410 p_data => x_msg_data
411 );
412
413 IF x_msg_count > 1 THEN
414 FOR I IN 1..x_msg_count LOOP
415 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
416 END LOOP;
417 END IF;
418
419 END Update_ListPrice;
420
421 END DPP_LISTPRICE_PVT;