[Home] [Help]
PACKAGE BODY: APPS.MRP_ATP_COLLECTION
Source
1 PACKAGE BODY MRP_ATP_COLLECTION AS
2 /* $Header: MRPATPCB.pls 115.4 1999/11/11 18:25:07 pkm ship $ */
3 PROCEDURE Collect_Atp_Info(
4 ERRBUF OUT VARCHAR2,
5 RETCODE OUT NUMBER)
6 IS
7 l_oe_install VARCHAR2(3);
8 BEGIN
9
10 RETCODE := 0;
11 -- Before inserting new records, delete existing records
12 -- to prevent duplicates.
13
14 DELETE FROM mrp_atp_supply_demand;
15
16 -- SUPPLY DEMAND SOURCE TYPE (existing in mfg_lookups:)
17 -- 1: Purchase order
18 -- 2: Sales order
19 -- 3: Account number
20 -- 4: WIP repetitive schedule
21 -- 5: WIP discrete job
22 -- 6: Account alias
23 -- 7: WIP nonstandard job
24 -- 8: Onhand quantity
25 -- 9: Reserved sales order
26 -- 10: Reserved account number
27 -- 11: Reserved account alias
28 -- 12: Intransit receipt
29 -- 13: Discrete MPS
30 -- 14: Repetitive MPS
31 -- 15: Onhand Reservation
32 -- 16: User supply
33 -- 17: User Demand
34 -- 18: PO Requisition
35 -- 19: Reserved user source
36 -- 20: Internal requisition
37 -- 21: Internal order
38 -- 22: Reserved internal order
39 -- 23: WIP Supply Reservation
40 -- 24: Flow Schedule
41
42 -- SUPPLY DEMAND TYPE:
43 -- 1: Demand
44 -- 2: Supply
45
46
47 -- source_identifier1: instance id. -1 for non-distributed environment
48 -- source_identifier2: null for now
49
50 -- plan_id: -1 if it is from execution system
51 -- (-2 if populated from scheduling manager)
52
53 -- Inserting new records.
54
55 -- First insert onhand information.
56 INSERT INTO mrp_atp_supply_demand(
57 source_identifier1,
58 source_identifier2,
59 source_identifier3,
60 plan_id,
61 organization_id,
62 inventory_item_id,
63 supply_demand_date,
64 supply_demand_source_type,
65 supply_demand_quantity,
66 reservation_quantity,
67 last_update_date,
68 last_updated_by,
69 creation_date,
70 created_by,
71 demand_class,
72 supply_demand_type,
73 product_family_item_id)
74 SELECT -1, -- instance id
75 to_number(NULL),
76 0, -- source identifier
77 -1, -- plan_id
78 org_id,
79 item_id,
80 MRP_CALENDAR.next_work_day(org_id, 1, SYSDATE),
81 8, -- onhand
82 sum(transaction_qty),
83 NULL, -- reservation quantity
84 SYSDATE,
85 FND_GLOBAL.USER_ID,
86 SYSDATE,
87 FND_GLOBAL.USER_ID,
88 NULL,
89 2, -- supply
90 NULL
91 FROM (
92 SELECT I.INVENTORY_ITEM_ID item_id,
93 I.ORGANIZATION_ID org_id,
94 Q.TRANSACTION_QUANTITY transaction_qty
95 FROM MTL_SECONDARY_INVENTORIES S,
96 MTL_PARAMETERS P ,
97 MTL_ONHAND_QUANTITIES Q ,
98 MTL_ATP_RULES R ,
99 MTL_SYSTEM_ITEMS I
100 WHERE I.ATP_FLAG in ('Y', 'C')
101 AND Q.ORGANIZATION_ID = I.ORGANIZATION_ID
102 AND Q.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
103 AND S.SECONDARY_INVENTORY_NAME = Q.SUBINVENTORY_CODE
104 AND S.ORGANIZATION_ID = Q.ORGANIZATION_ID
105 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
106 AND S.INVENTORY_ATP_CODE = 1 -- atpable
107 AND Q.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_ONHAND_AVAILABLE,
108 2, -1, Q.INVENTORY_ITEM_ID)
109 AND P.ORGANIZATION_ID=I.ORGANIZATION_ID
110 UNION ALL
111 SELECT I.INVENTORY_ITEM_ID item_id,
112 I.ORGANIZATION_ID org_id,
113 T.PRIMARY_QUANTITY transaction_qty
114 FROM MTL_SECONDARY_INVENTORIES S,
115 MTL_PARAMETERS P ,
116 MTL_MATERIAL_TRANSACTIONS_TEMP T ,
117 MTL_ATP_RULES R ,
118 MTL_SYSTEM_ITEMS I
119 WHERE I.ATP_FLAG in ('Y', 'C')
120 AND T.ORGANIZATION_ID = I.ORGANIZATION_ID
121 AND T.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
122 AND S.SECONDARY_INVENTORY_NAME = T.SUBINVENTORY_CODE
123 AND S.ORGANIZATION_ID = T.ORGANIZATION_ID
124 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
125 AND S.INVENTORY_ATP_CODE = 1 -- atpable
126 AND T.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_ONHAND_AVAILABLE,
127 2, -1, T.INVENTORY_ITEM_ID)
128 AND P.ORGANIZATION_ID=I.ORGANIZATION_ID
129 )
130 GROUP BY item_id, org_id;
131
132 -- insert MPS supply
133 INSERT INTO mrp_atp_supply_demand(
134 source_identifier1,
135 source_identifier2,
136 source_identifier3,
137 plan_id,
138 organization_id,
139 inventory_item_id,
140 supply_demand_date,
141 supply_demand_source_type,
142 supply_demand_quantity,
143 reservation_quantity,
144 last_update_date,
145 last_updated_by,
146 creation_date,
147 created_by,
148 demand_class,
149 supply_demand_type,
150 product_family_item_id)
151 SELECT -1, -- instance id
152 NULL,
153 D2.MPS_TRANSACTION_ID ,
154 -1, -- plan_id
155 I.ORGANIZATION_ID,
156 I.INVENTORY_ITEM_ID,
157 C.CALENDAR_DATE,
158 DECODE(I.REPETITIVE_PLANNING_FLAG, 'Y', 14, 13) ,
159 DECODE(I.REPETITIVE_PLANNING_FLAG, 'Y',D2.REPETITIVE_DAILY_RATE,
160 D2.SCHEDULE_QUANTITY) ,
161 NULL, -- reservation quantity
162 SYSDATE,
163 FND_GLOBAL.USER_ID,
164 SYSDATE,
165 FND_GLOBAL.USER_ID,
166 D1.DEMAND_CLASS,
167 2, -- supply
168 DECODE(I.BOM_ITEM_TYPE, 5, I.INVENTORY_ITEM_ID, NULL)
169 FROM BOM_CALENDAR_DATES C ,
170 MTL_ATP_RULES R ,
171 MTL_PARAMETERS P ,
172 MTL_SYSTEM_ITEMS I,
173 MRP_SCHEDULE_DATES D2,
174 MRP_SCHEDULE_DESIGNATORS D1
175 WHERE D1.INVENTORY_ATP_FLAG=1
176 AND D1.SCHEDULE_TYPE=2
177 AND D2.SCHEDULE_DESIGNATOR=D1.SCHEDULE_DESIGNATOR
178 AND DECODE(I.REPETITIVE_PLANNING_FLAG, 'Y',
179 D2.REPETITIVE_DAILY_RATE,D2.SCHEDULE_QUANTITY) > 0
180 AND D2.SUPPLY_DEMAND_TYPE = 2
181 AND D2.SCHEDULE_LEVEL = 2
182 AND I.ORGANIZATION_ID=D2.ORGANIZATION_ID
183 AND I.INVENTORY_ITEM_ID= D2.INVENTORY_ITEM_ID
184 AND I.ATP_FLAG in ('C', 'Y')
185 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
186 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
187 AND (R.INCLUDE_REP_MPS = 1 OR R.INCLUDE_DISCRETE_MPS = 1)
188 AND C.CALENDAR_CODE=P.CALENDAR_CODE
189 AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
190 AND C.CALENDAR_DATE BETWEEN D2.SCHEDULE_DATE
191 AND NVL(D2.RATE_END_DATE, D2.SCHEDULE_DATE)
192 AND C.SEQ_NUM IS NOT NULL
193 AND C.CALENDAR_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
194 NULL, C.CALENDAR_DATE,
195 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
196 -NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)));
197
198 -- insert user defined supply
199 INSERT INTO mrp_atp_supply_demand(
200 source_identifier1,
201 source_identifier2,
202 source_identifier3,
203 plan_id,
204 organization_id,
205 inventory_item_id,
206 supply_demand_date,
207 supply_demand_source_type,
208 supply_demand_quantity,
209 reservation_quantity,
210 last_update_date,
211 last_updated_by,
212 creation_date,
213 created_by,
214 demand_class,
215 supply_demand_type,
216 product_family_item_id)
217 SELECT -1,
218 NULL ,
219 U.SOURCE_ID ,
220 -1,
221 U.ORGANIZATION_ID,
222 U.INVENTORY_ITEM_ID,
223 C.NEXT_DATE,
224 16 ,
225 U.PRIMARY_UOM_QUANTITY ,
226 NULL, -- reservation quantity
230 FND_GLOBAL.USER_ID,
227 SYSDATE,
228 FND_GLOBAL.USER_ID,
229 SYSDATE,
231 U.DEMAND_CLASS,
232 2, -- supply
233 NULL
234 FROM BOM_CALENDAR_DATES C,
235 MTL_ATP_RULES R ,
236 MTL_PARAMETERS P ,
237 MTL_SYSTEM_ITEMS I ,
238 MTL_USER_SUPPLY U
239 WHERE I.ORGANIZATION_ID = U.ORGANIZATION_ID
240 AND I.INVENTORY_ITEM_ID = U.INVENTORY_ITEM_ID
241 AND I.ATP_FLAG in ('C', 'Y')
242 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
243 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
244 AND R.INCLUDE_USER_DEFINED_SUPPLY = 1
245 AND C.NEXT_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
246 NULL, C.NEXT_DATE,
247 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
248 -NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)))
249 AND C.CALENDAR_CODE = P.CALENDAR_CODE
250 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
251 AND C.CALENDAR_DATE = TRUNC(U.EXPECTED_DELIVERY_DATE);
252
253 -- insert mtl_supply information, that is PO, REQ, SHIP, RCV
254 -- question here,
255 -- I select NVL(S.MRP_PRIMARY_QUANTITY, S.TO_ORG_PRIMARY_QUANTITY)
256 -- as the supply_demand_quantity if discrete mps is included,
257 -- S.TO_ORG_PRIMARY_QUANTITY if not included.
258 -- However, in inldsd.ppc, it selects
259 -- S.TO_ORG_PRIMARY_QUANTITY for shipment, NVL(S.MRP_PRIMARY_QUANTITY, 0)
260 -- if discrete mps is included, S.TO_ORG_PRIMARY_QUANTITY if not included
261
262 INSERT INTO mrp_atp_supply_demand(
263 source_identifier1,
264 source_identifier2,
265 source_identifier3,
266 plan_id,
267 organization_id,
268 inventory_item_id,
269 supply_demand_date,
270 supply_demand_source_type,
271 supply_demand_quantity,
272 reservation_quantity,
273 last_update_date,
274 last_updated_by,
275 creation_date,
276 created_by,
277 demand_class,
278 supply_demand_type,
279 product_family_item_id)
280 SELECT -1,
281 NULL,
282 DECODE( S.PO_HEADER_ID,
283 NULL,DECODE(S.SUPPLY_TYPE_CODE,
284 'REQ', REQ_HEADER_ID,
285 SHIPMENT_HEADER_ID),
286 PO_HEADER_ID),
287 -1,
288 I.ORGANIZATION_ID,
289 I.INVENTORY_ITEM_ID,
290 C.NEXT_DATE,
291 DECODE( S.PO_HEADER_ID,
292 NULL, DECODE(S.SUPPLY_TYPE_CODE,'REQ',
293 DECODE(S.FROM_ORGANIZATION_ID,NULL,18,20),
294 12),
295 1) ,
296 DECODE(R.INCLUDE_DISCRETE_MPS,
297 1,NVL(S.MRP_PRIMARY_QUANTITY, S.TO_ORG_PRIMARY_QUANTITY),
298 S.TO_ORG_PRIMARY_QUANTITY),
299 NULL, -- reservation quantity
300 SYSDATE,
301 FND_GLOBAL.USER_ID,
302 SYSDATE,
306 NULL
303 FND_GLOBAL.USER_ID,
304 NULL,
305 2, -- supply
307 FROM BOM_CALENDAR_DATES C ,
308 MTL_SUPPLY S,
309 MTL_ATP_RULES R ,
310 MTL_PARAMETERS P ,
311 MTL_SYSTEM_ITEMS I
312 WHERE I.ATP_FLAG in ('C', 'Y')
313 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
314 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
315 AND
316 (
317 ( -- this identifies interorg shipping and receiving
318 R.INCLUDE_INTERORG_TRANSFERS = 1 AND
319 S.REQ_HEADER_ID IS NULL AND
320 S.PO_HEADER_ID IS NULL
321 )
322 OR
323 ( -- this identifies internal req
324 S.REQ_HEADER_ID=DECODE(R.INCLUDE_INTERNAL_REQS,
325 1,S.REQ_HEADER_ID) AND
326 S.FROM_ORGANIZATION_ID IS NOT NULL
327 )
328 OR
329 ( -- this identifies vendor req
330 S.SUPPLY_TYPE_CODE= DECODE(R.INCLUDE_VENDOR_REQS,1,'REQ') AND
331 S.FROM_ORGANIZATION_ID IS NULL
332 )
333 OR -- this identifies PO
334 S.PO_HEADER_ID=DECODE(R.INCLUDE_PURCHASE_ORDERS,
335 1, S.PO_HEADER_ID)
336 )
337 AND S.TO_ORGANIZATION_ID=I.ORGANIZATION_ID
338 AND S.ITEM_ID = I.INVENTORY_ITEM_ID
339 AND S.DESTINATION_TYPE_CODE='INVENTORY'
340 AND (S.TO_SUBINVENTORY IS NULL OR
341 EXISTS (SELECT 'X'
342 FROM MTL_SECONDARY_INVENTORIES S2
343 WHERE S2.ORGANIZATION_ID=S.TO_ORGANIZATION_ID
344 AND S2.SECONDARY_INVENTORY_NAME = S.TO_SUBINVENTORY
345 AND S2.INVENTORY_ATP_CODE = 1))
346 AND C.CALENDAR_CODE = P.CALENDAR_CODE
347 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
348 AND C.CALENDAR_DATE = TRUNC(S.EXPECTED_DELIVERY_DATE)
349 AND C.NEXT_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
350 NULL, C.NEXT_DATE,
351 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
352 -NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)));
353
354 -- insert wip discrete job information
355 -- question here: do I need to apply bug 791215 here?
356 -- that is , using net_quantity instead of mps_net_quantity?
357 INSERT INTO mrp_atp_supply_demand(
358 source_identifier1,
359 source_identifier2,
360 source_identifier3,
361 plan_id,
362 organization_id,
363 inventory_item_id,
364 supply_demand_date,
365 supply_demand_source_type,
366 supply_demand_quantity,
367 reservation_quantity,
368 last_update_date,
369 last_updated_by,
370 creation_date,
371 created_by,
372 demand_class,
373 supply_demand_type,
374 product_family_item_id)
375 SELECT -1,
376 NULL,
377 D.WIP_ENTITY_ID,
378 -1,
379 I.ORGANIZATION_ID,
380 I.INVENTORY_ITEM_ID,
381 C.NEXT_DATE,
385 1, DECODE(I.MRP_PLANNING_CODE,
382 DECODE(D.JOB_TYPE, 1, 5, 7) ,
383 (DECODE(R.INCLUDE_DISCRETE_MPS,
384 1, DECODE(D.JOB_TYPE,
386 4, NVL(D.MPS_NET_QUANTITY,0),
387 8, NVL(D.MPS_NET_QUANTITY,0),
388 D.NET_QUANTITY),
389 D.NET_QUANTITY),
390 D.NET_QUANTITY)-D.QUANTITY_COMPLETED-D.QUANTITY_SCRAPPED),
391 NULL, -- reservation quantity
392 SYSDATE,
393 FND_GLOBAL.USER_ID,
394 SYSDATE,
395 FND_GLOBAL.USER_ID,
396 D.DEMAND_CLASS,
397 2, -- supply
398 NULL
399 FROM BOM_CALENDAR_DATES C ,
400 WIP_DISCRETE_JOBS D,
401 MTL_ATP_RULES R ,
402 MTL_PARAMETERS P ,
403 MTL_SYSTEM_ITEMS I
404 WHERE I.ATP_FLAG in ('C', 'Y')
405 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
406 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
407 AND (R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1 OR
408 R.INCLUDE_NONSTD_WIP_RECEIPTS = 1)
409 AND D.STATUS_TYPE IN (1,3,4,6)
410 AND (D.START_QUANTITY-D.QUANTITY_COMPLETED-D.QUANTITY_SCRAPPED) >0
411 AND D.ORGANIZATION_ID=I.ORGANIZATION_ID
412 AND D.PRIMARY_ITEM_ID= I.INVENTORY_ITEM_ID
413 AND (D.JOB_TYPE =DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
414 OR
415 D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
416 AND C.NEXT_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
417 NULL, C.NEXT_DATE,
418 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
419 -NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)))
420 AND C.CALENDAR_CODE = P.CALENDAR_CODE
421 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
422 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE);
423
424
425 -- insert wip neg requirement information
426 -- I have applied bug 454103 here.
427
428 INSERT INTO mrp_atp_supply_demand(
429 source_identifier1,
430 source_identifier2,
431 source_identifier3,
432 plan_id,
433 organization_id,
434 inventory_item_id,
435 supply_demand_date,
436 supply_demand_source_type,
437 supply_demand_quantity,
438 reservation_quantity,
439 last_update_date,
440 last_updated_by,
441 creation_date,
442 created_by,
443 demand_class,
444 supply_demand_type,
445 product_family_item_id)
446 SELECT -1,
447 NULL,
448 D.WIP_ENTITY_ID ,
449 -1,
450 I.ORGANIZATION_ID,
451 I.INVENTORY_ITEM_ID,
452 C.NEXT_DATE,
453 DECODE(D.JOB_TYPE, 1, 5, 7) ,
454 -1*O.REQUIRED_QUANTITY ,
455 NULL, -- reservation quantity
459 FND_GLOBAL.USER_ID,
456 SYSDATE,
457 FND_GLOBAL.USER_ID,
458 SYSDATE,
460 D.DEMAND_CLASS,
461 2, -- supply
462 NULL
463 FROM BOM_CALENDAR_DATES C ,
464 WIP_DISCRETE_JOBS D,
465 WIP_REQUIREMENT_OPERATIONS O ,
466 MTL_ATP_RULES R ,
467 MTL_PARAMETERS P ,
468 MTL_SYSTEM_ITEMS I
469 WHERE I.ATP_FLAG in ('C', 'Y')
470 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
471 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
472 AND (R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1 OR
473 R.INCLUDE_NONSTD_WIP_RECEIPTS = 1)
474 AND O.ORGANIZATION_ID=I.ORGANIZATION_ID
475 AND O.INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID
476 AND O.WIP_SUPPLY_TYPE <> 6
477 AND O.REQUIRED_QUANTITY < 0
478 AND O.OPERATION_SEQ_NUM > 0
479 AND D.WIP_ENTITY_ID= O.WIP_ENTITY_ID
480 AND D.ORGANIZATION_ID = O.ORGANIZATION_ID
481 AND (D.JOB_TYPE= DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 1, -1)
482 OR
483 D.JOB_TYPE = DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 3, -1))
484 AND D.STATUS_TYPE IN (1,3, 4,6)
485 AND C.NEXT_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
486 NULL, C.NEXT_DATE,
487 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
488 -NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)))
489 AND C.CALENDAR_CODE = P.CALENDAR_CODE
490 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
491 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE);
492
493
494 -- insert wip repetitive supply
495
496 INSERT INTO mrp_atp_supply_demand(
497 source_identifier1,
498 source_identifier2,
499 source_identifier3,
500 plan_id,
501 organization_id,
502 inventory_item_id,
503 supply_demand_date,
504 supply_demand_source_type,
505 supply_demand_quantity,
506 reservation_quantity,
507 last_update_date,
508 last_updated_by,
509 creation_date,
510 created_by,
511 demand_class,
512 supply_demand_type,
513 product_family_item_id )
514 SELECT -1,
515 NULL,
516 WRS.WIP_ENTITY_ID ,
517 -1,
518 I.ORGANIZATION_ID,
519 I.INVENTORY_ITEM_ID,
520 C.NEXT_DATE,
521 4 ,
522 DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*
523 (C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM) -WRS.QUANTITY_COMPLETED),
524 -1, WRS.DAILY_PRODUCTION_RATE* LEAST(C.NEXT_SEQ_NUM
525 -C1.NEXT_SEQ_NUM+1, WRS.PROCESSING_WORK_DAYS)
526 -WRS.QUANTITY_COMPLETED,
527 LEAST(C1.NEXT_SEQ_NUM+WRS.PROCESSING_WORK_DAYS
528 -C.NEXT_SEQ_NUM,1) *WRS.DAILY_PRODUCTION_RATE) ,
529 NULL, -- reservation quantity
530 SYSDATE,
531 FND_GLOBAL.USER_ID,
532 SYSDATE,
533 FND_GLOBAL.USER_ID,
534 WRS.DEMAND_CLASS,
535 2, -- supply
536 NULL
540 WIP_REPETITIVE_ITEMS WRI,
537 FROM BOM_CALENDAR_DATES C ,
538 BOM_CALENDAR_DATES C1 ,
539 WIP_REPETITIVE_SCHEDULES WRS ,
541 MTL_ATP_RULES R ,
542 MTL_PARAMETERS P ,
543 MTL_SYSTEM_ITEMS I
544 WHERE I.ATP_FLAG in ('C', 'Y')
545 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
546 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
547 AND R.INCLUDE_REP_WIP_RECEIPTS = 1
548 AND WRI.ORGANIZATION_ID = I.ORGANIZATION_ID
549 AND WRI.PRIMARY_ITEM_ID = I.INVENTORY_ITEM_ID
550 AND WRS.WIP_ENTITY_ID = WRI.WIP_ENTITY_ID
551 AND WRS.LINE_ID = WRI.LINE_ID
552 AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
553 AND WRS.STATUS_TYPE IN (1,3,4,6)
554 AND C1.CALENDAR_CODE=P.CALENDAR_CODE
555 AND C1.EXCEPTION_SET_ID= P.CALENDAR_EXCEPTION_SET_ID
556 AND C1.CALENDAR_DATE= TRUNC(WRS.FIRST_UNIT_COMPLETION_DATE)
557 AND C.CALENDAR_CODE=P.CALENDAR_CODE
558 AND C.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
559 AND C.SEQ_NUM BETWEEN C1.NEXT_SEQ_NUM AND
560 C1.NEXT_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
561 AND WRS.DAILY_PRODUCTION_RATE*
562 LEAST(C.NEXT_SEQ_NUM-C1.NEXT_SEQ_NUM+1,
563 WRS.PROCESSING_WORK_DAYS) > WRS.QUANTITY_COMPLETED
564 AND C.CALENDAR_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
565 NULL, C.CALENDAR_DATE,
566 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
567 -NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)));
568
569 -- insert flow schedule supply information
570
571 INSERT INTO mrp_atp_supply_demand(
572 source_identifier1,
573 source_identifier2,
574 source_identifier3,
575 plan_id,
576 organization_id,
577 inventory_item_id,
578 supply_demand_date,
579 supply_demand_source_type,
580 supply_demand_quantity,
581 reservation_quantity,
582 last_update_date,
583 last_updated_by,
584 creation_date,
585 created_by,
586 demand_class,
587 supply_demand_type,
588 product_family_item_id)
589 SELECT -1,
590 NULL,
591 D.WIP_ENTITY_ID ,
592 -1,
593 I.ORGANIZATION_ID,
594 I.INVENTORY_ITEM_ID,
595 C.NEXT_DATE,
596 24 ,
597 (DECODE(R.INCLUDE_DISCRETE_MPS,
598 1, DECODE(I.MRP_PLANNING_CODE,
599 4, NVL(D.MPS_NET_QUANTITY,0),
600 8, NVL(D.MPS_NET_QUANTITY,0),
601 D.PLANNED_QUANTITY),
602 D.PLANNED_QUANTITY) - D.QUANTITY_COMPLETED) ,
603 NULL, -- reservation quantity
604 SYSDATE,
605 FND_GLOBAL.USER_ID,
606 SYSDATE,
607 FND_GLOBAL.USER_ID,
608 D.DEMAND_CLASS,
612 WIP_FLOW_SCHEDULES D,
609 2, -- supply
610 NULL
611 FROM BOM_CALENDAR_DATES C ,
613 MTL_PARAMETERS P ,
614 MTL_ATP_RULES R,
615 MTL_SYSTEM_ITEMS I
616 WHERE I.ATP_FLAG in ('C', 'Y')
617 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
618 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
619 AND R.INCLUDE_FLOW_SCHEDULE_RECEIPTS = 1
620 AND D.STATUS = 1
621 AND (D.PLANNED_QUANTITY-D.QUANTITY_COMPLETED) >0
622 AND D.ORGANIZATION_ID=I.ORGANIZATION_ID
623 AND D.PRIMARY_ITEM_ID= I.INVENTORY_ITEM_ID
624 AND C.CALENDAR_CODE = P.CALENDAR_CODE
625 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
626 AND C.CALENDAR_DATE = TRUNC(D.SCHEDULED_COMPLETION_DATE)
627 AND C.NEXT_DATE >= MRP_CALENDAR.next_work_day(
628 P.ORGANIZATION_ID,1,SYSDATE);
629
630 -- now we insert the demand information
631 -- insert wip discrete requirement information
632
633 INSERT INTO mrp_atp_supply_demand(
634 source_identifier1,
635 source_identifier2,
636 source_identifier3,
637 plan_id,
638 organization_id,
639 inventory_item_id,
640 supply_demand_date,
641 supply_demand_source_type,
642 supply_demand_quantity,
643 reservation_quantity,
644 last_update_date,
645 last_updated_by,
646 creation_date,
647 created_by,
648 demand_class,
649 supply_demand_type,
650 product_family_item_id)
651 SELECT -1,
652 NULL,
653 D.WIP_ENTITY_ID ,
654 -1,
655 I.ORGANIZATION_ID,
656 I.INVENTORY_ITEM_ID,
657 C.PRIOR_DATE,
658 DECODE(D.JOB_TYPE, 1, 5, 7) ,
659 LEAST(-1*(O.REQUIRED_QUANTITY-O.QUANTITY_ISSUED),0) ,
660 NULL, -- reservation quantity
661 SYSDATE,
662 FND_GLOBAL.USER_ID,
663 SYSDATE,
664 FND_GLOBAL.USER_ID,
665 D.DEMAND_CLASS,
666 1, -- demand
667 I.PRODUCT_FAMILY_ITEM_ID
668 FROM BOM_CALENDAR_DATES C ,
669 WIP_DISCRETE_JOBS D,
670 WIP_REQUIREMENT_OPERATIONS O ,
671 MTL_ATP_RULES R ,
672 MTL_PARAMETERS P ,
673 MTL_SYSTEM_ITEMS I
674 WHERE I.ATP_FLAG in ('C', 'Y')
675 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
676 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
677 AND (R.INCLUDE_DISCRETE_WIP_DEMAND = 1 OR
678 R.INCLUDE_NONSTD_WIP_DEMAND = 1)
679 AND O.ORGANIZATION_ID=I.ORGANIZATION_ID
680 AND O.INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID
681 AND O.WIP_SUPPLY_TYPE <> 6
682 AND O.REQUIRED_QUANTITY > 0
683 AND (O.REQUIRED_QUANTITY-O.QUANTITY_ISSUED) > 0
684 AND O.OPERATION_SEQ_NUM > 0
685 AND D.ORGANIZATION_ID=O.ORGANIZATION_ID
686 AND D.WIP_ENTITY_ID=O.WIP_ENTITY_ID
687 AND (D.JOB_TYPE=DECODE(R.INCLUDE_DISCRETE_WIP_DEMAND, 1, 1, -1)
688 OR
689 D.JOB_TYPE =DECODE(R.INCLUDE_NONSTD_WIP_DEMAND, 1, 3, -1))
690 AND D.STATUS_TYPE IN (1,3,4,6)
691 AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
692 NULL, C.PRIOR_DATE,
693 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
694 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
695 AND C.CALENDAR_CODE = P.CALENDAR_CODE
696 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
697 AND C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED);
698
699
700 -- insert wip repetitive requirement information
701 -- unlike inldsd.ppc, I combine DRJ1 and DRJ2
702
703 INSERT INTO mrp_atp_supply_demand(
704 source_identifier1,
705 source_identifier2,
706 source_identifier3,
707 plan_id,
708 organization_id,
709 inventory_item_id,
710 supply_demand_date,
711 supply_demand_source_type,
712 supply_demand_quantity,
713 reservation_quantity,
714 last_update_date,
715 last_updated_by,
716 creation_date,
717 created_by,
718 demand_class,
719 supply_demand_type,
720 product_family_item_id)
721 SELECT -1,
722 NULL,
723 WRS.WIP_ENTITY_ID ,
724 -1,
725 I.ORGANIZATION_ID,
726 I.INVENTORY_ITEM_ID,
727 C.PRIOR_DATE,
728 4 ,
729 DECODE(SIGN(WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*
730 (C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM)-WRO.QUANTITY_ISSUED),
731 -1, -1*(WRS.DAILY_PRODUCTION_RATE*
732 WRO.QUANTITY_PER_ASSEMBLY*
733 LEAST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM+1,
734 WRS.PROCESSING_WORK_DAYS)-WRO.QUANTITY_ISSUED),
735 GREATEST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM-
736 WRS.PROCESSING_WORK_DAYS,-1)*WRS.DAILY_PRODUCTION_RATE
737 *WRO.QUANTITY_PER_ASSEMBLY) ,
738 NULL, -- reservation quantity
739 SYSDATE,
740 FND_GLOBAL.USER_ID,
741 SYSDATE,
742 FND_GLOBAL.USER_ID,
743 WRS.DEMAND_CLASS,
744 1, -- demand
745 I.PRODUCT_FAMILY_ITEM_ID
746 FROM BOM_CALENDAR_DATES C ,
747 BOM_CALENDAR_DATES C1 ,
751 MTL_PARAMETERS P ,
748 WIP_REPETITIVE_SCHEDULES WRS ,
749 WIP_REQUIREMENT_OPERATIONS WRO,
750 MTL_ATP_RULES R ,
752 MTL_SYSTEM_ITEMS I
753 WHERE I.ATP_FLAG in ('C', 'Y')
754 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
755 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
756 AND R.INCLUDE_REP_WIP_DEMAND = 1
757 AND WRO.ORGANIZATION_ID = I.ORGANIZATION_ID
758 AND WRO.INVENTORY_ITEM_ID= I.INVENTORY_ITEM_ID
759 AND WRO.WIP_SUPPLY_TYPE <> 6
760 AND WRO.REQUIRED_QUANTITY > 0
761 AND (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED) > 0
762 AND WRO.OPERATION_SEQ_NUM > 0
763 AND WRS.ORGANIZATION_ID = WRO.ORGANIZATION_ID
764 AND WRS.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
765 AND WRS.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
766 AND WRS.STATUS_TYPE IN (1,3,4,6)
767 AND WRS.DAILY_PRODUCTION_RATE*WRO.QUANTITY_PER_ASSEMBLY*
768 LEAST(C.PRIOR_SEQ_NUM-C1.PRIOR_SEQ_NUM+1,
769 WRS.PROCESSING_WORK_DAYS) >WRO.QUANTITY_ISSUED
770 AND C1.CALENDAR_CODE= P.CALENDAR_CODE
771 AND C1.EXCEPTION_SET_ID=P.CALENDAR_EXCEPTION_SET_ID
772 AND C1.CALENDAR_DATE=TRUNC(WRS.FIRST_UNIT_START_DATE)
773 AND C.CALENDAR_CODE=P.CALENDAR_CODE
774 AND C.EXCEPTION_SET_ID= P.CALENDAR_EXCEPTION_SET_ID
775 AND C.SEQ_NUM BETWEEN C1.PRIOR_SEQ_NUM AND
776 C1.PRIOR_SEQ_NUM + CEIL(WRS.PROCESSING_WORK_DAYS - 1)
777 AND C.CALENDAR_DATE >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
778 NULL, C.CALENDAR_DATE,
779 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
780 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)));
781
782 -- insert user defined demand information
783 INSERT INTO mrp_atp_supply_demand(
784 source_identifier1,
785 source_identifier2,
786 source_identifier3,
787 plan_id,
788 organization_id,
789 inventory_item_id,
790 supply_demand_date,
791 supply_demand_source_type,
792 supply_demand_quantity,
793 reservation_quantity,
794 last_update_date,
795 last_updated_by,
796 creation_date,
797 created_by,
798 demand_class,
799 supply_demand_type,
800 product_family_item_id)
801 SELECT -1,
802 NULL,
803 U.SOURCE_ID,
804 -1,
805 U.ORGANIZATION_ID,
806 U.INVENTORY_ITEM_ID,
807 C.PRIOR_DATE,
808 17 ,
809 -1*U.PRIMARY_UOM_QUANTITY ,
810 NULL, -- reservation quantity
811 SYSDATE,
812 FND_GLOBAL.USER_ID,
813 SYSDATE,
814 FND_GLOBAL.USER_ID,
815 U.DEMAND_CLASS,
816 1, -- demand
817 I.PRODUCT_FAMILY_ITEM_ID
818 FROM BOM_CALENDAR_DATES C,
819 MTL_ATP_RULES R ,
820 MTL_PARAMETERS P ,
821 MTL_SYSTEM_ITEMS I ,
822 MTL_USER_DEMAND U
823 WHERE I.ORGANIZATION_ID = U.ORGANIZATION_ID
824 AND I.INVENTORY_ITEM_ID = U.INVENTORY_ITEM_ID
825 AND I.ATP_FLAG in ('C', 'Y')
826 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
827 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
828 AND R.INCLUDE_USER_DEFINED_DEMAND = 1
829 AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
830 NULL, C.PRIOR_DATE,
831 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
832 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
833 AND C.CALENDAR_CODE = P.CALENDAR_CODE
834 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
835 AND C.CALENDAR_DATE = TRUNC(U.REQUIREMENT_DATE);
836
837 -- insert wip flow schedule demand information
838 -- haven't added the logic to explode phantom
839
840 INSERT INTO mrp_atp_supply_demand(
841 source_identifier1,
842 source_identifier2,
843 source_identifier3,
844 plan_id,
845 organization_id,
846 inventory_item_id,
847 supply_demand_date,
848 supply_demand_source_type,
849 supply_demand_quantity,
850 reservation_quantity,
851 last_update_date,
852 last_updated_by,
853 creation_date,
854 created_by,
855 demand_class,
856 supply_demand_type,
857 product_family_item_id)
858 SELECT -1,
859 NULL,
860 F.WIP_ENTITY_ID ,
861 -1,
862 I.ORGANIZATION_ID,
863 I.INVENTORY_ITEM_ID,
864 C.PRIOR_DATE,
865 24 ,
866 LEAST(-1*(F.PLANNED_QUANTITY-F.QUANTITY_COMPLETED)*
867 COMPONENT_QUANTITY, 0),
868 NULL, -- reservation quantity
869 SYSDATE,
870 FND_GLOBAL.USER_ID,
871 SYSDATE,
872 FND_GLOBAL.USER_ID,
873 F.DEMAND_CLASS,
874 1, -- demand
875 I.PRODUCT_FAMILY_ITEM_ID
876 FROM WIP_FLOW_SCHEDULES F,
877 BOM_BILL_OF_MATERIALS BOM ,
878 BOM_INVENTORY_COMPONENTS BIC ,
879 BOM_CALENDAR_DATES C ,
880 MTL_PARAMETERS P ,
881 MTL_ATP_RULES R,
882 MTL_SYSTEM_ITEMS I
886 AND R.INCLUDE_FLOW_SCHEDULE_DEMAND = 1
883 WHERE I.ATP_FLAG in ('C', 'Y')
884 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
885 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
887 AND BIC.COMPONENT_ITEM_ID = I.INVENTORY_ITEM_ID
888 AND TRUNC(BIC.EFFECTIVITY_DATE)<=TRUNC(F.SCHEDULED_COMPLETION_DATE)
889 AND TRUNC(NVL(BIC.DISABLE_DATE, F.SCHEDULED_COMPLETION_DATE+1))
890 > TRUNC(F.SCHEDULED_COMPLETION_DATE)
891 AND BIC.COMPONENT_QUANTITY > 0
892 AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
893 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
894 AND F.PRIMARY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
895 AND F.ORGANIZATION_ID = BOM.ORGANIZATION_ID
896 AND F.STATUS = 1
897 AND (F.PLANNED_QUANTITY - F.QUANTITY_COMPLETED) >0
898 AND C.CALENDAR_CODE = P.CALENDAR_CODE
899 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
900 AND C.CALENDAR_DATE = TRUNC(F.SCHEDULED_COMPLETION_DATE)
901 AND C.PRIOR_DATE>=MRP_CALENDAR.next_work_day
902 (P.ORGANIZATION_ID, 1, SYSDATE);
903
904 SELECT OE_INSTALL.Get_Active_Product
905 INTO l_oe_install
906 FROM DUAL;
907
908 IF l_oe_install = 'OE' THEN
909
910 -- insert sales order demand
911 INSERT INTO mrp_atp_supply_demand(
912 source_identifier1,
913 source_identifier2,
914 source_identifier3,
915 source_identifier4,
916 plan_id,
917 organization_id,
918 inventory_item_id,
919 supply_demand_date,
920 supply_demand_source_type,
921 supply_demand_quantity,
922 reservation_quantity,
923 last_update_date,
924 last_updated_by,
925 creation_date,
926 created_by,
927 demand_class,
928 supply_demand_type,
929 product_family_item_id)
930 SELECT -1,
931 NULL,
932 D.DEMAND_SOURCE_LINE,
933 D.DEMAND_SOURCE_HEADER_ID,
934 -1,
935 I.ORGANIZATION_ID,
936 I.INVENTORY_ITEM_ID,
937 C.PRIOR_DATE,
938 DECODE(D.DEMAND_SOURCE_TYPE,
939 2,DECODE(D.RESERVATION_TYPE,1,2, 3,23,9),
940 8,DECODE(D.RESERVATION_TYPE,1,21,22),
941 D.DEMAND_SOURCE_TYPE),
942 -1*(D.PRIMARY_UOM_QUANTITY-
943 GREATEST(NVL(D.RESERVATION_QUANTITY,0),D.COMPLETED_QUANTITY)),
944 NULL,
945 SYSDATE,
946 FND_GLOBAL.USER_ID,
947 SYSDATE,
948 FND_GLOBAL.USER_ID,
949 D.DEMAND_CLASS,
950 1, -- demand
951 I.PRODUCT_FAMILY_ITEM_ID
952 FROM BOM_CALENDAR_DATES C ,
953 MTL_DEMAND D,
957 WHERE I.ATP_FLAG in ('C', 'Y')
954 MTL_ATP_RULES R ,
955 MTL_PARAMETERS P ,
956 MTL_SYSTEM_ITEMS I
958 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
959 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
960 AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
961 AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
962 AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
963 D.COMPLETED_QUANTITY)
964 AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
965 AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
966 AND D.AVAILABLE_TO_ATP = 1
967 AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
968 (SELECT S.SECONDARY_INVENTORY_NAME
969 FROM MTL_SECONDARY_INVENTORIES S
970 WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
971 AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
972 1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
973 AND S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
974 2, 1, S.AVAILABILITY_TYPE)))
975 AND (D.RESERVATION_TYPE = 2
976 OR D.PARENT_DEMAND_ID IS NULL
977 OR (D.RESERVATION_TYPE = 3 AND
978 ((R.include_DISCRETE_WIP_RECEIPTS = 1) or
979 (R.include_NONSTD_WIP_RECEIPTS = 1))))
980 AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
981 NULL, C.PRIOR_DATE,
982 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
983 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
984 AND C.CALENDAR_CODE = P.CALENDAR_CODE
985 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
986 AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE);
987
988 ELSE
989 INSERT INTO mrp_atp_supply_demand(
990 source_identifier1,
991 source_identifier2,
992 source_identifier3,
993 plan_id,
994 organization_id,
995 inventory_item_id,
996 supply_demand_date,
997 supply_demand_source_type,
998 supply_demand_quantity,
999 reservation_quantity,
1000 last_update_date,
1001 last_updated_by,
1002 creation_date,
1003 created_by,
1004 demand_class,
1005 supply_demand_type,
1006 product_family_item_id)
1007 SELECT -1,
1008 NULL,
1009 L.LINE_ID,
1010 -1,
1011 I.ORGANIZATION_ID,
1012 I.INVENTORY_ITEM_ID,
1013 C.PRIOR_DATE,
1014 2 ,
1015 -1*(L.ORDERED_QUANTITY-NVL(SHIPPED_QUANTITY, 0)),
1019 SYSDATE,
1016 NULL, -- reservation quantity
1017 SYSDATE,
1018 FND_GLOBAL.USER_ID,
1020 FND_GLOBAL.USER_ID,
1021 L.DEMAND_CLASS_CODE,
1022 1, -- demand
1023 I.PRODUCT_FAMILY_ITEM_ID
1024 FROM BOM_CALENDAR_DATES C ,
1025 OE_ORDER_LINES L,
1026 MTL_ATP_RULES R ,
1027 MTL_PARAMETERS P ,
1028 MTL_SYSTEM_ITEMS I
1029 WHERE I.ATP_FLAG in ('C', 'Y')
1030 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
1031 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
1032 AND R.INCLUDE_SALES_ORDERS = 1
1033 AND L.SHIP_FROM_ORG_ID = I.ORGANIZATION_ID
1034 AND L.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1035 AND L.VISIBLE_DEMAND_FLAG = 'Y'
1036 AND L.ORDERED_QUANTITY > NVL(L.SHIPPED_QUANTITY,0)
1037 AND C.PRIOR_DATE >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1038 NULL, C.PRIOR_DATE,
1039 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
1040 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)))
1041 AND C.CALENDAR_CODE = P.CALENDAR_CODE
1042 AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
1043 AND C.CALENDAR_DATE = TRUNC(L.SCHEDULE_SHIP_DATE);
1044
1045 END IF;
1046
1047 /*
1048 This part is for planning server
1049
1050 l_instance_id := ****
1051
1052 -- select demand records from ODS for items
1053 INSERT INTO mrp_atp_supply_demand(
1054 source_identifier1,
1055 source_identifier2,
1056 source_identifier3,
1057 plan_id,
1058 organization_id,
1059 inventory_item_id,
1060 supply_demand_date,
1061 supply_demand_source_type,
1062 supply_demand_quantity,
1063 reservation_quantity,
1064 last_update_date,
1065 last_updated_by,
1066 creation_date,
1067 created_by,
1068 demand_class,
1069 supply_demand_type,
1070 product_family_item_id)
1071 SELECT l_instance_id,
1072 NULL,
1073 D.DISPOSITION_ID,
1074 -1,
1075 D.SR_INVENTORY_ITEM_ID,
1076 D.ORGANIZATION_ID,
1077 D.USING_ASSEMBLY_DEMAND_DATE,
1078 DECODE(D.ORIGINATION_TYPE,
1079 2, NONSTD_JOBS_DEMAND,
1080 3, DISCRETE_JOBS_DEMAND,
1081 4, REPETITIVE_SCHEDULE_DEMAND,
1082 6, SALES_ORDER_DEMAND,
1083 24, SALES_ORDER_DEMAND),
1084 -1*D.USING_REQUIREMENT_QUANTITY,
1088 SYSDATE,
1085 NULL, -- reservation quantity
1086 SYSDATE,
1087 FND_GLOBAL.USER_ID,
1089 FND_GLOBAL.USER_ID,
1090 D.DEMAND_CLASS,
1091 1, -- demand
1092 NULL -- actually this should be the product family item id
1093 FROM MSC_DEMANDS D,
1094 MSC_ATP_RULES R,
1095 MSC_PARAMETERS P ,
1096 MSC_SYSTEM_ITEMS I
1097 WHERE I.ATP_FLAG in ('C', 'Y')
1098 AND I.SR_INSTANCE_ID = l_instance_id
1099 AND I.PLAN_ID = -1
1100 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
1101 AND P.SR_INSTANCE_ID = I.SR_INSTANCE_ID
1102 AND R.RULE_ID = NVL(I.ATP_RULE_ID, P.DEFAULT_ATP_RULE_ID)
1103 AND R.SR_INSTANCE_ID = I.SR_INSTANCE_ID
1104 AND D.PLAN_ID = -1
1105 AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
1106 AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1107 AND D.PLAN_ID = -1
1108 AMD D.ORIGINATION_TYPE in (
1109 DECODE(R.INCLUDE_SALES_ORDERS, 1, 6, -1),
1110 DECODE(R.INCLUDE_SALES_ORDERS, 1, 24, -1),
1111 DECODE(R.INCLUDE_DISCRETE_WIP_DEMAND, 1, 3, -1),
1112 DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 2, -1),
1113 DECODE(R.INCLUDE_REP_WIP_DEMAND, 1, 4, -1))
1114 AND D.USING_ASSEMBLY_DEMAND_DATE >=
1115 DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
1116 NULL,NVL(D.FIRM_DATE, D.USING_REQUIREMENT_QUANTITY),
1117 MRP_CALENDAR.DATE_OFFSET(P.ORGANIZATION_ID, 1, SYSDATE,
1118 -NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0)));
1119
1120 */
1121 END Collect_Atp_Info;
1122
1123 END MRP_ATP_COLLECTION;