1 PACKAGE inv_quantity_tree_grp as
2 /* $Header: INVGQTTS.pls 120.0 2005/05/25 04:57:09 appldev noship $*/
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 -- invConv change
12 -- srqoh secondary reservable quantity on hand -- invConv change
13 -- sqr secondary quantity reserved -- invConv change
14 -- sqs secondare quantity suggested -- invConv change
15 -- satt secondary available to transact -- invConv change
16 -- satr secondary available to reserve -- invConv change
17
18 -- Constant Definition
19 --
20 -- Tree mode constants
21 -- Users can call create_tree() in two mode, reservation mode and
22 -- transaction mode
23 g_reservation_mode CONSTANT INTEGER := inv_quantity_tree_pvt.g_reservation_mode;
24 g_transaction_mode CONSTANT INTEGER := inv_quantity_tree_pvt.g_transaction_mode;
25 --
26 --
27 -- Quantity type constants
28 -- User can call update_quantities to change quantities at a given level.
29 -- Quantity type constans should be used to specify which quantity the user
30 -- intents to change: quantity onhand, or quantity reserved for the (same) demand
31 -- source that was used to create the tree, and in the future, quantity suggested
32 -- when quantity suggestion is implemented.
33 g_qoh CONSTANT INTEGER := inv_quantity_tree_pvt.g_qoh;
34 -- quantity reserved by same demand source
35 g_qr_same_demand CONSTANT INTEGER := inv_quantity_tree_pvt.g_qr_same_demand;
36 -- quantity for suggested reservation
37 g_qs_rsv CONSTANT INTEGER := inv_quantity_tree_pvt.g_qs_rsv;
38 -- quantity for suggested transaction
39 g_qs_txn CONSTANT INTEGER := inv_quantity_tree_pvt.g_qs_txn;
40
41 -- Procedure
42 -- clear_quantity_cache
43 -- Description
44 -- Delete all quantity trees in the memory. Should be called when you call
45 -- rollback. Otherwise the trees in memory may not be in sync with the data
46 -- in the corresponding database tables
47 PROCEDURE clear_quantity_cache;
48
49 -- Procedure
50 -- create_tree
51 -- Description
52 -- Create a quantity tree, if it does not exist, in memory based on the input
53 -- and return the tree id
54 PROCEDURE create_tree
55 ( p_api_version_number IN NUMBER
56 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
57 , x_return_status OUT NOCOPY VARCHAR2
58 , x_msg_count OUT NOCOPY NUMBER
59 , x_msg_data OUT NOCOPY VARCHAR2
60 , p_organization_id IN NUMBER
61 , p_inventory_item_id IN NUMBER
62 , p_tree_mode IN INTEGER
63 , p_is_revision_control IN BOOLEAN
64 , p_is_lot_control IN BOOLEAN
65 , p_is_serial_control IN BOOLEAN
66 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
67 , p_asset_sub_only IN BOOLEAN DEFAULT FALSE
68 , p_include_suggestion IN BOOLEAN DEFAULT FALSE
69 , p_demand_source_type_id IN NUMBER DEFAULT -9999
70 , p_demand_source_header_id IN NUMBER DEFAULT -9999
71 , p_demand_source_line_id IN NUMBER DEFAULT -9999
72 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
73 , p_lot_expiration_date IN DATE DEFAULT NULL
74 , p_onhand_source IN NUMBER DEFAULT inv_quantity_tree_pvt.g_all_subs
75 , x_tree_id OUT NOCOPY INTEGER
76 );
77
78
79 -- Procedure
80 -- query_tree
81 -- Description
82 -- Query a quantity tree for quantity information at the level
83 -- specified by the input
84 PROCEDURE query_tree
85 ( p_api_version_number IN NUMBER
86 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
87 , x_return_status OUT NOCOPY VARCHAR2
88 , x_msg_count OUT NOCOPY NUMBER
89 , x_msg_data OUT NOCOPY VARCHAR2
90 , p_tree_id IN INTEGER
91 , p_revision IN VARCHAR2
92 , p_lot_number IN VARCHAR2
93 , p_subinventory_code IN VARCHAR2
94 , p_locator_id IN NUMBER
95 , x_qoh OUT NOCOPY NUMBER
96 , x_rqoh OUT NOCOPY NUMBER
97 , x_qr OUT NOCOPY NUMBER
98 , x_qs OUT NOCOPY NUMBER
99 , x_att OUT NOCOPY NUMBER
100 , x_atr OUT NOCOPY NUMBER
101 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
102 , p_cost_group_id IN NUMBER DEFAULT NULL
103 , p_lpn_id IN NUMBER DEFAULT NULL
104 , p_transfer_locator_id IN NUMBER DEFAULT NULL
105 );
106
107 -- invConv changes begin: overload
108 PROCEDURE query_tree
109 ( p_api_version_number IN NUMBER
110 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
111 , x_return_status OUT NOCOPY VARCHAR2
112 , x_msg_count OUT NOCOPY NUMBER
113 , x_msg_data OUT NOCOPY VARCHAR2
114 , p_tree_id IN INTEGER
115 , p_revision IN VARCHAR2
116 , p_lot_number IN VARCHAR2
117 , p_subinventory_code IN VARCHAR2
118 , p_locator_id IN NUMBER
119 , x_qoh OUT NOCOPY NUMBER
120 , x_rqoh OUT NOCOPY NUMBER
121 , x_qr OUT NOCOPY NUMBER
122 , x_qs OUT NOCOPY NUMBER
123 , x_att OUT NOCOPY NUMBER
124 , x_atr OUT NOCOPY NUMBER
125 , x_sqoh OUT NOCOPY NUMBER -- invConv change
126 , x_srqoh OUT NOCOPY NUMBER -- invConv change
127 , x_sqr OUT NOCOPY NUMBER -- invConv change
128 , x_sqs OUT NOCOPY NUMBER -- invConv change
129 , x_satt OUT NOCOPY NUMBER -- invConv change
130 , x_satr OUT NOCOPY NUMBER -- invConv change
131 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
132 , p_cost_group_id IN NUMBER DEFAULT NULL
133 , p_lpn_id IN NUMBER DEFAULT NULL
134 , p_transfer_locator_id IN NUMBER DEFAULT NULL
135 );
136 -- invConv changes end.
137
138 -- Procedure
139 -- update_quantities
140 -- Description
141 -- Update quantity at the level specified by the input and
142 -- return the quantities at the level after the update
143 PROCEDURE update_quantities
144 ( p_api_version_number IN NUMBER
145 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
146 , x_return_status OUT NOCOPY VARCHAR2
147 , x_msg_count OUT NOCOPY NUMBER
148 , x_msg_data OUT NOCOPY VARCHAR2
149 , p_tree_id IN INTEGER
150 , p_revision IN VARCHAR2 DEFAULT NULL
151 , p_lot_number IN VARCHAR2 DEFAULT NULL
152 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
153 , p_locator_id IN NUMBER DEFAULT NULL
154 , p_primary_quantity IN NUMBER
155 , p_quantity_type IN INTEGER
156 , x_qoh OUT NOCOPY NUMBER
157 , x_rqoh OUT NOCOPY NUMBER
158 , x_qr OUT NOCOPY NUMBER
159 , x_qs OUT NOCOPY NUMBER
160 , x_att OUT NOCOPY NUMBER
161 , x_atr OUT NOCOPY NUMBER
162 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
163 , p_cost_group_id IN NUMBER DEFAULT NULL
164 , p_containerized IN NUMBER DEFAULT inv_quantity_tree_pvt.g_containerized_false
165 , p_lpn_id IN NUMBER DEFAULT NULL
166 , p_transfer_locator_id IN NUMBER DEFAULT NULL
167 ) ;
168
169 -- invConv changes begin: overload
170 PROCEDURE update_quantities
171 ( p_api_version_number IN NUMBER
172 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
173 , x_return_status OUT NOCOPY VARCHAR2
174 , x_msg_count OUT NOCOPY NUMBER
175 , x_msg_data OUT NOCOPY VARCHAR2
176 , p_tree_id IN INTEGER
177 , p_revision IN VARCHAR2 DEFAULT NULL
178 , p_lot_number IN VARCHAR2 DEFAULT NULL
179 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
180 , p_locator_id IN NUMBER DEFAULT NULL
181 , p_primary_quantity IN NUMBER
182 , p_secondary_quantity IN NUMBER -- invConv change
183 , p_quantity_type IN INTEGER
184 , x_qoh OUT NOCOPY NUMBER
185 , x_rqoh OUT NOCOPY NUMBER
186 , x_qr OUT NOCOPY NUMBER
187 , x_qs OUT NOCOPY NUMBER
188 , x_att OUT NOCOPY NUMBER
189 , x_atr OUT NOCOPY NUMBER
190 , x_sqoh OUT NOCOPY NUMBER -- invConv change
191 , x_srqoh OUT NOCOPY NUMBER -- invConv change
192 , x_sqr OUT NOCOPY NUMBER -- invConv change
193 , x_sqs OUT NOCOPY NUMBER -- invConv change
194 , x_satt OUT NOCOPY NUMBER -- invConv change
195 , x_satr OUT NOCOPY NUMBER -- invConv change
196 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
197 , p_cost_group_id IN NUMBER DEFAULT NULL
198 , p_containerized IN NUMBER DEFAULT inv_quantity_tree_pvt.g_containerized_false
199 , p_lpn_id IN NUMBER DEFAULT NULL
200 , p_transfer_locator_id IN NUMBER DEFAULT NULL
201 ) ;
202 -- invConv changes end.
203
204 -- Procedure
205 -- do_check
206 -- Description
207 -- check whether the updates done in a tree so far are still valid
208 -- Return
209 -- x_no_violation = true if no violation has found
210 -- , otherwise = false
211 PROCEDURE do_check
212 ( p_api_version_number IN NUMBER
213 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
214 , x_return_status OUT NOCOPY VARCHAR2
215 , x_msg_count OUT NOCOPY NUMBER
216 , x_msg_data OUT NOCOPY VARCHAR2
217 , p_tree_id IN INTEGER
218 , x_no_violation OUT NOCOPY BOOLEAN
219 );
220
221 -- Procedure
222 -- do_check
223 -- Description
224 -- check whether the updates done in all trees so far are still valid
225 -- Return
226 -- x_no_violation = true if no violation has found
227 -- , otherwise = false
228 PROCEDURE do_check
229 ( p_api_version_number IN NUMBER
230 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
231 , x_return_status OUT NOCOPY VARCHAR2
232 , x_msg_count OUT NOCOPY NUMBER
233 , x_msg_data OUT NOCOPY VARCHAR2
234 , x_no_violation OUT NOCOPY BOOLEAN
235 );
236
237 -- Procedure
238 -- free_tree
239 -- Description
240 -- Free a tree when no longer needed.
241 -- Warning
242 -- If you have called update_quantities to change quantity on the tree
243 -- but have not make corresponding changes to the underlying database
244 -- tables, these changes are lost when you call free_tree.
245 PROCEDURE free_tree
246 ( p_api_version_number IN NUMBER
247 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
248 , x_return_status OUT NOCOPY VARCHAR2
249 , x_msg_count OUT NOCOPY NUMBER
250 , x_msg_data OUT NOCOPY VARCHAR2
251 , p_tree_id IN INTEGER
252 );
253
254 -- Procedure
255 -- backup_tree
256 -- Description
257 -- backup the current state of a tree
258 -- Note
259 -- This is only a one level backup. Calling it twice will
260 -- overwrite the previous backup
261 PROCEDURE backup_tree
262 (
263 x_return_status OUT NOCOPY VARCHAR2
264 , p_tree_id IN INTEGER
265 );
266
267 -- Procedure
268 -- restore_tree
269 -- Description
270 -- restore the current state of a tree to the state
271 -- at the last time when savepoint_tree is called
272 -- Note
273 -- This is only a one level restore. Calling it more than once
274 -- has the same effect as calling it once.
275 PROCEDURE restore_tree
276 (
277 x_return_status OUT NOCOPY VARCHAR2
278 , p_tree_id IN INTEGER
279 );
280
281 END inv_quantity_tree_grp;
282 /*
283 **show errors package INV_QUANTITY_TREE_GRP
284 **
285 **SELECT to_date('SQLERROR') FROM user_errors
286 **WHERE name = 'INV_QUANTITY_TREE_GRP'
287 **AND type = 'PACKAGE';
288 */