Terex Daily Order Items Revenue Report

SELECT
    operating_unit,
    organization_code,
    delivery_number,
    customer,
    country,
    salesrep_name,
    product_category,
    order_number,
    order_type,
    inco_term,
    item,
    SUM(nvl(quantity_invoiced, 0)) invoiced_qty,
    entered_currency,
    ledger_currency,
    invoice_number,
    invoice_date,
    SUM(nvl(invoiced_amount, 0))   func_currency_invoiced_amount,
    usd_conversion_rate,
    round(SUM(nvl(invoiced_amount, 0) * usd_conversion_rate), 2)   sales_value_in_usd,
    item_description
FROM
    (
        SELECT
            hu.name                                             operating_unit,
            ood.organization_code,
            hp.party_name                                       customer,
            hca.account_number                                  customer_number,
            gl.currency_code                                    ledger_currency,
            rt.trx_number                                       invoice_number,
            rt.trx_date                                         invoice_date,
            rt.invoice_currency_code                            entered_currency,
            ol.freight_terms_code                               inco_term,
            (
                SELECT
                    glr.conversion_rate
                FROM
                    gl_daily_rates_v glr
                WHERE
                        glr.conversion_type = 'Corporate'
                    AND trunc(glr.conversion_date) = trunc(sysdate)
                    AND glr.to_currency = 'USD'
                    AND glr.from_currency = gl.currency_code
            )                                                   usd_conversion_rate,
            (
                SELECT
                    ft.territory_short_name
                FROM
                    hz_cust_site_uses_all  hs,
                    hz_cust_acct_sites_all hca,
                    hz_party_sites         hps,
                    hz_locations           hl,
                    fnd_territories_vl     ft
                WHERE
                        hs.site_use_id = rt.ship_to_site_use_id
                    AND hs.cust_acct_site_id = hca.cust_acct_site_id
                    AND hca.party_site_id = hps.party_site_id
                    AND hps.location_id = hl.location_id
                    AND hl.country = ft.territory_code
            )                                                   country,
            (
                SELECT
                    name
                FROM
                    jtf_rs_salesreps
                WHERE
                        salesrep_id = oh.salesrep_id
                    AND org_id = oh.org_id
            )                                                   salesrep_name,-- oh.salesrep_id,
            (
                SELECT
                    mc.segment1
                FROM
                    mtl_item_categories mic,
                    mtl_categories      mc
                WHERE
                        mic.inventory_item_id = rctl.inventory_item_id
                    AND mic.organization_id = rctl.warehouse_id
                    AND mic.category_id = mc.category_id
                    AND mic.category_set_id = 1100000022
            )                                                   product_category,
            rt.org_id,
            rt.trx_number,
            rt.cust_trx_type_id,
            oh.order_number,
            otl.name                                            order_type,
            (
                SELECT
                    segment1
                FROM
                    mtl_system_items_b
                WHERE
                        inventory_item_id = rctl.inventory_item_id
                    AND organization_id = rctl.warehouse_id
            )                                                   item,
            (
                SELECT
                    description
                FROM
                    mtl_system_items_b
                WHERE
                        inventory_item_id = rctl.inventory_item_id
                    AND organization_id = rctl.warehouse_id
            )                                                   item_description,
            rctl.quantity_ordered,
            nvl(rctl.quantity_invoiced, rctl.quantity_credited) quantity_invoiced,
            rt.invoice_currency_code,
            rctl.extended_amount,
            round(rctl.extended_amount * nvl(rt.exchange_rate, 1), 2) invoiced_amount,
            decode(rctl.interface_line_context, 'INTERCOMPANY',(
                SELECT
                    shipment_number
                FROM
                    mtl_material_transactions
                WHERE
                    transaction_id = rctl.interface_line_attribute7
            ), rctl.interface_line_attribute3)                  delivery_number
        FROM
            ra_customer_trx_all          rt,
            gl_ledgers                   gl,
            hr_operating_units           hu,
            ra_customer_trx_lines_all    rctl,
            ra_cust_trx_line_gl_dist_all rgd,
            oe_order_headers_all         oh,
            oe_order_lines_all           ol,
            oe_transaction_types_tl      otl,
            hz_cust_accounts             hca,
            hz_parties                   hp,
            org_organization_definitions ood
        WHERE
                1=1
            AND rt.customer_trx_id = rctl.customer_trx_id
          --AND rt.org_id = 11023
            AND rt.org_id = hu.organization_id
            AND hu.set_of_books_id = gl.ledger_id
            AND rt.customer_trx_id = rgd.customer_trx_id
            AND rgd.account_class = 'REC'
            AND rctl.line_type = 'LINE'
            AND rgd.latest_rec_flag = 'Y'
            AND rgd.gl_date >= nvl(:p_fdate,   nvl(trunc(:p_tdate ,'MM'), trunc(sysdate,'MM')) )
            AND rgd.gl_date <= nvl(:p_tdate,trunc(sysdate))
            AND oh.header_id = ol.header_id (+)
            AND oh.order_number = rctl.sales_order
         --AND ol.line_id (+)= rctl.interface_line_attribute6 --Shipping Charges Not Expected
            AND ol.line_id = rctl.interface_line_attribute6
            AND oh.order_type_id = otl.transaction_type_id
            AND otl.language = userenv('LANG')
            AND rctl.interface_line_context IN ( 'ORDER ENTRY', 'INTERCOMPANY' )
            AND hca.cust_account_id = rt.bill_to_customer_id
            AND hca.party_id = hp.party_id 
            AND rctl.warehouse_id = ood.organization_id
    )
WHERE
    2=2
GROUP BY
    operating_unit,
    organization_code,
    delivery_number,
    customer,
    country,
    salesrep_name,
    product_category,
    order_number,
    order_type,
    inco_term,
    item,
    entered_currency,
    ledger_currency,
    invoice_number,
    invoice_date,
    usd_conversion_rate,
    item_description