Sebi Compliance

Select tblCldata.cClientcode as Oowncode, Ldfibs.Fibsacct as Fibsacct,tblCldata.cClientcode as cTermcode,
to_char(Accountgensplit.dOpeningdate,'DD/MM/YY') as cOpeningdate, to_char(Max(tblCldata.dLeddate),'DD/MM/YY') as
cLeddate, Max(tblCldata.cClearledger) as cLedindicator, to_char(Max(tblCldata.dDemdate),'DD/MM/YY') as cDemdate,
Max(tblCldata.cClearDemat) as cDemindicator From (Select tblLedger.cClientcode, Max(dLeddate) as dLeddate, Case When
Sum(tblLedger.nIndicator) > 0 Then 'Y' Else 'N' End as cClearLedger, NULL as dDemdate, ' ' as cClearDemat From (Select
cClientcode, Decode(nledbalance, 0, dLeddate, NULL) as dLeddate, Decode(nledbalance, 0, 1, 0) as nIndicator From (Select
Ledger.Oowncode as cClientcode, Ledger.Dtoftran as dLeddate, Sum(Sum(Ledger.Damount - Ledger.Camount)) over(Partition by
Ledger.Oowncode Order by Ledger.Oowncode, Ledger.Dtoftran rows between unbounded preceding and current row) as nledbalance
From Ledger Ledger, Accountgensplit Accountgensplit where Ledger.Dtoftran <= '31-MAR-11' and Ledger.Firmnumber =
'ACML-00001' and Ledger.Oowncode = Accountgensplit.Oowncode and Ledger.Firmnumber = Accountgensplit.Firmnumber and
Accountgensplit.cAuthorizationtype = 'Quarterly' group by Ledger.Oowncode, Ledger.Dtoftran) where dLeddate >= '01-JAN-11'
and dLeddate <='31-MAR-11' Union all Select Ledger.Oowncode as cClientcode,
to_date(Ledger.Dtoftran, 'DD/MM/YY'), 0 as nindicator
from Ledger Ledger, Accountgensplit Accountgensplit
Where Ledger.Dtoftran <= '31-MAR-11' and Ledger.Firmnumber = 'ACML-00001' and Ledger.Oowncode = Accountgensplit.Oowncode
and Ledger.Firmnumber = Accountgensplit.Firmnumber and Accountgensplit.cAuthorizationtype = 'Quarterly' group by Ledger.Dtoftran,Ledger.Oowncode having Sum(Case
When Substr(Ledger.Voucher, 1, 4) != 'OPNG' and Ledger.Dtoftran >= '01-JAN-11' then Ledger.Damount else 0.00 end) = 0 and
Sum(Case When Substr(Ledger.Voucher, 1, 4) != 'OPNG' and Ledger.Dtoftran >= '01-JAN-11' then Ledger.Camount else 0.00 end)
= 0 and Sum(Ledger.Damount - Ledger.Camount) != 0) tblLedger Group by tblLedger.cClientcode Union Select cClientcode, NULL
as dLeddate, ' ' as cClearLedger, to_date(dDemdate, 'DD/MM/YY'), Case When nIndicator > 0 Then 'Y' Else 'N' End as
cClearDemat From (Select cClientcode, Max(Decode(nIndicator, 0, NULL, dLeddate)) as dDemdate, Sum(Decode(nIndicator, 0, -1,
nIndicator)) as nIndicator From (Select cClientcode, cScripcode, max(Decode(nDembalance, 0, dLeddate, Null)) as dLeddate,
Max(Decode(nDembalance, 0, 1, 0)) as nIndicator From (Select Demat.Clientocode as cClientcode, Demat.Compcode as cScripcode,
Demat.Transferdate as dleddate, Sum(Sum(Demat.Givqty - Demat.Recqty)) over(Partition by Demat.Clientocode, Demat.Compcode
Order by Demat.Clientocode, Demat.Compcode, Demat.Transferdate rows between unbounded preceding and current row) as
nDembalance From Demat Demat, tblBenmaster tblBenmaster, Accountgensplit Accountgensplit where Demat.Transferdate <=
'31-MAR-11' and Demat.Firmnumber = 'ACML-00001' and Demat.Firmnumber = tblBenmaster.cFirmnumber and Demat.Dpcode =
tblBenmaster.cBendpcode and Demat.Dpid = tblBenmaster.cBendpid and Demat.Firmnumber = Accountgensplit.Firmnumber and
Demat.Clientocode = Accountgensplit.Oowncode and Accountgensplit.cAuthorizationtype = 'Quarterly' group by
Demat.Clientocode, Demat.Compcode,Demat.Transferdate) where dLeddate >= '01-JAN-11' and dLeddate <= '31-MAR-11' group by
cClientcode, cScripcode) group by cClientcode)) tblCldata, Ldfibs Ldfibs, Accountgensplit Accountgensplit Where
Ldfibs.Firmnumber = 'ACML-00001' and tblCldata.cClientcode = Ldfibs.Oowncode and tblCldata.cClientcode =
Accountgensplit.Oowncode and Accountgensplit.Firmnumber = 'ACML-00001' and Accountgensplit.cAuthorizationtype = 'Quarterly'
group by tblCldata.cClientcode, Ldfibs.Fibsacct, Accountgensplit.dOpeningdate;