1 PACKAGE INV_CONSIGNED_VALIDATIONS AS
2 /* $Header: INVVMILS.pls 120.1.12010000.2 2008/07/29 12:55:34 ptkumar ship $ */
3
4 /** This package is created as part of VMI quantities in patchset H
5 ** and continue to support Consigned inventory from patchset I */
6
7 /*------------------*
8 * Global variables *
9 *------------------*/
10 -- Onhand Source
11 -- Defined in mtl_onhand_source lookup
12 -- Used to determine which subs are included in calculation of
13 -- onhand qty
14 g_atpable_only CONSTANT NUMBER := 1;
15 g_nettable_only CONSTANT NUMBER := 2;
16 g_all_subs CONSTANT NUMBER := 3;
17
18 -- Containerized
19 -- Used to indicate packed quantities for use in quantity calculations
20 -- If 0, then record is not packed in container.
21 -- If 1, then record is packed in containter.
22 g_containerized_true CONSTANT NUMBER := 1;
23 g_containerized_false CONSTANT NUMBER := 0;
24
25 -- pjm support
26 g_unit_eff_enabled VARCHAR(1) := NULL;
27
28 --Modes
29 g_reservation_mode CONSTANT INTEGER := 1;
30 g_transaction_mode CONSTANT INTEGER := 2;
31 g_loose_only_mode CONSTANT INTEGER := 3;
32
33 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_VMI_VALIDATIONS';
34
35 -- Query Mode
36 -- This is used when query VMI/CONSIGNED quantity
37 -- G_TXN_MODE(Transaction Mode) is for VMI/Consign Receipt/Issue, and
38 -- Min-Max report of VMI stock. API will query the quantity tree and
39 -- returns the minimum of available-to-transact(from tree) and VMI/Consign
40 -- onhand quantity
41 -- G_XFR_MODE(Transfer mode) is for VMI/Consign transfer to regular transaction
42 -- Because there is no quantity change, only the property of onhand will change
43 -- , it does not query quantity tree, only returns the VMI/Consigned quantity
44 -- G_REG_MODE(Regular mode) is for 'Regular transfer to consign' transaction
45 -- It will return non-consigned onhand quantity.
46 G_TXN_MODE CONSTANT INTEGER :=1;
47 G_XFR_MODE CONSTANT INTEGER :=2;
48 G_REG_MODE CONSTANT INTEGER :=3;
49
50 /*----------------*
51 * API Spec *
52 *----------------*/
53
54 --The API GET_CONSIGNED_QUANTITY returns the onhand quantity and
55 -- available to transact quantities for three kinds of VMI/CONSIGN
56 -- related transactions.
57 -- It will first check whether there is data populated in global
58 -- temp table mtl_consigned_qty_temp (invvmitb.sql). If not, it first
59 -- populate the temp table with data in MOQD where
60 -- organization_id <> planning_organization_id or
61 -- organization_id <> owning_organization_id
62 -- Then it queries the onhand and available-to-transact quantity
63 -- depends on the query mode.
64 -- The return values for the three query modes are
65 /*
66 Query Mode Value Meaning Consider Quantity Returned Value
67 Reservation? x_att x_qoh
68 ---------- ----- ----------- ------------ -------------------- -------------------
69 G_TXN_MODE 1 VMI/Consign Y VCATT QOH VCATT= Min(QATT, VCOH)
70 Misc Txn (from Qty tree)
71
72 G_XRF_MODE 2 VMI/Consign N VCOH VCOH VMI/Consign onhand qty
73 Transafer to reg from the global temp table
74
75 G_REG_MODE 3 Regular Transfer N ROH ROH Quantity from MOQD where
76 to Consigned txn organization_id = owning_organization_id
77 = p_organization_id
78 */
79
80 PROCEDURE GET_CONSIGNED_QUANTITY(
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_return_msg OUT NOCOPY VARCHAR2,
83 p_tree_mode IN NUMBER,
84 p_organization_id IN NUMBER,
85 p_owning_org_id IN NUMBER,
86 p_planning_org_id IN NUMBER,
87 p_inventory_item_id IN NUMBER,
88 p_is_revision_control IN VARCHAR2,
89 p_is_lot_control IN VARCHAR2,
90 p_is_serial_control IN VARCHAR2,
91 p_revision IN VARCHAR2,
92 p_lot_number IN VARCHAR2,
93 p_lot_expiration_date IN DATE,
94 p_subinventory_code IN VARCHAR2,
95 p_locator_id IN NUMBER,
96 p_source_type_id IN NUMBER DEFAULT -999,
97 p_demand_source_line_id IN NUMBER DEFAULT NULL,
98 p_demand_source_header_id IN NUMBER DEFAULT -999,
99 p_demand_source_name IN VARCHAR2 DEFAULT NULL,
100 p_onhand_source IN NUMBER DEFAULT g_all_subs,
101 p_cost_group_id IN NUMBER,
102 p_query_mode IN NUMBER,
103 x_qoh OUT NOCOPY NUMBER,
104 x_att OUT NOCOPY NUMBER);
105
106 -- invConv changes begin : overloading version :
107 PROCEDURE GET_CONSIGNED_QUANTITY(
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_return_msg OUT NOCOPY VARCHAR2,
110 p_tree_mode IN NUMBER,
111 p_organization_id IN NUMBER,
112 p_owning_org_id IN NUMBER,
113 p_planning_org_id IN NUMBER,
114 p_inventory_item_id IN NUMBER,
115 p_is_revision_control IN VARCHAR2,
116 p_is_lot_control IN VARCHAR2,
117 p_is_serial_control IN VARCHAR2,
118 p_revision IN VARCHAR2,
119 p_lot_number IN VARCHAR2,
120 p_lot_expiration_date IN DATE,
121 p_subinventory_code IN VARCHAR2,
122 p_locator_id IN NUMBER,
123 p_grade_code IN VARCHAR2, -- invConv change
124 p_source_type_id IN NUMBER DEFAULT -999,
125 p_demand_source_line_id IN NUMBER DEFAULT NULL,
126 p_demand_source_header_id IN NUMBER DEFAULT -999,
127 p_demand_source_name IN VARCHAR2 DEFAULT NULL,
128 p_onhand_source IN NUMBER DEFAULT g_all_subs,
129 p_cost_group_id IN NUMBER,
130 p_query_mode IN NUMBER,
131 x_qoh OUT NOCOPY NUMBER,
132 x_att OUT NOCOPY NUMBER,
133 x_sqoh OUT NOCOPY NUMBER, -- invConv change
134 x_satt OUT NOCOPY NUMBER); -- invConv change
135 -- invConv changes end.
136
137
138 -- This API will allow update of the existing temp table.
139 ---This API needs to be called after a transaction is commited or
140 -- when moving onto the next line for the same transaction without a
141 --commit.
142
143 PROCEDURE update_consigned_quantities
144 ( x_return_status OUT NOCOPY varchar2
145 , x_msg_count OUT NOCOPY varchar2
146 , x_msg_data OUT NOCOPY varchar2
147 , p_organization_id IN NUMBER
148 , p_inventory_item_id IN NUMBER
149 , p_revision IN VARCHAR2
150 , p_lot_number IN VARCHAR
151 , p_subinventory_code IN VARCHAR2
152 , p_locator_id IN NUMBER
153 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
154 , p_primary_quantity IN NUMBER
155 , p_secondary_quantity IN NUMBER DEFAULT NULL -- invConv change
156 , p_cost_group_id IN NUMBER
157 , p_containerized IN NUMBER DEFAULT 2
158 , p_planning_organization_id IN NUMBER
159 , p_owning_organization_id IN number
160 );
161
162
163 -- This api wil be called to decide whether to consume the consigned or VMI
164 --item according to the set up in the consumption form
165 -- X_CONSUME_CONSIGNED: 1 => Consume , 0 => Do not consume
166 -- X_CONSUME_VMI: 1 => Consume , 0 => Do not consume
167 PROCEDURE CHECK_CONSUME
168 (
169 P_TRANSACTION_TYPE_ID IN NUMBER,
170 P_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
171 P_SUBINVENTORY_CODE IN VARCHAR2 DEFAULT NULL,
172 P_XFER_SUBINVENTORY_CODE IN VARCHAR2 DEFAULT NULL,
173 p_from_locator_id IN NUMBER DEFAULT NULL,
174 p_TO_locator_id IN NUMBER DEFAULT NULL,
175 P_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
176 P_OWNING_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
177 P_PLANNING_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
178 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
179 X_MSG_COUNT OUT NOCOPY NUMBER,
180 X_MSG_DATA OUT NOCOPY VARCHAR2,
181 X_CONSUME_CONSIGNED OUT NOCOPY NUMBER,
182 X_CONSUME_VMI OUT NOCOPY NUMBER
183 );
184
185 -- This API checks whether there is existing pending transactions
186 -- for consign transfer txns.
187 FUNCTION check_pending_transactions(
188 P_ORGANIZATION_ID IN NUMBER,
189 P_SUBINVENTORY_CODE IN VARCHAR2,
190 p_locator_id IN VARCHAR2 DEFAULT NULL,
191 p_item_id IN NUMBER,
192 p_lpn_id IN NUMBER DEFAULT NULL
193 ) RETURN VARCHAR2;
194
195
196 -- This API returns the onhand quantity for planning purpose
197 -- , which does not include VMI quantity
198 -- The quantity is calculated with onhand quantity from
199 -- MTL_ONHAND_QUANTITIES_DETAIL and pending transactions from
200 -- MTL_MATERIAL_TRANSACTIONS_TEMP
201 -- The quantities does not include suggestions
202 -- Input Parameters
203 -- P_INCLUDE_NONNET: Whether include non-nettable subinventories
204 -- Values: 1 => Include non-nettable subinventories
205 -- 2 => Only include nettabel subinventores
206 -- P_LEVEL: Query onhand at Organization level (1)
207 -- or Subinventory level (2)
208 -- P_ORG_ID: Organization ID
209 -- P_SUBINV: Subinventory
210 -- P_ITEM_ID: Item ID
211
212 -- Note that this may includes pending transactions that
213 -- will keep the VMI attributes of inventory stock
214 FUNCTION GET_PLANNING_QUANTITY(
215 P_INCLUDE_NONNET NUMBER
216 , P_LEVEL NUMBER
217 , P_ORG_ID NUMBER
218 , P_SUBINV VARCHAR2
219 , P_ITEM_ID NUMBER
220 ) RETURN NUMBER;
221
222 -- invConv changes begin : new procedure because GET_PLANNING_QUANTITY based on original function GET_PLANNING_QUANTITY.
223 PROCEDURE GET_PLANNING_QUANTITY(
224 P_INCLUDE_NONNET IN NUMBER
225 , P_LEVEL IN NUMBER
226 , P_ORG_ID IN NUMBER
227 , P_SUBINV IN VARCHAR2
228 , P_ITEM_ID IN NUMBER
232 -- invConv changes end.
229 , P_GRADE_CODE IN VARCHAR2 -- invConv change
230 , X_QOH OUT NOCOPY NUMBER -- invConv change
231 , X_SQOH OUT NOCOPY NUMBER); -- invConv change
233
234
235 -- Bug 4247148: Added a new function to get the onhand qty
236 -- This API returns the onhand quantity for planning purpose
237 -- , which does not include VMI quantity based on atp/nettable/all subs
238 -- The quantity is calculated with onhand quantity from
239 -- MTL_ONHAND_QUANTITIES_DETAIL and pending transactions from
240 -- MTL_MATERIAL_TRANSACTIONS_TEMP
241 -- The quantities does not include suggestions
242 -- Input Parameters
243 -- P_INCLUDE_NONNET: Whether include non-nettable subinventories
244 -- Values: g_atpable_only => Include only atpable subinventories
245 -- g_netable_only => Only include nettabel subinventores
246 -- g_allsubs => Include all subinventores
247 -- P_ORG_ID: Organization ID
248 -- P_ITEM_ID: Item ID
249
250 -- Note that this may includes pending transactions that
251 -- will keep the VMI attributes of inventory stock
252 FUNCTION get_planning_sd_quantity
253 (
254 P_ONHAND_SOURCE NUMBER
255 , P_ORG_ID NUMBER
256 , P_ITEM_ID NUMBER
257 ) RETURN NUMBER;
258
259 --Bug#6157532. Overloaded the procedure for the case
260 --where LPN is involved .
264 p_tree_mode IN NUMBER,
261 PROCEDURE GET_CONSIGNED_LPN_QUANTITY(
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_return_msg OUT NOCOPY VARCHAR2,
265 p_organization_id IN NUMBER,
266 p_owning_org_id IN NUMBER,
267 p_planning_org_id IN NUMBER,
268 p_inventory_item_id IN NUMBER,
269 p_is_revision_control IN VARCHAR2,
270 p_is_lot_control IN VARCHAR2,
271 p_is_serial_control IN VARCHAR2,
272 p_revision IN VARCHAR2,
273 p_lot_number IN VARCHAR2,
274 p_lot_expiration_date IN DATE,
275 p_subinventory_code IN VARCHAR2,
276 p_locator_id IN NUMBER,
277 p_source_type_id IN NUMBER,
278 p_demand_source_line_id IN NUMBER,
279 p_demand_source_header_id IN NUMBER,
280 p_demand_source_name IN VARCHAR2,
281 p_onhand_source IN NUMBER,
282 p_cost_group_id IN NUMBER,
283 p_query_mode IN NUMBER,
284 p_lpn_id IN NUMBER,
285 x_qoh OUT NOCOPY NUMBER,
286 x_att OUT NOCOPY NUMBER) ;
287
288 PROCEDURE clear_vmi_cache ; --Bug#6157532.Added this procedure in SPEC.
289
290
291 END INV_CONSIGNED_VALIDATIONS;
[Home] [Help]