PROCEDURE DO_NORMALISE (p_batch_date IN DATE)
IS
 v_partkey number := to_number(to_char(p_batch_date,'YYYYMMDD'));
BEGIN

INSERT into GTT_AC_NORMAL( SUB_GRP_NAME, SPID_NAME,MSG_ID, SUCCESS_COUNT, FAIL_COUNT, VISITED_MCC)                           
(select
    SUB_GRP_NAME,
    (select 
          NVL (SUB_GRP_TO_NAME.SPID_NAME, 'Unknown SPID')
          from AC_LOAD
              inner join SUB_GRP_TO_NAME 
              on (SUB_GRP_TO_NAME.SUB_GRP_NAME = NVL(AC_LOAD.SUB_GRP_NAME, '9999') )
          )SPID_NAME,  
    AL.MSG_ID,
    case when (AL.SEND_STATUS = '1') then 1 
          when (AL.SEND_STATUS = '0') then 0
    end SUCCESS_COUNT,
    case when (AL.SEND_STATUS = '1') then 0 
         when (AL.SEND_STATUS = '0') then 1
    end FAIL_COUNT,
    MC1.MCC_NAME as VISITED_MCC 
from AC_LOAD AL, 
MCC_TO_NAME MC1


where (SEND_STATUS is not null 
and PARTKEY          = V_PARTKEY
and BATCH_DATE      = P_BATCH_DATE
and AL.VISITED_MCC = MC1.MCC
) and MSG_ID <> 'Warning'
);

END;

Table examples:
AC_LOAD
PARTKEY	  BATCH_DATE	        SUB_GRP_NAME	SEND_STATUS	MSG_ID	VISITED_MCC	
20140704  04/JUL/14 01:45:00	0156	        0	        pp_msg1	91	
20140704  04/JUL/14 01:45:00	0123	        1	        mp_msg2	125	  
20140704  04/JUL/14 01:45:00	(null)	        0	        ep_msg1	36	
20140704  04/JUL/14 01:45:00	2121	        1	        pp_msg2	47	

SUB_GRP_TO_NAME
SUB_GRP_NAME	SPID_NAME
0156	        Bobby
0123	        Barry
9999	        Older

What I would like to see in GTT_AC__NORMAL

PARTKEY	  BATCH_DATE	        SUB_GRP_NAME	SEND_STATUS	MSG_ID	VISITED_MCC	
20140704  04/JUL/14 01:45:00	Bobby	        0	        pp_msg1	New York	
20140704  04/JUL/14 01:45:00	Barry	        1	        mp_msg2	Boston	
20140704  04/JUL/14 01:45:00	Older	        0	        ep_msg1	Paris	
20140704  04/JUL/14 01:45:00	Unknown	        1	        pp_msg2	Melbourne
	

SQL> select nvl(null,'Michel') val from dual where 1=1;
VAL
------
Michel

1 row selected.

SQL> select nvl(null,'Michel') val from dual where 1=2;

no rows selected

from AC_LOAD AL, 
MCC_TO_NAME MC1, 

SUB_GRP_TO_NAME
outer join AC_LOAD
on (SUB_GRP_TO_NAME.SUB_GRP_NAME = NVL(AC_LOAD.SUB_GRP_NAME, '9999'))

where AL.SEND_STATUS is not null 
and AL.PARTKEY          = V_PARTKEY
and AL.BATCH_DATE      = P_BATCH_DATE
and AL.VISITED_MCC = MC1.MCC
and AL.MSG_ID <> 'Wholesale_Warning'
)

SUB_GRP_TO_NAME
outer join AC_LOAD

from 
MCC_TO_NAME MC1, 
SUB_GRP_TO_NAME
right inner join AC_LOAD
on (SUB_GRP_TO_NAME.SUB_GRP_NAME = NVL(AC_LOAD.SUB_GRP_NAME, '9999') )

SQL> select * from dual d1 outer join dual d2 on d1.dummy = d2.dummy;
select * from dual d1 outer join dual d2 on d1.dummy = d2.dummy
                      *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select * from dual d1 right inner join dual d2 on d1.dummy = d2.dummy;
select * from dual d1 right inner join dual d2 on d1.dummy = d2.dummy
                            *
ERROR at line 1:
ORA-00905: missing keyword