System Run time Error

System Run time Error

LD is not able to open


use ?

D:\LD\SYSUSER\lderror.dbf

check error

Index does not match the table. Delete the index file and re-create the index.
Index file "m:\ld\sysuser\firm.cdx" tag "User_code" is corrupted. Please rebuild it.

firm.cdx is corrupt
replace it from backup

reindex

Note: never replace complete LD folder

GLOBAL(NET OUSTANDING DETAILS)

Select Sauda.Oowncode as Oowncode, Sauda.Compcode as Compcode,' ' as Ndel, Rpad(' ',20) as Companyname, Sauda.Exchcode as Exchcode,Sauda.Egroup as
Egroup,Rpad(' ',20) as Booktype,Sauda.Vallan as Vallan, Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' ,0,Sauda.Quantity),0)) as
Purchqty,Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N', (Sauda.Final_rat1-Sauda.Havala_rate)*Sauda.Quantity,Sauda.Quantity*Sauda.Final_rat1),0))
as Purchvalue,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' ,0,Sauda.Quantity),0)) as
Salesqty,Sum(Decode(Sauda.Buysell,'S',Decode(Sauda.Saudatype,'N' , (Sauda.Final_rat1-Sauda.Havala_rate)*Sauda.Quantity,Sauda.Quantity*Sauda.Final_rat1),0))
as Salesvalue,Sum(Decode(Sauda.Saudatype,'N' ,0,Decode(Sauda.Buysell,'B',Sauda.Quantity*1,Sauda.Quantity*-1))) as Netqty,
Sum(Decode(Sauda.Buysell,'B',Decode(Sauda.Saudatype,'N' , (Sauda.Final_rat1-Sauda.Havala_rate)*Sauda.Quantity,Sauda.Quantity*Sauda.Final_rat1),
Decode(Sauda.Saudatype,'N' ,(Sauda.Final_rat1-Sauda.Havala_rate)*Sauda.Quantity*-1, Sauda.Quantity*Sauda.Final_rat1*-1))) as Netvalue, 0.00 as Paverage,0.00
as Saverage,0.00 as Naverage,0.00 as Market,0.00 as Markvalue,0.00 as Proloss,Sauda.Dtoftran as Saudadate From Sauda Sauda Where Sauda.Egroup!='FU' and
Sauda.Sterminal!='99999' and Sauda.Firmnumber='ACML-00001' and Oowncode='570058312' Group by
Sauda.Oowncode,Sauda.Compcode,Sauda.Exchcode,Sauda.Egroup,Sauda.Vallan,Sauda.Dtoftran
Union All
Select Billcharges.Oowncode as Oowncode,Billcharges.Compcode as Compcode,' ' as Ndel, Rpad(' ',20) as Companyname, Billcharges.Exchcode as
Exchcode,Billcharges.Egroup as Egroup,Rpad(' ',20) as Booktype,Billcharges.Vallan as Vallan, Sum(Billcharges.Quantity) as
Purchqty,Sum(Billcharges.Quantity*Billcharges.Final_rat1) as Purchvalue, 0 as Salesqty,0 as Salesvalue,Sum(Billcharges.Quantity) as
Netqty,Sum(Billcharges.Quantity*Billcharges.Final_Rat1) as Netvalue, 0.00 as Paverage,0.00 as Saverage,0.00 as Naverage,0.00 as Market,0.00 as
Markvalue,Sum(Billcharges.Quantity*Billcharges.Final_rat1*-1) as Proloss,Billcharges.Dtoftran as Saudadate From Billcharges Billcharges Where
Billcharges.Parent!='Y' and Billcharges.Egroup!='FU' and Billcharges.Firmnumber='ACML-00001' and Oowncode='570058312' Group by
Billcharges.Oowncode,Billcharges.Compcode,Billcharges.Exchcode,Billcharges.Egroup,Billcharges.Vallan,Billcharges.Dtoftran;

Word cannot start the converter mswrd632

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Text Converters\Import\MSWord6.wpc
# On the Edit menu, click Delete.
# Click Yes.
# Exit Registry Editor.



reenable

Locate and then click the following registry subkey. Or, create it if it is not present.

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Applets\Wordpad

# On the Edit menu, point to New, and then click DWORD Value.
# Type AllowConversion for the name of the DWORD, and then press ENTER.
# Right-click AllowConversion, and then click Modify.
# In the Value data box, type 1, and then click OK.

Outlook Delivery report

1. On the Tools menu, click Options.
2. On the Preferences tab, click E-mail Options, and then click Tracking Options.
3. Under For all messages I send, request, click to select the Delivery receipt check box, and then click OK.

Auto Run disable

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer
NoDriveTypeAutoRun
0xFF

LD Digital socket error 10054

This error happens when a connection is started (and working), but then closed by the other side of things before the SMTP conversation is completed. This can also be caused by a firewall in between you and the server that might be 'proxying' the SMTP conversation, and then terminates it.


There are several common reasons:

* The wrong SMTP server was specified (Edit -> Options, Email). Check your email client to see what it's using for an SMTP server, or talk to your system administratory.
* There is a firewall or antivirus package running that's aborting the SMTP conversation. This might be something running on your machine (like Norton Antivirus), or some hardware firewall that does stateful inspection. If other software works, but PingPlotter/MultiPing does not, then it's probably local to your machine - something that is filtering by which application is sending data.
* There might be something wrong with the SMTP server. Try using another email client and make sure the same SMTP server works with that.


telnet ecnsmtp.logix.in 25

Check the ip address

VBScript Email with authenticated SMTP User

Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName

Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "Backoffice Oracle Job Status"
objMessage.From = "kshitij.rakesh@ldserver.com"
objMessage.To = "kshitij.rakesh@ldserver.com"
objMessage.TextBody = "Backoffice Oracle on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.ldserver.com"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25


'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1


'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "ldsupport@ldserver.com"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Ld@support"

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

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

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