ALTER TABLE imp_calculated_commission NOCHECK CONSTRAINT ALL
GO
INSERT INTO imp_calculated_commission
            (
                        year_month_of_calculation,
                        policy_no,
                        policy_start_date,
                        sum_insured_eur,
                        lob,
                        agent_id,
                        year_month_of_commission,
                        collection_amount,
                        collection_date,
                        collection_currency_id,
                        commission_amount,
                        commission_currency_id,
                        exchange_rate,
                        commission_scheme_detail_id,
                        commission_scheme_detail_history_date,
                        user_commission_scheme_id,
                        user_commission_scheme_history_date,
                        commission_exception_id,
                        commission_exception_history_date,
                        split_commission_id,
                        split_commission_history_date,
                        agent_transfer_history_id,
                        is_new_business,
                        is_preliminary,
                        is_pi_agent,
                        is_foreign_partner,
                        commission_rate,
                        calculation_internal_details,
                        confirmed_user_id,
                        confirmed_datetime,
                        calculation_user_id,
                        calculation_date_time,
                        commission_vias_nsp_payed_premium_import_id,
                        commission_cashflow_adipolicies_payed_premium_closing_id,
                        adi_policy_id,
                        external_document_id,
                        calculated_commission_parent_id,
                        is_cancellation
            )
SELECT      201904              AS YEAR_MONTH_OF_CALCULATION,
            g.policy_number     AS POLICY_NO,
            g.policy_begin_date AS POLICY_START_DATE,
            g.sum_insured       AS SUM_INSURED_EUR,
            zv.alt_sifra        AS LOB,
            calc.agent_id,
            g.accounting_year_month AS YEAR_MONTH_OF_COMMISSION,
            g.collected_premium     AS COLLECTION_AMOUNT,
            g.collection_date,
            collVal.valuta_id                                                                                                              AS COLLECTION_CURRENCY_ID,
            calc.rate * g.collected_premium                                                                                                AS COMMISSION_AMOUNT,
            commVal.valuta_id                                                                                                              AS COMMISSION_CURRENCY_ID,
            dbo.Fn_currencyconverter(g.collected_premium, collVal.valuta_id, commVal.valuta_id, '2019-04-30') / g.collected_premium AS EXCHANGE_RATE,
            cDetail.commission_scheme_detail_id,
            cDetail.commission_scheme_detail_history_date,
            cUsrDetail.user_commission_scheme_id,
            cUsrDetail.user_commission_scheme_history_date,
            exe.commission_exception_id,
            exe.commission_exception_history_date,
            split.split_commission_id,
            split.split_commission_history_date,
            calc.agent_transfer_history_id,
            g.is_new_business,
            COALESCE(0, 0)                  AS IS_PRELIMINARY,
            COALESCE(commUsr.is_pi_agent, 0)       AS IS_PI_AGENT,
            COALESCE(commUsr.is_foreign_partner,0) AS IS_FOREIGN_PARTNER,
            calc.rate                              AS COMMISSION_RATE,
            calc.calculation_internal_details,
            NULL                AS CONFIRMED_USER_ID,
            NULL                AS CONFIRMED_DATETIME,
            0                   AS CALCULATION_USER_ID,
            GETDATE()			AS CALCULATION_DATE_TIME,
            NULL                AS COMMISSION_VIAS_NSP_PAYED_PREMIUM_IMPORT_ID,
            g.zapiranje_id      AS COMMISSION_CASHFLOW_ADIPOLICIES_PAYED_PREMIUM_CLOSING_ID,
            g.policy_number     AS ADI_POLICY_ID,
            NULL                AS EXTERNAL_DOCUMENT_ID,
            NULL                AS CALCULATED_COMMISSION_PARENT_ID,
            0                   AS IS_CANCELLATION
FROM        imp_gcommission_cashflow_adipolicies_directpayments_and_depositsfromagents g
JOIN        sf_zav_vrsta zv
ON          zv.zav_vrsta_id = g.insurance_class_id
JOIN        sf_valuta commVal
ON          commVal.valuta_ozn = g.transaction_currency
JOIN        sf_valuta collVal
ON          collVal.valuta_ozn = g.agreed_upon_collection_currency
OUTER apply Fn_commission_getcommissionratedetail_table(g.policy_number, zv.alt_sifra, g.sum_insured, g.collection_date, g.is_new_business, g.agent_id) calc
LEFT JOIN
            (
                     SELECT   split_commission_id,
                              Max(split_commission_history_date) AS SPLIT_COMMISSION_HISTORY_DATE
                     FROM     imp_split_commission_history
                     GROUP BY split_commission_id) split
ON          calc.split_commission_id = split.split_commission_id
LEFT JOIN
            (
                     SELECT   commission_exception_id,
                              Max(commission_exception_history_date) AS COMMISSION_EXCEPTION_HISTORY_DATE
                     FROM     imp_commission_exception_history
                     GROUP BY commission_exception_id) exe
ON          calc.commission_exception_id = exe.commission_exception_id
LEFT JOIN
            (
                     SELECT   commission_scheme_detail_id,
                              Max(commission_scheme_detail_history_date) AS COMMISSION_SCHEME_DETAIL_HISTORY_DATE
                     FROM     imp_commission_scheme_detail_history
                     GROUP BY commission_scheme_detail_id) cDetail
ON          calc.commission_scheme_detail_id = cDetail.commission_scheme_detail_id
LEFT JOIN
            (
                     SELECT   user_commission_scheme_id,
                              Max(user_commission_scheme_history_date) AS USER_COMMISSION_SCHEME_HISTORY_DATE
                     FROM     imp_user_commission_scheme_history
                     GROUP BY user_commission_scheme_id) cUsrDetail
ON          calc.user_commission_scheme_id = cUsrDetail.user_commission_scheme_id
LEFT JOIN   imp_user_commission commUsr
ON          commUsr.user_id = g.agent_id
WHERE       g.accounting_year_month BETWEEN 201904 AND         201904
AND         (
                        NULL IS NULL
            OR          zv.alt_sifra = NULL)
AND         (
                        NULL IS NULL
            OR          g.policy_number = NULL)
AND         (
                        NULL IS NULL
            OR          g.agent_id = NULL)
AND         (
                        0 IS NULL
            OR          0 <> 1
            OR          commUsr.is_calc_first_day_in_the_month = 0)
GO
ALTER TABLE imp_calculated_commission WITH CHECK check CONSTRAINT ALL
GO