Finalizing...

select distinct e.fseClaimId, e.fseClaimStatus, e.ediSenderId, d.fsl_dstId from tbl_EDI_867 e inner join tbl_EDI_Senders s on s.senderId = e.ediSenderId and s.ownerId = e.ownerId and s.ownerType = e.ownerType inner join tbl_CDR_Distributors d on d.cdr_recordId = s.partnerId and 'CDR' = s.partnerType and d.ownerId = s.ownerId and d.fsl_tablecode = s.ownerType where e.ownerId = and e.ownerType = and e.eai_batchId = and e.fseClaimStatus in ( 'A', 'R' , 'U' ) and e.fseClaimId > 0 select contractNbr from tbl_TPM_ClaimContracts where ownerId = and ownerType = and claimId = update tbl_TPM_Claims set adminFee = where ownerId = and ownerType = and claimId = and adminFee is null update tbl_TPM_Claims set ediReady = 'Y' where ownerId = and ownerType = and claimId = and coalesce( ediReady, 'N' ) in ( 'N', 'Y' ) update tbl_TPM_Claims set ediReady = 'N' where ownerId = and ownerType = and claimId in ( ) select distinct claimDate, claimInvoice, claimRef, claimAmount, fseEPODBatchId ePODBatch from tbl_TPM_Claims c with (nolock) inner join tbl_EDI_867 e with (nolock) on e.ownerId = c.ownerId and e.ownerType = c.ownerType and e.fseClaimId = c.claimId where c.claimId in ( ) and c.ownerId = and c.ownerType = and e.eai_batchId =

The following claims are problematic, most likely the distributors could not be resolved. Please review the identified ePOD batch for specific issues. Once resolved, process this EDI file again

#qmd_batch.resultsText# select sender.partnerId, sender.partnertype, sender.enabled_867, sender.senderId from tbl_EDI_Senders sender with (nolock) where sender.ownerId = and sender.ownerType = and exists ( select * from tbl_EDI_867 data where data.ownerId = sender.ownerId and data.ownerType = sender.ownerType and data.ediSenderId = sender.senderId and data.eai_batchId = ) select top 1 ediSenderId from tbl_EDI_867 with (nolock) where ownerId = and ownerType = and eai_batchId = select distinct case when remitDuns is not null then remitDuns else distDuns end dunsId, case when remitDuns is not null then remitName else distName end dunsName, case when remitDuns is not null then 'remitDuns' else 'distDuns' end source select distinct #_dunsIdCol# dunsId, #_dunsNameCol# dunsName, '#_dunsIdCol#' source from tbl_EDI_867 where eai_batchId = and ownerId = and ownerType = select cdr.cdr_recordId from tbl_CDR_Distributors cdr inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId where cdr.ownerId = and cdr.fsl_tablecode = and cdr.fsl_mapPriority = 1 and dst.dunsId = update tbl_EDI_867 set fseClaimantPartnerId = , fseClaimantPartnerType = 'CDR' set fseCdrRecordId = where eai_batchId = and ownerId = and ownerType = and remitName = and remitDuns = and distName = and distDuns = select distinct remitName from tbl_EDI_867 where eai_batchId = and ownerId= and ownerType = and nullif( remitName, '' ) is not null select cdr.cdr_recordId from tbl_CDR_Distributors cdr inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId and dst.dstParentCo = where cdr.ownerId = and cdr.fsl_tablecode = and cdr.fsl_mapPriority = 1 and dst.dstBranchId = and dst.dstName = update tbl_EDI_867 set fseClaimantPartnerId = , fseClaimantPartnerType = 'CDR' where ownerId = and ownerType = and eai_batchId = and remitName = and fseClaimantPartnerId is null and fseClaimantPartnerType is null update tbl_EDI_867 set fseClaimantPartnerId = , fseClaimantPartnerType = where eai_batchId = and ownerId = and ownerType = and fseClaimantPartnerId is null and fseClaimantPartnerType is null update tbl_EDI_867 set billbackinvoiceNum = billbackrefId where eai_batchId = and ownerId = and ownerType = and nullif( billbackinvoicenum, '' ) is null and nullif( billbackrefId, '' ) is not null update tbl_EDI_867 set billbackrefId = billbackinvoiceNum where eai_batchId = and ownerId = and ownerType = and nullif( billbackinvoicenum, '' ) is not null and nullif( billbackrefId, '' ) is null update tbl_EDI_867 set submittedClaimTotalAmt = null where ownerId = and ownerType = and eai_batchId = and productTransferType = 'SUMMARY' select fseClaimantPartnerId, fseClaimantPartnerType, billbackInvoiceNum, billbackinvdate, billbackrefId, submittedClaimTotalAmt, productTransferType, sum( submittedClaimAmt ) fixedSubmittedClaimTotalAmt from tbl_EDI_867 where ownerId = and ownerType = and eai_batchId = and fseclaimantPartnerType is not null and fseclaimantPartnerId is not null and ( billbackinvoicenum is not null or billbackrefid is not null) and billbackinvdate is not null and submittedClaimTotalAmt is not null and productTransferType in ( 'STOCK_SALE', 'CONTRACT_SALE', 'RECEIVED_FROM_VENDOR' ) group by fseClaimantPartnerId, fseClaimantPartnerType, billbackInvoiceNum, billbackinvdate, billbackrefId, submittedClaimTotalAmt, productTransferType update tbl_EDI_867 set submittedClaimTotalAmt = where ownerId = and ownerType = and eai_batchId = and fseClaimantPartnerId = and fseClaimantPartnerType = and billbackinvoicenum = and submittedClaimTotalAmt = and billbackinvdate = and productTransferType = select distinct fseClaimantPartnerId, fseClaimantPartnerType, billbackInvoiceNum, billbackinvdate, billbackrefId, submittedClaimTotalAmt, productTransferType from tbl_EDI_867 where ownerId = and ownerType = and eai_batchId = and fseclaimantPartnerType is not null and fseclaimantPartnerId is not null and ( billbackinvoicenum is not null or billbackrefid is not null) and billbackinvdate is not null and submittedClaimTotalAmt is not null and productTransferType in ( 'STOCK_SALE', 'CONTRACT_SALE', 'RECEIVED_FROM_VENDOR', 'SUMMARY' ) select claimId, ediReady, edi from tbl_TPM_Claims where ownerId = and ownerType = and partnerType = and partnerId = and claimAmount = and claimInvoice = and claimDate = update tbl_EDI_867 set fseClaimId = , fseClaimStatus = where ownerId = and ownerType = and eai_batchId = and fseClaimantPartnerId = and fseClaimantPartnerType = and billbackinvoicenum = and submittedClaimTotalAmt = and billbackinvdate = select remitName, billbackinvdate, fseClaimId, fseClaimStatus, fseClaimantPartnerId, fseClaimantPartnerType, billbackinvoicenum, billbackRefId, submittedClaimTotalAmt, productTransferType, min( reportStartDate ) reportStartDate, max( reportEndDate ) reportEndDate from tbl_EDI_867 where ownerId = and ownerType = and eai_batchId = and fseClaimStatus in ( 'A', 'R' , 'U' ) group by remitName, billbackinvdate, fseClaimId, fseClaimStatus, fseClaimantPartnerId, fseClaimantPartnerType, billbackinvoicenum, billbackRefId, submittedClaimTotalAmt, productTransferType select distinct case when nullif( edi.agreementNumber, '' ) is not null then edi.agreementDescr else case when nullif( edi.contractNumberName, '' ) is not null then edi.contractNumberDescr else edi.programName end end agreementDesc, case when nullif( edi.agreementNumber, '' ) is not null then edi.agreementNumber else case when nullif( edi.contractNumberName, '' ) is not null then edi.contractNumberName else edi.programNumber end end agreementNumber from tbl_EDI_867 edi where ownerId = and ownerType = and eai_batchId = and fseClaimStatus = and fseClaimId = and coalesce( remitName, '' ) = and billbackinvdate = is null and fseClaimantPartnerId = and fseClaimantPartnerType = and coalesce( billbackInvoiceNum, '' ) = and submittedClaimTotalAmt is null = 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,ediReady ) values ( , -1, , -1, , , , , , , , , 'D', 'HOLD', , , , 'N''Y','Y', , , 'N' ) update tbl_EDI_867 set fseClaimId = where ownerId = and ownerType = and eai_batchId = and billbackInvoiceNum = and fseClaimantPartnerId = and fseClaimantPartnerType = and submittedClaimTotalAmt = and billbackinvdate = update tbl_TPM_Claims set claimDate = , claimAmount = , claimDescription = , updateDate = , updateUserId = -1, edi = 'Y' ,volumeBased = 'Y' , ediReady = 'N' where ownerId = and ownerType = and claimId = delete from tbl_TPM_ClaimContracts where ownerId = and ownerType = and claimId = delete from tbl_TPM_ProofOfDelivery where ownerId = and ownerType = and claimId = update tbl_TPM_Claims set edi = 'Y', ediReady = 'N' where ownerId = and ownerType = and claimId = ---> select edi.edi_lineNumber, edi.fseClaimId, case when nullif( edi.agreementNumber, '' ) is not null then edi.agreementDescr else case when nullif( edi.contractNumberName, '' ) is not null then edi.contractNumberDescr else edi.programName end end agreementDesc, case when nullif( edi.agreementNumber, '' ) is not null then edi.agreementDescr else case when nullif( edi.contractNumberName, '' ) is not null then edi.contractNumberDescr else edi.programName end end agreementNumber, case when nullif( edi.agreementNumber, '' ) is not null then edi.agreementNumber else case when nullif( edi.contractNumberName, '' ) is not null then edi.contractNumberName else edi.programNumber end end agreementNumber, case when cdr.cdr_recordId is not null then edi.distName when cdr2.cdr_recordId is not null then edi.remitName else edi.dstName end distName, case when cdr.cdr_recordId is not null then cdr.cdr_dstcode when cdr2.cdr_recordId is not null then cdr2.cdr_dstCode else '' end mfrDistCode, coalesce( edi.dstCode, '' ) distCode, edi.shipToName, edi.shipToAcctNum, edi.dstItemCode, edi.proddescript, edi.mfritemcode, edi.gtin, edi.cases, edi.lbs, coalesce( edi.allowanceAmt, case when edi.qtySold is not null then edi.submittedClaimAmt else edi.submittedClaimAmt * -1.0 end ) allowanceAmt, coalesce( edi.allowanceRate, edi.adjustedClaimAmt ) allowanceRate, case when edi.allowanceUOM = 'CA' and edi.detailType = 'PERCENTAGE' then coalesce( edi.cases, coalesce( edi.qtySold, edi.qtyReturned * -1.0 ) ) else coalesce( edi.allowanceQty, coalesce( edi.qtySold, edi.qtyReturned * -1.0 ) ) end allowanceQty, edi.allowanceUOM, edi.allowancePercent, coalesce( edi.qtySold, edi.qtyReturned * -1.0 ) quantity, edi.contractprice, edi.standardPrice, edi.customerinvoicenumber, edi.customerinvoicedate, edi.productTransferType, edi.detailType, edi.shiptoaddress, edi.shipTocity, edi.shipToState, edi.shipToZip from tbl_EDI_867 edi with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = edi.fseCdrRecordId and cdr.ownerId = edi.ownerId and cdr.fsl_tablecode = edi.ownerType left outer join tbl_CDR_Distributors cdr2 with (nolock) on cdr2.cdr_recordId = edi.fseClaimantPartnerId and cdr2.ownerId = edi.ownerId and cdr2.fsl_tablecode = edi.ownerType where edi.ownerId = and edi.ownerType = and edi.eai_batchId = and edi.fseClaimId > 0 and edi.fseClaimStatus in ( 'A', 'R' , 'U' ) and edi.productTransferType in ( 'STOCK_SALE', 'CONTRACT_SALE', 'RECEIVED_FROM_VENDOR' ) order by edi.fseClaimId, edi.edi_lineNumber update tbl_EDI_867 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, operator_cust_nbr, distributor_name, distributor_code, mfr_distributor_code, distributor_item_code, mfr_item_code, gtin, item_description, quantity, total_rebate_price, rebate_unit_price, incentive_percent, contract_price, vendor_cost, operator_inv_nbr, operator_inv_date, operator_street, operator_city, operator_state, operator_zip_code ) values ( , , , , , , , , , , , , , ,, , , , , , , , , , , , ) insert into tbl_IMPORT_ProofOfDelivery ( ownerId, ownertype, eai_batchId, eai_lineNumber, contract_description, contract_nbr, operator_name, operator_cust_nbr, distributor_name, distributor_code, mfr_distributor_code, distributor_item_code, mfr_item_code, gtin, item_description, quantity, total_rebate_price, rebate_unit_price, incentive_percent, contract_price, vendor_cost, operator_inv_nbr, operator_inv_date, operator_street, operator_city, operator_state, operator_zip_code ) values ( , , , , , , , , , , , , , ,, , , , , , , , , , , , ) -- order by edi.edi_lineNumber insert into tbl_TPM_ClaimContracts( ownerId, ownerType, claimId, contractNbr, firstDate, lastDate, currencyCode, currencyConversionRate ) values ( , , , , , , , ) ---> select distinct remitDuns, remitName, remitAddr, remitAddr2, remitCity, remitState, remitZip from tbl_EDI_867 where ( fseClaimantPartnerId is null or fseClaimantPartnerType is null) and eai_batchId = order by remitName select distinct distDuns, distName from tbl_EDI_867 with (nolock) where ( fseCdrRecordId is null ) and eai_batchId = and ownerId = and ownerType = order by distName select distinct cdr.cdr_dstName claimant, billbackinvoicenum, billbackinvdate, billbackrefid, submittedClaimTotalAmt, case when fseClaimStatus = 'A' then 'Added' when fseClaimStatus = 'U' then 'Reset''Update Skipped' when fseClaimStatus = 'X' then 'Skipped' when fseClaimStatus = 'R' then 'Reprocessed' else 'Unknown' end as ClaimStatus, fseClaimId from tbl_EDI_867 edi with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = edi.fseClaimantPartnerId and 'CDR' = edi.fseClaimantPartnerType where edi.ownerId = and edi.ownerType = and edi.eai_batchId = select distinct fseEPODBatchId from tbl_EDI_867 with (nolock) where ownerId = and ownerType = and eai_batchId = and fseEPODBatchId > 0