1 PACKAGE INV_CONSIGNED_VALIDATIONS_GRP AUTHID CURRENT_USER AS
2 /* $Header: INVVMIGS.pls 120.1 2005/06/16 15:30:01 appldev $ */
3
4 /** This package is created as part of Consign Inventory in patchset I
5 ** This contains the public APIs that can be called from other product*/
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_CONSIGNED_VALIDATIONS_GRP';
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 intern calls INV_CONSIGNED_VALIDATIONS.GET_CONSIGNED_QUANTITY()
58
59 PROCEDURE GET_CONSIGNED_QUANTITY(
60 p_api_version_number IN NUMBER DEFAULT 1.0
61 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
62 , x_return_status OUT NOCOPY VARCHAR2
63 , x_msg_count OUT NOCOPY NUMBER
64 , x_msg_data OUT NOCOPY VARCHAR2
65 , p_tree_mode IN NUMBER
66 , p_organization_id IN NUMBER
67 , p_owning_org_id IN NUMBER
68 , p_planning_org_id IN NUMBER
69 , p_inventory_item_id IN NUMBER
70 , p_is_revision_control IN VARCHAR2
71 , p_is_lot_control IN VARCHAR2
72 , p_is_serial_control IN VARCHAR2
73 , p_revision IN VARCHAR2
74 , p_lot_number IN VARCHAR2
75 , p_lot_expiration_date IN DATE
76 , p_subinventory_code IN VARCHAR2
77 , p_locator_id IN NUMBER
78 , p_source_type_id IN NUMBER DEFAULT -999
79 , p_demand_source_line_id IN NUMBER DEFAULT NULL
80 , p_demand_source_header_id IN NUMBER DEFAULT -999
81 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
82 , p_onhand_source IN NUMBER DEFAULT g_all_subs
83 , p_cost_group_id IN NUMBER
84 , p_query_mode IN NUMBER
85 , x_qoh OUT NOCOPY NUMBER
86 , x_att OUT NOCOPY NUMBER
87 );
88
89 -- invConv changes begin : Overloaded API
90 PROCEDURE GET_CONSIGNED_QUANTITY(
91 p_api_version_number IN NUMBER
92 , p_init_msg_lst IN VARCHAR2
93 , x_return_status OUT NOCOPY VARCHAR2
94 , x_msg_count OUT NOCOPY NUMBER
95 , x_msg_data OUT NOCOPY VARCHAR2
96 , p_tree_mode IN NUMBER
97 , p_organization_id IN NUMBER
98 , p_owning_org_id IN NUMBER
99 , p_planning_org_id IN NUMBER
100 , p_inventory_item_id IN NUMBER
101 , p_is_revision_control IN VARCHAR2
102 , p_is_lot_control IN VARCHAR2
103 , p_is_serial_control IN VARCHAR2
104 , p_revision IN VARCHAR2
105 , p_lot_number IN VARCHAR2
106 , p_lot_expiration_date IN DATE
107 , p_subinventory_code IN VARCHAR2
108 , p_locator_id IN NUMBER
109 , p_grade_code IN VARCHAR2 -- invConv changes
110 , p_source_type_id IN NUMBER
111 , p_demand_source_line_id IN NUMBER
112 , p_demand_source_header_id IN NUMBER
113 , p_demand_source_name IN VARCHAR2
114 , p_onhand_source IN NUMBER
115 , p_cost_group_id IN NUMBER
116 , p_query_mode IN NUMBER
117 , x_qoh OUT NOCOPY NUMBER
118 , x_att OUT NOCOPY NUMBER
119 , x_sqoh OUT NOCOPY NUMBER -- invConv changes
120 , x_satt OUT NOCOPY NUMBER); -- invConv changes
121 -- invConv changes end.
122
123 -- This API returns the onhand quantity for planning purpose
124 -- , which does not include VMI quantity
125 -- The quantity is calculated with onhand quantity from
126 -- MTL_ONHAND_QUANTITIES_DETAIL and pending transactions from
127 -- MTL_MATERIAL_TRANSACTIONS_TEMP
128 -- The quantities does not include suggestions
129 -- Input Parameters
130 -- P_INCLUDE_NONNET: Whether include non-nettable subinventories
131 -- Values: 1 => Include non-nettable subinventories
132 -- 2 => Only include nettabel subinventores
133 -- P_LEVEL: Query onhand at Organization level (1)
134 -- or Subinventory level (2)
135 -- P_ORG_ID: Organization ID
136 -- P_SUBINV: Subinventory
137 -- P_ITEM_ID: Item ID
138
139 -- Note that this may includes pending transactions that
140 -- will keep the VMI attributes of inventory stock
141
142 -- It intern calls INV_CONSIGNED_VALIDATIONS.GET_PLANNING_QUANTITY()
143 PROCEDURE GET_PLANNING_QUANTITY(
144 p_api_version_number IN NUMBER DEFAULT 1.0
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_include_nonnet IN NUMBER
150 , p_level IN NUMBER
151 , p_org_id IN NUMBER
152 , p_subinv IN VARCHAR2
153 , p_item_id IN NUMBER
154 , x_planning_qty OUT NOCOPY NUMBER
155 );
156
157 -- invConv changes begin : new overloaded API
158 PROCEDURE GET_PLANNING_QUANTITY(
159 p_api_version_number IN NUMBER
160 , p_init_msg_lst IN VARCHAR2
161 , x_return_status OUT NOCOPY VARCHAR2
162 , x_msg_count OUT NOCOPY NUMBER
163 , x_msg_data OUT NOCOPY VARCHAR2
164 , p_include_nonnet IN NUMBER
165 , p_level IN NUMBER
166 , p_org_id IN NUMBER
167 , p_subinv IN VARCHAR2
168 , p_item_id IN NUMBER
169 , p_grade_code IN VARCHAR2 -- invConv change
170 , x_planning_qty OUT NOCOPY NUMBER
171 , x_planning_sqty OUT NOCOPY NUMBER); -- invConv change
172 -- invConv changes end.
173
174 --Bug 4239469: Added this new function to get the available qty
175 -- This API returns the onhand quantity for planning purpose
176 -- , which does not include VMI quantity but checks ATP condition also
177 -- The quantity is calculated with onhand quantity from
178 -- MTL_ONHAND_QUANTITIES_DETAIL and pending transactions from
179 -- MTL_MATERIAL_TRANSACTIONS_TEMP
180 -- The quantities does not include suggestions
181 -- Input Parameters
182 -- P_ONHAND_SOURCE: Whether include non-nettable subinventories
183 -- Values: g_atpable_only => Only Include atpable subinventories
184 -- g_nettable_only => Only include nettabl subinventores
185 -- g_all_subs => include all subinventores
186 -- P_ORG_ID: Organization ID
187 -- P_ITEM_ID: Item ID
188
189 -- Note that this may includes pending transactions that
190 -- will keep the VMI attributes of inventory stock
191
192 -- It intern calls INV_CONSIGNED_VALIDATIONS.GET_PLANNING_SD_QUANTITY()
193 PROCEDURE get_planning_sd_quantity
194 (
195 p_api_version_number IN NUMBER DEFAULT 1.0
196 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
197 , x_return_status OUT NOCOPY VARCHAR2
198 , x_msg_count OUT NOCOPY NUMBER
199 , x_msg_data OUT NOCOPY VARCHAR2
200 , p_onhand_source IN NUMBER
201 , p_org_id IN NUMBER
202 , p_item_id IN NUMBER
203 , x_planning_qty OUT NOCOPY NUMBER
204 );
205
206
207 END INV_CONSIGNED_VALIDATIONS_GRP;