update ba set ba.externalProgramNumber = ba.agreementNumber, ba.ExternalProgramComponent = ba.agreementComponent, ba.agreementNumber = CASE WHEN CHARINDEX( '-', ba.agreementNumber ) > 0 THEN SUBSTRING( ba.agreementNumber, CHARINDEX( '-', ba.agreementNumber ) + 1, LEN( ba.agreementNumber ) ) ELSE ba.agreementNumber END from tbl_IMPORT_BlacksmithAgreements ba with (nolock) where ba.eai_batchId = and ba.ownerId = and ba.ownerType = select sum( case when coalesce( qualification, '' ) != 'Golden Contract' then 1 else 0 end ) qualification_errors, sum( case when coalesce( qualification_answer, '' ) not in ( 'YES', 'NO' ) then 1 else 0 end ) qualification_answer_errors from tbl_IMPORT_BlacksmithAgreements ba with (nolock) where ba.eai_batchId = and ba.ownerId = and ba.ownerType =

Qualification Value Errors: #qmd_qualification.qualification_errors#

Qualification Answer Value Errors: #qmd_qualification.qualification_answer_errors#

Qualification and Qualification_Answer must both be present.

 with consistencyCheck as ( select distinct agreementNumber, agreementVersion, count( distinct qualification ) qualificationCount, count( distinct qualification_answer ) qualificationAnswerCount from tbl_IMPORT_BlacksmithAgreements with (nolock) where eai_batchId = and ownerId = and ownerType = group by agreementNumber, agreementVersion ) select * from consistencyCheck where qualificationCount > 1 or qualificationAnswerCount > 1 order by agreementNumber select autoProcessLoadableAgreements from tbl_TPM_Settings with (nolock) where ownerId = and ownerType =

This is not currently supported for Rich Products until an agreement number conversion takes place

 select formatid, formatName from tbl_eai_inboundFIleformats with (nolock) where ownerid = and ownerType = and formatname = 'TPM_AGREEMENT_IMPORT_TXT' select formatid, formatName from tbl_eai_inboundFIleformats with (nolock) where ownerid = 0 and ownerType = 'MFR' and formatname = 'TPM_AGREEMENT_IMPORT_TXT' select formatid, formatName from tbl_eai_inboundFIleformats with (nolock) where ownerid = 0 and ownerType = '*' and formatname = 'TPM_AGREEMENT_IMPORT_TXT' select distinct stage.agreementType, stage.agreementComponent, stage.paidTo from tbl_IMPORT_BlackSmithAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and stage.fseContractId is null and nullif( stage.agreementComponent, '' ) is not null and nullif( stage.paidTo, '' ) is not null select requestTypeId from tbl_TPM_RequestTypes with (nolock) where ownerType = and ownerId = and requestType = update stage set fseRequestTypeId = from tbl_IMPORT_BlackSmithAgreements stage with (nolock) where stage.eai_batchid = and stage.ownerId = and stage.ownerType = and stage.agreementType = and stage.agreementComponent = and stage.paidTo = and stage.fseContractId is null update a set a.fseAgreementVariant = case when rt.primaryPartnerType = rt.payableTo then 'X' else 'Z' end + case when rt.requestModel = 'FIXED' then 'F' when rt.requestModel = 'INCENTIVE' then 'P' else 'A' end from tbl_IMPORT_BlacksmithAgreements a inner join tbl_TPM_RequestTypes rt on rt.requestTypeId = a.fseRequestTypeId and rt.ownerId = a.ownerId and rt.ownerType = a.ownerType where a.eai_batchid = and a.ownerId = and a.ownerType = and a.fseAgreementVariant is null select BenefiicaryType, rtrim( AgreementType ) + ' - ' + ltrim( AgreementComponent ) + ' - ' + ltrim( paidTo ) AgreementType, count( distinct agreementNumber ) as Agreements from tbl_IMPORT_BlackSmithAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and fseRequestTypeId is null group by BenefiicaryType, AgreementType, AgreementComponent, paidTo order by Agreements desc, BenefiicaryType, AgreementType select agreementNumber, agreementVersion, agreementName, AgreementStartDate, agreementEndDate, count( distinct BSACustomerNumber ) beneficiaries, count( distinct fseBeneficiaryId ) resolvedBeneficiaries from tbl_IMPORT_BlacksmithAgreements i with (nolock) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseContractId is null /* and exists ( select * from tbl_IMPORT_BlacksmithAgreements i2 with (nolock) where i2.eai_batchId = i.eai_batchId and i2.ownerId = i.ownerId and i2.ownerType = i.ownerType and i2.agreementNumber = i.agreementNumber and i2.agreementVersion = i.agreementVersion and i2.agreementName = i.agreementName and i2.fseBeneficiaryId is null ) */ group by agreementNumber, agreementVersion, agreementName, AgreementStartDate, agreementEndDate having count( distinct BSACustomerNumber ) > 1 or count( distinct fseBeneficiaryId ) > 1 order by agreementName update tbl_IMPORT_BlacksmithAgreements set fseInclude = 'N' where eai_batchId = and ownerId = and ownerType = and agreementNumber = and agreementVersion = and agreementName = and agreementStartDate = and agreementEndDate = select AgreementNumber,BenefiicaryType, rtrim( AgreementType ) + ' - ' + ltrim( AgreementComponent ) AgreementType, productCode from tbl_IMPORT_BlackSmithAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and (stage.paidTo is null or stage.paidTo = '') order by BenefiicaryType, AgreementType select AgreementNumber,BenefiicaryType, rtrim( AgreementType ) + ' - ' + ltrim( AgreementComponent ) AgreementType, productCode from tbl_IMPORT_BlackSmithAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and nullif( stage.agreementComponent, '' ) is null order by BenefiicaryType, AgreementType declare @agreementNumbers as table( agreementNUmber varchar(max), beneficiaries int ); insert into @agreementNumbers( agreementNumber, beneficiaries) select agreementnumber, count( distinct beneficiaryCustomerNbr ) beneficiaries from tbl_IMPORT_BlacksmithAgreements with (nolock) where ownerType = and ownerId = and eai_batchId = group by ownerId, ownerType, eai_batchId, agreementnumber having count( distinct beneficiaryCustomerNbr ) = 1; declare @beneficiaries as table( benefiicaryType varchar(max), beneficiaryCustomerNbr varchar(max), beneficiaryName varchar(max), agreementNumber varchar(max) ); insert into @beneficiaries ( benefiicaryType, beneficiaryCustomerNbr, beneficiaryName, agreementNumber ) select distinct a.benefiicaryType, a.beneficiaryCustomerNbr, a.beneficiaryName, a.agreementNumber from tbl_IMPORT_BlacksmithAgreements a with (nolock) where a.ownerType = and a.ownerId = and a.eai_batchid = and a.fseBeneficiaryId is null; select b.benefiicaryType, b.beneficiaryCustomerNbr, b.beneficiaryName, count( distinct b.agreementNumber ) agreements from @beneficiaries b inner join @agreementNumbers n on n.agreementNumber = b.agreementNumber group by benefiicaryType, beneficiaryCustomerNbr, beneficiaryName order by beneficiaryName declare @agreementNumbers as table( agreementNUmber varchar(max), beneficiaries int ); insert into @agreementNumbers( agreementNumber, beneficiaries) select agreementnumber, count( distinct beneficiaryCustomerNbr ) beneficiaries from tbl_IMPORT_BlacksmithAgreements with (nolock) where ownerType = and ownerId = and eai_batchId = group by ownerId, ownerType, eai_batchId, agreementnumber having count( distinct beneficiaryCustomerNbr ) = 1; declare @beneficiaries as table( benefiicaryType varchar(max), beneficiaryCustomerNbr varchar(max), beneficiaryName varchar(max), agreementNumber varchar(max) ); insert into @beneficiaries ( benefiicaryType, beneficiaryCustomerNbr, beneficiaryName, agreementNumber ) select distinct a.benefiicaryType, a.beneficiaryCustomerNbr, a.beneficiaryName, a.agreementNumber from tbl_IMPORT_BlacksmithAgreements a with (nolock) where a.ownerType = and a.ownerId = and a.eai_batchid = and a.fseBeneficiaryId is null; select b.benefiicaryType, b.beneficiaryCustomerNbr, b.beneficiaryName, count( distinct b.agreementNumber ) agreements from @beneficiaries b inner join @agreementNumbers n on n.agreementNumber = b.agreementNumber group by benefiicaryType, beneficiaryCustomerNbr, beneficiaryName order by beneficiaryName select a.benefiicaryType, a.beneficiaryCustomerNbr, a.beneficiaryName, count( distinct a.agreementNumber ) agreements from tbl_IMPORT_BlacksmithAgreements a with (nolock) where a.ownerType = and a.ownerId = and a.eai_batchid = and a.fseBeneficiaryId is null group by a.benefiicaryType, a.beneficiaryCustomerNbr, a.beneficiaryName order by count( distinct a.agreementNumber) desc ---> select benefiicaryType, beneficiaryCustomerNbr, beneficiaryName, count( distinct agreementNumber) agreements from qry_unknownBeneficiaries group by benefiicaryType, beneficiaryCustomerNbr, beneficiaryName ---> select a.benefiicaryType, a.BSACustomerNumber, a.BeneficiaryCustomerNbr, a.beneficiaryName, a.fseBeneficiaryStatusComment status, count( distinct a.agreementNumber ) agreements from tbl_IMPORT_BlacksmithAgreements a with (nolock) where a.ownerType = and a.ownerId = and a.eai_batchid = and a.fseBeneficiaryId is null group by a.benefiicaryType, a.BSACustomerNumber, a.beneficiaryName, a.BeneficiaryCustomerNbr, a.fseBeneficiaryStatusComment order by count( distinct a.agreementNumber) desc

Beneficiaries are resolved in the following order:

  1. Existing Agreement Version
  2. Existing Agreement witout Version
  3. Blacksmith Library and 1FS Mapping - Single Record
  4. Blacksmith Library and 1FS - Duplicates resolved by Beneficiary Customer Nbr
  5. Blacksmith Library and 1FS - Duplicates resolved by Independent Agreement Owners
  6. Blacksmith Library and 1FS - Duplicates resolved as Oldest Independent (lowest FSL OperatorID)
Those listed below are either not in the Blacksmith library or multiple records exists that cannot be resolved using the above rules.

select distinct '?' as agreement_owner_number, '?' as agreement_owner_name, 0 as agreement_owner_1fsid, BenefiicaryType, case when fseAgreementVariant is not null then agreementNumber + '.' + fseAgreementVariant else agreementNumber end as agreement_number, agreementVersion as agreement_version, format( agreementStartDate, 'MM/dd/yyyy' ) as agreement_start_date, format( agreementEndDate, 'MM/dd/yyyy') as agreement_end_date, agreementName as agreement_name, externalProgramNumber, externalProgramComponent, BeneficiaryCustomerNbr as externalCustomerOperatorNumber, BSACustomerNumber as externalTPMECustomerNumber, coalesce( rt.requestType, agreementType + ' - ' + agreementComponent + ' - ' + paidTo) as agreement_type, 1 as sku_level, '99999' as sku, 'Misc Product Code' as sku_description, format( coalesce( effectiveStartDate, agreementStartDate ), 'MM/dd/yyyy' ) as agreement_effective_start_date, format( coalesce( effectiveEndDate, agreementEndDate ), 'MM/dd/yyyy') as agreement_effective_end_date, 0 as est_annual_volume, 0 as rate, case when UOM = 'cases' then 'CASE' when UOM = 'pounds' then 'LB' when UOM = 'percentages' then 'PCT' else 'CASE' end rate_uom, 'MONTHLY' as claim_frequency, 'N' as exclusion, 'Y' as external_agreement from tbl_IMPORT_BlacksmithAgreements a with (nolock) left outer join tbl_TPM_RequestTypes rt with (nolock) on rt.requestTypeId = a.fseRequestTypeId and rt.ownerid = a.ownerid and rt.ownerType = a.ownerType where ( a.fseBeneficiaryId is null or a.fseInclude = 'N' ) and a.eai_batchId = and a.ownerId = and a.ownerType = order by agreement_name
Agreements where Primary Beneficiary could not be determined. Please contact clients to determine approriate Beneficiaries:
Unknown Beneficiary Agreements - #numberformat(qry_getUnknownAgreements.recordCount)# records


#_unkownAgreements# with opr as ( select distinct 'OPR' as partnerType, opr.operatorid as partnerId, opr.companyname as partnerName, COALESCE( nullif(opr.directCustomerNbr,'') , coalesce( nullif( opr.mfrCustNum, '' ), '?')) partnerNbr, opr.fsl_choId as oneFSId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_IMPORT_BlackSmithAgreements stage with (nolock) on stage.fseBeneficiaryId = opr.operatorid and stage.ownerId = opr.ownerId and stage.ownerType = opr.fsltablecode where stage.fseCatalogType = 'SKU' and stage.eai_batchId = and stage.ownerid = and stage.ownerType = and stage.fseBeneficiaryType = 'OPR' and opr.fsl_choId NOT IN ( ) ), cdr as ( select distinct 'CDR' as partnerType, cdr.cdr_recordID as partnerId, cdr.cdr_dstName as partnerName, cdr.cdr_dstCode partnerNbr, cdr.fsl_dstId oneFSId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_IMPORT_BlackSmithAgreements stage with (nolock) on stage.fseBeneficiaryId = cdr.cdr_recordID and stage.ownerId = cdr.ownerId and stage.ownerType = cdr.fsl_tablecode where stage.fseCatalogType = 'SKU' and stage.eai_batchId = and stage.ownerid = and stage.ownerType = and stage.fseBeneficiaryType = 'CDR' and 1 = 2 ), sku as ( select distinct stage.fseCatalogId as catalogId, stage.fseCatalogType as catalogType, s.sku as catalogCode, s.skuDesc as catalogDescription, 1 as catalogLevel from tbl_PRD_SKUS s with (nolock) inner join tbl_IMPORT_BlackSmithAgreements stage with (nolock) on stage.fseCatalogId = s.skuId and stage.ownerId = s.ownerId and stage.ownerType = s.fsl_tablecode where stage.fseCatalogType = 'SKU' and stage.eai_batchId = and stage.ownerid = and stage.ownerType = ), prd as ( select distinct stage.fseCatalogId as catalogId, stage.fseCatalogType as catalogType, p.product as catalogCode, p.shortDesc as catalogDescription, 2 as catalogLevel from tbl_PRD_Products p with (nolock) inner join tbl_IMPORT_BlackSmithAgreements stage with (nolock) on stage.fseCatalogId = p.prodid and stage.ownerId = p.ownerId and stage.ownerType = p.fsl_tablecode where stage.fseCatalogType = 'PRD' and stage.eai_batchId = and stage.ownerid = and stage.ownerType = ), cat as ( select distinct stage.fseCatalogId as catalogId, stage.fseCatalogType as catalogType, c.catnum as catalogCode, c.category as catalogDescription, 3 as catalogLevel from tbl_PRD_Categories c with (nolock) inner join tbl_IMPORT_BlackSmithAgreements stage with (nolock) on stage.fseCatalogId = c.categoryid and stage.ownerId = c.ownerId and stage.ownerType = c.fsl_tablecode where stage.fseCatalogType = 'CAT' and stage.eai_batchId = and stage.ownerid = and stage.ownerType = ), lin as ( select distinct stage.fseCatalogId as catalogId, stage.fseCatalogType as catalogType, pl.productCode as catalogCode, pl.productLine as catalogDescription, 4 as catalogLevel from tbl_MFR_ProductLines pl with (nolock) inner join tbl_IMPORT_BlackSmithAgreements stage with (nolock) on stage.fseCatalogId = pl.prodLine_id and stage.ownerId = pl.mfr_id and stage.ownerType = 'MFR' where stage.fseCatalogType = 'LIN' and stage.eai_batchId = and stage.ownerid = and stage.ownerType = ), allProducts as ( select catalogId, catalogType, catalogCode, catalogDescription, catalogLevel from sku union all select catalogId, catalogType, catalogCode, catalogDescription, catalogLevel from prd union all select catalogId, catalogType, catalogCode, catalogDescription, catalogLevel from cat union all select catalogId, catalogType, catalogCode, catalogDescription, catalogLevel from lin ), allPartners as ( select partnerType, partnerId, partnerName, partnerNbr, oneFSId from opr union all select partnerType, partnerId, partnerName, partnerNbr, oneFSId from cdr ) select distinct case when stage.fseAgreementVariant is not null then stage.agreementNumber + '.' + fseAgreementVariant else stage.agreementNumber end as agreement_number, AgreementVersion as agreement_version, format(AgreementCreateDate,'MM/dd/yyyy') as agreement_create_date, format(AgreementUpdateDate,'MM/dd/yyyy') as agreement_update_date, BenefiicaryType, partnerNbr as agreement_owner_number, case when Qualification = 'Golden Contract' AND Qualification_Answer = 'Yes' then 'Y' else 'N' end as CountThisAgreement, case when allPartners.partnerType = 'OPR' then allPartners.partnerName + ' {OPR:' + convert( varchar, allPartners.partnerId ) + '}' else allPartners.partnerName end as agreement_owner_name, rt.requestType as agreement_type, AgreementName as agreement_name, format(AgreementStartDate,'MM/dd/yyyy') as agreement_start_date, format(AgreementEndDate,'MM/dd/yyyy') as agreement_end_date, stage.externalProgramNumber, stage.externalProgramComponent, stage.BeneficiaryCustomerNbr as externalCustomerOperatorNumber, stage.BSACustomerNumber as externalTPMECustomerNumber, format(EffectiveStartDate,'MM/dd/yyyy') as agreement_effective_start_date, format(EffectiveEndDate,'MM/dd/yyyy') as agreement_effective_end_date, 'MONTHLY' as claim_frequency, ProductExclusion as exclusion, allProducts.catalogLevel as sku_level, allProducts.catalogCode as sku, allProducts.catalogDescription as sku_description, case when UOM = 'cases' then 'CASE' when UOM = 'pounds' then 'LB' when UOM = 'percentages' then 'PCT' else 'CASE' end rate_uom, 0 as est_annual_volume, case when UOM = 'percentages' then DiscountBasis * 100.0 else DiscountBasis end as rate, allPartners.oneFSId as agreement_owner_1fsId, 'Y' external_agreement from tbl_IMPORT_BlackSmithAgreements stage with (nolock) inner join tbl_TPM_RequestTypes rt with (nolock) on rt.requestTypeId = stage.fseRequestTypeId and rt.ownerType = and rt.ownerid = inner join allProducts on allProducts.catalogId = stage.fseCatalogId and allProducts.catalogType = stage.fseCatalogType inner join allPartners on allPartners.partnerid = stage.fseBeneficiaryId and allPartners.partnerType = stage.fseBeneficiaryType where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and stage.fseInclude = 'Y' and (fseBeneficiaryId is not null and fseRequestTypeId is not null) order by agreement_number, sku_level, sku
Agreements that are skipped: Agreements that can be loaded using TPM_AGREEMENT_IMPORT_TXT:
Created batch #_autoProcessAgreementsUploadResult.batchId#Not Loaded
SkippedLoadable Agreements - #numberformat(qry_getAgreementImportData.recordCount)# records


#_newAgreements#