/* Formatted on 2007/08/07 10:48 (Formatter Plus v4.8.6) */
SELECT *
  FROM (SELECT /*+ parallel(c 4)*/
               NVL (d.call_type_id, -99) AS db_call_type_id,
               NVL (d.service_id, -99) AS db_service_id,
               NVL (d.distance_band_id, -99) AS db_distance_band_id,
               ROW_NUMBER () OVER (PARTITION BY c.call_id, d.call_type_id ORDER BY LENGTH
                                              (d.check_digits) DESC NULLS LAST)
                                                              AS record_order,
               c.record_no, c.call_id, c.subscriber_id, c.service_id,
               c.tariff_id, c.subscriber_business_type_id,
               c.subscriber_marketing_type_id, c.gsm_payment_type_id,
               c.distance_band_id, c.time_band_id, c.time_id,
               c.duration_band_id, c.cell_id, c.equipment_id, c.roaming_id,
               c.gsm_method_id, c.cboss_call_type_id, c.a_number, c.b_number,
               c.a_number_original, c.b_number_original, c.call_date,
               c.etl_date, c.DURATION, c.rounded_duration, c.imei,
               c.call_charge, c.interoper_charge, c.uplink, c.downlink,
               c.service_id_2, c.call_type_id_2, c.call_charge_with_vat,
               c.direction_type_id, c.account_typ
          FROM (SELECT   /*+ parallel (e 4)  */
                         k.master_source_system_value1
                                                  AS orga_call_type_src_value,
                         k.detail_dwh_id AS cboss_call_type_id, e.record_no,
                         e.call_id, e.subscriber_id, e.service_id,
                         e.tariff_id, e.subscriber_business_type_id,
                         e.subscriber_marketing_type_id,
                         e.gsm_payment_type_id, e.distance_band_id,
                         e.time_band_id, e.time_id, e.duration_band_id,
                         e.cell_id, e.equipment_id, e.roaming_id,
                         e.gsm_method_id, e.call_type_id, e.a_number,
                         e.b_number, e.a_number_original, e.b_number_original,
                         e.call_date, e.etl_date, e.DURATION,
                         e.rounded_duration, e.imei, e.call_charge,
                         e.interoper_charge, e.uplink, e.downlink,
                         e.service_id_2, e.call_type_id_2,
                         e.call_charge_with_vat, e.direction_type_id,
                         e.local_flag, e.account_typ
                    FROM dwh_adm_key_transformation k,
                         (SELECT *
                            FROM dwh_tmp_pre_calldetails_6_1
                           WHERE local_flag = 'INT') e
                   WHERE k.master_trans_src_sys_value1 = 374
                     AND k.source_system_id = 2
                     AND e.call_type_id_2 = k.master_dwh_id(+)
                ORDER BY e.call_id, e.call_type_id, b_number) c,
               (SELECT *
                  FROM dwh_dim_distance_band
                 WHERE local_flag = 'INT' AND NVL (gsm_method_id, 2) = 2) d
         WHERE c.cboss_call_type_id = d.call_type_id(+)
           AND c.gsm_method_id = NVL (d.gsm_method_id(+), 2)
           AND c.direction_type_id = TO_NUMBER (d.service_direction(+))
           AND NVL (c.b_number, -1) LIKE NVL (d.check_digits(+), -1) || '%') a
 WHERE a.record_order = 1

INSERT STATEMENT Optimizer=ALL_ROWS (Cost=46107.3959168591 Card=763992 Bytes=490482864)
  PX COORDINATOR
    PX SEND* (QC (RANDOM)) OF :TQ10004 (Cost=46107.3959168591 Card=763992 Bytes=490482864)
      VIEW* (Cost=46107.3959168591 Card=763992 Bytes=490482864)
        WINDOW* (SORT PUSHED RANK) (Cost=46107.3959168591 Card=763992 Bytes=484370928)
          PX RECEIVE* (Cost=46107.3959168591 Card=763992 Bytes=484370928)
            PX SEND* (HASH) OF :TQ10003 (Cost=46107.3959168591 Card=763992 Bytes=484370928)
              VIEW*
                WINDOW* (CHILD PUSHED RANK) (Cost=46107.3959168591 Card=763992 Bytes=484370928)
                  HASH JOIN* (RIGHT OUTER) (Cost=17670.097249308 Card=763992 Bytes=484370928)
                    BUFFER* (SORT)
                      PX RECEIVE* (Cost=32.1019571406856 Card=6048 Bytes=187488)
                        PX SEND* (BROADCAST) OF :TQ10001 (Cost=32.1019571406856 Card=6048 Bytes=187488)
                          TABLE ACCESS (FULL) OF DWH_DIM_DISTANCE_BAND (TABLE) (Cost=32.1019571406856 Card=6048 Bytes=187488)
                    VIEW* (Cost=17635.1924274298 Card=763992 Bytes=460687176)
                      SORT* (ORDER BY) (Cost=17635.1924274298 Card=763992 Bytes=127586664)
                        PX RECEIVE* (Cost=9824.85769449657 Card=763992 Bytes=127586664)
                          PX SEND* (RANGE) OF :TQ10002 (Cost=9824.85769449657 Card=763992 Bytes=127586664)
                            HASH JOIN* (Cost=9824.85769449657 Card=763992 Bytes=127586664)
                              BUFFER* (SORT)
                                PX RECEIVE* (Cost=170.804665557204 Card=1 Bytes=21)
                                  PX SEND* (BROADCAST) OF :TQ10000 (Cost=170.804665557204 Card=1 Bytes=21)
                                    TABLE ACCESS (FULL) OF DWH_ADM_KEY_TRANSFORMATION (TABLE) (Cost=170.804665557204 Card=1 Bytes=21)
                              PX BLOCK* (ITERATOR) (Cost=9629.73513927536 Card=7995580 Bytes=1167354680)
                                TABLE ACCESS* (FULL) OF DWH_TMP_PRE_CALLDETAILS_6_1 (TABLE) (Cost=9629.73513927536 Card=7995580 Bytes=1167354680)