---> with partners as ( select distinct c.ownerId, c.ownerType, c.partnerId, c.partnerType, 'DISTRIBUTOR' accountType, cdr.cdr_dstName as accountName, cdr.cdr_dstCode as accountNumber from tbl_TPM_Contracts c with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = c.partnerId and 'CDR' = c.partnerType and cdr.ownerId = c.ownerId and cdr.fsl_tablecode = c.ownerType where c.ownerId = and c.ownerType = union all select distinct c.ownerId, c.ownerType, c.partnerId, c.partnerType, 'OPERATOR' accountType, opr.companyName as accountName, case when opr.directCustomer = 'Y' then opr.directCustomerNbr else opr.mfrCustNum end as accountNumber from tbl_TPM_Contracts c with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = c.partnerId and 'OPR' = c.partnerType and opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType where c.ownerId = and c.ownerType = ), productCatalogRaw as ( select p.ownerId, p.ownerType, p.catalogType, c.skuId as catalogId, c.sku as code, c.skuDesc as description from tbl_PRD_Skus c with (nolock) inner join tbl_TPM_RequestProducts p with (nolock) on p.ownerId = c.ownerId and p.ownerType = c.fsl_tablecode and p.catalogId = c.skuId where p.catalogType = 'SKU' and p.ownerId = and p.ownerType = union all select p.ownerId, p.ownerType, p.catalogType, c.prodId as catalogId, c.product as code, c.shortDesc as description from tbl_PRD_Products c with (nolock) inner join tbl_TPM_RequestProducts p with (nolock) on p.ownerId = c.ownerId and p.ownerType = c.fsl_tablecode and p.catalogId = c.prodId where p.catalogType = 'PRD' and p.ownerId = and p.ownerType = union all select p.ownerId, p.ownerType, p.catalogType, c.categoryId as catalogId, c.catNum as code, c.category as description from tbl_PRD_Categories c with (nolock) inner join tbl_TPM_RequestProducts p with (nolock) on p.ownerId = c.ownerId and p.ownerType = c.fsl_tablecode and p.catalogId = c.categoryId where p.catalogType = 'CAT' and p.ownerId = and p.ownerType = union all select p.ownerId, p.ownerType, p.catalogType, c.prodline_id as catalogId, c.productCode as code, c.productLine as description from tbl_MFR_ProductLines c with (nolock) inner join tbl_TPM_RequestProducts p with (nolock) on p.ownerId = c.mfr_id and p.ownerType = 'MFR' and p.catalogId = c.prodline_id where p.catalogType = 'LIN' and p.ownerId = and p.ownerType = ), productCatalog as ( select distinct pcr.ownerId, pcr.ownerType, pcr.catalogType, pcr.catalogId, pcr.code, pcr.description, ph.unitPrice_USD as listPrice from productCatalogRaw pcr left outer join tbl_DW_ProductHierarchy_#qmd_batch.ownerType##qmd_batch.ownerId# ph with (nolock) on ph.ownerId = pcr.ownerId and ph.ownerType = pcr.ownerType and ph.skuId = pcr.catalogId and 'SKU' = pcr.catalogType ---> ) select r.requestId, convert( varchar, coalesce( r.updateDate, r.createDate )) updateDateTime, a.accountNumber, a.accountName, a.accountType, c.assignedContractNbr agreementNumber, r.description as agreementName, rt.requestType as agreementType, r.chainSequence, case when r.payableTo = 'OPR' then 'OPERATOR' when r.payableTo = 'CDR' then 'DISTRIBUTOR' else 'UNKNOWN' end as payableTo, case when rt.requestModel = 'FIXED' then 'FIXED' when rt.requestModel = 'REBATE' or rt.requestModel = 'ALLOWANCE' then 'ALLOWANCE' when rt.requestModel = 'INCENTIVE' then 'PERCENT' when rt.requestModel = 'SPENDING' then 'LUMPSUM' end as calculationMethod, case when r.paymentMethod = 'OI' then 'OFF-INVOICE' when r.paymentMethod = 'BB' then 'REBATE' when r.paymentMethod = 'D' then 'DEDUCTION' end as paymentMethod, case when r.frequency = 'I' then 'PER-INVOICE' when r.frequency = 'Q' then 'QUARTERLY' when r.frequency = 'M' then 'MONTHLY' when r.frequency = '1' then 'ONE-TIME' else 'UNKNOWN' end as paymentFrequency, r.startDate, r.endDate, p.catalogType productType, pc.code productCode, pc.description productDescription, case when rt.requestModel = 'FIXED' then p.fixedAmt when rt.requestModel = 'REBATE' or rt.requestModel = 'ALLOWANCE' then p.allowanceAmt when rt.requestModel = 'INCENTIVE' then p.incentivePct else 0.0 end as rate, cast( null as float ) as allocation, coalesce( nullif( r.generalLedgerCode, '' ), rt.generalLedgerCode ) as generalLedgerCode, coalesce( bo.email, '' ) as userLogin, case when bo.fspro_userId > 0 then bo.firstName + ' ' + bo.lastName else '' end as userName, coalesce( nullif( p.estQuantity, 0 ), 1.0 ) estimatedCases, case when p.tradeAmt = 0 or 1 = 1 then round( case when rt.requestModel = 'FIXED' then coalesce( pc.listPrice, p.listPrice ) - p.fixedAmt when rt.requestModel = 'REBATE' or rt.requestModel = 'ALLOWANCE' then p.allowanceAmt when rt.requestModel = 'INCENTIVE' then coalesce( pc.listPrice, p.listPrice ) * p.incentivePct else 0.0 end * coalesce( nullif( p.estQuantity, 0 ), 1.0 ), 2 ) else p.tradeAmt end as estimatedDollars, round( case when p.tradeAmt = 0 or 1 = 1 then round( case when rt.requestModel = 'FIXED' then coalesce( pc.listPrice, p.listPrice ) - p.fixedAmt when rt.requestModel = 'REBATE' or rt.requestModel = 'ALLOWANCE' then p.allowanceAmt when rt.requestModel = 'INCENTIVE' then coalesce( pc.listPrice, p.listPrice ) * p.incentivePct else 0.0 end * coalesce( nullif( p.estQuantity, 0 ), 1.0 ), 2 ) else p.tradeAmt end / coalesce( nullif( p.estQuantity, 0 ), 1.0 ), 2 ) estimatedRate from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_RequestTypes rt with (nolock) on rt.ownerId = r.ownerId and rt.ownerType = r.ownerType and rt.requestTypeId = r.requestTypeId inner join tbl_TPM_Contracts c with (nolock) on c.contractId = r.contractId and c.ownerId = r.ownerId and c.ownerType = r.ownerType left outer join tbl_FSPRO_members bo with (nolock) on bo.fspro_userId = c.businessOwnerUserId and bo.ownerId = c.ownerId and bo.fsl_tablecode = c.ownerType inner join partners as a with (nolock) on a.ownerId = c.ownerId and a.ownerType = c.ownerType and a.partnerId = c.partnerId and a.partnerType = c.partnerType inner join tbl_TPM_RequestProducts p with (nolock) on p.ownerId = r.ownerId and p.ownerType = r.ownerType and p.requestId = r.requestId inner join productCatalog pc with (nolock) on pc.ownerId = p.ownerId and pc.ownerType = p.ownerType and pc.catalogId = p.catalogId and pc.catalogType = p.catalogType where r.status = 'APPROVED' and r.paymentMethod in () and c.ownerId = and c.ownerType = and not exists ( select * from tbl_EAI_BatchHistory bh with (nolock) where bh.ownerId = r.ownerId and bh.ownerType = r.ownerType and bh.linkId = r.requestId and bh.linkType = 'TPM' and bh.reference = '#_historyReference# ' + convert( varchar, coalesce( r.updateDate, r.createDate )) ) order by accountName, c.assignedContractNbr, productType, code, startDate

#htmleditformat( _r.sql )#

select distinct cdr.cdr_dstCode, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) inner join tbl_TPM_RequestPartners rp with (nolock) on rp.ownerId = cdr.ownerId and rp.ownerType = cdr.fsl_tablecode and rp.partnerId = cdr.cdr_recordId and rp.partnerType = 'CDR' and rp.claimPartner = 'N' and rp.primaryPartner = 'N' inner join tbl_TPM_Requests r with (nolock) on r.ownerId = rp.ownerId and r.ownerType = rp.ownerType and r.requestId = rp.requestId and r.status = 'APPROVED' inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId where ct.ownerId = and ct.ownerType = and ct.assignedContractNbr = select distinct requestId, accountName, accountNumber, accountType, agreementNumber, agreementType, payableTo, calculationMethod, paymentMethod, paymentFrequency, startDate, endDate from qmd_export where accountNumber = '' order by accountName, requestId #_exceptionsHTML#

The following agreements do not have an associated account number and will not be included in the daily Off-Invoice file until they do:

#_exceptionsHTML#

Please make the necessary changes to ensure these agreements are handled properly.

select * from qmd_export where accountNumber <> '' insert into tbl_EAI_BatchHistory ( ownerType, ownerId, linkType, linkId, reference, batchId, batchTime ) values ( , , 'TPM', , , , ) 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 =

#qmd_export.recordCount# rows written to #htmleditformat( _exportFileName )#

select ftpDirectory, ftpPassword, ftpServer, ftpUserName from tbl_EAI_InboundFTPSources where ownerId = and ownerType = and outbound = 'Y'

File sent to #qmd_ftpSources.ftpUserName#@#qmd_ftpSources.ftpServer#

File not sent, dev testing only

File not sent, no outbound FTP source

update tbl_EAI_inboundBatches set fileName = where batchId = and ownerId = and ownerType =