/**
 * this view wraps the ccc_rightsholder_agreement table. it's
 * only purpose is to fill the PTY_INST column with the rightsholders
 * account number. This is temporary work around to deal with a bug in
 * RMS migration.
 */
create or replace view rms_mig_rh_agreement (
AGR_INST,
PTY_INST,
AGT_INST,
AGREEMENT_TYPE,
START_DATE,
END_DATE,
AGR_STATUS_CD,
OPTIONS,
RENEWAL_NOTIFICATION,
NOTIFICATION_STATUS,
SIGNED_BY_NAME,
SIGNED_BY_TITLE,
CCC_ACCT_REP_ID,
CCC_SIGNED_BY_NAME,
CCC_SIGNED_BY_TITLE,
EFFECTIVE_DATE,
CRE_DTM,
CRE_USER,
UPD_DTM,
UPD_USER,
AUTHOR,
FULL_EXTENT,
COMMENTS,
AGREEMENT_TEXT_CHANGE,
EXCEPTION_TEXT,
VLD_BEG_DTM,
VLD_END_DTM) as
select
     a.AGR_INST,
     p.account_num PTY_INST,
     a.AGT_INST,
     a.AGREEMENT_TYPE,
     a.START_DATE,
     a.END_DATE,
     a.AGR_STATUS_CD,
     a.OPTIONS,
     a.RENEWAL_NOTIFICATION,
     a.NOTIFICATION_STATUS,
     a.SIGNED_BY_NAME,
     a.SIGNED_BY_TITLE,
     decode(rep.emp_inst,
         null,'Not Applied in TF',
         rep.lname||decode(rep.fname,null,null,', ')||rep.fname) as CCC_ACCT_REP_ID,
     a.CCC_SIGNED_BY_NAME,
     a.CCC_SIGNED_BY_TITLE,
     a.EFFECTIVE_DATE,
     a.CRE_DTM,
     a.CRE_USER,
     a.UPD_DTM,
     a.UPD_USER,
     a.AUTHOR,
     a.FULL_EXTENT,
     a.COMMENTS,
     a.AGREEMENT_TEXT_CHANGE,
     a.EXCEPTION_TEXT,
     a.VLD_BEG_DTM,
     a.VLD_END_DTM
from ccctf.ccc_rightsholder_agreement a,
     ccctf.ccc_party p,
     ccctf.ccc_employee rep
where rep.emp_inst(+) = a.ccc_acct_rep_id
and a.pty_inst = p.pty_inst
and a.agt_inst in (4,5,6,12,16,39,58);

/*
  Creates a view of the agreements that need to be migrated.
  First, we include all agreement types that were at one time active and
  participating but have since been end dated.
  Second, we include all agreements that are active as of today and are
  either Participating, Refused, Withdrawn, Ceased, or Lost.
*/
create or replace view rms_mig_agreements_v as
   -- first get "ended" participating agreements
select rha.*
from
       CCCTF.CCC_RIGHTSHOLDER_AGREEMENT    rha
where  (pty_inst, agt_inst) in
   (select rha2.pty_inst, rha2.agt_inst
    from
           CCCTF.CCC_RIGHTSHOLDER_AGREEMENT    rha2
    where rha2.agr_status_cd = 'P'
    and sysdate not between rha2.start_date and rha2.end_date
    and rha2.agt_inst in (4,5,6,12,16,39,58,60) )
union
  -- now get agreements that are "current" as of today
select rha.*
from
       CCCTF.CCC_RIGHTSHOLDER_AGREEMENT    rha
where  (rha.pty_inst, agt_inst) in
   (select rha2.pty_inst, rha2.agt_inst
    from
           CCCTF.CCC_RIGHTSHOLDER_AGREEMENT    rha2
    where rha2.agr_status_cd in ('P','R','W','C','L')
    and sysdate between rha2.start_date and rha2.end_date
    and rha2.agt_inst in (4,5,6,12,16,39,58,60) );

/*
   Create a view that has a row for each rightsholder with additional columns
   describing its parent/child status
*/
create or replace view rms_rightsholder_childtype_v
    as
        SELECT p.pty_inst rh_pty_inst,
       orgname rh_name,
       party_id rh_party_id,
       NVL (
          (SELECT UNIQUE 'YES'
             FROM ccc_party p1, ccc_pty_pTY_type rel -- is this an rro?
            WHERE     p.pty_inst = rel.pty_inst
                  AND ptt_inst = 3
                  AND p.pty_inst = p1.pty_inst),
          'NO')
          is_rro,
       NVL (
          (SELECT UNIQUE 'YES'
             FROM ccc_party p1, ccc_pty_pTY_rel rel -- any tf parties have rro relationships?
            WHERE     p1.pty_inst = rel.child_pty_inst
                  AND rel_type = 'RRO'
                  AND p.pty_inst = p1.pty_inst),
          'NO')
          is_rro_member,
         (SELECT (select ORGNAME
                  from ccc_party rro
                  where rro.pty_inst = par_pty_inst) rroname
             FROM ccc_party p1, ccc_pty_pTY_rel rel -- rro name if member
            WHERE     p.pty_inst = rel.child_pty_inst
            AND rel_type = 'RRO'
            AND p.pty_inst = p1.pty_inst)          members_rro_name,
         (SELECT (select par_pty_inst
                  from ccc_party rro
                  where rro.pty_inst = par_pty_inst) parent_pty_inst
             FROM ccc_party p1,
                  ccc_pty_pTY_rel rel
            WHERE     p.pty_inst = rel.child_pty_inst
            AND rel_type = 'RRO'
            AND p.pty_inst = p1.pty_inst)         members_rro_pty_inst,
       NVL (
          (SELECT UNIQUE 'YES'
             FROM ccc_party p1, ccc_pty_pTY_rel rel -- any tf parties have rro relationships?
            WHERE     p1.pty_inst = rel.child_pty_inst
                  AND rel_type = 'SUBSIDIARY'
                  AND p.pty_inst = p1.pty_inst),
          'NO')
          is_subsidiary,
          (SELECT Par_pty_inst
             FROM ccc_party p1,
                  ccc_pty_pTY_rel rel -- any tf parties have subsidiaries?
            WHERE     p1.pty_inst = rel.child_pty_inst
            AND rel_type = 'SUBSIDIARY'
            AND p.pty_inst = p1.pty_inst
            AND ROWNUM = 1)
           parents_pty_inst,
          (SELECT (select ORGNAME
                  from ccc_party parent
                  where parent.pty_inst = par_pty_inst) name
             FROM ccc_party p1,
                  ccc_pty_pTY_rel rel -- parents name if subsidiary
            WHERE     p.pty_inst = rel.child_pty_inst
            AND rel_type = 'SUBSIDIARY'
            AND p.pty_inst = p1.pty_inst)          parents_name,
        NULL chrh_country
  FROM ccc_party p,
       ccc_pty_pty_type pt
  WHERE p.pty_inst = pt.pty_inst
  AND ptt_inst = 1;

/*
   Creates a view with a row for each RH showing whether or not they have
   a direct deal with CCC.
*/
create or replace view rms_direct_deal_right_v
    as
    SELECT
          case
            when rh_rct.is_rro = 'NO'
            and ra.agr_status_cd = 'P'
            and (rr.rgh_inst = ra.pty_inst
               or agr_rct.is_rro = 'NO')
            then 'Y'
            else 'N'
          end direct_deal,
         rr.rgh_inst rh_pty_inst,
         rh_rct.rh_name,
         rh_rct.is_rro,
         rh_rct.is_rro_member,
         rh_rct.is_subsidiary,
         ra.pty_inst agr_holders_pty_inst,
         agr_rct.rh_name agr_holders_name,
         agr_rct.is_rro agr_holder_is_rro,
         agr_rct.is_rro_member agr_holder_is_rro_member,
         r.rgt_inst,
         r.vld_beg_dtm,
         r.vld_end_dtm
     FROM
          rms_rightsholder_childtype_v rh_rct,
          rms_rightsholder_childtype_v agr_rct,
          ccctf.ccc_rightsholder_agreement ra,
          ccctf.ccc_right_rightsholder rr,
          ccctf.ccc_right r,
          ccctf.ccc_agreement_product_use apu,
          ccctf.ccc_product_use_schedule psc
    WHERE     rh_rct.rh_pty_inst = rr.rgh_inst
      AND agr_rct.rh_pty_inst = ra.pty_inst
      AND r.rgt_inst = rr.rgt_inst(+)
      and r.vld_beg_dtm = rr.vld_beg_dtm(+)
      and r.vld_end_dtm = rr.vld_end_dtm(+)
      AND ra.agr_inst = apu.agr_inst
      AND r.apu_inst = apu.apu_inst
      AND apu.psc_inst = psc.psc_inst;

/*
 creates a table with all WR works from each WR RRO collection
  runtime: 352 secs. in PRE1 637 secs dev1
 */
--drop the table if exists
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE rms_rro_collection_works';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

-- ensure we don't pick up non-preferred works
create table rms_rro_collection_works
 (collection_name, tf_wks_inst, wr_wrk_inst, tf_wrk_inst, cre_dtm,
  cre_user, upd_dtm, upd_user)
 as
 SELECT c.collection_name, c.tf_wks_inst, w.wr_wrk_inst, w.tf_wrk_inst,
        wc.cre_dtm,wc.cre_usr, wc.upd_dtm, wc.upd_usr
 FROM COLLECTION@CCC2WR c,
      WORK_COLLECTION@CCC2WR wc,
      WORK@CCC2WR w
 WHERE c.COLLECTION_TYPE_CD = 'RRO'
 and c.col_inst = wc.col_inst
 and wc.wr_wrk_inst = w.wr_wrk_inst
 and w.preferred_wrk_ind = 1;

CREATE OR REPLACE VIEW RMS_MIG_workset_rights AS
  SELECT
         rgt.rgt_inst,
         prd.prd_inst,
         prd.abrv,
         rgt.wks_inst rgt_wks_inst,
         rgt.wrk_inst rgt_wrk_inst,
         pty.account_num rgt_account,
         decode(pty.person_org_type,'O',pty.orgname,pty.lname||', '||pty.fname) rh_name,
         ddr.is_rro rh_rro_flag,
         ddr.is_rro_member rh_rro_mem_flag,
         apty.account_num agr_account,
         ddr.agr_holders_name,
         ddr.agr_holder_is_rro agr_holder_rro_flag,
         ddr.agr_holder_is_rro_member agr_holder_rro_member_flag,
         rma.agr_inst,
         rma.agt_inst agr_type_inst,
         rma.agreement_type agr_type,
         rma.start_date agr_start_date,
         rma.end_date agr_end_date,
         rma.effective_date agr_eff_date,
         rma.agr_status_cd,
         rma.cre_dtm agr_cre_dtm,
         rma.cre_user agr_cre_user,
         rma.upd_dtm agr_upd_dtm,
         rma.upd_user agr_upd_user,
         rgt.grant_deny rgt_permission,
         rgt.tps_inst rgt_tps_inst,
         rgt.tpu_inst rgt_tpu_inst,
         rgt.vld_beg_dtm rgt_start_date,
         rgt.vld_end_dtm rgt_end_date,
         rgt.cre_dtm rgt_cre_dtm,
         rgt.cre_user rgt_cre_user,
         rgt.upd_dtm rgt_upd_dtm,
         rgt.upd_user rgt_upd_user,
         rgt.publication_beg_date rgt_pub_beg_date,
         rgt.publication_end_date rgt_pub_end_date,
         rgt.external_comments rgt_term,
         rgq.qualifier_text rgt_rqs,
         DECODE (RGTS_LIMITATION_TPS, 146, 'T', 'F') rgt_reponsive_right_flag,
         ddr.direct_deal rgt_direct_deal_flag,
         wks.setname workset_name,
         wkstyp.brf_dscr workset_type,
         wks.comments workset_comments
  FROM         CCCTF.CCC_RIGHT                  rgt
    LEFT JOIN  CCCTF.CCC_RIGHT_QUALIFIER        rgq    ON rgt.rgq_inst = rgq.rgq_inst
    INNER JOIN CCCTF.CCC_WORK_SET               wks    ON rgt.wks_inst = wks.wks_inst
    INNER JOIN CCCTF.CCC_GENERAL_LOOK_UP        wkstyp ON (wks.wks_type_cd = wkstyp.cd AND wkstyp.catg = 'WORK_SET_TYPE_CODE')
    INNER JOIN RMS_DIRECT_DEAL_RIGHT_V          ddr    ON (rgt.rgt_inst = ddr.rgt_inst AND rgt.vld_beg_dtm = ddr.vld_beg_dtm AND rgt.vld_end_dtm = ddr.vld_end_dtm)
    INNER JOIN CCCTF.CCC_RIGHT_RIGHTSHOLDER     rr     ON (rgt.rgt_inst = rr.rgt_inst AND rgt.vld_beg_dtm = rr.vld_beg_dtm AND rgt.vld_end_dtm = rr.vld_end_dtm)
    INNER JOIN CCCTF.CCC_PARTY                  pty    ON rr.rgh_inst = pty.pty_inst
    INNER JOIN CCCTF.CCC_AGREEMENT_PRODUCT_USE  apu    ON rgt.apu_inst = apu.apu_inst
    INNER JOIN CCCTF.CCC_PRODUCT_USE_SCHEDULE   psc    ON apu.psc_inst = psc.psc_inst
    INNER JOIN CCCTF.CCC_PRODUCT                prd    ON psc.prd_inst = prd.prd_inst
    INNER JOIN RMS_MIG_AGREEMENTS_V             rma    ON apu.agr_inst = rma.agr_inst
    INNER JOIN CCCTF.CCC_PARTY                  apty   ON rma.pty_inst = apty.pty_inst
  WHERE
    rgt.lcn_inst IS NULL
    AND rgt.wks_inst IS NOT NULL
    AND rgt.wrk_inst IS NULL
    AND (prd.abrv in ('DPS') AND (rgt.tpu_inst IN (204,203,134,133) OR rgt.tps_inst IN (133,134))
     OR prd.abrv in ('TRS') AND (rgt.tpu_inst IN (1) OR rgt.tps_inst IN (3))
     OR prd.abrv in ('ARS') AND (rgt.tpu_inst IN (254) OR rgt.tps_inst IN (144))
);

CREATE OR REPLACE VIEW RMS_MIG_work_rights AS
  SELECT
         rgt.rgt_inst,
         prd.prd_inst,
         prd.abrv,
         rgt.wks_inst rgt_wks_inst,
         rgt.wrk_inst rgt_wrk_inst,
         pty.account_num rgt_account,
         decode(pty.person_org_type,'O',pty.orgname,pty.lname||', '||pty.fname) rh_name,
         ddr.is_rro rh_rro_flag,
         ddr.is_rro_member rh_rro_mem_flag,
         apty.account_num agr_account,
         ddr.agr_holders_name,
         ddr.agr_holder_is_rro agr_holder_rro_flag,
         ddr.agr_holder_is_rro_member agr_holder_rro_member_flag,
         rma.agr_inst,
         rma.agt_inst agr_type_inst,
         rma.agreement_type agr_type,
         rma.start_date agr_start_date,
         rma.end_date agr_end_date,
         rma.effective_date agr_eff_date,
         rma.agr_status_cd,
         rma.cre_dtm agr_cre_dtm,
         rma.cre_user agr_cre_user,
         rma.upd_dtm agr_upd_dtm,
         rma.upd_user agr_upd_user,
         rgt.grant_deny rgt_permission,
         rgt.tps_inst rgt_tps_inst,
         rgt.tpu_inst rgt_tpu_inst,
         rgt.vld_beg_dtm rgt_start_date,
         rgt.vld_end_dtm rgt_end_date,
         rgt.cre_dtm rgt_cre_dtm,
         rgt.cre_user rgt_cre_user,
         rgt.upd_dtm rgt_upd_dtm,
         rgt.upd_user rgt_upd_user,
         rgt.publication_beg_date rgt_pub_beg_date,
         rgt.publication_end_date rgt_pub_end_date,
         rgt.external_comments rgt_term,
         rgq.qualifier_text rgt_rqs,
         DECODE (RGTS_LIMITATION_TPS, 146, 'T', 'F') rgt_reponsive_right_flag,
         ddr.direct_deal rgt_direct_deal_flag,
         wks.setname workset_name,
         wks.wks_type_cd workset_type,
         wks.comments workset_comments
  FROM         CCCTF.CCC_RIGHT                  rgt
    LEFT JOIN  CCCTF.CCC_RIGHT_QUALIFIER        rgq    ON rgt.rgq_inst = rgq.rgq_inst
    LEFT JOIN  CCCTF.CCC_WORK_SET               wks    ON rgt.wks_inst = wks.wks_inst
    INNER JOIN RMS_DIRECT_DEAL_RIGHT_V          ddr    ON (rgt.rgt_inst = ddr.rgt_inst AND rgt.vld_beg_dtm = ddr.vld_beg_dtm AND rgt.vld_end_dtm = ddr.vld_end_dtm)
    INNER JOIN CCCTF.CCC_RIGHT_RIGHTSHOLDER     rr     ON (rgt.rgt_inst = rr.rgt_inst AND rgt.vld_beg_dtm = rr.vld_beg_dtm AND rgt.vld_end_dtm = rr.vld_end_dtm)
    INNER JOIN CCCTF.CCC_PARTY                  pty    ON rr.rgh_inst = pty.pty_inst
    INNER JOIN CCCTF.CCC_AGREEMENT_PRODUCT_USE  apu    ON rgt.apu_inst = apu.apu_inst
    INNER JOIN CCCTF.CCC_PRODUCT_USE_SCHEDULE   psc    ON apu.psc_inst = psc.psc_inst
    INNER JOIN CCCTF.CCC_PRODUCT                prd    ON psc.prd_inst = prd.prd_inst
    INNER JOIN RMS_MIG_AGREEMENTS_V             rma    ON apu.agr_inst = rma.agr_inst
    INNER JOIN CCCTF.CCC_PARTY                  apty   ON rma.pty_inst = apty.pty_inst
  WHERE
    rgt.lcn_inst IS NULL
    AND rgt.wks_inst IS NULL
    AND rgt.wrk_inst IS NOT NULL
    AND (prd.abrv in ('DPS') AND (rgt.tpu_inst IN (204,203,134,133) OR rgt.tps_inst IN (133,134))
     OR prd.abrv in ('TRS') AND (rgt.tpu_inst IN (1) OR rgt.tps_inst IN (3))
     OR prd.abrv in ('ARS') AND (rgt.tpu_inst IN (254) OR rgt.tps_inst IN (144))
);

 /*
  Create a table that associates works and their worksets. Table
  contains the works from TF that are in TF worksets unioned with
  the works from WR that are in RRO collections.
*/
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE RMS_MIG_workset_work';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

create table RMS_MIG_workset_work as
-- first, get a row for each work in a tf workset
  select
    r.rgt_wks_inst,
    to_number(i.idno) wr_wrk_inst,
    rel.vld_beg_dtm ws_wrk_start_date,
    rel.vld_end_dtm ws_wrk_end_date,
    rel.cre_dtm ws_wrk_cre_dtm,
    rel.cre_user ws_wrk_cre_user,
    rel.upd_dtm ws_wrk_upd_dtm,
    rel.upd_user ws_wrk_upd_user
  from (select distinct rgt_wks_inst from RMS_MIG_workset_rights) r
    inner join ccc_wks_wrk_rel rel on r.rgt_wks_inst = rel.wks_inst
    inner join ccc_work_idno   i   on rel.wrk_inst = i.wrk_inst
  where i.type_cd = 'WR_WRK'
    and (sysdate between rel.vld_beg_dtm and rel.vld_end_dtm or sysdate < rel.vld_beg_dtm);

insert /*+ APPEND */ into RMS_MIG_workset_work
-- second, get a row for each work in an rro collection that is not already
-- in the tf workset
select r.rgt_wks_inst,
       cw.wr_wrk_inst,
       cw.cre_dtm ws_wrk_start_date,
       to_date('31-DEC-3000') ws_wrk_end_date,
       cw.cre_dtm ws_wrk_cre_dtm,
       cw.cre_user ws_wrk_cre_user,
       cw.upd_dtm ws_wrk_upd_dtm,
       cw.upd_user ws_wrk_upd_user
from (select distinct rgt_wks_inst from RMS_MIG_workset_rights) r
  inner join rro_coll_worksets        rcw on r.rgt_wks_inst = rcw.secondary_tf_wks_inst
  inner join rms_rro_collection_works cw  on rcw.primary_tf_wks_inst = cw.tf_wks_inst
where
  (r.rgt_wks_inst, cw.wr_wrk_inst) in
    (select r2.rgt_wks_inst, cw2.wr_wrk_inst
     from (select distinct rgt_wks_inst from RMS_MIG_workset_rights) r2
       inner join rro_coll_worksets        rcw2 on r2.rgt_wks_inst = rcw2.secondary_tf_wks_inst
       inner join rms_rro_collection_works cw2  on rcw2.primary_tf_wks_inst = cw2.tf_wks_inst
     minus
       select rgt_wks_inst, wr_wrk_inst from RMS_MIG_workset_work);
commit;

/*
   Create a table that contains all the rights in TF. This
   includes rights defined at the workset level and those defined
   at the work level.
*/
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE RMS_MIG_right_data';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

create table RMS_MIG_right_data as
   SELECT
          NULL wr_wrk_inst, -- these rights at the workset level
          r.agr_inst,
          r.agr_account agr_account,
          r.agr_holders_name,
          ast.brf_dscr agr_status,
          r.agr_start_date,
          r.agr_end_date,
          r.agr_eff_date,
          r.agr_cre_dtm,
          r.agr_cre_user,
          r.agr_upd_dtm,
          r.agr_upd_user,
          tuse.dscr product,
          r.rgt_wks_inst tf_workset,
          r.rgt_account rgt_account,
          r.rh_name rh_name,
          r.rh_rro_flag rh_rro_flag,
          r.rh_rro_mem_flag rh_rro_member,
          g.brf_dscr permission_dscr,
          r.rgt_inst,
          r.rgt_start_date,
          r.rgt_end_date,
          r.rgt_pub_beg_date,
          r.rgt_pub_end_date,
          r.rgt_cre_dtm,
          r.rgt_cre_user,
          r.rgt_upd_dtm,
          r.rgt_upd_user,
          r.rgt_term external_comments,
          r.rgt_rqs right_qualifying_stmt,
          decode(r.prd_inst,3,'PRINT',48,'DIGITAL',80,'DIGITAL','unknown') distribution,
          r.rgt_reponsive_right_flag digital_deny,
          r.rgt_direct_deal_flag direct_deal,
          r.workset_name,
          r.workset_type,
          r.workset_comments,
          'C' term_type,
          r.rgt_tps_inst
   FROM        RMS_MIG_workset_rights r
    INNER JOIN ccc_general_look_up g ON (r.rgt_permission = g.cd AND g.catg = 'GRANT_DENY')
    INNER JOIN ccc_general_look_up ast ON (ast.cd = r.agr_status_cd AND ast.catg ='CCC_AGREEMENT_STATUS')
    LEFT JOIN  ccc_tpus_tpu_rel trel ON trel.tps_inst = r.rgt_tps_inst
    INNER JOIN ccc_type_use tuse ON (tuse.tpu_inst = trel.tpu_inst OR tuse.tpu_inst = r.rgt_tpu_inst)
   WHERE
    (sysdate between r.rgt_start_date and r.rgt_end_date or sysdate < r.rgt_start_date)
   UNION
   SELECT
          to_number(wi.idno) wr_wrk_inst, -- these rights are at the work level
          r.agr_inst,
          r.agr_account agr_account,
          r.agr_holders_name,
          ast.brf_dscr agr_status,
          r.agr_start_date,
          r.agr_end_date,
          r.agr_eff_date,
          r.agr_cre_dtm,
          r.agr_cre_user,
          r.agr_upd_dtm,
          r.agr_upd_user,
          tuse.dscr product,
          r.rgt_wks_inst tf_workset,
          r.rgt_account rgt_account,
          r.rh_name rh_name,
          r.rh_rro_flag rh_rro_flag,
          r.rh_rro_mem_flag rh_rro_member,
          g.brf_dscr permission_dscr,
          r.rgt_inst,
          r.rgt_start_date,
          r.rgt_end_date,
          r.rgt_pub_beg_date,
          r.rgt_pub_end_date,
          r.rgt_cre_dtm,
          r.rgt_cre_user,
          r.rgt_upd_dtm,
          r.rgt_upd_user,
          r.rgt_term external_comments,
          r.rgt_rqs right_qualifying_stmt,
          decode(r.prd_inst,3,'PRINT',48,'DIGITAL',80,'DIGITAL','unknown') distribution,
          r.rgt_reponsive_right_flag digital_deny,
          r.rgt_direct_deal_flag direct_deal,
          r.workset_name,
          r.workset_type,
          r.workset_comments,
          'C' term_type,
          r.rgt_tps_inst
   FROM        RMS_MIG_work_rights r
    INNER JOIN ccc_general_look_up g ON (r.rgt_permission = g.cd AND g.catg = 'GRANT_DENY')
    INNER JOIN ccc_general_look_up ast ON (ast.cd = r.agr_status_cd AND ast.catg ='CCC_AGREEMENT_STATUS')
    INNER JOIN ccc_work_idno wi ON (r.rgt_wrk_inst = wi.wrk_inst AND wi.type_cd = 'WR_WRK')
    LEFT JOIN  ccc_tpus_tpu_rel trel ON trel.tps_inst = r.rgt_tps_inst
    INNER JOIN ccc_type_use tuse ON (tuse.tpu_inst = trel.tpu_inst OR tuse.tpu_inst = r.rgt_tpu_inst)
   WHERE
    (sysdate between r.rgt_start_date and r.rgt_end_date or sysdate < r.rgt_start_date)
;

-- remove whitespace from END of terms
update RMS_MIG_right_data
set external_comments = regexp_replace(external_comments,'\s*$')
where external_comments is not null
and length(external_comments) != nvl(length(regexp_replace(external_comments,'\s*$')),0);
commit;

-- remove whitespace from END of RQS
update RMS_MIG_right_data
set right_qualifying_stmt = regexp_replace(right_qualifying_stmt,'\s*$')
where right_qualifying_stmt is not null
and length(right_qualifying_stmt) != nvl(length(regexp_replace(right_qualifying_stmt,'\s*$')),0);
commit;

-- remove whitespace from START of terms
update RMS_MIG_right_data
set external_comments = regexp_replace(external_comments,'^\s*')
where external_comments is not null
and length(external_comments) != nvl(length(regexp_replace(external_comments,'^\s*')),0);
commit;

-- remove whitespace from START of RQS
update RMS_MIG_right_data
set right_qualifying_stmt = regexp_replace(right_qualifying_stmt,'^\s*')
where right_qualifying_stmt is not null
and length(right_qualifying_stmt) != nvl(length(regexp_replace(right_qualifying_stmt,'^\s*')),0);
commit;

-- set the term type so Standard (S) if term is in the general_look_up table
update RMS_MIG_right_data rd
set term_type = 'S'
where 1 = (select count(*) from ccc_general_look_up
          where dscr = rd.external_comments and catg = 'OVR_RGT_TF');
commit;

/*
  This view is a union of the workset level rights (tf_workset is not null)
  and the work level rights (wr_wrk_inst is not null).
  For the workset level rights, we join to the rms_workset_work to get the
  works in the workset. No need to do this for the work level rights since the
  wr_wrk_inst is on the right record already

  ensure we don't pick up Public Domain or CR
*/
create or replace view rms_mig_work_right_data_v as
   SELECT
          ww.wr_wrk_inst,
          ww.ws_wrk_start_date,
          ww.ws_wrk_end_date,
          ww.ws_wrk_cre_dtm,
          ww.ws_wrk_cre_user,
          ww.ws_wrk_upd_dtm,
          ww.ws_wrk_upd_user,
          agr_inst,
          agr_account,
          agr_holders_name,
          agr_status,
          agr_start_date,
          agr_end_date,
          agr_eff_date,
          agr_cre_dtm,
          agr_cre_user,
          agr_upd_dtm,
          agr_upd_user,
          product,
          tf_workset,
          rgt_account,
          rh_name,
          rh_rro_flag,
          rh_rro_member,
          permission_dscr,
          rgt_inst,
          rgt_start_date,
          rgt_end_date,
          rgt_pub_beg_date,
          rgt_pub_end_date,
          rgt_cre_dtm,
          rgt_cre_user,
          rgt_upd_dtm,
          rgt_upd_user,
          external_comments,
          right_qualifying_stmt,
          distribution,
          digital_deny,
          direct_deal,
          d2.workset_name,
          d2.workset_type,
          d2.workset_comments,
          D2.term_type,
          rgt_tps_inst
   FROM RMS_MIG_right_data d2
    INNER JOIN RMS_MIG_workset_work ww ON d2.tf_workset = ww.rgt_wks_inst
   WHERE
    d2.tf_workset is not null
    AND d2.wr_wrk_inst is null
    AND ww.ws_wrk_end_date > ww.ws_wrk_start_date
    AND permission_dscr in ('Grant','Deny', 'Contact Rightsholder', 'Contact Rightsholder Directly') --ensure we don't pick up Public Domain or CR
   UNION ALL
   SELECT
          wr_wrk_inst,
          null ws_work_start_date,
          null ws_work_end_date,
          null ws_work_cre_dtm,
          null ws_work_cre_user,
          null ws_work_upd_dtm,
          null ws_work_upd_user,
          agr_inst,
          agr_account,
          agr_holders_name,
          agr_status,
          agr_start_date,
          agr_end_date,
          agr_eff_date,
          agr_cre_dtm,
          agr_cre_user,
          agr_upd_dtm,
          agr_upd_user,
          product,
          tf_workset,
          rgt_account,
          rh_name,
          rh_rro_flag,
          rh_rro_member,
          permission_dscr,
          rgt_inst,
          rgt_start_date,
          rgt_end_date,
          rgt_pub_beg_date,
          rgt_pub_end_date,
          rgt_cre_dtm,
          rgt_cre_user,
          rgt_upd_dtm,
          rgt_upd_user,
          external_comments,
          right_qualifying_stmt,
          distribution,
          digital_deny,
          direct_deal,
          d3.workset_name,
          d3.workset_type,
          d3.workset_comments,
          D3.term_type,
          rgt_tps_inst
   FROM RMS_MIG_right_data d3
   WHERE
    d3.tf_workset is null
    AND d3.wr_wrk_inst is not null
    AND permission_dscr in ('Grant','Deny', 'Contact Rightsholder', 'Contact Rightsholder Directly');

commit;