$P{Date}))
left join product_price_classifications ppc on pp.product_price_id = ppc.product_price_id
left join lookups ppg on ppc.lookup_id = ppg.lookup_id and ppg.arch_short_name = "lookup.pricingGroup"
where e.name like concat(ifnull($P{Product Name},""),"%")
union
select pp.price as price, e.entity_id as pid, pp.start_time, pp.end_time, pp.name, ppg.name as ppgName, pt.entity_id as lkid,
if(pp.arch_short_name="productPrice.fixedPrice","LF",if(pp.arch_short_name="productPrice.unitPrice","LU","?")) as type
from products p
join entities e on p.product_id = e.entity_id
join entity_relationships erl on erl.source_id = e.entity_id and erl.arch_short_name = 'entityRelationship.productLink'
join entities pt on pt.entity_id = erl.target_id
join product_prices pp on pt.entity_id = pp.product_id
and ((pp.start_time is null or pp.start_time <= $P{Date}) and (pp.end_time is null or pp.end_time > $P{Date}))
left join product_price_classifications ppc on pp.product_price_id = ppc.product_price_id
left join lookups ppg on ppc.lookup_id = ppg.lookup_id and ppg.arch_short_name = "lookup.pricingGroup"
where e.name like concat(ifnull($P{Product Name},""),"%")
) as pp on pp.pid = e.entity_id
where e.name like concat(ifnull($P{Product Name},""),"%")
and ifnull(pt.name,'-') like concat(ifnull( $P{Product Type},""),"%")
and ifnull(pp.ppgName,'-') like concat(ifnull( $P{Pricing Group},""),"%")
and ((e.arch_short_name = "product.service" and $P{IncServices})
or (e.arch_short_name = "product.medication" and $P{IncMedications})
or (e.arch_short_name = "product.merchandise" and $P{IncMerchandise})
or (e.arch_short_name = "product.priceTemplate" and $P{IncTemplates}))
and pp.start_time <= $P{startsBefore}
and ((not $P{LinkedOnly} ) or ( $P{LinkedOnly} and pp.lkid is not null))
order by product, pp.type, pp.name, pp.start_time;]]>
1]]>