select cs.clientsegid from tbl_OPR_ClientSegments cs inner join tbl_OPR_Segments s on cs.segmentId = cs.segmentId where cs.ownerId = and cs.fsl_tablecode = and s.segment = 'Healthcare' order by cs.sortRank select opr.operatorId from tbl_OPR_ClientOperators opr inner join tbl_CHO_Operators o on o.cho_id = opr.fsl_choid and o.cho_name = 'Novation' where opr.ownerId = and opr.fsltablecode = update tbl_IMPORT_NovationActivity set fseNovationOperatorId = where eai_batchId = and ownerId = and ownerType = update activity set activity.fseZip3d = left( usps.zipcode, 3 ) from tbl_IMPORT_NovationActivity activity inner join tbl_USPS_CityStateDetails usps on usps.cityStateName = activity.nov_cust_city and usps.stateAbbr = activity.nov_cust_st where activity.eai_batchId = and activity.ownerId = and activity.ownerType = update activity set activity.fseOperatorId = opr.operatorId, activity.fseOperatorStatus = 'U' from tbl_IMPORT_NovationActivity activity inner join tbl_CRM_Affiliations af on af.orgId = activity.fseNovationOperatorId and af.orgType = 'OPR' and af.partnerType = 'OPR' and af.partnerAffiliateNbr = activity.LIC inner join tbl_OPR_CLientOperators opr on opr.operatorId = af.partnerId and opr.ownerId = af.ownerId and opr.fsltablecode = af.ownerType where activity.eai_batchId = and activity.ownerId = and activity.ownerType = select distinct eai_batchId, ownerId, ownerType, lic, Nov_cust_Name, Nov_Cust_City, Nov_cust_St, fseZip3d from tbl_IMPORT_NovationActivity activity where activity.eai_batchId = and activity.ownerId = and activity.ownerType = and activity.fseOperatorId is null and activity.fseOperatorStatus is null select opr.operatorId, opr.companyName, opr.city, opr.state from tbl_OPR_ClientOperators opr left outer join tbl_CRM_Affiliations af on af.partnerId = opr.operatorId and af.partnerType = 'OPR' and af.affiliationType = 'GPO' and af.orgId = and af.orgType = 'OPR' where opr.ownerId = and opr.fsltablecode = and ( ( opr.city = and opr.state = ) or ( left( opr.zipCode, 3 ) = ) ) and af.affiliationId is null queryAddColumn( qmd_operatorLookup, "companyNameEditDistance", "integer", ArrayNew(1) ); Candidate Count = #qmd_operatorLookup.recordCount#
querySetCell( qmd_operatorLookup, "companyNameEditDistance", _companyNameDistance, qmd_operatorLookup.currentRow ); StructDelete( variables, "matcher" ); select * from qmd_operatorLookup where ( companyNameEditDistance <= 4 ) order by companyNameEditDistance update tbl_IMPORT_NovationActivity set fseOperatorId = , fseOperatorStatus = 'M' where eai_batchId = and ownerId = and ownerType = and lic = update tbl_IMPORT_NovationActivity set fseOperatorStatus = 'A', fseOperatorId = null where eai_batchId = and ownerId = and ownerType = and lic = update activity set activity.fseTerritoryId = ter.territoryId from tbl_IMPORT_NovationActivity activity inner join tbl_UT_ZipCodes_3d z3d on z3d.zipcode_3d = activity.fseZip3d inner join tbl_TER_ZipCodeLink zcl on zcl.zip3d_id = z3d.zip3d_id inner join tbl_TER_Territories ter on ter.territoryId = zcl.territoryId and ter.ownerId = activity.ownerId and ter.fsl_tablecode = activity.ownerType where activity.eai_batchId = and activity.ownerId = and activity.ownerType = and activity.fseOperatorStatus = 'A' select distinct lic, nov_cust_name, nov_cust_city, nov_cust_st, coalesce( fseTerritoryId, 0 ) fseTerritoryId from tbl_IMPORT_NovationActivity activity where activity.fseOperatorStatus = 'A' and activity.ownerId = and activity.ownerType = and activity.eai_batchId = set nocount on insert into tbl_OPR_ClientOperators ( ownerId, fsltablecode, companyName, city, state, recordSource, territoryId, setMethod, oprsegment ) values ( , , , , , 'Novation Activity #lsdateformat( Now(), "mm/dd/yyyy")#', , 2, ) update tbl_IMPORT_NovationActivity set fseOperatorId = @@identity where lic = and ownerId = and ownerType = and eai_batchId = select operatorId = @@identity, rowsAffected = @@rowcount set nocount off Identity = #qmd_addOperator.operatorId# Rows Affected = #qmd_addOperator.rowsAffected#
insert into tbl_CRM_Affiliations ( ownerId, ownerType, orgId, orgType, partnerAffiliateNbr, partnerId, partnerType, busName, busCity, busState, busPostalCode, affiliationStatus, affiliationDate, sourceId, sourceType, affiliationType ) select distinct activity.ownerId, activity.ownerType, activity.fseNovationOperatorId, 'OPR', activity.lic, activity.fseOperatorId, 'OPR', activity.nov_cust_name, activity.nov_cust_city, activity.nov_cust_st, activity.fseZip3D, 'A', { fn now() }, -1, 'SYS', 'GPO' from tbl_IMPORT_NovationActivity activity where activity.ownerId = and activity.ownerType = and activity.eai_batchId = and activity.fseOperatorStatus in ( 'M', 'A' ) and activity.fseOperatorId is not null update af set af.busName = activity.nov_cust_name, af.busCity = activity.nov_cust_city, af.busState = activity.nov_cust_st, af.busPostalCode = activity.fseZip3d, af.affiliationStatus = 'A' from tbl_CRM_Affiliations af inner join tbl_IMPORT_NovationActivity activity on activity.fseNovationOperatorId = af.orgId and activity.lic = af.partnerAffiliateNbr and activity.fseOperatorId = af.partnerId where af.orgType = 'OPR' and af.partnerType = 'OPR' and activity.ownerId = and activity.ownerType = and activity.eai_batchId = and activity.fseOperatorStatus = 'U' update activity set activity.fseMktCategoryId = mktCat.filterId, activity.fseMktSubCategoryId = mktSubCat.attrId from tbl_IMPORT_NovationActivity activity inner join tbl_PRD_SKus s on s.sku = activity.mfg_code and s.ownerId = activity.ownerId and s.fsl_tablecode = activity.ownerType inner join tbl_PRD_Products p on p.prodId = s.prodId inner join tbl_ORG_AttributeLinks scl on scl.child_attrId = s.prodId and scl.child_attrTypeId = 59 and scl.mstr_attrTypeId = 29 inner join tbl_ORG_Attributes mktSubCat on mktSubCat.attrId = scl.mstr_attrId and mktSubCat.ownerId = s.ownerId and mktSubCat.fsl_tablecode = s.fsl_tablecode inner join tbl_ORG_AttributeLinks cl on cl.child_attrId = mktSubCat.attrId and cl.child_attrTypeId = 29 and cl.mstr_attrTypeId = 28 inner join tbl_CMM_Filters mktCat on mktCat.filterId = cl.mstr_attrId and mktCat.ownerId = mktSubCat.ownerId and mktCat.fsl_tablecode = mktSubCat.fsl_tablecode inner join tbl_CMM_FilterTypes ft on ft.filterTypeId = mktCat.filterTypeId and ft.filterTypeName = 'ProductHierarchy' where activity.ownerId = and activity.ownerType = and activity.eai_batchId = select fp.focusProductId, rollup.fseOperatorId, rollup.fseMktCategoryId, rollup.fseMktSubCategoryId, sum( rollup.avgQty ) avgQty, sum( rollup.avgSale ) avgSale from ( select fseOperatorId, fseMktCategoryId, fseMktSubCategoryId, mfg_code, round( avg( convert( float, qty )), 0 ) avgQty, round( avg( convert( float, contract_sales )), 0) avgSale from tbl_IMPORT_NovationActivity where ownerId = and ownerType = and eai_batchId = and fseOperatorId is not null group by fseOperatorId, fseMktCategoryId, fseMktSubCategoryId, mfg_code ) rollup inner join tbl_MFR_FocusProducts fp on fp.ownerId = and fp.ownerType = and fp.mktCategoryId = rollup.fseMktCategoryId and fp.mktSubCategoryId = rollup.fseMktSubCategoryId group by fp.focusProductId, rollup.fseOperatorId, rollup.fseMktCategoryId, rollup.fseMktSubCategoryId #qmd_purchasingSummary.recordCount# select distinct activity.LIC, activity.nov_cust_name, activity.nov_cust_city, activity.nov_cust_st, activity.fseOperatorStatus, activity.fseZip3d, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode from tbl_IMPORT_NovationActivity activity inner join tbl_OPR_ClientOperators opr on opr.operatorId = activity.fseOperatorId and opr.ownerId = activity.ownerId and opr.fsltablecode = activity.ownerType where activity.ownerId = and activity.ownerType = and activity.eai_batchId = and activity.fseOperatorStatus in ( 'M','U', 'A') order by activity.nov_cust_name

FoodServiceRewards Distributor File Load Results

Novation Operators Loaded

LIC	Name	Addr 1	Addr 2	City	State	Zip	Status
#LIC#	#nov_cust_name#	&##160;	&##160;	#nov_cust_city#	#nov_cust_st#	#fseZip3d#	Matched	AddedExisting
&##160;	#companyName#	#address#	#address2#&##160;	#city#	#state#	#zipCode#	Matched
#qmd_loaded.recordCount# Operators Loaded

select distinct activity.fseOperatorId, activity.lic, activity.nov_cust_name, activity.nov_cust_city, activity.nov_cust_st, activity.fseZip3d from tbl_IMPORT_NovationActivity activity inner join tbl_OPR_CLientOperators opr on opr.operatorId = activity.fseOperatorId and opr.ownerId = activity.ownerId and opr.fsltablecode = activity.ownerType left outer join tbl_TER_Territories ter on ter.territoryId = opr.territoryId and ter.ownerId = opr.ownerId and ter.fsl_tablecode = opr.fsltablecode where activity.eai_batchId = and activity.ownerId = and activity.ownerType = and ter.territoryId is null

Novation Operators NOT Mapped to Territory

LIC	Name	City	State	Zip3d
#LIC#	#nov_cust_name#	#nov_cust_city#	#nov_cust_st#	#fseZip3d#