select distinct m.mfr_name, rp.ownerType, rp.ownerId, rp.requestId, rp.requestProductId, rp.catalogType, rp.catalogId, rp.comment, ct.contractId, ct.assignedContractNbr, ct.contractName, case when ct.partnerType = 'OPR' then opr.companyName else cdr.cdr_dstName end as companyName, r.chainSequence
from tbl_TPM_requestProducts rp
inner join tbl_TPM_Requests r
on r.requestId = rp.requestId
and r.ownerId = rp.ownerId
and r.ownerType = rp.ownerType
inner join tbl_TPM_Contracts ct
on ct.contractId = r.contractId
and ct.ownerId = r.ownerId
and ct.ownerType = r.ownerType
left outer join tbl_OPR_ClientOperators opr
on opr.operatorId = ct.partnerId
and 'OPR' = ct.partnerType
and opr.ownerId = ct.ownerId
and opr.fsltablecode = ct.ownerType
left outer join tbl_CDR_Distributors cdr
on cdr.cdr_recordId = ct.partnerId
and 'CDR' = ct.partnerType
and cdr.ownerId = ct.ownerId
and cdr.fsl_tablecode = ct.ownerType
inner join tblManufacturers m
on m.mfr_id = ct.ownerId
and 'MFR' = ct.ownerType
where exists ( select * from tbl_TPM_RequestProducts rps
where rps.catalogType = 'SKU'
and rp.catalogType = 'MFR'
and rps.requestId = rp.requestId
and rps.ownerId = rp.ownerId
and rps.ownerType = rp.ownerType )
or exists ( select * from tbl_TPM_RequestProducts rps
inner join tbl_PRD_Skus s
on s.prodId = rp.catalogId
and s.ownerId = rps.ownerId
and s.fsl_tablecode = rps.ownerType
where rps.catalogType = 'SKU'
and rp.catalogType = 'PRD'
and rps.requestId = rp.requestId
and rps.ownerId = rp.ownerId
and rps.ownerType = rp.ownerType )
or exists ( select * from tbl_TPM_RequestProducts rps
inner join tbl_PRD_Products p
on p.categoryId = rp.catalogId
and p.ownerId = rps.ownerId
and p.fsl_tablecode = rps.ownerType
inner join tbl_PRD_Skus s
on s.prodId = p.prodId
and s.ownerId = p.ownerId
and s.fsl_tablecode = p.fsl_tablecode
where rps.catalogType = 'SKU'
and rp.catalogType = 'CAT'
and rps.requestId = rp.requestId
and rps.ownerId = rp.ownerId
and rps.ownerType = rp.ownerType )
or exists ( select * from tbl_TPM_RequestProducts rps
inner join tbl_PRD_Categories c
on c.prodLineId = rp.catalogId
and c.ownerId = rps.ownerId
and c.fsl_tablecode = rps.ownerType
inner join tbl_PRD_Products p
on p.categoryId = c.categoryId
and p.ownerId = c.ownerId
and p.fsl_tablecode = c.fsl_tablecode
inner join tbl_PRD_Skus s
on s.prodId = p.prodId
and s.ownerId = p.ownerId
and s.fsl_tablecode = p.fsl_tablecode
where rps.catalogType = 'SKU'
and rp.catalogType = 'LIN'
and rps.requestId = rp.requestId
and rps.ownerId = rp.ownerId
and rps.ownerType = rp.ownerType )
order by rp.ownerType, rp.ownerId, companyName, ct.contractId, rp.requestId
#htmleditformat( mfr_Name )#
#htmleditformat( companyName )#
#htmleditformat( assignedContractNbr )# - #htmleditformat( contractName )#
- Version #chainSequence#
update tbl_TPM_RequestProducts
set estAnnualVolume = 0
where ownerId =
and ownerType =
and catalogType =
and catalogId =
and requestId =
and requestProductId =
- #htmleditformat( _types )#