1 PACKAGE BODY PO_ITEMS_SV2 as
2 /* $Header: POXCOI2B.pls 120.0 2005/06/01 18:36:46 appldev noship $ */
3 /*============================= PO_ITEMS_SV2 ===============================*/
4
5 /*===========================================================================
6
7 PROCEDURE NAME: get_item_details()
8
9 ===========================================================================*/
10 procedure get_item_details( X_item_id IN NUMBER,
11 X_inventory_organization_id IN NUMBER,
12 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
13 X_outside_operation_flag IN OUT NOCOPY VARCHAR2,
14 X_outside_op_uom_type IN OUT NOCOPY VARCHAR2,
15 X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
16 X_receive_close_tolerance IN OUT NOCOPY NUMBER,
17 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
18 X_stock_enabled_flag IN OUT NOCOPY VARCHAR2,
19 X_internal_orderable IN OUT NOCOPY VARCHAR2,
20 X_purchasing_enabled IN OUT NOCOPY VARCHAR2,
21 X_inventory_asset_flag IN OUT NOCOPY VARCHAR2,
22 /* INVCONV BEGIN PBAMB */
23 X_secondary_default_ind IN OUT NOCOPY VARCHAR2,
24 X_grade_control_flag IN OUT NOCOPY VARCHAR2,
25 X_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2
26 /* INVCONV END PBAMB */) IS
27
28 X_Progress varchar2(3) := '';
29
30 begin
31
32 X_Progress := '010';
33 /*Bug 979118
34 If the item's MRP Planning codes are (MRP/DRP-7
35 MPS/DRP-8
36 DRP -9) then the item should be
37 considered as a planned item.Prior to the fix items with planning codes
38 MRP(3) and MPS(4) only were considered as a planned item.
39 */
40
41 SELECT decode(msi.mrp_planning_code, 3,'Y',4,'Y',7,'Y',8,'Y',9,'Y',
42 decode(msi.inventory_planning_code,1,'Y',2,'Y', 'N')),
43 msi.outside_operation_flag ,
44 msi.outside_operation_uom_type,
45 msi.invoice_close_tolerance,
46 receive_close_tolerance,
47 msi.receipt_required_flag,
48 nvl(msi.stock_enabled_flag,'N'),
49 nvl(msi.internal_order_enabled_flag,'N'),
50 nvl(msi.purchasing_enabled_flag,'N'),
51 msi.inventory_asset_flag,
52 decode(msi.tracking_quantity_ind,'PS',msi.secondary_default_ind,NULL),
53 msi.grade_control_flag,
54 decode(msi.tracking_quantity_ind,'PS',mum.unit_of_measure,NULL)
55 INTO X_planned_item_flag ,
56 X_outside_operation_flag ,
57 X_outside_op_uom_type ,
58 X_invoice_close_tolerance ,
59 X_receive_close_tolerance ,
60 X_receipt_required_flag ,
61 X_stock_enabled_flag ,
62 X_internal_orderable ,
63 X_purchasing_enabled ,
64 X_inventory_asset_flag ,
65 /* INVCONV BEGIN PBAMB */
66 X_secondary_default_ind ,
67 X_grade_control_flag ,
68 X_secondary_unit_of_measure
69 /* INVCONV END PBAMB */
70 FROM mtl_system_items msi , mtl_units_of_measure mum
71 WHERE inventory_item_id = X_item_id
72 AND organization_id = X_inventory_organization_id
73 AND mum.uom_code(+) = msi.secondary_uom_code;
74
75 EXCEPTION
76
77 WHEN NO_DATA_FOUND THEN
78 null;
79
80 WHEN OTHERS THEN
81
82 po_message_s.sql_error('get_item_details', X_progress, sqlcode);
83 raise;
84
85 end get_item_details;
86
87 /*===========================================================================
88
89 PROCEDURE NAME: get_item_status
90
91 ===========================================================================*/
92
93
94 procedure get_item_status( X_item_id IN NUMBER,
95 X_ship_org_id IN NUMBER,
96 X_item_status IN OUT NOCOPY VARCHAR2) is
97 X_Progress varchar2(3) := NULL;
98
99 begin
100 X_Progress := '010';
101
102 /* item_status values:
103 ** 'O' = outside processing item
104 ** 'E' = item stockable in the org
105 ** 'D' = item defined but not stockable in org
106 ** null = item not defined in org */
107
108 X_item_status := '';
109
110 /* 714670 - SVAIDYAN: If item id is null, return D for item status */
111
112 IF (X_item_id is null) THEN
113 X_item_status := 'D';
114 return;
115 END IF;
116
117 SELECT decode(msi.outside_operation_flag,'Y','O',
118 decode(msi.stock_enabled_flag,'Y','E','D'))
119 INTO X_item_status
120 FROM mtl_system_items msi
121 WHERE msi.organization_id = X_ship_org_id
122 AND msi.inventory_item_id = X_item_id;
123
124
125 exception
126 when no_data_found then
127 null;
128
129 when others then
130 po_message_s.sql_error('get_item_status',X_progress,to_char(sqlcode));
131 raise;
132 end get_item_status;
133
134
135 /*===========================================================================
136
137 PROCEDURE NAME: get_latest_item_rev
138
139 ===========================================================================*/
140
141 /*
142 ** Get the latest implementation of an item rev
143 */
144 PROCEDURE get_latest_item_rev (X_item_id IN NUMBER,
145 X_organization_id IN NUMBER,
146 X_item_revision IN OUT NOCOPY VARCHAR2,
147 X_rev_exists OUT NOCOPY BOOLEAN) IS
148
149 X_progress VARCHAR2(4) := '000';
150
151 BEGIN
152
153 X_item_revision := NULL;
154 X_progress := '700';
155 /*
156 ** Go get the latest item revision based on effectivity.
157 ** Debug: Isn't there another function that we could use to do
158 ** this
159 */
160 SELECT max(mir.revision) -- Bug 448708 (a no of revisions could have same effective date
161 INTO X_item_revision -- thus returning more than 1 row and causing errors
162 FROM mtl_item_revisions mir -- Use max to return the maximum revision num)
163 WHERE mir.organization_id = X_organization_id
164 AND mir.inventory_item_id = X_item_id
165 AND mir.effectivity_date in
166 (SELECT MAX(mir2.effectivity_date)
167 FROM mtl_item_revisions mir2
168 WHERE mir2.organization_id = X_organization_id
169 AND mir2.inventory_item_id = X_item_id
170 /* Bug 1407438 - Filtering revisions which are not effective on current
171 date and also those which are not implemented */
172 AND mir2.effectivity_date <= SYSDATE
173 AND mir2.implementation_date is not NULL);
174
175 /*END Bug 1407438*/
176 --dbms_output.put_line ('get_latest_item_rev: rev : ' || X_item_revision);
177
178 X_rev_exists := TRUE;
179
180 RETURN;
181 /*
182 ** If no rows were found then you have to bail out since the
183 ** transaction cannot be processed
184 */
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 --dbms_output.put_line ('A default item rev does not exist');
188 X_rev_exists := FALSE;
189
190 WHEN OTHERS THEN
191 po_message_s.sql_error('get_latest_item_rev', x_progress, sqlcode);
192 RAISE;
193
194 END get_latest_item_rev;
195
196 /*===========================================================================
197
198 FUNCTION NAME: val_item_rev_controls
199
200 ===========================================================================*/
201 /*
202 ** get the item revision control flag and if needed, try
203 ** to get the most up-to-date revision.
204 ** You only need to check for item rev if you're doing an
205 ** express receipt/delivery for an inventory final destination
206 */
207 FUNCTION val_item_rev_controls (
208 X_transaction_type IN VARCHAR2,
209 X_auto_transact_code IN VARCHAR2,
210 X_po_line_location_id IN NUMBER,
211 X_shipment_line_id IN NUMBER,
212 X_to_organization_id IN NUMBER,
213 X_destination_type_code IN VARCHAR2,
214 X_item_id IN NUMBER,
215 X_item_revision IN VARCHAR2)
216 RETURN BOOLEAN IS
217
218 transaction_ok BOOLEAN := FALSE;
219 inventory_receipt BOOLEAN := FALSE;
220 X_item_rev_control NUMBER := 2;
221 default_item_revision VARCHAR2(4) := '0';
222 X_progress VARCHAR2(4) := '000';
223 item_rev_exists BOOLEAN := FALSE;
224
225 BEGIN
226
227 /*
228 ** Check if any distributions for this receipt are destined for
229 ** inventory. If so then you need to check that there is a
230 ** revision. This is only for express receipts. For express deliveries
231 ** you can base it on the destination type
232 */
233 IF (X_transaction_type = 'RECEIVE') THEN
234
235 inventory_receipt := rcv_transactions_sv.val_if_inventory_destination (
236 X_po_line_location_id,
237 X_shipment_line_id);
238 ELSE
239 /*
240 ** If this is an express delivery then check the destination type
241 ** of the transaction top determine the destination type
242 */
243 IF (X_destination_type_code = 'INVENTORY') THEN
244 inventory_receipt := TRUE;
245 END IF;
246
247 END IF;
248
249 IF (inventory_receipt) THEN
250 --dbms_output.put_line ('val_rev_control : inventory_receipt : TRUE');
251 null;
252 ELSE
253 --dbms_output.put_line ('val_rev_control : inventory_receipt : FALSE');
254 null;
255 END IF;
256
257 /* Only check item rev control if the final destination is inventory */
258 IF (NOT inventory_receipt) THEN
259 transaction_ok := TRUE;
260
261 ELSE
262
263 /* Can only check the rev control if you have and item id */
264
265 IF (X_item_id IS NOT NULL) THEN
266
267 X_progress := 400;
268
269 SELECT msi.revision_qty_control_code
270 INTO X_item_rev_control
271 FROM mtl_system_items_kfv msi
272 WHERE X_item_id = msi.inventory_item_id
273 AND X_to_organization_id = msi.organization_id;
274
275 /* Express transactions were erroring out when item revision control was 1 and
276 when default revision was used. This is because the code expects no revision
277 when item_revision_control is 1 . As default revision can be used for a
278 a non -revision controlled item ,modified the code to check if the
279 revision provided is default revision for a non-revision controlled item.
280 For Bug 2058582 */
281
282
283 Select starting_revision
284 into default_item_revision
285 from mtl_parameters
286 where organization_id = X_to_organization_id;
287
288
289 ELSE
290 /*
291 ** If there is no item id then there should not be an item rev
292 */
293 X_item_rev_control := 1;
294
295 END IF;
296
297 /*
298 dbms_output.put_line ('val_rev_control : X_item_rev_control : ' ||
299 to_char(X_item_rev_control));
300
301 dbms_output.put_line ('val_rev_control :item_rev : ' ||
302 X_item_revision);
303 */
304
305 /* If the trx is still not ok then do the main check on the rev */
306 IF (NOT transaction_ok) THEN
307
308 /* Check to see that the control matches the item rev setting */
309 IF (X_item_rev_control = 1 AND X_item_revision IS NULL) OR
310 (X_item_rev_control =1 AND X_item_revision = default_item_revision) OR
311 (X_item_rev_control = 2 AND X_item_revision IS NOT NULL) OR
312 (X_item_rev_control NOT IN (1,2)) THEN
313 transaction_ok := TRUE;
314 END IF;
315
316 END IF;
317
318 END IF;
319
320 RETURN transaction_ok;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 po_message_s.sql_error('val_item_rev_controls', x_progress, sqlcode);
325 RAISE;
326
327 END val_item_rev_controls;
328
329
330 /*===========================================================================
331
332 PROCEDURE NAME: get_item_cost
333
334 ===========================================================================*/
335
336 PROCEDURE get_item_cost (x_item_id IN NUMBER,
337 x_organization_id IN NUMBER,
338 x_inv_cost OUT NOCOPY NUMBER)
339 IS
340
341 x_progress VARCHAR2(3) := NULL;
342
343 BEGIN
344
345 /*
346 ** Obtain the standard cost.
347 */
348
349 x_progress := '010';
350
351 SELECT cic.item_cost
352 INTO x_inv_cost
353 FROM cst_item_costs_for_gl_view cic
354 WHERE cic.inventory_item_id = x_item_id
355 AND cic.organization_id = x_organization_id;
356
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 x_inv_cost := 0;
361
362 WHEN OTHERS THEN
363 --dbms_output.put_line('In exception');
364 po_message_s.sql_error('get_item_cost',
365 x_progress, sqlcode);
366 raise;
367 END get_item_cost;
368
369 END PO_ITEMS_SV2;
370
[Home] [Help]