I am constantly getting the following while executing a stored proc which is called from within another stored proc .What i am using is ORA10g. ORA-06502: PL/SQL: numeric or value error

create or replace
PROCEDURE STPR_INDIANSTRELECTRONIC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
   AS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  iCount NUMBER(10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  cMaxLineNo CHAR(8);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  ILen NUMBER(10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  nACC NUMBER(10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  nTRN NUMBER(10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  nLPE NUMBER(10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  nINP NUMBER(10);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
   -- insert into tbl_a values('The very first');
  EXECUTE IMMEDIATE 'delete from tbl_IndianSTR_SBALPE ';                                                    
  EXECUTE IMMEDIATE 'delete from tbl_IndianSTR_SBAACC ';                                                    
  EXECUTE IMMEDIATE 'delete from tbl_IndianSTR_SBAINP ';                                                    
  EXECUTE IMMEDIATE 'delete from tbl_IndianSTR_SBABRC ';                                                    
  EXECUTE IMMEDIATE 'delete from tbl_IndianSTR_SBATRN ';                                                    
  EXECUTE IMMEDIATE 'delete from tbl_IndianSTR_SBACTL ';  
  -- insert into tbl_a values('The very second');
  --Execute Immediate 'Trucate table tbl_a ';
 
  --insert into tbl_a values('before');
-- SBALPE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
 
--EXECUTE IMMEDIATE 'Truncate table TT_LPE_LINENUMBER ' ;    
 
  --insert into tbl_a values('after');
  
  INSERT INTO TT_LPE_LINENUMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
  Select RecordType, DateOfReport, ROWNUM AS LineNumber,BSRCode,AccountNo,                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         annexEnclosed,NameOfLegalPerson,CustomerId,NatureofBusiness,incorporationDate,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
         ConstitutionType,RegistrationNumber,RegisteringAuth,RegisteringPlace,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         PANNo,addBuildingNo,addStreet,addLocality,addCity,addState,addPinCode,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         addTelNo,addFaxNo,addEmail,Comm_AddressLine1,Comm_AddressLine2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         Comm_City,Comm_State,SecondAddress5,Comm_PinCode,Comm_PhoneNo,Comm_FaxNo ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  From                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
  (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
  SELECT Distinct 'LPE' AS RecordType, SYSTIMESTAMP AS DateOfReport,i.BSRCode, i.AccountNo,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
         i.annexEnclosed, i.NameOfLegalPerson, i.CustomerId,  i.NatureofBusiness, i.incorporationDate incorporationDate,                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         i.ConstitutionType, i.RegistrationNumber, i.RegisteringAuth, i.RegisteringPlace,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
         i.PANNo, substr(i.addBuildingNo,1,40) addBuildingNo, i.addStreet, i.addLocality, i.addCity, i.addState, i.addPinCode,                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
         i.addTelNo, i.addFaxNo, i.addEmail, i.Comm_AddressLine1, i.Comm_AddressLine2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         i.Comm_City, i.Comm_State, i.Comm_State AS SecondAddress5, i.Comm_PinCode, i.Comm_PhoneNo, i.Comm_FaxNo ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         'N' AS Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
         FROM tbl_IndianSTRLeglPersonDetils i                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
              INNER JOIN (SELECT Distinct * FROM Tbl_IndianSTRs_File WHERE UPPER(Status) = 'APPROVED' ) t ON i.AlertNo = t.AlertNo                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  )A;   
  
  --insert into tbl_a values('first insert');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  UPDATE TT_LPE_LINENUMBER SET incorporationDate='' WHERE TRIM(incorporationDate) IS NULL;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
/*  UPDATE TT_LPE_LINENUMBER A SET AccountNo=(Select AccountNo from tbl_AccountsMaster B Where A.CustomerId=B.CustomerID and rownum=1)                                                                                                                                                                                                                                                                                                                                                                                                                                                              
  Where CustomerId in (Select CustomerID from tbl_AccountsMaster B Where A.CustomerId=B.CustomerID);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
*/              
 -- insert into tbl_a values('first Update');
  
  SELECT COUNT(*) INTO iCount                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         FROM TT_LPE_LINENUMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
         WHERE BSRCode||AccountNo NOT IN (SELECT BranchReferenceNumber||AccountNumber FROM tbl_IndianSTR_SBAACC);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  --IF iCount = 0 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
     INSERT INTO tbl_IndianSTR_SBALPE                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
             SELECT * FROM TT_LPE_LINENUMBER ;                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
 --                  WHERE BSRCode||AccountNo NOT IN (SELECT BranchReferenceNumber||AccountNumber FROM tbl_IndianSTR_SBAACC);                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
     nLPE :=SQL%rowCount;     
  --END IF;                   
  -- insert into tbl_a values('second Update');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-- SBAINP                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  UPDATE tbl_IndianSTR_SBALPE A SET AccountNumber=(Select AccountNo from tbl_AccountsMaster B Where A.CUSTOMERIDNUMBER=B.CustomerID and rownum=1)                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  Where CUSTOMERIDNUMBER in (Select CustomerID from tbl_AccountsMaster B Where A.CUSTOMERIDNUMBER=B.CustomerID);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    
    --insert into tbl_a values('third Update');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  --dbms_output.put_line('0');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  EXECUTE IMMEDIATE 'delete from TT_INP_LINENUMBER ';                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  INSERT INTO TT_INP_LINENUMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
  Select RecordType,DateOfReport,ROWNUM AS LineNumber,BRANCHREFERENCENUMBER,AccountNumber,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
         relationFlag,FULLNAMEOFINDIVIDUAL,CUSTOMERIDNUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         NAMEOFFATHERSPOUSE,OCCUPATION,DATEOFBIRTH, --TRUNC(i.DOB) DATEOFBIRTH,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         SEX,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         NATIONALITY,TYPEOFIDENTIFICATION, IDENTIFICATIONNUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         ISSUINGAUTHORITY, PLACEOFISSUE,                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         PAN, COMMUNICATIONADDRESS1,  COMMUNICATIONADDRESS2,                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         COMMUNICATIONADDRESS3,COMMUNICATIONADDRESS4,COMMUNICATIONADDRESS5,                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         COMMUNICATIONADDRESSPINCODE,CONTACTTELEPHONE,CONTACTMOBILENUMBER,CONTACTEMAIL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
         PLACEOFWORK,SECONDADDRESS1,SECONDADDRESS2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         SECONDADDRESS3,SECONDADDRESS4,SECONDADDRESS5,SECONDADDRESSPINCODE,                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         SECONDTELEPHONE,'N' GENERATED,ANNEXENCLOSED                                                                                                                                                                                                                                                                                                                                                                                                                                                                
 From                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
  SELECT Distinct 'INP' AS RecordType, SYSTIMESTAMP AS DateOfReport,i.BSRCode BRANCHREFERENCENUMBER, I.AccountNo AccountNumber,                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
         RelationFlag relationFlag, i.FullName FULLNAMEOFINDIVIDUAL, i.CustomerId CUSTOMERIDNUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         i.FATHERNAME NAMEOFFATHERSPOUSE, i.OCCUPATION OCCUPATION,TO_CHAR(NVL(i.DOB,''),'DDMMYYYY') DATEOFBIRTH, --TRUNC(i.DOB) DATEOFBIRTH,                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         i.SEX SEX,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         i.NATIONALITY NATIONALITY, i.IDDOC TYPEOFIDENTIFICATION, i.IDNUMBER IDENTIFICATIONNUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         i.ISSUINGAUTH ISSUINGAUTHORITY, i.PLACEOFISSUE PLACEOFISSUE,                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         i.PANNO PAN, i.ADDBUILDINGNO COMMUNICATIONADDRESS1,  i.ADDSTREET COMMUNICATIONADDRESS2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         i.ADDLOCALITY COMMUNICATIONADDRESS3,  i.ADDCITY COMMUNICATIONADDRESS4, i.ADDSTATE COMMUNICATIONADDRESS5,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
         i.ADDPINCODE COMMUNICATIONADDRESSPINCODE, i.ADDTELNO CONTACTTELEPHONE, i.ADDMOBILENO CONTACTMOBILENUMBER, i.ADDEMAIL CONTACTEMAIL,                                                                                                                                                                                                                                                                                                                                                                                                                                                         
         i.ADDEMPLOYERNAME PLACEOFWORK, i.SECADDBUILDINGNO SECONDADDRESS1, i.SECADDSTREET SECONDADDRESS2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
         i.SECADDLOCALITY SECONDADDRESS3, i.SECADDCITY SECONDADDRESS4, i.SECADDSTATE SECONDADDRESS5, i.SECADDPINCODE SECONDADDRESSPINCODE,                                                                                                                                                                                                                                                                                                                                                                                                                                                          
         i.SECADDTELNO SECONDTELEPHONE,'N' GENERATED,i.annexEnclosed ANNEXENCLOSED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         FROM tbl_IndianSTRIndividualDetails i                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
              INNER JOIN (SELECT DISTINCT * FROM Tbl_IndianSTRs_File WHERE UPPER(Status) = 'APPROVED' ) t ON i.AlertNo = t.AlertNo                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  )A;  
  
  
 -- insert into tbl_a values('second  insert');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  UPDATE TT_INP_LINENUMBER SET DATEOFBIRTH=' ' WHERE TRIM(DATEOFBIRTH) IS NULL;                                                                                                                                                                                                                                                                                                                                                                                                                                     
             
  -- insert into tbl_a values('4  update');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
  UPDATE TT_INP_LINENUMBER A SET AccountNumber=(Select AccountNo from tbl_AccountsMaster B Where A.CUSTOMERIDNUMBER=B.CustomerID and rownum=1)                                                                                                                                                                                                                                                                                                                                                                                                                                                      
  Where CUSTOMERIDNUMBER in (Select CustomerID from tbl_AccountsMaster B Where A.CUSTOMERIDNUMBER=B.CustomerID);       
  
  -- insert into tbl_a values('5  update');       
  SELECT COUNT(*) INTO iCount                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         FROM TT_LPE_LINENUMBER ;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  --       WHERE BSRCode||AccountNo NOT IN (SELECT BranchReferenceNumber||AccountNumber FROM tbl_IndianSTR_SBAACC);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
  --IF iCount = 0 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
     INSERT INTO tbl_IndianSTR_SBAINP                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
             SELECT * FROM TT_INP_LINENUMBER ;                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
    --               WHERE BRANCHREFERENCENUMBER||AccountNumber NOT IN (SELECT BranchReferenceNumber||AccountNumber FROM tbl_IndianSTR_SBAACC);                                                                                                                                                                                                                                                                                                                                                                                                                                                     
     nINP :=SQL%rowCount;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  --END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      dbms_output.put_line('1');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-- SBABRC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  SELECT Count(*) INTO iCount                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         FROM tbl_IndianSTR_SBABRC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         WHERE BranchReferenceNumber in (SELECT repBSRCode FROM tbl_IndianSTRManualFormDetails);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
 --IF iCount = 0 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
    SELECT COUNT(*) INTO iCount from tbl_IndianSTR_SBABRC;                                                                                                                                                                                                                                                                                                                                                                                                                                                          
    INSERT INTO tbl_IndianSTR_SBABRC(RecordType, DateOfReport, LineNumber, NameOfBranch, BRANCHREFERENCENUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                 UIDISSUEDBYFIU, BRANCHADDRESS1, BRANCHADDRESS2, BRANCHADDRESS3, BRANCHADDRESS4, BRANCHADDRESS5, BRANCHPINCODE,                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                 BRANCHTELEPHONE, BRANCHFAX, BRANCHEMAIL )                                                                                                                                                                                                                                                                                                                                                                                                                                                          
         Select RecordType, ReportSendingDate,ROWNUM AS LineNumber,repBranchName,repBSRCode,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                 repIDFIUIND,repBuildingNo,repStreet,repLocality,repCity,repState,repPinCode,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                 reptelNo,repFaxNo,repEmail                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
         From                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
           SELECT Distinct 'BRC' AS RecordType, ReportSendingDate,i.repBranchName, i.repBSRCode,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                 i.repIDFIUIND, i.repBuildingNo, i.repStreet, i.repLocality, i.repCity, i.repState, i.repPinCode,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                 i.reptelNo, i.repFaxNo, i.repEmail                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                 FROM tbl_IndianSTRManualFormDetails i                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                      INNER JOIN (SELECT DISTINCT * FROM Tbl_IndianSTRs_File WHERE UPPER(Status) = 'APPROVED' ) t ON i.AlertNo = t.AlertNo                                                                                                                                                                                                                                                                                                                                                                                                                                                          
         )A;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 --END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
  dbms_output.put_line('22');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
--SBAACC            
--insert into tbl_a values('6666666'); 
 
Begin
 STPR_ACCOUNTSSUMMARY_STR();            
End;
 
--insert into tbl_a values('777777'); 
EXECUTE IMMEDIATE 'TRUNCATE TABLE TT_FINAL ';                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
EXECUTE IMMEDIATE 'TRUNCATE TABLE TT_ACC_LINENUMBER ';
 
   INSERT INTO TT_ACC_LINENUMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
   Select RecordType, DateOfReport, ROWNUM AS LineNumber,BranchReferenceNumber,AccountNumber,
   '-' AS NameOffirstoleaccountholder,TypeOfAccount, TypeOfAccountHolder,DateofAccountOpening,
          Case                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
             When RiskCategory = 1 Then 'A'                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
             When RiskCategory = 2 Then 'B'                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
             When RiskCategory = 3 Then 'C'                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
             When RiskCategory = 4 Then 'C'                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
              else 'A'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
          End AS  RiskCategory ,--RiskCategory ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          CumulativeCreditTurnover,CumulativeDebitTurnover,                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          CumultCshDeptTrnover, CumultCshWithdlTrnover,                                                                                                                                                                                                                                                                                                                                                                                                                                                             
          Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
   From                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
   (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   SELECT Distinct 'RAC' AS RecordType, SYSTIMESTAMP AS DateOfReport,isad.BSRCode BranchReferenceNumber, isad.AccountNo AccountNumber,                                                                                                                                                                                                                                                                                                                                                                                                                                                              
          '-' AS NameOffirstoleaccountholder, isad.AccountType TypeOfAccount, isad.ACCOUNTHOLDERTYPE TypeOfAccountHolder,
          isad.ACCOUNTOPENDATE DateofAccountOpening, am.RiskRating AS RiskCateGory,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
          ASM.CUMULCREDITTURNOVER CumulativeCreditTurnover, ASM.CUMULDEBITTURNOVER CumulativeDebitTurnover,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          ASM.CUMULCASHDEPOSITTURNOVER CumultCshDeptTrnover, ASM.CUMULCASHWITHDRAWALTURNOVER CumultCshWithdlTrnover,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          'N' AS Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          FROM (Select * from tbl_IndianSTRAccountDetails Where AlertNo in                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                   (Select AlertNo from Tbl_IndianSTRs_File Where Upper(Status)='APPROVED')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
               )isad                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          INNER JOIN tbl_AccountsMaster am ON isad.AccountNo = am.AccountNo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          INNER JOIN TBL_ACCOUNTSSUMMARY_STR ASM on isad.AccountNo = ASM.AccountNo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
          INNER JOIN (SELECT DISTINCT * FROM Tbl_IndianSTRs_File WHERE UPPER(Status) = 'APPROVED') test ON isad.AlertNo = test.AlertNo                                                                                                                                                                                                                                                                                                                                                                                                                                                              
   )A;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
              insert into tbl_a values('88888');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
   INSERT INTO tbl_IndianSTR_SBAACC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
       (RECORDTYPE,DATEOFREPORT,LINENUMBER,BRANCHREFERENCENUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                   
	ACCOUNTNUMBER,NAMEOFFIRSTSOLEACCOUNTHOLDER,TYPEOFACCOUNT,                                                                                                                                                                                                                                                                                                                                                                                                                                                          
	TYPEOFACCOUNTHOLDER,DATEOFACCOUNTOPENING,                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
	RISKCATEGORY,CUMULATIVECREDITTURNOVER,CUMULATIVEDEBITTURNOVER,                                                                                                                                                                                                                                                                                                                                                                                                                                                     
	CUMULATIVECASHDEPTURNOVER,CUMULATIVECASHWITHTURNOVER,GENERATED )                                                                                                                                                                                                                                                                                                                                                                                                                                                   
    SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
		RecordType, DateOfReport, LineNumber, BranchReferenceNumber,                                                                                                                                                                                                                                                                                                                                                                                                                                                      
		AccountNumber,NameOffirstoleaccountholder, TypeOfAccount,                                                                                                                                                                                                                                                                                                                                                                                                                                                         
		TypeOfAccountHolder, DateofAccountOpening,                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
		RiskCateGory,Round(CumulativeCreditTurnover), Round(CumulativeDebitTurnover),                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
		Round(CumultCshDeptTrnover), Round(CumultCshWithdlTrnover),                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                /*Round(0), Round(0),                                                                                                                                                                                                                                                                                                                                                                                                                                                               
		Round(0), Round(0),*/ 'N' AS Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
	FROM TT_ACC_LINENUMBER ;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    --           WHERE BranchReferenceNumber||AccountNumber NOT IN(SELECT BranchReferenceNumber||AccountNumber FROM tbl_IndianSTR_SBAACC) Order by LINENUMBER;                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
 nACC :=SQL%rowCount;    
 
 insert into tbl_a values('999999'); 
 Update tbl_IndianSTR_SBAACC A set NAMEOFFIRSTSOLEACCOUNTHOLDER=(Select CustomerName from tbl_CustomerMaster Where CustomerID in                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            (Select CustomerID from tbl_AccountsMaster C Where A.ACCOUNTNUMBER=C.AccountNo and rownum=1) and rownum=1)  --Changed by jitendra for IOB 27 Feb                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  Where ACCOUNTNUMBER In (Select AccountNo from tbl_AccountsMaster C Where A.ACCOUNTNUMBER=C.AccountNo and rownum=1);                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  dbms_output.put_line('333');  insert into tbl_a values('12121212');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--  SBATRN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
  INSERT INTO tbl_IndianSTR_SBATRN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
        	(RECORDTYPE,LINENUMBER,BRANCHREFERENCENUMBER,                                                                                                                                                                                                                                                                                                                                                                                                                                                              
		ACCOUNTNUMBER,TRANSACTIONID,DATEOFTRANSACTION,                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
		MODEOFTRANSACTION,DEBITCREDIT,AMOUNT,                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
		CURRENCYOFTRANSACTION,DISPOSITIONOFFUNDS,                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
		REMARKS,GENERATED )                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
     Select RecordType, ROWNUM AS LineNumber, BSRCODE, AccountNo, TransactionNo,TRANSACTIONTIMESTAMP,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                MODEOFTRANSACTION, DipositOrWithdrawal, Amount, CurrencyOfTransaction,DispositionFunds,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                Remarks, Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     From                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
     (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         SELECT Distinct 'TRN' AS RecordType,a.BSRCODE, a.AccountNo, Replace(trn.TransactionID,' ','') AS TransactionNo, b.TRANSACTIONTIMESTAMP,--b.TransactionNo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                --Substr(TransactionType,1,1) MODEOFTRANSACTION,                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                b.INSTRUMENTCODE MODEOFTRANSACTION,                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                b.DipositOrWithdrawal, b.Amount,trn.AcctCurrencyCode CurrencyOfTransaction,'X' AS DispositionFunds,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                'wert' AS Remarks, 'N' AS Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         FROM tbl_IndianSTRAccountDetails a                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
         INNER JOIN tbl_IndianSTRAccCusTranDetail b ON a.AlertNo = b.AlertNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         --INNER JOIN tbl_Transactions trn ON a.TransactionNo = trn.TransactionNo
         INNER JOIN tbl_Transactions trn ON b.TransactionNo = trn.TransactionNo
         INNER JOIN (SELECT DISTINCT * FROM Tbl_IndianSTRs_File WHERE UPPER(Status) = 'APPROVED') test ON a.AlertNo = test.AlertNo                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
     )A;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  nTRN :=SQL%rowCount;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
  dbms_output.put_line('4444'); 
  insert into tbl_a values('13131313');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-- SBACTL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
   /*SELECT COUNT(*) INTO iCount                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          FROM  tbl_IndianSTR_SBACTL WHERE BSRCode IN (SELECT BSRCode FROM tbl_IndianSTRManualFormDetails);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
   IF iCount = 0 THEN */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
      SELECT COUNT(SNoReport) INTO iCount FROM tbl_IndianSTR_SBACTL;                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      IF iCount = 0 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         cMaxLineNo := '00000001';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
      ELSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
         SELECT MAX(SNoReport) into cMaxLineNo FROM tbl_IndianSTR_SBACTL;                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         cMaxLineNo := cMaxLineNo + 1;                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         iLen := LENGTH(cMaxLineNo);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
         IF iLen = 1 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
            cMaxLineNo := '0000000' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
         ELSIF iLen = 2 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            cMaxLineNo := '000000' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         ELSIF iLen = 3 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            cMaxLineNo := '00000' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
         ELSIF iLen  = 4 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            cMaxLineNo := '0000' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
         ELSIF iLen  = 5 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            cMaxLineNo := '000' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         ELSIF iLen  = 6 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            cMaxLineNo := '00' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         ELSIF iLen  = 7 THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            cMaxLineNo := '0' + cMaxLineNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
         END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
     END iF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
    INSERT INTO tbl_IndianSTR_SBACTL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       (REPORTNAME,SNOREPORT,RECORDTYPE,DATEOFREPORT,                                                                                                                                                                                                                                                                                                                                                                                                                                                               
	COMPLETENAMEOFBANK,CATEGORYOFBANK,BSRCODE,UIDISSUEDBYFIU,                                                                                                                                                                                                                                                                                                                                                                                                                                                          
	PRINCIPALOFFICERNAME,PRINCIPALOFFICERDESIGNATION,PRINCIPALOFFICERADDRESS1,                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	PRINCIPALOFFICERADDRESS2,PRINCIPALOFFICERADDRESS3,                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
	PRINCIPALOFFICERADDRESS4,PRINCIPALOFFICERADDRESS5,                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
	PRINCIPALOFFICERPINCODE,PRINCIPALOFFICERTELEPHONE,PRINCIPALOFFICERFAX,                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	PRINCIPALOFFICEREMAIL,REPORTTYPE,REASONFORREPLACEMENT,SNOOFORIGINALREPORT,                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	OPERATIONALMODE,DATASTRUCTUREVERSION,NOACCTLINKEDTOSUSTRAN,                                                                                                                                                                                                                                                                                                                                                                                                                                                        
	NUMBEROFTRANSACTIONS,NOOFINDIVIDUALPERSONS,NOOFLEGALPERSONENTI,                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	SUSPICIONDUETOIDENTITYOFCLIENT,SUSPDUETOBGOFCLIENT,SUSPONDUETOMULTIACCT,                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	SUSPDUETOACTINACCT,SUSPONDUETONATUREOFTRANS,SUSPONDUETOVALUEOFTRANS,                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
	GROUNDSOFSUSPICION,DETAILSOFOTHERINVESTIGATIONS,                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
	ACKNOWLEDGEMENTNUMBER,DATEOFACKNOWLEDGEMENT,GENERATED)      --cMaxLineNo                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
     SELECT Distinct 'SBA', To_Number(cMaxLineNo), 'CTL' AS ReportType, ReportSendingDate, princNameOfBank,princBankCategory,                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                    princBSRCode, princIDFIUIND, princOfficerName, princDesignation, princBuildingNo, princStreet,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                    princLocality, princCity, princState, princPinCode, princTelNo, princFax, princEmail, 'N' AS ReportType,                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                    'N' AS ReasonforReplacement, '00000000' SNOOfOriginalReport, 'P' OperationalMode, '1' AS DataStructureVerson,                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    nACC AS NOOFACCOUNTSLINKEDTOSUSPTRANS, nTRN AS NumberofTransactions , nINP AS NOofIndividualPersons,                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                    nLPE AS NOofLegalPersonentities , 'Y' AS SUSPICIONIDENTITYOFCLIENT, 'Y' AS SUSPICIONBACKGROUND,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                    'N' AS SUSPICIONDUETOMULTIPLEACCOUNTS, 'Y' AS SUSPICIONACTIVITYINACCOUNTS,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                    'Y' AS SUSPICIONNATUREOFTRANSACTIONS, 'Y' AS SUSPICIONVALUEOFTRANSACTIONS,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                    Part7SusGrounds AS GroundsofSuspicion, 'info' AS Detailsofotherinvestigations,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                    '0000000000' AS AcknowledgementNumber, SYSTIMESTAMP AS DateofAcknowledgement, 'N' AS Generated                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
             FROM tbl_IndianSTRManualFormDetails a                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                  INNER JOIN (SELECT DISTINCT * FROM Tbl_IndianSTRs_File WHERE UPPER(Status) = 'APPROVED') test ON a.AlertNo = test.AlertNo;                                                                                                                                                                                                                                                                                                                                                                                                                                                        
   --END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
   Execute Immediate 'Truncate Table Tbl_IndianSTRs_File';                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    WHEN NO_DATA_FOUND THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      NULL;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
    WHEN OTHERS THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM, TRUE);                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
END;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:

create or replace PROCEDURE STPR_ACCOUNTSSUMMARY_STR AS
dtFromDate varchar2(10);
dtToDate varchar2(10);
nCount int:=0;
begin
 
 
Execute Immediate 'delete from tbl_AccountsSummary_STR';
 
  Execute Immediate 'Delete from  tbl_STR_Account' ;
  
  insert into tbl_STR_Account Select Distinct AccountNo from tbl_IndianSTRAccountDetails Where AlertNo in
                   (Select AlertNo from Tbl_IndianSTRs_File Where Upper(Status)='APPROVED') ; -- Changed by jitendra for IOB 27 Feb
 
 
dtFromDate := TO_TIMESTAMP('01/'|| TO_CHAR(sysdate,'MM') || '/' || TO_CHAR(sysdate,'YYYY'), 'DD/MM/YYYY');
dtToDate   := LAST_DAY(dtFromDate);
 
INSERT into tbl_AccountsSummary_STR 
 Select AccountNo,SUM(CUMULDEBITTURNOVER) CUMULDEBITTURNOVER, SUM(CUMULCREDITTURNOVER) CUMULCREDITTURNOVER,
		   SUM(CUMULCASHDEPOSITTURNOVER) CUMULCASHDEPOSITTURNOVER, SUM(CUMULCASHWITHDRAWALTURNOVER) CUMULCASHWITHDRAWALTURNOVER,
		   MIN(STARTDATE) STARTDATE, MAX(ENDDATE) ENDDATE, SYSTIMESTAMP UPDATETIMESTAMP,'SA' SECURITYID from
(
SELECT AccountNo,
	     SUM(CASE WHEN DepositOrWithDrawal = 'W' THEN Amount ELSE 0.0 END) AS CUMULDEBITTURNOVER,
	     SUM(CASE WHEN DepositOrWithDrawal = 'D' THEN Amount ELSE 0.0 END) AS CUMULCREDITTURNOVER,
	     SUM(CASE WHEN DepositOrWithDrawal = 'D' AND SUBSTR(TRANSACTIONTYPE,1,1) = 'C' THEN Amount ELSE 0.0 END) AS CUMULCASHDEPOSITTURNOVER,
	     SUM(CASE WHEN DepositOrWithDrawal = 'W' AND SUBSTR(TRANSACTIONTYPE,1,1) = 'C' THEN Amount ELSE 0.0 END) AS CUMULCASHWITHDRAWALTURNOVER,
	     TO_TIMESTAMP(dtFromDate, 'DD/MM/YYYY') STARTDATE,
	     TO_TIMESTAMP(dtToDate,'DD/MM/YYYY') ENDDATE
		   FROM TBL_TRANSACTIONS 
      -- WHERE AccountNo in (select AccountNo from tbl_AccountsSummary_STR) AND -- Commented by jitendar for IOB 27 feb 
       WHERE AccountNo in (select AccountNo from tbl_STR_Account) AND
       TRANSACTIONDATETIME >= TO_TIMESTAMP(dtFromDate, 'DD/MM/YYYY') 
       AND TRANSACTIONDATETIME < TO_TIMESTAMP(dtToDate,'DD/MM/YYYY')+1
       GROUP BY AccountNo
      UNION ALL
      Select Accountno,CUMULDEBITTURNOVER, CUMULCREDITTURNOVER, CUMULCASHDEPOSITTURNOVER, CUMULCASHWITHDRAWALTURNOVER,STARTDATE, ENDDATE 
      from tbl_AccountsSummary where AccountNo in (select AccountNo from tbl_AccountsSummary_STR) 
      and monthofsummary = TO_CHAR(dtFromDate-1,'MM') and yearofsummary = TO_CHAR(dtFromDate-1,'YYYY')
) Group by AccountNo ;
 
 
 
End STPR_ACCOUNTSSUMMARY_STR;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45: