1 PACKAGE BODY PO_UOM_SV2 as
2 /* $Header: RCVTXUOB.pls 120.0.12000000.4 2007/10/18 14:01:22 grohit ship $*/
3
4 /*============================= PO_UOM_SV2 ==============================*/
5 /*===========================================================================
6
7 FUNCTION NAME : convert_inv_cost()
8
9 ===========================================================================*/
10 function convert_inv_cost(x_item_id in number,
11 x_current_uom in varchar2,
12 x_primary_uom in varchar2,
13 x_primary_inv_cost in number,
14 x_result_price in out NOCOPY number) return boolean is
15
16 convert_rate number := null;
17 progress varchar2(30) := null;
18 current_code varchar2(3) := null;
19 primary_code varchar2(3) := null;
20
21 begin
22
23 /* If the inventory price is zero or the primary UOM the
24 ** the current user's UOM are the same, there is no reason
25 ** to call the inventory convert routine.
26 */
27
28 if ((x_primary_inv_cost = 0) or
29 (x_current_uom = x_primary_uom) or
30 (x_primary_inv_cost is null)) then
31
32 return (FALSE);
33
34 else
35
36 /* Get the uom conversion rate between 2 UOMs for
37 ** a given item. We need to obtain the code for
38 ** the unit that we use in PO since Inventory's
39 ** routine expects to get the code.
40 */
41 progress := '005';
42
43 SELECT uom_code
44 INTO primary_code
45 FROM mtl_units_of_measure
46 WHERE unit_of_measure = x_primary_uom;
47
48 progress := '006';
49
50 /*
51 Bug 2810994
52 In the SELECT statement, changed mtl_units_of_measure to mtl_units_of_measure_vl
53 and unit_of_measure to unit_of_measure_tl to handle translated values
54 */
55 /* 4718263 changed where condtion of the below SQL: in place of unit_of_measure_tl unit_of_measure should be used.
56 from the Form we always pass unit_of_measure value not the translated value. */
57 SELECT uom_code
58 INTO current_code
59 FROM mtl_units_of_measure_vl
60 WHERE unit_of_measure = x_current_uom;
61
62 progress := '010';
63 inv_convert.inv_um_conversion(primary_code,
64 current_code,
65 x_item_id,
66 convert_rate);
67
68 x_result_price := round((x_primary_inv_cost/
69 convert_rate), 5);
70
71 return (TRUE);
72
73 end if;
74
75 return (FALSE);
76
77 exception
78
79 when others then
80 po_message_s.sql_error('convert_inv_cost', progress, sqlcode);
81 raise;
82
83 end convert_inv_cost;
84
85 /*===========================================================================
86
87 FUNCTION NAME : convert_quantity()
88
89 ===========================================================================*/
90 function convert_quantity(x_item_id in number,
91 x_source_org_id in number,
92 x_order_quantity in number,
93 x_order_uom in varchar2,
94 x_result_quantity in out NOCOPY number,
95 x_rounding_factor in out NOCOPY number,
96 x_unit_of_issue in out NOCOPY varchar2,
97 x_error_type in out NOCOPY varchar2) return boolean is
98
99 x_progress varchar2(3) := NULL;
100
101 begin
102
103 /* Select the unit of issue and rounding factor for the
104 ** soure organization and internally ordered item.
105 */
106
107 x_progress := '010';
108
109 SELECT nvl(msi.unit_of_issue, NULL),
110 nvl(msi.rounding_factor, NULL)
111 INTO x_unit_of_issue,
112 x_rounding_factor
113 FROM mtl_system_items msi
114 WHERE msi.inventory_item_id = x_item_id
115 AND msi.organization_id = x_source_org_id;
116
117 if ((x_unit_of_issue is null) or
118 (x_rounding_factor is null) or
119 (x_order_uom = x_unit_of_issue)) then
120
121 x_error_type := 'CONVERT_NOT_REQUIRED';
122 return (FALSE);
123
124 end if;
125
126 /* Call the UOM conversion routine to change the order quantity/order UOM
127 ** into the unit of issue quantity/UOM.
128 */
129
130 x_progress := '020';
131
132 x_result_quantity := inv_convert.inv_um_convert(x_item_id,
133 /* Precision */ 10,
134 x_order_quantity,
135 /* UOM Code */ NULL,
136 /* UOM Code */ NULL,
137 x_order_uom,
138 x_unit_of_issue);
139
140 /* A null result probably indicates bad data, and should
141 ** be handled by the client.
142 */
143
144 if (x_result_quantity is null) then
145 x_error_type := 'INV_UM_CONVERT_FAIL';
146 return (FALSE);
147 else
148 return (TRUE);
149 end if;
150
151 exception
152
153 when others then
154 po_message_s.sql_error('convert_quantity', x_progress, sqlcode);
155 raise;
156
157 end convert_quantity;
158
159 PROCEDURE reqimport_convert_uom_qty (x_request_id in NUMBER ) IS
160
161 /* Bug#2470849, This procedure is called from Reqimport and it converts
162 the uom to the unit_of_issue of the Source uom and also rounds the quantity
163 depending on the rounding factor */
164
165 CURSOR inv_items IS
166
167 select pri.rowid,
168 pri.item_id,
169 uomd.uom_code pri_uom_code,
170 uoms.uom_code issue_uom_code,
171 pri.quantity,
172 pri.source_organization_id,
173 pri.unit_of_measure,
174 msi.unit_of_issue,
175 msi.rounding_factor
176 from po_requisitions_interface pri,
177 mtl_system_items msi,
178 mtl_units_of_measure uoms,
179 mtl_units_of_measure uomd
180 where msi.inventory_item_id = pri.item_id
181 AND msi.organization_id = pri.source_organization_id
182 AND uoms.unit_of_measure = msi.unit_of_issue
183 AND uomd.unit_of_measure = pri.unit_of_measure
184 AND msi.unit_of_issue is NOT NULL
185 AND pri.source_type_code = 'INVENTORY'
186 AND pri.item_id is not NULL
187 AND pri.source_organization_id is NOT NULL
188 AND pri.unit_of_measure is NOT NULL
189 AND pri.quantity > 0
190 AND pri.request_id = x_request_id;
191
192 CURSOR Enforce_full_lot IS
193 select enforce_full_lot_quantities
194 from po_system_parameters;
195
196 x_quantity NUMBER;
197 x_round_quantity NUMBER;
198 remainder NUMBER;
199 x_rowid VARCHAR2(100);
200 x_enforce_full_lot_quantities VARCHAR2(30);
201
202 BEGIN
203 OPEN Enforce_full_lot;
204 FETCH Enforce_full_lot into x_enforce_full_lot_quantities;
205 if nvl(x_enforce_full_lot_quantities,'NONE') = 'NONE' then
206 CLOSE Enforce_full_lot;
207 return;
208 End if;
209 CLOSE Enforce_full_lot;
210 /* Bug# 3105048, Move the Close Cursor down. It was before the if
211 and in case enforce_full_lot_quantities was other than NULL or NONE we
212 would be closing the Cursor Twice which will lead to Invalid Cursor
213 Error. */
214
215 /* If enforce_full_lot_quantities is MANDATORY or ADVISORY then
216 execute the Code below. 'Advisory' will be treated as 'Mandatory'
217 for Requisitions created from Reqimport as per discussion with PM */
218
219 FOR pri_inv IN inv_items LOOP
220
221 /* Need to do the conversion only if the unit_of_issue and
222 unit_of_measure are different */
223
224 IF pri_inv.unit_of_measure <> pri_inv.unit_of_issue then
225
226 x_quantity:= inv_convert.inv_um_convert(pri_inv.item_id,
227 10,
228 pri_inv.quantity,
229 pri_inv.pri_uom_code,
230 pri_inv.issue_uom_code,
231 pri_inv.unit_of_measure,
232 pri_inv.unit_of_issue);
233 ELSE
234 x_quantity := pri_inv.quantity;
235
236 END IF;
237
238 /* if x_quantity is = -99999 then the inv api returned a Error
239 so don't process the row */
240
241 if nvl(x_quantity,-99999) <> -99999
242 and pri_inv.rounding_factor is NOT NULL then
243
244 if (x_quantity < 1) then
245 remainder := x_quantity;
246 else
247 remainder := mod(x_quantity, trunc(x_quantity));
248 end if;
249 if (remainder >= pri_inv.rounding_factor) then
250 x_round_quantity := trunc(x_quantity) + 1;
251 elsif (remainder < pri_inv.rounding_factor) then
252 x_round_quantity := trunc(x_quantity);
253 end if;
254
255 update po_requisitions_interface
256 set (uom_code,
257 unit_of_measure,
258 quantity )= (Select pri_inv.issue_uom_code,
259 pri_inv.unit_of_issue,
260 x_round_quantity
261 from sys.dual)
262 where rowid= pri_inv.rowid;
263
264 end if;
265
266 END LOOP;
267 END reqimport_convert_uom_qty;
268
269
270 END PO_UOM_SV2;
[Home] [Help]