[Home] [Help]
PACKAGE BODY: APPS.MRP_MRPRPROP_XMLP_PKG
Source
1 PACKAGE BODY MRP_MRPRPROP_XMLP_PKG AS
2 /* $Header: MRPRPROPB.pls 120.3 2008/01/02 12:53:39 nchinnam noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 DATE_FORMAT varchar2(20):='DD'||'-MON-'||'YY';
5 BEGIN
6 DECLARE
7 CAT_STRUCT_NUM NUMBER;
8 CAL_CODE VARCHAR2(20);
9 EXC_SET_ID NUMBER;
10 BEGIN
11 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 IF (P_DEBUG = 'Y') THEN
14 EXECUTE IMMEDIATE
15 'ALTER SESSION SET SQL_TRACE TRUE';
16 END IF;
17 P_REPETITIVE_ITEM := 3;
18 P_LEVEL := 1;
19 P_NET_RSV := 1;
20 P_NET_UNRSV := 1;
21 P_NET_WIP := 1;
22 P_SUBINV := NULL;
23 IF ((P_FIRST_SORT = 2) OR (P_SECOND_SORT = 2) OR (P_THIRD_SORT = 2) OR (P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL)) THEN
24 NULL;
25 END IF;
26 IF ((P_LOW_ITEM IS NOT NULL) OR (P_HIGH_ITEM IS NOT NULL)) THEN
27 NULL;
28 END IF;
29 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL)) THEN
30 NULL;
31 END IF;
32 IF ((P_FIRST_SORT = 6) OR (P_SECOND_SORT = 6) OR (P_THIRD_SORT = 6) OR (P_ABC_CLASS IS NOT NULL)) THEN
33 P_DYNAMIC_ABC := 'abc_cls.abc_class_name';
34 ELSE
35 P_DYNAMIC_ABC := ''' || ''';
36 END IF;
37 P_PUR_REVISION := VALUE('INV_PURCHASING_BY_REVISION');
38 IF P_RESTOCK = 1 THEN
39 SELECT_CALENDAR_DEFAULTS(P_ORG_ID
40 ,CAL_CODE
41 ,EXC_SET_ID);
42 P_CAL_CODE := CAL_CODE;
43 P_EXC_SET_ID := EXC_SET_ID;
44 P_APPROVAL := TO_NUMBER(NVL(VALUE('INV_MINMAX_REORDER_APPROVED')
45 ,'2'));
46 SELECT
47 sysdate
48 INTO P_CURRENT_DATE
49 FROM
50 DUAL;
51 SELECT
52 EMPLOYEE_ID
53 INTO P_EMPLOYEE_ID
54 FROM
55 FND_USER
56 WHERE USER_ID = P_USER_ID;
57 SELECT
58 OPERATING_UNIT
59 INTO P_PO_ORG_ID
60 FROM
61 ORG_ORGANIZATION_DEFINITIONS
62 WHERE ORGANIZATION_ID = P_ORG_ID;
63 SELECT
64 NVL(REQ_ENCUMBRANCE_FLAG
65 ,'N')
66 INTO P_ENCUM_FLAG
67 FROM
68 FINANCIALS_SYSTEM_PARAMS_ALL
69 WHERE NVL(ORG_ID
70 ,-11) = NVL(P_PO_ORG_ID
71 ,-11);
72 SELECT
73 NVL(PO.CUSTOMER_ID
74 ,0)
75 INTO P_CUSTOMER_ID
76 FROM
77 PO_LOCATION_ASSOCIATIONS_ALL PO,
78 HR_LOCATIONS HR
79 WHERE HR.LOCATION_ID = P_DEFAULT_DELIVERY_TO
80 AND HR.LOCATION_ID = po.location_id (+)
81 AND NVL(PO.ORG_ID
82 ,-11) = NVL(P_PO_ORG_ID
83 ,-11);
84 SELECT
85 WIP_JOB_SCHEDULE_INTERFACE_S.NEXTVAL
86 INTO P_WIP_BATCH_ID
87 FROM
88 DUAL;
89 END IF;
90 END;
91 DP_DEMAND_CUTOFF_DATE:=to_char(P_DEMAND_CUTOFF_DATE,DATE_FORMAT);
92 DP_SUPPLY_CUTOFF_DATE:=to_char(P_SUPPLY_CUTOFF_DATE,DATE_FORMAT);
93 RETURN (TRUE);
94 END BEFOREREPORT;
95
96 FUNCTION AFTERREPORT RETURN BOOLEAN IS
97 BEGIN
98 BEGIN
99 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
100 END;
101 RETURN (TRUE);
102 END AFTERREPORT;
103
104 FUNCTION C_CATEGORY_WHEREFORMULA RETURN VARCHAR2 IS
105 BEGIN
106 DECLARE
107 CATEGORY_WHERE VARCHAR2(250);
108 BEGIN
109 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_FIRST_SORT = 2) OR (P_SECOND_SORT = 2) OR (P_THIRD_SORT = 2)) THEN
110 CATEGORY_WHERE := ' AND cat.structure_id = ' || TO_CHAR(P_CAT_STRUCT_NUM);
111 ELSE
112 CATEGORY_WHERE := ' ';
113 END IF;
114 RETURN (CATEGORY_WHERE);
115 END;
116 RETURN NULL;
117 END C_CATEGORY_WHEREFORMULA;
118
119 FUNCTION C_PLANNER_RANGEFORMULA RETURN VARCHAR2 IS
120 BEGIN
121 DECLARE
122 PLANNER_RANGE VARCHAR2(80);
123 BEGIN
124 IF (P_LOW_PLANNER IS NOT NULL) AND (P_HIGH_PLANNER IS NOT NULL) THEN
125 PLANNER_RANGE := ' AND sys.planner_code BETWEEN ''' || P_LOW_PLANNER || ''' AND ''' || P_HIGH_PLANNER || '''';
126 ELSIF (P_LOW_PLANNER IS NOT NULL) THEN
127 PLANNER_RANGE := ' AND sys.planner_code = ''' || P_LOW_PLANNER || '''';
128 ELSIF (P_HIGH_PLANNER IS NOT NULL) THEN
129 PLANNER_RANGE := ' AND sys.planner_code = ''' || P_HIGH_PLANNER || '''';
130 ELSE
131 PLANNER_RANGE := ' ';
132 END IF;
133 RETURN (PLANNER_RANGE);
134 END;
135 RETURN NULL;
136 END C_PLANNER_RANGEFORMULA;
137
138 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
139 BEGIN
140 DECLARE
141 FIRST_ORDER_BY VARCHAR2(250);
142 SECOND_ORDER_BY VARCHAR2(250);
143 THIRD_ORDER_BY VARCHAR2(250);
144 ORDER_BY VARCHAR2(250);
145 BEGIN
146 IF (P_FIRST_SORT = 1) THEN
147 FIRST_ORDER_BY := 1;
148 ELSIF (P_FIRST_SORT = 2) THEN
149 FIRST_ORDER_BY := 2;
150 ELSIF (P_FIRST_SORT = 3) THEN
151 FIRST_ORDER_BY := 3;
152 ELSIF (P_FIRST_SORT = 4) THEN
153 FIRST_ORDER_BY := 4;
154 ELSIF (P_FIRST_SORT = 6) THEN
155 FIRST_ORDER_BY := 5;
156 ELSE
157 FIRST_ORDER_BY := P_ITEM_ORDER_BY;
158 END IF;
159 IF (P_SECOND_SORT = 1) THEN
160 SECOND_ORDER_BY := ',1';
161 ELSIF (P_SECOND_SORT = 2) THEN
162 SECOND_ORDER_BY := ',2';
163 ELSIF (P_SECOND_SORT = 3) THEN
164 SECOND_ORDER_BY := ',3';
165 ELSIF (P_SECOND_SORT = 4) THEN
166 SECOND_ORDER_BY := ',4';
167 ELSIF (P_SECOND_SORT = 6) THEN
168 SECOND_ORDER_BY := ',5';
169 ELSE
170 SECOND_ORDER_BY := ' ';
171 END IF;
172 IF (P_THIRD_SORT = 1) THEN
173 THIRD_ORDER_BY := ',1';
174 ELSIF (P_THIRD_SORT = 2) THEN
175 THIRD_ORDER_BY := ',2';
176 ELSIF (P_THIRD_SORT = 3) THEN
177 THIRD_ORDER_BY := ',3';
178 ELSIF (P_THIRD_SORT = 4) THEN
179 THIRD_ORDER_BY := ',4';
180 ELSIF (P_THIRD_SORT = 6) THEN
181 THIRD_ORDER_BY := ',5';
182 ELSE
183 THIRD_ORDER_BY := ' ';
184 END IF;
185 ORDER_BY := FIRST_ORDER_BY || SECOND_ORDER_BY || THIRD_ORDER_BY || ',1';
186 RETURN (ORDER_BY);
187 END;
188 RETURN NULL;
189 END C_ORDER_BYFORMULA;
190
191 FUNCTION C_TOT_AVAILFORMULA(C_DEMAND_QTY IN NUMBER) RETURN NUMBER IS
192 BEGIN
193 RETURN (C_ONHAND_QTY + C_SUPPLY_QTY - C_DEMAND_QTY);
194 END C_TOT_AVAILFORMULA;
195
196 FUNCTION C_SAFETY_STOCKFORMULA(C_ITEM_ID IN NUMBER
197 ,C_ORD_LEAD_TIME IN NUMBER) RETURN NUMBER IS
198 BEGIN
199 DECLARE
200 SAFETY_STOCK NUMBER;
201 BEGIN
202 SELECT
203 NVL(MAX(S1.SAFETY_STOCK_QUANTITY)
204 ,0)
205 INTO SAFETY_STOCK
206 FROM
207 MTL_SAFETY_STOCKS S1
208 WHERE S1.ORGANIZATION_ID = P_ORG_ID
209 AND S1.INVENTORY_ITEM_ID = C_ITEM_ID
210 AND ( S1.EFFECTIVITY_DATE <= ( sysdate + C_ORD_LEAD_TIME )
211 AND S1.EFFECTIVITY_DATE >= (
212 SELECT
213 NVL(MAX(S2.EFFECTIVITY_DATE)
214 ,SYSDATE)
215 FROM
216 MTL_SAFETY_STOCKS S2
217 WHERE S2.ORGANIZATION_ID = P_ORG_ID
218 AND S2.INVENTORY_ITEM_ID = C_ITEM_ID
219 AND S2.EFFECTIVITY_DATE <= sysdate ) );
220 RETURN (SAFETY_STOCK);
221 END;
222 RETURN NULL;
223 END C_SAFETY_STOCKFORMULA;
224
225 FUNCTION C_FIRST_SORT_PFORMULA RETURN VARCHAR2 IS
226 BEGIN
227 DECLARE
228 SORT VARCHAR2(80);
229 BEGIN
230 SELECT
231 MEANING
232 INTO SORT
233 FROM
234 MFG_LOOKUPS
235 WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
236 AND LOOKUP_CODE = P_FIRST_SORT;
237 RETURN (SORT);
238 END;
239 RETURN NULL;
240 END C_FIRST_SORT_PFORMULA;
241
242 FUNCTION C_SECOND_SORT_PFORMULA RETURN VARCHAR2 IS
243 BEGIN
244 DECLARE
245 SORT VARCHAR2(80);
246 BEGIN
247 SELECT
248 MEANING
249 INTO SORT
250 FROM
251 MFG_LOOKUPS
252 WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
253 AND LOOKUP_CODE = P_SECOND_SORT;
254 RETURN (SORT);
255 END;
256 RETURN NULL;
257 END C_SECOND_SORT_PFORMULA;
258
259 FUNCTION C_THIRD_SORT_PFORMULA RETURN VARCHAR2 IS
260 BEGIN
261 DECLARE
262 SORT VARCHAR2(80);
263 BEGIN
264 SELECT
265 MEANING
266 INTO SORT
267 FROM
268 MFG_LOOKUPS
269 WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
270 AND LOOKUP_CODE = P_THIRD_SORT;
271 RETURN (SORT);
272 END;
273 RETURN NULL;
274 END C_THIRD_SORT_PFORMULA;
275
276 FUNCTION C_CATEGORY_SET_PFORMULA RETURN VARCHAR2 IS
277 BEGIN
278 DECLARE
279 CAT_SET VARCHAR2(30);
280 BEGIN
281 SELECT
282 CATEGORY_SET_NAME
283 INTO CAT_SET
284 FROM
285 MTL_CATEGORY_SETS
286 WHERE CATEGORY_SET_ID = P_CATEGORY_SET;
287 RETURN (CAT_SET);
288 END;
289 RETURN NULL;
290 END C_CATEGORY_SET_PFORMULA;
291
292 FUNCTION C_DISPLAY_DESCRIPTION_PFORMULA RETURN VARCHAR2 IS
293 BEGIN
294 DECLARE
295 TEXT VARCHAR2(80);
296 BEGIN
297 SELECT
298 MEANING
299 INTO TEXT
300 FROM
301 MFG_LOOKUPS
302 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
303 AND LOOKUP_CODE = P_DISPLAY_DESCRIPTION;
304 RETURN (TEXT);
305 END;
306 RETURN NULL;
307 END C_DISPLAY_DESCRIPTION_PFORMULA;
308
309 FUNCTION C_RESTOCK_PFORMULA RETURN VARCHAR2 IS
310 BEGIN
311 DECLARE
312 TEXT VARCHAR2(80);
313 BEGIN
314 SELECT
315 MEANING
316 INTO TEXT
317 FROM
318 MFG_LOOKUPS
319 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
320 AND LOOKUP_CODE = P_RESTOCK;
321 RETURN (TEXT);
322 END;
323 RETURN NULL;
324 END C_RESTOCK_PFORMULA;
325
326 FUNCTION C_REORDER_POINTFORMULA(C_SAFETY_STOCK IN NUMBER) RETURN NUMBER IS
327 BEGIN
328 RETURN (C_LEAD_TIME_DEMAND + C_SAFETY_STOCK);
329 END C_REORDER_POINTFORMULA;
330
331 FUNCTION C_DEMAND_QTYFORMULA(C_ITEM_ID IN NUMBER
332 ,C_ORD_LEAD_TIME IN NUMBER
333 ,C_LOT_CONTROL IN NUMBER) RETURN NUMBER IS
334 BEGIN
335 C_LEAD_TIME_DEMAND := GET_LEAD_TIME_DEMAND(C_ITEM_ID
336 ,P_ORG_ID
337 ,P_FORECAST
338 ,C_ORD_LEAD_TIME);
339 C_ONHAND_QTY := GET_ONHAND_QTY(C_ITEM_ID
340 ,C_LOT_CONTROL
341 ,P_ORG_ID
342 ,P_SUBINV
343 ,P_INCLUDE_NONNET);
344 C_SUPPLY_QTY := GET_SUPPLY(P_SUPPLY_CUTOFF_DATE
345 ,P_ORG_ID
346 ,C_ITEM_ID
347 ,P_INCLUDE_PO
348 ,P_INCLUDE_NONNET
349 ,P_INCLUDE_WIP
350 ,P_INCLUDE_IF
351 ,P_SUBINV);
352 RETURN (GET_DEMAND(C_ITEM_ID
353 ,P_ORG_ID
354 ,P_DEMAND_CUTOFF_DATE
355 ,P_NET_RSV
356 ,P_INCLUDE_NONNET
357 ,P_INCLUDE_WIP
358 ,P_NET_UNRSV
359 ,P_NET_WIP
360 ,P_SUBINV));
361 END C_DEMAND_QTYFORMULA;
362
363 FUNCTION C_REORDER_QTYFORMULA(C_ITEM_ID IN NUMBER
364 ,C_TOT_AVAIL IN NUMBER
365 ,C_REORDER_POINT IN NUMBER
366 ,C_FIX_LOT_MULT IN NUMBER
367 ,C_MIN_ORD_QTY IN NUMBER
368 ,C_MAX_ORD_QTY IN NUMBER) RETURN NUMBER IS
369 BEGIN
370 RETURN (GET_REORDER_QTY(C_ITEM_ID
371 ,C_TOT_AVAIL
372 ,C_REORDER_POINT
373 ,P_ORG_ID
374 ,P_FORECAST
375 ,C_FIX_LOT_MULT
376 ,C_MIN_ORD_QTY
377 ,C_MAX_ORD_QTY));
378 END C_REORDER_QTYFORMULA;
379
380 FUNCTION C_RUN_RESTOCKFORMULA(C_TOT_AVAIL IN NUMBER
381 ,C_REORDER_POINT IN NUMBER
382 ,C_REPETITIVE_PLANNED_ITEM IN VARCHAR2
383 ,C_MAKE_BUY IN NUMBER
384 ,C_CHARGE_ACCT IN NUMBER
385 ,C_ACCRUAL_ACCT IN NUMBER
386 ,C_IPV_ACCT IN NUMBER
387 ,C_BUDGET_ACCT IN NUMBER
388 ,C_SRC_TYPE IN NUMBER
389 ,C_SRC_ORG IN VARCHAR2
390 ,C_ORDER_FLAG IN VARCHAR2
391 ,C_PURCH_FLAG IN VARCHAR2
392 ,C_PROCESS_ENABLED IN VARCHAR2
393 ,C_BUILD_IN_WIP IN VARCHAR2
394 ,C_PICK_COMPONENTS IN VARCHAR2
395 ,C_RECIPE_ENABLED IN VARCHAR2
396 ,C_EXECUTION_ENABLED IN VARCHAR2
397 ,C_ITEM_ID IN NUMBER
398 ,C_REORDER_QTY IN NUMBER
399 ,C_FIXED_LEAD_TIME IN NUMBER
400 ,C_VARIABLE_LEAD_TIME IN NUMBER
401 ,C_PUR_LEAD_TIME IN NUMBER
402 ,C_PRIMARY_UOM IN VARCHAR2
403 ,C_UNIT_PRICE IN NUMBER
404 ,C_DESCRIPTION IN VARCHAR2
405 ,C_SRC_SUBINV IN VARCHAR2) RETURN VARCHAR2 IS
406 BEGIN
407 DECLARE
408 MAKE_BUY NUMBER;
409 L_RETURN_STATUS VARCHAR2(1);
410 L_MSG_COUNT NUMBER;
411 L_MSG_DATA VARCHAR2(2000);
412 L_MSG VARCHAR2(2000);
413 BEGIN
414 IF ((P_RESTOCK = 1) AND (C_TOT_AVAIL < C_REORDER_POINT)) THEN
415 IF (C_REPETITIVE_PLANNED_ITEM = 'Y' AND P_REPETITIVE_ITEM = 3) THEN
416 RETURN (P_REPETITIVE_MESSAGE);
417 END IF;
418 IF (P_LEVEL = 2) THEN
419 MAKE_BUY := 2;
420 ELSE
421 MAKE_BUY := C_MAKE_BUY;
422 END IF;
423 IF MAKE_BUY = 2 THEN
424 IF (C_CHARGE_ACCT IS NULL) OR (C_ACCRUAL_ACCT IS NULL) OR (C_IPV_ACCT IS NULL) OR ((P_ENCUM_FLAG <> 'N') AND (C_BUDGET_ACCT IS NULL)) THEN
425 RETURN (P_MESSAGE_PO1);
426 ELSIF ((C_SRC_TYPE = 1) AND (C_SRC_ORG IS NULL)) THEN
427 RETURN (P_MESSAGE_PO2);
428 ELSIF ((C_SRC_TYPE = 1) AND (P_CUSTOMER_ID = 0)) THEN
429 RETURN (P_MESSAGE_PO3);
430 ELSIF NOT ((C_SRC_TYPE IS NOT NULL) AND (C_SRC_TYPE <> 1 OR C_ORDER_FLAG = 'Y') AND (C_SRC_TYPE <> 2 OR C_PURCH_FLAG = 'Y') AND (C_SRC_TYPE <> 3)) THEN
431 RETURN (P_MESSAGE_PO4);
432 END IF;
433 ELSIF MAKE_BUY = 1 THEN
434 IF C_PROCESS_ENABLED = 'N' THEN
435 IF C_BUILD_IN_WIP <> 'Y' THEN
436 RETURN (P_MESSAGE_WIP1);
437 ELSIF C_PICK_COMPONENTS <> 'N' THEN
438 RETURN (P_MESSAGE_WIP2);
439 END IF;
440 ELSE
441 IF C_RECIPE_ENABLED <> 'Y' OR C_EXECUTION_ENABLED <> 'Y' THEN
442 RETURN (P_MESSAGE_BATCH);
443 END IF;
444 END IF;
445 END IF;
446 ELSE
447 RETURN ('');
448 END IF;
449 INV_MMX_WRAPPER_PVT.DO_RESTOCK(X_RETURN_STATUS => L_RETURN_STATUS
450 ,X_MSG_COUNT => L_MSG_COUNT
451 ,X_MSG_DATA => L_MSG_DATA
452 ,P_ITEM_ID => C_ITEM_ID
453 ,P_MBF => MAKE_BUY
454 ,P_HANDLE_REPETITIVE_ITEM => P_REPETITIVE_ITEM
455 ,P_REPETITIVE_PLANNED_ITEM => C_REPETITIVE_PLANNED_ITEM
456 ,P_QTY => C_REORDER_QTY
457 ,P_FIXED_LEAD_TIME => C_FIXED_LEAD_TIME
458 ,P_VARIABLE_LEAD_TIME => C_VARIABLE_LEAD_TIME
459 ,P_BUYING_LEAD_TIME => C_PUR_LEAD_TIME
460 ,P_UOM => C_PRIMARY_UOM
461 ,P_ACCRU_ACCT => C_ACCRUAL_ACCT
462 ,P_IPV_ACCT => C_IPV_ACCT
463 ,P_BUDGET_ACCT => C_BUDGET_ACCT
464 ,P_CHARGE_ACCT => C_CHARGE_ACCT
465 ,P_PURCH_FLAG => C_PURCH_FLAG
466 ,P_ORDER_FLAG => C_ORDER_FLAG
467 ,P_TRANSACT_FLAG => 'Y'
468 ,P_UNIT_PRICE => C_UNIT_PRICE
469 ,P_WIP_ID => P_WIP_BATCH_ID
470 ,P_USER_ID => P_USER_ID
471 ,P_SYSD => P_CURRENT_DATE
472 ,P_ORGANIZATION_ID => P_ORG_ID
473 ,P_APPROVAL => P_APPROVAL
474 ,P_BUILD_IN_WIP => C_BUILD_IN_WIP
475 ,P_PICK_COMPONENTS => C_PICK_COMPONENTS
476 ,P_SRC_TYPE => C_SRC_TYPE
477 ,P_ENCUM_FLAG => P_ENCUM_FLAG
478 ,P_CUSTOMER_ID => P_CUSTOMER_ID
479 ,P_CAL_CODE => P_CAL_CODE
480 ,P_EXCEPT_ID => P_EXC_SET_ID
481 ,P_EMPLOYEE_ID => P_EMPLOYEE_ID
482 ,P_DESCRIPTION => C_DESCRIPTION
483 ,P_SRC_ORG => TO_NUMBER(C_SRC_ORG)
484 ,P_SRC_SUBINV => C_SRC_SUBINV
485 ,P_SUBINV => P_SUBINV
486 ,P_LOCATION_ID => P_DEFAULT_DELIVERY_TO
487 ,P_PO_ORG_ID => P_PO_ORG_ID
488 ,P_PUR_REVISION => P_PUR_REVISION);
489 IF (L_RETURN_STATUS <> 'S') THEN
490 IF L_MSG_COUNT > 0 THEN
491 FOR i IN 1 .. L_MSG_COUNT LOOP
492 L_MSG := FND_MSG_PUB.GET(I
493 ,'F');
494 /*SRW.MESSAGE(100
495 ,'INV_MMX_WRAPPER_PVT.do_restock returned error:' || L_MSG)*/NULL;
496 FND_MSG_PUB.DELETE_MSG(I);
497 END LOOP;
498 ELSE
499 /*SRW.MESSAGE(100
500 ,'INV_MMX_WRAPPER_PVT.do_restock returned an error: ' || L_MSG_DATA)*/NULL;
501 END IF;
502 RETURN (L_RETURN_STATUS);
503 END IF;
504 RETURN NULL;
505 END;
506 RETURN NULL;
507 END C_RUN_RESTOCKFORMULA;
508
509 FUNCTION C_ABC_ASSGN_PFORMULA RETURN VARCHAR2 IS
510 BEGIN
511 DECLARE
512 ABC_ASSGN VARCHAR2(40);
513 BEGIN
514 SELECT
515 ASSIGNMENT_GROUP_NAME
516 INTO ABC_ASSGN
517 FROM
518 MTL_ABC_ASSIGNMENT_GROUPS
519 WHERE ASSIGNMENT_GROUP_ID = P_ABC_ASSGN
520 AND ORGANIZATION_ID = P_ORG_ID;
521 RETURN (ABC_ASSGN);
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524 NULL;
525 END;
526 RETURN NULL;
527 END C_ABC_ASSGN_PFORMULA;
528
529 FUNCTION C_ABC_CLASS_PFORMULA RETURN VARCHAR2 IS
530 BEGIN
531 DECLARE
532 ABC_CLASS VARCHAR2(40);
533 BEGIN
534 IF (P_ABC_CLASS IS NOT NULL) THEN
535 SELECT
536 ABC_CLASS_NAME
537 INTO ABC_CLASS
538 FROM
539 MTL_ABC_CLASSES
540 WHERE ABC_CLASS_ID = P_ABC_CLASS
541 AND ORGANIZATION_ID = P_ORG_ID;
542 END IF;
543 RETURN (ABC_CLASS);
544 END;
545 RETURN NULL;
546 END C_ABC_CLASS_PFORMULA;
547
548 FUNCTION C_DEFAULT_DEL_PFORMULA RETURN VARCHAR2 IS
549 BEGIN
550 DECLARE
551 LOCATION_NAME HR_LOCATIONS.LOCATION_CODE%TYPE;
552 BEGIN
553 IF (P_DEFAULT_DELIVERY_TO IS NOT NULL) THEN
554 SELECT
555 LOCATION_CODE
556 INTO LOCATION_NAME
557 FROM
558 HR_LOCATIONS
559 WHERE LOCATION_ID = P_DEFAULT_DELIVERY_TO;
560 RETURN (LOCATION_NAME);
561 ELSE
562 RETURN (' ');
563 END IF;
564 END;
565 RETURN NULL;
566 END C_DEFAULT_DEL_PFORMULA;
567
568 FUNCTION C_ITEM_SELECTION_PFORMULA RETURN VARCHAR2 IS
569 BEGIN
570 DECLARE
571 ITEM_SEL VARCHAR2(80);
572 BEGIN
573 SELECT
574 MEANING
575 INTO ITEM_SEL
576 FROM
577 MFG_LOOKUPS
578 WHERE LOOKUP_TYPE = 'MTL_REORDER_RPT'
579 AND LOOKUP_CODE = P_ITEM_SELECTION;
580 RETURN (ITEM_SEL);
581 END;
582 RETURN NULL;
583 END C_ITEM_SELECTION_PFORMULA;
584
585 FUNCTION C_ABC_RANGEFORMULA RETURN VARCHAR2 IS
586 BEGIN
587 DECLARE
588 ABC_RANGE VARCHAR2(80);
589 BEGIN
590 IF (P_ABC_CLASS IS NOT NULL) THEN
591 ABC_RANGE := ' AND abc.abc_class_id = ' || P_ABC_CLASS;
592 ELSE
593 ABC_RANGE := ' ';
594 END IF;
595 IF (P_ABC_ASSGN IS NOT NULL) THEN
596 ABC_RANGE := ABC_RANGE || ' AND abc.assignment_group_id = ' || P_ABC_ASSGN;
597 END IF;
598 RETURN (ABC_RANGE);
599 END;
600 RETURN NULL;
601 END C_ABC_RANGEFORMULA;
602
603 FUNCTION C_INCLUDE_PO_PFORMULA RETURN VARCHAR2 IS
604 BEGIN
605 DECLARE
606 TEXT VARCHAR2(80);
607 BEGIN
608 SELECT
609 MEANING
610 INTO TEXT
611 FROM
612 MFG_LOOKUPS
613 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
614 AND LOOKUP_CODE = P_INCLUDE_PO;
615 RETURN (TEXT);
616 END;
617 RETURN NULL;
618 END C_INCLUDE_PO_PFORMULA;
619
620 FUNCTION C_INCLUDE_WIP_PFORMULA RETURN VARCHAR2 IS
621 BEGIN
622 DECLARE
623 TEXT VARCHAR2(80);
624 BEGIN
625 SELECT
626 MEANING
627 INTO TEXT
628 FROM
629 MFG_LOOKUPS
630 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
631 AND LOOKUP_CODE = P_INCLUDE_WIP;
632 RETURN (TEXT);
633 END;
634 RETURN NULL;
635 END C_INCLUDE_WIP_PFORMULA;
636
637 FUNCTION C_INCLUDE_IF_PFORMULA RETURN VARCHAR2 IS
638 BEGIN
639 DECLARE
640 TEXT VARCHAR2(80);
641 BEGIN
642 SELECT
643 MEANING
644 INTO TEXT
645 FROM
646 MFG_LOOKUPS
647 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
648 AND LOOKUP_CODE = P_INCLUDE_IF;
649 RETURN (TEXT);
650 END;
651 RETURN NULL;
652 END C_INCLUDE_IF_PFORMULA;
653
654 FUNCTION C_INCLUDE_NONNET_PFORMULA RETURN VARCHAR2 IS
655 BEGIN
656 DECLARE
657 TEXT VARCHAR2(80);
658 BEGIN
659 SELECT
660 MEANING
661 INTO TEXT
662 FROM
663 MFG_LOOKUPS
664 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
665 AND LOOKUP_CODE = P_INCLUDE_NONNET;
666 RETURN (TEXT);
667 END;
668 RETURN NULL;
669 END C_INCLUDE_NONNET_PFORMULA;
670
671 FUNCTION C_DISPLAY_ADD_INFO_PFORMULA RETURN VARCHAR2 IS
672 BEGIN
673 DECLARE
674 TEXT VARCHAR2(80);
675 BEGIN
676 SELECT
677 MEANING
678 INTO TEXT
679 FROM
680 MFG_LOOKUPS
681 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
682 AND LOOKUP_CODE = P_DISPLAY_ADD_INFO;
683 RETURN (TEXT);
684 END;
685 RETURN NULL;
686 END C_DISPLAY_ADD_INFO_PFORMULA;
687
688 FUNCTION C_BUYER_RANGEFORMULA(C_BUYER_FROM_P IN VARCHAR2
689 ,C_BUYER_TO_P IN VARCHAR2) RETURN VARCHAR2 IS
690 BEGIN
691 DECLARE
692 BUYER_RANGE VARCHAR2(240);
693 BEGIN
694 IF (P_LOW_BUYER IS NOT NULL) AND (P_HIGH_BUYER IS NOT NULL) THEN
695 BUYER_RANGE := ' AND emp.full_name between ''' || C_BUYER_FROM_P || ''' AND ''' || C_BUYER_TO_P || '''';
696 ELSIF (P_LOW_BUYER IS NOT NULL) THEN
697 BUYER_RANGE := ' AND sys.buyer_id = ' || TO_CHAR(P_LOW_BUYER);
698 ELSIF (P_HIGH_BUYER IS NOT NULL) THEN
699 BUYER_RANGE := ' AND sys.buyer_id = ' || TO_CHAR(P_HIGH_BUYER);
700 ELSE
701 BUYER_RANGE := ' ';
702 END IF;
703 RETURN (BUYER_RANGE);
704 END;
705 RETURN NULL;
706 END C_BUYER_RANGEFORMULA;
707
708 FUNCTION C_BUYER_FROM_PFORMULA RETURN VARCHAR2 IS
709 BEGIN
710 DECLARE
711 VAR_NAME VARCHAR2(240);
712 BEGIN
713 IF (P_LOW_BUYER IS NOT NULL) THEN
714 SELECT
715 FULL_NAME
716 INTO VAR_NAME
717 FROM
718 MTL_EMPLOYEES_VIEW
719 WHERE EMPLOYEE_ID = P_LOW_BUYER
720 AND ORGANIZATION_ID = P_ORG_ID;
721 END IF;
722 RETURN (VAR_NAME);
723 END;
724 RETURN NULL;
725 END C_BUYER_FROM_PFORMULA;
726
727 FUNCTION C_BUYER_TO_PFORMULA RETURN VARCHAR2 IS
728 BEGIN
729 DECLARE
730 VAR_NAME VARCHAR2(240);
731 BEGIN
732 IF (P_HIGH_BUYER IS NOT NULL) THEN
733 SELECT
734 FULL_NAME
735 INTO VAR_NAME
736 FROM
737 MTL_EMPLOYEES_VIEW
738 WHERE EMPLOYEE_ID = P_HIGH_BUYER
739 AND ORGANIZATION_ID = P_ORG_ID;
740 END IF;
741 RETURN (VAR_NAME);
742 END;
743 RETURN NULL;
744 END C_BUYER_TO_PFORMULA;
745
746 FUNCTION C_ABC_WHEREFORMULA RETURN VARCHAR2 IS
747 BEGIN
748 DECLARE
749 ABC_WHERE VARCHAR2(200):=' ';
750 BEGIN
751 IF (P_ABC_CLASS IS NOT NULL) THEN
752 ABC_WHERE := ' AND abc.inventory_item_id = sys.inventory_item_id ' || ' AND abc_cls.abc_class_id = abc.abc_class_id ';
753 ELSIF ((P_FIRST_SORT = 6) OR (P_SECOND_SORT = 6) OR (P_THIRD_SORT = 6)) THEN
754 ABC_WHERE := ' AND abc.inventory_item_id(+) = sys.inventory_item_id ' || ' AND abc_cls.abc_class_id(+) = abc.abc_class_id ';
755 ELSIF (P_ABC_ASSGN IS NOT NULL) THEN
756 ABC_WHERE := ' AND abc.inventory_item_id = sys.inventory_item_id ';
757 END IF;
758 RETURN (ABC_WHERE);
759 END;
760 RETURN NULL;
761 END C_ABC_WHEREFORMULA;
762
763 FUNCTION C_ABC_FROMFORMULA RETURN VARCHAR2 IS
764 BEGIN
765 DECLARE
766 ABC_FROM VARCHAR2(80):=' ';
767 BEGIN
768 IF ((P_ABC_CLASS IS NOT NULL) OR (P_FIRST_SORT = 6) OR (P_SECOND_SORT = 6) OR (P_THIRD_SORT = 6)) THEN
769 ABC_FROM := 'mtl_abc_assignments abc, mtl_abc_classes abc_cls, ';
770 ELSIF (P_ABC_ASSGN IS NOT NULL) THEN
771 ABC_FROM := 'mtl_abc_assignments abc,';
772 END IF;
773 RETURN (ABC_FROM);
774 END;
775 RETURN NULL;
776 END C_ABC_FROMFORMULA;
777
778 FUNCTION C_LEAD_TIME_DEMAND_P RETURN NUMBER IS
779 BEGIN
780 RETURN C_LEAD_TIME_DEMAND;
781 END C_LEAD_TIME_DEMAND_P;
782
783 FUNCTION C_ONHAND_QTY_P RETURN NUMBER IS
784 BEGIN
785 RETURN C_ONHAND_QTY;
786 END C_ONHAND_QTY_P;
787
788 FUNCTION C_SUPPLY_QTY_P RETURN NUMBER IS
789 BEGIN
790 RETURN C_SUPPLY_QTY;
791 END C_SUPPLY_QTY_P;
792
793 FUNCTION NEXT_WORK_DAY(ARG_ORG_ID IN NUMBER
794 ,ARG_BUCKET IN NUMBER
795 ,ARG_DATE IN DATE) RETURN DATE IS
796 X0 DATE;
797 BEGIN
798 /*STPROC.INIT('begin :X0 := MRP_CALENDAR.NEXT_WORK_DAY(:ARG_ORG_ID, :ARG_BUCKET, :ARG_DATE); end;');
799 STPROC.BIND_O(X0);
800 STPROC.BIND_I(ARG_ORG_ID);
801 STPROC.BIND_I(ARG_BUCKET);
802 STPROC.BIND_I(ARG_DATE);
803 STPROC.EXECUTE;
804 STPROC.RETRIEVE(1
805 ,X0);*/
806 X0 := MRP_CALENDAR.NEXT_WORK_DAY(ARG_ORG_ID, ARG_BUCKET, ARG_DATE);
807 RETURN X0;
808 END NEXT_WORK_DAY;
809
810 FUNCTION PREV_WORK_DAY(ARG_ORG_ID IN NUMBER
811 ,ARG_BUCKET IN NUMBER
812 ,ARG_DATE IN DATE) RETURN DATE IS
813 X0 DATE;
814 BEGIN
815 /* STPROC.INIT('begin :X0 := MRP_CALENDAR.PREV_WORK_DAY(:ARG_ORG_ID, :ARG_BUCKET, :ARG_DATE); end;');
816 STPROC.BIND_O(X0);
817 STPROC.BIND_I(ARG_ORG_ID);
818 STPROC.BIND_I(ARG_BUCKET);
819 STPROC.BIND_I(ARG_DATE);
820 STPROC.EXECUTE;
821 STPROC.RETRIEVE(1
822 ,X0);*/
823 X0 := MRP_CALENDAR.PREV_WORK_DAY(ARG_ORG_ID, ARG_BUCKET, ARG_DATE);
824 RETURN X0;
825 END PREV_WORK_DAY;
826
827 FUNCTION DATE_OFFSET(ARG_ORG_ID IN NUMBER
828 ,ARG_BUCKET IN NUMBER
829 ,ARG_DATE IN DATE
830 ,ARG_OFFSET IN NUMBER) RETURN DATE IS
831 X0 DATE;
832 BEGIN
833 /*STPROC.INIT('begin :X0 := MRP_CALENDAR.DATE_OFFSET(:ARG_ORG_ID, :ARG_BUCKET, :ARG_DATE, :ARG_OFFSET); end;');
834 STPROC.BIND_O(X0);
835 STPROC.BIND_I(ARG_ORG_ID);
836 STPROC.BIND_I(ARG_BUCKET);
837 STPROC.BIND_I(ARG_DATE);
838 STPROC.BIND_I(ARG_OFFSET);
839 STPROC.EXECUTE;
840 STPROC.RETRIEVE(1
841 ,X0);*/
842 X0 := MRP_CALENDAR.DATE_OFFSET(ARG_ORG_ID, ARG_BUCKET, ARG_DATE, ARG_OFFSET);
843 RETURN X0;
844 END DATE_OFFSET;
845
846 FUNCTION DAYS_BETWEEN(ARG_ORG_ID IN NUMBER
847 ,ARG_BUCKET IN NUMBER
848 ,ARG_DATE1 IN DATE
849 ,ARG_DATE2 IN DATE) RETURN NUMBER IS
850 X0 NUMBER;
851 BEGIN
852 /*STPROC.INIT('begin :X0 := MRP_CALENDAR.DAYS_BETWEEN(:ARG_ORG_ID, :ARG_BUCKET, :ARG_DATE1, :ARG_DATE2); end;');
853 STPROC.BIND_O(X0);
854 STPROC.BIND_I(ARG_ORG_ID);
855 STPROC.BIND_I(ARG_BUCKET);
856 STPROC.BIND_I(ARG_DATE1);
857 STPROC.BIND_I(ARG_DATE2);
858 STPROC.EXECUTE;
859 STPROC.RETRIEVE(1
860 ,X0);*/
861 X0 := MRP_CALENDAR.DAYS_BETWEEN(ARG_ORG_ID, ARG_BUCKET, ARG_DATE1, ARG_DATE2);
862 RETURN X0;
863 END DAYS_BETWEEN;
864
865 PROCEDURE SELECT_CALENDAR_DEFAULTS(ARG_ORG_ID IN NUMBER
866 ,ARG_CALENDAR_CODE OUT NOCOPY VARCHAR2
867 ,ARG_EXCEPTION_SET_ID OUT NOCOPY NUMBER) IS
868 BEGIN
869 /*STPROC.INIT('begin MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(:ARG_ORG_ID, :ARG_CALENDAR_CODE, :ARG_EXCEPTION_SET_ID); end;');
870 STPROC.BIND_I(ARG_ORG_ID);
871 STPROC.BIND_O(ARG_CALENDAR_CODE);
872 STPROC.BIND_O(ARG_EXCEPTION_SET_ID);
873 STPROC.EXECUTE;
874 STPROC.RETRIEVE(2
875 ,ARG_CALENDAR_CODE);
876 STPROC.RETRIEVE(3
877 ,ARG_EXCEPTION_SET_ID);*/
878 MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ARG_ORG_ID, ARG_CALENDAR_CODE, ARG_EXCEPTION_SET_ID);
879 END SELECT_CALENDAR_DEFAULTS;
880
881 PROCEDURE PUT(NAME IN VARCHAR2
882 ,VAL IN VARCHAR2) IS
883 BEGIN
884 /*STPROC.INIT('begin FND_PROFILE.PUT(:NAME, :VAL); end;');
885 STPROC.BIND_I(NAME);
886 STPROC.BIND_I(VAL);
887 STPROC.EXECUTE;*/
888 FND_PROFILE.PUT(NAME, VAL);
889 END PUT;
890
891 FUNCTION DEFINED(NAME IN VARCHAR2) RETURN BOOLEAN IS
892 X0 BOOLEAN;
893 --X0rv BOOLEAN;
894 BEGIN
895 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.DEFINED(:NAME); :X0 := sys.diutil.bool_to_int(X0rv); end;');
896 STPROC.BIND_I(NAME);
897 STPROC.BIND_O(X0);
898 STPROC.EXECUTE;
899 STPROC.RETRIEVE(2
900 ,X0);*/
901 X0 := FND_PROFILE.DEFINED(NAME);
902 --X0 := sys.diutil.bool_to_int(X0rv);
903 RETURN X0;
904 END DEFINED;
905
906 PROCEDURE GET(NAME IN VARCHAR2
907 ,VAL OUT NOCOPY VARCHAR2) IS
908 BEGIN
909 /* STPROC.INIT('begin FND_PROFILE.GET(:NAME, :VAL); end;');
910 STPROC.BIND_I(NAME);
911 STPROC.BIND_O(VAL);
912 STPROC.EXECUTE;
913 STPROC.RETRIEVE(2
914 ,VAL);*/
915 FND_PROFILE.GET(NAME, VAL);
916 END GET;
917
918 FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
919 X0 VARCHAR2(2000);
920 BEGIN
921 /* STPROC.INIT('begin :X0 := FND_PROFILE.VALUE(:NAME); end;');
922 STPROC.BIND_O(X0);
923 STPROC.BIND_I(NAME);
924 STPROC.EXECUTE;
925 STPROC.RETRIEVE(1
926 ,X0);*/
927 X0 := FND_PROFILE.VALUE(NAME);
928 RETURN X0;
929 END VALUE;
930
931 FUNCTION SAVE_USER(X_NAME IN VARCHAR2
932 ,X_VALUE IN VARCHAR2) RETURN BOOLEAN IS
933 X0 BOOLEAN;
934
935 BEGIN
936 /* STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE_USER(:X_NAME, :X_VALUE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
937 STPROC.BIND_I(X_NAME);
938 STPROC.BIND_I(X_VALUE);
939 STPROC.BIND_O(X0);
940 STPROC.EXECUTE;
941 STPROC.RETRIEVE(3
942 ,X0);*/
943 X0 := FND_PROFILE.SAVE_USER(X_NAME, X_VALUE);
944
945 RETURN X0;
946 END SAVE_USER;
947
948 FUNCTION SAVE(X_NAME IN VARCHAR2
949 ,X_VALUE IN VARCHAR2
950 ,X_LEVEL_NAME IN VARCHAR2
951 ,X_LEVEL_VALUE IN VARCHAR2
952 ,X_LEVEL_VALUE_APP_ID IN VARCHAR2) RETURN BOOLEAN IS
953 X0 BOOLEAN;
954 --X0rv BOOLEAN;
955 BEGIN
956 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE(:X_NAME, :X_VALUE, :X_LEVEL_NAME, :X_LEVEL_VALUE, :X_LEVEL_VALUE_APP_ID); :X0 := sys.diutil.bool_to_int(X0rv); end;');
957 STPROC.BIND_I(X_NAME);
958 STPROC.BIND_I(X_VALUE);
959 STPROC.BIND_I(X_LEVEL_NAME);
960 STPROC.BIND_I(X_LEVEL_VALUE);
961 STPROC.BIND_I(X_LEVEL_VALUE_APP_ID);
962 STPROC.BIND_O(X0);
963 STPROC.EXECUTE;
964 STPROC.RETRIEVE(6
965 ,X0);*/
966 X0 := FND_PROFILE.SAVE(X_NAME, X_VALUE,X_LEVEL_NAME, X_LEVEL_VALUE, X_LEVEL_VALUE_APP_ID);
967 -- X0 := sys.diutil.bool_to_int(X0rv);
968 RETURN X0;
969 END SAVE;
970
971 PROCEDURE GET_SPECIFIC(NAME_Z IN VARCHAR2
972 ,USER_ID_Z IN NUMBER
973 ,RESPONSIBILITY_ID_Z IN NUMBER
974 ,APPLICATION_ID_Z IN NUMBER
975 ,VAL_Z OUT NOCOPY VARCHAR2
976 ,DEFINED_Z OUT NOCOPY BOOLEAN) IS
977
978 BEGIN
979 /*STPROC.INIT('declare DEFINED_Z BOOLEAN; begin DEFINED_Z := sys.diutil.int_to_bool(:DEFINED_Z);
980 FND_PROFILE.GET_SPECIFIC(:NAME_Z, :USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :VAL_Z, DEFINED_Z);
981 :DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z); end;');
982 STPROC.BIND_O(DEFINED_Z);
983 STPROC.BIND_I(NAME_Z);
984 STPROC.BIND_I(USER_ID_Z);
985 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
986 STPROC.BIND_I(APPLICATION_ID_Z);
987 STPROC.BIND_O(VAL_Z);
988 STPROC.EXECUTE;
989 STPROC.RETRIEVE(1
990 ,DEFINED_Z);
991 STPROC.RETRIEVE(6
992 ,VAL_Z);*/
993 --DEFINED_Z1 := sys.diutil.int_to_bool(DEFINED_Z);
994 FND_PROFILE.GET_SPECIFIC(NAME_Z, USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z, VAL_Z, DEFINED_Z);
995
996 --DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z1);
997 END GET_SPECIFIC;
998
999 FUNCTION VALUE_SPECIFIC(NAME IN VARCHAR2
1000 ,USER_ID IN NUMBER
1001 ,RESPONSIBILITY_ID IN NUMBER
1002 ,APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
1003 X0 VARCHAR2(2000);
1004 BEGIN
1005 /*STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_SPECIFIC(:NAME, :USER_ID, :RESPONSIBILITY_ID, :APPLICATION_ID); end;');
1006 STPROC.BIND_O(X0);
1007 STPROC.BIND_I(NAME);
1008 STPROC.BIND_I(USER_ID);
1009 STPROC.BIND_I(RESPONSIBILITY_ID);
1010 STPROC.BIND_I(APPLICATION_ID);
1011 STPROC.EXECUTE;
1012 STPROC.RETRIEVE(1
1013 ,X0);*/
1014 X0 := FND_PROFILE.VALUE_SPECIFIC(NAME, USER_ID, RESPONSIBILITY_ID, APPLICATION_ID);
1015 RETURN X0;
1016 END VALUE_SPECIFIC;
1017
1018 PROCEDURE INITIALIZE(USER_ID_Z IN NUMBER
1019 ,RESPONSIBILITY_ID_Z IN NUMBER
1020 ,APPLICATION_ID_Z IN NUMBER
1021 ,SITE_ID_Z IN NUMBER) IS
1022 BEGIN
1023 /*STPROC.INIT('begin FND_PROFILE.INITIALIZE(:USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :SITE_ID_Z); end;');
1024 STPROC.BIND_I(USER_ID_Z);
1025 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
1026 STPROC.BIND_I(APPLICATION_ID_Z);
1027 STPROC.BIND_I(SITE_ID_Z);
1028 STPROC.EXECUTE;*/
1029 FND_PROFILE.INITIALIZE(USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z, SITE_ID_Z);
1030 END INITIALIZE;
1031
1032 PROCEDURE PUTMULTIPLE(NAMES IN VARCHAR2
1033 ,VALS IN VARCHAR2
1034 ,NUM IN NUMBER) IS
1035 BEGIN
1036 /*STPROC.INIT('begin FND_PROFILE.PUTMULTIPLE(:NAMES, :VALS, :NUM); end;');
1037 STPROC.BIND_I(NAMES);
1038 STPROC.BIND_I(VALS);
1039 STPROC.BIND_I(NUM);
1040 STPROC.EXECUTE;*/
1041 FND_PROFILE.PUTMULTIPLE(NAMES, VALS, NUM);
1042 END PUTMULTIPLE;
1043
1044 PROCEDURE ESTIMATE_LEADTIME(X_ORG_ID IN NUMBER
1045 ,X_FIXED_LEAD IN NUMBER
1046 ,X_VAR_LEAD IN NUMBER
1047 ,X_QUANTITY IN NUMBER
1048 ,X_PROC_DAYS IN NUMBER
1049 ,X_ENTITY_TYPE IN NUMBER
1050 ,X_FUSD IN DATE
1051 ,X_FUCD IN DATE
1052 ,X_LUSD IN DATE
1053 ,X_LUCD IN DATE
1054 ,X_SCHED_DIR IN NUMBER
1055 ,X_EST_DATE OUT NOCOPY DATE) IS
1056 BEGIN
1057 /*STPROC.INIT('begin WIP_CALENDAR.ESTIMATE_LEADTIME(:X_ORG_ID, :X_FIXED_LEAD, :X_VAR_LEAD, :X_QUANTITY, :X_PROC_DAYS, :X_ENTITY_TYPE, :X_FUSD, :X_FUCD, :X_LUSD, :X_LUCD, :X_SCHED_DIR, :X_EST_DATE); end;');
1058 STPROC.BIND_I(X_ORG_ID);
1059 STPROC.BIND_I(X_FIXED_LEAD);
1060 STPROC.BIND_I(X_VAR_LEAD);
1061 STPROC.BIND_I(X_QUANTITY);
1062 STPROC.BIND_I(X_PROC_DAYS);
1063 STPROC.BIND_I(X_ENTITY_TYPE);
1064 STPROC.BIND_I(X_FUSD);
1065 STPROC.BIND_I(X_FUCD);
1066 STPROC.BIND_I(X_LUSD);
1067 STPROC.BIND_I(X_LUCD);
1068 STPROC.BIND_I(X_SCHED_DIR);
1069 STPROC.BIND_O(X_EST_DATE);
1070 STPROC.EXECUTE;
1071 STPROC.RETRIEVE(12
1072 ,X_EST_DATE);*/
1073 WIP_CALENDAR.ESTIMATE_LEADTIME(X_ORG_ID, X_FIXED_LEAD, X_VAR_LEAD, X_QUANTITY, X_PROC_DAYS, X_ENTITY_TYPE, X_FUSD, X_FUCD, X_LUSD, X_LUCD, X_SCHED_DIR, X_EST_DATE);
1074 END ESTIMATE_LEADTIME;
1075
1076 FUNCTION GET_FORECAST_QUANTITY(ITEM_ID IN NUMBER
1077 ,ORG_ID IN NUMBER
1078 ,FORECAST_DESIG IN CHAR
1079 ,START_DATE IN DATE
1080 ,END_DATE IN DATE) RETURN NUMBER IS
1081 DAY_FC_QTY NUMBER;
1082 WEEK_FC_QTY NUMBER;
1083 PERIOD_FC_QTY NUMBER;
1084 CAL_CODE VARCHAR2(20);
1085 EXC_SET_ID NUMBER;
1086 START_SEQ_NUM NUMBER;
1087 END_SEQ_NUM NUMBER;
1088 BEGIN
1089 MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ORG_ID
1090 ,CAL_CODE
1091 ,EXC_SET_ID);
1092 SELECT
1093 CAL1.NEXT_SEQ_NUM,
1094 CAL2.NEXT_SEQ_NUM
1095 INTO START_SEQ_NUM,END_SEQ_NUM
1096 FROM
1097 BOM_CALENDAR_DATES CAL1,
1098 BOM_CALENDAR_DATES CAL2
1099 WHERE CAL1.CALENDAR_CODE = CAL2.CALENDAR_CODE
1100 AND CAL1.CALENDAR_CODE = CAL_CODE
1101 AND CAL1.EXCEPTION_SET_ID = CAL2.EXCEPTION_SET_ID
1102 AND CAL1.EXCEPTION_SET_ID = EXC_SET_ID
1103 AND CAL1.CALENDAR_DATE = START_DATE
1104 AND CAL2.CALENDAR_DATE = END_DATE;
1105 SELECT
1106 NVL(SUM(FC.ORIGINAL_FORECAST_QUANTITY * (DECODE(SIGN(END_SEQ_NUM - CAL2.NEXT_SEQ_NUM)
1107 ,-1
1108 ,END_SEQ_NUM
1109 ,CAL2.NEXT_SEQ_NUM) - DECODE(SIGN(START_SEQ_NUM - CAL1.NEXT_SEQ_NUM)
1110 ,-1
1111 ,CAL1.NEXT_SEQ_NUM
1112 ,START_SEQ_NUM)))
1113 ,0)
1114 INTO DAY_FC_QTY
1115 FROM
1116 BOM_CALENDAR_DATES CAL1,
1117 BOM_CALENDAR_DATES CAL2,
1118 MRP_FORECAST_DATES FC,
1119 MRP_FORECAST_DESIGNATORS DESIG1,
1120 MRP_FORECAST_DESIGNATORS DESIG2
1121 WHERE DESIG2.FORECAST_DESIGNATOR = FORECAST_DESIG
1122 AND DESIG1.ORGANIZATION_ID = FC.ORGANIZATION_ID
1123 AND DESIG2.ORGANIZATION_ID = FC.ORGANIZATION_ID
1124 AND FC.ORGANIZATION_ID = ORG_ID
1125 AND DESIG1.FORECAST_SET = NVL(DESIG2.FORECAST_SET
1126 ,FORECAST_DESIG)
1127 AND DESIG1.FORECAST_DESIGNATOR = DECODE(DESIG2.FORECAST_SET
1128 ,NULL
1129 ,DESIG1.FORECAST_DESIGNATOR
1130 ,FORECAST_DESIG)
1131 AND NVL(DESIG1.DISABLE_DATE
1132 ,SYSDATE) >= sysdate
1133 AND FC.FORECAST_DATE < END_DATE
1134 AND FC.INVENTORY_ITEM_ID = ITEM_ID
1135 AND FC.FORECAST_DESIGNATOR = DESIG1.FORECAST_DESIGNATOR
1136 AND FC.BUCKET_TYPE = 1
1137 AND CAL1.CALENDAR_CODE = CAL_CODE
1138 AND CAL1.EXCEPTION_SET_ID = EXC_SET_ID
1139 AND CAL1.CALENDAR_DATE = FC.FORECAST_DATE
1140 AND CAL2.CALENDAR_CODE = CAL1.CALENDAR_CODE
1141 AND CAL2.EXCEPTION_SET_ID = CAL1.EXCEPTION_SET_ID
1142 AND CAL2.CALENDAR_DATE = NVL(FC.RATE_END_DATE
1143 ,FC.FORECAST_DATE) + 1
1144 AND CAL2.CALENDAR_DATE > START_DATE;
1145 SELECT
1146 NVL(SUM(FC.ORIGINAL_FORECAST_QUANTITY / (CAL2.NEXT_SEQ_NUM - CAL1.NEXT_SEQ_NUM) * (DECODE(SIGN(END_SEQ_NUM - CAL2.NEXT_SEQ_NUM)
1147 ,-1
1148 ,END_SEQ_NUM
1149 ,CAL2.NEXT_SEQ_NUM) - DECODE(SIGN(START_SEQ_NUM - CAL1.NEXT_SEQ_NUM)
1150 ,-1
1151 ,CAL1.NEXT_SEQ_NUM
1152 ,START_SEQ_NUM)))
1153 ,0)
1154 INTO WEEK_FC_QTY
1155 FROM
1156 BOM_CALENDAR_DATES CAL1,
1157 BOM_CALENDAR_DATES CAL2,
1158 BOM_CAL_WEEK_START_DATES WEEK,
1159 MRP_FORECAST_DATES FC,
1160 MRP_FORECAST_DESIGNATORS DESIG1,
1161 MRP_FORECAST_DESIGNATORS DESIG2
1162 WHERE DESIG2.FORECAST_DESIGNATOR = FORECAST_DESIG
1163 AND DESIG1.ORGANIZATION_ID = FC.ORGANIZATION_ID
1164 AND DESIG2.ORGANIZATION_ID = FC.ORGANIZATION_ID
1165 AND FC.ORGANIZATION_ID = ORG_ID
1166 AND DESIG1.FORECAST_SET = NVL(DESIG2.FORECAST_SET
1167 ,FORECAST_DESIG)
1168 AND DESIG1.FORECAST_DESIGNATOR = DECODE(DESIG2.FORECAST_SET
1169 ,NULL
1170 ,DESIG1.FORECAST_DESIGNATOR
1171 ,FORECAST_DESIG)
1172 AND NVL(DESIG1.DISABLE_DATE
1173 ,SYSDATE) >= sysdate
1174 AND FC.FORECAST_DATE < END_DATE
1175 AND FC.INVENTORY_ITEM_ID = ITEM_ID
1176 AND FC.FORECAST_DESIGNATOR = DESIG1.FORECAST_DESIGNATOR
1177 AND FC.BUCKET_TYPE = 2
1178 AND WEEK.CALENDAR_CODE = CAL_CODE
1179 AND WEEK.EXCEPTION_SET_ID = EXC_SET_ID
1180 AND ( WEEK.WEEK_START_DATE >= FC.FORECAST_DATE
1181 AND WEEK.WEEK_START_DATE < END_DATE
1182 AND WEEK.WEEK_START_DATE <= NVL(FC.RATE_END_DATE
1183 ,FC.FORECAST_DATE) )
1184 AND WEEK.NEXT_DATE > START_DATE
1185 AND CAL1.CALENDAR_CODE = WEEK.CALENDAR_CODE
1186 AND CAL2.CALENDAR_CODE = WEEK.CALENDAR_CODE
1187 AND CAL1.EXCEPTION_SET_ID = WEEK.EXCEPTION_SET_ID
1188 AND CAL2.EXCEPTION_SET_ID = WEEK.EXCEPTION_SET_ID
1189 AND CAL1.CALENDAR_DATE = WEEK.WEEK_START_DATE
1190 AND CAL2.CALENDAR_DATE = WEEK.NEXT_DATE;
1191 SELECT
1192 NVL(SUM(FC.ORIGINAL_FORECAST_QUANTITY / (CAL2.NEXT_SEQ_NUM - CAL1.NEXT_SEQ_NUM) * (DECODE(SIGN(END_SEQ_NUM - CAL2.NEXT_SEQ_NUM)
1193 ,-1
1194 ,END_SEQ_NUM
1195 ,CAL2.NEXT_SEQ_NUM) - DECODE(SIGN(START_SEQ_NUM - CAL1.NEXT_SEQ_NUM)
1196 ,-1
1197 ,CAL1.NEXT_SEQ_NUM
1198 ,START_SEQ_NUM)))
1199 ,0)
1200 INTO PERIOD_FC_QTY
1201 FROM
1202 BOM_CALENDAR_DATES CAL1,
1203 BOM_CALENDAR_DATES CAL2,
1204 BOM_PERIOD_START_DATES PER,
1205 MRP_FORECAST_DATES FC,
1206 MRP_FORECAST_DESIGNATORS DESIG1,
1207 MRP_FORECAST_DESIGNATORS DESIG2
1208 WHERE DESIG2.FORECAST_DESIGNATOR = FORECAST_DESIG
1209 AND DESIG1.ORGANIZATION_ID = FC.ORGANIZATION_ID
1210 AND DESIG2.ORGANIZATION_ID = FC.ORGANIZATION_ID
1211 AND FC.ORGANIZATION_ID = ORG_ID
1212 AND DESIG1.FORECAST_SET = NVL(DESIG2.FORECAST_SET
1213 ,FORECAST_DESIG)
1214 AND DESIG1.FORECAST_DESIGNATOR = DECODE(DESIG2.FORECAST_SET
1215 ,NULL
1216 ,DESIG1.FORECAST_DESIGNATOR
1217 ,FORECAST_DESIG)
1218 AND NVL(DESIG1.DISABLE_DATE
1219 ,SYSDATE) >= sysdate
1220 AND FC.FORECAST_DATE < END_DATE
1221 AND FC.INVENTORY_ITEM_ID = ITEM_ID
1222 AND FC.FORECAST_DESIGNATOR = DESIG1.FORECAST_DESIGNATOR
1223 AND FC.BUCKET_TYPE = 3
1224 AND PER.CALENDAR_CODE = CAL_CODE
1225 AND PER.EXCEPTION_SET_ID = EXC_SET_ID
1226 AND ( PER.PERIOD_START_DATE >= FC.FORECAST_DATE
1227 AND PER.PERIOD_START_DATE < END_DATE
1228 AND PER.PERIOD_START_DATE <= NVL(FC.RATE_END_DATE
1229 ,FC.FORECAST_DATE) )
1230 AND PER.NEXT_DATE > START_DATE
1231 AND CAL1.CALENDAR_CODE = PER.CALENDAR_CODE
1232 AND CAL2.CALENDAR_CODE = PER.CALENDAR_CODE
1233 AND CAL1.EXCEPTION_SET_ID = PER.EXCEPTION_SET_ID
1234 AND CAL2.EXCEPTION_SET_ID = PER.EXCEPTION_SET_ID
1235 AND CAL1.CALENDAR_DATE = PER.PERIOD_START_DATE
1236 AND CAL2.CALENDAR_DATE = PER.NEXT_DATE;
1237 RETURN (DAY_FC_QTY + WEEK_FC_QTY + PERIOD_FC_QTY);
1238 END GET_FORECAST_QUANTITY;
1239
1240 FUNCTION GET_REORDER_QTY(ITEM_ID IN NUMBER
1241 ,TOT_AVAIL IN NUMBER
1242 ,REORDER_POINT IN NUMBER
1243 ,ORG_ID IN NUMBER
1244 ,FORECAST_DESIG IN CHAR
1245 ,FIX_LOT_MULT IN NUMBER
1246 ,MIN_ORD_QTY IN NUMBER
1247 ,MAX_ORD_QTY IN NUMBER) RETURN NUMBER IS
1248 MAX_PT NUMBER;
1249 COST_RATIO NUMBER;
1250 PERIOD_START_DATE DATE;
1251 PERIOD_END_DATE DATE;
1252 FC_QTY NUMBER;
1253 ANNUAL_DEMAND NUMBER;
1254 REORDER_QTY NUMBER;
1255 QUOTIENT NUMBER;
1256 CAL_CODE VARCHAR2(20);
1257 EXC_SET_ID NUMBER;
1258 L_ROUND NUMBER;
1259 C_PROCESS_ENABLED VARCHAR2(5);
1260 BEGIN
1261 IF (TOT_AVAIL < REORDER_POINT) THEN
1262 MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ORG_ID
1263 ,CAL_CODE
1264 ,EXC_SET_ID);
1265 MAX_PT := REORDER_POINT - TOT_AVAIL;
1266 SELECT
1267 NVL(PROCESS_ENABLED_FLAG
1268 ,'N')
1269 INTO C_PROCESS_ENABLED
1270 FROM
1271 MTL_PARAMETERS
1272 WHERE ORGANIZATION_ID = ORG_ID;
1273 IF C_PROCESS_ENABLED <> 'Y' THEN
1274 SELECT
1275 DECODE(NVL(CST.ITEM_COST
1276 ,0) * NVL(SYS.CARRYING_COST / 100
1277 ,0)
1278 ,0
1279 ,0
1280 ,NVL(SYS.ORDER_COST
1281 ,0) / (CST.ITEM_COST * (SYS.CARRYING_COST / 100)))
1282 INTO COST_RATIO
1283 FROM
1284 MTL_SYSTEM_ITEMS SYS,
1285 CST_ITEM_COSTS_FOR_GL_VIEW CST
1286 WHERE cst.organization_id (+) = SYS.ORGANIZATION_ID
1287 AND SYS.ORGANIZATION_ID = ORG_ID
1288 AND cst.inventory_item_id (+) = SYS.INVENTORY_ITEM_ID
1289 AND SYS.INVENTORY_ITEM_ID = ITEM_ID;
1290 ELSE
1291 SELECT
1292 DECODE(NVL(GMP_APS_OUTPUT_PKG.RETRIEVE_ITEM_COST(ITEM_ID
1293 ,ORG_ID)
1294 ,0) * NVL(SYS.CARRYING_COST / 100
1295 ,0)
1296 ,0
1297 ,0
1298 ,NVL(SYS.ORDER_COST
1299 ,0) / (GMP_APS_OUTPUT_PKG.RETRIEVE_ITEM_COST(ITEM_ID
1300 ,ORG_ID) * (SYS.CARRYING_COST / 100)))
1301 INTO COST_RATIO
1302 FROM
1303 MTL_SYSTEM_ITEMS SYS
1304 WHERE SYS.ORGANIZATION_ID = ORG_ID
1305 AND SYS.INVENTORY_ITEM_ID = ITEM_ID;
1306 END IF;
1307 SELECT
1308 NVL(MAX(PER.PERIOD_START_DATE)
1309 ,TRUNC(SYSDATE)),
1310 NVL(MAX(PER.NEXT_DATE)
1311 ,TRUNC(SYSDATE))
1312 INTO PERIOD_START_DATE,PERIOD_END_DATE
1313 FROM
1314 BOM_PERIOD_START_DATES PER
1315 WHERE PER.CALENDAR_CODE = CAL_CODE
1316 AND PER.EXCEPTION_SET_ID = EXC_SET_ID
1317 AND PER.PERIOD_START_DATE <= TRUNC(SYSDATE);
1318 FC_QTY := GET_FORECAST_QUANTITY(ITEM_ID
1319 ,ORG_ID
1320 ,FORECAST_DESIG
1321 ,PERIOD_START_DATE
1322 ,PERIOD_END_DATE);
1323 SELECT
1324 DECODE(CAL.QUARTERLY_CALENDAR_TYPE
1325 ,4
1326 ,(FC_QTY * 13)
1327 ,(FC_QTY * 12))
1328 INTO ANNUAL_DEMAND
1329 FROM
1330 BOM_CALENDARS CAL,
1331 MTL_PARAMETERS PARAM
1332 WHERE PARAM.ORGANIZATION_ID = ORG_ID
1333 AND CAL.CALENDAR_CODE = PARAM.CALENDAR_CODE;
1334 SELECT
1335 SQRT(2 * ANNUAL_DEMAND * COST_RATIO)
1336 INTO REORDER_QTY
1337 FROM
1338 DUAL;
1339 IF (MAX_PT < MIN_ORD_QTY) THEN
1340 MAX_PT := MIN_ORD_QTY;
1341 END IF;
1342 IF (MAX_PT >= REORDER_QTY) THEN
1343 REORDER_QTY := MAX_PT;
1344 END IF;
1345 IF (FIX_LOT_MULT <> 0) THEN
1346 SELECT
1347 ROUND(REORDER_QTY / FIX_LOT_MULT
1348 ,0)
1349 INTO QUOTIENT
1350 FROM
1351 DUAL;
1352 REORDER_QTY := FIX_LOT_MULT * QUOTIENT;
1353 IF (REORDER_QTY < MAX_PT) THEN
1354 REORDER_QTY := REORDER_QTY + FIX_LOT_MULT;
1355 END IF;
1356 END IF;
1357 IF (MAX_ORD_QTY <> 0) THEN
1358 IF (REORDER_QTY >= MAX_ORD_QTY) THEN
1359 REORDER_QTY := MAX_ORD_QTY;
1360 END IF;
1361 END IF;
1362 ELSE
1363 REORDER_QTY := 0;
1364 END IF;
1365 SELECT
1366 ROUNDING_CONTROL_TYPE
1367 INTO L_ROUND
1368 FROM
1369 MTL_SYSTEM_ITEMS
1370 WHERE ORGANIZATION_ID = ORG_ID
1371 AND INVENTORY_ITEM_ID = ITEM_ID;
1372 IF L_ROUND = 1 THEN
1373 RETURN (ROUND(REORDER_QTY));
1374 ELSE
1375 RETURN (REORDER_QTY);
1376 END IF;
1377 END GET_REORDER_QTY;
1378
1379 FUNCTION GET_LEAD_TIME_DEMAND(ITEM_ID IN NUMBER
1380 ,ORG_ID IN NUMBER
1381 ,FORECAST_DESIG IN CHAR
1382 ,ORD_LEAD_TIME IN NUMBER) RETURN NUMBER IS
1383 START_DATE DATE;
1384 END_DATE DATE;
1385 CAL_CODE VARCHAR2(20);
1386 EXC_SET_ID NUMBER;
1387 BEGIN
1388 MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ORG_ID
1389 ,CAL_CODE
1390 ,EXC_SET_ID);
1391 BEGIN
1392 SELECT
1393 TRUNC(SYSDATE),
1394 CAL2.CALENDAR_DATE
1395 INTO START_DATE,END_DATE
1396 FROM
1397 BOM_CALENDAR_DATES CAL1,
1398 BOM_CALENDAR_DATES CAL2
1399 WHERE CAL1.CALENDAR_CODE = CAL_CODE
1400 AND CAL1.EXCEPTION_SET_ID = EXC_SET_ID
1401 AND CAL1.CALENDAR_CODE = CAL2.CALENDAR_CODE
1402 AND CAL1.EXCEPTION_SET_ID = CAL2.EXCEPTION_SET_ID
1403 AND CAL1.CALENDAR_DATE = TRUNC(SYSDATE)
1404 AND CAL2.SEQ_NUM = ROUND(CAL1.NEXT_SEQ_NUM + ORD_LEAD_TIME);
1405 EXCEPTION
1406 WHEN OTHERS THEN
1407 /*SRW.MESSAGE(100
1408 ,'Error getting data from bom_calendar_dates, make sure calendar is extended ')*/NULL;
1409 /*SRW.MESSAGE(100
1410 ,'Calendar_code = ' || CAL_CODE)*/NULL;
1411 /*SRW.MESSAGE(100
1412 ,'Organization Id = ' || ORG_ID)*/NULL;
1413 /*SRW.MESSAGE(100
1414 ,'Forecast Designator = ' || FORECAST_DESIG)*/NULL;
1415 /*SRW.MESSAGE(100
1416 ,'Item Id = ' || ITEM_ID)*/NULL;
1417 /*SRW.MESSAGE(100
1418 ,'Lead Time = ' || ORD_LEAD_TIME)*/NULL;
1419 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
1420 END;
1421 RETURN (GET_FORECAST_QUANTITY(ITEM_ID
1422 ,ORG_ID
1423 ,FORECAST_DESIG
1424 ,START_DATE
1425 ,END_DATE));
1426 END GET_LEAD_TIME_DEMAND;
1427
1428 FUNCTION GET_DEMAND(ITEM_ID IN NUMBER
1429 ,ORG_ID IN NUMBER
1430 ,DEMAND_CUTOFF_DATE IN DATE
1431 ,NET_RSV IN NUMBER
1432 ,INCLUDE_NONNET IN NUMBER
1433 ,INCLUDE_WIP IN NUMBER
1434 ,NET_UNRSV IN NUMBER
1435 ,NET_WIP IN NUMBER
1436 ,SUBINV IN CHAR) RETURN NUMBER IS
1437 QTY NUMBER;
1438 TOTAL NUMBER;
1439 LV_ORG_ID NUMBER;
1440 C_PROCESS_ENABLED VARCHAR2(5);
1441 BEGIN
1442 TOTAL := 0;
1443 LV_ORG_ID := ORG_ID;
1444 IF (NET_RSV = 1) THEN
1445 SELECT
1446 SUM(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY
1447 ,0)
1448 ,COMPLETED_QUANTITY))
1449 INTO QTY
1450 FROM
1451 MTL_DEMAND
1452 WHERE RESERVATION_TYPE = 2
1453 AND DEMAND_SOURCE_TYPE NOT IN ( 2 , 8 , 12 )
1454 AND ORGANIZATION_ID = ORG_ID
1455 AND INVENTORY_ITEM_ID = ITEM_ID
1456 AND PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY
1457 ,0)
1458 ,COMPLETED_QUANTITY)
1459 AND REQUIREMENT_DATE <= DEMAND_CUTOFF_DATE
1460 AND ( NVL(SUBINVENTORY
1461 ,'x') = DECODE(SUBINV
1462 ,NULL
1463 ,NVL(SUBINVENTORY
1464 ,'x')
1465 ,SUBINV)
1466 OR EXISTS (
1467 SELECT
1468 1
1469 FROM
1470 MTL_SECONDARY_INVENTORIES S
1471 WHERE S.ORGANIZATION_ID = ORG_ID
1472 AND S.SECONDARY_INVENTORY_NAME = NVL(SUBINV
1473 ,SUBINVENTORY)
1474 AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1475 ,1
1476 ,S.AVAILABILITY_TYPE
1477 ,1) ) )
1478 AND ( LOCATOR_ID IS NULL
1479 OR EXISTS (
1480 SELECT
1481 1
1482 FROM
1483 MTL_ITEM_LOCATIONS MIL
1484 WHERE MIL.ORGANIZATION_ID = ORG_ID
1485 AND MIL.INVENTORY_LOCATION_ID = LOCATOR_ID
1486 AND MIL.SUBINVENTORY_CODE = NVL(SUBINVENTORY
1487 ,MIL.SUBINVENTORY_CODE)
1488 AND MIL.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1489 ,1
1490 ,MIL.AVAILABILITY_TYPE
1491 ,1) ) )
1492 AND ( LOT_NUMBER IS NULL
1493 OR EXISTS (
1494 SELECT
1495 1
1496 FROM
1497 MTL_LOT_NUMBERS MLN
1498 WHERE MLN.ORGANIZATION_ID = ORG_ID
1499 AND MLN.LOT_NUMBER = LOT_NUMBER
1500 AND MLN.INVENTORY_ITEM_ID = ITEM_ID
1501 AND MLN.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1502 ,1
1503 ,MLN.AVAILABILITY_TYPE
1504 ,1) ) );
1505 TOTAL := TOTAL + NVL(QTY
1506 ,0);
1507 END IF;
1508 IF (NET_UNRSV = 1) THEN
1509 SELECT
1510 SUM(DECODE(OOL.ORDERED_QUANTITY
1511 ,NULL
1512 ,0
1513 ,INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(OOL.SHIP_FROM_ORG_ID
1514 ,OOL.INVENTORY_ITEM_ID
1515 ,OOL.ORDER_QUANTITY_UOM
1516 ,OOL.ORDERED_QUANTITY)))
1517 INTO QTY
1518 FROM
1519 OE_ORDER_LINES_ALL OOL
1520 WHERE OPEN_FLAG = 'Y'
1521 AND VISIBLE_DEMAND_FLAG = 'Y'
1522 AND SHIPPED_QUANTITY IS NULL
1523 AND SHIP_FROM_ORG_ID = LV_ORG_ID
1524 AND INVENTORY_ITEM_ID = ITEM_ID
1525 AND SCHEDULE_SHIP_DATE <= DEMAND_CUTOFF_DATE
1526 AND ( NVL(SUBINVENTORY
1527 ,1) = DECODE(SUBINV
1528 ,NULL
1529 ,NVL(SUBINVENTORY
1530 ,1)
1531 ,SUBINV)
1532 OR EXISTS (
1533 SELECT
1534 1
1535 FROM
1536 MTL_SECONDARY_INVENTORIES S
1537 WHERE S.ORGANIZATION_ID = LV_ORG_ID
1538 AND S.SECONDARY_INVENTORY_NAME = NVL(SUBINV
1539 ,SUBINVENTORY)
1540 AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1541 ,1
1542 ,S.AVAILABILITY_TYPE
1543 ,1) ) );
1544 TOTAL := TOTAL + NVL(QTY
1545 ,0);
1546 END IF;
1547 IF (NET_WIP = 1) THEN
1548 SELECT
1549 NVL(PROCESS_ENABLED_FLAG
1550 ,'N')
1551 INTO C_PROCESS_ENABLED
1552 FROM
1553 MTL_PARAMETERS
1554 WHERE ORGANIZATION_ID = ORG_ID;
1555 IF C_PROCESS_ENABLED = 'Y' THEN
1556 SELECT
1557 SUM((NVL((NVL(D.WIP_PLAN_QTY
1558 ,D.PLAN_QTY) - D.ACTUAL_QTY)
1559 ,0) * (D.ORIGINAL_PRIMARY_QTY / D.ORIGINAL_QTY)) - NVL(MTR.PRIMARY_RESERVATION_QUANTITY
1560 ,0))
1561 INTO QTY
1562 FROM
1563 GME_MATERIAL_DETAILS D,
1564 GME_BATCH_HEADER H,
1565 MTL_RESERVATIONS MTR
1566 WHERE H.BATCH_TYPE IN ( 0 , 10 )
1567 AND H.BATCH_STATUS IN ( 1 , 2 )
1568 AND H.BATCH_ID = D.BATCH_ID
1569 AND D.LINE_TYPE = - 1
1570 AND NVL(D.ORIGINAL_QTY
1571 ,0) <> 0
1572 AND D.ORGANIZATION_ID = ORG_ID
1573 AND D.INVENTORY_ITEM_ID = ITEM_ID
1574 AND D.BATCH_ID = mtr.demand_source_header_id (+)
1575 AND D.MATERIAL_DETAIL_ID = mtr.demand_source_line_id (+)
1576 AND D.INVENTORY_ITEM_ID = mtr.inventory_item_id (+)
1577 AND D.ORGANIZATION_ID = mtr.organization_id (+)
1578 AND ( ( NVL((NVL(D.WIP_PLAN_QTY
1579 ,D.PLAN_QTY) - D.ACTUAL_QTY)
1580 ,0) * ( D.ORIGINAL_PRIMARY_QTY / D.ORIGINAL_QTY ) ) - NVL(MTR.PRIMARY_RESERVATION_QUANTITY
1581 ,0) ) > 0
1582 AND NVL(MTR.DEMAND_SOURCE_TYPE_ID
1583 ,5) = 5
1584 AND D.MATERIAL_REQUIREMENT_DATE <= DEMAND_CUTOFF_DATE
1585 AND ( MTR.SUBINVENTORY_CODE IS NULL
1586 OR EXISTS (
1587 SELECT
1588 1
1589 FROM
1590 MTL_SECONDARY_INVENTORIES S
1591 WHERE S.ORGANIZATION_ID = ORG_ID
1592 AND S.SECONDARY_INVENTORY_NAME = MTR.SUBINVENTORY_CODE
1593 AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1594 ,1
1595 ,S.AVAILABILITY_TYPE
1596 ,1) ) )
1597 AND ( MTR.LOCATOR_ID IS NULL
1598 OR EXISTS (
1599 SELECT
1600 1
1601 FROM
1602 MTL_ITEM_LOCATIONS MIL
1603 WHERE MIL.ORGANIZATION_ID = ORG_ID
1604 AND MIL.INVENTORY_LOCATION_ID = MTR.LOCATOR_ID
1605 AND MIL.SUBINVENTORY_CODE = NVL(MTR.SUBINVENTORY_CODE
1606 ,MIL.SUBINVENTORY_CODE)
1607 AND MIL.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1608 ,1
1609 ,MIL.AVAILABILITY_TYPE
1610 ,1) ) )
1611 AND ( MTR.LOT_NUMBER IS NULL
1612 OR EXISTS (
1613 SELECT
1614 1
1615 FROM
1616 MTL_LOT_NUMBERS MLN
1617 WHERE MLN.ORGANIZATION_ID = ORG_ID
1618 AND MLN.LOT_NUMBER = MTR.LOT_NUMBER
1619 AND MLN.INVENTORY_ITEM_ID = ITEM_ID
1620 AND MLN.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1621 ,1
1622 ,MLN.AVAILABILITY_TYPE
1623 ,1) ) );
1624 TOTAL := TOTAL + NVL(QTY
1625 ,0);
1626 ELSE
1627 SELECT
1628 SUM(O.REQUIRED_QUANTITY - O.QUANTITY_ISSUED)
1629 INTO QTY
1630 FROM
1631 WIP_DISCRETE_JOBS D,
1632 WIP_REQUIREMENT_OPERATIONS O
1633 WHERE O.WIP_ENTITY_ID = D.WIP_ENTITY_ID
1634 AND O.ORGANIZATION_ID = D.ORGANIZATION_ID
1635 AND D.ORGANIZATION_ID = ORG_ID
1636 AND O.INVENTORY_ITEM_ID = ITEM_ID
1637 AND O.DATE_REQUIRED <= DEMAND_CUTOFF_DATE
1638 AND O.REQUIRED_QUANTITY > 0
1639 AND O.OPERATION_SEQ_NUM > 0
1640 AND D.STATUS_TYPE in ( 1 , 3 , 4 , 6 )
1641 AND O.WIP_SUPPLY_TYPE NOT IN ( 5 , 6 )
1642 AND NVL(O.SUPPLY_SUBINVENTORY
1643 ,1) = DECODE(SUBINV
1644 ,NULL
1645 ,NVL(O.SUPPLY_SUBINVENTORY
1646 ,1)
1647 ,SUBINV)
1648 AND NOT EXISTS (
1649 SELECT
1650 WIP.WIP_ENTITY_ID
1651 FROM
1652 WIP_SO_ALLOCATIONS WIP,
1653 MTL_DEMAND MTL
1654 WHERE WIP_ENTITY_ID = O.WIP_ENTITY_ID
1655 AND WIP.ORGANIZATION_ID = ORG_ID
1656 AND WIP.ORGANIZATION_ID = MTL.ORGANIZATION_ID
1657 AND WIP.DEMAND_SOURCE_HEADER_ID = MTL.DEMAND_SOURCE_HEADER_ID
1658 AND WIP.DEMAND_SOURCE_LINE = MTL.DEMAND_SOURCE_LINE
1659 AND WIP.DEMAND_SOURCE_DELIVERY = MTL.DEMAND_SOURCE_DELIVERY
1660 AND MTL.INVENTORY_ITEM_ID = ITEM_ID );
1661 TOTAL := TOTAL + NVL(QTY
1662 ,0);
1663 END IF;
1664 END IF;
1665 SELECT
1666 SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED
1667 ,0))
1668 INTO QTY
1669 FROM
1670 MTL_TXN_REQUEST_LINES MTRL,
1671 MTL_TRANSACTION_TYPES MTT
1672 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
1673 AND MTRL.ORGANIZATION_ID = ORG_ID
1674 AND MTRL.INVENTORY_ITEM_ID = ITEM_ID
1675 AND MTRL.LINE_STATUS NOT IN ( 5 , 6 )
1676 AND MTT.TRANSACTION_ACTION_ID = 1
1677 AND ( P_LEVEL = 1
1678 OR MTRL.FROM_SUBINVENTORY_CODE = SUBINV )
1679 AND ( MTRL.FROM_SUBINVENTORY_CODE IS NULL
1680 OR P_LEVEL = 2
1681 OR EXISTS (
1682 SELECT
1683 1
1684 FROM
1685 MTL_SECONDARY_INVENTORIES S
1686 WHERE S.ORGANIZATION_ID = ORG_ID
1687 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
1688 AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1689 ,1
1690 ,S.AVAILABILITY_TYPE
1691 ,1) ) )
1692 AND MTRL.DATE_REQUIRED <= DEMAND_CUTOFF_DATE
1693 AND ( MTRL.FROM_LOCATOR_ID IS NULL
1694 OR EXISTS (
1695 SELECT
1696 1
1697 FROM
1698 MTL_ITEM_LOCATIONS MIL
1699 WHERE MIL.ORGANIZATION_ID = ORG_ID
1700 AND MIL.INVENTORY_LOCATION_ID = MTRL.FROM_LOCATOR_ID
1701 AND MIL.SUBINVENTORY_CODE = NVL(MTRL.FROM_SUBINVENTORY_CODE
1702 ,MIL.SUBINVENTORY_CODE)
1703 AND MIL.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1704 ,1
1705 ,MIL.AVAILABILITY_TYPE
1706 ,1) ) )
1707 AND ( MTRL.LOT_NUMBER IS NULL
1708 OR EXISTS (
1709 SELECT
1710 1
1711 FROM
1712 MTL_LOT_NUMBERS MLN
1713 WHERE MLN.ORGANIZATION_ID = ORG_ID
1714 AND MLN.LOT_NUMBER = MTRL.LOT_NUMBER
1715 AND MLN.INVENTORY_ITEM_ID = ITEM_ID
1716 AND MLN.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1717 ,1
1718 ,MLN.AVAILABILITY_TYPE
1719 ,1) ) );
1720 TOTAL := TOTAL + NVL(QTY
1721 ,0);
1722 RETURN (ROUND(TOTAL
1723 ,2));
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 /*SRW.MESSAGE(91
1727 ,'Error while calculating DemandQty')*/NULL;
1728 RETURN (0);
1729 END GET_DEMAND;
1730
1731 FUNCTION GET_SUPPLY(SUPPLY_CUTOFF_DATE IN DATE
1732 ,ORG_ID IN NUMBER
1733 ,CURRENT_ITEM_ID IN NUMBER
1734 ,INCLUDE_PO IN NUMBER
1735 ,INCLUDE_NONNET IN NUMBER
1736 ,INCLUDE_WIP IN NUMBER
1737 ,INCLUDE_IF IN NUMBER
1738 ,SUBINV IN CHAR) RETURN NUMBER IS
1739 QTY NUMBER;
1740 TOTAL NUMBER;
1741 L_VMI_ENABLED VARCHAR2(1);
1742 L_STMT VARCHAR2(4000);
1743 L_VMI_STMT VARCHAR2(2000);
1744 SCD VARCHAR2(20);
1745 ORG_ID1 NUMBER;
1746 C_PROCESS_ENABLED VARCHAR2(5);
1747 BEGIN
1748 TOTAL := 0;
1749 QTY := 0;
1750 ORG_ID1 := ORG_ID;
1751 L_VMI_ENABLED := NVL(FND_PROFILE.VALUE('PO_VMI_ENABLED')
1752 ,'N');
1753 SCD := TO_CHAR(SUPPLY_CUTOFF_DATE
1754 ,'DD-MON-RRRR');
1755 L_STMT := 'SELECT to_char(nvl(sum(to_org_primary_quantity), 0))
1756 INTO :char_qty
1757 FROM mtl_supply sup, mtl_system_items items
1758 WHERE sup.supply_type_code in (''PO'',''REQ'',''ASN'',''SHIPMENT'',''RECEIVING'')
1759 AND sup.destination_type_code =''INVENTORY''
1760 AND sup.to_organization_id =' || TO_CHAR(ORG_ID) || '
1761 AND sup.item_id =' || TO_CHAR(CURRENT_ITEM_ID) || '
1762 AND items.organization_id = sup.to_organization_id' || '
1763 AND items.inventory_item_id = sup.item_id' || '
1764 AND TRUNC(DECODE(NVL(items.postprocessing_lead_time,0),0,MRP_CALENDAR.NEXT_WORK_DAY(items.organization_id,1,
1765 DECODE(sup.supply_type_code,''PO'',sup.need_by_date,
1766 ''REQ'',sup.need_by_date,
1767 ''ASN'',sup.need_by_date,''RECEIVING'',sup.receipt_date,''SHIPMENT'',
1768 sup.receipt_date)),' || ' MRP_CALENDAR.DATE_OFFSET(items.organization_id,1,DECODE(sup.supply_type_code,''PO'',sup.need_by_date,
1769 ''REQ'',sup.need_by_date,''ASN'',sup.need_by_date,''RECEIVING'',sup.receipt_date,''SHIPMENT'',sup.receipt_date),
1770 items.postprocessing_lead_time))) <=
1771 TO_DATE(''' || SCD || ''',''DD-MON-RRRR'')' || ' AND (NVL(sup.FROM_organization_id,-1) <>' || TO_CHAR(ORG_ID) || '
1772 OR (sup.FROM_organization_id =' || TO_CHAR(ORG_ID) || ' AND ' || TO_CHAR(INCLUDE_NONNET) || '= 2' || ' AND EXISTS (SELECT ''x''
1773 FROM mtl_secondary_inventories sub1
1774 WHERE sub1.organization_id = sup.FROM_organization_id
1775 AND sup.FROM_subinventory = sub1.secondary_inventory_name
1776 AND sub1.availability_type <> 1)))' || ' AND NOT EXISTS (select ''y''
1777 from oe_drop_ship_sources odss
1778 where sup.po_header_id is null and sup.req_line_id = odss.requisition_line_id ) ' || ' AND NOT EXISTS (select ''y''
1779 from oe_drop_ship_sources odss
1780 where sup.req_line_id is null and sup.po_line_location_id = odss.line_location_id)';
1781 L_VMI_STMT := ' AND (sup.po_line_location_id is NULL
1782 OR EXISTS (SELECT ''x''
1783 FROM po_line_locations_all lilo
1784 WHERE lilo.line_location_id = sup.po_line_location_id
1785 AND NVL(lilo.vmi_flag,''N'') =''N''
1786 )
1787 )
1788 AND (sup.req_line_id IS NULL
1789 OR EXISTS (SELECT ''x''
1790 FROM po_requisition_lines_all prl
1791 WHERE prl.requisition_line_id = sup.req_line_id
1792 AND NVL(prl.vmi_flag,''N'') =''N''
1793 )
1794 )';
1795 IF (INCLUDE_PO = 1) THEN
1796 IF L_VMI_ENABLED = 'Y' THEN
1797 L_STMT := L_STMT || L_VMI_STMT;
1798 END IF;
1799 EXECUTE IMMEDIATE
1800 L_STMT;
1801 QTY := TO_NUMBER(CHAR_QTY);
1802 TOTAL := TOTAL + NVL(QTY
1803 ,0);
1804 END IF;
1805 IF (INCLUDE_WIP = 1) THEN
1806 SELECT
1807 NVL(PROCESS_ENABLED_FLAG
1808 ,'N')
1809 INTO C_PROCESS_ENABLED
1810 FROM
1811 MTL_PARAMETERS
1812 WHERE ORGANIZATION_ID = ORG_ID;
1813 IF C_PROCESS_ENABLED = 'Y' THEN
1814 SELECT
1815 SUM(NVL((NVL(D.WIP_PLAN_QTY
1816 ,D.PLAN_QTY) - D.ACTUAL_QTY)
1817 ,0) * (ORIGINAL_PRIMARY_QTY / ORIGINAL_QTY))
1818 INTO QTY
1819 FROM
1820 GME_MATERIAL_DETAILS D,
1821 GME_BATCH_HEADER H
1822 WHERE H.BATCH_TYPE IN ( 0 , 10 )
1823 AND H.BATCH_STATUS IN ( 1 , 2 )
1824 AND H.BATCH_ID = D.BATCH_ID
1825 AND D.INVENTORY_ITEM_ID = CURRENT_ITEM_ID
1826 AND D.ORGANIZATION_ID = ORG_ID
1827 AND D.MATERIAL_REQUIREMENT_DATE <= SUPPLY_CUTOFF_DATE
1828 AND D.LINE_TYPE > 0;
1829 TOTAL := TOTAL + NVL(QTY
1830 ,0);
1831 ELSE
1832 SELECT
1833 SUM(NVL(START_QUANTITY
1834 ,0) - NVL(QUANTITY_COMPLETED
1835 ,0) - NVL(QUANTITY_SCRAPPED
1836 ,0))
1837 INTO QTY
1838 FROM
1839 WIP_DISCRETE_JOBS
1840 WHERE ORGANIZATION_ID = ORG_ID
1841 AND PRIMARY_ITEM_ID = CURRENT_ITEM_ID
1842 AND STATUS_TYPE in ( 1 , 3 , 4 , 6 )
1843 AND JOB_TYPE in ( 1 , 3 )
1844 AND SCHEDULED_COMPLETION_DATE <= TO_DATE(TO_CHAR(SUPPLY_CUTOFF_DATE)
1845 ,'DD-MON-RR')
1846 AND NVL(COMPLETION_SUBINVENTORY
1847 ,1) = DECODE(SUBINV
1848 ,NULL
1849 ,NVL(COMPLETION_SUBINVENTORY
1850 ,1)
1851 ,SUBINV);
1852 TOTAL := TOTAL + NVL(QTY
1853 ,0);
1854 SELECT
1855 SUM(DAILY_PRODUCTION_RATE * LEAST(0
1856 ,GREATEST(PROCESSING_WORK_DAYS
1857 ,SUPPLY_CUTOFF_DATE - FIRST_UNIT_COMPLETION_DATE)) - QUANTITY_COMPLETED)
1858 INTO QTY
1859 FROM
1860 WIP_REPETITIVE_SCHEDULES WRS,
1861 WIP_REPETITIVE_ITEMS WRI
1862 WHERE WRS.ORGANIZATION_ID = ORG_ID
1863 AND WRS.STATUS_TYPE IN ( 1 , 3 , 4 , 6 )
1864 AND WRI.ORGANIZATION_ID = ORG_ID
1865 AND WRI.PRIMARY_ITEM_ID = CURRENT_ITEM_ID
1866 AND WRI.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
1867 AND WRI.LINE_ID = WRS.LINE_ID
1868 AND NVL(WRI.COMPLETION_SUBINVENTORY
1869 ,1) = DECODE(SUBINV
1870 ,NULL
1871 ,NVL(WRI.COMPLETION_SUBINVENTORY
1872 ,1)
1873 ,SUBINV);
1874 TOTAL := TOTAL + NVL(QTY
1875 ,0);
1876 END IF;
1877 END IF;
1878 IF (INCLUDE_IF = 1) THEN
1879 SELECT
1880 SUM(QUANTITY)
1881 INTO QTY
1882 FROM
1883 PO_REQUISITIONS_INTERFACE_ALL
1884 WHERE ITEM_ID = CURRENT_ITEM_ID
1885 AND DESTINATION_ORGANIZATION_ID = ORG_ID1
1886 AND INCLUDE_PO = 1
1887 AND ( PROCESS_FLAG <> 'ERROR'
1888 OR PROCESS_FLAG IS NULL )
1889 AND NEED_BY_DATE <= SUPPLY_CUTOFF_DATE
1890 AND ( NVL(DESTINATION_SUBINVENTORY
1891 ,1) = DECODE(SUBINV
1892 ,NULL
1893 ,NVL(DESTINATION_SUBINVENTORY
1894 ,1)
1895 ,SUBINV)
1896 OR EXISTS (
1897 SELECT
1898 1
1899 FROM
1900 MTL_SECONDARY_INVENTORIES SUB2
1901 WHERE SECONDARY_INVENTORY_NAME = DESTINATION_SUBINVENTORY
1902 AND DESTINATION_SUBINVENTORY = NVL(SUBINV
1903 ,DESTINATION_SUBINVENTORY)
1904 AND SUB2.ORGANIZATION_ID = ORG_ID1
1905 AND SUB2.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
1906 ,1
1907 ,SUB2.AVAILABILITY_TYPE
1908 ,1) ) );
1909 TOTAL := ROUND(TOTAL + NVL(QTY
1910 ,0)
1911 ,2);
1912 IF C_PROCESS_ENABLED = 'N' THEN
1913 SELECT
1914 SUM(START_QUANTITY)
1915 INTO QTY
1916 FROM
1917 WIP_JOB_SCHEDULE_INTERFACE
1918 WHERE PRIMARY_ITEM_ID = CURRENT_ITEM_ID
1919 AND ORGANIZATION_ID = ORG_ID
1920 AND INCLUDE_WIP = 1
1921 AND PROCESS_STATUS <> 3
1922 AND LAST_UNIT_COMPLETION_DATE <= SUPPLY_CUTOFF_DATE;
1923 TOTAL := ROUND(TOTAL + NVL(QTY
1924 ,0)
1925 ,2);
1926 END IF;
1927 END IF;
1928 RETURN (TOTAL);
1929 END GET_SUPPLY;
1930
1931 FUNCTION GET_ONHAND_QTY(ITEM_ID IN NUMBER
1932 ,LOT_CONTROL IN NUMBER
1933 ,ORG_ID IN NUMBER
1934 ,SUBINV IN CHAR
1935 ,INCLUDE_NONNET IN NUMBER) RETURN NUMBER IS
1936 L_IS_LOT_CONTROL VARCHAR2(20) := 'TRUE';
1937 X_RETURN_STATUS VARCHAR2(30);
1938 X_MSG_COUNT NUMBER;
1939 X_MSG_DATA VARCHAR2(1000);
1940 L_ONHAND_SOURCE NUMBER := 3;
1941 L_SUBINVENTORY_CODE VARCHAR2(30);
1942 L_SYSDATE DATE;
1943 L_CURSOR_STMT VARCHAR2(1000);
1944 X_QOH NUMBER;
1945 X_RQOH NUMBER;
1946 X_QR NUMBER;
1947 X_QS NUMBER;
1948 X_ATT NUMBER;
1949 X_ATR NUMBER;
1950 X_VOH NUMBER;
1951 X_VATT NUMBER;
1952 L_QOH NUMBER;
1953 BEGIN
1954 SELECT
1955 sysdate
1956 INTO L_SYSDATE
1957 FROM
1958 SYS.DUAL;
1959 IF (INCLUDE_NONNET = 1) THEN
1960 L_ONHAND_SOURCE := NULL;
1961 ELSE
1962 L_ONHAND_SOURCE := 2;
1963 END IF;
1964 MRP_GET_ONHAND.GET_OH_QTY(ITEM_ID => ITEM_ID
1965 ,ORG_ID => ORG_ID
1966 ,INCLUDE_NONNET => INCLUDE_NONNET
1967 ,X_QOH => X_QOH
1968 ,X_RETURN_STATUS => X_RETURN_STATUS
1969 ,X_MSG_DATA => X_MSG_DATA);
1970 IF X_RETURN_STATUS = 'S' THEN
1971 RETURN (X_QOH);
1972 ELSE
1973 /*SRW.MESSAGE(92
1974 ,'Error while calculating OnHandQty')*/NULL;
1975 RETURN (0);
1976 END IF;
1977 END GET_ONHAND_QTY;
1978
1979 END MRP_MRPRPROP_XMLP_PKG;
1980
1981