Oracle EBS: ASCP ms_supplies中order_type所對應類別, 及pegging資料擷取

SQL:

   SELECT mso.sales_order_number,
         md1.*,
         MSI1.ITEM_NAME || ' (' || MSI1.INVENTORY_ITEM_ID || ')'  "COMPONENT (ID)",
         SUBSTR (md1.order_number, 1, 22) order_num,
         MS1.NEW_ORDER_QUANTITY COMP_QTY -- this is the pegged or previously pegged column
         ,DECODE (ms1.order_type,
                 '1', 'Purchase order',
                 '11', 'Intransit shipment',
                 '12', 'Intransit receipt',
                 '13', 'Suggested repetitive schedule',
                 '14', 'Discrete job co-product/by-product',
                 '15', 'Nonstandard job by-product',
                 '16', 'Repetitive schedule by-product',
                 '17', 'Planned order co-product/by-product',
                 '18', 'On Hand',
                 '2', 'Purchase requisition',
                 '27', 'Flow schedule',
                 '28', 'Flow schedule by-product',
                 '29', 'Payback Supply',
                 '3', 'Discrete job',
                 '30', 'Current repetitive schedule',
                 '32', 'Returns',
                 '4', 'Suggested aggregate repetitive schedule',
                 '41', 'User Supply',
                 '45', 'Demand Class Consumption',
                 '46', 'Supply Due To Stealing',
                 '47', 'Demand Due To Stealing',
                 '48', 'Supply Adjustment',
                 '49', 'PO Acknowledgment',
                 '5', 'Planned order',
                 '50', 'ATP Aggregate Supply',
                 '51', 'Planned inbound shipment',
                 '52', 'Requested inbound shipment',
                 '53', 'Internal requisition',
                 '60', 'Order Rescheduling Adjustment',
                 '7', 'Non-standard job',
                 '70', 'Maintenance Work Order',
                 '8', 'PO in receiving')
            Order_type-- ,decode (MFP1.supply_type, 1, ‘Purchase Order', 2, ‘Purchase Requisition', 3, ‘Work Order', 11, ‘Intransit Shipment', 12, ‘Intransit receipt') Supply_type
                      -- ,decode (MFP2.supply_type, 1, ‘Purchase Order', 2, ‘Purchase Requisition', 3, ‘Work Order', 11, ‘Intransit Shipment', 12, ‘Intransit receipt') Supply_type2
         ,MFP1.demand_quantity--, MFP1.supply_quantity
         ,MFP1.demand_date,
         MFP1.supply_date,
         ROUND (MFP1.ALLOCATED_QUANTITY, 1) COMP_ALLOC,
         DECODE (MD1.origination_type,  '29', 'Peg_Fcast',  '30', 'Peg_Sales')
            Pegged_To,
         DECODE (MFP1.DEMAND_ID,  -1, 'peg_Excess',  -2, 'Peg_SS') Pegged_To_1,
         MFP2.ORGANIZATION_ID,
         MFP1.ORGANIZATION_ID,
         DECODE (MD2.origination_type,  '29', 'Peg_Fcast',  '30', 'Peg_Sales')
            Pegged_To_2,
         TRUNC (MS1.NEW_SCHEDULE_DATE) COMP_SUP_DUE,
         'X',
         DECODE (
            MD2.ORDER_NUMBER,
            NULL, TO_CHAR (MD2.DEMAND_ID),
            (SUBSTR (MD2.ORDER_NUMBER, 1, 14)
             || SUBSTR (MD2.ORDER_NUMBER, 42, 5)))
            SO_NUM,
         MSI2.ITEM_NAME || ' (' || MSI2.INVENTORY_ITEM_ID || ')'
            "ASSEMBLY (ID)"-- , MSI2.DESCRIPTION
         ,TO_CHAR (MD2.REQUEST_SHIP_DATE, 'DD/MM/YYYY') RDATE,
         TRUNC (MD2.USING_ASSEMBLY_DEMAND_DATE) SSDATE,
         MD1.Creation_date,
         ROUND ( (MD2.DMD_SATISFIED_DATE - MD2.USING_ASSEMBLY_DEMAND_DATE), 1)
            LATE,
         MD2.DEMAND_PRIORITY PRIORITY,
         ROUND (MFP2.DEMAND_QUANTITY, 1) so_QTY,
         ROUND (MFP2.ALLOCATED_QUANTITY, 1) WIP_QTY,
         TRUNC (MD1.OLD_DEMAND_DATE) ULSD,
         TRUNC (MD1.USING_ASSEMBLY_DEMAND_DATE) COMP_DEM_DUE
    FROM APPS.MSC_SUPPLIES MS1                                         -- COMP
        ,APPS.MSC_SYSTEM_ITEMS MSI1                                    -- COMP
        ,APPS.MSC_FULL_PEGGING MFP1                                    -- COMP
        ,apps.msc_sales_orders mso,
         (SELECT *
            FROM APPS.MSC_DEMANDS
           WHERE SR_INSTANCE_ID = &1 AND ORGANIZATION_ID = &2 AND PLAN_ID = &3) MD1 -- COMP
        ,(SELECT *
            FROM APPS.MSC_FULL_PEGGING
           WHERE SR_INSTANCE_ID = &1 AND ORGANIZATION_ID = &2 AND PLAN_ID = &3) MFP2 -- FG
        ,(SELECT *
            FROM APPS.MSC_DEMANDS
           WHERE SR_INSTANCE_ID = &1 AND ORGANIZATION_ID = &2 AND PLAN_ID = &3) MD2 -- FG
        ,(SELECT *
            FROM APPS.MSC_SYSTEM_ITEMS
           WHERE SR_INSTANCE_ID = &1 AND ORGANIZATION_ID = &2 AND PLAN_ID = &3) MSI2 -- FG LEVEL
   WHERE     1 = 1
         AND MS1.SR_INSTANCE_ID = &1
         AND MS1.ORGANIZATION_ID = &2
         AND MS1.PLAN_ID = &3
         AND mso.demand_id = md1.demand_id
         AND MSI1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
         AND MSI1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
         AND MSI1.PLAN_ID = MS1.PLAN_ID
         AND MSI1.INVENTORY_ITEM_ID = MS1.INVENTORY_ITEM_ID
         AND MFP1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
         AND MFP1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
         AND MFP1.PLAN_ID = MS1.PLAN_ID
         AND MFP1.TRANSACTION_ID = MS1.TRANSACTION_ID
         AND MD1.DEMAND_ID(+) = MFP1.DEMAND_ID
         AND MFP2.PEGGING_ID(+) = MFP1.PREV_PEGGING_ID
         AND MD2.DEMAND_ID(+) = MFP2.DEMAND_ID
         AND MSI2.INVENTORY_ITEM_ID(+) = MFP2.INVENTORY_ITEM_ID
         AND MSI1.ITEM_NAME = 'item'
         AND md1.order_number = 'ord'
ORDER BY MS1.OLD_SCHEDULE_DATE, MD2.REQUEST_SHIP_DATE

Ref:
QUERY TO FIND PEGGING DETAILS IN ASCP
http://www.oracleerpappsguide.com/2015/04/query-to-find-pegging-details-in-ascp.html