Branchwise Turnover

select distinct Brcode, sum(Grosspurch) Purchases, sum(Grosssales) Sales, sum(Totalvol) GrossTurnover, sum(Netpsvalue) NetTurnover, sum(Brokerage) Brokerage, sum(Volper), sum(NetBrokerage), sum(Netvolper), sum(nTurnpie), sum(nGrossbrkpie),sum(nNetbrkpie) from (Select sauda.brcode Brcode, Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosspurch,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosssales,Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Orignalno,0, Sauda.Quantity*Sauda.Market,0))) as Totalvol, Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Buysell,'B', Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0), Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market*-1,0)))) as Netpsvalue,Sum((Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Final_rat1-Sauda.Oldrate),Sauda.Quantity*(Sauda.Final_rat1-Sauda.Market)),Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Oldrate-Sauda.Final_rat1),Sauda.Quantity*(Sauda.Market-Sauda.Final_rat1))))+Sauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From Sauda Sauda Where Sauda.Firmnumber='ACML-00001' and Sauda.Egroup!='MB' and Sauda.Egroup!='YC' and Sauda.Egroup!='MO' and Sauda.Egroup!='OK' and Sauda.Egroup!='ST' and Sauda.Sterminal!='99999' and Sauda.Sterminal!='99998' Group by sauda.oowncode,sauda.brcode Union Select fosauda.brcode Brcode, Sum(Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosspurch,Sum(Decode(foSauda.Buysell,'S',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosssales,Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Orignalno,0, foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0))) as Totalvol, Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Buysell,'B', Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0), Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice)*-1,0)))) as Netpsvalue,Sum((Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Final_rat1-foSauda.Oldrate),foSauda.Quantity*(foSauda.Final_rat1-foSauda.Market)),Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Oldrate-foSauda.Final_rat1),foSauda.Quantity*(foSauda.Market-foSauda.Final_rat1))))+foSauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From foSauda foSauda Where foSauda.Firmnumber='ACML-00001' and foSauda.Egroup!='MB' and foSauda.Egroup!='YC' and foSauda.Egroup!='MO' and foSauda.Egroup!='OK' and foSauda.Egroup!='ST' and foSauda.Sterminal!='99999' and foSauda.Sterminal!='99998' and fosauda.saudatype != 'B/' and fosauda.oowncode!='F12838' Group by fosauda.oowncode,fosauda.brcode ) group by brcode order by sum(Totalvol) desc

JV for specific code

Select journal.jvdate as jv_date, ' ' as jvdate,Journdc.Jvoowncode as oowncode, journal.jvnumber as jvnumber,journal.jvnumber as jvnumchk, Decode(Journdc.Jvpos,'Dr',Ldfibs.Fibsacct,Rpad(' ',4)||Ldfibs.Fibsacct) as jvname, journdc.jvpos as jvpos,journdc.jvdebit as jvdebit,journdc.jvcredit as jvcredit,Rpad(' ',184) as Descript, journal.exchange as exchange,journal.booktype as booktype,journal.vallan as vallan,journal.special as special, journdc.Jvdesc1 as Jvdesc1,Journdc.Jvdesc2 as Jvdesc2,Journdc.Jvdesc3 as Jvdesc3,Journdc.Jvdesc4 as Jvdesc4,Journal.nJournaltype From journal journal,journdc journdc,Ldfibs Ldfibs where Journal.Jvdate>=:V001 and Journal.Jvdate<=:V002 and Journdc.Jvdate>=:V003 and Journdc.Jvdate<=:V004 and Journal.Jvnumber=Journdc.Jvnumber and Journal.Firmnumber='TIS-000001' and Journdc.Firmnumber='TIS-000001' and Journal.nEntrytype=:V005 and Journdc.Firmnumber=Ldfibs.Firmnumber and Journdc.Jvoowncode=Ldfibs.Oowncode




Select distinct journal.jvdate as jv_date, Journdc.Jvoowncode as oowncode, journal.jvnumber as jvnumber,journal.jvnumber as jvnumchk,
Decode(Journdc.Jvpos,'Dr',Ldfibs.Fibsacct,Rpad(' ',4)||Ldfibs.Fibsacct) as jvname, journdc.jvpos as jvpos,journdc.jvdebit as
jvdebit,journdc.jvcredit as jvcredit,Rpad(' ',184) as Descript, journal.exchange as exchange,journal.booktype as
booktype,journal.vallan as vallan,journal.special as special, journdc.Jvdesc1 as Jvdesc1,Journdc.Jvdesc2 as
Jvdesc2,Journdc.Jvdesc3 as Jvdesc3,Journdc.Jvdesc4 as Jvdesc4,Journal.nJournaltype
From
journal journal,journdc journdc,Ldfibs Ldfibs where Journal.Jvnumber=Journdc.Jvnumber and Journal.Firmnumber='TIS-000001'
and Journdc.Firmnumber='TIS-000001' and Journdc.Firmnumber=Ldfibs.Firmnumber and Journdc.Jvoowncode=Ldfibs.Oowncode
and oowncode like '%GINT%'

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;

Acounts Brokerage

----------------JOBBING Brokerage-----------------------

select ACCOUNTDETAIL.OOWNCODE , cljobb.brokcode, cljobb.DVALIDUPTO from ldbo.cljobb, LDBO.ACCOUNTDETAIL WHERE cljobb.firmnumber='SNS-000001' and cljobb.exchange='NSE' AND cljobb.BOOKCODE='CA' AND ACCOUNTDETAIL.ACTIVE='Y' AND ACCOUNTDETAIL.CODE = cljobb.CODE AND ACCOUNTDETAIL.FIRMNUMBER = cljobb.FIRMNUMBER ORDER BY ACCOUNTDETAIL.OOWNCODE



-------------------option brokerage-----------

select ACCOUNTDETAIL.OOWNCODE , cljobb.brokcode, cljobb.DVALIDUPTO from ldbo.cljobb, LDBO.ACCOUNTDETAIL WHERE cljobb.firmnumber='SNS-000001' and cljobb.exchange='NSEF' AND cljobb.BOOKCODE='OP' AND ACCOUNTDETAIL.ACTIVE='Y' AND ACCOUNTDETAIL.CODE = cljobb.CODE AND ACCOUNTDETAIL.FIRMNUMBER = cljobb.FIRMNUMBER ORDER BY ACCOUNTDETAIL.OOWNCODE

--------------------future brokerage-------------

select ACCOUNTDETAIL.OOWNCODE , cljobb.brokcode, cljobb.DVALIDUPTO from ldbo.cljobb, LDBO.ACCOUNTDETAIL WHERE cljobb.firmnumber='SNS-000001' and cljobb.exchange='NSEF' AND cljobb.BOOKCODE='FU' AND ACCOUNTDETAIL.ACTIVE='Y' AND ACCOUNTDETAIL.CODE = cljobb.CODE AND ACCOUNTDETAIL.FIRMNUMBER = cljobb.FIRMNUMBER ORDER BY ACCOUNTDETAIL.OOWNCODE


--------------delivery borkerage------------

select ACCOUNTDETAIL.OOWNCODE , cldeliv.brokcode,cldeliv.DVALIDUPTO from ldbo.cldeliv, LDBO.ACCOUNTDETAIL WHERE cldeliv.firmnumber='SNS-000001' and cldeliv.bookcode='CA' AND ACCOUNTDETAIL.ACTIVE='Y' AND cldeliv.EXCHANGE='NSE' AND ACCOUNTDETAIL.CODE = cldeliv.CODE AND ACCOUNTDETAIL.FIRMNUMBER = cldeliv.FIRMNUMBER ORDER BY ACCOUNTDETAIL.OOWNCODE


--------------currency brokerage-----------------

select ACCOUNTDETAIL.OOWNCODE , cljobb.brokcode, cljobb.DVALIDUPTO from ldbo.cljobb, LDBO.ACCOUNTDETAIL WHERE cljobb.firmnumber='SNS-000001' and cljobb.exchange='NSEC' AND cljobb.BOOKCODE='FU' AND ACCOUNTDETAIL.ACTIVE='Y' AND ACCOUNTDETAIL.CODE = cljobb.CODE AND ACCOUNTDETAIL.FIRMNUMBER = cljobb.FIRMNUMBER ORDER BY ACCOUNTDETAIL.OOWNCODE

Client Wrong Email ID

select distinct trim(accountemaildetail.oowncode),trim(accountemaildetail.email) from accountemaildetail,accountdetail
where
email like '%@@%'
or email like '_@%'
or email like '.%'
or email like '_@%'
or email like '%uniconglobe.com'
or email like '%.ocm'
or email like '%@uniconindia.com'
and accountemaildetail.firmnumber =accountdetail.firmnumber
and accountemaildetail.firmnumber='SNS-000001'
and accountemaildetail.oowncode =accountdetail.oowncode
AND ACCOUNTDETAIL.ACTIVE = 'Y'
or not REGEXP_LIKE (trim(email), '^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$')

Client Details

-----------Client complete details--------------------

SELECT distinct ACCOUNTS.OOWNCODE "CLIENT ID",
TRIM(ACCOUNTS.FIBSACCT) "Client NAME",
TRIM(ACCOUNTDETAIL.BRCODE) "BRANCH",
TRIM(ACCOUNTS.family) "FAMILY",
TRIM(accountaddressdetail.REFERRED) "REFERRED BY",
TRIM(accountaddressdetail.pangir) "PAN NO.",
TRIM(TBLCLIENTUCCDETAIL.Ddateofbirth) "DOB",
trim(ACCOUNTADDRESSDETAIL.address1) "ADDRESS1",
trim(ACCOUNTADDRESSDETAIL.address2) "ADDRESS2",
trim(ACCOUNTADDRESSDETAIL.address3) "ADDRESS3",
TRIM(ACCOUNTADDRESSDETAIL.CITY) "CITY",
TRIM(ACCOUNTADDRESSDETAIL.CSTATE) "STATE",
TRIM(ACCOUNTADDRESSDETAIL.PINCODE) "PINCODE",
TRIM(ACCOUNTADDRESSDETAIL.cstdno) "STD NO.",
TRIM(ACCOUNTADDRESSDETAIL.tel1) "TEL NO. 1",
TRIM(ACCOUNTADDRESSDETAIL.tel2) "TEL NO. 2",
TRIM(accountaddressdetail.mobile) "MOBILE NO.",
TRIM(accountemaildetail.email) "CLIENTEMAIL",
TRIM(ACCOUNTGENSPLITDETAIL.DOPENINGDATE) "DATE OF ACTIVATION",
TRIM(ACCOUNTDETAIL.ACTIVE) "ACTIVE STATUS" ,
TRIM(ACCOUNTDETAIL.CECONFIRMATION) "E-CONFIRMATION ONLY",
accounts.brcode
FROM
LDBO.ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL,
LDBO.ACCOUNTDETAIL ACCOUNTDETAIL,
LDBO.ACCOUNTS ACCOUNTS,
LDBO.ACCOUNTGENSPLITDETAIL ACCOUNTGENSPLITDETAIL,
ldbo.accountemaildetail accountemaildetail,
ldbo.TBLCLIENTUCCDETAIL TBLCLIENTUCCDETAIL
WHERE
ACCOUNTADDRESSDETAIL.CODE = ACCOUNTDETAIL.CODE
AND ACCOUNTDETAIL.CODE = ACCOUNTS.CODE
AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTDETAIL.FIRMNUMBER
AND ACCOUNTDETAIL.FIRMNUMBER = ACCOUNTS.FIRMNUMBER
AND ACCOUNTADDRESSDETAIL.OOWNCODE = ACCOUNTDETAIL.OOWNCODE
AND ACCOUNTDETAIL.OOWNCODE = ACCOUNTS.OOWNCODE
AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTEMAILDETAIL.FIRMNUMBER
AND ACCOUNTADDRESSDETAIL.oowncode = ACCOUNTEMAILDETAIL.oowncode
AND ACCOUNTGENSPLITDETAIL.FIRMNUMBER = ACCOUNTS.FIRMNUMBER
AND ACCOUNTGENSPLITDETAIL.CODE = ACCOUNTS.CODE
and aCCOUNTADDRESSDETAIL.CODE = ACCOUNTDETAIL.CODE
AND ACCOUNTDETAIL.CODE = ACCOUNTS.CODE
AND ACCOUNTDETAIL.CODE = TBLCLIENTUCCDETAIL.CINTERNALCODE
AND ACCOUNTDETAIL.FIRMNUMBER = TBLCLIENTUCCDETAIL.CFIRMNUMBER
AND ACCOUNTDETAIL.OOWNCODE = TBLCLIENTUCCDETAIL.CCLIENTCODE
------AND ACCOUNTDETAIL.FIRMNUMBER='SNS-000001'
;

-------------------------------
SELECT distinct ACCOUNTDETAIL.FIRMNUMBER,ACCOUNTS.OOWNCODE "CLIENT ID", TRIM(ACCOUNTS.FIBSACCT) "Client NAME",TRIM(ACCOUNTDETAIL.BRCODE) "BRANCH",trim(ACCOUNTADDRESSDETAIL.address1) "ADDRESS1",trim(ACCOUNTADDRESSDETAIL.address2) "ADDRESS2",trim(ACCOUNTADDRESSDETAIL.address3) "ADDRESS3",TRIM(ACCOUNTADDRESSDETAIL.CITY) "CITY",TRIM(ACCOUNTADDRESSDETAIL.CSTATE) "STATE", TRIM(ACCOUNTADDRESSDETAIL.PINCODE) "PINCODE",TRIM(ACCOUNTADDRESSDETAIL.cstdno) "STD NO.",TRIM(ACCOUNTADDRESSDETAIL.tel1) "TEL NO. 1",TRIM(ACCOUNTADDRESSDETAIL.tel2) "TEL NO. 2",TRIM(accountaddressdetail.mobile) "MOBILE NO." FROM LDBO.ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL, LDBO.ACCOUNTDETAIL ACCOUNTDETAIL, LDBO.ACCOUNTS ACCOUNTS, LDBO.ACCOUNTGENSPLITDETAIL ACCOUNTGENSPLITDETAIL,ldbo.accountemaildetail accountemaildetail,ldbo.TBLCLIENTUCCDETAIL TBLCLIENTUCCDETAIL WHERE ACCOUNTADDRESSDETAIL.CODE = ACCOUNTDETAIL.CODE AND ACCOUNTDETAIL.CODE = ACCOUNTS.CODE AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTDETAIL.FIRMNUMBER AND ACCOUNTDETAIL.FIRMNUMBER = ACCOUNTS.FIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE = ACCOUNTDETAIL.OOWNCODE AND ACCOUNTDETAIL.OOWNCODE = ACCOUNTS.OOWNCODE AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTEMAILDETAIL.FIRMNUMBER AND ACCOUNTADDRESSDETAIL.oowncode = ACCOUNTEMAILDETAIL.oowncode AND ACCOUNTGENSPLITDETAIL.FIRMNUMBER = ACCOUNTS.FIRMNUMBER AND ACCOUNTGENSPLITDETAIL.CODE = ACCOUNTS.CODE and aCCOUNTADDRESSDETAIL.CODE = ACCOUNTDETAIL.CODE AND ACCOUNTDETAIL.CODE = ACCOUNTS.CODE AND ACCOUNTDETAIL.CODE = TBLCLIENTUCCDETAIL.CINTERNALCODE AND ACCOUNTDETAIL.FIRMNUMBER = TBLCLIENTUCCDETAIL.CFIRMNUMBER AND ACCOUNTDETAIL.OOWNCODE = TBLCLIENTUCCDETAIL.CCLIENTCODE AND ACCOUNTDETAIL.ACTIVE='Y' and ACCOUNTDETAIL.FIRMNUMBER = 'SNS-000001' order by ACCOUNTS.OOWNCODE



-------------client birthday mailer---------

SELECT distinct ACCOUNTS.OOWNCODE "CLIENT ID",TRIM(ACCOUNTS.FIBSACCT) "Client NAME",TRIM(ACCOUNTDETAIL.BRCODE) "BRANCH",TRIM(accountaddressdetail.REFERRED) "REFERRED BY",TRIM(to_char(TBLCLIENTUCCDETAIL.Ddateofbirth,'DD-Mon-YYYY')) "DOB",TRIM(accountemaildetail.email) "CLIENTEMAIL" FROM LDBO.ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL,LDBO.ACCOUNTDETAIL ACCOUNTDETAIL,LDBO.ACCOUNTS ACCOUNTS,LDBO.ACCOUNTGENSPLITDETAIL ACCOUNTGENSPLITDETAIL,ldbo.accountemaildetail accountemaildetail,ldbo.TBLCLIENTUCCDETAIL TBLCLIENTUCCDETAIL WHERE ACCOUNTADDRESSDETAIL.CODE = ACCOUNTDETAIL.CODE AND ACCOUNTDETAIL.CODE = ACCOUNTS.CODE AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTDETAIL.FIRMNUMBER AND ACCOUNTDETAIL.FIRMNUMBER = ACCOUNTS.FIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE = ACCOUNTDETAIL.OOWNCODE AND ACCOUNTDETAIL.OOWNCODE = ACCOUNTS.OOWNCODE AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTEMAILDETAIL.FIRMNUMBER AND ACCOUNTADDRESSDETAIL.oowncode = ACCOUNTEMAILDETAIL.oowncode AND ACCOUNTGENSPLITDETAIL.FIRMNUMBER = ACCOUNTS.FIRMNUMBER AND ACCOUNTGENSPLITDETAIL.CODE = ACCOUNTS.CODE and aCCOUNTADDRESSDETAIL.CODE = ACCOUNTDETAIL.CODE AND ACCOUNTDETAIL.CODE = ACCOUNTS.CODE AND ACCOUNTDETAIL.CODE = TBLCLIENTUCCDETAIL.CINTERNALCODE AND ACCOUNTDETAIL.FIRMNUMBER = TBLCLIENTUCCDETAIL.CFIRMNUMBER AND ACCOUNTDETAIL.OOWNCODE = TBLCLIENTUCCDETAIL.CCLIENTCODE AND ACCOUNTDETAIL.ACTIVE='Y' AND TRIM(to_char(TBLCLIENTUCCDETAIL.Ddateofbirth,'DD-Mon'))=TRIM(to_char(SYSDATE,'DD-Mon'))

---------------------client mailer---------------

SELECT distinct ACCOUNTADDRESSDETAIL.OOWNCODE "Code",accountemaildetail.email FROM LDBO.ACCOUNTS ACCOUNTS,LDBO.ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL, ldbo.accountemaildetail accountemaildetail,LDBO.ACCOUNTDETAIL ACCOUNTDETAIL WHERE ACCOUNTS.CODE = ACCOUNTADDRESSDETAIL.CODE AND ACCOUNTS.CODE = ACCOUNTDETAIL.CODE AND ACCOUNTS.FIRMNUMBER = ACCOUNTDETAIL.FIRMNUMBER AND ACCOUNTS.FIRMNUMBER = ACCOUNTADDRESSDETAIL.FIRMNUMBER AND ACCOUNTADDRESSDETAIL.CODE = ACCOUNTemailDETAIL.CODE AND ACCOUNTADDRESSDETAIL.FIRMNUMBER = ACCOUNTEMAILDETAIL.FIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE = ACCOUNTEMAILDETAIL.OOWNCODE and ACCOUNTADDRESSDETAIL.OOWNCODE not like ('EMP%') and accountemaildetail.email not like '%@uniconglobal.com%' and accountemaildetail.email!=' ' ORDER BY ACCOUNTADDRESSDETAIL.OOWNCODE

Daily Client Brokerage with Branch

---------------------Daily Client Brokerage with Branch----------------------------------
select distinct ClientCode,Brcode,Dtoftran, sum(Grosspurch) Purchases, sum(Grosssales) Sales, sum(Totalvol) GrossTurnover, sum(Netpsvalue) NetTurnover, sum(Brokerage) Brokerage, sum(Volper), sum(NetBrokerage), sum(Netvolper), sum(nTurnpie), sum(nGrossbrkpie),sum(nNetbrkpie) from (Select sauda.oowncode ClientCode,sauda.brcode Brcode,Sauda.Dtoftran as Dtoftran, Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosspurch,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosssales,Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Orignalno,0, Sauda.Quantity*Sauda.Market,0))) as Totalvol, Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Buysell,'B', Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0), Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market*-1,0)))) as Netpsvalue,Sum((Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Final_rat1-Sauda.Oldrate),Sauda.Quantity*(Sauda.Final_rat1-Sauda.Market)),Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Oldrate-Sauda.Final_rat1),Sauda.Quantity*(Sauda.Market-Sauda.Final_rat1))))+Sauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From Sauda Sauda Where Sauda.Firmnumber='SNS-000001' and Sauda.Egroup!='MB' and Sauda.Egroup!='YC' and Sauda.Egroup!='MO' and Sauda.Egroup!='OK' and Sauda.Egroup!='ST' and Sauda.Sterminal!='99999' and Sauda.Sterminal!='99998' and trim(dtoftran)=trunc(SYSDATE) Group by Sauda.Dtoftran,sauda.oowncode,sauda.brcode Union Select fosauda.oowncode ClientCode,fosauda.brcode Brcode,foSauda.Dtoftran as Dtoftran, Sum(Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosspurch,Sum(Decode(foSauda.Buysell,'S',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosssales,Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Orignalno,0, foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0))) as Totalvol, Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Buysell,'B', Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0), Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice)*-1,0)))) as Netpsvalue,Sum((Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Final_rat1-foSauda.Oldrate),foSauda.Quantity*(foSauda.Final_rat1-foSauda.Market)),Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Oldrate-foSauda.Final_rat1),foSauda.Quantity*(foSauda.Market-foSauda.Final_rat1))))+foSauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From foSauda foSauda Where foSauda.Firmnumber='SNS-000001' and foSauda.Egroup!='MB' and foSauda.Egroup!='YC' and foSauda.Egroup!='MO' and foSauda.Egroup!='OK' and foSauda.Egroup!='ST' and foSauda.Sterminal!='99999' and foSauda.Sterminal!='99998' and fosauda.saudatype != 'B/' and fosauda.oowncode!='F12838' and trim(dtoftran)=trunc(SYSDATE) Group by foSauda.Dtoftran,fosauda.oowncode,fosauda.brcode ) group by clientcode,brcode,dtoftran

-----------Total_Brokerage_Datewise-----------------
select sum(brokerage) "Total Brokerage",dtoftran "Date" from (Select /*+ FULL(Sauda) */ Sauda.Dtoftran as Dtoftran, Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosspurch,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosssales,Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Orignalno,0, Sauda.Quantity*Sauda.Market,0))) as Totalvol, Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Buysell,'B', Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0), Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market*-1,0)))) as Netpsvalue,Sum((Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Final_rat1-Sauda.Oldrate),Sauda.Quantity*(Sauda.Final_rat1-Sauda.Market)),Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Oldrate-Sauda.Final_rat1),Sauda.Quantity*(Sauda.Market-Sauda.Final_rat1))))+Sauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From Sauda Sauda Where Sauda.Firmnumber='SNS-000001' and Sauda.Egroup!='MB' and Sauda.Egroup!='YC' and Sauda.Egroup!='MO' and Sauda.Egroup!='OK' and Sauda.Egroup!='ST' and Sauda.Sterminal!='99999' and Sauda.Sterminal!='99998' Group by Sauda.Dtoftran Union Select /*+ FULL(foSauda) */ foSauda.Dtoftran as Dtoftran, Sum(Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*foSauda.Market,0)),0)) as Grosspurch,Sum(Decode(foSauda.Buysell,'S',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*foSauda.Market,0)),0)) as Grosssales,Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Orignalno,0, foSauda.Quantity*foSauda.Market,0))) as Totalvol, Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Buysell,'B', Decode(foSauda.Orignalno,0,foSauda.Quantity*foSauda.Market,0), Decode(foSauda.Orignalno,0,foSauda.Quantity*foSauda.Market*-1,0)))) as Netpsvalue,Sum((Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Final_rat1-foSauda.Oldrate),foSauda.Quantity*(foSauda.Final_rat1-foSauda.Market)),Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Oldrate-foSauda.Final_rat1),foSauda.Quantity*(foSauda.Market-foSauda.Final_rat1))))+foSauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From foSauda foSauda Where foSauda.Firmnumber='SNS-000001' and foSauda.Egroup!='MB' and foSauda.Egroup!='YC' and foSauda.Egroup!='MO' and foSauda.Egroup!='OK' and foSauda.Egroup!='ST' and foSauda.Sterminal!='99999' and foSauda.Sterminal!='99998' Group by foSauda.Dtoftran ) group by dtoftran order by 2 desc

--------------------Daily Branch Brokerage--------------
select distinct Brcode,Dtoftran, sum(Grosspurch) Purchases, sum(Grosssales) Sales, sum(Totalvol) GrossTurnover, sum(Netpsvalue) NetTurnover, sum(Brokerage) Brokerage, sum(Volper), sum(NetBrokerage), sum(Netvolper), sum(nTurnpie), sum(nGrossbrkpie),sum(nNetbrkpie) from (Select sauda.brcode Brcode,Sauda.Dtoftran as Dtoftran, Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosspurch,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosssales,Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Orignalno,0, Sauda.Quantity*Sauda.Market,0))) as Totalvol, Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Buysell,'B', Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0), Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market*-1,0)))) as Netpsvalue,Sum((Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Final_rat1-Sauda.Oldrate),Sauda.Quantity*(Sauda.Final_rat1-Sauda.Market)),Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Oldrate-Sauda.Final_rat1),Sauda.Quantity*(Sauda.Market-Sauda.Final_rat1))))+Sauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From Sauda Sauda Where Sauda.Firmnumber='SNS-000001' and Sauda.Egroup!='MB' and Sauda.Egroup!='YC' and Sauda.Egroup!='MO' and Sauda.Egroup!='OK' and Sauda.Egroup!='ST' and Sauda.Sterminal!='99999' and Sauda.Sterminal!='99998' and trim(dtoftran)=trunc(SYSDATE-1) Group by Sauda.Dtoftran,sauda.brcode Union Select fosauda.brcode Brcode,foSauda.Dtoftran as Dtoftran, Sum(Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosspurch,Sum(Decode(foSauda.Buysell,'S',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosssales,Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Orignalno,0, foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0))) as Totalvol, Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Buysell,'B', Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0), Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice)*-1,0)))) as Netpsvalue,Sum((Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Final_rat1-foSauda.Oldrate),foSauda.Quantity*(foSauda.Final_rat1-foSauda.Market)),Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Oldrate-foSauda.Final_rat1),foSauda.Quantity*(foSauda.Market-foSauda.Final_rat1))))+foSauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From foSauda foSauda Where foSauda.Firmnumber='SNS-000001' and foSauda.Egroup!='MB' and foSauda.Egroup!='YC' and foSauda.Egroup!='MO' and foSauda.Egroup!='OK' and foSauda.Egroup!='ST' and foSauda.Sterminal!='99999' and foSauda.Sterminal!='99998' and fosauda.saudatype != 'B/' and fosauda.oowncode!='F12838' and trim(dtoftran)=trunc(SYSDATE-1) Group by foSauda.Dtoftran,fosauda.oowncode,fosauda.brcode ) group by brcode,dtoftran

------------------------Daily Family Brokerage------------------

select distinct family,Dtoftran, sum(Grosspurch) Purchases, sum(Grosssales) Sales, sum(Totalvol) GrossTurnover, sum(Netpsvalue) NetTurnover, sum(Brokerage) Brokerage, sum(Volper), sum(NetBrokerage), sum(Netvolper), sum(nTurnpie), sum(nGrossbrkpie),sum(nNetbrkpie) from (Select ldfibs.family,Sauda.Dtoftran as Dtoftran, Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosspurch,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' ,0, Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0)),0)) as Grosssales,Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Orignalno,0, Sauda.Quantity*Sauda.Market,0))) as Totalvol, Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Buysell,'B', Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market,0), Decode(Sauda.Orignalno,0,Sauda.Quantity*Sauda.Market*-1,0)))) as Netpsvalue,Sum((Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Final_rat1-Sauda.Oldrate),Sauda.Quantity*(Sauda.Final_rat1-Sauda.Market)),Decode(Sauda.Saudatype,'N',Sauda.Quantity*(Sauda.Oldrate-Sauda.Final_rat1),Sauda.Quantity*(Sauda.Market-Sauda.Final_rat1))))+Sauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From Sauda Sauda,ldfibs ldfibs Where Sauda.Firmnumber='SNS-000001' and Sauda.Egroup!='MB' and Sauda.Egroup!='YC' and Sauda.Egroup!='MO' and Sauda.Egroup!='OK' and Sauda.Egroup!='ST' and Sauda.Sterminal!='99999' and Sauda.Sterminal!='99998' and trim(dtoftran)=trunc(SYSDATE-1) and sauda.oowncode=ldfibs.oowncode and sauda.brcode=ldfibs.brcode Group by ldfibs.family,Sauda.Dtoftran Union Select ldfibs.family,foSauda.Dtoftran as Dtoftran, Sum(Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosspurch,Sum(Decode(foSauda.Buysell,'S',Decode(foSauda.Saudatype,'N' ,0, Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0)),0)) as Grosssales,Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Orignalno,0, foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0))) as Totalvol, Sum(Decode(foSauda.Saudatype,'N' ,0,Decode(foSauda.Buysell,'B', Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice),0), Decode(foSauda.Orignalno,0,foSauda.Quantity*(foSauda.Market + foSauda.strikeprice)*-1,0)))) as Netpsvalue,Sum((Decode(foSauda.Buysell,'B',Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Final_rat1-foSauda.Oldrate),foSauda.Quantity*(foSauda.Final_rat1-foSauda.Market)),Decode(foSauda.Saudatype,'N',foSauda.Quantity*(foSauda.Oldrate-foSauda.Final_rat1),foSauda.Quantity*(foSauda.Market-foSauda.Final_rat1))))+foSauda.Brokpercontract) as Brokerage,000.0000 as Volper,0000000000.00 as NetBrokerage,000.0000 as Netvolper, 000.0000 as nTurnpie,000.0000 as nGrossbrkpie,000.0000 as nNetbrkpie From foSauda foSauda, ldfibs ldfibs Where foSauda.Firmnumber='SNS-000001' and foSauda.Egroup!='MB' and foSauda.Egroup!='YC' and foSauda.Egroup!='MO' and foSauda.Egroup!='OK' and foSauda.Egroup!='ST' and foSauda.Sterminal!='99999' and foSauda.Sterminal!='99998' and fosauda.saudatype != 'B/' and fosauda.oowncode!='F12838' and fosauda.oowncode=ldfibs.oowncode and fosauda.brcode=ldfibs.brcode and trim(dtoftran)=trunc(SYSDATE-1) Group by ldfibs.family,foSauda.Dtoftran,fosauda.oowncode ) group by family,dtoftran

Trade Confirmation Query

---------------------bse-------------------------
XLODBC
1
DSN=sns1011odbc;UID=;Pwd=;ConnectString=sns1011srv;
SELECT sauda.exchcode, sauda.vallan, sauda.brcode, sauda.oowncode, a.fibsacct "Name", aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email,sauda.compcode "Scrip Code", company.fibsacct "Scrip Name", SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)) "P.QTY", ROUND(SUM(decode(sauda.buysell, 'B', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / decode(SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)), 0, 1, SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0))), 2) "P.avg",SUM(decode(sauda.buysell, 'S', sauda.quantity, 0)) "S.QTY", ROUND(SUM(decode(sauda.buysell, 'S', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / SUM(decode(sauda.buysell, 'S', sauda.quantity)), 2) "S.avg" FROM sauda sauda, company company, ldbo.markclose m, ldbo.accounts a, ldbo.accountaddressdetail aa, ldbo.ACCOUNTEMAILDETAIL aaa WHERE sauda.firmnumber = 'SNS-000001' AND sauda.exchcode = 'BSE' AND sauda.dtoftran = TRUNC(sysdate) AND sauda.firmnumber = a.firmnumber AND sauda.oowncode = a.oowncode AND sauda.firmnumber = aa.firmnumber AND sauda.oowncode = aa.oowncode AND sauda.compcode = company.oowncode AND sauda.firmnumber = company.firmnumber AND sauda.firmnumber = m.firmnumber AND company.firmnumber = m.firmnumber AND sauda.compcode = m.code AND sauda.saudatype = 'N' and aa.oowncode=aaa.oowncode and aa.firmnumber=aaa.firmnumber GROUP BY sauda.exchcode, sauda.vallan, sauda.compcode, company.fibsacct, sauda.oowncode, m.market, sauda.vallan, a.fibsacct, aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email, sauda.brcode, sauda.dtoftran ORDER BY sauda.oowncode


---------------------nse-------------------------

XLODBC
1
DSN=sns1011odbc;UID=;Pwd=;ConnectString=sns1011srv;
SELECT sauda.exchcode, sauda.vallan, sauda.brcode, sauda.oowncode, a.fibsacct "Name", aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email,sauda.compcode "Scrip Code", company.fibsacct "Scrip Name", SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)) "P.QTY", ROUND(SUM(decode(sauda.buysell, 'B', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / decode(SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)), 0, 1, SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0))), 2) "P.avg",SUM(decode(sauda.buysell, 'S', sauda.quantity, 0)) "S.QTY", ROUND(SUM(decode(sauda.buysell, 'S', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / SUM(decode(sauda.buysell, 'S', sauda.quantity)), 2) "S.avg" FROM sauda sauda, ldbo.ACCOUNTEMAILDETAIL aaa, company company, ldbo.markclose m, ldbo.accounts a, ldbo.accountaddressdetail aa WHERE sauda.firmnumber = 'SNS-000001' AND sauda.exchcode = 'NSE' AND sauda.egroup IN('NN', 'NW') AND sauda.dtoftran = TRUNC(sysdate) AND sauda.firmnumber = a.firmnumber AND sauda.oowncode = a.oowncode AND sauda.firmnumber = aa.firmnumber AND sauda.oowncode = aa.oowncode AND sauda.compcode = company.oowncode AND sauda.firmnumber = company.firmnumber AND sauda.firmnumber = m.firmnumber AND company.firmnumber = m.firmnumber AND sauda.compcode = m.code AND sauda.saudatype = 'N' and aa.firmnumber=aaa.firmnumber and aa.oowncode=aaa.oowncode GROUP BY sauda.exchcode, sauda.vallan, sauda.compcode, company.fibsacct, sauda.oowncode, m.market, sauda.vallan, a.fibsacct, aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email, sauda.brcode, sauda.dtoftran ORDER BY sauda.oowncode

------------------------nsef------------------------

SELECT fotransactions.exchcode,fotransactions.saudatype,a.brcode,fotransactions.oowncode,a.fibsacct,aa.cstdno,aa.tel1,aa.mobile,aaa.email,fotransactions.compcode "Scrip Code", company.fibsacct "Scrip Name",company.expirydate,fotransactions.strikeprice,fotransactions.optiontype,SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)) "P.QTY",ROUND(SUM(decode(fotransactions.buysell, 'B', decode(fotransactions.saudatype, 'N',(fotransactions.market -fotransactions.havala_rate) *fotransactions.quantity, fotransactions.quantity *fotransactions.market), 0)) / decode(SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)), 0, 1, SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0))), 2) "P.avg",SUM(decode(fotransactions.buysell, 'S', fotransactions.quantity, 0)) "S.QTY",ROUND(SUM(decode(fotransactions.buysell, 'S', decode(fotransactions.saudatype, 'N',(fotransactions.market -fotransactions.havala_rate) *fotransactions.quantity, fotransactions.quantity *fotransactions.market), 0)) / SUM(decode(fotransactions.buysell, 'S', fotransactions.quantity)), 2) "S.avg" FROM ldbo.fotransactions fotransactions,company company,ldbo.accounts a,accountaddressdetail aa,ldbo.ACCOUNTEMAILDETAIL aaa WHERE fotransactions.firmnumber = 'SNS-000001' AND fotransactions.saudatype != 'B/' AND fotransactions.exchcode = 'NSEF' AND fotransactions.dtoftran = TRUNC(sysdate) AND fotransactions.firmnumber = a.firmnumber AND fotransactions.oowncode = a.oowncode AND fotransactions.firmnumber = aa.firmnumber AND fotransactions.oowncode = aa.oowncode AND fotransactions.compcode = company.oowncode AND fotransactions.firmnumber = company.firmnumber AND fotransactions.saudatype != 'B/' AND a.firmnumber = 'SNS-000001' and aa.oowncode=aaa.oowncode and aa.firmnumber=aaa.firmnumber GROUP BY fotransactions.exchcode,aa.cstdno,aa.tel1,aa.mobile,a.fibsacct,a.brcode,company.expirydate,fotransactions.compcode,fotransactions.saudatype,fotransactions.optiontype, company.fibsacct,fotransactions.strikeprice,fotransactions.oowncode,fotransactions.dtoftran,aaa.email ORDER BY fotransactions.oowncode

-----------------------nsec---------------------
select fotransactions.EXCHCODE,fotransactions.saudatype,A.BRCODE,fotransactions.OOWNCODE,A.FIBSACCT,Aa.CSTDNO,Aa.tel1,aa.mobile,aaa.email,fotransactions.compcode "Scrip Code",company.fibsacct "Scrip Name",company.EXPIRYDATE,fotransactions.strikeprice,fotransactions.OPTIONTYPE,ROUND(SUM(DECODE(BUYSELL,'B',DECODE(EGROUP,'NA',0,QUANTITY),0))/COMPANY.MARKLOT,2) "P.QTY",round(Sum(Decode(fotransactions.Buysell,'B',Decode(fotransactions.saudatype,'N' ,(fotransactions.MARKET-fotransactions.Havala_rate)*fotransactions.Quantity,fotransactions.Quantity*fotransactions.MARKET),0))/DECODE(SUM(DECODE(BUYSELL,'B',DECODE(EGROUP,'NA',0,QUANTITY),0)),0,1,SUM(DECODE(BUYSELL,'B',DECODE(EGROUP,'NA',0,QUANTITY),0))),4) "P.avg", ROUND(Sum(Decode(fotransactions.Buysell,'S',fotransactions.Quantity,0))/COMPANY.MARKLOT,2) "S.QTY", round(Sum(Decode(fotransactions.Buysell,'S',Decode(fotransactions.saudatype,'N' ,(fotransactions.MARKET-fotransactions.Havala_rate)*fotransactions.Quantity,fotransactions.Quantity*fotransactions.MARKET),0))/Sum(Decode(fotransactions.Buysell,'S',fotransactions.Quantity)),4) "S.avg" FROM ldbo.fotransactions fotransactions,COMPANY,ldbo.accounts a,ldbo.accountaddressdetail aa,ldbo.ACCOUNTEMAILDETAIL aaa WHERE fotransactions.FIRMNUMBER = 'SNS-000001' and fotransactions.saudatype !='B/' and fotransactions.EXCHCODE='NSEC'and fotransactions.dtoftran=TRUNC(sysdate -1) and fotransactions.firmnumber=a.firmnumber and fotransactions.oowncode=a.oowncode and fotransactions.firmnumber=aa.firmnumber and fotransactions.oowncode=aa.oowncode AND fotransactions.COMPCODE = COMPANY.OOWNCODE AND fotransactions.FIRMNUMBER = COMPANY.FIRMNUMBER and fotransactions.saudatype !='B/' and a.FIRMNUMBER = 'SNS-000001' and aa.oowncode=aaa.oowncode and aa.firmnumber=aaa.firmnumber GROUP BY fotransactions.EXCHCODE,Aa.CSTDNO,Aa.tel1,aa.mobile,aaa.email,COMPANY.MARKLOT,A.FIBSACCT,A.BRCODE,company.EXPIRYDATE,fotransactions.compcode,fotransactions.saudatype,fotransactions.OPTIONTYPE,COMPANY.FIBSACCT,fotransactions.strikeprice,fotransactions.OOWNCODE order by fotransactions.OOWNCODE

CLIENT NONTRADING DAYS CASH

Select Ldfibs.Oowncode as Oowncode,Ldfibs.Fibsacct as Fibsacct,Ldfibs.cTermcode as cTermcode,Ldfibs.Brcode as Brcode, Accountgensplit.Dopeningdate,Max(Sauda.Dtoftran) as dLastTradeDate,Round((Sysdate-Max(Sauda.Dtoftran)-1), 0) as nNonTradeDays from Ldfibs Ldfibs, Sauda Sauda, Accountgensplit Accountgensplit,(Select Ldfibs.Oowncode as Oowncode From Ldfibs Where Ldfibs.Firmnumber ='ACML-00001' Minus Select Distinct Sauda.Oowncode as Oowncode From Sauda Where Sauda.Firmnumber = 'ACML-00001' ) tblLdfibs Where Ldfibs.Firmnumber='ACML-00001' and Ldfibs.nfinancialyear='2010' and Ldfibs.Firmnumber=Sauda.Firmnumber(+) and Ldfibs.Oowncode=Sauda.Oowncode(+) and Ldfibs.Oowncode=tblLdfibs.Oowncode(+) and Ldfibs.Firmnumber=Accountgensplit.Firmnumber(+) and Ldfibs.Oowncode=Accountgensplit.Oowncode(+) and Accountgensplit.Dopeningdate is not null Group by Ldfibs.Oowncode, Ldfibs.cTermcode,Ldfibs.Fibsacct,Ldfibs.Brcode,Accountgensplit.Dopeningdate ;

LTM Monitoring

select distinct cexchcode EXCHANGE,
------max(nordertime) OTIME,
max(ntradetime)TTIME from TBLRKTRANHISTORY where trim(dtransactiondate)=trunc(sysdate) group by cexchcode;

Kill DLLhost from task Manager

When memory leaks occur in IIS scripts, you will find DLLHost.exe eating up a lot of memory and ultimately reaching a point where you will need to restart IIS to get the service working again… One way to contain this automatically is to use the Performance Logs and Alerts snapin.

1. Create a batch file that does nothing but run tskill /a dllhost
2. Surf to Control Panel | Administrative Tools | Performance
3. Open up Performance Logs and Alerts
4. Right-click on Alerts and click New Alert Settings. Name it “Auto-kill DLLHost”.
5. Click the “ADD” button, choose the computer, choose Memory and then create an alert for 1GB.

1. In the Action tab, choose Run this Program and set it to run the batch file you created above.

Now, every time memory crosses 1GB usage, the dllhost process is automatically killed.

DataBase Template FY 11-12

MULTIPURPOSE
20
40
false
WE8MSWIN1252
AL16UTF16
true
{ORACLE_BASE}\admin\{DB_UNIQUE_NAME}\pfile\init.ora

{ORACLE_HOME}\database\spfile{SID}.ora



100
16
3
1
8





INDX
false
true
0
35000
false
true
1000
-1


SYSAUX
false
true
0
450
true
true
10240
-1


SYSTEM
false
true
0
500
true
true
10240
-1


TEMPORARY
false
true
0
2000
true
true
640
-1


UNDOTBS1
false
true
0
2000
true
true
5120
-1


USR
false
true
0
55000
true
true
1000
-1


true
1
false
false
false
false
true
-1
1
-1
-1
-1
-1
-1
-2
-1
true
false
0
true
true


1




true
1
false
false
false
false
true
-1
1
-1
64
64
50
1
4096
64
true
false
0
true
false


-1




true
1
false
false
false
false
true
-1
3
-1
64
64
50
1
-1
64
true
false
0
true
false


-1




true
1
false
true
true
false
true
-1
1
-1
64
64
0
1
0
64
true
false
0
true
false


-1




true
1
false
false
false
true
true
-1
1
-1
512
512
50
8
4096
512
true
false
0
true
false


-1




true
1
false
false
false
false
true
-1
1
-1
128
128
0
1
4096
128
true
false
0
true
true


-1




false
204800
1



false
204800
1



false
204800
1

LD Branch Change Process

Branch Change Process

---------------------------------

File
Code Branch
22500T001|2760
22500T002|2760
22500V001|2760
22500V002|2760
580817183|8230
580817105|8230
580817135|8230

---------------------------------

Disconnect all LD users

---------------------------------

Error
225000000 TIMIR PATEL (COMM) 2760 Journal Voucher JVAUTOJ3097426 contains duplicate Clients

Search that JV into Ledger of Client 225000000

and copy jounral register entry(DR Cr) for that JV

if you forget the find it from MIS > journal audit delete


Delete Duplicate JVs
------------

again branch change

---------------------------------

if ODBC error

Client Branch Code change could not be Synchronized.


then run

set serveroutput on ;
execute sp_serseq('2010-2011') ;


---------------------------------

Enter journal entries again for deleted JVs

----------------

Which file include platform info about oracle server setup

\database\stage\prereq\prerequisite.properties

some instances on 10.2.0.1 and some on 10.2.0.3 on different servers. We have to create database for all instances on one server

Scenario
some instances on 10.2.0.1 and some on 10.2.0.3 on different servers. We have to create database for all instances on one server


Install 10.2.0.1
Apply patch set 10.2.0.3

Before you apply the patchset you have to check whether the Oracle Version is 32 bit or 64 bit Version, because
the patchsets are different.


SQL> SHUTDOWN IMMEDIATE.
Stop all the Oracle Related Services.
Ex:- Oracle Listener, Oracle DB Console,Oracle JobScheduler,
Distrubed Transaction Co-ordinator.
Run the Patch Setup.exe in the same Oracle Home.

------------------------startup upgrade for all instances------------
SQL> CONNECT SYS/oracle@ari0809srv AS SYSDBA

STARTUP UPGRADE
SPOOL c:\patch.log
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql
SQL> SPOOL OFF

SHUTDOWN IMMEDIATE
STARTUP
------------------------

@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlip.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql



mkdir D:\oracle\product\10.2.0\admin\ari0405\adump
mkdir D:\oracle\product\10.2.0\admin\ari0405\bdump
mkdir D:\oracle\product\10.2.0\admin\ari0405\cdump
mkdir D:\oracle\product\10.2.0\admin\ari0405\dpdump
mkdir D:\oracle\product\10.2.0\admin\ari0405\pfile
mkdir D:\oracle\product\10.2.0\admin\ari0405\udump


mkdir D:\oracle\product\10.2.0\admin\ari0506\adump
mkdir D:\oracle\product\10.2.0\admin\ari0506\bdump
mkdir D:\oracle\product\10.2.0\admin\ari0506\cdump
mkdir D:\oracle\product\10.2.0\admin\ari0506\dpdump
mkdir D:\oracle\product\10.2.0\admin\ari0506\pfile
mkdir D:\oracle\product\10.2.0\admin\ari0506\udump


mkdir D:\oracle\product\10.2.0\admin\ari0607\adump
mkdir D:\oracle\product\10.2.0\admin\ari0607\bdump
mkdir D:\oracle\product\10.2.0\admin\ari0607\cdump
mkdir D:\oracle\product\10.2.0\admin\ari0607\dpdump
mkdir D:\oracle\product\10.2.0\admin\ari0607\pfile
mkdir D:\oracle\product\10.2.0\admin\ari0607\udump

mkdir D:\oracle\product\10.2.0\admin\ari0708\adump
mkdir D:\oracle\product\10.2.0\admin\ari0708\bdump
mkdir D:\oracle\product\10.2.0\admin\ari0708\cdump
mkdir D:\oracle\product\10.2.0\admin\ari0708\dpdump
mkdir D:\oracle\product\10.2.0\admin\ari0708\pfile
mkdir D:\oracle\product\10.2.0\admin\ari0708\udump

mkdir D:\oracle\product\10.2.0\admin\ari0809\adump
mkdir D:\oracle\product\10.2.0\admin\ari0809\bdump
mkdir D:\oracle\product\10.2.0\admin\ari0809\cdump
mkdir D:\oracle\product\10.2.0\admin\ari0809\dpdump
mkdir D:\oracle\product\10.2.0\admin\ari0809\pfile
mkdir D:\oracle\product\10.2.0\admin\ari0809\udump


mkdir D:\oracle\product\10.2.0\admin\ari0910\adump
mkdir D:\oracle\product\10.2.0\admin\ari0910\bdump
mkdir D:\oracle\product\10.2.0\admin\ari0910\cdump
mkdir D:\oracle\product\10.2.0\admin\ari0910\dpdump
mkdir D:\oracle\product\10.2.0\admin\ari0910\pfile
mkdir D:\oracle\product\10.2.0\admin\ari0910\udump


copy paste init files to database folder and tnsnames, listener to network admin folder


set ORACLE_SID=ari2

oradim -new -sid ari2 -SRVC OracleServiceari2 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initari2.ORA

set ORACLE_SID=ari3

oradim -new -sid ari3 -SRVC OracleServiceari3 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initari3.ORA


set ORACLE_SID=ari4
oradim -new -sid ari4 -SRVC OracleServiceari4 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initari4.ORA


set ORACLE_SID=ari5
oradim -new -sid ari5 -SRVC OracleServiceari5 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initari5.ORA


set ORACLE_SID=ari6
oradim -new -sid ari6 -SRVC OracleServiceari6 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initari6.ORA


set ORACLE_SID=ari7
oradim -new -sid ari7 -SRVC OracleServiceari7 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initari7.ORA



sqlplusw sys/oracle@ari0405srv as sysdba
startup pfile='D:\oracle\product\10.2.0\db_1\database\initari2.ora';
create spfile from pfile;
shut immediate
startup

sqlplusw ldbo/ldbo@ari0405srv as sysdba




sqlplusw sys/oracle@ari0506srv as sysdba
startup pfile='D:\oracle\product\10.2.0\db_1\database\initari3.ora';
create spfile from pfile;
shut immediate
startup

sqlplusw ldbo/ldbo@ari0506srv as sysdba



sqlplusw sys/oracle@ari0607srv as sysdba
startup pfile='D:\oracle\product\10.2.0\db_1\database\initari4.ora';
create spfile from pfile;
shut immediate
startup

sqlplusw ldbo/ldbo@ari0607srv as sysdba



sqlplusw sys/oracle@ari0708srv as sysdba
startup pfile='D:\oracle\product\10.2.0\db_1\database\initari5.ora';
create spfile from pfile;
shut immediate
startup


sqlplusw ldbo/ldbo@ari0708srv as sysdba



sqlplusw sys/oracle@ari0809srv as sysdba
startup pfile='D:\oracle\product\10.2.0\db_1\database\initari6.ora';
create spfile from pfile;
shut immediate
startup


sqlplusw sys/oracle@ari0910srv as sysdba
startup pfile='D:\oracle\product\10.2.0\db_1\database\initari7.ora';
create spfile from pfile;
shut immediate
startup

Patch

Apply patch

Before you apply the patchset you have to check whether the Oracle Version is 32 bit or 64 bit Version, because
the patchsets are different.
For 32 Bit Version, Patch no is p6810189_10204_Win32_patchset
For 64 Bit Version, Patch no is p6810189_10204_MSWIN-x86-64.

1. Shut down the database:
SQL> SHUTDOWN IMMEDIATE.
Stop all the Oracle Related Services.
Ex:- Oracle Listener, Oracle DB Console,Oracle JobScheduler,
Distrubed Transaction Co-ordinator.
Run the Patch Setup.exe in the same Oracle Home.
For Example: If Existing Oracle is installed in c:\Oracle\product\10.2.0\db_1 then you
have to select the same path When you run the Setup.exe. After Successful installation
start the Listener & Db Console etc.,
Enter the following SQL*Plus commands:
SQL> SET ORACLE_SID=sns6
SQL> SQLPLUS/NOLOG
SQL> CONNECT SYS/LINUX@sns1011SRV AS SYSDBA
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF

@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102s.sql TO DISPLAY RESULT OF UPGRADE

Review the patch.log file for errors and inspect the list of components that is displayed at the end of
catupgrd.sql script.
This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.


SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are
accessed for the first time. This step is optional but recommended.

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlip.sql
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlirp.sql

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

result of upgrade

@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102s.sql

if owr is not installed and if require then instal manually

to install oracle workspace manager

select username from dba_users where username ='WMSYS';

ALTER USER WMSYS IDENTIFIED BY WMSYS ACCOUNT UNLOCK;

@%oracle_home%\RDBMS\ADMIN\owminst.plb

select comp_name,version,status from dba_registry;

Email Notification for LDBO login

CREATE OR REPLACE TRIGGER LDBO_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - LDBO Login';
l_message VARCHAR2(500);

BEGIN
if (USER in ('LDBO') AND SYS_CONTEXT('USERENV','Module') not IN ('vfp9.exe') AND SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) NOT IN ('192.168.0.25','192.168.1.84','192.168.1.199','192.168.1.64','192.168.1.126','192.168.1.127','192.168.1.95','192.168.1.163') ) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
l_message :=
l_message || 'Application Name: ' || SYS_CONTEXT('USERENV','Module') || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END LDBO_logon_notifications;
/

Oracle Database Incarnation, Open Resetlogs , SCN

Q. What happens when you run ALTER DATABASE OPEN RESETLOGS ?
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.
.

Q. In what scenarios open resetlogs required ?

An ALTER DATABASE OPEN RESETLOGS statement is required after incomplete recovery (Point in Time Recovery) or recovery with a backup control file.
.

Q. What is SCN (System Change Number) ?

The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.

Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.
.

Q. What is Database Incarnation ?

Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.

Database incarnation falls into following category Current, Parent, Ancestor and Sibling

i) Current Incarnation : The database incarnation in which the database is currently generating redo.

ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.

iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.

iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
.

Q. How to view incarnation history of Database ?

Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;

However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.

•For example my current database INCARNATION is 3 and now I have used
FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,

RMAN> RESET DATABASE TO INCARNATION 2;
RMAN> RECOVER DATABASE TO SCN 3000;

Multiplexing of Redo logs

SELECT group#, members, status, bytes FROM v$log;
SELECT group#, member, type FROM v$logfile;

ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO1.LOG' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO2.LOG' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO3.LOG' TO GROUP 3;


SELECT group#, members, status, bytes FROM v$log;
SELECT group#, member, type FROM v$logfile;

RMAN Configuration Channel Allocation

Automatic channel allocation:
CONFIGURE PARALLELISM FOR AUTOMATIC CHANNELS:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
CONFIGURE AUTOMATIC CHANNEL OPTIONS:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT='F:\BACKUP\%U';
CONFIGURING BACKUPPIECE SIZE:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ='F:\BACKUP\%U' MAXPIECESIZE
500M;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK
CHANGE THE DEFAULT DEVICE TYPE:
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURATION OF EXCLUDING TABLESPACE FROM DATABASE BACKUP:
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE ;
NOTE: when the following command is issued, it will skip the tablespace which is specified in the above
command. SYSTEM tablespace can not be excluded from whole database backup.
RMAN> BACKUP DATABASE;
NOTE: To overwrite this one,
RMAN> BACKUP DATABASE NOEXCLUDE;
EXCLUDING READ ONLY AND OFFLINE TABLESPACE:
RMAN> BACKUP DATABASE SKIP READONLY SKIP OFFLINE;
TO LIST THE CURRENT CONFIGURATION OF THE TARGET DATABASE:
RMAN> REPORT SCHEMA;
RETENTION POLICY:
There are two options for implementing a backup retention policy.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
NOTE: Default is redundancy 1.
RMAN> CONFIGURE RETENTION POLICY CLEAR;
Clear means it will take the default setting (i.e. Redundancy 1)
RMAN> CONFIGURE RETENTION POLICY TO NONE;
RMAN> REPORT OBSOLETE;
RMAN> DELETE OBSOLETE;
RMAN> SHOW RETENTION POLICY;
BACKUP OPTIMIZATION:
RMAN> SHOW BACKUP OPTIMIZATION;
the BACKUP command skips the backup of a file when the identical file backup exist
RMAN> CONFIGURE BACKUP OPTIMIZATION ON / OFF;
CONFIGURING DUPLEXED BACKUP SET:
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
SHOW COMMAND:
RMAN> SHOW CHANNEL;
RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN> SHOW BACKUP OPTIMIZATION;
RMAN> SHOW RETENTION POLICY;
RMAN> SHOW MAXSETSIZE;
RMAN> SHOW ALL;
LIST COMMAND:
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF TABLESPACE USERS;
RMAN> LIST BACKUP OF DATAFILE 1;
RMAN> LIST COPY;
RMAN> LIST COPY OF TABLESPACE USERS;
RMAN> LIST COPY OF DATAFILE 2;
REPORT COMMAND:
RMAN> REPORT SCHEMA;
RMAN> REPORT OBSOLETE;
RMAN> REPORT NEED BACKUP DAYS 5;
RMAN> REPORT NEED BACKUP REDUNDANCY 10;
RMAN> REPORT NEED BACKUP INCREMENTAL 3 DATABASE;
Manual channel allocation:
RMAN>RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT=’E:\DATAFILE3.BAK’;
BACKUP DATAFIE 3;
}
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='C:\BACKUP\%U','E:\BACKUP\%U','F:\BACKUP\%U';
BACKUP COPIES 3 DATAFILE 9;
}
.Creating different backup piece in different locations:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK FORMAT='F:\BACKUP\%U';
ALLOCATE CHANNEL C2 TYPE DISK FORMAT='F:\BACKUP\%U';
ALLOCATE CHANNEL C3 TYPE DISK FORMAT='F:\BACKUP\%U';
BACKUP DATAFILE 8;
}
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='F:\BACKUP\DATAFILE8_%c.BAK';
BACKUP COPIES 3 DATAFILE 8;
}
NOTE: In Format %c is mandatory
RMAN>RUN {
BACKUP AS BACKUPSET
FORMAT '/u01/db01/backup/%d_%s_%p'
DURATION 10:00 MINIMIZE LOAD
(DATABASE);
SQL 'alter system archive log current';
}
RMAN BACKUP:
RMAN> BACKUP AS BACKUPSET
FORMAT '/BACKUP/df_%d_%s_%p.bus'
TABLESPACE hr_data;
DATABASE BACKUP:
RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='F:\BACKUP\%U';
BACKUP DATABASE;
}
TABLESPACE BACKUP:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='F:\BACKUP\%U';
BACKUP TABLESPACE USERS,UNDOTBS1;
}
DATAFILE BACKUP:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='F:\BACKUP\%U';
BACKUP DATAFILE 7,8,9;
}
ARCHIVED REDOLOG BACKUP:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP TABLESPACE USERS PLUS ARCHIVELOG;
RMAN> BACKUP DATAFILE 7 PLUS ARCHIVELOG;
RMAN>BACKUP
FORMAT '/disk1/backup/ar_%t_%s_%p'
ARCHIVELOG FROM SEQUENCE=234
DELETE INPUT;
Multiplexed Backup Sets
RMAN>RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
BACKUP
INCREMENTAL LEVEL = 0
FORMAT '%u.bak'
(DATAFILE 1 CHANNEL c1)
(DATAFILE 2 CHANNEL c2) (DATAFILE 3 CHANNEL c3);
Sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
}
RMAN>RUN {
ALLOCATE CHANNEL c1 type disk;
COPY INCREMENTAL LEVEL = 0
DATAFILE 1 tag=DF3,
ARCHIVELOG all;
}
RMAN>BACKUP COPY OF DATABASE;
RMAN>ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f'
REUSE;
RMAN>RECOVER COPY OF
DATAFILE {n|'file_name'}
WITH TAG 'incr_upd_df1';
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK MAXOPENFILES=3;
BACKUP FILESPERSET 6 FORMAT 'F:\BACKUP\%U' DATABASE;
}
DUPLEXED BACKUP SET:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='C:\BACKUP\%U','C:\NEW\%U';
BACKUP COPIES 2 DATAFILE 2;
}
PARALLELIZATION OF BACKUP SETS:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE CHANNEL C2 TYPE DISK;
ALLOCATE CHANNEL C3 TYPE DISK;
BACKUP
FORMAT 'C:\BACKUP\%U'
(DATAFILE 1,2,3 CHANNEL C1)
(DATAFILE 4,5,6 CHANNEL C2)
(DATAFILE 7,8,9 CHANNEL C3);
}
BACKUP PIECE SIZE:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK MAXPIECESIZE=200M;
BACKUP FORMAT 'C:\BACKUP\%U' (TABLESPACE SYSTEM);
}
ARCHIVED REDOLOG BACKUP SETS:
RMAN> BACKUP
FORMAT 'C:\BACKUP\%U'
ARCHIVELOG ALL;
RMAN> BACKUP
FORMAT 'C:\BACKUP\%U'
ARCHIVELOG ALL DELETE ALL INPUT;
CONTROLFILE SPFILE BACKUP:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATAFILE 5 INCLUDE CURRENT CONTROLFILE;
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='C:\BACKUP\SPCONTROL.BAK';
BACKUP CURRENT CONTROLFILE;
}
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='C:\BACKUP\SPCONTROL.BAK';
BACKUP SPFILE;
}
TAGS FOR BACKUP AND IMAGE COPY:
RMAN> RUN
{
ALLOCATE CHANNEL C1 TYPE DISK
FORMAT='C:\BACKUP\DF7.BAK';
BACKUP DATAFILE 7 TAG='DF7';
}
RMAN> RUN
{
COPY
DATAFILE 6 TO 'C:\BACKUP\DF6.BAK' TAG='DF6';
}