Kannst du es bitte kommentieren und dokumentieren für den Projekt Wiki Eintrag.
Auf english bitte.

-- CTE zur Herleitung von FXO und FXA  - Effekten
WITH fx_base_aggr AS (
        SELECT
            FISCAL_PERIOD as FISCAL_PERIOD,
            sum(IFF(BOOKING_CODE_TYPE = 'BALANCE', TRANSACTION_CURRENCY_AMOUNT, 0))  as BALANCE_TC,
            sum(IFF(BOOKING_CODE_TYPE = 'DELTA', TRANSACTION_CURRENCY_AMOUNT, 0))    as DELTA_TC,
            -- technical fields 
            null as tech_record_tl_id,
            null as tech_record_sl_id,
        max(tech_supply_id) as tech_supply_id,
        max(tech_supply_id_orig) as tech_supply_id_orig,
            concat('FXI_', tech_source_system, '_',  max(FISCAL_PERIOD))as tech_log_id,
        null as tech_run_id,
            tech_source_system                  as tech_source_system,
            current_timestamp()                 as tech_load_dt,
            current_timestamp()                 as tech_change_dt,
            -- date calculations
            LAST_DAY(to_date( max(FISCAL_PERIOD), 'YYYYMM')) as POSTING_DATE,
            LAST_DAY(to_date( max(FISCAL_PERIOD), 'YYYYMM')) as POSTING_DATE_ORIG,
            -- null data fields
            null                                as BILLING_DATE_FROM,
            null                                as BILLING_DATE_TO,
            null                                as ENTRY_TIME_OF_CLAIM,
            null                                as CLAIM_NOTIFICATION_YEAR_MONTH,
            -- ult conversion rates
            gc.CONV_RATE_EUR                     as CONV_RATE_ULT_GC,
            gc.CONV_RATE_EUR / fc.CONV_RATE_EUR  as CONV_RATE_ULT_FC,
            gc.CONV_RATE_EUR / lc.CONV_RATE_EUR  as CONV_RATE_ULT_LC,
            -- pm conversion rates
            gcpm.CONV_RATE_EUR                      as CONV_RATE_PVM_GC,
            gcpm.CONV_RATE_EUR / fcpm.CONV_RATE_EUR as CONV_RATE_PVM_FC,
            gcpm.CONV_RATE_EUR / lcpm.CONV_RATE_EUR as CONV_RATE_PVM_LC,
            -- business fields
            LEGAL_ENTITY,
            SUB_LEGAL_ENTITY, 
            SUB_LEGAL_ENTITY_OLD, 
            NATURE_OF_BUSINESS,
            UNDERWRITING_TYPE,
            UNDERWRITING_YEAR,
            VALUE_TYPE,
            null                                as DUE_DATE,
            null                                as COST_CENTER,
            BOOKING_CODE,
            BOOKING_CODE_CLASS,
            'FXI' as BOOKING_CODE_TYPE,
            TRANSACTION_CURRENCY,
            FUNCTIONAL_CURRENCY,
            LOCAL_CURRENCY,
            RI_FORM,
            RI_NATURE_OF_TREATY,
            RI_TYPE,
            RI_CHAR,
            INTERNAL_KEY,
            SEGMENT_LOB,
            SEGMENT_COB,
            SEGMENT_HGB,
            SEGMENT_ACTUARIAL,
            SEGMENT_CO,
            SEGMENT_GAAP,
            TAX_COUNTRY,
            RISK_COUNTRY,
            RESIDENCE_COUNTRY,
            CONTRACT_COUNTRY,
            PARTNER_PAYMENT,
            PARTNER_COMPANY,
            LEDGER_HGB,
            LEDGER_IFRS17
        FROM    INTEGRATION_LAYER.IL_BASE m
        -- join with currency rates
        LEFT JOIN (SELECT   SOURCE_CURRENCY, CONV_RATE_EUR, FISCAL_PERIOD as CONV_DATE , CURRENCY_FLAG 
                    FROM    MASTER_DATA.V_CURRENCY_RATES ) as gc
        ON      m.TRANSACTION_CURRENCY  = gc.SOURCE_CURRENCY    and
                m.fiscal_period               = gc.CONV_DATE          and
                gc.CURRENCY_FLAG        = 'U'
        LEFT JOIN (SELECT   SOURCE_CURRENCY, CONV_RATE_EUR, FISCAL_PERIOD as CONV_DATE , CURRENCY_FLAG 
                   FROM     MASTER_DATA.V_CURRENCY_RATES ) as fc
        ON      m.FUNCTIONAL_CURRENCY   = fc.SOURCE_CURRENCY 
        and     m.fiscal_period                = fc.CONV_DATE
        and     fc.CURRENCY_FLAG        = 'U'
        LEFT JOIN (SELECT   SOURCE_CURRENCY, CONV_RATE_EUR, FISCAL_PERIOD as CONV_DATE , CURRENCY_FLAG 
                   FROM     MASTER_DATA.V_CURRENCY_RATES ) as lc
        ON      m.LOCAL_CURRENCY        = lc.SOURCE_CURRENCY 
        and     m.fiscal_period                = lc.CONV_DATE
        and     lc.CURRENCY_FLAG        = 'U'
        -- pm
        LEFT JOIN (SELECT   SOURCE_CURRENCY, CONV_RATE_EUR, FISCAL_PERIOD as CONV_DATE , CURRENCY_FLAG 
                    FROM    MASTER_DATA.V_CURRENCY_RATES ) as gcpm
        ON      m.TRANSACTION_CURRENCY  = gcpm.SOURCE_CURRENCY    and
                m.fiscal_period                = gcpm.CONV_DATE          and
                gcpm.CURRENCY_FLAG        = 'P'
    
        LEFT JOIN (SELECT   SOURCE_CURRENCY, CONV_RATE_EUR, FISCAL_PERIOD as CONV_DATE , CURRENCY_FLAG 
                   FROM     MASTER_DATA.V_CURRENCY_RATES ) as fcpm
        ON      m.FUNCTIONAL_CURRENCY   = fcpm.SOURCE_CURRENCY 
        and     m.fiscal_period                = fcpm.CONV_DATE
        and     fcpm.CURRENCY_FLAG      = 'P'
        
        LEFT JOIN (SELECT   SOURCE_CURRENCY, CONV_RATE_EUR,  FISCAL_PERIOD as CONV_DATE , CURRENCY_FLAG 
                   FROM     MASTER_DATA.V_CURRENCY_RATES ) as lcpm
        ON      m.LOCAL_CURRENCY        = lcpm.SOURCE_CURRENCY 
        and     m.fiscal_period                = lcpm.CONV_DATE
        and     lcpm.CURRENCY_FLAG      = 'P'
        
        WHERE   
                BOOKING_CODE_TYPE in ('BALANCE','DELTA') 
               -- and EXTRACT(YEAR FROM TO_DATE('202403', 'YYYYMM')) =  EXTRACT(YEAR FROM TO_DATE(FISCAL_PERIOD, 'YYYYMM'))
                and  (FISCAL_PERIOD in ('202403', '202402'))
              
                and (
                        TRANSACTION_CURRENCY <> 'EUR'
                        or FUNCTIONAL_CURRENCY <> 'EUR'  
                        or LOCAL_CURRENCY <> 'EUR'
                    )
   
                and TECH_SOURCE_SYSTEM = 'GERAS_PREMIUM'      
--and    INTERNAL_KEY = '0126997914010'
-- and TECH_RECORD_IL_ID <> 99334
-- INTERNAL_KEY = '0126906614021'                
--and BOOKING_CODE = 'UP003301'
        group by all
    
     )
     /*
     select *
     from   fx_base_aggr
     ;
     */
     ,      
     -- calculate cumulative amounts
     fx_cumulativ AS (
        SELECT
                nvl(LAG(BALANCE_TC) OVER (
                        PARTITION BY    LEGAL_ENTITY, SUB_LEGAL_ENTITY, NATURE_OF_BUSINESS, UNDERWRITING_YEAR, BILLING_DATE_FROM,
                                        BILLING_DATE_TO, DUE_DATE, BOOKING_CODE, TRANSACTION_CURRENCY, INTERNAL_KEY, SEGMENT_LOB, TAX_COUNTRY, RISK_COUNTRY, CONTRACT_COUNTRY, 
                                        PARTNER_PAYMENT, LEDGER_HGB, LEDGER_IFRS17  
                        ORDER BY FISCAL_PERIOD),0)      as TMP_LAG_TC,
                -- FXo
                0 as FXO_TC,
                (1/CONV_RATE_ULT_FC - 1/CONV_RATE_PVM_FC) as quote_fc,
                (1/CONV_RATE_ULT_LC - 1/CONV_RATE_PVM_LC) as quote_lc,
                TMP_LAG_TC * (1/CONV_RATE_ULT_FC - 1/CONV_RATE_PVM_FC)      as FXO_FC,
                TMP_LAG_TC * (1/CONV_RATE_ULT_LC - 1/CONV_RATE_PVM_LC)      as FXO_LC,
                -- FXa
                0 as FXA_TC,
                DELTA_TC * (1/CONV_RATE_ULT_FC - 1/CONV_RATE_PVM_FC)      as FXA_FC,
                DELTA_TC * (1/CONV_RATE_ULT_LC - 1/CONV_RATE_PVM_LC)      as FXA_LC,
                m.*  
        FROM    fx_base_aggr m
        where   BALANCE_TC <> 0 or
                DELTA_TC <> 0
    ),
    final AS (
    select      'FXO'   as BOOKING_CODE_TYPE,
                0       as TRANSACTION_CURRENCY_AMOUNT,
                FXO_LC  as LOCAL_CURRENCY_AMOUNT,
                FXO_FC  as FUNCTIONAL_CURRENCY_AMOUNT,
                * EXCLUDE (BOOKING_CODE_TYPE)
    from    fx_cumulativ
    where   FISCAL_PERIOD = '202403'
       
    UNION ALL

    select      'FXA'   as BOOKING_CODE_TYPE,
                0       as TRANSACTION_CURRENCY_AMOUNT,
                FXA_LC  as LOCAL_CURRENCY_AMOUNT,
                FXA_FC  as FUNCTIONAL_CURRENCY_AMOUNT,
                * EXCLUDE (BOOKING_CODE_TYPE)
    from    fx_cumulativ
    where   FISCAL_PERIOD = '202403'
    )

    select      *
    from    final
    ;