CREATE MATERIALIZED VIEW REPORT_AMORT_AMOUNT_MV
USING NO INDEX
REFRESH FORCE ON DEMAND
AS
SELECT aa.company_code as company_code,
p.code AS plan_code,
p.plan_short_name,
aa.award_code as award_code,
a.award_reason,
pa.executive_id,
pa.participant_id,
aa.participant_uid as participant_uid,
pa.last_name,
pa.first_name,
g.code AS group_code,
aa.tranche_id AS tranche_id,
award_value.currency_iso_code AS award_currency,
nvl(award_value.award_value, 0) AS original_award_value,
nvl(award_value.award_value_nic, 0) AS original_award_value_nic
--Dates
      ,
       aa.effective_date as effective_date,
       aa.booking_date   as booking_date
       -- Base Rates
      ,
       sum(nvl(base.amortization_rate, 0)) AS base_rate,
       sum(nvl(base.ited_base_percentage, 0)) as ited_base_percentage
       -- Base Amounts
      ,
       case
         when round(sum(nvl(base.itsd_amount, 0)) -
                    sum(nvl(base.itsd_dist_amt, 0)),
                    4) <> 0 then
          sum(nvl(base.itsd_amount, 0)) - sum(nvl(base.itsd_dist_amt, 0))
         else
          0
       end as itsd_base_value -- changed
      ,
       sum(nvl(base.amount_amortization, 0)) AS base_amount,
       sum(nvl(base.accelerated_amount, 0)) AS base_positive_corr,
       sum(nvl(base.reversal_amount, 0)) AS base_negative_corr,
       case
         when round(sum(nvl(base.dist_amt, 0)), 4) <> 0 then
          sum(nvl(base.dist_amt, 0))
         else
          0
       end AS base_dist_amount,
       case
         when round(sum(nvl(base.cum_amount_amortization, 0)) -
                    sum(nvl(base.cum_dist_amt, 0)),
                    4) <> 0 then
          sum(nvl(base.cum_amount_amortization, 0)) -
          sum(nvl(base.cum_dist_amt, 0))
         else
          0
       end AS cum_base_amount -- changed
       -- Base Amount_NIC
      ,
       case
         when round(sum(nvl(base.itsd_amount_nic, 0)) -
                    sum(nvl(base.itsd_dist_amt_nic, 0)),
                    4) <> 0 then
          sum(nvl(base.itsd_amount_nic, 0)) -
          sum(nvl(base.itsd_dist_amt_nic, 0))
         else
          0
       end as itsd_base_value_nic -- Changed
      ,
       sum(nvl(base.amount_amortization_nic, 0)) AS base_amount_nic,
       sum(nvl(base.accelerated_amount_nic, 0)) AS base_positive_corr_nic,
       sum(nvl(base.reversal_amount_nic, 0)) AS base_negative_corr_nic,
       case
         when round(sum(nvl(base.dist_amt_nic, 0)), 4) <> 0 then
          sum(nvl(base.dist_amt_nic, 0))
         else
          0
       end AS base_dist_amount_nic,
       case
         when round(sum(nvl(base.cum_amount_amortization_nic, 0)) -
                    sum(nvl(base.cum_dist_amt_nic, 0)),
                    4) <> 0 then
          sum(nvl(base.cum_amount_amortization_nic, 0)) -
          sum(nvl(base.cum_dist_amt_nic, 0))
         else
          0
       end AS cum_base_amount_nic -- changed
       -- GainLoss Rates
      ,
       sum(nvl(un_real_gl.amortization_rate, 0)) AS gainloss_rate,
       sum(nvl(un_real_gl.ited_gl_percentage, 0)) AS ited_gl_percentage
       -- GainLoss
      ,
       sum(nvl(un_real_gl.itsd_amount, 0)) as itsd_gainloss_value,
       sum(nvl(un_real_gl.amount_amortization, 0)) AS un_real_gl_amount,
       sum(nvl(un_real_gl.accelerated_amount, 0)) AS un_real_gl_positive_corr,
       sum(nvl(un_real_gl.reversal_amount, 0)) AS un_real_gl_negative_corr,
       case
         when round(sum(nvl(un_real_gl.dist_amt, 0)), 4) <> 0 then
          sum(nvl(un_real_gl.dist_amt, 0))
         else
          0
       end AS real_gl_amount,
       case
         when round(sum(nvl(un_real_gl.cum_amount_amortization, 0)), 4) <> 0 then
          sum(nvl(un_real_gl.cum_amount_amortization, 0))
         else
          0
       end AS cum_gainloss_amount -- AS cum_un_real_gl_amount
       -- GainLoss_NIC
      ,
       sum(nvl(un_real_gl.itsd_amount_nic, 0)) as itsd_gainloss_value_nic,
       sum(nvl(un_real_gl.amount_amortization_nic, 0)) AS un_real_gl_amount_nic,
       sum(nvl(un_real_gl.accelerated_amount_nic, 0)) AS un_real_gl_positive_corr_nic,
       sum(nvl(un_real_gl.reversal_amount_nic, 0)) AS un_real_gl_negative_corr_nic,
       case
         when round(sum(nvl(un_real_gl.dist_amt_nic, 0)), 4) <> 0 then
          sum(nvl(un_real_gl.dist_amt_nic, 0))
         else
          0
       end AS real_gl_amount_nic,
       case
         when round(sum(nvl(un_real_gl.cum_amount_amortization_nic, 0)), 4) <> 0 then
          sum(nvl(un_real_gl.cum_amount_amortization_nic, 0))
         else
          0
       end AS cum_gainloss_amount_nic -- AS cum_un_real_gl_amount_nic
       -- Dividend Rates
      ,
       sum(nvl(dividend.amortization_rate, 0)) AS dividend_rate,
       sum(nvl(dividend.ited_dividend_percentage, 0)) AS ited_dividend_percentage
       -- Dividend Amounts
      ,
       case
         when round(sum(nvl(dividend.itsd_amount, 0)) -
                    sum(nvl(dividend.itsd_dist_amt, 0)),
                    4) <> 0 then
          sum(nvl(dividend.itsd_amount, 0)) -
          sum(nvl(dividend.itsd_dist_amt, 0))
         else
          0
       end as itsd_dividend_value,
       sum(nvl(dividend.amount_amortization, 0)) AS dividend_amount,
       sum(nvl(dividend.accelerated_amount, 0)) AS dividend_positive_corr,
       sum(nvl(dividend.reversal_amount, 0)) AS dividend_negative_corr,
       case
         when round(sum(nvl(dividend.dist_amt, 0)), 4) <> 0 then
          sum(nvl(dividend.dist_amt, 0))
         else
          0
       end AS dividend_dist_amount,
       case
         when round(sum(nvl(dividend.cum_amount_amortization, 0)) -
                    sum(nvl(dividend.cum_dist_amt, 0)),
                    4) <> 0 then
          sum(nvl(dividend.cum_amount_amortization, 0)) -
          sum(nvl(dividend.cum_dist_amt, 0))
         else
          0
       end AS cum_dividend_amount -- changed
       -- Dividend Amount NIC
      ,
       case
         when round(sum(nvl(dividend.itsd_amount_nic, 0)) -
                    sum(nvl(dividend.itsd_dist_amt_nic, 0)),
                    4) <> 0 then
          sum(nvl(dividend.itsd_amount_nic, 0)) -
          sum(nvl(dividend.itsd_dist_amt_nic, 0))
         else
          0
       end as itsd_dividend_value_nic -- Changed
      ,
       sum(nvl(dividend.amount_amortization_nic, 0)) AS dividend_amount_nic,
       sum(nvl(dividend.accelerated_amount_nic, 0)) AS dividend_positive_corr_nic,
       sum(nvl(dividend.reversal_amount_nic, 0)) AS dividend_negative_corr_nic,
       case
         when round(sum(nvl(dividend.dist_amt_nic, 0)), 4) <> 0 then
          sum(nvl(dividend.dist_amt_nic, 0))
         else
          0
       end AS dividend_dist_amount_nic,
       case
         when round(sum(nvl(dividend.cum_amount_amortization_nic, 0)) -
                    sum(nvl(dividend.cum_dist_amt_nic, 0)),
                    4) <> 0 then
          sum(nvl(dividend.cum_amount_amortization_nic, 0)) -
          sum(nvl(dividend.cum_dist_amt_nic, 0))
         else
          0
       end AS cum_dividend_amount_nic -- changed
       -- Interest Rates
      ,
       sum(nvl(interest.amortization_rate, 0)) AS interest_rate,
       sum(nvl(interest.ited_interest_percentage, 0)) AS ited_interest_percentage
       -- Interest Amounts
      ,
       case
         when round(sum(nvl(interest.itsd_amount, 0)) -
                    sum(nvl(interest.itsd_dist_amt, 0)),
                    4) <> 0 then
          sum(nvl(interest.itsd_amount, 0)) -
          sum(nvl(interest.itsd_dist_amt, 0))
         else
          0
       end as itsd_interest_value -- changed
      ,
       sum(nvl(interest.amount_amortization, 0)) AS interest_amount,
       sum(nvl(interest.accelerated_amount, 0)) AS interest_positive_corr,
       sum(nvl(interest.reversal_amount, 0)) AS interest_negative_corr,
       case
         when round(sum(nvl(interest.dist_amt, 0)), 4) <> 0 then
          sum(nvl(interest.dist_amt, 0))
         else
          0
       end AS interest_dist_amount,
       case
         when round(sum(nvl(interest.cum_amount_amortization, 0)) -
                    sum(nvl(interest.cum_dist_amt, 0)),
                    4) <> 0 then
          sum(nvl(interest.cum_amount_amortization, 0)) -
          sum(nvl(interest.cum_dist_amt, 0))
         else
          0
       end AS cum_interest_amount -- changed
       -- Interest Amount Nic
      ,
       case
         when round(sum(nvl(interest.itsd_amount_nic, 0)) -
                    sum(nvl(interest.itsd_dist_amt_nic, 0)),
                    4) <> 0 then
          sum(nvl(interest.itsd_amount_nic, 0)) -
          sum(nvl(interest.itsd_dist_amt_nic, 0))
         else
          0
       end as itsd_interest_value_nic -- Changed
      ,
       sum(nvl(interest.amount_amortization_nic, 0)) AS interest_amount_nic,
       sum(nvl(interest.accelerated_amount_nic, 0)) AS interest_positive_corr_nic,
       sum(nvl(interest.reversal_amount_nic, 0)) AS interest_negative_corr_nic,
       case
         when round(sum(nvl(interest.dist_amt_nic, 0)), 4) <> 0 then
          sum(nvl(interest.dist_amt_nic, 0))
         else
          0
       end AS interest_dist_amount_nic,
       case
         when sum(nvl(interest.cum_amount_amortization_nic, 0)) -
              sum(nvl(interest.cum_dist_amt_nic, 0)) <> 0 then
          sum(nvl(interest.cum_amount_amortization_nic, 0)) -
          sum(nvl(interest.cum_dist_amt_nic, 0))
         else
          0
       end AS cum_interest_amount_nic -- changed
       --Amort Schedule Change Fields
      ,
       coalesce(base.current_amortization_end_date,
                UN_REAL_GL.current_amortization_end_date,
                dividend.current_amortization_end_date,
                interest. current_amortization_end_date) as current_amortization_end_date,
       coalesce(base.amort_schedule_change_flag,
                UN_REAL_GL.amort_schedule_change_flag,
                dividend.amort_schedule_change_flag,
                interest.amort_schedule_change_flag) as amort_schedule_change_flag,
       coalesce(base.amort_schedule_change_date,
                UN_REAL_GL.amort_schedule_change_date,
                dividend.amort_schedule_change_date,
                interest.amort_schedule_change_date) as amort_schedule_change_date
       -- Org Fields
      ,
       coalesce(base.org_type1,
                UN_REAL_GL.org_type1,
                dividend.org_type1,
                interest.org_type1) as org_type1,
       coalesce(base.org_unit1,
                UN_REAL_GL.org_unit1,
                dividend.org_unit1,
                interest.org_unit1) as org_unit1,
       coalesce(base.org_type2,
                UN_REAL_GL.org_type2,
                dividend.org_type2,
                interest.org_type2) as org_type2,
       coalesce(base.org_unit2,
                UN_REAL_GL.org_unit2,
                dividend.org_unit2,
                interest.org_unit2) as org_unit2,
       coalesce(base.org_type3,
                UN_REAL_GL.org_type3,
                dividend.org_type3,
                interest.org_type3) as org_type3,
       coalesce(base.org_unit3,
                UN_REAL_GL.org_unit3,
                dividend.org_unit3,
                interest.org_unit3) as org_unit3,
       coalesce(base.org_type4,
                UN_REAL_GL.org_type4,
                dividend.org_type4,
                interest.org_type4) as org_type4,
       coalesce(base.org_unit4,
                UN_REAL_GL.org_unit4,
                dividend.org_unit4,
                interest.org_unit4) as org_unit4,
       coalesce(base.org_type5,
                UN_REAL_GL.org_type5,
                dividend.org_type5,
                interest.org_type5) as org_type5,
       coalesce(base.org_unit5,
                UN_REAL_GL.org_unit5,
                dividend.org_unit5,
                interest.org_unit5) as org_unit5,
       coalesce(base.org_type6,
                UN_REAL_GL.org_type6,
                dividend.org_type6,
                interest.org_type6) as org_type6,
       coalesce(base.org_unit6,
                UN_REAL_GL.org_unit6,
                dividend.org_unit6,
                interest.org_unit6) as org_unit6,
       coalesce(base.org_type7,
                UN_REAL_GL.org_type7,
                dividend.org_type7,
                interest.org_type7) as org_type7,
       coalesce(base.org_unit7,
                UN_REAL_GL.org_unit7,
                dividend.org_unit7,
                interest.org_unit7) as org_unit7,
       coalesce(base.org_type8,
                UN_REAL_GL.org_type8,
                dividend.org_type8,
                interest.org_type8) as org_type8,
       coalesce(base.org_unit8,
                UN_REAL_GL.org_unit8,
                dividend.org_unit8,
                interest.org_unit8) as org_unit8,
       coalesce(base.org_type9,
                UN_REAL_GL.org_type9,
                dividend.org_type9,
                interest.org_type9) as org_type9,
       coalesce(base.org_unit9,
                UN_REAL_GL.org_unit9,
                dividend.org_unit9,
                interest.org_unit9) as org_unit9,
       coalesce(base.org_type10,
                UN_REAL_GL.org_type10,
                dividend.org_type10,
                interest.org_type10) as org_type10,
       coalesce(base.org_unit10,
                UN_REAL_GL.org_unit10,
                dividend.org_unit10,
                interest.org_unit10) as org_unit10
  FROM plans p,
       award a,
       participant pa,
       groups g,
       (SELECT pav.participant_uid,
               pav.award_code,
               pav.currency_iso_code,
               pav.award_value,
               pav.award_value_nic
          FROM part_award_value pav) award_value,
       (SELECT aa1.company_code,
               aa1.participant_uid,
               aa1.award_code,
               aa1.tranche_id,
               aa1.component,
               aa1.effective_date,
               aa1.booking_date,
               aa1.org_type1,
               aa1.org_unit1,
               aa1.org_type2,
               aa1.org_unit2,
               aa1.org_type3,
               aa1.org_unit3,
               aa1.org_type4,
               aa1.org_unit4,
               aa1.org_type5,
               aa1.org_unit5,
               aa1.org_type6,
               aa1.org_unit6,
               aa1.org_type7,
               aa1.org_unit7,
               aa1.org_type8,
               aa1.org_unit8,
               aa1.org_type9,
               aa1.org_unit9,
               aa1.org_type10,
               aa1.org_unit10
          FROM amort_amount aa1) aa,
       (SELECT aa.company_code,
               aa.participant_uid,
               aa.award_code,
               aa.tranche_id,
               aa.component,
               aa.booking_date,
               aa.effective_date,
               aa.amortization_rate,
               aa.amount_amortization,
               aa.amount_amortization_nic,
               aa.cum_amount_amortization,
               aa.cum_amount_amortization_nic,
               aa.accelerated_amount,
               aa.accelerated_amount_nic,
               aa.reversal_amount,
               aa.reversal_amount_nic,
               aa.itsd_amount,
               aa.itsd_amount_nic,
               ited_rate.ited_base_percentage,
               ited_rate.current_amortization_end_date,
               ited_rate.amort_schedule_change_flag,
               ited_rate.amort_schedule_change_date,
               aa.dist_amt,
               aa.dist_amt_nic,
               aa.cum_dist_amt,
               aa.cum_dist_amt_nic,
               aa.itsd_dist_amt,
               aa.itsd_dist_amt_nic,
               aa.org_type1,
               aa.org_unit1,
               aa.org_type2,
               aa.org_unit2,
               aa.org_type3,
               aa.org_unit3,
               aa.org_type4,
               aa.org_unit4,
               aa.org_type5,
               aa.org_unit5,
               aa.org_type6,
               aa.org_unit6,
               aa.org_type7,
               aa.org_unit7,
               aa.org_type8,
               aa.org_unit8,
               aa.org_type9,
               aa.org_unit9,
               aa.org_type10,
               aa.org_unit10
          FROM amort_amount aa,
               (SELECT arwi.company_code,
                       arwi.participant_uid,
                       arwi.award_code,
                       arwi.tranche_id,
                       arwi.effective_date,
                       arwi.cumul_rate AS ited_base_percentage,
                       arwi.current_amortization_end_date,
                       arwi.amort_schedule_change_flag,
                       arwi.amort_schedule_change_date
                  FROM amort_rate_with_ipu arwi
                 WHERE arwi.component = 'BASE') ited_rate
         WHERE aa.component = 'BASE'
           AND aa.effective_date = ited_rate.effective_date(+)
           AND aa.participant_uid = ited_rate.participant_uid(+)
           AND aa.award_code = ited_rate.award_code(+)
           AND aa.tranche_id = ited_rate.tranche_id(+)
           AND aa.company_code = ited_rate.company_code(+)) BASE,
       (SELECT aa.company_code,
               aa.participant_uid,
               aa.award_code,
               aa.tranche_id,
               aa.component,
               aa.booking_date,
               aa.effective_date,
               aa.amortization_rate,
               aa.amount_amortization,
               aa.amount_amortization_nic,
               aa.cum_amount_amortization,
               aa.cum_amount_amortization_nic,
               aa.accelerated_amount,
               aa.accelerated_amount_nic,
               aa.reversal_amount,
               aa.reversal_amount_nic,
               aa.itsd_amount,
               aa.itsd_amount_nic,
               ited_rate.ited_gl_percentage,
               ited_rate.current_amortization_end_date,
               ited_rate.amort_schedule_change_flag,
               ited_rate.amort_schedule_change_date,
               aa.dist_amt,
               aa.dist_amt_nic,
               aa.cum_dist_amt,
               aa.cum_dist_amt_nic,
               aa.itsd_dist_amt,
               aa.itsd_dist_amt_nic,
               aa.org_type1,
               aa.org_unit1,
               aa.org_type2,
               aa.org_unit2,
               aa.org_type3,
               aa.org_unit3,
               aa.org_type4,
               aa.org_unit4,
               aa.org_type5,
               aa.org_unit5,
               aa.org_type6,
               aa.org_unit6,
               aa.org_type7,
               aa.org_unit7,
               aa.org_type8,
               aa.org_unit8,
               aa.org_type9,
               aa.org_unit9,
               aa.org_type10,
               aa.org_unit10
          FROM amort_amount aa,
               (SELECT arwi.company_code,
                       arwi.participant_uid,
                       arwi.award_code,
                       arwi.tranche_id,
                       arwi.effective_date,
                       arwi.cumul_rate AS ited_gl_percentage,
                       arwi.current_amortization_end_date,
                       arwi.amort_schedule_change_flag,
                       arwi.amort_schedule_change_date
                  FROM amort_rate_with_ipu arwi
                 WHERE arwi.component = 'GAINLOSS') ited_rate
         WHERE aa.component = 'GAINLOSS'
           AND aa.effective_date = ited_rate.effective_date(+)
           AND aa.participant_uid = ited_rate.participant_uid(+)
           AND aa.award_code = ited_rate.award_code(+)
           AND aa.tranche_id = ited_rate.tranche_id(+)
           AND aa.company_code = ited_rate.company_code(+)) UN_REAL_GL,
       (SELECT aa.company_code,
               aa.participant_uid,
               aa.award_code,
               aa.tranche_id,
               aa.component,
               aa.booking_date,
               aa.effective_date,
               aa.amortization_rate,
               aa.amount_amortization,
               aa.amount_amortization_nic,
               aa.cum_amount_amortization,
               aa.cum_amount_amortization_nic,
               aa.accelerated_amount,
               aa.accelerated_amount_nic,
               aa.reversal_amount,
               aa.reversal_amount_nic,
               aa.itsd_amount,
               aa.itsd_amount_nic,
               ited_rate.ited_dividend_percentage,
               ited_rate.current_amortization_end_date,
               ited_rate.amort_schedule_change_flag,
               ited_rate.amort_schedule_change_date,
               aa.dist_amt,
               aa.dist_amt_nic,
               aa.cum_dist_amt,
               aa.cum_dist_amt_nic,
               aa.itsd_dist_amt,
               aa.itsd_dist_amt_nic,
               aa.org_type1,
               aa.org_unit1,
               aa.org_type2,
               aa.org_unit2,
               aa.org_type3,
               aa.org_unit3,
               aa.org_type4,
               aa.org_unit4,
               aa.org_type5,
               aa.org_unit5,
               aa.org_type6,
               aa.org_unit6,
               aa.org_type7,
               aa.org_unit7,
               aa.org_type8,
               aa.org_unit8,
               aa.org_type9,
               aa.org_unit9,
               aa.org_type10,
               aa.org_unit10
          FROM amort_amount aa,
               (SELECT arwi.company_code,
                       arwi.participant_uid,
                       arwi.award_code,
                       arwi.tranche_id,
                       arwi.effective_date,
                       arwi.cumul_rate AS ited_dividend_percentage,
                       arwi.current_amortization_end_date,
                       arwi.amort_schedule_change_flag,
                       arwi.amort_schedule_change_date
                  FROM amort_rate_with_ipu arwi
                 WHERE arwi.component = 'DIVIDEND') ited_rate
         WHERE aa.component = 'DIVIDEND'
           AND aa.effective_date = ited_rate.effective_date(+)
           AND aa.participant_uid = ited_rate.participant_uid(+)
           AND aa.award_code = ited_rate.award_code(+)
           AND aa.tranche_id = ited_rate.tranche_id(+)
           AND aa.company_code = ited_rate.company_code(+)) DIVIDEND,
       (SELECT aa.company_code,
               aa.participant_uid,
               aa.award_code,
               aa.tranche_id,
               aa.component,
               aa.booking_date,
               aa.effective_date,
               aa.amortization_rate,
               aa.amount_amortization,
               aa.amount_amortization_nic,
               aa.cum_amount_amortization,
               aa.cum_amount_amortization_nic,
               aa.accelerated_amount,
               aa.accelerated_amount_nic,
               aa.reversal_amount,
               aa.reversal_amount_nic,
               aa.itsd_amount,
               aa.itsd_amount_nic,
               ited_rate.ited_interest_percentage,
               ited_rate.current_amortization_end_date,
               ited_rate.amort_schedule_change_flag,
               ited_rate.amort_schedule_change_date,
               aa.dist_amt,
               aa.dist_amt_nic,
               aa.cum_dist_amt,
               aa.cum_dist_amt_nic,
               aa.itsd_dist_amt,
               aa.itsd_dist_amt_nic,
               aa.org_type1,
               aa.org_unit1,
               aa.org_type2,
               aa.org_unit2,
               aa.org_type3,
               aa.org_unit3,
               aa.org_type4,
               aa.org_unit4,
               aa.org_type5,
               aa.org_unit5,
               aa.org_type6,
               aa.org_unit6,
               aa.org_type7,
               aa.org_unit7,
               aa.org_type8,
               aa.org_unit8,
               aa.org_type9,
               aa.org_unit9,
               aa.org_type10,
               aa.org_unit10
          FROM amort_amount aa,
               (SELECT arwi.company_code,
                       arwi.participant_uid,
                       arwi.award_code,
                       arwi.tranche_id,
                       arwi.effective_date,
                       arwi.cumul_rate AS ited_interest_percentage,
                       arwi.current_amortization_end_date,
                       arwi.amort_schedule_change_flag,
                       arwi.amort_schedule_change_date
                  FROM amort_rate_with_ipu arwi
                 WHERE arwi.component = 'INTEREST') ited_rate
         WHERE aa.component = 'INTEREST'
           AND aa.effective_date = ited_rate.effective_date(+)
           AND aa.participant_uid = ited_rate.participant_uid(+)
           AND aa.award_code = ited_rate.award_code(+)
           AND aa.tranche_id = ited_rate.tranche_id(+)
           AND aa.company_code = ited_rate.company_code(+)) INTEREST
 WHERE aa.PARTICIPANT_UID = dividend.participant_uid(+)
   AND aa.award_code = dividend.award_code(+)
   AND aa.tranche_id = dividend.tranche_id(+)
   AND aa.effective_date = dividend.effective_date(+)
   and aa.component = dividend.component(+)
   AND aa.PARTICIPANT_UID = un_real_gl.participant_uid(+)
   AND aa.award_code = un_real_gl.award_code(+)
   AND aa.tranche_id = un_real_gl.tranche_id(+)
   AND aa.effective_date = un_real_gl.effective_date(+)
   and aa.component = un_real_gl.component(+)
   AND aa.PARTICIPANT_UID = BASE.participant_uid(+)
   AND aa.award_code = BASE.award_code(+)
   AND aa.tranche_id = BASE.tranche_id(+)
   AND aa.effective_date = BASE.effective_date(+)
   and aa.component = BASE.component(+)
   AND aa.PARTICIPANT_UID = interest.participant_uid(+)
   AND aa.award_code = interest.award_code(+)
   AND aa.tranche_id = interest.tranche_id(+)
   AND aa.effective_date = interest.effective_date(+)
   and aa.component = interest.component(+)
   AND SYS_OP_MAP_NONNULL(aa.org_type1) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit1) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type2) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit2) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type3) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit3) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type4) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit4) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type5) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit5) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type6) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit6) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type7) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit7) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type8) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit8) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type9) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit9) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type10) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_type10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit10) =
       SYS_OP_MAP_NONNULL(un_real_gl.org_unit10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type1) =
       SYS_OP_MAP_NONNULL(base.org_type1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit1) =
       SYS_OP_MAP_NONNULL(base.org_unit1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type2) =
       SYS_OP_MAP_NONNULL(base.org_type2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit2) =
       SYS_OP_MAP_NONNULL(base.org_unit2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type3) =
       SYS_OP_MAP_NONNULL(base.org_type3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit3) =
       SYS_OP_MAP_NONNULL(base.org_unit3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type4) =
       SYS_OP_MAP_NONNULL(base.org_type4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit4) =
       SYS_OP_MAP_NONNULL(base.org_unit4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type5) =
       SYS_OP_MAP_NONNULL(base.org_type5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit5) =
       SYS_OP_MAP_NONNULL(base.org_unit5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type6) =
       SYS_OP_MAP_NONNULL(base.org_type6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit6) =
       SYS_OP_MAP_NONNULL(base.org_unit6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type7) =
       SYS_OP_MAP_NONNULL(base.org_type7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit7) =
       SYS_OP_MAP_NONNULL(base.org_unit7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type8) =
       SYS_OP_MAP_NONNULL(base.org_type8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit8) =
       SYS_OP_MAP_NONNULL(base.org_unit8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type9) =
       SYS_OP_MAP_NONNULL(base.org_type9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit9) =
       SYS_OP_MAP_NONNULL(base.org_unit9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type10) =
       SYS_OP_MAP_NONNULL(base.org_type10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit10) =
       SYS_OP_MAP_NONNULL(base.org_unit10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type1) =
       SYS_OP_MAP_NONNULL(dividend.org_type1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit1) =
       SYS_OP_MAP_NONNULL(dividend.org_unit1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type2) =
       SYS_OP_MAP_NONNULL(dividend.org_type2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit2) =
       SYS_OP_MAP_NONNULL(dividend.org_unit2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type3) =
       SYS_OP_MAP_NONNULL(dividend.org_type3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit3) =
       SYS_OP_MAP_NONNULL(dividend.org_unit3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type4) =
       SYS_OP_MAP_NONNULL(dividend.org_type4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit4) =
       SYS_OP_MAP_NONNULL(dividend.org_unit4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type5) =
       SYS_OP_MAP_NONNULL(dividend.org_type5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit5) =
       SYS_OP_MAP_NONNULL(dividend.org_unit5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type6) =
       SYS_OP_MAP_NONNULL(dividend.org_type6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit6) =
       SYS_OP_MAP_NONNULL(dividend.org_unit6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type7) =
       SYS_OP_MAP_NONNULL(dividend.org_type7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit7) =
       SYS_OP_MAP_NONNULL(dividend.org_unit7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type8) =
       SYS_OP_MAP_NONNULL(dividend.org_type8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit8) =
       SYS_OP_MAP_NONNULL(dividend.org_unit8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type9) =
       SYS_OP_MAP_NONNULL(dividend.org_type9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit9) =
       SYS_OP_MAP_NONNULL(dividend.org_unit9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type10) =
       SYS_OP_MAP_NONNULL(dividend.org_type10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit10) =
       SYS_OP_MAP_NONNULL(dividend.org_unit10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type1) =
       SYS_OP_MAP_NONNULL(interest.org_type1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit1) =
       SYS_OP_MAP_NONNULL(interest.org_unit1(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type2) =
       SYS_OP_MAP_NONNULL(interest.org_type2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit2) =
       SYS_OP_MAP_NONNULL(interest.org_unit2(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type3) =
       SYS_OP_MAP_NONNULL(interest.org_type3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit3) =
       SYS_OP_MAP_NONNULL(interest.org_unit3(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type4) =
       SYS_OP_MAP_NONNULL(interest.org_type4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit4) =
       SYS_OP_MAP_NONNULL(interest.org_unit4(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type5) =
       SYS_OP_MAP_NONNULL(interest.org_type5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit5) =
       SYS_OP_MAP_NONNULL(interest.org_unit5(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type6) =
       SYS_OP_MAP_NONNULL(interest.org_type6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit6) =
       SYS_OP_MAP_NONNULL(interest.org_unit6(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type7) =
       SYS_OP_MAP_NONNULL(interest.org_type7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit7) =
       SYS_OP_MAP_NONNULL(interest.org_unit7(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type8) =
       SYS_OP_MAP_NONNULL(interest.org_type8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit8) =
       SYS_OP_MAP_NONNULL(interest.org_unit8(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type9) =
       SYS_OP_MAP_NONNULL(interest.org_type9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit9) =
       SYS_OP_MAP_NONNULL(interest.org_unit9(+))
   AND SYS_OP_MAP_NONNULL(aa.org_type10) =
       SYS_OP_MAP_NONNULL(interest.org_type10(+))
   AND SYS_OP_MAP_NONNULL(aa.org_unit10) =
       SYS_OP_MAP_NONNULL(interest.org_unit10(+))
   AND aa.participant_uid = pa.participant_uid
   AND aa.award_code = a.award_code
   AND p.PLANS_UID = pa.plans_uid
   AND aa.participant_uid = award_value.participant_uid
   AND aa.award_code = award_value.award_code
   AND pa.groups_uid = g.groups_uid
 GROUP BY aa.company_code,
          p.code,
          p.plan_short_name,
          aa.award_code,
          a.award_reason,
          pa.executive_id,
          pa.participant_id,
          aa.participant_uid,
          pa.last_name,
          pa.first_name,
          g.code,
          aa.tranche_id,
          award_value.currency_iso_code,
          nvl(award_value.award_value, 0),
          nvl(award_value.award_value_nic, 0),
          coalesce(base.current_amortization_end_date,
                   UN_REAL_GL.current_amortization_end_date,
                   dividend.current_amortization_end_date,
                   interest. current_amortization_end_date),
          coalesce(base.amort_schedule_change_flag,
                   UN_REAL_GL.amort_schedule_change_flag,
                   dividend.amort_schedule_change_flag,
                   interest.amort_schedule_change_flag),
          coalesce(base.amort_schedule_change_date,
                   UN_REAL_GL.amort_schedule_change_date,
                   dividend.amort_schedule_change_date,
                   interest.amort_schedule_change_date),
          coalesce(base.org_type1,
                   UN_REAL_GL.org_type1,
                   dividend.org_type1,
                   interest.org_type1),
          coalesce(base.org_unit1,
                   UN_REAL_GL.org_unit1,
                   dividend.org_unit1,
                   interest.org_unit1),
          coalesce(base.org_type2,
                   UN_REAL_GL.org_type2,
                   dividend.org_type2,
                   interest.org_type2),
          coalesce(base.org_unit2,
                   UN_REAL_GL.org_unit2,
                   dividend.org_unit2,
                   interest.org_unit2),
          coalesce(base.org_type3,
                   UN_REAL_GL.org_type3,
                   dividend.org_type3,
                   interest.org_type3),
          coalesce(base.org_unit3,
                   UN_REAL_GL.org_unit3,
                   dividend.org_unit3,
                   interest.org_unit3),
          coalesce(base.org_type4,
                   UN_REAL_GL.org_type4,
                   dividend.org_type4,
                   interest.org_type4),
          coalesce(base.org_unit4,
                   UN_REAL_GL.org_unit4,
                   dividend.org_unit4,
                   interest.org_unit4),
          coalesce(base.org_type5,
                   UN_REAL_GL.org_type5,
                   dividend.org_type5,
                   interest.org_type5),
          coalesce(base.org_unit5,
                   UN_REAL_GL.org_unit5,
                   dividend.org_unit5,
                   interest.org_unit5),
          coalesce(base.org_type6,
                   UN_REAL_GL.org_type6,
                   dividend.org_type6,
                   interest.org_type6),
          coalesce(base.org_unit6,
                   UN_REAL_GL.org_unit6,
                   dividend.org_unit6,
                   interest.org_unit6),
          coalesce(base.org_type7,
                   UN_REAL_GL.org_type7,
                   dividend.org_type7,
                   interest.org_type7),
          coalesce(base.org_unit7,
                   UN_REAL_GL.org_unit7,
                   dividend.org_unit7,
                   interest.org_unit7),
          coalesce(base.org_type8,
                   UN_REAL_GL.org_type8,
                   dividend.org_type8,
                   interest.org_type8),
          coalesce(base.org_unit8,
                   UN_REAL_GL.org_unit8,
                   dividend.org_unit8,
                   interest.org_unit8),
          coalesce(base.org_type9,
                   UN_REAL_GL.org_type9,
                   dividend.org_type9,
                   interest.org_type9),
          coalesce(base.org_unit9,
                   UN_REAL_GL.org_unit9,
                   dividend.org_unit9,
                   interest.org_unit9),
          coalesce(base.org_type10,
                   UN_REAL_GL.org_type10,
                   dividend.org_type10,
                   interest.org_type10),
          coalesce(base.org_unit10,
                   UN_REAL_GL.org_unit10,
                   dividend.org_unit10,
                   interest.org_unit10),
          aa.effective_date,
          aa.booking_date;