select formatName, formatReady from tbl_EAI_inboundFileFormats where formatName = update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseCdrRecordId = null, fseOperatorId = null, fseMfrId = null, fseSkuId = null, clientOperatorId = null, clientCdrRecordId = null where ownerId = and ownerType = and eai_batchId = select csop.distributor, cdr.cdr_recordId, cdr.fsl_dstId, count( distinct csop.school_district ) as 'Importable Operators' from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = 1003723 and cdr.fsl_tablecode = 'CHO' and LTRIM( RTRIM( cdr.cdr_dstName ) ) = LTRIM( RTRIM( csop.distributor ) ) where csop.eai_batchId = and csop.ownerId = and csop.ownerType = group by csop.distributor, cdr.cdr_recordId, cdr.fsl_dstId insert into tbl_CDR_Distributors ( ownerId, fsl_tablecode, cdr_dstName, crmActive, recordSource, createDate ) values ( 1003723, 'CHO', , 'Y', 'Added via Cool School Operator Purchasing Import', { fn now() } ) update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseCdrRecordId = where eai_batchId = and distributor = select queueId from tbl_1FS_MappingQueue with (nolock) where operatorOwnerId = 1003723 and operatorOwnerType = 'CHO' and cdr_recordId = insert into tbl_1FS_MappingQueue ( queueDate, operatorOwnerId, operatorOwnerType, cdr_recordId, linkLevel, mapCompanyType ) values ( , 1003723, 'CHO', , , 'P,B,RDC' ) select cdr_recordId from tbl_CDR_Distributors with (nolock) where ownerId = and fsl_tablecode = and fsl_dstId = and fsl_mapPriority = 1 update tbl_IMPORT_CoolSchoolOperatorPurchasing set clientCdrRecordId = where eai_batchId = and distributor = select distinct csop.iris_id, csop.school_district, csop.city, csop.state, csop.fseCdrRecordId, cdr.fsl_dstId from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = 1003723 and cdr.fsl_tablecode = 'CHO' and cdr.cdr_recordId = csop.fseCdrRecordId where csop.eai_batchId = and csop.ownerId = and csop.ownerType = --and nullif( cdr.fsl_dstId, 0 ) is not null select operatorId from tbl_OPR_ClientOperators with (nolock) where ownerId = 1003723 and fsltablecode = 'CHO' and mfrCustNum = insert into tbl_OPR_ClientOperators ( ownerId, fsltablecode, mfrCustNum, companyName, city, state, crmActive, recordSource, dateCreated, independentYN, oprcompanytype, distrib1 ) values ( 1003723, 'CHO', , , , , 'Y', 'Added via Cool School Operator Purchasing Import', { fn now() }, 1, '0', ) update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseOperatorId = where eai_batchId = and iris_id = update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseOperatorId = where eai_batchId = and iris_id = update tbl_OPR_ClientOperators set companyName = , city = , state = , distrib1 = where ownerId = 1003723 and fsltablecode = 'CHO' and operatorId = select fsl_choId from tbl_OPR_ClientOperators with (nolock) where ownerId = 1003723 and fsltablecode = 'CHO' and operatorId = and nullif( fsl_choId, 0 ) is not null select operatorId from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and fsl_choId = update tbl_IMPORT_CoolSchoolOperatorPurchasing set clientOperatorId = where eai_batchId = and fseOperatorId = select * from tbl_IMPORT_CoolSchoolOperatorPurchasing where eai_batchId = and fseOperatorId = select queueId from tbl_1FS_MappingQueue with (nolock) where operatorOwnerId = 1003723 and operatorOwnerType = 'CHO' and operatorId = insert into tbl_1FS_MappingQueue ( queueDate, operatorOwnerId, operatorOwnerType, operatorId, linkLevel, mapCompanyType, listName ) values ( , 1003723, 'CHO', , , 'O,I', 'Cool School Operator Purchasing Batch' ) select distinct csop.mfr_name from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) where csop.eai_batchId = and csop.ownerId = and csop.ownerType = select mfr_id from tblManufacturers where trim( mfr_name ) = trim( ) update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseMFRId = where ownerType = and ownerId = and eai_batchid = and mfr_name = select distinct csop.product_code, csop.fseMfrId from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) where csop.eai_batchId = and csop.ownerId = and csop.ownerType = and nullif( csop.fseMfrId, 0 ) is not null update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseSKUId = , fseSKU = where ownerType = and ownerId = and eai_batchid = and product_code = select distinct csop.product_code from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) where csop.eai_batchId = and csop.ownerId = and csop.ownerType = and nullif( csop.product_code, '' ) is not null and nullif( csop.fseMfrId, 0 ) is null update tbl_IMPORT_CoolSchoolOperatorPurchasing set fseMFRId = , fseSKUId = , fseSKU = where ownerType = and ownerId = and eai_batchid = and product_code = ---> select distinct csop.mfr_name from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) where csop.eai_batchId = and csop.ownerId = and csop.ownerType = and nullif( csop.fseMfrId, 0 ) is null

Unresolved Manufacturers

 select distinct csop.product_code, csop.product_description, csop.mfr_name from tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) where csop.eai_batchId = and csop.ownerId = and csop.ownerType = and nullif( csop.fseSKUId, 0 ) is null

Unresolved SKUs

 select fsl_dstId, cdr_dstName AS distributorName from tbl_CDR_Distributors WITH (NOLOCK) where ownerId = 1003723 and fsl_tablecode = 'CHO' and fsl_dstId in ( )

Distributors Missing in Client Library

update csop set loadStatus = case when isnull( fseOperatorId, 0 ) <> 0 and isnull( clientOperatorId, 0 ) <> 0 and isnull( fseCdrRecordId, 0 ) <> 0 and isnull( clientCdrRecordId, 0 ) <> 0 and isnull( fseMfrId, 0 ) <> 0 and isnull( fseSkuId, 0 ) <> 0 and last_purchase_date >= first_purchase_date then 'READY' else 'NOT_LOADED' end from tbl_IMPORT_CoolSchoolOperatorPurchasing csop where csop.eai_batchId = and csop.ownerId = and csop.ownerType = SELECT loadStatus, COUNT(*) AS totalCount FROM tbl_IMPORT_CoolSchoolOperatorPurchasing csop (nolock) WHERE csop.eai_batchId = AND csop.ownerId = AND csop.ownerType = GROUP BY loadStatus SELECT * FROM tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) WHERE csop.eai_batchId = AND csop.ownerId = AND csop.ownerType = select distinct fsl_id, ra_nbr, iris_id, school_district, city, state from qmd_records SELECT MAX( csop.fseOperatorId ) fseOperatorId, MAX( csop.clientOperatorId ) clientOperatorId, opr.companyName, opr.fsl_choId, CASE WHEN MAX( ISNULL( csop.fseOperatorId, 0 ) ) <> 0 AND MAX( ISNULL( csop.clientOperatorId, 0 ) ) <> 0 THEN 'Mapped Successfully' WHEN MAX( ISNULL( csop.fseOperatorId, 0 ) ) <> 0 AND MAX( ISNULL( csop.clientOperatorId, 0 ) ) = 0 THEN 'Missing Operator from Client' ELSE 'OK' END AS issue FROM tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) left join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = 1003723 and opr.fsltablecode = 'CHO' and opr.operatorId = csop.fseOperatorId WHERE csop.eai_batchId = AND csop.ownerId = AND csop.ownerType = GROUP BY companyName, fsl_choId ORDER BY companyName select * from qmd_operatorSummary where issue = 'Mapped Successfully' select companyName, fseOperatorId, fsl_choId from qmd_operatorSummary where issue = 'Missing Operator from Client' select distinct distributor from qmd_records SELECT MAX( csop.fseCdrRecordId ) fseCdrRecordId, MAX( csop.clientCdrRecordId ) clientCdrRecordId, cdr.cdr_dstName companyName, cdr.fsl_dstId, CASE WHEN MAX( ISNULL( fseCdrRecordId, 0 ) ) <> 0 AND MAX( ISNULL( clientCdrRecordId, 0 ) ) <> 0 THEN 'Mapped Successfully' WHEN MAX( ISNULL( fseCdrRecordId, 0 ) ) <> 0 AND MAX( ISNULL( clientCdrRecordId, 0 ) ) = 0 THEN 'Missing Distributor from Client' ELSE 'OK' END AS issue FROM tbl_IMPORT_CoolSchoolOperatorPurchasing csop with (nolock) left join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = 1003723 and cdr.fsl_tablecode = 'CHO' and cdr.cdr_recordId = csop.fseCdrRecordId WHERE csop.eai_batchId = AND csop.ownerId = AND csop.ownerType = GROUP BY cdr_dstName, fsl_dstId ORDER BY cdr_dstName select * from qmd_distributorSummary where issue = 'Mapped Successfully' select companyName, fseCdrRecordId, fsl_dstId from qmd_distributorSummary where issue = 'Missing Distributor from Client'

Batch Processing Summary

Total Rows From File:#_totalRecordsFromFile#
Total Rows Processed:#_recordsLoaded#
Total Rows Not Processed:#_recordsNotLoaded#

Operator Mapping Summary

Total Operators Detected:#_totalOperatorsFromFile#
Mapped Successfully:#qmd_mappedOperators.recordCount#
Missing or Unmapped in Client:#qmd_missingOperators_client.recordCount#

Distributor Mapping Summary

Total Distributors Detected:#_totalDistributorsFromFile#
Mapped Successfully:#qmd_mappedDistributors.recordCount#
Missing or Unmapped in Client:#qmd_missingDistributors_client.recordCount#

Unresolved Records

Unresolved Manufacturers:#_totalUnresolvedManufacturers#
Unresolved SKUs:#_totalUnresolvedSKUs#
Unresolved Distributors:#_totalUnresolvedDistributors#
select distinct iris_id, school_district, first_purchase_date, last_purchase_date from tbl_IMPORT_CoolSchoolOperatorPurchasing with (nolock) where eai_batchId = and ownerId = and ownerType = and last_purchase_date < first_purchase_date

Invalid Purchase Date Ranges (Wacky Dates)

Unresolved Invoice Records

 #results#