Updating Claim Details

truncate table tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix# -- this is in lieu of checking the volumeGenerator field because it needs to match the data that was capture, not specifically the state of the current volume generator field with generators as ( select mfr_id as ownerId, 'MFR' as ownerType, rebateContractId as requestId from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where forecast_status = 'C' and mfr_id = and rebateContractId > 0 and tPartnerType = 'OPR' union all select ownerId, ownerType, requestId from tbl_TPM_Requests with (nolock) where ownerId = and ownerType = and volumeGenerator = 'Y' ) select distinct ownerId, ownerType, requestId into ###_generatorsTable# from generators; create index idx_#_generatorsTable# on ###_generatorsTable#(ownerType,ownerId,requestId); insert into tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix# ( ownerType, ownerId, cho_id, cho_name, assignedContractNbr, contractName, claimInvoice, claimDate, periodStart, periodEnd, claimant, skuId, sku, skuDesc, itemQty, locationName, contractId ) select ct.ownerType, ct.ownerId, coalesce( cho.cho_id, 0 ) cho_id, case when cho.cho_id is not null then cho.cho_name else 'Unmapped Operator' end cho_name, ct.assignedContractNbr, ct.contractName, c.claimInvoice, c.claimDate, -- coalesce( cc.firstDate, c.claimPeriodStart ) periodStart, case when cc.lastDate is not null then cc.firstDate when c.claimPeriodEnd is not null then c.claimPeriodStart else dateadd( month, -1, format( c.claimDate, 'MM/01/yyyy' )) end as periodStart, -- coalesce( cc.lastDate, c.claimPeriodEnd ) periodEnd, case when cc.lastDate is not null then cc.lastDate when c.claimPeriodEnd is not null then c.claimPeriodEnd else dateadd( day, -1, format( c.claimDate, 'MM/01/yyyy' )) end as periodEnd, -- oprc.companyName as claimant, case when c.partnerType = 'OPR' then oprc.companyName else cdrc.cdr_dstName end as claimant, s.skuId, s.sku, s.skuDesc, sum( pod.#_itemQtyCol# ) itemQty, coalesce( opr.companyName, 'Unknown Location' ) as locationName, ct.contractId from tbl_TPM_Claims c with (nolock) left outer join tbl_OPR_ClientOperators oprc with (nolock) on oprc.ownerId = c.ownerId and oprc.fsltablecode = c.ownerType and oprc.operatorId = c.partnerId and 'OPR' = c.partnerType left outer join tbl_CDR_Distributors cdrc with (nolock) on cdrc.ownerId = c.ownerId and cdrc.fsl_tablecode = c.ownerType and cdrc.cdr_recordId = c.partnerId and 'CDR' = c.partnerType inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.reconciled = 'Y' and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimId inner join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType inner join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId= r.ownerId and ct.ownerType = r.ownerType inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr and pod.claimId = cc.claimId and pod.status != 'DENIED' left outer join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = pod.ownerId and opr.fsltablecode = pod.ownerType and opr.operatorId = pod.operatorId left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_PRD_Skus s with (nolock) on s.skuId = pod.skuId and s.ownerId = pod.ownerId and s.fsl_tablecode = pod.ownertype inner join ###_generatorsTable# as g with (nolock) on g.ownerId = r.ownerId and g.ownerType = r.ownerType and g.requestId = r.requestId where c.ownerId = and c.ownerType = -- and r.requestId in ( select rebateContractId from ###_generatorsTable# ) -- and r.volumeGenerator = 'Y' group by ct.ownerType, ct.ownerId, ct.assignedContractNbr, ct.contractName, c.claimInvoice, c.claimDate, cc.firstDate, c.claimPeriodStart, cc.lastDate, c.claimPeriodEnd, oprc.companyName, cho.cho_id, cho.cho_name, s.skuId, s.sku, s.skuDesc, opr.companyName, ct.contractId, c.partnerType, cdrc.cdr_dstName if object_id( 'tempdb.dbo.###_generatorsTable#', 'U' ) is not null drop table ###_generatorsTable#; select count(*) detailRecords from tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix#

#lsnumberformat( qmd_summary.detailRecords )# Detail Records

delete from tbl_TPM_OperatorClaimMappings where ownerId = and ownertype = with allMappings as ( select c.ownerType, c.ownerId, c.partnerType, c.partnerId, pod.operatorId, pod.operatorName, pod.operatorStreet, pod.operatorCity, pod.operatorState, pod.operatorZipCode, count(*) over ( partition by c.partnerType, c.partnerId, pod.operatorName, pod.operatorStreet, pod.operatorCity, pod.operatorState, pod.operatorZipCode, pod.operatorId ) usageCount from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.claimId = pod.claimId and c.ownerId = pod.ownerId and c.ownerType = pod.ownerType where pod.operatorId > 0 and nullif( pod.operatorName, '' ) is not null and nullif( pod.operatorStreet, '' ) is not null and c.ownerId = and c.ownertype = ), rankedMappings as ( select *, row_number() over ( partition by partnerType, partnerId, operatorName, operatorStreet, operatorCity, operatorState, operatorZipcode order by usageCount desc ) ranking from allMappings with (nolock) ) insert into tbl_TPM_OperatorClaimMappings( ownerType, ownerId, partnerType, partnerId, operatorName, operatorStreet, operatorCity, operatorState, operatorZipCode, operatorId ) select ownerType, ownerId, partnerType, partnerId, coalesce( operatorName, '' ) operatorName, coalesce( operatorStreet, '' ) operatorStreet, coalesce( operatorCity, '' ) operatorCity, coalesce( operatorState, '' ) operatorState, coalesce( operatorZipCode, '' ) operatorZipCode, operatorId from rankedMappings with (nolock) where ranking = 1 order by ownerType, ownerId, partnerType, partnerId, operatorName, operatorStreet, operatorCity, operatorState, operatorZipCode select count(*) mappingCount from tbl_TPM_OperatorClaimMappings with (nolock) where ownerId = and ownertype =

#lsnumberformat( qmd_mappingSummary.mappingCount )# Operator Mappings

insert into tbl_EAI_inboundBatchMessages( batchId, lineNumber, importId, message, messageLevel ) values ( , null, null, , 'INFO' )

#htmleditformat( _timingMessage )#