-- ***********************************************************
END basp0099 ;
--------------------------------------------------------------------------------
-- Function : getCountOfChar()
-- Desc : Returns the no of times a character exists in a string(string, char)
--------------------------------------------------------------------------------
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR )
RETURN NUMBER;
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR)
RETURN NUMBER AS
lv_input_length NUMBER;
lv_index NUMBER := 1;
lv_count_of_char NUMBER := 0;
BEGIN
BEGIN
lv_input_length := LENGTH(lv_string);
WHILE lv_index <= lv_input_length
LOOP
IF(SUBSTR(lv_string, lv_index, 1) = 'U') THEN
--{
lv_count_of_char := lv_count_of_char +1 ;
--}
END IF;
lv_index := lv_index + 1;
END LOOP;
END;
RETURN lv_count_of_char;
END getCountOfChar;
-----------------------------------------------------------------------------------------------------------
--- Function : eabBal()
--- Desc : Takes Acid and As On Date as input and returns
--- : Outstanding Balance of the Acid based on the input Dates
-----------------------------------------------------------------------------------------------------------
FUNCTION eabBal(lv_acid varchar2,
lv_as_on_date DATE,bankId varchar2) RETURN NUMBER is
outstanding_amount number;
BEGIN
--{
BEGIN
SELECT nvl(tran_date_bal,0)
INTO outstanding_amount
FROM tbaadm.EAB
WHERE EAB.acid = lv_acid
AND eod_date <= lv_as_on_date
AND end_eod_date >= lv_as_on_date
AND bank_id = bankId ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
outstanding_amount := 0;
END;
RETURN outstanding_amount;
--}
END eabBal;
--create a test table by gary qu
drop table test_gary1;
create table test_gary1
(
id number,
name varchar2(20),
password varchar2(20),
account number(10,2),
emailaddress varchar2(20),
introduce varchar2(50)
);
-- insert into data for table test_gary1
insert into test_gary1 values(1,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(2,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(3,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(4,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(5,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(6,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(7,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(8,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(9,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(10,'gary','123','1000.00','gary