| SQL |
with
updatedClaims as (
select distinct c.claimId
from tbl_TPM_CLaims c with (nolock)
where ownerType = 'MFR'
and ownerId = 1
-- and claimId in ( select claimId from batchHistory )
and claimId in ( 176391,176580,173448,163957,176575 )
)
select c.claimId, 'U' 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 = 'MFR'
and c.ownerid = 1
and c.claimType IN ('B','D')
and r.volumegenerator = 'Y'
and cc.reconciled = 'Y'
and pod.status != 'DENIED'
and year( c.claimPeriodEnd ) >= 2018
and cc.claimId in ( select claimId from updatedClaims )
and cc.claimId = 163957
order by c.claimId desc, unit.companyName, RowTotal desc, Qty, pod.itemCode, pod.itemDescription; |