THEN SQL_statement := 'INSERT INTO IR_TEAM_MEMBER_MAP_2 ' ||
                                                'VALUES ( '
                                                          || IB_2_row.IR_ID || ', ' 
                                                          || IB_2_row.COUNTRY || ', '
                                                          || IB_2_row.IRTEAM || ', '
                                                          || IB_2_row.IRTEAM_MEMBER ||', '
                                                          || IB_2_row.IRTEAM_ALLOCATION  
                                                          ||', :head_cnt ) '  ;

CREATE OR REPLACE
	PROCEDURE IRleadsApportioning AS
	
    --TYPE NumList IS TABLE OF NUMBER;		  
	leads number;
	N number;
	N2 number;			  			  
	duplicate integer := -1;
    nl NumList := NumList(0) ; -- := NumList(1,3,5,7);
    nl_2 NumList;
	workload NUMBER;
    sales_agent_id IB_TECH_APPS_TEMPLATE.IRTEAM_MEMBER%TYPE;	
    inserted INTEGER := 0;
    random_no number := 0;
    index_I number;
    FSC IB_TECH_APPS.FOLLOWUP_SALES_CHANNEL%TYPE;
    IR_C number; 
    head_cnt number;
    
    
   -- FUNCTION EXISTED (RAND_NO number, n NumList)
   --    RETURN BOOLEAN;


    
    type curtype is ref cursor;
    
    cur_2 curtype;
    cur_1 curtype;
    
    sumry_row IB_TECH_APPS_SUMMARY%ROWTYPE;
    IB_2_row IR_TEAM_MEMBER_MAP%ROWTYPE;
    
    subtype sqltype is varchar2(1000);
    SQL_Statement sqltype; 
        
        			  
	CURSOR IB 
		 IS 
		   SELECT a.* FROM IB_TECH_APPS a;

			
 	CURSOR IB_2 (IR_TEAM_NAME IN CHAR)
		  IS
		  
        SELECT * FROM (
            SELECT a.*, rownum AS ROW_NO FROM IR_TEAM_MEMBER_MAP a 
            WHERE IRTEAM = IR_TEAM_NAME
            ) ;
            

    CURSOR IB_3 (sales_agent_id IN VARCHAR2)
        IS
        
        SELECT COUNT(*) FROM IB_TECH_APPS_TEMPLATE 
        WHERE TRIM(IRTEAM_MEMBER) = sales_agent_id
        AND random_no != 0 
        AND random_no IS NOT NULL;
    
    CURSOR IB_4 (FSC IN VARCHAR2)
        IS
        
        SELECT COUNT(*) FROM IR_TEAM_MEMBER_MAP_2 
        WHERE IRTEAM = FSC
        AND HEAD_CNT != 0; 
        

BEGIN

	 FOR IB_leads in IB LOOP
	 	 
         FSC := IB_leads.FOLLOWUP_SALES_CHANNEL;
        
        DBMS_OUTPUT.PUT_LINE ( 'FSC = ' || FSC );
     
        OPEN cur_2 for SELECT * FROM IB_TECH_APPS_SUMMARY
                            WHERE IRTEAM = FSC;
        
        LOOP 
--            FETCH IB_2 INTO FSC;
--            FSC:= IB_2.IRTEAM;

         FETCH cur_2 INTO  sumry_row;
         EXIT WHEN cur_2%NOTFOUND; 

         N := sumry_row.IR_COUNTS; 
         DBMS_OUTPUT.PUT_LINE ( 'N = ' || N );
         --N  := IB_2.IR_COUNTS;
        
         --CLOSE cur_2;
         
        /* 2 scenarios: 
        leads <= IR_TEAM_MEMBERS 
        
        use draw_wo_replacement to randomize drawing  
        
        leads > IR_TEAM_MEMBERS (n) 
    
        #_of_leads mod n 
        */
        
        /*<<draw_wo_replacement>> */         
         WHILE (duplicate = 1 OR duplicate = -1) 
		 LOOP
               SELECT rand_gen (N) INTO N2 FROM dual; 
               
               IF EXISTED(N2, nl)    
			      THEN  Duplicate := 1;
			   ELSE nl.extend;
					FOR i IN nl.FIRST .. nl.LAST
                    LOOP
                        IF nl(i) IS NULL THEN
                            nl(i) := N2;
                        END IF;
                        index_I := i;
                    END LOOP;    
    		   END IF;    

                    /*               
                        sales_agent_id := TRIM(IB_leads.IRTEAM_MEMBER) ;
                        DBMS_OUTPUT.PUT_LINE ( 'sales_agent_id = ' || sales_agent_id );
                        OPEN IB_3 (sales_agent_id);
                            FETCH  IB_3 INTO INSERTED;     
                     
                     
                            DBMS_OUTPUT.PUT_LINE( 'sumry_row.IR_COUNTS = ' || sumry_row.IR_COUNTS );
                                                
                            workload := ROUND( sumry_row.IR_COUNTS * IB_leads.IRTEAM_ALLOCATION / 100) ;         
        
                        CLOSE IB_3;
                      */
        
                        OPEN IB_4 (FSC);
                            FETCH  IB_4 INTO INSERTED;
                            EXIT WHEN IB_4%NOTFOUND;
                        CLOSE IB_4;
                       -- IF IB_4%NOTFOUND THEN INSERTED := 0;
                        --END IF;                                                    
                        
                        
                        OPEN cur_1 for  SELECT IR_ID, COUNTRY, IRTEAM, IRTEAM_MEMBER, 
                                               IRTEAM_ALLOCATION, HEAD_CNT
                                         FROM (
                                            SELECT a.*, rownum AS ROW_NO FROM IR_TEAM_MEMBER_MAP a 
                                            WHERE IRTEAM = FSC
                                        )  WHERE ROW_NO = inserted + 1;
                        
             
                        
                        FETCH cur_1 INTO IB_2_row;
                        EXIT WHEN cur_1%NOTFOUND; 
                        

                        DBMS_OUTPUT.PUT_LINE( 'sumry_row.IR_COUNTS = ' || sumry_row.IR_COUNTS );
                                                
                        workload := ROUND( sumry_row.IR_COUNTS * IB_2_row.IRTEAM_ALLOCATION / 100) ;   
                        
		                IF (workload > inserted AND N2 IS NOT NULL AND IB_2_row.IR_ID IS NOT NULL) 
                        
                        THEN SQL_statement := 'INSERT INTO IR_TEAM_MEMBER_MAP_2 ' ||
                                                'VALUES ( '
                                                          || IB_2_row.IR_ID || ', ' 
                                                          || IB_2_row.COUNTRY || ', '
                                                          || IB_2_row.IRTEAM || ', '
                                                          || IB_2_row.IRTEAM_MEMBER ||', '
                                                          || IB_2_row.IRTEAM_ALLOCATION  
                                                          ||', :head_cnt ) '  ;
                                                          
                        --N2 := SELECT NVL(N2) FROM DUAL;                              
                        DBMS_OUTPUT.PUT_LINE ( 'N2 = ' || N2 );
                        execute immediate SQL_statement using N2;
                        
                         
				        dbms_output.put_line('random_no ' || random_no || ' inserted');
				        COMMIT;
				 
                       --inserted := inserted + 1;
				 
                        --ELSE GOTO <<draw_wo_replacement>>         
		                ELSE 
                             nl.delete(index_I);
                        
                             duplicate := 1;
                            /* to get back to drawing */
		                END IF;
                        
                        CLOSE cur_1;
         END LOOP;
         
                  
		 nl.DELETE;
         END LOOP;
         CLOSE cur_2;    
                 --nl := nl_2;
                 --dbms_output.put_line(nl.next(Index_I));

	 	 
	 END LOOP; 

END;