Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
DWADW.DW_MV_TRXCONTABILIDAD_GL
Base de datos origen: ADW COEXITO
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "DWADW"."DW_MV_TRXCONTABILIDAD_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
),
/* ============================================================
BLOQUE XLA
============================================================ */
distinct_1 AS (
SELECT DISTINCT
f.fiscal_code,
f.party_id,
f.lcls_c_thrtprty_n AS party_name,
f.is_modified AS is_modified
FROM dwadw.fact_flex_gl f
WHERE f.party_id IS NOT NULL
),
join_customer AS (
SELECT
x.*,
flex.party_id AS flex_party_id,
flex.party_name AS flex_party_name,
flex.fiscal_code AS flex_fiscal_code,
flex.is_modified AS is_modified
FROM dwadw.fact_journaleline_gl x
LEFT JOIN distinct_1 flex
ON x.custacctpartyid = flex.party_id
),
distinct_6 AS (
SELECT DISTINCT
cash.receiptid,
cus.party_id,
cus.party_name,
cus.fiscal_code
FROM dwadw.fact_cashreceipt_gl cash
LEFT JOIN dwadw.dim_customer_ar cus
ON cus.customer_ar_id = cash.dim_customer_ar_id
),
join_cashreceipt AS (
SELECT
jc.*,
cash.party_id AS cash_party_id,
cash.party_name AS cash_party_name,
cash.fiscal_code AS cash_fiscal_code
FROM join_customer jc
LEFT JOIN distinct_6 cash
ON jc.cashreceiptid = cash.receiptid
),
distinct_5 AS (
SELECT DISTINCT
rec.rec_event_id,
rec.rec_accounting_eventid,
sup.party_id,
sup.party_name,
sup.fiscal_code,
--rec.po_header_comments,
--rec.po_number
NULLIF(TRIM(rec.po_header_comments), 'nan') AS po_header_comments,
NULLIF(TRIM(rec.po_number), 'nan') AS po_number
FROM dwadw.fact_receiptaccounting_gl rec
LEFT JOIN dwadw.dim_supplier_ap sup
ON sup.supplier_ap_id = rec.dim_supplier_ap_id
),
join_rec_acc AS (
SELECT
jcash.*,
rec.party_id AS receipt_party_id,
rec.party_name AS receipt_party_name,
rec.fiscal_code AS receipt_fiscal_code,
rec.po_header_comments AS po_header_comments,
rec.po_number AS po_number
FROM join_cashreceipt jcash
LEFT JOIN distinct_5 rec
ON jcash.cmraccountingeventid = rec.rec_accounting_eventid
AND jcash.xlahdreventid = rec.rec_event_id
),
distinct_2 AS (
SELECT DISTINCT
ap.invoiceid,
ap.invoice_num,
ap.vendorid,
sup.party_id,
sup.party_name,
sup.fiscal_code
FROM dwadw.fact_trxheader_ap ap
LEFT JOIN dwadw.dim_supplier_ap sup
ON sup.supplier_ap_id = ap.dim_supplier_ap_id
),
join_apcustomer AS (
SELECT
jrec.*,
ap.party_id AS ap_party_id,
ap.party_name AS ap_party_name,
ap.fiscal_code AS ap_fiscal_code
FROM join_rec_acc jrec
LEFT JOIN distinct_2 ap
ON jrec.apinvoiceid = ap.invoiceid
),
distinct_3 AS (
SELECT DISTINCT
pay.checkid,
pay.vendorid,
sup.party_id AS partyid,
sup.party_name,
sup.fiscal_code
FROM dwadw.fact_payment_ap pay
LEFT JOIN dwadw.dim_supplier_ap sup
ON sup.supplier_ap_id = pay.dim_supplier_ap_id
),
join_paymentcustomer AS (
SELECT
jap.*,
pay.partyid AS payment_party_id,
pay.party_name AS payment_party_name,
pay.fiscal_code AS payment_fiscal_code
FROM join_apcustomer jap
LEFT JOIN distinct_3 pay
ON jap.apcheckid = pay.checkid
),
distinct_4 AS (
SELECT DISTINCT
po.segment1,
sup.party_id,
sup.party_name,
sup.fiscal_code
FROM dwadw.fact_trxheader_po po
LEFT JOIN dwadw.dim_supplier_ap sup
ON sup.supplier_ap_id = po.dim_supplier_ap_id
),
join_pocustomer AS (
SELECT
jpay.*,
po.party_id AS po_party_id,
po.party_name AS po_party_name,
po.fiscal_code AS po_fiscal_code
FROM join_paymentcustomer jpay
LEFT JOIN distinct_4 po
ON jpay.transactionentitytransactionnumber = po.segment1
),
idtabla AS (
SELECT
jp.*,
SUBSTR(jp.xlahdrperiodname, 5, 2) AS periodyear,
SUBSTR(jp.xlahdrperiodname, 1, 3) AS periodnumber,
CASE
WHEN LENGTH(jp.aelinenum) >= 15
THEN jp.xlalinesdisplayedlinenumber
ELSE jp.aelinenum
END AS aelinenumjoin,
COALESCE(
jp.flex_party_name,
jp.cash_party_name,
jp.ap_party_name,
jp.payment_party_name,
jp.po_party_name,
jp.receipt_party_name
) AS tercero,
COALESCE(
jp.flex_fiscal_code,
jp.cash_fiscal_code,
jp.ap_fiscal_code,
jp.payment_fiscal_code,
jp.po_fiscal_code,
jp.receipt_fiscal_code
) AS fiscal_code_tercero,
COALESCE(
jp.flex_party_id,
jp.cash_party_id,
jp.ap_party_id,
jp.payment_party_id,
jp.po_party_id,
jp.receipt_party_id
) AS party_id_tercero,
'XLA' AS idorigen
FROM join_pocustomer jp
),
expression_2 AS (
SELECT
gl.code_combination_id,
gl.segment2,
gl.segment3,
gl.segment4,
gl.segment5,
gl.segment6,
gl.segment7,
gl.segment8,
gl.segment9,
gl.segment10
FROM dwadw.dim_codecombination_gl gl
),
join_gl AS (
SELECT
idt.*,
gl.segment2,
gl.segment3,
gl.segment4,
gl.segment5,
gl.segment6,
gl.segment7,
gl.segment8,
gl.segment9,
gl.segment10
FROM idtabla idt
LEFT JOIN expression_2 gl
ON idt.xlalinescodecombinationid = gl.code_combination_id
),
join_dist AS (
SELECT
jgl.*,
dist.jeheaderid,
dist.jelinenum
FROM join_gl jgl
LEFT JOIN dwadw.fact_journalentrydistri_gl dist
ON jgl.aeheaderid = dist.aeheaderid
AND jgl.aelinenumjoin = dist.aelinenum
),
join_jjournallinegl AS (
SELECT
jd.*,
jgl.journalbatchpostingstatuscode AS posting_status_code
FROM join_dist jd
LEFT JOIN dwadw.fact_journallinegl_gl jgl
ON jd.jeheaderid = jgl.jeheaderid
AND jd.jelinenum = jgl.jelinenum
)
/* ============================================================
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,
add_data.lcls_c_thrtprty_n 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,
CAST(NULL AS VARCHAR2(1)) 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
)
UNION ALL
/* ============================================================
SELECT XLA
============================================================ */
SELECT
TO_NUMBER(j.journalelineid) AS trxcontabilidadid,
j.periodyear AS period_year,
j.periodnumber AS period_number,
j.xlalinesaccountingdate AS gl_date,
j.xlahdrcreationdate AS transaction_date,
j.transactionentitytransactionnumber AS transaction_number,
j.xlalinesaccounteddr AS accounted_dr,
j.xlalinesaccountedcr AS accounted_cr,
j.xlalinescurrencycode AS entered_currency,
j.xlaapplnvllinesjesourcename AS je_source_name,
j.xlahdrjecategoryname AS je_category_name,
j.segment2 AS segment2,
j.segment3 AS segment3,
j.segment4 AS segment4,
j.segment5 AS segment5,
j.segment6 AS segment6,
j.segment7 AS segment7,
j.segment8 AS segment8,
j.segment9 AS segment9,
j.segment10 AS segment10,
j.xlahdrcreatedby AS created_by,
j.xlalinesdescription AS line_description,
j.evnttypexlahdrvleventclasscode AS event_class_code,
j.aeheaderid AS xlaheaderid,
j.aelinenum AS xlalinenum,
j.jeheaderid AS glaeheaderid,
j.jelinenum AS glaelinenum,
j.posting_status_code AS posting_status_code,
j.tercero AS tercero,
j.fiscal_code_tercero AS fiscal_code_tercero,
j.aeheaderid AS pkheaderid,
j.aelinenum AS pklinenum,
j.idorigen AS pkorigen,
j.po_header_comments AS po_header_comments,
j.po_number AS po_number,
j.is_modified AS is_modified
FROM join_jjournallinegl j
WHERE j.xlaapplnvllinesjesourcename IN (
'Assets',
'Receipt Accounting',
'Cost Accounting',
'Receivables',
'Payables',
'Cash Management'
)AND (
NVL(j.xlalinesaccounteddr,0) <> 0
OR NVL(j.xlalinesaccountedcr,0) <> 0
);