stress.sql

   1  SELECT xx.xx_rec_id                                    promise_unit_ID,
   2         xx.xx_volume                                    aggregate_volume,
   3         xx_category                                     category,
   4         xx_difference                                   difference,
   5         xx_initial_liability_amt                        initial_liability,
   6         xx_out_of_pocket                                out_of_pocket,
   7         xx_holdings_Secured_Flag                        holdings_UNSECURED_FLAG,
   8         xx.xx_normal_liability_chart                    normal_liability_chart,
   9         xx.xx_facility_liability_chart                  FACILITY_liability_chart,
  10         yy.yy_rec_i                                     target_REC_ID,
  11         yy.yy_prediction_meter                          chart_prediction_meter,
  12         yy.yy_unique_instance_liability_chart           unique_instance_liability_chart,
  13         yy.yy_target_liability_chart                    target_liability_chart,
  14         xx.xx_indicator_id,
  15         xx.xx_unused_src_calc                           UNUSED_count_SOURCE,
  16         yy.yy_target_nm                                 target_identifier,
  17         yy.yy_target_id                                 target_ID,
  18         xx.xx_facility_id                               mqxy_promise_unit_ID,
  19         xx.xx_amt                                       mqxy_unit_present_count,
  20         xx.xx_units                                     mqxy_unit_present_units,
  21         xx.xx_standard_amt                              mqxy_standard_promise_count,
  22         xx.xx_units                                     mqxy_standard_unit_units,
  23         to_CHAR(trunc(xx_during_contract_DT))           during_contract_DATE,
  24         to_CHAR(trunc(xx_termination_DT))               termination_DATE,
  25         to_CHAR(trunc(xx.xx_expiration_DT))             expiration_DATE,
  26         nvl(agreement_aggregate.agreement_aggregate,0)  agreement_aggregate,
  27         nvl(lc_aggregate.lc_aggregate,0)                LC_aggregate,
  28         r.r_xoynx_RTG                                   xoynxS_RATING,
  29         r.r_grrrr_RTG                                   grrrr_RATING
  30  FROM   (SELECT yy.xx_rec_id,
  31                 SUM(yy.l_count)                 agreement_aggregate
  32         FROM    target                  yy,
  33                 promise_agreement       zz,
  34                 promise_unit            aa,
  35                 agreement_unit          lo
  36         WHERE   zz.yy_rec_id = yy.yy_rec_id AND
  37                 xx.ca_rec_id = zz.ca_rec_id AND
  38                 lo.xx_rec_id = xx.xx_rec_id
  39         GROUP BY        yy.yy_rec_id,
  40                         lo.xx_rec_id
  41         )                       agreement_aggregate,
  42         (SELECT lc.xx_rec_id,
  43                 SUM(lc.lc_amt)          lc_aggregate
  44         FROM    target                  yy,
  45                 promise_agreement       zz,
  46                 promise_unit            aa,
  47                 lc_unit                 lc
  48         WHERE   zz.yy_rec_id = yy.yy_rec_id AND
  49                 xx.ca_rec_id = zz.ca_rec_id AND
  50                 lc.xx_rec_id = xx.xx_rec_id
  51         GROUP BY        yy.yy_rec_id,
  52                         lc.xx_rec_id
  53         )                       lc_aggregate,
  54         target                  yy,
  55         promise_agreement       zz,
  56         rating                  r,
  57         promise_unit            cs
  58  WHERE  zz.yy_rec_id = yy.yy_rec_id AND
  59         r.yy_rec_id = yy.yy_rec_id AND
  60         xx.ca_rec_id = zz.ca_rec_id AND
  61         lc_aggregate.xx_rec_id = xx.xx_rec_id AND
  62         agreement_aggegate.xx_rec_id    = xx.xx_rec_id AND
  63         xx.xx_indicator_id = 1 AND
  64         zz.ca_indicator_id = 1 AND
  65         (       r.r_indicator_id = 1 OR
  66                 r.r_indicator_id IS NULL)
  67  ORDER BY target_identifier