[Home] [Help]
PACKAGE BODY: APPS.INV_MINMAX_PVT
Source
1 PACKAGE BODY INV_Minmax_PVT AS
2 /* $Header: INVVMMXB.pls 120.17.12020000.3 2013/01/04 20:38:30 cbyrappa ship $ */
3
4 --
5 -- Replenishment Move Order Consolidation
6 -- Initialize the Global variables to the values set in INV_MMX_WRAPPER_PVT.
7 --
8 G_USER_NAME fnd_user.user_name%TYPE := FND_GLOBAL.USER_NAME;
9 G_TRACE_ON NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),2);
10
11
12
13 TYPE minmax_items_rectype IS RECORD
14 ( item VARCHAR2(1000)
15 , description mtl_system_items.description%TYPE
16 , fix_mult mtl_system_items.fixed_lot_multiplier%TYPE
17 , min_qty mtl_system_items.min_minmax_quantity%TYPE
18 , max_qty mtl_system_items.max_minmax_quantity%TYPE
19 , min_ord_qty mtl_system_items.minimum_order_quantity%TYPE
20 , max_ord_qty mtl_system_items.maximum_order_quantity%TYPE
21 , fixed_lead_time mtl_system_items.fixed_lead_time%TYPE
22 , variable_lead_time mtl_system_items.variable_lead_time%TYPE
23 , postprocessing_lead_time mtl_system_items.postprocessing_lead_time%TYPE
24 , buying_lead_time mtl_system_items.full_lead_time%TYPE
25 , planner mtl_system_items.planner_code%TYPE
26 , buyer per_all_people_f.full_name%TYPE
27 , category VARCHAR2(800)
28 , category_id mtl_categories.category_id%TYPE
29 , item_id mtl_system_items.inventory_item_id%TYPE
30 , lot_ctl mtl_system_items.lot_control_code%TYPE
31 , repetitive_planned_item mtl_system_items.repetitive_planning_flag%TYPE
32 , primary_uom mtl_system_items.primary_uom_code%TYPE
33 , accru_acct mtl_parameters.ap_accrual_account%TYPE
34 , ipv_acct mtl_parameters.invoice_price_var_account%TYPE
35 , budget_acct mtl_system_items.encumbrance_account%TYPE
36 , charge_acct mtl_system_items.expense_account%TYPE
37 , src_type mtl_system_items.source_type%TYPE
38 , src_org mtl_system_items.source_organization_id%TYPE
39 , src_subinv mtl_system_items.source_subinventory%TYPE
40 , purch_flag mtl_system_items.purchasing_enabled_flag%TYPE
41 , order_flag mtl_system_items.internal_order_enabled_flag%TYPE
42 , transact_flag mtl_system_items.mtl_transactions_enabled_flag%TYPE
43 , unit_price mtl_system_items.list_price_per_unit%TYPE
44 , mbf mtl_system_items.planning_make_buy_code%TYPE
45 , build_in_wip mtl_system_items.build_in_wip_flag%TYPE
46 /* nsinghi MIN-MAX INVCONV start */
47 , process_enabled mtl_parameters.process_enabled_flag%TYPE
48 , recipe_enabled mtl_system_items.recipe_enabled_flag%TYPE
49 , execution_enabled mtl_system_items.process_execution_enabled_flag%TYPE
50 /* nsinghi MIN-MAX INVCONV end */
51 , pick_components mtl_system_items.pick_components_flag%TYPE
52 );
53
54 --
55 -- Start of forward declarations
56 --
57
58 FUNCTION get_catg_disp( p_category_id NUMBER
59 , p_struct_id NUMBER) RETURN VARCHAR2;
60
61 /*
62 FUNCTION get_onhand_qty( p_include_nonnet NUMBER
63 , p_level NUMBER
64 , p_org_id NUMBER
65 , p_subinv VARCHAR2
66 , p_item_id NUMBER
67 , p_sysdate DATE) RETURN NUMBER;
68 */
69
70 FUNCTION get_supply_qty( p_org_id NUMBER
71 , p_subinv VARCHAR2
72 , p_item_id NUMBER
73 , p_postproc_lead_time NUMBER
74 , p_cal_code VARCHAR2
75 , p_except_id NUMBER
76 , p_level NUMBER
77 , p_s_cutoff DATE
78 , p_include_po NUMBER
79 , p_include_mo NUMBER
80 , p_vmi_enabled VARCHAR2
81 , p_include_nonnet NUMBER
82 , p_include_wip NUMBER
83 , p_include_if NUMBER
84 /* nsinghi MIN-MAX INVCONV start */
85 , p_process_org VARCHAR2
86 /* nsinghi MIN-MAX INVCONV end */
87 ) RETURN NUMBER;
88
89 FUNCTION get_demand_qty( p_org_id NUMBER
90 , p_subinv VARCHAR2
91 , p_level NUMBER
92 , p_item_id NUMBER
93 , p_d_cutoff DATE
94 , p_include_nonnet NUMBER
95 , p_net_rsv NUMBER
96 , p_net_unrsv NUMBER
97 , p_net_wip NUMBER
98 /* nsinghi MIN-MAX INVCONV start */
99 , p_process_org VARCHAR2
100 /* nsinghi MIN-MAX INVCONV end */
101 ) RETURN NUMBER;
102
103 FUNCTION get_reord_qty( p_min_qty NUMBER
104 , p_max_qty NUMBER
105 , p_min_ord_qty NUMBER
106 , p_max_ord_qty NUMBER
107 , p_tot_avail_qty NUMBER
108 , p_fix_mult NUMBER) RETURN NUMBER;
109
110 FUNCTION get_reord_stat( p_restock NUMBER
111 , p_handle_rep_item NUMBER
112 , p_level NUMBER
113 , p_reord_qty NUMBER
114 , p_wip_batch_id NUMBER
115 , p_org_id NUMBER
116 , p_subinv VARCHAR2
117 , p_user_id NUMBER
118 , p_employee_id NUMBER
119 , p_sysdate DATE
120 , p_approval NUMBER
121 , p_encum_flag VARCHAR2
122 , p_cust_id NUMBER
123 , p_cust_site_id NUMBER
124 , p_cal_code VARCHAR2
125 , p_exception_set_id NUMBER
126 , p_dd_loc_id NUMBER
127 , p_po_org_id NUMBER
128 , p_pur_revision NUMBER
129 , p_item_rec minmax_items_rectype
130 , p_osfm_batch_id NUMBER DEFAULT NULL /* Added for Bug 6807835 */
131 ) RETURN VARCHAR2;
132
133 PROCEDURE re_po( p_item_id IN NUMBER
134 , p_qty IN NUMBER
135 , p_nb_time IN DATE
136 , p_uom IN VARCHAR2
137 , p_accru_acct IN NUMBER
138 , p_ipv_acct IN NUMBER
139 , p_budget_acct IN NUMBER
140 , p_charge_acct IN NUMBER
141 , p_purch_flag IN VARCHAR2
142 , p_order_flag IN VARCHAR2
143 , p_transact_flag IN VARCHAR2
144 , p_unit_price IN NUMBER
145 , p_user_id IN NUMBER
146 , p_sysd IN DATE
147 , p_organization_id IN NUMBER
148 , p_approval IN NUMBER
149 , p_src_type IN NUMBER
150 , p_encum_flag IN VARCHAR2
151 , p_customer_id IN NUMBER
152 , p_employee_id IN NUMBER
153 , p_description IN VARCHAR2
154 , p_src_org IN NUMBER
155 , p_src_subinv IN VARCHAR2
156 , p_subinv IN VARCHAR2
157 , p_location_id IN NUMBER
158 , p_po_org_id IN NUMBER
159 , p_pur_revision IN NUMBER
160 , x_ret_stat OUT NOCOPY VARCHAR2
161 , x_ret_mesg OUT NOCOPY VARCHAR2);
162
163 PROCEDURE re_wip( p_item_id IN NUMBER
164 , p_qty IN NUMBER
165 , p_nb_time IN DATE
166 , p_uom IN VARCHAR2
167 , p_wip_id IN NUMBER
168 , p_user_id IN NUMBER
169 , p_sysd IN DATE
170 , p_organization_id IN NUMBER
171 , p_approval IN NUMBER
172 , p_build_in_wip IN VARCHAR2
173 , p_pick_components IN VARCHAR2
174 , x_ret_stat OUT NOCOPY VARCHAR2
175 , x_ret_mesg OUT NOCOPY VARCHAR2
176 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
177 );
178 /* Bug 6240025. Added the Procedure*/
179 FUNCTION get_loaded_qty(p_org_id NUMBER
180 , p_subinv VARCHAR2
181 , p_level NUMBER
182 , p_item_id NUMBER
183 , p_net_rsv NUMBER
184 , p_net_unrsv NUMBER) RETURN NUMBER;
185 /* nsinghi MIN-MAX INVCONV start */
186
187 PROCEDURE re_batch( p_item_id IN NUMBER
188 , p_qty IN NUMBER
189 , p_nb_time IN DATE
190 , p_uom IN VARCHAR2
191 , p_organization_id IN NUMBER
192 , p_execution_enabled IN VARCHAR2
193 , p_recipe_enabled IN VARCHAR2
194 , p_user_id IN NUMBER
195 , x_ret_stat OUT NOCOPY VARCHAR2
196 , x_ret_mesg OUT NOCOPY VARCHAR2);
197
198 /* nsinghi MIN-MAX INVCONV end */
199
200 --
201 -- End of forward declarations
202 --
203
204
205 PROCEDURE print_debug
206 ( p_message IN VARCHAR2
207 , p_module IN VARCHAR2
208 , p_level IN NUMBER
209 ) IS
210 BEGIN
211 inv_log_util.trace( G_USER_NAME || ': ' || p_message
212 , G_PKG_NAME || '.' || p_module
213 , p_level
214 );
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 -- dbms_output.put_line(sqlcode || ', ' || l_err_msg);
219 NULL;
220 END print_debug;
221
222
223
224 PROCEDURE run_min_max_plan ( p_item_select IN VARCHAR2
225 , p_handle_rep_item IN NUMBER
226 , p_pur_revision IN NUMBER
227 , p_cat_select IN VARCHAR2
228 , p_cat_set_id IN NUMBER
229 , p_mcat_struct IN NUMBER
230 , p_level IN NUMBER
231 , p_restock IN NUMBER
232 , p_include_nonnet IN NUMBER
233 , p_include_po IN NUMBER
234 , p_include_mo IN NUMBER DEFAULT 1
235 , p_include_wip IN NUMBER
236 , p_include_if IN NUMBER
237 , p_net_rsv IN NUMBER
238 , p_net_unrsv IN NUMBER
239 , p_net_wip IN NUMBER
240 , p_org_id IN NUMBER
241 , p_user_id IN NUMBER
242 , p_employee_id IN NUMBER
243 , p_subinv IN VARCHAR2
244 , p_dd_loc_id IN NUMBER
245 , p_wip_batch_id IN NUMBER
246 , p_approval IN NUMBER
247 , p_buyer_hi IN VARCHAR2
248 , p_buyer_lo IN VARCHAR2
249 , p_range_buyer IN VARCHAR2
250 , p_cust_id IN NUMBER
251 , p_cust_site_id IN NUMBER
252 , p_po_org_id IN NUMBER
253 , p_range_sql IN VARCHAR2
254 , p_sort IN VARCHAR2
255 , p_selection IN NUMBER
256 , p_sysdate IN DATE
257 , p_s_cutoff IN DATE
258 , p_d_cutoff IN DATE
259 , p_order_by IN VARCHAR2
260 , p_encum_flag IN VARCHAR2
261 , p_cal_code IN VARCHAR2
262 , p_exception_set_id IN NUMBER
263 , p_gen_report IN VARCHAR2
264 , x_return_status OUT NOCOPY VARCHAR2
265 , x_msg_data OUT NOCOPY VARCHAR2
266 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
267 ) IS
268
269 TYPE c_items_curtype IS REF CURSOR;
270 c_items_to_plan c_items_curtype;
271
272 item_rec minmax_items_rectype;
273 /*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
274 /* bug no 6009682 added parallel hints */
275 sql_stmt1 VARCHAR2(8000) :=
276 ' SELECT /*+ parallel(b) parallel(a) */
277 c.concatenated_segments item,
278 c.description description,
279 c.fixed_lot_multiplier fix_mult,
280 c.min_minmax_quantity min_qty,
281 c.max_minmax_quantity max_qty,
282 c.minimum_order_quantity min_ord_qty,
283 c.maximum_order_quantity max_ord_qty,
284 c.fixed_lead_time,
285 c.variable_lead_time,
286 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
287 NVL(c.preprocessing_lead_time, 0) +
288 NVL(c.full_lead_time, 0) buying_lead_time,
289 c.planner_code planner,
290 NULL buyer,
291 ' || p_cat_select || ' category,
292 b.category_id category_id,
293 c.inventory_item_id item_id,
294 c.lot_control_code lot_ctl,
295 c.repetitive_planning_flag repetitive_planned_item,
296 c.primary_uom_code primary_uom,
297 p.ap_accrual_account accru_acct,
298 p.invoice_price_var_account ipv_acct,
299 NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
300 DECODE(c.inventory_asset_flag,
301 ''Y'', p.material_account,
302 NVL(c.expense_account, p.expense_account)) charge_acct,
303 NVL(c.source_type, p.source_type) src_type,
304 DECODE(c.source_type,
305 NULL, DECODE(p.source_type, NULL, NULL, p.source_organization_id),
306 c.source_organization_id) src_org,
307 DECODE(c.source_type,
308 NULL, DECODE(p.source_type, NULL, NULL, p.source_subinventory),
309 c.source_subinventory) src_subinv,
310 c.purchasing_enabled_flag purch_flag,
311 c.internal_order_enabled_flag order_flag,
312 c.mtl_transactions_enabled_flag transact_flag,
313 c.list_price_per_unit unit_price,
314 c.planning_make_buy_code mbf,
315 build_in_wip_flag build_in_wip,
316
317 /* nsinghi MIN-MAX INVCONV start */
318 NVL(p.process_enabled_flag,''N'') process_enabled,
319 NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
320 NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
321 /* nsinghi MIN-MAX INVCONV end */
322 pick_components_flag pick_components
323 FROM mtl_categories b,
324 mtl_item_categories a,
325 mtl_system_items_vl c,
326 mtl_parameters p
327 WHERE b.category_id = a.category_id
328 AND b.structure_id = :mcat_struct_id
329 AND c.inventory_item_flag = ''Y''
330 AND p.organization_id = :org_id
331 AND a.organization_id = c.organization_id
332 AND a.organization_id = :org_id /* bug no 6009682 */
333 AND c.inventory_planning_code = 2
334 AND a.category_set_id = :cat_set_id
335 AND a.inventory_item_id = c.inventory_item_id
336 AND ( ' || p_range_sql || ' ) ';
337 /*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
338 sql_stmt2 VARCHAR2(8000) :=
339 ' SELECT c.concatenated_segments item,
340 c.description description,
341 s.fixed_lot_multiple fix_mult,
342 s.min_minmax_quantity min_qty,
343 s.max_minmax_quantity max_qty,
344 s.minimum_order_quantity min_ord_qty,
345 s.maximum_order_quantity max_ord_qty,
346 c.fixed_lead_time,
347 c.variable_lead_time,
348 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
349 NVL(s.preprocessing_lead_time,
350 NVL(m.preprocessing_lead_time,
351 NVL(c.preprocessing_lead_time, 0))) +
352 NVL(s.processing_lead_time,
353 NVL(m.processing_lead_time,
354 NVL(c.full_lead_time, 0))) buying_lead_time,
355 c.planner_code planner,
356 NULL,
357 ' || p_cat_select || ',
358 b.category_id category_id,
359 c.inventory_item_id item_id,
360 c.lot_control_code,
361 c.repetitive_planning_flag repetitive_planned_item,
362 c.primary_uom_code,
363 p.ap_accrual_account,
364 p.invoice_price_var_account,
365 NVL(s.encumbrance_account,
366 NVL(m.encumbrance_account,
367 NVL(c.encumbrance_account, p.encumbrance_account))),
368 DECODE(c.inventory_asset_flag,
369 ''Y'', m.material_account,
370 NVL(m.expense_account,
371 NVL(c.expense_account, p.expense_account))),
372 NVL(s.source_type,
373 NVL(m.source_type,
374 NVL(c.source_type, p.source_type))),
375 DECODE(s.source_type,
376 NULL, DECODE(m.source_type,
377 NULL, DECODE(c.source_type,
378 NULL, DECODE(p.source_type,
379 NULL, NULL,
380 p.source_organization_id),
381 c.source_organization_id),
382 m.source_organization_id),
383 s.source_organization_id),
384 DECODE(s.source_type,
385 NULL, DECODE(m.source_type,
386 NULL, DECODE(c.source_type,
387 NULL, DECODE(p.source_type,
388 NULL, NULL,
389 p.source_subinventory),
390 c.source_subinventory),
391 m.source_subinventory),
392 s.source_subinventory),
393 c.purchasing_enabled_flag,
394 c.internal_order_enabled_flag,
395 c.mtl_transactions_enabled_flag,
396 c.list_price_per_unit,
397 c.planning_make_buy_code,
398 build_in_wip_flag,
399 /* nsinghi MIN-MAX INVCONV start */
400 NVL(p.process_enabled_flag,''N'') process_enabled,
401 NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
402 NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
403 /* nsinghi MIN-MAX INVCONV end */
404 pick_components_flag
405 FROM mtl_categories b,
406 mtl_item_categories a,
407 mtl_system_items_vl c,
408 mtl_parameters p,
409 mtl_secondary_inventories m,
410 mtl_item_sub_inventories s
411 WHERE b.category_id = a.category_id
412 AND b.structure_id = :mcat_struct_id
413 AND c.inventory_item_flag = ''Y''
414 AND p.organization_id = :org_id
415 AND a.organization_id = c.organization_id
416 AND c.organization_id = :org_id
417 AND c.inventory_item_id = s.inventory_item_id
418 AND a.category_set_id = :cat_set_id
419 AND a.inventory_item_id = s.inventory_item_id
420 AND s.organization_id = :org_id
421 AND s.inventory_planning_code = 2
422 AND s.secondary_inventory = :sub
423 AND m.organization_id = :org_id
424 AND m.secondary_inventory_name = :sub
425 AND ( ' || p_range_sql || ' ) ';
426 /*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
427 sql_stmt3 VARCHAR2(8000) :=
428 ' SELECT c.concatenated_segments,
429 c.description,
430 c.fixed_lot_multiplier,
431 c.min_minmax_quantity,
432 c.max_minmax_quantity,
433 c.minimum_order_quantity,
434 c.maximum_order_quantity,
435 c.fixed_lead_time,
436 c.variable_lead_time,
437 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
438 NVL(c.preprocessing_lead_time, 0) +
439 NVL(c.full_lead_time, 0) buying_lead_time,
440 c.planner_code planner,
441 SUBSTR(v.full_name, 1, 10),
442 ' || p_cat_select || ',
443 b.category_id category_id,
444 c.inventory_item_id,
445 c.lot_control_code,
446 c.repetitive_planning_flag repetitive_planned_item,
447 c.primary_uom_code,
448 p.ap_accrual_account,
449 p.invoice_price_var_account,
450 NVL(c.encumbrance_account, p.encumbrance_account),
451 decode(c.inventory_asset_flag,
452 ''Y'', p.material_account,
453 NVL(c.expense_account, p.expense_account)),
454 NVL(c.source_type, p.source_type),
455 decode(c.source_type, NULL, decode(p.source_type, NULL, NULL,
456 p.source_organization_id), c.source_organization_id),
457 decode(c.source_type, NULL, decode(p.source_type, NULL, NULL,
458 p.source_subinventory), c.source_subinventory),
459 c.purchasing_enabled_flag,
460 c.internal_order_enabled_flag,
461 c.mtl_transactions_enabled_flag,
462 c.list_price_per_unit,
463 c.planning_make_buy_code,
464 build_in_wip_flag,
465 /* nsinghi MIN-MAX INVCONV start */
466 NVL(p.process_enabled_flag,''N'') process_enabled,
467 NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
468 NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
469 /* nsinghi MIN-MAX INVCONV end */
470 pick_components_flag
471 FROM mtl_categories b,
472 mtl_item_categories a,
473 mtl_system_items_vl c,
474 mtl_parameters p,
475 per_all_people_f v
476 WHERE b.category_id = a.category_id
477 AND b.structure_id = :mcat_struct_id
478 AND c.inventory_item_flag = ''Y''
479 AND p.organization_id = :org_id
480 AND a.organization_id = c.organization_id
481 AND c.organization_id = :org_id
482 AND c.inventory_planning_code = 2
483 AND a.category_set_id = :cat_set_id
484 AND a.inventory_item_id = c.inventory_item_id
485 AND v.person_id (+) = c.buyer_id
486 AND (
487 (:l_sysdate between v.effective_start_date and v.effective_end_date)
488 OR
489 (v.effective_start_date IS NULL AND v.effective_end_date IS NULL)
490 )
491 AND ( ' || p_range_sql || ' )
492 AND ( ' || p_range_buyer || ' ) ';
493
494 /*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
495 sql_stmt4 VARCHAR2(8000) :=
496 ' SELECT c.concatenated_segments,
497 c.description,
498 s.fixed_lot_multiple,
499 s.min_minmax_quantity,
500 s.max_minmax_quantity,
501 s.minimum_order_quantity,
502 s.maximum_order_quantity,
503 c.fixed_lead_time,
504 c.variable_lead_time,
505 NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
506 NVL(s.preprocessing_lead_time,
507 NVL(m.preprocessing_lead_time,
508 NVL(c.preprocessing_lead_time, 0))) +
509 NVL(s.processing_lead_time,
510 NVL(m.processing_lead_time,
511 NVL(c.full_lead_time, 0))) buying_lead_time,
512 c.planner_code planner,
513 SUBSTR(v.full_name, 1, 10),
514 ' || p_cat_select || ',
515 b.category_id category_id,
516 c.inventory_item_id,
517 c.lot_control_code,
518 c.repetitive_planning_flag repetitive_planned_item,
519 c.primary_uom_code,
520 p.ap_accrual_account,
521 p.invoice_price_var_account,
522 NVL(s.encumbrance_account,
523 NVL(m.encumbrance_account,
524 NVL(c.encumbrance_account, p.encumbrance_account))),
525 DECODE(c.inventory_asset_flag,
526 ''Y'', m.material_account,
527 NVL(m.expense_account,
528 NVL(c.expense_account, p.expense_account))),
529 NVL(s.source_type,
530 NVL(m.source_type,
531 NVL(c.source_type, p.source_type))),
532 DECODE(s.source_type,
533 NULL, DECODE(m.source_type,
534 NULL, DECODE(c.source_type,
535 NULL, DECODE(p.source_type,
536 NULL, NULL,
537 p.source_organization_id),
538 c.source_organization_id),
539 m.source_organization_id),
540 s.source_organization_id),
541 DECODE(s.source_type,
542 NULL, DECODE(m.source_type,
543 NULL, DECODE(c.source_type,
544 NULL, DECODE(p.source_type,
545 NULL, NULL,
546 p.source_subinventory),
547 c.source_subinventory),
548 m.source_subinventory),
549 s.source_subinventory),
550 c.purchasing_enabled_flag,
551 c.internal_order_enabled_flag,
552 c.mtl_transactions_enabled_flag,
553 c.list_price_per_unit,
554 c.planning_make_buy_code,
555 build_in_wip_flag,
556 /* nsinghi MIN-MAX INVCONV start */
557 NVL(p.process_enabled_flag,''N'') process_enabled,
558 NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
559 NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
560 /* nsinghi MIN-MAX INVCONV end */
561 pick_components_flag
562 FROM mtl_categories b,
563 mtl_item_categories a,
564 mtl_system_items_vl c,
565 mtl_parameters p,
566 mtl_secondary_inventories m,
567 mtl_item_sub_inventories s,
568 per_all_people_f v
569 WHERE b.category_id = a.category_id
570 AND b.structure_id = :mcat_struct_id
571 AND c.inventory_item_flag = ''Y''
572 AND p.organization_id = :org_id
573 AND a.organization_id = c.organization_id
574 AND c.organization_id = :org_id
575 AND c.inventory_item_id = s.inventory_item_id
576 AND a.category_set_id = :cat_set_id
577 AND a.inventory_item_id = s.inventory_item_id
578 AND s.organization_id = :org_id
579 AND s.inventory_planning_code = 2
580 AND s.secondary_inventory = :sub
581 AND m.organization_id = :org_id
582 AND m.secondary_inventory_name = :sub
583 AND v.person_id (+) = c.buyer_id
584 AND (
585 (:l_sysdate between v.effective_start_date and v.effective_end_date)
586 OR
587 (v.effective_start_date IS NULL AND v.effective_end_date IS NULL)
588 )
589 AND ( ' || p_range_sql || ' )
590 AND ( ' || p_range_buyer || ' ) ' ;
591
592
593 l_proc_name CONSTANT VARCHAR2(30) := 'RUN_MIN_MAX_PLAN';
594
595 -- Report columns
596 l_item_segments INV_MIN_MAX_TEMP.item_segments%TYPE;
597 l_catg_disp VARCHAR2(300);
598 l_sortee INV_MIN_MAX_TEMP.sortee%TYPE;
599 l_onhand_qty INV_MIN_MAX_TEMP.onhand_qty%TYPE;
600
601 l_stat INV_MIN_MAX_TEMP.error%TYPE;
602 l_supply_qty INV_MIN_MAX_TEMP.supply_qty%TYPE;
603 l_demand_qty INV_MIN_MAX_TEMP.demand_qty%TYPE;
604 l_tot_avail_qty INV_MIN_MAX_TEMP.tot_avail_qty%TYPE;
605 l_reord_qty INV_MIN_MAX_TEMP.reord_qty%TYPE;
606
607 l_err_msg VARCHAR2(2000);
608 l_vmi_enabled VARCHAR2(1) := 'N';
609 BEGIN
610
611 --
612 -- Query debug settings, set global variables
613 --
614 IF G_TRACE_ON = 1 THEN
615 print_debug
616 ('Starting Min-max planning with the following parameters: ' || fnd_global.local_chr(10) ||
617 ' p_item_select: ' || p_item_select || fnd_global.local_chr(10) ||
618 ', p_handle_rep_item: ' || to_char(p_handle_rep_item) || fnd_global.local_chr(10) ||
619 ', p_pur_revision: ' || to_char(p_pur_revision) || fnd_global.local_chr(10) ||
620 ', p_cat_select: ' || p_cat_select || fnd_global.local_chr(10) ||
621 ', p_cat_set_id: ' || to_char(p_cat_set_id) || fnd_global.local_chr(10) ||
622 ', p_mcat_struct: ' || to_char(p_mcat_struct) || fnd_global.local_chr(10) ||
623 ', p_level: ' || to_char(p_level) || fnd_global.local_chr(10) ||
624 ', p_restock: ' || to_char(p_restock) || fnd_global.local_chr(10) ||
625 ', p_include_nonnet: ' || to_char(p_include_nonnet) || fnd_global.local_chr(10) ||
626 ', p_include_po: ' || to_char(p_include_po) || fnd_global.local_chr(10) ||
627 ', p_include_mo: ' || to_char(p_include_mo) || fnd_global.local_chr(10) ||
628 ', p_include_wip: ' || to_char(p_include_wip) || fnd_global.local_chr(10) ||
629 ', p_include_if: ' || to_char(p_include_if) || fnd_global.local_chr(10)
630 , l_proc_name
631 , 5
632 );
633
634 print_debug
635 ('Parameters contd..: ' || fnd_global.local_chr(10) ||
636 ' p_net_rsv: ' || to_char(p_net_rsv) || fnd_global.local_chr(10) ||
637 ', p_net_unrsv: ' || to_char(p_net_unrsv) || fnd_global.local_chr(10) ||
638 ', p_net_wip: ' || to_char(p_net_wip) || fnd_global.local_chr(10) ||
639 ', p_org_id: ' || to_char(p_org_id) || fnd_global.local_chr(10) ||
640 ', p_user_id: ' || to_char(p_user_id) || fnd_global.local_chr(10) ||
641 ', p_employee_id: ' || to_char(p_employee_id) || fnd_global.local_chr(10) ||
642 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
643 ', p_dd_loc_id: ' || to_char(p_dd_loc_id) || fnd_global.local_chr(10) ||
644 ', p_approval: ' || to_char(p_approval) || fnd_global.local_chr(10) ||
645 ', p_wip_batch_id: ' || to_char(p_wip_batch_id) || fnd_global.local_chr(10) ||
646 ', p_buyer_hi: ' || p_buyer_hi || fnd_global.local_chr(10) ||
647 ', p_buyer_lo: ' || p_buyer_lo || fnd_global.local_chr(10) ||
648 ', p_range_buyer: ' || p_range_buyer || fnd_global.local_chr(10)
649 , l_proc_name
650 , 5
651 );
652
653 print_debug
654 ('Parameters contd..: ' || fnd_global.local_chr(10) ||
655 ' p_cust_id: ' || to_char(p_cust_id) || fnd_global.local_chr(10) ||
656 ', p_cust_site_id: ' || to_char(p_cust_site_id) || fnd_global.local_chr(10) ||
657 ', p_po_org_id: ' || to_char(p_po_org_id) || fnd_global.local_chr(10) ||
658 ', p_range_sql: ' || p_range_sql || fnd_global.local_chr(10) ||
659 ', p_sort: ' || p_sort || fnd_global.local_chr(10) ||
660 ', p_selection: ' || to_char(p_selection) || fnd_global.local_chr(10) ||
661 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS') ||
662 fnd_global.local_chr(10) ||
663 ', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
664 fnd_global.local_chr(10) ||
665 ', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
666 fnd_global.local_chr(10) ||
667 ', p_order_by: ' || p_order_by || fnd_global.local_chr(10) ||
668 ', p_encum_flag: ' || p_encum_flag || fnd_global.local_chr(10) ||
669 ', p_cal_code: ' || p_cal_code || fnd_global.local_chr(10) ||
670 ', p_exception_set_id: ' || to_char(p_exception_set_id)|| fnd_global.local_chr(10) ||
671 ', p_gen_report: ' || p_gen_report || fnd_global.local_chr(10) ||
672 ', p_osfm_batch_id: ' || p_osfm_batch_id || fnd_global.local_chr(10)
673 , l_proc_name
674 , 5
675 );
676 END IF;
677
678 --
679 -- Determine if we need to account for VMI
680 --
681 BEGIN
682 IF p_level = 1 THEN --only for org level; default is 'N'
683
684 SELECT NVL(fnd_profile.value('PO_VMI_ENABLED'),'N')
685 INTO l_vmi_enabled
686 FROM dual;
687 END IF;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 l_vmi_enabled := 'N';
692 END;
693
694 print_debug('Profile PO_VMI_ENABLED is: ' || l_vmi_enabled
695 , 'run_min_max_plan'
696 , 5);
697
698 --
699 -- Decide which SQL statement to execute based on
700 -- planning level, sort options and whether or not
701 -- buyers are specified
702 --
703 IF p_level = 1
704 THEN
705 IF (p_sort = '1' OR p_sort = '2' OR p_sort = '3')
706 AND
707 (p_buyer_hi IS NULL AND p_buyer_lo IS NULL)
708 THEN
709 OPEN c_items_to_plan FOR sql_stmt1 || p_order_by
710 USING
711 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id;
712 ELSE
713 OPEN c_items_to_plan FOR sql_stmt3 || p_order_by
714 USING
715 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id, p_sysdate;
716 END IF;
717 ELSE
718 --
719 -- Planning at subinventory level (p_level = 2)
720 --
721 IF (p_sort = '1' OR p_sort = '2' OR p_sort = '3')
722 AND
723 (p_buyer_hi IS NULL AND p_buyer_lo IS NULL)
724 THEN
725 OPEN c_items_to_plan FOR sql_stmt2 || p_order_by
726 USING
727 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id,
728 p_org_id, p_subinv, p_org_id, p_subinv;
729 ELSE
730 OPEN c_items_to_plan FOR sql_stmt4 || p_order_by
731 USING
732 p_mcat_struct, p_org_id, p_org_id, p_cat_set_id,
733 p_org_id, p_subinv, p_org_id, p_subinv, p_sysdate;
734 END IF;
735 END IF;
736
737 --
738 --
739 LOOP
740 FETCH c_items_to_plan INTO item_rec;
741 EXIT WHEN c_items_to_plan%NOTFOUND;
742
743 IF G_TRACE_ON = 1 THEN
744 print_debug(' Item #: ' || item_rec.item ||
745 ', Item ID: ' || to_char(item_rec.item_id)
746 , l_proc_name
747 , 7);
748 END IF;
749
750 l_onhand_qty := get_onhand_qty( p_include_nonnet => p_include_nonnet
751 , p_level => p_level
752 , p_org_id => p_org_id
753 , p_subinv => p_subinv
754 , p_item_id => item_rec.item_id
755 , p_sysdate => p_sysdate);
756
757 l_supply_qty := get_supply_qty( p_org_id => p_org_id
758 , p_subinv => p_subinv
759 , p_item_id => item_rec.item_id
760 , p_postproc_lead_time => item_rec.postprocessing_lead_time
761 , p_cal_code => p_cal_code
762 , p_except_id => p_exception_set_id
763 , p_level => p_level
764 , p_s_cutoff => p_s_cutoff
765 , p_include_po => p_include_po
766 , p_include_mo => p_include_mo
767 , p_vmi_enabled => l_vmi_enabled
768 , p_include_nonnet => p_include_nonnet
769 , p_include_wip => p_include_wip
770 , p_include_if => p_include_if
771 /* nsinghi MIN-MAX INVCONV start */
772 , p_process_org => item_rec.process_enabled
773 /* nsinghi MIN-MAX INVCONV end */
774 );
775
776 l_demand_qty := get_demand_qty( p_org_id => p_org_id
777 , p_subinv => p_subinv
778 , p_level => p_level
779 , p_item_id => item_rec.item_id
780 , p_d_cutoff => p_d_cutoff
781 , p_include_nonnet => p_include_nonnet
782 , p_net_rsv => p_net_rsv
783 , p_net_unrsv => p_net_unrsv
784 , p_net_wip => p_net_wip
785 /* nsinghi MIN-MAX INVCONV start */
786 , p_process_org => item_rec.process_enabled
787 /* nsinghi MIN-MAX INVCONV end */
788 );
789
790 l_tot_avail_qty := NVL(l_onhand_qty,0) + NVL(l_supply_qty,0) - NVL(l_demand_qty,0);
791
792 IF G_TRACE_ON = 1 THEN
793 print_debug(' Onhand: ' || to_char(l_onhand_qty) ||
794 ', Supply: ' || to_char(l_supply_qty) ||
795 ', Demand: ' || to_char(l_demand_qty) ||
796 ', Available: ' || to_char(l_tot_avail_qty)
797 , l_proc_name
798 , 7);
799 END IF;
800
801 --
802 -- Only need to display this item if:
803 -- 1. User chose "Items under min qty" and avail qty < min
804 -- 2. User chose "Items over max qty" and avail > max qty or
805 -- 3. User chose "All min-max planned items"
806 --
807 IF (p_selection = 1 AND l_tot_avail_qty < NVL(item_rec.min_qty, 0))
808 OR
809 (p_selection = 2 AND l_tot_avail_qty > NVL(item_rec.max_qty, 0))
810 OR
811 (p_selection = 3)
812 THEN
813 --
814 --
815 l_item_segments := SUBSTR(item_rec.item,1,800);
816
817 IF item_rec.category IS NOT NULL THEN
818 l_catg_disp := get_catg_disp(item_rec.category_id, p_mcat_struct);
819 ELSE
820 l_catg_disp := NULL;
821 END IF;
822
823 IF p_sort = '3'
824 THEN
825 l_sortee := substr(item_rec.planner,1,10);
826 ELSIF p_sort = '4'
827 THEN
828 l_sortee := substr(item_rec.buyer,1,10);
829 ELSE
830 l_sortee := l_catg_disp;
831 END IF;
832
833 l_reord_qty := get_reord_qty( p_min_qty => item_rec.min_qty
834 , p_max_qty => item_rec.max_qty
835 , p_min_ord_qty => item_rec.min_ord_qty
836 , p_max_ord_qty => item_rec.max_ord_qty
837 , p_tot_avail_qty => l_tot_avail_qty
838 , p_fix_mult => item_rec.fix_mult);
839
840 l_stat := get_reord_stat( p_restock => p_restock
841 , p_handle_rep_item => p_handle_rep_item
842 , p_level => p_level
843 , p_reord_qty => l_reord_qty
844 , p_wip_batch_id => p_wip_batch_id
845 , p_org_id => p_org_id
846 , p_subinv => p_subinv
847 , p_user_id => p_user_id
848 , p_employee_id => p_employee_id
849 , p_sysdate => p_sysdate
850 , p_approval => p_approval
851 , p_encum_flag => p_encum_flag
852 , p_cust_id => p_cust_id
853 , p_cust_site_id => p_cust_site_id
854 , p_cal_code => p_cal_code
855 , p_exception_set_id => p_exception_set_id
856 , p_dd_loc_id => p_dd_loc_id
857 , p_po_org_id => p_po_org_id
858 , p_pur_revision => p_pur_revision
859 , p_item_rec => item_rec
860 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
861 );
862
863 IF G_TRACE_ON = 1 THEN
864 print_debug(' Reord qty: ' || to_char(l_reord_qty) ||
865 ', Reorder status: ' || l_stat ||
866 ', l_sortee: ' || l_sortee
867 , l_proc_name
868 , 7);
869 END IF;
870
871 --
872 -- Insert into the global temp table INV_MIN_MAX_TEMP (defined
873 -- in patch/115/sql/invmmxtb.sql).
874 --
875 -- Replenishment Move Order Consolidation
876 -- only INSERT if the value of the new report parameter p_gen_report is 'Y'
877 --
878 --
879 -- kkoothan Bug Fix:2748471
880 -- Populated the value for the newly added column Subinventory_Code.
881 --
882 IF p_gen_report = 'Y' THEN
883 INSERT INTO INV_MIN_MAX_TEMP (
884 ITEM_SEGMENTS
885 , DESCRIPTION
886 , ERROR
887 , SORTEE
888 , SUBINVENTORY_CODE
889 , MIN_QTY
890 , MAX_QTY
891 , ONHAND_QTY
892 , SUPPLY_QTY
893 , DEMAND_QTY
894 , TOT_AVAIL_QTY
895 , MIN_ORD_QTY
896 , MAX_ORD_QTY
897 , FIX_MULT
898 , REORD_QTY)
899 VALUES ( l_item_segments
900 , item_rec.description
901 , l_stat
902 , l_sortee
903 , p_subinv
904 , item_rec.min_qty
905 , item_rec.max_qty
906 , l_onhand_qty
907 , l_supply_qty
908 , l_demand_qty
909 , l_tot_avail_qty
910 , item_rec.min_ord_qty
911 , item_rec.max_ord_qty
912 , item_rec.fix_mult
913 , l_reord_qty);
914 END IF;
915 END IF;
916 END LOOP;
917 CLOSE c_items_to_plan;
918
919 x_return_status := 'S';
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 IF c_items_to_plan%ISOPEN THEN
924 CLOSE c_items_to_plan;
925 END IF;
926
927 l_err_msg := sqlerrm;
928 IF G_TRACE_ON = 1 THEN
929 print_debug(sqlcode || ', ' || l_err_msg, l_proc_name, 1);
930 END IF;
931 x_return_status := 'E';
932 x_msg_data := l_err_msg;
933 END run_min_max_plan;
934
935
936
937 FUNCTION get_catg_disp ( p_category_id NUMBER
938 , p_struct_id NUMBER) RETURN VARCHAR2 IS
939
940 CURSOR c_catg_disp IS
941 SELECT concatenated_segments
942 FROM mtl_categories_kfv
943 WHERE category_id = p_category_id
944 AND structure_id = p_struct_id;
945
946 c_catg_rec c_catg_disp%ROWTYPE;
947
948 BEGIN
949 OPEN c_catg_disp;
950 FETCH c_catg_disp INTO c_catg_rec;
951 CLOSE c_catg_disp;
952
953 RETURN c_catg_rec.concatenated_segments;
954 END get_catg_disp;
955
956
957
958 FUNCTION get_onhand_qty( p_include_nonnet NUMBER
959 , p_level NUMBER
960 , p_org_id NUMBER
961 , p_subinv VARCHAR2
962 , p_item_id NUMBER
963 , p_sysdate DATE) RETURN NUMBER IS
964
965
966 --Bug# 2677358
967 l_moq_qty1 NUMBER := 0;
968 l_mmtt_qty1 NUMBER := 0;
969 l_mmtt_qty2 NUMBER := 0;
970 l_qoh NUMBER := 0;
971
972 BEGIN
973
974 IF g_trace_on = 1 THEN
975 print_debug('p_include_nonnet: ' || to_char(p_include_nonnet) ||
976 ', p_level: ' || to_char(p_level) ||
977 ', p_org_id: ' || to_char(p_org_id) ||
978 ', p_subinv: ' || p_subinv ||
979 ', p_item_id: ' || to_char(p_item_id) ||
980 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS')
981 , 'get_onhand_qty'
982 , 9);
983 END IF;
984
985
986 --distinction at Org/sub level is made in this API
987 l_qoh := INV_CONSIGNED_VALIDATIONS.get_planning_quantity
988 (p_include_nonnet => p_include_nonnet
989 , P_LEVEL => p_level
990 , P_ORG_ID => p_org_id
991 , P_SUBINV => p_subinv
992 , P_ITEM_ID => p_item_id
993 );
994
995
996 IF g_trace_on = 1 THEN
997 print_debug('Total quantity on-hand: ' || to_char(l_qoh), 'get_onhand_qty', 9);
998 END IF;
999
1000 return (l_qoh);
1001
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 IF G_TRACE_ON = 1 THEN
1005 print_debug(sqlcode || ', ' || sqlerrm, 'get_onhand_qty', 1);
1006 END IF;
1007 RAISE;
1008 END get_onhand_qty;
1009
1010
1011
1012 FUNCTION get_supply_qty( p_org_id NUMBER
1013 , p_subinv VARCHAR2
1014 , p_item_id NUMBER
1015 , p_postproc_lead_time NUMBER
1016 , p_cal_code VARCHAR2
1017 , p_except_id NUMBER
1018 , p_level NUMBER
1019 , p_s_cutoff DATE
1020 , p_include_po NUMBER
1021 , p_include_mo NUMBER
1022 , p_vmi_enabled VARCHAR2
1023 , p_include_nonnet NUMBER
1024 , p_include_wip NUMBER
1025 , p_include_if NUMBER
1026 /* nsinghi MIN-MAX INVCONV start */
1027 , p_process_org VARCHAR2
1028 /* nsinghi MIN-MAX INVCONV end */
1029 ) RETURN NUMBER IS
1030
1031 l_qty NUMBER;
1032 l_total NUMBER;
1033 l_puom VARCHAR2(3); --Bug3894347
1034
1035 -- Bug 3005403 Min-Max was not showing correct supply quantity for some
1036 -- supply records. Modified the query to consider receipt_date for
1037 -- records with supply type code 'SHIPMENT' and 'RECEIVING'
1038
1039 l_stmt VARCHAR2(4000) :=
1040 ' SELECT NVL(sum(to_org_primary_quantity), 0)
1041 FROM mtl_supply sup
1042 , bom_calendar_dates c
1043 , bom_calendar_dates c1
1044 WHERE sup.supply_type_code IN (''PO'',''REQ'',''SHIPMENT'',''RECEIVING'')
1045 AND sup.destination_type_code = ''INVENTORY''
1046 AND sup.to_organization_id = :l_org_id
1047 AND sup.item_id = :l_item_id
1048 AND c.calendar_code = :l_cal_code
1049 AND c.exception_set_id = :l_except_id
1050 AND c.calendar_date = trunc(decode(sup.supply_type_code, ''SHIPMENT'', sup.receipt_date, ''RECEIVING'', sup.receipt_date,nvl(sup.need_by_date, sup.receipt_date)))
1051 AND c1.calendar_code = c.calendar_code
1052 AND c1.exception_set_id = c.exception_set_id
1053 AND c1.seq_num = (c.next_seq_num + trunc(:l_postproc_lead_time))
1054 AND c1.calendar_date <= :l_s_cutoff + 0.99999 /* bug no 6009682 */
1055 AND (NVL(sup.from_organization_id,-1) <> :l_org_id
1056 OR (sup.from_organization_id = :l_org_id
1057 AND ((:l_include_nonnet = 2
1058 AND
1059 EXISTS (SELECT ''x''
1060 FROM mtl_secondary_inventories sub1
1061 WHERE sub1.organization_id = sup.from_organization_id
1062 AND sub1.secondary_inventory_name = sup.from_subinventory
1063 AND sub1.availability_type <> 1
1064 )
1065 )
1066 OR :l_level = 2
1067 )
1068 )
1069 )
1070 AND (sup.to_subinventory IS NULL
1071 OR
1072 (EXISTS (SELECT ''x''
1073 FROM mtl_secondary_inventories sub2
1074 WHERE sub2.secondary_inventory_name = sup.to_subinventory
1075 AND sub2.organization_id = sup.to_organization_id
1076 AND sub2.availability_type = decode(:l_include_nonnet,
1077 1,sub2.availability_type,
1078 1)
1079 )
1080 )
1081 OR :l_level = 2
1082 )
1083 AND (:l_level = 1 OR to_subinventory = :l_subinv)
1084 -- Bug 5041763 Not considering supply from drop ship orders
1085 AND NOT EXISTS (SELECT ''X'' FROM OE_DROP_SHIP_SOURCES ODSS
1086 WHERE DECODE(sup.PO_HEADER_ID, NULL, sup.REQ_LINE_ID, sup.PO_LINE_LOCATION_ID) =
1087 DECODE(sup.PO_HEADER_ID,NULL, ODSS.REQUISITION_LINE_ID, ODSS.LINE_LOCATION_ID)) ';
1088
1089
1090
1091 l_vmi_stmt VARCHAR2(2000) :=
1092 ' AND (sup.po_line_location_id IS NULL
1093 OR EXISTS (SELECT ''x''
1094 FROM po_line_locations_all lilo
1095 WHERE lilo.line_location_id = sup.po_line_location_id
1096 AND NVL(lilo.vmi_flag,''N'') = ''N''
1097 )
1098 )
1099 AND (sup.req_line_id IS NULL
1100 OR EXISTS (SELECT ''x''
1101 FROM po_requisition_lines_all prl
1102 WHERE prl.requisition_line_id = sup.req_line_id
1103 AND NVL(prl.vmi_flag,''N'') = ''N''
1104 )
1105 )';
1106
1107
1108 TYPE c_po_sup_curtype IS REF CURSOR;
1109 c_po_qty c_po_sup_curtype;
1110
1111 BEGIN
1112 IF G_TRACE_ON = 1 THEN
1113 print_debug('sbitrap_org_id: ' || to_char(p_org_id) ||
1114 ', p_subinv: ' || p_subinv ||
1115 ', p_item_id: ' || to_char(p_item_id) ||
1116 ', p_postproc_lead_time: ' || to_char(p_postproc_lead_time) ||
1117 ', p_cal_code: ' || p_cal_code ||
1118 ', p_except_id: ' || to_char(p_except_id) ||
1119 ', p_level: ' || to_char(p_level) ||
1120 ', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
1121 ', p_include_po: ' || to_char(p_include_po) ||
1122 ', p_include_mo: ' || to_char(p_include_mo) ||
1123 ', p_include_nonnet: ' || to_char(p_include_nonnet) ||
1124 ', p_include_wip: ' || to_char(p_include_wip) ||
1125 ', p_include_if: ' || to_char(p_include_if)
1126 , 'get_supply_qty'
1127 , 9);
1128 END IF;
1129
1130 l_total := 0;
1131
1132 --
1133 -- MTL_SUPPLY
1134 --
1135 IF p_include_po = 1 THEN
1136 IF (p_vmi_enabled = 'Y') and (p_level= 1) then
1137 OPEN c_po_qty FOR l_stmt || l_vmi_stmt
1138 USING
1139 p_org_id, p_item_id, p_cal_code, p_except_id, p_postproc_lead_time,
1140 p_s_cutoff, p_org_id, p_org_id, p_include_nonnet,
1141 p_level, p_include_nonnet, p_level, p_level, p_subinv;
1142 else
1143 OPEN c_po_qty FOR l_stmt
1144 USING
1145 p_org_id, p_item_id, p_cal_code, p_except_id, p_postproc_lead_time,
1146 p_s_cutoff, p_org_id, p_org_id, p_include_nonnet,
1147 p_level, p_include_nonnet, p_level, p_level, p_subinv;
1148 END IF;
1149
1150 FETCH c_po_qty INTO l_qty;
1151 CLOSE c_po_qty;
1152 IF G_TRACE_ON = 1 THEN
1153 print_debug('Supply from mtl_supply: ' || to_char(l_qty)
1154 , 'get_supply_qty'
1155 , 9);
1156 END IF;
1157
1158 l_total := l_total + l_qty;
1159
1160 END IF;
1161 --
1162 -- Take into account the quantity for which a move order
1163 -- has already been created assuming that the move order
1164 -- can be created only within the same org
1165 -- Bug 3057273, Added new parameter p_include_mo check.
1166
1167 IF (p_level = 2 and p_include_mo = 1) THEN
1168 -- kkoothan Part of Bug Fix: 2875583
1169 -- Converting the quantities to the primary uom as the quantity
1170 -- and quantity delivered in mtl_txn_request_lines
1171 -- are in transaction uom.
1172
1173 /* SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
1174 INTO l_qty
1175 FROM mtl_transaction_types mtt,
1176 mtl_txn_request_lines mtrl
1177 WHERE mtt.transaction_action_id IN (2,28)
1178 AND mtt.transaction_type_id = mtrl.transaction_type_id
1179 AND mtrl.organization_id = p_org_id
1180 AND mtrl.inventory_item_id = p_item_id
1181 AND mtrl.to_subinventory_code = p_subinv
1182 AND mtrl.line_status NOT IN (5,6)
1183 AND mtrl.date_required <= p_s_cutoff;*/
1184
1185 /*4518296*/
1186 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
1187 ,p_item_id
1188 , mtrl.uom_code
1189 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
1190 ),0)
1191 INTO l_qty
1192 FROM mtl_transaction_types mtt,
1193 mtl_txn_request_lines mtrl
1194 WHERE mtt.transaction_action_id IN (2,28)
1195 AND mtt.transaction_type_id = mtrl.transaction_type_id
1196 AND mtrl.organization_id = p_org_id
1197 AND mtrl.inventory_item_id = p_item_id
1198 AND mtrl.to_subinventory_code = p_subinv
1199 AND mtrl.line_status IN (3,7) --Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
1200 AND mtrl.date_required <= p_s_cutoff + 0.99999; /* bug no 6009682 */
1201
1202 IF G_TRACE_ON = 1 THEN
1203 print_debug('Supply from move orders: ' || to_char(l_qty)
1204 , 'get_supply_qty'
1205 , 9);
1206 END IF;
1207 l_total := l_total + l_qty;
1208 END IF;
1209
1210 --
1211 -- Supply FROM WIP discrete job is to be included at Org Level Planning Only
1212 --
1213 IF p_level = 1 AND p_include_wip = 1
1214 THEN
1215
1216 /* nsinghi MIN-MAX INVCONV start */
1217
1218 /* Here check will need to be made if the org is Process org or Discrete org. */
1219
1220 IF p_process_org = 'Y' THEN
1221 SELECT
1222 SUM ( NVL((NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty), 0) *
1223 (original_primary_qty/original_qty))
1224 INTO l_qty
1225 FROM gme_material_details d
1226 , gme_batch_header h
1227 WHERE h.batch_type IN (0,10)
1228 AND h.batch_status IN (1,2)
1229 AND h.batch_id = d.batch_id
1230 AND d.inventory_item_id = p_item_id
1231 AND d.organization_id = p_org_id
1232 AND NVL(d.original_qty, 0) <> 0
1233 AND d.material_requirement_date <= p_s_cutoff
1234 AND d.line_type > 0;
1235
1236 IF G_TRACE_ON = 1 THEN
1237 print_debug('Supply from OPM Batches : ' || to_char(l_qty)
1238 , 'get_supply_qty'
1239 , 9);
1240 END IF;
1241 l_total := l_total + NVL(l_qty,0);
1242
1243 ELSE
1244 /* nsinghi MIN-MAX INVCONV end */
1245
1246 SELECT sum(NVL(start_quantity,0)
1247 - NVL(quantity_completed,0)
1248 - NVL(quantity_scrapped,0))
1249 INTO l_qty
1250 FROM wip_discrete_jobs
1251 WHERE organization_id = p_org_id
1252 AND primary_item_id = p_item_id
1253 AND job_type in (1,3)
1254 AND status_type IN (1,3,4,6)
1255 --Bug 2647862
1256 AND scheduled_completion_date <= p_s_cutoff + 0.99999 /* bug no 6009682 */
1257 AND (NVL(start_quantity,0) - NVL(quantity_completed,0)
1258 - NVL(quantity_scrapped,0)) > 0;
1259
1260 IF G_TRACE_ON = 1 THEN
1261 print_debug('Supply from WIP discrete jobs: ' || to_char(l_qty)
1262 , 'get_supply_qty'
1263 , 9);
1264 END IF;
1265 l_total := l_total + NVL(l_qty,0);
1266
1267 --
1268 -- WIP REPETITIVE JOBS to be included at Org Level Planning Only
1269 --
1270 SELECT SUM(daily_production_rate *
1271 GREATEST(0, LEAST(processing_work_days,
1272 p_s_cutoff - first_unit_completion_date
1273 )
1274 )
1275 - quantity_completed)
1276 INTO l_qty
1277 FROM wip_repetitive_schedules wrs,
1278 wip_repetitive_items wri
1279 WHERE wrs.organization_id = p_org_id
1280 AND wrs.status_type IN (1,3,4,6)
1281 AND wri.organization_id = p_org_id
1282 AND wri.primary_item_id = p_item_id
1283 AND wri.wip_entity_id = wrs.wip_entity_id
1284 AND wri.line_id = wrs.line_id
1285 AND (daily_production_rate *
1286 GREATEST(0, LEAST(processing_work_days,
1287 p_s_cutoff - first_unit_completion_date
1288 )
1289 )
1290 - quantity_completed) > 0;
1291
1292 IF G_TRACE_ON = 1 THEN
1293 print_debug('Supply from WIP repetitive schedules: ' || to_char(l_qty)
1294 , 'get_supply_qty'
1295 , 9);
1296 END IF;
1297
1298 l_total := l_total + NVL(l_qty,0);
1299
1300 END IF; /* p_process_org = 'Y' */
1301
1302 END IF;
1303
1304 IF (p_include_if = 2)
1305 THEN
1306 RETURN(l_total);
1307 END IF;
1308
1309 --
1310 -- po_requisitions_interface_all
1311 --
1312 -- Bug: 2320752
1313 -- Do not include records in error status
1314 -- kkoothan Bug Fix 2891818
1315 -- Used NVL function for the condition involving process_flag
1316 -- so that the interface records with NULL value for process_flag
1317 -- are considered as Interface Supply.
1318 --
1319 /* Bug 3894347 -- Added the following section of code to consider conversion
1320 of quantities in po_requisitions_interface_all if uom_code is different than
1321 primary uom code */
1322
1323 SELECT uom_code
1324 INTO l_puom
1325 FROM mtl_system_items_vl msiv , mtl_units_of_measure_vl muom
1326 WHERE msiv.inventory_item_id = p_item_id
1327 AND msiv.organization_id = p_org_id
1328 AND muom.unit_of_measure = msiv.primary_unit_of_measure;
1329
1330 --Bug9122329, calling the function get_item_uom_code in case when uom_code is null
1331 --in the po_requisitions_interface_all table.
1332
1333 SELECT NVL(SUM(DECODE(NVL(uom_code,get_item_uom_code(unit_of_measure)),
1334 l_puom,quantity,
1335 INV_CONVERT.INV_UM_CONVERT(p_item_id,null,quantity,NVL(uom_code,get_item_uom_code(unit_of_measure)),l_puom,null,null)
1336 )),0)
1337 INTO l_qty
1338 FROM po_requisitions_interface_all
1339 WHERE destination_organization_id = p_org_id
1340 AND item_id = p_item_id
1341 AND p_include_po = 1
1342 AND (p_level = 1 or destination_subinventory = p_subinv)
1343 AND need_by_date <= (trunc(p_s_cutoff) + 1 - (1/(24*60*60)))
1344 AND NVL(process_flag,'@@@') <> 'ERROR'
1345 AND (NVL(source_organization_id,-1) <> p_org_id OR
1346 (source_organization_id = p_org_id AND
1347 (( p_include_nonnet = 2 AND
1348 EXISTS (SELECT 'x'
1349 FROM mtl_secondary_inventories sub1
1350 WHERE sub1.organization_id = source_organization_id
1351 AND sub1.secondary_inventory_name = source_subinventory
1352 AND sub1.availability_type <> 1)) OR
1353 p_level = 2)
1354 ))
1355 AND (destination_subinventory IS NULL OR
1356 EXISTS (SELECT 1
1357 FROM mtl_secondary_inventories sub2
1358 WHERE secondary_inventory_name = destination_subinventory
1359 AND destination_subinventory = NVL(p_subinv,
1360 destination_subinventory)
1361 AND sub2.organization_id = p_org_id
1362 AND sub2.availability_type = decode(p_include_nonnet,
1363 1,sub2.availability_type,1)) OR
1364 p_level = 2);
1365
1366 IF G_TRACE_ON = 1 THEN
1367 print_debug('Supply from po_requisitions_interface_all: ' || to_char(l_qty)
1368 , 'get_supply_qty'
1369 , 9);
1370 END IF;
1371 l_total := l_total + NVL(l_qty,0);
1372
1373 --
1374 -- WIP_JOB_SCHEDULE_INTERFACE, processed immediately, hence not included
1375 --
1376 -- Supply FROM Flow to be included in org level only
1377 --
1378 IF p_level = 1
1379 THEN
1380 SELECT SUM(NVL(planned_quantity,0)
1381 - NVL(quantity_completed,0))
1382 INTO l_qty
1383 FROM wip_flow_schedules
1384 WHERE organization_id = p_org_id
1385 AND primary_item_id = p_item_id
1386 AND status = 1
1387 AND scheduled_flag = 1 -- Bug 3151797
1388 --Bug 2647862
1389 AND scheduled_completion_date <= p_s_cutoff + 0.99999 /* bug no 6009682 */
1390 AND (NVL(planned_quantity,0)
1391 - NVL(quantity_completed,0)) > 0;
1392
1393 IF G_TRACE_ON = 1 THEN
1394 print_debug('Supply from WIP flow schedules: ' || to_char(l_qty)
1395 , 'get_supply_qty'
1396 , 9);
1397 END IF;
1398 l_total := l_total + NVL(l_qty,0);
1399 END IF;
1400
1401 RETURN(l_total);
1402
1403 EXCEPTION
1404 WHEN others THEN
1405 IF c_po_qty%ISOPEN THEN
1406 CLOSE c_po_qty;
1407 END IF;
1408 IF G_TRACE_ON = 1 THEN
1409 print_debug(sqlcode || ', ' || sqlerrm, 'get_supply_qty', 1);
1410 END IF;
1411 RAISE;
1412 END get_supply_qty;
1413
1414
1415
1416 FUNCTION get_demand_qty( p_org_id NUMBER
1417 , p_subinv VARCHAR2
1418 , p_level NUMBER
1419 , p_item_id NUMBER
1420 , p_d_cutoff DATE
1421 , p_include_nonnet NUMBER
1422 , p_net_rsv NUMBER
1423 , p_net_unrsv NUMBER
1424 , p_net_wip NUMBER
1425 /* nsinghi MIN-MAX INVCONV start */
1426 , p_process_org VARCHAR2
1427 /* nsinghi MIN-MAX INVCONV end */
1428 ) RETURN NUMBER IS
1429
1430 qty NUMBER := 0;
1431 total NUMBER := 0;
1432 l_total_demand_qty NUMBER := 0;
1433 l_demand_qty NUMBER := 0;
1434 l_total_reserve_qty NUMBER := 0;
1435 l_pick_released_qty NUMBER := 0;
1436 l_staged_qty NUMBER := 0;
1437 l_sub_reserve_qty NUMBER := 0;
1438 l_allocated_qty NUMBER := 0;
1439 l_loaded_qty NUMBER := 0; /*Bug 6240025 */
1440
1441 BEGIN
1442 IF G_TRACE_ON = 1 THEN
1443 print_debug('p_org_id: ' || to_char(p_org_id) ||
1444 ', p_subinv: ' || p_subinv ||
1445 ', p_level: ' || to_char(p_level) ||
1446 ', p_item_id: ' || to_char(p_item_id) ||
1447 ', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
1448 ', p_include_nonnet: ' || to_char(p_include_nonnet) ||
1449 ', p_net_rsv: ' || to_char(p_net_rsv) ||
1450 ', p_net_unrsv: ' || to_char(p_net_unrsv) ||
1451 ', p_net_wip: ' || to_char(p_net_wip)
1452 , 'get_demand_qty'
1453 , 9);
1454 END IF;
1455
1456 --
1457 -- select unreserved qty from mtl_demand for non oe rows.
1458 --
1459 IF p_net_unrsv = 1 THEN
1460 /*4518296*/
1461 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1462 NVL(COMPLETED_QUANTITY,0)))
1463 into qty
1464 from mtl_demand
1465 WHERE RESERVATION_TYPE = 1
1466 AND parent_demand_id IS NULL
1467 AND ORGANIZATION_ID = p_org_id
1468 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1469 NVL(COMPLETED_QUANTITY,0))
1470 and INVENTORY_ITEM_ID = p_item_id
1471 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1472 and demand_source_type not in (2,8,12)
1473 and (p_level = 1 or
1474 SUBINVENTORY = p_subinv) -- Included later for ORG Level
1475 and (SUBINVENTORY is null or
1476 p_level = 2 or
1477 EXISTS (SELECT 1
1478 FROM MTL_SECONDARY_INVENTORIES S
1479 WHERE S.ORGANIZATION_ID = p_org_id
1480 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1481 AND S.availability_type = DECODE(p_include_nonnet,
1482 1,
1483 S.availability_type,
1484 1)))
1485 /* nsinghi MIN-MAX INVCONV start */
1486 AND (locator_id IS NULL OR
1487 p_level = 2 OR
1488 EXISTS (SELECT 1 FROM mtl_item_locations mil
1489 WHERE mil.organization_id = p_org_id
1490 AND mil.inventory_location_id = locator_id
1491 AND mil.subinventory_code = NVL(subinventory, mil.subinventory_code)
1492 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1493 AND (lot_number IS NULL OR
1494 p_level = 2 OR
1495 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1496 WHERE mln.organization_id = p_org_id
1497 AND mln.lot_number = lot_number
1498 AND mln.inventory_item_id = p_item_id
1499 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
1500 /* nsinghi MIN-MAX INVCONV end */
1501
1502 IF G_TRACE_ON = 1 THEN
1503 print_debug('Demand from mtl_demand: ' || to_char(qty), 'get_demand_qty', 9);
1504 END IF;
1505 total := total + NVL(qty,0);
1506 END IF;
1507
1508 --
1509 -- select the reserved quantity from mtl_reservations for non OE rows
1510 --
1511 IF p_net_rsv = 1 THEN
1512 select sum(PRIMARY_RESERVATION_QUANTITY)
1513 into qty
1514 from mtl_reservations
1515 where ORGANIZATION_ID = p_org_id
1516 and INVENTORY_ITEM_ID = p_item_id
1517 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1518 and demand_source_type_id not in (2,8,12)
1519 and (p_level = 1 or
1520 SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
1521 and (SUBINVENTORY_CODE is null or
1522 p_level = 2 or
1523 EXISTS (SELECT 1
1524 FROM MTL_SECONDARY_INVENTORIES S
1525 WHERE S.ORGANIZATION_ID = p_org_id
1526 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1527 AND S.availability_type = DECODE(p_include_nonnet,
1528 1,
1529 S.availability_type,
1530 1)))
1531 /* nsinghi MIN-MAX INVCONV start */
1532 AND (locator_id IS NULL OR
1533 p_level = 2 OR
1534 EXISTS (SELECT 1 FROM mtl_item_locations mil
1535 WHERE mil.organization_id = p_org_id
1536 AND mil.inventory_location_id = locator_id
1537 AND mil.subinventory_code = NVL(subinventory_code, mil.subinventory_code)
1538 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1539 AND (lot_number IS NULL OR
1540 p_level = 2 OR
1541 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1542 WHERE mln.organization_id = p_org_id
1543 AND mln.lot_number = lot_number
1544 AND mln.inventory_item_id = p_item_id
1545 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
1546 /* nsinghi MIN-MAX INVCONV end */
1547
1548 IF G_TRACE_ON = 1 THEN
1549 print_debug('Demand (reserved qty) for non OE rows in mtl_reservations: ' || to_char(qty)
1550 , 'get_demand_qty'
1551 , 9);
1552 END IF;
1553 total := total + NVL(qty,0);
1554 END IF;
1555
1556 --
1557 -- get the total demand which is the difference between the
1558 -- ordered qty. and the shipped qty.
1559 -- This gives the total demand including the reserved
1560 -- and the unreserved material.
1561 --
1562 -- Bug 2333526: For sub level planning we need to compute
1563 -- the staged qty. The existing WHERE clause makes sure
1564 -- we only do this when the order is sourced from the
1565 -- planning sub: level = 1... or SUBINVENTORY = p_subinv
1566 --
1567 -- Bug 2350243: For sub level, calculate pick released
1568 -- (move order) qty
1569 --
1570
1571 -- Bug 3480523, from patch I onwards schedule_ship_date is being populated
1572 -- with time component, hence truncating it to get the same day demand. These
1573 -- changes are also in mtl_reservation queries.
1574 if p_net_unrsv = 1 then
1575 select SUM(inv_decimals_pub.get_primary_quantity( ship_from_org_id
1576 , inventory_item_id
1577 , order_quantity_uom
1578 , NVL(ordered_quantity,0)) -
1579 get_shipped_qty(p_org_id, p_item_id, ool.line_id)),
1580 SUM(DECODE(p_level,
1581 2, get_staged_qty( p_org_id
1582 , p_subinv
1583 , p_item_id
1584 , ool.line_id
1585 , p_include_nonnet),
1586 0)
1587 ),
1588 SUM(DECODE(p_level,
1589 2, get_pick_released_qty( p_org_id
1590 , p_subinv
1591 , p_item_id
1592 , ool.line_id),
1593 0)
1594 )
1595 into l_total_demand_qty, l_staged_qty, l_pick_released_qty
1596 from oe_order_lines_all ool
1597 where ship_from_org_id = p_org_id
1598 and open_flag = 'Y'
1599 AND visible_demand_flag = 'Y'
1600 AND shipped_quantity is null
1601 and INVENTORY_ITEM_ID = p_item_id
1602 and schedule_ship_date <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1603 AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
1604 , 10, 8
1605 , DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
1606 and ((p_level = 1
1607 AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
1608 , 10, 8
1609 , DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8)
1610 OR SUBINVENTORY = p_subinv) -- Included later for ORG Level
1611 and (SUBINVENTORY is null or
1612 p_level = 2 or
1613 EXISTS (SELECT 1
1614 FROM MTL_SECONDARY_INVENTORIES S
1615 WHERE S.ORGANIZATION_ID = p_org_id
1616 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1617 AND S.availability_type = DECODE(p_include_nonnet,
1618 1,
1619 S.availability_type,
1620 1)));
1621 IF G_TRACE_ON = 1 THEN
1622 print_debug('Demand from sales orders: ' ||
1623 ' Ordered: ' || to_char(l_total_demand_qty) ||
1624 ', Pick released: ' || to_char(l_pick_released_qty) ||
1625 ', Staged: ' || to_char(l_staged_qty)
1626 , 'get_demand_qty'
1627 , 9);
1628 END IF;
1629 end if;
1630
1631 --
1632 -- Find out the reserved qty for the material from mtl_reservations
1633 --
1634 -- Since total demand = reserved + unreserved, and we know total
1635 -- demand from oe_order_lines_all (above) we only need to query
1636 -- mtl_reservations if the user wants one of the following:
1637 --
1638 -- 1) Only reserved: (p_net_rsv = 1 and p_net_unrsv = 2)
1639 --
1640 -- OR
1641 --
1642 -- 2) Only unreserved: (p_net_rsv = 2 and p_net_unrsv = 1)
1643 --
1644
1645 IF ((p_net_rsv = 1 and p_net_unrsv = 2)
1646 OR
1647 (p_net_rsv = 2 and p_net_unrsv = 1))
1648 THEN
1649 select sum(PRIMARY_RESERVATION_QUANTITY)
1650 into l_total_reserve_qty
1651 from mtl_reservations
1652 WHERE ORGANIZATION_ID = p_org_id
1653 and INVENTORY_ITEM_ID = p_item_id
1654 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1655 and demand_source_type_id in (2,8,12)
1656 and ((p_level = 1 AND demand_source_type_id <> 8) OR
1657 SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
1658 and (SUBINVENTORY_CODE is null or
1659 p_level = 2 or
1660 EXISTS (SELECT 1
1661 FROM MTL_SECONDARY_INVENTORIES S
1662 WHERE S.ORGANIZATION_ID = p_org_id
1663 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1664 AND S.availability_type = DECODE(p_include_nonnet,
1665 1,
1666 S.availability_type,
1667 1)))
1668 /* nsinghi MIN-MAX INVCONV start */
1669 AND (locator_id IS NULL OR
1670 p_level = 2 OR
1671 EXISTS (SELECT 1 FROM mtl_item_locations mil
1672 WHERE mil.organization_id = p_org_id
1673 AND mil.inventory_location_id = locator_id
1674 AND mil.subinventory_code = NVL(subinventory_code, mil.subinventory_code)
1675 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1676 AND (lot_number IS NULL OR
1677 p_level = 2 OR
1678 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1679 WHERE mln.organization_id = p_org_id
1680 AND mln.lot_number = lot_number
1681 AND mln.inventory_item_id = p_item_id
1682 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)))
1683 -- Bug 5041763 excluding drop ship demand
1684 and NOT EXISTS (SELECT 1
1685 FROM OE_DROP_SHIP_SOURCES ODSS
1686 WHERE ODSS.LINE_ID = DEMAND_SOURCE_LINE_ID);
1687
1688 /* nsinghi MIN-MAX INVCONV end */
1689
1690 IF G_TRACE_ON = 1 THEN
1691 print_debug('Reserved demand (sales orders): ' || to_char(l_total_reserve_qty)
1692 , 'get_demand_qty'
1693 , 9);
1694 END IF;
1695 END IF;
1696
1697
1698 --
1699 -- Bug 3238390, we need to take care of reservations with sub but sales order
1700 -- with no sub for sub level planning. Adding the below query
1701 --
1702 IF (p_level = 2 and (p_net_rsv = 1 or p_net_unrsv = 1)) THEN
1703
1704 select sum(mr.PRIMARY_RESERVATION_QUANTITY) into l_sub_reserve_qty
1705 from mtl_reservations mr, oe_order_lines_all ool
1706 where mr.organization_id = p_org_id
1707 AND mr.inventory_item_id = p_item_id
1708 AND mr.demand_source_line_id = ool.line_id
1709 AND mr.demand_source_type_id in (2,8,12)
1710 AND ool.subinventory is NULL
1711 AND ool.open_flag = 'Y'
1712 AND ool.visible_demand_flag = 'Y'
1713 AND ool.shipped_quantity is null
1714 AND mr.REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1715 AND mr.subinventory_code IS NOT NULL
1716 AND mr.subinventory_code = p_subinv;
1717
1718 print_debug('Reserved demand (sales orders with no sub and reservations with sub): ' || to_char(l_sub_reserve_qty)
1719 , 'get_demand_qty'
1720 , 9);
1721
1722 END IF;
1723
1724 IF (p_level = 2 and p_net_rsv = 1) THEN
1725
1726 BEGIN
1727
1728 SELECT NVL(SUM(primary_quantity),0)
1729 INTO l_allocated_qty
1730 FROM mtl_material_transactions_temp mmtt
1731 WHERE inventory_item_id = p_item_id
1732 AND organization_id = p_org_id
1733 AND subinventory_code = p_subinv
1734 AND transfer_subinventory <> p_subinv
1735 AND NVL(transaction_status, 1) = 2
1736 AND transaction_source_type_id in (2,8)
1737 AND not exists (SELECT 1 from mtl_reservations
1738 WHERE reservation_id = mmtt.reservation_id
1739 AND nvl(subinventory_code, '@@@') = p_subinv)
1740 AND exists (SELECT 1 from mtl_txn_request_lines
1741 WHERE line_id = mmtt.move_order_line_id
1742 AND from_subinventory_code is null
1743 AND line_status NOT IN (5,6)
1744 AND date_required <= p_d_cutoff + 0.99999); /* bug no 6009682 */
1745
1746 EXCEPTION
1747 WHEN OTHERS THEN
1748 l_allocated_qty := 0;
1749 END;
1750 END IF;
1751
1752 print_debug('Allocated demand for subinventory: ' || to_char(l_allocated_qty)
1753 , 'get_demand_qty'
1754 , 9);
1755
1756 --
1757 -- total demand is calculated as follows:
1758 -- if we have to consider both unreserved matl and reserved matl. then the
1759 -- demand is simply the total demand = ordered qty - shipped qty.
1760 -- Bug 2333526: Deduct staged qty for sub level. (l_staged_qty
1761 -- is always set to 0 for org level planning).
1762 -- Bug 3238390, add reserved qty for sales orders with no sub
1763 -- and reservation with sub for sub level planning.
1764 -- elsif we have to take into account only reserved matl. then the
1765 -- demand is simply the reservations from mtl_reservations for the matl.
1766 -- elsif we have to take into account just the unreserved matl. then the
1767 -- demand is total demand - the reservations for the material.
1768 -- Bug 3238390, add reserved qty for sales orders with no sub
1769 -- and reservation with sub for sub level planning, so that demand doesn't go -ve.
1770 IF p_net_unrsv = 1 AND p_net_rsv = 1 THEN
1771 l_demand_qty := NVL(l_total_demand_qty,0)
1772 - NVL(l_staged_qty,0)
1773 - NVL(l_pick_released_qty,0)
1774 + NVL(l_sub_reserve_qty,0)
1775 + NVL(l_allocated_qty,0);
1776
1777 ELSIF p_net_rsv = 1 THEN
1778 l_demand_qty := NVL(l_total_reserve_qty,0) + NVL(l_allocated_qty,0);
1779
1780 ELSIF p_net_unrsv = 1 THEN
1781 l_demand_qty := NVL(l_total_demand_qty,0) - NVL(l_total_reserve_qty,0) + NVL(l_sub_reserve_qty,0);
1782
1783 END IF;
1784 IF G_TRACE_ON = 1 THEN
1785 print_debug('Demand from shippable orders: ' || to_char(l_demand_qty)
1786 , 'get_demand_qty'
1787 , 9);
1788 END IF;
1789 total := total + NVL(l_demand_qty,0);
1790
1791 --
1792 -- Take care of internal orders for org level planning
1793 --
1794 if p_level = 1 then
1795 l_total_demand_qty := 0;
1796 l_demand_qty := 0;
1797 l_total_reserve_qty := 0;
1798
1799 --
1800 -- get the total demand which is the difference between the
1801 -- ordered qty. and the shipped qty.
1802 -- This gives the total demand including the reserved
1803 -- and the unreserved material.
1804 --
1805 -- Bug 2820011. Modified the where clause to make use of source_document_id
1806 -- and source_document_line_id of oe_order_lines_all instead of
1807 -- orig_sys_document_ref and orig_sys_line_ref to identify requisitions
1808 -- and requisition lines uniquely.
1809
1810 if p_net_unrsv = 1 then
1811 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( SHIP_FROM_ORG_ID
1812 , INVENTORY_ITEM_ID
1813 , ORDER_QUANTITY_UOM
1814 , NVL(ordered_quantity,0)) -
1815 get_shipped_qty(p_org_id, p_item_id, so.line_id))
1816 into l_total_demand_qty
1817 from oe_order_lines_all so,
1818 -- po_requisition_headers_all poh,
1819 po_requisition_lines_all pol
1820 where so.SOURCE_DOCUMENT_ID = pol.requisition_header_id
1821 -- and poh.requisition_header_id = pol.requisition_header_id
1822 and so.source_document_line_id = pol.requisition_line_id
1823 and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
1824 (pol.DESTINATION_ORGANIZATION_ID = p_org_id and -- Added code Bug#1012179
1825 ( pol.DESTINATION_TYPE_CODE = 'EXPENSE' OR --Bug#3619239 started
1826 -- Bug 3619239 The functionality is added so that demand from Internal Sales Requisitions are taken
1827 -- into consideration if Destination Type is Inventory and Destination Subinventory is Non Quantity Tracked
1828 ( pol.DESTINATION_TYPE_CODE = 'INVENTORY'
1829 AND pol.DESTINATION_SUBINVENTORY IS NOT NULL
1830 AND EXISTS (select 1 from
1831 MTL_SECONDARY_INVENTORIES
1832 where SECONDARY_INVENTORY_NAME = pol.DESTINATION_SUBINVENTORY
1833 and ORGANIZATION_ID = pol.DESTINATION_ORGANIZATION_ID
1834 and QUANTITY_TRACKED = 2)
1835 )
1836 )-- Bug#3619239 ended
1837 )
1838 )
1839 and so.ship_from_org_ID = p_org_id
1840 and so.open_flag = 'Y'
1841 AND so.visible_demand_flag = 'Y'
1842 AND shipped_quantity is null
1843 and so.INVENTORY_ITEM_ID = p_item_id
1844 and schedule_ship_date <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1845 and DECODE(so.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(so.LINE_CATEGORY_CODE, 'ORDER',2,12)) = 8
1846 and (SUBINVENTORY is null or
1847 EXISTS (SELECT 1
1848 FROM MTL_SECONDARY_INVENTORIES S
1849 WHERE S.ORGANIZATION_ID = p_org_id
1850 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1851 AND S.availability_type = DECODE(p_include_nonnet,
1852 1,
1853 S.availability_type,
1854 1)));
1855
1856 IF G_TRACE_ON = 1 THEN
1857 print_debug('Total demand (internal orders): ' || to_char(l_total_demand_qty)
1858 , 'get_demand_qty'
1859 , 9);
1860 END IF;
1861 end if;
1862
1863 --
1864 -- Find out the reserved qty for the material from mtl_reservations
1865 --
1866 IF ((p_net_rsv = 1 and p_net_unrsv = 2)
1867 OR
1868 (p_net_rsv = 2 and p_net_unrsv = 1))
1869 THEN
1870 --
1871 -- Include the reserved demand from mtl_reservations
1872 --
1873 select sum(PRIMARY_RESERVATION_QUANTITY)
1874 into l_total_reserve_qty
1875 from mtl_reservations md, oe_order_lines_all so,
1876 -- po_req_distributions_all pod, Bug 5934651
1877 po_requisition_lines_all pol
1878 where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
1879 -- and to_number(so.ORIG_SYS_LINE_REF) = pod.DISTRIBUTION_ID --Bug#2883172
1880 and so.SOURCE_DOCUMENT_ID = pol.requisition_header_id -- Bug 5934651
1881 and so.source_document_line_id = pol.requisition_line_id
1882 -- and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
1883 and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
1884 (pol.DESTINATION_ORGANIZATION_ID = p_org_id
1885 and -- Added code Bug#1012179
1886 ( pol.DESTINATION_TYPE_CODE = 'EXPENSE' OR -- Bug#3619239 started
1887 -- Bug 3619239 The functionality is added so that demand from Internal Sales Requisitions are taken
1888 -- into consideration if Destination Type is Inventory and Destination Subinventory is Non Quantity Tracked
1889 ( pol.DESTINATION_TYPE_CODE = 'INVENTORY'
1890 AND pol.DESTINATION_SUBINVENTORY IS NOT NULL
1891 AND EXISTS (select 1 from
1892 MTL_SECONDARY_INVENTORIES
1893 where SECONDARY_INVENTORY_NAME = pol.DESTINATION_SUBINVENTORY
1894 and ORGANIZATION_ID = pol.DESTINATION_ORGANIZATION_ID
1895 and QUANTITY_TRACKED = 2)
1896 )
1897 )-- Bug#3619239 ended
1898 )
1899 )
1900 and ORGANIZATION_ID = p_org_id
1901 and md.INVENTORY_ITEM_ID = p_item_id
1902 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1903 and demand_source_type_id = 8
1904 and (SUBINVENTORY_CODE is null or
1905 EXISTS (SELECT 1
1906 FROM MTL_SECONDARY_INVENTORIES S
1907 WHERE S.ORGANIZATION_ID = p_org_id
1908 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1909 AND S.availability_type = DECODE(p_include_nonnet,
1910 1,
1911 S.availability_type,
1912 1)))
1913 /* nsinghi MIN-MAX INVCONV start */
1914 AND (md.locator_id IS NULL OR
1915 p_level = 2 OR
1916 EXISTS (SELECT 1 FROM mtl_item_locations mil
1917 WHERE mil.organization_id = p_org_id
1918 AND mil.inventory_location_id = md.locator_id
1919 AND mil.subinventory_code = NVL(md.subinventory_code, mil.subinventory_code)
1920 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1921 AND (md.lot_number IS NULL OR
1922 p_level = 2 OR
1923 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1924 WHERE mln.organization_id = p_org_id
1925 AND mln.lot_number = md.lot_number
1926 AND mln.inventory_item_id = p_item_id
1927 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
1928 /* nsinghi MIN-MAX INVCONV end */
1929
1930 IF G_TRACE_ON = 1 THEN
1931 print_debug('Reserved demand (internal orders): ' || to_char(l_total_reserve_qty)
1932 , 'get_demand_qty'
1933 , 9);
1934 END IF;
1935 END IF;
1936
1937 --
1938 -- total demand is calculated as follows:
1939 -- if we have to consider both unreserved matl and reserved matl. then the
1940 -- demand is simply the total demand = ordered qty - shipped qty.
1941 -- elsif we have to take into account only reserved matl. then the
1942 -- demand is simply the reservations from mtl_reservations for the matl.
1943 -- elsif we have to take into account just the unreserved matl. then the
1944 -- demand is total demand - the reservations for the material.
1945 --
1946 if p_net_unrsv = 1 and p_net_rsv = 1 then
1947 l_demand_qty := NVL(l_total_demand_qty,0);
1948
1949 elsif p_net_rsv = 1 then
1950 l_demand_qty := NVL(l_total_reserve_qty,0);
1951
1952 elsif p_net_unrsv = 1 then
1953 l_demand_qty := NVL(l_total_demand_qty,0) - NVL(l_total_reserve_qty,0);
1954 end if;
1955 IF G_TRACE_ON = 1 THEN
1956 print_debug('Demand from internal orders: ' || to_char(l_demand_qty)
1957 , 'get_demand_qty'
1958 , 9);
1959 END IF;
1960 total := total + NVL(l_demand_qty,0);
1961
1962 end if; -- end if level=1
1963
1964 --
1965 /* Bug 3364512. Demand is double for back-to-back sales orders after
1966 auto-create requisition and for sales orders with ATO items after
1967 auto-create WIP job. Commenting the below code which fetches duplicate
1968 demand */
1969
1970 -- WIP Reservations from mtl_demand
1971 --
1972 /* IF p_level = 1 THEN
1973 --
1974 -- SUBINVENTORY IS Always expected to be Null when Reservation_type is 3.
1975 --
1976 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1977 NVL(COMPLETED_QUANTITY,0)))
1978 into qty
1979 from mtl_demand
1980 where RESERVATION_TYPE = 3
1981 and ORGANIZATION_ID = p_org_id
1982 and PRIMARY_UOM_QUANTITY >
1983 GREATEST(NVL(RESERVATION_QUANTITY,0), NVL(COMPLETED_QUANTITY,0))
1984 and INVENTORY_ITEM_ID = p_item_id
1985 and REQUIREMENT_DATE <= p_d_cutoff
1986 and p_net_rsv = 1;
1987
1988 IF G_TRACE_ON = 1 THEN
1989 print_debug('WIP Reservations from mtl_demand: ' || to_char(qty)
1990 , 'get_demand_qty'
1991 , 9);
1992 END IF;
1993 total := total + NVL(qty,0);
1994 END IF; */
1995
1996 --
1997 -- Wip Components are to be included at the Org Level Planning only.
1998 -- Qty Issued Substracted from the Qty Required
1999 --
2000 if (p_net_wip = 1 and p_level = 1)
2001 then
2002
2003 /* nsinghi MIN-MAX INVCONV start */
2004
2005 IF p_process_org = 'Y' THEN
2006
2007 /* Here we need include the query to include OPM as source of demand.
2008 Since GME will always give the complete demand (including reserved demand)
2009 so subtracting the reserved demand as reserved demand will be considered
2010 above from mtl_reservations query. */
2011
2012
2013 SELECT
2014 SUM (INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( p_org_id
2015 , p_item_id
2016 , d.dtl_um
2017 , NVL(NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty, 0))-
2018 NVL(mtr.primary_reservation_quantity,0))
2019 INTO qty
2020 FROM gme_material_details d
2021 , gme_batch_header h
2022 , mtl_reservations mtr
2023 WHERE h.batch_type IN (0,10)
2024 AND h.batch_status IN (1,2)
2025 AND h.batch_id = d.batch_id
2026 AND d.line_type = -1
2027 -- AND NVL(d.original_qty, 0) <> 0 --commented as part of bug 8434499
2028 AND d.organization_id = p_org_id
2029 AND d.inventory_item_id = p_item_id
2030 AND d.batch_id = mtr.demand_source_header_id (+)
2031 AND d.material_detail_id = mtr.demand_source_line_id (+)
2032 AND d.inventory_item_id = mtr.inventory_item_id (+)
2033 AND d.organization_id = mtr.organization_id (+)
2034 AND (INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( p_org_id
2035 , p_item_id
2036 , d.dtl_um
2037 , NVL(NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty, 0))-
2038 NVL(mtr.primary_reservation_quantity,0)) > 0
2039 AND NVL(mtr.demand_source_type_id, 5) = 5
2040 AND d.material_requirement_date <= p_d_cutoff
2041 AND (mtr.subinventory_code IS NULL OR
2042 EXISTS (SELECT 1
2043 FROM mtl_secondary_inventories s
2044 WHERE s.organization_id = p_org_id
2045 AND s.secondary_inventory_name = mtr.subinventory_code
2046 AND s.availability_type = DECODE(p_include_nonnet,1,s.availability_type,1)))
2047 AND (mtr.locator_id IS NULL OR
2048 EXISTS (SELECT 1 FROM mtl_item_locations mil
2049 WHERE mil.organization_id = p_org_id
2050 AND mil.inventory_location_id = mtr.locator_id
2051 AND mil.subinventory_code = NVL(mtr.subinventory_code, mil.subinventory_code)
2052 AND mil.availability_type = DECODE(p_include_nonnet,1,mil.availability_type,1)))
2053 AND (mtr.lot_number IS NULL OR
2054 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
2055 WHERE mln.organization_id = p_org_id
2056 AND mln.lot_number = mtr.lot_number
2057 AND mln.inventory_item_id = p_item_id
2058 AND mln.availability_type = DECODE(p_include_nonnet,1,mln.availability_type,1)));
2059
2060 IF G_TRACE_ON = 1 THEN
2061 print_debug('Batch Material requirements for OPM Batches : ' || to_char(qty)
2062 , 'get_demand_qty'
2063 , 9);
2064 END IF;
2065 total := total + NVL(qty,0);
2066
2067 ELSE
2068 /* nsinghi MIN-MAX INVCONV end */
2069 /*4518296*/
2070
2071 select sum(o.required_quantity - o.quantity_issued)
2072 into qty
2073 from wip_discrete_jobs d, wip_requirement_operations o
2074 where o.wip_entity_id = d.wip_entity_id
2075 and o.organization_id = d.organization_id
2076 and d.organization_id = p_org_id
2077 and o.inventory_item_id = p_item_id
2078 and o.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2079 and o.required_quantity > 0
2080 and o.required_quantity > o.quantity_issued
2081 and o.operation_seq_num > 0
2082 and d.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
2083 and o.wip_supply_type not in (5,6); -- Included 5 from the selection Bug#4488415
2084
2085 IF G_TRACE_ON = 1 THEN
2086 print_debug('WIP component requirements for discrete jobs: ' || to_char(qty)
2087 , 'get_demand_qty'
2088 , 9);
2089 END IF;
2090 total := total + NVL(qty,0);
2091
2092 --
2093 -- Demand Qty to be added for a released repetitive schedule
2094 -- Bug#691471
2095 --
2096 /*4518296*/
2097 select sum(o.required_quantity - o.quantity_issued)
2098 into qty
2099 from wip_repetitive_schedules r, wip_requirement_operations o
2100 where o.wip_entity_id = r.wip_entity_id
2101 and o.repetitive_schedule_id = r.repetitive_schedule_id
2102 and o.organization_id = r.organization_id
2103 and r.organization_id = p_org_id
2104 and o.inventory_item_id = p_item_id
2105 and o.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2106 and o.required_quantity > 0
2107 and o.required_quantity > o.quantity_issued
2108 and o.operation_seq_num > 0
2109 and r.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
2110 and o.wip_supply_type not in (5,6); -- Included 5 from the selection Bug#4488415
2111 IF G_TRACE_ON = 1 THEN
2112 print_debug('WIP component requirements for repetitive schedules: ' || to_char(qty)
2113 , 'get_demand_qty'
2114 , 9);
2115 END IF;
2116 total := total + NVL(qty,0);
2117
2118 END IF; /* p_process_org = 'Y' */
2119
2120 end if;
2121
2122 --
2123 -- Include move orders:
2124 -- Leave out the closed or cancelled lines
2125 -- Select only Issue from Stores for org level planning
2126 -- Also select those lines for sub level planning.
2127 --
2128 -- Exclude move orders created for WIP Issue transaction
2129 -- (txn type = 35, INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE)
2130 -- since these are already taken into account (above) by
2131 -- directly querying the WIP tables for open component requirements
2132 --
2133
2134 -- kkoothan Part of Bug Fix: 2875583
2135 -- Converting the quantities to the primary uom as the quantity
2136 -- and quantity delivered in mtl_txn_request_lines
2137 -- are in transaction uom.
2138
2139 --Bug 3057273, Move order demand should be excluded if net unreserved demand is No.
2140 if p_net_unrsv = 1 then
2141
2142 /*SELECT SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED,0))
2143 INTO qty
2144 FROM MTL_TXN_REQUEST_LINES MTRL,
2145 MTL_TRANSACTION_TYPES MTT
2146 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
2147 AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2148 AND MTRL.ORGANIZATION_ID = p_org_id
2149 AND MTRL.INVENTORY_ITEM_ID = p_item_id
2150 AND MTRL.LINE_STATUS NOT IN (5,6)
2151 AND MTT.TRANSACTION_ACTION_ID = 1
2152 AND (p_level = 1 OR
2153 MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
2154 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
2155 p_level = 2 OR
2156 EXISTS (SELECT 1
2157 FROM MTL_SECONDARY_INVENTORIES S
2158 WHERE S.ORGANIZATION_ID = p_org_id
2159 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
2160 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
2161 1,S.AVAILABILITY_TYPE,1)))
2162 AND MTRL.DATE_REQUIRED <= p_d_cutoff;*/
2163
2164 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
2165 ,p_item_id
2166 , mtrl.uom_code
2167 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
2168 ),0)
2169 INTO qty
2170 FROM MTL_TXN_REQUEST_LINES MTRL,
2171 MTL_TRANSACTION_TYPES MTT
2172 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
2173 AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2174 AND MTRL.ORGANIZATION_ID = p_org_id
2175 AND MTRL.INVENTORY_ITEM_ID = p_item_id
2176 AND MTRL.LINE_STATUS IN (3,7)--Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
2177 AND MTT.TRANSACTION_ACTION_ID = 1
2178 AND (p_level = 1 OR
2179 MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
2180 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
2181 p_level = 2 OR
2182 EXISTS (SELECT 1
2183 FROM MTL_SECONDARY_INVENTORIES S
2184 WHERE S.ORGANIZATION_ID = p_org_id
2185 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
2186 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
2187 1,S.AVAILABILITY_TYPE,1)))
2188 AND mtrl.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2189 /* nsinghi MIN-MAX INVCONV start */
2190 AND (mtrl.from_locator_id IS NULL OR
2191 p_level = 2 OR
2192 EXISTS (SELECT 1 FROM mtl_item_locations mil
2193 WHERE mil.organization_id = p_org_id
2194 AND mil.inventory_location_id = mtrl.from_locator_id
2195 AND mil.subinventory_code = NVL(mtrl.from_subinventory_code, mil.subinventory_code)
2196 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
2197 AND (mtrl.lot_number IS NULL OR
2198 p_level = 2 OR
2199 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
2200 WHERE mln.organization_id = p_org_id
2201 AND mln.lot_number = mtrl.lot_number
2202 AND mln.inventory_item_id = p_item_id
2203 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
2204 /* nsinghi MIN-MAX INVCONV end */
2205
2206 IF G_TRACE_ON = 1 THEN
2207 print_debug('Demand from open move orders: ' || to_char(qty), 'get_demand_qty', 9);
2208 END IF;
2209
2210 total := total + NVL(qty,0);
2211
2212 end if;
2213
2214 --
2215 -- Include the sub transfer and the staging transfer move orders
2216 -- for sub level planning
2217 -- Bug 3057273, Move order demand should be excluded if net unreserved demand is No.
2218
2219 IF (p_level = 2 and p_net_unrsv = 1) THEN
2220 -- kkoothan Part of Bug Fix: 2875583
2221 -- Converting the quantities to the primary uom as the quantity
2222 -- and quantity delivered in mtl_txn_request_lines
2223 -- are in transaction uom.
2224
2225 /*SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
2226 INTO qty
2227 FROM mtl_transaction_types mtt,
2228 mtl_txn_request_lines mtrl
2229 WHERE mtt.transaction_action_id IN (2,28)
2230 AND mtt.transaction_type_id = mtrl.transaction_type_id
2231 AND mtrl.organization_id = p_org_id
2232 AND mtrl.inventory_item_id = p_item_id
2233 AND mtrl.from_subinventory_code = p_subinv
2234 AND mtrl.line_status NOT IN (5,6)
2235 AND mtrl.date_required <= p_d_cutoff;*/
2236
2237 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
2238 ,p_item_id
2239 ,mtrl.uom_code
2240 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
2241 ),0)
2242 INTO qty
2243 FROM mtl_transaction_types mtt,
2244 mtl_txn_request_lines mtrl
2245 WHERE mtt.transaction_action_id IN (2,28)
2246 AND mtt.transaction_type_id = mtrl.transaction_type_id
2247 AND mtrl.organization_id = p_org_id
2248 AND mtrl.inventory_item_id = p_item_id
2249 AND mtrl.from_subinventory_code = p_subinv
2250 AND MTRL.LINE_STATUS IN (3,7) --Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
2251 AND mtrl.date_required <= p_d_cutoff + 0.99999; /* bug no 6009682 */
2252
2253 IF G_TRACE_ON = 1 THEN
2254 print_debug('Qty pending out due to sub transfers and the staging transfer move orders: '
2255 || to_char(qty)
2256 , 'get_demand_qty'
2257 , 9);
2258 END IF;
2259 total := total + NVL(qty,0);
2260 END IF;
2261
2262 -- Bug 5041763 need to exclude drop ship reservation from on-hand qty to get correct availability
2263 select sum(PRIMARY_RESERVATION_QUANTITY)
2264 into qty
2265 from mtl_reservations
2266 WHERE ORGANIZATION_ID = p_org_id
2267 and INVENTORY_ITEM_ID = p_item_id
2268 and demand_source_type_id = 2
2269 and supply_source_type_id = 13
2270 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2271 and ((p_level = 1 ) OR
2272 SUBINVENTORY_CODE = p_subinv)
2273 and ( SUBINVENTORY_CODE is null or
2274 p_level = 2 or
2275 EXISTS (SELECT 1
2276 FROM MTL_SECONDARY_INVENTORIES S
2277 WHERE S.ORGANIZATION_ID = p_org_id
2278 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
2279 AND S.availability_type = DECODE(p_include_nonnet,
2280 1,
2281 S.availability_type,
2282 1)))
2283 and EXISTS (SELECT 1
2284 FROM OE_DROP_SHIP_SOURCES ODSS
2285 WHERE ODSS.LINE_ID = DEMAND_SOURCE_LINE_ID);
2286 total := total + NVL(qty,0);
2287 --Bug 6240025 BEGIN
2288 l_loaded_qty := get_loaded_qty(p_org_id
2289 , p_subinv
2290 , p_level
2291 , p_item_id
2292 , p_net_rsv
2293 , p_net_unrsv);
2294 total := total+NVL(l_loaded_qty,0);
2295 --Bug 6240025 END
2296 return(total);
2297
2298 exception
2299 when others then
2300 IF G_TRACE_ON = 1 THEN
2301 print_debug(sqlcode || ', ' || sqlerrm, 'get_demand_qty', 1);
2302 END IF;
2303 RAISE;
2304 end get_demand_qty;
2305
2306
2307
2308 FUNCTION get_shipped_qty( p_organization_id IN NUMBER
2309 , p_inventory_item_id IN NUMBER
2310 , p_order_line_id IN NUMBER) RETURN NUMBER IS
2311
2312 l_shipped_qty NUMBER := 0;
2313
2314 BEGIN
2315
2316 --
2317 -- Only look at source types 2 and 8 (sales orders, internal orders)
2318 --
2319 SELECT SUM(primary_quantity)
2320 INTO l_shipped_qty
2321 FROM mtl_material_transactions
2322 WHERE transaction_action_id = 1
2323 AND source_line_id = p_order_line_id
2324 AND organization_id = p_organization_id
2325 AND inventory_item_id = p_inventory_item_id
2326 AND transaction_source_type_id in (2,8);
2327
2328 IF l_shipped_qty IS NULL THEN
2329 l_shipped_qty := 0;
2330 ELSE
2331 l_shipped_qty := -1 * l_shipped_qty;
2332 END IF;
2333
2334 RETURN l_shipped_qty;
2335
2336 END get_shipped_qty;
2337
2338
2339
2340 FUNCTION get_staged_qty( p_org_id NUMBER
2341 , p_subinv VARCHAR2
2342 , p_item_id NUMBER
2343 , p_order_line_id NUMBER
2344 , p_include_nonnet NUMBER) RETURN NUMBER IS
2345
2346 l_staged_qty NUMBER := 0;
2347
2348 BEGIN
2349
2350 BEGIN
2351 --
2352 -- Bugfix 2333526: Need to calculate staged quantity
2353 -- for sub level planning. If passed-in (planning)
2354 -- sub is the also the staging sub, then ignore
2355 -- p_include_nonnet
2356 --
2357 SELECT NVL(SUM(primary_reservation_quantity),0)
2358 INTO l_staged_qty
2359 FROM mtl_reservations
2360 WHERE organization_id = p_org_id
2361 AND inventory_item_id = p_item_id
2362 AND demand_source_line_id = p_order_line_id
2363 AND demand_source_type_id IN (2,8,12)
2364 AND NVL(staged_flag, 'X') = 'Y'
2365 AND subinventory_code IS NOT NULL
2366 AND subinventory_code <> p_subinv; -- Bug 4313204
2367
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 l_staged_qty := 0;
2371 END;
2372
2373 RETURN l_staged_qty;
2374
2375 END get_staged_qty;
2376
2377
2378
2379 FUNCTION get_pick_released_qty( p_org_id NUMBER
2380 , p_subinv VARCHAR2
2381 , p_item_id NUMBER
2382 , p_order_line_id NUMBER) RETURN NUMBER IS
2383
2384 l_pick_released_qty NUMBER := 0;
2385
2386 BEGIN
2387
2388 BEGIN
2389 --
2390 -- Move order type 3 is pick wave, source type 2 is sales order
2391 -- Bug 3181367 added transaction_source_type_id 8 too.
2392 SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
2393 INTO l_pick_released_qty
2394 FROM mtl_txn_request_headers mtrh,
2395 mtl_txn_request_lines mtrl
2396 WHERE mtrh.move_order_type = 3
2397 AND mtrh.header_id = mtrl.header_id
2398 AND mtrl.organization_id = p_org_id
2399 AND mtrl.inventory_item_id = p_item_id
2400 AND mtrl.from_subinventory_code = p_subinv
2401 AND mtrl.txn_source_line_id = p_order_line_id
2402 AND mtrl.transaction_source_type_id in (2,8)
2403 AND mtrl.line_status NOT IN (5,6);
2404
2405 EXCEPTION
2406 WHEN OTHERS THEN
2407 l_pick_released_qty := 0;
2408 END;
2409
2410 RETURN l_pick_released_qty;
2411
2412 END get_pick_released_qty;
2413
2414 FUNCTION get_reord_qty( p_min_qty NUMBER
2415 , p_max_qty NUMBER
2416 , p_min_ord_qty NUMBER
2417 , p_max_ord_qty NUMBER
2418 , p_tot_avail_qty NUMBER
2419 , p_fix_mult NUMBER) RETURN NUMBER IS
2420
2421 l_min_qty NUMBER;
2422 l_max_qty NUMBER;
2423 l_min_ord_qty NUMBER;
2424 l_fix_mult NUMBER;
2425
2426 l_reorder NUMBER;
2427 l_min_restock_qty NUMBER;
2428 l_qty_for_last_order NUMBER;
2429 l_round_reord_qty VARCHAR2(1);
2430
2431 BEGIN
2432 IF G_TRACE_ON = 1 THEN
2433 print_debug('p_min_qty: ' || to_char(p_min_qty) ||
2434 ', p_max_qty: ' || to_char(p_max_qty) ||
2435 ', p_min_ord_qty: ' || to_char(p_min_ord_qty) ||
2436 ', p_max_ord_qty: ' || to_char(p_max_ord_qty) ||
2437 ', p_tot_avail_qty: ' || to_char(p_tot_avail_qty) ||
2438 ', p_fix_mult: ' || to_char(p_fix_mult)
2439 , 'get_reord_qty'
2440 , 9);
2441 END IF;
2442
2443
2444 /* GENERAL ALGORITHM:
2445
2446 When to order?
2447 When total available < minimum for item
2448
2449 How much to order?
2450 reorder qty = max stockable qty - total available
2451
2452 If reorder qty < min ord qty, increase reorder qty to min ord qty
2453
2454 If a fixed lot multiple is defined
2455 Round the reorder up or down based on profile INV_ROUND_REORDER_QTY
2456
2457 If a max ord qty is not specified
2458 or if reorder qty < max ord qty, no changes required
2459
2460 If max ord qty is specified
2461 and reorder qty exceeds max ord qty:
2462
2463 We need to make sure that after creating one or more orders
2464 for max order qty, the remaining quantity exceeds min ord qty
2465
2466 For e.g.:
2467 reorder qty = 34
2468 max ord qty = 10
2469 min ord qty = 5
2470
2471 Then restocking code will create 3 orders (move orders, requisitions
2472 or work orders) for 10 each, which is 30. The left over qty is
2473 34 - 30 = 4. Since the min ord qty is 5, we should discard the
2474 remaining qty of 4. If the remaining qty was say 8, then the last
2475 move order/requisition/work order would be for qty 8, and so on.
2476
2477 If no min ord qty is specified (or if it is 0) then this downward
2478 adjustment is not required.
2479 end if;
2480 */
2481
2482
2483
2484 l_min_qty := NVL(p_min_qty,0);
2485 l_max_qty := NVL(p_max_qty,0);
2486 l_min_ord_qty := NVL(p_min_ord_qty,0);
2487 l_fix_mult := NVL(p_fix_mult,0);
2488
2489 IF p_tot_avail_qty >= l_min_qty
2490 THEN
2491 RETURN 0;
2492 END if;
2493
2494 l_reorder := l_max_qty - p_tot_avail_qty;
2495
2496 IF G_TRACE_ON = 1 THEN
2497 print_debug('Initial estimated reorder qty: ' || to_char(l_reorder)
2498 , 'get_reord_qty'
2499 , 9);
2500 END IF;
2501
2502 IF l_min_ord_qty >= l_reorder
2503 THEN
2504 RETURN l_min_ord_qty;
2505 END if;
2506
2507 IF l_fix_mult > 0
2508 THEN
2509 l_round_reord_qty := NVL(FND_PROFILE.VALUE('INV_ROUND_REORDER_QTY'), 'Y');
2510
2511 IF G_TRACE_ON = 1 THEN
2512 print_debug('l_round_reord_qty: ' || l_round_reord_qty, 'get_reord_qty', 9);
2513 END IF;
2514
2515 IF l_round_reord_qty = 'N'
2516 THEN
2517 l_reorder := floor(l_reorder/l_fix_mult) * l_fix_mult;
2518 ELSE
2519 l_reorder := ceil(l_reorder/l_fix_mult) * l_fix_mult;
2520 END if;
2521
2522 IF G_TRACE_ON = 1 THEN
2523 print_debug('Reorder qty after applying fix lot multiple: '
2524 || to_char(l_reorder)
2525 , 'get_reord_qty'
2526 , 9);
2527 END IF;
2528 END if;
2529
2530 IF p_max_ord_qty IS NULL OR l_reorder <= p_max_ord_qty
2531 THEN
2532 RETURN l_reorder;
2533 ELSIF p_max_ord_qty > 0
2534 THEN
2535 l_min_restock_qty := floor(l_reorder/p_max_ord_qty) * p_max_ord_qty;
2536 l_qty_for_last_order := l_reorder - l_min_restock_qty;
2537
2538 IF G_TRACE_ON = 1 THEN
2539 print_debug('Min reord qty that is a multiple of max ord qty: '
2540 || to_char(l_min_restock_qty)
2541 , 'get_reord_qty'
2542 , 9);
2543 END IF;
2544
2545 IF l_qty_for_last_order >= l_min_ord_qty
2546 THEN
2547 RETURN l_reorder;
2548 ELSE
2549 RETURN l_min_restock_qty;
2550 END IF;
2551 END if;
2552
2553 RETURN l_reorder;
2554
2555 EXCEPTION
2556 WHEN OTHERS THEN
2557 IF G_TRACE_ON = 1 THEN
2558 print_debug(sqlcode || ', ' || sqlerrm, 'get_reord_qty', 1);
2559 END IF;
2560 RAISE;
2561 END get_reord_qty;
2562
2563
2564 --
2565 -- Added a new parameter p_cust_site_id for Patchset I Enhancement
2566 -- Min Max Leadtime Enhancement.
2567 --
2568 FUNCTION get_reord_stat ( p_restock NUMBER
2569 , p_handle_rep_item NUMBER
2570 , p_level NUMBER
2571 , p_reord_qty NUMBER
2572 , p_wip_batch_id NUMBER
2573 , p_org_id NUMBER
2574 , p_subinv VARCHAR2
2575 , p_user_id NUMBER
2576 , p_employee_id NUMBER
2577 , p_sysdate DATE
2578 , p_approval NUMBER
2579 , p_encum_flag VARCHAR2
2580 , p_cust_id NUMBER
2581 , p_cust_site_id NUMBER
2582 , p_cal_code VARCHAR2
2583 , p_exception_set_id NUMBER
2584 , p_dd_loc_id NUMBER
2585 , p_po_org_id NUMBER
2586 , p_pur_revision NUMBER
2587 , p_item_rec minmax_items_rectype
2588 , p_osfm_batch_id NUMBER DEFAULT NULL /* Added for Bug 6807835 */
2589 ) RETURN VARCHAR2 IS
2590
2591 v_make_buy_flag NUMBER;
2592 l_error_message VARCHAR2(100);
2593 l_ret_stat VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2594 l_reorder_qty NUMBER;
2595 l_move_ord_qty NUMBER;
2596
2597 BEGIN
2598 IF G_TRACE_ON = 1 THEN
2599 print_debug('p_restock: ' || to_char(p_restock) ||
2600 ', p_handle_rep_item: ' || to_char(p_handle_rep_item) ||
2601 ', p_level; ' || to_char(p_level) ||
2602 ', p_reord_qty: ' || to_char(p_reord_qty) ||
2603 ', p_wip_batch_id: ' || to_char(p_wip_batch_id) ||
2604 ', p_org_id: ' || to_char(p_org_id) ||
2605 ', p_subinv: ' || p_subinv ||
2606 ', p_user_id: ' || to_char(p_user_id) ||
2607 ', p_employee_id: ' || to_char(p_employee_id) ||
2608 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS')
2609 , 'get_reord_stat'
2610 , 9);
2611
2612 print_debug('p_approval: ' || to_char(p_approval) ||
2613 ', p_encum_flag: ' || p_encum_flag ||
2614 ', p_cust_id: ' || to_char(p_cust_id) ||
2615 ', p_cust_site_id: ' || to_char(p_cust_site_id) ||
2616 ', p_cal_code: ' || p_cal_code ||
2617 ', p_exception_set_id: ' || to_char(p_exception_set_id) ||
2618 ', p_dd_loc_id: ' || to_char(p_dd_loc_id) ||
2619 ', p_po_org_id: ' || to_char(p_po_org_id) ||
2620 ', p_pur_revision: ' || to_char(p_pur_revision) ||
2621 ', p_item_rec: ' || to_char(p_item_rec.item_id) ||
2622 ', p_osfm_batch_id: ' || to_char(p_osfm_batch_id)
2623 , 'get_reord_stat'
2624 , 9);
2625 END IF;
2626
2627 -- kkoothan fix for Bug 2661176,3020869
2628 -- If the item is a repetitive item and the user chose not to restock
2629 -- repetitive items, or if the planning level is "Org" and source type
2630 -- is subinventory (3) do not restock - but pass some meaningful messages
2631 -- which would be printed on the report output as :
2632 -- "Cannot create move orders for organization level planning"
2633 -- or "Repetitive Planning with Do Not Restock Option Chosen" respectively.
2634 --
2635 -- Restocking with source type sub will result in a move order and this
2636 -- only makes sense for sub level planning.
2637 --
2638 -- For sub level planning, always set the make_or_buy flag to "buy",
2639 -- i.e., do not create a work order for sub level planning.
2640 --
2641 IF p_restock = 1 THEN
2642 BEGIN
2643 IF G_TRACE_ON = 1 THEN
2644 print_debug('Item Source Type and Make or Buy Flag value: '|| p_item_rec.src_type ||' and '|| p_item_rec.mbf
2645 , 'get_reord_stat'
2646 , 9);
2647 END IF;
2648 IF (p_item_rec.repetitive_planned_item = 'Y' AND p_handle_rep_item = 3) THEN
2649 IF G_TRACE_ON = 1 THEN
2650 print_debug('For a repetitive item, Handle Repetitive Item parameter in the report has been chosen as Do Not Restock(Report Only)'
2651 , 'get_reord_stat'
2652 , 9);
2653 END IF;
2654 SELECT meaning
2655 INTO l_error_message
2656 FROM mfg_lookups
2657 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2658 AND lookup_code = 7;
2659 RETURN(l_error_message);
2660 ELSIF (p_level = 1 AND p_item_rec.src_type = 3 AND p_item_rec.mbf = 2) THEN
2661 IF G_TRACE_ON = 1 THEN
2662 print_debug('In Organization level planning, Source type for this min max item has been set up as ''Subinventory'''
2663 , 'get_reord_stat'
2664 , 9);
2665 END IF;
2666 SELECT meaning
2667 INTO l_error_message
2668 FROM mfg_lookups
2669 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2670 AND lookup_code = 6;
2671 RETURN(l_error_message);
2672 ELSE
2673 IF p_level = 2 THEN
2674 v_make_buy_flag := 2;
2675 ELSE
2676 v_make_buy_flag := p_item_rec.mbf;
2677 END IF;
2678 END IF;
2679 EXCEPTION
2680 WHEN no_data_found THEN
2681 RETURN('');
2682 END;
2683
2684 ELSE
2685 RETURN ('');
2686 END IF;
2687
2688 l_reorder_qty := NVL(p_reord_qty,0);
2689
2690 WHILE (l_reorder_qty > 0)
2691 LOOP
2692 IF NVL(p_item_rec.max_ord_qty,0) = 0
2693 THEN
2694 l_move_ord_qty := l_reorder_qty;
2695 ELSIF (l_reorder_qty > p_item_rec.max_ord_qty)
2696 THEN
2697 l_move_ord_qty := p_item_rec.max_ord_qty;
2698 ELSE
2699 l_move_ord_qty := l_reorder_qty;
2700 END IF;
2701
2702 do_restock( p_item_id => p_item_rec.item_id
2703 , p_mbf => v_make_buy_flag
2704 , p_handle_repetitive_item => p_handle_rep_item
2705 , p_repetitive_planned_item => p_item_rec.repetitive_planned_item
2706 , p_qty => l_move_ord_qty
2707 , p_fixed_lead_time => p_item_rec.fixed_lead_time
2708 , p_variable_lead_time => p_item_rec.variable_lead_time
2709 , p_buying_lead_time => p_item_rec.buying_lead_time
2710 , p_uom => p_item_rec.primary_uom
2711 , p_accru_acct => p_item_rec.accru_acct
2712 , p_ipv_acct => p_item_rec.ipv_acct
2713 , p_budget_acct => p_item_rec.budget_acct
2714 , p_charge_acct => p_item_rec.charge_acct
2715 , p_purch_flag => p_item_rec.purch_flag
2716 , p_order_flag => p_item_rec.order_flag
2717 , p_transact_flag => p_item_rec.transact_flag
2718 , p_unit_price => p_item_rec.unit_price
2719 , p_wip_id => p_wip_batch_id
2720 , p_user_id => p_user_id
2721 , p_sysd => p_sysdate
2722 , p_organization_id => p_org_id
2723 , p_approval => p_approval
2724 , p_build_in_wip => p_item_rec.build_in_wip
2725 , p_pick_components => p_item_rec.pick_components
2726 , p_src_type => p_item_rec.src_type
2727 , p_encum_flag => p_encum_flag
2728 , p_customer_id => p_cust_id
2729 , p_customer_site_id => p_cust_site_id
2730 , p_cal_code => p_cal_code
2731 , p_except_id => p_exception_set_id
2732 , p_employee_id => p_employee_id
2733 , p_description => p_item_rec.description
2734 , p_src_org => TO_NUMBER(p_item_rec.src_org)
2735 , p_src_subinv => p_item_rec.src_subinv
2736 , p_subinv => p_subinv
2737 , p_location_id => p_dd_loc_id
2738 , p_po_org_id => p_po_org_id
2739 , p_pur_revision => p_pur_revision
2740 /* nsinghi MIN-MAX INVCONV start */
2741 , p_execution_enabled => p_item_rec.execution_enabled
2742 , p_recipe_enabled => p_item_rec.recipe_enabled
2743 , p_process_enabled => p_item_rec.process_enabled
2744 /* nsinghi MIN-MAX INVCONV end */
2745 , x_ret_stat => l_ret_stat
2746 , x_ret_mesg => l_error_message
2747 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
2748 );
2749
2750 IF l_ret_stat <> FND_API.G_RET_STS_SUCCESS
2751 THEN
2752 IF G_TRACE_ON = 1 THEN
2753 print_debug('do_restock returned message: ' || l_error_message
2754 , 'get_reord_stat'
2755 , 9);
2756 END IF;
2757 RETURN(l_error_message);
2758 END IF;
2759
2760 l_reorder_qty := l_reorder_qty - l_move_ord_qty;
2761 END LOOP;
2762
2763 RETURN(''); /*bug2838809*/
2764
2765 EXCEPTION
2766 WHEN others THEN
2767 IF G_TRACE_ON = 1 THEN
2768 print_debug(sqlcode || ', ' || sqlerrm, 'get_reord_stat', 1);
2769 END IF;
2770 RAISE;
2771 end get_reord_stat;
2772
2773
2774 --
2775 -- Min Max Lead time Enhancement.
2776 --
2777
2778 PROCEDURE get_intransit_time(
2779 x_return_status OUT NOCOPY VARCHAR2
2780 , x_msg_count OUT NOCOPY NUMBER
2781 , x_msg_data OUT NOCOPY VARCHAR2
2782 , x_intransit_time OUT NOCOPY NUMBER
2783 , x_scheduled_ship_date OUT NOCOPY DATE
2784 , p_organization_id IN NUMBER
2785 , p_subinv IN VARCHAR2
2786 , p_to_customer_site_id IN NUMBER
2787 , p_src_org IN NUMBER
2788 , p_src_subinv IN VARCHAR2
2789 , p_item_id IN NUMBER
2790 , p_sourcing_date IN DATE
2791 ) IS
2792 l_proc_name CONSTANT VARCHAR2(30) := 'GET_INTRANSIT_TIME';
2793 l_return_status VARCHAR2(1);
2794 l_msg_count NUMBER;
2795 l_msg_data VARCHAR2(240);
2796 l_return BOOLEAN;
2797 l_session_id NUMBER;
2798 l_src_org NUMBER;
2799 l_src_subinv VARCHAR2(10);
2800 l_ship_method VARCHAR2(30);
2801 l_src_rule_id NUMBER;
2802 l_ven_site_id NUMBER;
2803 l_ven_id NUMBER;
2804 l_mode VARCHAR2(20) := 'INVENTORY';
2805 l_from_location_id NUMBER;
2806 l_intransit_time NUMBER;
2807 l_so_cal_code VARCHAR2(10);
2808 l_so_exception_set_id NUMBER;
2809
2810 BEGIN
2811 SAVEPOINT sp_get_intransit_time;
2812 l_return_status := FND_API.G_RET_STS_SUCCESS;
2813 IF G_TRACE_ON = 1 THEN
2814 print_debug('Executing get_intransit_time with the following parameters:' || fnd_global.local_chr(10) ||
2815 ' p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10) ||
2816 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
2817 ', p_customer_site_id: ' || to_char(p_to_customer_site_id)|| fnd_global.local_chr(10) ||
2818 ', p_src_org: ' || to_char(p_src_org) || fnd_global.local_chr(10) ||
2819 ', p_src_subinv: ' || p_src_subinv || fnd_global.local_chr(10) ||
2820 ', p_item_id: ' || to_char(p_item_id) || fnd_global.local_chr(10) ||
2821 ', p_sourcing_date : ' || to_char(p_sourcing_date) || fnd_global.local_chr(10)
2822 , l_proc_name
2823 , 9);
2824 END IF;
2825
2826 -- Include intransit time also along with pre-processing and processing lead times for Sourcing date
2827 -- while determining the need-by date for internal requisitions.
2828 -- IF (any one of (src_org,src_subinv) is null)
2829 -- 1. Call MRP API to find src org and src sub, passing in p_sourcing_date as arg_autosource_date
2830 -- END IF;
2831 -- Calculate intransit time by calling Planning API passing in ship-from location ID
2832 -- and ship-to location ID (p_location_id).
2833 --
2834 l_src_org := p_src_org;
2835 l_src_subinv := p_src_subinv;
2836
2837 IF (p_src_org IS NULL OR p_src_subinv IS NULL) THEN
2838 IF G_TRACE_ON = 1 THEN
2839 print_debug('Calling MRP_SOURCING_API_PK.mrp_sourcing'
2840 , l_proc_name
2841 , 9);
2842 END IF;
2843 l_return := MRP_SOURCING_API_PK.mrp_sourcing
2844 ( arg_mode => l_mode
2845 , arg_item_id => p_item_id
2846 , arg_commodity_id => NULL
2847 , arg_dest_organization_id => p_organization_id
2848 , arg_dest_subinventory => p_subinv
2849 , arg_autosource_date => p_sourcing_date
2850 , arg_vendor_id => l_ven_id
2851 , arg_vendor_site_id => l_ven_site_id
2852 , arg_source_organization_id=> l_src_org
2853 , arg_source_subinventory => l_src_subinv
2854 , arg_sourcing_rule_id => l_src_rule_id
2855 , arg_error_message => l_msg_data
2856 ) ;
2857 IF NOT l_return THEN
2858 IF G_TRACE_ON = 1 THEN
2859 print_debug('MRP_SOURCING_API_PK.mrp_sourcing failed with error '|| l_msg_data
2860 , l_proc_name
2861 , 9);
2862 END IF;
2863 RAISE fnd_api.g_exc_error;
2864 ELSE
2865 IF G_TRACE_ON = 1 THEN
2866 print_debug('MRP_SOURCING_API_PK.mrp_sourcing returned success with Source Sub and Source Org '|| fnd_global.local_chr(10) || l_src_subinv || ' and '|| l_src_org
2867 , l_proc_name
2868 , 9);
2869 END IF;
2870 END IF;
2871 END IF; -- any one of (src_org,src_subinv) is null
2872
2873 --
2874 -- Calculate the Schedlued Ship Date based on Shipping Org's Calendar.
2875 --
2876
2877 BEGIN
2878 SELECT p.calendar_code, p.calendar_exception_set_id
2879 INTO l_so_cal_code, l_so_exception_set_id
2880 FROM mtl_parameters p
2881 WHERE p.organization_id = l_src_org;
2882
2883 SELECT c1.calendar_date
2884 INTO x_scheduled_ship_date
2885 FROM bom_calendar_dates c1,
2886 bom_calendar_dates c
2887 WHERE c1.calendar_code = c.calendar_code
2888 AND c1.exception_set_id = c.exception_set_id
2889 AND c1.seq_num = c.next_seq_num
2890 AND c.calendar_code = l_so_cal_code
2891 AND c.exception_set_id = l_so_exception_set_id
2892 AND c.calendar_date = trunc(p_sourcing_date);
2893 EXCEPTION
2894 WHEN no_data_found THEN
2895 IF G_TRACE_ON = 1 THEN
2896 print_debug('Exception: Organization '||l_src_org ||' is not defined'
2897 , l_proc_name
2898 , 9);
2899 END IF;
2900 RAISE fnd_api.g_exc_error;
2901 END;
2902
2903 --
2904 -- Get the Location associated with the Source Subinventory.
2905 --
2906 IF l_src_subinv IS NOT NULL THEN
2907 BEGIN
2908 SELECT LOCATION_ID
2909 INTO l_from_location_id
2910 FROM MTL_SECONDARY_INVENTORIES
2911 WHERE SECONDARY_INVENTORY_NAME = l_src_subinv
2912 AND ORGANIZATION_ID = l_src_org ;
2913 EXCEPTION
2914 WHEN no_data_found THEN
2915 IF G_TRACE_ON = 1 THEN
2916 print_debug('Exception: Subinventory '|| l_src_subinv || ' does not exist in the Organization '|| l_src_org
2917 , l_proc_name
2918 , 9);
2919 END IF;
2920 RAISE fnd_api.g_exc_error;
2921 END;
2922 END IF;
2923
2924 --
2925 -- Get the Default value for Delivery To Location for the Souce Org .
2926 -- If a source subinventory is specified and has a location ID associated,
2927 -- use the source subinventory's location ID instead of the source organization's location ID.
2928 --
2929 IF l_from_location_id IS NULL THEN
2930 BEGIN
2931 SELECT LOC.LOCATION_ID
2932 INTO l_from_location_id
2933 FROM HR_ORGANIZATION_UNITS ORG,HR_LOCATIONS LOC
2934 WHERE ORG.ORGANIZATION_ID = l_src_org
2935 AND ORG.LOCATION_ID = LOC.LOCATION_ID;
2936 EXCEPTION
2937 WHEN no_data_found THEN
2938 IF G_TRACE_ON = 1 THEN
2939 print_debug('Exception: No Source Location Exists for the Organization '|| l_src_org
2940 , l_proc_name
2941 , 9);
2942 END IF;
2943 RAISE fnd_api.g_exc_error;
2944 END;
2945 END IF;
2946 IF G_TRACE_ON = 1 THEN
2947 print_debug('From Location Id is: ' || l_from_location_id
2948 , l_proc_name
2949 , 9);
2950 END IF;
2951
2952
2953 BEGIN
2954 SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
2955 INTO l_session_id
2956 FROM SYS.DUAL;
2957 EXCEPTION
2958 WHEN no_data_found THEN
2959 IF G_TRACE_ON = 1 THEN
2960 print_debug('Exception: MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL is not defined'
2961 , l_proc_name
2962 , 9);
2963 END IF;
2964 RAISE fnd_api.g_exc_error;
2965 END;
2966 IF G_TRACE_ON = 1 THEN
2967 print_debug('Calling MSC_ATP_PROC.ATP_Shipping_Lead_Time with session Id:'||l_session_id
2968 , l_proc_name
2969 , 9);
2970 END IF;
2971
2972 MSC_SCH_WB.set_session_id(l_session_id);
2973 MSC_ATP_PROC.ATP_Shipping_Lead_Time (p_from_loc_id => l_from_location_id
2974 ,p_to_customer_site_id => p_to_customer_site_id
2975 ,p_session_id => l_session_id
2976 ,x_ship_method => l_ship_method
2977 ,x_intransit_time => l_intransit_time
2978 ,x_return_status => l_return_status
2979 );
2980 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2981 IF G_TRACE_ON = 1 THEN
2982 print_debug('MSC_ATP_PROC.ATP_Shipping_Lead_Time failed with unexpected error returning message: ' || l_msg_data
2983 , l_proc_name
2984 , 9);
2985 END IF;
2986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2987 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2988 IF G_TRACE_ON = 1 THEN
2989 print_debug('MSC_ATP_PROC.ATP_Shipping_Lead_Time failed with expected error returning message: ' || l_msg_data
2990 , l_proc_name
2991 , 9);
2992 END IF;
2993 RAISE FND_API.G_EXC_ERROR;
2994 ELSE
2995 x_intransit_time := NVL(l_intransit_time,0) ;
2996 IF G_TRACE_ON = 1 THEN
2997 print_debug('MSC_ATP_PROC.ATP_Shipping_Lead_Time returned success with Intransit Time '|| l_intransit_time
2998 , l_proc_name
2999 , 9);
3000 END IF;
3001 END IF;
3002 EXCEPTION
3003 WHEN fnd_api.g_exc_error THEN
3004 ROLLBACK TO sp_get_intransit_time;
3005 x_return_status := fnd_api.g_ret_sts_error;
3006 fnd_msg_pub.count_and_get
3007 ( p_count => x_msg_count,
3008 p_data => x_msg_data
3009 );
3010
3011 WHEN fnd_api.g_exc_unexpected_error THEN
3012 ROLLBACK TO sp_get_intransit_time;
3013 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3014 fnd_msg_pub.count_and_get
3015 ( p_count => x_msg_count,
3016 p_data => x_msg_data
3017 );
3018
3019 WHEN OTHERS THEN
3020 ROLLBACK TO sp_get_intransit_time;
3021 x_return_status := fnd_api.g_ret_sts_unexp_error;
3022 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)THEN
3023 fnd_msg_pub.add_exc_msg
3024 ( G_PKG_NAME
3025 ,l_proc_name
3026 );
3027 END IF;
3028 fnd_msg_pub.count_and_get
3029 ( p_count => x_msg_count,
3030 p_data => x_msg_data
3031 );
3032 END get_intransit_time;
3033
3034 /* nsinghi MIN-MAX INVCONV start */
3035 /* overloaded do_restock procedure. For process orgs , the overloaded procedure will be called
3036 directly, whereas, the exiting code can make call to original do_restock procedure. */
3037
3038 PROCEDURE do_restock( p_item_id IN NUMBER
3039 , p_mbf IN NUMBER
3040 , p_handle_repetitive_item IN NUMBER
3041 , p_repetitive_planned_item IN VARCHAR2
3042 , p_qty IN NUMBER
3043 , p_fixed_lead_time IN NUMBER
3044 , p_variable_lead_time IN NUMBER
3045 , p_buying_lead_time IN NUMBER
3046 , p_uom IN VARCHAR2
3047 , p_accru_acct IN NUMBER
3048 , p_ipv_acct IN NUMBER
3049 , p_budget_acct IN NUMBER
3050 , p_charge_acct IN NUMBER
3051 , p_purch_flag IN VARCHAR2
3052 , p_order_flag IN VARCHAR2
3053 , p_transact_flag IN VARCHAR2
3054 , p_unit_price IN NUMBER
3055 , p_wip_id IN NUMBER
3056 , p_user_id IN NUMBER
3057 , p_sysd IN DATE
3058 , p_organization_id IN NUMBER
3059 , p_approval IN NUMBER
3060 , p_build_in_wip IN VARCHAR2
3061 , p_pick_components IN VARCHAR2
3062 , p_src_type IN NUMBER
3063 , p_encum_flag IN VARCHAR2
3064 , p_customer_id IN NUMBER
3065 , p_customer_site_id IN NUMBER
3066 , p_cal_code IN VARCHAR2
3067 , p_except_id IN NUMBER
3068 , p_employee_id IN NUMBER
3069 , p_description IN VARCHAR2
3070 , p_src_org IN NUMBER
3071 , p_src_subinv IN VARCHAR2
3072 , p_subinv IN VARCHAR2
3073 , p_location_id IN NUMBER
3074 , p_po_org_id IN NUMBER
3075 , p_pur_revision IN NUMBER
3076 , x_ret_stat OUT NOCOPY VARCHAR2
3077 , x_ret_mesg OUT NOCOPY VARCHAR2
3078 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
3079 ) IS
3080
3081 l_recipe_enabled mtl_system_items.recipe_enabled_flag%TYPE;
3082 l_execution_enabled mtl_system_items.process_execution_enabled_flag%TYPE;
3083 l_process_enabled mtl_parameters.process_enabled_flag%TYPE;
3084
3085 BEGIN
3086
3087 l_recipe_enabled := 'N';
3088 l_execution_enabled := 'N';
3089 l_process_enabled := 'N';
3090
3091 SELECT NVL(process_enabled_flag,'N') INTO l_process_enabled
3092 FROM mtl_parameters
3093 WHERE organization_id = p_organization_id;
3094
3095 IF l_process_enabled = 'Y' THEN
3096 SELECT NVL(recipe_enabled_flag, 'N'), NVL(process_execution_enabled_flag, 'N')
3097 INTO l_recipe_enabled, l_execution_enabled
3098 FROM mtl_system_items
3099 WHERE organization_id = p_organization_id
3100 AND inventory_item_id = p_item_id;
3101 END IF;
3102
3103 do_restock( p_item_id => p_item_id
3104 , p_mbf => p_mbf
3105 , p_handle_repetitive_item => p_handle_repetitive_item
3106 , p_repetitive_planned_item => p_repetitive_planned_item
3107 , p_qty => p_qty
3108 , p_fixed_lead_time => p_fixed_lead_time
3109 , p_variable_lead_time => p_variable_lead_time
3110 , p_buying_lead_time => p_buying_lead_time
3111 , p_uom => p_uom
3112 , p_accru_acct => p_accru_acct
3113 , p_ipv_acct => p_ipv_acct
3114 , p_budget_acct => p_budget_acct
3115 , p_charge_acct => p_charge_acct
3116 , p_purch_flag => p_purch_flag
3117 , p_order_flag => p_order_flag
3118 , p_transact_flag => p_transact_flag
3119 , p_unit_price => p_unit_price
3120 , p_wip_id => p_wip_id
3121 , p_user_id => p_user_id
3122 , p_sysd => p_sysd
3123 , p_organization_id => p_organization_id
3124 , p_approval => p_approval
3125 , p_build_in_wip => p_build_in_wip
3126 , p_pick_components => p_pick_components
3127 , p_src_type => p_src_type
3128 , p_encum_flag => p_encum_flag
3129 , p_customer_id => p_customer_id
3130 , p_customer_site_id => p_customer_site_id
3131 , p_cal_code => p_cal_code
3132 , p_except_id => p_except_id
3133 , p_employee_id => p_employee_id
3134 , p_description => p_description
3135 , p_src_org => p_src_org
3136 , p_src_subinv => p_src_subinv
3137 , p_subinv => p_subinv
3138 , p_location_id => p_location_id
3139 , p_po_org_id => p_po_org_id
3140 , p_pur_revision => p_pur_revision
3141 /* calling the overloaded procedure call with 'No' for process parameters. */
3142 , p_execution_enabled => l_execution_enabled
3143 , p_recipe_enabled => l_recipe_enabled
3144 , p_process_enabled => l_process_enabled
3145 , x_ret_stat => x_ret_stat
3146 , x_ret_mesg => x_ret_mesg
3147 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
3148 );
3149
3150 END do_restock;
3151 /* nsinghi MIN-MAX INVCONV end */
3152
3153 PROCEDURE do_restock( p_item_id IN NUMBER
3154 , p_mbf IN NUMBER
3155 , p_handle_repetitive_item IN NUMBER
3156 , p_repetitive_planned_item IN VARCHAR2
3157 , p_qty IN NUMBER
3158 , p_fixed_lead_time IN NUMBER
3159 , p_variable_lead_time IN NUMBER
3160 , p_buying_lead_time IN NUMBER
3161 , p_uom IN VARCHAR2
3162 , p_accru_acct IN NUMBER
3163 , p_ipv_acct IN NUMBER
3164 , p_budget_acct IN NUMBER
3165 , p_charge_acct IN NUMBER
3166 , p_purch_flag IN VARCHAR2
3167 , p_order_flag IN VARCHAR2
3168 , p_transact_flag IN VARCHAR2
3169 , p_unit_price IN NUMBER
3170 , p_wip_id IN NUMBER
3171 , p_user_id IN NUMBER
3172 , p_sysd IN DATE
3173 , p_organization_id IN NUMBER
3174 , p_approval IN NUMBER
3175 , p_build_in_wip IN VARCHAR2
3176 , p_pick_components IN VARCHAR2
3177 , p_src_type IN NUMBER
3178 , p_encum_flag IN VARCHAR2
3179 , p_customer_id IN NUMBER
3180 , p_customer_site_id IN NUMBER
3181 , p_cal_code IN VARCHAR2
3182 , p_except_id IN NUMBER
3183 , p_employee_id IN NUMBER
3184 , p_description IN VARCHAR2
3185 , p_src_org IN NUMBER
3186 , p_src_subinv IN VARCHAR2
3187 , p_subinv IN VARCHAR2
3188 , p_location_id IN NUMBER
3189 , p_po_org_id IN NUMBER
3190 , p_pur_revision IN NUMBER
3191 /* nsinghi MIN-MAX INVCONV start */
3192 , p_execution_enabled IN VARCHAR2
3193 , p_recipe_enabled IN VARCHAR2
3194 , p_process_enabled IN VARCHAR2
3195 /* nsinghi MIN-MAX INVCONV end */
3196 , x_ret_stat OUT NOCOPY VARCHAR2
3197 , x_ret_mesg OUT NOCOPY VARCHAR2
3198 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
3199 ) IS
3200 l_proc_name CONSTANT VARCHAR2(30) := 'DO_RESTOCK';
3201 l_msg VARCHAR2(1000);
3202 l_need_by_date DATE;
3203 l_ret_value VARCHAR2(200);
3204 move_ord_exc EXCEPTION;
3205 requisition_exc EXCEPTION;
3206 l_ret_stat VARCHAR2(1);
3207 l_msg_count NUMBER;
3208 l_msg_data VARCHAR2(1000);
3209 l_mo_header_id NUMBER;
3210 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3211 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3212 l_trohdr_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3213 l_trohdr_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3214 l_x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3215 l_x_trohdr_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3216 l_x_trohdr_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3217 l_x_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3218 l_commit VARCHAR2(1) := FND_API.G_TRUE;
3219 l_mo_line_num NUMBER;
3220 l_order_count NUMBER := 1; /* total number of lines */
3221 l_intransit_time NUMBER := 0;
3222 l_approval NUMBER;
3223 l_sourcing_date DATE; -- This is the Date Required for MOs and Sourcing Date for Internal Requisitions.
3224 l_scheduled_ship_date DATE;
3225 l_sub_loc_id NUMBER;
3226 l_location_id NUMBER;
3227 l_asset_flag NUMBER :=1 ; -- Bug 4178417
3228 l_exp_acct NUMBER ; -- Bug 4178417
3229 l_charge_acct NUMBER ; -- Bug 4178417
3230 l_dual_uom_control NUMBER ;
3231 l_secondary_qty NUMBER ;
3232 l_secondary_uom VARCHAR2(3) ;
3233 BEGIN
3234 SAVEPOINT sp_do_restock;
3235 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
3236 x_ret_mesg := '';
3237
3238 --
3239 -- Query debug settings, set global variables.
3240 -- This is done since MRP will call do_restock directly
3241 -- from Reorder Point report (MRPRPROP, bug 2442596).
3242 --
3243
3244 --
3245 -- Bug 3174141, if Sub's location is defined use that instead of Org's.
3246 --
3247
3248 l_location_id := p_location_id;
3249 If p_subinv is not null then
3250 begin
3251 select nvl(location_id,0) into l_sub_loc_id from mtl_secondary_inventories
3252 where secondary_inventory_name = p_subinv and organization_id = p_organization_id;
3253 exception
3254 when others then
3255 print_debug('Error getting Subinventory location_id', 'do_restock', 9);
3256 l_sub_loc_id := 0;
3257 end;
3258
3259 If l_sub_loc_id <> 0 then
3260 l_location_id := l_sub_loc_id;
3261 print_debug('Subinventory location_id = ' ||to_char(l_location_id), 'do_restock', 9);
3262 End if;
3263 End if;
3264
3265 -- Bug 4178417 Min Max was not calculating charge account on basis of the Subinventory to be sourced
3266 -- from , i.e for expense subinventories the charge account should be the expense account of the subinventory
3267 l_charge_acct := p_charge_acct ;
3268
3269 If p_subinv is not null then
3270 begin
3271 SELECT asset_inventory,expense_account INTO l_asset_flag, l_exp_acct FROM mtl_secondary_inventories
3272 WHERE secondary_inventory_name = p_subinv and organization_id = p_organization_id;
3273 IF l_asset_flag = 2 AND l_exp_acct IS NOT NULL then
3274 l_charge_acct := l_exp_acct ;
3275 END IF;
3276 exception
3277 when others then
3278 print_debug('Error getting Subinventory Asset Information', 'do_restock', 9);
3279 end;
3280 End If ;
3281 -- Bug 4178417
3282
3283 IF G_TRACE_ON = 1 THEN
3284 print_debug('Executing Do_restock with the following parameters' || fnd_global.local_chr(10) ||
3285 ' p_item_id ' || to_char(p_item_id) || fnd_global.local_chr(10) ||
3286 ', p_mbf: ' || to_char(p_mbf) || fnd_global.local_chr(10) ||
3287 ', p_handle_repetitive_item: ' || to_char(p_handle_repetitive_item) || fnd_global.local_chr(10) ||
3288 ', p_repetitive_planned_item: ' || p_repetitive_planned_item || fnd_global.local_chr(10) ||
3289 ', p_qty: ' || to_char(p_qty) || fnd_global.local_chr(10) ||
3290 ', p_fixed_lead_time: ' || to_char(p_fixed_lead_time) || fnd_global.local_chr(10) ||
3291 ', p_variable_lead_time: ' || to_char(p_variable_lead_time) || fnd_global.local_chr(10) ||
3292 ', p_buying_lead_time: ' || to_char(p_buying_lead_time) || fnd_global.local_chr(10) ||
3293 ', p_uom: ' || p_uom || fnd_global.local_chr(10) ||
3294 ', p_accru_acct: ' || to_char(p_accru_acct) || fnd_global.local_chr(10) ||
3295 ', p_ipv_acct: ' || to_char(p_ipv_acct) || fnd_global.local_chr(10) ||
3296 ', p_budget_acct: ' || to_char(p_budget_acct) || fnd_global.local_chr(10)
3297 , l_proc_name
3298 , 9);
3299
3300 print_debug('p_charge_acct: ' || to_char(l_charge_acct) || fnd_global.local_chr(10) ||
3301 ', p_purch_flag: ' || p_purch_flag || fnd_global.local_chr(10) ||
3302 ', p_order_flag: ' || p_order_flag || fnd_global.local_chr(10) ||
3303 ', p_transact_flag: ' || p_transact_flag || fnd_global.local_chr(10) ||
3304 ', p_unit_price: ' || to_char(p_unit_price) || fnd_global.local_chr(10) ||
3305 ', p_wip_id: ' || to_char(p_wip_id) || fnd_global.local_chr(10) ||
3306 ', p_user_id: ' || to_char(p_user_id) || fnd_global.local_chr(10) ||
3307 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10) ||
3308 ', p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10) ||
3309 ', p_approval: ' || to_char(p_approval) || fnd_global.local_chr(10) ||
3310 ', p_build_in_wip: ' || p_build_in_wip || fnd_global.local_chr(10) ||
3311 ', p_pick_components: ' || p_pick_components || fnd_global.local_chr(10) ||
3312 ', p_src_type: ' || to_char(p_src_type) || fnd_global.local_chr(10)
3313 , l_proc_name
3314 , 9);
3315
3316 print_debug('p_encum_flag: ' || p_encum_flag || fnd_global.local_chr(10) ||
3317 ', p_customer_id: ' || to_char(p_customer_id) || fnd_global.local_chr(10) ||
3318 ', p_customer_site_id: ' || to_char(p_customer_site_id) || fnd_global.local_chr(10) ||
3319 ', p_cal_code: ' || p_cal_code || fnd_global.local_chr(10) ||
3320 ', p_except_id: ' || to_char(p_except_id) || fnd_global.local_chr(10) ||
3321 ', p_employee_id: ' || to_char(p_employee_id) || fnd_global.local_chr(10) ||
3322 ', p_description: ' || p_description || fnd_global.local_chr(10) ||
3323 ', p_src_org: ' || to_char(p_src_org) || fnd_global.local_chr(10) ||
3324 ', p_src_subinv: ' || p_src_subinv || fnd_global.local_chr(10) ||
3325 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
3326 ', l_location_id: ' || to_char(l_location_id) || fnd_global.local_chr(10) ||
3327 ', p_po_org_id: ' || to_char(p_po_org_id) || fnd_global.local_chr(10) ||
3328 ', p_pur_revision: ' || to_char(p_pur_revision) || fnd_global.local_chr(10) ||
3329 ', p_osfm_batch_id: ' || to_char(p_osfm_batch_id) || fnd_global.local_chr(10)
3330 , l_proc_name
3331 , 9);
3332 END IF;
3333
3334 IF p_qty <= 0 THEN
3335 RETURN;
3336 END IF;
3337
3338 IF (p_repetitive_planned_item = 'Y' AND p_handle_repetitive_item = 1) OR
3339 (p_repetitive_planned_item = 'N' AND p_mbf = 2)THEN
3340 --
3341 -- Lead time for buy items is sum of PREPROCESSING_LEAD_TIME
3342 -- AND PROCESSING_LEAD_TIME (sub level) OR PREPROCESSING_LEAD_TIME
3343 -- AND FULL_LEAD_TIME (org level)
3344 --
3345 -- Here, total lead time is the total buying Lead time
3346 --
3347 SELECT c1.calendar_date
3348 INTO l_sourcing_date
3349 FROM bom_calendar_dates c1,
3350 bom_calendar_dates c
3351 WHERE c1.calendar_code = c.calendar_code
3352 AND c1.exception_set_id = c.exception_set_id
3353 AND c1.seq_num = (c.next_seq_num + trunc(p_buying_lead_time))
3354 AND c.calendar_code = p_cal_code
3355 AND c.exception_set_id = p_except_id
3356 AND c.calendar_date = trunc(sysdate);
3357
3358 IF G_TRACE_ON = 1 THEN
3359 print_debug('Sourcing Date is:'|| l_sourcing_date
3360 , l_proc_name
3361 , 9);
3362 END IF;
3363 l_need_by_date := l_sourcing_date;
3364 --
3365 -- Min Max Lead time Enhancement.
3366 -- If source type is Inventory then
3367 -- Call the newly added private procedure to calculate the intransit time.
3368 -- Add intransit time to p_buying_lead_time to calculate l_needby_date
3369 -- End if;
3370 --
3371 IF p_src_type =1 THEN
3372 IF G_TRACE_ON = 1 THEN
3373 print_debug('Calling get_intransit_time '
3374 , l_proc_name
3375 , 9);
3376 END IF;
3377 get_intransit_time ( x_return_status => l_ret_stat
3378 , x_msg_count => l_msg_count
3379 , x_msg_data => l_msg_data
3380 , x_intransit_time => l_intransit_time
3381 , x_scheduled_ship_date => l_scheduled_ship_date
3382 , p_organization_id => p_organization_id
3383 , p_subinv => p_subinv
3384 , p_to_customer_site_id => p_customer_site_id
3385 , p_src_org => p_src_org
3386 , p_src_subinv => p_src_subinv
3387 , p_item_id => p_item_id
3388 , p_sourcing_date => l_sourcing_date);
3389
3390
3391 IF l_ret_stat = FND_API.G_RET_STS_ERROR THEN
3392 IF G_TRACE_ON = 1 THEN
3393 print_debug('INV_Minmax_PVT.get_lead_time failed with unexpected error returning message: ' || l_msg_data
3394 , l_proc_name
3395 , 9);
3396 END IF;
3397 RAISE requisition_exc;
3398 ELSIF l_ret_stat = FND_API.G_RET_STS_UNEXP_ERROR THEN
3399 IF G_TRACE_ON = 1 THEN
3400 print_debug('INV_Minmax_PVT.get_lead_time failed with expected error returning message: ' || l_msg_data
3401 , l_proc_name
3402 , 9);
3403 END IF;
3404 RAISE requisition_exc;
3405 ELSE
3406 IF G_TRACE_ON = 1 THEN
3407 print_debug('INV_Minmax_PVT.get_lead_time returned success with Intransit Time and Scheduled Ship Date '|| l_intransit_time ||' and '||l_scheduled_ship_date
3408 , l_proc_name
3409 , 9);
3410 END IF;
3411 --
3412 -- Add the intransit Time to Scheduled Ship Date toarrive at Need By Date.
3413 -- and find the next calendar date corresponding to this Need By Date.
3414 --
3415 l_need_by_date := l_scheduled_ship_date + l_intransit_time;
3416
3417 IF G_TRACE_ON = 1 THEN
3418 print_debug('Need by date after adding intransit Time : ' || to_char(l_need_by_date,'DD-MON-YYYY HH24:MI:SS')
3419 , l_proc_name
3420 , 9);
3421 END IF;
3422
3423 --
3424 -- kkoothan Fix for Bug 2795828.
3425 -- Passed appropriate message to the Report if BOM calender returns exception.
3426 --
3427 BEGIN
3428 SELECT c1.calendar_date
3429 INTO l_need_by_date
3430 FROM bom_calendar_dates c1,
3431 bom_calendar_dates c
3432 WHERE c1.calendar_code = c.calendar_code
3433 AND c1.exception_set_id = c.exception_set_id
3434 AND c1.seq_num = (c.next_seq_num)
3435 AND c.calendar_code = p_cal_code
3436 AND c.exception_set_id = p_except_id
3437 AND c.calendar_date = trunc(l_need_by_date);
3438 EXCEPTION
3439 WHEN others THEN
3440 IF G_TRACE_ON = 1 THEN
3441 print_debug('Exception occured in BOM Calendar'
3442 , l_proc_name
3443 , 9);
3444 END IF;
3445 x_ret_mesg := 'Exception occured in BOM Calendar';
3446 x_ret_stat := fnd_api.g_ret_sts_error;
3447 RETURN;
3448 END;
3449
3450 IF G_TRACE_ON = 1 THEN
3451 print_debug('Final Need by date: ' || to_char(l_need_by_date,'DD-MON-YYYY HH24:MI:SS')
3452 , l_proc_name
3453 , 9);
3454 END IF;
3455 END IF;
3456 END IF; -- Source Type is 'Inventory'
3457
3458
3459 IF p_src_type = 3 THEN
3460 IF p_transact_flag = 'Y' THEN
3461 BEGIN
3462 --
3463 -- Replenishment Move Order Consolidation
3464 -- Replace the call to INV_Create_Move_Order_PVT.Create_Move_Orders with
3465 -- a call to INV_MMX_WRAPPER_PVT.get_move_order_info to get the correct header ID
3466 -- and Line Number.
3467 -- Then call INV_Move_Order_PUB.Create_Move_Order_Lines to create a single move order line
3468 -- for the current item. For the input record p_trolin_tbl, use the header ID returned by
3469 -- get_move_order_header_id.
3470 --
3471 -- the profile value set at the profile "INV: Minmax Reorder Approval"
3472 -- This profile can have 3 values:
3473 -- (Lookup Type 'MTL_REQUISITION_APPROVAL' defined in MFG_LOOKUPS)
3474 -- 1- Pre-approve d
3475 -- 2- Pre-approve move orders only
3476 -- 3- Approval Required
3477 -- Converting these codes to the ones defined in MFG_LOOKUPS under the
3478 -- lookup type'MTL_TXN_REQUEST_STATUS'.
3479 -- IF l_approval = 3 THEN
3480 -- l_approval := 1; -- Incomplete
3481 -- ELSE
3482 -- l_approval := 7; -- Pre-approved
3483 -- END IF;
3484 --
3485
3486 IF p_approval = 3 THEN
3487 l_approval := 1; -- Incomplete
3488 ELSE
3489 l_approval := 7; -- Pre Approved
3490 END IF;
3491
3492
3493 IF G_TRACE_ON = 1 THEN
3494 print_debug('Approval Status is: '||l_approval
3495 , l_proc_name
3496 , 9);
3497 print_debug('Calling INV_MMX_WRAPPER_PVT.get_move_order_info'
3498 , l_proc_name
3499 , 9);
3500 END IF;
3501
3502 INV_MMX_WRAPPER_PVT.get_move_order_info(x_return_status => l_ret_stat
3503 , x_msg_count => l_msg_count
3504 , x_msg_data => l_msg_data
3505 , x_move_order_header_ID => l_mo_header_id
3506 , x_move_order_line_num => l_mo_line_num
3507 , p_user_id => p_user_id
3508 , p_organization_id => p_organization_id
3509 , p_subinv => p_subinv
3510 , p_src_subinv => p_src_subinv
3511 , p_approval => l_approval
3512 , p_need_by_date => l_need_by_date
3513 );
3514 IF l_ret_stat = FND_API.G_RET_STS_ERROR THEN
3515 IF G_TRACE_ON = 1 THEN
3516 print_debug('INV_MMX_WRAPPER_PVT.get_move_order_info failed with unexpected error returning message: ' || l_msg_data
3517 , l_proc_name
3518 , 9);
3519 END IF;
3520 RAISE fnd_api.g_exc_unexpected_error;
3521 ELSIF l_ret_stat = FND_API.G_RET_STS_UNEXP_ERROR THEN
3522 IF G_TRACE_ON = 1 THEN
3523 print_debug('INV_MMX_WRAPPER_PVT.get_move_order_info failed with expected error returning message: ' || l_msg_data
3524 , l_proc_name
3525 , 9);
3526 END IF;
3527 RAISE fnd_api.g_exc_error;
3528 ELSE
3529 IF G_TRACE_ON = 1 THEN
3530 print_debug('INV_MMX_WRAPPER_PVT.get_move_order_info returned success with MO Header Id and MO Line Number '|| fnd_global.local_chr(10) || l_mo_header_id ||' and '|| l_mo_line_num
3531 , l_proc_name
3532 , 9);
3533 END IF;
3534 END IF;
3535 /* --------INVCONV changes------------------------------ */
3536 /* 4004567 Instead of checking whether the item is dual uom controlled by checking if the item is defined to the org which is process enabled or not. Made change to check if the item has secondary_uom */
3537
3538 IF inv_cache.set_item_rec(p_organization_id =>p_organization_id,p_item_id => p_item_id) THEN
3539 l_secondary_uom := inv_cache.item_rec.secondary_uom_code;
3540 END IF;
3541
3542
3543
3544 IF l_secondary_uom IS NOT NULL THEN
3545 l_secondary_qty := inv_convert.inv_um_convert
3546 (item_id => p_item_id
3547 ,precision => 5
3548 ,from_quantity => p_qty
3549 ,from_unit => p_uom
3550 ,to_unit => l_secondary_uom
3551 ,from_name => NULL
3552 ,to_name => NULL);
3553 /* UOM conversion failure check */
3554 IF l_secondary_qty < 0 THEN
3555
3556 IF G_TRACE_ON = 1 THEN
3557 print_debug('UOM Conversion failed in creating move order: ' || p_item_id || ', ' || p_organization_id , l_proc_name , 9);
3558 END IF;
3559
3560 RAISE move_ord_exc;
3561 END IF ; /* if l_secondary_qty < 0 */
3562
3563
3564 ELSE
3565 l_secondary_qty := NULL ;
3566 END IF ;
3567
3568 /* ---------INVCONV Changes end-------------------------- */
3569
3570 l_trolin_tbl(l_order_count).header_id := l_mo_header_id;
3571 l_trolin_tbl(l_order_count).created_by := p_user_id;
3572 l_trolin_tbl(l_order_count).creation_date := sysdate;
3573 /* Bug# 3437350 */
3574 -- l_trolin_tbl(l_order_count).date_required := TRUNC(sysdate);
3575 l_trolin_tbl(l_order_count).date_required := l_need_by_date;
3576 /* End of Bug# 3437350 */
3577 l_trolin_tbl(l_order_count).from_subinventory_code := p_src_subinv;
3578 l_trolin_tbl(l_order_count).inventory_item_id := p_item_id;
3579 l_trolin_tbl(l_order_count).last_updated_by := p_user_id;
3580 l_trolin_tbl(l_order_count).last_update_date := sysdate;
3581 l_trolin_tbl(l_order_count).last_update_login := p_user_id;
3582 l_trolin_tbl(l_order_count).line_number := l_mo_line_num;
3583 l_trolin_tbl(l_order_count).line_status := l_approval;
3584 l_trolin_tbl(l_order_count).organization_id := p_organization_id;
3585 l_trolin_tbl(l_order_count).quantity := p_qty;
3586 l_trolin_tbl(l_order_count).status_date := sysdate;
3587 l_trolin_tbl(l_order_count).to_subinventory_code := p_subinv;
3588 l_trolin_tbl(l_order_count).uom_code := p_uom;
3589 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
3590 l_trolin_tbl(l_order_count).reference_type_code := INV_Transfer_Order_PVT.G_REF_TYPE_MINMAX;-- kkoothan For Bug Fix:2756930
3591 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
3592 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
3593 /* ------INVCONV Change Added secondary qty and uom------ */
3594 l_trolin_tbl(l_order_count).secondary_quantity := l_secondary_qty;
3595 l_trolin_tbl(l_order_count).secondary_uom := l_secondary_uom;
3596 /* ------INVCONV Change Added secondary qty and uom------ */
3597
3598 IF G_TRACE_ON = 1 THEN
3599 print_debug('Calling INV_Move_Order_PUB.Create_Move_Order_Lines'
3600 , l_proc_name
3601 , 9);
3602 END IF;
3603 INV_Move_Order_PUB.Create_Move_Order_Lines
3604 ( p_api_version_number => 1.0 ,
3605 p_init_msg_list => FND_API.G_TRUE,
3606 p_commit => l_commit,
3607 x_return_status => l_ret_stat,
3608 x_msg_count => l_msg_count,
3609 x_msg_data => l_msg_data,
3610 p_trolin_tbl => l_trolin_tbl,
3611 p_trolin_val_tbl => l_trolin_val_tbl,
3612 x_trolin_tbl => l_x_trolin_tbl,
3613 x_trolin_val_tbl => l_x_trolin_val_tbl
3614 );
3615
3616 IF l_ret_stat = FND_API.G_RET_STS_ERROR THEN
3617 IF G_TRACE_ON = 1 THEN
3618 print_debug('INV_Move_Order_PUB.Create_Move_Order_Lines failed with expected error returning message: ' || l_msg_data|| l_msg_count
3619 , l_proc_name
3620 , 9);
3621 END IF;
3622 IF l_msg_count > 0 THEN
3623 FOR i in 1..l_msg_count
3624 LOOP
3625 l_msg := fnd_msg_pub.get(i,'F');
3626 print_debug(l_msg
3627 , l_proc_name
3628 , 9);
3629 fnd_msg_pub.delete_msg(i);
3630 END LOOP;
3631 END IF;
3632 RAISE fnd_api.g_exc_unexpected_error;
3633 ELSIF l_ret_stat = FND_API.G_RET_STS_UNEXP_ERROR THEN
3634 IF G_TRACE_ON = 1 THEN
3635 print_debug('INV_Move_Order_PUB.Create_Move_Order_Lines failed with unexpected error returning message: ' || l_msg_data
3636 , l_proc_name
3637 , 9);
3638 END IF;
3639 RAISE fnd_api.g_exc_error;
3640 ELSE
3641 IF G_TRACE_ON = 1 THEN
3642 print_debug('INV_Move_Order_PUB.Create_Move_Order_Lines returned success'
3643 , l_proc_name
3644 , 9);
3645 END IF;
3646 END IF;
3647
3648 EXCEPTION
3649 WHEN OTHERS THEN
3650 IF G_TRACE_ON = 1 THEN
3651 print_debug('Error creating move order: ' || sqlcode || ', ' || sqlerrm
3652 , l_proc_name
3653 , 1);
3654 END IF;
3655 RAISE move_ord_exc;
3656 END;
3657 ELSE
3658 IF G_TRACE_ON = 1 THEN
3659 print_debug('Src type is sub, item not transactable.', l_proc_name, 9);
3660 END IF;
3661 RAISE move_ord_exc;
3662 END IF; -- Transact Flag is 'N'
3663
3664 ELSE
3665
3666 re_po( p_item_id => p_item_id
3667 , p_qty => p_qty
3668 , p_nb_time => l_need_by_date
3669 , p_uom => p_uom
3670 , p_accru_acct => p_accru_acct
3671 , p_ipv_acct => p_ipv_acct
3672 , p_budget_acct => p_budget_acct
3673 , p_charge_acct => l_charge_acct -- Bug 4178417
3674 , p_purch_flag => p_purch_flag
3675 , p_order_flag => p_order_flag
3676 , p_transact_flag => p_transact_flag
3677 , p_unit_price => p_unit_price
3678 , p_user_id => p_user_id
3679 , p_sysd => p_sysd
3680 , p_organization_id => p_organization_id
3681 , p_approval => p_approval
3682 , p_src_type => p_src_type
3683 , p_encum_flag => p_encum_flag
3684 , p_customer_id => p_customer_id
3685 , p_employee_id => p_employee_id
3686 , p_description => p_description
3687 , p_src_org => p_src_org
3688 , p_src_subinv => p_src_subinv
3689 , p_subinv => p_subinv
3690 , p_location_id => l_location_id -- 3174141
3691 , p_po_org_id => p_po_org_id
3692 , p_pur_revision => p_pur_revision
3693 , x_ret_stat => l_ret_stat
3694 , x_ret_mesg => x_ret_mesg);
3695
3696 x_ret_stat := l_ret_stat;
3697
3698 END IF;
3699
3700 ELSE
3701
3702 --
3703 -- Either a make item, or repetitive item and the user chose
3704 -- "Create Discrete Job"
3705 --
3706 IF G_TRACE_ON = 1 THEN
3707 print_debug('Calling wip_calendar.estimate_leadtime to calculate need_by_date'
3708 , l_proc_name
3709 , 9);
3710 END IF;
3711
3712 wip_calendar.estimate_leadtime(x_org_id => p_organization_id,
3713 x_fixed_lead => p_fixed_lead_time,
3714 x_var_lead => p_variable_lead_time,
3715 x_quantity => p_qty,
3716 x_proc_days => 0,
3717 x_entity_type => 1,
3718 x_fusd => p_sysd,
3719 x_fucd => NULL,
3720 x_lusd => NULL,
3721 x_lucd => NULL,
3722 x_sched_dir => 1,
3723 x_est_date => l_need_by_date);
3724 /* nsinghi MIN-MAX INVCONV start */
3725 /* For process org, call needs to be made against to the GME batch API.
3726 We need to retain the above estimate_leadtime call for both Process
3727 and discrete as the leadtime would require to be calculated for the mtl_system_items in
3728 the converged model. The leadtime calculated by wip_calendar.estimate_leadtime is
3729 fixed_lead_time + (variable_lead_time * qty) which is common to process too. */
3730
3731
3732 IF p_process_enabled = 'Y' THEN
3733
3734 re_batch( p_item_id => p_item_id
3735 , p_qty => p_qty
3736 , p_nb_time => l_need_by_date
3737 , p_uom => p_uom
3738 , p_organization_id => p_organization_id
3739 , p_execution_enabled => p_execution_enabled
3740 , p_recipe_enabled => p_recipe_enabled
3741 , p_user_id => p_user_id
3742 , x_ret_stat => l_ret_stat
3743 , x_ret_mesg => x_ret_mesg);
3744
3745 ELSE
3746 /* nsinghi MIN-MAX INVCONV end */
3747
3748 re_wip( p_item_id => p_item_id
3749 , p_qty => p_qty
3750 , p_nb_time => l_need_by_date
3751 , p_uom => p_uom
3752 , p_wip_id => p_wip_id
3753 , p_user_id => p_user_id
3754 , p_sysd => p_sysd
3755 , p_organization_id => p_organization_id
3756 , p_approval => p_approval
3757 , p_build_in_wip => p_build_in_wip
3758 , p_pick_components => p_pick_components
3759 , x_ret_stat => l_ret_stat
3760 , x_ret_mesg => x_ret_mesg
3761 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
3762 );
3763
3764 END IF;
3765
3766 x_ret_stat := l_ret_stat;
3767 END IF;
3768
3769 EXCEPTION
3770 WHEN fnd_api.g_exc_error THEN
3771 ROLLBACK TO sp_do_restock;
3772 x_ret_stat := fnd_api.g_ret_sts_error;
3773 fnd_msg_pub.count_and_get
3774 ( p_count =>l_msg_count ,
3775 p_data => x_ret_mesg
3776 );
3777 WHEN fnd_api.g_exc_unexpected_error THEN
3778 ROLLBACK TO sp_do_restock;
3779 x_ret_stat := fnd_api.g_ret_sts_unexp_error;
3780 fnd_msg_pub.count_and_get
3781 ( p_count =>l_msg_count ,
3782 p_data => x_ret_mesg
3783 );
3784
3785 WHEN requisition_exc THEN
3786 ROLLBACK TO sp_do_restock;
3787 SELECT meaning
3788 INTO x_ret_mesg
3789 FROM mfg_lookups
3790 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
3791 AND lookup_code = 1;
3792
3793 x_ret_stat := FND_API.G_RET_STS_ERROR;
3794
3795 WHEN move_ord_exc THEN
3796 ROLLBACK TO sp_do_restock;
3797 SELECT meaning
3798 INTO x_ret_mesg
3799 FROM mfg_lookups
3800 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
3801 AND lookup_code = 5;
3802
3803 x_ret_stat := FND_API.G_RET_STS_ERROR;
3804
3805 WHEN others THEN
3806 IF G_TRACE_ON = 1 THEN
3807 print_debug(sqlcode || ', ' || sqlerrm, l_proc_name, 1);
3808 END IF;
3809 RAISE;
3810
3811 END do_restock;
3812
3813
3814
3815 PROCEDURE re_po( p_item_id IN NUMBER
3816 , p_qty IN NUMBER
3817 , p_nb_time IN DATE
3818 , p_uom IN VARCHAR2
3819 , p_accru_acct IN NUMBER
3820 , p_ipv_acct IN NUMBER
3821 , p_budget_acct IN NUMBER
3822 , p_charge_acct IN NUMBER
3823 , p_purch_flag IN VARCHAR2
3824 , p_order_flag IN VARCHAR2
3825 , p_transact_flag IN VARCHAR2
3826 , p_unit_price IN NUMBER
3827 , p_user_id IN NUMBER
3828 , p_sysd IN DATE
3829 , p_organization_id IN NUMBER
3830 , p_approval IN NUMBER
3831 , p_src_type IN NUMBER
3832 , p_encum_flag IN VARCHAR2
3833 , p_customer_id IN NUMBER
3834 , p_employee_id IN NUMBER
3835 , p_description IN VARCHAR2
3836 , p_src_org IN NUMBER
3837 , p_src_subinv IN VARCHAR2
3838 , p_subinv IN VARCHAR2
3839 , p_location_id IN NUMBER
3840 , p_po_org_id IN NUMBER
3841 , p_pur_revision IN NUMBER
3842 , x_ret_stat OUT NOCOPY VARCHAR2
3843 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
3844
3845 l_item_rev_ctl NUMBER := 0;
3846 l_item_revision VARCHAR2(4) := '@@@';
3847 l_orgn_id NUMBER := p_organization_id;
3848
3849 l_unit_of_issue VARCHAR2(3); -- For Bug 3894347
3850 l_check_uom NUMBER; -- For Bug 3894347
3851 l_qty_conv NUMBER; -- For Bug 3894347
3852
3853 po_exc EXCEPTION;
3854
3855 BEGIN
3856 IF G_TRACE_ON = 1 THEN
3857 print_debug('p_item_id: ' || to_char(p_item_id) ||
3858 ', p_qty: ' || to_char(p_qty) ||
3859 ', p_nb_time:' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
3860 ', p_uom: ' || p_uom ||
3861 ', p_accru_acct: ' || to_char(p_accru_acct) ||
3862 ', p_ipv_acct: ' || to_char(p_ipv_acct) ||
3863 ', p_budget_acct: ' || to_char(p_budget_acct) ||
3864 ', p_charge_acct: ' || to_char(p_charge_acct) ||
3865 ', p_purch_flag: ' || p_purch_flag ||
3866 ', p_order_flag: ' || p_order_flag ||
3867 ', p_transact_flag: ' || p_transact_flag ||
3868 ', p_unit_price: ' || to_char(p_unit_price) ||
3869 ', p_user_id: ' || to_char(p_user_id) ||
3870 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS')
3871 , 're_po'
3872 , 9);
3873
3874 print_debug('p_organization_id: ' || to_char(p_organization_id) ||
3875 ', p_approval: ' || to_char(p_approval) ||
3876 ', p_src_type: ' || to_char(p_src_type) ||
3877 ', p_encum_flag: ' || p_encum_flag ||
3878 ', p_customer_id: ' || to_char(p_customer_id) ||
3879 ', p_employee_id: ' || to_char(p_employee_id) ||
3880 ', p_description: ' || p_description ||
3881 ', p_src_org: ' || to_char(p_src_org) ||
3882 ', p_src_subinv: ' || p_src_subinv ||
3883 ', p_subinv: ' || p_subinv ||
3884 ', p_location_id: ' || to_char(p_location_id) ||
3885 ', p_po_org_id: ' || to_char(p_po_org_id) ||
3886 ', p_pur_revision: ' || to_char(p_pur_revision)
3887 , 're_po'
3888 , 9);
3889 END IF;
3890
3891 --
3892 -- Do not create a requisition if any of the following apply:
3893 -- 1. Source type (Inventory/Supplier/Subinventory) is not specified
3894 -- 2. Item is not transactable
3895 -- 3. Source type is Inventory (1) but "Internal Orders Enabled"
3896 -- is not checked
3897 -- 4. Source type is Supplier (2) but "Purchasable" flag unchecked
3898 --
3899 IF (p_src_type IS NULL)
3900 OR
3901 (p_transact_flag <> 'Y')
3902 OR
3903 (p_src_type = 1 AND p_order_flag <> 'Y')
3904 OR
3905 (p_src_type = 2 AND p_purch_flag <> 'Y')
3906 THEN
3907 IF G_TRACE_ON = 1 THEN
3908 print_debug('Null src type or invalid transact_flag, order_flag or purch_flag'
3909 , 're_po', 9);
3910 END IF;
3911 RAISE po_exc;
3912 END IF;
3913
3914 IF (p_charge_acct IS NULL)
3915 OR (p_accru_acct IS NULL)
3916 OR (p_ipv_acct IS NULL)
3917 OR ((p_encum_flag <> 'N') AND (p_budget_acct is NULL))
3918 THEN
3919 IF G_TRACE_ON = 1 THEN
3920 print_debug('Charge/accrual/IPV/budget accts not setup correctly.', 're_po', 9);
3921 END IF;
3922 RAISE po_exc;
3923 END IF;
3924
3925 IF NVL(p_customer_id,0) < 0
3926 THEN
3927 IF G_TRACE_ON = 1 THEN
3928 print_debug('Invalid customer ID: ' || to_char(p_customer_id), 're_po', 9);
3929 END IF;
3930 RAISE po_exc;
3931 END IF;
3932
3933
3934 /* Fix for bug 774532. To get the item revisions, IF profile is Yes
3935 OR IF profile is NULL AND item is revision controlled */
3936
3937 --
3938 -- Bug 2323099:
3939 -- We should only specify a revision if the item is revision-controlled
3940 -- and the profile "INV:Purchasing By Revision" is set to yes
3941 --
3942 -- p_pur_revision will never be NULL - this is handled in the
3943 -- BEFORE-REPORT trigger of INVISMMX.
3944 --
3945
3946 IF p_pur_revision = 1
3947 THEN
3948 SELECT revision_qty_control_code
3949 INTO l_item_rev_ctl
3950 FROM mtl_system_items msi
3951 WHERE msi.organization_id = l_orgn_id
3952 AND msi.inventory_item_id = p_item_id;
3953
3954 IF G_TRACE_ON = 1 THEN
3955 print_debug('Rev ctl: ' || to_char(l_item_rev_ctl), 're_po', 9);
3956 END IF;
3957
3958 /* Commented for ER 6698138
3959 IF l_item_rev_ctl = 2 THEN
3960 */
3961 SELECT MAX(revision)
3962 INTO l_item_revision
3963 FROM mtl_item_revisions mir
3964 WHERE inventory_item_id = p_item_id
3965 AND organization_id = l_orgn_id
3966 AND effectivity_date < SYSDATE
3967 AND implementation_date is not null /* Added for Bug 7110794 */
3968 AND effectivity_date =
3969 (
3970 SELECT MAX(effectivity_date)
3971 FROM mtl_item_revisions mir1
3972 WHERE mir1.inventory_item_id = mir.inventory_item_id
3973 AND mir1.organization_id = mir.organization_id
3974 AND implementation_date is not null /* Added for Bug 7110794 */
3975 AND effectivity_date < SYSDATE
3976 );
3977 /* Commented for ER 6698138
3978 END IF;
3979 */
3980
3981 IF G_TRACE_ON = 1 THEN
3982 print_debug('Item rev: ' || l_item_revision, 're_po', 9);
3983 END IF;
3984 END IF ;
3985
3986 /* Changes for Bug 3894347 */
3987 l_check_uom := 0;
3988
3989 select uom_code
3990 into l_unit_of_issue
3991 from mtl_system_items_vl msiv , mtl_units_of_measure_vl muom
3992 where msiv.inventory_item_id = p_item_id
3993 and msiv.organization_id = p_organization_id
3994 and muom.unit_of_measure = NVL(msiv.unit_of_issue,msiv.primary_unit_of_measure);
3995
3996 IF G_TRACE_ON = 1 THEN
3997 print_debug('l_unit_of_issue: '||l_unit_of_issue, 're_po', 9);
3998 END IF;
3999
4000 IF ( l_unit_of_issue <> p_uom) THEN
4001
4002 IF G_TRACE_ON = 1 THEN
4003 print_debug('p_item_id: ' || to_char(p_item_id) ||
4004 ', p_qty: ' || to_char(p_qty) ||
4005 ', p_organization_id: ' || to_char(p_organization_id) ||
4006 ', p_uom: ' || p_uom ||
4007 ', l_unit_of_issue: ' || l_unit_of_issue
4008 , 're_po'
4009 , 9);
4010 END IF;
4011
4012 l_qty_conv := INV_CONVERT.INV_UM_CONVERT(
4013 item_id => p_item_id,
4014 precision => null,
4015 from_quantity => p_qty,
4016 from_unit => p_uom,
4017 to_unit => l_unit_of_issue,
4018 from_name => null,
4019 to_name => null);
4020
4021 IF G_TRACE_ON = 1 THEN
4022 print_debug('l_qty_conv = ' || to_char(l_qty_conv), 're_po', 9);
4023 END IF;
4024
4025 l_check_uom := 1;
4026
4027 END IF;
4028 /* End of Changes for Bug 3894347 */
4029
4030 IF G_TRACE_ON = 1 THEN
4031 print_debug('Inserting into PO_REQUISITIONS_INTERFACE_ALL', 're_po', 9);
4032 END IF;
4033
4034 INSERT INTO po_requisitions_interface_all(
4035 LAST_UPDATE_DATE,
4036 LAST_UPDATED_BY,
4037 ITEM_DESCRIPTION,
4038 CREATION_DATE,
4039 CREATED_BY,
4040 PREPARER_ID,
4041 INTERFACE_SOURCE_CODE,
4042 REQUISITION_TYPE,
4043 AUTHORIZATION_STATUS,
4044 SOURCE_TYPE_CODE,
4045 SOURCE_ORGANIZATION_ID,
4046 SOURCE_SUBINVENTORY,
4047 DESTINATION_ORGANIZATION_ID,
4048 DESTINATION_SUBINVENTORY,
4049 DELIVER_TO_REQUESTOR_ID,
4050 DESTINATION_TYPE_CODE,
4051 UOM_CODE,
4052 DELIVER_TO_LOCATION_ID,
4053 ITEM_ID,
4054 ITEM_REVISION,
4055 QUANTITY,
4056 NEED_BY_DATE,
4057 GL_DATE,
4058 CHARGE_ACCOUNT_ID,
4059 ACCRUAL_ACCOUNT_ID,
4060 VARIANCE_ACCOUNT_ID,
4061 BUDGET_ACCOUNT_ID,
4062 AUTOSOURCE_FLAG,
4063 ORG_ID)
4064 VALUES (
4065 sysdate,
4066 p_user_id,
4067 p_description,
4068 sysdate,
4069 p_user_id,
4070 p_employee_id,
4071 'INV',
4072 DECODE(p_src_type, 1, 'INTERNAL', 'PURCHASE'),
4073 DECODE(p_approval, 1, 'APPROVED','INCOMPLETE'),
4074 DECODE(p_src_type, 1, 'INVENTORY', 'VENDOR'),
4075 p_src_org,
4076 p_src_subinv,
4077 p_organization_id,
4078 p_subinv,
4079 p_employee_id,
4080 'INVENTORY',
4081 DECODE(l_check_uom,1,l_unit_of_issue,p_uom), -- Bug 3894347
4082 p_location_id,
4083 p_item_id,
4084 DECODE(l_item_revision,'@@@',NULL,l_item_revision),
4085 DECODE(l_check_uom,1,l_qty_conv,p_qty), -- Bug 3894347
4086 (trunc(p_nb_time) + 1 - (1/(24*60*60))),
4087 SYSDATE,
4088 p_charge_acct,
4089 p_accru_acct,
4090 p_ipv_acct,
4091 p_budget_acct,
4092 'P',
4093 p_po_org_id);
4094
4095 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
4096 x_ret_mesg := '';
4097
4098 EXCEPTION
4099 WHEN OTHERS THEN
4100 IF G_TRACE_ON = 1 THEN
4101 print_debug(sqlcode || ', ' || sqlerrm, 're_po', 1);
4102 END IF;
4103
4104 SELECT meaning
4105 INTO x_ret_mesg
4106 FROM mfg_lookups
4107 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
4108 AND lookup_code = 1;
4109
4110 x_ret_stat := FND_API.G_RET_STS_ERROR;
4111 END re_po;
4112
4113
4114
4115 PROCEDURE re_wip( p_item_id IN NUMBER
4116 , p_qty IN NUMBER
4117 , p_nb_time IN DATE
4118 , p_uom IN VARCHAR2
4119 , p_wip_id IN NUMBER
4120 , p_user_id IN NUMBER
4121 , p_sysd IN DATE
4122 , p_organization_id IN NUMBER
4123 , p_approval IN NUMBER
4124 , p_build_in_wip IN VARCHAR2
4125 , p_pick_components IN VARCHAR2
4126 , x_ret_stat OUT NOCOPY VARCHAR2
4127 , x_ret_mesg OUT NOCOPY VARCHAR2
4128 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
4129 ) IS
4130
4131 wip_exc EXCEPTION;
4132 /* Added for Bug 6807835 */
4133
4134 l_header_id NUMBER := NULL;
4135 l_mode_flag NUMBER := NULL;
4136 l_job_name VARCHAR2(255);
4137 l_first_unit_start_date DATE;
4138 l_last_unit_completion_date DATE;
4139 l_scheduling_method NUMBER := 2;
4140 l_cfm_flag NUMBER;
4141 l_osfm_batch_id NUMBER;
4142 l_is_lot_control VARCHAR2(1) := NULL;
4143
4144 /* End of changes for Bug 6807835 */
4145
4146
4147 BEGIN
4148 IF G_TRACE_ON = 1 THEN
4149 print_debug('p_item_id: ' || to_char(p_item_id) ||
4150 ', p_qty: ' || to_char(p_qty) ||
4151 ', p_nb_time: ' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
4152 ', p_uom: ' || p_uom ||
4153 ', p_wip_id: ' || to_char(p_wip_id) ||
4154 ', p_user_id: ' || to_char(p_user_id) ||
4155 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') ||
4156 ', p_organization_id: ' || to_char(p_organization_id) ||
4157 ', p_approval: ' || to_char(p_approval) ||
4158 ', p_build_in_wip: ' || p_build_in_wip ||
4159 ', p_pick_components: ' || p_pick_components ||
4160 ', p_osfm_batch_id: ' || p_osfm_batch_id
4161 , 're_wip'
4162 , 9);
4163 END IF;
4164
4165 /* Added for Bug 6807835 */
4166
4167 IF(to_number(NVL(FND_PROFILE.VALUE('WSM_CREATE_LBJ_COPY_ROUTING'),0)) = 1 ) THEN
4168 l_scheduling_method := 1;
4169 ELSE
4170 l_scheduling_method := 2;
4171 END IF;
4172 select wsm_lot_sm_ifc_header_s.nextval
4173 into l_header_id
4174 from dual;
4175
4176 l_mode_flag := 1;
4177
4178 /*Bug 15837698 Moved this part just before inserting into wsm_lot_job_interface*/
4179 /* select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
4180 INTO l_job_name
4181 from dual;
4182
4183 print_debug('OSFM Job Name '||l_job_name
4184 , 're_wip', 9);*/
4185
4186 IF p_nb_time IS NOT NULL THEN
4187 l_first_unit_start_date := NULL;
4188 l_last_unit_completion_date := p_nb_time;
4189
4190 ELSE
4191 l_first_unit_start_date := SYSDATE;
4192 l_last_unit_completion_date := NULL;
4193 END IF;
4194
4195 BEGIN
4196 select nvl(cfm_routing_flag,0) into l_cfm_flag
4197 from BOM_OPERATIONAL_ROUTINGS
4198 where assembly_item_id = p_item_id
4199 AND organization_id = p_organization_id
4200 AND alternate_routing_designator is NULL;
4201 EXCEPTION
4202 when NO_DATA_FOUND then
4203 l_cfm_flag := 2;
4204 when others then
4205 RAISE wip_exc;
4206 END;
4207
4208 /* End of changes for Bug 6807835 */
4209
4210 IF p_build_in_wip <> 'Y' OR p_pick_components <> 'N'
4211 THEN
4212 IF G_TRACE_ON = 1 THEN
4213 print_debug('Item either not build_in_wip or has pick components flag checked'
4214 , 're_wip', 9);
4215 END IF;
4216 RAISE wip_exc;
4217 /* Added for Bug 6807835 */
4218 ELSIF (l_cfm_flag = 3) AND (wsmpvers.get_osfm_release_version > '110509')
4219 THEN
4220 BEGIN
4221 SELECT 'Y' INTO l_is_lot_control
4222 FROM dual
4223 WHERE exists
4224 (SELECT 1 FROM mtl_system_items
4225 WHERE organization_id = p_organization_id
4226 AND inventory_item_id = p_item_id
4227 AND lot_control_code = 2);
4228 EXCEPTION
4229 WHEN OTHERS THEN
4230 l_is_lot_control := 'N';
4231 END;
4232
4233 IF (p_osfm_batch_id is null) THEN
4234
4235 --
4236 -- Set L_OSFM_BATCH_ID to the next Sequence of WSM_LOT_JOB_INTERFACE_S.
4237 --
4238
4239 BEGIN
4240 SELECT WSM_LOT_JOB_INTERFACE_S.NEXTVAL
4241 INTO l_osfm_batch_id
4242 FROM SYS.DUAL;
4243 EXCEPTION
4244 WHEN no_data_found THEN
4245 IF G_TRACE_ON = 1 THEN
4246 print_debug('Exception: WSM_LOT_JOB_INTERFACE_S.NEXTVAL is not defined'
4247 , 're_wip'
4248 , 9);
4249 END IF;
4250 RAISE wip_exc;
4251 END;
4252 ELSE
4253 l_osfm_batch_id := p_osfm_batch_id;
4254 END IF;
4255
4256 IF G_TRACE_ON = 1 THEN
4257 print_debug('OSFM Batch Id is: ' || l_osfm_batch_id
4258 , 're_wip'
4259 , 9);
4260 END IF;
4261
4262
4263 IF l_is_lot_control = 'Y' THEN
4264
4265 /*Bug 15837698 If it is a lot controlled item, only then use wip_job_number_s.nextval
4266 before inserting data into WSM_LOT_JOB_INTERFACE */
4267 select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
4268 INTO l_job_name
4269 from dual;
4270
4271 print_debug('OSFM Job Name '||l_job_name , 're_wip', 9);
4272 /*End of Bug 15837698*/
4273
4274 INSERT INTO WSM_LOT_JOB_INTERFACE (
4275 mode_flag,
4276 last_update_date,
4277 last_updated_by,
4278 creation_date,
4279 created_by,
4280 last_update_login,
4281 group_id,
4282 source_line_id,
4283 organization_id,
4284 load_type,
4285 status_type,
4286 primary_item_id,
4287 job_name,
4288 start_Quantity,
4289 process_Status,
4290 first_unit_start_date,
4291 last_unit_completion_date,
4292 scheduling_method,
4293 completion_subinventory,
4294 completion_locator_id,
4295 class_code,
4296 description,
4297 bom_revision_date,
4298 routing_revision_date,
4299 header_id)
4300 VALUES (
4301 1,
4302 sysdate,
4303 fnd_global.user_id,
4304 sysdate,
4305 fnd_global.user_id,
4306 fnd_global.login_id,
4307 l_osfm_batch_id,
4308 Decode(l_mode_flag, 1,null,l_header_id),
4309 p_organization_id,
4310 5, --job creation
4311 3, --1:unreleased, 3: released
4312 p_item_id,
4313 l_job_name,
4314 p_qty,
4315 1,
4316 l_first_unit_start_date,
4317 l_last_unit_completion_date,
4318 l_scheduling_method,
4319 null,
4320 null,
4321 '',
4322 null,
4323 '',
4324 '',
4325 l_header_id);
4326
4327 ELSE -- l_is_lot_control = 'Y'
4328 IF G_TRACE_ON = 1 THEN
4329 print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
4330 END IF;
4331 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
4332 LAST_UPDATE_DATE,
4333 LAST_UPDATED_BY,
4334 CREATION_DATE,
4335 CREATED_BY,
4336 GROUP_ID,
4337 PROCESS_PHASE,
4338 PROCESS_STATUS,
4339 ORGANIZATION_ID,
4340 LOAD_TYPE,
4341 FIRST_UNIT_START_DATE , -- LAST_UNIT_COMPLETION_DATE, -- added by bug 13709690
4342 PRIMARY_ITEM_ID,
4343 START_QUANTITY,
4344 STATUS_TYPE)
4345 VALUES(
4346 p_sysd,
4347 p_user_id,
4348 p_sysd,
4349 p_user_id,
4350 p_wip_id,
4351 2,
4352 1,
4353 p_organization_id,
4354 1,
4355 p_sysd , -- p_nb_time, -- added by bug 13709690
4356 p_item_id,
4357 p_qty,
4358 DECODE(p_approval,1,3,1));
4359
4360 END IF; -- end of l_is_lot_control = 'Y'
4361
4362 /* End of changes for 6807835 */
4363
4364 ELSE
4365 IF G_TRACE_ON = 1 THEN
4366 print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
4367 END IF;
4368 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
4369 LAST_UPDATE_DATE,
4370 LAST_UPDATED_BY,
4371 CREATION_DATE,
4372 CREATED_BY,
4373 GROUP_ID,
4374 PROCESS_PHASE,
4375 PROCESS_STATUS,
4376 ORGANIZATION_ID,
4377 LOAD_TYPE,
4378 FIRST_UNIT_START_DATE , -- LAST_UNIT_COMPLETION_DATE, -- added by bug 13709690
4379 PRIMARY_ITEM_ID,
4380 START_QUANTITY,
4381 STATUS_TYPE)
4382 VALUES(
4383 p_sysd,
4384 p_user_id,
4385 p_sysd,
4386 p_user_id,
4387 p_wip_id,
4388 2,
4389 1,
4390 p_organization_id,
4391 1,
4392 p_sysd , -- p_nb_time, -- added by bug 13709690
4393 p_item_id,
4394 p_qty,
4395 DECODE(p_approval,1,3,1));
4396 END IF;
4397
4398 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
4399 x_ret_mesg := '';
4400
4401 EXCEPTION
4402 WHEN OTHERS THEN
4403 IF G_TRACE_ON = 1 THEN
4404 print_debug(sqlcode || ', ' || sqlerrm, 're_wip', 1);
4405 END IF;
4406
4407 SELECT meaning
4408 INTO x_ret_mesg
4409 FROM mfg_lookups
4410 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
4411 AND lookup_code = 2;
4412
4413 x_ret_stat := FND_API.G_RET_STS_ERROR;
4414 END re_wip;
4415 --
4416 /* nsinghi MIN-MAX INVCONV start */
4417
4418 PROCEDURE re_batch( p_item_id IN NUMBER
4419 , p_qty IN NUMBER
4420 , p_nb_time IN DATE
4421 , p_uom IN VARCHAR2
4422 , p_organization_id IN NUMBER
4423 , p_execution_enabled IN VARCHAR2
4424 , p_recipe_enabled IN VARCHAR2
4425 , p_user_id IN NUMBER
4426 , x_ret_stat OUT NOCOPY VARCHAR2
4427 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
4428
4429
4430 l_gme_batch_header GME_BATCH_HEADER%ROWTYPE;
4431 l_eff_id NUMBER(15);
4432 batch_exc EXCEPTION;
4433 x_message_count NUMBER;
4434 x_message_list VARCHAR2(1000);
4435 return_status VARCHAR2(1000);
4436 x_gme_batch_header gme_batch_header%ROWTYPE;
4437 x_exception_material_tbl gmp_batch_wrapper_pkg.exceptions_tab;
4438
4439 BEGIN
4440
4441 IF G_TRACE_ON = 1 THEN
4442 print_debug('p_item_id: ' || to_char(p_item_id) ||
4443 ', p_qty: ' || to_char(p_qty) ||
4444 ', p_nb_time: ' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
4445 ', p_uom: ' || p_uom ||
4446 ', p_organization_id: ' || to_char(p_organization_id)
4447 , 're_wip'
4448 , 9);
4449 END IF;
4450
4451 IF p_execution_enabled <> 'Y' OR p_recipe_enabled <> 'Y'
4452 THEN
4453 IF G_TRACE_ON = 1 THEN
4454 print_debug('Item either not Execution Enabled or not Recipe Enabled'
4455 , 're_batch', 9);
4456 END IF;
4457 RAISE batch_exc;
4458 ELSE
4459 fnd_profile.initialize(p_user_id);
4460
4461 l_gme_batch_header.organization_id := p_organization_id;
4462 l_gme_batch_header.plan_cmplt_date := p_nb_time;
4463 l_gme_batch_header.due_date := p_nb_time;
4464 l_gme_batch_header.batch_type := 0; /* 0 for batches, 10 for FPOs */
4465 l_gme_batch_header.update_inventory_ind := 'Y' ;
4466 -- l_gme_batch_header.RECIPE_VALIDITY_RULE_ID := l_eff_id ;
4467
4468 IF G_TRACE_ON = 1 THEN
4469 print_debug('Calling the GMP Create_Batch Wrapper API', 're_batch', 9);
4470 END IF;
4471
4472 gmp_batch_wrapper_pkg.create_batch(
4473 p_api_version => 2.0
4474 ,p_validation_level => 100
4475 ,p_init_msg_list => FND_API.G_TRUE
4476 ,p_commit => FND_API.G_TRUE
4477 ,x_message_count => x_message_count
4478 ,x_message_list => x_message_list
4479 ,x_return_status => return_status
4480 ,p_org_code => NULL
4481 ,p_batch_header_rec => l_gme_batch_header
4482 ,x_batch_header_rec => x_gme_batch_header
4483 ,p_batch_size => p_qty
4484 ,p_batch_size_uom => p_uom
4485 ,p_creation_mode => 'PRODUCT'
4486 ,p_recipe_id => NULL
4487 ,p_recipe_no => NULL
4488 ,p_recipe_version => NULL
4489 ,p_product_no => NULL
4490 ,p_item_revision => NULL
4491 ,p_product_id => p_item_id
4492 ,p_ignore_qty_below_cap => FND_API.G_TRUE
4493 ,p_use_workday_cal => NULL
4494 ,p_contiguity_override => NULL
4495 ,p_use_least_cost_validity_rule => FND_API.G_FALSE
4496 ,x_exception_material_tbl => x_exception_material_tbl
4497 );
4498
4499 -- IF (return_status <> 'S') THEN -- nsinghi bug 5931402
4500 IF (return_status NOT IN ('S', 'V')) THEN
4501 IF G_TRACE_ON = 1 THEN
4502 print_debug('Could not create batch. gmp_batch_wrapper_pkg.create_batch returned with status '||return_status
4503 , 're_batch', 9);
4504 print_debug('x_message_count '||to_char(x_message_count)||', x_message_list '||x_message_list
4505 , 're_batch', 9);
4506 END IF;
4507 RAISE batch_exc;
4508 ELSE
4509 IF G_TRACE_ON = 1 THEN
4510 print_debug('Created batch with batch_id '||to_char(x_gme_batch_header.batch_id)
4511 , 're_batch', 9);
4512 END IF;
4513 END IF;
4514
4515 -- END IF; /* For l_eff_id <> NULL */
4516
4517 END IF; /* For p_execution_enabled <> 'Y' AND p_recipe_enabled <> 'Y' */
4518
4519 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
4520 x_ret_mesg := '';
4521
4522 EXCEPTION
4523 WHEN OTHERS THEN
4524 IF G_TRACE_ON = 1 THEN
4525 print_debug(sqlcode || ', ' || sqlerrm, 're_batch', 1);
4526 END IF;
4527
4528 SELECT meaning
4529 INTO x_ret_mesg
4530 FROM mfg_lookups
4531 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
4532 AND lookup_code = 2;
4533
4534 x_ret_stat := FND_API.G_RET_STS_ERROR;
4535 END re_batch;
4536
4537
4538 --This procedure is to copmpute the loaded quantities for a WMS enabled org.
4539 --This is the qty loaded using Pick load and not yet Pick dropped.
4540 FUNCTION get_loaded_qty( p_org_id NUMBER
4541 , p_subinv VARCHAR2
4542 , p_level NUMBER
4543 , p_item_id NUMBER
4544 , p_net_rsv NUMBER
4545 , p_net_unrsv NUMBER ) RETURN NUMBER IS
4546
4547 CURSOR c_loaded_quantities_v IS
4548 SELECT SUM(quantity) FROM wms_loaded_quantities_v
4549 WHERE inventory_item_id = p_item_id
4550 AND subinventory_code = nvl(p_subinv , subinventory_code )
4551 AND organization_id = p_org_id;
4552
4553 l_loaded_qty NUMBER := 0 ;
4554
4555 BEGIN
4556 --The loaded quantity will be calculated only if the report is ran with
4557 --parameters "reserved demand=>No , unreserved demand=>No".
4558 --If the parameters are "yes", the MTL_RESERVATIONS or MMTT will be accounted for this qty.
4559
4560 IF ( p_net_rsv = 2 and p_net_unrsv = 2 )THEN
4561
4562 OPEN c_loaded_quantities_v ;
4563 FETCH c_loaded_quantities_v INTO l_loaded_qty;
4564 CLOSE c_loaded_quantities_v;
4565 END IF;
4566
4567 IF g_trace_on = 1 THEN
4568 print_debug('(WMS only) Total quantity loaded : ' ||
4569 to_char(l_loaded_qty), 'get_loaded_qty', 9);
4570 END IF;
4571
4572 return ( l_loaded_qty ) ;
4573 EXCEPTION
4574 WHEN OTHERS THEN
4575 IF G_TRACE_ON = 1 THEN
4576 print_debug(sqlcode || ', ' || sqlerrm, 'get_loaded_qty', 1);
4577 END IF;
4578 RAISE;
4579 END get_loaded_qty;
4580
4581 -- Bug9122329, get_item_uom_code function added to fetch the uom_code from
4582 -- MUOM table based on unit_of_measure info from the po_requisitions_interface_all.
4583
4584 FUNCTION get_item_uom_code (p_uom_name VARCHAR2) RETURN VARCHAR2 IS
4585
4586 l_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%type := NULL;
4587
4588 BEGIN
4589
4590 SELECT uom_code
4591 INTO l_uom_code
4592 FROM mtl_units_of_measure_vl
4593 WHERE unit_of_measure = p_uom_name;
4594
4595 RETURN (l_uom_code);
4596
4597 EXCEPTION
4598 WHEN OTHERS THEN
4599 IF G_TRACE_ON = 1 THEN
4600 print_debug('Error in get_item_uom_code function', 'get_item_uom_code', 9);
4601 END IF;
4602 RAISE;
4603
4604 END get_item_uom_code;
4605
4606 /* nsinghi MIN-MAX INVCONV end */
4607
4608 END INV_Minmax_PVT;