select distinct source_type, alias, alias_desc, program_id, program_desc, '' contractName, '' assignedContractNbr, 0 contractId, 0 requestId, '' contractRef from tbl_IMPORT_ContractReferences with (nolock) where ownerId = and ownerType = and eai_batchId = and source_type like '%EDI%' select r.requestId, c.assignedContractNbr, c.contractName, c.contractId from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts c with (nolock) on c.ownerId = r.ownerId and c.ownerType = r.ownerType and c.contractId = r.contractId where c.ownerId = and c.ownerType = and c.assignedContractNbr = and r.chainSequence = 1 and r.payableTo = 'CDR' update tbl_IMPORT_ContractReferences set fseContractId = , fseRequestId = , fseContractName = , fseAssignedContractNbr = , fseContractRef = where ownerId = and ownerType = and eai_batchId = and source_type = and alias = and alias_desc = and program_id = and program_desc = select * from qmd_aliases where requestId > 0 select * from qmd_aliases where requestId = 0 select cdr_recordId from tbl_CDR_Distributors with (nolock) where fsl_dstId = and cdr_dstCompanyType = 'P' and fsl_mapPriority = 1 and ownerId = and fsl_tablecode = select cr.*, ct.contractId, ct.assignedContractNbr, ct.contractName from tbl_TPM_ContractReferences cr with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = cr.ownerId and r.ownerType = cr.ownerType and r.requestId = cr.requestId inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId where cr.ownerId = and cr.ownerType = and cr.contractRef = and cr.partnerId = and cr.partnerType = insert into tbl_TPM_ContractReferences ( ownerId, ownerType, partnerId, partnerType, requestId, contractRef, confirmed, confirmDate, confirmUserId, createDate, createUserId ) values ( , , , , , , 'N', null, null, , 0 ) update tbl_TPM_ContractReferences set confirmed = 'N', confirmDate = null, confirmUserId = null where ownerId = and ownerType = and contractRef = and partnerId = and partnerType = and requestId =

#lsnumberformat( _newReferences )# New Aliases Added

select source_type, alias, alias_desc, program_id, program_desc, assignedContractNbr, contractName, conflictAssignedContractNbr, conflictContractName from qmd_resolvedAliases where conflictContractId > 0 select distinct cdr.cdr_dstName, cr.contractRef, ct.assignedContractNbr, ct.contractName, r.payableTo, ( select count(*) from tbl_TPM_Claims c inner join tbl_TPM_CLaimContracts cc on cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimID where cc.requestId = r.requestId and cc.ownerId = r.ownerId and cc.ownerType = r.ownerType and c.partnerType <> cr.partnerType ) claims from tbl_TPM_requests r with (nolock) inner join tbl_TPM_ContractReferences cr with (nolock) on cr.requestId = r.requestId and cr.ownerId = r.ownerId and cr.ownerType = r.ownerType inner join tbl_TPM_Contracts ct on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = cr.ownerId and cdr.fsl_tablecode = cr.ownerType and cdr.cdr_recordId = cr.partnerId where r.ownerId = and r.ownerType = and r.payableTo <> cr.partnerType and cr.partnerType = 'CDR' order by cdr_dstName select distinct c.claimId, cc.contractNbr from tbl_TPM_ClaimContracts cc with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.ownerId = cc.ownerId and c.ownerType = cc.ownerType and c.claimId = cc.claimId inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.claimId = cc.claimId and pod.contractNbr = cc.contractNbr where c.ownerId = and c.ownerType = and c.edi = 'Y' and c.ediReady = 'Y' and nullif( cc.requestId, 0 ) is null and exists ( select * from tbl_TPM_COntractReferences cr with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId= cr.ownerId and r.ownerType = cr.ownerType and r.requestId = cr.requestId where cr.contractRef = cc.contractNbr and cr.ownerId = cc.ownerId and cr.ownerType = cc.ownerType ) order by c.claimId

Updated #lsnumberformat( _bindCount )# EDI Sub-Claims

select distinct 'program' as Type, cc.contractNbr [Alias], c.claimDescription [Alias_Desc], '' as Program_ID, '' as [Program_Desc], case when cdr.fsl_dstId = 4036 or pcdr.fsl_dstId = 4036 then 'SYSCO EDI' else 'USFS EDI' end as [Source_Type] from tbl_TPM_ClaimContracts cc with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.edi = 'Y' and c.partnerType = 'CDR' and c.ediReady = 'Y' and c.ownerId = cc.ownerId and c.ownerType= cc.ownerType inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = c.ownerId and cdr.fsl_tablecode = c.ownertype and cdr.cdr_recordId = c.partnerId left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId and dst.dstParentCo > 0 left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode and pcdr.fsl_dstId = dst.dstParentCo where cc.contractNbr <> '$MISCELLANEOUS' and nullif( cc.requestId, 0 ) is null and cc.ownerId = and cc.ownerType = order by [Source_Type], [Alias_Desc]