and year( claimPeriodEnd ) >= order by claimYear, claimMonth

#claimYear# #claimMonth# - #listLen( _claimIds )# claims update

---> select companyName from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and operatorId = select * from tbl_EAI_inboundFileFormats with (nolock) where formatName = and ownerId = and ownerType = with updatedClaims as ( select distinct c.claimId from tbl_TPM_CLaims c with (nolock) where ownerType = and ownerId = -- and claimId in ( select claimId from batchHistory ) and claimId in ( ) ) batchHistory as ( select distinct linkId as claimId from tbl_EAI_BatchHistory with (nolock) where ownerType = and ownerId = and linkType = 'TPC' and reference = '$RPC_1FS_Trade_DataExport' ) select c.claimId, '#_displayClaimIdStatus#' display_claimId_status, c.claimId as display_claimId, '' as oprSector1, '' as oprSector2, c.claimPeriodStart InvoiceStart, c.claimPeriodEnd InvoiceEnd, unit.operatorid fsl_operatorId, case when nullif(pod.operatorid,0) is null then pod.operatorName else unit.companyName end as unit_companyname, case when nullif(pod.operatorid,0) is null then pod.operatorStreet else unit.address end as address, case when nullif(pod.operatorid,0) is null then '' else unit.address2 end as address2, case when nullif(pod.operatorid,0) is null then pod.operatorCity else unit.city end as city, case when nullif(pod.operatorid,0) is null then pod.operatorState else unit.state end as state, case when nullif(pod.operatorid,0) is null then pod.operatorZipCode else unit.zipCode end as zipCode, pod.itemCost as unitCost, case when pod.itemQty > 0 then cast( coalesce( pod.effectiveItemCost, pod.itemCost )/pod.itemQty as decimal(10,2) ) else 0 end as UnitPrice, '' as UnitofMeasure, cast( pod.itemQty * pod.itemCost as decimal(10,2) ) as totalDollars, pod.itemCode productCode, pod.itemDescription as productDesc, round( pod.itemQty, 2) qty, pod.qualifiedRate Rate, (pod.itemQty * pod.qualifiedRate) AS RowTotal, coalesce(o.companyName, d.cdr_dstname) as companyName, case when nullif(pod.cdrRecordId,0) is null then pod.distributorName else cdr.cdr_dstName end as cdr_dstName, cdr_parent.cdr_dstName as Distributor, cs.customerSku as Distributor_Sku, pod.operatorAffiliateNbr as oprunitID, pod.distributorName as Distributor_Branch, '' as oprTerritoryCode, '' as transactionDate, pod.distributorCode as DistributorId, -- pod.distributorCode as DistributorId, cdr_parent.cdr_dstCode as DistributorParentID, pod.itemDescription as ProductDescSubmitted, cdr_parent.cdr_dstname cdr_dstParentName, cdr_parent.cdr_dstCode, cdr.cdr_dstname cdr_dstBranchName, cdr.cdr_dstCode cdr_dstBranchCode, unit.fsl_choId, cdr.fsl_dstId, ct.assignedContractNbr, ct.contractName, c.claimDescription, coalesce( ob.companyName, db.cdr_dstName ) as beneficiary, ob.fsl_choId as beneficiary_1fs_ID from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType left outer join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerid = r.ownerid and ct.ownerType = r.ownerType and r.contractId = ct.contractId inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.claimId = cc.claimId and pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr left outer join tbl_OPR_ClientOperators o with (nolock) on o.OperatorID = c.partnerId and 'OPR' = c.partnerType and o.ownerId = c.ownerId and o.fsltablecode = c.ownerType left outer join tbl_CDR_Distributors d with (nolock) on d.cdr_recordId = c.partnerId and 'CDR' = c.partnerType and d.ownerId = c.ownerId and d.fsl_tablecode = c.ownerType left outer join tbl_OPR_ClientOperators ob with (nolock) on ob.OperatorID = ct.partnerId and 'OPR' = ct.partnerType and ob.ownerId = ct.ownerId and ob.fsltablecode = ct.ownerType left outer join tbl_CDR_Distributors db with (nolock) on db.cdr_recordId = ct.partnerId and 'CDR' = ct.partnerType and d.ownerId = ct.ownerId and d.fsl_tablecode = ct.ownerType inner join tbl_OPR_ClientOperators unit with (nolock) on unit.operatorid = pod.operatorid and unit.ownerId = pod.ownerId and unit.fsltablecode = pod.ownerType inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = unit.fsl_choId and cho.cho_type in ( 'U', 'I' ) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.cdrRecordId and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType left outer join tbl_DST_Distributors fsl1_branch with (nolock) ON (cdr.fsl_DstID = fsl1_branch.dstID) left outer join tbl_DST_Distributors fsl2_parent with (nolock) ON (fsl1_branch.dstParentCo = fsl2_parent.dstid) left outer join tbl_CDR_Distributors cdr_parent with (nolock) ON fsl2_parent.dstid = cdr_parent.fsl_dstID and cdr_parent.fsl_tableCode = cdr.fsl_tablecode and cdr_parent.ownerid = cdr.ownerId left outer join tbl_PRD_CustomerSkus cs with (nolock) on cs.skuid = pod.skuid and cs.customerId = cdr.cdr_recordId and cs.customerType IN ('DST','PDST') where c.ownerType = and c.ownerid = and ct.partnerId = and ct.partnerType = 'OPR' and c.claimType IN ('B','D') and c.historical = and ( c.historical = 'N' or ( c.historical = 'Y' and c.claimType = 'D' and c.claimDescription = 'BSA' ) ) and r.volumegenerator = 'Y' ---> and cc.reconciled = 'Y' and pod.status != 'DENIED' and year( c.claimPeriodEnd ) = and month( c.claimPeriodEnd ) in () and year( c.claimPeriodEnd ) >= 2018 and year( c.claimPeriodEnd ) >= 2019 and cc.claimId not in ( select claimId from batchHistory ) and cc.claimId in ( select claimId from updatedClaims ) and cc.reconciledDate >= '2020-12-01 05:50:00.000' order by c.claimId desc, unit.companyName, RowTotal desc, Qty, pod.itemCode, pod.itemDescription; with batchHistory as ( select distinct linkId as claimId from tbl_EAI_BatchHistory with (nolock) where ownerType = and ownerId = and linkType = 'TPC' and reference = '$RPC_1FS_Trade_DataExport' ) , removedClaims as ( select distinct c.claimId from tbl_TPM_CLaims c with (nolock) where ownerType = and ownerId = and claimId in ( select claimId from batchHistory ) and not exists ( select * from tbl_TPM_ClaimContracts cc with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = cc.ownerId and r.ownerType = cc.ownerType and r.requestId = cc.requestId and r.volumeGenerator = 'Y' inner join tbl_TPM_COntracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.partnerType = 'OPR' where cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimId and cc.reconciled = 'Y' ) and year( claimPeriodEnd ) >= ) select c.claimId, '#_displayClaimIdStatus#' display_claimId_status, c.claimId as display_claimId, '' as oprSector1, '' as oprSector2, c.claimPeriodStart InvoiceStart, c.claimPeriodEnd InvoiceEnd, 0 fsl_operatorId, '' as unit_companyname, '' as address, '' address2, '' city, '' state, '' zipCode, 0 as unitCost, 0 UnitPrice, '' as UnitofMeasure, 0.0 as totalDollars, '' productCode, '' as productDesc, 0.0 qty, 0.0 Rate, 0 AS RowTotal, coalesce(o.companyName, d.cdr_dstname) as companyName, '' cdr_dstName, '' as Distributor, '' Distributor_Sku, '' as oprunitID, '' Distributor_Branch, '' as oprTerritoryCode, '' as transactionDate, '' DistributorId, '' as DistributorParentID, '' as ProductDescSubmitted, '' cdr_dstParentName, '' cdr_dstCode, '' cdr_dstBranchName, '' cdr_dstBranchCode, 0 fsl_choId, 0 fsl_dstId, ct.assignedContractNbr, ct.contractName, c.claimDescription, coalesce( ob.companyName, db.cdr_dstName ) as beneficiary, ob.fsl_choId as beneficiary_1fs_ID from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType left outer join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerid = r.ownerid and ct.ownerType = r.ownerType and r.contractId = ct.contractId left outer join tbl_OPR_ClientOperators o with (nolock) on o.OperatorID = c.partnerId and 'OPR' = c.partnerType and o.ownerId = c.ownerId and o.fsltablecode = c.ownerType left outer join tbl_CDR_Distributors d with (nolock) on d.cdr_recordId = c.partnerId and 'CDR' = c.partnerType and d.ownerId = c.ownerId and d.fsl_tablecode = c.ownerType left outer join tbl_OPR_ClientOperators ob with (nolock) on ob.OperatorID = ct.partnerId and 'OPR' = ct.partnerType and ob.ownerId = ct.ownerId and ob.fsltablecode = ct.ownerType left outer join tbl_CDR_Distributors db with (nolock) on db.cdr_recordId = ct.partnerId and 'CDR' = ct.partnerType and d.ownerId = ct.ownerId and d.fsl_tablecode = ct.ownerType on unit.operatorid = pod.operatorid and unit.ownerId = pod.ownerId and unit.fsltablecode = pod.ownerType inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = unit.fsl_choId and cho.cho_type in ( 'U', 'I' ) ---> where c.ownerType = and c.ownerid = and cc.claimId in ( select claimId from removedClaims ) order by c.claimId desc

Rich Products Transaction File - #lsnumberformat( qmd_getOperatorData.recordCount )#

select *, 0 rowId from qmd_getOperatorData select c.name from syscolumns c inner join sysobjects o on o.id = c.id where o.xtype = 'U' and o.name = 'tbl_TPM_EAI_ClaimFaults' and c.name not in ( 'ID', 'ownerType', 'ownerId', 'batchId', 'batchFaultId', 'dataFault', 'rowId' ) delete from tbl_TPM_EAI_ClaimFaults where ownerId = and ownerType = and batchId = select stateId from tbl_UT_States with (nolock) where state = INSERT INTO tbl_TPM_EAI_ClaimFaults ( ownerType ,ownerId ,batchId ,dataFault ,rowId ,#colName# ) VALUES ( , , , , , ) select dataFault, count( * ) as records from tbl_TPM_EAI_ClaimFaults where ownerId = and ownerType = and batchId = group by dataFault select * from qmd_faultSummary order by dataFault select qmd_dataChecks.* from qmd_dataChecks, qmd_dataFaults where qmd_dataFaults.rowId = qmd_dataChecks.rowId and qmd_dataFaults.dataFault = order by rowId ---> select display_claimId, companyName as claimant, claimDescription, InvoiceStart, InvoiceEnd, beneficiary, assignedContractnbr agreementNbr, contractName agreementName, sum( qty ) qty from qmd_getOperatorData group by display_claimId, InvoiceStart, InvoiceEnd, claimDescription, companyName, beneficiary, assignedContractNbr, contractName select sum( qty ) as qty from qmd_summary select distinct claimId from qmd_getOperatorData select distinct fileSuffix from qmd_getOperatorDataRaw select #_originalColumnList# from qmd_getOperatorDataRaw where fileSuffix = select fieldName from tbl_EAI_inboundFileFormatFields with (nolock) where formatId = and hiddenField = 'N' order by sequence, fieldName

The following batch ID: #qmd_batch.batchId# did not send any file via FTP due to a discrepancy in data rows and columns.

#min( qmd_getOperatorData.recordCount, _maxRows )##qmd_getOperatorData.recordCount# rows exported

select ftpDirectory, ftpPassword, ftpServer, ftpUserName from tbl_EAI_InboundFTPSources with (nolock) where outbound = 'Y' and connectionName like and ownerId = and ownerType =

Development Environment: FTP Transfer Disabled

--->

FTP Queued: #htmleditformat( _ftpResult )#

FTP NOT QUEUED

No FTP Source - file not transfered

update tbl_EAI_inboundBatches set fileName = , ttlDays = 396, lineCount = where batchId = and ownerId = and ownerType = delete from tbl_EAI_BatchHistory where ownerId = and ownerType = and linkType = 'TPC' and linkId = and reference = '$RPC_1FS_Trade_DataExport' insert into tbl_EAI_BatchHistory (ownerId, ownerType, batchid, batchTime, linkType, linkId, reference ) values ( , , , , 'TPC', , '$RPC_1FS_Trade_DataExport' ) update tbl_TPM_Claims set oneFSMappingUpdated = null where ownerId = and ownerType = and claimId = select distinct Year( c.claimPeriodEnd ) claimYear, Month( c.claimPeriodEnd ) claimMonth, c.claimId from tbl_TPM_CLaims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimId and cc.reconciled = 'Y' inner join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType and r.volumeGenerator = 'Y' -- added by mwg on 10/4/2022 to match the actual resend logic, this will cut down on batches that do nothing 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 and pod.status != 'DENIED' -- added by mwg on 10/4/2022 to match the actual resend logic, this will cut down on batches that do nothing inner join tbl_OPR_ClientOperators unit with (nolock) on unit.operatorid = pod.operatorid and unit.ownerId = pod.ownerId and unit.fsltablecode = pod.ownerType -- added by mwg on 10/4/2022 to match the actual resend logic, this will cut down on batches that do nothing inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = unit.fsl_choId and cho.cho_type in ( 'U', 'I' ) where c.ownerId = and c.ownerType = and c.oneFSMappingUpdated is not null and c.reconciledDate is not null and Year( c.claimPeriodEnd ) <= Year( getDate() ) and year( c.claimPeriodEnd ) >= order by claimYear, claimMonth

#claimYear# #claimMonth# - #listLen( _claimIds )# claims update

Creating batch for removed claims...

Creating batch for removed claims done!

ReconciledDate: 2020-12-01 05:50:00.000

Created #listLen( _batchFileNames )# Files...

#_idx#. Export FileName: #_batchExportFileName#

#_idx#. Archive FileName: #_batchArchiveFileName#

#htmleditformat( _rQuery.sql )#