[Home] [Help]
PACKAGE BODY: APPS.INV_MINMAX_PVT
Source
1 PACKAGE BODY INV_Minmax_PVT AS
2 /* $Header: INVVMMXB.pls 120.12.12010000.2 2008/07/29 13:48:48 ptkumar 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 SELECT NVL(SUM(DECODE(uom_code,
1331 l_puom,quantity,
1332 INV_CONVERT.INV_UM_CONVERT(p_item_id,null,quantity,uom_code,l_puom,null,null)
1333 )),0)
1334 INTO l_qty
1335 FROM po_requisitions_interface_all
1336 WHERE destination_organization_id = p_org_id
1337 AND item_id = p_item_id
1338 AND p_include_po = 1
1339 AND (p_level = 1 or destination_subinventory = p_subinv)
1340 AND need_by_date <= (trunc(p_s_cutoff) + 1 - (1/(24*60*60)))
1341 AND NVL(process_flag,'@@@') <> 'ERROR'
1342 AND (NVL(source_organization_id,-1) <> p_org_id OR
1343 (source_organization_id = p_org_id AND
1344 (( p_include_nonnet = 2 AND
1345 EXISTS (SELECT 'x'
1346 FROM mtl_secondary_inventories sub1
1347 WHERE sub1.organization_id = source_organization_id
1348 AND sub1.secondary_inventory_name = source_subinventory
1349 AND sub1.availability_type <> 1)) OR
1350 p_level = 2)
1351 ))
1352 AND (destination_subinventory IS NULL OR
1353 EXISTS (SELECT 1
1354 FROM mtl_secondary_inventories sub2
1355 WHERE secondary_inventory_name = destination_subinventory
1356 AND destination_subinventory = NVL(p_subinv,
1357 destination_subinventory)
1358 AND sub2.organization_id = p_org_id
1359 AND sub2.availability_type = decode(p_include_nonnet,
1360 1,sub2.availability_type,1)) OR
1361 p_level = 2);
1362
1363 IF G_TRACE_ON = 1 THEN
1364 print_debug('Supply from po_requisitions_interface_all: ' || to_char(l_qty)
1365 , 'get_supply_qty'
1366 , 9);
1367 END IF;
1368 l_total := l_total + NVL(l_qty,0);
1369
1370 --
1371 -- WIP_JOB_SCHEDULE_INTERFACE, processed immediately, hence not included
1372 --
1373 -- Supply FROM Flow to be included in org level only
1374 --
1375 IF p_level = 1
1376 THEN
1377 SELECT SUM(NVL(planned_quantity,0)
1378 - NVL(quantity_completed,0))
1379 INTO l_qty
1380 FROM wip_flow_schedules
1381 WHERE organization_id = p_org_id
1382 AND primary_item_id = p_item_id
1383 AND status = 1
1384 AND scheduled_flag = 1 -- Bug 3151797
1385 --Bug 2647862
1386 AND scheduled_completion_date <= p_s_cutoff + 0.99999 /* bug no 6009682 */
1387 AND (NVL(planned_quantity,0)
1388 - NVL(quantity_completed,0)) > 0;
1389
1390 IF G_TRACE_ON = 1 THEN
1391 print_debug('Supply from WIP flow schedules: ' || to_char(l_qty)
1392 , 'get_supply_qty'
1393 , 9);
1394 END IF;
1395 l_total := l_total + NVL(l_qty,0);
1396 END IF;
1397
1398 RETURN(l_total);
1399
1400 EXCEPTION
1401 WHEN others THEN
1402 IF c_po_qty%ISOPEN THEN
1403 CLOSE c_po_qty;
1404 END IF;
1405 IF G_TRACE_ON = 1 THEN
1406 print_debug(sqlcode || ', ' || sqlerrm, 'get_supply_qty', 1);
1407 END IF;
1408 RAISE;
1409 END get_supply_qty;
1410
1411
1412
1413 FUNCTION get_demand_qty( p_org_id NUMBER
1414 , p_subinv VARCHAR2
1415 , p_level NUMBER
1416 , p_item_id NUMBER
1417 , p_d_cutoff DATE
1418 , p_include_nonnet NUMBER
1419 , p_net_rsv NUMBER
1420 , p_net_unrsv NUMBER
1421 , p_net_wip NUMBER
1422 /* nsinghi MIN-MAX INVCONV start */
1423 , p_process_org VARCHAR2
1424 /* nsinghi MIN-MAX INVCONV end */
1425 ) RETURN NUMBER IS
1426
1427 qty NUMBER := 0;
1428 total NUMBER := 0;
1429 l_total_demand_qty NUMBER := 0;
1430 l_demand_qty NUMBER := 0;
1431 l_total_reserve_qty NUMBER := 0;
1432 l_pick_released_qty NUMBER := 0;
1433 l_staged_qty NUMBER := 0;
1434 l_sub_reserve_qty NUMBER := 0;
1435 l_allocated_qty NUMBER := 0;
1436 l_loaded_qty NUMBER := 0; /*Bug 6240025 */
1437
1438 BEGIN
1439 IF G_TRACE_ON = 1 THEN
1440 print_debug('p_org_id: ' || to_char(p_org_id) ||
1441 ', p_subinv: ' || p_subinv ||
1442 ', p_level: ' || to_char(p_level) ||
1443 ', p_item_id: ' || to_char(p_item_id) ||
1444 ', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
1445 ', p_include_nonnet: ' || to_char(p_include_nonnet) ||
1446 ', p_net_rsv: ' || to_char(p_net_rsv) ||
1447 ', p_net_unrsv: ' || to_char(p_net_unrsv) ||
1448 ', p_net_wip: ' || to_char(p_net_wip)
1449 , 'get_demand_qty'
1450 , 9);
1451 END IF;
1452
1453 --
1454 -- select unreserved qty from mtl_demand for non oe rows.
1455 --
1456 IF p_net_unrsv = 1 THEN
1457 /*4518296*/
1458 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1459 NVL(COMPLETED_QUANTITY,0)))
1460 into qty
1461 from mtl_demand
1462 WHERE RESERVATION_TYPE = 1
1463 AND parent_demand_id IS NULL
1464 AND ORGANIZATION_ID = p_org_id
1465 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1466 NVL(COMPLETED_QUANTITY,0))
1467 and INVENTORY_ITEM_ID = p_item_id
1468 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1469 and demand_source_type not in (2,8,12)
1470 and (p_level = 1 or
1471 SUBINVENTORY = p_subinv) -- Included later for ORG Level
1472 and (SUBINVENTORY is null or
1473 p_level = 2 or
1474 EXISTS (SELECT 1
1475 FROM MTL_SECONDARY_INVENTORIES S
1476 WHERE S.ORGANIZATION_ID = p_org_id
1477 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1478 AND S.availability_type = DECODE(p_include_nonnet,
1479 1,
1480 S.availability_type,
1481 1)))
1482 /* nsinghi MIN-MAX INVCONV start */
1483 AND (locator_id IS NULL OR
1484 p_level = 2 OR
1485 EXISTS (SELECT 1 FROM mtl_item_locations mil
1486 WHERE mil.organization_id = p_org_id
1487 AND mil.inventory_location_id = locator_id
1488 AND mil.subinventory_code = NVL(subinventory, mil.subinventory_code)
1489 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1490 AND (lot_number IS NULL OR
1491 p_level = 2 OR
1492 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1493 WHERE mln.organization_id = p_org_id
1494 AND mln.lot_number = lot_number
1495 AND mln.inventory_item_id = p_item_id
1496 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
1497 /* nsinghi MIN-MAX INVCONV end */
1498
1499 IF G_TRACE_ON = 1 THEN
1500 print_debug('Demand from mtl_demand: ' || to_char(qty), 'get_demand_qty', 9);
1501 END IF;
1502 total := total + NVL(qty,0);
1503 END IF;
1504
1505 --
1506 -- select the reserved quantity from mtl_reservations for non OE rows
1507 --
1508 IF p_net_rsv = 1 THEN
1509 select sum(PRIMARY_RESERVATION_QUANTITY)
1510 into qty
1511 from mtl_reservations
1512 where ORGANIZATION_ID = p_org_id
1513 and INVENTORY_ITEM_ID = p_item_id
1514 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1515 and demand_source_type_id not in (2,8,12)
1516 and (p_level = 1 or
1517 SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
1518 and (SUBINVENTORY_CODE is null or
1519 p_level = 2 or
1520 EXISTS (SELECT 1
1521 FROM MTL_SECONDARY_INVENTORIES S
1522 WHERE S.ORGANIZATION_ID = p_org_id
1523 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1524 AND S.availability_type = DECODE(p_include_nonnet,
1525 1,
1526 S.availability_type,
1527 1)))
1528 /* nsinghi MIN-MAX INVCONV start */
1529 AND (locator_id IS NULL OR
1530 p_level = 2 OR
1531 EXISTS (SELECT 1 FROM mtl_item_locations mil
1532 WHERE mil.organization_id = p_org_id
1533 AND mil.inventory_location_id = locator_id
1534 AND mil.subinventory_code = NVL(subinventory_code, mil.subinventory_code)
1535 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1536 AND (lot_number IS NULL OR
1537 p_level = 2 OR
1538 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1539 WHERE mln.organization_id = p_org_id
1540 AND mln.lot_number = lot_number
1541 AND mln.inventory_item_id = p_item_id
1542 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
1543 /* nsinghi MIN-MAX INVCONV end */
1544
1545 IF G_TRACE_ON = 1 THEN
1546 print_debug('Demand (reserved qty) for non OE rows in mtl_reservations: ' || to_char(qty)
1547 , 'get_demand_qty'
1548 , 9);
1549 END IF;
1550 total := total + NVL(qty,0);
1551 END IF;
1552
1553 --
1554 -- get the total demand which is the difference between the
1555 -- ordered qty. and the shipped qty.
1556 -- This gives the total demand including the reserved
1557 -- and the unreserved material.
1558 --
1559 -- Bug 2333526: For sub level planning we need to compute
1560 -- the staged qty. The existing WHERE clause makes sure
1561 -- we only do this when the order is sourced from the
1562 -- planning sub: level = 1... or SUBINVENTORY = p_subinv
1563 --
1564 -- Bug 2350243: For sub level, calculate pick released
1565 -- (move order) qty
1566 --
1567
1568 -- Bug 3480523, from patch I onwards schedule_ship_date is being populated
1569 -- with time component, hence truncating it to get the same day demand. These
1570 -- changes are also in mtl_reservation queries.
1571 if p_net_unrsv = 1 then
1572 select SUM(inv_decimals_pub.get_primary_quantity( ship_from_org_id
1573 , inventory_item_id
1574 , order_quantity_uom
1575 , NVL(ordered_quantity,0)) -
1576 get_shipped_qty(p_org_id, p_item_id, ool.line_id)),
1577 SUM(DECODE(p_level,
1578 2, get_staged_qty( p_org_id
1579 , p_subinv
1580 , p_item_id
1581 , ool.line_id
1582 , p_include_nonnet),
1583 0)
1584 ),
1585 SUM(DECODE(p_level,
1586 2, get_pick_released_qty( p_org_id
1587 , p_subinv
1588 , p_item_id
1589 , ool.line_id),
1590 0)
1591 )
1592 into l_total_demand_qty, l_staged_qty, l_pick_released_qty
1593 from oe_order_lines_all ool
1594 where ship_from_org_id = p_org_id
1595 and open_flag = 'Y'
1596 AND visible_demand_flag = 'Y'
1597 AND shipped_quantity is null
1598 and INVENTORY_ITEM_ID = p_item_id
1599 and schedule_ship_date <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1600 AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
1601 , 10, 8
1602 , DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
1603 and ((p_level = 1
1604 AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
1605 , 10, 8
1606 , DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8)
1607 OR SUBINVENTORY = p_subinv) -- Included later for ORG Level
1608 and (SUBINVENTORY is null or
1609 p_level = 2 or
1610 EXISTS (SELECT 1
1611 FROM MTL_SECONDARY_INVENTORIES S
1612 WHERE S.ORGANIZATION_ID = p_org_id
1613 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1614 AND S.availability_type = DECODE(p_include_nonnet,
1615 1,
1616 S.availability_type,
1617 1)));
1618 IF G_TRACE_ON = 1 THEN
1619 print_debug('Demand from sales orders: ' ||
1620 ' Ordered: ' || to_char(l_total_demand_qty) ||
1621 ', Pick released: ' || to_char(l_pick_released_qty) ||
1622 ', Staged: ' || to_char(l_staged_qty)
1623 , 'get_demand_qty'
1624 , 9);
1625 END IF;
1626 end if;
1627
1628 --
1629 -- Find out the reserved qty for the material from mtl_reservations
1630 --
1631 -- Since total demand = reserved + unreserved, and we know total
1632 -- demand from oe_order_lines_all (above) we only need to query
1633 -- mtl_reservations if the user wants one of the following:
1634 --
1635 -- 1) Only reserved: (p_net_rsv = 1 and p_net_unrsv = 2)
1636 --
1637 -- OR
1638 --
1639 -- 2) Only unreserved: (p_net_rsv = 2 and p_net_unrsv = 1)
1640 --
1641
1642 IF ((p_net_rsv = 1 and p_net_unrsv = 2)
1643 OR
1644 (p_net_rsv = 2 and p_net_unrsv = 1))
1645 THEN
1646 select sum(PRIMARY_RESERVATION_QUANTITY)
1647 into l_total_reserve_qty
1648 from mtl_reservations
1649 WHERE ORGANIZATION_ID = p_org_id
1650 and INVENTORY_ITEM_ID = p_item_id
1651 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1652 and demand_source_type_id in (2,8,12)
1653 and ((p_level = 1 AND demand_source_type_id <> 8) OR
1654 SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
1655 and (SUBINVENTORY_CODE is null or
1656 p_level = 2 or
1657 EXISTS (SELECT 1
1658 FROM MTL_SECONDARY_INVENTORIES S
1659 WHERE S.ORGANIZATION_ID = p_org_id
1660 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1661 AND S.availability_type = DECODE(p_include_nonnet,
1662 1,
1663 S.availability_type,
1664 1)))
1665 /* nsinghi MIN-MAX INVCONV start */
1666 AND (locator_id IS NULL OR
1667 p_level = 2 OR
1668 EXISTS (SELECT 1 FROM mtl_item_locations mil
1669 WHERE mil.organization_id = p_org_id
1670 AND mil.inventory_location_id = locator_id
1671 AND mil.subinventory_code = NVL(subinventory_code, mil.subinventory_code)
1672 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1673 AND (lot_number IS NULL OR
1674 p_level = 2 OR
1675 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1676 WHERE mln.organization_id = p_org_id
1677 AND mln.lot_number = lot_number
1678 AND mln.inventory_item_id = p_item_id
1679 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)))
1680 -- Bug 5041763 excluding drop ship demand
1681 and NOT EXISTS (SELECT 1
1682 FROM OE_DROP_SHIP_SOURCES ODSS
1683 WHERE ODSS.LINE_ID = DEMAND_SOURCE_LINE_ID);
1684
1685 /* nsinghi MIN-MAX INVCONV end */
1686
1687 IF G_TRACE_ON = 1 THEN
1688 print_debug('Reserved demand (sales orders): ' || to_char(l_total_reserve_qty)
1689 , 'get_demand_qty'
1690 , 9);
1691 END IF;
1692 END IF;
1693
1694
1695 --
1696 -- Bug 3238390, we need to take care of reservations with sub but sales order
1697 -- with no sub for sub level planning. Adding the below query
1698 --
1699 IF (p_level = 2 and (p_net_rsv = 1 or p_net_unrsv = 1)) THEN
1700
1701 select sum(mr.PRIMARY_RESERVATION_QUANTITY) into l_sub_reserve_qty
1702 from mtl_reservations mr, oe_order_lines_all ool
1703 where mr.organization_id = p_org_id
1704 AND mr.inventory_item_id = p_item_id
1705 AND mr.demand_source_line_id = ool.line_id
1706 AND mr.demand_source_type_id in (2,8,12)
1707 AND ool.subinventory is NULL
1708 AND ool.open_flag = 'Y'
1709 AND ool.visible_demand_flag = 'Y'
1710 AND ool.shipped_quantity is null
1711 AND mr.REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1712 AND mr.subinventory_code IS NOT NULL
1713 AND mr.subinventory_code = p_subinv;
1714
1715 print_debug('Reserved demand (sales orders with no sub and reservations with sub): ' || to_char(l_sub_reserve_qty)
1716 , 'get_demand_qty'
1717 , 9);
1718
1719 END IF;
1720
1721 IF (p_level = 2 and p_net_rsv = 1) THEN
1722
1723 BEGIN
1724
1725 SELECT NVL(SUM(primary_quantity),0)
1726 INTO l_allocated_qty
1727 FROM mtl_material_transactions_temp mmtt
1728 WHERE inventory_item_id = p_item_id
1729 AND organization_id = p_org_id
1730 AND subinventory_code = p_subinv
1731 AND transfer_subinventory <> p_subinv
1732 AND NVL(transaction_status, 1) = 2
1733 AND transaction_source_type_id in (2,8)
1734 AND not exists (SELECT 1 from mtl_reservations
1735 WHERE reservation_id = mmtt.reservation_id
1736 AND nvl(subinventory_code, '@@@') = p_subinv)
1737 AND exists (SELECT 1 from mtl_txn_request_lines
1738 WHERE line_id = mmtt.move_order_line_id
1739 AND from_subinventory_code is null
1740 AND line_status NOT IN (5,6)
1741 AND date_required <= p_d_cutoff + 0.99999); /* bug no 6009682 */
1742
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 l_allocated_qty := 0;
1746 END;
1747 END IF;
1748
1749 print_debug('Allocated demand for subinventory: ' || to_char(l_allocated_qty)
1750 , 'get_demand_qty'
1751 , 9);
1752
1753 --
1754 -- total demand is calculated as follows:
1755 -- if we have to consider both unreserved matl and reserved matl. then the
1756 -- demand is simply the total demand = ordered qty - shipped qty.
1757 -- Bug 2333526: Deduct staged qty for sub level. (l_staged_qty
1758 -- is always set to 0 for org level planning).
1759 -- Bug 3238390, add reserved qty for sales orders with no sub
1760 -- and reservation with sub for sub level planning.
1761 -- elsif we have to take into account only reserved matl. then the
1762 -- demand is simply the reservations from mtl_reservations for the matl.
1763 -- elsif we have to take into account just the unreserved matl. then the
1764 -- demand is total demand - the reservations for the material.
1765 -- Bug 3238390, add reserved qty for sales orders with no sub
1766 -- and reservation with sub for sub level planning, so that demand doesn't go -ve.
1767 IF p_net_unrsv = 1 AND p_net_rsv = 1 THEN
1768 l_demand_qty := NVL(l_total_demand_qty,0)
1769 - NVL(l_staged_qty,0)
1770 - NVL(l_pick_released_qty,0)
1771 + NVL(l_sub_reserve_qty,0)
1772 + NVL(l_allocated_qty,0);
1773
1774 ELSIF p_net_rsv = 1 THEN
1775 l_demand_qty := NVL(l_total_reserve_qty,0) + NVL(l_allocated_qty,0);
1776
1777 ELSIF p_net_unrsv = 1 THEN
1778 l_demand_qty := NVL(l_total_demand_qty,0) - NVL(l_total_reserve_qty,0) + NVL(l_sub_reserve_qty,0);
1779
1780 END IF;
1781 IF G_TRACE_ON = 1 THEN
1782 print_debug('Demand from shippable orders: ' || to_char(l_demand_qty)
1783 , 'get_demand_qty'
1784 , 9);
1785 END IF;
1786 total := total + NVL(l_demand_qty,0);
1787
1788 --
1789 -- Take care of internal orders for org level planning
1790 --
1791 if p_level = 1 then
1792 l_total_demand_qty := 0;
1793 l_demand_qty := 0;
1794 l_total_reserve_qty := 0;
1795
1796 --
1797 -- get the total demand which is the difference between the
1798 -- ordered qty. and the shipped qty.
1799 -- This gives the total demand including the reserved
1800 -- and the unreserved material.
1801 --
1802 -- Bug 2820011. Modified the where clause to make use of source_document_id
1803 -- and source_document_line_id of oe_order_lines_all instead of
1804 -- orig_sys_document_ref and orig_sys_line_ref to identify requisitions
1805 -- and requisition lines uniquely.
1806
1807 if p_net_unrsv = 1 then
1808 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( SHIP_FROM_ORG_ID
1809 , INVENTORY_ITEM_ID
1810 , ORDER_QUANTITY_UOM
1811 , NVL(ordered_quantity,0)) -
1812 get_shipped_qty(p_org_id, p_item_id, so.line_id))
1813 into l_total_demand_qty
1814 from oe_order_lines_all so,
1815 -- po_requisition_headers_all poh,
1816 po_requisition_lines_all pol
1817 where so.SOURCE_DOCUMENT_ID = pol.requisition_header_id
1818 -- and poh.requisition_header_id = pol.requisition_header_id
1819 and so.source_document_line_id = pol.requisition_line_id
1820 and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
1821 (pol.DESTINATION_ORGANIZATION_ID = p_org_id and -- Added code Bug#1012179
1822 ( pol.DESTINATION_TYPE_CODE = 'EXPENSE' OR --Bug#3619239 started
1823 -- Bug 3619239 The functionality is added so that demand from Internal Sales Requisitions are taken
1824 -- into consideration if Destination Type is Inventory and Destination Subinventory is Non Quantity Tracked
1825 ( pol.DESTINATION_TYPE_CODE = 'INVENTORY'
1826 AND pol.DESTINATION_SUBINVENTORY IS NOT NULL
1827 AND EXISTS (select 1 from
1828 MTL_SECONDARY_INVENTORIES
1829 where SECONDARY_INVENTORY_NAME = pol.DESTINATION_SUBINVENTORY
1830 and ORGANIZATION_ID = pol.DESTINATION_ORGANIZATION_ID
1831 and QUANTITY_TRACKED = 2)
1832 )
1833 )-- Bug#3619239 ended
1834 )
1835 )
1836 and so.ship_from_org_ID = p_org_id
1837 and so.open_flag = 'Y'
1838 AND so.visible_demand_flag = 'Y'
1839 AND shipped_quantity is null
1840 and so.INVENTORY_ITEM_ID = p_item_id
1841 and schedule_ship_date <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1842 and DECODE(so.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(so.LINE_CATEGORY_CODE, 'ORDER',2,12)) = 8
1843 and (SUBINVENTORY is null or
1844 EXISTS (SELECT 1
1845 FROM MTL_SECONDARY_INVENTORIES S
1846 WHERE S.ORGANIZATION_ID = p_org_id
1847 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1848 AND S.availability_type = DECODE(p_include_nonnet,
1849 1,
1850 S.availability_type,
1851 1)));
1852
1853 IF G_TRACE_ON = 1 THEN
1854 print_debug('Total demand (internal orders): ' || to_char(l_total_demand_qty)
1855 , 'get_demand_qty'
1856 , 9);
1857 END IF;
1858 end if;
1859
1860 --
1861 -- Find out the reserved qty for the material from mtl_reservations
1862 --
1863 IF ((p_net_rsv = 1 and p_net_unrsv = 2)
1864 OR
1865 (p_net_rsv = 2 and p_net_unrsv = 1))
1866 THEN
1867 --
1868 -- Include the reserved demand from mtl_reservations
1869 --
1870 select sum(PRIMARY_RESERVATION_QUANTITY)
1871 into l_total_reserve_qty
1872 from mtl_reservations md, oe_order_lines_all so,
1873 -- po_req_distributions_all pod, Bug 5934651
1874 po_requisition_lines_all pol
1875 where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
1876 -- and to_number(so.ORIG_SYS_LINE_REF) = pod.DISTRIBUTION_ID --Bug#2883172
1877 and so.SOURCE_DOCUMENT_ID = pol.requisition_header_id -- Bug 5934651
1878 and so.source_document_line_id = pol.requisition_line_id
1879 -- and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
1880 and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
1881 (pol.DESTINATION_ORGANIZATION_ID = p_org_id
1882 and -- Added code Bug#1012179
1883 ( pol.DESTINATION_TYPE_CODE = 'EXPENSE' OR -- Bug#3619239 started
1884 -- Bug 3619239 The functionality is added so that demand from Internal Sales Requisitions are taken
1885 -- into consideration if Destination Type is Inventory and Destination Subinventory is Non Quantity Tracked
1886 ( pol.DESTINATION_TYPE_CODE = 'INVENTORY'
1887 AND pol.DESTINATION_SUBINVENTORY IS NOT NULL
1888 AND EXISTS (select 1 from
1889 MTL_SECONDARY_INVENTORIES
1890 where SECONDARY_INVENTORY_NAME = pol.DESTINATION_SUBINVENTORY
1891 and ORGANIZATION_ID = pol.DESTINATION_ORGANIZATION_ID
1892 and QUANTITY_TRACKED = 2)
1893 )
1894 )-- Bug#3619239 ended
1895 )
1896 )
1897 and ORGANIZATION_ID = p_org_id
1898 and md.INVENTORY_ITEM_ID = p_item_id
1899 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
1900 and demand_source_type_id = 8
1901 and (SUBINVENTORY_CODE is null or
1902 EXISTS (SELECT 1
1903 FROM MTL_SECONDARY_INVENTORIES S
1904 WHERE S.ORGANIZATION_ID = p_org_id
1905 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1906 AND S.availability_type = DECODE(p_include_nonnet,
1907 1,
1908 S.availability_type,
1909 1)))
1910 /* nsinghi MIN-MAX INVCONV start */
1911 AND (md.locator_id IS NULL OR
1912 p_level = 2 OR
1913 EXISTS (SELECT 1 FROM mtl_item_locations mil
1914 WHERE mil.organization_id = p_org_id
1915 AND mil.inventory_location_id = md.locator_id
1916 AND mil.subinventory_code = NVL(md.subinventory_code, mil.subinventory_code)
1917 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
1918 AND (md.lot_number IS NULL OR
1919 p_level = 2 OR
1920 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
1921 WHERE mln.organization_id = p_org_id
1922 AND mln.lot_number = md.lot_number
1923 AND mln.inventory_item_id = p_item_id
1924 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
1925 /* nsinghi MIN-MAX INVCONV end */
1926
1927 IF G_TRACE_ON = 1 THEN
1928 print_debug('Reserved demand (internal orders): ' || to_char(l_total_reserve_qty)
1929 , 'get_demand_qty'
1930 , 9);
1931 END IF;
1932 END IF;
1933
1934 --
1935 -- total demand is calculated as follows:
1936 -- if we have to consider both unreserved matl and reserved matl. then the
1937 -- demand is simply the total demand = ordered qty - shipped qty.
1938 -- elsif we have to take into account only reserved matl. then the
1939 -- demand is simply the reservations from mtl_reservations for the matl.
1940 -- elsif we have to take into account just the unreserved matl. then the
1941 -- demand is total demand - the reservations for the material.
1942 --
1943 if p_net_unrsv = 1 and p_net_rsv = 1 then
1944 l_demand_qty := NVL(l_total_demand_qty,0);
1945
1946 elsif p_net_rsv = 1 then
1947 l_demand_qty := NVL(l_total_reserve_qty,0);
1948
1949 elsif p_net_unrsv = 1 then
1950 l_demand_qty := NVL(l_total_demand_qty,0) - NVL(l_total_reserve_qty,0);
1951 end if;
1952 IF G_TRACE_ON = 1 THEN
1953 print_debug('Demand from internal orders: ' || to_char(l_demand_qty)
1954 , 'get_demand_qty'
1955 , 9);
1956 END IF;
1957 total := total + NVL(l_demand_qty,0);
1958
1959 end if; -- end if level=1
1960
1961 --
1962 /* Bug 3364512. Demand is double for back-to-back sales orders after
1963 auto-create requisition and for sales orders with ATO items after
1964 auto-create WIP job. Commenting the below code which fetches duplicate
1965 demand */
1966
1967 -- WIP Reservations from mtl_demand
1968 --
1969 /* IF p_level = 1 THEN
1970 --
1971 -- SUBINVENTORY IS Always expected to be Null when Reservation_type is 3.
1972 --
1973 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1974 NVL(COMPLETED_QUANTITY,0)))
1975 into qty
1976 from mtl_demand
1977 where RESERVATION_TYPE = 3
1978 and ORGANIZATION_ID = p_org_id
1979 and PRIMARY_UOM_QUANTITY >
1980 GREATEST(NVL(RESERVATION_QUANTITY,0), NVL(COMPLETED_QUANTITY,0))
1981 and INVENTORY_ITEM_ID = p_item_id
1982 and REQUIREMENT_DATE <= p_d_cutoff
1983 and p_net_rsv = 1;
1984
1985 IF G_TRACE_ON = 1 THEN
1986 print_debug('WIP Reservations from mtl_demand: ' || to_char(qty)
1987 , 'get_demand_qty'
1988 , 9);
1989 END IF;
1990 total := total + NVL(qty,0);
1991 END IF; */
1992
1993 --
1994 -- Wip Components are to be included at the Org Level Planning only.
1995 -- Qty Issued Substracted from the Qty Required
1996 --
1997 if (p_net_wip = 1 and p_level = 1)
1998 then
1999
2000 /* nsinghi MIN-MAX INVCONV start */
2001
2002 IF p_process_org = 'Y' THEN
2003
2004 /* Here we need include the query to include OPM as source of demand.
2005 Since GME will always give the complete demand (including reserved demand)
2006 so subtracting the reserved demand as reserved demand will be considered
2007 above from mtl_reservations query. */
2008
2009 SELECT
2010 SUM (( NVL((NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty), 0) *
2011 (d.original_primary_qty/d.original_qty)) - NVL(mtr.primary_reservation_quantity,0))
2012 INTO qty
2013 FROM gme_material_details d
2014 , gme_batch_header h
2015 , mtl_reservations mtr
2016 WHERE h.batch_type IN (0,10)
2017 AND h.batch_status IN (1,2)
2018 AND h.batch_id = d.batch_id
2019 AND d.line_type = -1
2020 AND NVL(d.original_qty, 0) <> 0
2021 AND d.organization_id = p_org_id
2022 AND d.inventory_item_id = p_item_id
2023 AND d.batch_id = mtr.demand_source_header_id (+)
2024 AND d.material_detail_id = mtr.demand_source_line_id (+)
2025 AND d.inventory_item_id = mtr.inventory_item_id (+)
2026 AND d.organization_id = mtr.organization_id (+)
2027 AND (( NVL((NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty), 0) *
2028 (d.original_primary_qty/d.original_qty)) - NVL(mtr.primary_reservation_quantity,0)) > 0
2029 AND NVL(mtr.demand_source_type_id, 5) = 5
2030 AND d.material_requirement_date <= p_d_cutoff
2031 AND (mtr.subinventory_code IS NULL OR
2032 EXISTS (SELECT 1
2033 FROM mtl_secondary_inventories s
2034 WHERE s.organization_id = p_org_id
2035 AND s.secondary_inventory_name = mtr.subinventory_code
2036 AND s.availability_type = DECODE(p_include_nonnet,1,s.availability_type,1)))
2037 AND (mtr.locator_id IS NULL OR
2038 EXISTS (SELECT 1 FROM mtl_item_locations mil
2039 WHERE mil.organization_id = p_org_id
2040 AND mil.inventory_location_id = mtr.locator_id
2041 AND mil.subinventory_code = NVL(mtr.subinventory_code, mil.subinventory_code)
2042 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
2043 AND (mtr.lot_number IS NULL OR
2044 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
2045 WHERE mln.organization_id = p_org_id
2046 AND mln.lot_number = mtr.lot_number
2047 AND mln.inventory_item_id = p_item_id
2048 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
2049
2050 IF G_TRACE_ON = 1 THEN
2051 print_debug('Batch Material requirements for OPM Batches : ' || to_char(qty)
2052 , 'get_demand_qty'
2053 , 9);
2054 END IF;
2055 total := total + NVL(qty,0);
2056
2057 ELSE
2058 /* nsinghi MIN-MAX INVCONV end */
2059 /*4518296*/
2060
2061 select sum(o.required_quantity - o.quantity_issued)
2062 into qty
2063 from wip_discrete_jobs d, wip_requirement_operations o
2064 where o.wip_entity_id = d.wip_entity_id
2065 and o.organization_id = d.organization_id
2066 and d.organization_id = p_org_id
2067 and o.inventory_item_id = p_item_id
2068 and o.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2069 and o.required_quantity > 0
2070 and o.required_quantity > o.quantity_issued
2071 and o.operation_seq_num > 0
2072 and d.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
2073 and o.wip_supply_type not in (5,6); -- Included 5 from the selection Bug#4488415
2074
2075 IF G_TRACE_ON = 1 THEN
2076 print_debug('WIP component requirements for discrete jobs: ' || to_char(qty)
2077 , 'get_demand_qty'
2078 , 9);
2079 END IF;
2080 total := total + NVL(qty,0);
2081
2082 --
2083 -- Demand Qty to be added for a released repetitive schedule
2084 -- Bug#691471
2085 --
2086 /*4518296*/
2087 select sum(o.required_quantity - o.quantity_issued)
2088 into qty
2089 from wip_repetitive_schedules r, wip_requirement_operations o
2090 where o.wip_entity_id = r.wip_entity_id
2091 and o.repetitive_schedule_id = r.repetitive_schedule_id
2092 and o.organization_id = r.organization_id
2093 and r.organization_id = p_org_id
2094 and o.inventory_item_id = p_item_id
2095 and o.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2096 and o.required_quantity > 0
2097 and o.required_quantity > o.quantity_issued
2098 and o.operation_seq_num > 0
2099 and r.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
2100 and o.wip_supply_type not in (5,6); -- Included 5 from the selection Bug#4488415
2101 IF G_TRACE_ON = 1 THEN
2102 print_debug('WIP component requirements for repetitive schedules: ' || to_char(qty)
2103 , 'get_demand_qty'
2104 , 9);
2105 END IF;
2106 total := total + NVL(qty,0);
2107
2108 END IF; /* p_process_org = 'Y' */
2109
2110 end if;
2111
2112 --
2113 -- Include move orders:
2114 -- Leave out the closed or cancelled lines
2115 -- Select only Issue from Stores for org level planning
2116 -- Also select those lines for sub level planning.
2117 --
2118 -- Exclude move orders created for WIP Issue transaction
2119 -- (txn type = 35, INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE)
2120 -- since these are already taken into account (above) by
2121 -- directly querying the WIP tables for open component requirements
2122 --
2123
2124 -- kkoothan Part of Bug Fix: 2875583
2125 -- Converting the quantities to the primary uom as the quantity
2126 -- and quantity delivered in mtl_txn_request_lines
2127 -- are in transaction uom.
2128
2129 --Bug 3057273, Move order demand should be excluded if net unreserved demand is No.
2130 if p_net_unrsv = 1 then
2131
2132 /*SELECT SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED,0))
2133 INTO qty
2134 FROM MTL_TXN_REQUEST_LINES MTRL,
2135 MTL_TRANSACTION_TYPES MTT
2136 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
2137 AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2138 AND MTRL.ORGANIZATION_ID = p_org_id
2139 AND MTRL.INVENTORY_ITEM_ID = p_item_id
2140 AND MTRL.LINE_STATUS NOT IN (5,6)
2141 AND MTT.TRANSACTION_ACTION_ID = 1
2142 AND (p_level = 1 OR
2143 MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
2144 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
2145 p_level = 2 OR
2146 EXISTS (SELECT 1
2147 FROM MTL_SECONDARY_INVENTORIES S
2148 WHERE S.ORGANIZATION_ID = p_org_id
2149 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
2150 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
2151 1,S.AVAILABILITY_TYPE,1)))
2152 AND MTRL.DATE_REQUIRED <= p_d_cutoff;*/
2153
2154 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
2155 ,p_item_id
2156 , mtrl.uom_code
2157 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
2158 ),0)
2159 INTO qty
2160 FROM MTL_TXN_REQUEST_LINES MTRL,
2161 MTL_TRANSACTION_TYPES MTT
2162 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
2163 AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
2164 AND MTRL.ORGANIZATION_ID = p_org_id
2165 AND MTRL.INVENTORY_ITEM_ID = p_item_id
2166 AND MTRL.LINE_STATUS IN (3,7)--Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
2167 AND MTT.TRANSACTION_ACTION_ID = 1
2168 AND (p_level = 1 OR
2169 MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
2170 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
2171 p_level = 2 OR
2172 EXISTS (SELECT 1
2173 FROM MTL_SECONDARY_INVENTORIES S
2174 WHERE S.ORGANIZATION_ID = p_org_id
2175 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
2176 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
2177 1,S.AVAILABILITY_TYPE,1)))
2178 AND mtrl.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2179 /* nsinghi MIN-MAX INVCONV start */
2180 AND (mtrl.from_locator_id IS NULL OR
2181 p_level = 2 OR
2182 EXISTS (SELECT 1 FROM mtl_item_locations mil
2183 WHERE mil.organization_id = p_org_id
2184 AND mil.inventory_location_id = mtrl.from_locator_id
2185 AND mil.subinventory_code = NVL(mtrl.from_subinventory_code, mil.subinventory_code)
2186 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
2187 AND (mtrl.lot_number IS NULL OR
2188 p_level = 2 OR
2189 EXISTS (SELECT 1 FROM mtl_lot_numbers mln
2190 WHERE mln.organization_id = p_org_id
2191 AND mln.lot_number = mtrl.lot_number
2192 AND mln.inventory_item_id = p_item_id
2193 AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
2194 /* nsinghi MIN-MAX INVCONV end */
2195
2196 IF G_TRACE_ON = 1 THEN
2197 print_debug('Demand from open move orders: ' || to_char(qty), 'get_demand_qty', 9);
2198 END IF;
2199
2200 total := total + NVL(qty,0);
2201
2202 end if;
2203
2204 --
2205 -- Include the sub transfer and the staging transfer move orders
2206 -- for sub level planning
2207 -- Bug 3057273, Move order demand should be excluded if net unreserved demand is No.
2208
2209 IF (p_level = 2 and p_net_unrsv = 1) THEN
2210 -- kkoothan Part of Bug Fix: 2875583
2211 -- Converting the quantities to the primary uom as the quantity
2212 -- and quantity delivered in mtl_txn_request_lines
2213 -- are in transaction uom.
2214
2215 /*SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
2216 INTO qty
2217 FROM mtl_transaction_types mtt,
2218 mtl_txn_request_lines mtrl
2219 WHERE mtt.transaction_action_id IN (2,28)
2220 AND mtt.transaction_type_id = mtrl.transaction_type_id
2221 AND mtrl.organization_id = p_org_id
2222 AND mtrl.inventory_item_id = p_item_id
2223 AND mtrl.from_subinventory_code = p_subinv
2224 AND mtrl.line_status NOT IN (5,6)
2225 AND mtrl.date_required <= p_d_cutoff;*/
2226
2227 SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
2228 ,p_item_id
2229 ,mtrl.uom_code
2230 , mtrl.quantity - NVL(mtrl.quantity_delivered,0))
2231 ),0)
2232 INTO qty
2233 FROM mtl_transaction_types mtt,
2234 mtl_txn_request_lines mtrl
2235 WHERE mtt.transaction_action_id IN (2,28)
2236 AND mtt.transaction_type_id = mtrl.transaction_type_id
2237 AND mtrl.organization_id = p_org_id
2238 AND mtrl.inventory_item_id = p_item_id
2239 AND mtrl.from_subinventory_code = p_subinv
2240 AND MTRL.LINE_STATUS IN (3,7) --Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
2241 AND mtrl.date_required <= p_d_cutoff + 0.99999; /* bug no 6009682 */
2242
2243 IF G_TRACE_ON = 1 THEN
2244 print_debug('Qty pending out due to sub transfers and the staging transfer move orders: '
2245 || to_char(qty)
2246 , 'get_demand_qty'
2247 , 9);
2248 END IF;
2249 total := total + NVL(qty,0);
2250 END IF;
2251
2252 -- Bug 5041763 need to exclude drop ship reservation from on-hand qty to get correct availability
2253 select sum(PRIMARY_RESERVATION_QUANTITY)
2254 into qty
2255 from mtl_reservations
2256 WHERE ORGANIZATION_ID = p_org_id
2257 and INVENTORY_ITEM_ID = p_item_id
2258 and demand_source_type_id = 2
2259 and supply_source_type_id = 13
2260 and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
2261 and ((p_level = 1 ) OR
2262 SUBINVENTORY_CODE = p_subinv)
2263 and ( SUBINVENTORY_CODE is null or
2264 p_level = 2 or
2265 EXISTS (SELECT 1
2266 FROM MTL_SECONDARY_INVENTORIES S
2267 WHERE S.ORGANIZATION_ID = p_org_id
2268 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
2269 AND S.availability_type = DECODE(p_include_nonnet,
2270 1,
2271 S.availability_type,
2272 1)))
2273 and EXISTS (SELECT 1
2274 FROM OE_DROP_SHIP_SOURCES ODSS
2275 WHERE ODSS.LINE_ID = DEMAND_SOURCE_LINE_ID);
2276 total := total + NVL(qty,0);
2277 --Bug 6240025 BEGIN
2278 l_loaded_qty := get_loaded_qty(p_org_id
2279 , p_subinv
2280 , p_level
2281 , p_item_id
2282 , p_net_rsv
2283 , p_net_unrsv);
2284 total := total+NVL(l_loaded_qty,0);
2285 --Bug 6240025 END
2286 return(total);
2287
2288 exception
2289 when others then
2290 IF G_TRACE_ON = 1 THEN
2291 print_debug(sqlcode || ', ' || sqlerrm, 'get_demand_qty', 1);
2292 END IF;
2293 RAISE;
2294 end get_demand_qty;
2295
2296
2297
2298 FUNCTION get_shipped_qty( p_organization_id IN NUMBER
2299 , p_inventory_item_id IN NUMBER
2300 , p_order_line_id IN NUMBER) RETURN NUMBER IS
2301
2302 l_shipped_qty NUMBER := 0;
2303
2304 BEGIN
2305
2306 --
2307 -- Only look at source types 2 and 8 (sales orders, internal orders)
2308 --
2309 SELECT SUM(primary_quantity)
2310 INTO l_shipped_qty
2311 FROM mtl_material_transactions
2312 WHERE transaction_action_id = 1
2313 AND source_line_id = p_order_line_id
2314 AND organization_id = p_organization_id
2315 AND inventory_item_id = p_inventory_item_id
2316 AND transaction_source_type_id in (2,8);
2317
2318 IF l_shipped_qty IS NULL THEN
2319 l_shipped_qty := 0;
2320 ELSE
2321 l_shipped_qty := -1 * l_shipped_qty;
2322 END IF;
2323
2324 RETURN l_shipped_qty;
2325
2326 END get_shipped_qty;
2327
2328
2329
2330 FUNCTION get_staged_qty( p_org_id NUMBER
2331 , p_subinv VARCHAR2
2332 , p_item_id NUMBER
2333 , p_order_line_id NUMBER
2334 , p_include_nonnet NUMBER) RETURN NUMBER IS
2335
2336 l_staged_qty NUMBER := 0;
2337
2338 BEGIN
2339
2340 BEGIN
2341 --
2342 -- Bugfix 2333526: Need to calculate staged quantity
2343 -- for sub level planning. If passed-in (planning)
2344 -- sub is the also the staging sub, then ignore
2345 -- p_include_nonnet
2346 --
2347 SELECT NVL(SUM(primary_reservation_quantity),0)
2348 INTO l_staged_qty
2349 FROM mtl_reservations
2350 WHERE organization_id = p_org_id
2351 AND inventory_item_id = p_item_id
2352 AND demand_source_line_id = p_order_line_id
2353 AND demand_source_type_id IN (2,8,12)
2354 AND NVL(staged_flag, 'X') = 'Y'
2355 AND subinventory_code IS NOT NULL
2356 AND subinventory_code <> p_subinv; -- Bug 4313204
2357
2358 EXCEPTION
2359 WHEN OTHERS THEN
2360 l_staged_qty := 0;
2361 END;
2362
2363 RETURN l_staged_qty;
2364
2365 END get_staged_qty;
2366
2367
2368
2369 FUNCTION get_pick_released_qty( p_org_id NUMBER
2370 , p_subinv VARCHAR2
2371 , p_item_id NUMBER
2372 , p_order_line_id NUMBER) RETURN NUMBER IS
2373
2374 l_pick_released_qty NUMBER := 0;
2375
2376 BEGIN
2377
2378 BEGIN
2379 --
2380 -- Move order type 3 is pick wave, source type 2 is sales order
2381 -- Bug 3181367 added transaction_source_type_id 8 too.
2382 SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
2383 INTO l_pick_released_qty
2384 FROM mtl_txn_request_headers mtrh,
2385 mtl_txn_request_lines mtrl
2386 WHERE mtrh.move_order_type = 3
2387 AND mtrh.header_id = mtrl.header_id
2388 AND mtrl.organization_id = p_org_id
2389 AND mtrl.inventory_item_id = p_item_id
2390 AND mtrl.from_subinventory_code = p_subinv
2391 AND mtrl.txn_source_line_id = p_order_line_id
2392 AND mtrl.transaction_source_type_id in (2,8)
2393 AND mtrl.line_status NOT IN (5,6);
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 l_pick_released_qty := 0;
2398 END;
2399
2400 RETURN l_pick_released_qty;
2401
2402 END get_pick_released_qty;
2403
2404 FUNCTION get_reord_qty( p_min_qty NUMBER
2405 , p_max_qty NUMBER
2406 , p_min_ord_qty NUMBER
2407 , p_max_ord_qty NUMBER
2408 , p_tot_avail_qty NUMBER
2409 , p_fix_mult NUMBER) RETURN NUMBER IS
2410
2411 l_min_qty NUMBER;
2412 l_max_qty NUMBER;
2413 l_min_ord_qty NUMBER;
2414 l_fix_mult NUMBER;
2415
2416 l_reorder NUMBER;
2417 l_min_restock_qty NUMBER;
2418 l_qty_for_last_order NUMBER;
2419 l_round_reord_qty VARCHAR2(1);
2420
2421 BEGIN
2422 IF G_TRACE_ON = 1 THEN
2423 print_debug('p_min_qty: ' || to_char(p_min_qty) ||
2424 ', p_max_qty: ' || to_char(p_max_qty) ||
2425 ', p_min_ord_qty: ' || to_char(p_min_ord_qty) ||
2426 ', p_max_ord_qty: ' || to_char(p_max_ord_qty) ||
2427 ', p_tot_avail_qty: ' || to_char(p_tot_avail_qty) ||
2428 ', p_fix_mult: ' || to_char(p_fix_mult)
2429 , 'get_reord_qty'
2430 , 9);
2431 END IF;
2432
2433
2434 /* GENERAL ALGORITHM:
2435
2436 When to order?
2437 When total available < minimum for item
2438
2439 How much to order?
2440 reorder qty = max stockable qty - total available
2441
2442 If reorder qty < min ord qty, increase reorder qty to min ord qty
2443
2444 If a fixed lot multiple is defined
2445 Round the reorder up or down based on profile INV_ROUND_REORDER_QTY
2446
2447 If a max ord qty is not specified
2448 or if reorder qty < max ord qty, no changes required
2449
2450 If max ord qty is specified
2451 and reorder qty exceeds max ord qty:
2452
2453 We need to make sure that after creating one or more orders
2454 for max order qty, the remaining quantity exceeds min ord qty
2455
2456 For e.g.:
2457 reorder qty = 34
2458 max ord qty = 10
2459 min ord qty = 5
2460
2461 Then restocking code will create 3 orders (move orders, requisitions
2462 or work orders) for 10 each, which is 30. The left over qty is
2463 34 - 30 = 4. Since the min ord qty is 5, we should discard the
2464 remaining qty of 4. If the remaining qty was say 8, then the last
2465 move order/requisition/work order would be for qty 8, and so on.
2466
2467 If no min ord qty is specified (or if it is 0) then this downward
2468 adjustment is not required.
2469 end if;
2470 */
2471
2472
2473
2474 l_min_qty := NVL(p_min_qty,0);
2475 l_max_qty := NVL(p_max_qty,0);
2476 l_min_ord_qty := NVL(p_min_ord_qty,0);
2477 l_fix_mult := NVL(p_fix_mult,0);
2478
2479 IF p_tot_avail_qty >= l_min_qty
2480 THEN
2481 RETURN 0;
2482 END if;
2483
2484 l_reorder := l_max_qty - p_tot_avail_qty;
2485
2486 IF G_TRACE_ON = 1 THEN
2487 print_debug('Initial estimated reorder qty: ' || to_char(l_reorder)
2488 , 'get_reord_qty'
2489 , 9);
2490 END IF;
2491
2492 IF l_min_ord_qty >= l_reorder
2493 THEN
2494 RETURN l_min_ord_qty;
2495 END if;
2496
2497 IF l_fix_mult > 0
2498 THEN
2499 l_round_reord_qty := NVL(FND_PROFILE.VALUE('INV_ROUND_REORDER_QTY'), 'Y');
2500
2501 IF G_TRACE_ON = 1 THEN
2502 print_debug('l_round_reord_qty: ' || l_round_reord_qty, 'get_reord_qty', 9);
2503 END IF;
2504
2505 IF l_round_reord_qty = 'N'
2506 THEN
2507 l_reorder := floor(l_reorder/l_fix_mult) * l_fix_mult;
2508 ELSE
2509 l_reorder := ceil(l_reorder/l_fix_mult) * l_fix_mult;
2510 END if;
2511
2512 IF G_TRACE_ON = 1 THEN
2513 print_debug('Reorder qty after applying fix lot multiple: '
2514 || to_char(l_reorder)
2515 , 'get_reord_qty'
2516 , 9);
2517 END IF;
2518 END if;
2519
2520 IF p_max_ord_qty IS NULL OR l_reorder <= p_max_ord_qty
2521 THEN
2522 RETURN l_reorder;
2523 ELSIF p_max_ord_qty > 0
2524 THEN
2525 l_min_restock_qty := floor(l_reorder/p_max_ord_qty) * p_max_ord_qty;
2526 l_qty_for_last_order := l_reorder - l_min_restock_qty;
2527
2528 IF G_TRACE_ON = 1 THEN
2529 print_debug('Min reord qty that is a multiple of max ord qty: '
2530 || to_char(l_min_restock_qty)
2531 , 'get_reord_qty'
2532 , 9);
2533 END IF;
2534
2535 IF l_qty_for_last_order >= l_min_ord_qty
2536 THEN
2537 RETURN l_reorder;
2538 ELSE
2539 RETURN l_min_restock_qty;
2540 END IF;
2541 END if;
2542
2543 RETURN l_reorder;
2544
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 IF G_TRACE_ON = 1 THEN
2548 print_debug(sqlcode || ', ' || sqlerrm, 'get_reord_qty', 1);
2549 END IF;
2550 RAISE;
2551 END get_reord_qty;
2552
2553
2554 --
2555 -- Added a new parameter p_cust_site_id for Patchset I Enhancement
2556 -- Min Max Leadtime Enhancement.
2557 --
2558 FUNCTION get_reord_stat ( p_restock NUMBER
2559 , p_handle_rep_item NUMBER
2560 , p_level NUMBER
2561 , p_reord_qty NUMBER
2562 , p_wip_batch_id NUMBER
2563 , p_org_id NUMBER
2564 , p_subinv VARCHAR2
2565 , p_user_id NUMBER
2566 , p_employee_id NUMBER
2567 , p_sysdate DATE
2568 , p_approval NUMBER
2569 , p_encum_flag VARCHAR2
2570 , p_cust_id NUMBER
2571 , p_cust_site_id NUMBER
2572 , p_cal_code VARCHAR2
2573 , p_exception_set_id NUMBER
2574 , p_dd_loc_id NUMBER
2575 , p_po_org_id NUMBER
2576 , p_pur_revision NUMBER
2577 , p_item_rec minmax_items_rectype
2578 , p_osfm_batch_id NUMBER DEFAULT NULL /* Added for Bug 6807835 */
2579 ) RETURN VARCHAR2 IS
2580
2581 v_make_buy_flag NUMBER;
2582 l_error_message VARCHAR2(100);
2583 l_ret_stat VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2584 l_reorder_qty NUMBER;
2585 l_move_ord_qty NUMBER;
2586
2587 BEGIN
2588 IF G_TRACE_ON = 1 THEN
2589 print_debug('p_restock: ' || to_char(p_restock) ||
2590 ', p_handle_rep_item: ' || to_char(p_handle_rep_item) ||
2591 ', p_level; ' || to_char(p_level) ||
2592 ', p_reord_qty: ' || to_char(p_reord_qty) ||
2593 ', p_wip_batch_id: ' || to_char(p_wip_batch_id) ||
2594 ', p_org_id: ' || to_char(p_org_id) ||
2595 ', p_subinv: ' || p_subinv ||
2596 ', p_user_id: ' || to_char(p_user_id) ||
2597 ', p_employee_id: ' || to_char(p_employee_id) ||
2598 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS')
2599 , 'get_reord_stat'
2600 , 9);
2601
2602 print_debug('p_approval: ' || to_char(p_approval) ||
2603 ', p_encum_flag: ' || p_encum_flag ||
2604 ', p_cust_id: ' || to_char(p_cust_id) ||
2605 ', p_cust_site_id: ' || to_char(p_cust_site_id) ||
2606 ', p_cal_code: ' || p_cal_code ||
2607 ', p_exception_set_id: ' || to_char(p_exception_set_id) ||
2608 ', p_dd_loc_id: ' || to_char(p_dd_loc_id) ||
2609 ', p_po_org_id: ' || to_char(p_po_org_id) ||
2610 ', p_pur_revision: ' || to_char(p_pur_revision) ||
2611 ', p_item_rec: ' || to_char(p_item_rec.item_id) ||
2612 ', p_osfm_batch_id: ' || to_char(p_osfm_batch_id)
2613 , 'get_reord_stat'
2614 , 9);
2615 END IF;
2616
2617 -- kkoothan fix for Bug 2661176,3020869
2618 -- If the item is a repetitive item and the user chose not to restock
2619 -- repetitive items, or if the planning level is "Org" and source type
2620 -- is subinventory (3) do not restock - but pass some meaningful messages
2621 -- which would be printed on the report output as :
2622 -- "Cannot create move orders for organization level planning"
2623 -- or "Repetitive Planning with Do Not Restock Option Chosen" respectively.
2624 --
2625 -- Restocking with source type sub will result in a move order and this
2626 -- only makes sense for sub level planning.
2627 --
2628 -- For sub level planning, always set the make_or_buy flag to "buy",
2629 -- i.e., do not create a work order for sub level planning.
2630 --
2631 IF p_restock = 1 THEN
2632 BEGIN
2633 IF G_TRACE_ON = 1 THEN
2634 print_debug('Item Source Type and Make or Buy Flag value: '|| p_item_rec.src_type ||' and '|| p_item_rec.mbf
2635 , 'get_reord_stat'
2636 , 9);
2637 END IF;
2638 IF (p_item_rec.repetitive_planned_item = 'Y' AND p_handle_rep_item = 3) THEN
2639 IF G_TRACE_ON = 1 THEN
2640 print_debug('For a repetitive item, Handle Repetitive Item parameter in the report has been chosen as Do Not Restock(Report Only)'
2641 , 'get_reord_stat'
2642 , 9);
2643 END IF;
2644 SELECT meaning
2645 INTO l_error_message
2646 FROM mfg_lookups
2647 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2648 AND lookup_code = 7;
2649 RETURN(l_error_message);
2650 ELSIF (p_level = 1 AND p_item_rec.src_type = 3 AND p_item_rec.mbf = 2) THEN
2651 IF G_TRACE_ON = 1 THEN
2652 print_debug('In Organization level planning, Source type for this min max item has been set up as ''Subinventory'''
2653 , 'get_reord_stat'
2654 , 9);
2655 END IF;
2656 SELECT meaning
2657 INTO l_error_message
2658 FROM mfg_lookups
2659 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
2660 AND lookup_code = 6;
2661 RETURN(l_error_message);
2662 ELSE
2663 IF p_level = 2 THEN
2664 v_make_buy_flag := 2;
2665 ELSE
2666 v_make_buy_flag := p_item_rec.mbf;
2667 END IF;
2668 END IF;
2669 EXCEPTION
2670 WHEN no_data_found THEN
2671 RETURN('');
2672 END;
2673
2674 ELSE
2675 RETURN ('');
2676 END IF;
2677
2678 l_reorder_qty := NVL(p_reord_qty,0);
2679
2680 WHILE (l_reorder_qty > 0)
2681 LOOP
2682 IF NVL(p_item_rec.max_ord_qty,0) = 0
2683 THEN
2684 l_move_ord_qty := l_reorder_qty;
2685 ELSIF (l_reorder_qty > p_item_rec.max_ord_qty)
2686 THEN
2687 l_move_ord_qty := p_item_rec.max_ord_qty;
2688 ELSE
2689 l_move_ord_qty := l_reorder_qty;
2690 END IF;
2691
2692 do_restock( p_item_id => p_item_rec.item_id
2693 , p_mbf => v_make_buy_flag
2694 , p_handle_repetitive_item => p_handle_rep_item
2695 , p_repetitive_planned_item => p_item_rec.repetitive_planned_item
2696 , p_qty => l_move_ord_qty
2697 , p_fixed_lead_time => p_item_rec.fixed_lead_time
2698 , p_variable_lead_time => p_item_rec.variable_lead_time
2699 , p_buying_lead_time => p_item_rec.buying_lead_time
2700 , p_uom => p_item_rec.primary_uom
2701 , p_accru_acct => p_item_rec.accru_acct
2702 , p_ipv_acct => p_item_rec.ipv_acct
2703 , p_budget_acct => p_item_rec.budget_acct
2704 , p_charge_acct => p_item_rec.charge_acct
2705 , p_purch_flag => p_item_rec.purch_flag
2706 , p_order_flag => p_item_rec.order_flag
2707 , p_transact_flag => p_item_rec.transact_flag
2708 , p_unit_price => p_item_rec.unit_price
2709 , p_wip_id => p_wip_batch_id
2710 , p_user_id => p_user_id
2711 , p_sysd => p_sysdate
2712 , p_organization_id => p_org_id
2713 , p_approval => p_approval
2714 , p_build_in_wip => p_item_rec.build_in_wip
2715 , p_pick_components => p_item_rec.pick_components
2716 , p_src_type => p_item_rec.src_type
2717 , p_encum_flag => p_encum_flag
2718 , p_customer_id => p_cust_id
2719 , p_customer_site_id => p_cust_site_id
2720 , p_cal_code => p_cal_code
2721 , p_except_id => p_exception_set_id
2722 , p_employee_id => p_employee_id
2723 , p_description => p_item_rec.description
2724 , p_src_org => TO_NUMBER(p_item_rec.src_org)
2725 , p_src_subinv => p_item_rec.src_subinv
2726 , p_subinv => p_subinv
2727 , p_location_id => p_dd_loc_id
2728 , p_po_org_id => p_po_org_id
2729 , p_pur_revision => p_pur_revision
2730 /* nsinghi MIN-MAX INVCONV start */
2731 , p_execution_enabled => p_item_rec.execution_enabled
2732 , p_recipe_enabled => p_item_rec.recipe_enabled
2733 , p_process_enabled => p_item_rec.process_enabled
2734 /* nsinghi MIN-MAX INVCONV end */
2735 , x_ret_stat => l_ret_stat
2736 , x_ret_mesg => l_error_message
2737 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
2738 );
2739
2740 IF l_ret_stat <> FND_API.G_RET_STS_SUCCESS
2741 THEN
2742 IF G_TRACE_ON = 1 THEN
2743 print_debug('do_restock returned message: ' || l_error_message
2744 , 'get_reord_stat'
2745 , 9);
2746 END IF;
2747 RETURN(l_error_message);
2748 END IF;
2749
2750 l_reorder_qty := l_reorder_qty - l_move_ord_qty;
2751 END LOOP;
2752
2753 RETURN(''); /*bug2838809*/
2754
2755 EXCEPTION
2756 WHEN others THEN
2757 IF G_TRACE_ON = 1 THEN
2758 print_debug(sqlcode || ', ' || sqlerrm, 'get_reord_stat', 1);
2759 END IF;
2760 RAISE;
2761 end get_reord_stat;
2762
2763
2764 --
2765 -- Min Max Lead time Enhancement.
2766 --
2767
2768 PROCEDURE get_intransit_time(
2769 x_return_status OUT NOCOPY VARCHAR2
2770 , x_msg_count OUT NOCOPY NUMBER
2771 , x_msg_data OUT NOCOPY VARCHAR2
2772 , x_intransit_time OUT NOCOPY NUMBER
2773 , x_scheduled_ship_date OUT NOCOPY DATE
2774 , p_organization_id IN NUMBER
2775 , p_subinv IN VARCHAR2
2776 , p_to_customer_site_id IN NUMBER
2777 , p_src_org IN NUMBER
2778 , p_src_subinv IN VARCHAR2
2779 , p_item_id IN NUMBER
2780 , p_sourcing_date IN DATE
2781 ) IS
2782 l_proc_name CONSTANT VARCHAR2(30) := 'GET_INTRANSIT_TIME';
2783 l_return_status VARCHAR2(1);
2784 l_msg_count NUMBER;
2785 l_msg_data VARCHAR2(240);
2786 l_return BOOLEAN;
2787 l_session_id NUMBER;
2788 l_src_org NUMBER;
2789 l_src_subinv VARCHAR2(10);
2790 l_ship_method VARCHAR2(30);
2791 l_src_rule_id NUMBER;
2792 l_ven_site_id NUMBER;
2793 l_ven_id NUMBER;
2794 l_mode VARCHAR2(20) := 'INVENTORY';
2795 l_from_location_id NUMBER;
2796 l_intransit_time NUMBER;
2797 l_so_cal_code VARCHAR2(10);
2798 l_so_exception_set_id NUMBER;
2799
2800 BEGIN
2801 SAVEPOINT sp_get_intransit_time;
2802 l_return_status := FND_API.G_RET_STS_SUCCESS;
2803 IF G_TRACE_ON = 1 THEN
2804 print_debug('Executing get_intransit_time with the following parameters:' || fnd_global.local_chr(10) ||
2805 ' p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10) ||
2806 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
2807 ', p_customer_site_id: ' || to_char(p_to_customer_site_id)|| fnd_global.local_chr(10) ||
2808 ', p_src_org: ' || to_char(p_src_org) || fnd_global.local_chr(10) ||
2809 ', p_src_subinv: ' || p_src_subinv || fnd_global.local_chr(10) ||
2810 ', p_item_id: ' || to_char(p_item_id) || fnd_global.local_chr(10) ||
2811 ', p_sourcing_date : ' || to_char(p_sourcing_date) || fnd_global.local_chr(10)
2812 , l_proc_name
2813 , 9);
2814 END IF;
2815
2816 -- Include intransit time also along with pre-processing and processing lead times for Sourcing date
2817 -- while determining the need-by date for internal requisitions.
2818 -- IF (any one of (src_org,src_subinv) is null)
2819 -- 1. Call MRP API to find src org and src sub, passing in p_sourcing_date as arg_autosource_date
2820 -- END IF;
2821 -- Calculate intransit time by calling Planning API passing in ship-from location ID
2822 -- and ship-to location ID (p_location_id).
2823 --
2824 l_src_org := p_src_org;
2825 l_src_subinv := p_src_subinv;
2826
2827 IF (p_src_org IS NULL OR p_src_subinv IS NULL) THEN
2828 IF G_TRACE_ON = 1 THEN
2829 print_debug('Calling MRP_SOURCING_API_PK.mrp_sourcing'
2830 , l_proc_name
2831 , 9);
2832 END IF;
2833 l_return := MRP_SOURCING_API_PK.mrp_sourcing
2834 ( arg_mode => l_mode
2835 , arg_item_id => p_item_id
2836 , arg_commodity_id => NULL
2837 , arg_dest_organization_id => p_organization_id
2838 , arg_dest_subinventory => p_subinv
2839 , arg_autosource_date => p_sourcing_date
2840 , arg_vendor_id => l_ven_id
2841 , arg_vendor_site_id => l_ven_site_id
2842 , arg_source_organization_id=> l_src_org
2843 , arg_source_subinventory => l_src_subinv
2844 , arg_sourcing_rule_id => l_src_rule_id
2845 , arg_error_message => l_msg_data
2846 ) ;
2847 IF NOT l_return THEN
2848 IF G_TRACE_ON = 1 THEN
2849 print_debug('MRP_SOURCING_API_PK.mrp_sourcing failed with error '|| l_msg_data
2850 , l_proc_name
2851 , 9);
2852 END IF;
2853 RAISE fnd_api.g_exc_error;
2854 ELSE
2855 IF G_TRACE_ON = 1 THEN
2856 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
2857 , l_proc_name
2858 , 9);
2859 END IF;
2860 END IF;
2861 END IF; -- any one of (src_org,src_subinv) is null
2862
2863 --
2864 -- Calculate the Schedlued Ship Date based on Shipping Org's Calendar.
2865 --
2866
2867 BEGIN
2868 SELECT p.calendar_code, p.calendar_exception_set_id
2869 INTO l_so_cal_code, l_so_exception_set_id
2870 FROM mtl_parameters p
2871 WHERE p.organization_id = l_src_org;
2872
2873 SELECT c1.calendar_date
2874 INTO x_scheduled_ship_date
2875 FROM bom_calendar_dates c1,
2876 bom_calendar_dates c
2877 WHERE c1.calendar_code = c.calendar_code
2878 AND c1.exception_set_id = c.exception_set_id
2879 AND c1.seq_num = c.next_seq_num
2880 AND c.calendar_code = l_so_cal_code
2881 AND c.exception_set_id = l_so_exception_set_id
2882 AND c.calendar_date = trunc(p_sourcing_date);
2883 EXCEPTION
2884 WHEN no_data_found THEN
2885 IF G_TRACE_ON = 1 THEN
2886 print_debug('Exception: Organization '||l_src_org ||' is not defined'
2887 , l_proc_name
2888 , 9);
2889 END IF;
2890 RAISE fnd_api.g_exc_error;
2891 END;
2892
2893 --
2894 -- Get the Location associated with the Source Subinventory.
2895 --
2896 IF l_src_subinv IS NOT NULL THEN
2897 BEGIN
2898 SELECT LOCATION_ID
2899 INTO l_from_location_id
2900 FROM MTL_SECONDARY_INVENTORIES
2901 WHERE SECONDARY_INVENTORY_NAME = l_src_subinv
2902 AND ORGANIZATION_ID = l_src_org ;
2903 EXCEPTION
2904 WHEN no_data_found THEN
2905 IF G_TRACE_ON = 1 THEN
2906 print_debug('Exception: Subinventory '|| l_src_subinv || ' does not exist in the Organization '|| l_src_org
2907 , l_proc_name
2908 , 9);
2909 END IF;
2910 RAISE fnd_api.g_exc_error;
2911 END;
2912 END IF;
2913
2914 --
2915 -- Get the Default value for Delivery To Location for the Souce Org .
2916 -- If a source subinventory is specified and has a location ID associated,
2917 -- use the source subinventory's location ID instead of the source organization's location ID.
2918 --
2919 IF l_from_location_id IS NULL THEN
2920 BEGIN
2921 SELECT LOC.LOCATION_ID
2922 INTO l_from_location_id
2923 FROM HR_ORGANIZATION_UNITS ORG,HR_LOCATIONS LOC
2924 WHERE ORG.ORGANIZATION_ID = l_src_org
2925 AND ORG.LOCATION_ID = LOC.LOCATION_ID;
2926 EXCEPTION
2927 WHEN no_data_found THEN
2928 IF G_TRACE_ON = 1 THEN
2929 print_debug('Exception: No Source Location Exists for the Organization '|| l_src_org
2930 , l_proc_name
2931 , 9);
2932 END IF;
2933 RAISE fnd_api.g_exc_error;
2934 END;
2935 END IF;
2936 IF G_TRACE_ON = 1 THEN
2937 print_debug('From Location Id is: ' || l_from_location_id
2938 , l_proc_name
2939 , 9);
2940 END IF;
2941
2942
2943 BEGIN
2944 SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
2945 INTO l_session_id
2946 FROM SYS.DUAL;
2947 EXCEPTION
2948 WHEN no_data_found THEN
2949 IF G_TRACE_ON = 1 THEN
2950 print_debug('Exception: MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL is not defined'
2951 , l_proc_name
2952 , 9);
2953 END IF;
2954 RAISE fnd_api.g_exc_error;
2955 END;
2956 IF G_TRACE_ON = 1 THEN
2957 print_debug('Calling MSC_ATP_PROC.ATP_Shipping_Lead_Time with session Id:'||l_session_id
2958 , l_proc_name
2959 , 9);
2960 END IF;
2961
2962 MSC_SCH_WB.set_session_id(l_session_id);
2963 MSC_ATP_PROC.ATP_Shipping_Lead_Time (p_from_loc_id => l_from_location_id
2964 ,p_to_customer_site_id => p_to_customer_site_id
2965 ,p_session_id => l_session_id
2966 ,x_ship_method => l_ship_method
2967 ,x_intransit_time => l_intransit_time
2968 ,x_return_status => l_return_status
2969 );
2970 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2971 IF G_TRACE_ON = 1 THEN
2972 print_debug('MSC_ATP_PROC.ATP_Shipping_Lead_Time failed with unexpected error returning message: ' || l_msg_data
2973 , l_proc_name
2974 , 9);
2975 END IF;
2976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2977 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2978 IF G_TRACE_ON = 1 THEN
2979 print_debug('MSC_ATP_PROC.ATP_Shipping_Lead_Time failed with expected error returning message: ' || l_msg_data
2980 , l_proc_name
2981 , 9);
2982 END IF;
2983 RAISE FND_API.G_EXC_ERROR;
2984 ELSE
2985 x_intransit_time := NVL(l_intransit_time,0) ;
2986 IF G_TRACE_ON = 1 THEN
2987 print_debug('MSC_ATP_PROC.ATP_Shipping_Lead_Time returned success with Intransit Time '|| l_intransit_time
2988 , l_proc_name
2989 , 9);
2990 END IF;
2991 END IF;
2992 EXCEPTION
2993 WHEN fnd_api.g_exc_error THEN
2994 ROLLBACK TO sp_get_intransit_time;
2995 x_return_status := fnd_api.g_ret_sts_error;
2996 fnd_msg_pub.count_and_get
2997 ( p_count => x_msg_count,
2998 p_data => x_msg_data
2999 );
3000
3001 WHEN fnd_api.g_exc_unexpected_error THEN
3002 ROLLBACK TO sp_get_intransit_time;
3003 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3004 fnd_msg_pub.count_and_get
3005 ( p_count => x_msg_count,
3006 p_data => x_msg_data
3007 );
3008
3009 WHEN OTHERS THEN
3010 ROLLBACK TO sp_get_intransit_time;
3011 x_return_status := fnd_api.g_ret_sts_unexp_error;
3012 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)THEN
3013 fnd_msg_pub.add_exc_msg
3014 ( G_PKG_NAME
3015 ,l_proc_name
3016 );
3017 END IF;
3018 fnd_msg_pub.count_and_get
3019 ( p_count => x_msg_count,
3020 p_data => x_msg_data
3021 );
3022 END get_intransit_time;
3023
3024 /* nsinghi MIN-MAX INVCONV start */
3025 /* overloaded do_restock procedure. For process orgs , the overloaded procedure will be called
3026 directly, whereas, the exiting code can make call to original do_restock procedure. */
3027
3028 PROCEDURE do_restock( p_item_id IN NUMBER
3029 , p_mbf IN NUMBER
3030 , p_handle_repetitive_item IN NUMBER
3031 , p_repetitive_planned_item IN VARCHAR2
3032 , p_qty IN NUMBER
3033 , p_fixed_lead_time IN NUMBER
3034 , p_variable_lead_time IN NUMBER
3035 , p_buying_lead_time IN NUMBER
3036 , p_uom IN VARCHAR2
3037 , p_accru_acct IN NUMBER
3038 , p_ipv_acct IN NUMBER
3039 , p_budget_acct IN NUMBER
3040 , p_charge_acct IN NUMBER
3041 , p_purch_flag IN VARCHAR2
3042 , p_order_flag IN VARCHAR2
3043 , p_transact_flag IN VARCHAR2
3044 , p_unit_price IN NUMBER
3045 , p_wip_id IN NUMBER
3046 , p_user_id IN NUMBER
3047 , p_sysd IN DATE
3048 , p_organization_id IN NUMBER
3049 , p_approval IN NUMBER
3050 , p_build_in_wip IN VARCHAR2
3051 , p_pick_components IN VARCHAR2
3052 , p_src_type IN NUMBER
3053 , p_encum_flag IN VARCHAR2
3054 , p_customer_id IN NUMBER
3055 , p_customer_site_id IN NUMBER
3056 , p_cal_code IN VARCHAR2
3057 , p_except_id IN NUMBER
3058 , p_employee_id IN NUMBER
3059 , p_description IN VARCHAR2
3060 , p_src_org IN NUMBER
3061 , p_src_subinv IN VARCHAR2
3062 , p_subinv IN VARCHAR2
3063 , p_location_id IN NUMBER
3064 , p_po_org_id IN NUMBER
3065 , p_pur_revision IN NUMBER
3066 , x_ret_stat OUT NOCOPY VARCHAR2
3067 , x_ret_mesg OUT NOCOPY VARCHAR2
3068 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
3069 ) IS
3070
3071 l_recipe_enabled mtl_system_items.recipe_enabled_flag%TYPE;
3072 l_execution_enabled mtl_system_items.process_execution_enabled_flag%TYPE;
3073 l_process_enabled mtl_parameters.process_enabled_flag%TYPE;
3074
3075 BEGIN
3076
3077 l_recipe_enabled := 'N';
3078 l_execution_enabled := 'N';
3079 l_process_enabled := 'N';
3080
3081 SELECT NVL(process_enabled_flag,'N') INTO l_process_enabled
3082 FROM mtl_parameters
3083 WHERE organization_id = p_organization_id;
3084
3085 IF l_process_enabled = 'Y' THEN
3086 SELECT NVL(recipe_enabled_flag, 'N'), NVL(process_execution_enabled_flag, 'N')
3087 INTO l_recipe_enabled, l_execution_enabled
3088 FROM mtl_system_items
3089 WHERE organization_id = p_organization_id
3090 AND inventory_item_id = p_item_id;
3091 END IF;
3092
3093 do_restock( p_item_id => p_item_id
3094 , p_mbf => p_mbf
3095 , p_handle_repetitive_item => p_handle_repetitive_item
3096 , p_repetitive_planned_item => p_repetitive_planned_item
3097 , p_qty => p_qty
3098 , p_fixed_lead_time => p_fixed_lead_time
3099 , p_variable_lead_time => p_variable_lead_time
3100 , p_buying_lead_time => p_buying_lead_time
3101 , p_uom => p_uom
3102 , p_accru_acct => p_accru_acct
3103 , p_ipv_acct => p_ipv_acct
3104 , p_budget_acct => p_budget_acct
3105 , p_charge_acct => p_charge_acct
3106 , p_purch_flag => p_purch_flag
3107 , p_order_flag => p_order_flag
3108 , p_transact_flag => p_transact_flag
3109 , p_unit_price => p_unit_price
3110 , p_wip_id => p_wip_id
3111 , p_user_id => p_user_id
3112 , p_sysd => p_sysd
3113 , p_organization_id => p_organization_id
3114 , p_approval => p_approval
3115 , p_build_in_wip => p_build_in_wip
3116 , p_pick_components => p_pick_components
3117 , p_src_type => p_src_type
3118 , p_encum_flag => p_encum_flag
3119 , p_customer_id => p_customer_id
3120 , p_customer_site_id => p_customer_site_id
3121 , p_cal_code => p_cal_code
3122 , p_except_id => p_except_id
3123 , p_employee_id => p_employee_id
3124 , p_description => p_description
3125 , p_src_org => p_src_org
3126 , p_src_subinv => p_src_subinv
3127 , p_subinv => p_subinv
3128 , p_location_id => p_location_id
3129 , p_po_org_id => p_po_org_id
3130 , p_pur_revision => p_pur_revision
3131 /* calling the overloaded procedure call with 'No' for process parameters. */
3132 , p_execution_enabled => l_execution_enabled
3133 , p_recipe_enabled => l_recipe_enabled
3134 , p_process_enabled => l_process_enabled
3135 , x_ret_stat => x_ret_stat
3136 , x_ret_mesg => x_ret_mesg
3137 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
3138 );
3139
3140 END do_restock;
3141 /* nsinghi MIN-MAX INVCONV end */
3142
3143 PROCEDURE do_restock( p_item_id IN NUMBER
3144 , p_mbf IN NUMBER
3145 , p_handle_repetitive_item IN NUMBER
3146 , p_repetitive_planned_item IN VARCHAR2
3147 , p_qty IN NUMBER
3148 , p_fixed_lead_time IN NUMBER
3149 , p_variable_lead_time IN NUMBER
3150 , p_buying_lead_time IN NUMBER
3151 , p_uom IN VARCHAR2
3152 , p_accru_acct IN NUMBER
3153 , p_ipv_acct IN NUMBER
3154 , p_budget_acct IN NUMBER
3155 , p_charge_acct IN NUMBER
3156 , p_purch_flag IN VARCHAR2
3157 , p_order_flag IN VARCHAR2
3158 , p_transact_flag IN VARCHAR2
3159 , p_unit_price IN NUMBER
3160 , p_wip_id IN NUMBER
3161 , p_user_id IN NUMBER
3162 , p_sysd IN DATE
3163 , p_organization_id IN NUMBER
3164 , p_approval IN NUMBER
3165 , p_build_in_wip IN VARCHAR2
3166 , p_pick_components IN VARCHAR2
3167 , p_src_type IN NUMBER
3168 , p_encum_flag IN VARCHAR2
3169 , p_customer_id IN NUMBER
3170 , p_customer_site_id IN NUMBER
3171 , p_cal_code IN VARCHAR2
3172 , p_except_id IN NUMBER
3173 , p_employee_id IN NUMBER
3174 , p_description IN VARCHAR2
3175 , p_src_org IN NUMBER
3176 , p_src_subinv IN VARCHAR2
3177 , p_subinv IN VARCHAR2
3178 , p_location_id IN NUMBER
3179 , p_po_org_id IN NUMBER
3180 , p_pur_revision IN NUMBER
3181 /* nsinghi MIN-MAX INVCONV start */
3182 , p_execution_enabled IN VARCHAR2
3183 , p_recipe_enabled IN VARCHAR2
3184 , p_process_enabled IN VARCHAR2
3185 /* nsinghi MIN-MAX INVCONV end */
3186 , x_ret_stat OUT NOCOPY VARCHAR2
3187 , x_ret_mesg OUT NOCOPY VARCHAR2
3188 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
3189 ) IS
3190 l_proc_name CONSTANT VARCHAR2(30) := 'DO_RESTOCK';
3191 l_msg VARCHAR2(1000);
3192 l_need_by_date DATE;
3193 l_ret_value VARCHAR2(200);
3194 move_ord_exc EXCEPTION;
3195 requisition_exc EXCEPTION;
3196 l_ret_stat VARCHAR2(1);
3197 l_msg_count NUMBER;
3198 l_msg_data VARCHAR2(1000);
3199 l_mo_header_id NUMBER;
3200 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3201 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3202 l_trohdr_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3203 l_trohdr_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3204 l_x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3205 l_x_trohdr_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3206 l_x_trohdr_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
3207 l_x_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
3208 l_commit VARCHAR2(1) := FND_API.G_TRUE;
3209 l_mo_line_num NUMBER;
3210 l_order_count NUMBER := 1; /* total number of lines */
3211 l_intransit_time NUMBER := 0;
3212 l_approval NUMBER;
3213 l_sourcing_date DATE; -- This is the Date Required for MOs and Sourcing Date for Internal Requisitions.
3214 l_scheduled_ship_date DATE;
3215 l_sub_loc_id NUMBER;
3216 l_location_id NUMBER;
3217 l_asset_flag NUMBER :=1 ; -- Bug 4178417
3218 l_exp_acct NUMBER ; -- Bug 4178417
3219 l_charge_acct NUMBER ; -- Bug 4178417
3220 l_dual_uom_control NUMBER ;
3221 l_secondary_qty NUMBER ;
3222 l_secondary_uom VARCHAR2(3) ;
3223 BEGIN
3224 SAVEPOINT sp_do_restock;
3225 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
3226 x_ret_mesg := '';
3227
3228 --
3229 -- Query debug settings, set global variables.
3230 -- This is done since MRP will call do_restock directly
3231 -- from Reorder Point report (MRPRPROP, bug 2442596).
3232 --
3233
3234 --
3235 -- Bug 3174141, if Sub's location is defined use that instead of Org's.
3236 --
3237
3238 l_location_id := p_location_id;
3239 If p_subinv is not null then
3240 begin
3241 select nvl(location_id,0) into l_sub_loc_id from mtl_secondary_inventories
3242 where secondary_inventory_name = p_subinv and organization_id = p_organization_id;
3243 exception
3244 when others then
3245 print_debug('Error getting Subinventory location_id', 'do_restock', 9);
3246 l_sub_loc_id := 0;
3247 end;
3248
3249 If l_sub_loc_id <> 0 then
3250 l_location_id := l_sub_loc_id;
3251 print_debug('Subinventory location_id = ' ||to_char(l_location_id), 'do_restock', 9);
3252 End if;
3253 End if;
3254
3255 -- Bug 4178417 Min Max was not calculating charge account on basis of the Subinventory to be sourced
3256 -- from , i.e for expense subinventories the charge account should be the expense account of the subinventory
3257 l_charge_acct := p_charge_acct ;
3258
3259 If p_subinv is not null then
3260 begin
3261 SELECT asset_inventory,expense_account INTO l_asset_flag, l_exp_acct FROM mtl_secondary_inventories
3262 WHERE secondary_inventory_name = p_subinv and organization_id = p_organization_id;
3263 IF l_asset_flag = 2 AND l_exp_acct IS NOT NULL then
3264 l_charge_acct := l_exp_acct ;
3265 END IF;
3266 exception
3267 when others then
3268 print_debug('Error getting Subinventory Asset Information', 'do_restock', 9);
3269 end;
3270 End If ;
3271 -- Bug 4178417
3272
3273 IF G_TRACE_ON = 1 THEN
3274 print_debug('Executing Do_restock with the following parameters' || fnd_global.local_chr(10) ||
3275 ' p_item_id ' || to_char(p_item_id) || fnd_global.local_chr(10) ||
3276 ', p_mbf: ' || to_char(p_mbf) || fnd_global.local_chr(10) ||
3277 ', p_handle_repetitive_item: ' || to_char(p_handle_repetitive_item) || fnd_global.local_chr(10) ||
3278 ', p_repetitive_planned_item: ' || p_repetitive_planned_item || fnd_global.local_chr(10) ||
3279 ', p_qty: ' || to_char(p_qty) || fnd_global.local_chr(10) ||
3280 ', p_fixed_lead_time: ' || to_char(p_fixed_lead_time) || fnd_global.local_chr(10) ||
3281 ', p_variable_lead_time: ' || to_char(p_variable_lead_time) || fnd_global.local_chr(10) ||
3282 ', p_buying_lead_time: ' || to_char(p_buying_lead_time) || fnd_global.local_chr(10) ||
3283 ', p_uom: ' || p_uom || fnd_global.local_chr(10) ||
3284 ', p_accru_acct: ' || to_char(p_accru_acct) || fnd_global.local_chr(10) ||
3285 ', p_ipv_acct: ' || to_char(p_ipv_acct) || fnd_global.local_chr(10) ||
3286 ', p_budget_acct: ' || to_char(p_budget_acct) || fnd_global.local_chr(10)
3287 , l_proc_name
3288 , 9);
3289
3290 print_debug('p_charge_acct: ' || to_char(l_charge_acct) || fnd_global.local_chr(10) ||
3291 ', p_purch_flag: ' || p_purch_flag || fnd_global.local_chr(10) ||
3292 ', p_order_flag: ' || p_order_flag || fnd_global.local_chr(10) ||
3293 ', p_transact_flag: ' || p_transact_flag || fnd_global.local_chr(10) ||
3294 ', p_unit_price: ' || to_char(p_unit_price) || fnd_global.local_chr(10) ||
3295 ', p_wip_id: ' || to_char(p_wip_id) || fnd_global.local_chr(10) ||
3296 ', p_user_id: ' || to_char(p_user_id) || fnd_global.local_chr(10) ||
3297 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10) ||
3298 ', p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10) ||
3299 ', p_approval: ' || to_char(p_approval) || fnd_global.local_chr(10) ||
3300 ', p_build_in_wip: ' || p_build_in_wip || fnd_global.local_chr(10) ||
3301 ', p_pick_components: ' || p_pick_components || fnd_global.local_chr(10) ||
3302 ', p_src_type: ' || to_char(p_src_type) || fnd_global.local_chr(10)
3303 , l_proc_name
3304 , 9);
3305
3306 print_debug('p_encum_flag: ' || p_encum_flag || fnd_global.local_chr(10) ||
3307 ', p_customer_id: ' || to_char(p_customer_id) || fnd_global.local_chr(10) ||
3308 ', p_customer_site_id: ' || to_char(p_customer_site_id) || fnd_global.local_chr(10) ||
3309 ', p_cal_code: ' || p_cal_code || fnd_global.local_chr(10) ||
3310 ', p_except_id: ' || to_char(p_except_id) || fnd_global.local_chr(10) ||
3311 ', p_employee_id: ' || to_char(p_employee_id) || fnd_global.local_chr(10) ||
3312 ', p_description: ' || p_description || fnd_global.local_chr(10) ||
3313 ', p_src_org: ' || to_char(p_src_org) || fnd_global.local_chr(10) ||
3314 ', p_src_subinv: ' || p_src_subinv || fnd_global.local_chr(10) ||
3315 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
3316 ', l_location_id: ' || to_char(l_location_id) || fnd_global.local_chr(10) ||
3317 ', p_po_org_id: ' || to_char(p_po_org_id) || fnd_global.local_chr(10) ||
3318 ', p_pur_revision: ' || to_char(p_pur_revision) || fnd_global.local_chr(10) ||
3319 ', p_osfm_batch_id: ' || to_char(p_osfm_batch_id) || fnd_global.local_chr(10)
3320 , l_proc_name
3321 , 9);
3322 END IF;
3323
3324 IF p_qty <= 0 THEN
3325 RETURN;
3326 END IF;
3327
3328 IF (p_repetitive_planned_item = 'Y' AND p_handle_repetitive_item = 1) OR
3329 (p_repetitive_planned_item = 'N' AND p_mbf = 2)THEN
3330 --
3331 -- Lead time for buy items is sum of PREPROCESSING_LEAD_TIME
3332 -- AND PROCESSING_LEAD_TIME (sub level) OR PREPROCESSING_LEAD_TIME
3333 -- AND FULL_LEAD_TIME (org level)
3334 --
3335 -- Here, total lead time is the total buying Lead time
3336 --
3337 SELECT c1.calendar_date
3338 INTO l_sourcing_date
3339 FROM bom_calendar_dates c1,
3340 bom_calendar_dates c
3341 WHERE c1.calendar_code = c.calendar_code
3342 AND c1.exception_set_id = c.exception_set_id
3343 AND c1.seq_num = (c.next_seq_num + trunc(p_buying_lead_time))
3344 AND c.calendar_code = p_cal_code
3345 AND c.exception_set_id = p_except_id
3346 AND c.calendar_date = trunc(sysdate);
3347
3348 IF G_TRACE_ON = 1 THEN
3349 print_debug('Sourcing Date is:'|| l_sourcing_date
3350 , l_proc_name
3351 , 9);
3352 END IF;
3353 l_need_by_date := l_sourcing_date;
3354 --
3355 -- Min Max Lead time Enhancement.
3356 -- If source type is Inventory then
3357 -- Call the newly added private procedure to calculate the intransit time.
3358 -- Add intransit time to p_buying_lead_time to calculate l_needby_date
3359 -- End if;
3360 --
3361 IF p_src_type =1 THEN
3362 IF G_TRACE_ON = 1 THEN
3363 print_debug('Calling get_intransit_time '
3364 , l_proc_name
3365 , 9);
3366 END IF;
3367 get_intransit_time ( x_return_status => l_ret_stat
3368 , x_msg_count => l_msg_count
3369 , x_msg_data => l_msg_data
3370 , x_intransit_time => l_intransit_time
3371 , x_scheduled_ship_date => l_scheduled_ship_date
3372 , p_organization_id => p_organization_id
3373 , p_subinv => p_subinv
3374 , p_to_customer_site_id => p_customer_site_id
3375 , p_src_org => p_src_org
3376 , p_src_subinv => p_src_subinv
3377 , p_item_id => p_item_id
3378 , p_sourcing_date => l_sourcing_date);
3379
3380
3381 IF l_ret_stat = FND_API.G_RET_STS_ERROR THEN
3382 IF G_TRACE_ON = 1 THEN
3383 print_debug('INV_Minmax_PVT.get_lead_time failed with unexpected error returning message: ' || l_msg_data
3384 , l_proc_name
3385 , 9);
3386 END IF;
3387 RAISE requisition_exc;
3388 ELSIF l_ret_stat = FND_API.G_RET_STS_UNEXP_ERROR THEN
3389 IF G_TRACE_ON = 1 THEN
3390 print_debug('INV_Minmax_PVT.get_lead_time failed with expected error returning message: ' || l_msg_data
3391 , l_proc_name
3392 , 9);
3393 END IF;
3394 RAISE requisition_exc;
3395 ELSE
3396 IF G_TRACE_ON = 1 THEN
3397 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
3398 , l_proc_name
3399 , 9);
3400 END IF;
3401 --
3402 -- Add the intransit Time to Scheduled Ship Date toarrive at Need By Date.
3403 -- and find the next calendar date corresponding to this Need By Date.
3404 --
3405 l_need_by_date := l_scheduled_ship_date + l_intransit_time;
3406
3407 IF G_TRACE_ON = 1 THEN
3408 print_debug('Need by date after adding intransit Time : ' || to_char(l_need_by_date,'DD-MON-YYYY HH24:MI:SS')
3409 , l_proc_name
3410 , 9);
3411 END IF;
3412
3413 --
3414 -- kkoothan Fix for Bug 2795828.
3415 -- Passed appropriate message to the Report if BOM calender returns exception.
3416 --
3417 BEGIN
3418 SELECT c1.calendar_date
3419 INTO l_need_by_date
3420 FROM bom_calendar_dates c1,
3421 bom_calendar_dates c
3422 WHERE c1.calendar_code = c.calendar_code
3423 AND c1.exception_set_id = c.exception_set_id
3424 AND c1.seq_num = (c.next_seq_num)
3425 AND c.calendar_code = p_cal_code
3426 AND c.exception_set_id = p_except_id
3427 AND c.calendar_date = trunc(l_need_by_date);
3428 EXCEPTION
3429 WHEN others THEN
3430 IF G_TRACE_ON = 1 THEN
3431 print_debug('Exception occured in BOM Calendar'
3432 , l_proc_name
3433 , 9);
3434 END IF;
3435 x_ret_mesg := 'Exception occured in BOM Calendar';
3436 x_ret_stat := fnd_api.g_ret_sts_error;
3437 RETURN;
3438 END;
3439
3440 IF G_TRACE_ON = 1 THEN
3441 print_debug('Final Need by date: ' || to_char(l_need_by_date,'DD-MON-YYYY HH24:MI:SS')
3442 , l_proc_name
3443 , 9);
3444 END IF;
3445 END IF;
3446 END IF; -- Source Type is 'Inventory'
3447
3448
3449 IF p_src_type = 3 THEN
3450 IF p_transact_flag = 'Y' THEN
3451 BEGIN
3452 --
3453 -- Replenishment Move Order Consolidation
3454 -- Replace the call to INV_Create_Move_Order_PVT.Create_Move_Orders with
3455 -- a call to INV_MMX_WRAPPER_PVT.get_move_order_info to get the correct header ID
3456 -- and Line Number.
3457 -- Then call INV_Move_Order_PUB.Create_Move_Order_Lines to create a single move order line
3458 -- for the current item. For the input record p_trolin_tbl, use the header ID returned by
3459 -- get_move_order_header_id.
3460 --
3461 -- the profile value set at the profile "INV: Minmax Reorder Approval"
3462 -- This profile can have 3 values:
3463 -- (Lookup Type 'MTL_REQUISITION_APPROVAL' defined in MFG_LOOKUPS)
3464 -- 1- Pre-approve d
3465 -- 2- Pre-approve move orders only
3466 -- 3- Approval Required
3467 -- Converting these codes to the ones defined in MFG_LOOKUPS under the
3468 -- lookup type'MTL_TXN_REQUEST_STATUS'.
3469 -- IF l_approval = 3 THEN
3470 -- l_approval := 1; -- Incomplete
3471 -- ELSE
3472 -- l_approval := 7; -- Pre-approved
3473 -- END IF;
3474 --
3475
3476 IF p_approval = 3 THEN
3477 l_approval := 1; -- Incomplete
3478 ELSE
3479 l_approval := 7; -- Pre Approved
3480 END IF;
3481
3482
3483 IF G_TRACE_ON = 1 THEN
3484 print_debug('Approval Status is: '||l_approval
3485 , l_proc_name
3486 , 9);
3487 print_debug('Calling INV_MMX_WRAPPER_PVT.get_move_order_info'
3488 , l_proc_name
3489 , 9);
3490 END IF;
3491
3492 INV_MMX_WRAPPER_PVT.get_move_order_info(x_return_status => l_ret_stat
3493 , x_msg_count => l_msg_count
3494 , x_msg_data => l_msg_data
3495 , x_move_order_header_ID => l_mo_header_id
3496 , x_move_order_line_num => l_mo_line_num
3497 , p_user_id => p_user_id
3498 , p_organization_id => p_organization_id
3499 , p_subinv => p_subinv
3500 , p_src_subinv => p_src_subinv
3501 , p_approval => l_approval
3502 , p_need_by_date => l_need_by_date
3503 );
3504 IF l_ret_stat = FND_API.G_RET_STS_ERROR THEN
3505 IF G_TRACE_ON = 1 THEN
3506 print_debug('INV_MMX_WRAPPER_PVT.get_move_order_info failed with unexpected error returning message: ' || l_msg_data
3507 , l_proc_name
3508 , 9);
3509 END IF;
3510 RAISE fnd_api.g_exc_unexpected_error;
3511 ELSIF l_ret_stat = FND_API.G_RET_STS_UNEXP_ERROR THEN
3512 IF G_TRACE_ON = 1 THEN
3513 print_debug('INV_MMX_WRAPPER_PVT.get_move_order_info failed with expected error returning message: ' || l_msg_data
3514 , l_proc_name
3515 , 9);
3516 END IF;
3517 RAISE fnd_api.g_exc_error;
3518 ELSE
3519 IF G_TRACE_ON = 1 THEN
3520 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
3521 , l_proc_name
3522 , 9);
3523 END IF;
3524 END IF;
3525 /* --------INVCONV changes------------------------------ */
3526
3527 IF p_process_enabled = 'Y' THEN
3528 SELECT dual_uom_control,
3529 secondary_uom_code
3530 INTO l_dual_uom_control,
3531 l_secondary_uom
3532 FROM mtl_system_items
3533 WHERE organization_id = p_organization_id
3534 AND inventory_item_id = p_item_id;
3535
3536 IF l_dual_uom_control > 1 THEN
3537 l_secondary_qty := inv_convert.inv_um_convert
3538 (item_id => p_item_id
3539 ,precision => 5
3540 ,from_quantity => p_qty
3541 ,from_unit => p_uom
3542 ,to_unit => l_secondary_uom
3543 ,from_name => NULL
3544 ,to_name => NULL);
3545 /* UOM conversion failure check */
3546 IF l_secondary_qty < 0 THEN
3547
3548 IF G_TRACE_ON = 1 THEN
3549 print_debug('UOM Conversion failed in creating move order: ' || p_item_id || ', ' || p_organization_id , l_proc_name , 9);
3550 END IF;
3551
3552 RAISE move_ord_exc;
3553 END IF ; /* if l_secondary_qty < 0 */
3554 END IF ;
3555
3556 ELSE
3557 l_secondary_uom := NULL ;
3558 l_secondary_qty := NULL ;
3559 END IF ; /* process enabled */
3560
3561 /* ---------INVCONV Changes end-------------------------- */
3562
3563 l_trolin_tbl(l_order_count).header_id := l_mo_header_id;
3564 l_trolin_tbl(l_order_count).created_by := p_user_id;
3565 l_trolin_tbl(l_order_count).creation_date := sysdate;
3566 /* Bug# 3437350 */
3567 -- l_trolin_tbl(l_order_count).date_required := TRUNC(sysdate);
3568 l_trolin_tbl(l_order_count).date_required := l_need_by_date;
3569 /* End of Bug# 3437350 */
3570 l_trolin_tbl(l_order_count).from_subinventory_code := p_src_subinv;
3571 l_trolin_tbl(l_order_count).inventory_item_id := p_item_id;
3572 l_trolin_tbl(l_order_count).last_updated_by := p_user_id;
3573 l_trolin_tbl(l_order_count).last_update_date := sysdate;
3574 l_trolin_tbl(l_order_count).last_update_login := p_user_id;
3575 l_trolin_tbl(l_order_count).line_number := l_mo_line_num;
3576 l_trolin_tbl(l_order_count).line_status := l_approval;
3577 l_trolin_tbl(l_order_count).organization_id := p_organization_id;
3578 l_trolin_tbl(l_order_count).quantity := p_qty;
3579 l_trolin_tbl(l_order_count).status_date := sysdate;
3580 l_trolin_tbl(l_order_count).to_subinventory_code := p_subinv;
3581 l_trolin_tbl(l_order_count).uom_code := p_uom;
3582 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
3583 l_trolin_tbl(l_order_count).reference_type_code := INV_Transfer_Order_PVT.G_REF_TYPE_MINMAX;-- kkoothan For Bug Fix:2756930
3584 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
3585 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
3586 /* ------INVCONV Change Added secondary qty and uom------ */
3587 l_trolin_tbl(l_order_count).secondary_quantity := l_secondary_qty;
3588 l_trolin_tbl(l_order_count).secondary_uom := l_secondary_uom;
3589 /* ------INVCONV Change Added secondary qty and uom------ */
3590
3591 IF G_TRACE_ON = 1 THEN
3592 print_debug('Calling INV_Move_Order_PUB.Create_Move_Order_Lines'
3593 , l_proc_name
3594 , 9);
3595 END IF;
3596 INV_Move_Order_PUB.Create_Move_Order_Lines
3597 ( p_api_version_number => 1.0 ,
3598 p_init_msg_list => FND_API.G_TRUE,
3599 p_commit => l_commit,
3600 x_return_status => l_ret_stat,
3601 x_msg_count => l_msg_count,
3602 x_msg_data => l_msg_data,
3603 p_trolin_tbl => l_trolin_tbl,
3604 p_trolin_val_tbl => l_trolin_val_tbl,
3605 x_trolin_tbl => l_x_trolin_tbl,
3606 x_trolin_val_tbl => l_x_trolin_val_tbl
3607 );
3608
3609 IF l_ret_stat = FND_API.G_RET_STS_ERROR THEN
3610 IF G_TRACE_ON = 1 THEN
3611 print_debug('INV_Move_Order_PUB.Create_Move_Order_Lines failed with expected error returning message: ' || l_msg_data|| l_msg_count
3612 , l_proc_name
3613 , 9);
3614 END IF;
3615 IF l_msg_count > 0 THEN
3616 FOR i in 1..l_msg_count
3617 LOOP
3618 l_msg := fnd_msg_pub.get(i,'F');
3619 print_debug(l_msg
3620 , l_proc_name
3621 , 9);
3622 fnd_msg_pub.delete_msg(i);
3623 END LOOP;
3624 END IF;
3625 RAISE fnd_api.g_exc_unexpected_error;
3626 ELSIF l_ret_stat = FND_API.G_RET_STS_UNEXP_ERROR THEN
3627 IF G_TRACE_ON = 1 THEN
3628 print_debug('INV_Move_Order_PUB.Create_Move_Order_Lines failed with unexpected error returning message: ' || l_msg_data
3629 , l_proc_name
3630 , 9);
3631 END IF;
3632 RAISE fnd_api.g_exc_error;
3633 ELSE
3634 IF G_TRACE_ON = 1 THEN
3635 print_debug('INV_Move_Order_PUB.Create_Move_Order_Lines returned success'
3636 , l_proc_name
3637 , 9);
3638 END IF;
3639 END IF;
3640
3641 EXCEPTION
3642 WHEN OTHERS THEN
3643 IF G_TRACE_ON = 1 THEN
3644 print_debug('Error creating move order: ' || sqlcode || ', ' || sqlerrm
3645 , l_proc_name
3646 , 1);
3647 END IF;
3648 RAISE move_ord_exc;
3649 END;
3650 ELSE
3651 IF G_TRACE_ON = 1 THEN
3652 print_debug('Src type is sub, item not transactable.', l_proc_name, 9);
3653 END IF;
3654 RAISE move_ord_exc;
3655 END IF; -- Transact Flag is 'N'
3656
3657 ELSE
3658
3659 re_po( p_item_id => p_item_id
3660 , p_qty => p_qty
3661 , p_nb_time => l_need_by_date
3662 , p_uom => p_uom
3663 , p_accru_acct => p_accru_acct
3664 , p_ipv_acct => p_ipv_acct
3665 , p_budget_acct => p_budget_acct
3666 , p_charge_acct => l_charge_acct -- Bug 4178417
3667 , p_purch_flag => p_purch_flag
3668 , p_order_flag => p_order_flag
3669 , p_transact_flag => p_transact_flag
3670 , p_unit_price => p_unit_price
3671 , p_user_id => p_user_id
3672 , p_sysd => p_sysd
3673 , p_organization_id => p_organization_id
3674 , p_approval => p_approval
3675 , p_src_type => p_src_type
3676 , p_encum_flag => p_encum_flag
3677 , p_customer_id => p_customer_id
3678 , p_employee_id => p_employee_id
3679 , p_description => p_description
3680 , p_src_org => p_src_org
3681 , p_src_subinv => p_src_subinv
3682 , p_subinv => p_subinv
3683 , p_location_id => l_location_id -- 3174141
3684 , p_po_org_id => p_po_org_id
3685 , p_pur_revision => p_pur_revision
3686 , x_ret_stat => l_ret_stat
3687 , x_ret_mesg => x_ret_mesg);
3688
3689 x_ret_stat := l_ret_stat;
3690
3691 END IF;
3692
3693 ELSE
3694
3695 --
3696 -- Either a make item, or repetitive item and the user chose
3697 -- "Create Discrete Job"
3698 --
3699 IF G_TRACE_ON = 1 THEN
3700 print_debug('Calling wip_calendar.estimate_leadtime to calculate need_by_date'
3701 , l_proc_name
3702 , 9);
3703 END IF;
3704
3705 wip_calendar.estimate_leadtime(x_org_id => p_organization_id,
3706 x_fixed_lead => p_fixed_lead_time,
3707 x_var_lead => p_variable_lead_time,
3708 x_quantity => p_qty,
3709 x_proc_days => 0,
3710 x_entity_type => 1,
3711 x_fusd => p_sysd,
3712 x_fucd => NULL,
3713 x_lusd => NULL,
3714 x_lucd => NULL,
3715 x_sched_dir => 1,
3716 x_est_date => l_need_by_date);
3717 /* nsinghi MIN-MAX INVCONV start */
3718 /* For process org, call needs to be made against to the GME batch API.
3719 We need to retain the above estimate_leadtime call for both Process
3720 and discrete as the leadtime would require to be calculated for the mtl_system_items in
3721 the converged model. The leadtime calculated by wip_calendar.estimate_leadtime is
3722 fixed_lead_time + (variable_lead_time * qty) which is common to process too. */
3723
3724
3725 IF p_process_enabled = 'Y' THEN
3726
3727 re_batch( p_item_id => p_item_id
3728 , p_qty => p_qty
3729 , p_nb_time => l_need_by_date
3730 , p_uom => p_uom
3731 , p_organization_id => p_organization_id
3732 , p_execution_enabled => p_execution_enabled
3733 , p_recipe_enabled => p_recipe_enabled
3734 , p_user_id => p_user_id
3735 , x_ret_stat => l_ret_stat
3736 , x_ret_mesg => x_ret_mesg);
3737
3738 ELSE
3739 /* nsinghi MIN-MAX INVCONV end */
3740
3741 re_wip( p_item_id => p_item_id
3742 , p_qty => p_qty
3743 , p_nb_time => l_need_by_date
3744 , p_uom => p_uom
3745 , p_wip_id => p_wip_id
3746 , p_user_id => p_user_id
3747 , p_sysd => p_sysd
3748 , p_organization_id => p_organization_id
3749 , p_approval => p_approval
3750 , p_build_in_wip => p_build_in_wip
3751 , p_pick_components => p_pick_components
3752 , x_ret_stat => l_ret_stat
3753 , x_ret_mesg => x_ret_mesg
3754 , p_osfm_batch_id => p_osfm_batch_id /* Added for Bug 6807835 */
3755 );
3756
3757 END IF;
3758
3759 x_ret_stat := l_ret_stat;
3760 END IF;
3761
3762 EXCEPTION
3763 WHEN fnd_api.g_exc_error THEN
3764 ROLLBACK TO sp_do_restock;
3765 x_ret_stat := fnd_api.g_ret_sts_error;
3766 fnd_msg_pub.count_and_get
3767 ( p_count =>l_msg_count ,
3768 p_data => x_ret_mesg
3769 );
3770 WHEN fnd_api.g_exc_unexpected_error THEN
3771 ROLLBACK TO sp_do_restock;
3772 x_ret_stat := fnd_api.g_ret_sts_unexp_error;
3773 fnd_msg_pub.count_and_get
3774 ( p_count =>l_msg_count ,
3775 p_data => x_ret_mesg
3776 );
3777
3778 WHEN requisition_exc THEN
3779 ROLLBACK TO sp_do_restock;
3780 SELECT meaning
3781 INTO x_ret_mesg
3782 FROM mfg_lookups
3783 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
3784 AND lookup_code = 1;
3785
3786 x_ret_stat := FND_API.G_RET_STS_ERROR;
3787
3788 WHEN move_ord_exc THEN
3789 ROLLBACK TO sp_do_restock;
3790 SELECT meaning
3791 INTO x_ret_mesg
3792 FROM mfg_lookups
3793 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
3794 AND lookup_code = 5;
3795
3796 x_ret_stat := FND_API.G_RET_STS_ERROR;
3797
3798 WHEN others THEN
3799 IF G_TRACE_ON = 1 THEN
3800 print_debug(sqlcode || ', ' || sqlerrm, l_proc_name, 1);
3801 END IF;
3802 RAISE;
3803
3804 END do_restock;
3805
3806
3807
3808 PROCEDURE re_po( p_item_id IN NUMBER
3809 , p_qty IN NUMBER
3810 , p_nb_time IN DATE
3811 , p_uom IN VARCHAR2
3812 , p_accru_acct IN NUMBER
3813 , p_ipv_acct IN NUMBER
3814 , p_budget_acct IN NUMBER
3815 , p_charge_acct IN NUMBER
3816 , p_purch_flag IN VARCHAR2
3817 , p_order_flag IN VARCHAR2
3818 , p_transact_flag IN VARCHAR2
3819 , p_unit_price IN NUMBER
3820 , p_user_id IN NUMBER
3821 , p_sysd IN DATE
3822 , p_organization_id IN NUMBER
3823 , p_approval IN NUMBER
3824 , p_src_type IN NUMBER
3825 , p_encum_flag IN VARCHAR2
3826 , p_customer_id IN NUMBER
3827 , p_employee_id IN NUMBER
3828 , p_description IN VARCHAR2
3829 , p_src_org IN NUMBER
3830 , p_src_subinv IN VARCHAR2
3831 , p_subinv IN VARCHAR2
3832 , p_location_id IN NUMBER
3833 , p_po_org_id IN NUMBER
3834 , p_pur_revision IN NUMBER
3835 , x_ret_stat OUT NOCOPY VARCHAR2
3836 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
3837
3838 l_item_rev_ctl NUMBER := 0;
3839 l_item_revision VARCHAR2(4) := '@@@';
3840 l_orgn_id NUMBER := p_organization_id;
3841
3842 l_unit_of_issue VARCHAR2(3); -- For Bug 3894347
3843 l_check_uom NUMBER; -- For Bug 3894347
3844 l_qty_conv NUMBER; -- For Bug 3894347
3845
3846 po_exc EXCEPTION;
3847
3848 BEGIN
3849 IF G_TRACE_ON = 1 THEN
3850 print_debug('p_item_id: ' || to_char(p_item_id) ||
3851 ', p_qty: ' || to_char(p_qty) ||
3852 ', p_nb_time:' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
3853 ', p_uom: ' || p_uom ||
3854 ', p_accru_acct: ' || to_char(p_accru_acct) ||
3855 ', p_ipv_acct: ' || to_char(p_ipv_acct) ||
3856 ', p_budget_acct: ' || to_char(p_budget_acct) ||
3857 ', p_charge_acct: ' || to_char(p_charge_acct) ||
3858 ', p_purch_flag: ' || p_purch_flag ||
3859 ', p_order_flag: ' || p_order_flag ||
3860 ', p_transact_flag: ' || p_transact_flag ||
3861 ', p_unit_price: ' || to_char(p_unit_price) ||
3862 ', p_user_id: ' || to_char(p_user_id) ||
3863 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS')
3864 , 're_po'
3865 , 9);
3866
3867 print_debug('p_organization_id: ' || to_char(p_organization_id) ||
3868 ', p_approval: ' || to_char(p_approval) ||
3869 ', p_src_type: ' || to_char(p_src_type) ||
3870 ', p_encum_flag: ' || p_encum_flag ||
3871 ', p_customer_id: ' || to_char(p_customer_id) ||
3872 ', p_employee_id: ' || to_char(p_employee_id) ||
3873 ', p_description: ' || p_description ||
3874 ', p_src_org: ' || to_char(p_src_org) ||
3875 ', p_src_subinv: ' || p_src_subinv ||
3876 ', p_subinv: ' || p_subinv ||
3877 ', p_location_id: ' || to_char(p_location_id) ||
3878 ', p_po_org_id: ' || to_char(p_po_org_id) ||
3879 ', p_pur_revision: ' || to_char(p_pur_revision)
3880 , 're_po'
3881 , 9);
3882 END IF;
3883
3884 --
3885 -- Do not create a requisition if any of the following apply:
3886 -- 1. Source type (Inventory/Supplier/Subinventory) is not specified
3887 -- 2. Item is not transactable
3888 -- 3. Source type is Inventory (1) but "Internal Orders Enabled"
3889 -- is not checked
3890 -- 4. Source type is Supplier (2) but "Purchasable" flag unchecked
3891 --
3892 IF (p_src_type IS NULL)
3893 OR
3894 (p_transact_flag <> 'Y')
3895 OR
3896 (p_src_type = 1 AND p_order_flag <> 'Y')
3897 OR
3898 (p_src_type = 2 AND p_purch_flag <> 'Y')
3899 THEN
3900 IF G_TRACE_ON = 1 THEN
3901 print_debug('Null src type or invalid transact_flag, order_flag or purch_flag'
3902 , 're_po', 9);
3903 END IF;
3904 RAISE po_exc;
3905 END IF;
3906
3907 IF (p_charge_acct IS NULL)
3908 OR (p_accru_acct IS NULL)
3909 OR (p_ipv_acct IS NULL)
3910 OR ((p_encum_flag <> 'N') AND (p_budget_acct is NULL))
3911 THEN
3912 IF G_TRACE_ON = 1 THEN
3913 print_debug('Charge/accrual/IPV/budget accts not setup correctly.', 're_po', 9);
3914 END IF;
3915 RAISE po_exc;
3916 END IF;
3917
3918 IF NVL(p_customer_id,0) < 0
3919 THEN
3920 IF G_TRACE_ON = 1 THEN
3921 print_debug('Invalid customer ID: ' || to_char(p_customer_id), 're_po', 9);
3922 END IF;
3923 RAISE po_exc;
3924 END IF;
3925
3926
3927 /* Fix for bug 774532. To get the item revisions, IF profile is Yes
3928 OR IF profile is NULL AND item is revision controlled */
3929
3930 --
3931 -- Bug 2323099:
3932 -- We should only specify a revision if the item is revision-controlled
3933 -- and the profile "INV:Purchasing By Revision" is set to yes
3934 --
3935 -- p_pur_revision will never be NULL - this is handled in the
3936 -- BEFORE-REPORT trigger of INVISMMX.
3937 --
3938
3939 IF p_pur_revision = 1
3940 THEN
3941 SELECT revision_qty_control_code
3942 INTO l_item_rev_ctl
3943 FROM mtl_system_items msi
3944 WHERE msi.organization_id = l_orgn_id
3945 AND msi.inventory_item_id = p_item_id;
3946
3947 IF G_TRACE_ON = 1 THEN
3948 print_debug('Rev ctl: ' || to_char(l_item_rev_ctl), 're_po', 9);
3949 END IF;
3950
3951 /* Commented for ER 6698138
3952 IF l_item_rev_ctl = 2 THEN
3953 */
3954 SELECT MAX(revision)
3955 INTO l_item_revision
3956 FROM mtl_item_revisions mir
3957 WHERE inventory_item_id = p_item_id
3958 AND organization_id = l_orgn_id
3959 AND effectivity_date < SYSDATE
3960 AND implementation_date is not null /* Added for Bug 7110794 */
3961 AND effectivity_date =
3962 (
3963 SELECT MAX(effectivity_date)
3964 FROM mtl_item_revisions mir1
3965 WHERE mir1.inventory_item_id = mir.inventory_item_id
3966 AND mir1.organization_id = mir.organization_id
3967 AND implementation_date is not null /* Added for Bug 7110794 */
3968 AND effectivity_date < SYSDATE
3969 );
3970 /* Commented for ER 6698138
3971 END IF;
3972 */
3973
3974 IF G_TRACE_ON = 1 THEN
3975 print_debug('Item rev: ' || l_item_revision, 're_po', 9);
3976 END IF;
3977 END IF ;
3978
3979 /* Changes for Bug 3894347 */
3980 l_check_uom := 0;
3981
3982 select uom_code
3983 into l_unit_of_issue
3984 from mtl_system_items_vl msiv , mtl_units_of_measure_vl muom
3985 where msiv.inventory_item_id = p_item_id
3986 and msiv.organization_id = p_organization_id
3987 and muom.unit_of_measure = NVL(msiv.unit_of_issue,msiv.primary_unit_of_measure);
3988
3989 IF G_TRACE_ON = 1 THEN
3990 print_debug('l_unit_of_issue: '||l_unit_of_issue, 're_po', 9);
3991 END IF;
3992
3993 IF ( l_unit_of_issue <> p_uom) THEN
3994
3995 IF G_TRACE_ON = 1 THEN
3996 print_debug('p_item_id: ' || to_char(p_item_id) ||
3997 ', p_qty: ' || to_char(p_qty) ||
3998 ', p_organization_id: ' || to_char(p_organization_id) ||
3999 ', p_uom: ' || p_uom ||
4000 ', l_unit_of_issue: ' || l_unit_of_issue
4001 , 're_po'
4002 , 9);
4003 END IF;
4004
4005 l_qty_conv := INV_CONVERT.INV_UM_CONVERT(
4006 item_id => p_item_id,
4007 precision => null,
4008 from_quantity => p_qty,
4009 from_unit => p_uom,
4010 to_unit => l_unit_of_issue,
4011 from_name => null,
4012 to_name => null);
4013
4014 IF G_TRACE_ON = 1 THEN
4015 print_debug('l_qty_conv = ' || to_char(l_qty_conv), 're_po', 9);
4016 END IF;
4017
4018 l_check_uom := 1;
4019
4020 END IF;
4021 /* End of Changes for Bug 3894347 */
4022
4023 IF G_TRACE_ON = 1 THEN
4024 print_debug('Inserting into PO_REQUISITIONS_INTERFACE_ALL', 're_po', 9);
4025 END IF;
4026
4027 INSERT INTO po_requisitions_interface_all(
4028 LAST_UPDATE_DATE,
4029 LAST_UPDATED_BY,
4030 ITEM_DESCRIPTION,
4031 CREATION_DATE,
4032 CREATED_BY,
4033 PREPARER_ID,
4034 INTERFACE_SOURCE_CODE,
4035 REQUISITION_TYPE,
4036 AUTHORIZATION_STATUS,
4037 SOURCE_TYPE_CODE,
4038 SOURCE_ORGANIZATION_ID,
4039 SOURCE_SUBINVENTORY,
4040 DESTINATION_ORGANIZATION_ID,
4041 DESTINATION_SUBINVENTORY,
4042 DELIVER_TO_REQUESTOR_ID,
4043 DESTINATION_TYPE_CODE,
4044 UOM_CODE,
4045 DELIVER_TO_LOCATION_ID,
4046 ITEM_ID,
4047 ITEM_REVISION,
4048 QUANTITY,
4049 NEED_BY_DATE,
4050 GL_DATE,
4051 CHARGE_ACCOUNT_ID,
4052 ACCRUAL_ACCOUNT_ID,
4053 VARIANCE_ACCOUNT_ID,
4054 BUDGET_ACCOUNT_ID,
4055 AUTOSOURCE_FLAG,
4056 ORG_ID)
4057 VALUES (
4058 sysdate,
4059 p_user_id,
4060 p_description,
4061 sysdate,
4062 p_user_id,
4063 p_employee_id,
4064 'INV',
4065 DECODE(p_src_type, 1, 'INTERNAL', 'PURCHASE'),
4066 DECODE(p_approval, 1, 'APPROVED','INCOMPLETE'),
4067 DECODE(p_src_type, 1, 'INVENTORY', 'VENDOR'),
4068 p_src_org,
4069 p_src_subinv,
4070 p_organization_id,
4071 p_subinv,
4072 p_employee_id,
4073 'INVENTORY',
4074 DECODE(l_check_uom,1,l_unit_of_issue,p_uom), -- Bug 3894347
4075 p_location_id,
4076 p_item_id,
4077 DECODE(l_item_revision,'@@@',NULL,l_item_revision),
4078 DECODE(l_check_uom,1,l_qty_conv,p_qty), -- Bug 3894347
4079 (trunc(p_nb_time) + 1 - (1/(24*60*60))),
4080 SYSDATE,
4081 p_charge_acct,
4082 p_accru_acct,
4083 p_ipv_acct,
4084 p_budget_acct,
4085 'P',
4086 p_po_org_id);
4087
4088 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
4089 x_ret_mesg := '';
4090
4091 EXCEPTION
4092 WHEN OTHERS THEN
4093 IF G_TRACE_ON = 1 THEN
4094 print_debug(sqlcode || ', ' || sqlerrm, 're_po', 1);
4095 END IF;
4096
4097 SELECT meaning
4098 INTO x_ret_mesg
4099 FROM mfg_lookups
4100 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
4101 AND lookup_code = 1;
4102
4103 x_ret_stat := FND_API.G_RET_STS_ERROR;
4104 END re_po;
4105
4106
4107
4108 PROCEDURE re_wip( p_item_id IN NUMBER
4109 , p_qty IN NUMBER
4110 , p_nb_time IN DATE
4111 , p_uom IN VARCHAR2
4112 , p_wip_id IN NUMBER
4113 , p_user_id IN NUMBER
4114 , p_sysd IN DATE
4115 , p_organization_id IN NUMBER
4116 , p_approval IN NUMBER
4117 , p_build_in_wip IN VARCHAR2
4118 , p_pick_components IN VARCHAR2
4119 , x_ret_stat OUT NOCOPY VARCHAR2
4120 , x_ret_mesg OUT NOCOPY VARCHAR2
4121 , p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
4122 ) IS
4123
4124 wip_exc EXCEPTION;
4125 /* Added for Bug 6807835 */
4126
4127 l_header_id NUMBER := NULL;
4128 l_mode_flag NUMBER := NULL;
4129 l_job_name VARCHAR2(255);
4130 l_first_unit_start_date DATE;
4131 l_last_unit_completion_date DATE;
4132 l_scheduling_method NUMBER := 2;
4133 l_cfm_flag NUMBER;
4134 l_osfm_batch_id NUMBER;
4135 l_is_lot_control VARCHAR2(1) := NULL;
4136
4137 /* End of changes for Bug 6807835 */
4138
4139
4140 BEGIN
4141 IF G_TRACE_ON = 1 THEN
4142 print_debug('p_item_id: ' || to_char(p_item_id) ||
4143 ', p_qty: ' || to_char(p_qty) ||
4144 ', p_nb_time: ' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
4145 ', p_uom: ' || p_uom ||
4146 ', p_wip_id: ' || to_char(p_wip_id) ||
4147 ', p_user_id: ' || to_char(p_user_id) ||
4148 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') ||
4149 ', p_organization_id: ' || to_char(p_organization_id) ||
4150 ', p_approval: ' || to_char(p_approval) ||
4151 ', p_build_in_wip: ' || p_build_in_wip ||
4152 ', p_pick_components: ' || p_pick_components ||
4153 ', p_osfm_batch_id: ' || p_osfm_batch_id
4154 , 're_wip'
4155 , 9);
4156 END IF;
4157
4158 /* Added for Bug 6807835 */
4159
4160 IF(to_number(NVL(FND_PROFILE.VALUE('WSM_CREATE_LBJ_COPY_ROUTING'),0)) = 1 ) THEN
4161 l_scheduling_method := 1;
4162 ELSE
4163 l_scheduling_method := 2;
4164 END IF;
4165 select wsm_lot_sm_ifc_header_s.nextval
4166 into l_header_id
4167 from dual;
4168
4169 l_mode_flag := 1;
4170
4171 select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
4172 INTO l_job_name
4173 from dual;
4174
4175 print_debug('OSFM Job Name '||l_job_name
4176 , 're_wip', 9);
4177
4178 IF p_nb_time IS NOT NULL THEN
4179 l_first_unit_start_date := NULL;
4180 l_last_unit_completion_date := p_nb_time;
4181
4182 ELSE
4183 l_first_unit_start_date := SYSDATE;
4184 l_last_unit_completion_date := NULL;
4185 END IF;
4186
4187 BEGIN
4188 select nvl(cfm_routing_flag,0) into l_cfm_flag
4189 from BOM_OPERATIONAL_ROUTINGS
4190 where assembly_item_id = p_item_id
4191 AND organization_id = p_organization_id
4192 AND alternate_routing_designator is NULL;
4193 EXCEPTION
4194 when NO_DATA_FOUND then
4195 l_cfm_flag := 2;
4196 when others then
4197 RAISE wip_exc;
4198 END;
4199
4200 /* End of changes for Bug 6807835 */
4201
4202 IF p_build_in_wip <> 'Y' OR p_pick_components <> 'N'
4203 THEN
4204 IF G_TRACE_ON = 1 THEN
4205 print_debug('Item either not build_in_wip or has pick components flag checked'
4206 , 're_wip', 9);
4207 END IF;
4208 RAISE wip_exc;
4209 /* Added for Bug 6807835 */
4210 ELSIF (l_cfm_flag = 3) AND (wsmpvers.get_osfm_release_version > '110509')
4211 THEN
4212 BEGIN
4213 SELECT 'Y' INTO l_is_lot_control
4214 FROM dual
4215 WHERE exists
4216 (SELECT 1 FROM mtl_system_items
4217 WHERE organization_id = p_organization_id
4218 AND inventory_item_id = p_item_id
4219 AND lot_control_code = 2);
4220 EXCEPTION
4221 WHEN OTHERS THEN
4222 l_is_lot_control := 'N';
4223 END;
4224
4225 IF (p_osfm_batch_id is null) THEN
4226
4227 --
4228 -- Set L_OSFM_BATCH_ID to the next Sequence of WSM_LOT_JOB_INTERFACE_S.
4229 --
4230
4231 BEGIN
4232 SELECT WSM_LOT_JOB_INTERFACE_S.NEXTVAL
4233 INTO l_osfm_batch_id
4234 FROM SYS.DUAL;
4235 EXCEPTION
4236 WHEN no_data_found THEN
4237 IF G_TRACE_ON = 1 THEN
4238 print_debug('Exception: WSM_LOT_JOB_INTERFACE_S.NEXTVAL is not defined'
4239 , 're_wip'
4240 , 9);
4241 END IF;
4242 RAISE wip_exc;
4243 END;
4244 ELSE
4245 l_osfm_batch_id := p_osfm_batch_id;
4246 END IF;
4247
4248 IF G_TRACE_ON = 1 THEN
4249 print_debug('OSFM Batch Id is: ' || l_osfm_batch_id
4250 , 're_wip'
4251 , 9);
4252 END IF;
4253
4254
4255 IF l_is_lot_control = 'Y' THEN
4256 INSERT INTO WSM_LOT_JOB_INTERFACE (
4257 mode_flag,
4258 last_update_date,
4259 last_updated_by,
4260 creation_date,
4261 created_by,
4262 last_update_login,
4263 group_id,
4264 source_line_id,
4265 organization_id,
4266 load_type,
4267 status_type,
4268 primary_item_id,
4269 job_name,
4270 start_Quantity,
4271 process_Status,
4272 first_unit_start_date,
4273 last_unit_completion_date,
4274 scheduling_method,
4275 completion_subinventory,
4276 completion_locator_id,
4277 class_code,
4278 description,
4279 bom_revision_date,
4280 routing_revision_date,
4281 header_id)
4282 VALUES (
4283 1,
4284 sysdate,
4285 fnd_global.user_id,
4286 sysdate,
4287 fnd_global.user_id,
4288 fnd_global.login_id,
4289 l_osfm_batch_id,
4290 Decode(l_mode_flag, 1,null,l_header_id),
4291 p_organization_id,
4292 5, --job creation
4293 3, --1:unreleased, 3: released
4294 p_item_id,
4295 l_job_name,
4296 p_qty,
4297 1,
4298 l_first_unit_start_date,
4299 l_last_unit_completion_date,
4300 l_scheduling_method,
4301 null,
4302 null,
4303 '',
4304 null,
4305 '',
4306 '',
4307 l_header_id);
4308
4309 ELSE -- l_is_lot_control = 'Y'
4310 IF G_TRACE_ON = 1 THEN
4311 print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
4312 END IF;
4313 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
4314 LAST_UPDATE_DATE,
4315 LAST_UPDATED_BY,
4316 CREATION_DATE,
4317 CREATED_BY,
4318 GROUP_ID,
4319 PROCESS_PHASE,
4320 PROCESS_STATUS,
4321 ORGANIZATION_ID,
4322 LOAD_TYPE,
4323 LAST_UNIT_COMPLETION_DATE,
4324 PRIMARY_ITEM_ID,
4325 START_QUANTITY,STATUS_TYPE)
4326 VALUES(
4327 p_sysd,
4328 p_user_id,
4329 p_sysd,
4330 p_user_id,
4331 p_wip_id,
4332 2,
4333 1,
4334 p_organization_id,
4335 1,
4336 p_nb_time,
4337 p_item_id,
4338 p_qty,
4339 DECODE(p_approval,1,3,1));
4340
4341 END IF; -- end of l_is_lot_control = 'Y'
4342
4343 /* End of changes for 6807835 */
4344
4345 ELSE
4346 IF G_TRACE_ON = 1 THEN
4347 print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
4348 END IF;
4349 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
4350 LAST_UPDATE_DATE,
4351 LAST_UPDATED_BY,
4352 CREATION_DATE,
4353 CREATED_BY,
4354 GROUP_ID,
4355 PROCESS_PHASE,
4356 PROCESS_STATUS,
4357 ORGANIZATION_ID,
4358 LOAD_TYPE,
4359 LAST_UNIT_COMPLETION_DATE,
4360 PRIMARY_ITEM_ID,
4361 START_QUANTITY,STATUS_TYPE)
4362 VALUES(
4363 p_sysd,
4364 p_user_id,
4365 p_sysd,
4366 p_user_id,
4367 p_wip_id,
4368 2,
4369 1,
4370 p_organization_id,
4371 1,
4372 p_nb_time,
4373 p_item_id,
4374 p_qty,
4375 DECODE(p_approval,1,3,1));
4376 END IF;
4377
4378 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
4379 x_ret_mesg := '';
4380
4381 EXCEPTION
4382 WHEN OTHERS THEN
4383 IF G_TRACE_ON = 1 THEN
4384 print_debug(sqlcode || ', ' || sqlerrm, 're_wip', 1);
4385 END IF;
4386
4387 SELECT meaning
4388 INTO x_ret_mesg
4389 FROM mfg_lookups
4390 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
4391 AND lookup_code = 2;
4392
4393 x_ret_stat := FND_API.G_RET_STS_ERROR;
4394 END re_wip;
4395 --
4396 /* nsinghi MIN-MAX INVCONV start */
4397
4398 PROCEDURE re_batch( p_item_id IN NUMBER
4399 , p_qty IN NUMBER
4400 , p_nb_time IN DATE
4401 , p_uom IN VARCHAR2
4402 , p_organization_id IN NUMBER
4403 , p_execution_enabled IN VARCHAR2
4404 , p_recipe_enabled IN VARCHAR2
4405 , p_user_id IN NUMBER
4406 , x_ret_stat OUT NOCOPY VARCHAR2
4407 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
4408
4409
4410 l_gme_batch_header GME_BATCH_HEADER%ROWTYPE;
4411 l_eff_id NUMBER(15);
4412 batch_exc EXCEPTION;
4413 x_message_count NUMBER;
4414 x_message_list VARCHAR2(1000);
4415 return_status VARCHAR2(1000);
4416 x_gme_batch_header gme_batch_header%ROWTYPE;
4417 x_exception_material_tbl gmp_batch_wrapper_pkg.exceptions_tab;
4418
4419 BEGIN
4420
4421 IF G_TRACE_ON = 1 THEN
4422 print_debug('p_item_id: ' || to_char(p_item_id) ||
4423 ', p_qty: ' || to_char(p_qty) ||
4424 ', p_nb_time: ' || to_char(p_nb_time, 'DD-MON-YYYY HH24:MI:SS') ||
4425 ', p_uom: ' || p_uom ||
4426 ', p_organization_id: ' || to_char(p_organization_id)
4427 , 're_wip'
4428 , 9);
4429 END IF;
4430
4431 IF p_execution_enabled <> 'Y' OR p_recipe_enabled <> 'Y'
4432 THEN
4433 IF G_TRACE_ON = 1 THEN
4434 print_debug('Item either not Execution Enabled or not Recipe Enabled'
4435 , 're_batch', 9);
4436 END IF;
4437 RAISE batch_exc;
4438 ELSE
4439 fnd_profile.initialize(p_user_id);
4440
4441 l_gme_batch_header.organization_id := p_organization_id;
4442 l_gme_batch_header.plan_cmplt_date := p_nb_time;
4443 l_gme_batch_header.due_date := p_nb_time;
4444 l_gme_batch_header.batch_type := 0; /* 0 for batches, 10 for FPOs */
4445 l_gme_batch_header.update_inventory_ind := 'Y' ;
4446 -- l_gme_batch_header.RECIPE_VALIDITY_RULE_ID := l_eff_id ;
4447
4448 IF G_TRACE_ON = 1 THEN
4449 print_debug('Calling the GMP Create_Batch Wrapper API', 're_batch', 9);
4450 END IF;
4451
4452 gmp_batch_wrapper_pkg.create_batch(
4453 p_api_version => 2.0
4454 ,p_validation_level => 100
4455 ,p_init_msg_list => FND_API.G_TRUE
4456 ,p_commit => FND_API.G_TRUE
4457 ,x_message_count => x_message_count
4458 ,x_message_list => x_message_list
4459 ,x_return_status => return_status
4460 ,p_org_code => NULL
4461 ,p_batch_header_rec => l_gme_batch_header
4462 ,x_batch_header_rec => x_gme_batch_header
4463 ,p_batch_size => p_qty
4464 ,p_batch_size_uom => p_uom
4465 ,p_creation_mode => 'PRODUCT'
4466 ,p_recipe_id => NULL
4467 ,p_recipe_no => NULL
4468 ,p_recipe_version => NULL
4469 ,p_product_no => NULL
4470 ,p_item_revision => NULL
4471 ,p_product_id => p_item_id
4472 ,p_ignore_qty_below_cap => FND_API.G_TRUE
4473 ,p_use_workday_cal => NULL
4474 ,p_contiguity_override => NULL
4475 ,p_use_least_cost_validity_rule => FND_API.G_FALSE
4476 ,x_exception_material_tbl => x_exception_material_tbl
4477 );
4478
4479 -- IF (return_status <> 'S') THEN -- nsinghi bug 5931402
4480 IF (return_status NOT IN ('S', 'V')) THEN
4481 IF G_TRACE_ON = 1 THEN
4482 print_debug('Could not create batch. gmp_batch_wrapper_pkg.create_batch returned with status '||return_status
4483 , 're_batch', 9);
4484 print_debug('x_message_count '||to_char(x_message_count)||', x_message_list '||x_message_list
4485 , 're_batch', 9);
4486 END IF;
4487 RAISE batch_exc;
4488 ELSE
4489 IF G_TRACE_ON = 1 THEN
4490 print_debug('Created batch with batch_id '||to_char(x_gme_batch_header.batch_id)
4491 , 're_batch', 9);
4492 END IF;
4493 END IF;
4494
4495 -- END IF; /* For l_eff_id <> NULL */
4496
4497 END IF; /* For p_execution_enabled <> 'Y' AND p_recipe_enabled <> 'Y' */
4498
4499 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
4500 x_ret_mesg := '';
4501
4502 EXCEPTION
4503 WHEN OTHERS THEN
4504 IF G_TRACE_ON = 1 THEN
4505 print_debug(sqlcode || ', ' || sqlerrm, 're_batch', 1);
4506 END IF;
4507
4508 SELECT meaning
4509 INTO x_ret_mesg
4510 FROM mfg_lookups
4511 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
4512 AND lookup_code = 2;
4513
4514 x_ret_stat := FND_API.G_RET_STS_ERROR;
4515 END re_batch;
4516
4517
4518 --This procedure is to copmpute the loaded quantities for a WMS enabled org.
4519 --This is the qty loaded using Pick load and not yet Pick dropped.
4520 FUNCTION get_loaded_qty( p_org_id NUMBER
4521 , p_subinv VARCHAR2
4522 , p_level NUMBER
4523 , p_item_id NUMBER
4524 , p_net_rsv NUMBER
4525 , p_net_unrsv NUMBER ) RETURN NUMBER IS
4526
4527 CURSOR c_loaded_quantities_v IS
4528 SELECT SUM(quantity) FROM wms_loaded_quantities_v
4529 WHERE inventory_item_id = p_item_id
4530 AND subinventory_code = nvl(p_subinv , subinventory_code )
4531 AND organization_id = p_org_id;
4532
4533 l_loaded_qty NUMBER := 0 ;
4534
4535 BEGIN
4536 --The loaded quantity will be calculated only if the report is ran with
4537 --parameters "reserved demand=>No , unreserved demand=>No".
4538 --If the parameters are "yes", the MTL_RESERVATIONS or MMTT will be accounted for this qty.
4539
4540 IF ( p_net_rsv = 2 and p_net_unrsv = 2 )THEN
4541
4542 OPEN c_loaded_quantities_v ;
4543 FETCH c_loaded_quantities_v INTO l_loaded_qty;
4544 CLOSE c_loaded_quantities_v;
4545 END IF;
4546
4547 IF g_trace_on = 1 THEN
4548 print_debug('(WMS only) Total quantity loaded : ' ||
4549 to_char(l_loaded_qty), 'get_loaded_qty', 9);
4550 END IF;
4551
4552 return ( l_loaded_qty ) ;
4553 EXCEPTION
4554 WHEN OTHERS THEN
4555 IF G_TRACE_ON = 1 THEN
4556 print_debug(sqlcode || ', ' || sqlerrm, 'get_loaded_qty', 1);
4557 END IF;
4558 RAISE;
4559 END get_loaded_qty;
4560
4561 /* nsinghi MIN-MAX INVCONV end */
4562
4563 END INV_Minmax_PVT;