AGENWSIE CUSTOMERWISE DEALERWISE REPORT

/* Formatted on 2013/01/11 21:10 (Formatter Plus v4.8.8) */
SELECT   account_name, customer_name, cust_po_number, country, fsc_nfsc,
         variety, gsm, width, SUM (NVL (reel_qty, 0)) reel_qty,
         SUM (NVL (sheet_qty, 0)) sheet_qty,
         SUM (NVL (reel_qty, 0) + NVL (sheet_qty, 0)) total_qty,
         creation_date, org
    FROM ( SELECT DISTINCT hps.party_name account_name,hp.party_name customer_name,
                          ooha.cust_po_number, flvc.description country,
                          ood.organization_code org, ooha.creation_date,
                          CASE
                             WHEN SUBSTR (oola.ordered_item, 3,
                                          1) = 'N'
                                THEN 'NFSC'
                             WHEN SUBSTR (oola.ordered_item, 1) <>
                                                              'N'
                                THEN 'FSC'
                          END AS fsc_nfsc,
                          SUBSTR (oola.ordered_item, 4, 3) variety,
                          SUBSTR (oola.ordered_item, 8, 4) gsm,
                          (SELECT CASE
                                     WHEN SUBSTR (ool.ordered_item,
                                                  1,
                                                  1
                                                 ) = 'B'
                                        THEN SUBSTR (ool.ordered_item, 12, 8)
WHEN SUBSTR (ool.ordered_item,
                                                  1,
                                                  1
                                                 ) = 'U'
                                        THEN SUBSTR (ool.ordered_item, 12, 8)
WHEN SUBSTR (ool.ordered_item,
                                                  1,
                                                  1
                                                 ) = 'P'
                                        THEN SUBSTR (ool.ordered_item, 12, 8)
WHEN SUBSTR (ool.ordered_item,
                                                  1,
                                                  1
                                                 ) = 'T'
                                        THEN SUBSTR (ool.ordered_item, 12, 8)
                                        WHEN SUBSTR (ool.ordered_item,
                                                  1,
                                                  1
                                                 ) = 'Z'
                                        THEN SUBSTR (ool.ordered_item, 12, 8)                                                                               
                                     ELSE SUBSTR (ool.ordered_item, 12, 4)
                                  END
                             FROM apps.oe_order_lines_all ool
                            WHERE oola.header_id = ool.header_id
                              AND oola.line_id = ool.line_id) width,
                          (SELECT SUM (ool.ordered_quantity)
                             FROM apps.oe_order_lines_all ool
                            WHERE ool.ordered_item LIKE 'R%'
                              AND ool.header_id = ooha.header_id
                              AND ool.line_id = oola.line_id) reel_qty,
                          (SELECT sum(ool.ordered_quantity)
                             FROM apps.oe_order_lines_all ool
                            WHERE substr(ool.ordered_item ,1 ,1) <> 'R'
                              AND ool.header_id = ooha.header_id
                              AND ool.line_id = oola.line_id) sheet_qty
                     FROM apps.oe_order_headers_all ooha,
                          apps.oe_order_lines_all oola,
                          apps.org_organization_definitions ood,
                          apps.hz_parties hp,
                          apps.hz_parties hps,
                          apps.hz_cust_accounts hca,
                          apps.oe_transaction_types_tl ottt,
                          apps.fnd_lookup_values flv,
                          apps.fnd_lookup_values flvc,
                          apps.mtl_system_items_b msi
                    WHERE ooha.header_id = oola.header_id
                      --
                      AND oola.flow_status_code = 'CLOSED'
                      AND SUBSTR (oola.ordered_item, 4, 3) =
                              NVL (:variety, SUBSTR (oola.ordered_item, 4, 3))
                      AND ooha.ship_from_org_id = ood.organization_id
                      AND ood.organization_id = :ORGANIZATION
                      AND ooha.attribute1 = hps.party_id
                      AND TRUNC (ooha.creation_date)
                             BETWEEN NVL (:from_date,
                                          TRUNC (ooha.creation_date)
                                         )
                                 AND NVL (:TO_DATE,
                                          TRUNC (ooha.creation_date))
                      AND hp.party_name =
                                          NVL (:customer, hp.party_name)
                      AND hca.party_id = hp.party_id
                      AND flvc.lookup_type = 'SG_COUNTRY_CODE'
                      AND flvc.lookup_code = hp.country
                      AND flvc.description = NVL (:country, flvc.description)
                      AND hps.party_name = NVL (:AGENT, hps.party_name)
                      AND ooha.order_type_id = ottt.transaction_type_id
                      AND flv.lookup_code = UPPER (ottt.NAME)
                      AND flv.tag = 'Export'
                      AND msi.inventory_item_id = oola.inventory_item_id
                      AND msi.organization_id = oola.ship_from_org_id
                      and hca.CUST_ACCOUNT_ID = ooha.SOLD_TO_ORG_ID
                      &sheet
                       &nff        
--                      AND SUBSTR (oola.ordered_item, 1, 1) =
--                             DECODE (:rs,
--                                     'Reel', 'R',
--                                     'Sheet', 'B',
--                                     NULL, 'B'
--                                    )
----                                   NVL (:rs, SUBSTR (oola.ordered_item, 1, 1))
--                      AND SUBSTR (oola.ordered_item, 3, 1) =
--                                  DECODE (:nf,
--                                          'N', 'N',
--                                          NULL, 'N',
--                                          'X', NULL
--                                         )
                                         )
GROUP BY account_name,
         customer_name,
         cust_po_number,
         country,
         fsc_nfsc,
         variety,
         gsm,
         width,
         creation_date,
         org