Untitled

WITH v0 AS
(SELECT owner,
         NAME,
         text,
         instr(text,
               '''',
               1,
               1) AS q1,
         instr(text,
               '''',
               1,
               2) AS q2,
         instr(text,
               '''',
               1,
               3) AS q3,
         instr(text,
               '''',
               1,
               4) AS q4,
         instr(lower(text),
               ':new.',
               1,
               1) AS n
    FROM dba_source
   WHERE TYPE = 'TRIGGER'
     AND lower(text) LIKE '%primary_key_lookup%'
     AND lower(text) NOT LIKE '%ref_db_link%'
     AND lower(text) NOT LIKE '%fi_replicate%'),
v1 AS
(SELECT owner,
         NAME,
         text,
         upper(substr(text,
                      q1 + 1,
                      q2 - q1 - 1)) AS pky_tbl_tmp,
         upper(substr(text,
                      q3 + 1,
                      q4 - q3 - 1)) AS pky_col,
         upper(substr(text,
                      n + 5,
                      instr(text,
                            ')',
                            n,
                            1) - n - 5)) AS fky_col_tmp
    FROM v0),
v2 AS
(SELECT v1.*,
         CASE
           WHEN substr(pky_tbl_tmp,
                       1,
                       8) = 'REFDATA.' THEN
            substr(pky_tbl_tmp,
                   9)
           WHEN substr(pky_tbl_tmp,
                       1,
                       5) = 'NSRD.' THEN
            substr(pky_tbl_tmp,
                   6)
           ELSE
            pky_tbl_tmp
         END AS pky_tbl_tmp2,
         CASE
           WHEN fky_col_tmp = 'LAST_MODIFIED_BY,TRUE' THEN
            'LAST_MODIFIED_BY'
           ELSE
            fky_col_tmp
         END AS fky_col
    FROM v1),
v3 AS
(SELECT v2.*,
         CASE
           WHEN pky_tbl_tmp2 = 'VEW_USER' THEN
            'USER_TBL'
           WHEN pky_tbl_tmp2 = 'VEW_FUND' THEN
            'FUND'
           WHEN pky_tbl_tmp2 = 'VEW_ACCOUNT' THEN
            'ACCT'
           WHEN pky_tbl_tmp2 = 'VEW_COUNTRY' THEN
            'COUNTRY'
           WHEN pky_tbl_tmp2 = 'VEW_CURRENCY' THEN
            'CURRENCY'
           WHEN pky_tbl_tmp2 = 'VEW_COMPANY' THEN
            'ONYX_COMPANY_TBL'
           ELSE
            pky_tbl_tmp2
         END AS pky_tbl
    FROM v2)
SELECT v3.*,
       t.table_name,
       t.status
  FROM v3
INNER JOIN dba_triggers t
    ON t.owner = v3.owner
   AND t.trigger_name = v3.name
   AND NOT EXISTS (SELECT NULL
          FROM dba_cons_columns cc
         INNER JOIN dba_constraints c
            ON c.owner = cc.owner
           AND c.constraint_type = 'R'
           AND c.table_name = cc.table_name
         WHERE cc.owner = v3.owner
           AND cc.table_name = t.table_name
           AND cc.column_name = v3.fky_col)