GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000496297' And TBLCDSLCLMAST.Cclientboid <= '1201370000496297' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000496297' And TBLCDSLCLMAST.Cclientboid <= '1201370000496297' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000500023' And TBLCDSLCLMAST.Cclientboid <= '1201370000500023' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000505089' And TBLCDSLCLMAST.Cclientboid <= '1201370000505089' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000479919' And TBLCDSLCLMAST.Cclientboid <= '1201370000479919' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000532351' And TBLCDSLCLMAST.Cclientboid <= '1201370000532351' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000532351' And TBLCDSLCLMAST.Cclientboid <= '1201370000532351' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('16-04-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('16-04-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('17-04-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('17-04-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2026-2027' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('17-04-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE GetCDSLHolding start Row count1 1 2 - OK with parameter as (select CGETLASTCLRATE,CGETFACEVALUE,nfirmnumber from TBLCDSLPARAMETER where nfirmnumber = 1 ), clientdetails as ( select distinct TBLCDSLBOHOLDINGNEW.nfirmnumber, TBLCDSLBOHOLDINGNEW.cclientboid, initcap(TBLCDSLCLMAST.cclientname) as cclientname, initcap(tblcdslbrmast.cbranchname) AS cbranchname, TBLCDSLCLMAST.cfamilycode AS CFAMILYCODE, initcap(TBLCDSLFAMILY.cfamilyname) AS CFAMILYNAME, TBLCDSLCLMAST.cbranchcode AS CBRANCHCODE, TBLCDSLCLDETAILS.citpannumber, (nvl(TBLCDSLCLDETAILS.cclientaddress1,'')) AS cclientaddress1, (nvl(TBLCDSLCLDETAILS.cclientaddress2,'')) AS cclientaddress2, (nvl(TBLCDSLCLDETAILS.cclientaddress3,'')) AS cclientaddress3, decode(trim(nvl(TBLCDSLCLDETAILS.cclientcity,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || nvl(TBLCDSLCLDETAILS.cclientpincode,' ') AS cclientcityPin, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' '))) AS cclientstatecountry, decode(trim(nvl(TBLCDSLCLDETAILS.cclientstate,'')),'','',trim((nvl(TBLCDSLCLDETAILS.cclientstate,' ')))||', ')||trim((nvl(TBLCDSLCLDETAILS.cclientcountry,' ')))||', '||decode(nvl(TBLCDSLCLDETAILS.cclientcity,''),'','',trim((nvl(TBLCDSLCLDETAILS.cclientcity,' ')))||', ') || 'Pin : ' || trim(nvl(TBLCDSLCLDETAILS.cclientpincode,' ')) AS cclientstatecountrypin, decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,'')),'','','Tel No. '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTTELEPHONENO,' '))) || decode(trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,'')),'','',' Fax : '||trim(nvl(TBLCDSLCLDETAILS.CCLIENTFAXNO,''))) as CCLIENTPHONEFAXNO , TBLCDSLCLMAST.CLDCLIENTCODE as CLDCLIENTCODE, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cshsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cshlastname, ''))) as cClientShName, trim(trim(nvl(TBLCDSLCUSTSTHOLDER.cthsuffix, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthtitle, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthfirstname, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthmiddlename, '')) || ' ' || trim(nvl(TBLCDSLCUSTSTHOLDER.cthlastname, ''))) as cClientThName, ' ' as ccustomerstatus, TBLCDSLCUSTSUBSTATUS.ccustomerstatus as cCustomerSubStatus, TBLCDSLDPSTATUS.cdpstatusdescription, ' ' as cClientPOA, ' ' as cguardianname, TBLCDSLCUSTCATEGORY.ccustomercategory as ccustomercategory, nvl(translate(TBLCDSLCLDETAILS.CEMAILADDRESS, ',', ';'), ' ') as CEMAILADDRESS, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSCC, ',', ';'), ' ') as CEMAILADDRESSCC, NVL(translate(TBLCDSLCLBACKOFFDETAILS.CEMAILADDRESSBCC, ',', ';'), ' ') as CEMAILADDRESSBCC, TO_DATE('31-03-2026', 'dd/mm/yyyy') as dmrkdate, TO_DATE('31-03-2026', 'dd/mm/yyyy') as dholdingdate from TBLCDSLBOHOLDINGNEW, TBLCDSLCLMAST, TBLCDSLCLBACKOFFDETAILS, TBLCDSLCLDETAILS, tblcdslbrmast, TBLCDSLFAMILY, TBLCDSLCUSTSTHOLDER, (select ccustomersubstatuscode,max(ccustomerstatus) as ccustomerstatus from tblcdslcustsubstatus group by ccustomersubstatuscode) TBLCDSLCUSTSUBSTATUS, TBLCDSLDPSTATUS, TBLCDSLCUSTCATEGORY ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslclfees,tblcdslparameter where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLCLMAST.nfirmnumber=1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('31-03-2026', 'dd/MM/yyyy') and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLMAST.cclientboid and TBLCDSLCLMAST.cbranchcode = tblcdslbrmast.cbranchcode and TBLCDSLCLMAST.nfirmnumber = tblcdslbrmast.nfirmnumber and TBLCDSLCLMAST.cfamilycode = TBLCDSLFAMILY.cfamilycode and TBLCDSLCLMAST.nfirmnumber = TBLCDSLFAMILY.nfirmnumber and TBLCDSLCLMAST.cbranchcode = TBLCDSLFAMILY.cbranchcode and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLBACKOFFDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLBACKOFFDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCLDETAILS.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCLDETAILS.cclientboid(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = TBLCDSLCUSTSTHOLDER.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = TBLCDSLCUSTSTHOLDER.cclientboid(+) and TBLCDSLCLDETAILS.CCUSTOMERSUBSTATUSCODE = TBLCDSLCUSTSUBSTATUS.CCUSTOMERSUBSTATUSCODE(+) and TBLCDSLCLMAST.cclientstatus = TBLCDSLDPSTATUS.cdpstatus and TBLCDSLCLDETAILS.ccustomercategorycode = TBLCDSLCUSTCATEGORY.ccustomercategorycode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 And (TBLCDSLCLMAST.Cclientboid >= '1201370000408868' And TBLCDSLCLMAST.Cclientboid <= '1201370000408868' ) and not exists (select 'x' from TBLCDSLCLMASTHide where TBLCDSLCLMAST.nfirmnumber=TBLCDSLCLMASTHide.nfirmnumber and TBLCDSLCLMAST.cclientboid=TBLCDSLCLMASTHide.cclientboid and TBLCDSLCLMASTHide.Cuser = 'cllvl' ) ), isindetails as (select TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber from TBLCDSLBOHOLDINGNEW where TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and TBLCDSLBOHOLDINGNEW.dholdingdate = TO_DATE('31-03-2026', 'dd/MM/yyyy') AND EXISTS (SELECT 'X' FROM clientdetails WHERE TBLCDSLBOHOLDINGNEW.Nfirmnumber=clientdetails.NFIRMNUMBER AND TBLCDSLBOHOLDINGNEW.CCLIENTBOID=clientdetails.CCLIENTBOID) group by TBLCDSLBOHOLDINGNEW.cisincode, TBLCDSLBOHOLDINGNEW.nfirmnumber), tblclosingrate as (select isindetails.cisincode, dpcdsl.newclosingrate(isindetails.cisincode, to_date('31-03-2026', 'dd/mm/yyyy'),parameter.cgetfacevalue, parameter.cgetlastclrate) .getrate() as nclosingrate from isindetails,parameter where isindetails.nfirmnumber=parameter.nfirmnumber ) select clientdetails.nfirmnumber, clientdetails.cclientboid, clientdetails.cclientname, clientdetails.cbranchname, clientdetails.CFAMILYCODE, clientdetails.CFAMILYNAME, clientdetails.CBRANCHCODE, clientdetails.citpannumber, clientdetails.cclientaddress1, clientdetails.cclientaddress2, clientdetails.cclientaddress3, clientdetails.cclientcityPin, clientdetails.cclientstatecountry, clientdetails.cclientstatecountrypin, clientdetails.CCLIENTPHONEFAXNO , clientdetails.CLDCLIENTCODE, clientdetails.cClientShName, clientdetails.cClientThName, clientdetails.ccustomerstatus, clientdetails.cCustomerSubStatus, clientdetails.cdpstatusdescription, nvl(tbltempled.nbal, 0) as nbal, clientdetails.cClientPOA, ' ' as cguardianname, clientdetails.ccustomercategory, clientdetails.CEMAILADDRESS, clientdetails.CEMAILADDRESSCC, clientdetails.CEMAILADDRESSBCC, TBLCDSLBOHOLDINGNEW.cisincode AS CISINCODE, TBLCDSLISIN.CISINSHORTNAME AS CISINSHORTNAME, TBLCDSLBOHOLDINGNEW.CSETTLEMENTID as CSETTLEMENTID, TBLCDSLHOLDINGTYPE.CHOLDINGTYPEDESCRIPTION, TBLCDSLBOHOLDINGNEW.NHOLDINGQTY as NHOLDINGQTY, tblclosingrate.nclosingrate as nrate, TBLCDSLBOHOLDINGNEW.nholdingqty * tblclosingrate.nclosingrate as namt , TO_DATE('31-03-2026', 'dd/mm/yyyy') as dmrkdate, TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE as NHOLDINGTYPE, TO_DATE('31-03-2026', 'dd/mm/yyyy') as dholdingdate, tblcdslisinliststatus.cisinlisteddescription , nvl( tblfees.cfeesmaincode ,' ') as cfeesmaincode from TBLCDSLBOHOLDINGNEW,TBLCDSLHOLDINGTYPE,tblclosingrate, TBLCDSLISIN, tblcdslisinliststatus , clientdetails ,(Select TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode, SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) as nbal From TBLCDSLLEDGER where TBLCDSLLEDGER.nfirmnumber = 1 And TBLCDSLLEDGER.Dledgerdate <= To_Date('31-03-2026','dd/mm/yyyy') And TBLCDSLLEDGER.Cfinancialyear = '2025-2026' group by TBLCDSLLEDGER.nfirmnumber, TBLCDSLLEDGER.cAccountcode having SUM(TBLCDSLLEDGER.Nledgerdebitamt - TBLCDSLLEDGER.Nledgercreditamt) > 0) tbltempled ,(select tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,case when tblcdslparameter.cprintfeescodeinholding='Y' then max(tblcdslclfees.cfeesdescription) else ' ' end as cfeesmaincode from tblcdslcustfee , tblcdslparameter,tblcdslclfees where tblcdslcustfee.nfirmnumber=tblcdslparameter.nfirmnumber and tblcdslcustfee.nfirmnumber=1 and tblcdslcustfee.dtodate ='31dec2999' and tblcdslcustfee.nfirmnumber = tblcdslclfees.nfirmnumber and tblcdslcustfee.cfeesmaincode = tblcdslclfees.cfeesmaincode and tblcdslclfees.nfeescode = 2 and tblcdslclfees.dtodate = '31dec2999' group by tblcdslcustfee.nfirmnumber,tblcdslcustfee.cclientboid,tblcdslparameter.cprintfeescodeinholding) tblfees Where TBLCDSLBOHOLDINGNEW.cisincode = TBLCDSLISIN.cisincode and TBLCDSLISIN.cisinunlisted = tblcdslisinliststatus.cisinlistedstatus(+) and TBLCDSLBOHOLDINGNEW.NHOLDINGTYPE = TBLCDSLHOLDINGTYPE.NHOLDINGTYPE and TBLCDSLBOHOLDINGNEW.nfirmnumber = clientdetails.nfirmnumber and TBLCDSLBOHOLDINGNEW.cclientboid = clientdetails.cclientboid and TBLCDSLISIN.cisincode = tblclosingrate.cisincode(+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tblfees.nfirmnumber (+) and TBLCDSLBOHOLDINGNEW.cclientboid = tblfees.cclientboid (+) and TBLCDSLBOHOLDINGNEW.nfirmnumber = tbltempled.nfirmnumber(+) and TBLCDSLBOHOLDINGNEW.cclientboid = tbltempled.caccountcode(+) and TBLCDSLBOHOLDINGNEW.nholdingtype !=13 and TBLCDSLBOHOLDINGNEW.nfirmnumber = 1 and tblcdslboholdingnew.dholdingdate = TO_DATE('31-03-2026', 'dd/MM/yyyy') Order By cclientname, cclientboid ,NHOLDINGTYPE,CISINSHORTNAME, CISINCODE