Disabled for now

 select autoProcessLoadableAgreements from tbl_TPM_Settings with (nolock) where ownerId = and ownerType = 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' delete from tbl_IMPORT_GoSimpleAgreements where eai_batchid = and ownerType = and ownerId = and eai_lineNumber < 0 with newSource as ( select ownerType,ownerId,eai_batchId,eai_lineNumber * -1 as eai_lineNumber, Contractee_Company_Name,Contractee_Company_Number,Internal_Company_ID,Contractee_Company_Type, Contract_Version_ID,Contract_Name,Contract_Status, Contract_Type,Contract_Start_Date,Contract_End_Date,Template_Description, Contract_Lumpsum_Type,Contract_Lumpsum_Description,Contract_Lumpsum_Amount, Contract_Product_Number,Contract_Product_Name, Direct_Percent,Direct_Price,Direct_Rate, Deviated_Percent,Deviated_Price,Deviated_Rate, Claim_Total, template_description as agreementType, '#_components[_component].label#' as agreementComponent, #_component# as discountBasis, Contract_UOM from tbl_IMPORT_GoSimpleAgreements with (nolock) where eai_batchid = and ownerType = and ownerId = and #_component# != 0 and Contractee_Company_Type = 'LOCATION' ) insert into tbl_IMPORT_GoSimpleAgreements ( ownerType,ownerId,eai_batchId,eai_lineNumber, Contractee_Company_Name,Contractee_Company_Number,Internal_Company_ID,Contractee_Company_Type, Contract_Version_ID,Contract_Name,Contract_Status, Contract_Type,Contract_Start_Date,Contract_End_Date,Template_Description, Contract_Lumpsum_Type,Contract_Lumpsum_Description,Contract_Lumpsum_Amount, Contract_Product_Number,Contract_Product_Name, Direct_Percent,Direct_Price,Direct_Rate, Deviated_Percent,Deviated_Price,Deviated_Rate, Claim_Total, agreementType, agreementComponent, discountBasis, Contract_UOM ) select ownerType,ownerId,eai_batchId,eai_lineNumber, Contractee_Company_Name,Contractee_Company_Number,Internal_Company_ID,Contractee_Company_Type, Contract_Version_ID,Contract_Name,Contract_Status, Contract_Type,Contract_Start_Date,Contract_End_Date,Template_Description, Contract_Lumpsum_Type,Contract_Lumpsum_Description,Contract_Lumpsum_Amount, Contract_Product_Number,Contract_Product_Name, Direct_Percent,Direct_Price,Direct_Rate, Deviated_Percent,Deviated_Price,Deviated_Rate, Claim_Total, agreementType, agreementComponent, discountBasis, contract_UOM from newSource select distinct contract_version_id from tbl_IMPORT_GoSimpleAgreements with (nolock) where ownerType = and ownerId = and eai_batchid = update tbl_IMPORT_GoSimpleAGreements set agreementNumber = , agreementVersion = where ownerType = and ownerId = and eai_batchid = and contract_version_id = select distinct stage.agreementType, stage.agreementComponent from tbl_IMPORT_GoSimpleAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and stage.agreementType is not null select requestTypeId from tbl_TPM_RequestTypes with (nolock) where ownerType = and ownerId = and requestType = update stage set fseRequestTypeId = from tbl_IMPORT_GoSimpleAgreements stage with (nolock) where stage.eai_batchid = and stage.ownerId = and stage.ownerType = and stage.agreementType = and stage.agreementComponent = 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_GoSimpleAgreements 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 rtrim( AgreementType ) + ' - ' + ltrim( AgreementComponent ) AgreementType, count( distinct agreementNumber ) as Agreements from tbl_IMPORT_GoSimpleAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and fseRequestTypeId is null and agreementType is not null group by AgreementType, AgreementComponent order by Agreements desc, AgreementType select distinct Contractee_Company_Type, Internal_Company_ID, Contractee_Company_Name, fseBeneficiaryStatusComment as status, count( distinct contract_version_id ) agreements, count(*) as Lines, min( abs( eai_lineNumber )) firstLine from tbl_IMPORT_GoSimpleAgreements stage with (nolock) where stage.ownerType = and stage.ownerId = and stage.eai_batchid = and fseBeneficiaryId is null and Contractee_Company_Type = 'LOCATION' and agreementType is not null group by Contractee_Company_Type, Internal_Company_ID, Contractee_Company_Name, fseBeneficiaryStatusComment order by lines desc, Contractee_Company_Type, Contractee_Company_Name

Beneficiaries are resolved in the following order:

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

Agreements that can be loaded using TPM_AGREEMENT_IMPORT_TXT:
Loadable Agreements - #numberformat(qmd_agreementSource.recordCount)# records
Created batch #_autoProcessAgreementsUploadResult.batchId#Not Loaded


#_newAgreements#