DISPACT SCHEDULE DETAIL REPORT

                                    Dispatch Schedule Detail Transporter Wise Report and Abstract Report .

/* Formatted on 2013/01/21 14:19 (Formatter Plus v4.8.8) */
SELECT   hl.state, hl.city,
         TO_CHAR (oola.schedule_ship_date,
                  'DD-MON-YYYY HH24:MI'
                 ) schedule_date,
         TO_CHAR (oola.schedule_ship_date, 'DDMMYYHH24MI') ref_no,
         ooha.order_number, hcaa.account_number customer_code,
         hp.party_name customer_name, indentor.account_number indentor_code,
         indentor.party_name indentor_name, oola.ordered_item item,
         NVL (varity_desc.description, micv.segment3) variety,
         micv.segment4 gsm,
         DECODE (msib.dimension_uom_code,
                 'CM', NVL (msib.unit_width, 0) || ' X '
                  || NVL (msib.unit_length, 0),
                 'MM', NVL (msib.unit_width, 0) / 10
                  || ' X '
                  || NVL (msib.unit_length, 0) / 10,
                 NULL, '0 X 0'
                ) item_size,
         wdd.quantity quantity,
         oola.line_number || '.' || oola.shipment_number line_number,
         ooha.attribute4 packing_instructions, nvl2(wcv.carrier_name,wcv.carrier_name||' / ',wcv.carrier_name) carrier_name,
            shipping_details.meaning shipping_code, excise_exempt_details.meaning excise_exempt_type,  
     hcsua.LOCATION||'-'||hl.city ship_to_location
    FROM apps.hz_cust_accounts_all hcaa,
         apps.hz_cust_acct_sites_all hcasa,
         apps.hz_cust_site_uses_all hcsua,
         apps.hz_party_sites hps,
         apps.hz_locations hl,
         apps.hz_parties hp,
         apps.oe_order_headers_all ooha,
         apps.oe_order_lines_all oola,
         apps.mtl_item_categories_v micv,
         apps.mtl_system_items_b msib,
         (SELECT   source_line_id, SUM (requested_quantity) quantity
              FROM apps.wsh_delivery_details
             WHERE released_status IN ('R', 'B', 'S')
          GROUP BY source_line_id) wdd,
         (SELECT ffvt.description, ffv.flex_value
            FROM apps.fnd_flex_value_sets ffvs,
                 apps.fnd_flex_values ffv,
                apps. fnd_flex_values_tl ffvt
           WHERE ffvs.flex_value_set_name = 'TNPL_OM_VARIETY_SIZE'
             AND ffv.flex_value_set_id = ffvs.flex_value_set_id
             AND ffvt.flex_value_id = ffv.flex_value_id) varity_desc,
         (SELECT hp.party_name, hp.party_id, hcaa.account_number
            FROM apps.hz_parties hp, apps.hz_cust_accounts_all hcaa
           WHERE hcaa.party_id = hp.party_id
             AND UPPER (hp.attribute1) = 'DEALER') indentor,
         (SELECT meaning, lookup_code
            FROM apps.fnd_lookup_values
           WHERE lookup_type = 'SHIP_METHOD'
             AND enabled_flag = 'Y'
             AND NVL (start_date_active, SYSDATE) <= SYSDATE
             AND NVL (end_date_active, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)) shipping_details,
         (SELECT meaning, lookup_code
            FROM apps.fnd_lookup_values
           WHERE lookup_type = 'JAI_EXCISE_EXEMPTED_TYPE'
             AND enabled_flag = 'Y'
             AND NVL (start_date_active, SYSDATE) <= SYSDATE
             AND NVL (end_date_active, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)) excise_exempt_details,
         apps.wsh_carriers_v wcv,
         apps.jai_om_oe_so_lines joosl
   WHERE hcasa.cust_account_id = hcaa.cust_account_id
     AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
--     AND hcsua.primary_flag = 'Y'
     AND hcsua.status = 'A'
      AND hcsua.site_use_code = 'SHIP_TO'
     AND hcasa.party_site_id = hps.party_site_id
     AND hl.location_id = hps.location_id
     AND hp.party_id = hcaa.party_id
     AND indentor.party_id(+) = ooha.attribute1
     AND oola.header_id = ooha.header_id
     AND oola.flow_status_code = 'AWAITING_SHIPPING'
     AND oola.schedule_status_code = 'SCHEDULED'
     AND oola.schedule_ship_date IS NOT NULL
     AND hcsua.site_use_id = oola.ship_to_org_id
     AND micv.inventory_item_id = oola.inventory_item_id
     AND micv.organization_id = oola.ship_from_org_id
     AND UPPER (micv.category_set_name) = 'INVENTORY'
AND micv.SEGMENT1 <> 'NB'
     AND varity_desc.flex_value(+) = micv.segment3
     AND msib.inventory_item_id = oola.inventory_item_id
     AND msib.organization_id = oola.ship_from_org_id
     AND hl.city = NVL (:city, hl.city)
     AND NVL (hl.state, 'A') = NVL (:state, NVL (hl.state, 'A'))
     AND ooha.order_type_id = NVL (:order_type, ooha.order_type_id)
     AND hcaa.account_number = NVL (:customer_code, hcaa.account_number)
     AND TRUNC (oola.schedule_ship_date)
            BETWEEN NVL (:from_schedule_date, TRUNC (oola.schedule_ship_date))
                AND NVL (:to_schedule_date, TRUNC (oola.schedule_ship_date))
     AND wdd.source_line_id = oola.line_id
     AND shipping_details.lookup_code(+) = oola.shipping_method_code
     AND wcv.freight_code(+) = oola.freight_carrier_code
     AND joosl.header_id = oola.header_id
     AND joosl.line_id = oola.line_id
     AND excise_exempt_details.lookup_code(+) = joosl.excise_exempt_type
     AND :group_by = 'City'
     AND oola.ship_from_org_id = :org_id
-- AND  OOHA.ORDER_NUMBER = NVL(:ORDER_NUMBER,OOHA.ORDER_NUMBER)
ORDER BY oola.schedule_ship_date;

 ---------------------------------------------------------------------------------------------------------
 
/* Formatted on 2013/01/08 01:12 (Formatter Plus v4.8.8) */
SELECT                                                               --ROWNUM,
         SUM (NVL (heet_qty, 0)) heet_qty, SUM (NVL (reel_qty, 0)) reel_qty,
         SUM (NVL (copier_qty, 0)) copier_qty, SUM (quantity), city,
         carrier_name
    FROM (SELECT hl.state, hl.city,
                 TO_CHAR (oola.schedule_ship_date,
                          'DD-MON-YYYY HH24:MI'
                         ) schedule_date,
                 TO_CHAR (oola.schedule_ship_date, 'DDMMYYHH24MI') ref_no,
                 ooha.order_number, hcaa.account_number customer_code,
                 hp.party_name customer_name,
                 indentor.account_number indentor_code,
                 indentor.party_name indentor_name, oola.ordered_item item,
                 NVL (varity_desc.description, micv.segment3) variety,
                 micv.segment4 gsm,
                 DECODE (msib.dimension_uom_code,
                         'CM', NVL (msib.unit_width, 0)
                          || ' X '
                          || NVL (msib.unit_length, 0),
                         'MM', NVL (msib.unit_width, 0) / 10
                          || ' X '
                          || NVL (msib.unit_length, 0) / 10,
                         NULL, '0 X 0'
                        ) item_size,
                 wdd.quantity quantity,
                 oola.line_number || '.' || oola.shipment_number line_number,
                 ooha.attribute4 packing_instructions,
                 NVL2 (wcv.carrier_name,
                       wcv.carrier_name || ' / ',
                       wcv.carrier_name
                      ) carrier_name,
                 shipping_details.meaning shipping_code,
                 excise_exempt_details.meaning excise_exempt_type,
                 hcsua.LOCATION || '-' || hl.city ship_to_location,
                 (SELECT SUM (ool.ordered_quantity)
                    FROM apps.oe_order_lines_all ool
                   WHERE ool.ordered_item LIKE 'B%'
                     AND ool.header_id = ooha.header_id
                     AND ool.line_id = oola.line_id) heet_qty,
                 (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 (   ool.ordered_item LIKE 'Z%'
                          OR ool.ordered_item LIKE 'X%'
                         )
                     AND ool.header_id = oola.header_id
                     AND ool.line_id = oola.line_id) copier_qty
            --                     SUM (heet_qty + reel_qty + copier_qty) total_qty
          FROM   apps.hz_cust_accounts_all hcaa,
                 apps.hz_cust_acct_sites_all hcasa,
                 apps.hz_cust_site_uses_all hcsua,
                 apps.hz_party_sites hps,
                 apps.hz_locations hl,
                 apps.hz_parties hp,
                 apps.oe_order_headers_all ooha,
                 apps.oe_order_lines_all oola,
                 apps.mtl_item_categories_v micv,
                 apps.mtl_system_items_b msib,
                 (SELECT   source_line_id, SUM (requested_quantity) quantity
                      FROM apps.wsh_delivery_details
                     WHERE released_status IN ('R', 'B', 'S')
                  GROUP BY source_line_id) wdd,
                 (SELECT ffvt.description, ffv.flex_value
                    FROM apps.fnd_flex_value_sets ffvs,
                         apps.fnd_flex_values ffv,
                         apps.fnd_flex_values_tl ffvt
                   WHERE ffvs.flex_value_set_name = 'TNPL_OM_VARIETY_SIZE'
                     AND ffv.flex_value_set_id = ffvs.flex_value_set_id
                     AND ffvt.flex_value_id = ffv.flex_value_id) varity_desc,
                 (SELECT hp.party_name, hp.party_id, hcaa.account_number
                    FROM apps.hz_parties hp, apps.hz_cust_accounts_all hcaa
                   WHERE hcaa.party_id = hp.party_id
                     AND UPPER (hp.attribute1) = 'DEALER') indentor,
                 (SELECT meaning, lookup_code
                    FROM apps.fnd_lookup_values
                   WHERE lookup_type = 'SHIP_METHOD'
                     AND enabled_flag = 'Y'
                     AND NVL (start_date_active, SYSDATE) <= SYSDATE
                     AND NVL (end_date_active, TRUNC (SYSDATE) + 1) >
                                                               TRUNC (SYSDATE)) shipping_details,
                 (SELECT meaning, lookup_code
                    FROM apps.fnd_lookup_values
                   WHERE lookup_type = 'JAI_EXCISE_EXEMPTED_TYPE'
                     AND enabled_flag = 'Y'
                     AND NVL (start_date_active, SYSDATE) <= SYSDATE
                     AND NVL (end_date_active, TRUNC (SYSDATE) + 1) >
                                                               TRUNC (SYSDATE)) excise_exempt_details,
                 apps.wsh_carriers_v wcv,
                 apps.jai_om_oe_so_lines joosl
           WHERE hcasa.cust_account_id = hcaa.cust_account_id
             AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
             --     AND hcsua.primary_flag = 'Y'
             AND hcsua.status = 'A'
             AND hcsua.site_use_code = 'SHIP_TO'
             AND hcasa.party_site_id = hps.party_site_id
             AND hl.location_id = hps.location_id
             AND hp.party_id = hcaa.party_id
             AND indentor.party_id(+) = ooha.attribute1
             AND oola.header_id = ooha.header_id
             AND oola.flow_status_code = 'AWAITING_SHIPPING'
             AND oola.schedule_status_code = 'SCHEDULED'
             AND oola.schedule_ship_date IS NOT NULL
             AND micv.segment1 <> 'NB'
             AND hcsua.site_use_id = oola.ship_to_org_id
             AND micv.inventory_item_id = oola.inventory_item_id
             AND micv.organization_id = oola.ship_from_org_id
             AND UPPER (micv.category_set_name) = 'INVENTORY'
             AND varity_desc.flex_value(+) = micv.segment3
             AND msib.inventory_item_id = oola.inventory_item_id
             AND msib.organization_id = oola.ship_from_org_id
             AND hl.city = NVL (:city, hl.city)
             AND NVL (hl.state, 'A') = NVL (:state, NVL (hl.state, 'A'))
             AND ooha.order_type_id = NVL (:order_type, ooha.order_type_id)
             AND hcaa.account_number =
                                     NVL (:customer_code, hcaa.account_number)
             AND TRUNC (oola.schedule_ship_date)
                    BETWEEN NVL (:from_schedule_date,
                                 TRUNC (oola.schedule_ship_date)
                                )
                        AND NVL (:to_schedule_date,
                                 TRUNC (oola.schedule_ship_date)
                                )
             AND wdd.source_line_id = oola.line_id
             AND shipping_details.lookup_code(+) = oola.shipping_method_code
             AND wcv.freight_code(+) = oola.freight_carrier_code
             AND joosl.header_id = oola.header_id
             AND joosl.line_id = oola.line_id
             AND excise_exempt_details.lookup_code(+) =
                                                      joosl.excise_exempt_type
             AND :group_by = 'City'
             AND oola.ship_from_org_id = NVL (:org_id, oola.ship_from_org_id))
GROUP BY carrier_name, city
UNION
SELECT                                                               --ROWNUM,
         SUM (NVL (heet_qty, 0)) heet_qty, SUM (NVL (reel_qty, 0)) reel_qty,
         SUM (NVL (copier_qty, 0)) copier_qty, SUM (quantity), city,
         carrier_name
    FROM (SELECT hl.state, hl.city,
                 TO_CHAR (oola.schedule_ship_date,
                          'DD-MON-YYYY HH24:MI'
                         ) schedule_date,
                 TO_CHAR (oola.schedule_ship_date, 'DDMMYYHH24MI') ref_no,
                 ooha.order_number, hcaa.account_number customer_code,
                 hp.party_name customer_name,
                 indentor.account_number indentor_code,
                 indentor.party_name indentor_name, oola.ordered_item item,
                 NVL (varity_desc.description, micv.segment3) variety,
                 micv.segment4 gsm,
                 DECODE (msib.dimension_uom_code,
                         'CM', NVL (msib.unit_width, 0)
                          || ' X '
                          || NVL (msib.unit_length, 0),
                         'MM', NVL (msib.unit_width, 0) / 10
                          || ' X '
                          || NVL (msib.unit_length, 0) / 10,
                         NULL, '0 X 0'
                        ) item_size,
                 wdd.quantity quantity,
                 oola.line_number || '.' || oola.shipment_number line_number,
                 ooha.attribute4 packing_instructions,
                 NVL2 (wcv.carrier_name,
                       wcv.carrier_name || ' / ',
                       wcv.carrier_name
                      ) carrier_name,
                 shipping_details.meaning shipping_code,
                 excise_exempt_details.meaning excise_exempt_type,
                 hcsua.LOCATION || '-' || hl.city ship_to_location,
                 (SELECT SUM (ool.ordered_quantity)
                    FROM apps.oe_order_lines_all ool
                   WHERE ool.ordered_item LIKE 'B%'
                     AND ool.header_id = ooha.header_id
                     AND ool.line_id = oola.line_id) heet_qty,
                 (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 (   ool.ordered_item LIKE 'Z%'
                          OR ool.ordered_item LIKE 'X%'
                         )
                     AND ool.header_id = oola.header_id
                     AND ool.line_id = oola.line_id) copier_qty
            --                     SUM (heet_qty + reel_qty + copier_qty) total_qty
          FROM   apps.hz_cust_accounts_all hcaa,
                 apps.hz_cust_acct_sites_all hcasa,
                 apps.hz_cust_site_uses_all hcsua,
                 apps.hz_party_sites hps,
                 apps.hz_locations hl,
                 apps.hz_parties hp,
                 apps.oe_order_headers_all ooha,
                 apps.oe_order_lines_all oola,
                 apps.mtl_item_categories_v micv,
                 apps.mtl_system_items_b msib,
                 (SELECT   source_line_id, SUM (requested_quantity) quantity
                      FROM apps.wsh_delivery_details
                     WHERE released_status IN ('R', 'B', 'S')
                  GROUP BY source_line_id) wdd,
                 (SELECT ffvt.description, ffv.flex_value
                    FROM apps.fnd_flex_value_sets ffvs,
                         apps.fnd_flex_values ffv,
                         apps.fnd_flex_values_tl ffvt
                   WHERE ffvs.flex_value_set_name = 'TNPL_OM_VARIETY_SIZE'
                     AND ffv.flex_value_set_id = ffvs.flex_value_set_id
                     AND ffvt.flex_value_id = ffv.flex_value_id) varity_desc,
                 (SELECT hp.party_name, hp.party_id, hcaa.account_number
                    FROM apps.hz_parties hp, apps.hz_cust_accounts_all hcaa
                   WHERE hcaa.party_id = hp.party_id
                     AND UPPER (hp.attribute1) = 'DEALER') indentor,
                 (SELECT meaning, lookup_code
                    FROM apps.fnd_lookup_values
                   WHERE lookup_type = 'SHIP_METHOD'
                     AND enabled_flag = 'Y'
                     AND NVL (start_date_active, SYSDATE) <= SYSDATE
                     AND NVL (end_date_active, TRUNC (SYSDATE) + 1) >
                                                               TRUNC (SYSDATE)) shipping_details,
                 (SELECT meaning, lookup_code
                    FROM apps.fnd_lookup_values
                   WHERE lookup_type = 'JAI_EXCISE_EXEMPTED_TYPE'
                     AND enabled_flag = 'Y'
                     AND NVL (start_date_active, SYSDATE) <= SYSDATE
                     AND NVL (end_date_active, TRUNC (SYSDATE) + 1) >
                                                               TRUNC (SYSDATE)) excise_exempt_details,
                 apps.wsh_carriers_v wcv,
                 apps.jai_om_oe_so_lines joosl
           WHERE hcasa.cust_account_id = hcaa.cust_account_id
             AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
             --     AND hcsua.primary_flag = 'Y'
             AND hcsua.status = 'A'
             AND hcsua.site_use_code = 'SHIP_TO'
             AND hcasa.party_site_id = hps.party_site_id
             AND hl.location_id = hps.location_id
             AND hp.party_id = hcaa.party_id
             AND indentor.party_id(+) = ooha.attribute1
             AND oola.header_id = ooha.header_id
             AND oola.flow_status_code = 'AWAITING_SHIPPING'
             AND oola.schedule_status_code = 'SCHEDULED'
             AND oola.schedule_ship_date IS NOT NULL
             AND micv.segment1 <> 'NB'
             AND hcsua.site_use_id = oola.ship_to_org_id
             AND micv.inventory_item_id = oola.inventory_item_id
             AND micv.organization_id = oola.ship_from_org_id
             AND UPPER (micv.category_set_name) = 'INVENTORY'
             AND varity_desc.flex_value(+) = micv.segment3
             AND msib.inventory_item_id = oola.inventory_item_id
             AND msib.organization_id = oola.ship_from_org_id
             AND hl.city = NVL (:city, hl.city)
             AND NVL (hl.state, 'A') = NVL (:state, NVL (hl.state, 'A'))
             AND ooha.order_type_id = NVL (:order_type, ooha.order_type_id)
             AND hcaa.account_number =
                                     NVL (:customer_code, hcaa.account_number)
             AND TRUNC (oola.schedule_ship_date)
                    BETWEEN NVL (:from_schedule_date,
                                 TRUNC (oola.schedule_ship_date)
                                )
                        AND NVL (:to_schedule_date,
                                 TRUNC (oola.schedule_ship_date)
                                )
             AND wdd.source_line_id = oola.line_id
             AND shipping_details.lookup_code(+) = oola.shipping_method_code
             AND wcv.freight_code(+) = oola.freight_carrier_code
             AND joosl.header_id = oola.header_id
             AND joosl.line_id = oola.line_id
             AND excise_exempt_details.lookup_code(+) =
                                                      joosl.excise_exempt_type
             AND :group_by = 'Schedule Date'
             AND oola.ship_from_org_id = NVL (:org_id, oola.ship_from_org_id))
GROUP BY carrier_name, city
ORDER BY carrier_name