[Home] [Help]
PACKAGE BODY: APPS.WPS_SUPPLY_DEMAND
Source
1 PACKAGE BODY WPS_SUPPLY_DEMAND AS
2 /* $Header: wpsmtsdb.pls 120.2 2006/03/09 11:17:31 mlouie 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,
54 p_org_id IN NUMBER) RETURN NUMBER
55 IS
56 l_err_buf VARCHAR2(2000);
57 l_ret_code NUMBER;
58 BEGIN
59
60 Clear_Supply_Demand_Info;
61
62 Collect_Supply_Demand_Info(p_group_id => p_group_id,
63 p_sys_seq_num => p_sys_seq_num,
64 p_mrp_status => p_mrp_status,
65 p_org_id => p_org_id,
66 p_sup_dem_table => g_supply_demand_table,
67 ERRBUF => l_err_buf,
68 RETCODE => l_ret_code);
69
70 RETURN g_supply_demand_table.COUNT;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 RETURN -1;
75
76 END Collect_Supply_Demand_Info;
77
78
79
80 PROCEDURE Get_Supply_Demand_Info(x_supply_demand_table OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE)
81 IS
82 BEGIN
83 x_supply_demand_table := g_supply_demand_table;
84
85 END Get_Supply_Demand_Info;
86
87
88 /*
89 This procedure exists only for the sake of Pro*C. At this point Pro*C allows only
90 arrays of primitives in pl/sql blocks, so this procedure essentially converts the
91 global "table of records" into a bunch of "table of numbers"
92 */
93 PROCEDURE Get_Supply_Demand_Info(p_starting_index IN NUMBER DEFAULT 1,
94 p_ending_index IN NUMBER DEFAULT -1,
95 x_rows_fetched OUT NOCOPY NUMBER,
96 x_reservation_type_tbl OUT NOCOPY Number_Tbl_Type,
97 x_supply_demand_src_type_tbl OUT NOCOPY Number_Tbl_Type,
98 x_txn_source_type_id_tbl OUT NOCOPY Number_Tbl_Type,
99 x_supply_demand_source_id_tbl OUT NOCOPY Number_Tbl_Type,
100 x_supply_demand_type_tbl OUT NOCOPY Number_Tbl_Type,
101 x_supply_demand_quantity_tbl OUT NOCOPY Number_Tbl_Type,
102 x_supply_demand_date_tbl OUT NOCOPY Number_Tbl_Type,
103 x_inventory_item_id_tbl OUT NOCOPY Number_Tbl_Type,
104 x_organization_id_tbl OUT NOCOPY Number_Tbl_Type)
105 IS
106 l_ending_index NUMBER;
107 l_starting_index NUMBER;
108 l_max_length NUMBER := g_supply_demand_table.COUNT;
109 BEGIN
110
111 x_rows_fetched := 0;
112
113 IF p_ending_index < 0 THEN
114 l_ending_index := l_max_length;
115 ELSIF p_ending_index > l_max_length THEN
116 l_ending_index := l_max_length;
117 ELSE
118 l_ending_index := p_ending_index;
119 END IF;
120
121 IF p_starting_index < 0 THEN
122 l_starting_index := 1;
123 ELSIF p_starting_index > l_max_length THEN
124 RETURN;
125 ELSE
126 l_starting_index := p_starting_index;
127 END IF;
128
129
130 FOR i IN l_starting_index..l_ending_index LOOP
131
132 x_reservation_type_tbl(x_rows_fetched+1) :=
133 Nvl(g_supply_demand_table(i).reservation_type,0);
134
135 x_supply_demand_src_type_tbl(x_rows_fetched+1) :=
136 Nvl(g_supply_demand_table(i).supply_demand_source_type,0);
137
138 x_txn_source_type_id_tbl(x_rows_fetched+1) :=
139 Nvl(g_supply_demand_table(i).txn_source_type_id,0);
140
141 x_supply_demand_source_id_tbl(x_rows_fetched+1) :=
142 Nvl(g_supply_demand_table(i).supply_demand_source_id,0);
143
144 x_supply_demand_type_tbl(x_rows_fetched+1) :=
145 Nvl(g_supply_demand_table(i).supply_demand_type,0);
146
147 x_supply_demand_quantity_tbl(x_rows_fetched+1) :=
148 Nvl(g_supply_demand_table(i).supply_demand_quantity,0);
149
150 x_supply_demand_date_tbl(x_rows_fetched+1) :=
151 Nvl(g_supply_demand_table(i).supply_demand_date,0);
152
153 x_inventory_item_id_tbl(x_rows_fetched+1) :=
154 Nvl(g_supply_demand_table(i).inventory_item_id,0);
155
156 x_organization_id_tbl(x_rows_fetched+1) :=
157 Nvl(g_supply_demand_table(i).organization_id,0);
158
159 x_rows_fetched := x_rows_fetched + 1;
160
161 END LOOP;
162
163 EXCEPTION
164 WHEN OTHERS THEN
165 NULL;
166
167 END Get_Supply_Demand_Info;
168
169
170
171 PROCEDURE Clear_Supply_Demand_Info
172 IS
173 BEGIN
174
175 g_supply_demand_table.delete;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 NULL;
180
181 END Clear_Supply_Demand_Info;
182
183
184 /*
185 This procedure calls two separate procedures, one to collect the supply information
186 and the other to collect the demand information. All the results are stored in a
187 global pl/sql table of records.
188 */
189 PROCEDURE Collect_Supply_Demand_Info(p_group_id IN NUMBER,
190 p_sys_seq_num IN NUMBER,
191 p_mrp_status IN NUMBER,
192 p_org_id IN NUMBER,
193 p_sup_dem_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
194 ERRBUF OUT NOCOPY VARCHAR2,
195 RETCODE OUT NOCOPY NUMBER)
196 IS
197
198 BEGIN
199
200 Collect_Supply_Info(p_group_id => p_group_id,
201 p_sys_seq_num => p_sys_seq_num,
202 p_mrp_status => p_mrp_status,
203 p_supply_table => p_sup_dem_table,
204 ERRBUF => ERRBUF,
205 RETCODE => RETCODE);
206
207 Collect_Demand_Info(p_group_id => p_group_id,
208 p_sys_seq_num => p_sys_seq_num,
209 p_mrp_status => p_mrp_status,
210 p_org_id => p_org_id,
211 p_demand_table => p_sup_dem_table,
212 ERRBUF => ERRBUF,
213 RETCODE => RETCODE);
214
215 END Collect_Supply_Demand_Info;
216
217
218 /*
219 This procedure is a wrapper to collect all the individual supply types.
220 */
221 PROCEDURE Collect_Supply_Info(p_group_id IN NUMBER,
222 p_sys_seq_num IN NUMBER,
223 p_mrp_status IN NUMBER,
224 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
225 ERRBUF OUT NOCOPY VARCHAR2,
226 RETCODE OUT NOCOPY NUMBER)
227 IS
228
229 BEGIN
230
231 Collect_OnHand_Supply(p_group_id => p_group_id,
232 p_sys_seq_num => p_sys_seq_num,
233 p_mrp_status => p_mrp_status,
234 p_supply_table => p_supply_table,
235 ERRBUF => ERRBUF,
236 RETCODE => RETCODE);
237
238 Collect_User_Supply(p_group_id => p_group_id,
239 p_sys_seq_num => p_sys_seq_num,
240 p_mrp_status => p_mrp_status,
241 p_supply_table => p_supply_table,
242 ERRBUF => ERRBUF,
243 RETCODE => RETCODE);
244
245 Collect_MTL_Supply(p_group_id => p_group_id,
246 p_sys_seq_num => p_sys_seq_num,
247 p_mrp_status => p_mrp_status,
248 p_supply_table => p_supply_table,
249 ERRBUF => ERRBUF,
250 RETCODE => RETCODE);
251
252 Collect_DiscreteJob_Supply(p_group_id => p_group_id,
253 p_sys_seq_num => p_sys_seq_num,
254 p_mrp_status => p_mrp_status,
255 p_supply_table => p_supply_table,
256 ERRBUF => ERRBUF,
257 RETCODE => RETCODE);
258
259 Collect_WipNegReq_Supply(p_group_id => p_group_id,
260 p_sys_seq_num => p_sys_seq_num,
261 p_mrp_status => p_mrp_status,
262 p_supply_table => p_supply_table,
263 ERRBUF => ERRBUF,
264 RETCODE => RETCODE);
265
266 Collect_RepSched_Supply(p_group_id => p_group_id,
267 p_sys_seq_num => p_sys_seq_num,
268 p_mrp_status => p_mrp_status,
269 p_supply_table => p_supply_table,
270 ERRBUF => ERRBUF,
271 RETCODE => RETCODE);
272
273 Collect_FlowSched_Supply(p_group_id => p_group_id,
274 p_sys_seq_num => p_sys_seq_num,
275 p_mrp_status => p_mrp_status,
276 p_supply_table => p_supply_table,
277 ERRBUF => ERRBUF,
278 RETCODE => RETCODE);
279
280
281
282
283 END Collect_Supply_Info;
284
285
286 /*
287 This procedure is a wrapper to collect all the individual demand types.
288 */
289 PROCEDURE Collect_Demand_Info(p_group_id IN NUMBER,
290 p_sys_seq_num IN NUMBER,
291 p_mrp_status IN NUMBER,
292 p_org_id IN NUMBER,
293 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
294 ERRBUF OUT NOCOPY VARCHAR2,
295 RETCODE OUT NOCOPY NUMBER)
296 IS
297
298 BEGIN
299
300 Collect_DiscreteJob_Demand(p_group_id => p_group_id,
301 p_sys_seq_num => p_sys_seq_num,
302 p_mrp_status => p_mrp_status,
303 p_demand_table => p_demand_table,
304 ERRBUF => ERRBUF,
305 RETCODE => RETCODE);
306
307 Collect_RepSched_Demand(p_group_id => p_group_id,
308 p_sys_seq_num => p_sys_seq_num,
309 p_mrp_status => p_mrp_status,
310 p_demand_table => p_demand_table,
311 ERRBUF => ERRBUF,
312 RETCODE => RETCODE);
313
314 Collect_User_Demand(p_group_id => p_group_id,
315 p_sys_seq_num => p_sys_seq_num,
316 p_mrp_status => p_mrp_status,
317 p_demand_table => p_demand_table,
318 ERRBUF => ERRBUF,
319 RETCODE => RETCODE);
320
321 Collect_FlowSched_Demand(p_group_id => p_group_id,
322 p_sys_seq_num => p_sys_seq_num,
323 p_mrp_status => p_mrp_status,
324 p_demand_table => p_demand_table,
325 ERRBUF => ERRBUF,
326 RETCODE => RETCODE);
327
328 Collect_SalesOrder_Demand(p_group_id => p_group_id,
329 p_sys_seq_num => p_sys_seq_num,
330 p_mrp_status => p_mrp_status,
331 p_org_id => p_org_id,
332 p_demand_table => p_demand_table,
333 ERRBUF => ERRBUF,
334 RETCODE => RETCODE);
335
336
337 END Collect_Demand_Info;
338
339
340
341 --
342 -- The procedures to collect individual supply/demand information follow.
343 --
344
345
346 PROCEDURE Collect_OnHand_Supply(p_group_id IN NUMBER,
347 p_sys_seq_num IN NUMBER,
348 p_mrp_status IN NUMBER,
349 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
350 ERRBUF OUT NOCOPY VARCHAR2,
351 RETCODE OUT NOCOPY NUMBER)
352 IS
353
354 CURSOR OH_SUPPLY_CURSOR IS
355 SELECT
356 1 reservation_type,
357 8 supply_demand_source_type,
358 0 txn_source_type_id,
359 0 supply_demand_source_id,
360 2 supply_demand_type,
361 SUM(Q.TRANSACTION_QUANTITY) supply_demand_quantity,
362 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
363 V.INVENTORY_ITEM_ID inventory_item_id,
364 V.ORGANIZATION_ID organization_id
365 FROM
366 MTL_SECONDARY_INVENTORIES S,
367 BOM_CALENDAR_DATES C,
368 MTL_PARAMETERS P,
369 MTL_ONHAND_QUANTITIES Q,
370 MTL_ATP_RULES R,
371 MTL_SYSTEM_ITEMS I,
372 MTL_GROUP_ITEM_ATPS_VIEW V
373 WHERE I.ORGANIZATION_ID = V.ORGANIZATION_ID
374 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
375 AND Q.ORGANIZATION_ID = V.ORGANIZATION_ID
376 AND Q.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
377 AND S.SECONDARY_INVENTORY_NAME = Q.SUBINVENTORY_CODE
378 AND S.ORGANIZATION_ID = Q.ORGANIZATION_ID
379 AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
380 AND S.AVAILABILITY_TYPE = DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)
381 AND V.AVAILABLE_TO_ATP = 1
382 AND V.ATP_RULE_ID = R.RULE_ID
383 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2, -1, V.INVENTORY_ITEM_ID)
384 AND V.ATP_GROUP_ID = P_GROUP_ID
385 AND R.DEMAND_CLASS_ATP_FLAG=2
386 AND P.CALENDAR_CODE = C.CALENDAR_CODE
387 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
388 AND C.CALENDAR_DATE = TRUNC(SYSDATE)
389 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
390 GROUP BY V.INVENTORY_ITEM_ID, V.ORGANIZATION_ID, C.NEXT_DATE, C.NEXT_SEQ_NUM
391 UNION ALL
392 SELECT
393 1 reservation_type,
394 8 supply_demand_source_type,
395 0 txn_source_type_id,
396 0 supply_demand_source_id,
397 2 supply_demand_type,
398 SUM(T.PRIMARY_QUANTITY) supply_demand_quantity,
399 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
400 V.INVENTORY_ITEM_ID inventory_item_id,
401 V.ORGANIZATION_ID organization_id
402 FROM MTL_SECONDARY_INVENTORIES S,
403 BOM_CALENDAR_DATES C,
404 MTL_PARAMETERS P,
405 MTL_MATERIAL_TRANSACTIONS_TEMP T,
406 MTL_SYSTEM_ITEMS I,
407 MTL_ATP_RULES R,
408 MTL_GROUP_ITEM_ATPS_VIEW V
409 WHERE I.ORGANIZATION_ID = V.ORGANIZATION_ID
410 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
411 AND T.ORGANIZATION_ID = V.ORGANIZATION_ID
412 AND T.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
413 AND T.POSTING_FLAG = 'Y'
414 AND S.SECONDARY_INVENTORY_NAME = T.SUBINVENTORY_CODE
415 AND S.ORGANIZATION_ID = T.ORGANIZATION_ID
416 AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
417 AND S.AVAILABILITY_TYPE = DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)
418 AND V.AVAILABLE_TO_ATP = 1
419 AND V.ATP_RULE_ID = R.RULE_ID
420 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2, -1, V.INVENTORY_ITEM_ID)
421 AND V.ATP_GROUP_ID = P_GROUP_ID
422 AND R.DEMAND_CLASS_ATP_FLAG=2
423 AND P.CALENDAR_CODE = C.CALENDAR_CODE
424 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
425 AND C.CALENDAR_DATE = TRUNC(SYSDATE)
426 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
427 GROUP BY V.INVENTORY_ITEM_ID, V.ORGANIZATION_ID, C.NEXT_DATE, C.NEXT_SEQ_NUM;
428
429 l_supply_rec OH_SUPPLY_CURSOR%ROWTYPE;
430 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
431 BEGIN
432
433 OPEN OH_SUPPLY_CURSOR;
434
435 LOOP
436 FETCH OH_SUPPLY_CURSOR INTO l_supply_rec;
437 EXIT WHEN OH_SUPPLY_CURSOR%NOTFOUND;
438
439 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
440 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
441 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
442 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
443 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
444 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
445 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
446 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
447 p_supply_table(j).organization_id := l_supply_rec.organization_id;
448
449 j := j+1;
450
451 END LOOP;
452
453 CLOSE OH_SUPPLY_CURSOR;
454
455 END Collect_OnHand_Supply;
456
457
458 PROCEDURE Collect_User_Supply(p_group_id IN NUMBER,
459 p_sys_seq_num IN NUMBER,
460 p_mrp_status IN NUMBER,
461 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
462 ERRBUF OUT NOCOPY VARCHAR2,
463 RETCODE OUT NOCOPY NUMBER)
464 IS
465
466 CURSOR USER_SUPPLY_CURSOR IS
467 SELECT
468 1 reservation_type,
469 16 supply_demand_source_type,
470 U.SOURCE_TYPE_ID txn_source_type_id,
471 U.SOURCE_ID supply_demand_source_id,
472 2 supply_demand_type,
473 U.PRIMARY_UOM_QUANTITY supply_demand_quantity,
474 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
475 V.INVENTORY_ITEM_ID inventory_item_id,
476 V.ORGANIZATION_ID organization_id
477 FROM
478 BOM_CALENDAR_DATES C,
479 MTL_USER_SUPPLY U,
480 MTL_SYSTEM_ITEMS I,
481 MTL_PARAMETERS P,
482 MTL_ATP_RULES R,
483 MTL_GROUP_ITEM_ATPS_VIEW V
484 WHERE U.ORGANIZATION_ID = V.ORGANIZATION_ID
485 AND U.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
486 AND V.AVAILABLE_TO_ATP = 1
487 AND V.ATP_RULE_ID = R.RULE_ID
488 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_USER_DEFINED_SUPPLY, 2, -1, V.INVENTORY_ITEM_ID)
489 AND V.ATP_GROUP_ID = P_GROUP_ID
490 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
491 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
492 AND NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
493 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
494 NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
495 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
496 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
497 NULL, C.NEXT_SEQ_NUM,
498 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
499 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM +(DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
500 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
501 2, I.CUM_MANUFACTURING_LEAD_TIME,
502 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
503 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
504 AND P.CALENDAR_CODE = C.CALENDAR_CODE
505 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
506 AND C.CALENDAR_DATE = TRUNC(U.EXPECTED_DELIVERY_DATE);
507
508 l_supply_rec USER_SUPPLY_CURSOR%ROWTYPE;
509 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
510 BEGIN
511
512 OPEN USER_SUPPLY_CURSOR;
513
514 LOOP
515 FETCH USER_SUPPLY_CURSOR INTO l_supply_rec;
516 EXIT WHEN USER_SUPPLY_CURSOR%NOTFOUND;
517
518 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
519 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
520 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
521 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
522 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
523 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
524 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
525 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
526 p_supply_table(j).organization_id := l_supply_rec.organization_id;
527
528 j := j+1;
529
530 END LOOP;
531
532 CLOSE USER_SUPPLY_CURSOR;
533
534
535 END Collect_User_Supply;
536
537
538 PROCEDURE Collect_MTL_Supply(p_group_id IN NUMBER,
539 p_sys_seq_num IN NUMBER,
540 p_mrp_status IN NUMBER,
541 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
542 ERRBUF OUT NOCOPY VARCHAR2,
543 RETCODE OUT NOCOPY NUMBER)
544 IS
545
546 -- queries mtl_supply information, that is PO, REQ, SHIP, RCV
547 -- question here,
548 -- I select NVL(S.MRP_PRIMARY_QUANTITY, S.TO_ORG_PRIMARY_QUANTITY)
549 -- as the supply_demand_quantity if discrete mps is included,
550 -- S.TO_ORG_PRIMARY_QUANTITY if not included.
551 -- However, in inldsd.ppc, it selects
552 -- S.TO_ORG_PRIMARY_QUANTITY for shipment, NVL(S.MRP_PRIMARY_QUANTITY, 0)
553 -- if discrete mps is included, S.TO_ORG_PRIMARY_QUANTITY if not included
554
555 CURSOR MTL_SUPPLY_CURSOR IS
556 SELECT
557 1 reservation_type,
558 DECODE(S.PO_HEADER_ID,
559 NULL,DECODE(S.SUPPLY_TYPE_CODE,
560 'REQ',DECODE(S.FROM_ORGANIZATION_ID,
561 NULL,18,
562 20),
563 12),
564 1) supply_demand_source_type,
565 DECODE(S.PO_HEADER_ID,
566 NULL,DECODE(S.SUPPLY_TYPE_CODE,
567 'REQ',10,
568 8),
569 1) txn_source_type_id,
570 DECODE(S.PO_HEADER_ID,
571 NULL,DECODE(S.SUPPLY_TYPE_CODE,
572 'REQ',REQ_HEADER_ID,
573 SHIPMENT_HEADER_ID),
574 PO_HEADER_ID) supply_demand_source_id,
575 2 supply_demand_type,
576 DECODE(P_MRP_STATUS,
577 1, DECODE(S.SUPPLY_TYPE_CODE,
578 'SHIPMENT', S.TO_ORG_PRIMARY_QUANTITY,
579 DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
580 1,NVL(S.MRP_PRIMARY_QUANTITY, 0),
581 S.TO_ORG_PRIMARY_QUANTITY)),
582 S.TO_ORG_PRIMARY_QUANTITY) supply_demand_quantity,
583 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
584 V.INVENTORY_ITEM_ID inventory_item_id,
585 V.ORGANIZATION_ID organization_id
586 FROM
587 MTL_GROUP_ITEM_ATPS_VIEW V,
588 MTL_ATP_RULES R,
589 MTL_SYSTEM_ITEMS I,
590 MTL_PARAMETERS P,
591 BOM_CALENDAR_DATES C,
592 MTL_SUPPLY S
593 WHERE V.ATP_GROUP_ID = P_GROUP_ID
594 AND R.DEMAND_CLASS_ATP_FLAG=2
595 AND V.AVAILABLE_TO_ATP = 1
596 AND V.ATP_RULE_ID = R.RULE_ID
597 AND((R.INCLUDE_INTERORG_TRANSFERS = 1
598 AND S.REQ_HEADER_ID IS NULL
599 AND S.PO_HEADER_ID IS NULL)
600 OR (S.REQ_HEADER_ID=DECODE(R.INCLUDE_INTERNAL_REQS,1,S.REQ_HEADER_ID)
601 AND S.FROM_ORGANIZATION_ID IS NOT NULL)
602 OR (S.SUPPLY_TYPE_CODE=DECODE(R.INCLUDE_VENDOR_REQS,1,'REQ')
603 AND S.FROM_ORGANIZATION_ID IS NULL)
604 OR S.PO_HEADER_ID=DECODE(R.INCLUDE_PURCHASE_ORDERS,1, S.PO_HEADER_ID))
605 AND S.TO_ORGANIZATION_ID=V.ORGANIZATION_ID
606 AND S.ITEM_ID = V.INVENTORY_ITEM_ID
607 AND S.DESTINATION_TYPE_CODE='INVENTORY'
608 AND (S.TO_SUBINVENTORY IS NULL OR EXISTS (SELECT
609 'X' FROM MTL_SECONDARY_INVENTORIES S2
610 WHERE S2.ORGANIZATION_ID=S.TO_ORGANIZATION_ID
611 AND S.TO_SUBINVENTORY=S2.SECONDARY_INVENTORY_NAME
612 AND S2.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
613 1, 1,
614 NULL, 1,
615 S2.INVENTORY_ATP_CODE)
616 AND S2.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
617 2, 1,
618 S2.AVAILABILITY_TYPE)))
619 AND I.ORGANIZATION_ID= V.ORGANIZATION_ID
620 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
621 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
622 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
623 NULL, C.NEXT_SEQ_NUM,
624 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
625 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
626 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
627 2, I.CUM_MANUFACTURING_LEAD_TIME,
628 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
629 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
630 AND P.CALENDAR_CODE = C.CALENDAR_CODE
631 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
632 AND C.CALENDAR_DATE = TRUNC(S.EXPECTED_DELIVERY_DATE);
633
634 l_supply_rec MTL_SUPPLY_CURSOR%ROWTYPE;
635 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
636 BEGIN
637
638 OPEN MTL_SUPPLY_CURSOR;
639
640 LOOP
641 FETCH MTL_SUPPLY_CURSOR INTO l_supply_rec;
642 EXIT WHEN MTL_SUPPLY_CURSOR%NOTFOUND;
643
644 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
645 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
646 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
647 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
648 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
649 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
650 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
651 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
652 p_supply_table(j).organization_id := l_supply_rec.organization_id;
653
654 j := j+1;
655
656 END LOOP;
657
658 CLOSE MTL_SUPPLY_CURSOR;
659
660 END Collect_MTL_Supply;
661
662 PROCEDURE Collect_DiscreteJob_Supply(p_group_id IN NUMBER,
663 p_sys_seq_num IN NUMBER,
664 p_mrp_status IN NUMBER,
665 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
666 ERRBUF OUT NOCOPY VARCHAR2,
667 RETCODE OUT NOCOPY NUMBER)
668 IS
669
670 -- insert wip discrete job information
671 -- question here: do I need to apply bug 791215 here?
672 -- that is , using net_quantity instead of mps_net_quantity?
673
674 CURSOR DISCRETEJOB_SUPPLY_CURSOR IS
675 SELECT
676 1 reservation_type,
677 DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
678 5 txn_source_type_id,
679 D.WIP_ENTITY_ID supply_demand_source_id,
680 2 supply_demand_type,
681 DECODE(P_MRP_STATUS,
682 1, DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
683 1, DECODE(D.JOB_TYPE,1,
684 DECODE(I.MRP_PLANNING_CODE,
685 4,NVL(D.MPS_NET_QUANTITY,0),
686 D.START_QUANTITY),
687 D.START_QUANTITY),
688 D.START_QUANTITY) - D.QUANTITY_COMPLETED - D.QUANTITY_SCRAPPED,
689 D.START_QUANTITY - D.QUANTITY_COMPLETED - D.QUANTITY_SCRAPPED) supply_demand_quantity,
690 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
691 V.INVENTORY_ITEM_ID inventory_item_id,
692 V.ORGANIZATION_ID organization_id
693 FROM WIP_DISCRETE_JOBS D,
694 BOM_CALENDAR_DATES C,
695 MTL_PARAMETERS P,
696 MTL_SYSTEM_ITEMS I,
697 MTL_ATP_RULES R,
698 MTL_GROUP_ITEM_ATPS_VIEW V
699 WHERE D.STATUS_TYPE IN (1,3,4,6)
700 AND (D.START_QUANTITY-D.QUANTITY_COMPLETED-D.QUANTITY_SCRAPPED) >0
701 AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
702 AND D.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
703 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,
704 V.INVENTORY_ITEM_ID, DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
705 1,V.INVENTORY_ITEM_ID,
706 -1))
707 AND (D.JOB_TYPE =DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
708 OR D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
709 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
710 AND V.AVAILABLE_TO_ATP = 1
711 AND V.ATP_RULE_ID = R.RULE_ID
712 AND V.ATP_GROUP_ID = P_GROUP_ID
713 AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
714 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
715 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
716 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
717 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
718 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
719 NULL, C.NEXT_SEQ_NUM,
720 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
721 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
722 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
723 2, I.CUM_MANUFACTURING_LEAD_TIME,
724 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
725 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
726 AND P.CALENDAR_CODE = C.CALENDAR_CODE
727 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
728 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE);
729
730 l_supply_rec DISCRETEJOB_SUPPLY_CURSOR%ROWTYPE;
731 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
732 BEGIN
733
734 OPEN DISCRETEJOB_SUPPLY_CURSOR;
735
736 LOOP
737 FETCH DISCRETEJOB_SUPPLY_CURSOR INTO l_supply_rec;
738 EXIT WHEN DISCRETEJOB_SUPPLY_CURSOR%NOTFOUND;
739
740 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
741 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
742 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
743 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
744 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
745 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
746 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
747 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
748 p_supply_table(j).organization_id := l_supply_rec.organization_id;
749
750 j := j+1;
751
752 END LOOP;
753
754 CLOSE DISCRETEJOB_SUPPLY_CURSOR;
755
756 END Collect_DiscreteJob_Supply;
757
758 PROCEDURE Collect_WipNegReq_Supply(p_group_id IN NUMBER,
759 p_sys_seq_num IN NUMBER,
760 p_mrp_status IN NUMBER,
761 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
762 ERRBUF OUT NOCOPY VARCHAR2,
763 RETCODE OUT NOCOPY NUMBER)
764 IS
765
766
767 -- insert wip neg requirement information
768 -- I have applied bug 454103 here.
769
770 CURSOR WIPNEGREQ_SUPPLY_CURSOR IS
771 SELECT
772 1 reservation_type,
773 DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
774 5 txn_source_type_id,
775 D.WIP_ENTITY_ID supply_demand_source_id,
776 2 supply_demand_type,
777 -1*O.REQUIRED_QUANTITY supply_demand_quantity,
778 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
779 V.INVENTORY_ITEM_ID inventory_item_id,
780 V.ORGANIZATION_ID organization_id
781 FROM MTL_GROUP_ITEM_ATPS_VIEW V,
782 MTL_PARAMETERS P,
783 MTL_ATP_RULES R,
784 MTL_SYSTEM_ITEMS I,
785 BOM_CALENDAR_DATES C,
786 WIP_REQUIREMENT_OPERATIONS O,
787 WIP_DISCRETE_JOBS D
788 WHERE O.ORGANIZATION_ID=D.ORGANIZATION_ID
789 AND O.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
790 AND O.WIP_ENTITY_ID=D.WIP_ENTITY_ID
791 AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
792 AND O.WIP_SUPPLY_TYPE <> 6
793 AND O.REQUIRED_QUANTITY < 0
794 AND O.OPERATION_SEQ_NUM > 0
795 AND (D.JOB_TYPE=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
796 OR D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
797 AND D.STATUS_TYPE IN (1,3,4,6)
798 AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
799 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
800 AND V.AVAILABLE_TO_ATP = 1
801 AND V.ATP_RULE_ID = R.RULE_ID
802 AND V.ATP_GROUP_ID = P_GROUP_ID
803 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,
804 V.INVENTORY_ITEM_ID, DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1,
805 V.INVENTORY_ITEM_ID, -1))
806 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
807 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
808 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
809 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
810 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
811 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
812 NULL, C.NEXT_SEQ_NUM,
813 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
814 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
815 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
816 2, I.CUM_MANUFACTURING_LEAD_TIME,
817 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
818 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
819 AND P.CALENDAR_CODE = C.CALENDAR_CODE
820 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
821 AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED);
822
823 l_supply_rec WIPNEGREQ_SUPPLY_CURSOR%ROWTYPE;
824 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
825 BEGIN
826
827 OPEN WIPNEGREQ_SUPPLY_CURSOR;
828
829 LOOP
830 FETCH WIPNEGREQ_SUPPLY_CURSOR INTO l_supply_rec;
831 EXIT WHEN WIPNEGREQ_SUPPLY_CURSOR%NOTFOUND;
832
833 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
834 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
835 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
836 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
837 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
838 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
839 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
840 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
841 p_supply_table(j).organization_id := l_supply_rec.organization_id;
842
843 j := j+1;
844
845 END LOOP;
846
847 CLOSE WIPNEGREQ_SUPPLY_CURSOR;
848
849 END Collect_WipNegReq_Supply;
850
851
852 PROCEDURE Collect_RepSched_Supply(p_group_id IN NUMBER,
853 p_sys_seq_num IN NUMBER,
854 p_mrp_status IN NUMBER,
855 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
856 ERRBUF OUT NOCOPY VARCHAR2,
857 RETCODE OUT NOCOPY NUMBER)
858 IS
859
860 -- insert wip repetitive supply
861
862 CURSOR REPSCHED_SUPPLY_CURSOR IS
863 SELECT
864 1 reservation_type,
865 4 supply_demand_source_type,
866 5 txn_source_type_id,
867 WRS.REPETITIVE_SCHEDULE_ID supply_demand_source_id,
868 2 supply_demand_type,
869 DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM)-WRS.QUANTITY_COMPLETED),
870 -1,WRS.DAILY_PRODUCTION_RATE*LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,
871 WRS.PROCESSING_WORK_DAYS)-WRS.QUANTITY_COMPLETED,
872 LEAST(C1.NEXT_SEQ_NUM+WRS.PROCESSING_WORK_DAYS-C.NEXT_SEQ_NUM,1)*WRS.DAILY_PRODUCTION_RATE) supply_demand_quantity,
873 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
874 V.INVENTORY_ITEM_ID inventory_item_id,
875 V.ORGANIZATION_ID organization_id
876 FROM
877 MTL_GROUP_ATPS_VIEW V,
878 MTL_ATP_RULES R,
879 MTL_SYSTEM_ITEMS I,
880 MTL_PARAMETERS P,
881 BOM_CALENDAR_DATES C,
882 BOM_CALENDAR_DATES C1,
883 WIP_REPETITIVE_SCHEDULES WRS,
884 WIP_REPETITIVE_ITEMS WRI
885 WHERE V.ATP_GROUP_ID = P_GROUP_ID
886 AND V.AVAILABLE_TO_ATP = 1
887 AND V.ATP_RULE_ID = R.RULE_ID
888 AND WRI.ORGANIZATION_ID = V.ORGANIZATION_ID
889 AND WRI.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
890 AND R.INCLUDE_REP_WIP_RECEIPTS = 1
891 AND WRI.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
892 AND WRI.LINE_ID = WRS.LINE_ID
893 AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
894 AND WRS.STATUS_TYPE IN (1,3,4,6)
895 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
896 AND C1.CALENDAR_CODE=P.CALENDAR_CODE
897 AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
898 AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_COMPLETION_DATE)
899 AND C.CALENDAR_CODE=P.CALENDAR_CODE
900 AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
901 AND C.SEQ_NUM BETWEEN C1.NEXT_SEQ_NUM AND C1.NEXT_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
902 AND WRS.DAILY_PRODUCTION_RATE*LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,WRS.PROCESSING_WORK_DAYS) > WRS.QUANTITY_COMPLETED
903 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
904 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
905 AND NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
906 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
907 NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
908 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
909 NULL, C.NEXT_SEQ_NUM,
910 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
911 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (
912 DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
913 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
914 2, I.CUM_MANUFACTURING_LEAD_TIME,
915 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
916 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1);
917
918 l_supply_rec REPSCHED_SUPPLY_CURSOR%ROWTYPE;
919 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
920 BEGIN
921
922 OPEN REPSCHED_SUPPLY_CURSOR;
923
924 LOOP
925 FETCH REPSCHED_SUPPLY_CURSOR INTO l_supply_rec;
926 EXIT WHEN REPSCHED_SUPPLY_CURSOR%NOTFOUND;
927
928 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
929 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
930 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
931 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
932 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
933 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
934 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
935 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
936 p_supply_table(j).organization_id := l_supply_rec.organization_id;
937
938 j := j+1;
939
940 END LOOP;
941
942 CLOSE REPSCHED_SUPPLY_CURSOR;
943
944 END Collect_RepSched_Supply;
945
946 PROCEDURE Collect_FlowSched_Supply(p_group_id IN NUMBER,
947 p_sys_seq_num IN NUMBER,
948 p_mrp_status IN NUMBER,
949 p_supply_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
950 ERRBUF OUT NOCOPY VARCHAR2,
951 RETCODE OUT NOCOPY NUMBER)
952 IS
953 -- insert flow schedule supply information
954 CURSOR FLOWSCHED_SUPPLY_CURSOR IS
955 SELECT
956 1 reservation_type,
957 24 supply_demand_source_type,
958 5 txn_source_type_id,
959 D.WIP_ENTITY_ID supply_demand_source_id,
960 2 supply_demand_type,
961 DECODE(P_MRP_STATUS,
962 1,DECODE(NVL(V.N_COLUMN1,R.INCLUDE_DISCRETE_MPS),
963 1,DECODE(I.MRP_PLANNING_CODE,
964 4,NVL(D.MPS_NET_QUANTITY,0),
965 8,NVL(D.MPS_NET_QUANTITY,0),
966 D.PLANNED_QUANTITY),
967 D.PLANNED_QUANTITY - D.QUANTITY_COMPLETED), /* I missed something here */
968 D.PLANNED_QUANTITY - D.QUANTITY_COMPLETED) supply_demand_quantity,
969 TO_NUMBER(TO_CHAR(C.NEXT_DATE,'J')) supply_demand_date,
970 V.INVENTORY_ITEM_ID inventory_item_id,
971 V.ORGANIZATION_ID organization_id
972 FROM
973 WIP_FLOW_SCHEDULES D,
974 BOM_CALENDAR_DATES C,
975 MTL_PARAMETERS P,
976 MTL_SYSTEM_ITEMS I,
977 MTL_ATP_RULES R,
978 MTL_GROUP_ITEM_ATPS_VIEW V
979 WHERE D.STATUS = 1
980 AND (D.PLANNED_QUANTITY-D.QUANTITY_COMPLETED) >0
981 AND D.ORGANIZATION_ID=V.ORGANIZATION_ID
982 AND D.PRIMARY_ITEM_ID=V.INVENTORY_ITEM_ID
983 AND D.SCHEDULED_FLAG = 1
984 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, V.INVENTORY_ITEM_ID, -1)
985 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
986 AND V.AVAILABLE_TO_ATP = 1
987 AND V.ATP_RULE_ID = R.RULE_ID
988 AND V.ATP_GROUP_ID = P_GROUP_ID
989 AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
990 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
991 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
992 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
993 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
994 AND C.NEXT_SEQ_NUM >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
995 NULL, C.NEXT_SEQ_NUM,
996 P_SYS_SEQ_NUM-R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
997 AND C.NEXT_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
998 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
999 2, I.CUM_MANUFACTURING_LEAD_TIME,
1000 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1001 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.NEXT_SEQ_NUM+1)
1002 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1003 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1004 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE)
1005 AND C.NEXT_SEQ_NUM >= P_SYS_SEQ_NUM;
1006
1007 l_supply_rec FLOWSCHED_SUPPLY_CURSOR%ROWTYPE;
1008 j NUMBER := Nvl(p_supply_table.LAST,0) + 1;
1009 BEGIN
1010
1011 OPEN FLOWSCHED_SUPPLY_CURSOR;
1012
1013 LOOP
1014 FETCH FLOWSCHED_SUPPLY_CURSOR INTO l_supply_rec;
1015 EXIT WHEN FLOWSCHED_SUPPLY_CURSOR%NOTFOUND;
1016
1017 p_supply_table(j).reservation_type := l_supply_rec.reservation_type;
1018 p_supply_table(j).supply_demand_source_type := l_supply_rec.supply_demand_source_type;
1019 p_supply_table(j).txn_source_type_id := l_supply_rec.txn_source_type_id;
1020 p_supply_table(j).supply_demand_source_id := l_supply_rec.supply_demand_source_id;
1021 p_supply_table(j).supply_demand_type := l_supply_rec.supply_demand_type;
1022 p_supply_table(j).supply_demand_quantity := l_supply_rec.supply_demand_quantity;
1023 p_supply_table(j).supply_demand_date := l_supply_rec.supply_demand_date;
1024 p_supply_table(j).inventory_item_id := l_supply_rec.inventory_item_id;
1025 p_supply_table(j).organization_id := l_supply_rec.organization_id;
1026
1027 j := j+1;
1028
1029 END LOOP;
1030
1031 CLOSE FLOWSCHED_SUPPLY_CURSOR;
1032
1033 END Collect_FlowSched_Supply;
1034
1035 --
1036 -- The rest of the procedures collect demand information.
1037 --
1038
1039 PROCEDURE Collect_DiscreteJob_Demand(p_group_id IN NUMBER,
1040 p_sys_seq_num IN NUMBER,
1041 p_mrp_status IN NUMBER,
1042 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1043 ERRBUF OUT NOCOPY VARCHAR2,
1044 RETCODE OUT NOCOPY NUMBER)
1045 IS
1046 -- insert wip discrete requirement information
1047
1048 CURSOR DISCRETEJOB_DEMAND_CURSOR IS
1049 SELECT
1050 1 reservation_type,
1051 DECODE(D.JOB_TYPE, 1, 5, 7) supply_demand_source_type,
1052 5 txn_source_type_id,
1053 D.WIP_ENTITY_ID supply_demand_source_id,
1054 1 supply_demand_type,
1055 LEAST(-1*(O.REQUIRED_QUANTITY-O.QUANTITY_ISSUED),0) supply_demand_quantity,
1056 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1057 V.INVENTORY_ITEM_ID inventory_item_id,
1058 V.ORGANIZATION_ID organization_id
1059 FROM
1060 MTL_GROUP_ITEM_ATPS_VIEW V,
1061 MTL_PARAMETERS P,
1062 MTL_ATP_RULES R,
1063 MTL_SYSTEM_ITEMS I,
1064 BOM_CALENDAR_DATES C,
1065 WIP_REQUIREMENT_OPERATIONS O,
1066 WIP_DISCRETE_JOBS D,
1067 BOM_CALENDAR_DATES C1
1068 WHERE O.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
1069 AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
1070 AND O.WIP_SUPPLY_TYPE <> 6
1071 AND O.REQUIRED_QUANTITY > 0
1072 AND O.OPERATION_SEQ_NUM > 0
1073 AND O.WIP_ENTITY_ID=D.WIP_ENTITY_ID
1074 AND O.ORGANIZATION_ID=D.ORGANIZATION_ID
1075 AND O.DATE_REQUIRED >= c1.calendar_date
1076 AND ((D.JOB_TYPE= 1 AND R.INCLUDE_DISCRETE_WIP_DEMAND = 1)
1077 OR (D.JOB_TYPE = 3 AND R.INCLUDE_NONSTD_WIP_DEMAND = 1))
1078 AND D.STATUS_TYPE IN (1,3,4,6)
1079 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1080 AND V.AVAILABLE_TO_ATP = 1
1081 AND V.ATP_RULE_ID = R.RULE_ID
1082 AND V.ATP_GROUP_ID = P_GROUP_ID
1083 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1084 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1085 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1086 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
1087 NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1088 AND C1.SEQ_NUM = greatest(1,P_SYS_SEQ_NUM-Nvl(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
1089 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1090 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1091 2, I.CUM_MANUFACTURING_LEAD_TIME,
1092 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1093 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1094 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1095 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1096 AND P.CALENDAR_CODE = C1.CALENDAR_CODE
1097 AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
1098 AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED)
1099 AND NOT EXISTS (SELECT 'exists in group?'
1100 FROM MTL_DEMAND_INTERFACE MDI1
1101 WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
1102 AND MDI1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
1103 AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1104 AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = D.WIP_ENTITY_ID);
1105
1106 l_demand_rec DISCRETEJOB_DEMAND_CURSOR%ROWTYPE;
1107 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1108 BEGIN
1109
1110 OPEN DISCRETEJOB_DEMAND_CURSOR;
1111
1112 LOOP
1113 FETCH DISCRETEJOB_DEMAND_CURSOR INTO l_demand_rec;
1114 EXIT WHEN DISCRETEJOB_DEMAND_CURSOR%NOTFOUND;
1115
1116 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1117 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1118 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1119 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1120 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1121 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1122 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1123 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1124 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1125
1126 j := j+1;
1127
1128 END LOOP;
1129
1130 CLOSE DISCRETEJOB_DEMAND_CURSOR;
1131
1132 END Collect_DiscreteJob_Demand;
1133
1134 PROCEDURE Collect_RepSched_Demand(p_group_id IN NUMBER,
1135 p_sys_seq_num IN NUMBER,
1136 p_mrp_status IN NUMBER,
1137 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1138 ERRBUF OUT NOCOPY VARCHAR2,
1139 RETCODE OUT NOCOPY NUMBER)
1140 IS
1141
1142 -- insert wip repetitive requirement information
1143 -- unlike inldsd.ppc, I combine DRJ1 and DRJ2
1144
1145 CURSOR REPSCHED_DEMAND_CURSOR IS
1146 SELECT
1147 1 reservation_type,
1148 4 supply_demand_source_type,
1149 5 txn_source_type_id,
1150 WRS.REPETITIVE_SCHEDULE_ID supply_demand_source_id,
1151 1 supply_demand_type,
1152 DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM)-WRO.QUANTITY_ISSUED),
1153 -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),
1154 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,
1155 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1156 V.INVENTORY_ITEM_ID inventory_item_id,
1157 V.ORGANIZATION_ID organization_id
1158 FROM
1159 MTL_GROUP_ITEM_ATPS_VIEW V,
1160 MTL_PARAMETERS P,
1161 MTL_ATP_RULES R,
1162 MTL_SYSTEM_ITEMS I,
1163 BOM_CALENDAR_DATES C,
1164 BOM_CALENDAR_DATES C1,
1165 WIP_REPETITIVE_SCHEDULES WRS,
1166 WIP_OPERATIONS WO,
1167 WIP_REQUIREMENT_OPERATIONS WRO
1168 WHERE WRO.ORGANIZATION_ID = V.ORGANIZATION_ID
1169 AND WRO.INVENTORY_ITEM_ID=V.INVENTORY_ITEM_ID
1170 AND V.AVAILABLE_TO_ATP = 1
1171 AND V.ATP_RULE_ID = R.RULE_ID
1172 AND V.ATP_GROUP_ID = P_GROUP_ID
1173 AND R.INCLUDE_REP_WIP_DEMAND = 1
1174 AND WRO.WIP_SUPPLY_TYPE <> 6
1175 AND WRO.REQUIRED_QUANTITY > 0
1176 AND WRO.OPERATION_SEQ_NUM > 0
1177 AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+)
1178 AND WRO.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID(+)
1179 AND WRO.ORGANIZATION_ID = WO.ORGANIZATION_ID(+)
1180 AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
1181 AND WRS.ORGANIZATION_ID = WRO.ORGANIZATION_ID
1182 AND WRS.STATUS_TYPE IN (1,3,4,6)
1183 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1184 AND C1.CALENDAR_CODE=P.CALENDAR_CODE
1185 AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
1186 AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_START_DATE)
1187 AND C.CALENDAR_CODE=P.CALENDAR_CODE
1188 AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
1189 AND C.SEQ_NUM BETWEEN C1.PRIOR_SEQ_NUM AND C1.PRIOR_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
1190 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
1191 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1192 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1193 AND NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1194 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
1195 NVL(WRS.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1196 AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1197 NULL, C.PRIOR_SEQ_NUM,
1198 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
1199 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1200 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1201 2, I.CUM_MANUFACTURING_LEAD_TIME,
1202 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1203 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1204 AND NOT EXISTS (SELECT 'exists in group?'
1205 FROM MTL_DEMAND_INTERFACE MDI1
1206 WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
1207 AND MDI1.ORGANIZATION_ID = V.ORGANIZATION_ID
1208 AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1209 AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = WRS.WIP_ENTITY_ID);
1210
1211
1212 l_demand_rec REPSCHED_DEMAND_CURSOR%ROWTYPE;
1213 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1214 BEGIN
1215
1216 OPEN REPSCHED_DEMAND_CURSOR;
1217
1218 LOOP
1219 FETCH REPSCHED_DEMAND_CURSOR INTO l_demand_rec;
1220 EXIT WHEN REPSCHED_DEMAND_CURSOR%NOTFOUND;
1221
1222 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1223 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1224 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1225 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1226 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1227 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1228 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1229 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1230 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1231
1232 j := j+1;
1233
1234 END LOOP;
1235
1236 CLOSE REPSCHED_DEMAND_CURSOR;
1237
1238 END Collect_RepSched_Demand;
1239
1240 PROCEDURE Collect_User_Demand(p_group_id IN NUMBER,
1241 p_sys_seq_num IN NUMBER,
1242 p_mrp_status IN NUMBER,
1243 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1244 ERRBUF OUT NOCOPY VARCHAR2,
1245 RETCODE OUT NOCOPY NUMBER)
1246 IS
1247
1248 -- insert user defined demand information
1249 CURSOR USER_DEMAND_CURSOR IS
1250 SELECT
1251 1 reservation_type,
1252 17 supply_demand_source_type,
1253 U.SOURCE_TYPE_ID txn_source_type_id,
1254 U.SOURCE_ID supply_demand_source_id,
1255 1 supply_demand_type,
1256 -1*U.PRIMARY_UOM_QUANTITY supply_demand_quantity,
1257 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1258 V.INVENTORY_ITEM_ID inventory_item_id,
1259 V.ORGANIZATION_ID organization_id
1260 FROM
1261 MTL_GROUP_ITEM_ATPS_VIEW V,
1262 MTL_PARAMETERS P,
1263 MTL_ATP_RULES R,
1264 MTL_SYSTEM_ITEMS I,
1265 MTL_USER_DEMAND U,
1266 BOM_CALENDAR_DATES C
1267 WHERE U.ORGANIZATION_ID = V.ORGANIZATION_ID
1268 AND U.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1269 AND V.AVAILABLE_TO_ATP = 1
1270 AND V.ATP_RULE_ID = R.RULE_ID
1271 AND V.INVENTORY_ITEM_ID = DECODE(R.INCLUDE_USER_DEFINED_DEMAND, 2, -1, V.INVENTORY_ITEM_ID)
1272 AND V.ATP_GROUP_ID = P_GROUP_ID
1273 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1274 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1275 AND NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1276 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(U.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1277 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1278 AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1279 NULL, C.PRIOR_SEQ_NUM,
1280 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
1281 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1282 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1283 2, I.CUM_MANUFACTURING_LEAD_TIME,
1284 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1285 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1286 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1287 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1288 AND C.CALENDAR_DATE = TRUNC(U.REQUIREMENT_DATE);
1289
1290 l_demand_rec USER_DEMAND_CURSOR%ROWTYPE;
1291 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1292 BEGIN
1293
1294 OPEN USER_DEMAND_CURSOR;
1295
1296 LOOP
1297 FETCH USER_DEMAND_CURSOR INTO l_demand_rec;
1298 EXIT WHEN USER_DEMAND_CURSOR%NOTFOUND;
1299
1300 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1301 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1302 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1303 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1304 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1305 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1306 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1307 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1308 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1309
1310 j := j+1;
1311
1312 END LOOP;
1313
1314 CLOSE USER_DEMAND_CURSOR;
1315
1316 END Collect_User_Demand;
1317
1318 PROCEDURE Collect_FlowSched_Demand(p_group_id IN NUMBER,
1319 p_sys_seq_num IN NUMBER,
1320 p_mrp_status IN NUMBER,
1321 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1322 ERRBUF OUT NOCOPY VARCHAR2,
1323 RETCODE OUT NOCOPY NUMBER)
1324 IS
1325
1326 -- insert wip flow schedule demand information
1327 -- haven't added the logic to explode phantom
1328
1329 CURSOR FLOWSCHED_DEMAND_CURSOR IS
1330 SELECT
1331 1 reservation_type,
1332 24 supply_demand_source_type,
1333 5 txn_source_type_id,
1334 F.WIP_ENTITY_ID supply_demand_source_id,
1335 1 supply_demand_type,
1336 LEAST(-1*(F.PLANNED_QUANTITY-F.QUANTITY_COMPLETED)*EXTENDED_QUANTITY, 0) supply_demand_quantity,
1337 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1338 V.INVENTORY_ITEM_ID inventory_item_id,
1339 V.ORGANIZATION_ID organization_id
1340 FROM WIP_FLOW_SCHEDULES F,
1341 BOM_BILL_OF_MATERIALS BOM ,
1342 BOM_EXPLOSIONS BE ,
1343 BOM_CALENDAR_DATES C,
1344 MTL_PARAMETERS P,
1345 MTL_SYSTEM_ITEMS I,
1346 MTL_ATP_RULES R,
1347 MTL_GROUP_ITEM_ATPS_VIEW V
1348 WHERE V.AVAILABLE_TO_ATP = 1
1349 AND V.ATP_RULE_ID = R.RULE_ID
1350 AND V.ATP_GROUP_ID = P_GROUP_ID
1351 AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND, 1,
1352 V.INVENTORY_ITEM_ID, -1)
1353 AND I.ORGANIZATION_ID=V.ORGANIZATION_ID
1354 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1355 AND BE.COMPONENT_ITEM_ID = V.INVENTORY_ITEM_ID
1356 AND BE.ORGANIZATION_ID = V.ORGANIZATION_ID
1357 AND BE.EXPLOSION_TYPE = 'ALL'
1358 AND BE.EXTENDED_QUANTITY > 0
1359 AND BE.COMPONENT_ITEM_ID <> BE.TOP_ITEM_ID
1360 AND BOM.COMMON_BILL_SEQUENCE_ID = BE.TOP_BILL_SEQUENCE_ID
1361 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
1362 AND TRUNC(BE.EFFECTIVITY_DATE) <= TRUNC(F.SCHEDULED_COMPLETION_DATE)
1363 AND TRUNC(BE.DISABLE_DATE) > TRUNC(F.SCHEDULED_COMPLETION_DATE)
1364 AND F.PRIMARY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
1365 AND F.ORGANIZATION_ID = BOM.ORGANIZATION_ID
1366 AND F.STATUS = 1
1367 AND F.SCHEDULED_FLAG = 1
1368 AND (F.PLANNED_QUANTITY - F.QUANTITY_COMPLETED) >0
1369 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1370 AND NVL(F.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1371 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),
1372 NVL(F.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1373 AND C.CALENDAR_CODE = P.CALENDAR_CODE
1374 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
1375 AND C.CALENDAR_DATE = TRUNC(F.SCHEDULED_COMPLETION_DATE)
1376 AND C.PRIOR_SEQ_NUM >= P_SYS_SEQ_NUM
1377 AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1378 NULL, C.PRIOR_SEQ_NUM,
1379 P_SYS_SEQ_NUM-R.PAST_DUE_DEMAND_CUTOFF_FENCE)
1380 AND C.PRIOR_SEQ_NUM < NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1381 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1382 2, I.CUM_MANUFACTURING_LEAD_TIME,
1383 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1384 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1)
1385 AND NOT EXISTS (SELECT 'exists in group?'
1386 FROM MTL_DEMAND_INTERFACE MDI1
1387 WHERE MDI1.ATP_GROUP_ID = P_GROUP_ID
1388 AND MDI1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
1389 AND MDI1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1390 AND NVL(MDI1.SUPPLY_HEADER_ID, -1) = F.WIP_ENTITY_ID);
1391
1392 l_demand_rec FLOWSCHED_DEMAND_CURSOR%ROWTYPE;
1393 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1394 BEGIN
1395
1396 OPEN FLOWSCHED_DEMAND_CURSOR;
1397
1398 LOOP
1399 FETCH FLOWSCHED_DEMAND_CURSOR INTO l_demand_rec;
1400 EXIT WHEN FLOWSCHED_DEMAND_CURSOR%NOTFOUND;
1401
1402 p_demand_table(j).reservation_type := l_demand_rec.reservation_type;
1403 p_demand_table(j).supply_demand_source_type := l_demand_rec.supply_demand_source_type;
1404 p_demand_table(j).txn_source_type_id := l_demand_rec.txn_source_type_id;
1405 p_demand_table(j).supply_demand_source_id := l_demand_rec.supply_demand_source_id;
1406 p_demand_table(j).supply_demand_type := l_demand_rec.supply_demand_type;
1407 p_demand_table(j).supply_demand_quantity := l_demand_rec.supply_demand_quantity;
1408 p_demand_table(j).supply_demand_date := l_demand_rec.supply_demand_date;
1409 p_demand_table(j).inventory_item_id := l_demand_rec.inventory_item_id;
1410 p_demand_table(j).organization_id := l_demand_rec.organization_id;
1411
1412 j := j+1;
1413
1414 END LOOP;
1415
1416 CLOSE FLOWSCHED_DEMAND_CURSOR;
1417
1418 END Collect_FlowSched_Demand;
1419
1420 PROCEDURE Collect_SalesOrder_Demand(p_group_id IN NUMBER,
1421 p_sys_seq_num IN NUMBER,
1422 p_mrp_status IN NUMBER,
1423 p_org_id IN NUMBER,
1424 p_demand_table IN OUT NOCOPY SUPPLY_DEMAND_TBL_TYPE,
1425 ERRBUF OUT NOCOPY VARCHAR2,
1426 RETCODE OUT NOCOPY NUMBER)
1427 IS
1428
1429 -- insert sales order demand
1430 CURSOR OE_SALESORDER_DEMAND_CURSOR IS
1431 SELECT D.RESERVATION_TYPE reservation_type,
1432 DECODE(D.DEMAND_SOURCE_TYPE,
1433 2, DECODE(D.RESERVATION_TYPE,1,2,3,23,9),
1434 8, DECODE(D.RESERVATION_TYPE,1,21,22),D.DEMAND_SOURCE_TYPE) supply_demand_source_type,
1435 DECODE(D.DEMAND_SOURCE_TYPE,
1436 8,2,D.DEMAND_SOURCE_TYPE) txn_source_type_id,
1437 D.DEMAND_SOURCE_HEADER_ID supply_demand_source_id,
1438 1 supply_demand_type,
1439 -1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)) supply_demand_quantity,
1440 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1441 V.INVENTORY_ITEM_ID inventory_item_id,
1442 V.ORGANIZATION_ID organization_id
1443 FROM
1444 MTL_GROUP_ITEM_ATPS_VIEW V,
1445 MTL_PARAMETERS P,
1446 MTL_SYSTEM_ITEMS I,
1447 MTL_ATP_RULES R,
1448 BOM_CALENDAR_DATES C,
1449 MTL_DEMAND D,
1450 BOM_CALENDAR_DATES C1
1451 WHERE D.ORGANIZATION_ID = V.ORGANIZATION_ID
1452 AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)
1453 AND D.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1454 AND D.AVAILABLE_TO_ATP = 1
1455 AND D.RESERVATION_TYPE <> DECODE(NVL(V.N_COLUMN1,R.INCLUDE_ONHAND_AVAILABLE), 2, 2, -1)
1456 AND D.RESERVATION_TYPE <> DECODE(R.DEMAND_CLASS_ATP_FLAG, 1, 2, -1)
1457 AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS, 2, 2, -1)
1458 AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS, 2, 8, -1)
1459 AND (D.SUBINVENTORY IS NULL
1460 OR D.SUBINVENTORY IN (SELECT S.SECONDARY_INVENTORY_NAME
1461 FROM MTL_SECONDARY_INVENTORIES S
1462 WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
1463 AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES, 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
1464 AND S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES, 2, 1, S.AVAILABILITY_TYPE)))
1465 AND V.AVAILABLE_TO_ATP = 1
1466 AND V.ATP_RULE_ID = R.RULE_ID
1467 AND V.ATP_GROUP_ID = P_GROUP_ID
1468 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID
1469 AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1470 AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(R.DEMAND_CLASS_ATP_FLAG,
1471 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@')))
1472 AND P.ORGANIZATION_ID = V.ORGANIZATION_ID
1473 AND C1.SEQ_NUM = greatest(1, P_SYS_SEQ_NUM - Nvl(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
1474 AND D.REQUIREMENT_DATE >= C1.CALENDAR_DATE
1475 AND D.RESERVATION_TYPE <> 2
1476 AND C.PRIOR_SEQ_NUM < DECODE(D.RESERVATION_TYPE,
1477 2,C.PRIOR_SEQ_NUM+1,
1478 NVL(P_SYS_SEQ_NUM + (DECODE(R.INFINITE_SUPPLY_FENCE_CODE,
1479 1, I.CUMULATIVE_TOTAL_LEAD_TIME,
1480 2, I.CUM_MANUFACTURING_LEAD_TIME,
1481 3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+I.POSTPROCESSING_LEAD_TIME,
1482 4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM+1))
1483 AND NOT EXISTS
1484 (SELECT 'exists in group?'
1485 FROM MTL_GROUP_ATPS_VIEW V1
1486 WHERE V1.ATP_GROUP_ID = P_GROUP_ID
1487 AND V1.ORGANIZATION_ID + 0 = V.ORGANIZATION_ID
1488 AND V1.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
1489 AND V1.AVAILABLE_TO_ATP = 1
1490 AND NVL(V1.DEMAND_SOURCE_TYPE, -1) = D.DEMAND_SOURCE_TYPE
1491 AND NVL(V1.DEMAND_SOURCE_HEADER_ID, -1) = D.DEMAND_SOURCE_HEADER_ID
1492 AND NVL(V1.DEMAND_SOURCE_LINE, '@@@') = NVL(D.DEMAND_SOURCE_LINE, '@@@')
1493 AND NVL(V1.DEMAND_SOURCE_DELIVERY, '@@@') = NVL(D.DEMAND_SOURCE_DELIVERY, '@@@')
1494 AND NVL(V1.DEMAND_SOURCE_NAME, '@@@') = NVL(D.DEMAND_SOURCE_NAME, '@@@'))
1495 AND P.CALENDAR_CODE = C.CALENDAR_CODE
1496 AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID
1497 AND P.CALENDAR_CODE = C1.CALENDAR_CODE
1498 AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
1499 AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
1500 AND V.INVENTORY_ITEM_ID=DECODE(D.RESERVATION_TYPE,
1501 1,DECODE(D.PARENT_DEMAND_ID, NULL,V.INVENTORY_ITEM_ID,-1),
1502 2,V.INVENTORY_ITEM_ID,
1503 3,DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS,
1504 1,V.INVENTORY_ITEM_ID,
1505 DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
1506 1,V.INVENTORY_ITEM_ID, -1)),-1)
1507 AND V.INVENTORY_ITEM_ID=
1508 DECODE(R.INCLUDE_SALES_ORDERS, 2,
1509 DECODE(R.INCLUDE_INTERNAL_ORDERS, 2,
1510 DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2,
1511 DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 2,
1512 DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 2, -1,
1513 V.INVENTORY_ITEM_ID),
1514 V.INVENTORY_ITEM_ID),
1515 V.INVENTORY_ITEM_ID),
1516 V.INVENTORY_ITEM_ID),
1517 V.INVENTORY_ITEM_ID);
1518
1519
1520 CURSOR ONT_SALESORDER_DEMAND_CURSOR IS
1521 SELECT
1522 1 reservation_type, -- fake
1523 2 supply_demand_source_type,
1524 2 txn_source_type_id, -- fake
1525 L.LINE_ID supply_demand_source_id,
1526 1 supply_demand_type,
1527 -1*(L.ORDERED_QUANTITY-NVL(SHIPPED_QUANTITY, 0)) supply_demand_quantity,
1528 TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) supply_demand_date,
1529 I.INVENTORY_ITEM_ID inventory_item_id,
1530 I.ORGANIZATION_ID organization_id
1531 FROM BOM_CALENDAR_DATES C ,
1532 OE_ORDER_LINES L,
1533 MTL_ATP_RULES R ,
1534 MTL_GROUP_ATPS_VIEW G ,
1535 MTL_PARAMETERS P ,
1536 MTL_SYSTEM_ITEMS I
1537 WHERE I.ATP_FLAG in ('C', 'Y')
1538 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
1539 AND G.ATP_GROUP_ID = p_group_id
1540 AND G.ORGANIZATION_ID = I.ORGANIZATION_ID
1541 AND G.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1542 AND G.ATP_RULE_ID = R.RULE_ID
1543 AND R.INCLUDE_SALES_ORDERS = 1
1544 AND L.SHIP_FROM_ORG_ID = I.ORGANIZATION_ID
1545 AND L.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1546 AND L.VISIBLE_DEMAND_FLAG = 'Y'
1547 AND L.ORDERED_QUANTITY > NVL(L.SHIPPED_QUANTITY,0)
1548 AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1549 NULL, C.PRIOR_DATE,
1550 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID,
1551 1,
1552 SYSDATE,
1553 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
1554 AND C.CALENDAR_CODE = P.CALENDAR_CODE
1555 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
1556 AND C.CALENDAR_DATE = TRUNC(L.SCHEDULE_SHIP_DATE);
1557
1558
1559 l_oe_demand_rec OE_SALESORDER_DEMAND_CURSOR%ROWTYPE;
1560 l_ont_demand_rec ONT_SALESORDER_DEMAND_CURSOR%ROWTYPE;
1561 l_oe_install VARCHAR2(10);
1562 j NUMBER := Nvl(p_demand_table.LAST,0) + 1;
1563 BEGIN
1564
1565 SELECT OE_INSTALL.Get_Active_Product
1566 INTO l_oe_install
1567 FROM DUAL;
1568
1569 IF l_oe_install = 'OE' THEN
1570 OPEN OE_SALESORDER_DEMAND_CURSOR;
1571
1572 LOOP
1573 FETCH OE_SALESORDER_DEMAND_CURSOR INTO l_oe_demand_rec;
1574 EXIT WHEN OE_SALESORDER_DEMAND_CURSOR%NOTFOUND;
1575
1576 p_demand_table(j).reservation_type := l_oe_demand_rec.reservation_type;
1577 p_demand_table(j).supply_demand_source_type := l_oe_demand_rec.supply_demand_source_type;
1578 p_demand_table(j).txn_source_type_id := l_oe_demand_rec.txn_source_type_id;
1579 p_demand_table(j).supply_demand_source_id := l_oe_demand_rec.supply_demand_source_id;
1580 p_demand_table(j).supply_demand_type := l_oe_demand_rec.supply_demand_type;
1581 p_demand_table(j).supply_demand_quantity := l_oe_demand_rec.supply_demand_quantity;
1582 p_demand_table(j).supply_demand_date := l_oe_demand_rec.supply_demand_date;
1583 p_demand_table(j).inventory_item_id := l_oe_demand_rec.inventory_item_id;
1584 p_demand_table(j).organization_id := l_oe_demand_rec.organization_id;
1585
1586 j := j+1;
1587
1588 END LOOP;
1589
1590 CLOSE OE_SALESORDER_DEMAND_CURSOR;
1591
1592 ELSE
1593
1594 -- fix for bug 4270650
1595 MO_GLOBAL.SET_POLICY_CONTEXT ('S', p_org_id);
1596
1597 OPEN ONT_SALESORDER_DEMAND_CURSOR;
1598
1599 LOOP
1600 FETCH ONT_SALESORDER_DEMAND_CURSOR INTO l_ont_demand_rec;
1601 EXIT WHEN ONT_SALESORDER_DEMAND_CURSOR%NOTFOUND;
1602
1603 p_demand_table(j).reservation_type := l_ont_demand_rec.reservation_type;
1604 p_demand_table(j).supply_demand_source_type := l_ont_demand_rec.supply_demand_source_type;
1605 p_demand_table(j).txn_source_type_id := l_ont_demand_rec.txn_source_type_id;
1606 p_demand_table(j).supply_demand_source_id := l_ont_demand_rec.supply_demand_source_id;
1607 p_demand_table(j).supply_demand_type := l_ont_demand_rec.supply_demand_type;
1608 p_demand_table(j).supply_demand_quantity := l_ont_demand_rec.supply_demand_quantity;
1609 p_demand_table(j).supply_demand_date := l_ont_demand_rec.supply_demand_date;
1610 p_demand_table(j).inventory_item_id := l_ont_demand_rec.inventory_item_id;
1611 p_demand_table(j).organization_id := l_ont_demand_rec.organization_id;
1612
1613 j := j+1;
1614
1615 END LOOP;
1616
1617 CLOSE ONT_SALESORDER_DEMAND_CURSOR;
1618 END IF;
1619
1620 END Collect_SalesOrder_Demand;
1621
1622
1623 END WPS_SUPPLY_DEMAND;