Finished Production Report Sales Order

/* Formatted on 2013/01/22 11:07 (Formatter Plus v4.8.8) */
select tag , item_code , machine , p_goodstdretretree,fsc_nfsc , gsm , width , sum(reel_qty) , TQ from (SELECT  flv.tag , SUBSTR (oola.ordered_item, 4, 3) || '-'
           || oola.ordered_item item_code,
          CASE
             WHEN SUBSTR (oola.ordered_item, 2, 1) = ('1')
                THEN 'PM1'
             WHEN SUBSTR (oola.ordered_item, 2, 1) = ('2')
                THEN 'PM2'
             WHEN SUBSTR (oola.ordered_item, 2, 1) = ('3')
                THEN 'PM3'
             ELSE SUBSTR (oola.ordered_item, 2, 1)
          END machine,
          SUBSTR (oola.ordered_item, 7, 1) p_goodstdretretree,
          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, 8, 4) gsm,
          CASE
             WHEN SUBSTR (oola.ordered_item, 1, 1) = 'B'
                THEN SUBSTR (oola.ordered_item, 12, 8)
             WHEN SUBSTR (oola.ordered_item, 1, 1) = 'U'
                THEN SUBSTR (oola.ordered_item, 12, 8)
             WHEN SUBSTR (oola.ordered_item, 1, 1) = 'P'
                THEN SUBSTR (oola.ordered_item, 12, 8)
             WHEN SUBSTR (oola.ordered_item, 1, 1) = 'T'
                THEN SUBSTR (oola.ordered_item, 12, 8)
             WHEN SUBSTR (oola.ordered_item, 1, 1) = 'Z'
                THEN SUBSTR (oola.ordered_item, 12, 8)
             ELSE SUBSTR (oola.ordered_item, 12, 4)
          END width,
          (SELECT SUM (ool.ordered_quantity)
             FROM apps.oe_order_lines_all ool
            WHERE ool.ordered_item = oola.ordered_item
              AND ool.header_id = ooha.header_id
              AND ool.line_id = oola.line_id) reel_qty,
          SUM (moq.transaction_quantity) TQ
    FROM apps.oe_order_headers_all ooha,
         apps.oe_order_lines_all oola,
         apps.mtl_system_items_b msib,
         apps.mtl_onhand_quantities moq,
         apps.fnd_lookup_values flv,
         apps.oe_transaction_types_tl ottt
   WHERE 1 = 1
     AND ooha.header_id = oola.header_id
     AND TRUNC (ooha.creation_date) BETWEEN NVL (:P_from_date,
                                                 TRUNC (ooha.creation_date)
                                                )
                                        AND NVL (:P_TO_DATE,
                                                 TRUNC (ooha.creation_date)
                                                )
     AND SUBSTR (oola.ordered_item, 7, 1) =
                     NVL (:P_goodstdretretree, SUBSTR (oola.ordered_item, 7, 1))
     AND oola.inventory_item_id = msib.inventory_item_id
     AND oola.ship_from_org_id = msib.organization_id
     AND moq.inventory_item_id = msib.inventory_item_id
     AND moq.organization_id = msib.organization_id
     AND moq.subinventory_code IN
                                 ('ASRS-1', 'ASRS-2', 'GODOWN-1', 'GODOWN-2')
     AND ooha.order_type_id = ottt.transaction_type_id
     AND flv.lookup_code = UPPER (ottt.NAME)
     AND flv.tag = NVL (:P_order_type, flv.tag)
     &P_fscnfsc
     &P_sheet
     &P_machine
GROUP BY oola.ordered_item, ooha.header_id, oola.line_id,flv.tag)
group by tag , item_code , machine , fsc_nfsc , gsm , width , TQ ,p_goodstdretretree