[Home] [Help]
PACKAGE BODY: APPS.DPP_LISTPRICE_PVT
Source
1 PACKAGE BODY DPP_LISTPRICE_PVT AS
2 /* $Header: dppvlprb.pls 120.19 2008/06/16 08:13:31 sdasan noship $ */
3
4 -- Package name : DPP_LISTPRICE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_LISTPRICE_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) := '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
44 l_return_status VARCHAR2(30);
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(4000);
47
48 l_txn_hdr_rec DPP_LISTPRICE_PVT.dpp_txn_hdr_rec_type := p_txn_hdr_rec;
49 l_item_cost_tbl DPP_LISTPRICE_PVT.dpp_txn_line_tbl_type := p_item_cost_tbl;
50 l_exe_update_rec DPP_ExecutionDetails_PVT.DPP_EXE_UPDATE_REC_TYPE;
51 l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
52
53 l_to_amount NUMBER := 0;
54 l_set_of_books_id NUMBER;
55 l_mrc_sob_type_code VARCHAR2(1);
56 l_fc_currency_code VARCHAR2(15);
57 l_exchange_rate_type VARCHAR2(30);
58 l_exchange_rate NUMBER;
59 l_execution_status VARCHAR2(10);
60
61 l_item_rec INV_ITEM_GRP.Item_rec_type;
62 l_x_item_rec INV_ITEM_GRP.Item_rec_type;
63 l_error_tbl INV_ITEM_GRP.Error_tbl_type;
64 l_revision_rec INV_ITEM_GRP.Item_Revision_Rec_Type;
65
66 l_output_xml CLOB;
67 l_queryCtx dbms_xmlquery.ctxType;
68 l_Transaction_Number CLOB;
69 l_control_level NUMBER;
70 l_count NUMBER;
71 l_reason fnd_new_messages.message_text%TYPE;
72 l_item_number VARCHAR2(240);
73
74 CURSOR Item_cur(p_inventory_item_id IN NUMBER,p_org_id IN NUMBER, p_control_level IN NUMBER)
75 IS
76 SELECT DECODE( p_control_level, 1, mp.master_organization_id, 2, mp.organization_id) organization_id,
77 msi.concatenated_segments item_number
78 FROM mtl_parameters mp,
79 financials_system_params_all fspa,
80 mtl_system_items_kfv msi
81 WHERE mp.organization_id = fspa.inventory_organization_id and
82 mp.organization_id = msi.organization_id and
83 msi.inventory_item_id = p_inventory_item_id and
84 fspa.org_id = p_org_id;
85
86 BEGIN
87 -- Standard begin of API savepoint
88 SAVEPOINT Update_ListPrice_PVT;
89 -- Standard call to check for call compatibility.
90 IF NOT FND_API.Compatible_API_Call ( l_api_version,
91 p_api_version,
92 l_api_name,
93 G_PKG_NAME)
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97 -- Initialize message list if p_init_msg_list is set to TRUE.
98 IF FND_API.to_Boolean( p_init_msg_list )
99 THEN
100 FND_MSG_PUB.initialize;
101 END IF;
102 -- Debug Message
103 IF g_debug THEN
104 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
105 END IF;
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
145 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
146 END IF;
147 RAISE FND_API.G_EXC_ERROR;
148 WHEN OTHERS THEN
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,
244 Currency,
245 Reason_For_Failure)
246 VALUES(l_item_number,
247 NVL(l_to_amount,l_item_cost_tbl(i).new_price),
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 IF g_debug THEN
337 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
338 END IF;
339 -- Standard call to get message count and if count is 1, get message info.
340 /* FND_MSG_PUB.Count_And_Get
341 (p_count => x_msg_count,
342 p_data => x_msg_data
343 );
344 IF x_msg_count > 1 THEN
345 FOR I IN 1..x_msg_count LOOP
346 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
347 END LOOP;
348 END IF;*/
349
350
351 --Exception Handling
352 EXCEPTION
353 WHEN DPP_UTILITY_PVT.resource_locked THEN
354 ROLLBACK TO UPDATE_LISTPRICE_PVT;
355 x_return_status := FND_API.g_ret_sts_error;
356 DPP_UTILITY_PVT.Error_Message(p_message_name => 'API_RESOURCE_LOCKED');
357 FND_MSG_PUB.Count_And_Get (
358 p_encoded => FND_API.G_FALSE,
359 p_count => x_msg_count,
360 p_data => x_msg_data
361 );
362
363 IF x_msg_count > 1 THEN
364 FOR I IN 1..x_msg_count LOOP
365 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
366 END LOOP;
367 END IF;
368
369 WHEN FND_API.G_EXC_ERROR THEN
370 ROLLBACK TO UPDATE_LISTPRICE_PVT;
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 -- Standard call to get message count and if count=1, get the message
373 FND_MSG_PUB.Count_And_Get (
374 p_encoded => FND_API.G_FALSE,
375 p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378 IF x_msg_count > 1 THEN
379 FOR I IN 1..x_msg_count LOOP
380 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
381 END LOOP;
382 END IF;
383
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 ROLLBACK TO UPDATE_LISTPRICE_PVT;
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 -- Standard call to get message count and if count=1, get the message
388 FND_MSG_PUB.Count_And_Get (
389 p_encoded => FND_API.G_FALSE,
390 p_count => x_msg_count,
391 p_data => x_msg_data
392 );
393 IF x_msg_count > 1 THEN
394 FOR I IN 1..x_msg_count LOOP
395 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
396 END LOOP;
397 END IF;
398
399 WHEN OTHERS THEN
400 ROLLBACK TO UPDATE_LISTPRICE_PVT;
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
402 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
403 fnd_message.set_token('ROUTINE', l_full_name);
404 fnd_message.set_token('ERRNO', sqlcode);
405 fnd_message.set_token('REASON', sqlerrm);
406 fnd_msg_pub.add;
407 -- Standard call to get message count and if count=1, get the message
408 FND_MSG_PUB.Count_And_Get (
409 p_encoded => FND_API.G_FALSE,
410 p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413
414 IF x_msg_count > 1 THEN
415 FOR I IN 1..x_msg_count LOOP
416 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
417 END LOOP;
418 END IF;
419
420 END Update_ListPrice;
421
422 END DPP_LISTPRICE_PVT;