[Home] [Help]
PACKAGE BODY: APPS.CST_INVENTORY_PUB
Source
1 PACKAGE BODY CST_Inventory_PUB AS
2 /* $Header: CSTPIVTB.pls 120.3.12010000.2 2010/01/08 19:05:58 fayang ship $ */
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 BEGIN
48 -- Standard Start of API savepoint
49 SAVEPOINT Calculate_InventoryValue_PUB;
50
51 -- Check for call compatibility
52 IF NOT FND_API.Compatible_API_Call
53 ( p_current_version_number => l_api_version,
54 p_caller_version_number => p_api_version,
55 p_api_name => l_api_name,
56 p_pkg_name => G_PKG_NAME
57 )
58 THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- Initialize API message list if necessary
63 IF FND_API.To_Boolean(p_init_msg_list)
64 THEN
65 FND_MSG_PUB.Initialize;
66 END IF;
67
68 -- Check for message level threshold
69 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
70 x_msg_count := l_msg_level_threshold;
71
72 -- Check for the value of p_commit
73 IF NOT FND_API.To_Boolean(p_commit)
74 THEN
75 FND_MSG_PUB.Add_Exc_Msg(
76 p_pkg_name => G_PKG_NAME,
77 p_procedure_name => l_api_name,
78 p_error_text => 'This API should not be called with p_commit set to false'
79 );
80 RAISE FND_API.G_EXC_ERROR;
81 END IF;
82
83 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
84 THEN
85 FND_MSG_PUB.Add_Exc_Msg(
86 p_pkg_name => G_PKG_NAME,
87 p_procedure_name => l_api_name,
88 p_error_text => SUBSTR(
89 l_stmt_num||':'||
90 p_organization_id||','||
91 p_onhand_value||','||
92 p_intransit_value||','||
93 p_receiving_value||','||
94 p_valuation_date||','||
95 p_cost_type_id||','||
96 p_item_from||','||
97 p_item_to||','||
98 p_category_set_id||','||
99 p_category_from||','||
100 p_category_to||','||
101 p_cost_group_from||','||
102 p_cost_group_to||','||
103 p_subinventory_from||','||
104 p_subinventory_to||','||
105 p_qty_by_revision||','||
106 p_zero_cost_only||','||
107 p_zero_qty||','||
108 p_expense_item||','||
109 p_expense_sub||','||
110 p_unvalued_txns||','||
111 p_receipt||','||
112 p_shipment||','||
113 p_cost_enabled_only||','||
114 p_one_time_item||','||
115 p_include_period_end,
116 1,
117 240
118 )
119 );
120 END IF;
121
122 -- Find the items that match the specifications
123 l_stmt_num := 10;
124 CST_Inventory_PVT.Populate_ItemList(
125 p_api_version => 1.0,
126 p_organization_id => p_organization_id,
127 p_cost_type_id => p_cost_type_id,
128 p_item_from => p_item_from,
129 p_item_to => p_item_to,
130 p_category_set_id => p_category_set_id,
131 p_category_from => p_category_from,
132 p_category_to => p_category_to,
133 p_zero_cost_only => p_zero_cost_only,
134 p_expense_item => p_expense_item,
135 p_cost_enabled_only => p_cost_enabled_only,
136 p_one_time_item => p_one_time_item,
137 x_return_status => x_return_status
138 );
139
140 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
141 THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END IF;
144
145 -- Find the cost groups that match the specifications
146 l_stmt_num := 20;
147 CST_Inventory_PVT.Populate_CostGroupList(
148 p_api_version => 1.0,
149 p_organization_id => p_organization_id,
150 p_cost_group_from => p_cost_group_from,
151 p_cost_group_to => p_cost_group_to,
152 p_own => p_own,
153 x_return_status => x_return_status
154 );
155
156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
157 THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 -- Find the subinventories that match the specifications
162 l_stmt_num := 30;
163 CST_Inventory_PVT.Populate_SubinventoryList(
164 p_api_version => 1.0,
165 p_organization_id => p_organization_id,
166 p_subinventory_from => p_subinventory_from,
167 p_subinventory_to => p_subinventory_to,
168 p_expense_sub => p_expense_sub,
169 x_return_status => x_return_status
170 );
171
172 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
173 THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177
178 -- Calculate the onhand quantity of matching items in the cost groups and subinventories
179 IF p_onhand_value = 1
180 THEN
181 l_stmt_num := 40;
182 CST_Inventory_PVT.Calculate_OnhandQty(
183 p_api_version => 1.0,
184 p_organization_id => p_organization_id,
185 p_valuation_date => p_valuation_date,
186 p_qty_by_revision => p_qty_by_revision,
187 p_zero_qty => p_zero_qty,
188 p_unvalued_txns => p_unvalued_txns,
189 x_return_status => x_return_status
190 );
191
192 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
193 THEN
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195 END IF;
196 END IF;
197
198 -- Calculate the intransit quantity of matching items in the cost groups
199 IF p_intransit_value = 1
200 THEN
201 l_stmt_num := 50;
202 CST_Inventory_PVT.Calculate_IntransitQty(
203 p_api_version => 1.0,
204 p_organization_id => p_organization_id,
205 p_valuation_date => p_valuation_date,
206 p_receipt => p_receipt,
207 p_shipment => p_shipment,
208 p_detail => p_detail,
209 p_own => p_own,
210 p_unvalued_txns => p_unvalued_txns,
211 x_return_status => x_return_status
212 );
213
214 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
215 THEN
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 END IF;
218 END IF;
219
220 -- Calculate the receiving quantity of matching items
221 IF p_receiving_value = 1
222 THEN
223 l_stmt_num := 60;
224 CST_Inventory_PVT.Calculate_ReceivingQty(
225 p_api_version => 1.0,
226 p_organization_id => p_organization_id,
227 p_valuation_date => p_valuation_date,
228 p_qty_by_revision => p_qty_by_revision,
229 p_include_period_end => p_include_period_end,
230 x_return_status => x_return_status
231 );
232
233 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
234 THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END IF;
237 END IF;
238
239
240 -- Calculate the costs
241 l_stmt_num := 70;
242 CST_Inventory_PVT.Calculate_InventoryCost(
243 p_api_version => 1.0,
244 p_valuation_date => p_valuation_date,
245 p_organization_id => p_organization_id,
246 x_return_status => x_return_status
247 );
248
249 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
250 THEN
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 END IF;
253
254
255 l_stmt_num := 80;
256 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
257 THEN
258 FND_MSG_PUB.Add_Exc_Msg(
259 p_pkg_name => G_PKG_NAME,
260 p_procedure_name => l_api_name,
261 p_error_text => l_stmt_num||': Finished calculating inventory value'
262 );
263 END IF;
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 ROLLBACK TO Calculate_InventoryValue_PUB;
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
270 ROLLBACK TO Calculate_InventoryValue_PUB;
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 WHEN OTHERS THEN
273 ROLLBACK TO Calculate_InventoryValue_PUB;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
276 THEN
277 FND_MSG_PUB.Add_Exc_Msg(
278 p_pkg_name => G_PKG_NAME,
279 p_procedure_name => l_api_name,
280 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
281 );
282 END IF;
283
284 END Calculate_InventoryValue;
285 END CST_Inventory_PUB;