procedure SP_EDIT_FN_GROUP(
  PI_ARW_FN_GRP_ID IN NUMBER,
 PI_FN_GRP_NAME IN VARCHAR2,
 PI_FN_GRP_DESC IN VARCHAR2,
 PI_ACTIVE IN VARCHAR2,
  PI_RPTG_OBJ_ID IN NUMBER,
 PI_FN_GRP_DETAILS IN FN_GRP_COLL,
 PI_USER IN VARCHAR2,
 x_return_code OUT NOCOPY NUMBER,
 x_return_msg OUT NOCOPY  VARCHAR2
 )
IS
L_ARW_FN_GRP_ID number;
L_ARW_PGM_OBJ_ID number;

BEGIN 
FOR I IN PI_FN_GRP_DETAILS.FIRST..PI_FN_GRP_DETAILS.LAST
LOOP

  
 MERGE INTO  ARW_RUN_FUNCTIONAL_GROUP A
	USING (select distinct ARW_FN_GRP_ID  from ARW_RUN_FUNCTIONAL_GROUP where PI_ARW_FN_GRP_ID=ARW_FN_GRP_ID) b--(SELECT ARW_FN_GRP_ID,ARW_PGM_OBJ_ID,EXECUTION_ORDER FROM ARW_RUN_FUNCTIONAL_GROUP where ARW_FN_GRP_ID=PI_ARW_FN_GRP_ID ) B
	ON  (A.ARW_FN_GRP_ID=b.ARW_FN_GRP_ID AND A.ARW_PGM_OBJ_ID=PI_FN_GRP_DETAILS(i).ARW_PGM_OBJ_ID)
	WHEN MATCHED THEN 
			UPDATE  SET
							ARW_FN_GRP_NAME    = PI_FN_GRP_NAME,
							ARW_FN_GRP_DESC_TXT= PI_FN_GRP_DESC,
							EXECUTION_ORDER    = PI_FN_GRP_DETAILS(I).EXECUTION_ORDER,
							ACTIVE             = PI_ACTIVE,
							MODIFIED_BY        = PI_USER,
							MODIFIED_ON        = SYSTIMESTAMP
	                    WHERE 	ARW_FN_GRP_ID=	PI_ARW_FN_GRP_ID
                         

	WHEN NOT MATCHED THEN		
        
	        INSERT   (ARW_FN_GRP_ID, 
											ARW_PGM_OBJ_ID     ,
											PGM_COMPANY_ID,
											PGM_LSH_OBJ_ID     ,
											PGM_LSH_OBJ_VER    ,
											ARW_FN_GRP_NAME    ,
											ARW_FN_GRP_DESC_TXT,
											EXECUTION_ORDER    ,
											ACTIVE             ,
											CREATED_BY         ,
											CREATED_ON         ,
											MODIFIED_BY        ,
											MODIFIED_ON        )
											VALUES
											(PI_ARW_FN_GRP_ID,
											 PI_FN_GRP_DETAILS(I).ARW_PGM_OBJ_ID,
											 PI_FN_GRP_DETAILS(I).PGM_COMPANY_ID,
											 PI_FN_GRP_DETAILS(I).PGM_LSH_OBJ_ID,
											 PI_FN_GRP_DETAILS(I).PGM_LSH_OBJ_VER,
											 PI_FN_GRP_NAME,
											 PI_FN_GRP_DESC,
											 PI_FN_GRP_DETAILS(I).EXECUTION_ORDER,
											 PI_ACTIVE,
											 PI_USER,
											 SYSTIMESTAMP,
											 PI_USER,
											 SYSTIMESTAMP);
                       
          END LOOP;
          
                        

x_return_code:=0;
 x_return_msg:='Functional Group '||PI_FN_GRP_NAME||' details has been modified successfully';
	
EXCEPTION
  
WHEN NO_dATA_FOUND THEN 
 x_return_code := 1;
  x_return_msg  := 'no data found from insert query.'; 
  DBMS_OUTPUT.PUT_LINE('Return Code:= '||x_return_code||' Return Msg:='||x_return_msg);
  
 WHEN OTHERS THEN
  x_return_code := 1;
  x_return_msg  := 'OTHER ERROR'||SQLERRM||' '||SQLCODE; 
  DBMS_OUTPUT.PUT_LINE('Return Code:= '||x_return_code||' Return Msg:='||x_return_msg);	
					
END SP_EDIT_FN_GROUP;

SET SERVEROUTPUT ON;
DECLARE
l_ret_code varchar2(1);
l_ret_status varchar2(4000);
BEGIN
PKG_BATCH_RUN_MACHINE.SP_EDIT_FN_GROUP
(
   PI_ARW_FN_GRP_ID   =>7,
   PI_FN_GRP_NAME     =>'FN_GROUP_3',
   PI_FN_GRP_DESC 	  =>'OUR Third FUNCTIONAL GROUP111',
   PI_ACTIVE 			    =>'Y',
   PI_FN_GRP_DETAILS  =>FN_GRP_COLL(fn_grp_type(7,11086,724,646744418,3)
                                      ,fn_grp_type(8,11756,724,646744318,1)
                                      ,fn_grp_type(10,11374,724,646747882,1)
                                      ,fn_grp_type(9,12045,724,646751937,2)
                                  ),
  PI_RPTG_OBJ_ID      =>  10752,                                
  PI_USER			        =>'ARW',
  x_return_code 	   	=>l_ret_code,
  x_return_msg 		    =>l_ret_status
 );
 DBMS_OUTPUT.PUT_LINE('Return Code:= '||l_ret_code||' Return Msg:='||l_ret_status);
 
 END;
 /
 SHOW ERRORS