LD YEP YEAR END DATABASE CREATION 2011-12

LD YEAR END DATABASE CREATION FY 1112


STEPS FOR CREATING NEW YEAR DATABASE ‘1112

Step 1 : Create the Database ARI1112 using template LDLARGE.
(Before that create folder d:\archive1112 )
Database templates should be copied in d:\oracle\product\10.2.0\db_1\assistants\dbca\templates

After database creation, Check the database folder all the datafiles are available or not.

* Only you have to change Database Name,SID,Sys Password & Database path
* Select Use Database File locations from Template.

Step 2 : Configure the Net Manager as ARI1112SRV and test it.

-------TNSNAMES.ORA-------
ARI1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ari1112)
)
)
------------LISTENER.ORA----------

(SID_DESC =
(GLOBAL_DBNAME = ari1112)
(SID_NAME = ari1112)
)


C:\>LSNRCTL STOP

C:\>LSNRCTL START

Step 3 : Create the User LDBO and assign the roles dba,connect,exp_full_database,imp_full_database,
Resource. Assign all System privileges except two viz.,( Freeze_any_workspace,
global Query rewrite).

------------
CREATE USER "LDBO" PROFILE "DEFAULT" IDENTIFIED BY "LDBO" DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;
BEGIN
DBMS_WM.GrantSystemPriv('ACCESS_ANY_WORKSPACE', 'LDBO', 'NO');
END;

GRANT ADMINISTER ANY SQL TUNING SET TO "LDBO" ;
GRANT ADMINISTER DATABASE TRIGGER TO "LDBO" ;
BEGIN
dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER', grantee_name=>'LDBO', admin_option=>FALSE);
END;
/
GRANT ADMINISTER SQL TUNING SET TO "LDBO" ;
GRANT ADVISOR TO "LDBO" ;
GRANT ALTER ANY CLUSTER TO "LDBO" ;
GRANT ALTER ANY DIMENSION TO "LDBO" ;
GRANT ALTER ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT ALTER ANY INDEX TO "LDBO" ;
GRANT ALTER ANY INDEXTYPE TO "LDBO" ;
GRANT ALTER ANY LIBRARY TO "LDBO" ;
GRANT ALTER ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT ALTER ANY OUTLINE TO "LDBO" ;
GRANT ALTER ANY PROCEDURE TO "LDBO" ;
GRANT ALTER ANY ROLE TO "LDBO" ;
GRANT ALTER ANY RULE TO "LDBO" ;
GRANT ALTER ANY RULE SET TO "LDBO" ;
GRANT ALTER ANY SEQUENCE TO "LDBO" ;
GRANT ALTER ANY SQL PROFILE TO "LDBO" ;
GRANT ALTER ANY TABLE TO "LDBO" ;
GRANT ALTER ANY TRIGGER TO "LDBO" ;
GRANT ALTER ANY TYPE TO "LDBO" ;
GRANT ALTER DATABASE TO "LDBO" ;
GRANT ALTER PROFILE TO "LDBO" ;
GRANT ALTER RESOURCE COST TO "LDBO" ;
GRANT ALTER ROLLBACK SEGMENT TO "LDBO" ;
GRANT ALTER SESSION TO "LDBO" ;
GRANT ALTER SYSTEM TO "LDBO" ;
GRANT ALTER TABLESPACE TO "LDBO" ;
GRANT ALTER USER TO "LDBO" ;
GRANT ANALYZE ANY TO "LDBO" ;
GRANT ANALYZE ANY DICTIONARY TO "LDBO" ;
GRANT AUDIT ANY TO "LDBO" ;
GRANT AUDIT SYSTEM TO "LDBO" ;
GRANT BACKUP ANY TABLE TO "LDBO" ;
GRANT BECOME USER TO "LDBO" ;
GRANT CHANGE NOTIFICATION TO "LDBO" ;
GRANT COMMENT ANY TABLE TO "LDBO" ;
GRANT CREATE ANY CLUSTER TO "LDBO" ;
GRANT CREATE ANY CONTEXT TO "LDBO" ;
GRANT CREATE ANY DIMENSION TO "LDBO" ;
GRANT CREATE ANY DIRECTORY TO "LDBO" ;
GRANT CREATE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE ANY INDEX TO "LDBO" ;
GRANT CREATE ANY INDEXTYPE TO "LDBO" ;
GRANT CREATE ANY JOB TO "LDBO" ;
GRANT CREATE ANY LIBRARY TO "LDBO" ;
GRANT CREATE ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE ANY OPERATOR TO "LDBO" ;
GRANT CREATE ANY OUTLINE TO "LDBO" ;
GRANT CREATE ANY PROCEDURE TO "LDBO" ;
GRANT CREATE ANY RULE TO "LDBO" ;
GRANT CREATE ANY RULE SET TO "LDBO" ;
GRANT CREATE ANY SEQUENCE TO "LDBO" ;
GRANT CREATE ANY SQL PROFILE TO "LDBO" ;
GRANT CREATE ANY SYNONYM TO "LDBO" ;
GRANT CREATE ANY TABLE TO "LDBO" ;
GRANT CREATE ANY TRIGGER TO "LDBO" ;
GRANT CREATE ANY TYPE TO "LDBO" ;
GRANT CREATE ANY VIEW TO "LDBO" ;
GRANT CREATE CLUSTER TO "LDBO" ;
GRANT CREATE DATABASE LINK TO "LDBO" ;
GRANT CREATE DIMENSION TO "LDBO" ;
GRANT CREATE EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE EXTERNAL JOB TO "LDBO" ;
GRANT CREATE INDEXTYPE TO "LDBO" ;
GRANT CREATE JOB TO "LDBO" ;
GRANT CREATE LIBRARY TO "LDBO" ;
GRANT CREATE MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE OPERATOR TO "LDBO" ;
GRANT CREATE PROCEDURE TO "LDBO" ;
GRANT CREATE PROFILE TO "LDBO" ;
GRANT CREATE PUBLIC DATABASE LINK TO "LDBO" ;
GRANT CREATE PUBLIC SYNONYM TO "LDBO" ;
GRANT CREATE ROLE TO "LDBO" ;
GRANT CREATE ROLLBACK SEGMENT TO "LDBO" ;
GRANT CREATE RULE TO "LDBO" ;
GRANT CREATE RULE SET TO "LDBO" ;
GRANT CREATE SEQUENCE TO "LDBO" ;
GRANT CREATE SESSION TO "LDBO" ;
GRANT CREATE SYNONYM TO "LDBO" ;
GRANT CREATE TABLE TO "LDBO" ;
GRANT CREATE TABLESPACE TO "LDBO" ;
GRANT CREATE TRIGGER TO "LDBO" ;
GRANT CREATE TYPE TO "LDBO" ;
GRANT CREATE USER TO "LDBO" ;
GRANT CREATE VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('CREATE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT DEBUG ANY PROCEDURE TO "LDBO" ;
GRANT DEBUG CONNECT SESSION TO "LDBO" ;
GRANT DELETE ANY TABLE TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'DEQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT DROP ANY CLUSTER TO "LDBO" ;
GRANT DROP ANY CONTEXT TO "LDBO" ;
GRANT DROP ANY DIMENSION TO "LDBO" ;
GRANT DROP ANY DIRECTORY TO "LDBO" ;
GRANT DROP ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT DROP ANY INDEX TO "LDBO" ;
GRANT DROP ANY INDEXTYPE TO "LDBO" ;
GRANT DROP ANY LIBRARY TO "LDBO" ;
GRANT DROP ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT DROP ANY OPERATOR TO "LDBO" ;
GRANT DROP ANY OUTLINE TO "LDBO" ;
GRANT DROP ANY PROCEDURE TO "LDBO" ;
GRANT DROP ANY ROLE TO "LDBO" ;
GRANT DROP ANY RULE TO "LDBO" ;
GRANT DROP ANY RULE SET TO "LDBO" ;
GRANT DROP ANY SEQUENCE TO "LDBO" ;
GRANT DROP ANY SQL PROFILE TO "LDBO" ;
GRANT DROP ANY SYNONYM TO "LDBO" ;
GRANT DROP ANY TABLE TO "LDBO" ;
GRANT DROP ANY TRIGGER TO "LDBO" ;
GRANT DROP ANY TYPE TO "LDBO" ;
GRANT DROP ANY VIEW TO "LDBO" ;
GRANT DROP PROFILE TO "LDBO" ;
GRANT DROP PUBLIC DATABASE LINK TO "LDBO" ;
GRANT DROP PUBLIC SYNONYM TO "LDBO" ;
GRANT DROP ROLLBACK SEGMENT TO "LDBO" ;
GRANT DROP TABLESPACE TO "LDBO" ;
GRANT DROP USER TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'ENQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT EXECUTE ANY CLASS TO "LDBO" ;
GRANT EXECUTE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT EXECUTE ANY INDEXTYPE TO "LDBO" ;
GRANT EXECUTE ANY LIBRARY TO "LDBO" ;
GRANT EXECUTE ANY OPERATOR TO "LDBO" ;
GRANT EXECUTE ANY PROCEDURE TO "LDBO" ;
GRANT EXECUTE ANY PROGRAM TO "LDBO" ;
GRANT EXECUTE ANY RULE TO "LDBO" ;
GRANT EXECUTE ANY RULE SET TO "LDBO" ;
GRANT EXECUTE ANY TYPE TO "LDBO" ;
GRANT EXPORT FULL DATABASE TO "LDBO" ;
GRANT FLASHBACK ANY TABLE TO "LDBO" ;
GRANT FORCE ANY TRANSACTION TO "LDBO" ;
GRANT FORCE TRANSACTION TO "LDBO" ;
GRANT GRANT ANY OBJECT PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY ROLE TO "LDBO" ;
GRANT IMPORT FULL DATABASE TO "LDBO" ;
GRANT INSERT ANY TABLE TO "LDBO" ;
GRANT LOCK ANY TABLE TO "LDBO" ;
GRANT MANAGE ANY FILE GROUP TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'MANAGE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT MANAGE FILE GROUP TO "LDBO" ;
GRANT MANAGE SCHEDULER TO "LDBO" ;
GRANT MANAGE TABLESPACE TO "LDBO" ;
GRANT MERGE ANY VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('MERGE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT ON COMMIT REFRESH TO "LDBO" ;
GRANT QUERY REWRITE TO "LDBO" ;
GRANT READ ANY FILE GROUP TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('REMOVE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT RESTRICTED SESSION TO "LDBO" ;
GRANT RESUMABLE TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('ROLLBACK_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT SELECT ANY DICTIONARY TO "LDBO" ;
GRANT SELECT ANY SEQUENCE TO "LDBO" ;
GRANT SELECT ANY TABLE TO "LDBO" ;
GRANT SELECT ANY TRANSACTION TO "LDBO" ;
GRANT SYSDBA TO "LDBO" ;
GRANT SYSOPER TO "LDBO" ;
GRANT UNDER ANY TABLE TO "LDBO" ;
GRANT UNDER ANY TYPE TO "LDBO" ;
GRANT UNDER ANY VIEW TO "LDBO" ;
GRANT UNLIMITED TABLESPACE TO "LDBO" ;
GRANT UPDATE ANY TABLE TO "LDBO" ;
GRANT "AQ_ADMINISTRATOR_ROLE" TO "LDBO" ;
GRANT "CONNECT" TO "LDBO" ;
GRANT "DBA" TO "LDBO" ;
GRANT "EXP_FULL_DATABASE" TO "LDBO" ;
GRANT "IMP_FULL_DATABASE" TO "LDBO" ;

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

Step 4 : Run the Optable in LDBO Schema & Run the four sqls in SYS Schema.

Step 5 : Run the Roles.sql(EXIST IN OPTABLE) in LDBO.
REVOKE LDBO ROLES EXCEPT IMPORTANT ROLES.


Step 6 : Create database link (1112 database) and test it.


CREATE DATABASE LINK "LNK_PREVIOUSYEARBALANCE"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'ari1112srv';

Select count(*) from ldfibs@lnk_previousyearbalance;

Step 7 : Analyze the database using old Analyze method.This is for one time (Analyze.sql)

---------ONE TIME-------------
spool c:\temp\analyze.sql


select 'ANALYZE TABLE '||Owner||'.'||table_name||' compute statistics;'
from sys.all_tables where table_name!='_default_auditing_options_'
/

select 'ANALYZE INDEX '||Owner||'.'||index_name||' compute statistics;'
from sys.all_indexes
/

spool off

set feed on
@c:\temp\analyze.sql



-----------REBUILD INDEX---------

SPOOL C:\temp.sql

SELECT 'ALTER INDEX ' || a.index_name || ' REBUILD;'
FROM all_indexes a
WHERE table_owner = 'LDBO'
ORDER BY 1
/

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

---------



Step 8 : Schedule analyze (newAnalyze.sql) and backup for 1112 database.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('LDBO',cascade=>TRUE);

Step 9 : Run users.sql in old year database(1011) and then run Recreate_users.sql in 1112 database.


set heading off verify off feedback off echo off term off linesize 200 wrap on

spool c:\temp\Recreate_Users.sql

SELECT distinct 'create profile '|| profile ||' Limit Sessions_per_user Unlimited;' from dba_profiles where profile!='DEFAULT' ;
Select 'Alter profile '|| profile ||' Limit '|| Resource_name ||' '|| Limit||';' from dba_profiles where profile!='DEFAULT' and Limit!='DEFAULT' ;

SELECT 'create user ' || username ||
' identified ' ||
DECODE(password, NULL, 'EXTERNALLY', ' by values ' || '''' || password || '''') ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace ||
' profile ' || profile || ';'
FROM dba_users
where username!='SYSTEM' and Username!='SYS' and Username!='DBSNMP' and Username!='REPADMIN' ORDER BY username ;

SELECT 'Grant '|| Granted_role ||' to '|| Grantee||';' from dba_role_privs Where Grantee!='SYSTEM' and
Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN' ;

spool off



Step 10 : Reduce the Previous year(1011) Memory size.(This should be done at 31/03/2011
before transmitting year end transfer procedure.)

---FY10-11----
ALTER SYSTEM SET SGA_TARGET=1521M;
ALTER SYSTEM SET pga_aggregate_target=1200M;
ALTER SYSTEM SET SGA_MAX_SIZE=1521MB SCOPE=SFILE;



---FY11-12----

ALTER SYSTEM SET SGA_TARGET=8152M;

ALTER SYSTEM SET SGA_MAX_SIZE=8152M SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target=2447M; ----workarea_size_policy ='AUTO'

ALTER SYSTEM SET parallel_execution_message_size=4096 SCOPE=SPFILE;


Step 11: compileinvalidobjects.SQL

pool c:\temp\invalid.sql ;
select OBJECT_NAME from dba_objects where owner='LDBO' AND STATUS='INVALID'

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER')
;
spool out ;
@ c:\temp\invalid.sql

STEP 12: FIRM VFP BACKUP

@ECHO OFF
copy d:\AKJ1112\*.* F:\VFP_BACKUP1112\LAKJ1112\*.* /v /y
copy d:\ACML1112\*.* F:\VFP_BACKUP1112\LACML1112\*.* /v /y
copy d:\AFLC1112\*.* F:\VFP_BACKUP1112\LAFLC1112\*.* /v /y
copy d:\AFSL1112\*.* F:\VFP_BACKUP1112\LAFSL1112\*.* /v /y
copy d:\Aric1112\*.* F:\VFP_BACKUP1112\LARIC1112\*.* /v /y
copy d:\AIBS1112\*.* F:\VFP_BACKUP1112\LAIBS1112\*.* /v /y
xcopy d:\ld\*.* F:\VFP_BACKUP1112\Lld\*.* /s /i /v /y
EXIT


STEP 13: EXPORT SCANIMAGE FROM PREVIOUS YEAR AND IMPORT INTO CURRENT FY

---------FY10-11-----------
expdp ldbo/ldbo@ari1011srv directory=dpump_dir1 tables=CLIENTSCANNEDIMAGE dumpfile=SCANTABLE1011.dmp LOGFILE=SCANTABLE1011.LOG

drop table CLIENTSCANNEDIMAGE;

-----create table---------otherwise clientmaster will not open----

CREATE TABLE "LDBO"."CLIENTSCANNEDIMAGE"
(
"FIRMNUMBER" CHAR(10 BYTE) NOT NULL ENABLE,
"CODE" CHAR(10 BYTE) NOT NULL ENABLE,
"PSCANNEDIMAGE" LONG RAW,
"NFINANCIALYEAR" NUMBER(4,0) NOT NULL ENABLE
)
PCTFREE 15 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USR" ;
CREATE UNIQUE INDEX "LDBO"."SCANNEDIMAGEINDEX" ON "LDBO"."CLIENTSCANNEDIMAGE"
(
"FIRMNUMBER", "NFINANCIALYEAR", "CODE"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "INDX" ;


grant INSERT on CLIENTSCANNEDIMAGE to CLIENTADD ;
grant SELECT on CLIENTSCANNEDIMAGE to CLIENTADD ;
grant DELETE on CLIENTSCANNEDIMAGE to CLIENTEDT ;
grant INSERT on CLIENTSCANNEDIMAGE to CLIENTEDT ;
grant SELECT on CLIENTSCANNEDIMAGE to CLIENTEDT ;
grant UPDATE on CLIENTSCANNEDIMAGE to CLIENTEDT ;


--------FY 11-12--------
drop table CLIENTSCANNEDIMAGE;

impdp ldbo/ldbo@ari1112srv DIRECTORY=dpump_dir1 DUMPFILE=SCANTABLE1011.dmp TABLES=CLIENTSCANNEDIMAGE

update clientscannedimage set nfinancialyear=2011;


STEP 14:
set oracle_sid=ari1112
RMAN TARGET sys/oracle@ari1112srv

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO F:\rman1112\%F';

SHOW ALL;

BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'F:\rman1112\%U';

set oracle_sid=ari1112
RMAN TARGET sys/oracle@ari1112srv cmdfile='c:\ksh\rman1112.sql'


STEP 15:
SQL> CREATE DIRECTORY dpump_dir1 AS 'F:\EXPDP1112';
SQL> grant read, write on directory dpump_dir1 to ldbo;

@echo off
expdp ldbo/ldbo@ari1112srv directory=dpump_dir1 schemas=ldbo dumpfile=ari1112.DMP schemas=ldbo LOGFILE=ari1112.LOG
ren F:\EXPDP1112\ARI1112.DMP ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%.DMP
ren F:\EXPDP1112\ARI1112log ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%log
exit


----after some months----
@echo off
expdp ldbo/ldbo@ari1112srv directory=dpump_dir1 dumpfile=ari1112.DMP schemas=ldbo LOGFILE=ari1112.LOG EXCLUDE=TABLE:\"LIKE \'%SCANNEDIMAGE%\'\"

ren F:\EXPDP1112\ARI1112.DMP ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%.DMP
ren F:\EXPDP1112\ARI1112log ARI1112_%date:~0,2%%date:~3,2%%date:~6,4%log
exit




STEP 16:


--------------TNSNAMES.ORA FOR ASP-----------------


ARI1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ARI1112)
)
)

DIG1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ARI1112)
)
)


STEP 17:

ASP STANDARD VERSION ROLES REVOKE
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 ;

REVOKE UPDATE ON ESETTLE FROM CASHBANKADD;

STEP 18:
CLIENTLEVEL ROLES

Create Role ClientPassEdit ;
Grant update on Accountemaildetail to ClientPassEdit ;
Grant clientpassedit to cllvl ;




STEP 19:

LD DIGITAL

CREATE DIRECTORY LDDGITAL AS 'd:\ldoutput\Lddigital';
grant read, write on directory LDDGITAL to ldbo;

STEP 20:

Disable the LD RK Rakshak job for Last year FY 1011


Select Owner,Job_Name,Job_Type,Job_Action,Enabled,State From Dba_Scheduler_Jobs Where Owner='LDBO' Order By State;

Exec DBMS_SCHEDULER.DISABLE('JLDRKBFTRADEUPDATION');



STEP 21:

PROCEDURE splogininformation
AS
V_PROG SYS.V_$SESSION.PROGRAM%TYPE;
V_MODULE SYS.V_$SESSION.PROGRAM%TYPE ;
LNUSERCODE NUMBER ;
LCUSERDETAILS VARCHAR2(100) ;
BEGIN
SELECT PROGRAM,MODULE INTO V_PROG,V_MODULE FROM SYS.V_$SESSION
WHERE AUDSID = USERENV('SESSIONID')
AND AUDSID != 0
AND ROWNUM = 1;

IF UPPER(V_PROG) LIKE '%TOAD%' OR UPPER(V_PROG) LIKE '%T.O.A.D%' OR
UPPER(V_PROG) LIKE '%SQLNAV%' OR
UPPER(V_PROG) LIKE '%PLSQLDEV%' OR
UPPER(V_PROG) LIKE '%BUSOBJ%' OR
UPPER(V_PROG) LIKE '%EXCEL%'
THEN
RAISE_APPLICATION_ERROR(-20000, 'On LD Database Development tools are Restricted and is violation of agreement entered into between 2 companies');
END IF;
LNUSERCODE:=-1;
IF ((UPPER(V_PROG)='LD.EXE' AND UPPER(V_MODULE)='LD.EXE') OR
(UPPER(V_PROG)='DLLHOST.EXE' AND UPPER(V_MODULE)='DLLHOST.EXE')) THEN
LCUSERDETAILS:=ORA_CLIENT_IP_ADDRESS ;
INSERT INTO TBLTEMPOPERATIONSTATISTICS (NOPERCODE,COPERATIONNAME) VALUES (LNUSERCODE,LCUSERDETAILS) ;
ELSE
LCUSERDETAILS:='USER LOGGED ON' ;
INSERT INTO TBLTEMPOPERATIONSTATISTICS (NOPERCODE,COPERATIONNAME) VALUES (LNUSERCODE,LCUSERDETAILS) ;
END IF ;
COMMIT ;
END;



Create or Replace trigger tglogininformation
after logon on Database
Begin
splogininformation() ;
End;
/

Premium Brokerage Update

CLient Master > Der+Gen VI > option brokerage on >

UPDATE Cldetail SET Cderbroktype='P' ,CDERSBROKTYPE='P' WHERE FIRMNUMBER='ACML-00001' AND oowncode= '952000001';

Constraint Check

SPOOL C:\CONS.TXT

select 'select '||cc.column_name-
||' from '||c.owner||'.'||c.table_name-
||' a where not exists (select ''x'' from '-
||r.owner||'.'||r.table_name-
||' where '||rc.column_name||' = a.'||cc.column_name||')'
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.constraint_type = 'R'
and c.owner not in ('SYS','SYSTEM')
and c.r_owner = r.owner
and c.owner = cc.owner
and r.owner = rc.owner
and c.constraint_name = cc.constraint_name
and r.constraint_name = rc.constraint_name
and c.r_constraint_name = r.constraint_name
and cc.position = rc.position
and c.owner = 'LDBO'
and c.table_name = 'CEXIST'
and c.constraint_name = 'CEXISTBOOK';


SPOOL OFF

Check Unused Space

SQL> set serveroutput on

SQL> set pages 1000

SQL> set lines 160

SQL> DECLARE

2 alc_bks NUMBER;

3 alc_bts NUMBER;

4 unsd_bks NUMBER;

5 unsd_bts NUMBER;

6 luefi NUMBER;

7 luebi NUMBER;

8 lub NUMBER;

9 BEGIN

10 DBMS_SPACE.UNUSED_SPACE (

11 segment_owner => 'RNCRY'

12 , segment_name => 'COMS'

13 , segment_type => 'TABLE'

14 , total_blocks => alc_bks

15 , total_bytes => alc_bts

16 , unused_blocks => unsd_bks

17 , unused_bytes => unsd_bts

18 , last_used_extent_file_id => luefi

19 , last_used_extent_block_id => luebi

20 , last_used_block => lub

21 );

22

23 DBMS_OUTPUT.PUT_LINE('Allocated space = '|| alc_bts );

24 DBMS_OUTPUT.PUT_LINE('Actual used space = '|| unsd_bts );

25 EXCEPTION

26 WHEN OTHERS THEN

27 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));

28 END;

29 /

Allocated space = 8534360064

Actual used space = 46874624



PL/SQL procedure successfully completed.

Constraint stats ORA-02298

Alter table CEXIST DISABLE constraint CEXISTBROKBOOK;

alter table CEXIST disable constraint CEXISTBOOK;
alter table CEXIST ENABLE constraint CK_COMPANYCATEGORY
alter table CEXIST ENABLE constraint CEXISTPRIMARY ;
alter table CEXIST ENABLE constraint COMPANYEXIST ;




SQL> alter table CEXIST ENABLE constraint CEXISTBOOK ;
alter table CEXIST ENABLE constraint CEXISTBOOK
*
ERROR at line 1:
ORA-02298: cannot validate (LDBO.CEXISTBOOK) - parent keys not found


alter table CEXIST MODIFY CONSTRAINTS CEXISTBOOK ENABLE VALIDATE;

ERROR at line 1:
ORA-02298: cannot validate (LDBO.CEXISTBOOK) - parent keys not found



alter table CEXIST MODIFY CONSTRAINTS CEXISTBOOK ENABLE NOVALIDATE;



Constraint stats
Here are the four type of constraint stats. These four constraint stats are applicable for all type of constraints(primary key, foreign key, check etc).

1. ENABLE VALIDATE
2. ENABLE NOVALIDATE
3. DISABLE VALIDATE
4. DISABLE NOVALIDATE


ENABLE VALIDATE is same as ENABLE. Constraint validate the data as soon as we entered in the table.


ENABLE NOVALIDATE is not same as ENABLE. Constraint validates the new data or modified data. It would not validate the existing data in table.


DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.


DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.



Note : Couple of things needs to be noted down here.

1. Converting NOVALIDATE constraint to VALIDATE would take longer time, depends on how big the data in the table. Although conversion in the other direction is not an issue

2. Disabling primary key constraint will drop the index associated with primary key. Again, when we enable the primary key constraint, it will create the index on the primary key column.

What is the ideal place to use ENABLE NOVALIDATE option?

In a busy environment, some one disabled the constraint accidently or intentionally, and we have already bad data in that table. Now business requested you to load the new set of data, but business wanted to make sure that new set of data should be validated during the load. At this circumstances, we can use ENABLE NOVALIDATE option. This option will validate the new data and old data will not be validated.

What is the ideal place to use DISABLE VALIDATE option?

We disabled the constraint for some reason. We do not want to load any data until we fix the issue and enable the constraint. We can use DISABLE VALIDATE option here. This option would not let you load any data when the constraint is disabled.

LD duplicate transaction number at time of billing

LD duplicate transaction number at time of billing


[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Bill Financial Posting cannot be Run. ~-1~ORA-00001: unique constraint (LDBO.PK_BILLPROCPRIM) violated~ ORA-06512: at "LDBO.SP_CASHJOBFINPOST", li

Ask Bill Prefix at time of Bill posting

Auto N/NN/060/

Manual N/NN/060-

website storage error

Not enough storage is available to process this command

If you recieve multiple storage alerts within the event log, the below procedure should resolve.

1. Click on Start > Run > regedit and click OK
2. Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\
LanmanServer\Parameters
3. Locate IRPStackSize. If this value does not exist, right click on Parameters key and Click on New > Dword Value and type in IRPStackSize under the name.
5. The name of the value must be exactly the same as the one in step 3. (Case sensitive)
6. Right click on IRPStackSize and click on modify
7. Select decimal and enter a value higher than 15 (Maximum Value is 50 decimal)
8. Click Ok
9. Exit from registry editor and restart your computer

LD bank holiday old billing

01/04/2011 holiday


existing 30/03/2011 financial posting is 05/04/2011
31/03/2011 financial posting is 06/04/2011

30/03/2011 old financial posting will be 01/04/2011
31/03/2011 old financial posting will be 04/04/2011

LD ASP YEAR END PROCESS FY 1112

LD ASP YEAR END PROCESS FY 1112

Before processing for New Year financial process for ASP. We have to log into the local LD and follow the following link-
o General Utilities-> General Utilities->Wan Enable
o Select Firm, select the New Financial Year, Tick on Wan Enable & click on save button.
o User has to do for all firm for which New Financial Year has to be change.
 These are following points which have to be done for the New Year financial Process for ASP
o Click Start->Run->CMD
1) Stop the IIS Server by typing the following command – iisreset/stop
o Click on the Control Panel -> Administrative Tools-> Component Services-> Select all components-> Right click and select Shut Down.
o Copy the following files from m:\LD\sysuser folder system.*, setup.*, esetup.*, excode.*, directory.* ,firm.* to asp server i.e. (d:\LD\sysuser)
o After copying the file Go to Fox Pro write the following command
 Set excl off
 use d:/ld/sysuser/directry.DBF
 Brow Change the Data_Drive (i.e. Drive letter for ld server) Change the Lwanenable =”T” (for the new firm if it is not been viewed at Branch level.)
 Clos all
o Go to cmd -> type the following command – iisreset/start
 If there is problem in Pre-Printed file then do the following Points
o Go Visual Foxpro and type the following text
 Set excl off
 Rest from m:\(firmfolder)\ldvar.mem
 Display memo
 _LDDDRIVE = ‘D:’ (Where the ld is stored on LD Server)
 _LDDRIVE = ‘D:’ (Where the ld is stored on LD Server
 Save all like *.* to m:\(firmfolder)\ldvar.mem
 Clea
 Close all

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

ARI1112SRV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.84)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ari1112)
)
)

---------------------------------------------
Issue
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 ;

REVOKE UPDATE ON ESETTLE FROM CASHBANKADD;




ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD

LD CLIENTLEVEL YEP1112

Create Role ClientPassEdit ;
Grant update on Accountemaildetail to ClientPassEdit ;
Grant clientpassedit to cllvl;


OTHERWISE

Technical Information (for support personnel)

• Error Type:
  Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
  [Microsoft][ODBC driver for Oracle][Oracle]ORA-01031: insufficient privileges
  /clientlevel/clientaccess/default.asp, line 257
• Browser Type:
  Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
• Page:
  POST 334 bytes to /clientlevel/clientaccess/default.asp

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)
)
)

LD Digital YEP 1112

connect sys@ksh1112srv as sysdba

CREATE DIRECTORY LDDGITAL AS 'd:\ldoutput\Lddigital';

grant read, write on directory LDDIGITAL to ldbo;

grant read, write on directory LDDIGITAL to ;

--------Make Tnsnames.ora(Net Manager) entry at ASP server to view digital contract at clientlevel

DIG1112SRV =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ksh1112)

)

)

LD network.dbf error at time of FnO sauda file import

LD network.dbf error at time of FnO sauda file import

SQL> drop sequence Sq_Scripuniquenumber ;

Sequence dropped.

SQL> SELECT MAX(CODE) FROM COMPANY ;

MAX(CODE)
----------
Co-0054502

SQL> Create Sequence Sq_Scripuniquenumber minvalue 54503 maxvalue 9999999 ;

Sequence created.

LD Application Year End Process YEP 1112

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)

select * From Esettle where Groupcode='OK';

ACML-00001 2011 BSE OPNG. STK OK 1 2 30-MAR-11 30-MAR-11 30-MAR-11 30-MAR-11 30-MAR-11 30-MAR-11
ACML-00001 2011 BSE OPNG. STK OK 2 3 31-MAR-11 31-MAR-11 04-APR-11 04-APR-11 04-APR-11 04-APR-11


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 ;

REVOKE UPDATE ON ESETTLE FROM CASHBANKADD;

ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD

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%

ORA-00600: internal error code

ORA-00600: internal error code, arguments: [15735], [2244], [2152], [], [], [], [], []

solution:

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.

When parallel_automatic_tuning = TRUE, message buffers are allocated out of the large pool. In this case, the default is generally higher.

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.