[Home] [Help]
PACKAGE BODY: APPS.FLM_SUPPLY_DEMAND
Source
1 PACKAGE BODY FLM_SUPPLY_DEMAND AS
2 /* $Header: FLMMTSDB.pls 120.3 2006/02/20 18:14:30 yulin noship $ */
3
4
5 -- Used SUPPLY DEMAND SOURCE TYPE (from the existing ones in mfg_lookups:)
6 -- 1: Purchase order (Used)
7 -- 2: Sales order (Used)
8 -- 3: Account number (Not Used)
9 -- 4: WIP repetitive schedule (Used)
10 -- 5: WIP discrete job (Used)
11 -- 6: Account alias (Not Used)
12 -- 7: WIP nonstandard job (Used)
13 -- 8: Onhand quantity (Used)
14 -- 9: Reserved sales order (Not Used)
15 -- 10: Reserved account number (Not Used)
16 -- 11: Reserved account alias (Not Used)
17 -- 12: Intransit receipt (Used)
18 -- 13: Discrete MPS (Not Used)
19 -- 14: Repetitive MPS (Not Used)
20 -- 15: Onhand Reservation (Not Used)
21 -- 16: User supply (Used)
22 -- 17: User Demand (Used)
23 -- 18: PO Requisition (Not Used)
24 -- 19: Reserved user source (Not Used)
25 -- 20: Internal requisition (Not Used)
26 -- 21: Internal order (Used)
27 -- 22: Reserved internal order (Not Used)
28 -- 23: WIP Supply Reservation (Not Used)
29 -- 24: Flow Schedule (Used)
30
31
32
33 -- SUPPLY DEMAND TYPE:
34 -- 1: Demand
35 -- 2: Supply
36
37
38 -- source_identifier1: instance id. -1 for non-distributed environment
39 -- source_identifier2: null for now
40
41 -- plan_id: -1 if it is from execution system
42 -- (-2 if populated from scheduling manager)
43
44
45
46
47 /*
48 This function calls the procedure by the same name and returns the number of
49 records collected in the global pl/sql variable. Returns -1 on error.
50 */
51 FUNCTION Collect_Supply_Demand_Info(p_group_id IN NUMBER,
52 p_sys_seq_num IN NUMBER,
53 p_mrp_status IN NUMBER) RETURN NUMBER
54 IS
55 l_err_buf VARCHAR2(2000);
56 l_ret_code NUMBER;
57 BEGIN
58
59 Clear_Supply_Demand_Info;
60
61 Collect_Supply_Demand_Info(p_group_id => p_group_id,
62 p_sys_seq_num => p_sys_seq_num,
63 p_mrp_status => p_mrp_status,
64 p_sup_dem_table => g_supply_demand_table,
65 ERRBUF => l_err_buf,
66 RETCODE => l_ret_code);
67
68 RETURN g_supply_demand_table.COUNT;
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 RETURN -1;
73
74 END Collect_Supply_Demand_Info;
75
76
77
78 PROCEDURE Get_Supply_Demand_Info(x_supply_demand_table OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE)
79 IS
80 BEGIN
81 x_supply_demand_table := g_supply_demand_table;
82
83 END Get_Supply_Demand_Info;
84
85
86 /*
87 This procedure exists only for the sake of Pro*C. At this point Pro*C allows only
88 arrays of primitives in pl/sql blocks, so this procedure essentially converts the
89 global "table of records" into a bunch of "table of numbers"
90 */
91 PROCEDURE Get_Supply_Demand_Info(p_starting_index IN NUMBER DEFAULT 1,
92 p_ending_index IN NUMBER DEFAULT -1,
93 x_rows_fetched OUT NOCOPY NUMBER,
94 x_reservation_type_tbl OUT NOCOPY Number_Tbl_Type,
95 x_supply_demand_src_type_tbl OUT NOCOPY Number_Tbl_Type,
96 x_txn_source_type_id_tbl OUT NOCOPY Number_Tbl_Type,
97 x_supply_demand_source_id_tbl OUT NOCOPY Number_Tbl_Type,
98 x_supply_demand_type_tbl OUT NOCOPY Number_Tbl_Type,
99 x_supply_demand_quantity_tbl OUT NOCOPY Number_Tbl_Type,
100 x_supply_demand_date_tbl OUT NOCOPY Number_Tbl_Type,
101 x_inventory_item_id_tbl OUT NOCOPY Number_Tbl_Type,
102 x_organization_id_tbl OUT NOCOPY Number_Tbl_Type)
103 IS
104 l_ending_index NUMBER;
105 l_starting_index NUMBER;
106 l_max_length NUMBER := g_supply_demand_table.COUNT;
107 BEGIN
108
109 x_rows_fetched := 0;
110
111 IF p_ending_index < 0 THEN
112 l_ending_index := l_max_length;
113 ELSIF p_ending_index > l_max_length THEN
114 l_ending_index := l_max_length;
115 ELSE
116 l_ending_index := p_ending_index;
117 END IF;
118
119 IF p_starting_index < 0 THEN
120 l_starting_index := 1;
121 ELSIF p_starting_index > l_max_length THEN
122 RETURN;
123 ELSE
124 l_starting_index := p_starting_index;
125 END IF;
126
127
128 FOR i IN l_starting_index..l_ending_index LOOP
129
130 x_reservation_type_tbl(x_rows_fetched+1) :=
131 Nvl(g_supply_demand_table(i).reservation_type,0);
132
133 x_supply_demand_src_type_tbl(x_rows_fetched+1) :=
134 Nvl(g_supply_demand_table(i).supply_demand_source_type,0);
135
136 x_txn_source_type_id_tbl(x_rows_fetched+1) :=
137 Nvl(g_supply_demand_table(i).txn_source_type_id,0);
138
139 x_supply_demand_source_id_tbl(x_rows_fetched+1) :=
140 Nvl(g_supply_demand_table(i).supply_demand_source_id,0);
141
142 x_supply_demand_type_tbl(x_rows_fetched+1) :=
143 Nvl(g_supply_demand_table(i).supply_demand_type,0);
144
145 x_supply_demand_quantity_tbl(x_rows_fetched+1) :=
146 Nvl(g_supply_demand_table(i).supply_demand_quantity,0);
147
148 x_supply_demand_date_tbl(x_rows_fetched+1) :=
149 Nvl(g_supply_demand_table(i).supply_demand_date,0);
150
151 x_inventory_item_id_tbl(x_rows_fetched+1) :=
152 Nvl(g_supply_demand_table(i).inventory_item_id,0);
153
154 x_organization_id_tbl(x_rows_fetched+1) :=
155 Nvl(g_supply_demand_table(i).organization_id,0);
156
157 x_rows_fetched := x_rows_fetched + 1;
158
159 END LOOP;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 NULL;
164
165 END Get_Supply_Demand_Info;
166
167
168
169 PROCEDURE Clear_Supply_Demand_Info
170 IS
171 BEGIN
172
173 g_supply_demand_table.delete;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 NULL;
178
179 END Clear_Supply_Demand_Info;
180
181
182 /*
183 This procedure calls two separate procedures, one to collect the supply information
184 and the other to collect the demand information. All the results are stored in a
185 global pl/sql table of records.
186 */
187 PROCEDURE Collect_Supply_Demand_Info(p_group_id IN NUMBER,
188 p_sys_seq_num IN NUMBER,
189 p_mrp_status IN NUMBER,
190 p_sup_dem_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
191 ERRBUF OUT NOCOPY VARCHAR2,
192 RETCODE OUT NOCOPY NUMBER)
193 IS
194
195 BEGIN
196
197 Collect_Supply_Info(p_group_id => p_group_id,
198 p_sys_seq_num => p_sys_seq_num,
199 p_mrp_status => p_mrp_status,
200 p_supply_table => p_sup_dem_table,
201 ERRBUF => ERRBUF,
202 RETCODE => RETCODE);
203
204 Collect_Demand_Info(p_group_id => p_group_id,
205 p_sys_seq_num => p_sys_seq_num,
206 p_mrp_status => p_mrp_status,
207 p_demand_table => p_sup_dem_table,
208 ERRBUF => ERRBUF,
209 RETCODE => RETCODE);
210
211 END Collect_Supply_Demand_Info;
212
213
214 /*
215 This procedure is a wrapper to collect all the individual supply types.
216 */
217 PROCEDURE Collect_Supply_Info(p_group_id IN NUMBER,
218 p_sys_seq_num IN NUMBER,
219 p_mrp_status IN NUMBER,
220 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
221 ERRBUF OUT NOCOPY VARCHAR2,
222 RETCODE OUT NOCOPY NUMBER)
223 IS
224
225 BEGIN
226
227 Collect_OnHand_Supply(p_group_id => p_group_id,
228 p_sys_seq_num => p_sys_seq_num,
229 p_mrp_status => p_mrp_status,
230 p_supply_table => p_supply_table,
231 ERRBUF => ERRBUF,
232 RETCODE => RETCODE);
233
234 Collect_User_Supply(p_group_id => p_group_id,
235 p_sys_seq_num => p_sys_seq_num,
236 p_mrp_status => p_mrp_status,
237 p_supply_table => p_supply_table,
238 ERRBUF => ERRBUF,
239 RETCODE => RETCODE);
240
241 Collect_MTL_Supply(p_group_id => p_group_id,
242 p_sys_seq_num => p_sys_seq_num,
243 p_mrp_status => p_mrp_status,
244 p_supply_table => p_supply_table,
245 ERRBUF => ERRBUF,
246 RETCODE => RETCODE);
247
248 Collect_DiscreteJob_Supply(p_group_id => p_group_id,
249 p_sys_seq_num => p_sys_seq_num,
250 p_mrp_status => p_mrp_status,
251 p_supply_table => p_supply_table,
252 ERRBUF => ERRBUF,
253 RETCODE => RETCODE);
254
255 Collect_WipNegReq_Supply(p_group_id => p_group_id,
256 p_sys_seq_num => p_sys_seq_num,
257 p_mrp_status => p_mrp_status,
258 p_supply_table => p_supply_table,
259 ERRBUF => ERRBUF,
260 RETCODE => RETCODE);
261
262 Collect_RepSched_Supply(p_group_id => p_group_id,
263 p_sys_seq_num => p_sys_seq_num,
264 p_mrp_status => p_mrp_status,
265 p_supply_table => p_supply_table,
266 ERRBUF => ERRBUF,
267 RETCODE => RETCODE);
268
269 Collect_FlowSched_Supply(p_group_id => p_group_id,
270 p_sys_seq_num => p_sys_seq_num,
271 p_mrp_status => p_mrp_status,
272 p_supply_table => p_supply_table,
273 ERRBUF => ERRBUF,
274 RETCODE => RETCODE);
275
276
277
278
279 END Collect_Supply_Info;
280
281
282 /*
283 This procedure is a wrapper to collect all the individual demand types.
284 */
285 PROCEDURE Collect_Demand_Info(p_group_id IN NUMBER,
286 p_sys_seq_num IN NUMBER,
287 p_mrp_status IN NUMBER,
288 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
289 ERRBUF OUT NOCOPY VARCHAR2,
290 RETCODE OUT NOCOPY NUMBER)
291 IS
292
293 BEGIN
294
295 Collect_DiscreteJob_Demand(p_group_id => p_group_id,
296 p_sys_seq_num => p_sys_seq_num,
297 p_mrp_status => p_mrp_status,
298 p_demand_table => p_demand_table,
299 ERRBUF => ERRBUF,
300 RETCODE => RETCODE);
301
302 Collect_RepSched_Demand(p_group_id => p_group_id,
303 p_sys_seq_num => p_sys_seq_num,
304 p_mrp_status => p_mrp_status,
305 p_demand_table => p_demand_table,
306 ERRBUF => ERRBUF,
307 RETCODE => RETCODE);
308
309 Collect_User_Demand(p_group_id => p_group_id,
310 p_sys_seq_num => p_sys_seq_num,
311 p_mrp_status => p_mrp_status,
312 p_demand_table => p_demand_table,
316 Collect_FlowSched_Demand(p_group_id => p_group_id,
313 ERRBUF => ERRBUF,
314 RETCODE => RETCODE);
315
317 p_sys_seq_num => p_sys_seq_num,
318 p_mrp_status => p_mrp_status,
319 p_demand_table => p_demand_table,
320 ERRBUF => ERRBUF,
321 RETCODE => RETCODE);
322
323 Collect_SalesOrder_Demand(p_group_id => p_group_id,
324 p_sys_seq_num => p_sys_seq_num,
325 p_mrp_status => p_mrp_status,
326 p_demand_table => p_demand_table,
327 ERRBUF => ERRBUF,
328 RETCODE => RETCODE);
329
330
331 END Collect_Demand_Info;
332
333
334
335 --
336 -- The procedures to collect individual supply/demand information follow.
337 --
338
339
340 PROCEDURE Collect_OnHand_Supply(p_group_id IN NUMBER,
341 p_sys_seq_num IN NUMBER,
342 p_mrp_status IN NUMBER,
343 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
344 ERRBUF OUT NOCOPY VARCHAR2,
345 RETCODE OUT NOCOPY NUMBER)
346 IS
347
348 CURSOR OH_SUPPLY_CURSOR IS
349 SELECT
350 1 reservation_type,
351 8 supply_demand_source_type,
352 0 txn_source_type_id,
353 0 supply_demand_source_id,
354 2 supply_demand_type,
355 SUM(Q.TRANSACTION_QUANTITY) supply_demand_quantity,
356 TO_NUMBER(TO_CHAR(C.NEXT_DATE-1,'J')) supply_demand_date,
357 V.INVENTORY_ITEM_ID inventory_item_id,
358 V.ORGANIZATION_ID organization_id
359 FROM
360 MTL_SECONDARY_INVENTORIES S,
361 BOM_CALENDAR_DATES C,
362 MTL_PARAMETERS P,
363 MTL_ONHAND_QUANTITIES Q,
364 MTL_ATP_RULES R,
365 MTL_SYSTEM_ITEMS I,
366 MTL_GROUP_ITEM_ATPS_VIEW V
367 WHERE I.ORGANIZATION_ID = V.ORGANIZATION_ID
368 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
369 AND Q.ORGANIZATION_ID = V.ORGANIZATION_ID
370 AND Q.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
371 AND S.SECONDARY_INVENTORY_NAME = Q.SUBINVENTORY_CODE
372 AND S.ORGANIZATION_ID = Q.ORGANIZATION_ID
373 AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
374 AND S.AVAILABILITY_TYPE = DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)
375 AND V.AVAILABLE_TO_ATP = 1
376 AND V.ATP_RULE_ID = R.RULE_ID
377 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2, -1, V.INVENTORY_ITEM_ID)
378 AND V.ATP_GROUP_ID = P_GROUP_ID
379 AND R.DEMAND_CLASS_ATP_FLAG=2
380 AND P.CALENDAR_CODE = C.CALENDAR_CODE
381 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
382 AND C.CALENDAR_DATE = TRUNC(SYSDATE)
383 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
384 GROUP BY V.INVENTORY_ITEM_ID, V.ORGANIZATION_ID, C.NEXT_DATE, C.NEXT_SEQ_NUM
385 UNION ALL
386 SELECT
387 1 reservation_type,
388 8 supply_demand_source_type,
389 0 txn_source_type_id,
390 0 supply_demand_source_id,
391 2 supply_demand_type,
392 SUM(T.PRIMARY_QUANTITY) supply_demand_quantity,
393 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
394 V.INVENTORY_ITEM_ID inventory_item_id,
395 V.ORGANIZATION_ID organization_id
396 FROM MTL_SECONDARY_INVENTORIES S,
397 BOM_CALENDAR_DATES C,
398 MTL_PARAMETERS P,
399 MTL_MATERIAL_TRANSACTIONS_TEMP T,
400 MTL_SYSTEM_ITEMS I,
401 MTL_ATP_RULES R,
402 MTL_GROUP_ITEM_ATPS_VIEW V
403 WHERE I.ORGANIZATION_ID = V.ORGANIZATION_ID
404 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
405 AND T.ORGANIZATION_ID = V.ORGANIZATION_ID
406 AND T.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
407 AND T.POSTING_FLAG = 'Y'
408 AND S.SECONDARY_INVENTORY_NAME = T.SUBINVENTORY_CODE
409 AND S.ORGANIZATION_ID = T.ORGANIZATION_ID
410 AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
411 AND S.AVAILABILITY_TYPE = DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)
412 AND V.AVAILABLE_TO_ATP = 1
413 AND V.ATP_RULE_ID = R.RULE_ID
414 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2, -1, V.INVENTORY_ITEM_ID)
415 AND V.ATP_GROUP_ID = P_GROUP_ID
416 AND R.DEMAND_CLASS_ATP_FLAG=2
417 AND P.CALENDAR_CODE = C.CALENDAR_CODE
418 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
419 AND C.CALENDAR_DATE = TRUNC(SYSDATE)
420 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
421 GROUP BY V.INVENTORY_ITEM_ID, V.ORGANIZATION_ID, C.NEXT_DATE, C.NEXT_SEQ_NUM;
422
423 l_supply_rec OH_SUPPLY_CURSOR%ROWTYPE;
424 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
425 BEGIN
426
427 OPEN OH_SUPPLY_CURSOR;
428
429 LOOP
430 FETCH OH_SUPPLY_CURSOR INTO l_supply_rec;
431 EXIT WHEN OH_SUPPLY_CURSOR%NOTFOUND;
432
433 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
434 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
435 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
436 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
437 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
438 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
439 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
440 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
441 p_supply_table(j).organization_id := l_supply_rec.organization_id;
442
443 j := j+1;
444
445 END LOOP;
446
447 CLOSE OH_SUPPLY_CURSOR;
448
449 END Collect_OnHand_Supply;
450
451
455 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
452 PROCEDURE Collect_User_Supply(p_group_id IN NUMBER,
453 p_sys_seq_num IN NUMBER,
454 p_mrp_status IN NUMBER,
456 ERRBUF OUT NOCOPY VARCHAR2,
457 RETCODE OUT NOCOPY NUMBER)
458 IS
459
460 CURSOR USER_SUPPLY_CURSOR IS
461 SELECT
462 1 reservation_type,
463 16 supply_demand_source_type,
464 U.SOURCE_TYPE_ID txn_source_type_id,
465 U.SOURCE_ID supply_demand_source_id,
466 2 supply_demand_type,
467 U.PRIMARY_UOM_QUANTITY supply_demand_quantity,
468 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
469 V.INVENTORY_ITEM_ID inventory_item_id,
470 V.ORGANIZATION_ID organization_id
471 FROM
472 BOM_CALENDAR_DATES C,
473 MTL_USER_SUPPLY U,
474 MTL_SYSTEM_ITEMS I,
475 MTL_PARAMETERS P,
476 MTL_ATP_RULES R,
477 MTL_GROUP_ITEM_ATPS_VIEW V
478 WHERE U.ORGANIZATION_ID = V.ORGANIZATION_ID
479 AND U.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
480 AND V.AVAILABLE_TO_ATP = 1
481 AND V.ATP_RULE_ID = R.RULE_ID
482 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_USER_DEFINED_SUPPLY, 2, -1, V.INVENTORY_ITEM_ID)
483 AND V.ATP_GROUP_ID = P_GROUP_ID
484 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
485 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
486 AND NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
487 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
488 NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
489 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
490 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
491 NULL, C.NEXT_SEQ_NUM,
492 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
493 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM +(DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
494 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
495 2, I.CUM_MANUFACTURING_LEAD_TIME,
496 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
497 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
498 AND P.CALENDAR_CODE = C.CALENDAR_CODE
499 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
500 AND C.CALENDAR_DATE = TRUNC(U.EXPECTED_DELIVERY_DATE);
501
502 l_supply_rec USER_SUPPLY_CURSOR%ROWTYPE;
503 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
504 BEGIN
505
506 OPEN USER_SUPPLY_CURSOR;
507
508 LOOP
509 FETCH USER_SUPPLY_CURSOR INTO l_supply_rec;
510 EXIT WHEN USER_SUPPLY_CURSOR%NOTFOUND;
511
512 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
513 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
514 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
515 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
516 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
517 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
518 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
519 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
520 p_supply_table(j).organization_id := l_supply_rec.organization_id;
521
522 j := j+1;
523
524 END LOOP;
525
526 CLOSE USER_SUPPLY_CURSOR;
527
528
529 END Collect_User_Supply;
530
531
532 PROCEDURE Collect_MTL_Supply(p_group_id IN NUMBER,
533 p_sys_seq_num IN NUMBER,
534 p_mrp_status IN NUMBER,
535 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
536 ERRBUF OUT NOCOPY VARCHAR2,
537 RETCODE OUT NOCOPY NUMBER)
538 IS
539
540 -- queries mtl_supply information, that is PO, REQ, SHIP, RCV
541 -- question here,
542 -- I select NVL(S.MRP_PRIMARY_QUANTITY, S.TO_ORG_PRIMARY_QUANTITY)
543 -- as the supply_demand_quantity if discrete mps is included,
544 -- S.TO_ORG_PRIMARY_QUANTITY if not included.
545 -- However, in inldsd.ppc, it selects
546 -- S.TO_ORG_PRIMARY_QUANTITY for shipment, NVL(S.MRP_PRIMARY_QUANTITY, 0)
547 -- if discrete mps is included, S.TO_ORG_PRIMARY_QUANTITY if not included
548
549 CURSOR MTL_SUPPLY_CURSOR IS
550 SELECT
551 1 reservation_type,
552 DECODE(S.PO_HEADER_ID,
553 NULL,DECODE(S.SUPPLY_TYPE_CODE,
554 'REQ',DECODE(S.FROM_ORGANIZATION_ID,
555 NULL,18,
556 20),
557 12),
558 1) supply_demand_source_type,
559 DECODE(S.PO_HEADER_ID,
560 NULL,DECODE(S.SUPPLY_TYPE_CODE,
561 'REQ',10,
562 8),
563 1) txn_source_type_id,
564 DECODE(S.PO_HEADER_ID,
565 NULL,DECODE(S.SUPPLY_TYPE_CODE,
566 'REQ',REQ_HEADER_ID,
567 SHIPMENT_HEADER_ID),
568 PO_HEADER_ID) supply_demand_source_id,
569 2 supply_demand_type,
570 DECODE(P_MRP_STATUS,
571 1, DECODE(S.SUPPLY_TYPE_CODE,
572 'SHIPMENT', S.TO_ORG_PRIMARY_QUANTITY,
573 DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
574 1,NVL(S.MRP_PRIMARY_QUANTITY, 0),
575 S.TO_ORG_PRIMARY_QUANTITY)),
576 S.TO_ORG_PRIMARY_QUANTITY) supply_demand_quantity,
577 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
578 V.INVENTORY_ITEM_ID inventory_item_id,
579 V.ORGANIZATION_ID organization_id
580 FROM
581 MTL_GROUP_ITEM_ATPS_VIEW V,
582 MTL_ATP_RULES R,
583 MTL_SYSTEM_ITEMS I,
584 MTL_PARAMETERS P,
585 BOM_CALENDAR_DATES C,
586 MTL_SUPPLY S
587 WHERE V.ATP_GROUP_ID = P_GROUP_ID
591 AND((R.INCLUDE_INTERORG_TRANSFERS = 1
588 AND R.DEMAND_CLASS_ATP_FLAG=2
589 AND V.AVAILABLE_TO_ATP = 1
590 AND V.ATP_RULE_ID = R.RULE_ID
592 AND S.REQ_HEADER_ID IS NULL
593 AND S.PO_HEADER_ID IS NULL)
594 OR (S.REQ_HEADER_ID=DECODE(R.INCLUDE_INTERNAL_REQS,1,S.REQ_HEADER_ID)
595 AND S.FROM_ORGANIZATION_ID IS NOT NULL)
596 OR (S.SUPPLY_TYPE_CODE=DECODE(R.INCLUDE_VENDOR_REQS,1,'REQ')
597 AND S.FROM_ORGANIZATION_ID IS NULL)
598 OR S.PO_HEADER_ID=DECODE(R.INCLUDE_PURCHASE_ORDERS,1, S.PO_HEADER_ID))
599 AND S.TO_ORGANIZATION_ID=V.ORGANIZATION_ID
600 AND S.ITEM_ID = V.INVENTORY_ITEM_ID
601 AND S.DESTINATION_TYPE_CODE='INVENTORY'
602 AND (S.TO_SUBINVENTORY IS NULL OR EXISTS (SELECT
603 'X' FROM MTL_SECONDARY_INVENTORIES S2
604 WHERE S2.ORGANIZATION_ID=S.TO_ORGANIZATION_ID
605 AND S.TO_SUBINVENTORY=S2.SECONDARY_INVENTORY_NAME
606 AND S2.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
607 1, 1,
608 NULL, 1,
609 S2.INVENTORY_ATP_CODE)
610 AND S2.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
611 2, 1,
612 S2.AVAILABILITY_TYPE)))
613 AND I.ORGANIZATION_ID= V.ORGANIZATION_ID
614 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
615 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
616 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
617 NULL, C.NEXT_SEQ_NUM,
618 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
619 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
620 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
621 2, I.CUM_MANUFACTURING_LEAD_TIME,
622 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
623 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
624 AND P.CALENDAR_CODE = C.CALENDAR_CODE
625 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
626 AND C.CALENDAR_DATE = TRUNC(S.EXPECTED_DELIVERY_DATE);
627
628 l_supply_rec MTL_SUPPLY_CURSOR%ROWTYPE;
629 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
630 BEGIN
631
632 OPEN MTL_SUPPLY_CURSOR;
633
634 LOOP
635 FETCH MTL_SUPPLY_CURSOR INTO l_supply_rec;
636 EXIT WHEN MTL_SUPPLY_CURSOR%NOTFOUND;
637
638 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
639 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
640 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
641 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
642 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
643 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
644 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
645 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
646 p_supply_table(j).organization_id := l_supply_rec.organization_id;
647
648 j := j+1;
649
650 END LOOP;
651
652 CLOSE MTL_SUPPLY_CURSOR;
653
654 END Collect_MTL_Supply;
655
656 PROCEDURE Collect_DiscreteJob_Supply(p_group_id IN NUMBER,
657 p_sys_seq_num IN NUMBER,
658 p_mrp_status IN NUMBER,
659 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
660 ERRBUF OUT NOCOPY VARCHAR2,
661 RETCODE OUT NOCOPY NUMBER)
662 IS
663
664 -- insert wip discrete job information
665 -- question here: do I need to apply bug 791215 here?
666 -- that is , using net_quantity instead of mps_net_quantity?
667
668 CURSOR DISCRETEJOB_SUPPLY_CURSOR IS
669 SELECT
670 1 reservation_type,
671 DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
672 5 txn_source_type_id,
673 D.WIP_ENTITY_ID supply_demand_source_id,
674 2 supply_demand_type,
675 DECODE(P_MRP_STATUS,
676 1, DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
677 1, DECODE(D.JOB_TYPE,1,
678 DECODE(I.MRP_PLANNING_CODE,
679 4,NVL(D.MPS_NET_QUANTITY,0),
680 D.START_QUANTITY),
681 D.START_QUANTITY),
682 D.START_QUANTITY) - D.QUANTITY_COMPLETED - D.QUANTITY_SCRAPPED,
683 D.START_QUANTITY - D.QUANTITY_COMPLETED - D.QUANTITY_SCRAPPED) supply_demand_quantity,
684 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
685 V.INVENTORY_ITEM_ID inventory_item_id,
686 V.ORGANIZATION_ID organization_id
687 FROM WIP_DISCRETE_JOBS D,
688 BOM_CALENDAR_DATES C,
689 MTL_PARAMETERS P,
690 MTL_SYSTEM_ITEMS I,
691 MTL_ATP_RULES R,
692 MTL_GROUP_ITEM_ATPS_VIEW V
693 WHERE D.STATUS_TYPE IN (1,3,4,6)
694 AND (D.START_QUANTITY-D.QUANTITY_COMPLETED-D.QUANTITY_SCRAPPED) >0
695 AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
696 AND D.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
697 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,
698 V.INVENTORY_ITEM_ID, DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
699 1,V.INVENTORY_ITEM_ID,
700 -1))
701 AND (D.JOB_TYPE =DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
702 OR D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
703 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
704 AND V.AVAILABLE_TO_ATP = 1
705 AND V.ATP_RULE_ID = R.RULE_ID
706 AND V.ATP_GROUP_ID = P_GROUP_ID
707 AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
708 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
709 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
710 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
711 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
715 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
712 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
713 NULL, C.NEXT_SEQ_NUM,
714 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
716 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
717 2, I.CUM_MANUFACTURING_LEAD_TIME,
718 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
719 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
720 AND P.CALENDAR_CODE = C.CALENDAR_CODE
721 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
722 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE);
723
724 l_supply_rec DISCRETEJOB_SUPPLY_CURSOR%ROWTYPE;
725 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
726 BEGIN
727
728 OPEN DISCRETEJOB_SUPPLY_CURSOR;
729
730 LOOP
731 FETCH DISCRETEJOB_SUPPLY_CURSOR INTO l_supply_rec;
732 EXIT WHEN DISCRETEJOB_SUPPLY_CURSOR%NOTFOUND;
733
734 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
735 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
736 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
737 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
738 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
739 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
740 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
741 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
742 p_supply_table(j).organization_id := l_supply_rec.organization_id;
743
744 j := j+1;
745
746 END LOOP;
747
748 CLOSE DISCRETEJOB_SUPPLY_CURSOR;
749
750 END Collect_DiscreteJob_Supply;
751
752 PROCEDURE Collect_WipNegReq_Supply(p_group_id IN NUMBER,
753 p_sys_seq_num IN NUMBER,
754 p_mrp_status IN NUMBER,
755 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
756 ERRBUF OUT NOCOPY VARCHAR2,
757 RETCODE OUT NOCOPY NUMBER)
758 IS
759
760
761 -- insert wip neg requirement information
762 -- I have applied bug 454103 here.
763
764 CURSOR WIPNEGREQ_SUPPLY_CURSOR IS
765 SELECT
766 1 reservation_type,
767 DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
768 5 txn_source_type_id,
769 D.WIP_ENTITY_ID supply_demand_source_id,
770 2 supply_demand_type,
771 -1*O.REQUIRED_QUANTITY supply_demand_quantity,
772 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
773 V.INVENTORY_ITEM_ID inventory_item_id,
774 V.ORGANIZATION_ID organization_id
775 FROM MTL_GROUP_ITEM_ATPS_VIEW V,
776 MTL_PARAMETERS P,
777 MTL_ATP_RULES R,
778 MTL_SYSTEM_ITEMS I,
779 BOM_CALENDAR_DATES C,
780 WIP_REQUIREMENT_OPERATIONS O,
781 WIP_DISCRETE_JOBS D
782 WHERE O.ORGANIZATION_ID=D.ORGANIZATION_ID
783 AND O.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
784 AND O.WIP_ENTITY_ID=D.WIP_ENTITY_ID
785 AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
786 AND O.WIP_SUPPLY_TYPE <> 6
787 AND O.REQUIRED_QUANTITY < 0
788 AND O.OPERATION_SEQ_NUM > 0
789 AND (D.JOB_TYPE=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
790 OR D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
791 AND D.STATUS_TYPE IN (1,3,4,6)
792 AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
793 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
794 AND V.AVAILABLE_TO_ATP = 1
795 AND V.ATP_RULE_ID = R.RULE_ID
796 AND V.ATP_GROUP_ID = P_GROUP_ID
797 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,
798 V.INVENTORY_ITEM_ID, DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1,
799 V.INVENTORY_ITEM_ID, -1))
800 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
801 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
802 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
803 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
804 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
805 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
806 NULL, C.NEXT_SEQ_NUM,
807 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
808 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
809 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
810 2, I.CUM_MANUFACTURING_LEAD_TIME,
811 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
812 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
813 AND P.CALENDAR_CODE = C.CALENDAR_CODE
814 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
815 AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED);
816
817 l_supply_rec WIPNEGREQ_SUPPLY_CURSOR%ROWTYPE;
818 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
819 BEGIN
820
821 OPEN WIPNEGREQ_SUPPLY_CURSOR;
822
823 LOOP
824 FETCH WIPNEGREQ_SUPPLY_CURSOR INTO l_supply_rec;
825 EXIT WHEN WIPNEGREQ_SUPPLY_CURSOR%NOTFOUND;
826
827 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
828 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
829 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
830 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
831 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
832 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
833 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
837 j := j+1;
834 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
835 p_supply_table(j).organization_id := l_supply_rec.organization_id;
836
838
839 END LOOP;
840
841 CLOSE WIPNEGREQ_SUPPLY_CURSOR;
842
843 END Collect_WipNegReq_Supply;
844
845
846 PROCEDURE Collect_RepSched_Supply(p_group_id IN NUMBER,
847 p_sys_seq_num IN NUMBER,
848 p_mrp_status IN NUMBER,
849 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
850 ERRBUF OUT NOCOPY VARCHAR2,
851 RETCODE OUT NOCOPY NUMBER)
852 IS
853
854 -- insert wip repetitive supply
855
856 CURSOR REPSCHED_SUPPLY_CURSOR IS
857 SELECT
858 1 reservation_type,
859 4 supply_demand_source_type,
860 5 txn_source_type_id,
861 WRS.REPETITIVE_SCHEDULE_ID supply_demand_source_id,
862 2 supply_demand_type,
863 DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM)-WRS.QUANTITY_COMPLETED),
864 -1,WRS.DAILY_PRODUCTION_RATE*LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,
865 WRS.PROCESSING_WORK_DAYS)-WRS.QUANTITY_COMPLETED,
866 LEAST(C1.NEXT_SEQ_NUM+WRS.PROCESSING_WORK_DAYS-C.NEXT_SEQ_NUM,1)*WRS.DAILY_PRODUCTION_RATE) supply_demand_quantity,
867 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
868 V.INVENTORY_ITEM_ID inventory_item_id,
869 V.ORGANIZATION_ID organization_id
870 FROM
871 MTL_GROUP_ATPS_VIEW V,
872 MTL_ATP_RULES R,
873 MTL_SYSTEM_ITEMS I,
874 MTL_PARAMETERS P,
875 BOM_CALENDAR_DATES C,
876 BOM_CALENDAR_DATES C1,
877 WIP_REPETITIVE_SCHEDULES WRS,
878 WIP_REPETITIVE_ITEMS WRI
879 WHERE V.ATP_GROUP_ID = P_GROUP_ID
880 AND V.AVAILABLE_TO_ATP = 1
881 AND V.ATP_RULE_ID = R.RULE_ID
882 AND WRI.ORGANIZATION_ID = V.ORGANIZATION_ID
883 AND WRI.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
884 AND R.INCLUDE_REP_WIP_RECEIPTS = 1
885 AND WRI.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
886 AND WRI.LINE_ID = WRS.LINE_ID
887 AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
888 AND WRS.STATUS_TYPE IN (1,3,4,6)
889 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
890 AND C1.CALENDAR_CODE=P.CALENDAR_CODE
891 AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
892 AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_COMPLETION_DATE)
893 AND C.CALENDAR_CODE=P.CALENDAR_CODE
894 AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
895 AND C.SEQ_NUM BETWEEN C1.NEXT_SEQ_NUM AND C1.NEXT_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
896 AND WRS.DAILY_PRODUCTION_RATE*LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS) > WRS.QUANTITY_COMPLETED
897 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
898 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
899 AND NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
900 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
901 NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
902 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
903 NULL, C.NEXT_SEQ_NUM,
904 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
905 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (
906 DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
907 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
908 2, I.CUM_MANUFACTURING_LEAD_TIME,
909 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
910 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1);
911
912 l_supply_rec REPSCHED_SUPPLY_CURSOR%ROWTYPE;
913 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
914 BEGIN
915
916 OPEN REPSCHED_SUPPLY_CURSOR;
917
918 LOOP
919 FETCH REPSCHED_SUPPLY_CURSOR INTO l_supply_rec;
920 EXIT WHEN REPSCHED_SUPPLY_CURSOR%NOTFOUND;
921
922 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
923 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
924 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
925 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
926 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
927 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
928 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
929 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
930 p_supply_table(j).organization_id := l_supply_rec.organization_id;
931
932 j := j+1;
933
934 END LOOP;
935
936 CLOSE REPSCHED_SUPPLY_CURSOR;
937
938 END Collect_RepSched_Supply;
939
940 PROCEDURE Collect_FlowSched_Supply(p_group_id IN NUMBER,
941 p_sys_seq_num IN NUMBER,
942 p_mrp_status IN NUMBER,
943 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
944 ERRBUF OUT NOCOPY VARCHAR2,
945 RETCODE OUT NOCOPY NUMBER)
946 IS
947 -- insert flow schedule supply information
948 CURSOR FLOWSCHED_SUPPLY_CURSOR IS
949 SELECT
950 1 reservation_type,
951 24 supply_demand_source_type,
952 5 txn_source_type_id,
953 D.WIP_ENTITY_ID supply_demand_source_id,
954 2 supply_demand_type,
955 DECODE(P_MRP_STATUS,
956 1,DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
957 1,DECODE(I.MRP_PLANNING_CODE,
958 4,NVL(D.MPS_NET_QUANTITY,0),
959 8,NVL(D.MPS_NET_QUANTITY,0),
960 D.PLANNED_QUANTITY),
961 D.PLANNED_QUANTITY - D.QUANTITY_COMPLETED), /* I missed something here */
962 D.PLANNED_QUANTITY - D.QUANTITY_COMPLETED) supply_demand_quantity,
966 FROM
963 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
964 V.INVENTORY_ITEM_ID inventory_item_id,
965 V.ORGANIZATION_ID organization_id
967 WIP_FLOW_SCHEDULES D,
968 BOM_CALENDAR_DATES C,
969 MTL_PARAMETERS P,
970 MTL_SYSTEM_ITEMS I,
971 MTL_ATP_RULES R,
972 MTL_GROUP_ITEM_ATPS_VIEW V
973 WHERE D.STATUS = 1
974 AND (D.PLANNED_QUANTITY-D.QUANTITY_COMPLETED) >0
975 AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
976 AND D.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
977 AND D.SCHEDULED_FLAG = 1
978 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, V.INVENTORY_ITEM_ID, -1)
979 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
980 AND V.AVAILABLE_TO_ATP = 1
981 AND V.ATP_RULE_ID = R.RULE_ID
982 AND V.ATP_GROUP_ID = P_GROUP_ID
983 AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
984 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
985 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
986 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
987 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
988 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
989 NULL, C.NEXT_SEQ_NUM,
990 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
991 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
992 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
993 2, I.CUM_MANUFACTURING_LEAD_TIME,
994 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
995 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
996 AND P.CALENDAR_CODE = C.CALENDAR_CODE
997 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
998 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE)
999 AND C.NEXT_SEQ_NUM >= P_SYS_SEQ_NUM;
1000
1001 l_supply_rec FLOWSCHED_SUPPLY_CURSOR%ROWTYPE;
1002 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
1003 BEGIN
1004
1005 OPEN FLOWSCHED_SUPPLY_CURSOR;
1006
1007 LOOP
1008 FETCH FLOWSCHED_SUPPLY_CURSOR INTO l_supply_rec;
1009 EXIT WHEN FLOWSCHED_SUPPLY_CURSOR%NOTFOUND;
1010
1011 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
1012 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
1013 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
1014 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
1015 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
1016 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
1017 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
1018 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
1019 p_supply_table(j).organization_id := l_supply_rec.organization_id;
1020
1021 j := j+1;
1022
1023 END LOOP;
1024
1025 CLOSE FLOWSCHED_SUPPLY_CURSOR;
1026
1027 END Collect_FlowSched_Supply;
1028
1029 --
1030 -- The rest of the procedures collect demand information.
1031 --
1032
1033 PROCEDURE Collect_DiscreteJob_Demand(p_group_id IN NUMBER,
1034 p_sys_seq_num IN NUMBER,
1035 p_mrp_status IN NUMBER,
1036 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1037 ERRBUF OUT NOCOPY VARCHAR2,
1038 RETCODE OUT NOCOPY NUMBER)
1039 IS
1040 -- insert wip discrete requirement information
1041
1042 CURSOR DISCRETEJOB_DEMAND_CURSOR IS
1043 SELECT
1044 1 reservation_type,
1045 DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
1046 5 txn_source_type_id,
1047 D.WIP_ENTITY_ID supply_demand_source_id,
1048 1 supply_demand_type,
1049 LEAST(-1*(O.REQUIRED_QUANTITY-O.QUANTITY_ISSUED),0) supply_demand_quantity,
1050 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1051 V.INVENTORY_ITEM_ID inventory_item_id,
1052 V.ORGANIZATION_ID organization_id
1053 FROM
1054 MTL_GROUP_ITEM_ATPS_VIEW V,
1055 MTL_PARAMETERS P,
1056 MTL_ATP_RULES R,
1057 MTL_SYSTEM_ITEMS I,
1058 BOM_CALENDAR_DATES C,
1059 WIP_REQUIREMENT_OPERATIONS O,
1060 WIP_DISCRETE_JOBS D,
1061 BOM_CALENDAR_DATES C1
1062 WHERE O.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
1063 AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
1064 AND O.WIP_SUPPLY_TYPE <> 6
1065 AND O.REQUIRED_QUANTITY > 0
1066 AND O.OPERATION_SEQ_NUM > 0
1067 AND O.WIP_ENTITY_ID=D.WIP_ENTITY_ID
1068 AND O.ORGANIZATION_ID=D.ORGANIZATION_ID
1069 AND O.DATE_REQUIRED >= c1.calendar_date
1070 AND ((D.JOB_TYPE= 1 AND R.INCLUDE_DISCRETE_WIP_DEMAND = 1)
1071 OR (D.JOB_TYPE = 3 AND R.INCLUDE_NONSTD_WIP_DEMAND = 1))
1072 AND D.STATUS_TYPE IN (1,3,4,6)
1073 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1074 AND V.AVAILABLE_TO_ATP = 1
1075 AND V.ATP_RULE_ID = R.RULE_ID
1076 AND V.ATP_GROUP_ID = P_GROUP_ID
1077 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1078 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1079 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1080 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
1081 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1082 AND C1.SEQ_NUM = greatest(1,P_SYS_SEQ_NUM-Nvl(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
1083 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1084 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1085 2, I.CUM_MANUFACTURING_LEAD_TIME,
1086 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1087 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1088 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1092 AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED)
1089 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1090 AND P.CALENDAR_CODE = C1.CALENDAR_CODE
1091 AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
1093 AND NOT EXISTS (SELECT 'exists in group?'
1094 FROM MTL_DEMAND_INTERFACE MDI1
1095 WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
1096 AND MDI1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
1097 AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1098 AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = D.WIP_ENTITY_ID);
1099
1100 l_demand_rec DISCRETEJOB_DEMAND_CURSOR%ROWTYPE;
1101 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1102 BEGIN
1103
1104 OPEN DISCRETEJOB_DEMAND_CURSOR;
1105
1106 LOOP
1107 FETCH DISCRETEJOB_DEMAND_CURSOR INTO l_demand_rec;
1108 EXIT WHEN DISCRETEJOB_DEMAND_CURSOR%NOTFOUND;
1109
1110 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1111 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1112 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1113 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1114 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1115 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1116 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1117 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1118 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1119
1120 j := j+1;
1121
1122 END LOOP;
1123
1124 CLOSE DISCRETEJOB_DEMAND_CURSOR;
1125
1126 END Collect_DiscreteJob_Demand;
1127
1128 PROCEDURE Collect_RepSched_Demand(p_group_id IN NUMBER,
1129 p_sys_seq_num IN NUMBER,
1130 p_mrp_status IN NUMBER,
1131 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1132 ERRBUF OUT NOCOPY VARCHAR2,
1133 RETCODE OUT NOCOPY NUMBER)
1134 IS
1135
1136 -- insert wip repetitive requirement information
1137 -- unlike inldsd.ppc, I combine DRJ1 and DRJ2
1138
1139 CURSOR REPSCHED_DEMAND_CURSOR IS
1140 SELECT
1141 1 reservation_type,
1142 4 supply_demand_source_type,
1143 5 txn_source_type_id,
1144 WRS.REPETITIVE_SCHEDULE_ID supply_demand_source_id,
1145 1 supply_demand_type,
1146 DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM)-WRO.QUANTITY_ISSUED),
1147 -1,-1*(WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*LEAST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS)-WRO.QUANTITY_ISSUED),
1148 GREATEST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM-WRS.PROCESSING_WORK_DAYS,-1)*WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY) supply_demand_quantity,
1149 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1150 V.INVENTORY_ITEM_ID inventory_item_id,
1151 V.ORGANIZATION_ID organization_id
1152 FROM
1153 MTL_GROUP_ITEM_ATPS_VIEW V,
1154 MTL_PARAMETERS P,
1155 MTL_ATP_RULES R,
1156 MTL_SYSTEM_ITEMS I,
1157 BOM_CALENDAR_DATES C,
1158 BOM_CALENDAR_DATES C1,
1159 WIP_REPETITIVE_SCHEDULES WRS,
1160 WIP_OPERATIONS WO,
1161 WIP_REQUIREMENT_OPERATIONS WRO
1162 WHERE WRO.ORGANIZATION_ID = V.ORGANIZATION_ID
1163 AND WRO.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
1164 AND V.AVAILABLE_TO_ATP = 1
1165 AND V.ATP_RULE_ID = R.RULE_ID
1166 AND V.ATP_GROUP_ID = P_GROUP_ID
1167 AND R.INCLUDE_REP_WIP_DEMAND = 1
1168 AND WRO.WIP_SUPPLY_TYPE <> 6
1169 AND WRO.REQUIRED_QUANTITY > 0
1170 AND WRO.OPERATION_SEQ_NUM > 0
1171 AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+)
1172 AND WRO.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID(+)
1173 AND WRO.ORGANIZATION_ID = WO.ORGANIZATION_ID(+)
1174 AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
1175 AND WRS.ORGANIZATION_ID = WRO.ORGANIZATION_ID
1176 AND WRS.STATUS_TYPE IN (1,3,4,6)
1177 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1178 AND C1.CALENDAR_CODE=P.CALENDAR_CODE
1179 AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
1180 AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_START_DATE)
1181 AND C.CALENDAR_CODE=P.CALENDAR_CODE
1182 AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
1183 AND C.SEQ_NUM BETWEEN C1.PRIOR_SEQ_NUM AND C1.PRIOR_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
1184 AND WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*LEAST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS)>WRO.QUANTITY_ISSUED
1185 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1186 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1187 AND NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1188 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
1189 NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1190 AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1191 NULL, C.PRIOR_SEQ_NUM,
1192 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
1193 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1194 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1195 2, I.CUM_MANUFACTURING_LEAD_TIME,
1196 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1197 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1198 AND NOT EXISTS (SELECT 'exists in group?'
1199 FROM MTL_DEMAND_INTERFACE MDI1
1200 WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
1201 AND MDI1.ORGANIZATION_ID = V.ORGANIZATION_ID
1202 AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1203 AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = WRS.WIP_ENTITY_ID);
1204
1205
1206 l_demand_rec REPSCHED_DEMAND_CURSOR%ROWTYPE;
1207 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1208 BEGIN
1209
1213 FETCH REPSCHED_DEMAND_CURSOR INTO l_demand_rec;
1210 OPEN REPSCHED_DEMAND_CURSOR;
1211
1212 LOOP
1214 EXIT WHEN REPSCHED_DEMAND_CURSOR%NOTFOUND;
1215
1216 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1217 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1218 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1219 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1220 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1221 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1222 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1223 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1224 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1225
1226 j := j+1;
1227
1228 END LOOP;
1229
1230 CLOSE REPSCHED_DEMAND_CURSOR;
1231
1232 END Collect_RepSched_Demand;
1233
1234 PROCEDURE Collect_User_Demand(p_group_id IN NUMBER,
1235 p_sys_seq_num IN NUMBER,
1236 p_mrp_status IN NUMBER,
1237 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1238 ERRBUF OUT NOCOPY VARCHAR2,
1239 RETCODE OUT NOCOPY NUMBER)
1240 IS
1241
1242 -- insert user defined demand information
1243 CURSOR USER_DEMAND_CURSOR IS
1244 SELECT
1245 1 reservation_type,
1246 17 supply_demand_source_type,
1247 U.SOURCE_TYPE_ID txn_source_type_id,
1248 U.SOURCE_ID supply_demand_source_id,
1249 1 supply_demand_type,
1250 -1*U.PRIMARY_UOM_QUANTITY supply_demand_quantity,
1251 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1252 V.INVENTORY_ITEM_ID inventory_item_id,
1253 V.ORGANIZATION_ID organization_id
1254 FROM
1255 MTL_GROUP_ITEM_ATPS_VIEW V,
1256 MTL_PARAMETERS P,
1257 MTL_ATP_RULES R,
1258 MTL_SYSTEM_ITEMS I,
1259 MTL_USER_DEMAND U,
1260 BOM_CALENDAR_DATES C
1261 WHERE U.ORGANIZATION_ID = V.ORGANIZATION_ID
1262 AND U.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1263 AND V.AVAILABLE_TO_ATP = 1
1264 AND V.ATP_RULE_ID = R.RULE_ID
1265 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_USER_DEFINED_DEMAND, 2, -1, V.INVENTORY_ITEM_ID)
1266 AND V.ATP_GROUP_ID = P_GROUP_ID
1267 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1268 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1269 AND NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1270 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1271 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1272 AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1273 NULL, C.PRIOR_SEQ_NUM,
1274 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
1275 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1276 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1277 2, I.CUM_MANUFACTURING_LEAD_TIME,
1278 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1279 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1280 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1281 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1282 AND C.CALENDAR_DATE = TRUNC(U.REQUIREMENT_DATE);
1283
1284 l_demand_rec USER_DEMAND_CURSOR%ROWTYPE;
1285 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1286 BEGIN
1287
1288 OPEN USER_DEMAND_CURSOR;
1289
1290 LOOP
1291 FETCH USER_DEMAND_CURSOR INTO l_demand_rec;
1292 EXIT WHEN USER_DEMAND_CURSOR%NOTFOUND;
1293
1294 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1295 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1296 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1297 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1298 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1299 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1300 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1301 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1302 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1303
1304 j := j+1;
1305
1306 END LOOP;
1307
1308 CLOSE USER_DEMAND_CURSOR;
1309
1310 END Collect_User_Demand;
1311
1312 PROCEDURE Collect_FlowSched_Demand(p_group_id IN NUMBER,
1313 p_sys_seq_num IN NUMBER,
1314 p_mrp_status IN NUMBER,
1315 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1316 ERRBUF OUT NOCOPY VARCHAR2,
1317 RETCODE OUT NOCOPY NUMBER)
1318 IS
1319
1320 -- insert wip flow schedule demand information
1321 -- haven't added the logic to explode phantom
1322
1323 CURSOR FLOWSCHED_DEMAND_CURSOR IS
1324 SELECT
1325 1 reservation_type,
1326 24 supply_demand_source_type,
1327 5 txn_source_type_id,
1328 F.WIP_ENTITY_ID supply_demand_source_id,
1329 1 supply_demand_type,
1330 F.PLANNED_QUANTITY-F.QUANTITY_COMPLETED schedule_quantity,
1331 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1332 V.INVENTORY_ITEM_ID inventory_item_id,
1333 V.ORGANIZATION_ID organization_id,
1334 F.primary_item_id assembly_item_id,
1335 F.bom_revision_date bom_revision_date,
1336 F.alternate_bom_designator alternate_bom_designator
1337 FROM WIP_FLOW_SCHEDULES F,
1338 BOM_BILL_OF_MATERIALS BOM ,
1342 MTL_SYSTEM_ITEMS I,
1339 BOM_EXPLOSIONS BE ,
1340 BOM_CALENDAR_DATES C,
1341 MTL_PARAMETERS P,
1343 MTL_ATP_RULES R,
1344 MTL_GROUP_ITEM_ATPS_VIEW V
1345 WHERE V.AVAILABLE_TO_ATP = 1
1346 AND V.ATP_RULE_ID = R.RULE_ID
1347 AND V.ATP_GROUP_ID = P_GROUP_ID
1348 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND, 1,
1349 V.INVENTORY_ITEM_ID, -1)
1350 AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
1351 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1352 AND BE.COMPONENT_ITEM_ID = V.INVENTORY_ITEM_ID
1353 AND BE.ORGANIZATION_ID = V.ORGANIZATION_ID
1354 AND BE.EXPLOSION_TYPE = 'ALL'
1355 AND BE.EXTENDED_QUANTITY > 0
1356 AND BE.COMPONENT_ITEM_ID <> BE.TOP_ITEM_ID
1357 AND BOM.COMMON_BILL_SEQUENCE_ID = BE.TOP_BILL_SEQUENCE_ID
1358 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
1359 AND TRUNC(BE.EFFECTIVITY_DATE) <= TRUNC(F.SCHEDULED_COMPLETION_DATE)
1360 AND TRUNC(BE.DISABLE_DATE) > TRUNC(F.SCHEDULED_COMPLETION_DATE)
1361 AND F.PRIMARY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
1362 AND F.ORGANIZATION_ID = BOM.ORGANIZATION_ID
1363 AND F.STATUS = 1
1364 AND F.SCHEDULED_FLAG = 1
1365 AND (F.PLANNED_QUANTITY - F.QUANTITY_COMPLETED) >0
1366 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1367 AND NVL(F.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1368 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
1369 NVL(F.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1370 AND C.CALENDAR_CODE = P.CALENDAR_CODE
1371 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
1372 AND C.CALENDAR_DATE = TRUNC(F.SCHEDULED_COMPLETION_DATE)
1373 AND C.PRIOR_SEQ_NUM >= P_SYS_SEQ_NUM
1374 AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1375 NULL, C.PRIOR_SEQ_NUM,
1376 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
1377 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1378 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1379 2, I.CUM_MANUFACTURING_LEAD_TIME,
1380 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1381 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1382 AND NOT EXISTS (SELECT 'exists in group?'
1383 FROM MTL_DEMAND_INTERFACE MDI1
1384 WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
1385 AND MDI1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
1386 AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1387 AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = F.WIP_ENTITY_ID);
1388
1389 l_demand_rec FLOWSCHED_DEMAND_CURSOR%ROWTYPE;
1390 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1391 l_qty NUMBER;
1392 l_err_code NUMBER;
1393 l_err_msg VARCHAR2(100);
1394 BEGIN
1395 OPEN FLOWSCHED_DEMAND_CURSOR;
1396
1397 LOOP
1398 FETCH FLOWSCHED_DEMAND_CURSOR INTO l_demand_rec;
1399 EXIT WHEN FLOWSCHED_DEMAND_CURSOR%NOTFOUND;
1400
1401 Get_Component_Qty(l_demand_rec.assembly_item_id,
1402 l_demand_rec.organization_id,
1403 l_demand_rec.alternate_bom_designator,
1404 l_demand_rec.inventory_item_id,
1405 l_demand_rec.bom_revision_date,
1406 l_demand_rec.schedule_quantity,
1407 l_qty,
1408 l_err_code,
1409 l_err_msg);
1410 if (l_qty > 0) then
1411 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1412 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1413 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1414 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1415 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1416 p_demand_table(j).supply_demand_quantity := -l_qty;
1417 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1418 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1419 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1420 j := j+1;
1421 end if;
1422
1423
1424 END LOOP;
1425
1426 CLOSE FLOWSCHED_DEMAND_CURSOR;
1427
1428 END Collect_FlowSched_Demand;
1429
1430 PROCEDURE Collect_SalesOrder_Demand(p_group_id IN NUMBER,
1431 p_sys_seq_num IN NUMBER,
1432 p_mrp_status IN NUMBER,
1433 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1434 ERRBUF OUT NOCOPY VARCHAR2,
1435 RETCODE OUT NOCOPY NUMBER)
1436 IS
1437
1438 -- insert sales order demand
1439 CURSOR OE_SALESORDER_DEMAND_CURSOR IS
1440 SELECT D.RESERVATION_TYPE reservation_type,
1441 DECODE(D.DEMAND_SOURCE_TYPE,
1442 2, DECODE(D.RESERVATION_TYPE,1,2,3,23,9),
1443 8, DECODE(D.RESERVATION_TYPE,1,21,22),D.DEMAND_SOURCE_TYPE) supply_demand_source_type,
1444 DECODE(D.DEMAND_SOURCE_TYPE,
1445 8,2,D.DEMAND_SOURCE_TYPE) txn_source_type_id,
1446 D.DEMAND_SOURCE_HEADER_ID supply_demand_source_id,
1447 1 supply_demand_type,
1448 -1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)) supply_demand_quantity,
1449 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1450 V.INVENTORY_ITEM_ID inventory_item_id,
1451 V.ORGANIZATION_ID organization_id
1452 FROM
1453 MTL_GROUP_ITEM_ATPS_VIEW V,
1454 MTL_PARAMETERS P,
1455 MTL_SYSTEM_ITEMS I,
1456 MTL_ATP_RULES R,
1457 BOM_CALENDAR_DATES C,
1458 MTL_DEMAND D,
1459 BOM_CALENDAR_DATES C1
1460 WHERE D.ORGANIZATION_ID = V.ORGANIZATION_ID
1461 AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)
1462 AND D.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1463 AND D.AVAILABLE_TO_ATP = 1
1467 AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS, 2, 8, -1)
1464 AND D.RESERVATION_TYPE <> DECODE(NVL(V.N_COLUMN1,R.INCLUDE_ONHAND_AVAILABLE), 2, 2, -1)
1465 AND D.RESERVATION_TYPE <> DECODE(R.DEMAND_CLASS_ATP_FLAG, 1, 2, -1)
1466 AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS, 2, 2, -1)
1468 AND (D.SUBINVENTORY IS NULL
1469 OR D.SUBINVENTORY IN (SELECT S.SECONDARY_INVENTORY_NAME
1470 FROM MTL_SECONDARY_INVENTORIES S
1471 WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
1472 AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
1473 AND S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)))
1474 AND V.AVAILABLE_TO_ATP = 1
1475 AND V.ATP_RULE_ID = R.RULE_ID
1476 AND V.ATP_GROUP_ID = P_GROUP_ID
1477 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1478 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1479 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1480 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1481 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1482 AND C1.SEQ_NUM = greatest(1, P_SYS_SEQ_NUM - Nvl(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
1483 AND D.REQUIREMENT_DATE >= C1.CALENDAR_DATE
1484 AND D.RESERVATION_TYPE <> 2
1485 AND C.PRIOR_SEQ_NUM < DECODE(D.RESERVATION_TYPE,
1486 2,C.PRIOR_SEQ_NUM+1,
1487 NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1488 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1489 2, I.CUM_MANUFACTURING_LEAD_TIME,
1490 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1491 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1))
1492 AND NOT EXISTS
1493 (SELECT 'exists in group?'
1494 FROM MTL_GROUP_ATPS_VIEW V1
1495 WHERE V1.ATP_GROUP_ID = P_GROUP_ID
1496 AND V1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
1497 AND V1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1498 AND V1.AVAILABLE_TO_ATP = 1
1499 AND NVL(V1.DEMAND_SOURCE_TYPE, -1) = D.DEMAND_SOURCE_TYPE
1500 AND NVL(V1.DEMAND_SOURCE_HEADER_ID, -1) = D.DEMAND_SOURCE_HEADER_ID
1501 AND NVL(V1.DEMAND_SOURCE_LINE, '@@@') = NVL(D.DEMAND_SOURCE_LINE, '@@@')
1502 AND NVL(V1.DEMAND_SOURCE_DELIVERY, '@@@') = NVL(D.DEMAND_SOURCE_DELIVERY, '@@@')
1503 AND NVL(V1.DEMAND_SOURCE_NAME, '@@@') = NVL(D.DEMAND_SOURCE_NAME, '@@@'))
1504 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1505 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1506 AND P.CALENDAR_CODE = C1.CALENDAR_CODE
1507 AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
1508 AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
1509 AND V.INVENTORY_ITEM_ID=DECODE(D.RESERVATION_TYPE,
1510 1,DECODE(D.PARENT_DEMAND_ID, NULL,V.INVENTORY_ITEM_ID,-1),
1511 2,V.INVENTORY_ITEM_ID,
1512 3,DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS,
1513 1,V.INVENTORY_ITEM_ID,
1514 DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
1515 1,V.INVENTORY_ITEM_ID, -1)),-1)
1516 AND V.INVENTORY_ITEM_ID=
1517 DECODE(R.INCLUDE_SALES_ORDERS, 2,
1518 DECODE(R.INCLUDE_INTERNAL_ORDERS, 2,
1519 DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2,
1520 DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 2,
1521 DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 2, -1,
1522 V.INVENTORY_ITEM_ID),
1523 V.INVENTORY_ITEM_ID),
1524 V.INVENTORY_ITEM_ID),
1525 V.INVENTORY_ITEM_ID),
1526 V.INVENTORY_ITEM_ID);
1527
1528
1529 CURSOR ONT_SALESORDER_DEMAND_CURSOR IS
1530 SELECT
1531 1 reservation_type, -- fake
1532 2 supply_demand_source_type,
1533 2 txn_source_type_id, -- fake
1534 L.LINE_ID supply_demand_source_id,
1535 1 supply_demand_type,
1536 -1*(L.ORDERED_QUANTITY-NVL(SHIPPED_QUANTITY, 0)) supply_demand_quantity,
1537 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1538 I.INVENTORY_ITEM_ID inventory_item_id,
1539 I.ORGANIZATION_ID organization_id
1540 FROM BOM_CALENDAR_DATES C ,
1541 OE_ORDER_LINES L,
1542 MTL_ATP_RULES R ,
1543 /* MTL_GROUP_ATPS_VIEW G , */
1544 MTL_DEMAND_INTERFACE G, /* use the table directly - perf bug 4899603 */
1545 MTL_PARAMETERS P ,
1546 MTL_SYSTEM_ITEMS I
1547 WHERE I.ATP_FLAG in ('C', 'Y')
1548 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
1549 AND G.ATP_GROUP_ID = p_group_id
1550 AND G.ORGANIZATION_ID = I.ORGANIZATION_ID
1551 AND G.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1552 AND G.ATP_RULE_ID = R.RULE_ID
1553 AND R.INCLUDE_SALES_ORDERS = 1
1554 AND L.SHIP_FROM_ORG_ID = I.ORGANIZATION_ID
1555 AND L.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1556 AND L.VISIBLE_DEMAND_FLAG = 'Y'
1557 AND L.ORDERED_QUANTITY > NVL(L.SHIPPED_QUANTITY,0)
1558 AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1559 NULL, C.PRIOR_DATE,
1560 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID,
1561 1,
1562 SYSDATE,
1563 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
1564 AND C.CALENDAR_CODE = P.CALENDAR_CODE
1565 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
1566 AND C.CALENDAR_DATE = TRUNC(L.SCHEDULE_SHIP_DATE);
1567
1568
1569 l_oe_demand_rec OE_SALESORDER_DEMAND_CURSOR%ROWTYPE;
1570 l_ont_demand_rec ONT_SALESORDER_DEMAND_CURSOR%ROWTYPE;
1571 l_oe_install VARCHAR2(10);
1572 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1573 BEGIN
1574
1575 SELECT OE_INSTALL.Get_Active_Product
1576 INTO l_oe_install
1577 FROM DUAL;
1578
1579 IF l_oe_install = 'OE' THEN
1580 OPEN OE_SALESORDER_DEMAND_CURSOR;
1581
1582 LOOP
1583 FETCH OE_SALESORDER_DEMAND_CURSOR INTO l_oe_demand_rec;
1584 EXIT WHEN OE_SALESORDER_DEMAND_CURSOR%NOTFOUND;
1585
1589 p_demand_table(j).supply_demand_source_id := l_oe_demand_rec.supply_demand_source_id;
1586 p_demand_table(j).reservation_type := l_oe_demand_rec.reservation_type;
1587 p_demand_table(j).supply_demand_source_type := l_oe_demand_rec.supply_demand_source_type;
1588 p_demand_table(j).txn_source_type_id := l_oe_demand_rec.txn_source_type_id;
1590 p_demand_table(j).supply_demand_type := l_oe_demand_rec.supply_demand_type;
1591 p_demand_table(j).supply_demand_quantity := l_oe_demand_rec.supply_demand_quantity;
1592 p_demand_table(j).supply_demand_date := l_oe_demand_rec.supply_demand_date;
1593 p_demand_table(j).inventory_item_id := l_oe_demand_rec.inventory_item_id;
1594 p_demand_table(j).organization_id := l_oe_demand_rec.organization_id;
1595
1596 j := j+1;
1597
1598 END LOOP;
1599
1600 CLOSE OE_SALESORDER_DEMAND_CURSOR;
1601
1602 ELSE
1603
1604 OPEN ONT_SALESORDER_DEMAND_CURSOR;
1605
1606 LOOP
1607 FETCH ONT_SALESORDER_DEMAND_CURSOR INTO l_ont_demand_rec;
1608 EXIT WHEN ONT_SALESORDER_DEMAND_CURSOR%NOTFOUND;
1609
1610 p_demand_table(j).reservation_type := l_ont_demand_rec.reservation_type;
1611 p_demand_table(j).supply_demand_source_type := l_ont_demand_rec.supply_demand_source_type;
1612 p_demand_table(j).txn_source_type_id := l_ont_demand_rec.txn_source_type_id;
1613 p_demand_table(j).supply_demand_source_id := l_ont_demand_rec.supply_demand_source_id;
1614 p_demand_table(j).supply_demand_type := l_ont_demand_rec.supply_demand_type;
1615 p_demand_table(j).supply_demand_quantity := l_ont_demand_rec.supply_demand_quantity;
1616 p_demand_table(j).supply_demand_date := l_ont_demand_rec.supply_demand_date;
1617 p_demand_table(j).inventory_item_id := l_ont_demand_rec.inventory_item_id;
1618 p_demand_table(j).organization_id := l_ont_demand_rec.organization_id;
1619
1620 j := j+1;
1621
1622 END LOOP;
1623
1624 CLOSE ONT_SALESORDER_DEMAND_CURSOR;
1625 END IF;
1626
1627 END Collect_SalesOrder_Demand;
1628
1629 /*
1630 To get component quantity of given component for a given bill.
1631 */
1632 PROCEDURE Get_Component_Qty(p_assembly_item_id IN NUMBER,
1633 p_organization_id IN NUMBER,
1634 p_alternate_bom_designator IN VARCHAR,
1635 p_component_item_id IN NUMBER,
1636 p_date IN DATE DEFAULT sysdate,
1637 p_assembly_qty IN NUMBER DEFAULT 1,
1638 x_qty OUT NOCOPY NUMBER,
1639 x_err_code OUT NOCOPY NUMBER,
1640 x_err_msg OUT NOCOPY VARCHAR
1641 ) IS
1642 l_ext_qty NUMBER := 0;
1643 l_qty NUMBER := 0;
1644 l_basis_type NUMBER := WIP_CONSTANTS.ITEM_BASED_MTL;
1645 l_top_bill_sequence_id NUMBER := 0;
1646 l_cnt NUMBER := 0;
1647 l_wip_supply_type NUMBER;
1648 l_sort_order VARCHAR2(2000);
1649 l_sort_order_len NUMBER := BOM_COMMON_DEFINITIONS.G_Bom_SortCode_Width;
1650 l_total_qty NUMBER := 0;
1651
1652 CURSOR find_comps (l_top_bill_seq_id NUMBER) IS
1653 SELECT sort_order
1654 FROM bom_explosions be
1655 WHERE be.top_bill_sequence_id = l_top_bill_seq_id
1656 and be.organization_id = p_organization_id
1657 and be.component_item_id = p_component_item_id
1658 and be.explosion_type = 'ALL'
1659 and be.component_item_id <> be.top_item_id
1660 and trunc(be.effectivity_date) <= trunc(nvl(p_date,sysdate))
1661 and trunc(be.disable_date) > trunc(nvl(p_date,sysdate));
1662 Begin
1663
1664 x_err_code := 0;
1665
1666 if (p_alternate_bom_designator is not null) then
1667 select common_bill_sequence_id
1668 into l_top_bill_sequence_id
1669 from bom_bill_of_materials
1670 where assembly_item_id = p_assembly_item_id
1671 and organization_id = p_organization_id
1672 and alternate_bom_designator = p_alternate_bom_designator;
1673 else
1674 select common_bill_sequence_id
1675 into l_top_bill_sequence_id
1676 from bom_bill_of_materials
1677 where assembly_item_id = p_assembly_item_id
1678 and organization_id = p_organization_id
1679 and alternate_bom_designator is NULL;
1680 end if;
1681
1682 OPEN find_comps(l_top_bill_sequence_id);
1683 LOOP
1684 FETCH find_comps INTO l_sort_order;
1685 EXIT WHEN find_comps%NOTFOUND;
1686 l_cnt := 0;
1687 l_ext_qty := 1;
1688 WHILE (length(l_sort_order) > l_sort_order_len) LOOP
1689 select NVL(bic.wip_supply_type,WIP_CONSTANTS.PUSH), NVL(bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),be.component_quantity
1690 into l_wip_supply_type,l_basis_type,l_qty
1691 from bom_explosions be, bom_inventory_components bic
1692 where be.top_bill_sequence_id = l_top_bill_sequence_id
1693 and be.explosion_type = 'ALL'
1694 and be.sort_order = l_sort_order
1695 and be.component_sequence_id = bic.component_sequence_id;
1696 l_ext_qty := l_ext_qty * l_qty;
1697 if ((l_cnt <> 0) and (l_wip_supply_type <> WIP_CONSTANTS.PHANTOM)) then
1698 l_ext_qty := 0;
1699 exit;
1700 end if;
1701 if (l_basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
1702 exit;
1703 end if;
1704 l_cnt := l_cnt+1;
1705
1706 /* Get the parent sort_order */
1707 l_sort_order := SUBSTR(l_sort_order,0,length(l_sort_order)-l_sort_order_len);
1708 END LOOP;
1709 if (l_basis_type = WIP_CONSTANTS.ITEM_BASED_MTL) then
1710 l_ext_qty := l_ext_qty * nvl(p_assembly_qty,1);
1711 end if;
1712 l_total_qty := l_total_qty + l_ext_qty;
1716
1713 END LOOP;
1714
1715 x_qty := l_total_qty;
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 x_err_msg := 'Unexpected SQL Error: '||sqlerrm;
1720 x_err_code := -1;
1721 x_qty := -1;
1722
1723 End Get_Component_Qty;
1724
1725 /*
1726 Procedure to read the component availabilty.
1727 This function finds out the demand and supply for certain item.
1728 Then it will uses that data to find out the supply/demand picture for given
1729 date range.
1730 It will take the lowest point on the supply/picture as the component availability.
1731 */
1732 PROCEDURE read_comp_avail(p_item_id_tbl IN Number_Tbl_Type,
1733 p_org_id IN NUMBER,
1734 p_from_date DATE,
1735 p_to_date DATE,
1736 p_ignore_line_id_tbl IN Number_Tbl_Type,
1737 x_qty_tbl OUT NOCOPY Number_Tbl_Type,
1738 x_return_status OUT NOCOPY VARCHAR2,
1739 x_msg_count OUT NOCOPY NUMBER,
1740 x_msg_data OUT NOCOPY VARCHAR2)
1741 IS
1742 l_rule_id NUMBER;
1743 l_group_id NUMBER;
1744 l_item_id NUMBER;
1745 l_sys_seq_num NUMBER;
1746 l_inf_fence_ind NUMBER;
1747 l_sd_size NUMBER;
1748 l_rows_fetched NUMBER;
1749 l_sd_reservation_type FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1750 l_sd_src_type FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1751 l_sd_txn_type FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1752 l_sd_src_id FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1753 l_sd_type FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1754 l_sd_qty FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1755 l_sd_requirement_date FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1756 l_sd_inventory_item_id FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1757 l_sd_org_id FLM_SUPPLY_DEMAND.Number_Tbl_Type;
1758 l_debug_line NUMBER;
1759 l_from_date_j NUMBER;
1760
1761 l_cur_index NUMBER;
1762 l_cur_item_index NUMBER := 1;
1763 l_temp_tbl Supply_Demand_Tbl_Type;
1764 l_running_qty NUMBER := 0;
1765 l_running_min_qty NUMBER := 0;
1766 l_include_qty NUMBER := 0;
1767 l_line_id NUMBER;
1768
1769 MAX_QTY NUMBER := 10E10;
1770
1771 BEGIN
1772 SAVEPOINT flm_read_comp_avail;
1773 l_debug_line := 10;
1774 x_return_status := FND_API.G_RET_STS_SUCCESS;
1775
1776 -- Preparing the init data
1777 SELECT MTL_ATP_RULES_S.NEXTVAL,MTL_SUPPLY_DEMAND_TEMP_S.NEXTVAL INTO l_rule_id,l_group_id from DUAL;
1778 DELETE FROM MTL_ATP_RULES WHERE RULE_NAME = 'FLM';
1779
1780 l_debug_line := 20;
1781
1782 SELECT C1.NEXT_SEQ_NUM, (C2.NEXT_SEQ_NUM - C1.NEXT_SEQ_NUM +1)
1783 INTO l_sys_seq_num, l_inf_fence_ind
1784 FROM BOM_CALENDAR_DATES C1, MTL_PARAMETERS P, BOM_CALENDAR_DATES C2
1785 WHERE P.ORGANIZATION_ID= p_org_id
1786 AND P.CALENDAR_CODE = C1.CALENDAR_CODE
1787 AND C1.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
1788 AND C1.CALENDAR_DATE = TRUNC(sysdate)
1789 AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
1790 AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
1791 AND C2.CALENDAR_DATE = p_to_date;
1792
1793 l_debug_line := 30;
1794 INSERT INTO MTL_ATP_RULES (
1795 RULE_ID,
1796 RULE_NAME,
1797 CREATED_BY,
1798 LAST_UPDATE_DATE,
1799 LAST_UPDATED_BY,
1800 CREATION_DATE,
1801 PAST_DUE_DEMAND_CUTOFF_FENCE,
1802 PAST_DUE_SUPPLY_CUTOFF_FENCE,
1803 INFINITE_SUPPLY_TIME_FENCE,
1804 INFINITE_SUPPLY_FENCE_CODE,
1805 DEMAND_CLASS_ATP_FLAG,
1806 INCLUDE_REP_MPS,
1807 INCLUDE_ONHAND_AVAILABLE,
1808 ACCUMULATE_AVAILABLE_FLAG,
1809 FORWARD_CONSUMPTION_FLAG,
1810 BACKWARD_CONSUMPTION_FLAG,
1811 INCLUDE_SALES_ORDERS,
1812 INCLUDE_INTERNAL_ORDERS,
1813 INCLUDE_DISCRETE_WIP_DEMAND,
1814 INCLUDE_REP_WIP_DEMAND,
1815 INCLUDE_NONSTD_WIP_DEMAND,
1816 INCLUDE_DISCRETE_MPS,
1817 INCLUDE_USER_DEFINED_DEMAND,
1818 INCLUDE_PURCHASE_ORDERS,
1819 INCLUDE_INTERNAL_REQS,
1820 INCLUDE_VENDOR_REQS,
1821 INCLUDE_DISCRETE_WIP_RECEIPTS,
1822 INCLUDE_REP_WIP_RECEIPTS,
1823 INCLUDE_NONSTD_WIP_RECEIPTS,
1824 INCLUDE_INTERORG_TRANSFERS,
1825 INCLUDE_USER_DEFINED_SUPPLY,
1826 INCLUDE_FLOW_SCHEDULE_DEMAND,
1827 INCLUDE_FLOW_SCHEDULE_RECEIPTS,
1828 DEFAULT_ATP_SOURCES)
1829 VALUES (
1830 l_rule_id, -- RULE_ID
1831 'FLM', -- RULE_NAME
1832 0, -- CREATED_BY
1833 SYSDATE, -- LAST_UPDATE_DATE
1834 0, -- LAST_UPDATED_BY
1835 SYSDATE, -- CREATION_DATE
1836 0, -- PAST_DUE_DEMAND_CUTOFF_FENCE
1837 0, -- PAST_DUE_SUPPLY_CUTOFF_FENCE
1838 l_inf_fence_ind, -- INFINITE_SUPPLY_TIME_FENCE
1839 4, -- INFINITE_SUPPLY_FENCE_CODE
1840 2, -- DEMAND_CLASS_ATP_FLAG
1841 2, -- INCLUDE_REP_MPS
1842 1, -- INCLUDE_ONHAND_AVAILABLE
1843 1, -- ACCUMULATE_AVAILABLE_FLAG
1844 1, -- FORWARD_CONSUMPTION_FLAG
1845 1, -- BACKWARD_CONSUMPTION_FLAG
1846 1, -- INCLUDE_SALES_ORDERS
1847 1, -- INCLUDE_INTERNAL_ORDERS
1848 1, -- INCLUDE_DISCRETE_WIP_DEMAND
1849 1, -- INCLUDE_REP_WIP_DEMAND
1850 1, -- INCLUDE_NONSTD_WIP_DEMAND
1851 2, -- INCLUDE_DISCRETE_MPS
1855 2, -- INCLUDE_VENDOR_REQS
1852 1, -- INCLUDE_USER_DEFINED_DEMAND
1853 1, -- INCLUDE_PURCHASE_ORDERS
1854 2, -- INCLUDE_INTERNAL_REQS
1856 1, -- INCLUDE_DISCRETE_WIP_RECEIPTS
1857 1, -- INCLUDE_REP_WIP_RECEIPTS
1858 1, -- INCLUDE_NONSTD_WIP_RECEIPTS
1859 1, -- INCLUDE_INTERORG_TRANSFERS
1860 1, -- INCLUDE_USER_DEFINED_SUPPLY
1861 1, -- INCLUDE_FLOW_SCHEDULE_DEMAND
1862 1, -- INCLUDE_FLOW_SCHEDULE_RECEIPTS
1863 3 -- DEFAULT_ATP_SOURCES (all subinventories)
1864 );
1865
1866 l_debug_line := 40;
1867 FOR item_index in 1..p_item_id_tbl.count LOOP
1868 INSERT INTO MTL_GROUP_ATPS_VIEW (
1869 ATP_GROUP_ID,
1870 ORGANIZATION_ID,
1871 INVENTORY_ITEM_ID,
1872 LAST_UPDATE_DATE,
1873 LAST_UPDATED_BY,
1874 CREATION_DATE,
1875 CREATED_BY,
1876 ATP_RULE_ID,
1877 REQUEST_QUANTITY,
1878 UOM_CODE,
1879 AVAILABLE_TO_ATP,
1880 N_COLUMN1)
1881 VALUES (
1882 l_group_id,
1883 p_org_id,
1884 p_item_id_tbl(item_index),
1885 SYSDATE,
1886 0,
1887 SYSDATE,
1888 0,
1889 l_rule_id,
1890 0,
1891 'SD',
1892 1,
1893 -1);
1894 END LOOP;
1895 l_debug_line := 50;
1896 l_sd_size := FLM_SUPPLY_DEMAND.Collect_Supply_Demand_Info(p_group_id => l_group_id,
1897 p_sys_seq_num => l_sys_seq_num,
1898 p_mrp_status => 1);
1899 l_debug_line := 60;
1900 FLM_SUPPLY_DEMAND.Get_Supply_Demand_Info(p_starting_index => 1,
1901 p_ending_index => l_sd_size,
1902 x_rows_fetched => l_rows_fetched,
1903 x_reservation_type_tbl => l_sd_reservation_type,
1904 x_supply_demand_src_type_tbl => l_sd_src_type,
1905 x_txn_source_type_id_tbl => l_sd_txn_type,
1906 x_supply_demand_source_id_tbl => l_sd_src_id,
1907 x_supply_demand_type_tbl => l_sd_type,
1908 x_supply_demand_quantity_tbl => l_sd_qty,
1909 x_supply_demand_date_tbl => l_sd_requirement_date,
1910 x_inventory_item_id_tbl => l_sd_inventory_item_id,
1911 x_organization_id_tbl => l_sd_org_id);
1912 l_from_date_j := to_number(to_char(p_from_date,'J'));
1913 l_debug_line := 70;
1914 FOR item_index IN 1..p_item_id_tbl.count LOOP
1915 -- To sort results in the increasing order of the demand date.
1916 -- The result of the sort will be per item stored in l_temp_tbl.
1917 FOR i IN 1..l_sd_inventory_item_id.count LOOP
1918 IF (l_sd_inventory_item_id(i) = p_item_id_tbl(item_index) ) THEN
1919 FOR j IN REVERSE 0..l_temp_tbl.count LOOP
1920 l_cur_index := j;
1921 IF ((j = 0) OR (l_sd_requirement_date(i) > l_temp_tbl(j).supply_demand_date)) THEN
1922 EXIT;
1923 END IF;
1924 END LOOP;
1925
1926 FOR k IN REVERSE (l_cur_index+1)..l_temp_tbl.count LOOP
1927 l_temp_tbl(k+1) := l_temp_tbl(k);
1928 END LOOP;
1929 l_temp_tbl(l_cur_index+1).inventory_item_id := l_sd_inventory_item_id(i);
1930 l_temp_tbl(l_cur_index+1).supply_demand_quantity := l_sd_qty(i);
1931 l_temp_tbl(l_cur_index+1).supply_demand_date := l_sd_requirement_date(i);
1932 l_temp_tbl(l_cur_index+1).supply_demand_source_type := l_sd_src_type(i);
1933 l_temp_tbl(l_cur_index+1).supply_demand_type := l_sd_type(i);
1934 l_temp_tbl(l_cur_index+1).supply_demand_source_id := l_sd_src_id(i);
1935 END IF;
1936 END LOOP;
1937
1938 l_debug_line := 80;
1939 l_running_min_qty := MAX_QTY;
1940 -- To find the minimum running quantity starting on the p_from_date.
1941 FOR i in 1..l_temp_tbl.count LOOP
1942 l_include_qty := 1;
1943
1944 -- This code is added to ignore demand coming from the flow schedule on the given line that
1945 -- lies in between from and start date.
1946 IF (l_temp_tbl(i).supply_demand_source_type = 24 AND l_temp_tbl(i).supply_demand_type = 1
1947 AND l_temp_tbl(i).supply_demand_date >= l_from_date_j) THEN
1948 select line_id
1949 into l_line_id
1950 from wip_flow_schedules
1951 where wip_entity_id = l_temp_tbl(i).supply_demand_source_id;
1952
1953 FOR j in 1..p_ignore_line_id_tbl.count LOOP
1954 IF l_line_id = p_ignore_line_id_tbl(j) THEN
1955 l_include_qty := 0;
1956 END IF;
1957 END LOOP;
1958
1959 END IF;
1960
1961 IF (l_include_qty = 1) THEN
1962 l_running_qty := l_running_qty + l_temp_tbl(i).supply_demand_quantity;
1963 IF (l_running_qty < l_running_min_qty AND l_temp_tbl(i).supply_demand_date >= l_from_date_j) THEN
1964 l_running_min_qty := l_running_qty;
1965 END IF;
1966 END IF;
1967 END LOOP;
1968 IF (l_running_min_qty = MAX_QTY) THEN
1969 l_running_min_qty := l_running_qty;
1970 END IF;
1971 x_qty_tbl(l_cur_item_index) := l_running_min_qty;
1972
1973 l_temp_tbl.delete;
1974 l_running_qty := 0;
1975 l_running_min_qty := 0;
1976 l_cur_item_index := l_cur_item_index + 1;
1977 END LOOP;
1978
1979 ROLLBACK TO flm_read_comp_avail;
1980
1981 EXCEPTION
1982
1983 WHEN OTHERS THEN
1984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1985 ROLLBACK TO flm_read_comp_avail;
1986
1987 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1988 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_reader_writer' ,'read_comp_avail('||l_debug_line||')');
1989 END IF;
1990
1991 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
1992
1993 return;
1994
1995 END read_comp_avail;
1996
1997
1998
1999 END FLM_SUPPLY_DEMAND;