1 PACKAGE inv_quantity_tree_pvt AUTHID CURRENT_USER as
2 /* $Header: INVVQTTS.pls 120.5.12020000.1 2012/06/26 14:14:57 appldev ship $*/
3
4 -- synonyms used in this program
5 -- qoh quantity on hand
6 -- rqoh reservable quantity on hand
7 -- qr quantity reserved
8 -- qs quantity suggested
9 -- att available to transact
10 -- atr available to reserve
11 -- sqoh secondary quantity on hand
12 -- srqoh secondary reservable quantity on hand
13 -- sqr secondary quantity reserved
14 -- sqs secondare quantity suggested
15 -- satt secondary available to transact
16 -- satr secondary available to reserve
17
18 /******************************************************************
19 *
20 * Using the Quantity Tree
21 *
22 * Here's some general instructions and guidelines on using the
23 * quantity tree. This section also mentions the C code equivalents
24 * of the pl/sql procedures.
25 * 1. Create_Tree
26 * call create_tree to build the tree for a given organization and
27 * item. The tree can be built in two modes: reservation mode
28 * and transaction mode. Reservation mode is used to determine
29 * the available to reserve (atr) quantity (for reservations).
30 * Transaction mode is used to determine the availabe to transact
31 * (att) quantity, used in transactions. The onhand_source passed
32 * to the create tree function help define which subs and locators
33 * will be used to determine onhand quantity. If Onhand_source is 1,
34 * then only the quantity in ATPable subs will be used to
35 * determine quantity. If onhand_source is 2, then only
36 * the quantity in nettable subs is considered. If onhand_source is 3,
37 * the subs are not limited based on the nettable and ATPable flags.
38 * Pick_release should be 0 except if called from the inventory or
39 * wms detailing engines.
40 * The create_tree procedure returns tree_id, which must be used
41 * to query or update the tree.
42 * The equivalent function in the C code is CreateTree.
43 *
44 * 2. Query_Tree
45 * This procedure is used to find the values for quantity onhand,
46 * reservable quantity on hand, quantity reserved, quantity suggested,
47 * availabe to transact, and available to reserve. This procedure
48 * takes the place of 2 C functions: QtyQuery and SubXQuery. If
49 * tree is being queried in transaction mode, and
50 * the transaction is a subinventory
51 * transfer, then pass the subinventory code of the destination
52 * sub in the p_transfer_subinventory_code parameter. In all other
53 * cases, set the p_transfer_subinventory_code parameter to NULL.
54 * ATT and ATR are calculated differently depending on whether
55 * the transaction is a subinventory transfer or some other
56 * transaction.
57 *
58 * 3. Update_Quantities
59 * The update procedure changes the quantity in the quantity tree
60 * for a given item/org/revision/lot/sub/locator. The quantity
61 * updated depends on the quantity type parameter. If the quantity
62 * type is g_qoh, then the p_primary_quantity value is added
63 * to the quantity onhand. If the quantity type is g_qs_txn,
64 * then the quantity suggested value is updated. Reservations
65 * work the same way. Update_quantities does not update the
66 * database - it only updates the local version of the qty tree.
67 * The database must be updated separately.
68 * There are a couple of important things to keep in mind.
69 * First, the quantity passed in to update_quantities is important.
70 * The quantity is always added to the appropriate node qty. So,
71 * for a receipt txn, the quantity passed in should be
72 * positive. For an issue txn, the quantity passed in
73 * should have a negative sign (to decrement
74 * on hand quantity). For reserving items or suggesting
75 * an issue, the value passed in should be positive (incrementing
76 * quantity reserved or quantity suggested). Do not update the
77 * tree with suggested receipts; including suggested receipts
78 * could lead to missing inventory if the suggestion is not
79 * transacted.
80 * Second, this function is the same as the C function
81 * QtyAvail. There is no pl/sql equivalent of the C function
82 * SubXFer. For a subinventory transfer transaction which
83 * updates both the destination location and the source location,
84 * update_quantities must be called twice. First, add the quantity to
85 * the destination sub/locator. Then decrement the quantity from the
86 * source sub/locator. Order is important - this ordering assures
87 * that higher level att/atr are not made negative. The updates
88 * to both the destination and source should only happen for actual
89 * transactions, not suggested transfers.
90 *
91 * 4. Do_check
92 * This procedure should be called before committing quantity
93 * updates to the database. There can be multiple quantity trees
94 * for each item and organization. Updates in a quantity tree
95 * are not reflected in other quantity trees of the same org/item.
96 * Thus, it would be possible for two different sessions to try
97 * to reserve or transact the same quantity, which would led
98 * to negative quantity, a big no-no. To solve this problem, call
99 * do_check before commiting. This procedure will rebuild the
100 * quantity tree with the current info in the database. If your
101 * updates would drive the quantity negative (and if negative
102 * quantities are not allowed), then x_no_violation will be false.
103 * You should then rollback your updates.
104 *
105 ***********************************************************************/
106 -- Constant Definition
107 --
108 -- Source Type ID Constants
109 --
110 -- The following constants are valid demand source type ids and
111 -- supply source type ids.
112 -- They are the same as TRANSACTION_SOURCE_TYPE_ID
113 -- in table MTL_TXN_SOURCE_TYPES.
114 g_source_type_po CONSTANT NUMBER := 1 ;
115 g_source_type_oe CONSTANT NUMBER := 2 ;
116 g_source_type_account CONSTANT NUMBER := 3 ;
117 g_source_type_trans_order CONSTANT NUMBER := 4 ;
118 g_source_type_wip CONSTANT NUMBER := 5 ;
119 g_source_type_account_alias CONSTANT NUMBER := 6 ;
120 g_source_type_internal_req CONSTANT NUMBER := 7 ;
121 g_source_type_internal_ord CONSTANT NUMBER := 8 ;
122 g_source_type_cycle_count CONSTANT NUMBER := 9 ;
123 g_source_type_physical_inv CONSTANT NUMBER := 10;
124 g_source_type_standard_cost CONSTANT NUMBER := 11;
125 g_source_type_rma CONSTANT NUMBER := 12;
126 g_source_type_inv CONSTANT NUMBER := 13;
127 --
128 -- Tree mode constants
129 -- Users can call create_tree() in three modes, reservation mode,
130 -- transaction mode, and loose items only mode. In loose items only
131 -- mode, only quantity not in containers is considered
132 g_reservation_mode CONSTANT INTEGER := 1;
133 g_transaction_mode CONSTANT INTEGER := 2;
134 g_loose_only_mode CONSTANT INTEGER := 3;
135 g_no_lpn_rsvs_mode CONSTANT INTEGER := 4;
136 --
137 -- Quantity type constants
138 -- User can call update_quantities to change quantities at a given level.
139 -- Quantity type constans should be used to specify which quantity the user
140 -- intents to change: quantity onhand, or quantity reserved
141 g_qoh CONSTANT INTEGER := 1; -- quantity on hand
142 g_qr_same_demand CONSTANT INTEGER := 2; -- quantity reserved for the same demand source
143 g_qr_other_demand CONSTANT INTEGER := 3; -- quantity reserved for other demand source
144 g_qs_rsv CONSTANT INTEGER := 4; -- quantity for suggested reservation
145 g_qs_txn CONSTANT INTEGER := 5; -- quantity for suggested transaction
146
147 -- Onhand Source
148 -- Defined in mtl_onhand_source lookup
149 -- Used to determine which subs are included in calculation of
150 -- onhand qty
151 g_atpable_only CONSTANT NUMBER := 1;
152 g_nettable_only CONSTANT NUMBER := 2;
153 g_all_subs CONSTANT NUMBER := 3;
154 g_atpable_nettable_only CONSTANT NUMBER := 4;
155
156 -- Containerized
157 -- Used to indicate packed quantities for use in quantity calculations
158 -- If 0, then record is not packed in container.
159 -- If 1, then record is packed in containter.
160 g_containerized_true CONSTANT NUMBER := 1;
161 g_containerized_false CONSTANT NUMBER := 0;
162
163 -- Exclusive
164 -- Used to indicate if tree should be build in exclusive mode
165 -- If 0, then tree is not locked when it is built.
166 -- If 1, then tree is locked when built.
167 g_exclusive CONSTANT NUMBER := 1;
168 g_non_exclusive CONSTANT NUMBER := 0;
169
170 -- Pick Release
171 -- Used to indicate if the tree is being called from the pick release
172 -- process. When the tree is built during pick release, we should
173 -- consider all reservations that have a staged flag, even if the
174 -- reservation is for the same transaction. This way, quantity in
175 -- staging lanes never appears to be available, and the pick
176 -- release process will not allocate from staging locations. All
177 -- other times, the tree should not consider reservations which are
178 -- for this current transactions.
179 -- If 0, then tree is built normally (ignoring reservations for current
180 -- transaction)
181 -- If 1, then tree is built in pick release (considering staged
182 -- reservations for the current transaction)
183 g_pick_release_yes CONSTANT NUMBER := 1;
184 g_pick_release_no CONSTANT NUMBER := 0;
185
186 -- invConv: need to set this variable... in order to stop populating the TOP nodes when the first QT is complete after a CT :
187 g_is_populating_top_node BOOLEAN := TRUE;
188 /* set the value of global var g_is_mat_status_used from the profile INV_MATERIAL_STATUS
189 in create_tree procedure body. no value should be defaulted here.
190 This variable detemines whether reservations allowed flag derived from
191 material status definition is used for lots and locators while computing atr
192 INV_MATERIAL_STATUS == 1 == YES
193 INV_MATERIAL_STATUS == 2 == NO */
194 g_is_mat_status_used NUMBER;
195
196 -- Function
197 -- demand_source_equals
198 -- Description
199 -- Compare whether two demand sources are the same.
200 -- Return Value
201 -- 'Y' if the two demand sources are the same; 'N' otherwise
202 Function demand_source_equals
203 ( p_demand_source_type_id1 NUMBER
204 ,p_demand_source_header_id1 NUMBER
205 ,p_demand_source_line_id1 NUMBER
206 ,p_demand_source_delivery1 NUMBER
207 ,p_demand_source_name1 VARCHAR2
208 ,p_demand_source_type_id2 NUMBER
209 ,p_demand_source_header_id2 NUMBER
210 ,p_demand_source_line_id2 NUMBER
211 ,p_demand_source_delivery2 NUMBER
212 ,p_demand_source_name2 VARCHAR2
213 ) RETURN VARCHAR2;
214 PRAGMA restrict_references(demand_source_equals, wnds, wnps, rnds);
215 --
216 -- Procedure
217 -- clear_quantity_cache
218 -- Description
219 -- Delete all quantity trees in the memory. Should be called when you call
220 -- rollback. Otherwise the trees in memory may not be in sync with the data
221 -- in the corresponding database tables
222 PROCEDURE clear_quantity_cache;
223
224 --
225 -- Procedure
226 -- create_tree
227 -- Description
228 -- Create a quantity tree
229 --
230 -- Version
231 -- Current version 1.0
232 -- Initial version 1.0
233 --
234 -- Input parameters:
235 -- p_api_version_number standard input parameter
236 -- p_init_msg_lst standard input parameter
237 -- p_organization_id organzation id
238 -- p_inventory_item_id inventory_item_id
239 -- p_tree_mode tree mode, either g_reservation_mode
240 -- or g_transaction_mode
241 -- p_is_revision_control
242 -- p_is_lot_control
243 -- p_is_serial_control
244 -- p_asset_sub_only
245 -- p_include_suggestion should be true only for pick/put engine
246 -- p_demand_source_type_id demand_source_type_id
247 -- p_demand_source_header_id demand_source_header_id
248 -- p_demand_source_line_id demand_source_line_id
249 -- p_demand_source_name demand_source_name
250 -- p_demand_source_delivery demand_source_delivery
251 -- p_onhand_source describes subinventories in which to search
252 -- for onhand - nettable, ATPable, all
253 --
254 -- Output parameters:
255 -- x_return_status standard output parameter
256 -- x_msg_count standard output parameter
257 -- x_msg_data standard output parameter
258 -- x_tree_id used later to refer to the same tree
259 --
260 PROCEDURE create_tree
261 ( p_api_version_number IN NUMBER
262 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
263 , x_return_status OUT NOCOPY VARCHAR2
264 , x_msg_count OUT NOCOPY NUMBER
265 , x_msg_data OUT NOCOPY VARCHAR2
266 , p_organization_id IN NUMBER
267 , p_inventory_item_id IN NUMBER
268 , p_tree_mode IN INTEGER
269 , p_is_revision_control IN BOOLEAN
270 , p_is_lot_control IN BOOLEAN
271 , p_is_serial_control IN BOOLEAN
272 , p_asset_sub_only IN BOOLEAN DEFAULT FALSE
273 , p_include_suggestion IN BOOLEAN DEFAULT FALSE
274 , p_demand_source_type_id IN NUMBER DEFAULT -9999
275 , p_demand_source_header_id IN NUMBER DEFAULT -9999
276 , p_demand_source_line_id IN NUMBER DEFAULT -9999
277 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
278 , p_demand_source_delivery IN NUMBER DEFAULT NULL
279 , p_lot_expiration_date IN DATE DEFAULT NULL
280 , x_tree_id OUT NOCOPY INTEGER
281 , p_onhand_source IN NUMBER DEFAULT 3 --g_all_subs
282 , p_exclusive IN NUMBER DEFAULT 0 --g_non_exclusive
283 , p_pick_release IN NUMBER DEFAULT 0 --g_pick_release_no
284 );
285
286 PROCEDURE create_tree
287 ( p_api_version_number IN NUMBER
288 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
289 , x_return_status OUT NOCOPY VARCHAR2
290 , x_msg_count OUT NOCOPY NUMBER
291 , x_msg_data OUT NOCOPY VARCHAR2
292 , p_organization_id IN NUMBER
293 , p_inventory_item_id IN NUMBER
294 , p_tree_mode IN INTEGER
295 , p_is_revision_control IN BOOLEAN
296 , p_is_lot_control IN BOOLEAN
297 , p_is_serial_control IN BOOLEAN
298 , p_grade_code IN VARCHAR2
299 , p_asset_sub_only IN BOOLEAN DEFAULT FALSE
300 , p_include_suggestion IN BOOLEAN DEFAULT FALSE
301 , p_demand_source_type_id IN NUMBER DEFAULT -9999
302 , p_demand_source_header_id IN NUMBER DEFAULT -9999
303 , p_demand_source_line_id IN NUMBER DEFAULT -9999
304 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
305 , p_demand_source_delivery IN NUMBER DEFAULT NULL
306 , p_lot_expiration_date IN DATE DEFAULT NULL
307 , x_tree_id OUT NOCOPY INTEGER
308 , p_onhand_source IN NUMBER DEFAULT 3 --g_all_subs
309 , p_exclusive IN NUMBER DEFAULT 0 --g_non_exclusive
310 , p_pick_release IN NUMBER DEFAULT 0 --g_pick_release_no
311 );
312
313
314 -- Procedure
315 -- query tree
316 --
317 -- Version
318 -- Current version 1.0
319 -- Initial version 1.0
320 --
321 -- Input parameters:
322 -- p_api_version_number standard input parameter
323 -- p_init_msg_lst standard input parameter
324 -- p_tree_id tree_id
325 -- p_revision revision
326 -- p_lot_number lot_number
327 -- p_subinventory_code subinventory code
328 -- p_locator_id locator_id
329 -- p_to_subinventory_code destination subinventory for subinventory transfer
330 -- transactions. Should be NULL otherwise.
331 --
332 -- Output parameters:
333 -- x_return_status standard output parameter
334 -- x_msg_count standard output parameter
335 -- x_msg_data standard output parameter
336 -- x_qoh qoh
337 -- x_rqoh rqoh
338 -- x_qr qr
339 -- x_qs qs
340 -- x_att att
341 -- x_atr atr
342 --
343 PROCEDURE query_tree
344 ( p_api_version_number IN NUMBER
345 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
346 , x_return_status OUT NOCOPY VARCHAR2
347 , x_msg_count OUT NOCOPY NUMBER
351 , p_lot_number IN VARCHAR2
348 , x_msg_data OUT NOCOPY VARCHAR2
349 , p_tree_id IN INTEGER
350 , p_revision IN VARCHAR2
352 , p_subinventory_code IN VARCHAR2
353 , p_locator_id IN NUMBER
354 , x_qoh OUT NOCOPY NUMBER
355 , x_rqoh OUT NOCOPY NUMBER
356 , x_qr OUT NOCOPY NUMBER
357 , x_qs OUT NOCOPY NUMBER
358 , x_att OUT NOCOPY NUMBER
359 , x_atr OUT NOCOPY NUMBER
360 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
361 , p_cost_group_id IN NUMBER DEFAULT NULL
362 , p_lpn_id IN NUMBER DEFAULT NULL
363 , p_transfer_locator_id IN NUMBER DEFAULT NULL
364 );
365
366 PROCEDURE query_tree
367 ( p_api_version_number IN NUMBER
368 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
369 , x_return_status OUT NOCOPY VARCHAR2
370 , x_msg_count OUT NOCOPY NUMBER
371 , x_msg_data OUT NOCOPY VARCHAR2
372 , p_tree_id IN INTEGER
373 , p_revision IN VARCHAR2
374 , p_lot_number IN VARCHAR2
375 , p_subinventory_code IN VARCHAR2
376 , p_locator_id IN NUMBER
377 , x_qoh OUT NOCOPY NUMBER
378 , x_rqoh OUT NOCOPY NUMBER
379 , x_qr OUT NOCOPY NUMBER
380 , x_qs OUT NOCOPY NUMBER
381 , x_att OUT NOCOPY NUMBER
382 , x_atr OUT NOCOPY NUMBER
383 , x_sqoh OUT NOCOPY NUMBER
384 , x_srqoh OUT NOCOPY NUMBER
385 , x_sqr OUT NOCOPY NUMBER
386 , x_sqs OUT NOCOPY NUMBER
387 , x_satt OUT NOCOPY NUMBER
388 , x_satr OUT NOCOPY NUMBER
389 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
390 , p_cost_group_id IN NUMBER DEFAULT NULL
391 , p_lpn_id IN NUMBER DEFAULT NULL
392 , p_transfer_locator_id IN NUMBER DEFAULT NULL
393 );
394
395 --Query_Tree
396 -- Use this query_tree to return the packed quantity on hand.
397 -- The Packed Quantity On Hand is the total on hand sitting in
398 -- LPNs.
399 -- PQOH is populated only if the tree is created in loose_only_mode.
400 -- If tree is not created in loose_only_mode, this API will return
401 -- PQOH of 0.
402
403 PROCEDURE query_tree
404 ( p_api_version_number IN NUMBER
405 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
406 , x_return_status OUT NOCOPY VARCHAR2
407 , x_msg_count OUT NOCOPY NUMBER
408 , x_msg_data OUT NOCOPY VARCHAR2
409 , p_tree_id IN INTEGER
410 , p_revision IN VARCHAR2
411 , p_lot_number IN VARCHAR2
412 , p_subinventory_code IN VARCHAR2
413 , p_locator_id IN NUMBER
414 , x_qoh OUT NOCOPY NUMBER
415 , x_rqoh OUT NOCOPY NUMBER
416 , x_pqoh OUT NOCOPY NUMBER
417 , x_qr OUT NOCOPY NUMBER
418 , x_qs OUT NOCOPY NUMBER
419 , x_att OUT NOCOPY NUMBER
420 , x_atr OUT NOCOPY NUMBER
421 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
422 , p_cost_group_id IN NUMBER DEFAULT NULL
423 , p_lpn_id IN NUMBER DEFAULT NULL
424 , p_transfer_locator_id IN NUMBER DEFAULT NULL
425 );
426
427 PROCEDURE query_tree
428 ( p_api_version_number IN NUMBER
429 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
430 , x_return_status OUT NOCOPY VARCHAR2
431 , x_msg_count OUT NOCOPY NUMBER
432 , x_msg_data OUT NOCOPY VARCHAR2
433 , p_tree_id IN INTEGER
434 , p_revision IN VARCHAR2
435 , p_lot_number IN VARCHAR2
436 , p_subinventory_code IN VARCHAR2
437 , p_locator_id IN NUMBER
438 , x_qoh OUT NOCOPY NUMBER
439 , x_rqoh OUT NOCOPY NUMBER
440 , x_pqoh OUT NOCOPY NUMBER
441 , x_qr OUT NOCOPY NUMBER
442 , x_qs OUT NOCOPY NUMBER
443 , x_att OUT NOCOPY NUMBER
444 , x_atr OUT NOCOPY NUMBER
445 , x_sqoh OUT NOCOPY NUMBER
446 , x_srqoh OUT NOCOPY NUMBER
447 , x_spqoh OUT NOCOPY NUMBER
448 , x_sqr OUT NOCOPY NUMBER
449 , x_sqs OUT NOCOPY NUMBER
450 , x_satt OUT NOCOPY NUMBER
451 , x_satr OUT NOCOPY NUMBER
452 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
453 , p_cost_group_id IN NUMBER DEFAULT NULL
454 , p_lpn_id IN NUMBER DEFAULT NULL
455 , p_transfer_locator_id IN NUMBER DEFAULT NULL
456 );
457
458 --
459 -- Procedure
460 -- update_quantities
461 -- Description
462 -- update a quantity tree
463 --
464 -- Version
465 -- Current version 1.0
466 -- Initial version 1.0
467 --
468 -- Input parameters:
469 -- p_api_version_number standard input parameter
470 -- p_init_msg_lst standard input parameter
471 -- p_tree_id tree_id
472 -- p_revision revision
473 -- p_lot_number lot_number
474 -- p_subinventory_code subinventory_code
475 -- p_locator_id locator_id
476 -- p_primary_quantity primary_quantity
477 -- p_quantity_type
481 -- x_return_status standard output parameter
478 -- p_containerized set to g_containerized_true if
479 -- quantity is in container
480 -- Output parameters:
482 -- x_msg_count standard output parameter
483 -- x_msg_data standard output parameter
484 -- x_tree_id used later to refer to the same tree
485 -- x_qoh qoh after the update
486 -- x_rqoh rqoh after the update
487 -- x_qr qr after the update
488 -- x_qs qs after the update
489 -- x_att att after the update
490 -- x_atr atr after the update
491 PROCEDURE update_quantities
492 ( p_api_version_number IN NUMBER
493 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
494 , x_return_status OUT NOCOPY VARCHAR2
495 , x_msg_count OUT NOCOPY NUMBER
496 , x_msg_data OUT NOCOPY VARCHAR2
497 , p_tree_id IN INTEGER
498 , p_revision IN VARCHAR2 DEFAULT NULL
499 , p_lot_number IN VARCHAR2 DEFAULT NULL
500 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
501 , p_locator_id IN NUMBER DEFAULT NULL
502 , p_primary_quantity IN NUMBER
503 , p_quantity_type IN INTEGER
504 , x_qoh OUT NOCOPY NUMBER
505 , x_rqoh OUT NOCOPY NUMBER
506 , x_qr OUT NOCOPY NUMBER
507 , x_qs OUT NOCOPY NUMBER
508 , x_att OUT NOCOPY NUMBER
509 , x_atr OUT NOCOPY NUMBER
510 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
511 , p_cost_group_id IN NUMBER DEFAULT NULL
512 , p_containerized IN NUMBER DEFAULT g_containerized_false
513 , p_lpn_id IN NUMBER DEFAULT NULL
514 , p_transfer_locator_id IN NUMBER DEFAULT NULL
515 ) ;
516
517 PROCEDURE update_quantities
518 ( p_api_version_number IN NUMBER
519 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
520 , x_return_status OUT NOCOPY VARCHAR2
521 , x_msg_count OUT NOCOPY NUMBER
522 , x_msg_data OUT NOCOPY VARCHAR2
523 , p_tree_id IN INTEGER
524 , p_revision IN VARCHAR2 DEFAULT NULL
525 , p_lot_number IN VARCHAR2 DEFAULT NULL
526 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
527 , p_locator_id IN NUMBER DEFAULT NULL
528 , p_primary_quantity IN NUMBER
529 , p_secondary_quantity IN NUMBER
530 , p_quantity_type IN INTEGER
531 , x_qoh OUT NOCOPY NUMBER
532 , x_rqoh OUT NOCOPY NUMBER
533 , x_qr OUT NOCOPY NUMBER
534 , x_qs OUT NOCOPY NUMBER
535 , x_att OUT NOCOPY NUMBER
536 , x_atr OUT NOCOPY NUMBER
537 , x_sqoh OUT NOCOPY NUMBER
538 , x_srqoh OUT NOCOPY NUMBER
539 , x_sqr OUT NOCOPY NUMBER
540 , x_sqs OUT NOCOPY NUMBER
541 , x_satt OUT NOCOPY NUMBER
542 , x_satr OUT NOCOPY NUMBER
543 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
544 , p_cost_group_id IN NUMBER DEFAULT NULL
545 , p_containerized IN NUMBER DEFAULT g_containerized_false
546 , p_lpn_id IN NUMBER DEFAULT NULL
547 , p_transfer_locator_id IN NUMBER DEFAULT NULL
548 ) ;
549
550 --
551 -- Procedure
552 -- do_check
553 -- Description
554 -- Check quantity violation
555 --
556 -- Version
557 -- Current version 1.0
558 -- Initial version 1.0
559 --
560 -- Input parameters:
561 -- p_api_version_number standard input parameter
562 -- p_init_msg_lst standard input parameter
563 -- p_tree_id tree id
564 --
565 -- Output parameters:
566 -- x_return_status standard output parameter
567 -- x_msg_count standard output parameter
568 -- x_msg_data standard output parameter
569 -- x_no_violation true if no violation, false otherwise
570 --
571 PROCEDURE do_check
572 ( p_api_version_number IN NUMBER
573 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
574 , x_return_status OUT NOCOPY VARCHAR2
575 , x_msg_count OUT NOCOPY NUMBER
576 , x_msg_data OUT NOCOPY VARCHAR2
577 , p_tree_id IN INTEGER
578 , x_no_violation OUT NOCOPY BOOLEAN
579 );
580
581 --
582 -- Procedure
583 -- do_check
584 -- Description
585 -- Check quantity violation
586 --
587 -- Version
588 -- Current version 1.0
589 -- Initial version 1.0
590 --
591 -- Input parameters:
592 -- p_api_version_number standard input parameter
593 -- p_init_msg_lst standard input parameter
594 --
595 -- Output parameters:
596 -- x_return_status standard output parameter
597 -- x_msg_count standard output parameter
598 -- x_msg_data standard output parameter
599 -- x_no_violation true if no violation, false otherwise
600 --
601 PROCEDURE do_check
602 ( p_api_version_number IN NUMBER
603 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
604 , x_return_status OUT NOCOPY VARCHAR2
605 , x_msg_count OUT NOCOPY NUMBER
606 , x_msg_data OUT NOCOPY VARCHAR2
607 , x_no_violation OUT NOCOPY BOOLEAN
608 );
609
610 --
611 -- Procedure
612 -- free_tree
613 -- Description
617 -- Current version 1.0
614 -- free the tree when it is no longer needed
615 --
616 -- Version
618 -- Initial version 1.0
619 --
620 -- Input parameters:
621 -- p_api_version_number standard input parameter
622 -- p_init_msg_lst standard input parameter
623 -- p_tree_id tree id
624 --
625 -- Output parameters:
626 -- x_return_status standard output parameter
627 -- x_msg_count standard output parameter
628 -- x_msg_data standard output parameter
629 --
630 PROCEDURE free_tree
631 ( p_api_version_number IN NUMBER
632 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
633 , x_return_status OUT NOCOPY VARCHAR2
634 , x_msg_count OUT NOCOPY NUMBER
635 , x_msg_data OUT NOCOPY VARCHAR2
636 , p_tree_id IN INTEGER
637 );
638
639 --
640 -- Procedure
641 -- free_all
642 -- Description
643 -- free all the trees
644 --
645 -- Version
646 -- Current version 1.0
647 -- Initial version 1.0
648 --
649 -- Input parameters:
650 -- p_api_version_number standard input parameter
651 -- p_init_msg_lst standard input parameter
652 --
653 -- Output parameters:
654 -- x_return_status standard output parameter
655 -- x_msg_count standard output parameter
656 -- x_msg_data standard output parameter
657 --
658 PROCEDURE free_all
659 ( p_api_version_number IN NUMBER
660 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
661 , x_return_status OUT NOCOPY VARCHAR2
662 , x_msg_count OUT NOCOPY NUMBER
663 , x_msg_data OUT NOCOPY VARCHAR2
664 );
665
666 --
667 -- Procedure
668 -- mark_all_for_refresh
669 -- Description
670 -- marks all existing trees as needing to be rebuilt. Unlike
671 -- free_tree and clear_quantity_cache, no quantity trees are deleted.
672 --
673 -- This API is needed so that the do_check_for_commit procedure in
674 -- INVRSV3B.pls will still work. That procedure stores tree_ids in a
675 -- temp table. When clear_quantity_cache is called, these tree_ids are
676 -- no longer valid. When this is called instead of clear_quantity_cache,
677 -- the tree_ids are still valid to be passed to do_check.
678 --
679 --
680 -- Version
681 -- Current version 1.0
682 -- Initial version 1.0
683 --
684 -- Input parameters:
685 -- p_api_version_number standard input parameter
686 -- p_init_msg_lst standard input parameter
687 --
688 -- Output parameters:
689 -- x_return_status standard output parameter
690 -- x_msg_count standard output parameter
691 -- x_msg_data standard output parameter
692 --
693 PROCEDURE mark_all_for_refresh
694 ( p_api_version_number IN NUMBER
695 , p_init_msg_lst IN VARCHAR2
696 , x_return_status OUT NOCOPY VARCHAR2
697 , x_msg_count OUT NOCOPY NUMBER
698 , x_msg_data OUT NOCOPY VARCHAR2
699 );
700
701
702 --
703 -- Procedure
704 -- find_rootinfo
705 -- Description
706 -- find a rootinfo record based on input parameters
707 -- Version
708 -- Current Version 1.0
709 -- Initial Version 1.0
710 --
711 -- Input Parameters
712 -- p_api_version_number standard input parameter
713 -- p_init_msg_lst standard input parameter
714 --
715 -- Output parameters:
716 -- x_return_status standard output parameter
717 -- x_msg_count standard output parameter
718 -- x_msg_data standard output parameter
719 --
720 -- Return
721 -- 0 if rootinfo not found
722 -- >0 index for the rootinfo in the rootinfo array
723 --
724 FUNCTION find_rootinfo
725 ( x_return_status OUT NOCOPY VARCHAR2
726 , p_organization_id IN NUMBER
727 , p_inventory_item_id IN NUMBER
728 , p_tree_mode IN INTEGER
729 , p_is_revision_control IN BOOLEAN
730 , p_is_lot_control IN BOOLEAN
731 , p_is_serial_control IN BOOLEAN
732 , p_asset_sub_only IN BOOLEAN
733 , p_include_suggestion IN BOOLEAN
734 , p_demand_source_type_id IN NUMBER
735 , p_demand_source_header_id IN NUMBER
736 , p_demand_source_line_id IN NUMBER
737 , p_demand_source_name IN VARCHAR2
738 , p_demand_source_delivery IN NUMBER
739 , p_lot_expiration_date IN DATE
740 , p_onhand_source IN NUMBER
741 , p_pick_release IN NUMBER DEFAULT 0 --g_pick_release_no
742 ) RETURN INTEGER;
743
744 -- Procedure
745 -- backup_tree
746 -- Description
747 -- backup the current state of a tree
748 -- Note
749 -- This is only a one level backup. Calling it twice will
750 -- overwrite the previous backup
751 PROCEDURE backup_tree
752 (
753 x_return_status OUT NOCOPY VARCHAR2
754 , p_tree_id IN INTEGER
755 );
756
757 -- Procedure
758 -- restore_tree
759 -- Description
760 -- restore the current state of a tree to the state
761 -- at the last time when savepoint_tree is called
762 -- Note
763 -- This is only a one level restore. Calling it more than once
764 -- has the same effect as calling it once.
765 PROCEDURE restore_tree
766 (
767 x_return_status OUT NOCOPY VARCHAR2
768 , p_tree_id IN INTEGER
769 );
773 -- We now need to support multi-level backup and restore capability
770
771 -- **NEW BACKUP/RESTORE PROCEDURES**
772 -- Bug 2788807
774 -- for the quantity tree. We'll overload the existing procedures.
775
776 -- Procedure
777 -- backup_tree
778 -- Description
779 -- backup the current state of a tree. This procedure returns a backup_id
780 -- which needs to be passed to restore_tree in order to restore the correct
781 -- version of the quantity tree. Unlike the older version of backup_tree,
782 -- this can be called multiple times without overwriting previous backups.
783 -- The backups dissappear when clear_quantity_cache is called.
784 --
785 PROCEDURE backup_tree
786 (
787 x_return_status OUT NOCOPY VARCHAR2
788 , p_tree_id IN INTEGER
789 , x_backup_id OUT NOCOPY NUMBER
790 );
791
792 -- Procedure
793 -- restore_tree
794 -- Description
795 -- Restores the quantity tree to the point indicated by the backup_id.
796 -- Tree_id is not strictly needed here, but is kept for overloading and
797 -- error checking purposes. Restore_tree can be called multiple times for
798 -- the same backup_id - a saved quantity tree is not deleted until
799 -- clear_quantity_cahce is called.
800 PROCEDURE restore_tree
801 (
802 x_return_status OUT NOCOPY VARCHAR2
803 , p_tree_id IN INTEGER
804 , p_backup_id IN NUMBER
805 );
806
807
808 -- Procedure
809 -- lock_tree
810 -- Description
811 -- this function places a user lock on an item/organization
812 -- combination. Once this lock is placed, no other sessions
813 -- can lock the same item/org combo. Users who call lock_tree
814 -- do not always have to call release_lock explicitly. The lock is
815 -- released automatically at commit, rollback, or session loss.
816 PROCEDURE lock_tree(
817 p_api_version_number IN NUMBER
818 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
819 , x_return_status OUT NOCOPY VARCHAR2
820 , x_msg_count OUT NOCOPY NUMBER
821 , x_msg_data OUT NOCOPY VARCHAR2
822 , p_organization_id IN NUMBER
823 , p_inventory_item_id IN NUMBER);
824
825
826
827 -- Procedure
828 -- release_lock
829 -- Description
830 -- this function releases the user lock on an item/organization
831 -- combination created by this session. Users who call lock_tree
832 -- do not always have to call release_lock explicitly. The lock is
833 -- released automatically at commit, rollback, or session loss.
834
835 PROCEDURE release_lock(
836 p_api_version_number IN NUMBER
837 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
838 , x_return_status OUT NOCOPY VARCHAR2
839 , x_msg_count OUT NOCOPY NUMBER
840 , x_msg_data OUT NOCOPY VARCHAR2
841 , p_organization_id IN NUMBER
842 , p_inventory_item_id IN NUMBER);
843
844 -- Procedure
845 -- prepare_reservation_quantities
846 -- Description
847 -- This procedure is called from the reservation form to
848 -- initialize the table used for the LOVs in that form.
849 -- The tree passed to this procedure should have been created in
850 -- reservation_mode.
851 PROCEDURE prepare_reservation_quantities(
852 x_return_status OUT NOCOPY VARCHAR2
853 , p_tree_id IN NUMBER
854 );
855
856
857 -- Get_Total_QOH
858 -- This API returns the TQOH, or total quantity on hand.
859 -- This value reflects any negative balances for this
860 -- item in the organization. The Total QOH is the minimum
861 -- of the current node's QOH and all ancestor nodes' QOH.
862 -- For example,
863 -- Consider 2 locators in the EACH subinventory:
864 -- E.1.1 has 10 onhand
865 -- E.1.2 has -20 onhand
866 -- Thus, the subinventory Each has -10 onhand.
867 --
868 -- Where calling query_tree, qoh for E.1.1 = 10.
869 -- However, when calling get_total_qoh, the TQOH
870 -- for E.1.1 = -10, reflecting the value at the subinventory level.
871 --
872 -- This procedure is used by the inventory transaction forms.
873
874 PROCEDURE get_total_qoh
875 ( x_return_status OUT NOCOPY VARCHAR2
876 , x_msg_count OUT NOCOPY NUMBER
877 , x_msg_data OUT NOCOPY VARCHAR2
878 , p_tree_id IN INTEGER
879 , p_revision IN VARCHAR2
880 , p_lot_number IN VARCHAR2
881 , p_subinventory_code IN VARCHAR2
882 , p_locator_id IN NUMBER
883 , p_cost_group_id IN NUMBER DEFAULT NULL
884 , x_tqoh OUT NOCOPY NUMBER
885 , p_lpn_id IN NUMBER DEFAULT NULL
886 );
887
888 PROCEDURE get_total_qoh
889 ( x_return_status OUT NOCOPY VARCHAR2
890 , x_msg_count OUT NOCOPY NUMBER
891 , x_msg_data OUT NOCOPY VARCHAR2
892 , p_tree_id IN INTEGER
893 , p_revision IN VARCHAR2
894 , p_lot_number IN VARCHAR2
895 , p_subinventory_code IN VARCHAR2
896 , p_locator_id IN NUMBER
897 , p_cost_group_id IN NUMBER DEFAULT NULL
898 , x_tqoh OUT NOCOPY NUMBER
899 , x_stqoh OUT NOCOPY NUMBER
900 , p_lpn_id IN NUMBER DEFAULT NULL
901 );
902
903 -- Procedure
904 -- print_tree
905 -- Description
906 -- print the information in a tree to dbms_output
907 PROCEDURE print_tree
908 (
909 p_tree_id IN INTEGER
910 );
911
912
913 --
914 -- Bug 2486318. The do check does not work. Trasactions get committed
918 -- update_quantities_for_form
915 -- even if there is a node violation. Added p_check_mark_node_only to mark
916 -- the nodes. A new procedure was added to bve called from inldqc.ppc
917 -- Procedure
919 -- Description
920 -- update a quantity tree
921 --
922 -- Version
923 -- Current version 1.0
924 -- Initial version 1.0
925 --
926 -- Input parameters:
927 -- p_api_version_number standard input parameter
928 -- p_init_msg_lst standard input parameter
929 -- p_tree_id tree_id
930 -- p_revision revision
931 -- p_lot_number lot_number
932 -- p_subinventory_code subinventory_code
933 -- p_locator_id locator_id
934 -- p_primary_quantity primary_quantity
935 -- p_quantity_type
936 -- p_containerized set to g_containerized_true if
937 -- quantity is in container
938 -- p_call_for_form chek if procedure called from form
939 -- Output parameters:
940 -- x_return_status standard output parameter
941 -- x_msg_count standard output parameter
942 -- x_msg_data standard output parameter
943 -- x_tree_id used later to refer to the same tree
944 -- x_qoh qoh after the update
945 -- x_rqoh rqoh after the update
946 -- x_qr qr after the update
947 -- x_qs qs after the update
948 -- x_att att after the update
949 -- x_atr atr after the update
950 PROCEDURE update_quantities_for_form
951 ( p_api_version_number IN NUMBER
952 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
953 , x_return_status OUT NOCOPY VARCHAR2
954 , x_msg_count OUT NOCOPY NUMBER
955 , x_msg_data OUT NOCOPY VARCHAR2
956 , p_tree_id IN INTEGER
957 , p_revision IN VARCHAR2 DEFAULT NULL
958 , p_lot_number IN VARCHAR2 DEFAULT NULL
959 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
960 , p_locator_id IN NUMBER DEFAULT NULL
961 , p_primary_quantity IN NUMBER
962 , p_quantity_type IN INTEGER
963 , x_qoh OUT NOCOPY NUMBER
964 , x_rqoh OUT NOCOPY NUMBER
965 , x_qr OUT NOCOPY NUMBER
966 , x_qs OUT NOCOPY NUMBER
967 , x_att OUT NOCOPY NUMBER
968 , x_atr OUT NOCOPY NUMBER
969 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
970 , p_cost_group_id IN NUMBER DEFAULT NULL
971 , p_containerized IN NUMBER DEFAULT g_containerized_false
972 , p_call_for_form IN VARCHAR2 DEFAULT fnd_api.g_true
973 , p_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
974 ) ;
975
976 PROCEDURE update_quantities_for_form
977 ( p_api_version_number IN NUMBER
978 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
979 , x_return_status OUT NOCOPY VARCHAR2
980 , x_msg_count OUT NOCOPY NUMBER
981 , x_msg_data OUT NOCOPY VARCHAR2
982 , p_tree_id IN INTEGER
983 , p_revision IN VARCHAR2 DEFAULT NULL
984 , p_lot_number IN VARCHAR2 DEFAULT NULL
985 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
986 , p_locator_id IN NUMBER DEFAULT NULL
987 , p_primary_quantity IN NUMBER
988 , p_secondary_quantity IN NUMBER
989 , p_quantity_type IN INTEGER
990 , x_qoh OUT NOCOPY NUMBER
991 , x_rqoh OUT NOCOPY NUMBER
992 , x_qr OUT NOCOPY NUMBER
993 , x_qs OUT NOCOPY NUMBER
994 , x_att OUT NOCOPY NUMBER
995 , x_atr OUT NOCOPY NUMBER
996 , x_sqoh OUT NOCOPY NUMBER
997 , x_srqoh OUT NOCOPY NUMBER
998 , x_sqr OUT NOCOPY NUMBER
999 , x_sqs OUT NOCOPY NUMBER
1000 , x_satt OUT NOCOPY NUMBER
1001 , x_satr OUT NOCOPY NUMBER
1002 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
1003 , p_cost_group_id IN NUMBER DEFAULT NULL
1004 , p_containerized IN NUMBER DEFAULT g_containerized_false
1005 , p_call_for_form IN VARCHAR2 DEFAULT fnd_api.g_true
1006 , p_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
1007 ) ;
1008
1009 function do_check_release_locks return boolean;
1010
1011 END inv_quantity_tree_pvt;
[Home] [Help]