CREATE OR REPLACE PROCEDURE PRC_INS(P_ID IN NUMBER,P_STAT OUT NUMBER) IS
  TYPE T_TEST_TAB IS TABLE OF T_DTLS%ROWTYPE;
  	V_PARAM 	 		T_TEST_TAB;
  	V_STATUS   		NUMBER;
  	V_BUS 		    VARCHAR2(20); 
  	V_UP          VARCHAR2(1);
  	V_Q	        VARCHAR2(50); 
   CURSOR C1 IS SELECT S_NO,MEM_ID,MEM_TITLE,MEM_FNAME,MEM_LNAME,F_DATE,N_DATE,MEM_RCODE,MEM_GEN_CODE,
                          FROM T_MEMBER_STAT  WHERE UPL_ID= P_ID AND FLAG='Y' AND MEM_ID IS NULL;
   CURSOR C2 IS SELECT S_NO,MEM_ID,MEM_TITLE,MEM_FNAME,MEM_LNAME,F_DATE,N_DATE,MEM_RCODE,MEM_GEN_CODE,
                          FROM T_MEMBER_STAT  WHERE UPL_ID= P_ID AND FLAG='Y' AND MEM_ID IS NOT NULL;
BEGIN
V_STATUS:=P_STAT;
 SELECT BUS_TYPE,UP_TYPE,QUOTE_EN_POL INTO V_BUS,V_UP,V_Q FROM T_PROCESS WHERE PROC_ID=P_ID;
  IF V_UP='NB' OR V_UP='EN' THEN
   OPEN C1;
      LOOP
         FETCH C1 BULK COLLECT INTO V_PARAM LIMIT 10000;
           EXIT WHEN C1%NOTFOUND;
               FORALL i in 1..V_PARAM.COUNT
                  -----INSERTING THE TABLE RECORDS FOR ENDO----
                  INSERT INTO T_DTLS VALUES V_PARAM(i);
       END LOOP;
       COMMIT;
   CLOSE C1;
 	   P_STAT:=0;
 	 ELSE V_UP='EN' 
 	       UPDATE T_MEMBER_STAT SET MEM_STAT ='1' WHERE UPL_ID=P_ID AND TYPE_OF='DEL';
 	  OPEN C2;
	    LOOP
	      FETCH C2 BULK COLLECT INTO V_PARAM LIMIT 10000;
	        EXIT WHEN C2%NOTFOUND;
	             FORALL i in 1..V_PARAM.COUNT
	                    -----INSERTING THE TABLE RECORDS FOR ENDO----
	              UPDATE T_DTLS SET   (NEEDED FOR MULTIPLE COLUMN VALUE UPDATES)
	               WHERE SR_NO=V_PARAM(i);
       END LOOP;
  COMMIT;
	 CLOSE C2;
	 P_STAT:=0;
END IF;
EXCEPTION
WHEN OTHERS THEN
	P_STAT:=-1;
END PRC_INS;
/

UPDATE T_DTLS SET ROW=V_PARAM(I) WHERE SR_NO=V_PARAM(I).SRNO;