SELECT DISTINCT hgghgg_AGENCY_NAME=(select top 1 name from hgg_Agency) , hgghgg_AGENCY_ID=(select top 1 AgencyID from hgg_Agency) , BROKERAGE_NAME=CONVERT(varchar(100), null) , BROKERAGE_ID=CONVERT(int, null) , GLDiv_Code=div.GLDivCode , GLDiv_Name=div.Name , GLDiv_ShortName=div.ShortName , REGION_NAME=CONVERT(varchar(100), null) , REGION_ID=CONVERT(int, null) , GLBrnch_Code=brnch.GLBrnchCode , GLBrnch_Name=brnch.Name , GLBrnch_ShortName=brnch.ShortName , OFFICE_NAME=CONVERT(varchar(100), null) , OFFICE_ID=CONVERT(int, null) , GLDept_Code=dpt.GLDeptCode , GLDept_Name=dpt.Name , GLDept_ShortName=dpt.ShortName , DEPARTMENT_NAME=CONVERT(varchar(100), null) , DEPARTMENT_ID=CONVERT(int, null) , GLGroup_Code=grp.GLGrpCode , GLGroup_Name=grp.Name , GLGroup_ShortName=grp.ShortName , CF_OPTION_VALUE_ID=CONVERT(varchar(100), null) , CF_ID=CONVERT(int, null) FROM hgg_GeneralLedgerDivision div JOIN hgg_GeneralLedgerDivBranch divbrnch on divbrnch.GLDivCode = div.GLDivCode and div.GLDivCode in ( SELECT DISTINCT GLDivCode from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') UNION SELECT DISTINCT c.GLDivCode from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') ) JOIN hgg_GeneralLedgerBranch brnch on brnch.GLBrnchCode = divbrnch.GLBrnchCode JOIN hgg_GeneralLedgerBranchDept brnchdpt on brnchdpt.GLBrnchCode = brnch.GLBrnchCode and brnch.GLBrnchCode in ( SELECT DISTINCT GLBrnchCode from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') UNION SELECT DISTINCT c.GLBrnchCode from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') ) JOIN hgg_GeneralLedgerDepartment dpt on dpt.GLDeptCode = brnchdpt.GLDeptCode JOIN hgg_GeneralLedgerDeptGroup dptgrp on dptgrp.GLDeptCode = dpt.GLDeptCode and dpt.GLDeptCode in ( SELECT DISTINCT GLDeptCode from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') UNION SELECT DISTINCT c.GLDeptCode from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') ) JOIN hgg_GeneralLedgerGroup grp on grp.GLGrpCode = dptgrp.GLGrpCode and grp.GLGrpCode in ( SELECT DISTINCT GLGrpCode from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') UNION SELECT DISTINCT c.GLGrpCode from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') ) SELECT ISNULL(e.EmpCode, u.EmpCode) as EmpCode , IsRep , IsProd , IsTeleMarketer , LastName , FirstName , MiddleName , ShortName , BusAreaCode , BusPhone , BusExt , FaxAreaCode , FaxPhone , FaxExt , Title , Status , Email , u.CustExecCount , u.CustRepCount , u.PolExecCount , u.PolRepCount , u.SuspByCount , u.SuspToCount , u.ActivityByCount , u.AttachmentByCount , [LegacyEmpCode]=CONVERT(varchar(3), null) , [USER_NAME]=CONVERT(varchar(100), null) , [USER_ID]=CONVERT(int, null) FROM hgg_EMPLOYEE e RIGHT OUTER JOIN ( SELECT c.EmpCode, CustExecCount=SUM(CustExecCount), CustRepCount=SUM(CustRepCount), PolExecCount=SUM(PolExecCount), PolRepCount=SUM(PolRepCount), SuspByCount=SUM(SuspByCount), SuspToCount=SUM(SuspToCount), ActivityByCount=SUM(ActivityByCount), AttachmentByCount=SUM(AttachmentByCount) FROM ( SELECT DISTINCT c.Prod1Code as EmpCode, CustExecCount=COUNT(*), CustRepCount=0, PolExecCount=0, PolRepCount=0, SuspByCount=0, SuspToCount=0, ActivityByCount=0, AttachmentByCount=0 from hgg_customer c where c.CustID in ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) group by c.Prod1Code UNION SELECT DISTINCT c.CsrCode as EmpCode, CustExecCount=0, CustRepCount=COUNT(*), PolExecCount=0, PolRepCount=0, SuspByCount=0, SuspToCount=0, ActivityByCount=0, AttachmentByCount=0 from hgg_customer c where c.CustID in ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) group by c.CsrCode UNION SELECT DISTINCT ExecCode as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=COUNT(*), PolRepCount=0, SuspByCount=0, SuspToCount=0, ActivityByCount=0, AttachmentByCount=0 from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') and ExecCode IS NOT NULL group by ExecCode UNION SELECT DISTINCT CsrCode as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=0, PolRepCount=COUNT(*), SuspByCount=0, SuspToCount=0, ActivityByCount=0, AttachmentByCount=0 from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') and CsrCode IS NOT NULL group by CsrCode UNION SELECT DISTINCT s.SuspToEmpCode as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=0, PolRepCount=0, SuspByCount=0, SuspToCount=COUNT(*), ActivityByCount=0, AttachmentByCount=0 from hgg_suspense s join hgg_customer c on CONVERT(varchar(36), c.CustId) = s.EntityId where c.CustID in ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) and s.SuspToEmpCode IS NOT NULL group by s.SuspToEmpCode UNION SELECT DISTINCT s.SuspByEmpCode as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=0, PolRepCount=0, SuspByCount=COUNT(*), SuspToCount=0, ActivityByCount=0, AttachmentByCount=0 from hgg_suspense s join hgg_customer c on CONVERT(varchar(36), c.CustId) = s.EntityId where c.CustID in ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) and s.SuspByEmpCode IS NOT NULL group by s.SuspByEmpCode UNION SELECT DISTINCT t.EmpCode as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=0, PolRepCount=0, SuspByCount=0, SuspToCount=0, ActivityByCount=COUNT(*), AttachmentByCount=0 from hgg_transaction t join hgg_customer c on CONVERT(varchar(36), c.CustId) = t.EntityId where c.CustID in ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) and t.EmpCode IS NOT NULL group by t.EmpCode UNION SELECT DISTINCT da.ChangedBy as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=0, PolRepCount=0, SuspByCount=0, SuspToCount=0, ActivityByCount=0, AttachmentByCount=COUNT(*) from hgg_docattachment da join hgg_docrelation dr on dr.DocAId = da.DocAid join hgg_Notes n on n.NoteId = dr.AttachId where da.[Status] in ('A', 'C') and da.StorageOpt in ('DB') and dr.attachtype = 45 -- NOTE and n.entitytype = 4 -- CUSTOMER and n.reftype in (26,39,53) -- Customer/Policy, Customer/Claim, Customer and n.EntityID in ( SELECT DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) group by da.ChangedBy UNION SELECT DISTINCT da.ChangedBy as EmpCode, CustExecCount=0, CustRepCount=0, PolExecCount=0, PolRepCount=0, SuspByCount=0, SuspToCount=0, ActivityByCount=0, AttachmentByCount=COUNT(*) from hgg_docattachment da join hgg_docrelation dr on dr.DocAId = da.DocAid join hgg_Transaction t on t.TranId = dr.AttachId where da.[Status] in ('A', 'C') and da.StorageOpt in ('DB') and dr.attachtype = 185 -- TRANSACTION and t.entitytype = 4 -- CUSTOMER and t.EntityID in ( SELECT DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) group by da.ChangedBy ) c group by EmpCode ) u ON upper(u.EmpCode) = upper(e.EmpCode) SELECT c1.CoCode , c1.Name , c1.ShortName , c1.[Type] , c1.[Status] , c1.IsHide , c1.ParentCoCode , ParentName=c2.Name , ParentShortName=c2.ShortName , ParentType=c2.[Type] , ParentStatus=c2.[Status] , ParentIsHide=c2.IsHide , [CARRIER_NAME]=CONVERT(varchar(100), null) , [CARRIER_ID]=CONVERT(int, null) , [IsPrimary]=CONVERT(bit, 1) , Duplicate_Type=CONVERT(varchar(100), null) , REPLACEMENT_COCODE=CONVERT(varchar(100), null) FROM hgg_COMPANY c1 LEFT JOIN ( SELECT CoCode , Name , ShortName , [Type] , [Status] , IsHide , ParentCoCode , [IsPrimary]=CONVERT(bit, 1) , [CARRIER_NAME]=CONVERT(varchar(100), null) , [CARRIER_ID]=CONVERT(int, null) FROM hgg_COMPANY WHERE upper(CoCode) in ( SELECT DISTINCT upper(p.CoCode) from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') and p.CoCode is not null UNION SELECT DISTINCT upper(WritingCoCode) from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') and p.WritingCoCode is not null ) ) c2 on c2.CoCode = c1.ParentCoCode WHERE upper(c1.CoCode) in ( SELECT DISTINCT upper(p.CoCode) from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') and p.CoCode is not null UNION SELECT DISTINCT upper(WritingCoCode) from hgg_basicpolinfo p where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') and p.WritingCoCode is not null ) SELECT NameLOBS , IdLOBS , TypeOfBusLOBS , DescriptionLOBS , PermFlagLOBS , IsHide , [PLAN_TYPE]=CONVERT(varchar(100), null) , [PLAN_TYPE_ID]=CONVERT(int, null) FROM hgg_LOBSetup where upper(NameLOBS) in ( SELECT DISTINCT upper(b.LineOfBus) from hgg_basicpolinfo p join hgg_LineOfBusiness b on b.polid = p.polid where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) SELECT DISTINCT aa.ActnId , aa.DBAction , aa.PermFlag , aa.IsHide , [ACT_LOG_SUBJECT]=CONVERT(varchar(100), null) , [ACT_LOG_SUBJECT_ID]=CONVERT(int, null) FROM hgg_ActivityAction aa JOIN hgg_transaction t on t.DBAction = aa.DBAction join hgg_Customer c on CONVERT(varchar(36), c.CustId) = EntityId join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') UNION SELECT DISTINCT aa.ActnId , aa.DBAction , aa.PermFlag , aa.IsHide , [ACT_LOG_SUBJECT]=CONVERT(varchar(100), null) , [ACT_LOG_SUBJECT_ID]=CONVERT(int, null) FROM hgg_ActivityAction aa JOIN hgg_suspense s on s.DBAction = aa.DBAction join hgg_Customer c on CONVERT(varchar(36), c.CustId) = EntityId join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') SELECT AttrCode , SortNo , Code , [Description] , Category , Id , PermFlag , IsHide , [EMPLOYER_BUSINESS_TYPE]=CONVERT(varchar(100), null) , [EMPLOYER_BUSINESS_TYPE_ID]=CONVERT(int, null) FROM hgg_PRCode WHERE AttrCode in ('BBT', 'TE') AND upper([Description]) in ( SELECT DISTINCT upper(BusType) as BusType FROM hgg_CusthggInfo ) SELECT AttrCode , SortNo , Code , [Description] , Category , Id , PermFlag , IsHide , [RESPONSIBILITY_TYPE]=CONVERT(varchar(100), null) , [RESPONSIBILITY_TYPE_ID]=CONVERT(int, null) FROM hgg_PRCode WHERE AttrCode in ('RES') AND upper([Description]) in ( SELECT DISTINCT upper(ccr.RespType) from hgg_custcontactresp ccr join hgg_custcontact cc on cc.CCntId = ccr.CCntId join hgg_basicpolinfo p on p.CustId = cc.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) SELECT DISTINCT BusDescr , Industry , [PRIMARY_INDUSTRY_NAME]=CONVERT(varchar(100), null) , [PRIMARY_INDUSTRY_ID]=CONVERT(int, null) FROM hgg_CusthggInfo b join ( SELECT DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) cl on cl.CustId = b.CustId WHERE BusDescr is not null AND Industry is not null SELECT AttrCode , SortNo , Code , [Description] , Category , Id , PermFlag , IsHide , [ATTACHMENT_TYPE_NAME]=CONVERT(varchar(100), null) , [ATTACHMENT_TYPE_ID]=CONVERT(int, null) FROM hgg_PRCode where AttrCode in ('DTY') SELECT DISTINCT Title , BROKER_ROLE_DESCRIPTION=CONVERT(nvarchar(100), null) , ROLE_ID=CONVERT(int, null) FROM hgg_EMPLOYEE WHERE Title IS NOT NULL AND LEN(Title) > 0 SELECT DISTINCT c.* from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') SELECT * FROM hgg_CusthggInfo SELECT DISTINCT * , PLAN_ID=CONVERT(int, null) from hgg_basicpolinfo p where p.TypeOfBus = 4 and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') SELECT [POLID]=CONVERT(varchar(100), null) , [PLAN_ID]=CONVERT(int, null) , [ADHOC_PRODUCT_ID]=CONVERT(int, null) , [PARENTPOLID]=CONVERT(varchar(100), null) SELECT [hgg_hgg_POLID]=CONVERT(varchar(100), null) , [EMPLOYER_ID]=CONVERT(int, null) , [PLAN_ID]=CONVERT(int, null) , [ADHOC_PRODUCT_ID]=CONVERT(int, null) , [CARRIER_ID]=CONVERT(int, null) , [PLAN_TYPE_ID]=CONVERT(int, null) , [PLAN_TYPE_NAME]=CONVERT(varchar(100), null) , [POLICY_NUM]=CONVERT(varchar(100), null) , [EFF_DATE]=CONVERT(datetime, null) , [RENEWAL_DATE]=CONVERT(datetime, null) SELECT [hgg_hgg_POLID]=CONVERT(varchar(100), null) , [hgg_hgg_POLICY_NUM]=CONVERT(varchar(100), null) , [hgg_hgg_CUST_ID]=CONVERT(varchar(100), null) , [hgg_hgg_CUST_NAME]=CONVERT(varchar(100), null) , [hgg_hgg_COCODE]=CONVERT(varchar(100), null) , [hgg_hgg_WRITINGCOCODE]=CONVERT(varchar(100), null) , [hgg_hgg_PRIMARY_CARRIER]=CONVERT(int, null) , [hgg_hgg_WRITING_PRI_CARRIER]=CONVERT(int, null) , [hgg_hgg_POLEFFDATE]=CONVERT(datetime, null) , [hgg_hgg_POLEXPDATE]=CONVERT(datetime, null) , [hgg_hgg_LOB]=CONVERT(varchar(100), null) SELECT [hgg_hgg_POLID]=CONVERT(varchar(100), null) , [hgg_hgg_POLICY_NUM]=CONVERT(varchar(100), null) , [hgg_hgg_CUST_ID]=CONVERT(varchar(100), null) , [hgg_hgg_CUST_NAME]=CONVERT(varchar(100), null) , [hgg_hgg_COCODE]=CONVERT(varchar(100), null) , [hgg_hgg_WRITINGCOCODE]=CONVERT(varchar(100), null) , [hgg_hgg_PRIMARY_CARRIER]=CONVERT(int, null) , [hgg_hgg_WRITING_PRI_CARRIER]=CONVERT(int, null) , [hgg_hgg_POLEFFDATE]=CONVERT(datetime, null) , [hgg_hgg_POLEXPDATE]=CONVERT(datetime, null) , [hgg_hgg_LOB]=CONVERT(varchar(100), null) SELECT DISTINCT cc.* from hgg_custcontact cc join hgg_basicpolinfo p on p.CustId = cc.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') SELECT DISTINCT ccr.* from hgg_custcontactresp ccr join hgg_custcontact cc on cc.CCntId = ccr.CCntId join hgg_basicpolinfo p on p.CustId = cc.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') select lob.* from hgg_lineofbusiness lob join hgg_basicpolinfo p on p.PolID = lob.PolID where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') and lob.EFFDATE != lob.EXPDATE --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') SELECT d.* from hgg_dependent d join ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) cl on cl.CustId = d.CustId SELECT s.SuspId , s.EntityId , s.EntityType , s.DueDate , s.Priority , s.SuspToEmpCode , s.SuspByEmpCode , s.PolId , s.EffDate , s.CoCode , s.ReschedNo , s.InitiateDate , s.Status , s.DescriptSusp , s.DBAction , s.RefId , s.RefType , s.LastExported , s.OutlookId , s.ChangedBy , s.ChangedDate , s.EnteredDate from hgg_suspense s join ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) cl on CONVERT(varchar(36), cl.CustId) = s.EntityId WHERE s.ENTEREDDATE is null or (s.ENTEREDDATE >= CONVERT(datetime, '01/01/1900')) --AND S.PolId IS NOT NULL SELECT t.TranId , t.EntityId , t.EntityType , t.PolId , t.CoCode , t.EndEffDate , t.DBAction , t.TranDate , t.EmpCode , t.PolNo , t.PolTypeLOB , t.EffDate , t.ExpDate , t.ExecCode , t.CsrCode , t.TranType , t.CommentTran , t.RefId , t.RefType , t.RefText , t.RefDate , t.RefGrId , t.RefGrChangedBy , t.RefGrChangedDate , t.ChangedBy , t.ChangedDate , t.EnteredDate , ACT_LOG_ID=CONVERT(int, null) from hgg_transaction t join ( select DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) cl on CONVERT(varchar(36), cl.CustId) = t.EntityId WHERE t.TRANDATE is null or (t.TRANDATE >= CONVERT(datetime, '01/01/1900')) --AND t.PolId IS NOT NULL SELECT da.DocAId , da.[Status] , da.DocumentStatus , da.AgencyDocType , [Description]=LEFT(da.[Description], 100) , Comment=CONVERT(varchar(500), da.Comment) , da.ReceivedDate , da.[FileName] , da.FileExt , da.DocType , da.StorageOpt , da.StorageSize , da.ImageID , dr.attachid , dr.attachtype , t.PolID , CustID=t.EntityID , da.ChangedBy , Empty=CONVERT(text,null) , [ATTACHMENT_ID]=CONVERT(int, null) from hgg_docattachment da join hgg_docrelation dr on dr.DocAId = da.DocAid join hgg_Transaction t on t.TranId = dr.AttachId join ( SELECT DISTINCT c.CustID from hgg_customer c join hgg_basicpolinfo p on p.CustId = c.CustId where p.TypeOfBus = 4 -- hggs and upper(p.Status) in ('A','C') and upper(p.PolType) in ('M','P') and upper(p.PolSubType) in ('P') --and p.PolEffDate > '2008-11-01' --and p.GLDivCode in ('','','') --and p.GLBrnchCode in ('','','') and p.GLDeptCode in ('!!$') --and p.GLGrpCode in ('','','') ) cl on CONVERT(nvarchar(36), cl.CustId) = t.EntityID WHERE da.[Status] in ('A', 'C') AND da.StorageOpt in ('DB') AND dr.attachtype = 185 -- TRANSACTION AND t.entitytype = 4 -- CUSTOMER AND da.ReceivedDate is null or (da.ReceivedDate >= CONVERT(datetime, '01/01/1900'))