select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat like '%' + and batchTime < and status = 'OK' order by batchTime desc delete from tbl_EXPORT_TPM_Billback where ownerId = and ownertype = and eai_batchId = select quotedLines, stagingTable from tbl_EAI_InboundFileFormats with (nolock) where ownerId in (0, ) and ownerType in ('*', ) and formatName like '%' + select billBackFileOperatorId from tbl_TPM_Settings with (nolock) where ownerId = and ownerType = and repaymentRemittanceText = 'TPM System' drop table if exists [#_tempTable#]; with export as ( select pp.payerCode as vendorId, pp.payableTo as vendorName, CASE when ct.externalProgramNumber is null then SUBSTRING(ct.assignedContractNbr, PATINDEX('%[0-9]%', ct.assignedContractNbr), PATINDEX('%[^0-9]%', SUBSTRING(ct.assignedContractNbr, PATINDEX('%[0-9]%', ct.assignedContractNbr), 8000) + 'X') - 1 ) else SUBSTRING(ct.externalProgramNumber, PATINDEX('%[0-9]%', ct.externalProgramNumber), PATINDEX('%[^0-9]%', SUBSTRING(ct.externalProgramNumber, PATINDEX('%[0-9]%', ct.externalProgramNumber), 8000) + 'X') - 1 ) end as program_num, c.claimInvoice as customer_reference, c.claimId, 'O' as payee_type, coalesce(d.cdr_dstCode,'') as distributor_id, pod.distributorName, coalesce(r.externalCustomerOperatorNumber, '') coalesce(r.externalTPMECustomerNumber, '') as operator_id, 'P' as closing_method, CONVERT(char(8), c.acknowledgedDate, 112) AS billback_date, CONVERT(char(8), c.claimPeriodStart, 112) AS bb_start_date, CONVERT(char(8), c.claimPeriodEnd, 112) AS bb_end_date, s.sku as item_number , case when pod.itemUOM = 'CASE' then ROUND(pod.itemQtyCs * -1.0, 2) else ROUND(pod.itemQtyLbs * -1.0, 2) end as item_volume_qty, ROUND(pod.totalRebate * -1.0, 2) AS item_dollar_amount, coalesce(d.cdr_dstCode,'') as assoc_distributor_id , case when pod.itemUOM = 'CASE' then 'CS' else 'LB' end as uom, 'O' as trade_indicator, ct.externalProgramComponent as component, '' as lump_sum_indicator, '' as file_name from tbl_TPM_ClaimContracts cc with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = cc.ownerId and r.ownerType = cc.ownerType and r.requestId = cc.requestId --and r.volumeGenerator = 'Y' inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.claimId = cc.claimId and pod.contractNbr = cc.contractNbr inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.ownerId = cc.ownerId and c.ownerType = cc.ownerType inner join tbl_TPM_PartnerPayables pp with (nolock) on pp.ownerId = r.ownerId and pp.ownerType = r.ownerType and pp.partnerType = 'OPR' and pp.partnerId = c.partnerId inner join tbl_PRD_Skus s with (nolock) on s.skuId = pod.skuId and s.ownerId = r.ownerId and s.fsl_tableCode = r.ownerType inner join tbl_CDR_Distributors d with (nolock) on d.cdr_recordId = pod.cdrRecordId and d.ownerId = pod.ownerId and d.fsl_tableCode = pod.ownerType where ct.ownerId = and ct.ownerType = and c.claimId = and coalesce(pod.exceptionSuppressList,'') = '' and pod.flagComment is not null and (pod.flagComment like '%Excluded Operator%' or pod.flagComment like '%Already Claimed%' ) ) select #qmd_batch.ownerId# as ownerId, '#qmd_batch.ownerType#' as ownerType, #qmd_batch.batchId# as eai_batchId, row_number() over( order by vendorId, item_number ) as eai_lineNumber, 'Default' as source, vendorId as vendor_num, program_num, customer_reference, payee_type, distributor_id, operator_id, closing_method, billback_date, bb_start_date, bb_end_date, item_number, item_volume_qty, item_dollar_amount, assoc_distributor_id, uom, trade_indicator, lump_sum_indicator, file_name, component into [#_tempTable#] from export insert into [#_stagingTable#] ( ownerId, ownerType, eai_batchId, eai_lineNumber, source, vendor_num, program_num, customer_reference, payee_type, distributor_id, operator_id, closing_method, billback_date, bb_start_date, bb_end_date, item_number, item_volume_qty, item_dollar_amount, assoc_distributor_id, uom, trade_indicator, lump_sum_indicator, file_name, component) select ownerId, ownerType, eai_batchId, eai_lineNumber, source, vendor_num, program_num, customer_reference, payee_type, distributor_id, operator_id, closing_method, billback_date, bb_start_date, bb_end_date, item_number, item_volume_qty, item_dollar_amount, assoc_distributor_id, uom, trade_indicator, lump_sum_indicator, file_name, component from [#_tempTable#] order by vendor_num, item_number update tbl_EAI_InboundBatches set loadedColumns = 'source,vendor_num,program_num,customer_reference,payee_type,distributor_id,operator_id,closing_method, billback_date,bb_start_date,bb_end_date,item_number,item_volume_qty,item_dollar_amount,assoc_distributor_id,uom,trade_indicator,lump_sum_indicator,file_name,component' where ownerId = and ownertype = and batchId = update tbl_EAI_inboundBatches set fileName = where batchId = and ownerId = and ownerType = select concat(firstname , ' ', lastname) as username, email from tbl_FSPro_Members with (nolock) where fspro_userId = and ownerId = and fsl_tableCode = update tbl_TPM_CheckRequests set notificationSent = 'Y' where ownerId = and ownerType = and claimId =
Exported #lsnumberformat( _exportCount )# BillBacks updated on or after #lsdateFormat( _cutoffDate, "MM/dd/yyyy" )#.
Email Sent to : #htmleditformat( qmd_getUserDetails.email )#
Subject: #htmleditformat( _emailSubject )#
Attachments: #htmleditformat( _attachmentsList )#
 #_emailBody#