Not a Monday
 ---> select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat = '$KELLANOVA_OPPORTUNITY_EXPORT' and batchTime < and status = 'OK' order by batchTime desc delete from tbl_EXPORT_Kellanova_Opportunity where ownertype = and ownerId = and eai_batchId = drop table if exists [#_tempFlexFieldOPRTable#]; with flexValues as ( select f.name as field, fv.partnerId as operatorId, fv.data as [value] from tbl_OPR_FieldValues fv with (nolock) inner join tbl_OPR_Fields f with (nolock) on f.fieldId = fv.fieldId and f.ownerId= and f.ownerType = where fv.partnerType = 'OPR' and f.name in ('TIBERSOFTID', 'CDSIID') ) select p.operatorId, p.TIBERSOFTID, p.CDSIID into [#_tempFlexFieldOPRTable#] from flexValues pivot( max( value ) for field in ( TIBERSOFTID, CDSIID )) as p where operatorId is not null; alter table [#_tempFlexFieldOPRTable#] alter column operatorId int not null; alter table [#_tempFlexFieldOPRTable#] add constraint [pk_#_tempFlexFieldOPRTable#] primary key (operatorId); drop table if exists [#_tempFlexFieldCDRTable#]; with flexValues as ( select f.name as field, fv.partnerId as distId, fv.data as [value] from tbl_CDR_FieldValues fv with (nolock) inner join tbl_CDR_Fields f with (nolock) on f.fieldId = fv.fieldId and f.ownerId= and f.ownerType = where fv.partnerType = 'CDR' and f.name in ('TIBERSOFTID', 'CDSIID') ) select p.distId, p.TIBERSOFTID, p.CDSIID into [#_tempFlexFieldCDRTable#] from flexValues pivot( max( value ) for field in ( TIBERSOFTID, CDSIID )) as p where distId is not null; alter table [#_tempFlexFieldCDRTable#] alter column distId int not null; alter table [#_tempFlexFieldCDRTable#] add constraint [pk_#_tempFlexFieldCDRTable#] primary key (distId); with opr_export as ( select ownerType, ownerId, #attributes.batchId# as eai_batchId, row_number() over (order by ownerId) as eai_lineNumber, -- Location details divisionName, regionName, territoryName, partnerTerritoryName, partnerAddress, partnerCity, partnerState, partnerZipCode, -- Partner and operator details mfr_name, partnerType, oprClassification, partnerId, partnerName, operatorPriority, parentOperatorName as OperatorHQ, clientSegName, -- CMC and GPO details cmcName_1, cmcName_2, cmcName_3, gpoName_1, gpoName_2, gpoName_3, -- Opportunity details opportunityId, opportunityDescription as comments, category, product, shortDesc, product + ' - ' + shortDesc as product_shortDesc, sku, skuDesc, sku + ' - ' + skuDesc as sku_skuDesc, brand, budgetCategory, typeCode, case when lto = 'N' then 'ANNUAL' else 'LTO' end as volumeType, -- Sales representative and dates effectiveRepFirstName + ' ' + effectiveRepLastName as salesRepName, createDate, updateDate, --isnull(nullif(validation_age_days, ''), 0) as validation_age_days, datediff( day, createDate, coalesce( dateClosed, getDate() ) ) as daysInPipeline, -- Stage and reason details stageName, noSaleGeneralReason, cdr_dstName, -- Distributor and financial details distributorLastInvoiceDate, isnull(nullif(distributorLastInvoiceCases, ''), 0) as distributorLastInvoiceCases, isnull(nullif(distributorLastInvoiceDollars, ''), 0) as distributorLastInvoiceDollars, cases, lbs, dollars, -- Additional details estOrderDate, pastDue, fiscalYear, isnull(nullif(impactCases, ''), 0) as impactCases, isnull(nullif(impactDollars, ''), 0) as impactDollars, first_opr_sku_purchase_month, last_opr_purchase_month, isnull(nullif(last_confirmed_cases, ''), 0) as last_confirmed_cases, isnull(nullif(last_confirmed_dollars, ''), 0) as last_confirmed_dollars, last_opr_sku_purchase_month as last_opr_sku_purchase, coalesce(ffo.TIBERSOFTID,'') as tibersoft_Id, coalesce(ffo.CDSIID,'') as cds_Id, cdr_dstCode as distributor_Id, -- Tse-1585 Objective Fields focusProduct as focus, objName1 as objective_1, objName2 as objective_2, objName3 as objective_3, objName4 as objective_4, objName5 as objective_5 from tbl_DW_Opportunities_#attributes.ownerType##attributes.ownerId# op with (nolock) left outer join [#_tempFlexFieldOPRTable#] ffo with (nolock) on ffo.operatorId = op.partnerId where op.updateDate between and and op.partnerType = 'OPR' ), cdr_export as ( select ownerType, ownerId, #attributes.batchId# as eai_batchId, row_number() over (order by ownerId) as eai_lineNumber, -- Location details divisionName, regionName, territoryName, partnerTerritoryName, partnerAddress, partnerCity, partnerState, partnerZipCode, -- Partner and operator details mfr_name, case when partnerType = 'CDR' then 'DST' else partnerType end as partnerType, oprClassification, partnerId, partnerName, operatorPriority, parentOperatorName as OperatorHQ, clientSegName, -- CMC and GPO details cmcName_1, cmcName_2, cmcName_3, gpoName_1, gpoName_2, gpoName_3, -- Opportunity details opportunityId, opportunityDescription as comments, category, product, shortDesc, product + ' - ' + shortDesc as product_shortDesc, sku, skuDesc, sku + ' - ' + skuDesc as sku_skuDesc, brand, budgetCategory, typeCode, case when lto = 'N' then 'ANNUAL' else 'LTO' end as volumeType, -- Sales representative and dates effectiveRepFirstName + ' ' + effectiveRepLastName as salesRepName, createDate, updateDate, --isnull(nullif(validation_age_days, ''), 0) as validation_age_days, datediff( day, createDate, coalesce( dateClosed, getDate() ) ) as daysInPipeline, -- Stage and reason details stageName, noSaleGeneralReason, cdr_dstName, -- Distributor and financial details distributorLastInvoiceDate, isnull(nullif(distributorLastInvoiceCases, ''), 0) as distributorLastInvoiceCases, isnull(nullif(distributorLastInvoiceDollars, ''), 0) as distributorLastInvoiceDollars, cases, lbs, dollars, -- Additional details estOrderDate, pastDue, fiscalYear, isnull(nullif(impactCases, ''), 0) as impactCases, isnull(nullif(impactDollars, ''), 0) as impactDollars, first_opr_sku_purchase_month, last_opr_purchase_month, isnull(nullif(last_confirmed_cases, ''), 0) as last_confirmed_cases, isnull(nullif(last_confirmed_dollars, ''), 0) as last_confirmed_dollars, last_opr_sku_purchase_month as last_opr_sku_purchase, coalesce(ffc.TIBERSOFTID,'') as tibersoft_Id, coalesce(ffc.CDSIID,'') as cds_Id, cdr_dstCode as distributor_Id, -- Tse-1585 Objective Fields focusProduct as focus, objName1 as objective_1, objName2 as objective_2, objName3 as objective_3, objName4 as objective_4, objName5 as objective_5 from tbl_DW_Opportunities_#attributes.ownerType##attributes.ownerId# op with (nolock) left outer join [#_tempFlexFieldCDRTable#] ffc with (nolock) on ffc.distId = op.partnerId where op.updateDate between and and op.partnerType = 'CDR' ), export as ( select * from opr_export union all select * from cdr_export ) insert into tbl_EXPORT_Kellanova_Opportunity ( ownerId, ownerType, eai_batchId, eai_lineNumber, divisionName, regionName, territoryName, mfr_name, partnerTerritoryName, partnerType, oprClassification, partnerId, partnerName, partnerAddress, partnerCity, partnerState, partnerZipCode, operatorPriority, OperatorHQ, clientSegName, cmcName_1, cmcName_2, cmcName_3, gpoName_1, gpoName_2, gpoName_3, opportunityId, comments, category, product, shortDesc, productDesc, sku, skuDesc, sku_skuDesc, brand, budgetCategory, typeCode, volume_type, salesRepName, createDate, updateDate, validation_age_days, stageName, noSaleGeneralReason, cdr_dstName, distributorLastInvoiceDate, distributorLastInvoiceCases, distributorLastInvoiceDollars, cases, lbs, dollars, estOrderDate, pastDue, fiscalYear, ty_cases, ty_dollars, first_opr_sku_purchase_month, last_opr_purchase_month, last_confirmed_cases, last_confirmed_dollars, last_opr_sku_purchase, tibersoft_Id, cds_Id, distributor_Id, focus, objective_1, objective_2, objective_3, objective_4, objective_5 ) select ownerId, ownerType, eai_batchId, eai_lineNumber, divisionName, regionName, territoryName, mfr_name, partnerTerritoryName, partnerType, oprClassification, partnerId, partnerName, partnerAddress, partnerCity, partnerState, partnerZipCode, operatorPriority, OperatorHQ, clientSegName, cmcName_1, cmcName_2, cmcName_3, gpoName_1, gpoName_2, gpoName_3, opportunityId, comments, category, product, shortDesc, product_shortDesc, sku, skuDesc, sku_skuDesc, brand, budgetCategory, typeCode, volumeType, salesRepName, createDate, updateDate, daysInPipeline, stageName, noSaleGeneralReason, cdr_dstName, distributorLastInvoiceDate, distributorLastInvoiceCases, distributorLastInvoiceDollars, cases, lbs, dollars, estOrderDate, pastDue, fiscalYear, impactCases, impactDollars, first_opr_sku_purchase_month, last_opr_purchase_month, last_confirmed_cases, last_confirmed_dollars, last_opr_sku_purchase, tibersoft_Id, cds_Id, distributor_Id, focus, objective_1, objective_2, objective_3, objective_4, objective_5 from export order by eai_lineNumber select ownerId, ownerType, eai_batchId, eai_lineNumber, divisionName, regionName, territoryName, mfr_name, partnerTerritoryName, partnerType, oprClassification, partnerId, partnerName, partnerAddress, partnerCity, partnerState, partnerZipCode, operatorPriority, OperatorHQ, clientSegName, cmcName_1, cmcName_2, cmcName_3, gpoName_1, gpoName_2, gpoName_3, opportunityId, comments, category, product, shortDesc, productDesc, sku, skuDesc, sku_skuDesc, brand, budgetCategory, typeCode, volume_type, salesRepName, createDate, updateDate, validation_age_days, stageName, noSaleGeneralReason, cdr_dstName, distributorLastInvoiceDate, distributorLastInvoiceCases, distributorLastInvoiceDollars, cases, lbs, dollars, estOrderDate, pastDue, fiscalYear, ty_cases, ty_dollars, first_opr_sku_purchase_month, last_opr_purchase_month, last_confirmed_cases, last_confirmed_dollars, last_opr_sku_purchase, tibersoft_Id, cds_Id, distributor_Id, focus, objective_1, objective_2, objective_3, objective_4, objective_5 from tbl_EXPORT_Kellanova_Opportunity with (nolock) where ownerId = and ownertype = and eai_batchId = order by eai_lineNumber update tbl_EAI_InboundBatches set loadedColumns = where ownerId = and ownertype = and batchId =
Exported #lsnumberformat( _exportCount )# Opportunities from #dateformat(_startDate,"mm/dd/yyyy")# to #dateformat(_endDate,"mm/dd/yyyy")#