MATERIALIZED VIEW: APPS.OKI_SRM_006_MV

Source

SELECT
  chr_id                           CHR_ID,
  contract_number                  CONTRACT_NUMBER,
  contract_number_modifier         CONTRACT_NUMBER_MODIFIER,
  contract_number || DECODE(contract_number_modifier,
                                        NULL, NULL ,
                                        ' ' || contract_number_modifier ) COMPLETE_K_NUMBER,
  customer_party_id                CUSTOMER_PARTY_ID,
  salesrep_id                      SALESREP_ID,
  authoring_org_id                 AUTHORING_ORG_ID,
  resource_group_id                RG_ID,
  resource_id                      RESOURCE_ID,
  scs_code                         SCS_CODE,
  hdr_sts_code                     STS_CODE,
  hdr_trn_code                     TRN_CODE,
  hdr_renewal_type                 RENEWAL_TYPE_CODE,
  hdr_order_number                 ORDER_NUMBER,
  curr_code                        CURR_CODE,
  curr_code_f                      CURR_CODE_F,
  win_percent                      WIN_PERCENT,
  TRUNC(expected_close_date)       EXPECTED_CLOSE_DATE,
  TRUNC(hdr_date_cancelled)        DATE_CANCELLED,
  TRUNC(hdr_date_signed)           DATE_SIGNED,
  TRUNC(hstart_date)               START_DATE,
  TRUNC(hend_date)                 END_DATE,
  TRUNC(hdr_grace_end_date)-1      GRACE_END_DATE,
  TRUNC(hdr_date_approved)         DATE_APPROVED,
  TRUNC(hdr_date_terminated)       DATE_TERMINATED,
  hdr_creation_date                CREATION_DATE,
  hdr_last_update_date             LAST_UPDATE_DATE,
  hdr_bill_site_id                 BILL_SITE_ID,
  hdr_ship_site_id                 SHIP_SITE_ID,
  hdr_acct_rule_id                 ACCT_RULE_ID,
  hdr_subsequent_renewal_type      SUBSEQUENT_RENEWAL_TYPE_CODE,
  negotiation_status               NEGOTIATION_STATUS,
  reminder                         REMINDER,
  hdr_term_cancel_source           TERM_CANCEL_SOURCE,
  SUM(ubt_amt)                     UBT_AMT,
  SUM(ubt_amt_f)                   UBT_AMT_F,
  SUM(ubt_amt_g)                   UBT_AMT_G,
  SUM(ubt_amt_sg)                  UBT_AMT_SG,
  SUM(ubt_amt_a)                   UBT_AMT_A,
  SUM(supp_credit)                 SUPP_CREDIT,
  SUM(supp_credit_f)               SUPP_CREDIT_F,
  SUM(supp_credit_g)               SUPP_CREDIT_G,
  SUM(supp_credit_sg)              SUPP_CREDIT_SG,
  SUM(supp_credit_a)               SUPP_CREDIT_A,
  SUM(credit_amt)                  CREDIT_AMT,
  SUM(credit_amt_f)                CREDIT_AMT_F,
  SUM(credit_amt_g)                CREDIT_AMT_G,
  SUM(credit_amt_sg)               CREDIT_AMT_SG,
  SUM(credit_amt_a)                CREDIT_AMT_A,
  SUM(override_amt)                OVERRIDE_AMT,
  SUM(override_amt_f)              OVERRIDE_AMT_F,
  SUM(override_amt_g)              OVERRIDE_AMT_G,
  SUM(override_amt_sg)             OVERRIDE_AMT_SG,
  SUM(override_amt_a)              OVERRIDE_AMT_A,
  SUM(price_negotiated)            PRICE_NEGO,
  SUM(price_negotiated_f)	         PRICE_NEGO_F,
  SUM(price_negotiated_a)	         PRICE_NEGO_A,
  SUM(price_negotiated_g)	         PRICE_NEGO_G,
  SUM(price_negotiated_sg)	       PRICE_NEGO_SG,
/* Count Columns Start*/
  COUNT(ubt_amt)                   C_UBT_AMT,
  COUNT(ubt_amt_f)                 C_UBT_AMT_F,
  COUNT(ubt_amt_g)                 C_UBT_AMT_G,
  COUNT(ubt_amt_sg)                C_UBT_AMT_SG,
  COUNT(ubt_amt_a)                 C_UBT_AMT_A,
  COUNT(supp_credit)               C_SUPP_CREDIT,
  COUNT(supp_credit_f)             C_SUPP_CREDIT_F,
  COUNT(supp_credit_g)             C_SUPP_CREDIT_G,
  COUNT(supp_credit_sg)            C_SUPP_CREDIT_SG,
  COUNT(supp_credit_a)             C_SUPP_CREDIT_A,
  COUNT(credit_amt)                C_CREDIT_AMT,
  COUNT(credit_amt_f)              C_CREDIT_AMT_F,
  COUNT(credit_amt_g)              C_CREDIT_AMT_G,
  COUNT(credit_amt_sg)             C_CREDIT_AMT_SG,
  COUNT(credit_amt_a)              C_CREDIT_AMT_A,
  COUNT(override_amt)              C_OVERRIDE_AMT,
  COUNT(override_amt_f)            C_OVERRIDE_AMT_F,
  COUNT(override_amt_g)            C_OVERRIDE_AMT_G,
  COUNT(override_amt_sg)           C_OVERRIDE_AMT_SG,
  COUNT(override_amt_a)            C_OVERRIDE_AMT_A,
  COUNT(price_negotiated)          C_PRICE_NEGO,
  COUNT(price_negotiated_f)	   C_PRICE_NEGO_F,
  COUNT(price_negotiated_a)        C_PRICE_NEGO_A,
  COUNT(price_negotiated_g)        C_PRICE_NEGO_G,
  COUNT(price_negotiated_sg)       C_PRICE_NEGO_SG,
  COUNT(*)			   C_TOTAL
/* Count Columns End */
FROM   OKI.OKI_DBI_CLE_B
WHERE  application_id = 515
  AND  buy_or_sell = 'S'
  AND  gsd_flag = 1
  AND  term_flag = 1
  AND  scs_code IN ('SERVICE','WARRANTY')
  AND  root_lty_code IN ('SERVICE','WARRANTY','EXT_WARRANTY')
--  AND  ((renewal_flag = 1 AND p_cle_id IS NOT NULL) OR renewal_flag = 0)
GROUP BY  chr_id
         , contract_number
         , contract_number_modifier
         , contract_number || DECODE(contract_number_modifier,
                                                  NULL, NULL ,
                                                  ' ' || contract_number_modifier )
         , customer_party_id
         , salesrep_id
         , authoring_org_id
         , resource_group_id
         , resource_id
         , scs_code
         , hdr_sts_code
         , hdr_trn_code
         , hdr_renewal_type
         , hdr_order_number
         , curr_code
         , curr_code_f
         , win_percent
         , TRUNC(expected_close_date)
         , TRUNC(hdr_date_cancelled)
         , TRUNC(hdr_date_signed)
         , TRUNC(hstart_date)
         , TRUNC(hend_date)
         , TRUNC(hdr_date_approved)
         , TRUNC(hdr_date_terminated)
         , TRUNC(hdr_grace_end_date)-1
         , hdr_creation_date
         , hdr_last_update_date
         , hdr_bill_site_id
         , hdr_ship_site_id
         , hdr_acct_rule_id
	       , hdr_subsequent_renewal_type
         , negotiation_status
         , reminder
         , hdr_term_cancel_source