[Home] [Help]
PACKAGE BODY: APPS.CST_INVENTORY_PUB
Source
1 PACKAGE BODY CST_Inventory_PUB AS
2 /* $Header: CSTPIVTB.pls 120.3 2005/08/24 11:09:51 awwang noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_Inventory_PUB';
5
6 PROCEDURE Calculate_InventoryValue(
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2,
9 p_commit IN VARCHAR2,
10 p_organization_id IN NUMBER,
11 p_onhand_value IN NUMBER,
12 p_intransit_value IN NUMBER,
13 p_receiving_value IN NUMBER,
14 p_valuation_date IN DATE,
15 p_cost_type_id IN NUMBER,
16 p_item_from IN VARCHAR2,
17 p_item_to IN VARCHAR2,
18 p_category_set_id IN NUMBER,
19 p_category_from IN VARCHAR2,
20 p_category_to IN VARCHAR2,
21 p_cost_group_from IN VARCHAR2,
22 p_cost_group_to IN VARCHAR2,
23 p_subinventory_from IN VARCHAR2,
24 p_subinventory_to IN VARCHAR2,
25 p_qty_by_revision IN NUMBER,
26 p_zero_cost_only IN NUMBER,
27 p_zero_qty IN NUMBER,
28 p_expense_item IN NUMBER,
29 p_expense_sub IN NUMBER,
30 p_unvalued_txns IN NUMBER,
31 p_receipt IN NUMBER,
32 p_shipment IN NUMBER,
33 p_detail IN NUMBER,
34 p_own IN NUMBER,
35 p_cost_enabled_only IN NUMBER,
36 p_one_time_item IN NUMBER,
37 p_include_period_end IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2
41 )
42 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_InventoryValue';
44 l_api_version CONSTANT NUMBER := 1.0;
45 l_msg_level_threshold NUMBER;
46 l_stmt_num NUMBER := 0;
47 l_schema VARCHAR2(30);
48 l_status VARCHAR2(1);
49 l_industry VARCHAR2(1);
50 l_gather_stats NUMBER;
51 BEGIN
52 -- Standard Start of API savepoint
53 SAVEPOINT Calculate_InventoryValue_PUB;
54
55 -- Check for call compatibility
56 IF NOT FND_API.Compatible_API_Call
57 ( p_current_version_number => l_api_version,
58 p_caller_version_number => p_api_version,
59 p_api_name => l_api_name,
60 p_pkg_name => G_PKG_NAME
61 )
62 THEN
63 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64 END IF;
65
66 -- Initialize API message list if necessary
67 IF FND_API.To_Boolean(p_init_msg_list)
68 THEN
69 FND_MSG_PUB.Initialize;
70 END IF;
71
72 -- Check for message level threshold
73 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
74 x_msg_count := l_msg_level_threshold;
75
76 -- Check for the value of p_commit
77 IF NOT FND_API.To_Boolean(p_commit)
78 THEN
79 FND_MSG_PUB.Add_Exc_Msg(
80 p_pkg_name => G_PKG_NAME,
81 p_procedure_name => l_api_name,
82 p_error_text => 'This API should not be called with p_commit set to false'
83 );
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86
87 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
88 THEN
89 FND_MSG_PUB.Add_Exc_Msg(
90 p_pkg_name => G_PKG_NAME,
91 p_procedure_name => l_api_name,
92 p_error_text => SUBSTR(
93 l_stmt_num||':'||
94 p_organization_id||','||
95 p_onhand_value||','||
96 p_intransit_value||','||
97 p_receiving_value||','||
98 p_valuation_date||','||
99 p_cost_type_id||','||
100 p_item_from||','||
101 p_item_to||','||
102 p_category_set_id||','||
103 p_category_from||','||
104 p_category_to||','||
105 p_cost_group_from||','||
106 p_cost_group_to||','||
107 p_subinventory_from||','||
108 p_subinventory_to||','||
109 p_qty_by_revision||','||
110 p_zero_cost_only||','||
111 p_zero_qty||','||
112 p_expense_item||','||
113 p_expense_sub||','||
114 p_unvalued_txns||','||
115 p_receipt||','||
116 p_shipment||','||
117 p_cost_enabled_only||','||
118 p_one_time_item||','||
119 p_include_period_end,
120 1,
121 240
122 )
123 );
124 END IF;
125
126 -- Find the items that match the specifications
127 l_stmt_num := 10;
128 CST_Inventory_PVT.Populate_ItemList(
129 p_api_version => 1.0,
130 p_organization_id => p_organization_id,
131 p_cost_type_id => p_cost_type_id,
132 p_item_from => p_item_from,
133 p_item_to => p_item_to,
134 p_category_set_id => p_category_set_id,
135 p_category_from => p_category_from,
136 p_category_to => p_category_to,
137 p_zero_cost_only => p_zero_cost_only,
138 p_expense_item => p_expense_item,
139 p_cost_enabled_only => p_cost_enabled_only,
140 p_one_time_item => p_one_time_item,
141 x_return_status => x_return_status
142 );
143
144 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
145 THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148
149 -- Find the cost groups that match the specifications
150 l_stmt_num := 20;
151 CST_Inventory_PVT.Populate_CostGroupList(
152 p_api_version => 1.0,
153 p_organization_id => p_organization_id,
154 p_cost_group_from => p_cost_group_from,
155 p_cost_group_to => p_cost_group_to,
156 p_own => p_own,
157 x_return_status => x_return_status
158 );
159
160 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
161 THEN
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 END IF;
164
165 -- Find the subinventories that match the specifications
166 l_stmt_num := 30;
167 CST_Inventory_PVT.Populate_SubinventoryList(
168 p_api_version => 1.0,
169 p_organization_id => p_organization_id,
170 p_subinventory_from => p_subinventory_from,
171 p_subinventory_to => p_subinventory_to,
172 p_expense_sub => p_expense_sub,
173 x_return_status => x_return_status
174 );
175
176 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
177 THEN
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180
181 l_stmt_num := 32;
182 l_gather_stats := CST_Utility_PUB.check_Db_Version(
183 p_api_version => 1.0,
184 x_return_status => x_return_status,
185 x_msg_count => x_msg_count,
186 x_msg_data => x_msg_data
187 );
188 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
189 THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193 IF l_gather_stats = 1
194 THEN
195 l_stmt_num := 35;
196 IF NOT FND_INSTALLATION.GET_APP_INFO('BOM', l_status, l_industry, l_schema)
197 THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200
201 IF l_schema IS NOT NULL
202 THEN
203 l_stmt_num := 37;
204 FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_ITEM_LIST_TEMP');
205 l_stmt_num := 38;
206 FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_CG_LIST_TEMP');
207 l_stmt_num := 39;
208 FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_SUB_LIST_TEMP');
209 END IF;
210
211 -- Re-establish the savepoint because gather statistics does an implicit commit
212 SAVEPOINT Calculate_InventoryValue_PUB;
213 END IF;
214
215 -- Calculate the onhand quantity of matching items in the cost groups and subinventories
216 IF p_onhand_value = 1
217 THEN
218 l_stmt_num := 40;
219 CST_Inventory_PVT.Calculate_OnhandQty(
220 p_api_version => 1.0,
221 p_organization_id => p_organization_id,
222 p_valuation_date => p_valuation_date,
223 p_qty_by_revision => p_qty_by_revision,
224 p_zero_qty => p_zero_qty,
225 p_unvalued_txns => p_unvalued_txns,
226 x_return_status => x_return_status
227 );
228
229 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
230 THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END IF;
233 END IF;
234
235 -- Calculate the intransit quantity of matching items in the cost groups
236 IF p_intransit_value = 1
237 THEN
238 l_stmt_num := 50;
239 CST_Inventory_PVT.Calculate_IntransitQty(
240 p_api_version => 1.0,
241 p_organization_id => p_organization_id,
242 p_valuation_date => p_valuation_date,
243 p_receipt => p_receipt,
244 p_shipment => p_shipment,
245 p_detail => p_detail,
246 p_own => p_own,
247 p_unvalued_txns => p_unvalued_txns,
248 x_return_status => x_return_status
249 );
250
251 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
252 THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255 END IF;
256
257 -- Calculate the receiving quantity of matching items
258 IF p_receiving_value = 1
259 THEN
260 l_stmt_num := 60;
261 CST_Inventory_PVT.Calculate_ReceivingQty(
262 p_api_version => 1.0,
263 p_organization_id => p_organization_id,
264 p_valuation_date => p_valuation_date,
265 p_qty_by_revision => p_qty_by_revision,
266 p_include_period_end => p_include_period_end,
267 x_return_status => x_return_status
268 );
269
270 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
271 THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 END IF;
275
276 -- Gather statistics on the global temporary table
277 -- CST_INV_QTY_TEMP. Although gather statistics does
278 -- not actually gather statistics in 8i environments,
279 -- this call helps customers on 9i.
280
281 l_stmt_num := 61;
282 l_gather_stats := CST_Utility_PUB.check_Db_Version(
283 p_api_version => 1.0,
284 x_return_status => x_return_status,
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data
287 );
288 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 IF l_gather_stats = 1
294 THEN
295 l_stmt_num := 63;
296 IF NOT FND_INSTALLATION.GET_APP_INFO('BOM', l_status, l_industry, l_schema)
297 THEN
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300
301 IF l_schema IS NOT NULL
302 THEN
303 l_stmt_num := 66;
304 FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_INV_QTY_TEMP');
305 END IF;
306
307 -- Re-establish the savepoint because gather statistics does an implicit commit
308 SAVEPOINT Calculate_InventoryValue_PUB;
309 END IF;
310
311 -- Calculate the costs
312 l_stmt_num := 70;
313 CST_Inventory_PVT.Calculate_InventoryCost(
314 p_api_version => 1.0,
315 p_valuation_date => p_valuation_date,
316 p_organization_id => p_organization_id,
317 x_return_status => x_return_status
318 );
319
320 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
321 THEN
322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323 END IF;
324
325 IF l_gather_stats = 1
326 THEN
327 IF l_schema IS NOT NULL
328 THEN
329 l_stmt_num := 75;
330 FND_STATS.GATHER_TABLE_STATS(l_schema, 'CST_INV_COST_TEMP');
331 END IF;
332 END IF;
333
334 l_stmt_num := 80;
335 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
336 THEN
337 FND_MSG_PUB.Add_Exc_Msg(
338 p_pkg_name => G_PKG_NAME,
339 p_procedure_name => l_api_name,
340 p_error_text => l_stmt_num||': Finished calculating inventory value'
341 );
342 END IF;
343
344 EXCEPTION
345 WHEN FND_API.G_EXC_ERROR THEN
346 ROLLBACK TO Calculate_InventoryValue_PUB;
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
349 ROLLBACK TO Calculate_InventoryValue_PUB;
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 WHEN OTHERS THEN
352 ROLLBACK TO Calculate_InventoryValue_PUB;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
355 THEN
359 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
356 FND_MSG_PUB.Add_Exc_Msg(
357 p_pkg_name => G_PKG_NAME,
358 p_procedure_name => l_api_name,
360 );
361 END IF;
362
363 END Calculate_InventoryValue;
364 END CST_Inventory_PUB;