[Home] [Help]
PACKAGE BODY: APPS.MRP_GET_ONHAND
Source
1 PACKAGE BODY MRP_GET_ONHAND AS
2 /* $Header: MRPGEOHB.pls 120.0 2005/05/24 19:18:55 appldev noship $ */
3
4 -- =========== Private Functions =============
5
6 PROCEDURE LOG_ERROR(
7 pBUFF IN VARCHAR2)
8 IS
9 BEGIN
10 IF fnd_global.conc_request_id > 0 THEN
11 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
12 ELSE
13 null;
14 --DBMS_OUTPUT.PUT_LINE( pBUFF);
15 END IF;
16 EXCEPTION
17 WHEN OTHERS THEN
18 RETURN;
19 END LOG_ERROR;
20
21 PROCEDURE LOG_DEBUG(
22 pBUFF IN VARCHAR2)
23 IS
24 BEGIN
25
26 IF (G_MRP_DEBUG = 'Y') THEN
27 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
28 ELSE
29 NULL;
30 --DBMS_OUTPUT.PUT_LINE( pBUFF);
31 END IF;
32 EXCEPTION
33 WHEN OTHERS THEN
34 RETURN;
35 END LOG_DEBUG;
36
37 PROCEDURE GET_OH_QTY(item_id IN NUMBER, org_id IN NUMBER,
38 include_nonnet IN NUMBER,
39 x_qoh OUT NOCOPY NUMBER,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_data OUT NOCOPY VARCHAR2) is
42
43
44 l_onhand_source NUMBER := 3;
45 l_sysdate DATE;
46 l_vmi_enabled VARCHAR2(1);
47 l_supplier_consigned_enabled VARCHAR2(1);
48 l_stmt VARCHAR2(10000);
49
50 BEGIN
51
52 /* Initialize date stuff */
53 select sysdate
54 into l_sysdate
55 from sys.dual;
56
57
58 IF (include_nonnet = 1) THEN
59
60 l_onhand_source := NULL;
61 ELSE
62 l_onhand_source := 2;
63 END IF;
64
65 l_supplier_consigned_enabled := NVL(fnd_profile.value('INV_SUPPLIER_CONSIGNED_ENABLED'),'N');
66 l_vmi_enabled := NVL(fnd_profile.value('INV_VMI_ENABLED'),'N');
67
68 if l_supplier_consigned_enabled = 'Y' then
69
70 l_stmt := 'declare '||
71 'x_return_status varchar2(20); '||
72 'x_msg_count number; '||
73 'x_msg_data varchar2(1000); '||
74 'x_planning_qty NUMBER; '||
75 'begin '||
76 'INV_CONSIGNED_VALIDATIONS_GRP.GET_PLANNING_QUANTITY( '||
77 ' x_return_status => x_return_status '||
78 ', x_msg_count => x_msg_count '||
79 ', x_msg_data => x_msg_data '||
80 ', P_INCLUDE_NONNET => :include_nonnet '||
81 ', P_LEVEL => 1 '||
82 ', P_ORG_ID => :org_id '||
83 ', P_SUBINV => NULL '||
84 ', P_ITEM_ID => :item_id '||
85 ', x_planning_qty => x_planning_qty '||'); '||
86 ' MRP_GET_ONHAND.g_x_return_status := x_return_status; '||
87 ' MRP_GET_ONHAND.g_x_msg_data := x_msg_data; '||
88 ' MRP_GET_ONHAND.g_x_qoh := x_planning_qty; '||
89 'end; ';
90
91 execute immediate l_stmt using include_nonnet, org_id, item_id;
92
93 else
94
95
96 if l_vmi_enabled = 'N' then /* if vmi is NOT enabled */
97
98 -- Calling the clear_quantity_cache procedure
99
100 l_stmt := 'begin '||
101 'inv_quantity_tree_grp.clear_quantity_cache; '||
102 'end;';
103
104 execute immediate l_stmt;
105
106
107 l_stmt := 'declare '||
108 'x_return_status varchar2(20); '||
109 'x_msg_count number; '||
110 'x_msg_data varchar2(1000); '||
111 'x_qoh number; '||
112 'x_rqoh number; '||
113 'x_qr number; '||
114 'x_qs number; '||
115 'x_atr number; '||
116 'x_att number; '||
117 'l_is_revision_control BOOLEAN := FALSE; '||
118 'l_is_lot_control BOOLEAN := TRUE; '||
119 'l_is_serial_control BOOLEAN := FALSE; '||
120 'begin '||
121 'INV_Quantity_Tree_PUB.Query_Quantities ( '||
122 'p_api_version_number => 1.0 '||
123 ', p_init_msg_lst => ''F'' '||
124 ', x_return_status => x_return_status '||
125 ', x_msg_count => x_msg_count '||
126 ', x_msg_data => x_msg_data '||
127 ', p_organization_id => :org_id '||
128 ', p_inventory_item_id => :item_id '||
129 ', p_tree_mode => 2 '||
130 ', p_is_revision_control=> l_is_revision_control '||
131 ', p_is_lot_control => l_is_lot_control '||
132 ', p_is_serial_control => l_is_serial_control '||
133 ', p_lot_expiration_date=> :l_sysdate '||
134 ', p_revision => NULL '||
135 ', p_lot_number => NULL '||
136 ', p_subinventory_code => NULL '||
137 ', p_locator_id => NULL '||
138 ', p_onhand_source => :l_onhand_source '||
139 ', x_qoh =>x_qoh '||
140 ', x_rqoh =>x_rqoh '||
141 ', x_qr =>x_qr '||
142 ', x_qs =>x_qs '||
143 ', x_att =>x_att '||
144 ', x_atr =>x_atr '||'); '||
145 ' MRP_GET_ONHAND.g_x_return_status := x_return_status; '||
146 ' MRP_GET_ONHAND.g_x_msg_data := x_msg_data; '||
147 ' MRP_GET_ONHAND.g_x_qoh := x_qoh; '||
148 'end; ';
149
150 execute immediate l_stmt using org_id, item_id, l_sysdate, l_onhand_source;
151
152
153 else
154
155
156 l_stmt := 'begin '||
157 'inv_vmi_validations.clear_vmi_cache; '||
158 'end;';
159
160 execute immediate l_stmt;
161
162 l_stmt := 'declare '||
163 'x_return_status varchar2(20); '||
164 'x_msg_data varchar2(1000); '||
165 'x_qoh number; '||
166 'x_att number; '||
167 'x_voh number; '||
168 'x_vatt number; '||
169 'begin '||
170 'inv_vmi_validations.get_available_vmi_quantity '||
171 '( x_return_status => x_return_status '||
172 ', x_return_msg => x_msg_data '||
173 ', p_tree_mode => 2 '||
174 ', p_organization_id => :org_id '||
175 ' , p_owning_org_id => NULL '||
176 ', p_planning_org_id => NULL '||
177 ', p_inventory_item_id => :item_id '||
178 ' , p_is_revision_control => ''FALSE'' '||
179 ', p_is_lot_control => ''TRUE'' '||
180 ', p_is_serial_control => ''FALSE'' '||
181 ', p_revision => NULL '||
182 ', p_lot_number => NULL '||
183 ', p_lot_expiration_date => :l_sysdate '||
184 ', p_subinventory_code => NULL '||
185 ', p_locator_id => NULL '||
186 ', p_onhand_source => :l_onhand_source '||
187 ' , p_cost_group_id => NULL '||
188 ', x_qoh => x_qoh '||
189 ', x_att => x_att '||
190 ', x_voh => x_voh '||
191 ', x_vatt => x_vatt '||
192 '); '||
193 'MRP_GET_ONHAND.g_x_return_status := x_return_status; '||
194 'MRP_GET_ONHAND.g_x_msg_data := x_msg_data; '||
195 'MRP_GET_ONHAND.g_x_qoh := nvl(x_qoh,0) - nvl(x_voh,0); '||
196 'end;';
197
198 execute immediate l_stmt using org_id, item_id, l_sysdate, l_onhand_source;
199
200 end if; /* end vmi check */
201
202 end if; /* end consigned validations check */
203
204 x_return_status := g_x_return_status;
205 x_msg_data := g_x_msg_data;
206 x_qoh := g_x_qoh;
207
208 end GET_OH_QTY;
209
210
211 /*
212 ** ---------------------------------------------------------------------------
213 ** Procedure : do_restock
214 **
215 ** Description : This procedure is called from MRP's Reorder Point report.
216 **
217 ** 1) This procedure will be called only when inventory
218 ** patchset J or above is installed.
219 ** It calls the following Inventory API :
220 ** INV_MMX_WRAPPER_PVT.do_restock
221 ** The inventory API is called as a Dynamic SQL statement.
222 ** This is to avoid compilation errors if this patch is
223 ** by a user on Inventory patchset I or below as this
224 ** package is not available in that code level.
225 **
226 ** Input Parameters:
227 **
228 ** p_item_id
229 ** Inventory Item Id of the Item to be replenished.
230 ** p_mbf
231 ** Make or Buy Flag of the Item to be replenished.
232 ** p_handle_repetitive_item
233 ** Parameter for Repetitive item handling.
234 ** 1- Create Requisition
235 ** 2- Create Discrete Job
236 ** 3- Do not Restock ,ie Report Only.
237 ** p_repetitive_planned_item
238 ** Flag indicating whether item has to be planned as repetitive
239 ** schedule.
240 ** p_qty
241 ** Quantity to be replenished.
242 ** p_fixed_lead_time
243 ** Fixed portion of the assembly Item's lead time.
244 ** p_variable_lead_time
245 ** Variable portion of the assembly Item's lead time.
246 ** p_buying_lead_time
247 ** Preprocessing Lead time + Full Lead Time of the Buy Item.
248 ** p_uom
249 ** Primary UOM of the Item.
250 ** p_accru_acct
251 ** Accrual Account of the Organization/Operating Unit.
252 ** p_ipv_acct
253 ** Invoice Process Variable Account.
254 ** p_budget_acct
255 ** Budget Account.
256 ** p_charge_acct
257 ** Charge Account.
258 ** p_purch_flag
259 ** Flag indicating if item may appear on outside operation purchase
260 ** order.
261 ** p_order_flag
262 ** Flag indicating if item is internally orderable.
263 ** p_transact_flag
264 ** Flag indicating if item is transactable.
265 ** p_unit_price
266 ** Unit list price - purchasing.
267 ** p_wip_id
268 ** WIP Batch Id of WIP_JOB_SCHEDULE_INTERFACE.
269 ** p_user_id
270 ** Identifier of the User performing the Min Max planning.
271 ** p_sysd
272 ** Current System Date.
273 ** p_organization_id
274 ** Identifier of organization for which Min Max planning is to be done.
275 ** p_approval
276 ** Approval status.
277 ** 1-Incomplete.
278 ** 7-pre-approved.
279 ** p_build_in_wip
280 ** Flag indicating if item may be built in WIP.
281 ** p_pick_components
282 ** Flag indicating whether all shippable components should be picked.
283 ** p_src_type
284 ** Source type for the Item.
285 ** 1-Inventory.
286 ** 2-Supplier.
287 ** 3-Subinventory.
288 ** p_encum_flag
289 ** Encumbrance Flag.
290 ** p_customer_id
291 ** Customer Id.
292 ** p_cal_code
293 ** Calendar Code of the Organization.
294 ** p_except_id
295 ** Exception Set Id of the Organization.
296 ** p_employee_id
297 ** Identifier of the Employee associated with the User.
298 ** p_description
299 ** Description of the Item.
300 ** p_src_org
301 ** Organization to source items from.
302 ** p_src_subinv
303 ** Subinventory to source items from.
304 ** p_subinv
305 ** Subinventory to be replenished.
306 ** p_location_id
307 ** Default Delivery To Location Id of the Planning Org.
308 ** p_po_org_id
309 ** Operating Unit Id.
310 ** p_pur_revision
311 ** Parameter for Purchasing By Revision .
312 ** Used for Revision controlled items.
313 **
314 ** Output Parameters:
315 **
316 ** x_return_status
317 ** Return status indicating success, error or unexpected error.
318 ** x_msg_count
319 ** Number of messages in the message list.
320 ** x_msg_data
321 ** If the number of messages in message list is 1, contains
322 ** message text.
323 **
324 ** ---------------------------------------------------------------------------
325 */
326
327 PROCEDURE do_restock
328 ( x_return_status OUT NOCOPY VARCHAR2
329 , x_msg_count OUT NOCOPY NUMBER
330 , x_msg_data OUT NOCOPY VARCHAR2
331 , p_item_id IN NUMBER
332 , p_mbf IN NUMBER
333 , p_handle_repetitive_item IN NUMBER
334 , p_repetitive_planned_item IN VARCHAR2
335 , p_qty IN NUMBER
336 , p_fixed_lead_time IN NUMBER
337 , p_variable_lead_time IN NUMBER
338 , p_buying_lead_time IN NUMBER
339 , p_uom IN VARCHAR2
340 , p_accru_acct IN NUMBER
341 , p_ipv_acct IN NUMBER
342 , p_budget_acct IN NUMBER
343 , p_charge_acct IN NUMBER
344 , p_purch_flag IN VARCHAR2
345 , p_order_flag IN VARCHAR2
346 , p_transact_flag IN VARCHAR2
347 , p_unit_price IN NUMBER
348 , p_wip_id IN NUMBER
349 , p_user_id IN NUMBER
350 , p_sysd IN DATE
351 , p_organization_id IN NUMBER
352 , p_approval IN NUMBER
353 , p_build_in_wip IN VARCHAR2
354 , p_pick_components IN VARCHAR2
355 , p_src_type IN NUMBER
356 , p_encum_flag IN VARCHAR2
357 , p_customer_id IN NUMBER
358 , p_cal_code IN VARCHAR2
359 , p_except_id IN NUMBER
360 , p_employee_id IN NUMBER
361 , p_description IN VARCHAR2
362 , p_src_org IN NUMBER
363 , p_src_subinv IN VARCHAR2
364 , p_subinv IN VARCHAR2
365 , p_location_id IN NUMBER
366 , p_po_org_id IN NUMBER
367 , p_pur_revision IN NUMBER
368 ) IS
369
370 l_sql_stmt VARCHAR2(2000);
371 BEGIN
372
373 IF (G_MRP_DEBUG = 'Y') THEN
374
375 -- Print the Input parameters
376 log_debug ('Input Parameters to mrp_get_onhand.do_restock ..');
377 log_debug ('--------------------------------------------------------');
378 log_debug ('Item ID: ' || to_char(p_item_id));
379 log_debug ('Make/Buy Flag: ' || to_char(p_mbf));
380 log_debug ('Handle Repetitive Item: ' ||
381 to_char(p_handle_repetitive_item));
382 log_debug ('Repetitive Planned Item : ' ||
383 p_repetitive_planned_item);
384 log_debug ('Reorder Quantity: ' || to_char(p_qty));
385 log_debug ('Fixed Lead Time: ' || to_char(p_fixed_lead_time));
386 log_debug ('Variable Lead Time: ' || to_char(p_variable_lead_time));
387 log_debug ('Buying Lead Time: ' || to_char(p_buying_lead_time));
388 log_debug ('Unit of Measure: ' || p_uom);
389 log_debug ('Accrual Account: ' || to_char(p_accru_acct));
390 log_debug ('Invoice Price Variances Account: ' || to_char(p_ipv_acct));
391 log_debug ('Budget Account: ' || to_char(p_budget_acct));
392 log_debug ('Charge Account: ' || to_char(p_charge_acct));
393 log_debug ('Purchase Flag: ' || p_purch_flag);
394 log_debug ('Order Flag: ' || p_order_flag);
395 log_debug ('Transact Flag: ' || p_transact_flag);
396 log_debug ('Unit Price: ' || to_char(p_unit_price));
397 log_debug ('WIP ID: ' || to_char(p_wip_id));
398 log_debug ('User ID: ' || to_char(p_user_id));
399 log_debug ('Current Date: ' || to_char(p_sysd,'DD-MON-RR'));
400 log_debug ('Organization ID: ' || to_char(p_organization_id));
401 log_debug ('Approval: ' || to_char(p_approval));
402 log_debug ('Build in WIP: ' || p_build_in_wip);
403 log_debug ('Pick Components Flag: ' || p_pick_components);
404 log_debug ('Source Type: ' || to_char(p_src_type));
405 log_debug ('Encumberance Flag: ' || p_encum_flag);
406 log_debug ('Customer ID: ' || to_char(p_customer_id));
407 log_debug ('Calendar Code: ' || p_cal_code);
408 log_debug ('Calendar Exception Set ID: ' || to_char(p_except_id));
409 log_debug ('Employee ID: ' || to_char(p_employee_id));
410 log_debug ('Description: ' || p_description);
411 log_debug ('Source Organization ID: ' || to_char(p_src_org));
412 log_debug ('Source Sub Inventory: ' || p_src_subinv);
413 log_debug ('Supply Sub Inventory: ' || p_subinv);
414 log_debug ('Location ID: ' || to_char(p_location_id));
415 log_debug ('PO Organization ID: ' || to_char(p_po_org_id));
416 log_debug ('Purchasing by revision: ' || to_char(p_pur_revision));
417 log_debug ('--------------------------------------------------------');
418 END IF;
419
420
421 l_sql_stmt :=
422 'BEGIN ' ||
423 'inv_mmx_wrapper_pvt.do_restock(' ||
424 'x_return_status => :x_return_status ' ||
425 ',x_msg_count => :x_msg_count ' ||
426 ',x_msg_data => :x_msg_data ' ||
427 ',p_item_id => :p_item_id ' ||
428 ',p_mbf => :p_mbf ' ||
429 ',p_handle_repetitive_item => :p_repetitive_item ' ||
430 ',p_repetitive_planned_item => :p_repetitive_planned_item ' ||
431 ',p_qty => :p_reorder_qty ' ||
432 ',p_fixed_lead_time => :p_fixed_lead_time ' ||
433 ',p_variable_lead_time => :p_variable_lead_time ' ||
434 ',p_buying_lead_time => :p_pur_lead_time ' ||
435 ',p_uom => :p_primary_uom ' ||
436 ',p_accru_acct => :p_accrual_acct ' ||
437 ',p_ipv_acct => :p_ipv_acct ' ||
438 ',p_budget_acct => :p_budget_acct ' ||
439 ',p_charge_acct => :p_charge_acct ' ||
440 ',p_purch_flag => :p_purch_flag ' ||
441 ',p_order_flag => :p_order_flag ' ||
442 ',p_transact_flag => :p_transact_flag ' ||
443 ',p_unit_price => :p_unit_price ' ||
444 ',p_wip_id => :p_wip_batch_id ' ||
445 ',p_user_id => :p_user_id ' ||
446 ',p_sysd => :p_current_date '||
447 ',p_organization_id => :p_org_id ' ||
448 ',p_approval => :p_approval ' ||
449 ',p_build_in_wip => :p_build_in_wip ' ||
450 ',p_pick_components => :p_pick_components ' ||
451 ',p_src_type => :p_src_type ' ||
452 ',p_encum_flag => :p_encum_flag ' ||
453 ',p_customer_id => :p_customer_id ' ||
454 ',p_cal_code => :p_cal_code ' ||
455 ',p_except_id => :p_exc_set_id ' ||
456 ',p_employee_id => :p_employee_id ' ||
457 ',p_description => :p_description ' ||
458 ',p_src_org => :p_src_org ' ||
459 ',p_src_subinv => :p_src_subinv ' ||
460 ',p_subinv => :p_subinv ' ||
461 ',p_location_id => :p_default_delivery_to ' ||
462 ',p_po_org_id => :p_po_org_id ' ||
463 ',p_pur_revision => :p_pur_revision ); ' ||
464 'END;';
465
466
467 BEGIN
468
469 EXECUTE IMMEDIATE l_sql_stmt USING
470 OUT x_return_status
471 ,OUT x_msg_count
472 ,OUT x_msg_data
473 , p_item_id
474 , p_mbf
475 , p_handle_repetitive_item
476 , p_repetitive_planned_item
477 , p_qty
478 , p_fixed_lead_time
479 , p_variable_lead_time
480 , p_buying_lead_time
481 , p_uom
482 , p_accru_acct
483 , p_ipv_acct
484 , p_budget_acct
485 , p_charge_acct
486 , p_purch_flag
487 , p_order_flag
488 , p_transact_flag
489 , p_unit_price
490 , p_wip_id
491 , p_user_id
492 , p_sysd
493 , p_organization_id
494 , p_approval
495 , p_build_in_wip
496 , p_pick_components
497 , p_src_type
498 , p_encum_flag
499 , p_customer_id
500 , p_cal_code
501 , p_except_id
502 , p_employee_id
503 , p_description
504 , p_src_org
505 , p_src_subinv
506 , p_subinv
507 , p_location_id
508 , p_po_org_id
509 , p_pur_revision;
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 log_error ('error in mrp_get_onhand.do_restock.');
514 log_error (SQLERRM);
515 x_return_status := 'E';
516 ROLLBACK;
517 END;
518
519 END do_restock;
520
521 end MRP_GET_ONHAND;