1 PACKAGE BODY CST_ItemResourceCosts_GRP AS
2 /* $Header: CSTGIRCB.pls 120.3 2006/02/06 13:36:55 vtkamath noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_ItemResourceCosts_GRP';
5 G_LOG_HEAD CONSTANT VARCHAR2(40) := 'cst.plsql.'||G_PKG_NAME;
6 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7
8 -------------------------------------------------------------------------------
9 -- API name : Get_ItemCost
10 -- Type : Group
11 -- Function : Returns item cost for the given item ID,
12 -- cost type, and organization.
13 -- Parameters :
14 -- IN :
15 -- p_api_version IN NUMBER Required
16 -- p_init_msg_list IN VARCHAR2 Optional
17 -- Default = FND_API.G_FALSE
18 -- p_commit IN VARCHAR2 Optional
19 -- Default = FND_API.G_FALSE
20 -- p_validation_level IN NUMBER Optional
21 -- Default = FND_API.G_VALID_LEVEL_FULL
22 -- p_item_id IN NUMBER Required
23 -- p_organization_id IN NUMBER Required
24 -- p_cost_source IN NUMBER Required
25 -- 1 - Return item cost from valuation cost type.
26 -- 2 - Return item cost from user-provided cost type.
27 -- 3 - Return item cost as the list price per unit
28 -- from item definition.
29 -- 4 - Return item cost as average of the
30 -- last 5 PO receipts of this item.
31 -- p_cost_type_id IN NUMBER Optional
32 -- Default = 0
33 --
34 -- OUT :
35 -- x_return_status OUT VARCHAR2(1)
36 -- x_msg_count OUT NUMBER
37 -- x_msg_data OUT VARCHAR2(2000)
38 -- x_item_cost OUT NUMBER
39 -- x_currency_code OUT VARCHAR2(15)
40 -- - functional currency of p_organizaiton_id
41 -- Version :
42 -- Initial version 1.0
43 --
44 -- End of comments
45 -------------------------------------------------------------------------------
46 PROCEDURE Get_ItemCost(
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2,
49 p_commit IN VARCHAR2,
50 p_validation_level IN NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 p_item_id IN NUMBER,
55 p_organization_id IN NUMBER,
56 p_cost_source IN NUMBER,
57 p_cost_type_id IN NUMBER,
58 x_item_cost OUT NOCOPY NUMBER,
59 x_currency_code OUT NOCOPY VARCHAR2
60 ) IS
61 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemCost';
62 l_api_version CONSTANT NUMBER := 1.0;
63
64 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
65 l_msg_count NUMBER := 0;
66 l_msg_data VARCHAR2(8000) := '';
67 l_stmt_num NUMBER := 0;
68 l_api_message VARCHAR2(1000);
69
70 l_cost_type_id NUMBER;
71
72 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
73 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
74 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
75 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
76 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
77 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
78
79 BEGIN
80 -- Standard start of API savepoint
81 SAVEPOINT Get_ItemCost_PVT;
82
83 IF l_procLog THEN
84 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
85 ,'Get_ItemCost <<');
86 END IF;
87
88 -- Standard call to check for call compatibility
89 IF NOT FND_API.Compatible_API_Call (
90 l_api_version,
91 p_api_version,
92 l_api_name,
93 G_PKG_NAME ) THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96
97 -- Initialize message list if p_init_msg_list is set to TRUE
98 IF FND_API.to_Boolean(p_init_msg_list) THEN
99 FND_MSG_PUB.initialize;
100 END IF;
101
102 -- Initialize API return status to success
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 -- Check the value passed in p_cost_type_id
106 -- If it's 0, then fetch the cost_type_id for the valuation cost type
107 l_cost_type_id := p_cost_type_id;
108 if (p_cost_source = 1 OR ((p_cost_source = 2) AND (p_cost_type_id = 0))) then
109 l_stmt_num := 10;
110
111 select primary_cost_method
112 into l_cost_type_id
113 from mtl_parameters
114 where organization_id = p_organization_id;
115 end if;
116
117 if (p_cost_source = 1 OR p_cost_source = 2) then
118 -- return the item cost from cst_item_costs for the provided combination of inputs
119 l_stmt_num := 20;
120
121 select item_cost
122 into x_item_cost
123 from cst_item_costs
124 where organization_id = p_organization_id
125 and inventory_item_id = p_item_id
126 and cost_type_id = l_cost_type_id;
127
128 elsif (p_cost_source = 3) then
129 -- return the item cost from the item definition for the provided organization and item
130 l_stmt_num := 30;
131
132 select list_price_per_unit
133 into x_item_cost
134 from mtl_system_items_b
135 where organization_id = p_organization_id
136 and inventory_item_id = p_item_id;
137
138 elsif (p_cost_source = 4) then
139 -- return the item cost as the average PO price of the last N=5 PO receipts
140 l_stmt_num := 40;
141
142 select avg(transaction_cost)
143 into x_item_cost
144 from mtl_material_transactions
145 where transaction_id in
146 ( select transaction_id
147 from
148 ( select transaction_id
149 from mtl_material_transactions
150 where transaction_action_id = 27
151 and transaction_source_type_id = 1
152 and organization_id = p_organization_id
153 and inventory_item_id = p_item_id
154 and transaction_quantity > 0
155 order by transaction_id desc)
156 where rownum <= CST_ItemResourceCosts_GRP.LAST_N_PO_RECEIPTS);
157
158 else
159 raise FND_API.g_exc_unexpected_error;
160 end if;
161
162 -- Also return the currency_code
163 l_stmt_num := 50;
164 select currency_code
165 into x_currency_code
166 from cst_organization_definitions
167 where organization_id = p_organization_id;
168
169 --- Standard check of p_commit
170 IF FND_API.to_Boolean(p_commit) THEN
171 COMMIT WORK;
172 END IF;
173
174 -- Standard Call to get message count and if count = 1, get message info
175 FND_MSG_PUB.Count_And_Get (
176 p_count => x_msg_count,
177 p_data => x_msg_data );
178
179 IF l_procLog THEN
180 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
181 ,'Get_ItemCost >>');
182 END IF;
183
184 EXCEPTION
185 WHEN FND_API.g_exc_error THEN
186 ROLLBACK TO Get_ItemCost_PVT;
187 x_return_status := FND_API.g_ret_sts_error;
188 FND_MSG_PUB.count_and_get
189 ( p_count => x_msg_count
190 , p_data => x_msg_data
191 );
192
193 WHEN FND_API.g_exc_unexpected_error THEN
194 ROLLBACK TO Get_ItemCost_PVT;
195 x_return_status := FND_API.g_ret_sts_unexp_error ;
196 FND_MSG_PUB.count_and_get
197 ( p_count => x_msg_count
198 , p_data => x_msg_data
199 );
200
201 WHEN NO_DATA_FOUND THEN
202 ROLLBACK TO Get_ItemCost_PVT;
203 x_return_status := FND_API.g_ret_sts_error;
204 if (l_stmt_num = 20) then
205 l_api_message := 'No item cost exists for the provided cost type, organization, and item.';
206 elsif (l_stmt_num = 30) then
207 l_api_message := 'No list price is defined in the item definition for this item and organization.';
208 elsif (l_stmt_num = 40) then
209 l_api_message := 'No PO receipts exist for this item and organization.';
210 else
211 l_api_message := 'Could not determine the currency code for the organization provided.';
212 end if;
213 IF l_errorLog THEN
214 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
215 ,'Get_ItemCost - '||l_api_message);
216 END IF;
217 x_msg_count := 1;
218 x_msg_data := l_api_message;
219
220 WHEN OTHERS THEN
221 ROLLBACK TO Get_ItemCost_PVT;
222 x_return_status := fnd_api.g_ret_sts_unexp_error ;
223
224 IF l_unexpLog THEN
225 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
226 ,'Get_ItemCost '||l_stmt_num||' : '||substr(SQLERRM,1,200));
227 END IF;
228
229 x_msg_count := 1;
230 x_msg_data := substr(SQLERRM,1,200);
231
232 END Get_ItemCost;
233
234
235 -------------------------------------------------------------------------------
236 -- API name : Get_ResourceRate
237 -- Type : Private
238 -- Function : Returns resource rate for the given resource,
239 -- cost type, and organization.
240 -- Parameters :
241 -- IN :
242 -- p_api_version IN NUMBER Required
243 -- p_init_msg_list IN VARCHAR2 Optional
244 -- Default = FND_API.G_FALSE
245 -- p_commit IN VARCHAR2 Optional
246 -- Default = FND_API.G_FALSE
247 -- p_validation_level IN NUMBER Optional
248 -- Default = FND_API.G_VALID_LEVEL_FULL
249 -- p_resource_id IN NUMBER Required
250 -- p_organization_id IN NUMBER Required
251 -- p_cost_type_id IN NUMBER Optional
252 -- Default = 0 -> will then default to AvgRates
253 --
254 -- OUT :
255 -- x_return_status OUT VARCHAR2(1)
256 -- x_msg_count OUT NUMBER
257 -- x_msg_data OUT VARCHAR2(2000)
258 -- x_resource_rate OUT NUMBER
259 -- x_currency_code OUT VARCHAR2(15)
260 -- - functional currency of p_organizaiton_id
261 -- Version :
262 -- Initial version 1.0
263 --
264 -- End of comments
265 -------------------------------------------------------------------------------
266 PROCEDURE Get_ResourceRate(
267 p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2,
269 p_commit IN VARCHAR2,
270 p_validation_level IN NUMBER,
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER,
273 x_msg_data OUT NOCOPY VARCHAR2,
274 p_resource_id IN NUMBER,
275 p_organization_id IN NUMBER,
276 p_cost_type_id IN NUMBER,
277 x_resource_rate OUT NOCOPY NUMBER,
278 x_currency_code OUT NOCOPY VARCHAR2
279 ) IS
280 l_api_name CONSTANT VARCHAR2(30) := 'Get_ResourceRate';
281 l_api_version CONSTANT NUMBER := 1.0;
282
283 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
284 l_msg_count NUMBER := 0;
285 l_msg_data VARCHAR2(8000) := '';
286 l_stmt_num NUMBER := 0;
287 l_api_message VARCHAR2(1000);
288
289 l_cost_type_id NUMBER;
290
291 l_module CONSTANT VARCHAR2(100) := G_LOG_HEAD ||'.'||l_api_name;
292 l_unexpLog CONSTANT BOOLEAN := (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL) AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
293 l_errorLog CONSTANT BOOLEAN := l_unexpLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
294 l_eventLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
295 l_procLog CONSTANT BOOLEAN := l_eventLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
296 l_stmtLog CONSTANT BOOLEAN := l_procLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
297
298 BEGIN
299 -- Standard start of API savepoint
300 SAVEPOINT Get_ResourceRate_PVT;
301
302 IF l_procLog THEN
303 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
304 ,'Get_ResourceRate <<');
305 END IF;
306
307 -- Standard call to check for call compatibility
308 IF NOT FND_API.Compatible_API_Call (
309 l_api_version,
310 p_api_version,
311 l_api_name,
312 G_PKG_NAME ) THEN
313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
314 END IF;
315
316 -- Initialize message list if p_init_msg_list is set to TRUE
317 IF FND_API.to_Boolean(p_init_msg_list) THEN
318 FND_MSG_PUB.initialize;
319 END IF;
320
321 -- Initialize API return status to success
322 x_return_status := FND_API.G_RET_STS_SUCCESS;
323
324 l_stmt_num := 10;
325
326 l_cost_type_id := p_cost_type_id;
327
328 -- Check the value passed in p_cost_type_id
329 -- If it's 0, then fetch the cost_type_id for the AvgRates cost type
330 if (p_cost_type_id = 0) then
331 select avg_rates_cost_type_id
332 into l_cost_type_id
333 from mtl_parameters
334 where organization_id = p_organization_id;
338 -- return the resource rate from cst_resource_costs for this combination of inputs
335 end if;
336
337 l_stmt_num := 20;
339 select resource_rate
340 into x_resource_rate
341 from cst_resource_costs
342 where organization_id = p_organization_id
343 and resource_id = p_resource_id
344 and cost_type_id = l_cost_type_id;
345
346 -- Also return the currency_code
347 l_stmt_num := 30;
348 select currency_code
349 into x_currency_code
350 from cst_organization_definitions
351 where organization_id = p_organization_id;
352
353 --- Standard check of p_commit
354 IF FND_API.to_Boolean(p_commit) THEN
355 COMMIT WORK;
356 END IF;
357
358 -- Standard Call to get message count and if count = 1, get message info
359 FND_MSG_PUB.Count_And_Get (
360 p_count => x_msg_count,
361 p_data => x_msg_data );
362
363 IF l_procLog THEN
364 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
365 ,'Get_ResourceRate >>');
366 END IF;
367
368 EXCEPTION
369 WHEN FND_API.g_exc_error THEN
370 ROLLBACK TO Get_ResourceRate_PVT;
371 x_return_status := FND_API.g_ret_sts_error;
372 FND_MSG_PUB.count_and_get
373 ( p_count => x_msg_count
374 , p_data => x_msg_data
375 );
376 WHEN FND_API.g_exc_unexpected_error THEN
377
378 ROLLBACK TO Get_ResourceRate_PVT;
379 x_return_status := FND_API.g_ret_sts_unexp_error ;
380 FND_MSG_PUB.count_and_get
381 ( p_count => x_msg_count
382 , p_data => x_msg_data
383 );
384
385 WHEN NO_DATA_FOUND THEN
386 ROLLBACK TO Get_ResourceRate_PVT;
387 x_return_status := FND_API.g_ret_sts_error;
388 if (l_stmt_num = 10) then
389 l_api_message := 'No AvgRates cost_type is setup in organization parameters, so no default cost type is available.';
390 elsif (l_stmt_num = 20) then
391 l_api_message := 'No resource rate is defined for the inputs provided.';
392 else
393 l_api_message := 'Could not determine the currency code for the organization provided.';
394 end if;
395 IF l_errorLog THEN
396 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
397 ,'Get_ResourceRate - '||l_api_message);
398 END IF;
399 x_msg_count := 1;
400 x_msg_data := l_api_message;
401
402 WHEN OTHERS THEN
403 ROLLBACK TO Get_ResourceRate_PVT;
404 x_return_status := fnd_api.g_ret_sts_unexp_error ;
405
406 IF l_unexpLog THEN
407 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
408 ,'Get_ResourceRate '||l_stmt_num||' : '||substr(SQLERRM,1,200));
409 END IF;
410
411 x_msg_count := 1;
412 x_msg_data := substr(SQLERRM,1,200);
413
414 END Get_ResourceRate;
415
416 END CST_ItemResourceCosts_GRP;
[Home] [Help]