Sales Order Booking Revenue Details

SELECT
    SOBRD.Territory,
    SOBRD.Order_Number,
    SOBRD.Line_Number,
    SOBRD.Fulfill_Line_Id,
    SOBRD.Item_Number,
    SOBRD.Submitted_Date,
    SUM(SOBRD.Net_Revenue_Unallocated)    AS Net_Revenue_Unallocated,
    SUM(SOBRD.Quantity_Unallocated)       AS Quantity_Unallocated,
    SOBRD.Item_Type
FROM
    (
        SELECT
            NVL(HCASB.ATTRIBUTE6, HCASA.ATTRIBUTE6)                          AS Territory,
            DHA.ORDER_NUMBER                                                 AS Order_Number,
            DLA.LINE_NUMBER                                                  AS Line_Number,
            TO_CHAR(DFLA.FULFILL_LINE_ID)                                    AS Fulfill_Line_Id,
            ESIBV.ITEM_NUMBER                                                AS Item_Number,
            TO_CHAR(DHA.SUBMITTED_DATE, 'DD-MON-YYYY')                       AS Submitted_Date,
            (DFLA.ORDERED_QTY * DFLA.UNIT_SELLING_PRICE)                     AS Net_Revenue_Unallocated,
            DECODE(
                DFLA.CATEGORY_CODE,
                'RETURN', (-1 * DFLA.ORDERED_QTY),
                DFLA.ORDERED_QTY
            )                                                                AS Quantity_Unallocated,
            DFLA.SALES_PRODUCT_TYPE_CODE                                     AS Item_Type
        FROM
            DOO_FULFILL_LINES_ALL         DFLA,
            DOO_LINES_ALL                 DLA,
            DOO_HEADERS_ALL               DHA,
            DOO_HEADERS_EFF_B             DHEB,
            HZ_CUST_SITE_USES_ALL         HCSUA,
            HZ_CUST_ACCT_SITES_ALL        HCASA,
            HZ_CUST_ACCT_SITES_ALL        HCASB,
            EGP_SYSTEM_ITEMS_B_V          ESIBV,
            HZ_PARTY_SITES                HPS
        WHERE
                DFLA.INVENTORY_ITEM_ID    = ESIBV.INVENTORY_ITEM_ID
            AND DFLA.INVENTORY_ORGANIZATION_ID = ESIBV.ORGANIZATION_ID
            AND DHA.HEADER_ID             = DHEB.HEADER_ID (+)
            AND DHEB.ATTRIBUTE_NUMBER3    = HPS.LOCATION_ID (+)
            AND HPS.PARTY_SITE_ID         = HCASB.PARTY_SITE_ID (+)
            AND DFLA.BILL_TO_SITE_USE_ID  = HCSUA.SITE_USE_ID (+)
            AND HCSUA.CUST_ACCT_SITE_ID   = HCASA.CUST_ACCT_SITE_ID (+)
            AND DFLA.LINE_ID              = DLA.LINE_ID
            AND DLA.HEADER_ID             = DHA.HEADER_ID
            AND DHA.SUBMITTED_FLAG        = 'Y'
            AND DHEB.CONTEXT_CODE (+)     = 'End Customer Details'
            AND NVL(DFLA.SALES_PRODUCT_TYPE_CODE, 'NA') <> 'SUBSCRIPTION'
    ) SOBRD
GROUP BY
    SOBRD.Territory,
    SOBRD.Order_Number,
    SOBRD.Line_Number,
    SOBRD.Fulfill_Line_Id,
    SOBRD.Item_Number,
    SOBRD.Submitted_Date,
    SOBRD.Item_Type