DWADW.DW_MV_PPTO_BGC_GL

Base de datos origen: ADW COEXITO
Tipo de objeto: MATERIALIZED VIEW
SQL

  CREATE MATERIALIZED VIEW "DWADW"."DW_MV_PPTO_BGC_GL" ("BUDGETCCID", "CUENTA", "CECO_BENE", "UBICACION", "LINEA", "PROYECTOS", "CECO_GEST", "PERIODO", "PRESUPUESTO_INICIAL", "AJUSTES_PRESUPUESTO", "PRESUPUESTO_TOTAL", "COMPROMISOS", "OBLIGACIONES", "OTRO_CONSUMO", "GASTOS", "CONSUMO_TOTAL", "FONDOS_DISPONIBLES", "FONDOS_DISPONIBLES_PORCENTAJE", "CONCATENAR")
  DEFAULT COLLATION "USING_NLS_COMP"  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 
 COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING LOGGING
  BUILD DEFERRED
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE DISABLE CONCURRENT REFRESH
  AS SELECT
        BAL.BUDGETCCID,

        /* Reemplaza únicamente la G inicial por 5 */
        CASE
            WHEN UPPER(TRIM(AC.SEGMENTVALUE1)) LIKE 'G%'
            THEN '5' || SUBSTR(TRIM(AC.SEGMENTVALUE1), 2)
            ELSE TRIM(AC.SEGMENTVALUE1)
        END AS CUENTA,
        TRIM(AC.SEGMENTVALUE2) AS CECO_BENE,
        TRIM(AC.SEGMENTVALUE3) AS UBICACION,
        TRIM(AC.SEGMENTVALUE4) AS LINEA,
        TRIM(AC.SEGMENTVALUE5) AS PROYECTOS,
        TRIM(AC.SEGMENTVALUE6) AS CECO_GEST,
        TRIM(BAL.PERIODNAME) AS PERIODO,
        NVL(BAL.BUDGETAMOUNT, 0) - NVL(BAL.BUDGETADJUSTMENTAMOUNT, 0) AS PRESUPUESTO_INICIAL,
        NVL(BAL.BUDGETADJUSTMENTAMOUNT, 0) AS AJUSTES_PRESUPUESTO,
        NVL(BAL.BUDGETAMOUNT, 0) AS PRESUPUESTO_TOTAL,
        NVL(BAL.COMMITMENTAMOUNT, 0) AS COMPROMISOS,
        NVL(BAL.OBLIGATIONAMOUNT, 0) AS OBLIGACIONES,
        NVL(BAL.OTHERAMOUNT, 0) AS OTRO_CONSUMO,
        NVL(BAL.ACTUALAMOUNT, 0) AS GASTOS,
        NVL(BAL.COMMITMENTAMOUNT, 0) + NVL(BAL.OBLIGATIONAMOUNT, 0)+ NVL(BAL.ACTUALAMOUNT, 0)+ NVL(BAL.OTHERAMOUNT, 0) AS CONSUMO_TOTAL,
        NVL(BAL.BUDGETAMOUNT, 0) - ( NVL(BAL.COMMITMENTAMOUNT, 0) + NVL(BAL.OBLIGATIONAMOUNT, 0)+ NVL(BAL.ACTUALAMOUNT, 0)+ NVL(BAL.OTHERAMOUNT, 0)) FONDOS_DISPONIBLES,
        ROUND(NVL(( NVL(BAL.BUDGETAMOUNT, 0)
            - ( NVL(BAL.COMMITMENTAMOUNT, 0)+ NVL(BAL.OBLIGATIONAMOUNT, 0)+ NVL(BAL.ACTUALAMOUNT, 0)+ NVL(BAL.OTHERAMOUNT, 0)))/ NULLIF(NVL(BAL.BUDGETAMOUNT, 0), 0)
            * 100,0),2) AS FONDOS_DISPONIBLES_PORCENTAJE,
        (CASE
            WHEN UPPER(TRIM(AC.SEGMENTVALUE1)) LIKE 'G%'
            THEN '5' || SUBSTR(TRIM(AC.SEGMENTVALUE1), 2)
            ELSE TRIM(AC.SEGMENTVALUE1)
        END)
        || TRIM(AC.SEGMENTVALUE2)
        || TRIM(AC.SEGMENTVALUE3)
        || TRIM(AC.SEGMENTVALUE4)
        || TRIM(AC.SEGMENTVALUE5)
        || TRIM(AC.SEGMENTVALUE6) AS CONCATENAR
    FROM DWADW.DIM_XCC_BUDGET_ACCOUNTS_BGC_GL AC
    INNER JOIN DWADW.FACT_XCC_BALANCES_BGC_GL BAL
        ON BAL.BUDGETCCID = AC.BUDGETCODECOMBINATIONID
   WHERE UPPER(TRIM(AC.SEGMENTVALUE1)) LIKE 'G%' AND TO_NUMBER(REGEXP_SUBSTR(TRIM(BAL.PERIODNAME),'[0-9]{2,4}$')) >= 25
        AND NOT REGEXP_LIKE(TRIM(BAL.PERIODNAME), '^[0-9]{4}$');
← Volver al esquema DWADW
🏠 Volver al menú principal