ORACLE: INSERT SELECT FROM 2 views and value from param

Question

I'm trying to insert some fields into MYTABLE from views MYVIEW1 and MYVIEW2 and then add a value from a parameter (this is part of a stored procedure) for UPDATED_BY, SYSDATE for UPDATED_ON. How can I correctly do this with INSERT SELECT or some other way entirely?

MYVIEW1    
+------+----+-----+-----------+---------+
|   YR | MO | QTR | USER_CODE | MO_PERF |
+------+----+-----+-----------+---------+
| 2012 |  1 |   1 |      1099 |      89 | 
| 2012 |  2 |   1 |      1099 |      86 |
| 2012 |  3 |   1 |      1099 |      95 |
+------+----+-----+-----------+---------+
MYVIEW2    
+------+-----+-----------+----------+
|   YR | QTR | USER_CODE | QTR_PERF |
+------+-----+-----------+----------+
| 2012 |   1 |      1099 |       90 | 
+------+-----+-----------+----------+

MYTABLE
+------+-----+-----------+---------+---------+---------+---------+-------------+------------+
|   YR | QTR | USER_CODE | MO1_PCT | MO2_PCT | MO3_PCT |     INC |  UPDATED_BY | UPDATED_ON |
+------+-----+-----------+---------+---------+---------+---------+-------------+------------+
| 2012 |   1 |      1099 |      89 |      86 |      95 |    7000 | SAMPLE NAME | 01/16/2013 |
+------+-----+-----------+---------+---------+---------+---------+-------------+------------+

INSERT INTO MYTABLE
 (YR,QTR,USER_CODE,MO1_PCT,MO2_PCT,MO3_PCT,INC,UPDATED_BY,UPDATED_ON)
    SELECT b.YR,b.QTR,b.USER_CODE,b.MO1_PCT,b.MO2_PCT,b.MO3_PCT,c.INC
    FROM MYVIEW1 b,
         MYVIEW2 c

How do I insert values for (first month of QTR's MO_PERF) as MO1_PCT and (second month of QTR's MO_PERF) as MO2_PCT and (last month of QTR's MO_PERF) as MO3_PCT, making sure that I've inserted the right month within the right quarter and year.And then check if the MO_PERF values of each month has reached at least 85, else set INC as NULL.

,CASE WHEN MO1_PCT>=85 AND MO2_PCT>=85 AND MO3_PCT>=85 THEN 7000
ELSE NULL 
END INC

Answer 1

If you're using oracle 11g then you can use PIVOT like this:

select YR, QTR, USER_CODE, "1_MO_PCT" MO1_PCT, "2_MO_PCT" MO2_PCT, "3_MO_PCT" MO3_PCT ,
case when "1_MO_PCT" >= 85 and "2_MO_PCT" >= 85 and "2_MO_PCT" >= 85 then 7000 end INC,
user updated_by, sysdate  updated_on
from (
select m1.yr, m1.mo, m1.qtr, m1.user_code, m1.mo_perf, m2.qtr_perf
from myview1 m1 join myview2 m2 on m1.yr=m2.yr 
and m1.qtr = m2.qtr and m1.user_code = m2.user_code )t
pivot(
  max(mo_perf) MO_PCT  for mo in (1,2,3)
  )

Here is a sqlfiddle demo