1 PACKAGE BODY MTL_SAFETY_STOCKS_PKG as
2 /* $Header: INVDDFSB.pls 120.6 2008/02/18 09:12:05 athammin noship $ */
3
4
5 /****************************************************************
6 * Enhancement Bug #2231655 Added Function. *
7 * The function check_project_task checks if there exists an *
8 * entry for project and task for the combination of item *
9 * Org and effectivity date >=start date. If exists then it *
10 * returns TRUE else returns FALSE. *
11 ****************************************************************/
12
13 FUNCTION check_project_task(p_inventory_item_id IN NUMBER
14 ,p_organization_id IN NUMBER
15 ,p_effect_date IN DATE
16 ) RETURN BOOLEAN IS
17 l_num NUMBER;
18 l_return_sts BOOLEAN := FALSE;
19 BEGIN
20 SELECT 1
21 INTO l_num
22 FROM dual
23 WHERE EXISTS (SELECT 1
24 FROM mtl_safety_stocks
25 WHERE organization_id = p_organization_id
26 AND inventory_item_id = p_inventory_item_id
27 AND effectivity_date >= p_effect_date
28 AND (project_id IS NOT NULL OR task_id IS NOT NULL)
29 );
30 l_return_sts := TRUE;
31 RETURN l_return_sts;
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 l_return_sts := FALSE;
35 RETURN l_return_sts;
36 END check_project_task;
37
38 /****************************************************************
39 * Enhancement Bug#2231655 Added local Procedure println *
40 * Procedure println writes a debug message in the log file. *
41 ****************************************************************/
42
43 PROCEDURE println(msg IN VARCHAR2,
44 g_debug_level IN NUMBER default NULL
45 ) IS
46 BEGIN
47 IF(g_debug_level IS NULL) THEN
48 fnd_file.put_line(FND_FILE.LOG,substr(msg,1,255));
49 ELSIF(g_debug_level in (2,3)) THEN
50 fnd_file.put_line(FND_FILE.LOG,substr(msg,1,255));
51 END IF;
52 END println;
53
54
55 procedure SafetyStock(X_ORGANIZATION_ID NUMBER,
56 X_SELECTION NUMBER,
57 X_INVENTORY_ITEM_ID NUMBER,
58 X_SAFETY_STOCK_CODE NUMBER,
59 X_FORECAST_NAME VARCHAR2,
60 X_CATEGORY_SET_ID NUMBER,
61 X_CATEGORY_ID NUMBER,
62 X_PERCENT NUMBER,
63 X_SERVICE_LEVEL NUMBER,
64 X_START_DATE DATE,
65 login_id NUMBER,
66 user_id NUMBER) IS
67
68 cursor ITEM1_cur IS
69 --Bug#2713829, also selected MRP_SAFETY_STOCK_PERCENT from MSI.
70 SELECT DISTINCT F.INVENTORY_ITEM_ID, MSI.MRP_SAFETY_STOCK_PERCENT
71 FROM MRP_FORECAST_DESIGNATORS D, MRP_FORECAST_DATES F, MTL_SYSTEM_ITEMS MSI
72 WHERE D.ORGANIZATION_ID = X_ORGANIZATION_ID AND
73 D.FORECAST_DESIGNATOR = X_FORECAST_NAME AND
74 D.ORGANIZATION_ID = F.ORGANIZATION_ID AND
75 D.FORECAST_DESIGNATOR = F.FORECAST_DESIGNATOR AND
76 MSI.ORGANIZATION_ID = F.ORGANIZATION_ID AND
77 MSI.INVENTORY_ITEM_ID = F.INVENTORY_ITEM_ID;
78
79 cursor ITEM2_cur IS
80 SELECT DISTINCT F.INVENTORY_ITEM_ID
81 FROM MRP_FORECAST_DESIGNATORS D, MRP_FORECAST_DATES F,
82 MTL_ITEM_CATEGORIES C
83 WHERE D.ORGANIZATION_ID = X_ORGANIZATION_ID AND
84 D.FORECAST_DESIGNATOR = X_FORECAST_NAME AND
85 D.ORGANIZATION_ID = F.ORGANIZATION_ID AND
86 D.FORECAST_DESIGNATOR = F.FORECAST_DESIGNATOR AND
87 C.CATEGORY_SET_ID = X_CATEGORY_SET_ID AND
88 C.CATEGORY_ID = X_CATEGORY_ID AND
89 C.ORGANIZATION_ID = X_ORGANIZATION_ID AND
90 C.INVENTORY_ITEM_ID = F.INVENTORY_ITEM_ID;
91
92 cursor FORECAST_cur IS
93 SELECT 'X' FROM MRP_FORECAST_DESIGNATORS
94 WHERE FORECAST_DESIGNATOR = X_FORECAST_NAME AND
95 FORECAST_DESIGNATOR
96 IN (SELECT d.FORECAST_DESIGNATOR
97 FROM MRP_FORECAST_DESIGNATORS d
98 WHERE d.ORGANIZATION_ID = X_ORGANIZATION_ID AND
99 EXISTS (SELECT 'X' FROM MRP_FORECAST_DATES f
100 WHERE f.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID AND
101 d.FORECAST_DESIGNATOR = f.FORECAST_DESIGNATOR AND
102 ORGANIZATION_ID = X_ORGANIZATION_ID));
103
104 ss_prcnt NUMBER;
105 srv_lvl NUMBER;
106 srv_factor NUMBER;
107 except_id NUMBER;
108 cal_code VARCHAR2(10);
109 forecast VARCHAR2(10);
110 item_tmp NUMBER;
111 ss_prcnt_temp NUMBER; --Bug#2713829.
112 l_return_sts BOOLEAN;
113 l_prcnt_level NUMBER := 1; --Bug#2713829. 1 = get % from Concurrent Program
114
115 BEGIN
116 IF X_SAFETY_STOCK_CODE = 3 THEN
117 ss_prcnt := NULL;
118 srv_lvl := X_SERVICE_LEVEL;
119 END IF;
120
121 IF X_SAFETY_STOCK_CODE = 2 THEN
122 ss_prcnt := X_PERCENT;
123 srv_lvl := NULL;
124 END IF;
125
126 IF ss_prcnt = 0 THEN
127 l_prcnt_level := 2; --2 = get % from item attributes
128 END IF;
129
130 Init(X_ORGANIZATION_ID, srv_lvl , srv_factor, cal_code, except_id);
131
132 -- Bug 5041094 Deleting the safety stock entries existing for forecast specified
133 -- when those items no longer exist on forecast but are selected by
134 -- item filter , but skipping items for which project/task level entries exist
135 IF (X_SELECTION = 1) THEN
136 DELETE FROM MTL_SAFETY_STOCKS M
137 WHERE ORGANIZATION_ID = X_ORGANIZATION_ID
138 AND FORECAST_DESIGNATOR = X_FORECAST_NAME
139 AND INVENTORY_ITEM_ID NOT IN ( SELECT DISTINCT F.INVENTORY_ITEM_ID
140 FROM MRP_FORECAST_DESIGNATORS D, MRP_FORECAST_DATES F
141 WHERE D.ORGANIZATION_ID = X_ORGANIZATION_ID
142 AND D.FORECAST_DESIGNATOR = X_FORECAST_NAME
143 AND D.ORGANIZATION_ID = F.ORGANIZATION_ID
144 AND D.FORECAST_DESIGNATOR = F.FORECAST_DESIGNATOR )
145 AND NOT EXISTS ( SELECT 1 FROM MTL_SAFETY_STOCKS
146 WHERE ORGANIZATION_ID = M.ORGANIZATION_ID
147 AND INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
148 AND EFFECTIVITY_DATE >= X_START_DATE
149 AND (PROJECT_ID IS NOT NULL OR TASK_ID IS NOT NULL))
150 AND EFFECTIVITY_DATE >= X_START_DATE ;
151 ELSIF ( X_SELECTION = 2 ) THEN
152 DELETE FROM MTL_SAFETY_STOCKS M
153 WHERE ORGANIZATION_ID = X_ORGANIZATION_ID
154 AND FORECAST_DESIGNATOR = X_FORECAST_NAME
155 AND INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID
156 AND NOT EXISTS ( SELECT 1 FROM MRP_FORECAST_DESIGNATORS D, MRP_FORECAST_DATES F
157 WHERE D.ORGANIZATION_ID = X_ORGANIZATION_ID
158 AND D.FORECAST_DESIGNATOR = X_FORECAST_NAME
159 AND D.ORGANIZATION_ID = F.ORGANIZATION_ID
160 AND D.FORECAST_DESIGNATOR = F.FORECAST_DESIGNATOR
161 AND F.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID )
162 AND NOT EXISTS ( SELECT 1 FROM MTL_SAFETY_STOCKS
163 WHERE ORGANIZATION_ID = M.ORGANIZATION_ID
164 AND INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
165 AND EFFECTIVITY_DATE >= X_START_DATE
166 AND (PROJECT_ID IS NOT NULL OR TASK_ID IS NOT NULL))
167 AND EFFECTIVITY_DATE >= X_START_DATE ;
168 ELSE
169 DELETE FROM MTL_SAFETY_STOCKS M
170 WHERE ORGANIZATION_ID = X_ORGANIZATION_ID
171 AND FORECAST_DESIGNATOR = X_FORECAST_NAME
172 AND EXISTS ( SELECT 1 FROM MTL_ITEM_CATEGORIES C
173 WHERE C.ORGANIZATION_ID = M.ORGANIZATION_ID
174 AND C.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
175 AND C.CATEGORY_SET_ID = X_CATEGORY_SET_ID
176 AND C.CATEGORY_ID = X_CATEGORY_ID
177 AND C.ORGANIZATION_ID = X_ORGANIZATION_ID )
178 AND INVENTORY_ITEM_ID NOT IN ( SELECT DISTINCT F.INVENTORY_ITEM_ID
179 FROM MRP_FORECAST_DESIGNATORS D, MRP_FORECAST_DATES F,
180 MTL_ITEM_CATEGORIES C
181 WHERE D.ORGANIZATION_ID = X_ORGANIZATION_ID
182 AND D.FORECAST_DESIGNATOR = X_FORECAST_NAME
183 AND D.ORGANIZATION_ID = F.ORGANIZATION_ID
184 AND D.FORECAST_DESIGNATOR = F.FORECAST_DESIGNATOR
185 AND C.CATEGORY_SET_ID = X_CATEGORY_SET_ID
186 AND C.CATEGORY_ID = X_CATEGORY_ID
187 AND C.ORGANIZATION_ID = X_ORGANIZATION_ID
188 AND C.INVENTORY_ITEM_ID = F.INVENTORY_ITEM_ID )
189 AND NOT EXISTS ( SELECT 1 FROM MTL_SAFETY_STOCKS
190 WHERE ORGANIZATION_ID = M.ORGANIZATION_ID
191 AND INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
192 AND EFFECTIVITY_DATE >= X_START_DATE
193 AND (PROJECT_ID IS NOT NULL OR TASK_ID IS NOT NULL))
194 AND EFFECTIVITY_DATE >= X_START_DATE ;
195 END IF ;
196 COMMIT;
197 -- Bug 5041094 Ends
198
199 IF X_SELECTION = 1 THEN
200 OPEN ITEM1_cur;
201 FETCH ITEM1_cur INTO item_tmp,ss_prcnt_temp;
202 IF ITEM1_cur%ROWCOUNT = 0 THEN
203 CLOSE ITEM1_cur;
204 RAISE NO_DATA_FOUND;
205 END IF;
206 CLOSE ITEM1_cur;
207 FOR item IN ITEM1_cur LOOP
208
209 /******************************************************************/
210 /* Enhancement Bug #2231655 . Changed the logic to check for */
211 /* project and task entry for the combination of item,Organization*/
212 /* and effectivity date. If an entry exists for a project and task*/
213 /* then the processing of that item is skipped and a log message */
214 /* is written. */
215 /******************************************************************/
216
217 l_return_sts := check_project_task(p_inventory_item_id => item.inventory_item_id
218 ,p_organization_id => x_organization_id
219 ,p_effect_date => x_start_date
220 );
221 IF l_return_sts THEN
222 println('Item skipped as Entry for project found');
223 println('X_SELECTION = 1 ');
224 println('item id ='||item.inventory_item_id);
225 println('Org id = '||x_organization_id);
226 println('effectivity date ='||x_start_date);
227 ELSE
228 --Bug#2713829.If ss_prcnt is not defined at the concurrent program, then obtain it
229 --from mtl_system_items.
230 IF l_prcnt_level = 2 THEN
231 ss_prcnt := nvl(item.MRP_SAFETY_STOCK_PERCENT,0);
232 END IF;
233 Main(X_ORGANIZATION_ID, item.INVENTORY_ITEM_ID,
234 X_SAFETY_STOCK_CODE, X_FORECAST_NAME,
235 ss_prcnt, srv_lvl, X_START_DATE,
236 srv_factor, cal_code, except_id, login_id, user_id);
237 END IF;
238 /* End Bug# */
239 END LOOP;
240 ELSIF X_SELECTION = 2 THEN
241 /******************************************************************/
242 /* Enhancement Bug #2231655 . Changed the logic to check for */
243 /* project and task entry for the combination of item,Organization*/
244 /* and effectivity date. If an entry exists for a project and task*/
245 /* then the processing of that item is skipped and a log message */
246 /* is written. */
247 /******************************************************************/
248
249 l_return_sts := check_project_task(p_inventory_item_id => x_inventory_item_id
250 ,p_organization_id => x_organization_id
251 ,p_effect_date => x_start_date
252 );
253 IF l_return_sts THEN
254 println('Item skipped as Entry for project found');
255 println('X_SELECTION = 2 ');
256 println('item id ='||x_inventory_item_id);
257 println('Org id = '||x_organization_id);
258 println('effectivity date ='||x_start_date);
259 ELSE
260 OPEN FORECAST_cur;
261 FETCH FORECAST_cur INTO forecast;
262 IF FORECAST_cur%NOTFOUND THEN
263 CLOSE FORECAST_cur;
264 RAISE NO_DATA_FOUND;
265 END IF;
266 CLOSE FORECAST_cur;
267 Main(X_ORGANIZATION_ID, X_INVENTORY_ITEM_ID,
268 X_SAFETY_STOCK_CODE, X_FORECAST_NAME,
269 ss_prcnt, srv_lvl, X_START_DATE,
270 srv_factor, cal_code, except_id, login_id, user_id);
271 END IF;
272 /* End Bug# */
273 ELSE
274 OPEN ITEM2_cur;
275 FETCH ITEM2_cur INTO item_tmp;
276 IF ITEM2_cur%ROWCOUNT = 0 THEN
277 CLOSE ITEM2_cur;
278 RAISE NO_DATA_FOUND;
279 END IF;
280 CLOSE ITEM2_cur;
281 FOR item IN ITEM2_cur LOOP
282 /******************************************************************/
283 /* Enhancement Bug #2231655 . Changed the logic to check for */
284 /* project and task entry for the combination of item,Organization*/
285 /* and effectivity date. If an entry exists for a project and task*/
286 /* then the processing of that item is skipped and a log message */
287 /* is written. */
288 /******************************************************************/
289
290 l_return_sts := check_project_task(p_inventory_item_id => item.inventory_item_id
291 ,p_organization_id => x_organization_id
292 ,p_effect_date => x_start_date
293 );
294 IF l_return_sts THEN
295 println('Item skipped as Entry for project found');
296 println('X_SELECTION <> 1 or 2 ');
297 println('item id ='||item.inventory_item_id);
298 println('Org id = '||x_organization_id);
299 println('effectivity date ='||x_start_date);
300 ELSE
301 Main(X_ORGANIZATION_ID, item.INVENTORY_ITEM_ID,
302 X_SAFETY_STOCK_CODE, X_FORECAST_NAME,
303 ss_prcnt, srv_lvl, X_START_DATE,
304 srv_factor, cal_code, except_id, login_id, user_id);
305 END IF;
306 /* End Bug# */
307 END LOOP;
308 END IF;
309 commit;
310
311 END SafetyStock;
312
313 procedure Init(org_id IN NUMBER,
314 srv_level IN NUMBER,
315 srv_factor OUT NOCOPY NUMBER,
316 cal_code OUT NOCOPY VARCHAR2,
317 except_id OUT NOCOPY NUMBER) IS
318
319 BEGIN
320 srv_factor := CalSF(srv_level)* 1.25;
321
322 /********************************************************/
326 /********************************************************/
323 /* Select calendar Code and Exception Set Id for future */
324 /* use so that the SQL statements do not have to join */
325 /* with MTL_PARAMETERS table. */
327
328 SELECT CALENDAR_CODE, CALENDAR_EXCEPTION_SET_ID
329 INTO cal_code, except_id
330 FROM MTL_PARAMETERS
331 WHERE ORGANIZATION_ID = org_id;
332
333 END Init;
334
335
336 procedure Main(org_id NUMBER,
337 item_id NUMBER,
338 ss_code NUMBER,
339 forc_name VARCHAR2,
340 ss_percent NUMBER,
341 srv_level NUMBER,
342 effect_date DATE,
343 srv_factor NUMBER,
344 cal_code VARCHAR2,
345 except_id NUMBER,
346 login_id NUMBER,
347 user_id NUMBER) IS
348
349 /********************************************************/
350 /* Select the the forecast quantity for each date. */
351 /********************************************************/
352
353 /* Bug# 5855934
354 * Modified the below cursor to prevent checks on Forecast Set as
355 * this value will never be passed through the 'forc_name' parameter.
356 * Removed the Nvl function as FORECAST_SET will never be 'NULL'.
357 * Removed the Decode function as FORECAST_DESIGNATOR will always refer
358 * to the Forecast Name and not the Forecast Set.
359 */
360 CURSOR SelForecast_mad IS -- 6797274 changes
361 SELECT 1,
362 TO_NUMBER(TO_CHAR(C1.CALENDAR_DATE,'J')),
363 ROUND(SUM(NVL(F.FORECAST_MAD, 0)) * srv_factor, 5) -- 6797274 changes
364 FROM
365 BOM_CALENDAR_DATES C1,
366 MRP_FORECAST_DESIGNATORS D1,
367 MRP_FORECAST_DATES F
368 WHERE D1.ORGANIZATION_ID = org_id
369 AND D1.FORECAST_DESIGNATOR = forc_name
370 AND F.ORGANIZATION_ID = org_id
371 AND F.INVENTORY_ITEM_ID = item_id
372 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
373 AND F.BUCKET_TYPE = 1
374 AND NVL(F.ORIGINATION_TYPE, -1) = 5 -- 6797274 changes
375 AND C1.CALENDAR_CODE = cal_code
376 AND C1.EXCEPTION_SET_ID = except_id
377 AND (C1.CALENDAR_DATE >= F.FORECAST_DATE
378 AND C1.CALENDAR_DATE >= effect_date
379 AND C1.CALENDAR_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
380 GROUP BY C1.CALENDAR_DATE
381 UNION
382 SELECT 2,
383 TO_NUMBER(TO_CHAR(C3.CALENDAR_DATE,'J')),
384 ROUND(SUM(NVL(F.FORECAST_MAD, 0)/(C2.NEXT_SEQ_NUM - -- 6797274 changes
385 C3.NEXT_SEQ_NUM)) * srv_factor, 5)
386 FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
387 BOM_CALENDAR_DATES C3,
388 BOM_CAL_WEEK_START_DATES W1, MRP_FORECAST_DATES F,
389 MRP_FORECAST_DESIGNATORS D1
390 WHERE D1.ORGANIZATION_ID = org_id
391 AND D1.FORECAST_DESIGNATOR = forc_name
392 AND F.ORGANIZATION_ID = org_id
393 AND F.INVENTORY_ITEM_ID = item_id
394 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
395 AND F.BUCKET_TYPE = 2
396 AND NVL(F.ORIGINATION_TYPE, -1) = 5 -- 6797274 changes
397 AND W1.CALENDAR_CODE = cal_code
398 AND W1.EXCEPTION_SET_ID = except_id
399 AND (W1.WEEK_START_DATE >= F.FORECAST_DATE
400 AND W1.WEEK_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
401 AND W1.NEXT_DATE > effect_date
402 AND C1.CALENDAR_CODE = cal_code
403 AND C2.CALENDAR_CODE = cal_code
404 AND C3.CALENDAR_CODE = cal_code
405 AND C1.EXCEPTION_SET_ID = except_id
406 AND C2.EXCEPTION_SET_ID = except_id
407 AND C3.EXCEPTION_SET_ID = except_id
408 AND C3.CALENDAR_DATE= W1.WEEK_START_DATE
409 AND C2.CALENDAR_DATE = W1.NEXT_DATE
410 AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
411 AND C1.CALENDAR_DATE >= effect_date
412 AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
413 GROUP BY C3.CALENDAR_DATE
414 UNION
415 SELECT 3,
416 TO_NUMBER(TO_CHAR(C3.CALENDAR_DATE,'J')),
417 ROUND(SUM(NVL(F.FORECAST_MAD, 0)/(C2.NEXT_SEQ_NUM - -- 6797274 changes
418 C3.NEXT_SEQ_NUM)) * srv_factor, 5)
419 FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
420 BOM_CALENDAR_DATES C3,
421 BOM_PERIOD_START_DATES W1, MRP_FORECAST_DATES F,
422 MRP_FORECAST_DESIGNATORS D1
423 WHERE D1.ORGANIZATION_ID = org_id
424 AND D1.FORECAST_DESIGNATOR = forc_name
425 AND F.ORGANIZATION_ID = org_id
426 AND F.INVENTORY_ITEM_ID = item_id
427 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
428 AND F.BUCKET_TYPE = 3
429 AND NVL(F.ORIGINATION_TYPE, -1) = 5 -- 6797274 changes
430 AND W1.CALENDAR_CODE = cal_code
431 AND W1.EXCEPTION_SET_ID = except_id
432 AND (W1.PERIOD_START_DATE >= F.FORECAST_DATE
433 AND W1.PERIOD_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
434 AND W1.NEXT_DATE > effect_date
435 AND C1.CALENDAR_CODE = cal_code
436 AND C2.CALENDAR_CODE = cal_code
440 AND C3.EXCEPTION_SET_ID = except_id
437 AND C3.CALENDAR_CODE = cal_code
438 AND C1.EXCEPTION_SET_ID = except_id
439 AND C2.EXCEPTION_SET_ID = except_id
441 AND C3.CALENDAR_DATE= W1.PERIOD_START_DATE
442 AND C2.CALENDAR_DATE = W1.NEXT_DATE
443 AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
444 AND C1.CALENDAR_DATE >= effect_date
445 AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
446 GROUP BY C3.CALENDAR_DATE
447 ORDER BY 2;
448
449 CURSOR SelForecast_userper IS -- 6797274 Changes Start
450 SELECT 1,
451 TO_NUMBER(TO_CHAR(C1.CALENDAR_DATE,'J')),
452 ROUND(SUM(F.ORIGINAL_FORECAST_QUANTITY)* ss_percent/100 , 5)
453 FROM
454 BOM_CALENDAR_DATES C1,
455 MRP_FORECAST_DESIGNATORS D1,
456 MRP_FORECAST_DATES F
457 WHERE D1.ORGANIZATION_ID = org_id
458 AND D1.FORECAST_DESIGNATOR = forc_name
459 AND F.ORGANIZATION_ID = org_id
460 AND F.INVENTORY_ITEM_ID = item_id
461 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
462 AND F.BUCKET_TYPE = 1
463 AND C1.CALENDAR_CODE = cal_code
464 AND C1.EXCEPTION_SET_ID = except_id
465 AND (C1.CALENDAR_DATE >= F.FORECAST_DATE
466 AND C1.CALENDAR_DATE >= effect_date
467 AND C1.CALENDAR_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
468 GROUP BY C1.CALENDAR_DATE
469 UNION
470 SELECT 2,
471 TO_NUMBER(TO_CHAR(C1.CALENDAR_DATE,'J')),
472 ROUND(SUM(ORIGINAL_FORECAST_QUANTITY/
473 (C2.NEXT_SEQ_NUM - C3.NEXT_SEQ_NUM))* ss_percent/100 ,5)
474 FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
475 BOM_CALENDAR_DATES C3,
476 BOM_CAL_WEEK_START_DATES W1, MRP_FORECAST_DATES F,
477 MRP_FORECAST_DESIGNATORS D1
478 WHERE D1.ORGANIZATION_ID = org_id
479 AND D1.FORECAST_DESIGNATOR = forc_name
480 AND F.ORGANIZATION_ID = org_id
481 AND F.INVENTORY_ITEM_ID = item_id
482 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
483 AND F.BUCKET_TYPE = 2
484 AND W1.CALENDAR_CODE = cal_code
485 AND W1.EXCEPTION_SET_ID = except_id
486 AND (W1.WEEK_START_DATE >= F.FORECAST_DATE
487 AND W1.WEEK_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
488 AND W1.NEXT_DATE > effect_date
489 AND C1.CALENDAR_CODE = cal_code
490 AND C2.CALENDAR_CODE = cal_code
491 AND C3.CALENDAR_CODE = cal_code
492 AND C1.EXCEPTION_SET_ID = except_id
493 AND C2.EXCEPTION_SET_ID = except_id
494 AND C3.EXCEPTION_SET_ID = except_id
495 AND C3.CALENDAR_DATE= W1.WEEK_START_DATE
496 AND C2.CALENDAR_DATE = W1.NEXT_DATE
497 AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
498 AND C1.CALENDAR_DATE >= effect_date
499 AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
500 GROUP BY C1.CALENDAR_DATE
501 UNION
502 SELECT 3,
503 TO_NUMBER(TO_CHAR(C1.CALENDAR_DATE,'J')),
504 ROUND(SUM(ORIGINAL_FORECAST_QUANTITY/
505 (C2.NEXT_SEQ_NUM - C3.NEXT_SEQ_NUM))* ss_percent/100 ,5)
506 FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
507 BOM_CALENDAR_DATES C3,
508 BOM_PERIOD_START_DATES W1, MRP_FORECAST_DATES F,
509 MRP_FORECAST_DESIGNATORS D1
510 WHERE D1.ORGANIZATION_ID = org_id
511 AND D1.FORECAST_DESIGNATOR = forc_name
512 AND F.ORGANIZATION_ID = org_id
513 AND F.INVENTORY_ITEM_ID = item_id
514 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
515 AND F.BUCKET_TYPE = 3
516 AND W1.CALENDAR_CODE = cal_code
517 AND W1.EXCEPTION_SET_ID = except_id
518 AND (W1.PERIOD_START_DATE >= F.FORECAST_DATE
519 AND W1.PERIOD_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
520 AND W1.NEXT_DATE > effect_date
521 AND C1.CALENDAR_CODE = cal_code
522 AND C2.CALENDAR_CODE = cal_code
523 AND C3.CALENDAR_CODE = cal_code
524 AND C1.EXCEPTION_SET_ID = except_id
525 AND C2.EXCEPTION_SET_ID = except_id
526 AND C3.EXCEPTION_SET_ID = except_id
527 AND C3.CALENDAR_DATE= W1.PERIOD_START_DATE
528 AND C2.CALENDAR_DATE = W1.NEXT_DATE
529 AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
530 AND C1.CALENDAR_DATE >= effect_date
531 AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
532 GROUP BY C1.CALENDAR_DATE
533 ORDER BY 2; -- 6797274 Changes End
534
535 --Added as a part of bug # 5718937
536 CURSOR c_bucket_type (cp_forecast IN VARCHAR2) IS
537 SELECT BUCKET_TYPE
538 FROM MRP_FORECAST_DESIGNATORS
539 WHERE FORECAST_DESIGNATOR = cp_forecast;
540
541 CURSOR QTY_cur IS
542 SELECT SAFETY_STOCK_QUANTITY
543 FROM MTL_SAFETY_STOCKS
544 WHERE ORGANIZATION_ID = org_id
545 AND INVENTORY_ITEM_ID = item_id
546 AND EFFECTIVITY_DATE = (
547 SELECT MAX(EFFECTIVITY_DATE)
548 FROM MTL_SAFETY_STOCKS
549 WHERE ORGANIZATION_ID = org_id
550 AND INVENTORY_ITEM_ID = item_id
551 AND EFFECTIVITY_DATE < effect_date);
552
556 j_effect_date NUMBER;
553 pro_fdate NUMBER;
554 pro_sdate NUMBER;
555 pro_fqty NUMBER;
557 forc_type NUMBER;
558 ss_date NUMBER;
559 forc_date NUMBER;
560 ss_qty NUMBER;
561 forc_qty NUMBER;
562 pro_sqty NUMBER;
563 bucket_type MRP_FORECAST_DESIGNATORS.BUCKET_TYPE%TYPE; -- Bug # 5718937
564 l_mad_calc BOOLEAN; -- Bug # 5718937
565
566 next_date DATE;
567
568 BEGIN
569
570 /********************************************************/
571 /* Select calendar Code and Exception Set Id for future */
572 /* use so that the SQL statements do not have to join */
573 /* with MTL_PARAMETERS table. */
574 /********************************************************/
575
576 SELECT TO_NUMBER(TO_CHAR(effect_date, 'J'))
577 INTO j_effect_date
578 FROM DUAL;
579
580 /********************************************************/
581 /* pro_sqty: Processing Safety Stock Qty. */
582 /* pro_sdate: Processing Safety Stock Effectivity Date. */
583 /* This means the last stored safety stock date and qty */
584 /* Initialize the last stored safety stock date and qty */
585 /* pro_sqty is set to the last safety stock qty, while */
586 /* pro_sdate is set to the date before the effect_date. */
587 /* If there is no safety stock record, pro_sqty is set */
588 /* to 0. */
589 /********************************************************/
590
591 pro_sdate := j_effect_date;
592
593 OPEN QTY_cur;
594 FETCH QTY_cur INTO pro_sqty;
595 IF QTY_cur%NOTFOUND THEN
596 pro_sqty := 0;
597 END IF;
598 CLOSE QTY_cur;
599
600 /********************************************************/
601 /* Delete all the records since the effect_date, and */
602 /* prepare to reload them. */
603 /********************************************************/
604
605 DELETE FROM MTL_SAFETY_STOCKS
606 WHERE ORGANIZATION_ID = org_id
607 AND INVENTORY_ITEM_ID = item_id
608 AND EFFECTIVITY_DATE >= effect_date;
609
610
611 /********************************************************/
612 /* The following opens the cursor and select the */
613 /* forecast results into the variables. */
614 /* If there is no row selected, it implies that there */
615 /* is no forecast qty. In this case, the user */
616 /* exit will insert one row into the MTL_SAFETY_STOCKS */
617 /* table (ie, qty = 0 and effectivety_date). */
618 /********************************************************/
619 /********************************************************/
620 /* In contrast to the pro_sdate and pro_sqty, pro_fdate */
621 /* and pro_fqty are defined as the processing forecast */
622 /* qty and date respetively. The forecast qty and date */
623 /* are selected from the MRP_FORECAST_DATES. */
624 /* Initialize the pro_fdate and pro_fqty. */
625 /* pro_fdate <- effect_date */
626 /* pro_fqty <- 0 */
627 /* */
628 /* The following variables are notable. */
629 /* ss_date: safety stock dates. */
630 /* ss_qty: safety stock qty. */
631 /********************************************************/
632
633 -- start Bug # 5718937
634 l_mad_calc := FALSE;
635 OPEN c_bucket_type (forc_name);
636 FETCH c_bucket_type INTO bucket_type;
637 IF c_bucket_type%FOUND AND bucket_type <> 1 THEN
638 l_mad_calc := TRUE;
639 END IF;
640 -- end Bug # 5718937
641
642 if ss_code = 3 then -- 6797274 changes start
643 OPEN SelForecast_mad;
644 else
645 OPEN SelForecast_userper;
646 end if; -- 6797274 changes end
647
648 pro_fdate := j_effect_date;
649 pro_fqty := 0;
650 loop
651 if ss_code = 3 then -- 6797274 changes
652 FETCH SelForecast_mad INTO forc_type, forc_date, forc_qty;
653 exit when (SelForecast_mad%NOTFOUND);
654 else
655 FETCH SelForecast_userper INTO forc_type, forc_date, forc_qty;
656 exit when (SelForecast_userper%NOTFOUND);
657 end if; -- 6797274 changes end
658
659 --Added IF for bug # 5718937
660 IF ss_code = 3 and l_mad_calc THEN -- 6797274 changes
661 Insert_Safety_Stocks(org_id, item_id, ss_code, forc_name,
662 ss_percent, srv_level, to_date(forc_date,'J'), forc_qty,
663 login_id, user_id);
664 pro_sdate := forc_date + 1;
665
666 ELSE
667
668 if pro_fdate = forc_date then
669 pro_fqty := pro_fqty + forc_qty;
670 elsif (pro_fdate <> pro_sdate or
671 pro_fqty <> pro_sqty) then
672 if pro_fdate <> pro_sdate then
673 ss_date := pro_sdate;
674 ss_qty := 0;
675 -- Changes for Bug 3146158
679 ss_percent, srv_level, to_date(ss_date,'J'), ss_qty,
676 next_date := MRP_CALENDAR.NEXT_WORK_DAY(org_id, 1 , to_date(ss_date,'J'));
677 if (next_date = to_date(ss_date,'J')) then
678 Insert_Safety_Stocks(org_id, item_id, ss_code, forc_name,
680 login_id, user_id);
681 end if;
682 end if;
683 pro_sdate := pro_fdate +1 ;
684 pro_sqty := pro_fqty;
685 ss_date := pro_fdate;
686 ss_qty := pro_fqty;
687 pro_fdate := forc_date;
688 pro_fqty := forc_qty;
689 Insert_Safety_Stocks(org_id, item_id, ss_code, forc_name,
690 ss_percent, srv_level, to_date(ss_date,'J'), ss_qty,
691 login_id, user_id);
692 else
693 pro_fdate := forc_date;
694 pro_fqty := forc_qty;
695 pro_sdate := pro_sdate + 1;
696 end if;
697 END IF;
698 end loop;
699
700 /********************************************************/
701 /* Test and insert the last one which has the forecast */
702 /* qty and date, if necessary. */
703 /********************************************************/
704
705 -- 6797274 changes for below if condition
706 if (ss_code <> 3 and SelForecast_userper%ROWCOUNT > 0) or (ss_code = 3 and SelForecast_mad%ROWCOUNT > 0 AND (NOT l_mad_calc )) then /* at least one row selected */ -- Modified for Bug # 5718937
707 if(pro_fdate <> pro_sdate or
708 pro_fqty <> pro_sqty) then
709 if pro_fdate <> pro_sdate then
710 ss_date := pro_sdate;
711 ss_qty := 0;
712 -- Changes for Bug 3146158
713 next_date := MRP_CALENDAR.NEXT_WORK_DAY(org_id, 1 , to_date(ss_date,'J'));
714 if (next_date = to_date(ss_date,'J')) then
715 Insert_Safety_Stocks(org_id, item_id, ss_code, forc_name,
716 ss_percent, srv_level, to_date(ss_date,'J'), ss_qty,
717 login_id, user_id);
718 end if;
719 end if;
720 pro_sdate := pro_fdate +1 ;
721 pro_sqty := pro_fqty;
722 ss_date := pro_fdate;
723 ss_qty := pro_fqty;
724 Insert_Safety_Stocks(org_id, item_id, ss_code, forc_name,
725 ss_percent, srv_level, to_date(ss_date,'J'), ss_qty,
726 login_id, user_id);
727 else
728 pro_sdate := pro_sdate + 1;
729 end if;
730 END IF;
731 if ss_code = 3 then -- 6797274 changes
732 CLOSE SelForecast_mad;
733 else
734 CLOSE SelForecast_userper;
735 end if ; -- 6797274 changes End
736
737 /********************************************************/
738 /* Insert the very last one whose qty is 0 and date is */
739 /* the next day of the last day which has forecast qty. */
740 /********************************************************/
741 ss_date := pro_sdate;
742 ss_qty := 0;
743 -- Changes for Bug 3146158
744 next_date := MRP_CALENDAR.NEXT_WORK_DAY(org_id, 1 , to_date(ss_date,'J'));
745 if (next_date <> to_date(ss_date,'J')) then
746 ss_date := TO_NUMBER(TO_CHAR(next_date, 'J'));
747 end if;
748 Insert_Safety_Stocks(org_id, item_id, ss_code, forc_name,
749 ss_percent, srv_level, to_date(ss_date,'J'), ss_qty,
750 login_id, user_id);
751
752 END Main;
753
754
755 /********************************************************/
756 /* Build a dynamic SQL to insert into the MTL_SAFETY_ */
757 /* STOCKS table. */
758 /* ss_code = 1 :User-defined quantity */
759 /* = 2 :User-defined percentage */
760 /* = 3 :Mean absolute deviation (MAD) */
761 /********************************************************/
762
763 procedure Insert_Safety_Stocks (org_id NUMBER,
764 item_id NUMBER,
765 ss_code NUMBER,
766 forc_name VARCHAR2,
770 ss_qty NUMBER,
767 ss_percent NUMBER,
768 srv_level NUMBER,
769 ss_date DATE,
771 login_id NUMBER,
772 user_id NUMBER) IS
773
774
775
776 BEGIN
777 INSERT INTO MTL_SAFETY_STOCKS(
778 EFFECTIVITY_DATE,SAFETY_STOCK_QUANTITY,
779 SAFETY_STOCK_PERCENT, LAST_UPDATE_DATE,
780 SERVICE_LEVEL, CREATION_DATE, LAST_UPDATED_BY,
781 CREATED_BY, LAST_UPDATE_LOGIN, ORGANIZATION_ID,
782 INVENTORY_ITEM_ID, SAFETY_STOCK_CODE,
783 FORECAST_DESIGNATOR)
784 VALUES(
785 ss_date, ss_qty,
786 ss_percent, SYSDATE,
787 srv_level, SYSDATE, user_id,
788 user_id, login_id,
789 org_id, item_id, ss_code, forc_name);
790 END Insert_Safety_Stocks;
791
792
793
794 /******* CALCULATE SAFETY FACTOR FOR SAFETY STOCK *******/
795 /* */
796 /* The safety factor calculation is based on piecewise */
797 /* linear interpolation. */
798 /* Interpolation: */
799 /* y = (y2 - y1)/(x2 - x1) * (x - x1) + y1 */
800 /* */
801 /********************************************************/
802
803 FUNCTION CalSF(service_level NUMBER)
804 RETURN NUMBER IS
805
806 safety_factor NUMBER;
807
808 BEGIN
809 IF 50 <= service_level THEN
810 safety_factor := 0;
811 end if;
812 IF 60 <= service_level THEN
813 safety_factor := 0.253*(service_level-50)/10;
814 end if;
818 IF 80 <= service_level THEN
815 IF 70 <= service_level THEN
816 safety_factor := (0.525-0.253)*(service_level-60)/10+0.253;
817 end if;
819 safety_factor := (0.84-0.525)*(service_level-70)/10+0.525;
820 end if;
821 IF 86 <= service_level THEN
822 safety_factor := (1.08-0.84)*(service_level-80)/6+0.84;
823 end if;
824 IF 90 <= service_level THEN
825 safety_factor := (1.28-1.08)*(service_level-86)/4+1.08;
826 end if;
827 IF 94 <= service_level THEN
828 safety_factor := (1.555-1.28)*(service_level-90)/4+1.28;
829 end if;
830 IF 97 <= service_level THEN
831 safety_factor := (1.88-1.555)*(service_level-94)/3+1.555;
832 end if;
833 IF 98 <= service_level THEN
834 safety_factor := (2.055-1.88)*(service_level-97)+1.88;
835 end if;
836 IF 99 <= service_level THEN
837 safety_factor := (2.33-2.055)*(service_level-98)+2.055;
838 end if;
839 IF 99.36 <= service_level THEN
840 safety_factor := (2.49-2.33)*(service_level-99)/0.36+2.33;
841 end if;
842 IF 99.56 <= service_level THEN
843 safety_factor := (2.62-2.49)*(service_level-99.36)/0.2+2.49;
844 end if;
845 IF 99.66 <= service_level THEN
846 safety_factor := (2.706-2.62)*(service_level-99.56)/0.1+2.62;
847 end if;
848 IF 99.76 <= service_level THEN
849 safety_factor := (2.82-2.706)*(service_level-99.66)/0.1+2.706;
850 end if;
851 IF 99.83 <= service_level THEN
852 safety_factor := (2.93-2.82)*(service_level-99.76)/0.07+2.82;
853 end if;
854 IF 99.83 <= service_level THEN
855 safety_factor := (2.93-2.82)*(service_level-99.76)/0.07+2.82;
856 end if;
857 IF 99.875 <= service_level THEN
858 safety_factor := (3-2.93)*(service_level-99.83)/0.045+2.93;
859 end if;
860 IF 99.9032 <= service_level THEN
861 safety_factor := (3.1-3)*(service_level-99.875)/(99.9032-99.875)+3;
862 end if;
863 IF 99.9313 <= service_level THEN
864 safety_factor := (3.2-3.1)*(service_level-99.9032)/(99.9313-99.9032)+3.1;
865 end if;
866 IF 99.9517 <= service_level THEN
867 safety_factor := (3.3-3.2)*(service_level-99.9313)/(99.9517-99.9313)+3.2;
868 end if;
869 IF 99.9663 <= service_level THEN
870 safety_factor := (3.4-3.3)*(service_level-99.9517)/(99.9663-99.9517)+3.3;
871 end if;
872 IF 99.9767 <= service_level THEN
873 safety_factor := (3.5-3.4)*(service_level-99.9663)/(99.9767-99.9663)+3.4;
874 end if;
875 IF 99.9841 <= service_level THEN
876 safety_factor := (3.6-3.5)*(service_level-99.9767)/(99.9841-99.9767)+3.5;
877 end if;
878 IF 99.992 <= service_level THEN
879 safety_factor := (3.7-3.6)*(service_level-99.9841)/(99.992-99.9841)+3.6;
880 end if;
881 IF 100 <= service_level THEN
882 safety_factor := (4-3.7)*(service_level-99.992)/(100-99.992)+3.7;
883 end if;
884 return(safety_factor);
885 END CalSF;
886
887
888 END MTL_SAFETY_STOCKS_PKG;
[Home] [Help]