🏠 Menú principal

← Volver a vistas simples

---

DWADW.VW_JOURNALLINEGL_FLUJO

Base de datos origen: ADW COEXITO

Tipo de objeto: VIEW

SQL

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "DWADW"."VW_JOURNALLINEGL_FLUJO" ("PERIOD_YEAR", "PERIOD_NUMBER", "CREATEDBYPERSONNAMEDISPLAYNAME", "CREATEDBYPERSONNAMEEFFECTIVESTARTDATE", "CREATEDBYPERSONNAMELASTUPDATEDATE", "FUNPOSTACCTSEQVERSIONSHEADERNAME", "JEHEADERID", "JELINENUM", "JOURNALLINEGLID", "JOURNALBATCHCOMPLETIONSTATUSCODE", "JOURNALBATCHPOSTINGSTATUSCODE", "JRNLBATCHCHARTOFACCOUNTSID", "JRNLBATCHDESCRIPTION", "JRNLBATCHJEBATCHID", "JRNLBATCHNAME", "JRNLHDRACCRUALREVPERIODNAME", "JRNLHDRCURRENCYCODE", "JRNLHDRCURRENCYCONVERSIONDATE", "JRNLHDRCURRENCYCONVERSIONRATE", "JRNLHDRDESCRIPTION", "JRNLHDRJECATEGORY", "JRNLHDRJESOURCE", "JRNLHDRLEDGERID", "JRNLLINEACCOUNTEDCR", "JRNLLINEACCOUNTEDDR", "JRNLLINECODECOMBINATIONID", "JRNLLINECREATEDBY", "JRNLLINECREATIONDATE", "JRNLLINECURRENCYCODE", "JRNLLINEDESCRIPTION", "JRNLLINEEFFECTIVEDATE", "JRNLLINEENTEREDCR", "JRNLLINEENTEREDDR", "JRNLLINEGLSLLINKID", "LOCALDOCSEQUENCENAME", "FLEX_CREATION_DATE", "LCLS_C_THRTPRTY_N", "FLEX_LASTUPDATEDATE", "PARTY_ID", "FISCAL_CODE", "CODE_COMBINATION_ID", "ENABLE_FLAG", "GL_DESCRIPTION", "GL_CREATION_DATE", "SEGMENT1", "SEGMENT2", "SEGMENT3", "SEGMENT4", "SEGMENT5", "SEGMENT6", "SEGMENT7", "SEGMENT8", "SEGMENT9", "SEGMENT10", "SOURCE_NAME", "JOURNALSOURCE_DESCRIPTION", "USER_SOURCE_NAME", "TRANSACTION_DATE", "TRANSACTION_NUMBER", "EVENT_CLASS_CODE", "SOURCE_NAME_FINAL", "IDORIGEN") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  WITH src_journallinegl AS (
    SELECT
        TO_CHAR(JRNLLINEEFFECTIVEDATE,'YY') AS PERIOD_YEAR,
        CASE 
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 1 THEN 'Ene'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 2 THEN 'Feb'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 3 THEN 'Mar'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 4 THEN 'Abr'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 5 THEN 'May'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 6 THEN 'Jun'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 7 THEN 'Jul'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 8 THEN 'Ago'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 9 THEN 'Sep'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 10 THEN 'Oct'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 11 THEN 'Nov'
            WHEN EXTRACT(MONTH FROM JRNLLINEEFFECTIVEDATE) = 12 THEN 'Dic'
        END AS PERIOD_NUMBER,
        CREATEDBYPERSONNAMEDISPLAYNAME,
        CREATEDBYPERSONNAMEEFFECTIVESTARTDATE,
        CREATEDBYPERSONNAMELASTUPDATEDATE,
        FUNPOSTACCTSEQVERSIONSHEADERNAME,
        JEHEADERID,
        JELINENUM,
        JOURNALLINEGLID,
        JOURNALBATCHCOMPLETIONSTATUSCODE,
        JOURNALBATCHPOSTINGSTATUSCODE,
        JRNLBATCHCHARTOFACCOUNTSID,
        JRNLBATCHDESCRIPTION,
        JRNLBATCHJEBATCHID,
        JRNLBATCHNAME,
        JRNLHDRACCRUALREVPERIODNAME,
        JRNLHDRCURRENCYCODE,
        JRNLHDRCURRENCYCONVERSIONDATE,
        JRNLHDRCURRENCYCONVERSIONRATE,
        JRNLHDRDESCRIPTION,
        JRNLHDRJECATEGORY,
        JRNLHDRJESOURCE,
        JRNLHDRLEDGERID,
        JRNLLINEACCOUNTEDCR,
        JRNLLINEACCOUNTEDDR,
        JRNLLINECODECOMBINATIONID,
        JRNLLINECREATEDBY,
        JRNLLINECREATIONDATE,
        JRNLLINECURRENCYCODE,
        JRNLLINEDESCRIPTION,
        JRNLLINEEFFECTIVEDATE,
        JRNLLINEENTEREDCR,
        JRNLLINEENTEREDDR,
        JRNLLINEGLSLLINKID,
        LOCALDOCSEQUENCENAME
    FROM STADW.ST_JOURNALLINEGL
),
distinct_2 AS (
    SELECT DISTINCT
        CREATION_DATE,
        LCLS_C_THRTPRTY_N,
        LASTUPDATEDATE,
        JE_HEADER_ID,
        JELINENUM,
        PARTY_ID,
        FISCAL_CODE
    FROM DWADW.DW_FLEX_GL
),
join_4 AS (
    SELECT
        s.*,
        f.CREATION_DATE  AS FLEX_CREATION_DATE,
        f.LCLS_C_THRTPRTY_N,
        f.LASTUPDATEDATE AS FLEX_LASTUPDATEDATE,
        f.PARTY_ID,
        f.FISCAL_CODE
    FROM src_journallinegl s
    LEFT JOIN distinct_2 f
        ON s.JEHEADERID = f.JE_HEADER_ID
       AND s.JELINENUM  = f.JELINENUM
),
join_1 AS (
    SELECT
        j4.*,
        gcc.CODE_COMBINATION_ID,
        gcc.ENABLE_FLAG,
        gcc.DESCRIPTION   AS GL_DESCRIPTION,
        gcc.CREATION_DATE AS GL_CREATION_DATE,
        gcc.SEGMENT1,
        gcc.SEGMENT2,
        gcc.SEGMENT3,
        gcc.SEGMENT4,
        gcc.SEGMENT5,
        gcc.SEGMENT6,
        gcc.SEGMENT7,
        gcc.SEGMENT8,
        gcc.SEGMENT9,
        gcc.SEGMENT10
    FROM join_4 j4
    INNER JOIN DWADW.DW_GLCCOMBINATION gcc
        ON j4.JRNLLINECODECOMBINATIONID = gcc.CODE_COMBINATION_ID
),
join_2 AS (
    SELECT
        j1.*,
        js.SOURCE_NAME,
        js.DESCRIPTION AS JOURNALSOURCE_DESCRIPTION,
        js.USER_SOURCE_NAME
    FROM join_1 j1
    LEFT JOIN DWADW.DW_JOURNALSOURCE js
        ON j1.JRNLHDRJESOURCE = js.SOURCE_NAME
),
add_data AS (
    SELECT
        j2.*,
        CAST(NULL AS DATE) AS TRANSACTION_DATE,
        CAST(NULL AS VARCHAR2(2000)) AS TRANSACTION_NUMBER,
        CAST(NULL AS VARCHAR2(2000)) AS EVENT_CLASS_CODE,
        CASE
            WHEN j2.SOURCE_NAME IN ('300000006344046', '300000006344047')
            THEN j2.USER_SOURCE_NAME
            ELSE j2.SOURCE_NAME
        END AS SOURCE_NAME_FINAL,
        'GL' AS IDORIGEN
    FROM join_2 j2
)
SELECT "PERIOD_YEAR","PERIOD_NUMBER","CREATEDBYPERSONNAMEDISPLAYNAME","CREATEDBYPERSONNAMEEFFECTIVESTARTDATE","CREATEDBYPERSONNAMELASTUPDATEDATE","FUNPOSTACCTSEQVERSIONSHEADERNAME","JEHEADERID","JELINENUM","JOURNALLINEGLID","JOURNALBATCHCOMPLETIONSTATUSCODE","JOURNALBATCHPOSTINGSTATUSCODE","JRNLBATCHCHARTOFACCOUNTSID","JRNLBATCHDESCRIPTION","JRNLBATCHJEBATCHID","JRNLBATCHNAME","JRNLHDRACCRUALREVPERIODNAME","JRNLHDRCURRENCYCODE","JRNLHDRCURRENCYCONVERSIONDATE","JRNLHDRCURRENCYCONVERSIONRATE","JRNLHDRDESCRIPTION","JRNLHDRJECATEGORY","JRNLHDRJESOURCE","JRNLHDRLEDGERID","JRNLLINEACCOUNTEDCR","JRNLLINEACCOUNTEDDR","JRNLLINECODECOMBINATIONID","JRNLLINECREATEDBY","JRNLLINECREATIONDATE","JRNLLINECURRENCYCODE","JRNLLINEDESCRIPTION","JRNLLINEEFFECTIVEDATE","JRNLLINEENTEREDCR","JRNLLINEENTEREDDR","JRNLLINEGLSLLINKID","LOCALDOCSEQUENCENAME","FLEX_CREATION_DATE","LCLS_C_THRTPRTY_N","FLEX_LASTUPDATEDATE","PARTY_ID","FISCAL_CODE","CODE_COMBINATION_ID","ENABLE_FLAG","GL_DESCRIPTION","GL_CREATION_DATE","SEGMENT1","SEGMENT2","SEGMENT3","SEGMENT4","SEGMENT5","SEGMENT6","SEGMENT7","SEGMENT8","SEGMENT9","SEGMENT10","SOURCE_NAME","JOURNALSOURCE_DESCRIPTION","USER_SOURCE_NAME","TRANSACTION_DATE","TRANSACTION_NUMBER","EVENT_CLASS_CODE","SOURCE_NAME_FINAL","IDORIGEN"
FROM add_data;

---

← Volver al esquema DWADW

🏠 Volver al menú principal