#qmd_batch.resultsText#

INITIALIZE_IMPORT_TABLE

update tbl_IMPORT_InvoiceHistory set fseSkuWeightMissing = '?' where ownerId = and ownerType = and eai_batchId = and nullif( lb_qty, 0 ) is null select distinct exclusionId from tbl_IMPORT_Exclusions with (nolock) where ownerId = and ownerType = and exclusionType = 'SKU' select distinct sku, coalesce( sku_description, '' ) sku_description, case when nullif( case_qty, 0 ) is null then 'N' else 'Y' end has_case_qty, case when nullif( lb_qty, 0 ) is null then 'N' else 'Y' end has_lb_qty from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and sku <> '' select s.skuId, s.skuType, s.isOrderable, coalesce( s.equivUnitsPerCase, 1 ) equivUnitsPerCase, s.bgtCatId l1BgtCatId, p.bgtCatId l2BgtCatId, s.bgtCatOverride l1BgtCatOverride, p.prodId from tbl_PRD_Skus s with (nolock) left outer join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and s.sku = select * from qmd_skuLookup where skuType = 'CA' or isOrderable = 'Y' with duplicates as ( select sku, count(*) repeatCount from tbl_PRD_Skus with (nolock) where ownerId = and fsl_tablecode = group by sku having count(*) > 1 ) select skuId from tbl_PRD_Skus with (nolock) where sku in ( select sku from duplicates ) and ownerId = and fsl_tablecode = and sku = select s.skuId, s.skuType, s.isOrderable, coalesce( s.equivUnitsPerCase, 1 ) equivUnitsPerCase, s.bgtCatId l1BgtCatId, p.bgtCatId l2BgtCatId, s.bgtCatOverride l1BgtCatOverride, p.prodId from tbl_PRD_Skus s with (nolock) left outer join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and LTRIM(RTRIM(SUBSTRING(s.sku, (PATINDEX('%[^0]%', s.sku)), 25))) = select * from qmd_skuLookup where skuType = 'CA' or isOrderable = 'Y' ---> update tbl_IMPORT_InvoiceHistory set fseSKUId = , fseEquivUnitsPerCase = , fseBgtCatId = , product_weight = , lb_qty = case_qty * , product_weight = , case_qty = lb_qty / #_caseWeight#0 , fseCalculateCases = 'Y''N' , fseCalculateLbs = 'Y''N' where ownerId = and ownerType = and eai_batchId = and sku = and coalesce( sku_description, '' ) = and case when nullif( case_qty, 0 ) is null then 'N' else 'Y' end = '#has_case_qty#' and case when nullif( lb_qty, 0 ) is null then 'N' else 'Y' end = '#has_lb_qty#' select prodId, crmActive from tbl_PRD_Products with (nolock) where ownerId = and fsl_tablecode = and ( product = '000' or prodId = ( select gs1_defaultProdId from tblManufacturers with (nolock) where mfr_id = ownerId )) insert into tbl_PRD_Products ( ownerId, fsl_tablecode, product, shortDesc, crmActive ) values ( , , '000', 'Unassigned', 'N' ) select prodId, crmActive from tbl_PRD_Products with (nolock) where ownerId = and fsl_tablecode = and prodId = insert into tbl_PRD_SKUs ( ownerId, fsl_tablecode, prodId, sku, skuDesc, crmActive, crmOverride, recordSource, mfrId, publishDate, unpublishDate ) values ( , , , , , , 'N', , , , ) update tbl_IMPORT_InvoiceHistory set fseSKUId = , fseEquivUnitsPerCase = 1, fseBgtCatId = null, fseSKUIdStatus = 'A' , product_weight = , lb_qty = case_qty * , product_weight = , case_qty = lb_qty / #_caseWeight#0 , fseCalculateCases = 'Y''N' , fseCalculateLbs = 'Y''N' where ownerId = and ownerType = and eai_batchId = and sku = and coalesce( sku_description, '' ) = and case when nullif( case_qty, 0 ) is null then 'N' else 'Y' end = '#has_case_qty#' and case when nullif( lb_qty, 0 ) is null then 'N' else 'Y' end = '#has_lb_qty#' update tbl_IMPORT_InvoiceHistory set extended_price = round( case_qty * , 2 ) where ownerId = and ownerType = and eai_batchId = and extended_price is null and fseSkuId = and case_qty <> 0 with duplicates as ( select sku, count(*) repeatCount from tbl_PRD_Skus with (nolock) where ownerId = and fsl_tablecode = group by sku having count(*) > 1 ) select distinct sku from tbl_PRD_Skus with (nolock) where sku in ( select sku from duplicates ) and ownerId = and fsl_tablecode =

The following SKUs were skipped as duplicates exist:

select distinct exclusionId from tbl_IMPORT_Exclusions with (nolock) where ownerId = and ownerType = and exclusionType = 'CUS' select customer_number, coalesce( customer_name, '' ) customer_name, customer_address, coalesce( customer_address2, '') customer_address2, customer_city, customer_state, customer_zipcode, customer_territory_id, sum( coalesce( case_qty, 0.0 )) case_qty, sum( coalesce( lb_qty, 0.0 ) ) lb_qty, sum( coalesce( extended_price, 0.0 )) extended_price from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = group by customer_number, customer_name, customer_address,customer_address2, customer_city, customer_state, customer_zipcode,customer_territory_id having sum( coalesce( case_qty, 0.0 )) <> 0 or sum( coalesce( lb_qty, 0.0 ) ) <> 0 or sum( coalesce( extended_price, 0.0 )) <> 0 select distinct cdr.cdr_recordId, coalesce( cdr.bgtCatId, coalesce( pcdr.bgtCatId, 0 )) bgtCatId from tbl_CDR_DstCodes cn with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = cn.cdr_recordId and cdr.ownerId = cn.ownerId and cdr.fsl_tablecode = cn.ownerType left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cn.ownerId = and cn.ownerType = and cn.cdr_dstcode = select ter.territoryId from tbl_TER_Territories ter with (nolock) inner join tbl_TER_TerritoryLevels lvl with (nolock) on lvl.levelId = ter.levelId and lvl.ownerId = ter.ownerId and lvl.fsl_tablecode = ter.fsl_tablecode where ter.ownerId = and ter.fsl_tablecode = and ter.internalSalesID = select top 1 territoryId, name, levelId from tbl_TER_Territories with (nolock) where levelId = ( select top 1 levelId from tbl_TER_TerritoryLevels with (nolock) where ownerId = and fsl_tablecode = and rank = 1 order by levelId ) and ownerId = and fsl_tablecode = order by territoryId SELECT levelId FROM tbl_TER_TerritoryLevels with (nolock) WHERE OWNERID = and fsl_TableCode = and parentLevelID = insert into tbl_TER_Territories (OwnerID,FSL_TableCode, Name, LevelID, ParentTerritoryID, InternalSalesID,managementRank, oprEnabled ,cdrEnabled, sName, shareWithFoodMark,countryId ) values (, , , , , , 'A','Y','Y', , 'N',27 ) select ter.territoryId from tbl_TER_Territories ter with (nolock) inner join tbl_TER_TerritoryLevels lvl with (nolock) on lvl.levelId = ter.levelId and lvl.ownerId = ter.ownerId and lvl.fsl_tablecode = ter.fsl_tablecode and lvl.BrokerLevel = 1 and lvl.zipCodeMapping = 'STANDARD' inner join tbl_TER_ZipCodeLink zcl with (nolock) on zcl.territoryId = ter.territoryId inner join tbl_UT_ZipCodes_3d z3 with (nolock) on z3.zip3d_id = zcl.zip3d_id where ter.ownerId = and ter.fsl_tablecode = and z3.zipcode_3d = select top 1 territoryId, name from tbl_TER_Territories with (nolock) where levelId = ( select top 1 levelId from tbl_TER_TerritoryLevels with (nolock) where ownerId = and fsl_tablecode = and rank = 1 order by levelId ) and ownerId = and fsl_tablecode = order by territoryId select distinct cdr.cdr_recordId, coalesce( cdr.bgtCatId, coalesce( pcdr.bgtCatId, 0 )) bgtCatId, cdr.fsl_dstId from tbl_CDR_DstCodes cn with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = cn.cdr_recordId and cdr.ownerId = cn.ownerId and cdr.fsl_tablecode = cn.ownerType left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cn.ownerId = and cn.ownerType = and cn.cdr_dstcode = and cdr.cdr_dstCompanyType = 'B' select cdr_recordId, cdr_dstCode from tbl_CDR_Distributors with (nolock) where cdr_dstCompanyType = 'R' and ownerId = and fsl_tablecode = and cdr_dstName like select cdr_recordId from tbl_CDR_Distributors with (nolock) where ownerId = and fsl_tableCode = and cdr_dstCode = and cdr_dstCompanyType = 'B' insert into tbl_CDR_Distributors ( ownerId, fsl_tablecode, cdr_dstCompanyType, cdr_dstCode, cdr_dstName, recordSource, cdr_territoryId, crmActive, lastUpdated, cdr_dstAddress1,cdr_dstAddress2, cdr_dstcity, cdr_dststate, cdr_dstzip, createDate) values( , , 'B', , , , , 'N', , , , , , , ) insert into tbl_CDR_DstCodes ( ownerId, ownerType, cdr_recordId, cdr_dstCode, cdr_dstCode_type ) values( , , , , 'M' ) select cdr_recordId from tbl_CDR_Distributors with (nolock) where ownerId = and fsl_tableCode = and cdr_dstCode = and cdr_dstCompanyType = 'S' insert into tbl_CDR_Distributors ( ownerId, fsl_tablecode, cdr_dstCompanyType, cdr_dstCode, cdr_dstName, recordSource, cdr_territoryId, crmActive, lastUpdated, cdr_dstAddress1,cdr_dstAddress2, cdr_dstcity, cdr_dststate, cdr_dstzip, createDate, fsl_mapPriority, cdr_specialAcctType, cdr_redistAcctFor , fsl_dstId ) values( , , 'S', , , , , 'Y', , , , , , , , 2, 'RDA', , ) insert into tbl_CDR_DstCodes ( ownerId, ownerType, cdr_recordId, cdr_dstCode, cdr_dstCode_type ) values( , , , , 'M' ) update tbl_IMPORT_InvoiceHistory set fseCDR_recordIdStatus = 'A' , fseCDR_recordId = where ownerId = and ownerType = and eai_batchId = and customer_number = and coalesce( customer_name, '' ) = select cdr.cdr_recordId, coalesce( cdr.bgtCatId, coalesce( pcdr.bgtCatId, 0 )) bgtCatId from tbl_CDR_DstCodes cn with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = cn.cdr_recordId and cdr.ownerId = cn.ownerId and cdr.fsl_tablecode = cn.ownerType left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId = select top 1 cdr_RecordId, fsl_dstId from tbl_CDR_distributors with (nolock) where cdr_recordId in ( select cdr_RecordId from tbl_CDR_DstCodes with (nolock) where cdr_dstCode = and ownerId = and ownerType = ) and cdr_dstcompanyType in ('B', 'O') order by cdr_dstcompanyType, cdr_RecordId select distinct redist_customer_number from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and customer_number = and reallocation = and redist_customer_number is not null select distinct cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstcompanytype from tbl_CDR_DstCodes cn with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = cn.cdr_recordId and cdr.ownerId = cn.ownerId and cdr.fsl_tablecode = cn.ownerType left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cn.ownerId = and cn.ownerType = and cn.cdr_dstcode = select distinct cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstcompanytype from tbl_CDR_DstCodes cn with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = cn.cdr_recordId and cdr.ownerId = cn.ownerId and cdr.fsl_tablecode = cn.ownerType left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cn.ownerId = and cn.ownerType = and cn.cdr_dstcode = and cdr.cdr_dstCompanytype = insert into tbl_CDR_Distributors (ownerId, fsl_tableCode, cdr_dstName, cdr_dstAddress1, cdr_dstAddress2, cdr_dstcity, cdr_dststate, cdr_dstzip, cdr_dstCountryId, cdr_dstmarketinggrp, cdr_territoryId, fsl_dstId, recState, setMethod, mfr_bsr_id, crmActive,FocusAccount,classificationId, fseTerritoryIdStatus, cdr_priority, useSysFields, hasSysFields, cdr_dstPath, cdr_dstCode, cdr_dstcompanyType, fsl_mapPriority, fsl_masterlinkYN, cdr_specialAcctType, cdr_redistAcctFor, recordSource ) select ownerId, fsl_tableCode, cdr_dstName, cdr_dstAddress1, cdr_dstAddress2, cdr_dstcity, cdr_dststate, cdr_dstzip, cdr_dstCountryId, cdr_dstmarketinggrp, cdr_territoryId, fsl_dstId, recState, setMethod, mfr_bsr_id, crmActive,FocusAccount, classificationId, fseTerritoryIdStatus, cdr_priority, useSysFields, hasSysFields, cdr_dstPath , '' cdr_dstCode, 'S' cdr_dstcompanyType, 2 fsl_mapPriority, 'N' fsl_masterlinkYN, 'RDA' cdr_specialAcctType, #qmd_redistCustomerDetails.cdr_recordId# cdr_redistAcctFor, '' recordSource from tbl_CDR_Distributors redist with (nolock) where ownerId = and fsl_tablecode = and cdr_recordId = select max(cdr_recordId) as cdr_recordId from tbl_CDR_Distributors with (nolock) where ownerId = and fsl_tablecode = and cdr_redistAcctFor = and cdr_dstcompanyType = 'S' select fsl_dstId from tbl_CDR_Distributors with (nolock) where ownerId = and fsl_tablecode = and cdr_recordId = update tbl_CDR_Distributors set cdr_dstCode = , recordSource = , createDate = where cdr_recordId = and ownerId = and fsl_tableCode = insert into tbl_CDR_DstCodes (ownerId, ownerType, cdr_recordId, cdr_dstCode, cdr_dstcode_type, cdr_dstCode_comments) values ( , , , , , ) update tbl_IMPORT_InvoiceHistory set fseCdr_RecordId = , customer_number = , fseCDR_recordIdStatus = 'A' where ownerId = and ownerType = and eai_batchId = and customer_number = and reallocation = and redist_customer_number = ` select cdr.cdr_recordId, coalesce( dst.dstId, 0 ) fsl_dstId, cdr.cdr_dstCompanyType from tbl_CDR_DstCodes cn with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = cn.cdr_recordId and cdr.ownerId = cn.ownerId and cdr.fsl_tablecode = cn.ownerType left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cn.ownerId = and cn.ownerType = and cn.cdr_dstcode = select mo.mfrCustNum, mo.directCustomer, mo.directCustomerNbr, mo.operatorId from tbl_OPR_ClientOperators do with (nolock) inner join tbl_OPR_ClientOperators mo with (nolock) on mo.fsl_choid = do.fsl_choId and mo.ownerId = and mo.fsltablecode = where do.ownerId = and do.fsltablecode = and do.mfrCustNum = and do.fsl_choId > 0 select cdr.cdr_recordId, coalesce( cdr.bgtCatId, 0 ) bgtCatId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_CDR_DstCodes cdrCode with (nolock) on cdrCode.ownerId = cdr.ownerId and cdrCode.ownerType = cdr.fsl_tablecode and cdrCode.cdr_recordId = cdr.cdr_recordId and cdrCode.cdr_dstCode = where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_dstCompanyType = 'O' update tbl_OPR_ClientOperators set directCustomerNbr = , directCustomer = 'Y' where operatorId = and ownerId = and fsltablecode = select cdr.cdr_recordId, coalesce( cdr.bgtCatId, coalesce( pcdr.bgtCatId, 0 )) bgtCatId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId and dst.dstId = left outer join tbl_CDR_Distributors pcdr on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cdr.ownerId = and cdr.fsl_tablecode = update tbl_IMPORT_InvoiceHistory set fseCDR_RecordId = where ownerId = and ownerType = and eai_batchId = and customer_number = and coalesce( customer_name, '' ) = and coalesce(fseCdr_RecordId,0) = 0 update tbl_IMPORT_InvoiceHistory set fseBgtCatId = where ownerId = and ownerType = and eai_batchId = and fseCDR_recordId = and fseSkuId is not null and fseBgtCatId is not null select customer_number, coalesce( customer_name, '' ) customer_name, sum( coalesce( case_qty, 0.0 )) case_qty, sum( coalesce( lb_qty, 0.0 ) ) lb_qty, sum( coalesce( extended_price, 0.0 )) extended_price from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and fseCdr_recordId is null and (coalesce( case_qty, 0.0 ) = 0.0 or coalesce( lb_qty, 0.0 ) = 0.0 or coalesce( extended_price, 0.0 ) = 0.0 ) group by customer_number, customer_name having sum( coalesce( case_qty, 0.0 )) = 0 and sum( coalesce( lb_qty, 0.0 ) ) = 0 and sum( coalesce( extended_price, 0.0 )) = 0 update tbl_IMPORT_InvoiceHistory set fseCDR_recordIdStatus = 'Z' where ownerId = and ownerType = and eai_batchId = and customer_number = and coalesce( customer_name, '' ) = and fseCdr_recordId is null select distinct invoice_date from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and invoice_date is not null update tbl_IMPORT_InvoiceHistory set fseForecastYear = , fseForecastMonth = , fseFiscalYear = , fseFiscalMonth = , fseFiscalWeek = where ownerId = and ownerType = and eai_batchId = and invoice_date = #qmd_batch.resultsText#

INVOICE HISTORY

select cdrRDC.cdr_recordId from tbl_CDR_Distributors cdrRDC with (nolock) inner join tbl_DST_Distributors dstRDC with (nolock) on dstRDC.dstId = cdrRDC.fsl_dstId and dstRDC.dstCompanyType = 'RDC' where cdrRDC.ownerId = and cdrRDC.fsl_tablecode = select cdr.cdr_recordId, cdr.rdcVolumeShare, cdr.cdr_territoryId, cdr.cdr_dstCode, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId inner join tbl_CDR_Distributors cdrRDC with (nolock) on cdrRDC.fsl_dstId = dst.dstRDCId and cdrRDC.ownerId = cdr.ownerId and cdrRDC.fsl_tablecode = cdr.fsl_tablecode inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = cdr.cdr_territoryId and ter.fsl_tablecode = cdr.fsl_tablecode and ter.ownerId = cdr.ownerId where cdrRDC.ownerId = and cdrRDC.fsl_tablecode = and cdrRDC.cdr_recordId = and cdr.rdcVolumeShare > 0.0 select invoice_number, invoice_date, fseForecastYear, fseForecastMonth, fseFiscalMonth, fseFiscalYear, fseSkuId, fseCDR_RecordId, sum( coalesce( extended_price, 0 )) extended_price, sum( case_qty ) case_qty, sum( coalesce( lb_qty, 0 ) ) lb_qty, sum( case_qty * fseEquivUnitsPerCase ) alt_qty, fseEquivUnitsPerCase, customer_number, fseBgtCatId, fseFiscalWeek from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and fseCDR_RecordId in ( ) and fseSKUId is not null and nullif( invoice_number, '' ) is not null group by invoice_number, invoice_date, fseForecastYear, fseForecastMonth, fseFiscalMonth, fseFiscalYear, fseSkuId, fseCDR_RecordId, fseEquivUnitsPerCase, customer_number, fseBgtCatId, fseFiscalWeek select sku, skudesc from tbl_PRD_Skus with (nolock) where ownerId = and fsl_tablecode = and skuId = insert into tbl_IMPORT_InvoiceHistory ( ownerId, ownerType, eai_batchId, eai_lineNumber, customer_number, invoice_number, invoice_date, fseCDR_RecordId, case_qty, lb_qty, extended_price, fseEquivUnitsPerCase, fseForecastYear, fseForecastMonth, fseFiscalMonth, fseFiscalYear, fseSkuId, fseRDCId, sku, sku_description, customer_name, fseBgtCatId, fseFiscalWeek ) values ( , , , , , , , , ,, , , , , , , , , , , ,, ) update tbl_IMPORT_InvoiceHistory set fseRDC = 'Y' where ownerId = and ownerType = and eai_batchId = and fseCDR_RecordId = and fseSKUId = and invoice_number = and invoice_date = and fseForecastYear = and fseForecastMonth = and fseFiscalMonth = and fseFiscalYear = and fseEquivUnitsPerCase = and customer_number = select invoice_number, invoice_date, fseForecastYear, fseForecastMonth, fseFiscalMonth, fseFiscalYear, fseSkuId, fseCDR_RecordId, order_date,ship_date, promo_number, sum( coalesce( extended_price, 0 )) extended_price, sum( coalesce( case_qty, 0) ) case_qty, sum( coalesce( lb_qty, 0 ) ) lb_qty, sum( coalesce( case_qty * fseEquivUnitsPerCase, 0) ) alt_qty, fseEquivUnitsPerCase, customer_number,reverseInvoice,reallocation from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and fseCDR_RecordId is not null and fseSKUId is not null and nullif( invoice_number, '' ) is not null group by invoice_number, invoice_date, fseForecastYear, fseForecastMonth, fseFiscalMonth, fseFiscalYear, fseSkuId, fseCDR_RecordId, order_date,ship_date, promo_number,fseEquivUnitsPerCase, customer_number,reverseInvoice,reallocation update tbl_CDR_InvoiceHistory set invoiceNumber = where ownerId = 54 and ownerType = 'MFR' and cdr_recordId = and skuId = and invoiceNumber = 'REDIST' and invoiceDate = and dollarAmt = select ownerId, ownertype, cdr_recordId, skuId, invoiceNumber, invoiceDate from tbl_CDR_InvoiceHistory with (nolock) where ownerId = and ownerType = and cdr_recordId = and skuId = and invoiceNumber = and invoiceDate = select d.cdr_recordId from tbl_cdr_dstCodes c with (nolock) inner join tbl_cdr_distributors d with (nolock) on d.ownerId = c.ownerId and d.fsl_tableCode = c.ownerType where c.ownerId = and c.ownerType = and c.cdr_dstCode = and c.cdr_dstCode like '%-DOT' and d.cdr_dstcompanyType NOT IN ('S','B','O','P') and d.cdr_recordId = ` insert into tbl_CDR_InvoiceHistory ( ownerId, ownerType, cdr_recordId, skuId, invoiceNumber, invoiceDate, forecast_year, forecast_month, fiscalYear, fiscalMonth, orderDate, shipDate, purchaseOrderNumber, caseAmt, dollarAmt, weightAmt, altAmt, equivUnitsPerCase, invoiceCustomerNbr, reverseInvoice ) values ( , , , , , , , , , , ,, , , , , , , , ) update tbl_CDR_InvoiceHistory set forecast_year = , forecast_month = , fiscalYear = , fiscalMonth = , orderDate = , shipDate = , purchaseOrderNumber = , caseAmt = , dollarAmt = , weightAmt = , altAmt = , equivUnitsPerCase = , invoiceCustomerNbr = , updateDate = { fn now() }, reverseInvoice = where ownerId = and ownerType = and cdr_recordId = and skuId = and invoiceNumber = and invoiceDate = #qmd_batch.resultsText#

FORECAST DETAILS

select * from qmd_forecastMonths union select * from qmd_anomalies select s.skuId, s.sku, p.prodId, case when s.bgtCatOverride = 'Y' and s.bgtCatId is not null then s.bgtCatId else p.bgtCatId end bgtCatId, coalesce( s.equivUnitsPerCase, 1 ) equivUnitsPerCase from tbl_PRD_Skus s with (nolock) inner join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = p.fsl_tablecode where s.skuId = and s.ownerId = and s.fsl_tablecode = select ter.territoryId, coalesce( cdr.bgtCatId, coalesce( pcdr.bgtCatId, 0 )) bgtCatId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_TER_TErritories ter with (nolock) on ter.territoryId = cdr.cdr_territoryId and ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = dst.dstParentCo and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId = update tbl_MFR_TPF_Detail#_tpfSuffix# set forecast_qty = , forecast_amt = , forecast_alt = , prodId = , bgtCategoryId = , lastUpdateSource = , lastUpdate = , productItemCode = , territoryId = where mfr_id = and tpartnerType = 'CDR' and tpartnerId = and skuId = and productLevel = 'SKU' and forecast_status = 'A' and forecast_year = and forecast_month = insert into tbl_MFR_TPF_Detail#_tpfSuffix# ( mfr_id, tpartnerType, tpartnerId, skuId, productLevel, forecast_status, forecast_year, forecast_month, forecast_qty, forecast_amt, forecast_alt, prodId, bgtCategoryId, lastUpdateSource, lastUpdate, productItemCode, territoryId ) values ( , 'CDR', , , 'SKU', 'A', , , , , , , , , , , ) select count(*) orphanCount from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) where not exists ( select * from tbl_CDR_InvoiceHistory ih with (nolock) where ih.ownerType = 'MFR' and ih.ownerId = tpf.mfr_Id and ih.cdr_recordId = tpf.tpartnerId and ih.skuId = tpf.skuId ) and tpf.mfr_id = and 'MFR' = and tpf.forecast_status = 'A' and tpf.tpartnerType = 'CDR' delete tpf from tbl_MFR_TPF_Detail#_tpfSuffix# tpf where not exists ( select * from tbl_CDR_InvoiceHistory ih with (nolock) where ih.ownerType = 'MFR' and ih.ownerId = tpf.mfr_Id and ih.cdr_recordId = tpf.tpartnerId and ih.skuId = tpf.skuId ) and tpf.mfr_id = and 'MFR' = and tpf.forecast_status = 'A' and tpf.tpartnerType = 'CDR' select territoryId, bgtCategoryId, sum( forecast_qty ) caseAmt, sum( forecast_amt ) dollarAmt, sum( forecast_alt ) altAmt from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where territoryId > 0 and bgtCategoryId > 0 and tpartnerType = 'CDR' and forecast_status = 'A' and mfr_id = and forecast_month = and forecast_year = group by territoryId, bgtCategoryId order by territoryId update tbl_MFR_TPF_Detail#_tpfSuffix# set forecast_qty = , forecast_amt = , forecast_alt = , lastUpdateSource = , lastUpdate = where mfr_id = and tpartnerType = 'TER' and tpartnerId = and bgtCategoryId = and productItemCode = and productLevel = 'UOM' and forecast_status = 'A' and forecast_year = and forecast_month = insert into tbl_MFR_TPF_Detail#_tpfSuffix# ( mfr_id, tpartnerType, tpartnerId, productLevel, forecast_status, forecast_year, forecast_month, forecast_qty, forecast_amt, forecast_alt, bgtCategoryId, lastUpdateSource, lastUpdate, productItemCode, territoryId ) values ( , 'TER', , 'UOM', 'A', , , , , , , , , , ) select distinct tpartnerId, bgtCategoryId from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where mfr_Id = and tpartnerType = 'TER' and productItemCode like 'BGT%' and forecast_year = and forecast_month = and forecast_status = 'A' select territoryId, bgtCategoryId from qmd_bgtCategoryActuals where territoryId = and bgtCategoryId =

Purged #_tpfMonth#/#_tpfYear# - TerritoryID #tPartnerId#, BudgetCategoryId #bgtCategoryId#

delete from tbl_MFR_TPF_Detail#_tpfSuffix# where mfr_Id = and tpartnerType = 'TER' and productItemCode like 'BGT%' and forecast_year = and forecast_month = and forecast_status = 'A' and bgtCategoryId = and tPartnerId = ---> select fseFiscalYear, max( fseFiscalWeek ) weeksElapsed from tbl_IMPORT_InvoiceHistory with (nolock ) where ownerId = and ownerType = and eai_batchId = and fseCDR_RecordId is not null and fseSKUId is not null group by fseFiscalYear order by fseFiscalYear update tbl_CRM_FiscalCalendar set weeksElapsed = where ownerId = and ownerType = and fiscalYear = and coalesce( weeksElapsed, 0 ) < select distinct fseFiscalYear from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and fseCDR_RecordId is not null and fseSKUId is not null #qmd_batch.resultsText#

RECALCULATE TERRITORIES

select territoryId, bgtCategoryId, sum( forecast_qty ) caseAmt, sum( forecast_amt ) dollarAmt, sum( forecast_alt ) altAmt from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where territoryId > 0 and bgtCategoryId > 0 and tpartnerType = 'CDR' and forecast_status = 'A' and mfr_id = and forecast_month = and forecast_year = group by territoryId, bgtCategoryId order by territoryId select lvl.businessPlanReviewCycle from tbl_TER_Territories ter inner join tbl_TER_TerritoryLevels lvl on lvl.levelId = ter.levelId where ter.territoryId =

BATCH DETAILS

select ter.territoryId, sma.brokerId, b.broker_name, ter.internalSalesId, ter.name territoryName, i.fseFiscalYear fiscalYear, max( convert( int, i.fseFiscalWeek )) weekNumber from tbl_IMPORT_InvoiceHistory i with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = fseCDR_recordId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = cdr.cdr_territoryId and ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.territoryId = ter.territoryId and sma.mfr_Id = ter.ownerId and sma.primaryOffice = 'Y' inner join tblBrokers b with (nolock) on b.broker_id = sma.brokerId where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseSkuId is not null and i.fseBgtCatId is not null group by ter.territoryId, sma.brokerid, b.broker_name, ter.internalSalesId, ter.name , i.fseFiscalYear order by fiscalYear, weekNumber, territoryName select fiscalYear, weekNumber from qmd_sharingScope order by fiscalYear desc, weekNumber desc #_dataSharingHTML#

Results from Prior Steps

#qmd_batch.resultsText# select top 1 businessPlanId from tbl_TER_BusinessPlans with (nolock) where ownerId = and ownerType = select rowId from tbl_EAI_inboundFileProcessors with (nolock) where ownerId = and ownerType = and fileProcessor = '/mstrcfmod/MKT/batch/recalculateBusinessPlans.cfm' insert into tbl_EAI_inboundFileProcessors ( ownerId, ownerType, fileName, fileProcessor, fileFormat, clientProcessEmail, fseProcessEmail, allowUpload, autoProcess, processingWindowStartHr24, processingWindowLengthHr, allowEmail, defaultBatchClass, repeating, allowFTP, repeatTime ) values ( ,,'', '/mstrcfmod/MKT/batch/recalculateBusinessPlans.cfm', '$RecalculateBusinessPlans', 'ddamelio@fsenablers.com', 'mgriffin@fsenablers.com', 'N', 'Y', 0, 24, 'N', 'I', 'Y', 'N', '05:00AM' ) select customer_number, customer_name, min( eai_lineNumber ) as firstLine, count( * ) lines from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and fseCdr_recordIdStatus = 'A' group by customer_number, customer_name order by firstLine select sku, sku_description, min( eai_lineNumber ) as firstLine, count( * ) lines from tbl_IMPORT_InvoiceHistory with (nolock) where ownerId = and ownerType = and eai_batchId = and fseSKUIdStatus = 'A' group by sku, sku_description order by firstLine

Please review and reconcile the following additions with previously vetted distributors and/or skus in the library before reprocessing this file.

The following Distributors were added:

The following SKUs were added:

select distinct fseForecastMonth, fseForecastYear, fseFiscalYear from tbl_IMPORT_InvoiceHistory where ownerId = and ownerType = and eai_batchId = and fseCDR_RecordId is not null and fseSKUId is not null order by fseFiscalYear, fseForecastYear, fseForecastMonth select s.skuId, s.skuType, s.isOrderable, coalesce( s.equivUnitsPerCase, 1 ) equivUnitsPerCase, s.bgtCatId l1BgtCatId, p.bgtCatId l2BgtCatId, s.bgtCatOverride l1BgtCatOverride, p.prodId from tbl_PRD_Skus s with (nolock) left outer join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and s.#_skuField# = select * from qmd_skuLookup where skuType = 'CA' or isOrderable = 'Y' with duplicates as ( select #_skuField#, count(*) repeatCount from tbl_PRD_Skus with (nolock) where ownerId = and fsl_tablecode = group by #_skuField# having count(*) > 1 ) select skuId from tbl_PRD_Skus with (nolock) where #_skuField# in ( select #_skuField# from duplicates ) and ownerId = and fsl_tablecode = and #_skuField# = select s.skuId, s.skuType, s.isOrderable, coalesce( s.equivUnitsPerCase, 1 ) equivUnitsPerCase, s.bgtCatId l1BgtCatId, p.bgtCatId l2BgtCatId, s.bgtCatOverride l1BgtCatOverride, p.prodId from tbl_PRD_Skus s with (nolock) left outer join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode where s.ownerId = and s.fsl_tablecode = and LTRIM(RTRIM(SUBSTRING(s.#_skuField#, (PATINDEX('%[^0]%', s.#_skuField#)), 25))) = select * from qmd_skuLookup where skuType = 'CA' or isOrderable = 'Y'