[Home] [Help]
PACKAGE BODY: APPS.INV_MMX_WRAPPER_PVT
Source
1 PACKAGE BODY INV_MMX_WRAPPER_PVT AS
2 /* $Header: INVMMXWB.pls 120.8 2011/03/01 10:08:34 gjyoti ship $ */
3
4 -- Package variable to store move order line grouping.
5 G_MO_LINE_GROUPING NUMBER := 1; -- one move order header per execution.
6
7 -- Package variable to store current subinventory being planned.
8 G_CURRENT_SUBINV VARCHAR2(10) := NULL;
9
10 -- Package variables to store current move order header ID and Line Number.
11 G_CURRENT_MO_HDR_ID NUMBER := NULL;
12 G_CURRENT_MO_LINE_NUM NUMBER := NULL;
13
14 --
15 -- Find the user name, INV debug profile setting
16 --
17
18 G_USER_NAME fnd_user.user_name%TYPE := FND_GLOBAL.USER_NAME;
19 G_TRACE_ON NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),2);
20
21
22 PROCEDURE print_debug
23 ( p_message IN VARCHAR2
24 , p_module IN VARCHAR2
25 , p_level IN NUMBER
26 ) IS
27 BEGIN
28 inv_log_util.trace( G_USER_NAME||': '||p_message,G_PKG_NAME||'.'||p_module||'($Revision: 120.8 $)',p_level);
29
30 EXCEPTION
31 WHEN OTHERS THEN
32 NULL;
33 END print_debug;
34
35
36 /*
37 ** ---------------------------------------------------------------------------
38 ** Procedure : exec_min_max
39 **
40 ** Description : This procedure is called from both the min-max planning report
41 ** as well as the Oracle Spares Management Applications.
42 **
43 ** 1) It performs most of the validations that were previously done
44 ** in the BEFORE-REPORT trigger of the min-max report report.
45 ** 2) Initializes the package variables for move order line consolidation,
46 ** current subinventory being planned and current move order header ID
47 ** and line Number.
48 ** 3) Loops through the passed in array of subinventories and calls
49 ** the existing INV_Minmax_PVT.run_min_max_plan API when
50 ** doing sub-level planning for more than one subinventory.
51 ** 4) After report is complete,submit FND request for WIP Mass Load
52 ** which was previously done in AFTER-REPORT trigger of the min-max report.
53 **
54 ** Input Parameters:
55 **
56 ** p_organization_id
57 ** Identifier of organization for which Min Max planning is to be done.
58 ** p_user_id
59 ** Identifier of the User performinng the Min Max planning.
60 ** p_subinv_tbl
61 ** Set of Subinventories for which Min Max planning is to be done.
62 ** Min-Max report will pass in a table with only one record since
63 ** planning for a set of subinventories from the INV UI is not supported .
64 ** Oracle Spares Management Applications will pass in a table with all the
65 ** subinventories that need to be planned.
66 ** If no table is passed in and sub level planning is being done,
67 ** then plan for all valid subinventories (that have at least one item set up
68 ** for min-max planning on the item-subinventory form in the org).
69 ** p_employee_id
70 ** Identifier of the Employee associated with the User.
71 ** Deafult value is NULL.
72 ** p_gen_report
73 ** Parameter to turn off report output generation (for Spares).
74 ** Default value is 'N'.
75 ** p_mo_line_grouping
76 ** Parameter to Control the number of move order headers created.
77 ** A value of 1 (one) denotes "one move order header per execution"
78 ** whereas a value of 2 stands for
79 ** "one move order header for each planned subinventory".
80 ** Defualt Value is 1.
81 ** p_item_select
82 ** Item Number.
83 ** Default Value is NULL.
84 ** p_handle_rep_item
85 ** Parameter for Repetitive item handling.
86 ** 1- Create Requistion
87 ** 2- Create Discrete Job
88 ** 3- Do not Restock ,ie Report Only.
89 ** Default Value is 3.
90 ** p_pur_revision
91 ** Parameter for Purchasing by Revision.
92 ** Used for Revision controlled items.
93 ** It can be 'Yes' or 'No' or NULL.
94 ** Default value is NULL.
95 ** p_cat_select
96 ** Item Category.
97 ** Defualt value is 'NULL'
98 ** p_cat_set_id
99 ** Category Set Id.
100 ** Default value is NULL.
101 ** p_mcat_struct
102 ** Category Structure Number.
103 ** Default value is NULL.
104 ** p_level
105 ** Min Max Planning Level.
106 ** 1-Organization
107 ** 2-Subinventory
108 ** Default value is 2.
109 ** p_restock
110 ** Restocking is required or not.
111 ** If Restock is No, only the report will be generated and
112 ** no replenishment will happen.
113 ** Default value is 1.
114 ** p_include_nonnet
115 ** Include Non-netable Subinventories or not.
116 ** Default value is 1.
117 ** p_include_po
118 ** Include PO as Supply or not.
119 ** Default value is 1.
120 ** p_include_mo -- Added for Bug 3057273
121 ** Include Move Orders as Supply or not.
122 ** Default value is 1.
123 ** p_include_wip
124 ** Include WIP as Supply or not.
125 ** Default value is 2.
126 ** p_include_if
127 ** Include Interface as Supply or not.
128 ** Default value is 1.
129 ** p_net_rsv
130 ** Inlclude Reserved Orders as Demands or not.
131 ** Default value is 1.
132 ** p_net_unrsv
133 ** Inlclude Unreserved Orders as Demands or not.
134 ** Default value is 1.
135 ** p_net_wip
136 ** Inlclude WIP Jobs as Demands or not.
137 ** Default value is 2.
138 ** p_dd_loc_id
139 ** Default Delivery To Location Id of the Planning Org.
140 ** Default value is NULL.
141 ** p_buyer_hi
142 ** Buyer Name From.
143 ** Default value is NULL.
144 ** p_buyer_lo
145 ** Buyer Name To.
146 ** Default value is NULL.
147 ** p_range_buyer
148 ** Where clause for Range of Buyers.
149 ** Default Value is '1 = 1'.
150 ** p_range_sql
151 ** Where clause for Range of Items,Categories and Planners.
152 ** Default Value is '1 = 1'.
153 ** p_sort
154 ** Min Max Report Sort By Criteria.
155 ** 1-Inventory Item
156 ** 2-Category
157 ** 3-Planner
158 ** 4-Buyer
159 ** Default Value is 1.
160 ** p_selection
161 ** Parameter for Min Max planned Item selection criteria.
162 ** 1- Min Max planned Items under minimum Qty.
163 ** 2- Min Max planned Items over minimum Qty.
164 ** 3- All Min Max planned Items.
165 ** Deafualt value is 3.
166 ** p_sysdate
167 ** Current System Date.
168 ** Default Value is sysdate.
169 ** p_s_cutoff
170 ** Supply Cut Off Date.
171 ** Default Value is NULL.
172 ** p_d_cutoff
173 ** Demand Cut Off Date.
174 ** Default Value is NULL.
175 **
176 ** Output Parameters:
177 **
178 ** x_return_status
179 ** Return status indicating success, error or unexpected error.
180 ** x_msg_count
181 ** Number of messages in the message list.
182 ** x_msg_data
183 ** If the number of messages in message list is 1, contains
184 ** message text.
185 **
186 ** ---------------------------------------------------------------------------
187 */
188
189 PROCEDURE exec_min_max
190 ( x_return_status OUT NOCOPY VARCHAR2
191 , x_msg_count OUT NOCOPY NUMBER
192 , x_msg_data OUT NOCOPY VARCHAR2
193 , p_organization_id IN NUMBER
194 , p_user_id IN NUMBER
195 , p_subinv_tbl IN SubInvTableType
196 , p_employee_id IN NUMBER
197 , p_gen_report IN VARCHAR2
198 , p_mo_line_grouping IN NUMBER
199 , p_item_select IN VARCHAR2
200 , p_handle_rep_item IN NUMBER
201 , p_pur_revision IN NUMBER
202 , p_cat_select IN VARCHAR2
203 , p_cat_set_id IN NUMBER
204 , p_mcat_struct IN NUMBER
205 , p_level IN NUMBER
206 , p_restock IN NUMBER
207 , p_include_nonnet IN NUMBER
208 , p_include_po IN NUMBER
209 , p_include_mo IN NUMBER
210 , p_include_wip IN NUMBER
211 , p_include_if IN NUMBER
212 , p_net_rsv IN NUMBER
213 , p_net_unrsv IN NUMBER
214 , p_net_wip IN NUMBER
215 , p_dd_loc_id IN NUMBER
216 , p_buyer_hi IN VARCHAR2
217 , p_buyer_lo IN VARCHAR2
218 , p_range_buyer IN VARCHAR2
219 , p_range_sql IN VARCHAR2
220 , p_sort IN VARCHAR2
221 , p_selection IN NUMBER
222 , p_sysdate IN DATE
223 , p_s_cutoff IN DATE
224 , p_d_cutoff IN DATE
225 ) IS
226
227 l_proc CONSTANT VARCHAR2(30) := 'EXEC_MIN_MAX';
228 l_pur_revision NUMBER;
229 l_cat_set_id NUMBER;
230 l_mcat_struct NUMBER;
231 l_employee_id NUMBER;
232 l_include_no_net NUMBER;
233 l_dd_loc_id NUMBER;
234 l_approval NUMBER;
235 l_range_buyer VARCHAR2(600); -- For Bug #2815313, changed the width from 100 to 600
236 l_cust_id NUMBER;
237 l_site_use_id NUMBER;
238 l_po_org_id NUMBER;
239 l_operating_unit NUMBER;
240 l_order_by VARCHAR2(20);
241 l_encum_flag VARCHAR2(1);
242 l_cal_code VARCHAR2(10);
243 l_exception_set_id NUMBER;
244 l_item_select VARCHAR2(400); -- bug# 11067996, changed length from 300 to 400
245 l_cat_select VARCHAR2(400); -- bug# 11067996, changed length from 300 to 400
246 l_gen_report VARCHAR2(1);
247 l_sysdate DATE;
248 l_s_cutoff DATE;
249 l_d_cutoff DATE;
250 l_valid NUMBER;
251 l_wip_batch_id NUMBER;
252 l_count NUMBER := 0;
253 l_bulk_fetch_limit NUMBER := 100;
254 l_subinv_tbl SubInvTableType;
255 l_return_status VARCHAR2(1);
256 l_msg_data VARCHAR2(1000);
257 l_warn Varchar2(1) := 'S'; --Bug 4681032
258 /* Added for Bug 6807835 */
259 l_reqid NUMBER := NULL;
260 l_osfm_batch_id NUMBER;
261 l_job_count NUMBER := 0;
262 /* End of Changes for Bug 6807835 */
263
264
265 --
266 -- Cursor for retrieving the list of subinventories for min-max planning,
267 -- if no subinventory is passed in the parameter p_subinv_tbl.
268 --
269 CURSOR c_subinv (cp_org_id IN NUMBER) IS
270 SELECT secondary_inventory_name
271 FROM mtl_secondary_inventories msi
272 WHERE msi.organization_id = cp_org_id
273 AND EXISTS
274 ( SELECT 1
275 FROM mtl_item_sub_inventories misi
276 WHERE misi.organization_id = msi.organization_id
277 AND misi.secondary_inventory = msi. secondary_inventory_name
278 AND misi.inventory_planning_code = 2
279 );
280
281 BEGIN
282 SAVEPOINT sp_exec_min_max;
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 IF G_TRACE_ON = 1 THEN
286 print_debug('Starting Min-max planning with the following parameters: ' || fnd_global.local_chr(10)||
287 ' p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10)||
288 ', p_user_id: ' || to_char(p_user_id) || fnd_global.local_chr(10)
289 , l_proc
290 , 9);
291
292 FOR i in 1..p_subinv_tbl.count
293 LOOP
294
295 print_debug(', p_subinv('|| i ||'): ' || p_subinv_tbl(i) || fnd_global.local_chr(10)
296 , l_proc
297 , 9);
298 END LOOP;
299
300 print_debug('Parameters contd..: ' || fnd_global.local_chr(10)||
301 ' p_employee_id: ' || to_char(p_employee_id) || fnd_global.local_chr(10)||
302 ', p_gen_report:' || p_gen_report || fnd_global.local_chr(10)||
303 ', p_mo_line_grouping:' || p_mo_line_grouping || fnd_global.local_chr(10)||
304 ', p_item_select: ' || p_item_select || fnd_global.local_chr(10)||
305 ', p_handle_rep_item: ' || to_char(p_handle_rep_item) || fnd_global.local_chr(10)||
306 ', p_pur_revision: ' || to_char(p_pur_revision) || fnd_global.local_chr(10)||
307 ', p_cat_select: ' || p_cat_select || fnd_global.local_chr(10)||
308 ', p_cat_set_id: ' || to_char(p_cat_set_id) || fnd_global.local_chr(10)||
309 ', p_mcat_struct: ' || to_char(p_mcat_struct) || fnd_global.local_chr(10)||
310 ', p_level: ' || to_char(p_level) || fnd_global.local_chr(10)||
311 ', p_restock: ' || to_char(p_restock) || fnd_global.local_chr(10)||
312 ', p_include_nonnet: ' || to_char(p_include_nonnet) || fnd_global.local_chr(10)||
313 ', p_include_po: ' || to_char(p_include_po) || fnd_global.local_chr(10)||
314 ', p_include_mo: ' || to_char(p_include_mo) || fnd_global.local_chr(10)||
315 ', p_include_wip: ' || to_char(p_include_wip) || fnd_global.local_chr(10)||
316 ', p_include_if: ' || to_char(p_include_if) || fnd_global.local_chr(10)
317 , l_proc
318 , 9);
319
320 print_debug('Parameters contd..: ' || fnd_global.local_chr(10)||
321 ' p_net_rsv: ' || to_char(p_net_rsv) || fnd_global.local_chr(10)||
322 ', p_net_unrsv: ' || to_char(p_net_unrsv) || fnd_global.local_chr(10)||
323 ', p_net_wip: ' || to_char(p_net_wip) || fnd_global.local_chr(10)||
324 ', p_dd_loc_id: ' || to_char(p_dd_loc_id) || fnd_global.local_chr(10)||
325 ', p_buyer_hi: ' || p_buyer_hi || fnd_global.local_chr(10)||
326 ', p_buyer_lo: ' || p_buyer_lo || fnd_global.local_chr(10)||
327 ', p_range_buyer: ' || p_range_buyer || fnd_global.local_chr(10)||
328 ', p_range_sql: ' || p_range_sql || fnd_global.local_chr(10)||
329 ', p_sort: ' || p_sort || fnd_global.local_chr(10)||
330 ', p_selection: ' || to_char(p_selection) || fnd_global.local_chr(10)||
331 ', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)||
332 ', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)||
333 ', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)||
334 ', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)
335 , l_proc
336 , 9);
337 END IF;
338
339 --
340 -- If the value of P_PUR_REVISION has not been initialized, set it to 'No'.
341 --
342 -- l_pur_revision := NVL(P_PUR_REVISION,NVL(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)) ;
343 /* Commented NVL for bug 11695871 */
344
345 l_pur_revision := NVL(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2) ;
346
347 IF G_TRACE_ON = 1 THEN
348 print_debug('Profile PUR_REVISION is: ' || l_pur_revision
349 ,l_proc
350 , 9);
351 END IF;
352
353 --
354 -- Validate category set and MCAT struct
355 --
356 IF P_CAT_SET_ID IS NOT NULL THEN
357 l_cat_set_id := P_CAT_SET_ID;
358 IF P_MCAT_STRUCT IS NULL THEN
359 BEGIN
360 SELECT STRUCTURE_ID
361 INTO l_mcat_struct
362 FROM MTL_CATEGORY_SETS
363 WHERE CATEGORY_SET_ID = P_CAT_SET_ID;
364 EXCEPTION
365 WHEN no_data_found THEN
366 IF G_TRACE_ON = 1 THEN
367 print_debug('Exception: No category set exists for the passed Category set ID:'|| P_CAT_SET_ID
368 ,l_proc
369 , 9);
370 END IF;
371 RAISE fnd_api.g_exc_error;
372 END;
373 ELSE
374 l_mcat_struct := P_MCAT_STRUCT;
375 END IF;
376 ELSE
377 BEGIN
378 SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
379 INTO l_cat_set_id,l_mcat_struct
380 FROM MTL_CATEGORY_SETS CSET,
381 MTL_DEFAULT_CATEGORY_SETS DEF
382 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
383 AND DEF.FUNCTIONAL_AREA_ID = 1;
384 EXCEPTION
385 WHEN no_data_found THEN
386 IF G_TRACE_ON = 1 THEN
387 print_debug('Exception: No default category set exists'
388 ,l_proc
389 , 9);
390 END IF;
391 RAISE fnd_api.g_exc_error;
392 END;
393
394 END IF;
395 IF G_TRACE_ON = 1 THEN
396 print_debug('CAT_SET_ID and MCAT_STRUCT are: ' || l_cat_set_id ||','|| l_mcat_struct
397 ,l_proc
398 , 9);
399 END IF;
400
401 --
402 -- Get the Employee Id from the passed in User ID.
403 --
404 IF P_EMPLOYEE_ID IS NULL THEN
405 BEGIN
406 SELECT EMPLOYEE_ID
407 INTO l_employee_id
408 FROM FND_USER
409 WHERE USER_ID = P_USER_ID;
410 EXCEPTION
411 WHEN no_data_found THEN
412 IF G_TRACE_ON = 1 THEN
413 print_debug('Exception: No Employee Exists for the passed in User Id: '|| P_USER_ID
414 ,l_proc
415 , 9);
416 END IF;
417 RAISE fnd_api.g_exc_error;
418 END;
419 ELSE
420 l_employee_id := P_EMPLOYEE_ID;
421 END IF;
422 IF G_TRACE_ON = 1 THEN
423 print_debug('EMPLOYEE_ID is: ' || l_employee_id
424 , l_proc
425 , 9);
426 END IF;
427
428 --
429 -- In case ,planning level is Subinventory, non-netable is always 'Yes'.
430 --
431 IF P_LEVEL = 2 THEN
432 l_include_no_net := 1;
433 ELSE
434 l_include_no_net := P_INCLUDE_NONNET;
435 END IF;
436 IF G_TRACE_ON = 1 THEN
437 print_debug('INCLUDE_NO_NET is: ' || l_include_no_net
438 ,l_proc
439 , 9);
440 END IF;
441
442 --
443 -- Set the Default value for Delivery To Location of the Planning Org,if it is null.
444 --
445 IF P_DD_LOC_ID IS NULL AND P_RESTOCK=1 THEN
446 --Bug 3942423 added p_restock=1 condition as it is not required in case of p_restock=2
447 -- and using p_organization_id parameter rather than MFG_ORGANIZATION_ID
448 BEGIN
449 SELECT LOC.LOCATION_ID
450 INTO l_dd_loc_id
451 FROM HR_ORGANIZATION_UNITS ORG,HR_LOCATIONS LOC
452 WHERE ORG.ORGANIZATION_ID = nvl(p_organization_id,-1)
453 AND ORG.LOCATION_ID = LOC.LOCATION_ID;
454 EXCEPTION
455 WHEN no_data_found THEN
456 IF G_TRACE_ON = 1 THEN
457 print_debug('Exception: No Default Delivery To Location Exists'
458 , l_proc
459 , 9);
460 END IF;
461 RAISE fnd_api.g_exc_error;
462 END;
463 ELSE
464 l_dd_loc_id := P_DD_LOC_ID;
465 END IF;
466 IF G_TRACE_ON = 1 THEN
467 print_debug('DD_LOC_ID is: ' || l_dd_loc_id
468 ,l_proc
469 , 9);
470 END IF;
471
472 --
473 -- From now onwards, Move Orders should also honor
474 -- the profile value set at the profile "INV: Minmax Reorder Approval"
475 -- This profile can have 3 values:
476 -- (Lookup Type 'MTL_REQUISITION_APPROVAL' defined in MFG_LOOKUPS)
477 -- 1- Pre-approve d
478 -- 2- Pre-approve move orders only
479 -- 3- Approval Required
480 --
481 l_approval := to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'2'));
482 IF G_TRACE_ON = 1 THEN
483 print_debug('APPROVAL STATUS is: ' || l_approval
484 ,l_proc
485 , 9);
486 END IF;
487
488 --
489 -- Construct the BUYER Range WHERE Clause.
490 -- Bug#3248005 - Buyer range where clause modified
491
492 --IF P_RANGE_BUYER IS NULL THEN
493 IF P_BUYER_LO IS NOT NULL AND P_BUYER_HI IS NOT NULL THEN
494 l_range_buyer := 'V.FULL_NAME BETWEEN ' ||''''||P_BUYER_LO||'''' || ' AND ' ||''''||P_BUYER_HI||'''';
495 ELSIF P_BUYER_LO IS NOT NULL THEN
496 l_range_buyer := 'V.FULL_NAME >= ' ||''''||P_BUYER_LO||'''';
497 ELSIF P_BUYER_HI IS NOT NULL THEN
498 l_range_buyer := 'V.FULL_NAME <= ' ||''''||P_BUYER_HI||'''';
499 ELSE
500 l_range_buyer := '1 = 1';
501 END IF;
502 /* ELSE
503 l_range_buyer := P_RANGE_BUYER;
504 END IF; */
505
506 IF G_TRACE_ON = 1 THEN
507 print_debug('RANGE_BUYER WHERE Clause is: ' || l_range_buyer
508 ,l_proc
509 , 9);
510 END IF;
511
512
513 --
514 -- Get the Operating Unit,Org Name etc.,
515 --
516 BEGIN
517 SELECT OPERATING_UNIT, OPERATING_UNIT
518 INTO l_operating_unit, l_po_org_id
519 FROM ORG_ORGANIZATION_DEFINITIONS
520 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
521 EXCEPTION
522 WHEN no_data_found THEN
523 IF G_TRACE_ON = 1 THEN
524 print_debug('Exception: Organization Id '|| P_ORGANIZATION_ID ||' Passed in is invalid'
525 , l_proc
526 , 9);
527 END IF;
528 RAISE fnd_api.g_exc_error;
529 END;
530 IF G_TRACE_ON = 1 THEN
531 print_debug('Operating Unit is: ' || l_operating_unit
532 ,l_proc
533 , 9);
534 END IF;
535
536 --
537 -- Get the customer Id.
538 --
539
540 -- MOAC : change from PO_LOCATION_ASSOCIATIONS table to PO_LOCATION_ASSOCIATIONS_ALL
541 --Bug :4968383 Added condition org_id=l_operating_unit to fetch the customer details
542 -- of the CURRENT operating unit in which the Min-Max report is requested.
543
544 BEGIN
545 SELECT CUSTOMER_ID,SITE_USE_ID
546 INTO l_cust_id,l_site_use_id
547 FROM PO_LOCATION_ASSOCIATIONS_ALL
548 WHERE LOCATION_ID = l_dd_loc_id
549 AND org_id=l_operating_unit;
550
551 EXCEPTION
552 WHEN NO_DATA_FOUND THEN
553 l_site_use_id := NULL;
554 l_cust_id := NULL;
555 END;
556 IF (P_RESTOCK = 1) AND (l_site_use_id IS NULL OR l_cust_id IS NULL) THEN
557 IF G_TRACE_ON = 1 THEN
558 print_debug('Exception: No Customer Set up has been done for the Delivery Location Id: '|| l_dd_loc_id
559 ,l_proc
560 , 9);
561 END IF;
562 END IF;
563 IF G_TRACE_ON = 1 THEN
564 print_debug('CUSTOMER ID and SITE USE ID are: ' || l_cust_id ||','|| l_site_use_id
565 ,l_proc
566 , 9);
567 END IF;
568
569
570 --
571 -- Set order by clause.
572 --
573 IF P_SORT = 1 OR P_SORT IS NULL THEN
574 l_order_by := ' ORDER BY 1';
575 ELSIF P_SORT = 2 THEN
576 l_order_by := ' ORDER BY 14,1';
577 ELSIF P_SORT = 3 THEN
578 l_order_by := ' ORDER BY 12,1';
579 ELSIF P_SORT = 4 THEN
580 l_order_by := ' ORDER BY 13,1';
581 END IF;
582 IF G_TRACE_ON = 1 THEN
583 print_debug('ORDER BY Clause is: ' || l_order_by
584 ,l_proc
585 , 9);
586 END IF;
587
588 --
589 -- Set the Encumbrance Flag.
590 --
591 BEGIN
592 SELECT NVL(REQ_ENCUMBRANCE_FLAG, 'N')
593 INTO l_encum_flag
594 FROM FINANCIALS_SYSTEM_PARAMS_ALL
595 WHERE NVL(ORG_ID,-11) = NVL(l_operating_unit,-11);
596 EXCEPTION
597 WHEN no_data_found THEN
598 IF G_TRACE_ON = 1 THEN
599 print_debug('Exception: No Encumbrance setup has been done for Organization Id '|| P_ORGANIZATION_ID ||' Passed'
600 , l_proc
601 , 9);
602 END IF;
603 RAISE fnd_api.g_exc_error;
604 END;
605 IF G_TRACE_ON = 1 THEN
606 print_debug('Encumbrance Flag is: ' || l_encum_flag
607 ,l_proc
608 , 9);
609 END IF;
610
611 --
612 -- Get calendar Code and Exception Set Id.
613 --
614 BEGIN
615 SELECT P.CALENDAR_CODE, P.CALENDAR_EXCEPTION_SET_ID
616 INTO l_cal_code, l_exception_set_id
617 FROM MTL_PARAMETERS P
618 WHERE P.ORGANIZATION_ID = P_ORGANIZATION_ID;
619 EXCEPTION
620 WHEN no_data_found THEN
621 IF G_TRACE_ON = 1 THEN
622 print_debug('Exception: Organization Id '||P_ORGANIZATION_ID||' Passed in does not exist'
623 ,l_proc
624 , 9);
625 END IF;
626 RAISE fnd_api.g_exc_error;
627 END;
628 IF G_TRACE_ON = 1 THEN
629 print_debug('Calendar Code and Exception Set Id are: ' || l_cal_code ||','|| l_exception_set_id
630 ,l_proc
631 , 9);
632 END IF;
633
634 --
635 -- Set Item and Category if they are null.
636 -- These values are used as select columns in the SQLs used in INV_MINMAX_PVT.run_min_max(),
637 -- but not used elsewhere in that procedure.
638 --
639 l_item_select := NVL(P_ITEM_SELECT,('C.SEGMENT1'));
640 l_cat_select := NVL(P_CAT_SELECT,('B.SEGMENT1||B.SEGMENT2'));
641 IF G_TRACE_ON = 1 THEN
642 print_debug('Item and Category are: ' || l_item_select ||','|| l_cat_select
643 , l_proc
644 , 9);
645 END IF;
646
647 --
648 -- Validate P_SORT.
649 --
650 BEGIN
651 SELECT 1
652 INTO l_valid
653 FROM MFG_LOOKUPS
654 WHERE LOOKUP_TYPE = 'MTL_MINMAX_RPT_SORT_BY'
655 AND LOOKUP_CODE = NVL(P_SORT,1);
656 EXCEPTION
657 WHEN no_data_found THEN
658 IF G_TRACE_ON = 1 THEN
659 print_debug('Exception: The Lookup MTL_MINMAX_RPT_SORT_BY is not defined'
660 , l_proc
661 , 9);
662 END IF;
663 RAISE fnd_api.g_exc_error;
664 END;
665
666 --
667 -- Validate P_SELECTION.
668 --
669 BEGIN
670 SELECT 1
671 INTO l_valid
672 FROM MFG_LOOKUPS
673 WHERE LOOKUP_TYPE = 'MTL_MINMAX_RPT_SEL'
674 AND LOOKUP_CODE = NVL(P_SELECTION,3);
675 EXCEPTION
676 WHEN no_data_found THEN
677 IF G_TRACE_ON = 1 THEN
678 print_debug('Exception: The Lookup MTL_MINMAX_RPT_SEL is not defined'
679 , l_proc
680 , 9);
681 END IF;
682 RAISE fnd_api.g_exc_error;
683 END;
684
685 --
686 -- Set P_S_CUTOFF and P_D_CUTOFF to sysdate if they are null.
687 --
688 l_sysdate := NVL(P_SYSDATE,SYSDATE);
689 l_s_cutoff := NVL(P_S_CUTOFF,trunc(l_sysdate));
690 l_d_cutoff := NVL(P_D_CUTOFF,trunc(l_sysdate));
691 IF G_TRACE_ON = 1 THEN
692 print_debug('Supply Cut-Off and Demand Cut-off Dates are: ' || l_s_cutoff ||','|| l_d_cutoff
693 , l_proc
694 , 9);
695 END IF;
696
697 --
698 -- Set L_WIP_BATCH_ID to the next Sequence of WIP_JOB_SCHEDULE_INTERFACE_S.
699 --
700 BEGIN
701 SELECT WIP_JOB_SCHEDULE_INTERFACE_S.NEXTVAL
702 INTO l_wip_batch_id
703 FROM SYS.DUAL;
704 EXCEPTION
705 WHEN no_data_found THEN
706 IF G_TRACE_ON = 1 THEN
707 print_debug('Exception: WIP_JOB_SCHEDULE_INTERFACE_S.NEXTVAL is not defined'
708 , l_proc
709 , 9);
710 END IF;
711 RAISE fnd_api.g_exc_error;
712 END;
713 IF G_TRACE_ON = 1 THEN
714 print_debug('WIP Batch Id is: ' || l_wip_batch_id
715 , l_proc
716 , 9);
717 END IF;
718
719
720 /* Added for Bug 6807835 */
721
722 --
723 -- Set L_OSFM_BATCH_ID to the next Sequence of WSM_LOT_JOB_INTERFACE_S.
724 --
725
726 BEGIN
727 SELECT WSM_LOT_JOB_INTERFACE_S.NEXTVAL
728 INTO l_osfm_batch_id
729 FROM SYS.DUAL;
730 EXCEPTION
731 WHEN no_data_found THEN
732 IF G_TRACE_ON = 1 THEN
733 print_debug('Exception: WSM_LOT_JOB_INTERFACE_S.NEXTVAL is not defined'
734 , l_proc
735 , 9);
736 END IF;
737 RAISE fnd_api.g_exc_error;
738 END;
739 IF G_TRACE_ON = 1 THEN
740 print_debug('OSFM Batch Id is: ' || l_osfm_batch_id
741 , l_proc
742 , 9);
743 END IF;
744 /* End of Changes for Bug 6807835 */
745 --
746 -- Set P_GEN_REPORT to 'Y', if it is not 'N'.
747 --
748 IF P_GEN_REPORT <> 'N' THEN
749 l_gen_report := 'Y';
750 ELSE
751 l_gen_report := 'N';
752 END IF;
753 IF G_TRACE_ON = 1 THEN
754 print_debug('Generate Report is: ' || l_gen_report
755 , l_proc
756 , 9);
757 END IF;
758
759 --
760 -- Initialize the Package variables.
761 --
762 G_MO_LINE_GROUPING := NVL(P_MO_LINE_GROUPING,1) ;
763 G_CURRENT_SUBINV := NULL;
764 G_CURRENT_MO_HDR_ID := NULL;
765 G_CURRENT_MO_LINE_NUM := NULL;
766
767 --
768 -- If planning level is sub level (2)then
769 -- If P_SUBINV_TBL count is Zero then
770 -- Open c_subinv, bulk fetch list of subs
771 -- If no subs found, return an error: "No items have been set up for
772 -- subinventory level min-max planning in this organization."
773 -- End if;
774 -- Loop through list of subs
775 -- Call inv_minmax_pvt.run_min_max_plan for each sub.
776 -- End loop;
777 -- Else
778 -- Call inv_minmax_pvt.run_min_max_plan for org level planning.
779 -- End if;
780 --
781 IF P_LEVEL = 2 THEN
782 IF P_SUBINV_TBL.COUNT = 0 THEN
783 OPEN c_subinv(P_ORGANIZATION_ID);
784 FETCH c_subinv BULK COLLECT INTO l_subinv_tbl;
785 CLOSE c_subinv;
786 IF l_subinv_tbl.COUNT = 0 THEN
787 IF G_TRACE_ON = 1 THEN
788 print_debug('No items have been set up for subinventory level min-max planning in this organization.'
789 , l_proc
790 , 9);
791 END IF;
792 fnd_message.set_name('INV','INV_MINMAX_NO_ITEM_SETUP');
793 fnd_msg_pub.add;
794 RAISE fnd_api.g_exc_error;
795 END IF;
796 ELSE
797 l_subinv_tbl := p_subinv_tbl;
798
799 END IF;
800
801 FOR l_subinv_count IN 1..l_subinv_tbl.COUNT
802 LOOP
803 IF G_TRACE_ON = 1 THEN
804 print_debug('Calling INV_Minmax_PVT.run_min_max_plan for subinventory level ' ||
805 'Min Max Planning with sub '||l_subinv_tbl(l_subinv_count)
806 ,l_proc
807 , 9);
808 END IF;
809
810 INV_Minmax_PVT.run_min_max_plan
811 ( p_item_select => l_item_select
812 , p_handle_rep_item => NVL(p_handle_rep_item,3)
813 , p_pur_revision => l_pur_revision
814 , p_cat_select => l_cat_select
815 , p_cat_set_id => l_cat_set_id
816 , p_mcat_struct => l_mcat_struct
817 , p_level => NVL(p_level,2)
818 , p_restock => NVL(p_restock,1)
819 , p_include_nonnet => l_include_no_net
820 , p_include_po => NVL(p_include_po,1)
821 , p_include_mo => NVL(p_include_mo,1)
822 , p_include_wip => NVL(p_include_wip,2)
823 , p_include_if => NVL(P_include_if,1)
824 , p_net_rsv => NVL(p_net_rsv,1)
825 , p_net_unrsv => NVL(p_net_unrsv,1)
826 , p_net_wip => NVL(p_net_wip,2)
827 , p_org_id => p_organization_id
828 , p_user_id => p_user_id
829 , p_employee_id => l_employee_id
830 , p_subinv => l_subinv_tbl(l_subinv_count)
831 , p_dd_loc_id => l_dd_loc_id
832 , p_wip_batch_id => l_wip_batch_id
833 , p_approval => l_approval
834 , p_buyer_hi => p_buyer_hi
835 , p_buyer_lo => p_buyer_lo
836 , p_range_buyer => l_range_buyer
837 , p_cust_id => l_cust_id
838 , p_cust_site_id => l_site_use_id
839 , p_po_org_id => l_po_org_id
840 , p_range_sql => NVL(p_range_sql,'1 = 1')
841 , p_sort => NVL(p_sort,1)
842 , p_selection => NVL(p_selection,3)
843 , p_sysdate => l_sysdate
844 , p_s_cutoff => l_s_cutoff
845 , p_d_cutoff => l_d_cutoff
846 , p_order_by => l_order_by
847 , p_encum_flag => l_encum_flag
848 , p_cal_code => l_cal_code
849 , p_exception_set_id => l_exception_set_id
850 , p_gen_report => l_gen_report
851 , x_return_status => l_return_status
852 , x_msg_data => l_msg_data
853 , p_osfm_batch_id => l_osfm_batch_id /* Added for Bug 6807835 */
854 );
855
856 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
857 l_warn := 'W'; --Bug 4681032
858 IF G_TRACE_ON = 1 THEN
859 print_debug('INV_Minmax_PVT.run_min_max_plan failed with unexpected error '||
860 'for subinventory '|| l_subinv_tbl(l_subinv_count) ||
861 'returning message: ' || l_msg_data
862 ,l_proc
863 , 9);
864 END IF;
865 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
866 l_warn := 'W'; --Bug 4681032
867 IF G_TRACE_ON = 1 THEN
868 print_debug('INV_Minmax_PVT.run_min_max_plan failed with expected error for subinventory '|| l_subinv_tbl(l_subinv_count) ||' returning message: ' || l_msg_data
869 ,l_proc
870 , 9);
871 END IF;
872 END IF;
873 END LOOP;
874 ELSE
875 IF G_TRACE_ON = 1 THEN
876 print_debug('Calling INV_Minmax_PVT.run_min_max_plan for Organization level Min Max Planning'
877 ,l_proc
878 , 9);
879 END IF;
880
881 INV_Minmax_PVT.run_min_max_plan
882 ( p_item_select => l_item_select
883 , p_handle_rep_item => NVL(p_handle_rep_item,3)
884 , p_pur_revision => l_pur_revision
885 , p_cat_select => l_cat_select
886 , p_cat_set_id => l_cat_set_id
887 , p_mcat_struct => l_mcat_struct
888 , p_level => NVL(p_level,2)
889 , p_restock => NVL(p_restock,1)
890 , p_include_nonnet => l_include_no_net
891 , p_include_po => NVL(p_include_po,1)
892 , p_include_mo => NVL(p_include_mo,1)
893 , p_include_wip => NVL(p_include_wip,2)
894 , p_include_if => NVL(P_include_if,1)
895 , p_net_rsv => NVL(p_net_rsv,1)
896 , p_net_unrsv => NVL(p_net_unrsv,1)
897 , p_net_wip => NVL(p_net_wip,2)
898 , p_org_id => p_organization_id
899 , p_user_id => p_user_id
900 , p_employee_id => l_employee_id
901 , p_subinv => NULL
902 , p_dd_loc_id => l_dd_loc_id
903 , p_wip_batch_id => l_wip_batch_id
904 , p_approval => l_approval
905 , p_buyer_hi => p_buyer_hi
906 , p_buyer_lo => p_buyer_lo
907 , p_range_buyer => l_range_buyer
908 , p_cust_id => l_cust_id
909 , p_cust_site_id => l_site_use_id
910 , p_po_org_id => l_po_org_id
911 , p_range_sql => NVL(p_range_sql,'1 = 1')
912 , p_sort => NVL(p_sort,1)
913 , p_selection => NVL(p_selection,3)
914 , p_sysdate => l_sysdate
915 , p_s_cutoff => l_s_cutoff
916 , p_d_cutoff => l_d_cutoff
917 , p_order_by => l_order_by
918 , p_encum_flag => l_encum_flag
919 , p_cal_code => l_cal_code
920 , p_exception_set_id => l_exception_set_id
921 , p_gen_report => l_gen_report
922 , x_return_status => l_return_status
923 , x_msg_data => l_msg_data
924 , p_osfm_batch_id => l_osfm_batch_id /* Added for Bug 6807835 */
925 );
926
927 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
928 IF G_TRACE_ON = 1 THEN
929 print_debug('INV_Minmax_PVT.run_min_max_plan failed with unexpected error ' ||
930 'returning message: ' || l_msg_data
931 ,l_proc
932 , 9);
933 END IF;
934 RAISE fnd_api.g_exc_unexpected_error;
935 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
936 IF G_TRACE_ON = 1 THEN
937 print_debug('INV_Minmax_PVT.run_min_max_plan failed with expected error returning message: ' || l_msg_data
938 ,l_proc
939 , 9);
940 END IF;
941 RAISE fnd_api.g_exc_error;
942 ELSE
943 IF G_TRACE_ON = 1 THEN
944 print_debug('INV_Minmax_PVT.run_min_max_plan returned success'
945 ,l_proc
946 , 9);
947 END IF;
948
949 END IF;
950
951 END IF;
952
953
954 --
955 -- Submit the Concurrent Request for WIP Mass Load.
956 --
957 SELECT COUNT(*)
958 INTO l_count
959 FROM WIP_JOB_SCHEDULE_INTERFACE
960 WHERE GROUP_ID = l_wip_batch_id;
961
962 IF l_count > 0 THEN
963
964 l_count := FND_REQUEST.SUBMIT_REQUEST('WIP', 'WICMLP',
965 NULL, NULL, FALSE,
966 TO_CHAR(l_wip_batch_id),
967 CHR(0), '', '', '', '',
968 '', '', '', '', '', '',
969 '', '', '', '', '', '',
970 '', '', '', '', '', '',
971 '', '', '', '', '', '',
972 '', '', '', '', '', '',
973 '', '', '', '', '', '',
974 '', '', '', '', '', '',
975 '', '', '', '', '', '',
976 '', '', '', '', '', '',
977 '', '', '', '', '', '',
978 '', '', '', '', '', '',
979 '', '', '', '', '', '',
980 '', '', '', '', '', '',
981 '', '', '', '', '', '',
982 '', '', '', '', '', '',
983 '', '', '', '');
984 COMMIT;
985 END IF;
986
987
988 /* Added for Bug 6807835 */
989
990 SELECT count(*)
991 INTO l_job_count
992 FROM WSM_LOT_JOB_INTERFACE
993 WHERE GROUP_ID = l_osfm_batch_id;
994
995 IF l_job_count > 0 THEN
996 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
997 application => 'WSM',
998 program => 'WSMPLBJI',
999 sub_request => FALSE,
1000 argument1 => l_osfm_batch_id);
1001 COMMIT;
1002 END IF;
1003 /* End of Changes for Bug 6807835 */
1004
1005 --Bug 4681032
1006 if x_return_status = FND_API.G_RET_STS_SUCCESS and l_warn='W' then
1007 x_return_status := 'W';
1008 end if;
1009 --Bug 4681032
1010
1011
1012 EXCEPTION
1013 WHEN fnd_api.g_exc_error THEN
1014 ROLLBACK TO sp_exec_min_max;
1015 x_return_status := fnd_api.g_ret_sts_error;
1016 fnd_msg_pub.count_and_get
1017 ( p_count => x_msg_count,
1018 p_data => x_msg_data
1019 );
1020
1021 WHEN fnd_api.g_exc_unexpected_error THEN
1022 ROLLBACK TO sp_exec_min_max;
1023 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1024 fnd_msg_pub.count_and_get
1025 ( p_count => x_msg_count,
1026 p_data => x_msg_data
1027 );
1028
1029 WHEN OTHERS THEN
1030 ROLLBACK TO sp_exec_min_max;
1031 x_return_status := fnd_api.g_ret_sts_unexp_error;
1032 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)THEN
1033 fnd_msg_pub.add_exc_msg
1034 ('INV_MMX_WRAPPER_PVT'
1035 ,l_proc
1036 );
1037 END IF;
1038 fnd_msg_pub.count_and_get
1039 ( p_count => x_msg_count,
1040 p_data => x_msg_data
1041 );
1042
1043 END exec_min_max;
1044
1045
1046 /*
1047 ** ---------------------------------------------------------------------------
1048 ** Procedure : do_restock
1049 **
1050 ** Description : This procedure is called from MRP's Reorder Point report.
1051 **
1052 ** 1) Initializes the package variables for move order line consolidation,
1053 ** current subinventory being planned and current move order header ID
1054 ** and line Number.
1055 ** 2) Calls INV_Minmax_PVT.do_restock.
1056 **
1057 ** Input Parameters:
1058 **
1059 ** p_item_id
1060 ** Inventory Item Id of the Item to be replenished.
1061 ** p_mbf
1062 ** Make or Buy Flag of the Item to be replenished.
1063 ** p_handle_repetitive_item
1064 ** Parameter for Repetitive item handling.
1065 ** 1- Create Requistion
1066 ** 2- Create Discrete Job
1067 ** 3- Do not Restock ,ie Report Only.
1068 ** p_repetitive_planned_item
1069 ** Flag indicating whether item has to be planned as repetitive schedule.
1070 ** p_qty
1071 ** Quantity to be replenished.
1072 ** p_fixed_lead_time
1073 ** Fixed portion of the assembly Item's lead time.
1074 ** p_variable_lead_time
1075 ** Variable portion of the assembly Item's lead time.
1076 ** p_buying_lead_time
1077 ** Preprocessing Lead time + Full Lead Time of the Buy Item.
1078 ** p_uom
1079 ** Primary UOM of the Item.
1080 ** p_accru_acct
1081 ** Accrual Account of the Organization/Operating Unit.
1082 ** p_ipv_acct
1083 ** Invoice Process Varialbe Account.
1084 ** p_budget_acct
1085 ** Budget Account.
1086 ** p_charge_acct
1087 ** Charge Account.
1088 ** p_purch_flag
1089 ** Flag indicating if item may appear on outside operation purchase order.
1090 ** p_order_flag
1091 ** Flag indicating if item is internally orderable.
1092 ** p_transact_flag
1093 ** Flag indicating if item is transactable.
1094 ** p_unit_price
1095 ** Unit list price - purchasing.
1096 ** p_wip_id
1097 ** WIP Batch Id of WIP_JOB_SCHEDULE_INTERFACE.
1098 ** p_user_id
1099 ** Identifier of the User performinng the Min Max planning.
1100 ** p_sysd
1101 ** Current System Date.
1102 ** p_organization_id
1103 ** Identifier of organization for which Min Max planning is to be done.
1104 ** p_approval
1105 ** Approval status.
1106 ** 1-Incomplete.
1107 ** 7-pre-approved.
1108 ** p_build_in_wip
1109 ** Flag indicating if item may be built in WIP.
1110 ** p_pick_components
1111 ** Flag indicating whether all shippable components should be picked.
1112 ** p_src_type
1113 ** Source type for the Item.
1114 ** 1-Inventory.
1115 ** 2-Supplier.
1116 ** 3-Subinventory.
1117 ** p_encum_flag
1118 ** Encumbrance Flag.
1119 ** p_customer_id
1120 ** Customer Id.
1121 ** p_customer_site_id
1122 ** Customer Site Id. Default value is NULL.
1123 ** p_cal_code
1124 ** Calendar Code of the Organization.
1125 ** p_except_id
1126 ** Exception Set Id of the Organization.
1127 ** p_employee_id
1128 ** Identifier of the Employee associated with the User.
1129 ** p_description
1130 ** Description of the Item.
1131 ** p_src_org
1132 ** Organization to source items from.
1133 ** p_src_subinv
1134 ** Subinventory to source items from.
1135 ** p_subinv
1136 ** Subinventory to be replenished.
1137 ** p_location_id
1138 ** Default Delivery To Location Id of the Planning Org.
1139 ** p_po_org_id
1140 ** Operating Unit Id.
1141 ** p_pur_revision
1142 ** Parameter for Purchasing By Revision .
1143 ** Used for Revision controlled items.
1144 ** p_mo_line_grouping
1145 ** Parameter to Control the number of move order headers created.
1146 ** A value of 1(one) denotes "one move order header per execution"
1147 ** whereas a value of 2 stands for
1148 ** "one move order header for each planned subinventory".
1149 ** Defualt Value is 1.
1150 **
1151 ** Output Parameters:
1152 **
1153 ** x_return_status
1154 ** Return status indicating success, error or unexpected error.
1155 ** x_msg_count
1156 ** Number of messages in the message list.
1157 ** x_msg_data
1158 ** If the number of messages in message list is 1, contains
1159 ** message text.
1160 **
1161 ** ---------------------------------------------------------------------------
1162 */
1163
1164 PROCEDURE do_restock
1165 ( x_return_status OUT NOCOPY VARCHAR2
1166 , x_msg_count OUT NOCOPY NUMBER
1167 , x_msg_data OUT NOCOPY VARCHAR2
1168 , p_item_id IN NUMBER
1169 , p_mbf IN NUMBER
1170 , p_handle_repetitive_item IN NUMBER
1171 , p_repetitive_planned_item IN VARCHAR2
1172 , p_qty IN NUMBER
1173 , p_fixed_lead_time IN NUMBER
1174 , p_variable_lead_time IN NUMBER
1175 , p_buying_lead_time IN NUMBER
1176 , p_uom IN VARCHAR2
1177 , p_accru_acct IN NUMBER
1178 , p_ipv_acct IN NUMBER
1179 , p_budget_acct IN NUMBER
1180 , p_charge_acct IN NUMBER
1181 , p_purch_flag IN VARCHAR2
1182 , p_order_flag IN VARCHAR2
1183 , p_transact_flag IN VARCHAR2
1184 , p_unit_price IN NUMBER
1185 , p_wip_id IN NUMBER
1186 , p_user_id IN NUMBER
1187 , p_sysd IN DATE
1188 , p_organization_id IN NUMBER
1189 , p_approval IN NUMBER
1190 , p_build_in_wip IN VARCHAR2
1191 , p_pick_components IN VARCHAR2
1192 , p_src_type IN NUMBER
1193 , p_encum_flag IN VARCHAR2
1194 , p_customer_id IN NUMBER
1195 , p_customer_site_id IN NUMBER
1196 , p_cal_code IN VARCHAR2
1197 , p_except_id IN NUMBER
1198 , p_employee_id IN NUMBER
1199 , p_description IN VARCHAR2
1200 , p_src_org IN NUMBER
1201 , p_src_subinv IN VARCHAR2
1202 , p_subinv IN VARCHAR2
1203 , p_location_id IN NUMBER
1204 , p_po_org_id IN NUMBER
1205 , p_pur_revision IN NUMBER
1206 , p_mo_line_grouping IN NUMBER
1207 ) IS
1208 l_proc_name CONSTANT VARCHAR2(30) := 'DO_RESTOCK';
1209 l_return_status VARCHAR2(1);
1210 l_msg_data VARCHAR2(100);
1211 l_msg_count NUMBER;
1212
1213 BEGIN
1214 SAVEPOINT sp_do_restock;
1215 x_return_status := FND_API.G_RET_STS_SUCCESS;
1216
1217 --
1218 -- Initialize the Package variables.
1219 --
1220 G_MO_LINE_GROUPING := NVL(p_mo_line_grouping,1);
1221 G_CURRENT_SUBINV := NULL;
1222 G_CURRENT_MO_HDR_ID := NULL;
1223 G_CURRENT_MO_LINE_NUM := NULL;
1224
1225 IF G_TRACE_ON = 1 THEN
1226 print_debug ('Executing do_restock with the following parameters: ' || fnd_global.local_chr(10) ||
1227 ' p_item_id ' || to_char(p_item_id) || fnd_global.local_chr(10) ||
1228 ', p_mbf: ' || to_char(p_mbf) || fnd_global.local_chr(10) ||
1229 ', p_handle_repetitive_item: ' || to_char(p_handle_repetitive_item) || fnd_global.local_chr(10) ||
1230 ', p_repetitive_planned_item: ' || p_repetitive_planned_item || fnd_global.local_chr(10) ||
1231 ', p_qty: ' || to_char(p_qty) || fnd_global.local_chr(10) ||
1232 ', p_fixed_lead_time: ' || to_char(p_fixed_lead_time) || fnd_global.local_chr(10) ||
1233 ', p_variable_lead_time: ' || to_char(p_variable_lead_time) || fnd_global.local_chr(10) ||
1234 ', p_buying_lead_time: ' || to_char(p_buying_lead_time) || fnd_global.local_chr(10) ||
1235 ', p_uom: ' || p_uom || fnd_global.local_chr(10) ||
1236 ', p_accru_acct: ' || to_char(p_accru_acct) || fnd_global.local_chr(10) ||
1237 ', p_ipv_acct: ' || to_char(p_ipv_acct) || fnd_global.local_chr(10) ||
1238 ', p_budget_acct: ' || to_char(p_budget_acct) || fnd_global.local_chr(10)
1239 , l_proc_name
1240 , 9);
1241
1242 print_debug ('Parameters Contd..' || fnd_global.local_chr(10) ||
1243 ' p_charge_acct: ' || to_char(p_charge_acct) || fnd_global.local_chr(10) ||
1244 ', p_purch_flag: ' || p_purch_flag || fnd_global.local_chr(10) ||
1245 ', p_order_flag: ' || p_order_flag || fnd_global.local_chr(10) ||
1246 ', p_transact_flag: ' || p_transact_flag || fnd_global.local_chr(10) ||
1247 ', p_unit_price: ' || to_char(p_unit_price) || fnd_global.local_chr(10) ||
1248 ', p_wip_id: ' || to_char(p_wip_id) || fnd_global.local_chr(10) ||
1249 ', p_user_id: ' || to_char(p_user_id) ||
1250 ', p_sysd: ' || to_char(p_sysd, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10) ||
1251 ', p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10) ||
1252 ', p_approval: ' || to_char(p_approval) || fnd_global.local_chr(10) ||
1253 ', p_build_in_wip: ' || p_build_in_wip || fnd_global.local_chr(10) ||
1254 ', p_pick_components: ' || p_pick_components || fnd_global.local_chr(10) ||
1255 ', p_src_type: ' || to_char(p_src_type) || fnd_global.local_chr(10)
1256 , l_proc_name
1257 , 9);
1258
1259 print_debug ('Parameters Contd..' || fnd_global.local_chr(10) ||
1260 ' p_encum_flag: ' || p_encum_flag || fnd_global.local_chr(10) ||
1261 ', p_customer_id: ' || to_char(p_customer_id) || fnd_global.local_chr(10) ||
1262 ', p_customer_site_id: ' || to_char(p_customer_site_id) || fnd_global.local_chr(10) ||
1263 ', p_cal_code: ' || p_cal_code || fnd_global.local_chr(10) ||
1264 ', p_except_id: ' || to_char(p_except_id) || fnd_global.local_chr(10) ||
1265 ', p_employee_id: ' || to_char(p_employee_id) || fnd_global.local_chr(10) ||
1266 ', p_description: ' || p_description || fnd_global.local_chr(10) ||
1267 ', p_src_org: ' || to_char(p_src_org) || fnd_global.local_chr(10) ||
1268 ', p_src_subinv: ' || p_src_subinv || fnd_global.local_chr(10) ||
1269 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
1270 ', p_location_id: ' || to_char(p_location_id) || fnd_global.local_chr(10) ||
1271 ', p_po_org_id: ' || to_char(p_po_org_id) || fnd_global.local_chr(10) ||
1272 ', p_pur_revision: ' || to_char(p_pur_revision) || fnd_global.local_chr(10) ||
1273 ', p_mo_line_grouping ' || to_char(p_mo_line_grouping) || fnd_global.local_chr(10)
1274 , l_proc_name
1275 , 9);
1276 print_debug('Calling INV_Minmax_PVT.do_restock'
1277 , l_proc_name
1278 , 9);
1279 END IF;
1280
1281 INV_Minmax_PVT.do_restock( p_item_id => p_item_id
1282 , p_mbf => p_mbf
1283 , p_handle_repetitive_item => p_handle_repetitive_item
1284 , p_repetitive_planned_item => p_repetitive_planned_item
1285 , p_qty => p_qty
1286 , p_fixed_lead_time => p_fixed_lead_time
1287 , p_variable_lead_time => p_variable_lead_time
1288 , p_buying_lead_time => p_buying_lead_time
1289 , p_uom => p_uom
1290 , p_accru_acct => p_accru_acct
1291 , p_ipv_acct => p_ipv_acct
1292 , p_budget_acct => p_budget_acct
1293 , p_charge_acct => p_charge_acct
1294 , p_purch_flag => p_purch_flag
1295 , p_order_flag => p_order_flag
1296 , p_transact_flag => p_transact_flag
1297 , p_unit_price => p_unit_price
1298 , p_wip_id => p_wip_Id
1299 , p_user_id => p_user_id
1300 , p_sysd => p_sysd
1301 , p_organization_id => p_organization_id
1302 , p_approval => p_approval
1303 , p_build_in_wip => p_build_in_wip
1304 , p_pick_components => p_pick_components
1305 , p_src_type => p_src_type
1306 , p_encum_flag => p_encum_flag
1307 , p_customer_id => p_customer_id
1308 , p_customer_site_id => p_customer_site_id
1309 , p_cal_code => p_cal_code
1310 , p_except_id => p_except_id
1311 , p_employee_id => p_employee_id
1312 , p_description => p_description
1313 , p_src_org => p_src_org
1314 , p_src_subinv => p_src_subinv
1315 , p_subinv => p_subinv
1316 , p_location_id => p_location_id
1317 , p_po_org_id => p_po_org_id
1318 , p_pur_revision => p_pur_revision
1319 , x_ret_stat => l_return_status
1320 , x_ret_mesg => l_msg_data
1321 );
1322
1323 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1324 IF G_TRACE_ON = 1 THEN
1325 print_debug('INV_Minmax_PVT.do_restock failed with unexpected error returning message: ' || l_msg_data
1326 , l_proc_name
1327 , 9);
1328 END IF;
1329 RAISE fnd_api.g_exc_unexpected_error;
1330 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1331 IF G_TRACE_ON = 1 THEN
1332 print_debug('INV_Minmax_PVT.do_restock failed with expected error returning message: ' || l_msg_data
1333 , l_proc_name
1334 , 9);
1335 END IF;
1336 RAISE fnd_api.g_exc_error;
1337 ELSE
1338 IF G_TRACE_ON = 1 THEN
1339 print_debug('INV_Minmax_PVT.do_restock returned success'
1340 , l_proc_name
1341 , 9);
1342 END IF;
1343 END IF;
1344
1345 EXCEPTION
1346 WHEN fnd_api.g_exc_error THEN
1347 ROLLBACK TO sp_do_restock;
1348 x_return_status := fnd_api.g_ret_sts_error;
1349 fnd_msg_pub.count_and_get
1350 ( p_count => x_msg_count,
1351 p_data => x_msg_data
1352 );
1353
1354 WHEN fnd_api.g_exc_unexpected_error THEN
1355 ROLLBACK TO sp_do_restock;
1356 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1357 fnd_msg_pub.count_and_get
1358 ( p_count => x_msg_count,
1359 p_data => x_msg_data
1360 );
1361
1362 WHEN OTHERS THEN
1363 ROLLBACK TO sp_do_restock;
1364 x_return_status := fnd_api.g_ret_sts_unexp_error;
1365 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)THEN
1366 fnd_msg_pub.add_exc_msg
1367 ( G_PKG_NAME
1368 , l_proc_name
1369 );
1370 END IF;
1371 fnd_msg_pub.count_and_get
1372 ( p_count => x_msg_count,
1373 p_data => x_msg_data
1374 );
1375 END do_restock;
1376
1377 /*
1378 ** ---------------------------------------------------------------------------
1379 ** Procedure : get_move_order_info
1380 ** Description : This procedure is called from INV_Minmax_PVT.do_restock to
1381 ** get the move order header ID and move order line number ,
1382 ** prior to creating a move order line.
1383 **
1384 ** 1) It returns the header ID and line number of the existing Header,
1385 ** to be stamped on the move order line based on how consolidation
1386 ** is being done.
1387 ** 2) If a move order header does not exist, it creates one.
1388 **
1389 ** Input Parameters:
1390 **
1391 ** p_user_id
1392 ** Identifier of the User performinng the Min Max planning.
1393 ** p_organization_id
1394 ** Identifier of organization for which Min Max planning is to be done.
1395 ** p_subinv
1396 ** Subinventory Being Planned.
1397 ** p_src_subinv
1398 ** Subinventory to source items from.
1399 ** p_approval
1400 ** Approval status.
1401 ** 1-Incomplete.
1402 ** 7- pre-approved.
1403 ** p_need_by_date
1404 ** Need By Date for the Move Order.
1405 **
1406 ** Output Parameters:
1407 **
1408 ** x_return_status
1409 ** Return status indicating success, error or unexpected error.
1410 ** x_msg_count
1411 ** Number of messages in the message list.
1412 ** x_msg_data
1413 ** If the number of messages in message list is 1, contains
1414 ** message text.
1415 ** x_move_order_header_ID
1416 ** Header Id of the MO to be used.
1417 ** x_move_order_line_num
1418 ** Next Line number of the Move Order.
1419 **
1420 ** ---------------------------------------------------------------------------
1421 */
1422
1423 PROCEDURE get_move_order_info
1424 ( x_return_status OUT NOCOPY VARCHAR2
1425 , x_msg_count OUT NOCOPY NUMBER
1426 , x_msg_data OUT NOCOPY VARCHAR2
1427 , x_move_order_header_id OUT NOCOPY NUMBER
1428 , x_move_order_line_num OUT NOCOPY NUMBER
1429 , p_user_id IN NUMBER
1430 , p_organization_id IN NUMBER
1431 , p_subinv IN VARCHAR2
1432 , p_src_subinv IN VARCHAR2
1433 , p_approval IN NUMBER
1434 , p_need_by_date IN DATE
1435 ) IS
1436 l_proc_name CONSTANT VARCHAR2(30) := 'GET_MOVE_ORDER_INFO';
1437 l_return_status VARCHAR2(1);
1438 l_msg_count NUMBER;
1439 l_msg_data VARCHAR2(240);
1440 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
1441 l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
1442 l_x_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
1443 l_x_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
1444 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1445
1446 BEGIN
1447 SAVEPOINT sp_get_move_order_info;
1448 l_return_status := FND_API.G_RET_STS_SUCCESS;
1449 IF G_TRACE_ON = 1 THEN
1450 print_debug ('Executing get_move_order_info with the following parameters: ' || fnd_global.local_chr(10) ||
1451 ' p_user_id: ' || to_char(p_user_id) || fnd_global.local_chr(10) ||
1452 ', p_organization_id: ' || to_char(p_organization_id) || fnd_global.local_chr(10) ||
1453 ', p_subinv: ' || p_subinv || fnd_global.local_chr(10) ||
1454 ', p_src_subinv: ' || p_src_subinv || fnd_global.local_chr(10) ||
1455 ', p_approval: ' || to_char(p_approval) || fnd_global.local_chr(10) ||
1456 ', p_need_by_time: ' || to_char(p_need_by_date) || fnd_global.local_chr(10)
1457 , l_proc_name
1458 , 9 );
1459 END IF;
1460
1461 IF G_CURRENT_MO_HDR_ID IS NULL OR (G_MO_LINE_GROUPING = 2 AND G_CURRENT_SUBINV <> p_subinv) THEN
1462 --
1463 -- Being called for the first time or (one move order per planning sub and the passed-in sub
1464 -- is different from the sub stored as package variable).
1465 --
1466 l_trohdr_rec.created_by := p_user_id;
1467 l_trohdr_rec.creation_date := sysdate;
1468 l_trohdr_rec.date_required := p_need_by_date;
1469 l_trohdr_rec.from_subinventory_code := p_src_subinv;
1470 l_trohdr_rec.header_status := p_approval;
1471 l_trohdr_rec.last_updated_by := p_user_id;
1472 l_trohdr_rec.last_update_date := sysdate;
1473 l_trohdr_rec.last_update_login := p_user_id;
1474 l_trohdr_rec.organization_id := p_organization_id;
1475 l_trohdr_rec.status_date := sysdate;
1476 l_trohdr_rec.to_subinventory_code := p_subinv;
1477 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT;
1478 l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1479 l_trohdr_rec.db_flag := FND_API.G_TRUE;
1480 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1481 IF G_TRACE_ON = 1 THEN
1482 print_debug('Calling INV_Move_Order_PUB.Create_Move_Order_Header'
1483 , l_proc_name
1484 , 9);
1485 END IF;
1486 INV_Move_Order_PUB.Create_Move_Order_Header(
1487 p_api_version_number => 1,
1488 p_init_msg_list => FND_API.G_FALSE,
1489 p_return_values => FND_API.G_TRUE,
1490 p_commit => l_commit,
1491 x_return_status => l_return_status,
1492 x_msg_count => l_msg_count,
1493 x_msg_data => l_msg_data,
1494 p_trohdr_rec => l_trohdr_rec,
1495 p_trohdr_val_rec => l_trohdr_val_rec,
1496 x_trohdr_rec => l_x_trohdr_rec,
1497 x_trohdr_val_rec => l_x_trohdr_val_rec
1498 );
1499
1500 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1501 IF G_TRACE_ON = 1 THEN
1502 print_debug('INV_Move_Order_PUB.Create_Move_Order_Header failed with unexpected error returning message: ' || l_msg_data
1503 , l_proc_name
1504 , 9);
1505 END IF;
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1508 IF G_TRACE_ON = 1 THEN
1509 print_debug('INV_Move_Order_PUB.Create_Move_Order_Header failed with expected error returning message: ' || l_msg_data
1510 , l_proc_name
1511 , 9);
1512 END IF;
1513 RAISE FND_API.G_EXC_ERROR;
1514 ELSE
1515 IF G_TRACE_ON = 1 THEN
1516 print_debug('INV_Move_Order_PUB.Create_Move_Order_Header returned success with header Id: ' || l_x_trohdr_rec.header_id
1517 , l_proc_name
1518 , 9);
1519 END IF;
1520 END IF;
1521
1522
1523 G_CURRENT_MO_HDR_ID := l_x_trohdr_rec.header_id;
1524 G_CURRENT_MO_LINE_NUM := 0;
1525
1526 IF G_MO_LINE_GROUPING = 2 THEN -- one move order per planning sub
1527 G_CURRENT_SUBINV := p_subinv;
1528 END IF;
1529 END IF;
1530
1531 G_CURRENT_MO_LINE_NUM := G_CURRENT_MO_LINE_NUM + 1;
1532 x_move_order_header_ID := G_CURRENT_MO_HDR_ID;
1533 x_move_order_line_num := G_CURRENT_MO_LINE_NUM ;
1534 EXCEPTION
1535 WHEN fnd_api.g_exc_error THEN
1536 ROLLBACK TO sp_get_move_order_info;
1537 x_return_status := fnd_api.g_ret_sts_error;
1538 fnd_msg_pub.count_and_get
1539 ( p_count => x_msg_count,
1540 p_data => x_msg_data
1541 );
1542
1543 WHEN fnd_api.g_exc_unexpected_error THEN
1544 ROLLBACK TO sp_get_move_order_info;
1545 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1546 fnd_msg_pub.count_and_get
1547 ( p_count => x_msg_count,
1548 p_data => x_msg_data
1549 );
1550
1551 WHEN OTHERS THEN
1552 ROLLBACK TO sp_get_move_order_info;
1553 x_return_status := fnd_api.g_ret_sts_unexp_error;
1554 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)THEN
1555 fnd_msg_pub.add_exc_msg
1556 ( G_PKG_NAME
1557 , l_proc_name
1558 );
1559 END IF;
1560 fnd_msg_pub.count_and_get
1561 ( p_count => x_msg_count,
1562 p_data => x_msg_data
1563 );
1564
1565 END get_move_order_info;
1566
1567
1568 END INV_MMX_WRAPPER_PVT;