[Home] [Help]
PACKAGE BODY: APPS.INV_CONSIGNED_VALIDATIONS_GRP
Source
1 PACKAGE BODY INV_CONSIGNED_VALIDATIONS_GRP AS
2 /* $Header: INVVMIGB.pls 120.1 2005/06/16 15:31:39 appldev $ */
3
4
5 /*------------------------*
6 * GET_CONSIGNED_QUANTITY *
7 *------------------------*/
8 /** This API will return VMI/consigned Quantity */
9
10
11 PROCEDURE GET_CONSIGNED_QUANTITY(
12 p_api_version_number IN NUMBER
13 , p_init_msg_lst IN VARCHAR2
14 , x_return_status OUT NOCOPY VARCHAR2
15 , x_msg_count OUT NOCOPY NUMBER
16 , x_msg_data OUT NOCOPY VARCHAR2
17 , p_tree_mode IN NUMBER
18 , p_organization_id IN NUMBER
19 , p_owning_org_id IN NUMBER
20 , p_planning_org_id IN NUMBER
21 , p_inventory_item_id IN NUMBER
22 , p_is_revision_control IN VARCHAR2
23 , p_is_lot_control IN VARCHAR2
24 , p_is_serial_control IN VARCHAR2
25 , p_revision IN VARCHAR2
26 , p_lot_number IN VARCHAR2
27 , p_lot_expiration_date IN DATE
28 , p_subinventory_code IN VARCHAR2
29 , p_locator_id IN NUMBER
30 , p_source_type_id IN NUMBER
31 , p_demand_source_line_id IN NUMBER
32 , p_demand_source_header_id IN NUMBER
33 , p_demand_source_name IN VARCHAR2
34 , p_onhand_source IN NUMBER
35 , p_cost_group_id IN NUMBER
36 , p_query_mode IN NUMBER
37 , x_qoh OUT NOCOPY NUMBER
38 , x_att OUT NOCOPY NUMBER
39 ) IS
40
41 -- invConv changes begin : Calling the overloaded API
42 l_sqoh NUMBER;
43 l_satt NUMBER;
44 BEGIN
45
46 GET_CONSIGNED_QUANTITY(
47 p_api_version_number => p_api_version_number
48 , p_init_msg_lst => p_init_msg_lst
49 , x_return_status => x_return_status
50 , x_msg_count => x_msg_count
51 , x_msg_data => x_msg_data
52 , p_tree_mode => p_tree_mode
53 , p_organization_id => p_organization_id
54 , p_owning_org_id => p_owning_org_id
55 , p_planning_org_id => p_planning_org_id
56 , p_inventory_item_id => p_inventory_item_id
57 , p_is_revision_control => p_is_revision_control
58 , p_is_lot_control => p_is_lot_control
59 , p_is_serial_control => p_is_serial_control
60 , p_revision => p_revision
61 , p_lot_number => p_lot_number
62 , p_lot_expiration_date => p_lot_expiration_date
63 , p_subinventory_code => p_subinventory_code
64 , p_locator_id => p_locator_id
65 , p_grade_code => NULL -- invConv change
66 , p_source_type_id => p_source_type_id
67 , p_demand_source_line_id => p_demand_source_line_id
68 , p_demand_source_header_id => p_demand_source_header_id
69 , p_demand_source_name => p_demand_source_name
70 , p_onhand_source => p_onhand_source
71 , p_cost_group_id => p_cost_group_id
72 , p_query_mode => p_query_mode
73 , x_qoh => x_qoh
74 , x_att => x_att
75 , x_sqoh => l_sqoh -- invConv change
76 , x_satt => l_satt); -- invConv change
77 -- invConv changes end.
78
79 END GET_CONSIGNED_QUANTITY;
80
81 -- invConv changes begin : Overloaded API
82 PROCEDURE GET_CONSIGNED_QUANTITY(
83 p_api_version_number IN NUMBER
84 , p_init_msg_lst IN VARCHAR2
85 , x_return_status OUT NOCOPY VARCHAR2
86 , x_msg_count OUT NOCOPY NUMBER
87 , x_msg_data OUT NOCOPY VARCHAR2
88 , p_tree_mode IN NUMBER
89 , p_organization_id IN NUMBER
90 , p_owning_org_id IN NUMBER
91 , p_planning_org_id IN NUMBER
92 , p_inventory_item_id IN NUMBER
93 , p_is_revision_control IN VARCHAR2
94 , p_is_lot_control IN VARCHAR2
95 , p_is_serial_control IN VARCHAR2
96 , p_revision IN VARCHAR2
97 , p_lot_number IN VARCHAR2
98 , p_lot_expiration_date IN DATE
99 , p_subinventory_code IN VARCHAR2
100 , p_locator_id IN NUMBER
101 , p_grade_code IN VARCHAR2 -- invConv changes
102 , p_source_type_id IN NUMBER
103 , p_demand_source_line_id IN NUMBER
104 , p_demand_source_header_id IN NUMBER
105 , p_demand_source_name IN VARCHAR2
106 , p_onhand_source IN NUMBER
107 , p_cost_group_id IN NUMBER
108 , p_query_mode IN NUMBER
109 , x_qoh OUT NOCOPY NUMBER
110 , x_att OUT NOCOPY NUMBER
111 , x_sqoh OUT NOCOPY NUMBER -- invConv changes
112 , x_satt OUT NOCOPY NUMBER -- invConv changes
113 ) IS
114
115 l_api_version_number CONSTANT NUMBER := 1.0;
116 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONSIGNED_QUANTITY';
117 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
118 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
119
120 BEGIN
121 -- Standard call to check for call compatibility
122 IF NOT fnd_api.compatible_api_call(l_api_version_number
123 , p_api_version_number
124 , l_api_name
125 , G_PKG_NAME
126 ) THEN
127 RAISE fnd_api.g_exc_unexpected_error;
128 END IF;
129
130 -- Initialize message list.
131 IF fnd_api.to_boolean(p_init_msg_lst) THEN
132 fnd_msg_pub.initialize;
133 END IF;
134
135 IF (l_debug = 1) THEN
136 inv_log_util.trace('****** GET_CONSIGNED_QUANTITIES *******','CONSIGNED_VALIDATIONS_GRP',9);
137 inv_log_util.trace(' Org, Owning_org, planning_org='|| p_organization_id ||','
138 || p_owning_org_id ||','||p_planning_org_id,'CONSIGNED_VALIDATIONS_GRP',9);
139 inv_log_util.trace(' Item, Is Rev, Lot, Serial controlled: '||p_inventory_item_id|| ','||
140 p_is_revision_control ||','|| p_is_lot_control ||','|| p_is_serial_control,'CONSIGNED_VALIDATIONS_GRP',9);
141 inv_log_util.trace(' Rev, Lot, LotExpDate: '|| p_revision ||','||p_lot_number ||','|| p_lot_expiration_date,'CONSIGNED_VALIDATIONS_GRP',9);
142 inv_log_util.trace(' Sub, Loc: '||p_subinventory_code||','||p_locator_id,'CONSIGNED_VALIDATIONS_GRP',9);
143 inv_log_util.trace(' SourceTypeID, DemdSrcLineID, DemdSrcHdrID, DemdSrcName: ' ||
144 p_source_type_id ||',' ||p_demand_source_line_id || ','||
145 p_demand_source_header_id || ',' || p_demand_source_name,'CONSIGNED_VALIDATIONS_GRP',9);
146 inv_log_util.trace(' OnhandSource, CstGroupID, QueryMode: '|| p_onhand_source || ','||
147 p_cost_group_id ||',' ||p_query_mode,'CONSIGNED_VALIDATIONS_GRP',9);
148 END IF;
149
150 INV_CONSIGNED_VALIDATIONS.GET_CONSIGNED_QUANTITY(
151 x_return_status => l_return_status
152 , x_return_msg => x_msg_data
153 , p_tree_mode => p_tree_mode
154 , p_organization_id => p_organization_id
155 , p_owning_org_id => p_owning_org_id
156 , p_planning_org_id => p_planning_org_id
157 , p_inventory_item_id => p_inventory_item_id
158 , p_is_revision_control => p_is_revision_control
159 , p_is_lot_control => p_is_lot_control
160 , p_is_serial_control => p_is_serial_control
161 , p_revision => p_revision
162 , p_lot_number => p_lot_number
163 , p_lot_expiration_date => p_lot_expiration_date
164 , p_subinventory_code => p_subinventory_code
165 , p_locator_id => p_locator_id
166 , p_grade_code => p_grade_code -- invConv change
167 , p_source_type_id => p_source_type_id
168 , p_demand_source_line_id => p_demand_source_line_id
169 , p_demand_source_header_id=> p_demand_source_header_id
170 , p_demand_source_name => p_demand_source_name
171 , p_onhand_source => p_onhand_source
172 , p_cost_group_id => p_cost_group_id
173 , p_query_mode => p_query_mode
174 , x_qoh => x_qoh
175 , x_att => x_att
176 , x_sqoh => x_sqoh -- invConv change
177 , x_satt => x_satt -- invConv change
178 );
179
180
181 IF l_return_status = fnd_api.g_ret_sts_error THEN
182 RAISE fnd_api.g_exc_error;
183 END IF ;
184
185 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
186 RAISE fnd_api.g_exc_unexpected_error;
187 END IF;
188
189 x_return_status := l_return_status;
190
191 IF(l_debug=1)THEN
192 inv_log_util.trace('Finished calling INV_CONSIGNED_VALIDATIONS.GET_CONSIGNED_QUANTITY','CONSIGNED_VALIDATIONS',9);
193 END IF;
194
195 EXCEPTION
196 WHEN fnd_api.g_exc_error THEN
197 x_return_status := fnd_api.g_ret_sts_error;
198
199 -- Get message count and data
200 fnd_msg_pub.count_and_get
201 ( p_count => x_msg_count
202 , p_data => x_msg_data
203 );
204
205 WHEN fnd_api.g_exc_unexpected_error THEN
206 x_return_status := fnd_api.g_ret_sts_unexp_error ;
207
208 -- Get message count and data
209 fnd_msg_pub.count_and_get
210 ( p_count => x_msg_count
211 , p_data => x_msg_data
212 );
213
214 WHEN OTHERS THEN
215 x_return_status := fnd_api.g_ret_sts_unexp_error ;
216
217 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
218 THEN
219 fnd_msg_pub.add_exc_msg
220 ( g_pkg_name
221 , l_api_name
222 );
223 END IF;
224
225 -- Get message count and data
226 fnd_msg_pub.count_and_get
227 ( p_count => x_msg_count
228 , p_data => x_msg_data
229 );
230 END get_consigned_quantity;
231
232
233 /* This API returns the onhand quantity for planning purpose */
234
235 PROCEDURE GET_PLANNING_QUANTITY(
236 p_api_version_number IN NUMBER
237 , p_init_msg_lst IN VARCHAR2
238 , x_return_status OUT NOCOPY VARCHAR2
239 , x_msg_count OUT NOCOPY NUMBER
240 , x_msg_data OUT NOCOPY VARCHAR2
241 , p_include_nonnet IN NUMBER
242 , p_level IN NUMBER
243 , p_org_id IN NUMBER
244 , p_subinv IN VARCHAR2
245 , p_item_id IN NUMBER
246 , x_planning_qty OUT NOCOPY NUMBER
247 ) IS
248
249 -- invConv changes begion : calling the overloaded API
250 l_planning_sqty NUMBER;
251 BEGIN
252 GET_PLANNING_QUANTITY(
253 p_api_version_number => p_api_version_number
254 , p_init_msg_lst => p_init_msg_lst
255 , x_return_status => x_return_status
256 , x_msg_count => x_msg_count
257 , x_msg_data => x_msg_data
258 , p_include_nonnet => p_include_nonnet
259 , p_level => p_level
260 , p_org_id => p_org_id
261 , p_subinv => p_subinv
262 , p_item_id => p_item_id
263 , p_grade_code => NULL
264 , x_planning_qty => x_planning_qty
265 , x_planning_sqty => l_planning_sqty);
266 -- invConv changes end.
267
268 END GET_PLANNING_QUANTITY;
269
270 -- invConv changes begin : new overloaded API
271 PROCEDURE GET_PLANNING_QUANTITY(
272 p_api_version_number IN NUMBER
273 , p_init_msg_lst IN VARCHAR2
274 , x_return_status OUT NOCOPY VARCHAR2
275 , x_msg_count OUT NOCOPY NUMBER
276 , x_msg_data OUT NOCOPY VARCHAR2
277 , p_include_nonnet IN NUMBER
278 , p_level IN NUMBER
279 , p_org_id IN NUMBER
280 , p_subinv IN VARCHAR2
281 , p_item_id IN NUMBER
282 , p_grade_code IN VARCHAR2 -- invConv change
283 , x_planning_qty OUT NOCOPY NUMBER
284 , x_planning_sqty OUT NOCOPY NUMBER -- invConv change
285 ) IS
286
287
288 l_api_version_number CONSTANT NUMBER := 1.0;
289 l_api_name CONSTANT VARCHAR2(30) := 'GET_PLANNING_QUANTITY';
290 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
291 l_qty NUMBER := 0;
292 l_sqty NUMBER := NULL;
293 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
294
295 BEGIN
296 -- Standard call to check for call compatibility
297 IF NOT fnd_api.compatible_api_call(l_api_version_number
298 , p_api_version_number
299 , l_api_name
300 , G_PKG_NAME
301 ) THEN
302 RAISE fnd_api.g_exc_unexpected_error;
303 END IF;
304
305 -- Initialize message list.
306 IF fnd_api.to_boolean(p_init_msg_lst) THEN
307 fnd_msg_pub.initialize;
308 END IF;
309
310 IF (l_debug=1) THEN
311 inv_log_util.trace('*** GET_PLANNING_QUANTITY ***','CONSIGNED_VALIDATIONS_GRP',9);
312 inv_log_util.trace('p_include_nonnet=' || to_char(p_include_nonnet) ||
313 ', p_level=' || to_char(p_level) ||
314 ', p_org_id=' || to_char(p_org_id) ||
315 ', p_subinv=' || p_subinv ||
316 ', p_item_id=' || to_char(p_item_id)
317 , 'CONSIGNED_VALIDATIONS_GRP', 9);
318 END IF;
319
320 -- invConv changes begin : calling API that returns secondary qty too
321 -- l_qty := INV_CONSIGNED_VALIDATIONS.GET_PLANNING_QUANTITY(
322 -- P_INCLUDE_NONNET => P_INCLUDE_NONNET
323 -- , P_LEVEL => P_LEVEL
324 -- , P_ORG_ID => P_ORG_ID
325 -- , P_SUBINV => P_SUBINV
326 -- , P_ITEM_ID => P_ITEM_ID);
327 --
328 INV_CONSIGNED_VALIDATIONS.GET_PLANNING_QUANTITY(
329 P_INCLUDE_NONNET => P_INCLUDE_NONNET
330 , P_LEVEL => P_LEVEL
331 , P_ORG_ID => P_ORG_ID
332 , P_SUBINV => P_SUBINV
333 , P_ITEM_ID => P_ITEM_ID
334 , P_GRADE_CODE => P_GRADE_CODE
335 , X_QOH => l_qty
336 , X_SQOH => l_sqty);
337 -- invConv changes end.
338
339 x_return_status := l_return_status;
340 x_planning_qty := l_qty;
341 x_planning_sqty := l_sqty; -- invConv changes
342
343 EXCEPTION
344 WHEN fnd_api.g_exc_error THEN
345 x_return_status := fnd_api.g_ret_sts_error;
346
347 -- Get message count and data
348 fnd_msg_pub.count_and_get
349 ( p_count => x_msg_count
350 , p_data => x_msg_data
351 );
352
353 WHEN fnd_api.g_exc_unexpected_error THEN
354 x_return_status := fnd_api.g_ret_sts_unexp_error ;
355
356 -- Get message count and data
357 fnd_msg_pub.count_and_get
358 ( p_count => x_msg_count
359 , p_data => x_msg_data
360 );
361
362 WHEN OTHERS THEN
363 x_return_status := fnd_api.g_ret_sts_unexp_error ;
364
365 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
366 THEN
367 fnd_msg_pub.add_exc_msg
368 ( g_pkg_name
369 , l_api_name
370 );
371 END IF;
372
373 -- Get message count and data
374 fnd_msg_pub.count_and_get
375 ( p_count => x_msg_count
376 , p_data => x_msg_data
377 );
378
379 END GET_PLANNING_QUANTITY;
380
381 --Bug 4239469: Added this new procedure to get the available qty
382 /* This API returns the onhand quantity for planning/atp purpose */
383
384 PROCEDURE get_planning_sd_quantity
385 (
386 p_api_version_number IN NUMBER
387 , p_init_msg_lst IN VARCHAR2
388 , x_return_status OUT NOCOPY VARCHAR2
389 , x_msg_count OUT NOCOPY NUMBER
390 , x_msg_data OUT NOCOPY VARCHAR2
391 , p_onhand_source IN NUMBER
392 , p_org_id IN NUMBER
393 , p_item_id IN NUMBER
394 , x_planning_qty OUT NOCOPY NUMBER
395 ) IS
396
397 l_api_version_number CONSTANT NUMBER := 1.0;
398 l_api_name CONSTANT VARCHAR2(30) := 'GET_PLANNING_SD_QUANTITY';
399 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
400 l_qty NUMBER := 0;
401 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
402
403 BEGIN
404 -- Standard call to check for call compatibility
405 IF NOT fnd_api.compatible_api_call
406 (l_api_version_number
407 , p_api_version_number
408 , l_api_name
409 , G_PKG_NAME
410 ) THEN
411 RAISE fnd_api.g_exc_unexpected_error;
412 END IF;
413
414 -- Initialize message list.
415 IF fnd_api.to_boolean(p_init_msg_lst) THEN
416 fnd_msg_pub.initialize;
417 END IF;
418
419 IF (l_debug=1) THEN
420 inv_log_util.trace('*** GET_PLANNING_SD_QUANTITY ***','CONSIGNED_VALIDATIONS_GRP',9);
421 inv_log_util.trace('p_onhand_source=' || to_char(p_onhand_source) ||
422 ', p_org_id=' || to_char(p_org_id) ||
423 ', p_item_id=' || to_char(p_item_id)
424 , 'CONSIGNED_VALIDATIONS_GRP', 9);
425 END IF;
426
427 l_qty := INV_CONSIGNED_VALIDATIONS.get_planning_sd_quantity
428 (
429 P_ONHAND_SOURCE => P_ONHAND_SOURCE
430 , P_ORG_ID => P_ORG_ID
431 , P_ITEM_ID => P_ITEM_ID);
432
433 x_return_status := l_return_status;
434 x_planning_qty := l_qty;
435
436 EXCEPTION
437 WHEN fnd_api.g_exc_error THEN
438 x_return_status := fnd_api.g_ret_sts_error;
439
440 -- Get message count and data
441 fnd_msg_pub.count_and_get
442 ( p_count => x_msg_count
443 , p_data => x_msg_data
444 );
445
446 WHEN fnd_api.g_exc_unexpected_error THEN
447 x_return_status := fnd_api.g_ret_sts_unexp_error ;
448
449 -- Get message count and data
450 fnd_msg_pub.count_and_get
451 ( p_count => x_msg_count
452 , p_data => x_msg_data
453 );
454
455 WHEN OTHERS THEN
456 x_return_status := fnd_api.g_ret_sts_unexp_error ;
457
458 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
459 THEN
460 fnd_msg_pub.add_exc_msg
461 ( g_pkg_name
462 , l_api_name
463 );
464 END IF;
465
466 -- Get message count and data
467 fnd_msg_pub.count_and_get
468 ( p_count => x_msg_count
469 , p_data => x_msg_data
470 );
471
472 END GET_PLANNING_SD_QUANTITY;
473
474
475 END INV_CONSIGNED_VALIDATIONS_GRP;