or cho_type = 'C' ) and m.fsltablecode = 'CHO' and m.fsl_choId > 0 and datediff( month, #_compareDateExpr#, getDate()) > #_inactiveMonthThreshold# and m.crmActive = 'Y' and m.oprCompanyType = '0' and m.ownerId = 155 order by ownerId, fsltablecode, lastActiveDate ---> select ownerId, fsltablecode, operatorId, crmActiveDate, crmDeactiveDate, datediff( day, crmActiveDate, crmDeactiveDate ), dateCreated, datediff( day, dateCreated, dateadd( month, -1, crmDeactiveDate )) test, case when datediff( day, dateCreated, dateadd( month, -1, crmDeactiveDate )) < 0 then dateadd( month, -1, crmDeactiveDate ) else convert( datetime, format( dateCreated, 'MM/dd/yyyy' )) end as newActiveDate from tbl_OPR_CLientOperators with (nolock) where crmActive = 'N' and datediff( day, crmActiveDate, crmDeactiveDate ) <= 0 and fsltablecode = 'CHO' and ownerId > 0 order by ownerId update tbl_OPR_ClientOperators set crmActiveDate = where ownerId = and fsltablecode = and operatorId =
#qmd_badActivationDates.recordCount# Bad Activation Date fixes applied
 or cho_type = 'C' ---> select cho_id, cho_type, cho_name, case when ( cho_type = 'G' and cho_segmentId = 54 ) then 1 when cho_type = 'G' then 2 else 3 end as sortRank from tbl_CHO_Operators with (nolock) where cho_type in ( 'G', 'C' ) and exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = cho_id and opr.fsltablecode = 'CHO' ) and cho_id not in ( select cho_id from tbl_CHO_Operators with (nolock) where cho_segmentId = 56 and cho_1fsSchoolsMember = 'Y' ) order by sortRank, cho_name with x as ( select cho_id, cho_type, cho_name, case when ( cho_type = 'G' and cho_segmentId = 54 ) then 3 when cho_type = 'G' then 2 else 2 end as oldSortRank , case when exists ( select * from tbl_TPM_Claims c with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and opr.fsl_choId = cho_id and opr.operatorId = c.partnerId and 'OPR' = c.partnerType inner join tbl_TPM_Settings s with (nolock) on s.ownerId = c.ownerId and s.ownerType = c.ownerType and s.dataHarvesting = 'Y' ) then 1 else 0 end as claims from tbl_CHO_Operators with (nolock) where cho_type in ( 'G', 'C' ) and exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = cho_id and opr.fsltablecode = 'CHO' ) and cho_id not in ( select cho_id from tbl_CHO_Operators with (nolock) where cho_segmentId = 56 /*and cho_1fsSchoolsMember = 'Y' */ ) ) select case when claims = 1 then case when cho_type = 'G' then 3 else 2 end else 1 end as sortRank, * from x order by sortRank desc, cho_name ---> with claimSources as ( select cho_id, cho_type, cho_name, case when exists ( select * from tbl_OPR_SubGroups sg where sg.ownerId = cho_id and sg.ownerType = 'CHO' ) then 1 else 0 end subGroups, 1 as claims from tbl_CHO_Operators with (nolock) where cho_type in ( 'G', 'C' ) and exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = cho_id and opr.fsltablecode = 'CHO' ) and cho_id not in ( select cho_id from tbl_CHO_Operators with (nolock) where cho_segmentId = 56 /*and cho_1fsSchoolsMember = 'Y' */ ) and exists ( select * from tbl_TPM_Claims c with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and opr.fsl_choId = cho_id and opr.operatorId = c.partnerId and 'OPR' = c.partnerType inner join tbl_TPM_Settings s with (nolock) on s.ownerId = c.ownerId and s.ownerType = c.ownerType and s.dataHarvesting = 'Y' ) ), subGroups as ( select cho.cho_id, cho.cho_type, cho.cho_name, 0 as subGroups, 0 as claims from claimSources cs inner join tbl_OPR_SubGroups sgl on sgl.ownerType = 'CHO' and sgl.ownerId = cs.cho_id inner join tbl_OPR_ClientOperators sg on sg.ownerId = sgl.ownerId and sg.fsltablecode = sgl.ownerType and sg.operatorId= sgl.operatorId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_type in ( 'G', 'C' ) and cho.cho_id = sg.fsl_choId and exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = cho.cho_id and opr.fsltablecode = 'CHO' ) and cho.cho_segmentId != 56 and not exists ( select * from tbl_TPM_Claims c with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and opr.fsl_choId = cho.cho_id and opr.operatorId = c.partnerId and 'OPR' = c.partnerType inner join tbl_TPM_Settings s with (nolock) on s.ownerId = c.ownerId and s.ownerType = c.ownerType and s.dataHarvesting = 'Y' ) where cs.subGroups = 1 ), allMemberGroups as ( select *, subGroups + claims as ranking from claimSources union all select *, subGroups + claims as ranking from subGroups ) select * from allMemberGroups order by ranking desc, cho_name update tbl_EAI_inboundBatches set message = , statusTime = getDate() where ownerId = and ownerType = and batchId = with s as ( select m.ownerId, m.fsltablecode, m.operatorId, m.crmActiveDate, m.freshnessDate, m.fsl_choId, coalesce( m.doNotUse, 'N' ) doNotUse, count( distinct c.claimId ) as claims, min( coalesce( c.claimPeriodEnd, c.claimDate ) ) firstClaim, max( coalesce( c.claimPeriodEnd, c.claimDate )) lastClaim from tbl_OPR_cLientOperators co with (nolock) inner join tbl_TPM_CLaims c with (nolock) on c.ownerId = co.ownerId and c.ownerType = co.fsltablecode and c.partnerType = 'OPR' and c.partnerId = co.operatorId and c.ownerId not in ( 6500, 6868 ) inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = c.ownerId and pod.ownerType = c.ownerType and pod.claimId = c.claimId inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = pod.operatorId and opr.ownerId = pod.ownerId and opr.fsltablecode = pod.ownerType and opr.fsl_choId > 0 inner join tbl_OPR_CLientOperators m with (nolock) on m.ownerId = co.fsl_choId and m.fsltablecode = 'CHO' and m.fsl_choId = opr.fsl_choId and m.oprCompanyType = '0' where co.fsl_choId = and co.fsltablecode = 'MFR' and ( coalesce( m.doNotUse, 'N' ) != 'Y' or ( m.doNotUse = 'Y' and m.mfrCustNum = pod.operatorAffiliateNbr )) group by m.ownerId, m.fsltablecode, m.operatorId, m.crmActiveDate, m.freshnessDate, m.fsl_choId, m.doNotUse ) select * from s where 1 = 2 or crmActiveDate is null or freshnessDate is null or crmActiveDate > firstClaim or lastClaim > freshnessDate update tbl_EAI_inboundBatches set message = , statusTime = getDate() where ownerId = and ownerType = and batchId = update tbl_OPR_CLientOperators set freshnessDate = , freshnessComment = 'updateMembershipStatus - doNoUse' where ownerId = and fsltablecode = and operatorId = inner join tbl_OPR_ClientOperators org with (nolock) on org.ownerId > 0 and org.fsltablecode = 'MFR' and org.fsl_choId = m.ownerId inner join tbl_CRM_Affiliations a with (nolock) on a.orgId = org.operatorId and a.ownerId = org.ownerId and a.ownerType = org.fsltablecode and a.orgType = 'OPR' and a.partnerType = 'OPR' inner join tbl_OPR_ClientOperators p with (nolock) on p.operatorId = a.partnerId and p.ownerId = a.ownerId and p.fsltablecode = a.ownerType and p.fsl_choId = m.fsl_choId where m.ownerId = and m.fsl_choId > 0 and m.fsltablecode = 'CHO' and m.oprCOmpanyType = '0' and m.crmActiveDate is null and not exists ( select * from tbl_TPM_Claims c with (nolock) inner join tbl_OPR_CLientOperators co with (nolock) on co.ownerId = c.ownerId and co.fsltablecode = c.ownerType and co.operatorId = c.partnerId where c.partnerType = 'OPR' and co.fsl_choId = m.ownerId ) group by m.ownerId, m.fsltablecode, m.operatorId, m.fsl_choId, m.companyName, m.crmActiveDate, m.crmDeactiveDate, m.freshnessDate, m.dateCreated, m.lastUpdated update tbl_OPR_CLientOperators set crmActiveDate = , freshnessDate = where ownerId = and fsltablecode = and operatorId = ---> update tbl_EAI_inboundBatches set message = , statusTime = getDate() where ownerId = and ownerType = and batchId = select m.ownerId, m.fsltablecode, m.operatorId, m.mfrCustNum, m.doNotUse, m.crmActiveDate, m.freshnessDate, m.fsl_choId, m.dateCreated from tbl_OPR_CLientOperators m with (nolock) where m.doNotUse = 'Y' and m.crmActive = 'Y' and m.fsltablecode = 'CHO' and m.ownerId = and nullif( m.mfrCustNum, '' ) is not null and not exists ( select * from tbl_OPR_ClientOperators co with (nolock) inner join tbl_TPM_CLaims c with (nolock) on c.ownerId = co.ownerId and c.ownerType = co.fsltablecode and c.partnerType = 'OPR' and c.partnerId = co.operatorId inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = c.ownerId and pod.ownerType = c.ownerType and pod.claimId = c.claimId and pod.operatorAffiliateNbr = m.mfrCustNum inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = pod.operatorId and opr.ownerId = pod.ownerId and opr.fsltablecode = pod.ownerType where co.fsltablecode = 'MFR' and co.ownerId > 0 and co.fsl_choId = m.ownerId ) update tbl_OPR_CLientOperators set crmActive = 'N', crmActiveDate = , crmDeactiveDate = , freshnessDate = , freshnessComment = 'updateMembershipStatus - unused doNotUse' where ownerId = and fsltablecode = 'CHO' and operatorId = update tbl_EAI_inboundBatches set message = , statusTime = getDate() where ownerId = and ownerType = and batchId = update m set m.crmDeactiveDate = case when coalesce( m.freshnessDate, coalesce( m.lastUpdated, coalesce( m.profileLastChecked, m.dateCreated ))) > m.crmActiveDate then coalesce( m.freshnessDate, coalesce( m.lastUpdated, coalesce( m.profileLastChecked, m.dateCreated ))) else dateadd( day, -1, dateadd( month, 1, dateFromParts( Year( m.crmActiveDate ), Month( m.crmActiveDate ), 1 ))) end from tbl_OPR_CLientOperators m where m.ownerId = and m.crmActive = 'N' and m.crmDeactiveDate is null and m.crmActiveDate is not null and m.fsl_choId > 0 select max( coalesce( claimPeriodEnd, claimDate )) mostRecentClaimPeriodEnd from tbl_TPM_Claims with (nolock) where partnerId in ( select operatorId from tbl_OPR_CLientOperators with (nolock) where fsltablecode ='MFR' and ownerId not in ( 6500, 6868 ) and fsl_choId = ) and partnerType = 'OPR' and reconciledDate is not null and coalesce( claimPeriodEnd, claimDate ) < getDate()
#cho_name# cutoff date is #lsdateformat( _cutOffDateToUse, "mm/dd/yyyy" )#
 select m.ownerId, m.fsltablecode, m.operatorId, #_compareDateExpr# lastActiveDate, m.fsl_choId from tbl_OPR_ClientOperators m with (nolock) where m.ownerId = and m.fsltablecode = 'CHO' -- and m.fsl_choId > 0 and datediff( day, #_compareDateExpr#, ) > 0 and m.crmActive = 'Y' and m.oprCompanyType = '0' update tbl_OPR_ClientOperators set crmActive = 'N', crmActiveDate = coalesce( crmActiveDate, case when datediff( day, dateCreated, dateadd( month, -1, )) < 0 then dateadd( month, -1, ) else convert( datetime, format( dateCreated, 'MM/dd/yyyy')) end ), crmDeactiveDate = , crmActiveReason = where ownerId = and fsltablecode = and operatorId = select m.ownerId, m.fsltablecode, m.operatorId, #_compareDateExpr# lastActiveDate, m.fsl_choId from tbl_OPR_ClientOperators m with (nolock) where m.ownerId = and m.fsltablecode = 'CHO' -- and m.fsl_choId > 0 and datediff( day, #_compareDateExpr#, ) <= 0 and m.crmActive = 'N' and m.oprCompanyType = '0' update tbl_OPR_ClientOperators set crmActive = 'Y', crmActiveDate = coalesce( crmActiveDate, coalesce( dateCreated, dateadd( month, -1, ))), crmDeactiveDate = null, crmActiveReason = null where ownerId = and fsltablecode = and operatorId = select distinct o.ownerId overrideOwnerId, o.fsltablecode overrideFslTablecode, o.operatorId overrideOperatorId, o2.ownerId, o2.fsltablecode, o2.operatorId, o2.fsl_choId from tbl_OPR_ClientOperators o with (nolock) inner join tbl_OPR_ClientOperators o2 with (nolock) on o2.ownerId > 0 and o2.fsl_choId = o.fsl_choId and o2.operatorId != o.operatorId where o.fsltablecode = 'CHO' and o.ownerId > 0 and o.freshnessDateOverridden = 'Y' and o.fsl_choId > 0 order by overrideOwnerId, overrideFslTablecode, overrideOperatorId update tbl_OPR_CLientOperators set freshnessDateOverridden = 'N' where ownerId = and fsltablecode = and operatorId =

Member Group Membership Status Processor

All 1FS library accounts for CMCs and Primary GPOs were processed as follows:

  1. Any membership that has been inactive more than #_inactiveMonthThreshold# months is deactivated (any member last active prior to the cutoff date)
  2. Operators with membership in more than one Primary GPO outside of a #_inactiveMonthThreshold# month transition period. The membership with the longest lapse in activity is deactivated.
  3. Any membership that has was inactive and has become active in the the past #_inactiveMonthThreshold# months has been activated (any member last active on or after the cutoff date)
    4.
Changes in membership status were propagated to client library accounts and other 1FS library accounts. No data was removed only the active state was changed.

select * from qmd_memberships where 1 = 1 and cho_type = 'G' and cho_type = 'C' order by cho_name
#_type# Membership Deactivation/Activation due to Membership Change
Member GroupDeactivate CountActivate Count
#htmleditformat( _memberships[ cho_id ].name )# #_memberships[ cho_id ].deactivateCount# #_memberships[ cho_id ].deactivateCount# #_memberships[ cho_id ].activateCount# #_memberships[ cho_id ].activateCount#

select m.fsl_choId, m.mfrCustNum, m.companyName, #_compareDateExpr# lastActiveDate from tbl_OPR_ClientOperators m with (nolock) where m.ownerId = and m.fsltablecode = 'CHO' and m.operatorId =
#htmleditformat( _memberships[ cho_id ].name )#'s Deactivated Members (#_memberships[ cho_id ].deactivateCount#) using #lsdateformat( _memberships[cho_id].cutoffDateToUse, 'MM/dd/yyyy' )#
1FS ID Member ## Operator Last Active Days Inactive
#qmd_operator.fsl_choId# #htmleditformat( qmd_operator.mfrCustNum )# #htmleditformat( qmd_operator.companyName )# (#_operatorId#) #lsdateformat( qmd_operator.lastActiveDate, 'mm/dd/yyyy' )# #datediff( 'd', qmd_operator.lastActiveDate, Now() )#

select m.fsl_choId, m.mfrCustNum, m.companyName, #_compareDateExpr# lastActiveDate from tbl_OPR_ClientOperators m with (nolock) where m.ownerId = and m.fsltablecode = 'CHO' and m.operatorId =
#htmleditformat( _memberships[ cho_id ].name )#'s Activated Members (#_memberships[ cho_id ].activateCount#) using #lsdateformat( _memberships[cho_id].cutoffDateToUse, 'MM/dd/yyyy' )#
1FS ID Member ## Operator Last Active Days Inactive
#qmd_operator.fsl_choId# #htmleditformat( qmd_operator.mfrCustNum )# #htmleditformat( qmd_operator.companyName )# (#_operatorId#) #lsdateformat( qmd_operator.lastActiveDate, 'mm/dd/yyyy' )# #datediff( 'd', qmd_operator.lastActiveDate, Now() )#
#_multipleMembershipsOutputHTML# insert into tbl_EAI_inboundBatchMessages( batchId, lineNumber, importId, message, messageLevel ) values ( , null, null, , 'INFO' )