INSERT INTO temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3  ) leading ( vd1 ,vd2 ,vd3  , tvd) */ vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' 
FROM vendor_data vd1,
vendor_data vd2,
vendor_data vd3,
     (SELECT  rownumber,
              MAX (DECODE (control_column_seq_no, 92004, original_value, NULL)) AS value1,
              MAX (DECODE (control_column_seq_no, 92000, original_value, NULL)) AS value2,
              MAX (DECODE (control_column_seq_no, 91999, original_value, NULL)) AS value3
      FROM (SELECT /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) FULL (vdt) */ rownumber,
                  VALUE AS original_value,control_column_seq_no
             FROM tst_temp_vendor_data_bkp vdt
            WHERE vdt.KEY = 'Y'
              AND error_flag IS NULL) temp 
      GROUP BY rownumber) tvd,
      tst_temp_vendor_record_bkp tvr 
WHERE vd1.study_seq_no = 25707
  AND vd1.control_column_seq_no = 92004
  AND tvd.value1 = vd1.original_value
  AND vd2.study_seq_no = 25707
  AND vd2.control_column_seq_no = 92000
  AND tvd.value2 = vd2.original_value
  AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  AND vd3.study_seq_no = 25707
  AND vd3.control_column_seq_no = 91999
  AND tvd.value3 = vd3.original_value
  AND vd1.vendor_record_seq_no = vd3.vendor_record_seq_no
  AND tvr.error_flag IS NULL
  AND tvr.rownumber = tvd.rownumber

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |                            |       |       | 65612 (100)|          |       |       |
|*  1 |  HASH JOIN                |                            |     1 |  6121 | 65612   (1)| 00:13:08 |       |       |
|*  2 |   HASH JOIN               |                            |     1 |  6091 | 65467   (1)| 00:13:06 |       |       |
|   3 |    NESTED LOOPS           |                            | 10808 |   759K| 64520   (1)| 00:12:55 |       |       |
|   4 |     NESTED LOOPS          |                            | 10759 |   504K| 32224   (1)| 00:06:27 |       |       |
|   5 |      PARTITION HASH SINGLE|                            | 10711 |   251K|    73   (0)| 00:00:01 |    96 |    96 |
|*  6 |       INDEX RANGE SCAN    | VENDOR_DATA_IDX04          | 10711 |   251K|    73   (0)| 00:00:01 |    96 |    96 |
|   7 |      PARTITION HASH SINGLE|                            |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|*  8 |       INDEX RANGE SCAN    | VENDOR_DATA_IDX04          |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|   9 |     PARTITION HASH SINGLE |                            |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|* 10 |      INDEX RANGE SCAN     | VENDOR_DATA_IDX04          |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|  11 |    VIEW                   |                            | 58053 |   333M|   947   (3)| 00:00:12 |       |       |
|  12 |     SORT GROUP BY         |                            | 58053 |   112M|   947   (3)| 00:00:12 |       |       |
|* 13 |      TABLE ACCESS FULL    | TST_TEMP_VENDOR_DATA_BKP   | 58053 |   112M|   943   (3)| 00:00:12 |       |       |
|* 14 |   TABLE ACCESS FULL       | TST_TEMP_VENDOR_RECORD_BKP | 20731 |   607K|   144   (1)| 00:00:02 |       |       |
------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT            |                            |       |       |  1967 (100)|          |       |       |
|*  1 |  FILTER                     |                            |       |       |            |          |       |       |
|   2 |   SORT GROUP BY             |                            |     1 |  8233 |  1967   (3)| 00:00:24 |       |       |
|*  3 |    HASH JOIN                |                            |     1 |  8233 |  1966   (3)| 00:00:24 |       |       |
|   4 |     MERGE JOIN CARTESIAN    |                            |     1 |  8191 |  1767   (3)| 00:00:22 |       |       |
|   5 |      NESTED LOOPS           |                            |     1 |  6159 |     0   (0)|          |       |       |
|   6 |       NESTED LOOPS          |                            |     1 |  4106 |     0   (0)|          |       |       |
|   7 |        PARTITION HASH SINGLE|                            |     1 |  2053 |     0   (0)|          |    67 |    67 |
|*  8 |         INDEX RANGE SCAN    | VENDOR_DATA_IDX04          |     1 |  2053 |     0   (0)|          |    67 |    67 |
|   9 |        PARTITION HASH SINGLE|                            |     1 |  2053 |     0   (0)|          |    67 |    67 |
|* 10 |         INDEX RANGE SCAN    | VENDOR_DATA_IDX04          |     1 |  2053 |     0   (0)|          |    67 |    67 |
|  11 |       PARTITION HASH SINGLE |                            |     1 |  2053 |     0   (0)|          |    67 |    67 |
|* 12 |        INDEX RANGE SCAN     | VENDOR_DATA_IDX04          |     1 |  2053 |     0   (0)|          |    67 |    67 |
|  13 |      BUFFER SORT            |                            | 59929 |   116M|  1767   (3)| 00:00:22 |       |       |
|* 14 |       TABLE ACCESS FULL     | TST_TEMP_VENDOR_DATA_BKP   | 59929 |   116M|  1767   (3)| 00:00:22 |       |       |
|* 15 |     TABLE ACCESS FULL       | TST_TEMP_VENDOR_RECORD_BKP | 22130 |   907K|   199   (1)| 00:00:03 |       |       |
--------------------------------------------------------------------------------------------------------------------------

WHERE vd1.study_seq_no = 25707 --changed value here
  AND vd1.control_column_seq_no = 92004 --changed value here
  AND tvd.value1 = vd1.original_value
  AND vd2.study_seq_no = 25707 --changed value here
  AND vd2.control_column_seq_no = 92000 --changed value here
  AND tvd.value2 = vd2.original_value
  AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  AND vd3.study_seq_no = 25707 --changed value here
  AND vd3.control_column_seq_no = 91999 --changed value here
  AND tvd.value3 = vd3.original_value
  AND vd1.vendor_record_seq_no = vd3.vendor_record_seq_no
  AND tvr.error_flag IS NULL
  AND tvr.rownumber = tvd.rownumber

Views 

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable. 

In the following example the select contains an inline view which cannot be merged: 

SQL> explain plan for
select ename,tot
from emp,
    (select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno; 

Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
  HASH JOIN
    TABLE ACCESS FULL EMP [ANALYZED]
    VIEW
      SORT GROUP BY
        INDEX FULL SCAN BE_IX 

In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step.