SHARE
TWEET

Untitled

a guest Sep 20th, 2019 187 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER TABLE imp_calculated_commission NOCHECK CONSTRAINT ALL
  2. GO
  3. INSERT INTO imp_calculated_commission
  4.             (
  5.                         year_month_of_calculation,
  6.                         policy_no,
  7.                         policy_start_date,
  8.                         sum_insured_eur,
  9.                         lob,
  10.                         agent_id,
  11.                         year_month_of_commission,
  12.                         collection_amount,
  13.                         collection_date,
  14.                         collection_currency_id,
  15.                         commission_amount,
  16.                         commission_currency_id,
  17.                         exchange_rate,
  18.                         commission_scheme_detail_id,
  19.                         commission_scheme_detail_history_date,
  20.                         user_commission_scheme_id,
  21.                         user_commission_scheme_history_date,
  22.                         commission_exception_id,
  23.                         commission_exception_history_date,
  24.                         split_commission_id,
  25.                         split_commission_history_date,
  26.                         agent_transfer_history_id,
  27.                         is_new_business,
  28.                         is_preliminary,
  29.                         is_pi_agent,
  30.                         is_foreign_partner,
  31.                         commission_rate,
  32.                         calculation_internal_details,
  33.                         confirmed_user_id,
  34.                         confirmed_datetime,
  35.                         calculation_user_id,
  36.                         calculation_date_time,
  37.                         commission_vias_nsp_payed_premium_import_id,
  38.                         commission_cashflow_adipolicies_payed_premium_closing_id,
  39.                         adi_policy_id,
  40.                         external_document_id,
  41.                         calculated_commission_parent_id,
  42.                         is_cancellation
  43.             )
  44. SELECT      201904              AS YEAR_MONTH_OF_CALCULATION,
  45.             g.policy_number     AS POLICY_NO,
  46.             g.policy_begin_date AS POLICY_START_DATE,
  47.             g.sum_insured       AS SUM_INSURED_EUR,
  48.             zv.alt_sifra        AS LOB,
  49.             calc.agent_id,
  50.             g.accounting_year_month AS YEAR_MONTH_OF_COMMISSION,
  51.             g.collected_premium     AS COLLECTION_AMOUNT,
  52.             g.collection_date,
  53.             collVal.valuta_id                                                                                                              AS COLLECTION_CURRENCY_ID,
  54.             calc.rate * g.collected_premium                                                                                                AS COMMISSION_AMOUNT,
  55.             commVal.valuta_id                                                                                                              AS COMMISSION_CURRENCY_ID,
  56.             dbo.Fn_currencyconverter(g.collected_premium, collVal.valuta_id, commVal.valuta_id, '2019-04-30') / g.collected_premium AS EXCHANGE_RATE,
  57.             cDetail.commission_scheme_detail_id,
  58.             cDetail.commission_scheme_detail_history_date,
  59.             cUsrDetail.user_commission_scheme_id,
  60.             cUsrDetail.user_commission_scheme_history_date,
  61.             exe.commission_exception_id,
  62.             exe.commission_exception_history_date,
  63.             split.split_commission_id,
  64.             split.split_commission_history_date,
  65.             calc.agent_transfer_history_id,
  66.             g.is_new_business,
  67.             COALESCE(0, 0)                  AS IS_PRELIMINARY,
  68.             COALESCE(commUsr.is_pi_agent, 0)       AS IS_PI_AGENT,
  69.             COALESCE(commUsr.is_foreign_partner,0) AS IS_FOREIGN_PARTNER,
  70.             calc.rate                              AS COMMISSION_RATE,
  71.             calc.calculation_internal_details,
  72.             NULL                AS CONFIRMED_USER_ID,
  73.             NULL                AS CONFIRMED_DATETIME,
  74.             0                   AS CALCULATION_USER_ID,
  75.             GETDATE()           AS CALCULATION_DATE_TIME,
  76.             NULL                AS COMMISSION_VIAS_NSP_PAYED_PREMIUM_IMPORT_ID,
  77.             g.zapiranje_id      AS COMMISSION_CASHFLOW_ADIPOLICIES_PAYED_PREMIUM_CLOSING_ID,
  78.             g.policy_number     AS ADI_POLICY_ID,
  79.             NULL                AS EXTERNAL_DOCUMENT_ID,
  80.             NULL                AS CALCULATED_COMMISSION_PARENT_ID,
  81.             0                   AS IS_CANCELLATION
  82. FROM        imp_gcommission_cashflow_adipolicies_directpayments_and_depositsfromagents g
  83. JOIN        sf_zav_vrsta zv
  84. ON          zv.zav_vrsta_id = g.insurance_class_id
  85. JOIN        sf_valuta commVal
  86. ON          commVal.valuta_ozn = g.transaction_currency
  87. JOIN        sf_valuta collVal
  88. ON          collVal.valuta_ozn = g.agreed_upon_collection_currency
  89. 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
  90. LEFT JOIN
  91.             (
  92.                      SELECT   split_commission_id,
  93.                               Max(split_commission_history_date) AS SPLIT_COMMISSION_HISTORY_DATE
  94.                      FROM     imp_split_commission_history
  95.                      GROUP BY split_commission_id) split
  96. ON          calc.split_commission_id = split.split_commission_id
  97. LEFT JOIN
  98.             (
  99.                      SELECT   commission_exception_id,
  100.                               Max(commission_exception_history_date) AS COMMISSION_EXCEPTION_HISTORY_DATE
  101.                      FROM     imp_commission_exception_history
  102.                      GROUP BY commission_exception_id) exe
  103. ON          calc.commission_exception_id = exe.commission_exception_id
  104. LEFT JOIN
  105.             (
  106.                      SELECT   commission_scheme_detail_id,
  107.                               Max(commission_scheme_detail_history_date) AS COMMISSION_SCHEME_DETAIL_HISTORY_DATE
  108.                      FROM     imp_commission_scheme_detail_history
  109.                      GROUP BY commission_scheme_detail_id) cDetail
  110. ON          calc.commission_scheme_detail_id = cDetail.commission_scheme_detail_id
  111. LEFT JOIN
  112.             (
  113.                      SELECT   user_commission_scheme_id,
  114.                               Max(user_commission_scheme_history_date) AS USER_COMMISSION_SCHEME_HISTORY_DATE
  115.                      FROM     imp_user_commission_scheme_history
  116.                      GROUP BY user_commission_scheme_id) cUsrDetail
  117. ON          calc.user_commission_scheme_id = cUsrDetail.user_commission_scheme_id
  118. LEFT JOIN   imp_user_commission commUsr
  119. ON          commUsr.user_id = g.agent_id
  120. WHERE       g.accounting_year_month BETWEEN 201904 AND         201904
  121. AND         (
  122.                         NULL IS NULL
  123.             OR          zv.alt_sifra = NULL)
  124. AND         (
  125.                         NULL IS NULL
  126.             OR          g.policy_number = NULL)
  127. AND         (
  128.                         NULL IS NULL
  129.             OR          g.agent_id = NULL)
  130. AND         (
  131.                         0 IS NULL
  132.             OR          0 <> 1
  133.             OR          commUsr.is_calc_first_day_in_the_month = 0)
  134. GO
  135. ALTER TABLE imp_calculated_commission WITH CHECK check CONSTRAINT ALL
  136. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top