1) export clientscannedimage table 2) drop table clientscannedimage; ---other master transmission take verc much time.. 3) impdb later 4) odbc connection 5) Create Firms (Financial Period) 6) Login to new year , run update the package , Reindex 7) Master Transmission 8) check sql 9) Transfer B/F transcation (No Delivery) SELECT * FROM ESETTLE; CHECK FROM PREVIOUS YEAR EXPORT FROM FY1011 30/03/11 BSE 2010254 SETTLE TO 2011001 31/03/11 BSE 2011001 SETTLE TO 2011002 01/04/11 BSE 2011002 SETTLE TO 2011003
30/03/11 NSE 2011060 SETTLE TO 2011061 31/03/11 NSE 2011061 SETTLE TO 2011062 01/04/11 NSE 2011062 SETTLE TO 2011063
01/04/11 NSEF 110401 SETTLE TO 110401
10) CREATE ABOVE SETTLEMENT INTO FY1112 IMPORT LIKE EXPORT SAME AS ABOVE
11) BEFORE DELETING FOLLOWINGS, TAKE BACKUP OF BILL SUMMARY AND TRANSACTION BOOK 12) DELETE BILL POSTING FOR ABOVE SEGMENTS AND SETTLEMENTS IN FY 1011 13) DELETE DAY DATA FOR ABOVE SEGMENTS AND SETTLEMENTS IN FY 1011
14) Transfer Demat Balance
SELECT * FROM ESETTLE; CHECK FROM PREVIOUS YEAR create zero 0 settlement in settlement master 01/04/2011 Login into FY 1112 do direct transmission
Note: if there is error in direct transmission then use export and import Note: there is some difference of INSIN in previous and new year, just update isin to isin master in both the years. Nobody can do anything wrong, change date in setup parameter to 31/03/2011
select * from demat;
15) Transfer Financial balances Create Profit loss account in in FY 1112 setup account option same like previous year FY1011 in FY1112, using direct transmission, Transfer Financial balances
select * from tblopeningbalance;
Note: this process will continue till some days.
Delete opening balances daily and run this option using internal code tick
if error in code then select * from ldfibs where oowncode=&code; in both years, internal code and oowncode should be in both firm and
same. it not same take backup and do it same and revert. internal code is same in all table for specific oowncode.
16) Margin Transfer will be same like point 9
17) Transfer Security files
18) Bill posting of point 9 settlement into new FY1112
19) Transfer Portfolio positions(global net position) to NEW Financial period (use it from old FY 1011)
SELECT * FROM ESETTLE; CHECK FROM PREVIOUS YEAR
Create Book type Opng. Stock
create 1 number settlement in in FY1112 in egroup Opening stock(OK)
please check dates in setup parameter – Global VI Note: It will take 1-2 days select * from sauda where egroup='OK';
20) Collateral Details select * from collateral;
21) Unreconciled Bank Entries
Problems: 1) constraints is voilated then disable it 2) trigger is voilated then disable it 3) datatype is not null, size, others 4) sp is missing 5) CPU usage is 100% then analyze schema.... 6) ORA-00600: internal error code, arguments: [15735], [2244], [2152], [], [], [], [], [] 7) roles exceed problem in ASP standard version, revoke roles from firmpermission and remoteuser 8) if direct transmission will not work or show some error then use export from oldFY and import into newFY ROLES TO GRANTED CONTAIN EXTRA PRIVILEGES
REASON:BRANCH HAVE STANDARD VERSION ------revoke_std_ver.sql
revoke insert,update on TBLUSERPROFILES from Remoteuser ; revoke insert,update on TBLUSERPROFILES from firmpermission ;
create role userprofiles ; grant insert,update on TBLUSERPROFILES to userprofiles ;
revoke execute on SP_ASIANEFFBAL from FIRMPERMISSION; revoke insert on TBLFIRMCREATION from FIRMPERMISSION ; revoke insert,update,delete on TBLMAILERDETAIL from FIRMPERMISSION ; revoke execute on SP_FIRMCREATION from FIRMPERMISSION;
revoke execute on PK_JOURNALIMPORT from Remoteuser ; revoke execute on PK_JOURNAL from remoteuser ; revoke execute on ldbo.SP_LIENFUNDTRANSFER from Remoteuser ;
revoke insert on TBLBILLTAGGING from cashbankadd ;
8) for ld digital, create directory same as previous year. SELECT * FROM DBA_DIRECTORIES; CREATE DIRECTORY LDDIGITAL AS 'd:\ldoutput\Lddigital'; grant read, write on directory LDDIGITAL to ldbo;
9) for clientlevel, grant role which is exist in clienlevel folder Create Role ClientPassEdit ; Grant update on Accountemaildetail to ClientPassEdit ; Grant clientpassedit to cllvl;
make entry in tnsnames.ora otherwise ECN is not be accessible from clientlevel
DIG1112SRV = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ARI1112) ) ) 10) backoffice code and branch change should be done only after financial balance carry process will be finished in somedays.
alter system set sga_max_size=8192M scope=spfile; alter system set parallel_execution_message_size=4096 scope=spfile;
AFTER ALL PROCESS, RESTART SERVER & ANALYZE ARE MUST OTHERWISE CPU USAGE WILL BE 100%
alter system set parallel_execution_message_size=4096 scope=spfile; shut immediate startup
On most platforms, the default value is 2148 bytes if parallel_automatic_tuning is set to FALSE, and 4096 bytes if parallel_automatic_tuning = TRUE. The default value is adequate for most applications. Larger values require a larger shared pool. Larger values result in better performance at the cost of higher memory use. For this reason, replication gets no benefit from increasing the size.
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.
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;
# 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.
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.
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.
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
'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"
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
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%'
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;
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
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})+$')
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---------------------------------- 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
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
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 ;
