DATE WISE BALANCE REPORT FUNCTION

function CF_debitFormula return Number is
s_debit number(20,3);
begin

If :P28 is Null then
Select nvl(SUM(DECODE(DBCRCODE,'D',JVLCAMNT,-Jvlcamnt)),0) credit
--- sum(decode(dbcrcode,'C',nvl(-jvlcamnt,0),nvl(jvlcamnt,0))) Credit
into :P28
From STTRNdtl
WHERE compcode =decode(:p0,'ALL',compcode,:p0)
AND doctdate < :p11
AND SUBSTR(acctcode,1,5)=:p1
Group By SUBSTR(acctcode,1,5);
End if;


Select nvl(SUM(DECODE(DBCRCODE,'D',JVLCAMNT,-jvlcamnt)),0) credit
---sum(decode(dbcrcode,'C',nvl(-jvlcamnt,0),nvl(jvlcamnt,0))) Credit
into s_debit
from actrndtl
where compcode =decode(:p0,'ALL',compcode,:p0)
and doctdate = :stholdte
and SUBSTR(acctcode,1,5)=:p1
group by substr(acctcode,1,5);

:P28 := nvl(:P28,0) + s_debit;

If :P28 > 0 then
return :P28;
Else
return 0;
End if;
Exception
when others then
If :P28 > 0 then
return :P28;
End If;
Return 0;
End;


------PROCEDURE

CREATE OR REPLACE PROCEDURE dpr_datewise_gl_balance (
p_date OUT DATE,
p_acctcode OUT VARCHAR2,
p_bal OUT NUMBER
) IS
s_datefrom DATE := TO_DATE ('01102008', 'ddmmrrrr');
s_tofrom DATE := TO_DATE ('31102008', 'ddmmrrrr');
s_opnbal NUMBER := 0;
s_curbal NUMBER := 0;
s_incrementbal NUMBER := 0;

CURSOR c_atchrtac IS
SELECT acctcode, acctname
FROM stchrtac
WHERE acctcode IN ('10200')
ORDER BY 1;
BEGIN
FOR i IN c_atchrtac LOOP
SELECT NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0)
INTO s_opnbal
FROM sttrndtl
WHERE compcode = DECODE (:p0, 'ALL', compcode, :p0)
AND SUBSTR (acctcode, 1, 5) = i.acctcode
AND doctdate < s_datefrom;

WHILE s_datefrom <= s_tofrom LOOP
p_acctcode := i.acctcode;
p_date := s_datefrom;

SELECT (NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0))
INTO s_curbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate = p_date;

s_incrementbal := s_incrementbal + s_curbal;
p_bal := s_opnbal + s_incrementbal;
DBMS_OUTPUT.put_line (p_date || ' ' || p_acctcode || ' ' || p_bal);
s_datefrom := s_datefrom + 1;
END LOOP;
END LOOP;
END;
/

----ANOTHER

var p_acctcode varchar2(100)
var p_date varchar2(100)
var p_bal number
set serverout on


CREATE OR REPLACE PROCEDURE dpr_datewise_gl_balance (
p_date OUT DATE,
p_acctcode OUT VARCHAR2,
p_bal OUT NUMBER
) IS
s_datefrom DATE := TO_DATE ('01102008', 'ddmmrrrr');
s_tofrom DATE := TO_DATE ('31102008', 'ddmmrrrr');
s_opnbal NUMBER := 0;
s_curbal NUMBER := 0;
s_incrementbal NUMBER := 0;

CURSOR c_atchrtac IS
SELECT acctcode, acctname
FROM stchrtac
WHERE acctcode IN ('10200')
ORDER BY 1;
BEGIN
FOR i IN c_atchrtac LOOP
SELECT NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0)
INTO s_opnbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate < s_datefrom;

WHILE s_datefrom <= s_tofrom LOOP
p_acctcode := i.acctcode;
p_date := s_datefrom;

SELECT (NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0))
INTO s_curbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate = p_date;

s_incrementbal := s_incrementbal + s_curbal;
p_bal := s_opnbal + s_incrementbal;
DBMS_OUTPUT.put_line (p_date || ' ' || p_acctcode || ' ' || p_bal);
s_datefrom := s_datefrom + 1;
END LOOP;
END LOOP;
END;
/
exec dpr_datewise_gl_balance(:p_date,:p_acctcode,:p_bal);


--For Check

SELECT SUBSTR (acctcode, 1, 5) acctcode,
NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0) balance
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) IN ('10100', '10200')
AND doctdate <= TO_DATE ('30092008', 'ddmmrrrr')
GROUP BY SUBSTR (acctcode, 1, 5)