MATERIALIZED VIEW: APPS.OKI_SRM_005_MV

Source

SELECT sle_id                      SLE_ID,
  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,
  sl_line_number                   LINE_NUMBER,
  sl_sts_code                      STS_CODE,
  sl_trn_code                      TRN_CODE,
  sl_renewal_type                  RENEWAL_TYPE_CODE,
  curr_code                        CURR_CODE,
  curr_code_f                      CURR_CODE_F,
  vbh_category_id                  ITEM_CATEGORY_ID,
  service_item_id                  ITEM_ID,
  service_item_org_id              INV_ORG_ID,
  scs_code                         SCS_CODE,
  authoring_org_id                 AUTHORING_ORG_ID,
  trx_func_curr_rate               TRX_FUNC_CURR_RATE,
  func_global_curr_rate            FUNC_GLOBAL_CURR_RATE,
  func_sglobal_curr_rate           FUNC_SGLOBAL_CURR_RATE,
  TRUNC(sl_date_cancelled)         DATE_CANCELLED,
  TRUNC(sl_start_date)             START_DATE,
  TRUNC(sl_end_date)               END_DATE,
  TRUNC(sl_date_terminated)        DATE_TERMINATED,
  sl_creation_date                 CREATION_DATE,
  sl_last_update_date              LAST_UPDATE_DATE,
  sl_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,
       ENI.ENI_OLTP_ITEM_STAR STAR
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)
  AND  star.inventory_item_id = service_item_id
  AND  star.organization_id = service_item_org_id
GROUP BY  sle_id
         , chr_id
         , contract_number
         , contract_number_modifier
         , contract_number || DECODE(contract_number_modifier,
                                                  NULL, NULL ,
                                                  ' ' || contract_number_modifier )
         , sl_line_number
         , sl_sts_code
         , sl_trn_code
         , sl_renewal_type
         , curr_code
         , curr_code_f
         , vbh_category_id
         , service_item_id
         , service_item_org_id
         , scs_code
         , authoring_org_id
         , trx_func_curr_rate
         , func_global_curr_rate
         , func_sglobal_curr_rate
         , TRUNC(sl_date_cancelled)
         , TRUNC(sl_start_date)
         , TRUNC(sl_end_date)
         , TRUNC(sl_date_terminated)
         , sl_creation_date
         , sl_last_update_date
         , sl_term_cancel_source