1 PACKAGE inv_decimals_pub AUTHID CURRENT_USER AS
2 /* $Header: INVDECPS.pls 120.1 2005/06/09 17:55:36 appldev $ */
3
4
5 /*--------------------------------------------------------------------------+
6 |Procedure validate_compare_quantities(..)
7 |Returns the quantity converted from the first UOM in the second UOM.
8 |If quantities in 2 UOMs are already available, then this procedure will
9 |compare and validate these quantities based on conversion rates
10 |and UOM and decimal qty controls. This procedure may be used to validate
11 |scenarios where quatities are entered in dual UOMs. We want to make sure
12 |quantities are valid based on conversion, and conversion
13 |rate tolerances.
14 |
15 |Procedure validate_and_compare(
16 |p_api_version_number IN NUMBER, -- version # of API
17 |p_init_msg_list IN VARCHAR2, -- whether to initialize list
18 |p_inventory_item_id IN NUMBER, -- inventory_item_id
19 |p_organization_id IN NUMBER, -- organization_id
20 |p_lot_control_code IN NUMBER, -- item's lot control code
21 |p_lot_number IN VARCHAR2, -- lot number
22 |p_sub_lot_control_code IN NUMBER, --sub lot control code
23 |p_sublot_number IN VARCHAR2, -- sublot number
24 |p_from_quantity IN NUMBER, -- qty in first UOM
25 |p_from_uom_code IN VARCHAR2, -- UOM of fisrt qty
26 |p_to_uom_code IN VARCHAR2, -- UOM of second qty
27 |p_to_quantity_to_check IN NUMBER, -- qty in second UOM
28 |x_resultant_to_quantity OUT NUMBER, -- calculated qty in second UOM
29 |x_comparison OUT NUMBER,--Possible values are 1,0,-1,-99
30 |x_msg_count OUT NUMBER, -- number of messages
31 |x_msg_data OUT VARCHAR2, -- populated,if msg count = 1
32 |x_return_status OUT VARCHAR2) -- return status
33 |
34 |Note: The comparisons are done in base UOM
35 | of the UOM class to which the first UOM belongs. x_comparison returns:
36 |-1 if from_quantity is less than to_quantity (A < B)
37 | 0 if from_quantity is equal to to_quantity (A = B)
38 | 1 if from_quantity is greater than to_quantity (A > B)
39 | -99 if the validations for the first/second quantity failed
40 | If the UOMs belong to different classes, then users can specify whether
41 | they want to use the effective interclass UOM conversion tolerance, say, T.
42 | CASE: p_use_interclass_tolerance = 1
43 | ------
44 | Q1 > Q2 if (Q1 - Q2) >= T
45 | Q1 = Q2 if ABS(Q1 - Q2) < T
46 | Q1 < Q2 if (Q1 - Q2 ) <= -T
47 |
48 |The output variable x_resultant_to_quantity will contain the converted
49 |quantity
50 |in the second UOM, using effective conversion rates.
51 |Usage: In a dual UOM scenario, this api will confirm whether quantities in
52 |the two UOMs are equal or not, based on x_comparison output variable.
53 +--------------------------------------------------------------------------*/
54
55 Procedure validate_compare_quantities(
56 p_api_version_number IN NUMBER,
57 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
58 p_inventory_item_id IN NUMBER,
59 p_organization_id IN NUMBER,
60 p_lot_control_code IN NUMBER,
61 p_lot_number IN VARCHAR2,
62 p_sub_lot_control_code IN NUMBER,
63 p_sublot_number IN VARCHAR2,
64 p_from_quantity IN NUMBER,
65 p_from_uom_code IN VARCHAR2,
66 p_to_uom_code IN VARCHAR2,
67 p_to_quantity_to_check IN NUMBER,
68 x_resultant_to_quantity OUT NOCOPY NUMBER,
69 x_valid_conversion OUT NOCOPY NUMBER,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2,
72 x_return_status OUT NOCOPY VARCHAR2);
73
74
75 /*--------------------------------------------------------------------------+
76 |Function convert_UOM(..) return NUMBER ;
77 |Returns the quantity converted from the first unit into the second unit.
78 |If conversion is not possible, return status is failure.
79 |Function convert(
80 |p_api_version_number IN NUMBER,
81 |p_init_msg_list IN VARCHAR2, -- whether to initialize list
82 |p_inventory_item_id IN NUMBER, -- inventory_item_id
83 |p_organization_id IN NUMBER, -- organization_id
84 |p_lot_control_code IN NUMBER, -- item's lot control code
85 |p_lot_number IN VARCHAR2, -- lot number
86 |p_sub_lot_control_code IN NUMBER,
87 |p_sublot_number IN VARCHAR2,
88 |p_from_quantity IN NUMBER, -- qty in first UOM
89 |p_from_uom_code IN VARCHAR2, -- UOM of fisrt qty
90 |p_to_uom_code IN VARCHAR2, -- UOM of second qty
91 |x_msg_count OUT NUMBER,
92 |x_msg_data OUT VARCHAR2,
93 |x_return_status OUT VARCHAR2)
94 | return NUMBER ;
95 |If there is an error, then -99 is returned.
96 |1) From_quantity must be an absolute value.
97 |2) From_quantity will then converted to base UOM in the class,
98 |3) Then converted to base UOM of the
99 | to_UOM class,
100 |4) Then converted to the quantity in to_UOM,
101 |5) Then rounded to 9 decimals
102 +--------------------------------------------------------------------------*/
103
104 Function convert_UOM(
105 p_api_version_number IN NUMBER,
106 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
107 p_inventory_item_id IN NUMBER,
108 p_organization_id IN NUMBER,
109 p_lot_control_code IN NUMBER,
110 p_lot_number IN VARCHAR2,
111 p_sub_lot_control_code IN NUMBER,
112 p_sublot_number IN VARCHAR2,
113 p_from_quantity IN NUMBER,
114 p_from_uom_code IN VARCHAR2,
115 p_to_uom_code IN VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2,
118 x_return_status OUT NOCOPY VARCHAR2) return NUMBER ;
119
120
121 /*--------------------------------------------------------------------------+
122 | get_uom_properties(..)
123 | This procedure is used to interrogate the UOM.
124 | It returns:
125 | uom class, base uom.
126 | if the UOM is not found, the return status indicates this.
127 | Procedure get_uom_properties(
128 | p_api_version_number IN NUMBER,
129 | p_init_msg_list IN VARCHAR2,
130 | p_uom_code IN VARCHAR2,
131 | x_uom_class OUT VARCHAR2,
132 | x_base_uom OUT VARCHAR2,
133 | x_msg_count OUT NUMBER,
134 | x_msg_data OUT VARCHAR2,
135 | x_return_status OUT VARCHAR2);
136 +--------------------------------------------------------------------------*/
137
138
139 Procedure get_uom_properties(
140 p_api_version_number IN NUMBER,
141 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
142 p_uom_code IN VARCHAR2,
143 x_uom_class OUT NOCOPY VARCHAR2,
144 x_base_uom OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 x_return_status OUT NOCOPY VARCHAR2);
148
149
150 /*-------------------------------------------------------------------------+
151 | Procedure compare_quantities(..)
152 | Procedure compare_quantities(
153 | p_api_version_number IN NUMBER,
154 | p_init_msg_list IN VARCHAR2,
155 | p_inventory_item_id IN NUMBER,
156 | p_organization_id IN NUMBER,
157 | p_lot_control_code IN NUMBER,
158 | p_lot_number IN VARCHAR2,
159 | p_sub_lot_control_code IN NUMBER,
160 | p_sublot_number IN VARCHAR2,
161 | p_fisrt_qauantity IN NUMBER,
162 | p_first_uom IN VARCHAR2,
163 | p_second_quantity IN NUMBER,
164 | p_second_uom IN VARCHAR2,
165 | p_use_interclass_tolerance IN VARCHAR2, -- Yes = 1, 2 = No
166 | x_comaprison_result OUT NUMBER,
167 | x_msg_count OUT NUMBER,
168 | x_msg_data OUT VARCHAR2,
169 | x_return_status OUT VARCHAR2);
170 |
171 | This procedure compares the quantities A and B and returns result in the
172 | output variable x_comparison_result. The comparisons are done in base UOM
173 | of the UOM class to which the first UOM belongs:
174 |-1 if quantity A is less than quantity B (A < B)
175 | 0 if quantity A is equal to quantity B (A = B)
176 | 1 if quantity A is greater than quantity B (A > B)
177 | If the UOMs belong to different classes, then users can specify whether
178 | they want to use interclass UOM conversion tolerance, say, T.
179 | CASE: p_use_interclass_tolerance = 1
180 | ------
181 | Q1 > Q2 if (Q1 - Q2) >= T
182 | Q1 = Q2 if ABS(Q1 - Q2) < T
183 | Q1 < Q2 if (Q1 - Q2 ) <= -T
184 +------------------------------------------------------------------------*/
185
186 Procedure compare_quantities(
187 p_api_version_number IN NUMBER,
188 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
189 p_inventory_item_id IN NUMBER,
190 p_organization_id IN NUMBER,
191 p_lot_control_code IN NUMBER,
192 p_lot_number IN VARCHAR2,
193 p_sub_lot_control_code IN NUMBER,
194 p_sublot_number IN VARCHAR2,
195 p_fisrt_qauantity IN NUMBER,
196 p_first_uom IN VARCHAR2,
197 p_second_quantity IN NUMBER,
198 p_second_uom IN VARCHAR2,
199 p_use_interclass_tolerance IN VARCHAR2,
200 x_comaprison_result OUT NOCOPY NUMBER,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2);
204
205
206 /*-----------------------------------------------------------------------+
207 | Procedure Validate_Quantity(
208 | p_item_id IN NUMBER,
209 | p_organization_id IN NUMBER,
210 | p_input_quantity IN NUMBER,
211 | p_UOM_code IN VARCHAR2,
212 | x_output_quantity OUT NUMBER,
213 | x_primary_quantity OUT NUMBER,
214 | x_return_status OUT VARCHAR2);
215 |
216 | Validates and returns the quantity in this manner (the caller does not need
217 | to adjust the result):
218 | This routine checks to make sure that the input quantity precision does not exceed
219 | the decimal precision. Max Precision is: 10 digits before the decimal point and
220 | 9 digits after the decimal point.
221 | The routine also makes sure that if the item is serial number controlled, then
222 | the quantity in primary UOM is an integer number.
223 | The routine also makes sure that if the item's indivisible_flag is set to yes,
224 | then the item quantity is an integer in the primary UOM.
225 | The routine also checks if the profile, INV:DETECT TRUNCATION, is set to yes
226 | the item quantity in primary UOM also obeys max precision and that it is not zero
227 | if the input quantity was not zero.
228 | The procedure retruns a correct output quantity in the transaction UOM, returns the
229 | the primary quantity (in priamry UOM, of course), and returns a status of success,failure
230 | or warning.
231 +-------------------------------------------------------------------------*/
232
233 Procedure Validate_Quantity(
234 p_item_id IN NUMBER,
235 p_organization_id IN NUMBER,
236 p_input_quantity IN NUMBER,
237 p_UOM_code IN VARCHAR2,
238 x_output_quantity OUT NOCOPY NUMBER,
239 x_primary_quantity OUT NOCOPY NUMBER,
240 x_return_status OUT NOCOPY VARCHAR2);
241
242 /*------------------------------------------------------------------------+
243 | Procedure Validate_Quantity(
244 | p_item_id IN NUMBER,
245 | p_organization_id IN NUMBER,
246 | p_input_quantity IN NUMBER,
247 | p_UOM_code IN VARCHAR2,
248 | p_max_decimal_digits IN NUMBER DEFAULT NULL,
249 | x_output_quantity OUT NUMBER,
250 | x_primary_quantity OUT NUMBER,
251 | x_return_status OUT VARCHAR2);
252 |
253 | This procedure overloads validate_quantity with one more parameter p_max_decimal_digits
254 | to adjust max precision. If the value of p_max_decimal_digits is null, then default
255 | it to g_max_decimal_digits. Other works the same as the above Validate_Quantity procedure.
256 +-------------------------------------------------------------------------*/
257
258 Procedure Validate_Quantity(
259 p_item_id IN NUMBER,
260 p_organization_id IN NUMBER,
261 p_input_quantity IN NUMBER,
262 p_UOM_code IN VARCHAR2,
263 p_max_decimal_digits IN NUMBER,
264 p_primary_uom IN VARCHAR2,
265 p_indivisible_flag IN VARCHAR2,
266 x_output_quantity OUT NOCOPY NUMBER,
267 x_primary_quantity OUT NOCOPY NUMBER,
268 x_return_status OUT NOCOPY VARCHAR2);
269
270
271 function get_primary_quantity(
272 p_organization_id IN NUMBER,
273 p_inventory_item_id IN NUMBER,
274 p_uom IN VARCHAR2,
275 p_quantity IN NUMBER) return number;
276
277
278
279 end INV_DECIMALS_PUB;