update tbl_IMPORT_RichsLegacyClaimData set fseRequestId = null, fseAssignedContractNbr = null, fseAgreementOwnerPartnerId = null, fseAgreementOwnerPartnerType = null, fsePaymentMethod = null, fsePayableTo = null, fseSkuId = null, fseSku = null, fseCdrRecordId = null, fseCdrDstName = null, fseCdrDstCode = null, fseClaimRef = null, fseClaimDate = null, fseClaimId = null, fseClaimInvoice = null, fseClaimStatus = null, fseEPodBatchId = null, fseParentCdrRecordId = null, fseInclude = 'Y' where eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_RichsLegacyClaimData set fseINclude = 'N' where ownerId = and ownerType = and eai_batchId = and program_number in ( ) outputResults(); #_resultsHTML# select * from qmd_claimStatusReasons where upper( reasonName ) = insert into tbl_TPM_ClaimStatusReasons ( ownerId, ownerType, reasonName, sortRank ) values( , , , 100 )

Processing #lsnumberformat( qmd_claimSource.recordCount )# possible claims

Limiting to a maximum of #lsnumberformat( _maxClaimCount )# new claims

select opr.mfrCustNum, cho.cho_name partnerName, cho.cho_address address1, cho.cho_city city, cho.cho_state state, cho.cho_zipCode zipcode from tbl_OPR_CLientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_OPR_CLientOperators mopr with (nolock) on mopr.ownerId = and mopr.fsltablecode = and mopr.fsl_choId = opr.ownerId where opr.mfrCustNum = 'ANONYMOUS' + convert( varchar, opr.ownerId ) and opr.fsltablecode = 'CHO' and mopr.operatorId = and 'OPR' = insert into tbl_TPM_Claims ( createDate, createUserId, updateDate, updateUserId, ownerId, ownerType, payeeId, partnerId, partnerType, claimInvoice, claimDate, claimRef, claimType, claimStatus, statusReasonId, claimAmount, claimDescription, volumeBased,edi,claimPeriodStart,claimPeriodEnd,historical ) values ( , -1, , -1, , , , , , , , , , , 'D', 'HOLD', , , , 'Y','N', , , 'Y' ) update tbl_IMPORT_RichsLegacyClaimData set fseClaimId = , fseClaimStatus = where eai_batchId = and ownerId = and ownerType = and fseAgreementOwnerPartnerId = and fseAgreementOwnerPartnerType = and fseAssignedContractNbr = and fseClaimDate = and fseClaimRef = and fseClaimInvoice = and fsePayableTo = and fsePaymentMethod = and claim_period_start = and claim_period_end = and fseParentCdrRecordId = and fseAgreementOwnerPartnerId = select i.fseSku mfr_item_code, i.mfr_sku_description item_description, i.operator_name, i.operator_address operator_street, i.operator_city, i.operator_state, i.operator_zip operator_zip_code, i.program_name contract_description, i.fseAssignedContractNbr contract_nbr, i.fseCdrDstCode mfr_distributor_code, i.fseCdrDstName distributor_name, sum( i.qty ) as quantity, sum( convert( decimal, i.rebate_dollars ) ) as total_rebate_price, i.contract_rate rebate_unit_price from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseClaimId = group by i.fseSku, i.mfr_sku_description, i.operator_name, i.operator_address, i.operator_city, i.operator_state, i.operator_zip, i.program_name, i.fseAssignedContractNbr, i.fseCdrDstCode, i.fseCdrDstName, i.contract_rate

Anonymous Operator: Using #htmleditformat( qmd_anonymousOperator.partnerName )# for #htmleditformat( _fseClaimRef )# #htmleditformat( _fseClaimInvoice )#

update tbl_IMPORT_RichsLegacyClaimData set fseEPodBatchId = where ownerId = and ownerType = and eai_batchId = and fseClaimId = insert into tbl_IMPORT_ProofOfDelivery ( ownerId, ownertype, eai_batchId, eai_lineNumber, contract_description, contract_nbr, operator_name, distributor_name, mfr_distributor_code, mfr_item_code, item_description, quantity, total_rebate_price, rebate_unit_price, operator_street, operator_city, operator_state, operator_zip_code ) values ( , ,, , , , , , , , , ,,, , , , ) update tbl_EAI_InboundBatches set lineCount = , batchTime = , statusTime = , loadedTime = where batchId = and ownerId = and ownerType = update tbl_IMPORT_RichsLegacyClaimData set fseClaimDate = dateadd( week, 1, claim_period_end ), fseClaimRef = 'PRG-' + fseAssignedContractNbr, fseClaimInvoice = 'HST-' + convert( varchar, fseParentCdrRecordId ) + '-' + format( claim_period_end, 'MMddyyyy' ) where eai_batchId = and ownerId = and ownerType = and fseAgreementOwnerPartnerType = 'OPR' and ( fsePaymentMethod = 'D' and fsePayableTo = 'CDR' and fseParentCdrRecordId > 0 ) update tbl_IMPORT_RichsLegacyClaimData set fseClaimDate = dateadd( week, 1, claim_period_end ), fseClaimRef = 'PRG-' + fseAssignedContractNbr, fseClaimInvoice = 'HST-' + convert( varchar, fseAgreementOwnerPartnerId ) + '-' + format( claim_period_end, 'MMddyyyy' ) where eai_batchId = and ownerId = and ownerType = and fseAgreementOwnerPartnerType = 'OPR' and ( fsePaymentMethod = 'BB' and fsePayableTo = 'OPR' ) select fsePaymentMethod, fsePayableTo, fseAgreementOwnerPartnerId, fseAgreementOwnerPartnerType, fseAssignedContractNbr, fseClaimDate, fseClaimInvoice, fseClaimRef, fseParentCdrRecordId, claim_period_start, claim_period_end, round( sum( rebate_dollars ), 2 ) fseClaimAmount, fseClaimId from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseAgreementOwnerPartnerType = 'OPR' and i.fseInclude = 'Y' and 1 = 2 and ( i.fsePaymentMethod = 'D' and i.fsePayableTo = 'CDR' and i.fseParentCdrRecordId > 0 ) and not exists ( select * from tbl_IMPORT_RichsLegacyClaimData i2 with (nolock) where i2.ownerId = i.ownerId and i2.ownerType = i.ownerType and i2.eai_batchId = i.eai_batchId and i2.fseAgreementOwnerPartnerId = i.fseAgreementOwnerPartnerId and i2.fseAgreementOwnerPartnerType = i.fseAgreementOwnerPartnerType and i2.fseAssignedContractNbr = i.fseAssignedContractNbr and i2.fseClaimDate = i.fseClaimDate and i2.fseClaimRef = i.fseClaimRef and i2.fseClaimInvoice = i.fseClaimInvoice and i2.fseParentCdrRecordId = i.fseParentCdrRecordId and i2.claim_period_start = i.claim_period_start and i2.claim_period_end = i.claim_period_end and i2.fseSkuId is null ) and not exists ( select * from tbl_IMPORT_RichsLegacyClaimData i2 with (nolock) where i2.ownerId = i.ownerId and i2.ownerType = i.ownerType and i2.eai_batchId = i.eai_batchId and i2.fseAgreementOwnerPartnerId = i.fseAgreementOwnerPartnerId and i2.fseAgreementOwnerPartnerType = i.fseAgreementOwnerPartnerType and i2.fseAssignedContractNbr = i.fseAssignedContractNbr and i2.fseClaimDate = i.fseClaimDate and i2.fseClaimRef = i.fseClaimRef and i2.fseClaimInvoice = i.fseClaimInvoice and i2.claim_period_start = i.claim_period_start and i2.claim_period_end = i.claim_period_end and i2.fseParentCdrRecordId is null ) group by fsePaymentMethod, fsePayableTo, fseAgreementOwnerPartnerId, fseAgreementOwnerPartnerType, fseAssignedContractNbr, fseClaimDate, fseClaimInvoice, fseClaimRef, fseParentCdrRecordId, claim_period_start, claim_period_end, fseClaimId union all select fsePaymentMethod, fsePayableTo, fseAgreementOwnerPartnerId, fseAgreementOwnerPartnerType, fseAssignedContractNbr, fseClaimDate, fseClaimInvoice, fseClaimRef, 0 fseParentCdrRecordId, claim_period_start, claim_period_end, round( sum( rebate_dollars ), 2 ) fseClaimAmount, fseClaimId from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseAgreementOwnerPartnerType = 'OPR' and i.fseInclude = 'Y' and 1 = 2 and ( i.fsePaymentMethod = 'BB' and i.fsePayableTo = 'OPR' ) and not exists ( select * from tbl_IMPORT_RichsLegacyClaimData i2 with (nolock) where i2.ownerId = i.ownerId and i2.ownerType = i.ownerType and i2.eai_batchId = i.eai_batchId and i2.fseAgreementOwnerPartnerId = i.fseAgreementOwnerPartnerId and i2.fseAgreementOwnerPartnerType = i.fseAgreementOwnerPartnerType and i2.fseAssignedContractNbr = i.fseAssignedContractNbr and i2.fseClaimDate = i.fseClaimDate and i2.fseClaimRef = i.fseClaimRef and i2.fseClaimInvoice = i.fseClaimInvoice and i2.claim_period_start = i.claim_period_start and i2.claim_period_end = i.claim_period_end and i2.fseSkuId is null ) group by fsePaymentMethod, fsePayableTo, fseAgreementOwnerPartnerId, fseAgreementOwnerPartnerType, fseAssignedContractNbr, fseClaimDate, fseClaimInvoice, fseClaimRef, claim_period_start, claim_period_end, fseClaimId order by fseClaimDate select claimId from tbl_TPM_Claims c with (nolock) where c.ownerId = and c.ownerType = and c.claimDate = and c.claimRef = and c.claimInvoice = and c.claimAmount = and c.partnerId = and c.partnerType = 'CDR' and c.partnerId = and c.partnerType = 'OPR' select distinct i.claim_period_start, i.claim_period_end, i.program_number from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.ownerId = and i.ownerType = and i.eai_batchId = select operatorId, companyName, fsl_choId from tbl_OPR_CLientOperators with (nolock) where fsl_choId in ( 15,20,87,88,94,106,141,155,166,251,292,353,358,360,490,499,598,601,610,612,676,688,693,704,718,719,725,813,1260,230414 ) and ownerId= and fsltablecode = order by companyName select r.requestId, ct.assignedContractNbr, ct.partnerType, ct.partnerId, r.payableTo, r.paymentMethod from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts ct with(nolock) on ct.ownerId = r.ownerId and ct.ownertype = r.ownerType and ct.contractId = r.contractId where r.ownerId = and r.ownerType = and ct.assignedContractNbr = and between r.startDate and r.endDate and r.status = 'APPROVED' select r.requestId, ct.assignedContractNbr, ct.partnerType, ct.partnerId, r.payableTo, r.paymentMethod from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts ct with(nolock) on ct.ownerId = r.ownerId and ct.ownertype = r.ownerType and ct.contractId = r.contractId where r.ownerId = and r.ownerType = and ct.assignedContractNbr = and r.status = 'APPROVED' update tbl_IMPORT_RichsLegacyClaimData set fseRequestId =, fseAssignedContractNbr = , fseAgreementOwnerPartnerId = , fseAgreementOwnerPartnerType = , fsePaymentMethod = , fsePayableTo = , fseInclude = 'N' where ownerId = and ownerType = and eai_batchId = and claim_period_start = and claim_period_end = and program_number = select distinct mfr_sku from tbl_IMPORT_RichsLegacyClaimData with (nolock) where eai_batchId = and ownerId = and ownerType = and coalesce( mfr_sku, '' ) is not null and fseInclude = 'Y' update tbl_IMPORT_RichsLegacyClaimData set fseSkuId = , fseSku = where eai_batchId = and ownerId = and ownerType = and mfr_sku = select distinct distributor_1fs_id from tbl_IMPORT_RichsLegacyClaimData with (nolock) where eai_batchId = and ownerId = and ownerType = and distributor_1fs_id > 0 and fseInclude = 'Y' select cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstCode from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.fsl_dstId = and cdr.cdr_dstCompanyType = 'B' and cdr.fsl_mapPriority = 1 update tbl_IMPORT_RichsLegacyClaimData set fseCdrRecordId = , fseCdrDstName = , fseCdrDstCode = where eai_batchId = and ownerId = and ownerType = and distributor_1fs_id = select distinct Distributor_Branch_ID from tbl_IMPORT_RichsLegacyClaimData with (nolock) where eai_batchId = and ownerId = and ownerType = and nullif( Distributor_Branch_ID, '' ) is not null and fseCdrRecordId is null and fseInclude = 'Y' select distinct cdr.fsl_dstId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_CDR_DstCodes cdrCd with (nolock) on cdrcd.ownerId= cdr.ownerId and cdrcd.ownerType = cdr.fsl_tablecode and cdrcd.cdr_recordId = cdr.cdr_recordId where cdr.ownerId = and cdr.fsl_tablecode = and cdr.fsl_dstId > 0 and cdrcd.cdr_dstCode = select cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstCode from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.fsl_dstId = and cdr.cdr_dstCompanyType = 'B' and cdr.fsl_mapPriority = 1 update tbl_IMPORT_RichsLegacyClaimData set fseCdrRecordId = , fseCdrDstName = , fseCdrDstCode = where eai_batchId = and ownerId = and ownerType = and Distributor_Branch_ID = and fseCdrRecordId is null update i set i.fseParentCdrRecordId = pcdr.cdr_recordId from tbl_IMPORT_RichsLegacyClaimData i inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = i.fseCdrRecordId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId inner join tbl_CDR_Distributors pcdr on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = i.ownerId and pcdr.fsl_tablecode = i.ownerType and pcdr.fsl_mapPriority = 1 and pcdr.cdr_dstCompanyType = 'P' where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseParentCdrRecordId is null update i set i.fseParentCdrRecordId = cdr.cdr_recordId from tbl_IMPORT_RichsLegacyClaimData i inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = i.fseCdrRecordId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId and nullif( dst.dstParentCo, 0 ) is null where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseParentCdrRecordId is null select distributor_1fs_id, distributor_branch_id, distributor_branch_name, count(*) lines, min( eai_lineNumber ) firstLine, round( sum( qty ), 0 ) volumeQty from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.ownerId = and i.ownerType = and i.eai_batchId = and fseParentCdrRecordId is null and i.fseInclude = 'Y' and i.distributor_branch_id not in ( 'Need Client Input' ) group by distributor_1fs_id, distributor_branch_id, distributor_branch_name order by volumeQty desc with allocationSummary as ( select sum( i.qty ) totalCases, sum( case when i.fseRequestId is null then 0 when i.fseClaimId > 0 then i.qty else 0 end ) allocated, sum( case when i.fseRequestId is not null and i.fseClaimId > 0 then 0 else i.qty end ) unallocated from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseInclude = 'Y' ) select *, round(( allocated / totalCases ) * 100.0, 2 ) as percentAllocated from allocationSummary select 'Added' status, cdr.cdr_dstName claimantName, count( distinct fseClaimId ) claims from tbl_IMPORT_RichsLegacyClaimData i with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = i.fseparentCdrRecordID and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseClaimId > 0 and i.fseClaimStatus = 'A' and i.fsePayableTo = 'CDR' group by cdr.cdr_dstName union all select 'Added' status, opr.companyName claimantName, count( distinct fseClaimId ) claims from tbl_IMPORT_RichsLegacyClaimData i with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = i.fseAgreementOwnerPartnerId and opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseClaimId > 0 and i.fseClaimStatus = 'A' and i.fsePayableTo = 'OPR' group by opr.companyName union all select 'Exists' status, cdr.cdr_dstName claimantName, count( distinct fseClaimId ) claims from tbl_IMPORT_RichsLegacyClaimData i with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = i.fseparentCdrRecordID and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseClaimId > 0 and i.fseClaimStatus = 'U' and i.fsePayableTo = 'CDR' group by cdr.cdr_dstName union all select 'Exists' status, opr.companyName claimantName, count( distinct fseClaimId ) claims from tbl_IMPORT_RichsLegacyClaimData i with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = i.fseAgreementOwnerPartnerId and opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseClaimId > 0 and i.fseClaimStatus = 'U' and i.fsePayableTo = 'OPR' group by opr.companyName order by status, claims desc select opr.companyName beneficiaryName, i.fseAssignedContractNbr agreementNbr, i.fsePayableTo payableTo, count( distinct fseClaimRef + fseClaimInvoice ) claims, sum( qty ) cases from tbl_IMPORT_RichsLegacyClaimData i with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = i.fseAgreementOwnerPartnerId and opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseClaimStatus = 'X' and i.fseRequestId > 0 group by opr.companyName , i.fseAssignedContractNbr, i.fsePayableTo order by payableTo, cases desc, beneficiaryName select mfr_sku, mfr_sku_description, count(*) lines, min( eai_lineNumber ) firstLine from tbl_IMPORT_RichsLegacyClaimData i where i.ownerId = and i.ownerType = and i.eai_batchId = and fseSkuId is null and nullif( mfr_sku, '' ) is not null and i.fseInclude = 'Y' group by mfr_sku, mfr_sku_description order by lines desc select i.primary_customer, i.program_number, i.program_name, min( claim_period_start ) STARTDATE, max( i.claim_period_end ) ENDDATE, count(*) lines, min( eai_lineNumber ) firstLine, round( sum( qty ), 0 ) volumeQty from tbl_IMPORT_RichsLegacyClaimData i with (nolock) where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseRequestId is null and i.fseInclude = 'Y' group by i.primary_customer, i.program_number, i.program_name -- , i.claim_period_start, i.claim_period_end order by volumeQty desc select primary_customer, program_number, program_name, claim_period_start, claim_period_end, sum( qty ) cases from tbl_IMPORT_RichsLegacyClaimData with (nolock) where eai_batchId = and fseInclude = 'N' and ownerId = and ownerType = group by primary_customer, program_number, program_name, claim_period_start, claim_period_end order by primary_customer, program_name, claim_period_end