select formatName, formatReady from tbl_EAI_inboundFileFormats where formatName = update tbl_EAI_inboundBatches set continueEditorPath = where batchId = and ownerId = and ownerType = and continueEditorPath is null

Import Mapping Confirmation Required

The Proof Of Delivery format [#htmleditformat( qmd_batch.fileFormat )#] has been created or changed. All batches with this format will be suspended until the format is confirmed.

The batch ## is #lsnumberformat( qmd_batch.batchId, "0000000" )#. To review the new/changed format, go to Batch Manger, view the batch, click on the results tab and review the mapping.

select s.importMappingApprover from tbl_tpm_settings s with (nolock) where s.ownerId = and s.ownerType = select m.email from tbl_fspro_members m with (nolock) where m.ownerid = 21 and m.fsl_tablecode = 'MSC' and m.fspro_userid = select opr.fsl_choId as sourceId, 'CHO' as sourceType from tbl_OPR_CLientOperators opr with (nolock) inner join tbl_CHO_Operators with (nolock) on cho_id = opr.fsl_choId where opr.operatorId = and opr.ownerId = and opr.fsltablecode = select cdr.fsl_dstId as sourceId, 'DST' as sourceType from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors with (nolock) on dstId = cdr.fsl_dstId where cdr.cdr_recordId = and cdr.ownerId = and cdr.fsl_tablecode = select contractNbr from tbl_TPM_ClaimContracts where claimId = and ownerId = and ownerType = and ( reconciled = 'Y' or repaymentRequested <> 'N' ) ePOD data can no longer be loaded for this claim because the following Sub-Claims have been processed to a point (cleared, repayment requested) at which loading / reloading ePod would compromise claim processing integrity! select eai_importId, eai_lineNumber, operator_city_state_zip from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and nullif( operator_city_state_zip, '' ) is not null and nullif( operator_city, '' ) is null and nullif( operator_state, '' ) is null and nullif( operator_zip_code, '' ) is null update tbl_IMPORT_ProofOfDelivery set operator_city = , operator_state = , operator_zip_code = where eai_batchId = and ownerId = and ownerType = and eai_lineNumber = and eai_importId = update tbl_IMPORT_ProofOfDelivery set fseClaimId = where eai_batchId = and ownerId = and ownerType = select distinct distributor_name, distributor_state, distributor_zip_code from tbl_IMPORT_ProofOfDelivery with (nolock) where nullif( distributor_state, '' ) is not null and nullif( distributor_zip_code, '' ) is not null and nullif( distributor_name, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select ccdr.cdr_recordId, ccdr.cdr_dstcode, ccdr.cdr_dstName, ccdr.cdr_territoryId, ccdr.cdr_dstcity, ccdr.cdr_dststate from tbl_CDR_Distributors pcdr with (nolock) inner join tbl_DST_Distributors cfsl with (nolock) on cfsl.dstbuyingGrp = pcdr.fslBuyingGroupId and cfsl.dstbuyingGrp > 0 inner join tbl_CDR_Distributors ccdr with (nolock) on ccdr.fsl_dstId = cfsl.dstId and ccdr.ownerId = pcdr.ownerId and ccdr.fsl_tablecode = pcdr.fsl_tablecode where pcdr.cdr_recordId = and pcdr.fsl_tablecode = and pcdr.ownerId = and cfsl.dstState = and left( cfsl.dstZip, 5 ) = and ( 1 = 2 or cfsl.dstName like ) update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = where distributor_name = and distributor_state = and distributor_zip_code = and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct mfr_distributor_code, fseCdrRecordId from tbl_IMPORT_ProofOfDelivery with (nolock) where nullif( mfr_distributor_code, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct cdr.crmActive, cdr.cdr_recordId, cdr.cdr_territoryId, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) inner join tbl_CDR_DstCodes cdrc with (nolock) on cdrc.cdr_recordId = cdr.cdr_recordId and cdrc.ownerid = cdr.ownerId and cdrc.ownerType = cdr.fsl_tablecode where cdr.cdr_dstcompanytype = 'B' and cdr.ownerId = and cdr.fsl_tablecode = and cdrc.cdr_dstCode = order by cdr.crmActive desc, cdr.cdr_recordId update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = , distributor_name = case when nullif( distributor_name, '' ) is null then else distributor_name end where mfr_distributor_code = and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct distributor_code, fseCdrRecordId from tbl_IMPORT_ProofOfDelivery with (nolock) where nullif( distributor_code, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select cdrb.fsl_mapPriority, cdrb.crmActive, cdrb.cdr_recordId, cdrb.cdr_territoryId, cdrb.cdr_dstCompanyType from tbl_DST_Distributors dstb with (nolock) inner join tbl_CDR_Distributors cdrp with (nolock) on cdrp.fsl_dstId = dstb.dstparentco inner join tbl_CDR_Distributors cdrb with (nolock) on cdrb.fsl_dstid = dstb.dstId and cdrb.ownerid = cdrp.ownerId and cdrb.fsl_tablecode = cdrp.fsl_tablecode where dstb.dstcompanytype = 'B' and dstb.dstparentco > 0 and cdrb.ownerId = and cdrb.fsl_tablecode = and ( nullif( dstb.dstbranchId, '' ) = or nullif( dstb.dstOtherId, '' ) = ) and cdrp.cdr_recordId = and cdrp.cdr_recordId = order by cdrb.fsl_mapPriority, cdrb.crmActive desc, cdrb.cdr_recordId select * from qmd_branchDistributorLookup where cdr_dstCompanyType = 'B' order by fsl_mapPriority update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = where distributor_code = and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null update tbl_CDR_Distributors set crmActive = 'Y' where ownerId = and fsl_tablecode = and cdr_recordId = select cdr_recordId from tbl_CDR_Distributors with (nolock) where cdr_recordId = and ownerId = and fsl_tablecode = and fsl_dstId = 1319 select distinct distributor_code, coalesce( distributor_code_suffix, '' ) distributor_code_suffix from tbl_IMPORT_ProofOfDelivery with (nolock) where nullif( distributor_code, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select cdr.cdr_recordId from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.fsl_dstId in ( select dotCdr.fsl_dstId from tbl_CDR_Distributors dotCdr with (nolock) inner join tbl_CDR_DstCodes dotCodes with (nolock) on dotCodes.cdr_recordId = dotCdr.cdr_recordId and dotCodes.ownerId= dotCdr.ownerId and dotCodes.ownerType = dotCdr.fsl_tablecode where dotCDR.ownerId = 2524 and dotCDR.fsl_tablecode = 'BRO' and ( dotCodes.cdr_dstCode like or dotCdr.cdr_dstCode like ) ) update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = where fseCdrRecordId is null and distributor_code = and coalesce( distributor_code_suffix, '' ) = and eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = where fseCdrRecordId is null and eai_batchId = and ownerId = and ownerType = select distributor_name, distributor_code, mfr_distributor_code, count(*) lines, min ( eai_lineNumber ) firstLine from tbl_IMPORT_ProofOfDelivery with (nolock) where fseCdrRecordId is null and eai_batchId = and ownerId = and ownerType = group by distributor_name, distributor_code, mfr_distributor_code order by distributor_name Distributors may not be found because they are either missing completely or the master data settings are incorrect - i.e. more than one, incorrect priorities, etc... update i set operator_street = dst.dstName + ' Acct ' + case when len( operator_cust_nbr ) = 6 then dst.dstBranchId + '-' + operator_cust_nbr else operator_cust_nbr end, operator_city = dst.dstCity, operator_state = dst.dstState, operator_zip_code = dst.dstZip, operator_cust_nbr = case when len( operator_cust_nbr ) = 6 then dst.dstBranchId + '-' + operator_cust_nbr else operator_cust_nbr end from tbl_IMPORT_ProofOfDelivery i inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = i.fseCdrRecordId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId and dst.dstParentCo = 4036 where i.ownerId = and i.ownerType = and i.eai_batchId = and nullif( i.operator_street, '' ) is null and nullif( i.operator_city, '' ) is null and nullif( i.operator_state, '' ) is null and nullif( i.operator_zip_code, '' ) is null update i set operator_cust_nbr = case when left( operator_cust_nbr, 4 ) != dst.dstBranchId + '-' then dst.dstBranchId + '-' + operator_cust_nbr else operator_cust_nbr end from tbl_IMPORT_ProofOfDelivery i inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = i.fseCdrRecordId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId and dst.dstParentCo = 4994 where i.ownerId = and i.ownerType = and i.eai_batchId = update tbl_IMPORT_ProofOfDelivery set fseTraceHash = convert( varchar(100), hashbytes( 'SHA2_256', convert( varchar, coalesce( fseCdrRecordId, 0 ) ) + coalesce( operator_cust_nbr, 'blank' ) + coalesce( nullif( operator_name, '' ), 'blank' ) + coalesce( nullif( operator_street, '' ), 'blank' ) + coalesce( nullif( operator_city, '' ), 'blank' ) + coalesce( nullif( operator_state, '' ), 'blank' ) + coalesce( nullif( operator_zip_code, '' ), 'blank' )), 2) from tbl_IMPORT_ProofOfDelivery with (nolock) where ownerId = and ownerType = and eai_batchId = select distinct cdr.cdr_dstCode as primary_distributor_id, 'OPR' rec_type, operator_cust_nbr as primary_distrib_acctnbr, operator_name as company_name, operator_street as company_address1, operator_city as company_city, operator_state as company_state, operator_zip_code as company_zip, 'D' as company_priority , fseTraceHash from tbl_IMPORT_ProofOfDelivery pod inner join tbl_CDR_DstCodes cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordId and cdr.ownerId = pod.ownerId and cdr.ownerType = pod.ownerType and cdr.cdr_dstCode_type = 'M' and cdr.cdr_dstCode <> '' where eai_batchId = and nullif( operator_name, '' ) is not null and fseOperatorId is null and ( 1 = 2 or ( nullif( operator_city, '' ) is not null and nullif( operator_state, '' ) is not null and nullif( operator_street, '' ) is not null and nullif( operator_zip_code, '' ) is not null ) or nullif( operator_cust_nbr, '' ) is not null ) order by company_name insert into tbl_IMPORT_Operators ( eai_batchId, eai_lineNumber, ownerId, ownerType , #_c# ) values ( , , , , ) update tbl_EAI_InboundBatches set loadedColumns = , lineCount = where batchId = update tbl_EAI_inboundBatches set processingState = where batchId = and ownerId = and ownerType = select distinct fseOperatorId, fseTraceHash from tbl_IMPORT_Operators i with (nolock) inner join tbl_OPR_ClientOperators dl with (nolock) on dl.ownerId = i.ownerId and dl.fsltablecode = i.ownerType and dl.operatorId = i.fseOperatorId and dl.oprCompanyType = '0' where i.ownerId = and i.ownerType = and i.eai_batchId in ( ) order by fseOperatorId update cop set cop.fseOperatorId = from tbl_IMPORT_ProofOfDelivery cop where cop.ownerId = and cop.ownerType = and cop.eai_batchId = and cop.fseTraceHash =

Base Operator Results Binding Complete at #qmd_baseOperatorResult.recordCount# of #qmd_baseOperatorResult.recordCount# (#getTickCount() - _startTicks# ms.)

select distinct pod.operator_name, pod.operator_street, pod.operator_city, pod.operator_state, pod.operator_zip_code from tbl_IMPORT_ProofOfDelivery pod with (nolock) where eai_batchId = and ownerId = and ownerType = and fseOperatorId is null and nullif( operator_city, '' ) is not null and nullif( operator_name, '' ) is not null and nullif( operator_state, '' ) is not null and nullif( operator_street, '' ) is not null and nullif( operator_zip_code, '' ) is not null select top 1 fseOperatorId from tbl_IMPORT_Operators with (nolock) where eai_batchId = and ownerId = and ownerType = and fseOperatorId > 0 and company_name = and company_address1 = and company_city = and company_state = and company_zip = update tbl_IMPORT_ProofOfDelivery set fseOperatorId = , fseOperatorIdStatus = 'IMPORT2' where eai_batchId = and ownerId = and ownerType = and operator_name = and operator_street = and operator_city = and operator_state = and operator_zip_code = and fseOperatorId is null ---> select opr.oprCompanyType, opr.companyName, cho.cho_name, cho.cho_id from tbl_OPR_ClientOperators opr with (nolock) left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.operatorId = and opr.ownerId = and opr.fsltablecode = select distinct coalesce( distributor_code, '' ) distributor_code, distributor_name from tbl_IMPORT_ProofOfDelivery with (nolock) where nullif( distributor_name, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct top 1 cdr.cdr_recordId, cdr.cdr_dstName, cdr.fsl_mapPriority from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId inner join tbl_MDM_Aliases a with (nolock) on a.objectOwnerId = 66 and a.objectOwnerType = 'MSC' and a.objectId = dst.dstId and a.objectType = 'DST' where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_dstCompanyType = 'B' and a.sourceId = and a.sourceType = 'CHO' and a.masterDataType = 'DISTRIBUTOR' and a.aliasText = and a.aliasCode = and a.normalizeMethod = 'SIMPLE_1' order by cdr.fsl_mapPriority update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = , fseCdrRecordIdStatus = 'A', distributor_name = case when nullif( distributor_name, '' ) is null then else distributor_name end where fseCdrRecordId is null and distributor_name = and coalesce( distributor_code, '' ) = and eai_batchId = and ownerId = and ownerType = select distinct mfr_distributor_code from tbl_IMPORT_ProofOfDelivery with (nolock) where nullif( mfr_distributor_code, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct cdr.crmActive, cdr.cdr_recordId, cdr.cdr_territoryId, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) inner join tbl_CDR_DstCodes cdrc with (nolock) on cdrc.cdr_recordId = cdr.cdr_recordId and cdrc.ownerid = cdr.ownerId and cdrc.ownerType = cdr.fsl_tablecode where cdr.cdr_dstcompanytype = 'B' and cdr.ownerId = and cdr.fsl_tablecode = and cdrc.cdr_dstCode = order by cdr.crmActive desc, cdr.cdr_recordId update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = , distributor_name = case when nullif( distributor_name, '' ) is null then else distributor_name end where fseCdrRecordId is null and mfr_distributor_code = and eai_batchId = and ownerId = and ownerType = update pod set pod.operator_member_id = c.newFBNbr from tbl_IMPORT_ProofOfDelivery pod inner join FoodbuyNbrConversion c on c.legacyFBNbr = pod.operator_member_id where pod.ownerId = and pod.ownerType = and pod.eai_batchId = and pod.operator_member_id like 'FO%' select distinct operator_member_id, operator_name from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and nullif( operator_member_id, '' ) is not null and fseOperatorId is null update tbl_IMPORT_ProofOfDelivery set fseOperatorId = , fseOperatorIdStatus = 'BEP' where eai_batchId = and ownerId = and ownerType = and operator_member_id = and operator_name = and fseOperatorId is null select distinct operator_member_id, operator_name from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and ( nullif( operator_member_id, '' ) is not null or nullif( operator_name, '' ) is not null ) and fseOperatorId is null update tbl_IMPORT_proofOfDelivery set fseOperatorId = , fseOperatorIdStatus = 'ROSTER1' , operator_street = coalesce( nullif( operator_street, '' ), ) , operator_city = coalesce( nullif( operator_city, '' ), ) , operator_state = coalesce( nullif( operator_state, '' ), ) , operator_zip_code = coalesce( nullif( operator_zip_code, '' ), ) where eai_batchId = and ownerId = and ownerType = and coalesce( operator_member_id, '' ) = and coalesce( operator_name, '' ) = and fseOperatorId is null select distinct 'OPR' rec_type, pod.fseOperatorId, case when pod.fseOperatorId > 0 then 'U' else '?' end as fseOperatorStatus, operator_name as company_name, operator_street as company_address1, operator_city as company_city, operator_state as company_state, operator_zip_code as company_zip , operator_member_id as affiliate_nbr, as affiliation_orgName, '#_affiliationType#' as affiliation_type, 'C''D' as company_priority, coalesce( cdr.cdr_dstCode, '' ) primary_distributor_id, coalesce( operator_segment, '' ) as company_segment, coalesce( operator_contact_full_name, '' ) as contact_full_name, coalesce( operator_contact_title, '' ) as contact_title, coalesce( operator_contact_email, '' ) as contact_email, coalesce( operator_member_subgroup, '' ) as affiliation_subOrgName from tbl_IMPORT_ProofOfDelivery pod with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordID and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where pod.eai_batchId = and pod.ownerId = and pod.ownertype = and nullif( pod.operator_name, '' ) is not null and not ( ltrim( pod.operator_name ) like 'CUSTOMER%' ) and coalesce( pod.fseOperatorIdStatus, '' ) != 'BEP' and ( ( nullif( operator_member_id, '' ) is not null ) or ( nullif( operator_city, '' ) is not null and nullif( operator_state, '' ) is not null and ( nullif( operator_zip_code, '' ) is not null or nullif( cdr.cdr_dstCode, '' ) is not null ) ) ) order by company_name select distinct 'OPR' rec_type, pod.fseOperatorId, case when pod.fseOperatorId > 0 then 'U' else '?' end as fseOperatorStatus, operator_name as company_name, operator_street as company_address1, operator_city as company_city, operator_state as company_state, operator_zip_code as company_zip , operator_member_id as external_id, as external_id_owner, 'HQ' as external_id_type, 'D' as company_priority, coalesce( cdr.cdr_dstCode, '' ) primary_distributor_id, coalesce( operator_segment, '' ) as company_segment, coalesce( operator_contact_full_name, '' ) as contact_full_name, coalesce( operator_contact_title, '' ) as contact_title, coalesce( operator_contact_email, '' ) as contact_email from tbl_IMPORT_ProofOfDelivery pod with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordID and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where pod.eai_batchId = and pod.ownerId = and pod.ownertype = and nullif( operator_name, '' ) is not null and nullif( operator_city, '' ) is not null and nullif( operator_state, '' ) is not null -- and nullif( operator_member_id, '' ) is not null and ( nullif( operator_zip_code, '' ) is not null or nullif( cdr.cdr_dstCode, '' ) is not null or nullif( operator_member_id, '' ) is not null ) -- and fseOperatorId is null order by company_name insert into tbl_IMPORT_Operators ( eai_batchId, eai_lineNumber, ownerId, ownerType , #_c# ) values ( , , , , ) update tbl_EAI_InboundBatches set loadedColumns = , lineCount = where batchId = and ownerId = and ownerType= update tbl_EAI_inboundBatches set processingState = where batchId = and ownerId = and ownerType = select distinct pod.operator_name, pod.operator_street, pod.operator_city, pod.operator_state, pod.operator_zip_code, cdr.cdr_dstCode, pod.operator_member_id from tbl_IMPORT_ProofOfDelivery pod with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordID and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where pod.eai_batchId = and pod.ownerId = and pod.ownerType = and pod.fseOperatorId is null and nullif( pod.operator_city, '' ) is not null and nullif( pod.operator_name, '' ) is not null and nullif( pod.operator_state, '' ) is not null and ( nullif( pod.operator_zip_code, '' ) is not null or nullif( cdr.cdr_dstCode, '' ) is not null or nullif( pod.operator_member_id, '' ) is not null ) select top 1 fseOperatorId from tbl_IMPORT_Operators with (nolock) where eai_batchId = and ownerId = and ownerType = and fseOperatorId > 0 and company_name = and coalesce( company_address1, '' ) = and company_city = and company_state = and coalesce( company_zip, '' ) = and coalesce( primary_distributor_id, '' ) = update pod set pod.fseOperatorId = from tbl_IMPORT_ProofOfDelivery left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordID and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where eai_batchId = and ownerId = and ownerType = and operator_name = and coalesce( operator_street, '' ) = and operator_city = and operator_state = and coalesce( operator_zip_code, '' ) = and coalesce( cdr.cdr_dstCode, '') = and fseOperatorId is null ---> update tbl_IMPORT_ProofOfDelivery set fseClaimId = where eai_batchId = and ownerId = and ownerType =

Applying Base Operator Results

select distinct -- pod.eai_importId, pod.eai_lineNumber, pod.operator_name, pod.operator_street, pod.operator_city, pod.operator_state, pod.operator_zip_code, cdr.cdr_dstCode, pod.operator_member_id from tbl_IMPORT_ProofOfDelivery pod with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordID and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where pod.eai_batchId = and pod.ownerId = and pod.ownerType = and pod.fseOperatorId is null and nullif( pod.operator_name, '' ) is not null and nullif( pod.operator_city, '' ) is not null and nullif( pod.operator_state, '' ) is not null and ( nullif( pod.operator_zip_code, '' ) is not null or nullif( cdr.cdr_dstCode, '' ) is not null or nullif( pod.operator_member_id, '' ) is not null ) select fseOperatorId from tbl_IMPORT_Operators with (nolock) where eai_batchId = and ownerId = and ownerType = and fseOperatorId > 0 and company_name = and company_city = and company_state = and coalesce( company_address1, '' ) = and coalesce( company_zip, '' ) = and coalesce( primary_distributor_id, '' ) = update pod set pod.fseOperatorId = , pod.fseOperatorIdStatus = 'IMPORT1' from tbl_IMPORT_ProofOfDelivery pod left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.fseCdrRecordID and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where pod.eai_batchId = and pod.ownerId = and pod.ownerType = and pod.operator_name = and pod.operator_city = and pod.operator_state = and coalesce( pod.operator_street, '' ) = and coalesce( pod.operator_zip_code, '' ) = and coalesce( cdr.cdr_dstCode, '' ) = and fseOperatorId is null select opr.oprCompanyType, opr.companyName, cho.cho_name, cho.cho_id from tbl_OPR_ClientOperators opr with (nolock) left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.operatorId = and opr.ownerId = and opr.fsltablecode = select distinct operator_member_id, operator_name from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and ( nullif( operator_member_id, '' ) is not null or nullif( operator_name, '' ) is not null ) and fseOperatorId is null update tbl_IMPORT_proofOfDelivery set fseOperatorId = , fseOperatorIdStatus = 'ROSTER2' where eai_batchId = and ownerId = and ownerType = and coalesce( operator_member_id, '' ) = and coalesce( operator_name, '' ) = and fseOperatorId is null select distinct fseCdrRecordId cdr_recordId, pod.operator_cust_nbr, pod.operator_name from tbl_IMPORT_ProofOfDelivery pod with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.claimId = pod.fseClaimId and c.ownerId = pod.ownerId and c.ownerType = pod.ownerType where pod.eai_batchId = and pod.ownerId = and pod.ownerType = and nullif( pod.operator_cust_nbr, '' ) is not null and fseOperatorId is null and fseCdrRecordId is not null select opr.operatorId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_OPR_DistributionDetails dd with (nolock) on dd.operatorId = opr.operatorId and dd.ownerId = opr.ownerId and dd.ownertype = opr.fsltablecode where dd.ownerId = and dd.ownerType = and dd.accountNumber = and dd.cdr_recordId = update tbl_IMPORT_ProofOfDelivery set fseOperatorId = where eai_batchId = and ownerId = and ownerType = and fseCdrRecordId = and operator_cust_nbr = and fseOperatorId is null delete from tbl_TPM_ProofOfDelivery where ownerId = and ownerType = and 1 = 1 and contentId = and claimId = update tbl_TPM_Claims set pod = 'E' where ownerId = and ownerType = and claimId = select distinct mfr_item_code, item_description from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and coalesce( mfr_item_code, '' ) is not null and fseSkuId is null select distinct s.skuId, s.sku from tbl_PRD_Skus s with (nolock) inner join tbl_MDM_Aliases a with (nolock) on a.objectOwnerId = s.ownerId and a.objectOwnerType = s.fsl_tablecode and a.objectId = s.skuId and a.objectType = 'SKU' where s.ownerId = and s.fsl_tablecode = and a.sourceId = and a.sourceType = and a.masterDataType = 'SKU' and a.aliasCode = and a.aliasText = and a.normalizeMethod = 'NONE' select distinct s.skuId, s.sku from tbl_PRD_Skus s with (nolock) inner join tbl_MDM_Aliases a with (nolock) on a.objectOwnerId = s.ownerId and a.objectOwnerType = s.fsl_tablecode and a.objectId = s.skuId and a.objectType = 'SKU' where s.ownerId = and s.fsl_tablecode = and a.sourceId = and a.sourceType = and a.masterDataType = 'SKU' and a.aliasCode = and a.normalizeMethod = 'NONE' update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = where eai_batchId = and ownerId = and ownerType = and mfr_item_code = and coalesce( item_description, '' ) = and fseSkuId is null select a.aliasId from tbl_MDM_Aliases a with (nolock) where a.sourceId = and a.sourceType = and a.masterDataType = 'SKU' and a.objectType = 'SKU' and a.claimAction = 'REMOVE' and a.objectOwnerId = and a.objectOwnerType = and a.aliasCode = and a.aliasText = and a.normalizeMethod = 'NONE' update tbl_IMPORT_ProofOfDelivery set fseSkuId = 0, fseSku = 'REMOVE', fseSkuIdStatus = 'R' where eai_batchId = and ownerId = and ownerType = and mfr_item_code = and coalesce( item_description, '' ) = and fseSkuId is null update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = 'S' where eai_batchId = and ownerId = and ownerType = and mfr_item_code = and coalesce( item_description, '' ) = and fseSkuId is null select skuId, sku from tbl_PRD_Skus with (nolock) where ownerId = and fsl_tablecode = and sku like update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = 'L' where eai_batchId = and ownerId = and ownerType = and mfr_item_code = and coalesce( item_description, '' ) = and fseSkuId is null ---> update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = 'S' where eai_batchId = and ownerId = and ownerType = and mfr_item_code = and fseSkuId is null ---> select distinct gtin from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and coalesce( gtin, '' ) is not null and fseSkuId is null update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = 'G' where eai_batchId = and ownerId = and ownerType = and gtin = and fseSkuId is null select distinct upc from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and coalesce( upc, '' ) is not null and fseSkuId is null update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = where eai_batchId = and ownerId = and ownerType = and upc = and fseSkuId is null select distinct s.sku, s.skuId, 'u' matchType from tbl_PRD_SKus s inner join tbl_PRD_Packaging p on p.linkId = s.skuId and p.linkType = 'SKU' and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and p.upc in ( ) update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = where eai_batchId = and ownerId = and ownerType = and upc = and fseSkuId is null ---> select distinct distributor_item_code from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and coalesce( distributor_item_code, '' ) is not null and fseSkuId is null select distinct s.sku, cs.customerId, cs.customerType, s.skuId from tbl_PRD_SKus s inner join tbl_PRD_CustomerSkus cs on cs.skuId = s.skuId and cs.ownerId = s.ownerId and cs.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and cs.customerSKU = and cs.customerType in ( 'DST', 'PDST' ) update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = 'D' where eai_batchId = and ownerId = and ownerType = and distributor_item_code = and fseSkuId is null select distinct s.skuId, s.sku from tbl_PRD_SKus bs with (nolock) inner join tbl_PRD_CustomerSkus cs with (nolock) on cs.skuId = bs.skuId and cs.ownerId = bs.ownerId and cs.fsl_tablecode = bs.fsl_tablecode inner join tbl_PRD_Skus s on s.sku = bs.sku where bs.mfrId = and bs.fsl_tablecode = 'BRO' and cs.customerSKU = and cs.customerType in ( 'DST', 'PDST' ) and s.ownerId = and s.fsl_tablecode = update tbl_IMPORT_ProofOfDelivery set fseSkuId = , fseSku = , fseSkuIdStatus = 'd' where eai_batchId = and ownerId = and ownerType = and distributor_item_code = and fseSkuId is null update tbl_IMPORT_ProofOfDelivery set original_mfr_item_code = mfr_item_code where eai_batchId = and ownerId = and ownerType = and fseSkuId is not null and fseSku is not null and coalesce( mfr_item_code, '' ) <> fseSku update tbl_IMPORT_ProofOfDelivery set mfr_item_code = fseSku where eai_batchId = and ownerId = and ownerType = and fseSkuId is not null and fseSku is not null select distinct fseSkuId from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and fseSkuId > 0 and nullif( item_description, '' ) is null select skuDesc from tbl_PRD_Skus with (nolock) where ownerId = and fsl_tablecode = and skuId = update tbl_IMPORT_ProofOfDelivery set item_description = where eai_batchId = and ownerId = and ownerType = and fseSkuId = and nullif( item_description, '' ) is null select distinct distributor_item_code, distributor_name from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and coalesce( distributor_item_code, '' ) is not null and nullif( distributor_name, '' ) is not null and fseCdrRecordId is null select distinct cs.customerId, cs.customerType from tbl_PRD_SKus s inner join tbl_PRD_CustomerSkus cs on cs.skuId = s.skuId and cs.ownerId = s.ownerId and cs.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and cs.customerSKU = and cs.customerType in ( 'DST', 'PDST' ) select bcdr.cdr_recordId from tbl_CDR_Distributors pcdr inner join tbl_DST_Distributors bdst on bdst.dstParentCo = pcdr.fsl_dstId and bdst.dstParentCo > 0 inner join tbl_CDR_Distributors bcdr on bcdr.fsl_dstId = bdst.dstId and bcdr.ownerId = pcdr.ownerId and bcdr.fsl_tablecode =pcdr.fsl_tablecode where pcdr.ownerId = and pcdr.fsl_tablecode = and pcdr.cdr_recordid = and bdst.dstName like update tbl_IMPORT_ProofOfDelivery set fseCdrRecordId = where eai_batchId = and ownerId = and ownerType = and distributor_item_code = and distributor_name = and fseCdrRecordId is null update tbl_IMPORT_ProofOfDelivery set vendor_cost = case when quantity <> 0 then total_vendor_cost / quantity else 0 end where total_vendor_cost is not null and vendor_cost is null and quantity is not null and eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_ProofOfDelivery set contract_price = case when quantity <> 0 then total_contract_cost / quantity else 0 end where total_contract_cost is not null and contract_price is null and quantity is not null and eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_ProofOfDelivery set rebate_unit_price = case when quantity <> 0 then total_rebate_price / quantity else 0 end where total_rebate_price is not null and rebate_unit_price is null and quantity is not null and eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_ProofOfDelivery set incentive_percent = incentive_rate * 100.0 where incentive_percent is null and incentive_rate is not null and eai_batchId = and ownerId = and ownerType = select sales_month, sales_year from tbl_IMPORT_ProofOfDelivery where sales_month between 1 and 12 and sales_year between #Year(now())-1# and #Year(now())# and nullif( operator_inv_date, '' ) is null and eai_batchId = and ownerId = and ownerType = order by sales_year, sales_month update tbl_IMPORT_ProofOfDelivery set operator_inv_date = where nullif( operator_inv_date, '' ) is null and sales_year = and sales_month = and eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_ProofOFDelivery set contract_nbr = CASE CHARINDEX(' ', operator_name, 1) WHEN 0 THEN operator_name ELSE SUBSTRING(operator_name, 1, CHARINDEX(' ', operator_name, 1) - 1) END where eai_batchId = and fseClaimId = and ownerId = and ownerType = and nullif( contract_nbr, '' ) is null and nullif( operator_name, '' ) is not null select distinct contract_nbr, coalesce( operator_cust_nbr, '' ) operator_cust_nbr, coalesce( operator_name, '' ) operator_name, coalesce( operator_street, '' ) operator_street, coalesce( operator_city, '' ) operator_city, coalesce( operator_state, '' ) operator_state, coalesce( operator_zip_code, '' ) operator_zip_code from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and fseClaimId = and nullif( contract_nbr, '' ) is null select assignedContractNbr from tbl_TPM_Requests where requestId = and ownerId = and ownerType = update tbl_IMPORT_ProofOfDelivery set contract_nbr = where eai_batchId = and ownerId = and ownerType = and fseClaimId = and nullif( contract_nbr, '' ) is null and coalesce( operator_cust_nbr, '' ) = and coalesce( operator_name, '' ) = and coalesce( operator_street, '' ) = and coalesce( operator_city, '' ) = and coalesce( operator_state, '' ) = and coalesce( operator_zip_code, '' ) = select contract_nbr from tbl_IMPORT_ProofOfDelivery with (nolock) where eai_batchId = and ownerId = and ownerType = and contract_nbr like '%,%' update tbl_IMPORT_ProofOfDelivery set contract_nbr = where eai_batchId = and ownerId = and ownerType = and contract_nbr = select #_selectColumns# from tbl_IMPORT_ProofOfDelivery where eai_batchId = and ownerId = and ownerType = and fseClaimId = and coalesce( fseSkuIdStatus, '' ) != 'R' and contract_nbr = and ( nullif( mfr_item_code, '' ) is not null or nullif( item_description, '' ) is not null ) group by #_groupColumns# insert into tbl_TPM_ProofOfDelivery ( ownerId, ownerType, claimId, contentId, operatorCustomerNbr, operatorName, operatorStreet, operatorCity, operatorState, operatorZipCode, operatorInvoiceNbr, operatorInvoiceDate, contractNbr, itemCode, itemDescription, skuId, originalItemCode, itemQty, itemUOM, itemCost, contractPrice, rebateUnitPrice, totalRebate, operatorId, cdrRecordId, createDate, createUserId, modifyDate, modifyUserId, contractDescription, incentivePct, operatorAffiliateNbr, distributorCode, distributorName, claimPeriodStart, claimPeriodEnd ) values ( , , , , , , , , , , , , , , , , , , , , , , , , , , -1, , -1, , , , , , , ) update tbl_TPM_Claims set ediReady = 'R' where ownerId = and ownerType = and claimId = select distinct opr.operatorId, cdr.cdr_RecordId from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = pod.operatorId and opr.ownerId = pod.ownerId and opr.fsltablecode = pod.ownerType and nullif( opr.distrib1, 0 ) is null -- no primary distributor inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.cdrRecordId and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType and cdr.cdr_dstCompanyType = 'B' -- branch distributors only where pod.ownerId = and pod.ownerType = and pod.claimId = and not exists( select * from tbl_OPR_DistributionDetails dd with (nolock) where dd.ownerId = opr.ownerId and dd.ownerType = opr.fsltablecode and dd.operatorId = opr.operatorId and dd.cdr_recordId = cdr.cdr_recordId ) update tbl_OPR_CLientOperators set distrib1 = where operatorId = and ownerId = and fsltablecode = and nullif( distrib1, 0 ) is null insert into tbl_OPR_DistributionDetails ( ownerId, ownerType, operatorId, cdr_recordId ) values ( , , , ) select ownerId, ownerType, claimId, contractNbr, min( coalesce( operatorInvoiceDate, claimPeriodStart ) ) firstDate, max( coalesce( operatorInvoiceDate, claimPeriodEnd )) lastDate from tbl_TPM_ProofOfDelivery where claimId = and ownerId = and ownerType = ---> group by ownerId, ownerType, claimId, contractNbr select contractNbr, firstDate, lastDate from tbl_TPM_claimContracts where claimId = and ownerId = and ownerType = and contractNbr = insert into tbl_TPM_ClaimContracts( ownerId, ownerType, claimId, contractNbr, firstDate, lastDate, currencyCode, currencyConversionRate ) values ( , , , , , , , ) update tbl_TPM_claimContracts set firstDate = , lastDate = , currencyCode = , currencyConversionRate = , status = null, statusComment = null where claimId = and ownerId = and ownerType = and contractNbr = delete from tbl_TPM_ClaimContracts where claimId = and ownerId = and ownerType = and coalesce( contractNbr, '' ) <> select sum( totalRebate ) totalRebate from tbl_TPM_ProofOfDelivery where claimId = and ownerId = and ownerType = with operators as ( select distinct pod.operatorId from tbl_TPM_ProofOfDelivery pod with (nolock) where pod.ownerId = and pod.ownerType = and pod.claimId = and pod.operatorId > 0 ) update tbl_OPR_CLientOperators set crmActive = 'Y' where ownerId = and fsltablecode = and operatorId in ( select operatorId from operators ) and crmActive = 'N' update tbl_TPM_Claims set claimStatus = 'OPEN', claimAmount = case when claimAmount = 0 then else claimAmount end, verifiedAmount = null, updateDate = , updateUserId = -1 where claimId = and ownerId = and ownerType = update tbl_TPM_ClaimContracts set adjustmentAmount = 0.0 where claimId = and ownerId = and ownerType = and contractnbr = '$MISCELLANEOUS' #_autoProcessDebug# insert into tbl_IMPORT_Operators ( eai_batchId, eai_lineNumber, ownerId, ownerType , #_c# ) values ( , , , , ) update tbl_EAI_InboundBatches set loadedColumns = , lineCount = where batchId = --->