 asked on 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
I am  constantly  getting the following while executing a stored proc which is called from within another stored proc .Server  is  ORA10g.
ORA-06502: PL/SQL: numeric or value error
I have checked all possbile causes for this error ... .and it not the common casuses .. ..like field lengths , data type mismatch source and destination tables  etc . . . .
To track the error i have inserted values in to a temporary table for each statement that is execurted in the procedure.
The statment executions stop at  :
EXECUTE IMMEDIATE 'TRUNCATE TABLE Tbl_XXXX';
Is there anything wrong with using execute immediate statements in a procs .....
Or any specific rules that apply to usage of this statement?
I do not know how to correlate a truncate statement with a numeric value error .
Experts please help.
ORA-06502: PL/SQL: numeric or value error
I have checked all possbile causes for this error ... .and it not the common casuses .. ..like field lengths , data type mismatch source and destination tables  etc . . . .
To track the error i have inserted values in to a temporary table for each statement that is execurted in the procedure.
The statment executions stop at  :
EXECUTE IMMEDIATE 'TRUNCATE TABLE Tbl_XXXX';
Is there anything wrong with using execute immediate statements in a procs .....
Or any specific rules that apply to usage of this statement?
I do not know how to correlate a truncate statement with a numeric value error .
Experts please help.
Oracle DatabaseDatabases
Last Comment
jitendrakalyan
as the truncate statement itself cannot raise that error, I can only presume an audit trigger...
You obtain that error message while executing the command
TRUNCATE TABLE Tbl_XXXX
because this table might have child records in some other table which prevents truncate on this table. Truncate all the child tables that originates from this table and try it again.
TRUNCATE TABLE Tbl_XXXX
because this table might have child records in some other table which prevents truncate on this table. Truncate all the child tables that originates from this table and try it again.
When you get this error,
Try 'TRUNCATE TABLE Tbl_XXXX' in the SQL PLUS or SQL Developer.
If there are child records, or triggers you should get error message, else you might have to investigate the real causue of error
Try 'TRUNCATE TABLE Tbl_XXXX' in the SQL PLUS or SQL Developer.
If there are child records, or triggers you should get error message, else you might have to investigate the real causue of error
One More thing..
You need to have ALTER table privileges to do TRUNCATE in that table.
You need to have ALTER table privileges to do TRUNCATE in that table.
ASKER
TRUNCATE TABLE Tbl_XXXX
The table mentioned does not have any child tables .
i have tried executing all statements in SQL developer sequentially as they are executed in the procedure.
It executes properly !!!
No child records or triggers attached to this table .....absolutely no relation with other objects .
Only when these statements  are executing in the procedure there is an error.
I have executed this proc from SQL developer to ensure there is no issue with the drivers i am using in the application. The same error occurs while executing the proc from sql developer.
Angelll
Can you tell me more about audit trigger . do u mean any system triggers in action could cause this .
There are quiet a few Truncate  and insert statements that i am using on few tables in the procedure ....
Does too many truncates and inserts impact anything.
Â
The table mentioned does not have any child tables .
i have tried executing all statements in SQL developer sequentially as they are executed in the procedure.
It executes properly !!!
No child records or triggers attached to this table .....absolutely no relation with other objects .
Only when these statements  are executing in the procedure there is an error.
I have executed this proc from SQL developer to ensure there is no issue with the drivers i am using in the application. The same error occurs while executing the proc from sql developer.
Angelll
Can you tell me more about audit trigger . do u mean any system triggers in action could cause this .
There are quiet a few Truncate  and insert statements that i am using on few tables in the procedure ....
Does too many truncates and inserts impact anything.
Â
if it works in some tool, but not in the other, it won't be an audit trigger.
please show the relevant part of the procedure.
please show the relevant part of the procedure.
ASKER
Dear Angell ,
I dint say it works in one tool and not in another .
It works if the execute the statments individually .
 When i call the procedure it throws the error.
I dint say it works in one tool and not in another .
It works if the execute the statments individually .
 When i call the procedure it throws the error.
comes to the same result: it's not the syntax you showed, so it must be the parts "around".
ASKER
Find the  procedure in the following code snippet .
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;
ASKER
at line no 157 in the above proc .. i am calling another proc .. .
Find in the snippet .
Find in the snippet .
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;
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks!!