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