6 Replies Latest reply: Feb 22, 2013 5:22 PM by 992621 RSS

    Edit Query to Do a Count (*)

    992621
      SELECT oh1.partner "Dealer",
      od.sales_code "Dealer Sales Code",
      oh1. external_order_number "Original Dealer Order Number",
      oh1.region,
      cust. billing_account_number "BAN",
      dsl.n_order "DSL OrderNo",
      dsl.tn_or_ftn "DSL TN",
      dsl.activation_date "DSL Activation",
      telco.tn "ACCESS_TN",
      to_char( oh1.created_ts, 'YYYY-MM-DD') "Original Order Date",
      pack1.display_name "Original Order Description",
      pack1.price "Original Plan Price",
      uname. user_first_name "Agent First Name",
      uname.user_last_name "Agent Last Name",
      uname.user_name "Agent ID",
      -- to_char(dh.timestamp, 'yyyy-mm-dd HH:MI') "USCS Button Pressed",
      to_char(oh2.created_ts, 'YYYY-MM-DD') "USCS Order Date",
      pack2.display_name "USCS - Order Description",
      CASE
      WHEN cd.state IN ('1','15') THEN 'RECEIVED'
      WHEN cd.state IN ('2') THEN 'PROCESSING'
      WHEN cd.state IN ('3','8','16','18') THEN 'COMPLETE'
      WHEN cd.state IN ('4','9','17') THEN 'CANCELED'
      WHEN cd.state IN ('6','10','13','14') THEN 'PENDING'
      WHEN cd.state IN ('7') THEN 'INCOMPLETE'
      WHEN cd.state IN ('12','19','20') THEN 'SUBMITTED'
      ELSE 'OTHER'
      END AS state_desc,
      pack2.price "USCS - Plan Price",
      pack2.price - pack1.price "Net Change",
      ina.first_name,
      ina.last_name,
      ina.add_line_1,
      ina.city,
      ina. STATE,
      ina.zip

      FROM ain.impl_oh_order_header oh1
      INNER JOIN ain.impl_oh_order_header oh2 ON oh1.external_order_number = oh2.external_order_number
      INNER JOIN ain.impl_order_data od ON oh1.transaction_id = od.transaction_id
      INNER JOIN ain.impl_package pack1 ON oh1.transaction_id = pack1.transaction_id
      INNER JOIN ain.impl_package pack2 ON oh2.transaction_id = pack2.transaction_id
      INNER JOIN ain.impl_name_address ina ON oh1.transaction_id = ina.transaction_id
      INNER JOIN ain.impl_customer cust ON oh2.transaction_id = cust.transaction_id
      LEFT OUTER JOIN ain.impl_dsl dsl ON oh2.transaction_id = dsl.transaction_id
      LEFT OUTER JOIN ain.impl_access telco ON oh2.transaction_id = telco.transaction_id
      INNER JOIN ain.sncr_order_curr_disp cd ON cd.transaction_id = oh2.transaction_id
      INNER JOIN AIN.sncr_order_disp_head dh ON oh1.transaction_id = dh.transaction_id
      INNER JOIN ain.sncr_order_disposition disp ON dh.disp_transaction_id = disp.disp_transaction_id
      INNER JOIN ain.sncr_ssm_principal uname ON uname.user_id = dh.user_id

      WHERE oh2.created_ts BETWEEN to_date('3/1/2012 00:00:00','mm/dd/yyyy hh24:mi:ss') AND to_date('3/31/2012 23:59:00', 'mm/dd/yyyy hh24:mi:ss')
      AND oh1.uscs = 0 AND oh2.uscs = 1
      AND oh1.external_order_number NOT LIKE '%PROD_TEST%'
      AND oh2.external_order_number NOT LIKE '%PROD_TEST%'
      AND oh1.order_type = 'ORDER'
      AND oh2.order_type = 'ORDER'
      AND pack1.product_type = 'ORDER'
      AND pack2.product_type = 'ORDER'
      AND disp.category = 110 AND disp.state = 5
      AND disp.trx_seq = (SELECT MAX(trx_seq)
      FROM ain.sncr_order_disposition sod, ain.sncr_order_disp_head sodh
      WHERE sodh.disp_transaction_id = sod.disp_transaction_id
      AND sodh.transaction_id = oh1.transaction_id
      AND sod.category = 110 AND sod.state = 5)
      AND pack1.offer_id IS NOT NULL
      AND pack2.offer_id IS NOT NULL
      AND ina.type = 'SERVICE'
      AND cd.category = 100

      ORDER BY to_char(oh2.created_ts, 'YYYY-MM-DD')