[Home] [Help]
PACKAGE BODY: APPS.CSP_MINMAX_PVT
Source
1 PACKAGE BODY CSP_Minmax_PVT AS
2 /* $Header: cspvmmxb.pls 115.0 2003/04/25 18:51:59 phegde noship $ */
3
4 -- user ID for logging trace
5 G_USER_NAME fnd_user.user_name%TYPE := NULL;
6 G_TRACE_ON BOOLEAN := FALSE;
7
8 TYPE minmax_items_rectype IS RECORD
9 ( item VARCHAR2(1000)
10 , description mtl_system_items.description%TYPE
11 , fix_mult mtl_system_items.fixed_lot_multiplier%TYPE
12 , min_qty mtl_system_items.min_minmax_quantity%TYPE
13 , max_qty mtl_system_items.max_minmax_quantity%TYPE
14 , min_ord_qty mtl_system_items.minimum_order_quantity%TYPE
15 , max_ord_qty mtl_system_items.maximum_order_quantity%TYPE
16 , fixed_lead_time mtl_system_items.fixed_lead_time%TYPE
17 , variable_lead_time mtl_system_items.variable_lead_time%TYPE
18 , postprocessing_lead_time mtl_system_items.postprocessing_lead_time%TYPE
19 , buying_lead_time mtl_system_items.full_lead_time%TYPE
20 , planner mtl_system_items.planner_code%TYPE
21 , buyer per_all_people_f.full_name%TYPE
22 , category VARCHAR2(800)
23 , category_id mtl_categories.category_id%TYPE
24 , item_id mtl_system_items.inventory_item_id%TYPE
25 , lot_ctl mtl_system_items.lot_control_code%TYPE
26 , repetitive_planned_item mtl_system_items.repetitive_planning_flag%TYPE
27 , primary_uom mtl_system_items.primary_uom_code%TYPE
28 , accru_acct mtl_parameters.ap_accrual_account%TYPE
29 , ipv_acct mtl_parameters.invoice_price_var_account%TYPE
30 , budget_acct mtl_system_items.encumbrance_account%TYPE
31 , charge_acct mtl_system_items.expense_account%TYPE
32 , src_type mtl_system_items.source_type%TYPE
33 , src_org mtl_system_items.source_organization_id%TYPE
34 , src_subinv mtl_system_items.source_subinventory%TYPE
35 , purch_flag mtl_system_items.purchasing_enabled_flag%TYPE
36 , order_flag mtl_system_items.internal_order_enabled_flag%TYPE
37 , transact_flag mtl_system_items.mtl_transactions_enabled_flag%TYPE
38 , unit_price mtl_system_items.list_price_per_unit%TYPE
39 , mbf mtl_system_items.planning_make_buy_code%TYPE
40 , build_in_wip mtl_system_items.build_in_wip_flag%TYPE
41 , pick_components mtl_system_items.pick_components_flag%TYPE
42 );
43
44 --
45 -- Start of forward declarations
46 --
47
48 FUNCTION get_item_segments( p_org_id NUMBER
49 , p_item_id NUMBER) RETURN VARCHAR2;
50
51 FUNCTION get_catg_disp( p_category_id NUMBER
52 , p_struct_id NUMBER) RETURN VARCHAR2;
53
54 --Bug# 2766358
55 /*
56 FUNCTION get_onhand_qty( p_include_nonnet NUMBER
57 , p_level NUMBER
58 , p_org_id NUMBER
59 , p_subinv VARCHAR2
60 , p_item_id NUMBER
61 , p_sysdate DATE) RETURN NUMBER;
62 */
63
64 FUNCTION get_supply_qty( p_org_id NUMBER
65 , p_subinv VARCHAR2
66 , p_item_id NUMBER
67 , p_postp_lead_time NUMBER
68 , p_cal_code VARCHAR2
69 , p_except_id NUMBER
70 , p_level NUMBER
71 , p_s_cutoff DATE
72 , p_include_po NUMBER
73 , p_vmi_enabled VARCHAR2
74 , p_include_nonnet NUMBER
75 , p_include_wip NUMBER
76 , p_include_if NUMBER) RETURN NUMBER;
77
78 FUNCTION get_demand_qty( p_org_id NUMBER
79 , p_subinv VARCHAR2
80 , p_level NUMBER
81 , p_item_id NUMBER
82 , p_d_cutoff DATE
83 , p_include_nonnet NUMBER
84 , p_net_rsv NUMBER
85 , p_net_unrsv NUMBER
86 , p_net_wip NUMBER) RETURN NUMBER;
87
88 FUNCTION get_reord_qty( p_min_qty NUMBER
89 , p_max_qty NUMBER
90 , p_min_ord_qty NUMBER
91 , p_max_ord_qty NUMBER
92 , p_tot_avail_qty NUMBER
93 , p_fix_mult NUMBER) RETURN NUMBER;
94
95 FUNCTION get_reord_stat( p_restock NUMBER
96 , p_handle_rep_item NUMBER
97 , p_level NUMBER
98 , p_reord_qty NUMBER
99 , p_wip_batch_id NUMBER
100 , p_org_id NUMBER
101 , p_subinv VARCHAR2
102 , p_user_id NUMBER
103 , p_employee_id NUMBER
104 , p_sysdate DATE
105 , p_approval NUMBER
106 , p_encum_flag VARCHAR2
107 , p_cust_id NUMBER
108 , p_cal_code VARCHAR2
109 , p_exception_set_id NUMBER
110 , p_dd_loc_id NUMBER
111 , p_po_org_id NUMBER
112 , p_pur_revision NUMBER
113 , p_item_rec minmax_items_rectype) RETURN VARCHAR2;
114
115 PROCEDURE re_po( p_item_id IN NUMBER
116 , p_qty IN NUMBER
117 , p_nb_time IN DATE
118 , p_uom IN VARCHAR2
119 , p_accru_acct IN NUMBER
120 , p_ipv_acct IN NUMBER
121 , p_budget_acct IN NUMBER
122 , p_charge_acct IN NUMBER
123 , p_purch_flag IN VARCHAR2
124 , p_order_flag IN VARCHAR2
125 , p_transact_flag IN VARCHAR2
126 , p_unit_price IN NUMBER
127 , p_user_id IN NUMBER
128 , p_sysd IN DATE
129 , p_organization_id IN NUMBER
130 , p_approval IN NUMBER
131 , p_src_type IN NUMBER
132 , p_encum_flag IN VARCHAR2
133 , p_customer_id IN NUMBER
134 , p_employee_id IN NUMBER
135 , p_description IN VARCHAR2
136 , p_src_org IN NUMBER
137 , p_src_subinv IN VARCHAR2
138 , p_subinv IN VARCHAR2
139 , p_location_id IN NUMBER
140 , p_po_org_id IN NUMBER
141 , p_pur_revision IN NUMBER
142 , x_ret_stat OUT NOCOPY VARCHAR2
143 , x_ret_mesg OUT NOCOPY VARCHAR2);
144
145 PROCEDURE re_wip( p_item_id IN NUMBER
146 , p_qty IN NUMBER
147 , p_nb_time IN DATE
148 , p_uom IN VARCHAR2
149 , p_wip_id IN NUMBER
150 , p_user_id IN NUMBER
151 , p_sysd IN DATE
152 , p_organization_id IN NUMBER
153 , p_approval IN NUMBER
154 , p_build_in_wip IN VARCHAR2
155 , p_pick_components IN VARCHAR2
156 , x_ret_stat OUT NOCOPY VARCHAR2
157 , x_ret_mesg OUT NOCOPY VARCHAR2);
158
159 --
160 -- End of forward declarations
161 --
162
163
164 PROCEDURE init_debug (p_user_id IN NUMBER) IS
165
166 l_inv_debug VARCHAR2(1);
167
168 BEGIN
169
170 --
171 -- Find the user name, INV debug profile setting
172 --
173 SELECT user_name
174 INTO G_USER_NAME
175 FROM fnd_user
176 WHERE user_id = p_user_id;
177
178 SELECT NVL(fnd_profile.value('INV_DEBUG_TRACE'),'2')
179 INTO l_inv_debug
180 FROM dual;
181
182 IF l_inv_debug = 1 THEN
183 G_TRACE_ON := TRUE;
184 ELSE
185 G_TRACE_ON := FALSE;
186 END IF;
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 -- dbms_output.put_line(sqlcode || ', ' || l_err_msg);
191 G_TRACE_ON := FALSE;
192 END init_debug;
193
194
195
196 PROCEDURE print_debug
197 (
198 p_message IN VARCHAR2
199 , p_module IN VARCHAR2
200 , p_level IN NUMBER
201 ) IS
202 BEGIN
203 -- dbms_output.put_line(p_message);
204
205 IF G_TRACE_ON THEN
206 inv_log_util.trace( G_USER_NAME || ': ' || p_message
207 , G_PKG_NAME || '.' || p_module
208 , p_level
209 );
210 END IF;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 -- dbms_output.put_line(sqlcode || ', ' || l_err_msg);
215 NULL;
216 END print_debug;
217
218
219
220 PROCEDURE run_min_max_plan ( p_item_select IN VARCHAR2
221 , p_handle_rep_item IN NUMBER
222 , p_pur_revision IN NUMBER
223 , p_cat_select IN VARCHAR2
224 , p_cat_set_id IN NUMBER
225 , p_mcat_struct IN NUMBER
226 , p_level IN NUMBER
227 , p_restock IN NUMBER
228 , p_include_nonnet IN NUMBER
229 , p_include_po IN NUMBER
230 , p_include_wip IN NUMBER
231 , p_include_if IN NUMBER
232 , p_net_rsv IN NUMBER
233 , p_net_unrsv IN NUMBER
234 , p_net_wip IN NUMBER
235 , p_org_id IN NUMBER
236 , p_user_id IN NUMBER
237 , p_employee_id IN NUMBER
238 , p_subinv IN VARCHAR2
239 , p_dd_loc_id IN NUMBER
240 , p_wip_batch_id IN NUMBER
241 , p_approval IN NUMBER
242 , p_buyer_hi IN VARCHAR2
243 , p_buyer_lo IN VARCHAR2
244 , p_range_buyer IN VARCHAR2
245 , p_cust_id IN NUMBER
246 , p_po_org_id IN NUMBER
247 , p_range_sql IN VARCHAR2
248 , p_sort IN VARCHAR2
249 , p_selection IN NUMBER
250 , p_sysdate IN DATE
251 , p_s_cutoff IN DATE
252 , p_d_cutoff IN DATE
253 , p_order_by IN VARCHAR2
254 , p_encum_flag IN VARCHAR2
255 , p_cal_code IN VARCHAR2
256 , p_exception_set_id IN NUMBER
257 , x_return_status OUT NOCOPY VARCHAR2
258 , x_msg_data OUT NOCOPY VARCHAR2
259 ) IS
260
261 TYPE c_items_curtype IS REF CURSOR;
262 c_items_to_plan c_items_curtype;
263
264 item_rec minmax_items_rectype;
265
266 sql_stmt1 VARCHAR2(8000) :=
267 ' SELECT ' || p_item_select || ' item,
268 c.description description,
269 c.fixed_lot_multiplier fix_mult,
270 c.min_minmax_quantity min_qty,
271 c.max_minmax_quantity max_qty,
272 c.minimum_order_quantity min_ord_qty,
273 c.maximum_order_quantity max_ord_qty,
274 c.fixed_lead_time,
275 c.variable_lead_time,
276 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
277 NVL(c.preprocessing_lead_time, 0) +
278 NVL(c.full_lead_time, 0) buying_lead_time,
279 c.planner_code planner,
280 NULL buyer,
281 ' || p_cat_select || ' category,
282 b.category_id category_id,
283 c.inventory_item_id item_id,
284 c.lot_control_code lot_ctl,
285 c.repetitive_planning_flag repetitive_planned_item,
286 c.primary_uom_code primary_uom,
287 p.ap_accrual_account accru_acct,
288 p.invoice_price_var_account ipv_acct,
289 NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
290 DECODE(c.inventory_asset_flag,
291 ''Y'', p.material_account,
292 NVL(c.expense_account, p.expense_account)) charge_acct,
293 NVL(c.source_type, p.source_type) src_type,
294 DECODE(c.source_type,
295 NULL, DECODE(p.source_type, NULL, NULL, p.source_organization_id),
296 c.source_organization_id) src_org,
297 DECODE(c.source_type,
298 NULL, DECODE(p.source_type, NULL, NULL, p.source_subinventory),
299 c.source_subinventory) src_subinv,
300 c.purchasing_enabled_flag purch_flag,
301 c.internal_order_enabled_flag order_flag,
302 c.mtl_transactions_enabled_flag transact_flag,
303 c.list_price_per_unit unit_price,
304 c.planning_make_buy_code mbf,
305 build_in_wip_flag build_in_wip,
306 pick_components_flag pick_components
307 FROM mtl_categories b,
308 mtl_item_categories a,
309 mtl_system_items c,
310 mtl_parameters p
311 WHERE b.category_id = a.category_id
312 AND b.structure_id = :mcat_struct_id
313 AND c.inventory_item_flag = ''Y''
314 AND p.organization_id = :org_id
315 AND a.organization_id = c.organization_id
316 AND c.organization_id = :org_id
317 AND c.inventory_planning_code = 2
318 AND a.category_set_id = :cat_set_id
319 AND a.inventory_item_id = c.inventory_item_id
320 AND ( ' || p_range_sql || ' ) ';
321
322 sql_stmt2 VARCHAR2(8000) :=
323 ' SELECT ' || p_item_select || ' item,
324 c.description description,
325 s.fixed_lot_multiple fix_mult,
326 s.min_minmax_quantity min_qty,
327 s.max_minmax_quantity max_qty,
328 s.minimum_order_quantity min_ord_qty,
329 s.maximum_order_quantity max_ord_qty,
330 c.fixed_lead_time,
331 c.variable_lead_time,
332 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
333 NVL(s.preprocessing_lead_time,
334 NVL(m.preprocessing_lead_time,
335 NVL(c.preprocessing_lead_time, 0))) +
336 NVL(s.processing_lead_time,
337 NVL(m.processing_lead_time,
338 NVL(c.full_lead_time, 0))) buying_lead_time,
339 c.planner_code planner,
340 NULL,
341 ' || p_cat_select || ',
342 b.category_id category_id,
343 c.inventory_item_id item_id,
344 c.lot_control_code,
345 c.repetitive_planning_flag repetitive_planned_item,
346 c.primary_uom_code,
347 p.ap_accrual_account,
348 p.invoice_price_var_account,
349 NVL(s.encumbrance_account,
350 NVL(m.encumbrance_account,
351 NVL(c.encumbrance_account, p.encumbrance_account))),
352 DECODE(c.inventory_asset_flag,
353 ''Y'', m.material_account,
354 NVL(m.expense_account,
355 NVL(c.expense_account, p.expense_account))),
356 NVL(s.source_type,
357 NVL(m.source_type,
358 NVL(c.source_type, p.source_type))),
359 DECODE(s.source_type,
360 NULL, DECODE(m.source_type,
361 NULL, DECODE(c.source_type,
362 NULL, DECODE(p.source_type,
363 NULL, NULL,
364 p.source_organization_id),
365 c.source_organization_id),
366 m.source_organization_id),
367 s.source_organization_id),
368 DECODE(s.source_type,
369 NULL, DECODE(m.source_type,
370 NULL, DECODE(c.source_type,
371 NULL, DECODE(p.source_type,
372 NULL, NULL,
373 p.source_subinventory),
374 c.source_subinventory),
375 m.source_subinventory),
376 s.source_subinventory),
377 c.purchasing_enabled_flag,
378 c.internal_order_enabled_flag,
379 c.mtl_transactions_enabled_flag,
380 c.list_price_per_unit,
381 c.planning_make_buy_code,
382 build_in_wip_flag,
383 pick_components_flag
384 FROM mtl_categories b,
385 mtl_item_categories a,
386 mtl_system_items c,
387 mtl_parameters p,
388 mtl_secondary_inventories m,
389 mtl_item_sub_inventories s
390 WHERE b.category_id = a.category_id
391 AND b.structure_id = :mcat_struct_id
392 AND c.inventory_item_flag = ''Y''
393 AND p.organization_id = :org_id
394 AND a.organization_id = c.organization_id
395 AND c.organization_id = :org_id
396 AND c.inventory_item_id = s.inventory_item_id
397 AND a.category_set_id = :cat_set_id
398 AND a.inventory_item_id = s.inventory_item_id
399 AND s.organization_id = :org_id
400 AND s.inventory_planning_code = 2
401 AND s.secondary_inventory = :sub
402 AND m.organization_id = :org_id
403 AND m.secondary_inventory_name = :sub
404 AND ( ' || p_range_sql || ' ) ';
405
406 sql_stmt3 VARCHAR2(8000) :=
407 ' SELECT ' || p_item_select || ',
408 c.description,
409 c.fixed_lot_multiplier,
410 c.min_minmax_quantity,
411 c.max_minmax_quantity,
412 c.minimum_order_quantity,
413 c.maximum_order_quantity,
414 c.fixed_lead_time,
415 c.variable_lead_time,
416 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
417 NVL(c.preprocessing_lead_time, 0) +
418 NVL(c.full_lead_time, 0) buying_lead_time,
419 c.planner_code planner,
420 SUBSTR(v.full_name, 1, 10),
421 ' || p_cat_select || ',
422 b.category_id category_id,
423 c.inventory_item_id,
424 c.lot_control_code,
425 c.repetitive_planning_flag repetitive_planned_item,
426 c.primary_uom_code,
427 p.ap_accrual_account,
428 p.invoice_price_var_account,
429 NVL(c.encumbrance_account, p.encumbrance_account),
430 decode(c.inventory_asset_flag,
431 ''Y'', p.material_account,
432 NVL(c.expense_account, p.expense_account)),
433 NVL(c.source_type, p.source_type),
434 decode(c.source_type, NULL, decode(p.source_type, NULL, NULL,
435 p.source_organization_id), c.source_organization_id),
436 decode(c.source_type, NULL, decode(p.source_type, NULL, NULL,
437 p.source_subinventory), c.source_subinventory),
438 c.purchasing_enabled_flag,
439 c.internal_order_enabled_flag,
440 c.mtl_transactions_enabled_flag,
441 c.list_price_per_unit,
442 c.planning_make_buy_code,
443 build_in_wip_flag,
444 pick_components_flag
445 FROM mtl_categories b,
446 mtl_item_categories a,
447 mtl_system_items c,
448 mtl_parameters p,
449 per_all_people_f v
450 WHERE b.category_id = a.category_id
451 AND b.structure_id = :mcat_struct_id
452 AND c.inventory_item_flag = ''Y''
453 AND p.organization_id = :org_id
454 AND a.organization_id = c.organization_id
455 AND c.organization_id = :org_id
456 AND c.inventory_planning_code = 2
457 AND a.category_set_id = :cat_set_id
458 AND a.inventory_item_id = c.inventory_item_id
459 AND v.person_id (+) = c.buyer_id
460 AND (
461 (:l_sysdate between v.effective_start_date and v.effective_end_date)
462 OR
463 (v.effective_start_date IS NULL AND v.effective_end_date IS NULL)
464 )
465 AND ( ' || p_range_sql || ' )
466 AND ( ' || p_range_buyer || ' ) ';
467
468 sql_stmt4 VARCHAR2(8000) :=
469 ' SELECT ' || p_item_select || ',
470 c.description,
471 s.fixed_lot_multiple,
472 s.min_minmax_quantity,
473 s.max_minmax_quantity,
474 s.minimum_order_quantity,
475 s.maximum_order_quantity,
476 c.fixed_lead_time,
477 c.variable_lead_time,
478 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
479 NVL(s.preprocessing_lead_time,
480 NVL(m.preprocessing_lead_time,
481 NVL(c.preprocessing_lead_time, 0))) +
482 NVL(s.processing_lead_time,
483 NVL(m.processing_lead_time,
484 NVL(c.full_lead_time, 0))) buying_lead_time,
485 c.planner_code planner,
486 SUBSTR(v.full_name, 1, 10),
487 ' || p_cat_select || ',
488 b.category_id category_id,
489 c.inventory_item_id,
490 c.lot_control_code,
491 c.repetitive_planning_flag repetitive_planned_item,
492 c.primary_uom_code,
493 p.ap_accrual_account,
494 p.invoice_price_var_account,
495 NVL(s.encumbrance_account,
496 NVL(m.encumbrance_account,
497 NVL(c.encumbrance_account, p.encumbrance_account))),
498 DECODE(c.inventory_asset_flag,
499 ''Y'', m.material_account,
500 NVL(m.expense_account,
501 NVL(c.expense_account, p.expense_account))),
502 NVL(s.source_type,
503 NVL(m.source_type,
504 NVL(c.source_type, p.source_type))),
505 DECODE(s.source_type,
506 NULL, DECODE(m.source_type,
507 NULL, DECODE(c.source_type,
508 NULL, DECODE(p.source_type,
509 NULL, NULL,
510 p.source_organization_id),
511 c.source_organization_id),
512 m.source_organization_id),
513 s.source_organization_id),
514 DECODE(s.source_type,
515 NULL, DECODE(m.source_type,
516 NULL, DECODE(c.source_type,
517 NULL, DECODE(p.source_type,
518 NULL, NULL,
519 p.source_subinventory),
520 c.source_subinventory),
521 m.source_subinventory),
522 s.source_subinventory),
523 c.purchasing_enabled_flag,
524 c.internal_order_enabled_flag,
525 c.mtl_transactions_enabled_flag,
526 c.list_price_per_unit,
527 c.planning_make_buy_code,
528 build_in_wip_flag,
529 pick_components_flag
530 FROM mtl_categories b,
531 mtl_item_categories a,
532 mtl_system_items c,
533 mtl_parameters p,
534 mtl_secondary_inventories m,
535 mtl_item_sub_inventories s,
536 per_all_people_f v
537 WHERE b.category_id = a.category_id
538 AND b.structure_id = :mcat_struct_id
539 AND c.inventory_item_flag = ''Y''
540 AND p.organization_id = :org_id
541 AND a.organization_id = c.organization_id
542 AND c.organization_id = :org_id
543 AND c.inventory_item_id = s.inventory_item_id
544 AND a.category_set_id = :cat_set_id
545 AND a.inventory_item_id = s.inventory_item_id
546 AND s.organization_id = :org_id
547 AND s.inventory_planning_code = 2
548 AND s.secondary_inventory = :sub
549 AND m.organization_id = :org_id
550 AND m.secondary_inventory_name = :sub
551 AND v.person_id (+) = c.buyer_id
552 AND (
553 (:l_sysdate between v.effective_start_date and v.effective_end_date)
554 OR
555 (v.effective_start_date IS NULL AND v.effective_end_date IS NULL)
556 )
557 AND ( ' || p_range_sql || ' )
558 AND ( ' || p_range_buyer || ' ) ';
559
560
561 -- Report columns
562 l_item_segments INV_MIN_MAX_TEMP.item_segments%TYPE;
563 l_catg_disp VARCHAR2(300);
564 l_sortee INV_MIN_MAX_TEMP.sortee%TYPE;
565 l_onhand_qty INV_MIN_MAX_TEMP.onhand_qty%TYPE;
566
567 l_stat INV_MIN_MAX_TEMP.error%TYPE;
568 l_supply_qty INV_MIN_MAX_TEMP.supply_qty%TYPE;
569 l_demand_qty INV_MIN_MAX_TEMP.demand_qty%TYPE;
570 l_tot_avail_qty INV_MIN_MAX_TEMP.tot_avail_qty%TYPE;
571 l_reord_qty INV_MIN_MAX_TEMP.reord_qty%TYPE;
572
573 l_err_msg VARCHAR2(2000);
574 l_vmi_enabled VARCHAR2(1);
575
576 BEGIN
577
578 --
579 -- Query debug settings, set global variables
580 --
581 init_debug(p_user_id);
582
583 IF G_TRACE_ON THEN
584 print_debug('Starting Min-max planning with the following parameters: ' ||
585 'p_item_select: ' || p_item_select ||
586 ', p_handle_rep_item: ' || to_char(p_handle_rep_item) ||
587 ', p_pur_revision: ' || to_char(p_pur_revision) ||
588 ', p_cat_select: ' || p_cat_select ||
589 ', p_cat_set_id: ' || to_char(p_cat_set_id) ||
590 ', p_mcat_struct: ' || to_char(p_mcat_struct) ||
591 ', p_level: ' || to_char(p_level) ||
592 ', p_restock: ' || to_char(p_restock) ||
593 ', p_include_nonnet: ' || to_char(p_include_nonnet) ||
594 ', p_include_po: ' || to_char(p_include_po) ||
595 ', p_include_wip: ' || to_char(p_include_wip) ||
596 ', p_include_if: ' || to_char(p_include_if)
597 , 'run_min_max_plan'
598 , 5);
599
600 print_debug('Parameters contd..: ' ||
601 'p_net_rsv: ' || to_char(p_net_rsv) ||
602 ', p_net_unrsv: ' || to_char(p_net_unrsv) ||
603 ', p_net_wip: ' || to_char(p_net_wip) ||
604 ', p_org_id: ' || to_char(p_org_id) ||
605 ', p_user_id: ' || to_char(p_user_id) ||
606 ', p_employee_id: ' || to_char(p_employee_id) ||
607 ', p_subinv: ' || p_subinv ||
608 ', p_dd_loc_id: ' || to_char(p_dd_loc_id) ||
609 ', p_wip_batch_id: ' || to_char(p_wip_batch_id) ||
610 ', p_approval: ' || to_char(p_approval) ||
611 ', p_buyer_hi: ' || p_buyer_hi ||
612 ', p_buyer_lo: ' || p_buyer_lo ||
613 ', p_range_buyer: ' || p_range_buyer
614 , 'run_min_max_plan'
615 , 5);
616
617 print_debug('Parameters contd..: ' ||
618 'p_cust_id: ' || to_char(p_cust_id) ||
619 ', p_po_org_id: ' || to_char(p_po_org_id) ||
620 ', p_range_sql: ' || p_range_sql ||
621 ', p_sort: ' || p_sort ||
622 ', p_selection: ' || to_char(p_selection) ||
623 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS') ||
624 ', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
625 ', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
626 ', p_order_by: ' || p_order_by ||
627 ', p_encum_flag: ' || p_encum_flag ||
628 ', p_cal_code: ' || p_cal_code ||
629 ', p_exception_set_id: ' || to_char(p_exception_set_id)
630 , 'run_min_max_plan'
631 , 5);
632 END IF;
633
634
635 --
636 -- Determine if we need to account for VMI
637 --
638 BEGIN
639 SELECT NVL(fnd_profile.value('PO_VMI_ENABLED'),'N')
640 INTO l_vmi_enabled
641 FROM dual;
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 l_vmi_enabled := 'N';
646 END;
647
648 print_debug('Profile PO_VMI_ENABLED is: ' || l_vmi_enabled
649 , 'run_min_max_plan'
650 , 5);
651
652 --
653 -- Decide which SQL statement to execute based on
654 -- planning level, sort options and whether or not
655 -- buyers are specified
656 --
657 IF p_level = 1
658 THEN
659 IF (p_sort = '1' OR p_sort = '2' OR p_sort = '3')
660 AND
661 (p_buyer_hi IS NULL AND p_buyer_lo IS NULL)
662 THEN
663 OPEN c_items_to_plan FOR sql_stmt1 || p_order_by
664 USING
665 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id;
666 ELSE
667 OPEN c_items_to_plan FOR sql_stmt3 || p_order_by
668 USING
669 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id, p_sysdate;
670 END IF;
671 ELSE
672 --
673 -- Planning at subinventory level (p_level = 2)
674 --
675 IF (p_sort = '1' OR p_sort = '2' OR p_sort = '3')
676 AND
677 (p_buyer_hi IS NULL AND p_buyer_lo IS NULL)
678 THEN
679 OPEN c_items_to_plan FOR sql_stmt2 || p_order_by
680 USING
681 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id,
682 p_org_id, p_subinv, p_org_id, p_subinv;
683 ELSE
684 OPEN c_items_to_plan FOR sql_stmt4 || p_order_by
685 USING
686 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id,
687 p_org_id, p_subinv, p_org_id, p_subinv, p_sysdate;
688 END IF;
689 END IF;
690
691 --
692 --
693 LOOP
694 FETCH c_items_to_plan INTO item_rec;
695 EXIT WHEN c_items_to_plan%NOTFOUND;
696
697 l_onhand_qty := get_onhand_qty( p_include_nonnet => p_include_nonnet
698 , p_level => p_level
699 , p_org_id => p_org_id
700 , p_subinv => p_subinv
701 , p_item_id => item_rec.item_id
702 , p_sysdate => p_sysdate);
703
704 l_supply_qty := get_supply_qty( p_org_id => p_org_id
705 , p_subinv => p_subinv
706 , p_item_id => item_rec.item_id
707 , p_postp_lead_time => item_rec.postprocessing_lead_time
708 , p_cal_code => p_cal_code
709 , p_except_id => p_exception_set_id
710 , p_level => p_level
711 , p_s_cutoff => p_s_cutoff
712 , p_include_po => p_include_po
713 , p_vmi_enabled => l_vmi_enabled
714 , p_include_nonnet => p_include_nonnet
715 , p_include_wip => p_include_wip
716 , p_include_if => p_include_if);
717
718 l_demand_qty := get_demand_qty( p_org_id => p_org_id
719 , p_subinv => p_subinv
720 , p_level => p_level
721 , p_item_id => item_rec.item_id
722 , p_d_cutoff => p_d_cutoff
723 , p_include_nonnet => p_include_nonnet
724 , p_net_rsv => p_net_rsv
725 , p_net_unrsv => p_net_unrsv
726 , p_net_wip => p_net_wip);
727
728 l_tot_avail_qty := NVL(l_onhand_qty,0) + NVL(l_supply_qty,0) - NVL(l_demand_qty,0);
729
730 print_debug('Item ID: ' || to_char(item_rec.item_id) ||
731 ', Onhand: ' || to_char(l_onhand_qty) ||
732 ', Supply: ' || to_char(l_supply_qty) ||
733 ', Demand: ' || to_char(l_demand_qty) ||
734 ', Avail: ' || to_char(l_tot_avail_qty)
735 , 'run_min_max_plan'
736 , 7);
737
738 --
739 -- Only need to display this item if:
740 -- 1. User chose "Items under min qty" and avail qty < min
741 -- 2. User chose "Items over max qty" and avail > max qty or
742 -- 3. User chose "All min-max planned items"
743 --
744 IF (p_selection = 1 AND l_tot_avail_qty < NVL(item_rec.min_qty, 0))
745 OR
746 (p_selection = 2 AND l_tot_avail_qty > NVL(item_rec.max_qty, 0))
747 OR
748 (p_selection = 3)
749 THEN
750 --
751 --
752 l_item_segments := get_item_segments(p_org_id, item_rec.item_id);
753
754 IF item_rec.category IS NOT NULL THEN
755 l_catg_disp := get_catg_disp(item_rec.category_id, p_mcat_struct);
756 ELSE
757 l_catg_disp := NULL;
758 END IF;
759
760 IF p_sort = '3'
761 THEN
762 l_sortee := substr(item_rec.planner,1,10);
763 ELSIF p_sort = '4'
764 THEN
765 l_sortee := substr(item_rec.buyer,1,10);
766 ELSE
767 l_sortee := l_catg_disp;
768 END IF;
769
770 l_reord_qty := get_reord_qty( p_min_qty => item_rec.min_qty
771 , p_max_qty => item_rec.max_qty
772 , p_min_ord_qty => item_rec.min_ord_qty
773 , p_max_ord_qty => item_rec.max_ord_qty
774 , p_tot_avail_qty => l_tot_avail_qty
775 , p_fix_mult => item_rec.fix_mult);
776
777 l_stat := get_reord_stat( p_restock => p_restock
778 , p_handle_rep_item => p_handle_rep_item
779 , p_level => p_level
780 , p_reord_qty => l_reord_qty
781 , p_wip_batch_id => p_wip_batch_id
782 , p_org_id => p_org_id
783 , p_subinv => p_subinv
784 , p_user_id => p_user_id
785 , p_employee_id => p_employee_id
786 , p_sysdate => p_sysdate
787 , p_approval => p_approval
788 , p_encum_flag => p_encum_flag
789 , p_cust_id => p_cust_id
790 , p_cal_code => p_cal_code
791 , p_exception_set_id => p_exception_set_id
792 , p_dd_loc_id => p_dd_loc_id
793 , p_po_org_id => p_po_org_id
794 , p_pur_revision => p_pur_revision
795 , p_item_rec => item_rec);
796
797 print_debug('Item ID: ' || to_char(item_rec.item_id) ||
798 ', Item Num: ' || l_item_segments ||
799 ', Reord qty: ' || to_char(l_reord_qty) ||
800 ', Reorder status: ' || l_stat
801 , 'run_min_max_plan'
802 , 7);
803
804 --
805 -- Insert into the global temp table INV_MIN_MAX_TEMP (defined
806 -- in patch/115/sql/invmmxtb.sql).
807 --
808 INSERT INTO INV_MIN_MAX_TEMP (
809 ITEM_SEGMENTS
810 , DESCRIPTION
811 , ERROR
812 , SORTEE
813 , MIN_QTY
814 , MAX_QTY
815 , ONHAND_QTY
816 , SUPPLY_QTY
817 , DEMAND_QTY
818 , TOT_AVAIL_QTY
819 , MIN_ORD_QTY
820 , MAX_ORD_QTY
821 , FIX_MULT
822 , REORD_QTY)
823 VALUES ( l_item_segments
824 , item_rec.description
825 , l_stat
826 , l_sortee
827 , item_rec.min_qty
828 , item_rec.max_qty
829 , l_onhand_qty
830 , l_supply_qty
831 , l_demand_qty
832 , l_tot_avail_qty
833 , item_rec.min_ord_qty
834 , item_rec.max_ord_qty
835 , item_rec.fix_mult
836 , l_reord_qty);
837 END IF;
838 END LOOP;
839 CLOSE c_items_to_plan;
840
841 x_return_status := 'S';
842
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF c_items_to_plan%ISOPEN THEN
846 CLOSE c_items_to_plan;
847 END IF;
848
849 l_err_msg := sqlerrm;
850 print_debug(sqlcode || ', ' || l_err_msg, 'run_min_max_plan', 1);
851 x_return_status := 'E';
852 x_msg_data := l_err_msg;
853
854 END run_min_max_plan;
855
856
857
858 FUNCTION get_item_segments ( p_org_id NUMBER
859 , p_item_id NUMBER ) RETURN VARCHAR2 IS
860
861 CURSOR c_item_segments IS
862 SELECT concatenated_segments
863 FROM mtl_system_items_kfv
864 WHERE organization_id = p_org_id
865 AND inventory_item_id = p_item_id;
866
867 c_item_seg_rec c_item_segments%ROWTYPE;
868
869 BEGIN
870 OPEN c_item_segments;
871 FETCH c_item_segments INTO c_item_seg_rec;
872 CLOSE c_item_segments;
873
874 RETURN c_item_seg_rec.concatenated_segments;
875 END get_item_segments;
876
877
878
879 FUNCTION get_catg_disp ( p_category_id NUMBER
880 , p_struct_id NUMBER) RETURN VARCHAR2 IS
881
882 CURSOR c_catg_disp IS
883 SELECT concatenated_segments
884 FROM mtl_categories_kfv
885 WHERE category_id = p_category_id
886 AND structure_id = p_struct_id;
887
888 c_catg_rec c_catg_disp%ROWTYPE;
889
890 BEGIN
891 OPEN c_catg_disp;
892 FETCH c_catg_disp INTO c_catg_rec;
893 CLOSE c_catg_disp;
894
895 RETURN c_catg_rec.concatenated_segments;
896 END get_catg_disp;
897
898
899
900 FUNCTION get_onhand_qty( p_include_nonnet NUMBER
901 , p_level NUMBER
902 , p_org_id NUMBER
903 , p_subinv VARCHAR2
904 , p_item_id NUMBER
905 , p_sysdate DATE) RETURN NUMBER IS
906
907 x_return_status VARCHAR2(30);
908
909 --Bug# 2677358
910 /*
911 x_msg_count NUMBER;
912 x_msg_data VARCHAR2(1000);
913 l_onhand_source NUMBER := 3;
914 l_subinventory_code VARCHAR2(30) := NULL;
915 l_qoh NUMBER := 0;
916 x_qoh NUMBER;
917 x_voh NUMBER;
918 x_rqoh NUMBER;
919 x_qr NUMBER;
920 x_qs NUMBER;
921 x_att NUMBER;
922 x_vatt NUMBER;
923 x_atr NUMBER;
924
925 l_onhand_exception EXCEPTION;
926 */
927 l_moq_qty1 NUMBER := 0;
928 l_mmtt_qty1 NUMBER := 0;
929 l_mmtt_qty2 NUMBER := 0;
930 l_qoh NUMBER := 0;
931
932 BEGIN
933
934 IF G_TRACE_ON THEN
935 print_debug('p_include_nonnet: ' || to_char(p_include_nonnet) ||
936 ', p_level: ' || to_char(p_level) ||
937 ', p_org_id: ' || to_char(p_org_id) ||
938 ', p_subinv: ' || p_subinv ||
939 ', p_item_id: ' || to_char(p_item_id) ||
940 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS')
941 , 'get_onhand_qty'
942 , 9);
943 END IF;
944
945 --Bug# 2677358
946 /*
947 IF (p_include_nonnet = 2)
948 THEN
949 l_onhand_source := 2;
950 END IF;
951
952 IF (p_level = 2)
953 THEN
954 l_subinventory_code := p_subinv;
955 END IF;
956 */
957
958 IF (p_level = 1) -- Org Level
959 THEN
960
961 SELECT SUM(moqd.transaction_quantity)
962 INTO l_moq_qty1
963 FROM mtl_onhand_quantities_detail moqd
964 WHERE moqd.organization_id = p_org_id
965 AND moqd.inventory_item_id = p_item_id
966 AND EXISTS (select 'x' from mtl_secondary_inventories msi
967 WHERE msi.organization_id = moqd.organization_id and
968 msi.secondary_inventory_name = moqd.subinventory_code
969 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
970 AND nvl(moqd.planning_tp_type,2) = 2;
971
972 print_debug('Total moqd quantity Org Level : ' || to_char(l_moq_qty1), 'get_onhand_qty', 9);
973
974 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
975 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
976 INTO l_mmtt_qty1
977 FROM mtl_material_transactions_temp mmtt
978 WHERE mmtt.organization_id = p_org_id
979 AND mmtt.inventory_item_id = p_item_id
980 AND mmtt.posting_flag = 'Y'
981 AND mmtt.subinventory_code IS NOT NULL
982 AND Nvl(mmtt.transaction_status,0) <> 2
983 AND mmtt.transaction_action_id NOT IN (24,30)
984 AND EXISTS (select 'x' from mtl_secondary_inventories msi
985 WHERE msi.organization_id = mmtt.organization_id
986 AND msi.secondary_inventory_name = mmtt.subinventory_code
987 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
988 AND nvl(mmtt.planning_tp_type,2) = 2;
989
990 print_debug('Total MMTT Trx quantity Source Org : ' || to_char(l_mmtt_qty1), 'get_onhand_qty', 9);
991
992 SELECT SUM(Abs(mmtt.primary_quantity))
993 INTO l_mmtt_qty2
994 FROM mtl_material_transactions_temp mmtt
995 WHERE decode(mmtt.transaction_action_id,3,
996 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
997 AND mmtt.inventory_item_id = p_item_id
998 AND mmtt.posting_flag = 'Y'
999 AND Nvl(mmtt.transaction_status,0) <> 2
1000 AND mmtt.transaction_action_id in (2,28,3)
1001 AND ((mmtt.transfer_subinventory IS NULL) OR
1002 (mmtt.transfer_subinventory IS NOT NULL
1003 AND EXISTS (select 'x' from mtl_secondary_inventories msi
1004 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
1005 3, mmtt.transfer_organization,mmtt.organization_id)
1006 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
1007 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))))
1008 AND nvl(mmtt.planning_tp_type,2) = 2;
1009
1010 print_debug('Total MMTT Trx quantity Dest Org : ' || to_char(l_mmtt_qty2), 'get_onhand_qty', 9);
1011
1012 ELSIF (p_level = 2) --Sub Level
1013 THEN
1014
1015 SELECT SUM(moqd.transaction_quantity)
1016 INTO l_moq_qty1
1017 FROM mtl_onhand_quantities_detail moqd
1018 WHERE moqd.organization_id = p_org_id
1019 AND moqd.inventory_item_id = p_item_id
1020 --AND moqd.planning_tp_type = 2
1021 AND moqd.subinventory_code = p_subinv;
1022
1023 print_debug('Total moqd quantity Sub Level : ' || to_char(l_moq_qty1), 'get_onhand_qty', 9);
1024
1025 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
1026 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
1027 INTO l_mmtt_qty1
1028 FROM mtl_material_transactions_temp mmtt
1029 WHERE mmtt.organization_id = p_org_id
1030 AND mmtt.inventory_item_id = p_item_id
1031 AND mmtt.subinventory_code = p_subinv
1032 --AND mmtt.planning_tp_type = 2
1033 AND mmtt.posting_flag = 'Y'
1034 AND mmtt.subinventory_code IS NOT NULL
1035 AND Nvl(mmtt.transaction_status,0) <> 2
1036 AND mmtt.transaction_action_id NOT IN (24,30);
1037
1038 print_debug('Total MMTT Trx quantity Source Org Sub : ' || to_char(l_mmtt_qty1), 'get_onhand_qty', 9);
1039
1040 SELECT SUM(Abs(mmtt.primary_quantity))
1041 INTO l_mmtt_qty2
1042 FROM mtl_material_transactions_temp mmtt
1043 WHERE decode(mmtt.transaction_action_id,3,
1044 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
1045 AND mmtt.inventory_item_id = p_item_id
1046 AND mmtt.transfer_subinventory = p_subinv
1047 --AND mmtt.planning_tp_type = 2
1048 AND mmtt.posting_flag = 'Y'
1049 AND Nvl(mmtt.transaction_status,0) <> 2
1050 AND mmtt.transaction_action_id in (2,28,3);
1051
1052 print_debug('Total MMTT Trx quantity Dest Org Sub : ' || to_char(l_mmtt_qty2), 'get_onhand_qty', 9);
1053
1054 END IF;
1055
1056 l_qoh := nvl(l_moq_qty1,0) + nvl(l_mmtt_qty1,0) + nvl(l_mmtt_qty2,0);
1057
1058 print_debug('Total quantity on-hand: ' || to_char(l_qoh), 'get_onhand_qty', 9);
1059 return (l_qoh);
1060
1061 --Bug# 2677358
1062 /*
1063 -- Clear the quantity tree cache
1064 inv_quantity_tree_grp.clear_quantity_cache;
1065
1066 inv_vmi_validations.get_available_vmi_quantity
1067 ( x_return_status => x_return_status
1068 , x_return_msg => x_msg_data
1069 , p_tree_mode => 2
1070 , p_organization_id => p_org_id
1071 , p_owning_org_id => NULL
1072 , p_planning_org_id => NULL
1073 , p_inventory_item_id => p_item_id
1074 , p_is_revision_control => 'FALSE'
1075 , p_is_lot_control => 'FALSE'
1076 , p_is_serial_control => 'FALSE'
1077 , p_revision => NULL
1078 , p_lot_number => NULL
1079 , p_lot_expiration_date => p_sysdate
1080 , p_subinventory_code => l_subinventory_code
1081 , p_locator_id => NULL
1082 , p_onhand_source => l_onhand_source
1083 , p_cost_group_id => NULL
1084 , x_qoh => x_qoh
1085 , x_att => x_att
1086 , x_voh => x_voh
1087 , x_vatt => x_vatt
1088 );
1089
1090
1091 IF G_TRACE_ON THEN
1092 print_debug('After call to inv_vmi_validations.get_available_vmi_quantity:' ||
1093 ' x_return_status: ' || x_return_status ||
1094 ', x_msg_data: ' || x_msg_data ||
1095 ', x_qoh: ' || to_char(x_qoh) ||
1096 ', x_att: ' || to_char(x_att) ||
1097 ', x_voh: ' || to_char(x_voh) ||
1098 ', x_vatt: ' || to_char(x_vatt)
1099 , 'get_onhand_qty'
1100 , 9);
1101 END IF;
1102
1103
1104 IF x_return_status = 'S' THEN
1105 l_qoh := NVL(x_qoh,0) - NVL(x_voh,0);
1106 ELSE
1107 RAISE l_onhand_exception;
1108 END IF;
1109 */
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 print_debug(sqlcode || ', ' || sqlerrm, 'get_onhand_qty', 1);
1114 RAISE;
1115
1116 END get_onhand_qty;
1117
1118
1119
1120 FUNCTION get_supply_qty( p_org_id NUMBER
1121 , p_subinv VARCHAR2
1122 , p_item_id NUMBER
1123 , p_postp_lead_time NUMBER
1124 , p_cal_code VARCHAR2
1125 , p_except_id NUMBER
1126 , p_level NUMBER
1127 , p_s_cutoff DATE
1128 , p_include_po NUMBER
1129 , p_vmi_enabled VARCHAR2
1130 , p_include_nonnet NUMBER
1131 , p_include_wip NUMBER
1132 , p_include_if NUMBER) RETURN NUMBER IS
1133
1134 l_qty NUMBER;
1135 l_total NUMBER;
1136
1137
1138 l_stmt VARCHAR2(4000) :=
1139 ' SELECT NVL(sum(to_org_primary_quantity), 0)
1140 FROM mtl_supply sup, bom_calendar_dates c1, bom_calendar_dates c
1141 WHERE sup.supply_type_code IN (''PO'',''REQ'',''SHIPMENT'',''RECEIVING'')
1142 AND sup.destination_type_code = ''INVENTORY''
1143 AND sup.to_organization_id = :l_org_id
1144 AND sup.item_id = :l_item_id
1145 AND c1.calendar_code = c.calendar_code
1146 AND c1.exception_set_id = c.exception_set_id
1147 AND c1.seq_num = (c.next_seq_num + trunc(:l_postp_lead_time))
1148 AND c.calendar_code = :l_cal_code
1149 AND c.exception_set_id = :l_except_id
1150 AND c.calendar_date = trunc(sup.need_by_date)
1151 AND trunc(c1.calendar_date) <= :l_s_cutoff
1152 AND (NVL(sup.from_organization_id,-1) <> :l_org_id
1153 OR (sup.from_organization_id = :l_org_id
1154 AND ((:l_include_nonnet = 2
1155 AND
1156 EXISTS (SELECT ''x''
1157 FROM mtl_secondary_inventories sub1
1158 WHERE sub1.organization_id = sup.from_organization_id
1159 AND sub1.secondary_inventory_name = sup.from_subinventory
1160 AND sub1.availability_type <> 1
1161 )
1162 )
1163 OR :l_level = 2
1164 )
1165 )
1166 )
1167 AND (sup.to_subinventory IS NULL
1168 OR
1169 (EXISTS (SELECT ''x''
1170 FROM mtl_secondary_inventories sub2
1171 WHERE sub2.secondary_inventory_name = sup.to_subinventory
1172 AND sub2.organization_id = sup.to_organization_id
1173 AND sub2.availability_type = decode(:l_include_nonnet,
1174 1,sub2.availability_type,
1175 1)
1176 )
1177 )
1178 OR :l_level = 2
1179 )
1180 AND (:l_level = 1 OR to_subinventory = :l_subinv) ';
1181
1182
1183
1184 l_vmi_stmt VARCHAR2(2000) :=
1185 ' AND (sup.po_line_location_id IS NULL
1186 OR EXISTS (SELECT ''x''
1187 FROM po_line_locations_all lilo
1188 WHERE lilo.line_location_id = sup.po_line_location_id
1189 AND NVL(lilo.vmi_flag,''N'') = ''N''
1190 )
1191 )
1192 AND (sup.req_line_id IS NULL
1193 OR EXISTS (SELECT ''x''
1194 FROM po_requisition_lines_all prl
1195 WHERE prl.requisition_line_id = sup.req_line_id
1196 AND NVL(prl.vmi_flag,''N'') = ''N''
1197 )
1198 )';
1199
1200 TYPE c_po_sup_curtype IS REF CURSOR;
1201 c_po_qty c_po_sup_curtype;
1202
1203 BEGIN
1204 IF G_TRACE_ON THEN
1205 print_debug('p_org_id: ' || to_char(p_org_id) ||
1206 ', p_subinv: ' || p_subinv ||
1207 ', p_item_id: ' || to_char(p_item_id) ||
1208 ', p_postp_lead_time: ' || to_char(p_postp_lead_time) ||
1209 ', p_cal_code: ' || p_cal_code ||
1210 ', p_except_id: ' || to_char(p_except_id) ||
1211 ', p_level: ' || to_char(p_level) ||
1212 ', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
1213 ', p_include_po: ' || to_char(p_include_po) ||
1214 ', p_include_nonnet: ' || to_char(p_include_nonnet) ||
1215 ', p_include_wip: ' || to_char(p_include_wip) ||
1216 ', p_include_if: ' || to_char(p_include_if)
1217 , 'get_supply_qty'
1218 , 9);
1219 END IF;
1220
1221 l_total := 0;
1222
1223 --
1224 -- MTL_SUPPLY
1225 --
1226 IF p_include_po = 1 THEN
1227
1228 IF (p_vmi_enabled = 'Y') AND (p_level= 1) THEN
1229 OPEN c_po_qty FOR l_stmt || l_vmi_stmt
1230 USING
1231 p_org_id, p_item_id, p_postp_lead_time, p_cal_code, p_except_id, p_s_cutoff, p_org_id,
1232 p_org_id, p_include_nonnet, p_level, p_include_nonnet, p_level, p_level, p_subinv;
1233 ELSE
1234 OPEN c_po_qty FOR l_stmt
1235 USING
1236 p_org_id, p_item_id, p_postp_lead_time, p_cal_code, p_except_id, p_s_cutoff, p_org_id,
1237 p_org_id, p_include_nonnet, p_level, p_include_nonnet, p_level, p_level, p_subinv;
1238 END IF;
1239
1240 FETCH c_po_qty INTO l_qty;
1241 CLOSE c_po_qty;
1242 print_debug('Supply from mtl_supply: ' || to_char(l_qty)
1243 , 'get_supply_qty'
1244 , 9);
1245
1246 l_total := l_total + l_qty;
1247
1248 END IF;
1249
1250 --
1251 -- Take into account the quantity for which a move order
1252 -- has already been created assuming that the move order
1253 -- can be created only within the same org
1254 --
1255 IF p_level = 2 THEN
1256 -- kkoothan Part of Bug Fix: 2875583
1257 -- Converting the quantities to the primary uom as the quantity
1258 -- and quantity delivered in mtl_txn_request_lines
1259 -- are in transaction uom.
1260
1261 /*SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
1262 INTO l_qty
1263 FROM mtl_transaction_types mtt,
1264 mtl_txn_request_lines mtrl
1265 WHERE mtt.transaction_action_id IN (2,28)
1266 AND mtt.transaction_type_id = mtrl.transaction_type_id
1267 AND mtrl.organization_id = p_org_id
1268 AND mtrl.inventory_item_id = p_item_id
1269 AND mtrl.to_subinventory_code = p_subinv
1270 AND mtrl.line_status NOT IN (5,6)
1271 AND mtrl.date_required <= p_s_cutoff; */
1272
1273 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
1274 ,p_item_id
1275 , mtrl.uom_code
1276 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
1277 ),0)
1278 INTO l_qty
1279 FROM mtl_transaction_types mtt,
1280 mtl_txn_request_lines mtrl
1281 WHERE mtt.transaction_action_id IN (2,28)
1282 AND mtt.transaction_type_id = mtrl.transaction_type_id
1283 AND mtrl.organization_id = p_org_id
1284 AND mtrl.inventory_item_id = p_item_id
1285 AND mtrl.to_subinventory_code = p_subinv
1286 AND mtrl.line_status NOT IN (5,6)
1287 AND mtrl.date_required <= p_s_cutoff;
1288
1289 print_debug('Supply from move orders: ' || to_char(l_qty)
1290 , 'get_supply_qty'
1291 , 9);
1292 l_total := l_total + l_qty;
1293 END IF;
1294
1295 --
1296 -- Supply FROM WIP discrete job is to be included at Org Level Planning Only
1297 --
1298 IF p_level = 1 AND p_include_wip = 1
1299 THEN
1300 SELECT sum(NVL(start_quantity,0)
1301 - NVL(quantity_completed,0)
1302 - NVL(quantity_scrapped,0))
1303 INTO l_qty
1304 FROM wip_discrete_jobs
1305 WHERE organization_id = p_org_id
1306 AND primary_item_id = p_item_id
1307 AND job_type in (1,3)
1308 AND status_type IN (1,3,4,6)
1309 --Bug 2647862
1310 AND trunc(scheduled_completion_date) <= p_s_cutoff
1311
1312 AND (NVL(start_quantity,0) - NVL(quantity_completed,0)
1313 - NVL(quantity_scrapped,0)) > 0;
1314
1315 print_debug('Supply from WIP discrete jobs: ' || to_char(l_qty)
1316 , 'get_supply_qty'
1317 , 9);
1318 l_total := l_total + NVL(l_qty,0);
1319
1320 --
1321 -- WIP REPETITIVE JOBS to be included at Org Level Planning Only
1322 --
1323 SELECT SUM(daily_production_rate *
1324 GREATEST(0, LEAST(processing_work_days,
1325 p_s_cutoff - first_unit_completion_date))
1326 - quantity_completed)
1327 INTO l_qty
1328 FROM wip_repetitive_schedules wrs,
1329 wip_repetitive_items wri
1330 WHERE wrs.organization_id = p_org_id
1331 AND wrs.status_type IN (1,3,4,6)
1332 AND wri.organization_id = p_org_id
1333 AND wri.primary_item_id = p_item_id
1334 AND wri.wip_entity_id = wrs.wip_entity_id
1335 AND wri.line_id = wrs.line_id
1336 AND (daily_production_rate *
1337 GREATEST(0, LEAST(processing_work_days,
1338 p_s_cutoff - first_unit_completion_date))
1339 - quantity_completed) > 0;
1340
1341
1342 print_debug('Supply from WIP repetitive jobs: ' || to_char(l_qty)
1343 , 'get_supply_qty'
1344 , 9);
1345 l_total := l_total + NVL(l_qty,0);
1346 END IF;
1347
1348 IF (p_include_if = 2)
1349 THEN
1350 RETURN(l_total);
1351 END IF;
1352
1353 --
1354 -- po_requisitions_interface_all
1355 --
1356 -- Bug: 2320752
1357 -- Do not include records in error status
1358 -- 2866966 added nvl to condition checking
1359 -- for error process flag
1360 SELECT NVL(SUM(quantity),0)
1361 INTO l_qty
1362 FROM po_requisitions_interface_all
1363 WHERE destination_organization_id = p_org_id
1364 AND item_id = p_item_id
1365 AND p_include_po = 1
1366 AND (p_level = 1 or destination_subinventory = p_subinv)
1367 AND need_by_date <= p_s_cutoff
1368 AND nvl(process_flag,'@@@') <> 'ERROR'
1369 AND (NVL(source_organization_id,-1) <> p_org_id OR
1370 (source_organization_id = p_org_id AND
1371 (( p_include_nonnet = 2 AND
1372 EXISTS (SELECT 'x'
1373 FROM mtl_secondary_inventories sub1
1374 WHERE sub1.organization_id = source_organization_id
1375 AND sub1.secondary_inventory_name = source_subinventory
1376 AND sub1.availability_type <> 1)) OR
1377 p_level = 2)
1378 ))
1379 AND (destination_subinventory IS NULL OR
1380 EXISTS (SELECT 1
1381 FROM mtl_secondary_inventories sub2
1382 WHERE secondary_inventory_name = destination_subinventory
1383 AND destination_subinventory = NVL(p_subinv,
1384 destination_subinventory)
1385 AND sub2.organization_id = p_org_id
1386 AND sub2.availability_type = decode(p_include_nonnet,
1387 1,sub2.availability_type,1)) OR
1388 p_level = 2);
1389
1390 print_debug('Supply from po_requisitions_interface_all: ' || to_char(l_qty)
1391 , 'get_supply_qty'
1392 , 9);
1393 l_total := l_total + NVL(l_qty,0);
1394
1395 --
1396 -- WIP_JOB_SCHEDULE_INTERFACE, processed immediately, hence not included
1397 --
1398 -- Supply FROM Flow to be included in org level only
1399 --
1400 IF p_level = 1
1401 THEN
1402 SELECT SUM(NVL(planned_quantity,0)
1403 - NVL(quantity_completed,0))
1404 INTO l_qty
1405 FROM wip_flow_schedules
1406 WHERE organization_id = p_org_id
1407 AND primary_item_id = p_item_id
1408 AND status = 1
1409 --Bug 2647862
1410 AND trunc(scheduled_completion_date) <= p_s_cutoff
1411 AND (NVL(planned_quantity,0)
1412 - NVL(quantity_completed,0)) > 0;
1413
1414 print_debug('Supply from WIP flow schedules: ' || to_char(l_qty)
1415 , 'get_supply_qty'
1416 , 9);
1417 l_total := l_total + NVL(l_qty,0);
1418 END IF;
1419
1420 RETURN(l_total);
1421
1422 EXCEPTION
1423 WHEN others THEN
1424 IF c_po_qty%ISOPEN THEN
1425 CLOSE c_po_qty;
1426 END IF;
1427
1428 print_debug(sqlcode || ', ' || sqlerrm, 'get_supply_qty', 1);
1429 RAISE;
1430 END get_supply_qty;
1431
1432
1433
1434 FUNCTION get_demand_qty( p_org_id NUMBER
1435 , p_subinv VARCHAR2
1436 , p_level NUMBER
1437 , p_item_id NUMBER
1438 , p_d_cutoff DATE
1439 , p_include_nonnet NUMBER
1440 , p_net_rsv NUMBER
1441 , p_net_unrsv NUMBER
1442 , p_net_wip NUMBER) RETURN NUMBER IS
1443
1444 qty NUMBER := 0;
1445 total NUMBER := 0;
1446 l_total_demand_qty NUMBER := 0;
1447 l_demand_qty NUMBER := 0;
1448 l_total_reserve_qty NUMBER := 0;
1449 l_pick_released_qty NUMBER := 0;
1450 l_staged_qty NUMBER := 0;
1451
1452 BEGIN
1453 IF G_TRACE_ON THEN
1454 print_debug('p_org_id: ' || to_char(p_org_id) ||
1455 ', p_subinv: ' || p_subinv ||
1456 ', p_level: ' || to_char(p_level) ||
1457 ', p_item_id: ' || to_char(p_item_id) ||
1458 ', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
1459 ', p_include_nonnet: ' || to_char(p_include_nonnet) ||
1460 ', p_net_rsv: ' || to_char(p_net_rsv) ||
1461 ', p_net_unrsv: ' || to_char(p_net_unrsv) ||
1462 ', p_net_wip: ' || to_char(p_net_wip)
1463 , 'get_demand_qty'
1464 , 9);
1465 END IF;
1466
1467 --
1468 -- select unreserved qty from mtl_demand for non oe rows.
1469 --
1470 IF p_net_unrsv = 1 THEN
1471 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1472 NVL(COMPLETED_QUANTITY,0)))
1473 into qty
1474 from mtl_demand
1475 WHERE RESERVATION_TYPE = 1
1476 AND parent_demand_id IS NULL
1477 AND ORGANIZATION_ID = p_org_id
1478 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1479 NVL(COMPLETED_QUANTITY,0))
1480 and INVENTORY_ITEM_ID = p_item_id
1481 and REQUIREMENT_DATE <= p_d_cutoff
1482 and demand_source_type not in (2,8,12)
1483 and (p_level = 1 or
1484 SUBINVENTORY = p_subinv) -- Included later for ORG Level
1485 and (SUBINVENTORY is null or
1486 p_level = 2 or
1487 EXISTS (SELECT 1
1488 FROM MTL_SECONDARY_INVENTORIES S
1489 WHERE S.ORGANIZATION_ID = p_org_id
1490 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1491 AND S.availability_type = DECODE(p_include_nonnet,
1492 1,
1493 S.availability_type,
1494 1)));
1495
1496 print_debug('Demand from mtl_demand: ' || to_char(qty), 'get_demand_qty', 9);
1497 total := total + NVL(qty,0);
1498 END IF;
1499
1500 --
1501 -- select the reserved quantity from mtl_reservations for non OE rows
1502 --
1503 IF p_net_rsv = 1 THEN
1504 select sum(PRIMARY_RESERVATION_QUANTITY)
1505 into qty
1506 from mtl_reservations
1507 where ORGANIZATION_ID = p_org_id
1508 and INVENTORY_ITEM_ID = p_item_id
1509 and REQUIREMENT_DATE <= p_d_cutoff
1510 and demand_source_type_id not in (2,8,12)
1511 and (p_level = 1 or
1512 SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
1513 and (SUBINVENTORY_CODE is null or
1514 p_level = 2 or
1515 EXISTS (SELECT 1
1516 FROM MTL_SECONDARY_INVENTORIES S
1517 WHERE S.ORGANIZATION_ID = p_org_id
1518 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1519 AND S.availability_type = DECODE(p_include_nonnet,
1520 1,
1521 S.availability_type,
1522 1)));
1523
1524 print_debug('Demand (reserved qty) for non OE rows in mtl_reservations: ' || to_char(qty)
1525 , 'get_demand_qty'
1526 , 9);
1527 total := total + NVL(qty,0);
1528 END IF;
1529
1530 --
1531 -- get the total demand which is the difference between the
1532 -- ordered qty. and the shipped qty.
1533 -- This gives the total demand including the reserved
1534 -- and the unreserved material.
1535 --
1536 -- Bug 2333526: For sub level planning we need to compute
1537 -- the staged qty. The existing WHERE clause makes sure
1538 -- we only do this when the order is sourced from the
1539 -- planning sub: level = 1... or SUBINVENTORY = p_subinv
1540 --
1541 -- Bug 2350243: For sub level, calculate pick released
1542 -- (move order) qty
1543 --
1544 if p_net_unrsv = 1 then
1545 select SUM(inv_decimals_pub.get_primary_quantity( ship_from_org_id
1546 , inventory_item_id
1547 , order_quantity_uom
1548 , NVL(ordered_quantity,0)) -
1549 get_shipped_qty(p_org_id, p_item_id, ool.line_id)),
1550 SUM(DECODE(p_level,
1551 2, get_staged_qty( p_org_id
1552 , p_subinv
1553 , p_item_id
1554 , ool.line_id
1555 , p_include_nonnet),
1556 0)
1557 ),
1558 SUM(DECODE(p_level,
1559 2, get_pick_released_qty( p_org_id
1560 , p_subinv
1561 , p_item_id
1562 , ool.line_id),
1563 0)
1564 )
1565 into l_total_demand_qty, l_staged_qty, l_pick_released_qty
1566 from oe_order_lines_all ool
1567 where ship_from_org_id = p_org_id
1568 and open_flag = 'Y'
1569 AND visible_demand_flag = 'Y'
1570 AND shipped_quantity is null
1571 and INVENTORY_ITEM_ID = p_item_id
1572 and schedule_ship_date <= p_d_cutoff
1573 AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
1574 , 10, 8
1575 , DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
1576 and ((p_level = 1
1577 AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
1578 , 10, 8
1579 , DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8)
1580 OR SUBINVENTORY = p_subinv) -- Included later for ORG Level
1581 and (SUBINVENTORY is null or
1582 p_level = 2 or
1583 EXISTS (SELECT 1
1584 FROM MTL_SECONDARY_INVENTORIES S
1585 WHERE S.ORGANIZATION_ID = p_org_id
1586 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1587 AND S.availability_type = DECODE(p_include_nonnet,
1588 1,
1589 S.availability_type,
1590 1)));
1591
1592 print_debug('Demand from sales orders: ' ||
1593 ' Ordered: ' || to_char(l_total_demand_qty) ||
1594 ', Pick released: ' || to_char(l_pick_released_qty) ||
1595 ', Staged: ' || to_char(l_staged_qty)
1596 , 'get_demand_qty'
1597 , 9);
1598 end if;
1599
1600 --
1601 -- Find out the reserved qty for the material from mtl_reservations
1602 --
1603 -- Since total demand = reserved + unreserved, and we know total
1604 -- demand from oe_order_lines_all (above) we only need to query
1605 -- mtl_reservations if the user wants one of the following:
1606 --
1607 -- 1) Only reserved: (p_net_rsv = 1 and p_net_unrsv = 2)
1608 --
1609 -- OR
1610 --
1611 -- 2) Only unreserved: (p_net_rsv = 2 and p_net_unrsv = 1)
1612 --
1613
1614 IF ((p_net_rsv = 1 and p_net_unrsv = 2)
1615 OR
1616 (p_net_rsv = 2 and p_net_unrsv = 1))
1617 THEN
1618 select sum(PRIMARY_RESERVATION_QUANTITY)
1619 into l_total_reserve_qty
1620 from mtl_reservations
1621 WHERE ORGANIZATION_ID = p_org_id
1622 and INVENTORY_ITEM_ID = p_item_id
1623 and REQUIREMENT_DATE <= p_d_cutoff
1624 and demand_source_type_id in (2,8,12)
1625 and ((p_level = 1 AND demand_source_type_id <> 8) OR
1626 SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
1627 and (SUBINVENTORY_CODE is null or
1628 p_level = 2 or
1629 EXISTS (SELECT 1
1630 FROM MTL_SECONDARY_INVENTORIES S
1631 WHERE S.ORGANIZATION_ID = p_org_id
1632 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1633 AND S.availability_type = DECODE(p_include_nonnet,
1634 1,
1635 S.availability_type,
1636 1)));
1637
1638 print_debug('Reserved demand (sales orders): ' || to_char(l_total_reserve_qty)
1639 , 'get_demand_qty'
1640 , 9);
1641 END IF;
1642
1643 --
1644 -- total demand is calculated as follows:
1645 -- if we have to consider both unreserved matl and reserved matl. then the
1646 -- demand is simply the total demand = ordered qty - shipped qty.
1647 -- Bug 2333526: Deduct staged qty for sub level. (l_staged_qty
1648 -- is always set to 0 for org level planning).
1649 -- elsif we have to take into account only reserved matl. then the
1650 -- demand is simply the reservations from mtl_reservations for the matl.
1651 -- elsif we have to take into account just the unreserved matl. then the
1652 -- demand is total demand - the reservations for the material.
1653 --
1654 IF p_net_unrsv = 1 AND p_net_rsv = 1 THEN
1655 l_demand_qty := NVL(l_total_demand_qty,0)
1656 - NVL(l_staged_qty,0)
1657 - NVL(l_pick_released_qty,0);
1658
1659 ELSIF p_net_rsv = 1 THEN
1660 l_demand_qty := NVL(l_total_reserve_qty,0);
1661
1662 ELSIF p_net_unrsv = 1 THEN
1663 l_demand_qty := NVL(l_total_demand_qty,0) - NVL(l_total_reserve_qty,0);
1664
1665 END IF;
1666
1667 print_debug('Demand from shippable orders: ' || to_char(l_demand_qty)
1668 , 'get_demand_qty'
1669 , 9);
1670 total := total + NVL(l_demand_qty,0);
1671
1672 --
1673 -- Take care of internal orders for org level planning
1674 --
1675 if p_level = 1 then
1676 l_total_demand_qty := 0;
1677 l_demand_qty := 0;
1678 l_total_reserve_qty := 0;
1679
1680 --
1681 -- get the total demand which is the difference between the
1682 -- ordered qty. and the shipped qty.
1683 -- This gives the total demand including the reserved
1684 -- and the unreserved material.
1685 --
1686 -- Bug 2820011. Modified the where clause to make use of source_document_id
1687 -- and source_document_line_id of oe_order_lines_all instead of
1688 -- orig_sys_document_ref and orig_sys_line_ref to identify requisitions
1689 -- and requisition lines uniquely.
1690
1691 if p_net_unrsv = 1 then
1692 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( SHIP_FROM_ORG_ID
1693 , INVENTORY_ITEM_ID
1694 , ORDER_QUANTITY_UOM
1695 , NVL(ordered_quantity,0)) -
1696 get_shipped_qty(p_org_id, p_item_id, so.line_id))
1697 into l_total_demand_qty
1698 from oe_order_lines_all so,
1699 -- po_requisition_headers_all poh,
1700 po_requisition_lines_all pol
1701 where so.SOURCE_DOCUMENT_ID = pol.requisition_header_id
1702 -- and poh.requisition_header_id = pol.requisition_header_id
1703 and so.source_document_line_id = pol.requisition_line_id
1704 and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
1705 (pol.DESTINATION_ORGANIZATION_ID = p_org_id and -- Added code Bug#1012179
1706 pol.DESTINATION_TYPE_CODE = 'EXPENSE')
1707 )
1708 and so.ship_from_org_ID = p_org_id
1709 and so.open_flag = 'Y'
1710 AND so.visible_demand_flag = 'Y'
1711 AND shipped_quantity is null
1712 and so.INVENTORY_ITEM_ID = p_item_id
1713 and schedule_ship_date <= p_d_cutoff
1714 and DECODE(so.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(so.LINE_CATEGORY_CODE, 'ORDER',2,12)) = 8
1715 and (SUBINVENTORY is null or
1716 EXISTS (SELECT 1
1717 FROM MTL_SECONDARY_INVENTORIES S
1718 WHERE S.ORGANIZATION_ID = p_org_id
1719 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1720 AND S.availability_type = DECODE(p_include_nonnet,
1721 1,
1722 S.availability_type,
1723 1)));
1724
1725 print_debug('Total demand (internal orders): ' || to_char(l_total_demand_qty)
1726 , 'get_demand_qty'
1727 , 9);
1728 end if;
1729
1730 --
1731 -- Find out the reserved qty for the material from mtl_reservations
1732 --
1733 IF ((p_net_rsv = 1 and p_net_unrsv = 2)
1734 OR
1735 (p_net_rsv = 2 and p_net_unrsv = 1))
1736 THEN
1737 --
1738 -- Include the reserved demand from mtl_reservations
1739 --
1740 select sum(PRIMARY_RESERVATION_QUANTITY)
1741 into l_total_reserve_qty
1742 from mtl_reservations md, oe_order_lines_all so,
1743 po_req_distributions_all pod,
1744 po_requisition_lines_all pol
1745 where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
1746 and so.ORIG_SYS_LINE_REF = to_char(pod.DISTRIBUTION_ID)
1747 and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
1748 and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
1749 (pol.DESTINATION_ORGANIZATION_ID = p_org_id
1750 and -- Added code Bug#1012179
1751 pol.DESTINATION_TYPE_CODE = 'EXPENSE')
1752 )
1753 and ORGANIZATION_ID = p_org_id
1754 and md.INVENTORY_ITEM_ID = p_item_id
1755 and REQUIREMENT_DATE <= p_d_cutoff
1756 and demand_source_type_id = 8
1757 and (SUBINVENTORY_CODE is null or
1758 EXISTS (SELECT 1
1759 FROM MTL_SECONDARY_INVENTORIES S
1760 WHERE S.ORGANIZATION_ID = p_org_id
1761 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1762 AND S.availability_type = DECODE(p_include_nonnet,
1763 1,
1764 S.availability_type,
1765 1)));
1766
1767 print_debug('Reserved demand (internal orders): ' || to_char(l_total_reserve_qty)
1768 , 'get_demand_qty'
1769 , 9);
1770 END IF;
1771
1772 --
1773 -- total demand is calculated as follows:
1774 -- if we have to consider both unreserved matl and reserved matl. then the
1775 -- demand is simply the total demand = ordered qty - shipped qty.
1776 -- elsif we have to take into account only reserved matl. then the
1777 -- demand is simply the reservations from mtl_reservations for the matl.
1778 -- elsif we have to take into account just the unreserved matl. then the
1779 -- demand is total demand - the reservations for the material.
1780 --
1781 if p_net_unrsv = 1 and p_net_rsv = 1 then
1782 l_demand_qty := NVL(l_total_demand_qty,0);
1783
1784 elsif p_net_rsv = 1 then
1785 l_demand_qty := NVL(l_total_reserve_qty,0);
1786
1787 elsif p_net_unrsv = 1 then
1788 l_demand_qty := NVL(l_total_demand_qty,0) - NVL(l_total_reserve_qty,0);
1789 end if;
1790
1791 print_debug('Demand from internal orders: ' || to_char(l_demand_qty)
1792 , 'get_demand_qty'
1793 , 9);
1794 total := total + NVL(l_demand_qty,0);
1795
1796 end if; -- end if level=1
1797
1798 --
1799 -- WIP Reservations from mtl_demand
1800 --
1801 IF p_level = 1 THEN
1802 --
1803 -- SUBINVENTORY IS Always expected to be Null when Reservation_type is 3.
1804 --
1805 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1806 NVL(COMPLETED_QUANTITY,0)))
1807 into qty
1808 from mtl_demand
1809 where RESERVATION_TYPE = 3
1810 and ORGANIZATION_ID = p_org_id
1811 and PRIMARY_UOM_QUANTITY >
1812 GREATEST(NVL(RESERVATION_QUANTITY,0), NVL(COMPLETED_QUANTITY,0))
1813 and INVENTORY_ITEM_ID = p_item_id
1814 and REQUIREMENT_DATE <= p_d_cutoff
1815 and p_net_rsv = 1;
1816
1817 print_debug('WIP Reservations from mtl_demand: ' || to_char(qty)
1818 , 'get_demand_qty'
1819 , 9);
1820 total := total + NVL(qty,0);
1821 END IF;
1822
1823 --
1824 -- Wip Components are to be included at the Org Level Planning only.
1825 -- Qty Issued Substracted from the Qty Required
1826 --
1827 if (p_net_wip = 1 and p_level = 1)
1828 then
1829 select sum(o.required_quantity - o.quantity_issued)
1830 into qty
1831 from wip_discrete_jobs d, wip_requirement_operations o
1832 where o.wip_entity_id = d.wip_entity_id
1833 and o.organization_id = d.organization_id
1834 and d.organization_id = p_org_id
1835 and o.inventory_item_id = p_item_id
1836 and o.date_required <= p_d_cutoff
1837 and o.required_quantity > 0
1838 and o.required_quantity > o.quantity_issued
1839 and o.operation_seq_num > 0
1840 and d.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
1841 and o.wip_supply_type <> 6;
1842
1843 print_debug('WIP component requirements for discrete jobs: ' || to_char(qty)
1844 , 'get_demand_qty'
1845 , 9);
1846 total := total + NVL(qty,0);
1847
1848 --
1849 -- Demand Qty to be added for a released repetitive schedule
1850 -- Bug#691471
1851 --
1852 select sum(o.required_quantity - o.quantity_issued)
1853 into qty
1854 from wip_repetitive_schedules r, wip_requirement_operations o
1855 where o.wip_entity_id = r.wip_entity_id
1856 and o.repetitive_schedule_id = r.repetitive_schedule_id
1857 and o.organization_id = r.organization_id
1858 and r.organization_id = p_org_id
1859 and o.inventory_item_id = p_item_id
1860 and o.date_required <= p_d_cutoff
1861 and o.required_quantity > 0
1862 and o.required_quantity > o.quantity_issued
1863 and o.operation_seq_num > 0
1864 and r.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
1865 and o.wip_supply_type <> 6;
1866
1867 print_debug('WIP component requirements for repetitve schedules: ' || to_char(qty)
1868 , 'get_demand_qty'
1869 , 9);
1870 total := total + NVL(qty,0);
1871 end if;
1872
1873 --
1874 -- Include move orders:
1875 -- Leave out the closed or cancelled lines
1876 -- Select only Issue from Stores for org level planning
1877 -- Also select those lines for sub level planning.
1878 --
1879 -- Exclude move orders created for WIP Issue transaction
1880 -- (txn type = 35, INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE)
1881 -- since these are already taken into account (above) by
1882 -- directly querying the WIP tables for open component requirements
1883
1884 -- kkoothan Part of Bug Fix: 2875583
1885 -- Converting the quantities to the primary uom as the quantity
1886 -- and quantity delivered in mtl_txn_request_lines
1887 -- are in transaction uom.
1888 --
1889
1890 /*SELECT SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED,0))
1891 INTO qty
1892 FROM MTL_TXN_REQUEST_LINES MTRL,
1893 MTL_TRANSACTION_TYPES MTT
1894 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
1895 AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
1896 AND MTRL.ORGANIZATION_ID = p_org_id
1897 AND MTRL.INVENTORY_ITEM_ID = p_item_id
1898 AND MTRL.LINE_STATUS NOT IN (5,6)
1899 AND MTT.TRANSACTION_ACTION_ID = 1
1900 AND (p_level = 1 OR
1901 MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
1902 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
1903 p_level = 2 OR
1904 EXISTS (SELECT 1
1905 FROM MTL_SECONDARY_INVENTORIES S
1906 WHERE S.ORGANIZATION_ID = p_org_id
1907 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
1908 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
1909 1,S.AVAILABILITY_TYPE,1)))
1910 AND MTRL.DATE_REQUIRED <= p_d_cutoff; */
1911
1912 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
1913 ,p_item_id
1914 , mtrl.uom_code
1915 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
1916 ),0)
1917 INTO qty
1918 FROM MTL_TXN_REQUEST_LINES MTRL,
1919 MTL_TRANSACTION_TYPES MTT
1920 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
1921 AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
1922 AND MTRL.ORGANIZATION_ID = p_org_id
1923 AND MTRL.INVENTORY_ITEM_ID = p_item_id
1924 AND MTRL.LINE_STATUS NOT IN (5,6)
1925 AND MTT.TRANSACTION_ACTION_ID = 1
1926 AND (p_level = 1 OR
1927 MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
1928 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
1929 p_level = 2 OR
1930 EXISTS (SELECT 1
1931 FROM MTL_SECONDARY_INVENTORIES S
1932 WHERE S.ORGANIZATION_ID = p_org_id
1933 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
1934 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
1935 1,S.AVAILABILITY_TYPE,1)))
1936 AND MTRL.DATE_REQUIRED <= p_d_cutoff;
1937
1938 print_debug('Demand from open move orders: ' || to_char(qty), 'get_demand_qty', 9);
1939
1940 total := total + NVL(qty,0);
1941
1942 --
1943 -- Include the sub transfer and the staging transfer move orders
1944 -- for sub level planning
1945 --
1946 IF p_level = 2 THEN
1947 -- kkoothan Part of Bug Fix: 2875583
1948 -- Converting the quantities to the primary uom as the quantity
1949 -- and quantity delivered in mtl_txn_request_lines
1950 -- are in transaction uom.
1951
1952 /*SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
1953 INTO qty
1954 FROM mtl_transaction_types mtt,
1955 mtl_txn_request_lines mtrl
1956 WHERE mtt.transaction_action_id IN (2,28)
1957 AND mtt.transaction_type_id = mtrl.transaction_type_id
1958 AND mtrl.organization_id = p_org_id
1959 AND mtrl.inventory_item_id = p_item_id
1960 AND mtrl.from_subinventory_code = p_subinv
1961 AND mtrl.line_status NOT IN (5,6)
1962 AND mtrl.date_required <= p_d_cutoff;*/
1963
1964 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
1965 ,p_item_id
1966 ,mtrl.uom_code
1967 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
1968 ),0)
1969 INTO qty
1970 FROM mtl_transaction_types mtt,
1971 mtl_txn_request_lines mtrl
1972 WHERE mtt.transaction_action_id IN (2,28)
1973 AND mtt.transaction_type_id = mtrl.transaction_type_id
1974 AND mtrl.organization_id = p_org_id
1975 AND mtrl.inventory_item_id = p_item_id
1976 AND mtrl.from_subinventory_code = p_subinv
1977 AND mtrl.line_status NOT IN (5,6)
1978 AND mtrl.date_required <= p_d_cutoff;
1979
1980 print_debug('Qty pending out due to sub transfers and the staging transfer move orders: '
1981 || to_char(qty)
1982 , 'get_demand_qty'
1983 , 9);
1984 total := total + NVL(qty,0);
1985 END IF;
1986
1987 return(total);
1988
1989 exception
1990 when others then
1991 print_debug(sqlcode || ', ' || sqlerrm, 'get_demand_qty', 1);
1992 RAISE;
1993 end get_demand_qty;
1994
1995
1996
1997 FUNCTION get_shipped_qty( p_organization_id IN NUMBER
1998 , p_inventory_item_id IN NUMBER
1999 , p_order_line_id IN NUMBER) RETURN NUMBER IS
2000
2001 l_shipped_qty NUMBER := 0;
2002
2003 BEGIN
2004
2005 --
2006 -- Only look at source types 2 and 8 (sales orders, internal orders)
2007 --
2008 SELECT SUM(primary_quantity)
2009 INTO l_shipped_qty
2010 FROM mtl_material_transactions
2011 WHERE transaction_action_id = 1
2012 AND source_line_id = p_order_line_id
2013 AND organization_id = p_organization_id
2014 AND inventory_item_id = p_inventory_item_id
2015 AND transaction_source_type_id in (2,8);
2016
2017 IF l_shipped_qty IS NULL THEN
2018 l_shipped_qty := 0;
2019 ELSE
2020 l_shipped_qty := -1 * l_shipped_qty;
2021 END IF;
2022
2023 RETURN l_shipped_qty;
2024
2025 END get_shipped_qty;
2026
2027
2028
2029 FUNCTION get_staged_qty( p_org_id NUMBER
2030 , p_subinv VARCHAR2
2031 , p_item_id NUMBER
2032 , p_order_line_id NUMBER
2033 , p_include_nonnet NUMBER) RETURN NUMBER IS
2034
2035 l_staged_qty NUMBER := 0;
2036
2037 BEGIN
2038
2039 BEGIN
2040 --
2041 -- Bugfix 2333526: Need to calculate staged quantity
2042 -- for sub level planning. If passed-in (planning)
2043 -- sub is the also the staging sub, then ignore
2044 -- p_include_nonnet
2045 --
2046 SELECT NVL(SUM(primary_reservation_quantity),0)
2047 INTO l_staged_qty
2048 FROM mtl_reservations
2049 WHERE organization_id = p_org_id
2050 AND inventory_item_id = p_item_id
2051 AND demand_source_line_id = p_order_line_id
2052 AND demand_source_type_id IN (2,8,12)
2053 AND NVL(staged_flag, 'X') = 'Y'
2054 AND subinventory_code IS NOT NULL
2055 AND (subinventory_code = p_subinv
2056 OR
2057 EXISTS (SELECT 1
2058 FROM mtl_secondary_inventories s
2059 WHERE s.organization_id = p_org_id
2060 AND s.secondary_inventory_name = subinventory_code
2061 AND s.availability_type = DECODE(p_include_nonnet,
2062 1,
2063 S.availability_type,
2064 1)));
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 l_staged_qty := 0;
2068 END;
2069
2070 RETURN l_staged_qty;
2071
2072 END get_staged_qty;
2073
2074
2075
2076 FUNCTION get_pick_released_qty( p_org_id NUMBER
2077 , p_subinv VARCHAR2
2078 , p_item_id NUMBER
2079 , p_order_line_id NUMBER) RETURN NUMBER IS
2080
2081 l_pick_released_qty NUMBER := 0;
2082
2083 BEGIN
2084
2085 BEGIN
2086 --
2087 -- Move order type 3 is pick wave, source type 2 is sales order
2088 --
2089 SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
2090 INTO l_pick_released_qty
2091 FROM mtl_txn_request_headers mtrh,
2092 mtl_txn_request_lines mtrl
2093 WHERE mtrh.move_order_type = 3
2094 AND mtrh.header_id = mtrl.header_id
2095 AND mtrl.organization_id = p_org_id
2096 AND mtrl.inventory_item_id = p_item_id
2097 AND mtrl.from_subinventory_code = p_subinv
2098 AND mtrl.txn_source_line_id = p_order_line_id
2099 AND mtrl.transaction_source_type_id = 2
2100 AND mtrl.line_status NOT IN (5,6);
2101
2102 EXCEPTION
2103 WHEN OTHERS THEN
2104 l_pick_released_qty := 0;
2105 END;
2106
2107 RETURN l_pick_released_qty;
2108
2109 END get_pick_released_qty;
2110
2111
2112
2113 FUNCTION get_reord_qty( p_min_qty NUMBER
2114 , p_max_qty NUMBER
2115 , p_min_ord_qty NUMBER
2116 , p_max_ord_qty NUMBER
2117 , p_tot_avail_qty NUMBER
2118 , p_fix_mult NUMBER) RETURN NUMBER IS
2119
2120 l_min_qty NUMBER;
2121 l_max_qty NUMBER;
2122 l_min_ord_qty NUMBER;
2123 l_fix_mult NUMBER;
2124 reorder NUMBER;
2125 min_restock_qty NUMBER;
2126 qty_for_last_order NUMBER;
2127 round_reord_qty VARCHAR2(1);
2128
2129 BEGIN
2130 IF G_TRACE_ON THEN
2131 print_debug('p_min_qty: ' || to_char(p_min_qty) ||
2132 ', p_max_qty: ' || to_char(p_max_qty) ||
2133 ', p_min_ord_qty: ' || to_char(p_min_ord_qty) ||
2134 ', p_max_ord_qty: ' || to_char(p_max_ord_qty) ||
2135 ', p_tot_avail_qty: ' || to_char(p_tot_avail_qty) ||
2136 ', p_fix_mult: ' || to_char(p_fix_mult)
2137 , 'get_reord_qty'
2138 , 9);
2139 END IF;
2140
2141
2142 /* GENERAL ALGORITHM:
2143
2144 When to order?
2145 When total available < minimum for item
2146
2147 How much to order?
2148 reorder qty = max stockable qty - total available
2149
2150 If reorder qty < min ord qty, increase reorder qty to min ord qty
2151
2152 If a fixed lot multiple is defined
2153 Round the reorder up or down based on profile INV_ROUND_REORDER_QTY
2154
2155 If a max ord qty is not specified
2156 or if reorder qty < max ord qty, no changes required
2157
2158 If max ord qty is specified
2159 and reorder qty exceeds max ord qty:
2160
2161 We need to make sure that after creating one or more orders
2162 for max order qty, the remaining quantity exceeds min ord qty
2163
2164 For e.g.:
2165 reorder qty = 34
2166 max ord qty = 10
2167 min ord qty = 5
2168
2169 Then restocking code will create 3 orders (move orders, requisitions
2170 or work orders) for 10 each, which is 30. The left over qty is
2171 34 - 30 = 4. Since the min ord qty is 5, we should discard the
2172 remaining qty of 4. If the remaining qty was say 8, then the last
2173 move order/requisition/work order would be for qty 8, and so on.
2174
2175 If no min ord qty is specified (or if it is 0) then this downward
2176 adjustment is not required.
2177 end if;
2178 */
2179
2180
2181 l_min_qty := NVL(p_min_qty,0);
2182 l_max_qty := NVL(p_max_qty,0);
2183 l_min_ord_qty := NVL(p_min_ord_qty,0);
2184 l_fix_mult := NVL(p_fix_mult, 0);
2185
2186 IF p_tot_avail_qty >= l_min_qty
2187 THEN
2188 RETURN 0;
2189 END if;
2190
2191 reorder := l_max_qty - p_tot_avail_qty;
2192
2193 print_debug('Initial estimated reorder qty: ' || to_char(reorder)
2194 , 'get_reord_qty'
2195 , 9);
2196
2197 IF l_min_ord_qty >= reorder
2198 THEN
2199 RETURN l_min_ord_qty;
2200 END if;
2201
2202 IF l_fix_mult > 0
2203 THEN
2204 round_reord_qty := NVL(FND_PROFILE.VALUE('INV_ROUND_REORDER_QTY'), 'Y');
2205 print_debug('round_reord_qty: ' || round_reord_qty, 'get_reord_qty', 9);
2206
2207 IF round_reord_qty = 'N'
2208 THEN
2209 reorder := floor(reorder/l_fix_mult) * l_fix_mult;
2210 ELSE
2211 reorder := ceil(reorder/l_fix_mult) * l_fix_mult;
2212 END if;
2213
2214 print_debug('Reorder qty after applying fix lot multiple: '
2215 || to_char(reorder)
2216 , 'get_reord_qty'
2217 , 9);
2218 END if;
2219
2220 IF p_max_ord_qty IS NULL OR reorder <= p_max_ord_qty
2221 THEN
2222 RETURN reorder;
2223 ELSIF p_max_ord_qty > 0
2224 THEN
2225 min_restock_qty := floor(reorder/p_max_ord_qty) * p_max_ord_qty;
2226 qty_for_last_order := reorder - min_restock_qty;
2227 print_debug('Min reord qty that is a multiple of max ord qty: '
2228 || to_char(min_restock_qty)
2229 , 'get_reord_qty'
2230 , 9);
2231
2232 IF p_min_ord_qty IS NULL OR qty_for_last_order >= p_min_ord_qty
2233 THEN
2234 RETURN reorder;
2235 ELSE
2236 RETURN min_restock_qty;
2237 END IF;
2238 END if;
2239
2240 RETURN reorder;
2241
2242 EXCEPTION
2243 WHEN OTHERS THEN
2244 print_debug(sqlcode || ', ' || sqlerrm, 'get_reord_qty', 1);
2245 RAISE;
2246 END get_reord_qty;
2247
2248
2249
2250 FUNCTION get_reord_stat ( p_restock NUMBER
2251 , p_handle_rep_item NUMBER
2252 , p_level NUMBER
2253 , p_reord_qty NUMBER
2254 , p_wip_batch_id NUMBER
2255 , p_org_id NUMBER
2256 , p_subinv VARCHAR2
2257 , p_user_id NUMBER
2258 , p_employee_id NUMBER
2259 , p_sysdate DATE
2260 , p_approval NUMBER
2261 , p_encum_flag VARCHAR2
2262 , p_cust_id NUMBER
2263 , p_cal_code VARCHAR2
2264 , p_exception_set_id NUMBER
2265 , p_dd_loc_id NUMBER
2266 , p_po_org_id NUMBER
2267 , p_pur_revision NUMBER
2268 , p_item_rec minmax_items_rectype) RETURN VARCHAR2 IS
2269
2270 v_make_buy_flag NUMBER;
2271 l_error_message VARCHAR2(100);
2272 l_ret_stat VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2273 need_by_date DATE;
2274 reorder_qty NUMBER;
2275 move_ord_qty NUMBER;
2276 counter INTEGER;
2277 no_of_iterations NUMBER;
2278 BEGIN
2279 IF G_TRACE_ON THEN
2280 print_debug('p_restock: ' || to_char(p_restock) ||
2281 ', p_handle_rep_item: ' || to_char(p_handle_rep_item) ||
2282 ', p_level; ' || to_char(p_level) ||
2283 ', p_reord_qty: ' || to_char(p_reord_qty) ||
2284 ', p_wip_batch_id: ' || to_char(p_wip_batch_id) ||
2285 ', p_org_id: ' || to_char(p_org_id) ||
2286 ', p_subinv: ' || p_subinv ||
2287 ', p_user_id: ' || to_char(p_user_id) ||
2288 ', p_employee_id: ' || to_char(p_employee_id) ||
2289 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS')
2290 , 'get_reord_stat'
2291 , 9);
2292
2293 print_debug('p_approval: ' || to_char(p_approval) ||
2294 ', p_encum_flag: ' || p_encum_flag ||
2295 ', p_cust_id: ' || to_char(p_cust_id) ||
2296 ', p_cal_code: ' || p_cal_code ||
2297 ', p_exception_set_id: ' || to_char(p_exception_set_id) ||
2298 ', p_dd_loc_id: ' || to_char(p_dd_loc_id) ||
2299 ', p_po_org_id: ' || to_char(p_po_org_id) ||
2300 ', p_pur_revision: ' || to_char(p_pur_revision) ||
2301 ', p_item_rec: ' || to_char(p_item_rec.item_id)
2302 , 'get_reord_stat'
2303 , 9);
2304 END IF;
2305
2306 --
2307 -- If the item is a repetitive item and the user chose not to restock
2308 -- repetitive items, or if the planning level is "Org" and source type
2309 -- is subinventory (3) do not restock - simply return a null.
2310 --
2311 -- Restocking with source type sub will result in a move order and this
2312 -- only makes sense for sub level planning.
2313 --
2314 -- For sub level planning, always set the make_or_buy flag to "buy",
2315 -- i.e., do not create a work order for sub level planning.
2316 --
2317 IF p_restock = 1 THEN
2318 IF (p_item_rec.repetitive_planned_item = 'Y' AND p_handle_rep_item = 3)
2319 OR
2320 (p_level = 1 and p_item_rec.src_type = 3)
2321 THEN
2322 RETURN('');
2323 ELSE
2324 IF p_level = 2
2325 THEN
2326 v_make_buy_flag := 2;
2327 ELSE
2328 v_make_buy_flag := p_item_rec.mbf;
2329 END IF;
2330 END IF;
2331 ELSE
2332 RETURN ('');
2333 END IF;
2334
2335 reorder_qty := NVL(p_reord_qty,0);
2336
2337 WHILE (reorder_qty > 0)
2338 LOOP
2339 IF NVL(p_item_rec.max_ord_qty,0) = 0
2340 THEN
2341 move_ord_qty := reorder_qty;
2342 ELSIF (reorder_qty > p_item_rec.max_ord_qty)
2343 THEN
2344 move_ord_qty := p_item_rec.max_ord_qty;
2345 ELSE
2346 move_ord_qty := reorder_qty;
2347 END IF;
2348
2349 do_restock( p_item_id => p_item_rec.item_id
2350 , p_mbf => v_make_buy_flag
2351 , p_handle_repetitive_item => p_handle_rep_item
2352 , p_repetitive_planned_item => p_item_rec.repetitive_planned_item
2353 , p_qty => move_ord_qty
2354 , p_fixed_lead_time => p_item_rec.fixed_lead_time
2355 , p_variable_lead_time => p_item_rec.variable_lead_time
2356 , p_buying_lead_time => p_item_rec.buying_lead_time
2357 , p_uom => p_item_rec.primary_uom
2358 , p_accru_acct => p_item_rec.accru_acct
2359 , p_ipv_acct => p_item_rec.ipv_acct
2360 , p_budget_acct => p_item_rec.budget_acct
2361 , p_charge_acct => p_item_rec.charge_acct
2362 , p_purch_flag => p_item_rec.purch_flag
2363 , p_order_flag => p_item_rec.order_flag
2364 , p_transact_flag => p_item_rec.transact_flag
2365 , p_unit_price => p_item_rec.unit_price
2366 , p_wip_id => p_wip_batch_id
2367 , p_user_id => p_user_id
2368 , p_sysd => p_sysdate
2369 , p_organization_id => p_org_id
2370 , p_approval => p_approval
2371 , p_build_in_wip => p_item_rec.build_in_wip
2372 , p_pick_components => p_item_rec.pick_components
2373 , p_src_type => p_item_rec.src_type
2374 , p_encum_flag => p_encum_flag
2375 , p_customer_id => p_cust_id
2376 , p_cal_code => p_cal_code
2377 , p_except_id => p_exception_set_id
2378 , p_employee_id => p_employee_id
2379 , p_description => p_item_rec.description
2380 , p_src_org => TO_NUMBER(p_item_rec.src_org)
2381 , p_src_subinv => p_item_rec.src_subinv
2382 , p_subinv => p_subinv
2383 , p_location_id => p_dd_loc_id
2384 , p_po_org_id => p_po_org_id
2385 , p_pur_revision => p_pur_revision
2386 , x_ret_stat => l_ret_stat
2387 , x_ret_mesg => l_error_message
2388 );
2389
2390 IF l_ret_stat <> FND_API.G_RET_STS_SUCCESS
2391 THEN
2392 print_debug('do_restock returned message: ' || l_error_message
2393 , 'get_reord_stat'
2394 , 9);
2395 RETURN(l_error_message);
2396 END IF;
2397
2398 reorder_qty := reorder_qty - move_ord_qty;
2399 END LOOP;
2400
2401 RETURN(''); /*bug2838809*/
2402
2403 EXCEPTION
2404 WHEN others THEN
2405 print_debug(sqlcode || ', ' || sqlerrm, 'get_reord_stat', 1);
2406 RAISE;
2407 end get_reord_stat;
2408
2409
2410
2411 PROCEDURE do_restock( p_item_id IN NUMBER
2412 , p_mbf IN NUMBER
2413 , p_handle_repetitive_item IN NUMBER
2414 , p_repetitive_planned_item IN VARCHAR2
2415 , p_qty IN NUMBER
2416 , p_fixed_lead_time IN NUMBER
2417 , p_variable_lead_time IN NUMBER
2418 , p_buying_lead_time IN NUMBER
2419 , p_uom IN VARCHAR2
2420 , p_accru_acct IN NUMBER
2421 , p_ipv_acct IN NUMBER
2422 , p_budget_acct IN NUMBER
2423 , p_charge_acct IN NUMBER
2424 , p_purch_flag IN VARCHAR2
2425 , p_order_flag IN VARCHAR2
2426 , p_transact_flag IN VARCHAR2
2427 , p_unit_price IN NUMBER
2428 , p_wip_id IN NUMBER
2429 , p_user_id IN NUMBER
2430 , p_sysd IN DATE
2431 , p_organization_id IN NUMBER
2432 , p_approval IN NUMBER
2433 , p_build_in_wip IN VARCHAR2
2434 , p_pick_components IN VARCHAR2
2435 , p_src_type IN NUMBER
2436 , p_encum_flag IN VARCHAR2
2437 , p_customer_id IN NUMBER
2438 , p_cal_code IN VARCHAR2
2439 , p_except_id IN NUMBER
2440 , p_employee_id IN NUMBER
2441 , p_description IN VARCHAR2
2442 , p_src_org IN NUMBER
2443 , p_src_subinv IN VARCHAR2
2444 , p_subinv IN VARCHAR2
2445 , p_location_id IN NUMBER
2446 , p_po_org_id IN NUMBER
2447 , p_pur_revision IN NUMBER
2448 , x_ret_stat OUT NOCOPY VARCHAR2
2449 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
2450
2451 l_need_by_date DATE;
2452 l_ret_value VARCHAR2(200);
2453 move_ord_exc EXCEPTION;
2454 l_ret_stat VARCHAR2(1);
2455
2456 BEGIN
2457 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
2458 x_ret_mesg := '';
2459
2460 --
2461 -- Query debug settings, set global variables.
2462 -- This is done since MRP will call do_restock directly
2463 -- from Reorder Point report (MRPRPROP, bug 2442596).
2464 --
2465 IF G_USER_NAME IS NULL THEN
2466 init_debug(p_user_id);
2467 END IF;
2468
2469 IF G_TRACE_ON THEN
2470 print_debug('p_item_id ' || to_char(p_item_id) ||
2471 ', p_mbf: ' || to_char(p_mbf) ||
2472 ', p_handle_repetitive_item: ' || to_char(p_handle_repetitive_item) ||
2473 ', p_repetitive_planned_item: ' || p_repetitive_planned_item ||
2474 ', p_qty: ' || to_char(p_qty) ||
2475 ', p_fixed_lead_time: ' || to_char(p_fixed_lead_time) ||
2476 ', p_variable_lead_time: ' || to_char(p_variable_lead_time) ||
2477 ', p_buying_lead_time: ' || to_char(p_buying_lead_time) ||
2478 ', p_uom: ' || p_uom ||
2479 ', p_accru_acct: ' || to_char(p_accru_acct) ||
2480 ', p_ipv_acct: ' || to_char(p_ipv_acct) ||
2481 ', p_budget_acct: ' || to_char(p_budget_acct)
2482 , 'do_restock'
2483 , 9);
2484
2485 print_debug('p_charge_acct: ' || to_char(p_charge_acct) ||
2486 ', p_purch_flag: ' || p_purch_flag ||
2487 ', p_order_flag: ' || p_order_flag ||
2488 ', p_transact_flag: ' || p_transact_flag ||
2489 ', p_unit_price: ' || to_char(p_unit_price) ||
2490 ', p_wip_id: ' || to_char(p_wip_id) ||
2491 ', p_user_id: ' || to_char(p_user_id) ||
2492 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') ||
2493 ', p_organization_id: ' || to_char(p_organization_id) ||
2494 ', p_approval: ' || to_char(p_approval) ||
2495 ', p_build_in_wip: ' || p_build_in_wip ||
2496 ', p_pick_components: ' || p_pick_components ||
2497 ', p_src_type: ' || to_char(p_src_type)
2498 , 'do_restock'
2499 , 9);
2500
2501 print_debug('p_encum_flag: ' || p_encum_flag ||
2502 ', p_customer_id: ' || to_char(p_customer_id) ||
2503 ', p_cal_code: ' || p_cal_code ||
2504 ', p_except_id: ' || to_char(p_except_id) ||
2505 ', p_employee_id: ' || to_char(p_employee_id) ||
2506 ', p_description: ' || p_description ||
2507 ', p_src_org: ' || to_char(p_src_org) ||
2508 ', p_src_subinv: ' || p_src_subinv ||
2509 ', p_subinv: ' || p_subinv ||
2510 ', p_location_id: ' || to_char(p_location_id) ||
2511 ', p_po_org_id: ' || to_char(p_po_org_id) ||
2512 ', p_pur_revision: ' || to_char(p_pur_revision)
2513 , 'do_restock'
2514 , 9);
2515 END IF;
2516
2517 IF p_qty <= 0
2518 THEN
2519 RETURN;
2520 END IF;
2521
2522 IF (p_repetitive_planned_item = 'Y' AND p_handle_repetitive_item = 1) OR
2523 (p_repetitive_planned_item = 'N' AND p_mbf = 2)
2524 THEN
2525 --
2526 -- Lead time for buy items is sum of POSTPROCESSING_LEAD_TIME,
2527 -- PREPROCESSING_LEAD_TIME AND PROCESSING_LEAD_TIME (sub level)
2528 -- OR POSTPROCESSING_LEAD_TIME, PREPROCESSING_LEAD_TIME
2529 -- AND FULL_LEAD_TIME (item level)
2530 --
2531 -- Here, total lead time is the total buying Lead time
2532 --
2533
2534 SELECT c1.calendar_date
2535 INTO l_need_by_date
2536 FROM bom_calendar_dates c1,
2537 bom_calendar_dates c
2538 WHERE c1.calendar_code = c.calendar_code
2539 AND c1.exception_set_id = c.exception_set_id
2540 AND c1.seq_num = (c.next_seq_num + trunc(p_buying_lead_time))
2541 AND c.calendar_code = p_cal_code
2542 AND c.exception_set_id = p_except_id
2543 AND c.calendar_date = trunc(sysdate);
2544
2545 print_debug('Need by date: ' || to_char(l_need_by_date,'DD-MON-YYYY HH24:MI:SS')
2546 , 'do_restock'
2547 , 9);
2548
2549 IF p_src_type = 3
2550 THEN
2551 IF p_transact_flag = 'Y'
2552 THEN
2553 print_debug('Calling INV_Create_Move_Order_PVT.Create_Move_Orders'
2554 , 'do_restock'
2555 , 9);
2556 BEGIN
2557 l_ret_value :=
2558 INV_Create_Move_Order_PVT.Create_Move_Orders
2559 ( p_item_id => p_item_id
2560 , p_quantity => p_qty
2561 , p_need_by_date => l_need_by_date
2562 , p_primary_uom_code => p_uom
2563 , p_user_id => p_user_id
2564 , p_organization_id => p_organization_id
2565 , p_src_type => p_src_type
2566 , p_src_subinv => p_src_subinv
2567 , p_subinv => p_subinv
2568 );
2569
2570 IF l_ret_value <> FND_API.G_RET_STS_SUCCESS
2571 THEN
2572 RAISE move_ord_exc;
2573 END IF;
2574
2575 EXCEPTION
2576 WHEN OTHERS THEN
2577 print_debug('Error creating move order: ' || sqlcode || ', ' || sqlerrm
2578 , 'do_restock'
2579 , 1);
2580 RAISE move_ord_exc;
2581 END;
2582 ELSE
2583 print_debug('Src type is sub, item not transactable.', 'do_restock', 9);
2584 RAISE move_ord_exc;
2585 END IF;
2586
2587 ELSE
2588
2589 re_po( p_item_id => p_item_id
2590 , p_qty => p_qty
2591 , p_nb_time => l_need_by_date
2592 , p_uom => p_uom
2593 , p_accru_acct => p_accru_acct
2594 , p_ipv_acct => p_ipv_acct
2595 , p_budget_acct => p_budget_acct
2596 , p_charge_acct => p_charge_acct
2597 , p_purch_flag => p_purch_flag
2598 , p_order_flag => p_order_flag
2599 , p_transact_flag => p_transact_flag
2600 , p_unit_price => p_unit_price
2601 , p_user_id => p_user_id
2602 , p_sysd => p_sysd
2603 , p_organization_id => p_organization_id
2604 , p_approval => p_approval
2605 , p_src_type => p_src_type
2606 , p_encum_flag => p_encum_flag
2607 , p_customer_id => p_customer_id
2608 , p_employee_id => p_employee_id
2609 , p_description => p_description
2610 , p_src_org => p_src_org
2611 , p_src_subinv => p_src_subinv
2612 , p_subinv => p_subinv
2613 , p_location_id => p_location_id
2614 , p_po_org_id => p_po_org_id
2615 , p_pur_revision => p_pur_revision
2616 , x_ret_stat => l_ret_stat
2617 , x_ret_mesg => x_ret_mesg);
2618
2619 x_ret_stat := l_ret_stat;
2620
2621 END IF;
2622
2623 ELSE
2624
2625 --
2626 -- Either a make item, or repetitive item and the user chose
2627 -- "Create Discrete Job"
2628 --
2629
2630 print_debug('Calling wip_calendar.estimate_leadtime to calculate need_by_date'
2631 , 'do_restock'
2632 , 9);
2633
2634 wip_calendar.estimate_leadtime(x_org_id => p_organization_id,
2635 x_fixed_lead => p_fixed_lead_time,
2636 x_var_lead => p_variable_lead_time,
2637 x_quantity => p_qty,
2638 x_proc_days => 0,
2639 x_entity_type => 1,
2640 x_fusd => p_sysd,
2641 x_fucd => NULL,
2642 x_lusd => NULL,
2643 x_lucd => NULL,
2644 x_sched_dir => 1,
2645 x_est_date => l_need_by_date);
2646
2647 re_wip( p_item_id => p_item_id
2648 , p_qty => p_qty
2649 , p_nb_time => l_need_by_date
2650 , p_uom => p_uom
2651 , p_wip_id => p_wip_id
2652 , p_user_id => p_user_id
2653 , p_sysd => p_sysd
2654 , p_organization_id => p_organization_id
2655 , p_approval => p_approval
2656 , p_build_in_wip => p_build_in_wip
2657 , p_pick_components => p_pick_components
2658 , x_ret_stat => l_ret_stat
2659 , x_ret_mesg => x_ret_mesg);
2660
2661 x_ret_stat := l_ret_stat;
2662
2663 END IF;
2664
2665 EXCEPTION
2666 WHEN move_ord_exc THEN
2667 SELECT meaning
2668 INTO x_ret_mesg
2669 FROM mfg_lookups
2670 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2671 AND lookup_code = 5;
2672
2673 x_ret_stat := FND_API.G_RET_STS_ERROR;
2674
2675 WHEN others THEN
2676 print_debug(sqlcode || ', ' || sqlerrm, 'do_restock', 1);
2677 RAISE;
2678
2679 END do_restock;
2680
2681
2682
2683 PROCEDURE re_po( p_item_id IN NUMBER
2684 , p_qty IN NUMBER
2685 , p_nb_time IN DATE
2686 , p_uom IN VARCHAR2
2687 , p_accru_acct IN NUMBER
2688 , p_ipv_acct IN NUMBER
2689 , p_budget_acct IN NUMBER
2690 , p_charge_acct IN NUMBER
2691 , p_purch_flag IN VARCHAR2
2692 , p_order_flag IN VARCHAR2
2693 , p_transact_flag IN VARCHAR2
2694 , p_unit_price IN NUMBER
2695 , p_user_id IN NUMBER
2696 , p_sysd IN DATE
2697 , p_organization_id IN NUMBER
2698 , p_approval IN NUMBER
2699 , p_src_type IN NUMBER
2700 , p_encum_flag IN VARCHAR2
2701 , p_customer_id IN NUMBER
2702 , p_employee_id IN NUMBER
2703 , p_description IN VARCHAR2
2704 , p_src_org IN NUMBER
2705 , p_src_subinv IN VARCHAR2
2706 , p_subinv IN VARCHAR2
2707 , p_location_id IN NUMBER
2708 , p_po_org_id IN NUMBER
2709 , p_pur_revision IN NUMBER
2710 , x_ret_stat OUT NOCOPY VARCHAR2
2711 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
2712
2713 l_item_rev_ctl NUMBER := 0;
2714 l_item_revision VARCHAR2(4) := '@@@';
2715 l_orgn_id NUMBER := p_organization_id;
2716
2717 po_exc EXCEPTION;
2718
2719 BEGIN
2720 IF G_TRACE_ON THEN
2721 print_debug('p_item_id: ' || to_char(p_item_id) ||
2722 ', p_qty: ' || to_char(p_qty) ||
2723 ', p_nb_time:' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
2724 ', p_uom: ' || p_uom ||
2725 ', p_accru_acct: ' || to_char(p_accru_acct) ||
2726 ', p_ipv_acct: ' || to_char(p_ipv_acct) ||
2727 ', p_budget_acct: ' || to_char(p_budget_acct) ||
2728 ', p_charge_acct: ' || to_char(p_charge_acct) ||
2729 ', p_purch_flag: ' || p_purch_flag ||
2730 ', p_order_flag: ' || p_order_flag ||
2731 ', p_transact_flag: ' || p_transact_flag ||
2732 ', p_unit_price: ' || to_char(p_unit_price) ||
2733 ', p_user_id: ' || to_char(p_user_id) ||
2734 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS')
2735 , 're_po'
2736 , 9);
2737
2738 print_debug('p_organization_id: ' || to_char(p_organization_id) ||
2739 ', p_approval: ' || to_char(p_approval) ||
2740 ', p_src_type: ' || to_char(p_src_type) ||
2741 ', p_encum_flag: ' || p_encum_flag ||
2742 ', p_customer_id: ' || to_char(p_customer_id) ||
2743 ', p_employee_id: ' || to_char(p_employee_id) ||
2744 ', p_description: ' || p_description ||
2745 ', p_src_org: ' || to_char(p_src_org) ||
2746 ', p_src_subinv: ' || p_src_subinv ||
2747 ', p_subinv: ' || p_subinv ||
2748 ', p_location_id: ' || to_char(p_location_id) ||
2749 ', p_po_org_id: ' || to_char(p_po_org_id) ||
2750 ', p_pur_revision: ' || to_char(p_pur_revision)
2751 , 're_po'
2752 , 9);
2753 END IF;
2754
2755 --
2756 -- Do not create a requisition if any of the following apply:
2757 -- 1. Source type (Inventory/Supplier/Subinventory) is not specified
2758 -- 2. Item is not transactable
2759 -- 3. Source type is Inventory (1) but "Internal Orders Enabled"
2760 -- is not checked
2761 -- 4. Source type is Supplier (2) but "Purchasable" flag unchecked
2762 --
2763 IF (p_src_type IS NULL)
2764 OR
2765 (p_transact_flag <> 'Y')
2766 OR
2767 (p_src_type = 1 AND p_order_flag <> 'Y')
2768 OR
2769 (p_src_type = 2 AND p_purch_flag <> 'Y')
2770 THEN
2771 print_debug('Null src type or invalid transact_flag, order_flag or purch_flag'
2772 , 're_po', 9);
2773 RAISE po_exc;
2774 END IF;
2775
2776 IF (p_charge_acct IS NULL)
2777 OR (p_accru_acct IS NULL)
2778 OR (p_ipv_acct IS NULL)
2779 OR ((p_encum_flag <> 'N') AND (p_budget_acct is NULL))
2780 THEN
2781 print_debug('Charge/accrual/IPV/budget accts not setup correctly.', 're_po', 9);
2782 RAISE po_exc;
2783 END IF;
2784
2785 IF NVL(p_customer_id,0) < 0
2786 THEN
2787 print_debug('Invalid customer ID: ' || to_char(p_customer_id), 're_po', 9);
2788 RAISE po_exc;
2789 END IF;
2790
2791
2792 /* Fix for bug 774532. To get the item revisions, IF profile is Yes
2793 OR IF profile is NULL AND item is revision controlled */
2794
2795 --
2796 -- Bug 2323099:
2797 -- We should only specify a revision if the item is revision-controlled
2798 -- and the profile "INV:Purchasing By Revision" is set to yes
2799 --
2800 -- p_pur_revision will never be NULL - this is handled in the
2801 -- BEFORE-REPORT trigger of INVISMMX.
2802 --
2803
2804 IF p_pur_revision = 1
2805 THEN
2806 SELECT revision_qty_control_code
2807 INTO l_item_rev_ctl
2808 FROM mtl_system_items msi
2809 WHERE msi.organization_id = l_orgn_id
2810 AND msi.inventory_item_id = p_item_id;
2811
2812 print_debug('Rev ctl: ' || to_char(l_item_rev_ctl), 're_po', 9);
2813
2814 IF l_item_rev_ctl = 2
2815 THEN
2816 SELECT MAX(revision)
2817 INTO l_item_revision
2818 FROM mtl_item_revisions mir
2819 WHERE inventory_item_id = p_item_id
2820 AND organization_id = l_orgn_id
2821 AND effectivity_date < SYSDATE
2822 AND effectivity_date =
2823 (
2824 SELECT MAX(effectivity_date)
2825 FROM mtl_item_revisions mir1
2826 WHERE mir1.inventory_item_id = mir.inventory_item_id
2827 AND mir1.organization_id = mir.organization_id
2828 AND effectivity_date < SYSDATE
2829 );
2830 END IF;
2831
2832 print_debug('Item rev: ' || l_item_revision, 're_po', 9);
2833 END IF ;
2834
2835
2836 print_debug('Inserting into PO_REQUISITIONS_INTERFACE_ALL', 're_po', 9);
2837
2838 INSERT INTO po_requisitions_interface_all(
2839 LAST_UPDATE_DATE,
2840 LAST_UPDATED_BY,
2841 ITEM_DESCRIPTION,
2842 CREATION_DATE,
2843 CREATED_BY,
2844 PREPARER_ID,
2845 INTERFACE_SOURCE_CODE,
2846 REQUISITION_TYPE,
2847 AUTHORIZATION_STATUS,
2848 SOURCE_TYPE_CODE,
2849 SOURCE_ORGANIZATION_ID,
2850 SOURCE_SUBINVENTORY,
2851 DESTINATION_ORGANIZATION_ID,
2852 DESTINATION_SUBINVENTORY,
2853 DELIVER_TO_REQUESTOR_ID,
2854 DESTINATION_TYPE_CODE,
2855 UOM_CODE,
2856 DELIVER_TO_LOCATION_ID,
2857 ITEM_ID,
2858 ITEM_REVISION,
2859 QUANTITY,
2860 NEED_BY_DATE,
2861 GL_DATE,
2862 CHARGE_ACCOUNT_ID,
2863 ACCRUAL_ACCOUNT_ID,
2864 VARIANCE_ACCOUNT_ID,
2865 BUDGET_ACCOUNT_ID,
2866 AUTOSOURCE_FLAG,
2867 ORG_ID)
2868 VALUES (
2869 sysdate,
2870 p_user_id,
2871 p_description,
2872 sysdate,
2873 p_user_id,
2874 p_employee_id,
2875 'INV',
2876 DECODE(p_src_type, 1, 'INTERNAL', 'PURCHASE'),
2877 DECODE(p_approval, 1, 'INCOMPLETE', 2,'APPROVED'),
2878 DECODE(p_src_type, 1, 'INVENTORY', 'VENDOR'),
2879 p_src_org,
2880 p_src_subinv,
2881 p_organization_id,
2882 p_subinv,
2883 p_employee_id,
2884 'INVENTORY',
2885 p_uom,
2886 p_location_id,
2887 p_item_id,
2888 DECODE(l_item_revision,'@@@',NULL,l_item_revision),
2889 p_qty,
2890 trunc(p_nb_time),
2891 SYSDATE,
2892 p_charge_acct,
2893 p_accru_acct,
2894 p_ipv_acct,
2895 p_budget_acct,
2896 'P',
2897 p_po_org_id);
2898
2899 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
2900 x_ret_mesg := '';
2901
2902 EXCEPTION
2903 WHEN OTHERS THEN
2904 print_debug(sqlcode || ', ' || sqlerrm, 're_po', 1);
2905
2906 SELECT meaning
2907 INTO x_ret_mesg
2908 FROM mfg_lookups
2909 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2910 AND lookup_code = 1;
2911
2912 x_ret_stat := FND_API.G_RET_STS_ERROR;
2913 END re_po;
2914
2915
2916
2917 PROCEDURE re_wip( p_item_id IN NUMBER
2918 , p_qty IN NUMBER
2919 , p_nb_time IN DATE
2920 , p_uom IN VARCHAR2
2921 , p_wip_id IN NUMBER
2922 , p_user_id IN NUMBER
2923 , p_sysd IN DATE
2924 , p_organization_id IN NUMBER
2925 , p_approval IN NUMBER
2926 , p_build_in_wip IN VARCHAR2
2927 , p_pick_components IN VARCHAR2
2928 , x_ret_stat OUT NOCOPY VARCHAR2
2929 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
2930
2931 wip_exc EXCEPTION;
2932
2933 BEGIN
2934 IF G_TRACE_ON THEN
2935 print_debug('p_item_id: ' || to_char(p_item_id) ||
2936 ', p_qty: ' || to_char(p_qty) ||
2937 ', p_nb_time: ' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
2938 ', p_uom: ' || p_uom ||
2939 ', p_wip_id: ' || to_char(p_wip_id) ||
2940 ', p_user_id: ' || to_char(p_user_id) ||
2941 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') ||
2942 ', p_organization_id: ' || to_char(p_organization_id) ||
2943 ', p_approval: ' || to_char(p_approval) ||
2944 ', p_build_in_wip: ' || p_build_in_wip ||
2945 ', p_pick_components: ' || p_pick_components
2946 , 're_wip'
2947 , 9);
2948 END IF;
2949
2950 IF p_build_in_wip <> 'Y' OR p_pick_components <> 'N'
2951 THEN
2952 print_debug('Item either not build_in_wip or has pick components flag checked'
2953 , 're_wip', 9);
2954 RAISE wip_exc;
2955 ELSE
2956 print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
2957 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
2958 LAST_UPDATE_DATE,
2959 LAST_UPDATED_BY,
2960 CREATION_DATE,
2961 CREATED_BY,
2962 GROUP_ID,
2963 PROCESS_PHASE,
2964 PROCESS_STATUS,
2965 ORGANIZATION_ID,
2966 LOAD_TYPE,
2967 LAST_UNIT_COMPLETION_DATE,
2968 PRIMARY_ITEM_ID,
2969 START_QUANTITY,STATUS_TYPE)
2970 VALUES(
2971 p_sysd,
2972 p_user_id,
2973 p_sysd,
2974 p_user_id,
2975 p_wip_id,
2976 2,
2977 1,
2978 p_organization_id,
2979 1,
2980 p_nb_time,
2981 p_item_id,
2982 p_qty,
2983 DECODE(p_approval,1,1,2,3));
2984 END IF;
2985
2986 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
2987 x_ret_mesg := '';
2988
2989 EXCEPTION
2990 WHEN OTHERS THEN
2991 print_debug(sqlcode || ', ' || sqlerrm, 're_wip', 1);
2992
2993 SELECT meaning
2994 INTO x_ret_mesg
2995 FROM mfg_lookups
2996 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2997 AND lookup_code = 2;
2998
2999 x_ret_stat := FND_API.G_RET_STS_ERROR;
3000 END re_wip;
3001 --
3002 END CSP_Minmax_PVT;