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