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