Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas simples |
DWADW.DW_TRXCONTABILIDAD_GL
Base de datos origen: ADW COEXITO
Tipo de objeto: VIEW
SQL
CREATE OR REPLACE FORCE EDITIONABLE VIEW "DWADW"."DW_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" 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
FROM dwadw.fact_flex_gl f
),
join_4 AS (
SELECT
st.*,
fx.lcls_c_thrtprty_n,
fx.party_id,
fx.fiscal_code
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_customer AS (
SELECT DISTINCT
c.party_id,
c.party_name,
c.fiscal_code
FROM dwadw.dim_customer_ar c
),
join_customer AS (
SELECT
x.*,
c.party_id AS customer_party_id,
c.party_name AS customer_party_name,
c.fiscal_code AS customer_fiscal_code
FROM dwadw.fact_journaleline_gl x
LEFT JOIN distinct_customer c
ON x.custacctpartyid = c.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
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.customer_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.customer_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.customer_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
),
dist_clean AS (
SELECT *
FROM (
SELECT
d.*,
ROW_NUMBER() OVER (
PARTITION BY d.fact_journaleline_gl_id
ORDER BY
d.fact_journallinegl_gl_id,
d.jeheaderid,
d.jelinenum
) AS rn
FROM dwadw.fact_journalentrydist_gl d
WHERE d.row_active = 'A'
AND d.fact_journaleline_gl_id IS NOT NULL
)
WHERE rn = 1
),
join_dist AS (
SELECT
jgl.*,
dist.jeheaderid,
dist.jelinenum,
dist.fact_journallinegl_gl_id,
dist.is_modified
FROM join_gl jgl
LEFT JOIN dist_clean dist
ON jgl.journaleline_gl_id = dist.fact_journaleline_gl_id
),
join_jjournallinegl AS (
SELECT
jd.*,
jgl.journalbatchpostingstatuscode AS posting_status_code
FROM join_dist jd
LEFT JOIN dwadw.fact_journallinegl_gl jgl
ON jd.fact_journallinegl_gl_id = jgl.journallinegl_gl_id
)
/* ============================================================
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 = 'Spreadsheet' or add_data.source_name = 'AutoCopy'
or add_data.source_name = 'Manual' or add_data.source_name = 'Interfase Nomina' or add_data.source_name = 'Revaluation'
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 = 'Assets' or j.xlaapplnvllinesjesourcename = 'Receipt Accounting'
or j.xlaapplnvllinesjesourcename = 'Cost Accounting' or j.xlaapplnvllinesjesourcename = 'Receivables'
or j.xlaapplnvllinesjesourcename = 'Payables' or j.xlaapplnvllinesjesourcename = 'Cash Management';