Full claim validation not been competed for accounts where data will not be imported already approved. UPDATE tmp SET tmp.territory_ID = t.territoryID FROM tbl_TER_Territories t INNER JOIN tbl_TER_ZipCodeLink zcl ON (t.TerritoryID = zcl.TerritoryID) INNER JOIN tbl_UT_ZipCodes_3D z3d ON (zcl.Zip3D_id = z3d.Zip3D_id) INNER JOIN tbl_IMPORT_CROREBATES tmp ON ( left(tmp.company_zip,3) = z3d.ZipCode_3d) WHERE t.ownerid = AND t.fsl_tableCode = AND tmp.eai_batchid = AND nullif(tmp.company_zip,'') IS NOT NULL AND nullif(tmp.territory_ID,'') IS NULL

Step 1

Created batch #variables.operator_batchId#: BASE_OPERATORS_TXT

#_resultsText#

Step 2

Batch #qry_getBatchIDs.cycle_batchID_1#: BASE_OPERATORS_TXT Completed

SELECT cycle_batchid_1 batchid FROM tbl_IMPORT_CroRebates WHERE eai_batchid = AND eai_lineNumber = 1; UPDATE cro SET cro.fseOperatorID = opr.fseOperatorID FROM tbl_IMPORT_CroRebates cro INNER JOIN tbl_IMPORT_Operators opr ON cro.company_id = opr.company_id WHERE cro.eai_batchid = AND opr.eai_batchid = AND cro.fseOperatorID IS NULL AND opr.fseOperatorID IS NOT NULL AND nullif(cro.company_id,'') IS NOT NULL UPDATE cro SET cro.fseOperatorID = opr.fseOperatorID FROM tbl_IMPORT_CroRebates cro INNER JOIN tbl_IMPORT_Operators opr ON (cro.Company_Name = opr.Company_Name AND cro.Company_Address1 = opr.Company_Address1 AND cro.Company_City = opr.Company_City) WHERE cro.eai_batchid = AND opr.eai_batchid = AND cro.fseOperatorID IS NULL AND opr.fseOperatorID IS NOT NULL UPDATE z SET z.rebate_campaignID = c.campaignID FROM tbl_IMPORT_CroRebates z INNER JOIN tbl_CRO_Campaigns c ON (c.title = z.rebate_campaign_title) WHERE z.eai_batchid = AND nullif(z.rebate_campaign_title,'') IS NOT NULL AND c.ownerid = AND c.ownerType = UPDATE z SET z.rebate_campaignID = c.campaignID FROM tbl_IMPORT_CroRebates z INNER JOIN tbl_CRO_Campaigns c ON (c.ProgramCode = z.rebate_campaign_code) WHERE z.eai_batchid = AND nullif(z.rebate_campaign_code,'') IS NOT NULL AND c.ownerid = AND c.ownerType = AND z.rebate_campaignID IS NULL UPDATE tmp SET tmp.fseOperatorId = tmp2.fseOperatorId, tmp.fseContactID = tmp2.fseContactID FROM tbl_IMPORT_CroRebates tmp INNER JOIN tbl_IMPORT_Operators tmp2 ON (tmp.eai_linenumber = tmp2.external_id) WHERE tmp.eai_batchid = AND tmp2.eai_batchid = UPDATE tmp SET tmp.fseOperatorId = tmp2.fseOperatorId, tmp.fseContactID = tmp2.fseContactID FROM tbl_IMPORT_CroRebates tmp INNER JOIN tbl_IMPORT_Operators tmp2 ON (tmp.company_name = tmp2.company_name AND tmp.contact_first_name = tmp2.contact_first_name AND tmp.contact_last_name = tmp2.contact_last_name) WHERE tmp.eai_batchid = AND tmp2.eai_batchid = AND tmp.fseOperatorId IS NULL UPDATE tmp SET tmp.validated = CASE WHEN cp.productCode IS NULL THEN 0 ELSE 1 END, tmp.validation_notes = CASE WHEN cp.productCode IS NULL THEN 'Product not found in Campaign' ELSE null END FROM tbl_IMPORT_CroRebates tmp LEFT OUTER JOIN tbl_CRO_CampaignProducts cp ON (tmp.rebate_campaignID = cp.campaignid AND tmp.product_code = cp.productCode) WHERE eai_batchid = UPDATE z SET z.unit_value = cp.unitvalue FROM tbl_IMPORT_CroRebates z INNER JOIN tbl_CRO_Campaigns c ON (c.ProgramCode = z.rebate_campaign_code) INNER JOIN tbl_CRO_CampaignProducts cp ON (cp.campaignID = c.campaignID AND cp.productCode = z.product_code) WHERE z.eai_batchid = AND nullif(z.rebate_campaign_code,'') IS NOT NULL AND c.ownerid = AND c.ownerType = AND z.rebate_campaignID IS NOT NULL SELECT eai_linenumber, company_id, company_name, contact_salutation, contact_last_name, contact_first_name, contact_full_name, fseOperatorId , fseContactId, rebate_campaignID, rebate_campaign_title, rebate_notes, rebate_campaign_code, invoice, invoice_date, product_code , product_description, unit_value, submitted_cases, approved_cases, company_dst1_fslid, c.title, tmp.company_dst1_name FROM tbl_IMPORT_CroRebates tmp INNER JOIN tbl_CRO_Campaigns c ON (c.campaignID = tmp.rebate_campaignID) WHERE eai_batchid = AND rebate_campaignID IS NOT NULL AND validated = 1 GROUP BY company_id, company_name, contact_salutation, contact_last_name, contact_first_name, contact_full_name, fseOperatorId, fseContactId , rebate_campaignID, rebate_campaign_title, rebate_notes, rebate_campaign_code, invoice, invoice_date, product_code, product_description , unit_value, submitted_cases, approved_cases, company_dst1_fslid, eai_linenumber, c.title, tmp.company_dst1_name, company_dst1_fslID ORDER BY FseOperatorID, Invoice, Invoice_Date, rebate_campaignID INSERT INTO tbl_CRO_Claims ( ownerid, ownerType, campaignid, status, fspro_userid, OrgID, OrgType, firstname, lastname, organization, notes, submitDate, datesent, distributorid, distributorName ) VALUES( , , , , , , , , , , , GetDate(), GetDate(), , ) SELECT ClaimID = @@IDENTITY UPDATE tbl_IMPORT_CroRebates SET rebate_claimID = WHERE eai_batchid = AND eai_linenumber = INSERT INTO tbl_CRM_Interactions(OwnerID ,OwnerType ,InteractionDate ,SourceType ,ContactID ,ParticipantID ,SourceID ,PartnerId ,PartnerType ,Comment ,Notes,recordSource, ApprovalStatus, ApprovalDate, disposition, dispositionDate, CloseComment) VALUES(, , getdate(), , , , , , , , '', 'CRO REBATES (M108)', 'A', getDate(), 'S', getDate(), 'Purchase confirmed via Rebate Submission [ClaimID:#qry_getRebateData.rebate_campaignID#]') SELECT InteractionID = @@IDENTITY SET NOCOUNT OFF INSERT INTO tbl_CRO_ClaimDetail ( claimid, invoice, invoiceDate, productLevel, productCode, productDesc, sub_quantity, unitvalue, apv_quantity) VALUES( , , , , , , , , ) SELECT claimDetailID = @@IDENTITY UPDATE tbl_IMPORT_CroRebates SET rebate_claimdetailID = WHERE eai_batchid = AND eai_linenumber = SELECT s.skuid FROM tbl_PRD_Skus s WHERE s.Ownerid = AND s.FSL_TableCode = AND s.sku = INSERT INTO tbl_CRM_InteractionSaleDetails(interactionid, ownerid, ownerType, SaleSubjectID, SaleSubjectType, SaleQty, SaleConfirmedDate) VALUES (, , , , 'SKU', , getDate() ) SELECT company_id, company_name, contact_first_name, contact_last_name, rebate_campaign_code, rebate_campaign_title, product_code, product_description, submitted_cases FROM tbl_IMPORT_CroRebates WHERE eai_batchid = AND rebate_campaignID IS NULL SELECT company_id, company_name, contact_first_name, contact_last_name, rebate_campaign_code, rebate_campaign_title, product_code, product_description, submitted_cases FROM tbl_IMPORT_CroRebates WHERE eai_batchid = AND rebate_campaignID IS NOT NULL SELECT company_id, company_name, contact_first_name, contact_last_name, rebate_campaign_code, rebate_campaign_title, product_code, product_description, submitted_cases FROM tbl_IMPORT_CroRebates WHERE eai_batchid = AND validated = 0

NO MATCHING REBATE CAMPAIGN

Company ID	Company Name	Contact Name	Rebate Campaign Code	Rebate Campaign Title	SKU	Description	Submitted Cases
#qry_getUnmappedClaims.company_id#	#qry_getUnmappedClaims.company_name#	#qry_getUnmappedClaims.contact_first_name# #qry_getUnmappedClaims.contact_last_name#	#qry_getUnmappedClaims.rebate_campaign_code#	#qry_getUnmappedClaims.rebate_campaign_title#	#qry_getUnmappedClaims.product_code#	#qry_getUnmappedClaims.product_description#	#qry_getUnmappedClaims.submitted_cases#

IMPORTED REBATE CLAIMS

Company ID	Company Name	Contact Name	Rebate Campaign Code	Rebate Campaign Title	SKU	Description	Submitted Cases
#qry_getMappedClaims.company_id#	#qry_getMappedClaims.company_name#	#qry_getMappedClaims.contact_first_name# #qry_getMappedClaims.contact_last_name#	#qry_getMappedClaims.rebate_campaign_code#	#qry_getMappedClaims.rebate_campaign_title#	#qry_getMappedClaims.product_code#	#qry_getMappedClaims.product_description#	#qry_getMappedClaims.submitted_cases#

INVALID SKUS

Company ID	Company Name	Contact Name	Rebate Campaign Code	Rebate Campaign Title	SKU	Description	Submitted Cases
#qry_invalidSkus.company_id#	#qry_invalidSkus.company_name#	#qry_invalidSkus.contact_first_name# #qry_invalidSkus.contact_last_name#	#qry_invalidSkus.rebate_campaign_code#	#qry_invalidSkus.rebate_campaign_title#	#qry_invalidSkus.product_code#	#qry_invalidSkus.product_description#	#qry_invalidSkus.submitted_cases#

#_resultTxt# UPDATE tmp SET tmp.validated = CASE WHEN cp.productCode IS NULL THEN 0 ELSE 1 END, tmp.validation_notes = CASE WHEN cp.productCode IS NULL THEN 'Product not found in Campaign' ELSE null END, tmp.unit_value = cp.unitvalue FROM tbl_IMPORT_CroRebates tmp LEFT OUTER JOIN tbl_CRO_CampaignProducts cp ON (tmp.rebate_campaignID = cp.campaignid AND tmp.product_code = cp.productCode) WHERE eai_batchid = SELECT rebate_claimID, eai_linenumber, fseOperatorid FROM tbl_IMPORT_CroRebates tmp WHERE eai_batchid = AND rebate_claimID IS NOT NULL ORDER BY rebate_claimID SELECT eai_lineNumber, company_id, fseOperatorId, fseContactId, rebate_campaignID, tmp.invoice, invoice_date, product_code, product_description, unit_value, submitted_cases, approved_cases, Count(c.claimID) ClaimSubmissions, sum(tmp.unit_value * tmp.submitted_cases) CurrentClaimTotal, sum(cd.apv_quantity * unitvalue) as TotalClaimDollarsSubmitted, cmp.claimsPerProgram, cmp.maxClaimRate, cmp.maxProgramRate, tmp.submitClaimTotal, tmp.submitCampaignTotal FROM tbl_IMPORT_CroRebates tmp LEFT OUTER JOIN tbl_CRO_Claims c ON (tmp.rebate_campaignID = c.campaignID AND tmp.fseContactID = c.fspro_userID AND tmp.fseOperatorID = c.orgID AND c.orgType = 'OPR') INNER JOIN tbl_CRO_ClaimDetail cd ON (c.claimid = cd.claimID) INNER JOIN tbl_CRO_Campaigns cmp ON (cmp.campaignid = tmp.rebate_campaignid) WHERE eai_batchid = AND rebate_claimID = AND c.orgID = AND validated <> 0 GROUP BY company_id, fseOperatorId, fseContactId, rebate_campaignID, tmp.invoice, tmp.invoice_date, product_code, product_description, unit_value, submitted_cases, approved_cases, cmp.claimsPerProgram, cmp.maxClaimRate, cmp.maxProgramRate, eai_lineNumber, tmp.submitClaimTotal, tmp.submitCampaignTotal ORDER BY rebate_claimID, FseOperatorID, tmp.Invoice, Invoice_Date, rebate_campaignID --->