LD Ledger Client Code not search

Insert into tblsaudatable select distinct Firmnumber,Oowncode,'C','N' from sauda minus
Select distinct cFirmnumber,cclientcode,'C','N' from tblsaudatable Where cSegment='C' ;

Insert into Tblsaudascriptable select distinct Firmnumber,Oowncode,Compcode,'C' from sauda minus
Select distinct cFirmnumber,Cclientcode,cscripcode,'C' from Tblsaudascriptable Where cSegment='C' ;

Insert into tblsaudatable select distinct Firmnumber,Oowncode,'D','N' from fosauda minus
Select distinct cFirmnumber,CClientcode,'D','N' from tblsaudatable Where cSegment='D' ;

Insert into Tblsaudascriptable select distinct Firmnumber,Oowncode,Compcode,'D' from fosauda minus
Select distinct cFirmnumber,Cclientcode,Cscripcode,'D' from Tblsaudascriptable Where cSegment='D' ;

Insert into Tblfintable select distinct firmnumber,oowncode,branchcode,'N' from ledger minus
select distinct cfirmnumber,Cclientcode,Cbranchcode,'N' from Tblfintable ;

YEP1314 Process

Please check required Space on storage for DB FY1314 = 1.5  * Database size of last year database FY1213


And follow following steps to create FY 1314 DB and database objects structure

1) Take Export Full Dump of apx1213 DB
Note: Please remove backup tables like (sauda050612 ldfibs070712) before take export

2) Create apx1314 Database same as last year database apx1213 (tablespace name, datafiles configuration should be same)

Email: Create Database Template for Database(APX1314) using existing Database(APX1213)

select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible from dba_data_files;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible from dba_temp_files;

Tablespace                 Datafile Size       Auotextend
SYSTEM SYSTEM01.ORA 500M 100M
SYSAUX SYSAUX01.ORA 500M 100M
UNDOTBS1 UNDOTBS01.ORA 1000M 500M
INDX INDEX01.ORA same as before same as before
USR USERS01.ORA same as before same as before
TEMPORARY TEMP01.ORA 1000M 500M


Keep Redolog size same as last year DB

SELECT LOG_MODE FROM V$DATABASE;
Archive log should be enabled


Tick on Use Bigfile tablespace Datafiles for USERS01.ORA, INDEX01.ORA. Or you can add datafiles whenever required

Parameters: db_block_size, sga_target, sga_max_size, utl_file_dir, processes, audit_trail, job_queue_processes, cursor_sharing, open_cursors and other should be same as last year.


3) Create Schema User LDBO using only attached scripts createuserLDBO10g_run_from_DB_server.SQL (Note: Don’t use previously having createuserldbo.sql)

Note: attached createuserLDBO10g_run_from_DB_server.SQL have all housekeeping job like analyze, purge queue, compile


Same time, Please scheduler database backup dump job using following or attached script and run, check & verify and confirm to Kshitij

/*
Note: Please change the details as per you client database name net services name (apx1213srv), user(ldbo) password (ldbo), oracle installation location(D:\oracle\product\10.2.0\db_1\BIN), dump location(E:\exp1314)
There should be entry of Net Manager(d:\oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora) in oracle database server
Also create folder where you want backup like following E:\exp1314
Change the backup job start as per server availability
*/

conn sys@apx1314srv as sysdba

begin
dbms_scheduler.create_job
(job_name => 'exp1314',
job_type => 'EXECUTABLE',
number_of_arguments => 5,
job_action => 'D:\oracle\product\10.2.0\db_1\BIN\exp.exe',
start_date => '01-APR-13 11:00.00.00 PM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
comments=>'export dump');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',1,'system/oracle@apx1314srv');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',2,'full=Y');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',3,'file=E:\exp1314\exp1314.DMP');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',4,'log=E:\exp1314\exp1314.log');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('exp1314',5,'STATISTICS= NONE');
end;
/

exec dbms_scheduler.run_job('exp1314');

4) Login into LDBO user at FY1314 db and drop and recreate Database Link

Match with last year database

Con ldbo/ldbo@apx1213srv
select owner,db_link,username,host from dba_db_links;

Con ldbo/ldbo@apx1314srv
select owner,db_link,username,host from dba_db_links;

Con ldbo/ldbo@apx1314srv

Spool c:\yep1314\drop_links.sql
SELECT 'DROP ' || decode (owner,'PUBLIC','PUBLIC DATABASE LINK ','DATABASE LINK ') || DB_LINK || ';' FROM DBA_DB_LINKS  WHERE USERNAME !=' ';
@ c:\yep1314\drop_links.sql


Con ldbo/ldbo@apx1213srv
Spool c:\yep1314\create_links.sql
SELECT 'CREATE ' || decode (owner,'PUBLIC','PUBLIC DATABASE LINK ','DATABASE LINK ') || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS  WHERE USERNAME !=' ';

/* put the password in above file and also change the connect string*/

Con ldbo/ldbo@apx1314srv
@ c:\yep1314\create_links.sql

Database link creation is below
Note: Please change the password and connect string as per client software

CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
  CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';

 CREATE PUBLIC DATABASE LINK "LNK_CCM"
  CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';

CREATE DATABASE LINK "LNK_DIGITAL"
  CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';

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



5) Create Database structure (previously called optable) using exp dump (rows=N) or expdp (content=metadata_only)

imp ldbo/ldbo@apx1314srv FILE=D:\apx1213.dmp ROWS=N FULL=Y CONSTRAINTS=Y GRANTS=Y INDEXES=Y STATISTICS=NONE

or

impdp ldbo/ldbo@apx1314SRV directory=DPUMP_DIR1 dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT



6) Login into LDBO user at FY1314 and drop and recreate Sequence
Sequences should be started from 1. Therefore we drop and recreate Sequences


Con ldbo/ldbo@apx1314srv

SET LINE 9999
SPOOL C:\YEP1314\SEQ1314.SQL
select 'DROP SEQUENCE ' || Sequence_Name || ';' from dba_Sequences where Sequence_Owner='LDBO';
SPOOL OFF
@ C:\YEP1314\SEQ1314.SQL

Create Sequence LDBO.Dematuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Saudauniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Billuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Jobbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Delbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Turnbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Fwdbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Extbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Dpuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Bankuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Payrequniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Riskuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Portuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Axiscms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Pmsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Debarreduniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Bkrecouniquenumber minvalue 50000000 maxvalue 9999999999 start with 50000000 increment by 1 nocache;
Create Sequence LDBO.Sq_Smsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Offlinenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Phytodemnumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Ipouniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Trademodification minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_SpicePoa minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Idbicms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Scripuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Clientuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Brokeruniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Nsdlcdsldpuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Orarupee minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Rtgssequence minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Bobbentopool minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Clientcode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Familycode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_NigPortclose Minvalue 1 maxvalue 9999999999 Start With 1 Increment by 1 noCache;
Create Sequence LDBO.Seqclientsequence minvalue 1 maxvalue 99 start with 1 increment by 1;
Create Sequence LDBO.Sq_collateral minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Nsdlkra minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.LdOraclebatchid minvalue 1 maxvalue 9999999999 nocache;
Create Sequence LDBO.Sq_Limittokenno minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.SQ_CDSLXMLUNIQUENUMBER minvalue 10000 maxvalue 99999;


/*Missing Sequence */
select object_name from user_objects@LNK_PREVIOUSYEARBALANCE where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%'
minus
select object_name from user_objects where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%';


7) Create splogin,tglogin manually from SYS user only (already present in createuserLDBO10g_run_from_DB_server.SQL )

SELECT owner,object_name FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');
Owner                  Object_name
SYS                         SPLOGININFORMATION
SYS                         TGLOGININFORMATION

8)  Compile All Invalid Objects

conn sys@apx1314srv as sysdba

EXEC sys.UTL_RECOMP.recomp_serial;

select object_name  from dba_objects where owner='LDBO' and status='INVALID';

SELECT owner,object_name,status FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');


9) Login into LDBO user at FY1314 db and Drop and recreate Database directory

 Match with last year database and change the path as per your requirement

conn sys@apx1213srv as sysdba
select 'create or replace directory '||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;


10) if Synonym is missing then create synonym from last year database apx1213 and run into current year apx1314

conn sys@apx1314srv as sysdba

Select synonym_Name From Dba_Synonyms@LNK_PREVIOUSYEARBALANCE Where table_Owner='LDBO'
Minus
Select synonym_Name From Dba_Synonyms Where table_Owner='LDBO';


conn sys@apx1213srv as sysdba

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

SPOOL C:\YEP1314\SYNONYMS1213.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name, 'PUBLIC') metadata
from dba_synonyms
where owner = 'PUBLIC'
  and table_owner = 'LDBO'
order by synonym_name;
SPOOL OFF

conn sys@apx1314srv as sysdba
@C:\YEP1314\SYNONYMS1213.SQL

11) Create Users from Last years


/* Missing Users */

conn sys@apx1213srv as sysdba

select username from dba_users@LNK_PREVIOUSYEARBALANCE
minus
select username from dba_users;

conn sys@apx1213srv as sysdba

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );


spool c:\yep1314\user_script.sql
SELECT DBMS_METADATA.GET_DDL('PROFILE',profile) FROM DBA_PROFILES WHERE profile != 'DEFAULT' GROUP BY PROFILE;
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) DDL FROM DBA_USERS where username in (select username from dba_ts_quotas);
SELECT DBMS_METADATA.GET_DDL('ROLE', role) FROM dba_roles;
spool off

conn sys@apx1314srv as sysdba

@c:\yep1314\user_script.sql


12) Create Object roles and user roles from last year using db link lnk_previousyearbalance

Conn ldbo/ldbo@apx1314srv


BEGIN
 FOR cur_rec IN (SELECT role
                  FROM   dba_roles@lnk_previousyearbalance) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


BEGIN
 FOR cur_rec IN (SELECT grantee,privilege
                  FROM   DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP
    BEGIN
      EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

BEGIN
 FOR cur_rec IN (SELECT grantee,privilege,table_name
                  FROM   dba_tab_privs@lnk_previousyearbalance  Where Grantor='LDBO') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

BEGIN
 FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name
                  FROM   dba_col_privs@lnk_previousyearbalance  Where Grantor='LDBO') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


BEGIN
 FOR cur_rec IN (SELECT grantee,granted_role
                  FROM   dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

13) If you have partitioned tables in last year then create DEMATMAIN table as partitioned (partitions should be same as TRANSACTIONS table) which is attached

Drop the dematmain table and recreate partitioned DEMATMAIN table using attached script

Conn ldbo/ldbo@apx1314srv

Select TABLE_NAME From Dba_Part_Tables@LNK_PREVIOUSYEARBALANCE where owner='LDBO'
Minus
Select TABLE_NAME From Dba_Part_Tables where owner='LDBO';


14)  Match Last and Current Year Database Objects (Run the following command in last and current year database and match the records)

Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*)  from user_objects where status='INVALID';
Select count(*) From Dba_Synonyms Where Table_Owner='LDBO';
select count(*) from dba_Constraints where owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
Select TABLE_NAME From Dba_Part_Tables where owner='LDBO';


Conn ldbo/ldbo@apx1314srv

/*Missing Object */
Select object_Name From Dba_objects@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO'
Minus
Select object_Name From Dba_objects Where Owner='LDBO';

/*invalid objects */
Select object_Name From Dba_objects@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO' and status='INVALID'
Minus
Select object_Name From Dba_objects Where Owner='LDBO' and status='INVALID';

/*Missing Tables */
Select table_Name From Dba_tables@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO'
Minus
Select table_Name From Dba_tables Where Owner='LDBO';

/*Missing Synonyms */
Select synonym_Name From Dba_Synonyms@LNK_PREVIOUSYEARBALANCE Where table_Owner='LDBO'
Minus
Select synonym_Name From Dba_Synonyms Where table_Owner='LDBO';

/*Missing Sequences */
select * from user_objects@LNK_PREVIOUSYEARBALANCE where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%'
minus
select * from user_objects where object_type='SEQUENCE' and object_NAME NOT LIKE 'SQFIN%';

/*Missing Constraints */
Select Constraint_Name From Dba_Constraints@LNK_PREVIOUSYEARBALANCE Where Owner='LDBO' and (Constraint_Name not like 'SYS%' and Constraint_Name not like 'BIN%')
Minus
Select Constraint_Name From Dba_Constraints Where Owner='LDBO' and (Constraint_Name not like 'SYS%' and Constraint_Name not like 'BIN%');

/* Missing Users */
select username from dba_users@LNK_PREVIOUSYEARBALANCE
minus
select username from dba_users;

/* Missing Roles */
select role from dba_roles@LNK_PREVIOUSYEARBALANCE
minus
select role from dba_roles;

select GRANTEE from dba_tab_privs where owner='LDBO'@LNK_PREVIOUSYEARBALANCE
minus
select GRANTEE from dba_tab_privs where owner='LDBO';



15) Import Lookup tables data using DB link LNK_PREVIOUSYEARBALANCE

Conn ldbo/ldbo@apx1314srv

SET LINE 9999
SET FEEDBACK OFF

Spool c:\yep1314\lookuptables.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";'
FROM   dba_constraints a
Where  A.Owner='LDBO' And A.Constraint_Type In ('R')
And    (A. Table_Name Like '%SMSPROVIDER%' OR A.Table_Name Like '%LOOKUP%');

select 'INSERT INTO ' || TABLE_NAME  || ' SELECT * FROM ' || TABLE_NAME || '@LNK_PREVIOUSYEARBALANCE;' from dba_tables where owner='LDBO'
and (Table_Name Like '%SMSPROVIDER%' OR Table_Name Like '%LOOKUP%');
Commit;
SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'
FROM   dba_constraints a
Where  A.Owner='LDBO' And A.Constraint_Type In ('R')
And    (A. Table_Name Like '%SMSPROVIDER%' OR A.Table_Name Like '%LOOKUP%');
spool off

@ c:\yep1314\lookuptables.sql
INSERT INTO TBLSOFTWAREACCESSLEVEL select * from TBLSOFTWAREACCESSLEVEL@LNK_PREVIOUSYEARBALANCE;
Commit;

/* Check and verify constraints, it should not be disabled */

select Constraint_Name from Dba_Constraints Where Owner='LDBO' AND status !='ENABLED';

Also check the row count in Lookup tables

16) Analyze whole database after all above steps

conn sys@apx1314srv as sysdba

exec DBMS_SCHEDULER.run_job ('ANALYZE_FULL');

Note: After Master transmission and other bulk transmission activities , it is required to run ANALYZE_FULL job for performance

Replace String in text file

c:\> BatchSubstitute.bat "&Yourname" kshitij up78896.sql > up78896_new.sql

==========

@echo off
REM -- Prepare the Command Processor --
SETLOCAL ENABLEEXTENSIONS
SETLOCAL DISABLEDELAYEDEXPANSION

::BatchSubstitude - parses a File line by line and replaces a substring"
::syntax: BatchSubstitude.bat OldStr NewStr File
:: OldStr [in] - string to be replaced
:: NewStr [in] - string to replace with
:: File [in] - file to be parsed
:$changed 20100115
:$source http://www.dostips.com
if "%~1"=="" findstr "^::" "%~f0"&GOTO:EOF
for /f "tokens=1,* delims=]" %%A in ('"type %3|find /n /v """') do (
set "line=%%B"
if defined line (
call set "line=echo.%%line:%~1=%~2%%"
for /f "delims=" %%X in ('"echo."%%line%%""') do %%~X
) ELSE echo.
)


======================

LD Billing Porcess Email Notification

CREATE OR REPLACE TRIGGER email_nsef_bill
AFTER insert on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(50) := 'NSEF Bill Process Done';
l_message VARCHAR2(500);

BEGIN

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

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

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

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

EXCEPTION
WHEN others THEN
RAISE;

END;
end if;
END email_nsef_bill;
/

Bulk Client Code Change prg

Set Exclusive OFF
Set Date BRITISH
Set Century on
Set Talk OFF
Set Safety OFF

Close ALL
Clear All
Clear

lcFirmnum='NBS-000001'
gnQryhandle=sqlconnect("NBS1011odbc","ldbo","ldbo")

lnSqlanswer=SQLPrepare(gnQryhandle,"select * from Ldfibs where firmnumber=?lcFirmnum ","ldfibs")
If lnSqlanswer!=-1
lnSqlanswer=SQLEXEC(gnQryhandle)
lnsqlanswer=sqlcommit(gnQryhandle)
EndIf
IF lnSqlanswer=-1
=AError(myErr)
=MessageBox(myErr(2)+" "+myErr(3),64,"Odbc Error")
Return
EndIf
Index on Oowncode tag oownin

Select 0
USE c:\temp\cldetail

Create Cursor erroroowncode(OldOowncode c(10),NewOowncode c(10),cErrortext char(150))
Create Cursor errortermcode(OldTermcode c(10),NewTermcode c(10),cErrortext char(150))

Create Cursor correctoowncode(OldOowncode c(10),NewOowncode c(10),cErrortext char(150))
Create Cursor correcttermcode(OldTermcode c(10),NewTermcode c(10),cErrortext char(150))

=messagebox(str(gnQryhandle))

lnCtr=0
Select Cldetail
Scan
lcOldcode=Cldetail.Oldcode
lcNewcode=Cldetail.Newcode
Select Ldfibs
Seek lcNewcode
If ! Found()
lnSqlanswer=SQLPREPARE(gnQryhandle,"{Call sp_Clientcodechange(?lcFirmnum,?lcOldcode,?lcNewcode,'N')}")
If lnSqlanswer!=-1
lnSqlanswer=SQLEXEC(gnQryhandle)
Insert into Correctoowncode values (lcOldcode,lcNewcode,'Successfully Changed')
EndIf
If lnSqlanswer=-1
=AError(myErr)
** =messagebox(myErr(2)+" "+myErr(3),64,"Odbc Error")
Select erroroowncode
Insert into erroroowncode values (lcOldcode,lcNewcode,x(2)+" "+x(3))
EndIf
lnCtr=lnCtr+1
@10,10 Say lnCtr
Else
Select erroroowncode
Insert into erroroowncode values (lcOldcode,lcNewcode,'Oowncode Already Exists')
EndIf
EndScan

=SQLCommit(gnQryhandle)

Select errorOowncode
Copy To c:\temp\erroroown.dbf

Select errorTermcode
Copy To c:\temp\errorterm.dbf

Close All
Return

LD AR > always release to AF > as per formula query

Client Master > Der+ Gen VI > Demat > Payout Status Quo
Client Master > Der+ Gen VI > Default product > Status Quo

AR > always release
AF > as per formula

Select T1.Oowncode From Accountdetail T1, Ldfibs T2
Where T1.Oowncode=T2.Oowncode And T1.Firmnumber=T2.Firmnumber
And T1.Firmnumber='ACML-00001'
And T1.Cpayoutstatusquo='AR' And T2.Brcode In(

)
;


Select distinct T1.Cclientcode From Tblclientproductactivation T1, Ldfibs T2
Where T1.Cclientcode=T2.Oowncode And T1.Cfirmnumber=T2.Firmnumber
And T1.Cfirmnumber='ACML-00001'
And T1.Cpayoutstatusquo='AR' And T2.Brcode In (

);


UPDATE ACCOUNTDETAIL SET Cpayoutstatusquo= 'AF' WHERE FIRMNUMBER='ACML-00001' and Cpayoutstatusquo= 'AR' AND oowncode= '20100A005';



UPDATE Tblclientproductactivation SET Cpayoutstatusquo='AF' Where Cfirmnumber='ACML-00001' And Nproductcode='0' And Cpayoutstatusquo='AR' and Cclientcode= '20100A005';

LD LOCKTABLE Email Notification

Select * From Tbllocktable Where Dcashbankfinancialentry Not Like ' ';
------------


CREATE OR REPLACE TRIGGER email_lock_table
AFTER UPDATE of Dcashbankreceiptentry,Dcashbankfinancialentry,Djournalentry on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ALERT: UNLOCK Data Files FY1112';
l_message VARCHAR2(500);

BEGIN

if (SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) not in ('LDBO')) then

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

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

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

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

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END email_lock_table;
/

Contra Entry Zero Token

Cheque is expired after 6 months
31 Mar 2011 20110331
update cashmain set bankreco=20110331 where firmnumber='ACML-00001' AND CBNUMBER= 'PYAUTOP1285007';

BRANCH HO roles

GRANT BRANCHCHANGE TO USER;
GRANT BRANCHMASTER TO USER;
GRANT BRANCHCHANGEREQUEST TO USER;
GRANT EXECUTE ON SP_BRANCHMASTER TO USER;

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

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

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;

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

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%'