select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat = '$KELLANOVA_PRD_CATALOG_EXPORT' and batchTime < and status = 'OK' order by batchTime desc delete from tbl_EXPORT_KELLANOVA_Operator where ownerId = and ownertype = and eai_batchId = drop table if exists [#_tempFlexFieldTable#]; with flexValues as ( select f.name as field, fv.partnerId as operatorId, fl.selectionLabel as [value] from tbl_OPR_Fields f with (nolock) inner join tbl_OPR_FieldValues fv with (nolock) on fv.fieldId = f.fieldId and fv.partnerType = 'OPR' inner join tbl_OPR_FieldLookups fl with (nolock) on fl.selectionValue = fv.data and fl.fieldName = f.name and fl.ownerId = f.ownerId and fl.ownerType = f.ownerType where f.ownerId = and f.ownerType = and f.name in ( 'POP', 'GSV', 'BEDS', 'STUDENTS', 'EMPLOYEES', 'FUNDING' ) ) select p.operatorId, p.POP, p.GSV, p.BEDS, p.STUDENTS, p.EMPLOYEES, p.FUNDING into [#_tempFlexFieldTable#] from flexValues pivot( max( value ) for field in ( POP, GSV, BEDS, STUDENTS, EMPLOYEES, FUNDING )) as p where operatorId is not null; alter table [#_tempFlexFieldTable#] alter column operatorId int not null; alter table [#_tempFlexFieldTable#] add constraint [pk_#_tempFlexFieldTable#] primary key (operatorId); drop table if exists [#_tempPartnerURLTable#]; with partnerUrls as ( select f.urlType as smType, f.partnerId as operatorId, f.urlValue as smValue from tbl_CRM_PartnerURLs f with (nolock) where f.partnerType = 'OPR' and f.ownerId = and f.ownerType = and f.urlType in ( 'facebook', 'instagram', 'linkedin' ) ) select p.operatorId, p.facebook, p.instagram, p.linkedin into[#_tempPartnerURLTable#] from partnerUrls pivot( max( smValue ) for smType in ( facebook, instagram, linkedin )) as p where operatorId is not null alter table [#_tempPartnerURLTable#] alter column operatorId int not null; alter table [#_tempPartnerURLTable#] add constraint pk_#_tempPartnerURLTable# primary key ( operatorId ); insert into tbl_EXPORT_KELLANOVA_Operator( ownerId, ownerType, eai_batchId, eai_lineNumber, novalytics_id, name, address, address2, city, state, zip,county,country, phone, website, company_email_address, facebook_company_page, instagram, linkedin_company_page, crm_create_date, data_source_origin_detail, territory_division, territory_region,territory_name, classification, priority, segment_parent,segment,cuisine, primary_distributor_name, primary_distributor_code, primary_distributor_address, primary_distributor_city, primary_distributor_state, primary_distributor_zip, secondary_distributor_name, secondary_distributor_code, secondary_distributor_address, secondary_distributor_city, secondary_distributor_state, secondary_distributor_zip, primary_contact, primary_contact_novalytics_id, rep_firstname,rep_lastname, rep_email, number_of_units_last_year, number_of_units_ty, last_interaction_date, last_interaction_sales_rep_name, last_interaction_purpose, season_open_date, season_close_date, parent_company_novalytics_id, buying_decisions, cmc1,cmc1_nbr, cmc2, cmc2_nbr, cmc3, cmc3_nbr, gpo1, gpo1_nbr, gpo2, gpo2_nbr, gpo3, gpo3_nbr,cu_student_population, annual_gsv, number_of_patient_beds, k12_student_population, numberofemployees,cu_funding,company_type ) select ownerId, ownerType, #qmd_batch.batchId# as eai_batchId, row_number() over( order by companyName, co.operatorId ) as eai_lineNumber, co.operatorId,companyName,address, address2, city, state, zipCode as zip, county,country, companyPhone as phone, url as website, companyEmail as company_email_address, pp.facebook as facebook_company_page, pp.instagram , pp.linkedin as linkedin_company_page, co.dateCreated as crm_create_date, recordSource as data_source_origin_detail, divisionName as territory_division, regionName as territory_region, territoryName as territory_name, classification, priority, parentSegmentName as segment_parent, segmentName as segment, cuisine, distrib1_cdr_dstName as primary_distributor_name, distrib1_cdr_dstCode as primary_distributor_code, distrib1_cdr_dstAddress1 as primary_distributor_address, distrib1_cdr_dstCity as primary_distributor_city, distrib1_cdr_dstState as primary_distributor_state, distrib1_cdr_dstZip as primary_distributor_zip, distrib2_cdr_dstName as secondary_distributor_name, distrib2_cdr_dstCode as secondary_distributor_code, distrib2_cdr_dstAddress1 as secondary_distributor_address, distrib2_cdr_dstCity as secondary_distributor_city, distrib2_cdr_dstState as secondary_distributor_state, distrib2_cdr_dstZip as secondary_distributor_zip, CONCAT(primaryContactFirstName, ' ', primaryContactLastName) as primary_contact, primaryContactId as primary_contact_novalytics_id, salesRepFirstName as rep_firstname, salesRepLastName as rep_lastname, salesRepEmail as rep_email, numUnitsLastYear as number_of_units_last_year, numUnits as number_of_units_ty, lastInteractionDate as last_interaction_date, CONCAT(interactionRepFirstName, ' ', interactionRepLastName) as last_interaction_sales_rep_name, interactionPurposeName as last_interaction_purpose, seasonOpenDate as season_open_date, seasonCloseDate as season_close_date, parentOperatorId as parent_company_novalytics_id, buyingDecisions as buying_decisions, cmcName_1 as cmc1,cmcId_1 as cmc1_nbr, cmcName_2 as cmc2, cmcId_2 as cmc2_nbr, cmcName_3 as cmc3, cmcId_3 as cmc3_nbr, gpoName_1 as gpo1, gpoId_1 as gpo1_nbr, gpoName_2 as gpo2, gpoId_2 as gpo2_nbr, gpoName_3 as gpo3, gpoId_3 as gpo3_nbr, ff.POP as cu_student_population, ff.GSV as annual_gsv,ff.BEDS as number_of_patient_beds, ff.STUDENTS as k12_student_population, ff.EMPLOYEES as numberofemployees, ff.FUNDING as cu_funding, case when cho.cho_type = 'I' then 'INDEPENDENT' when cho.cho_type = 'U' then 'UNIT' when cho.cho_type = 'O' then 'PARENT' when cho.cho_type = 'C' then 'CMC' when cho.cho_type = 'G' then 'GPO' when cho.cho_type = 'H' then 'HOLDING' end as company_type from tbl_DW_CRMOperators_MFR207 co with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = co.oneFSId left outer join [#_tempFlexFieldTable#] ff with (nolock) on ff.operatorId = co.operatorId left outer join [#_tempPartnerURLTable#] pp with (nolock) on pp.operatorId = co.operatorId order by companyName, operatorId update tbl_EAI_InboundBatches set loadedColumns = 'novalytics_id,name,address,address2,city,state,zip,county,country,phone,website,company_email_address,facebook_company_page,instagram,linkedin_company_page,crm_create_date,data_source_origin_detail,territory_division,territory_region,territory_name,classification,priority,segment_parent,segment,cuisine,primary_distributor_name,primary_distributor_code,primary_distributor_address,primary_distributor_city,primary_distributor_state,primary_distributor_zip,secondary_distributor_name,secondary_distributor_code,secondary_distributor_address,secondary_distributor_city,secondary_distributor_state,secondary_distributor_zip,primary_contact,primary_contact_novalytics_id,rep_firstname,rep_lastname,rep_email,number_of_units_last_year,number_of_units_ty,last_interaction_date,last_interaction_sales_rep_name,last_interaction_purpose,season_open_date,season_close_date, parent_company_novalytics_id,buying_decisions,cmc1,cmc1_nbr,cmc2,cmc2_nbr,cmc3,cmc3_nbr,gpo1,gpo1_nbr,gpo2,gpo2_nbr,gpo3,gpo3_nbr,cu_student_population,annual_gsv,number_of_patient_beds,k12_student_population,numberofemployees,cu_funding,company_type' where ownerId = and ownertype = and batchId = drop table if exists [#_tempFlexFieldTable#]; drop table if exists [#_tempPartnerURLTable#]

Exported #lsnumberformat( _exportCount )# Operators updated on or after #lsdateFormat( _cutoffDate, "MM/dd/yyyy" )#.

select * from tbl_EXPORT_KELLANOVA_Operator with (nolock) where ownerId = and ownertype = and eai_batchId = order by eai_lineNumber