select formatId from tbl_EAI_inboundFileFormats with (nolock) where ownerId = and ownerType = and formatName = select formatId from tbl_EAI_inboundFileFormats with (nolock) where ownerId = 0 and ownerType = and formatName =

Outbound Clearing File Processor

select h.referenceNbr as clearing_ref, case when c.claimType = 'D' then 'DEDUCTION' when c.claimType = 'B' then 'CHECK_REQUEST' else c.claimType end as clearing_type, c.deductionRef deduction_id, h.historyId clearing_id, h.reconcilationDate clearing_date, h.generalLedgerCode expense_category_id, coalesce( s.sku, 'NA' ) sku, coalesce( s.skuDesc, '' ) sku_description, h.reconciliationAmount expense_dollars, h.reconciliationCases cases, h.altReconciliationAmount alt_expense_dollars, h.altReconciliationCurrency alt_expense_currency, coalesce( cdr.cdr_dstCode, '' ) distributor_code, coalesce( cdr.cdr_dstName, 'NA' ) distributor_name from tbl_TPM_ReconciliationHistory h with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = h.ownerId and cdr.fsl_tablecode = h.ownerType and cdr.cdr_recordId = h.cdrRecordId inner join tbl_TPM_CLaims c with (nolock) on c.claimId = h.claimId and c.ownerId= h.ownerId and c.ownerType = h.ownerType left outer join tbl_PRD_Skus s with (nolock) on s.skuId = h.skuId and s.ownerId = h.ownerId and s.fsl_tablecode = h.ownerType where ( ( c.claimType = 'D' and c.deductionRef is not null ) or ( c.claimType = 'B' ) ) and c.claimType = 'D' and c.ownerId = and c.ownerType = and h.dateSent is null and h.reconcilationDate >= and h.reconcilationDate <= and h.dateSent is not null order by clearing_ref, clearing_id

Resending (#lsdateformat( _config.OPT_RESENDSTART, "MM/DD/YYYY" )# - #lsdateformat( _config.OPT_RESENDEND, "MM/DD/YYYY" )#)

#lsnumberformat( qmd_clearingToSend.recordCount )# Clearing Records to Send.

 update tbl_EAI_inboundBatches set fileName = where batchId = select ftpDirectory, ftpPassword, ftpServer, ftpUserName from tbl_EAI_InboundFTPSources where ( ftpDirectory = 'fse' or ( outbound = 'Y' and connectionName like '%CLEARING%')) and ownerId = and ownerType =

#htmleditformat( listLast( _fileName, "\" ))# NOT sent to ftp:#htmleditformat( qmd_ftpSources.ftpUserName )#@#htmleditformat( qmd_ftpSources.ftpServer )#/#htmleditformat( qmd_ftpSources.ftpDirectory )#

No FTP Source - file not transfered

 update tbl_TPM_ReconciliationHistory set dateSent = where historyId = and ownerId = and ownerType = select c.partnerId, c.partnerType, c.deductionRef deduction_id, h.historyId clearing_id, h.reconcilationDate clearing_date, h.generalLedgerCode expense_category_id, sum( h.reconciliationAmount ) expense_dollars from tbl_TPM_ReconciliationHistory h with (nolock) inner join tbl_TPM_CLaims c with (nolock) on c.claimId = h.claimId and c.ownerId = h.ownerId and c.ownerType = h.ownerType inner join tbl_PRD_Skus s with (nolock) on s.skuId = h.skuId and s.ownerId = h.ownerId and s.fsl_tablecode = h.ownerType where c.claimType = 'D' and c.deductionRef is not null and c.ownerId = and c.ownerType = and h.historyId IN ( ) and 1 = 2 and h.dateSent is not null group by c.partnerId, c.partnerType, c.deductionRef, h.historyId, h.reconcilationDate, h.generalLedgerCode order by clearing_id select ter.internalSalesId, ter.name territoryName, pter.internalSalesId parentInternalSalesId, pter.name parentTerritoryName, lvl.brokerLevel localMarket from tbl_TER_Territories ter inner join tbl_TER_TerritoryLevels lvl on lvl.levelId = ter.levelId and lvl.ownerId = ter.ownerId and lvl.fsl_tablecode = ter.fsl_tablecode left outer join tbl_TER_Territories pter on pter.territoryId = ter.parentTerritoryId and pter.ownerId = ter.ownerId and pter.fsl_tablecode = ter.fsl_tablecode where ter.ownerId = and ter.fsl_tablecode = and ter.territoryId =

Cleared Deduction Summary Attached

Cleared Deduction Summary (#htmleditformat( listlast( _summaryAttachmentFilename, "\" ))#) sent to #htmleditformat( _to )#