SELECT COUNT (a.sci_las_sec_id) lmtcnt, a.sci_las_sec_id
FROM cms_limit_security_map a,
(SELECT *
FROM  cms_limit_security_map
WHERE sci_las_lmt_id = recs(i).lmt_id
AND   update_status_ind != 'D') b
WHERE a.sci_las_sec_id =b.sci_las_sec_id 				and
a.cms_collateral_id=b.cms_collateral_id 				and
a.sci_las_llp_id=b.sci_las_llp_id 						and
--a.charge_id = b.charge_id 								and
b.update_status_ind != 'D'
GROUP BY a.sci_las_sec_id
ORDER BY lmtcnt, a.sci_las_sec_id

lmtcnt  sci_las_sec_id
-----   ------------
11	23204574
17	23161738

create bitmap index cms_lmt_sec_bmap_indx on cms_limit_security_map (sci_las_sec_id,sci_las_lmt_id,update_status_ind)


create bitmap index cms_lmt_sec_bmap_indx1 on cms_limit_security_map (update_status_ind,sci_las_lmt_id)

CREATE GLOBAL TEMPORARY TABLE CMS_SECURITY_MOD
(
  CMS_COLLATERAL_ID    NUMBER(19),
  SCI_SECURITY_DTL_ID  NUMBER(10),
  BAL_CMV              NUMBER(30,2)
)
ON COMMIT PRESERVE ROWS

CREATE OR REPLACE package body pkg_sec_cal_gtt 
IS

procedure pro_sec_cal_gtt 
is 

cursor cur_security_details is
select cms_collateral_id,sci_security_dtl_id,cmv
from  cms_security
where status = 'ACTIVE'  	 					   	   and
decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';


cursor cur_limit_details
is
select  lmt_id, 						 
(NVL (cms_activated_limit, 0) * NVL (cms_req_sec_coverage, 0) / 100) REQ_ABS_VALUE, 
null, cms_act_sec_coverage, 
(NVL (cms_activated_limit, 0) * NVL (cms_req_sec_coverage, 0) / 100) bal_security_value,
sysdate created_date,nvl(cms_activated_limit,0) cms_activated_limit
from  sci_lsp_appr_lmts where
upper(lmt_type_value) <> 'CONTROL' 					
and   upper (cms_limit_status) = 'ACTIVE'
and 	update_status_ind !='D'
order by lmt_id;

cursor cur_sec_det(p_lmt_id sci_lsp_appr_lmts.lmt_id%type) is  
SELECT COUNT (a.sci_las_sec_id) lmtcnt, a.sci_las_sec_id
FROM cms_limit_security_map a,
(SELECT *
FROM cms_limit_security_map
WHERE sci_las_lmt_id = p_lmt_id
AND update_status_ind != 'D') b
WHERE a.sci_las_sec_id =b.sci_las_sec_id and
a.cms_collateral_id=b.cms_collateral_id and
a.sci_las_llp_id=b.sci_las_llp_id and
b.update_status_ind != 'D'
GROUP BY a.sci_las_sec_id
ORDER BY lmtcnt, a.sci_las_sec_id;

TYPE reclist IS TABLE OF cur_security_details%ROWTYPE;
recs reclist;

TYPE lmtdetails IS TABLE OF cur_limit_details%ROWTYPE;
lmtdet lmtdetails;

TYPE secdetails IS TABLE OF cur_sec_det%ROWTYPE;
secdet secdetails;

coll_cnt1		  number ;
coll_cnt2		  number ;


v_cms_collateral_id		 number(19)  ;
v_cmv					 number(30,2);			 
v_status				 varchar2(20);		 
v_req_sec_cov_ind		 varchar2(2) ;
v_sci_security_dtl_id	 number(10) ;
v_cmv_currency			 char(3) ;

spec_lmt_cnt			 number;
temp_bal_security_value	 number(30,2) ;

Begin

    EXECUTE IMMEDIATE 'TRUNCATE TABLE CMS_SECURITY_MOD';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE SEC_SECURITY_EVALUATION_RESULT';
    COMMIT;
	  
    OPEN cur_security_details;
    LOOP
    FETCH cur_security_details BULK COLLECT INTO recs LIMIT 5000;

    FORALL i IN 1..recs.COUNT
    INSERT INTO CMS_SECURITY_MOD VALUES recs(i);

    EXIT WHEN cur_security_details%NOTFOUND;
    END LOOP;
    CLOSE cur_security_details;
	
	
	OPEN cur_limit_details;
    FETCH cur_limit_details BULK COLLECT INTO lmtdet;
	
	FOR j IN 1..lmtdet.COUNT
	Loop
		OPEN cur_sec_det(lmtdet(j).lmt_id);
    	FETCH cur_sec_det BULK COLLECT INTO secdet ;
    	coll_cnt2 := secdet.COUNT;	
		
		FOR k IN 1..secdet.COUNT
		loop
		if ((coll_cnt2 = 1)  and (secdet(k).lmtcnt = 1)) then
		   
		   	 select count(*) into spec_lmt_cnt
			   from cms_security
			   where sci_security_dtl_id = secdet(k).sci_las_sec_id and
			   		 status = 'ACTIVE' 	  						   and
					 decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
   
   
   			 	   if ( spec_lmt_cnt > 0 ) then
	
  	   		 select cms_collateral_id,cmv,status,req_sec_cov_ind,
	   		  	   	sci_security_dtl_id,cmv_currency
			  into  v_cms_collateral_id,v_cmv,v_status,v_req_sec_cov_ind,
			  		v_sci_security_dtl_id,v_cmv_currency
	    	  from  cms_security
			  where sci_security_dtl_id =  secdet(k).sci_las_sec_id and
			  		status = 'ACTIVE'  	 					   	   and  
         			decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
					
	     temp_bal_security_value :=  0 ;
		 
		update SEC_SECURITY_EVALUATION_RESULT
       	   set allocated_cmv = v_cmv,
		   	   act_sec_coverage = nvl(v_cmv,0) / decode(lmtdet(j).cms_activated_limit,0,Null,lmtdet(j).cms_activated_limit) * 100,
		   	   bal_security_value = temp_bal_security_value
         where lmt_id = lmtdet(j).lmt_id; 					 
		
		if sql%notfound then
		 
		  insert into SEC_SECURITY_EVALUATION_RESULT 
		  		 values (lmtdet(j).lmt_id,lmtdet(j).req_abs_value,v_cmv,
				 		 nvl(v_cmv,0) / decode(lmtdet(j).cms_activated_limit,0,Null,lmtdet(j).cms_activated_limit) * 100,
						 temp_bal_security_value,sysdate);	
		   
		End if;
		
--		else
		
		end if ;
				end if ;
		End loop;
				close cur_sec_det;	
				End loop;
			commit;  
	close cur_limit_details;		
	
	

End pro_sec_cal_gtt;
End pkg_sec_cal_gtt;
/

FOR j IN 1..lmtdet.COUNT
Loop
    OPEN cur_sec_det(lmtdet(j).lmt_id);
    FETCH cur_sec_det BULK COLLECT INTO secdet ;

FOR k IN 1..secdet.COUNT
loop
    if ((coll_cnt2 = 1)  and (secdet(k).lmtcnt = 1)) then	   
        select count(*) into spec_lmt_cnt
        from cms_security
        ...