now())
left outer join entity_link_details packageSize on packageSize.id = productSupplier.id and packageSize.name = "packageSize"
left outer join entity_link_details preferred on preferred.id = productSupplier.id and preferred.name = "preferred"
left outer join entity_link_details reorderCode on reorderCode.id = productSupplier.id and reorderCode.name = "reorderCode"
left outer join entity_link_details reorderDesc on reorderDesc.id = productSupplier.id and reorderDesc.name = "reorderDescription"
left outer join entity_link_details nettprice on nettprice.id = productSupplier.id and nettprice.name = "nettPrice"
join entities supplier on supplier.entity_id = productSupplier.target_id
left outer join participations productParticipation on productParticipation.entity_id = product.entity_id
left outer join acts orderLine on orderLine.act_id = productParticipation.act_id and orderLine.arch_short_name = "act.supplierOrderItem"
left outer join financial_acts orderDetail on orderDetail.financial_act_id = orderLine.act_id
left outer join act_details receivedqty on receivedqty.act_id = orderLine.act_id and receivedqty.name ="receivedQuantity"
left outer join act_details cancelledqty on cancelledqty.act_id = orderLine.act_id and cancelledqty.name ="cancelledQuantity"
left outer join act_details orderpacksize on orderpacksize.act_id = orderLine.act_id and orderpacksize.name ="packageSize"
left outer join act_relationships orderLink on orderLink.target_id = orderLine.act_id and orderLink.arch_short_name = "actRelationship.supplierOrderItem"
left outer join acts orders on orders.act_id = orderLink.source_id
left outer join act_details deliveryStatus on deliveryStatus.act_id = orders.act_id and deliveryStatus.name ="deliveryStatus"
left outer join participations stockParticipation on stockParticipation.act_id = orders.act_id and stockParticipation.arch_short_name = "participation.stockLocation"
where
product.name like concat(ifnull( $P{Product},""),"%") and
product.active = 1 and
stockLocation.name like concat(ifnull( $P{Stock Location},""),"%") and
supplier.name like concat(ifnull( $P{Supplier},""),"%") and
supplier.active = 1 and
ifnull(grouplookup.name,'-') like concat(ifnull($P{Classification},""),"%") and
preferred.value = "true" and
idealqty.value <> "0.00" and
(productParticipation.act_arch_short_name = "act.supplierOrderItem") and
(orders.status = "POSTED" or orders.status = "ACCEPTED" or orders.status is null) and
(deliveryStatus.value <> "FULL" or deliveryStatus.value is null) and
stockParticipation.entity_id = stockLocation.entity_id
union
select
stockLocation.entity_id as stockLocationId,
stockLocation.name as stockLocation,
supplier.entity_id as supplierId,
supplier.name as supplier,
product.entity_id as productId,
product.name as product,
cast(currentqty.value as decimal(18,3)) as current,
cast(idealqty.value as decimal(18,3)) as ideal,
cast(criticalqty.value as decimal(18,3)) as critical,
cast(packageSize.value as signed) as packsize,
preferred.value as prefSupplier,
reorderCode.value as reorderCode,
reorderDesc.value as reorderDesc,
cast(nettprice.value as decimal(18,3)) as nettPrice,
0 as orderedQty,
0 as receivedQty,
0 as cancelledQty,
cast(packageSize.value as signed) as orderSize
from entities product
join entity_links productStock on product.entity_id = productStock.source_id and productStock.arch_short_name = "entityLink.productStockLocation"
left outer join entity_link_details currentqty on currentqty.id = productStock.id and currentqty.name = "quantity"
left outer join entity_link_details idealqty on idealqty.id = productStock.id and idealqty.name = "idealQty"
left outer join entity_link_details criticalqty on criticalqty.id = productStock.id and criticalqty.name = "criticalQty"
join entities stockLocation on stockLocation.entity_id = productStock.target_id
left outer join entity_classifications productgroup on productgroup.entity_id = product.entity_id
left outer join lookups grouplookup on grouplookup.lookup_id = productgroup.lookup_id
join entity_links productSupplier on product.entity_id = productSupplier.source_id and productSupplier.arch_short_name = "entityLink.productSupplier"
and (productSupplier.active_start_time is null or productSupplier.active_start_time < now())
and (productSupplier.active_end_time is null or productSupplier.active_end_time > now())
left outer join entity_link_details packageSize on packageSize.id = productSupplier.id and packageSize.name = "packageSize"
left outer join entity_link_details preferred on preferred.id = productSupplier.id and preferred.name = "preferred"
left outer join entity_link_details reorderCode on reorderCode.id = productSupplier.id and reorderCode.name = "reorderCode"
left outer join entity_link_details reorderDesc on reorderDesc.id = productSupplier.id and reorderDesc.name = "reorderDescription"
left outer join entity_link_details nettprice on nettprice.id = productSupplier.id and nettprice.name = "nettPrice"
join entities supplier on supplier.entity_id = productSupplier.target_id
where
product.name like concat(ifnull( $P{Product},""),"%") and
product.active = 1 and
stockLocation.name like concat(ifnull($P{Stock Location},""),"%") and
supplier.name like concat(ifnull( $P{Supplier},""),"%") and
ifnull(grouplookup.name,'-') like concat(ifnull($P{Classification},""),"%") and
preferred.value = "true" and
idealqty.value <> "0.00"
) as tmp
group by stockLocationId, supplierId, productId
having ((((current + onorder) < ideal) and ($P{orderCrit} =false ) ) or (((current + onorder) <= critical) and ($P{orderCrit} =true ))) and toorder > 0]]>