Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
DWADW.DW_MV_TRXCONTABILIDAD_GL_GL
Base de datos origen: ADW COEXITO
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "DWADW"."DW_MV_TRXCONTABILIDAD_GL_GL" ("TRXCONTABILIDADID", "PERIOD_YEAR", "PERIOD_NUMBER", "GL_DATE", "TRANSACTION_DATE", "TRANSACTION_NUMBER", "ACCOUNTED_DR", "ACCOUNTED_CR", "ENTERED_CURRENCY", "JE_SOURCE_NAME", "JE_CATEGORY_NAME", "SEGMENT2", "SEGMENT3", "SEGMENT4", "SEGMENT5", "SEGMENT6", "SEGMENT7", "SEGMENT8", "SEGMENT9", "SEGMENT10", "CREATED_BY", "LINE_DESCRIPTION", "EVENT_CLASS_CODE", "XLAHEADERID", "XLALINENUM", "GLAEHEADERID", "GLAELINENUM", "POSTING_STATUS_CODE", "TERCERO", "FISCAL_CODE_TERCERO", "PKHEADERID", "PKLINENUM", "PKORIGEN", "PO_HEADER_COMMENTS", "PO_NUMBER", "IS_MODIFIED")
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 WITH
/* ============================================================
BLOQUE GL
============================================================ */
conversion_data AS (
SELECT
SUBSTR(TO_CHAR(EXTRACT(YEAR FROM s.jrnllineeffectivedate)), 3, 2) AS period_year,
CASE
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 1 THEN 'Ene'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 2 THEN 'Feb'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 3 THEN 'Mar'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 4 THEN 'Abr'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 5 THEN 'May'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 6 THEN 'Jun'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 7 THEN 'Jul'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 8 THEN 'Ago'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 9 THEN 'Sep'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 10 THEN 'Oct'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 11 THEN 'Nov'
WHEN EXTRACT(MONTH FROM s.jrnllineeffectivedate) = 12 THEN 'Dic'
END AS period_number,
s.*
FROM dwadw.fact_journallinegl_gl s
),
distinct_flexgl AS (
SELECT DISTINCT
f.lcls_c_thrtprty_n,
f.je_header_id,
f.jelinenum,
f.party_id,
f.fiscal_code,
f.is_modified
FROM dwadw.fact_flex_gl f
),
join_4 AS (
SELECT
st.*,
fx.lcls_c_thrtprty_n,
fx.party_id,
fx.fiscal_code,
fx.is_modified
FROM conversion_data st
LEFT JOIN distinct_flexgl fx
ON st.jeheaderid = fx.je_header_id
AND st.jelinenum = fx.jelinenum
),
join_1 AS (
SELECT
j4.*,
gl.segment2,
gl.segment3,
gl.segment4,
gl.segment5,
gl.segment6,
gl.segment7,
gl.segment8,
gl.segment9,
gl.segment10
FROM join_4 j4
LEFT JOIN dwadw.dim_codecombination_gl gl
ON j4.jrnllinecodecombinationid = gl.code_combination_id
),
join_2 AS (
SELECT
j1.*,
js.source_name AS dw_source_name,
js.user_source_name
FROM join_1 j1
LEFT JOIN dwadw.dim_journalsource_gl js
ON j1.jrnlhdrjesource = js.source_name
),
add_data AS (
SELECT
j2.*,
CAST(NULL AS DATE) AS transaction_date,
CAST(NULL AS VARCHAR2(200)) AS transaction_number,
CAST(NULL AS VARCHAR2(500)) AS event_class_code,
CASE
WHEN j2.dw_source_name IN ('300000006344046', '300000006344047')
THEN j2.user_source_name
ELSE j2.dw_source_name
END AS source_name,
'GL' AS idorigen
FROM join_2 j2
)
/*============================================================
SELECT GL
============================================================ */
SELECT DISTINCT
TO_NUMBER(add_data.journallineglid) AS trxcontabilidadid,
add_data.period_year AS period_year,
add_data.period_number AS period_number,
add_data.jrnllineeffectivedate AS gl_date,
add_data.transaction_date AS transaction_date,
add_data.transaction_number AS transaction_number,
add_data.jrnllineaccounteddr AS accounted_dr,
add_data.jrnllineaccountedcr AS accounted_cr,
add_data.jrnllinecurrencycode AS entered_currency,
add_data.source_name AS je_source_name,
add_data.jrnlhdrjecategory AS je_category_name,
add_data.segment2 AS segment2,
add_data.segment3 AS segment3,
add_data.segment4 AS segment4,
add_data.segment5 AS segment5,
add_data.segment6 AS segment6,
add_data.segment7 AS segment7,
add_data.segment8 AS segment8,
add_data.segment9 AS segment9,
add_data.segment10 AS segment10,
add_data.jrnllinecreatedby AS created_by,
add_data.jrnlhdrdescription AS line_description,
add_data.event_class_code AS event_class_code,
CAST(NULL AS NUMBER) AS xlaheaderid,
CAST(NULL AS NUMBER) AS xlalinenum,
add_data.jeheaderid AS glaeheaderid,
add_data.jelinenum AS glaelinenum,
add_data.journalbatchpostingstatuscode AS posting_status_code,
nvl(add_data.LCLS_C_THRTPRTY_N,'0000') AS tercero,
add_data.fiscal_code AS fiscal_code_tercero,
add_data.jeheaderid AS pkheaderid,
add_data.jelinenum AS pklinenum,
add_data.idorigen AS pkorigen,
CAST(NULL AS VARCHAR2(4000)) AS po_header_comments,
CAST(NULL AS VARCHAR2(200)) AS po_number,
add_data.is_modified AS is_modified
FROM add_data
WHERE add_data.source_name IN (
'Spreadsheet',
'AutoCopy',
'Manual',
'Interfase Nomina',
'Revaluation'
)
AND (
NVL(add_data.jrnllineaccounteddr,0) <> 0
OR NVL(add_data.jrnllineaccountedcr,0) <> 0
)
/*AND NOT (
add_data.source_name = 'Manual'
AND add_data.journalbatchpostingstatuscode IN ('M')
)*/;