VAR B13 VARCHAR2(32);
VAR B12 NUMBER;
VAR B11 NUMBER;
VAR B10 NUMBER;
VAR B9 NUMBER;
VAR B8 NUMBER;
VAR B7 NUMBER;
VAR B6 VARCHAR2(32);
VAR B5 NUMBER;
VAR B4 NUMBER;
VAR B3 VARCHAR2(32);
VAR B2 VARCHAR2(32);
VAR B1 VARCHAR2(32);

EXEC :B11 := 509173;
EXEC :B10 := 462;
EXEC :B9 := 1;
EXEC :B8 := -1;
EXEC :B6 := 'KFC:Knouse';
EXEC :B5 := -1;
EXEC :B3 := '27-APR-09';

--3m0hb99x42fuv

SELECT   SD_DATE,
         SUM(SD_QTY)
FROM
         ( SELECT C.PRIOR_DATE SD_DATE,
                 -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
         FROM    MSC_CALENDAR_DATES C,
                 MSC_DEMANDS D       ,
                 MSC_ATP_RULES R     ,
                 MSC_SYSTEM_ITEMS I
         WHERE   I.SR_INVENTORY_ITEM_ID      = :B11
             AND I.ORGANIZATION_ID           = :B10
             AND I.SR_INSTANCE_ID            = :B9
             AND I.PLAN_ID                   = :B8
             AND R.RULE_ID (+)               = NVL(I.ATP_RULE_ID, :B7 )
             AND R.SR_INSTANCE_ID (+)        = I.SR_INSTANCE_ID
             AND D.PLAN_ID                   = I.PLAN_ID
             AND D.SR_INSTANCE_ID            = I.SR_INSTANCE_ID
             AND D.INVENTORY_ITEM_ID         = I.INVENTORY_ITEM_ID
             AND D.ORGANIZATION_ID           = I.ORGANIZATION_ID
             AND USING_REQUIREMENT_QUANTITY <> 0
             AND D.ORIGINATION_TYPE         IN ( DECODE(R.INCLUDE_DISCRETE_WIP_DEMAND,
                                                        1, 3,
                                                        -1)
                                                 ,
                                                 DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND,
                                                        1, 25,
                                                        -1)
                                                 ,
                                                 DECODE(R.INCLUDE_USER_DEFINED_DEMAND,
                                                        1, 42,
                                                        -1)
                                                 ,
                                                 DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
                                                        1, 2,
                                                        -1)
                                                 ,
                                                 DECODE(R.INCLUDE_REP_WIP_DEMAND,
                                                        1, 4,
                                                        -1)
                                                 )
             AND C.CALENDAR_CODE    = :B6
             AND C.EXCEPTION_SET_ID = :B5
             AND C.SR_INSTANCE_ID   = I.SR_INSTANCE_ID
             AND C.CALENDAR_DATE BETWEEN TRUNC(D.USING_ASSEMBLY_DEMAND_DATE) AND TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE, D.USING_ASSEMBLY_DEMAND_DATE))
             AND
                 (
                         R.PAST_DUE_DEMAND_CUTOFF_FENCE IS NULL
                      OR C.PRIOR_SEQ_NUM >= :B4 - R.PAST_DUE_DEMAND_CUTOFF_FENCE
                 )
             AND C.PRIOR_DATE < NVL(:B3 , C.PRIOR_DATE + 1)
             AND
                 (
                         R.DEMAND_CLASS_ATP_FLAG             <> 1
                      OR NVL(D.DEMAND_CLASS, NVL(:B1 ,'@@@')) = NVL(:B2 , NVL(:B1 ,'@@@'))
                 )
         UNION ALL
         SELECT DECODE(D.RESERVATION_TYPE,
                       2, TO_DATE(:B13) ,
                       TRUNC(D.REQUIREMENT_DATE))
                SD_DATE,
                -1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0), D.COMPLETED_QUANTITY)) SD_QTY
         FROM   MSC_SALES_ORDERS D,
                MSC_ATP_RULES R   ,
                MSC_SYSTEM_ITEMS I,
                MSC_CALENDAR_DATES C
         WHERE  I.SR_INVENTORY_ITEM_ID = :B11
            AND I.ORGANIZATION_ID      = :B10
            AND I.SR_INSTANCE_ID       = :B9
            AND I.PLAN_ID              = :B8
            AND R.RULE_ID (+)          = NVL(I.ATP_RULE_ID, :B7 )
            AND R.SR_INSTANCE_ID (+)   = I.SR_INSTANCE_ID
            AND D.SR_INSTANCE_ID       = I.SR_INSTANCE_ID
            AND D.INVENTORY_ITEM_ID    = I.INVENTORY_ITEM_ID
            AND D.ORGANIZATION_ID      = I.ORGANIZATION_ID
            AND D.DEMAND_SOURCE_TYPE  <> DECODE(R.INCLUDE_SALES_ORDERS,
                                                2,2,-1)
            AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,
                                               2,8,-1)
            AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0), D.COMPLETED_QUANTITY)
            AND DECODE(:B12 ,
                       3,D.COMPLETED_QUANTITY,
                       0)
                = 0
            AND
                (
                       D.SUBINVENTORY IS NULL
                    OR D.SUBINVENTORY IN
                                         (SELECT S.SUB_INVENTORY_CODE
                                         FROM    MSC_SUB_INVENTORIES S
                                         WHERE   S.ORGANIZATION_ID    =D.ORGANIZATION_ID
                                             AND S.PLAN_ID            = I.PLAN_ID
                                             AND S.SR_INSTANCE_ID     = D.SR_INSTANCE_ID
                                             AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
                                                                              1, 1,
                                                                              NULL, 1,
                                                                              S.INVENTORY_ATP_CODE)
                                             AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
                                                                        2, 1,
                                                                        S.NETTING_TYPE)
                                         )
                )
            AND
                (
                       D.RESERVATION_TYPE = 2
                    OR D.PARENT_DEMAND_ID IS NULL
                    OR
                       (
                              D.RESERVATION_TYPE = 3
                          AND
                              (
                                     (
                                            R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1
                                     )
                                  OR
                                     (
                                            R.INCLUDE_NONSTD_WIP_RECEIPTS = 1
                                     )
                              )
                       )
                )
            AND
                (
                       R.PAST_DUE_DEMAND_CUTOFF_FENCE IS NULL
                    OR C.PRIOR_SEQ_NUM >= :B4 - R.PAST_DUE_DEMAND_CUTOFF_FENCE
                )
            AND C.CALENDAR_CODE    = :B6
            AND C.SR_INSTANCE_ID   = I.SR_INSTANCE_ID
            AND C.EXCEPTION_SET_ID = -1
            AND C.CALENDAR_DATE    = TRUNC(D.REQUIREMENT_DATE)
            AND C.PRIOR_DATE       < NVL(:B3 , C.PRIOR_DATE + 1)
            AND
                (
                       R.DEMAND_CLASS_ATP_FLAG             <> 1
                    OR NVL(D.DEMAND_CLASS, NVL(:B1 ,'@@@')) = NVL(:B2 , NVL(:B1 ,'@@@'))
                )
         UNION ALL
         SELECT C.NEXT_DATE SD_DATE,
                DECODE(ORDER_TYPE,
                       30, DECODE(SIGN(S.DAILY_RATE * (TRUNC(C.CALENDAR_DATE) - TRUNC(S.FIRST_UNIT_START_DATE))- S.QTY_COMPLETED),
                                  -1,S.DAILY_RATE   * (TRUNC(C.CALENDAR_DATE) - TRUNC(S.FIRST_UNIT_START_DATE) +1)- S.QTY_COMPLETED,
                                  S.DAILY_RATE)
                       ,
                       5, NVL(S.DAILY_RATE, NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)),
                       (NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) - NVL(S.NON_NETTABLE_QTY, 0)) )
                SD_QTY
         FROM   MSC_CALENDAR_DATES C,
                MSC_SUPPLIES S      ,
                MSC_ATP_RULES R     ,
                MSC_SYSTEM_ITEMS I  ,
                MSC_SUB_INVENTORIES MSI
         WHERE  I.SR_INVENTORY_ITEM_ID = :B11
            AND I.ORGANIZATION_ID      = :B10
            AND I.SR_INSTANCE_ID       = :B9
            AND I.PLAN_ID              = :B8
            AND R.RULE_ID (+)          = NVL(I.ATP_RULE_ID, :B7 )
            AND R.SR_INSTANCE_ID (+)   = I.SR_INSTANCE_ID
            AND S.PLAN_ID              = I.PLAN_ID
            AND S.SR_INSTANCE_ID       = I.SR_INSTANCE_ID
            AND S.INVENTORY_ITEM_ID    = I.INVENTORY_ITEM_ID
            AND S.ORGANIZATION_ID      = I.ORGANIZATION_ID
            AND DECODE(S.ORDER_TYPE,
                       30, S.DAILY_RATE* (TRUNC(C.CALENDAR_DATE) - TRUNC(S.FIRST_UNIT_START_DATE) + 1),
                       5, NVL(S.DAILY_RATE, ABS(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) ),
                       ABS(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) )
                > DECODE(S.ORDER_TYPE,
                         30, S.QTY_COMPLETED,
                         0)
            AND
                (
                       S.ORDER_TYPE IN ( DECODE(R.INCLUDE_PURCHASE_ORDERS,
                                                1, 1,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_PURCHASE_ORDERS,
                                                1, 8,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS,
                                                1, 3,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_REP_WIP_RECEIPTS,
                                                1, 30,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
                                                1, 7,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS,
                                                1, 15,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_INTERORG_TRANSFERS,
                                                1, 11,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_INTERORG_TRANSFERS,
                                                1, 12,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_ONHAND_AVAILABLE,
                                                1, 18,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_USER_DEFINED_SUPPLY,
                                                1, 41,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS,
                                                1, 27,
                                                -1)
                                         ,
                                         DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS,
                                                1, 28,
                                                -1)
                                         )
                    OR
                       (
                              INCLUDE_INTERNAL_REQS = 1
                          AND S.ORDER_TYPE          = 2
                          AND S.SOURCE_ORGANIZATION_ID IS NOT NULL
                       )
                    OR
                       (
                              INCLUDE_SUPPLIER_REQS = 1
                          AND S.ORDER_TYPE          = 2
                          AND S.SOURCE_ORGANIZATION_ID IS NULL
                       )
                    OR
                       (
                              (
                                     R.INCLUDE_REP_MPS      = 1
                                  OR R.INCLUDE_DISCRETE_MPS = 1
                              )
                          AND S.ORDER_TYPE = 5
                          AND EXISTS
                              (SELECT '1'
                              FROM    MSC_DESIGNATORS
                              WHERE   INVENTORY_ATP_FLAG = 1
                                  AND DESIGNATOR_TYPE    = 2
                                  AND DESIGNATOR_ID      = S.SCHEDULE_DESIGNATOR_ID
                                  AND DECODE(R.DEMAND_CLASS_ATP_FLAG,
                                             1, NVL(DEMAND_CLASS, NVL(:B1 ,'@@@')),
                                             '@@@')
                                      = DECODE(R.DEMAND_CLASS_ATP_FLAG,
                                               1, NVL(:B2 , NVL(:B1 ,'@@@')),
                                               '@@@')
                              )
                       )
                )
            AND C.CALENDAR_CODE    = :B6
            AND C.EXCEPTION_SET_ID = :B5
            AND C.SR_INSTANCE_ID   = I.SR_INSTANCE_ID
            AND C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) AND TRUNC(NVL(DECODE(S.ORDER_TYPE,
                                                                                                         5, S.LAST_UNIT_START_DATE,
                                                                                                         S.LAST_UNIT_COMPLETION_DATE)
                , NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
            AND DECODE(DECODE(S.ORDER_TYPE,
                              5, S.LAST_UNIT_START_DATE,
                              S.LAST_UNIT_COMPLETION_DATE)
                       ,
                       NULL, C.NEXT_SEQ_NUM,
                       C.SEQ_NUM)
                IS NOT NULL
            AND
                (
                       S.ORDER_TYPE = 18
                    OR R.PAST_DUE_SUPPLY_CUTOFF_FENCE IS NULL
                    OR C.NEXT_SEQ_NUM >= :B4 - R.PAST_DUE_SUPPLY_CUTOFF_FENCE
                )
            AND C.NEXT_DATE >= DECODE(S.ORDER_TYPE,
                                      27, TRUNC(SYSDATE),
                                      28, TRUNC(SYSDATE),
                                      C.NEXT_DATE)
            AND C.NEXT_DATE < NVL(:B3 , C.NEXT_DATE + 1)
            AND
                (
                       R.DEMAND_CLASS_ATP_FLAG             <> 1
                    OR S.ORDER_TYPE                         = 5
                    OR NVL(S.DEMAND_CLASS, NVL(:B1 ,'@@@')) = NVL(:B2 , NVL(:B1 ,'@@@'))
                )
            AND MSI.PLAN_ID (+)                = :B8
            AND MSI.ORGANIZATION_ID (+)        = :B10
            AND MSI.SR_INSTANCE_ID (+)         = :B9
            AND MSI.SUB_INVENTORY_CODE (+)     = S.SUBINVENTORY_CODE
            AND NVL(MSI.INVENTORY_ATP_CODE,1) <> 2
         )
GROUP BY SD_DATE
ORDER BY SD_DATE;
