Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
DWADW.DW_MV_TRXCONTABILIDAD_XLA_GL
Base de datos origen: ADW COEXITO
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "DWADW"."DW_MV_TRXCONTABILIDAD_XLA_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 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
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
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
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
INNER JOIN expression_2 gl
ON idt.xlalinescodecombinationid = gl.code_combination_id
),
join_dist AS (
SELECT
jgl.*,
dist.jeheaderid,
dist.jelinenum
FROM join_gl jgl
INNER 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--,
--jgl.jeheaderid ,
--jgl.jelinenum
FROM join_dist jd
left JOIN dwadw.fact_journallinegl_gl jgl
ON jd.jeheaderid = jgl.jeheaderid
AND jd.jelinenum = jgl.jelinenum
)*/
join_xla_gl_fallback AS (
SELECT
j.*,
gl.glaeheaderid AS gl_fallback_jeheaderid,
gl.glaelinenum AS gl_fallback_jelinenum,
gl.posting_status_code AS posting_status_code,
ROW_NUMBER() OVER (
PARTITION BY j.aeheaderid, j.aelinenum
ORDER BY gl.gl_date, gl.glaeheaderid, gl.glaelinenum
) AS rn_fallback
FROM join_dist j
LEFT JOIN DWADW.DW_MV_TRXCONTABILIDAD_GL_GL gl
ON j.jeheaderid IS NULL
AND gl.segment3 = j.segment3
AND NVL(gl.accounted_dr,0) = NVL(j.xlalinesaccounteddr,0)
AND NVL(gl.accounted_cr,0) = NVL(j.xlalinesaccountedcr,0)
AND gl.gl_date = j.xlalinesaccountingdate
)
/* ============================================================
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,
NVL(j.jeheaderid, j.gl_fallback_jeheaderid) AS glaeheaderid,
NVL(j.jelinenum, j.gl_fallback_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_xla_gl_fallback 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
)
AND j.rn_fallback = 1;