Home › Forums › Developers › General developer's discussion ›

report query issue

Submitted by dadmin on Wed, 24/07/2013 - 22:44
in

I am trying to modify the supplier reorder report to use the neverOrder and alwaysOrder properly. I am using the following query but it is not showing me all the results which I expect and I can't figure out what I am doing wrong. 

Can anyone see where I am going wrong here?

	select



	  stockLocation,



	  supplier,



	  productId,



	  product,



	  reorderCode,



	  reorderDesc,



	  nettPrice,



	  max(current) as current,



	  max(ideal) as ideal,



	  max(critical) as critical,



	  packsize,



	  max(orderSize) as orderPackSize,



	  sum((orderedQty-receivedQty-cancelledQty)*if(orderSize is not null, orderSize,packsize)) as onorder,



	  ceil((max(ideal) - (max(current) + sum((orderedQty-receivedQty-cancelledQty)*if(orderSize is not null, orderSize,packsize))))/packsize) as toorder,



	  neverOrder,



	  alwaysOrder



	from



	(



	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,



	  currentqty.value as current,



	  idealqty.value as ideal,



	  criticalqty.value as critical,



	  packageSize.value as packsize,



	  neverOrder.value as neverOrder,



	  alwaysOrder.value as alwaysOrder,



	  preferred.value as prefSupplier,



	  reordercode.value as reorderCode,



	  reorderdesc.value as reorderDesc,



	  nettprice.value as nettPrice,



	  orderDetail.quantity as orderedQty,



	  receivedqty.value as receivedQty,



	  cancelledqty.value as cancelledQty,



	  orderpackSize.value as orderSize



	from entities product



	    join entity_relationships productStock on product.entity_id = productStock.source_id and productStock.arch_short_name = "entityRelationship.productStockLocation"



	    left outer join entity_relationship_details currentqty on currentqty.entity_relationship_id = productStock.entity_relationship_id  and currentqty.name = "quantity"



	    left outer join entity_relationship_details idealqty on idealqty.entity_relationship_id = productStock.entity_relationship_id  and idealqty.name = "idealQty"



	    left outer join entity_relationship_details criticalqty on criticalqty.entity_relationship_id = productStock.entity_relationship_id  and criticalqty.name = "criticalQty"



	    left outer join entity_relationship_details neverOrder on neverOrder.entity_relationship_id = productStock.entity_relationship_id  and neverOrder.name = "neverOrder"



	    left outer join entity_relationship_details alwaysOrder on alwaysOrder.entity_relationship_id = productStock.entity_relationship_id  and alwaysOrder.name = "alwaysOrder"



	    left outer 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_relationships productSupplier on product.entity_id = productSupplier.source_id and productSupplier.arch_short_name = "entityRelationship.productSupplier"



	    left outer join entity_relationship_details packageSize on packageSize.entity_relationship_id = productSupplier.entity_relationship_id  and packageSize.name = "packageSize"



	    left outer join entity_relationship_details preferred on preferred.entity_relationship_id = productSupplier.entity_relationship_id  and preferred.name = "preferred"



	    left outer join entity_relationship_details reordercode on reordercode.entity_relationship_id = productSupplier.entity_relationship_id  and reordercode.name = "reorderCode"



	    left outer join entity_relationship_details reorderdesc on reorderdesc.entity_relationship_id = productSupplier.entity_relationship_id  and reorderdesc.name = "reorderDescription"



	    left outer join entity_relationship_details nettprice on nettprice.entity_relationship_id = productSupplier.entity_relationship_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 $P{Product} and



	  product.active = 1 and



	  (stockLocation.name like $P{Stock Location} or stockLocation.name is null) and



	  supplier.name like $P{Supplier} and



	  if($P{Classification} = "%",(grouplookup.name like $P{Classification} or grouplookup.lookup_id is null),grouplookup.name like $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 is null) and



	  (deliveryStatus.value <> "FULL" or deliveryStatus.value is null) and



	  stockParticipation.entity_id = stockLocation.entity_id



	union all



	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,



	  currentqty.value as current,



	  idealqty.value as ideal,



	  criticalqty.value as critical,



	  packageSize.value as packsize,



	  neverOrder.value as neverOrder,



	  alwaysOrder.value as alwaysOrder,



	  preferred.value as prefSupplier,



	  reordercode.value as reorderCode,



	  reorderdesc.value as reorderDesc,



	  nettprice.value as nettPrice,



	  0 as orderedQty,



	  0 as receivedQty,



	  0 as cancelledQty,



	  packageSize.value as orderSize



	from entities product



	    join entity_relationships productStock on product.entity_id = productStock.source_id and productStock.arch_short_name = "entityRelationship.productStockLocation"



	    left outer join entity_relationship_details currentqty on currentqty.entity_relationship_id = productStock.entity_relationship_id  and currentqty.name = "quantity"



	    left outer join entity_relationship_details idealqty on idealqty.entity_relationship_id = productStock.entity_relationship_id  and idealqty.name = "idealQty"



	    left outer join entity_relationship_details criticalqty on criticalqty.entity_relationship_id = productStock.entity_relationship_id  and criticalqty.name = "criticalQty"



	    left outer join entity_relationship_details neverOrder on neverOrder.entity_relationship_id = productStock.entity_relationship_id  and neverOrder.name = "neverOrder"



	    left outer join entity_relationship_details alwaysOrder on alwaysOrder.entity_relationship_id = productStock.entity_relationship_id  and alwaysOrder.name = "alwaysOrder"



	    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_relationships productSupplier on product.entity_id = productSupplier.source_id and productSupplier.arch_short_name = "entityRelationship.productSupplier"



	    left outer join entity_relationship_details packageSize on packageSize.entity_relationship_id = productSupplier.entity_relationship_id  and packageSize.name = "packageSize"



	    left outer join entity_relationship_details preferred on preferred.entity_relationship_id = productSupplier.entity_relationship_id  and preferred.name = "preferred"



	    left outer join entity_relationship_details reordercode on reordercode.entity_relationship_id = productSupplier.entity_relationship_id  and reordercode.name = "reorderCode"



	    left outer join entity_relationship_details reorderdesc on reorderdesc.entity_relationship_id = productSupplier.entity_relationship_id  and reorderdesc.name = "reorderDescription"



	    left outer join entity_relationship_details nettprice on nettprice.entity_relationship_id = productSupplier.entity_relationship_id  and nettprice.name = "nettPrice"



	    join entities supplier on supplier.entity_id = productSupplier.target_id



	where



	  product.name like $P{Product} and



	  product.active = 1 and



	  (stockLocation.name like $P{Stock Location} or stockLocation.name is null) and



	  supplier.name like $P{Supplier} and



	  if($P{Classification} = "%",(grouplookup.name like $P{Classification} or grouplookup.lookup_id is null),grouplookup.name like $P{Classification}) and



	  preferred.value = "true" and



	  idealqty.value <> "0.00"



	) as tmp



	group by stockLocationId, supplierId, productId



	having (current + onorder) <= critical and toorder > 0 and neverOrder != "true" and alwaysOrder = "true"
  

Thanks,

Matt Y.

‹ 1.7 Alpha - Medication display on Med Recs has newline report query issue ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: report query issue

Submitted by tgething (not verified) on Thu, 25/07/2013 - 11:36.

Matt - I am afraid that the following is not very scientific. I did the following:

  1. adjust things so that there was something to order (initially I had the problem that my conversion has not set any of the suppliers as preferred)
  2. ran Suppliers|Orders|Generate - it produced the expected order. Deleted that so nothing on order.
  3. ran your report - produced 0 orders
  4. ran the old report - produced 1 order
  5. used a compare program to see what the differences were in the two SQL queries

The one that worried me was the last line where you had:

 having (current + onorder) <= critical and toorder > 0 and neverOrder != "true" and alwaysOrder = "true"

My product has alwaysOrder = false

The CSH text at http://www.openvpms.org/documentation/csh/1.7/product/tabs#stockLoc [I copied the logic from the Jira] does not match your logic - which is if alwaysOrder false then don't order - and hence the difference between the Generate output and your SQL.

Regards, Tim G

Re: report query issue

Submitted by tanderson on Thu, 25/07/2013 - 14:15.

To debug it, try commenting out the having clause and see what the query returns for current, onorder, critical, toorder, neverOrder, and alwaysOrder.

Then add them back into the having clause one term at a time, to check that each term works as expected.

Note that the Stock Reorder Report has been updated recently as it was not excluding inactive relationships. You can find the update here: http://www.openvpms.org/customisation/stock-reorder-report

-Tim A

Re: report query issue

Submitted by dadmin on Thu, 25/07/2013 - 23:32.

Thanks Tims,

You have steered me in the correct direction.

It turns out I have a whole heap of products where the neverOrder value is null so these weren't getting picked up. I thought if I said neverOrder != "true" it would pick up false and null values but it appears that is incorrect. 

my having clasue is now:

 having (current + onorder) <= critical and toorder > 0 and alwaysOrder = "true" and (neverOrder = "false" or neverOrder is null)

Thanks again,

Matt Y.

Re: report query issue

Submitted by tgething (not verified) on Fri, 26/07/2013 - 09:00.

Tim A - can you please clarify the algorithm used by the Generate Orders processing.  In the CSH text (which I cloned from the jira), I have:

The order quantity used by the system to generate orders (when the Generate button on the Suppliers|Orders screen is pressed) is as follows:

 if product not already on order
  if Always Order set then
    if in-stock quantity S < ideal quantity I, then order (I-S) units
  else
    if in-stock quantity S <= critical quantity C, then order (I-S) units

 

This logic would appear to differ from Matt's logic in that for Matt:

  1. alwayOrder MUST be true for an order to be generated
  2. the logic calculates the 'projected stock' (ie what is in stock plus what is on order) whereas the jira said 'if there is already an order, then irrespective of the stock situation, do not order'

[Note that the use of the 'projected stock' is problematical - the last stock control system I built (admittedly 25 years ago) used this, but the algorithm also included current usage rates and supplier lead times. The very simple 'if it's already on order, don't order more' approach is quite acceptable provided that: a) usage rates are low; b) lead times are short; and c) there is a reasonable pad between the ideal and critical stock levels.] 

Regards, Tim G

Re: report query issue

Submitted by tanderson on Fri, 26/07/2013 - 10:28.

The Generate Orders support doesn't use the alwaysOrder and neverOrder at present. It replicates the behaviour of the original report,  as per http://www.openvpms.org/project/automatic-order-generation and https://openvpms.atlassian.net/browse/OVPMS-1269

I think you may be referring to https://openvpms.atlassian.net/browse/OVPMS-755 which hasn't been implemented.

-Tim A

Re: report query issue

Submitted by tgething (not verified) on Fri, 26/07/2013 - 18:17.

Tim A - you are correct. I have adjusted the CSH text to say as follows:

The algorithm used by the system to to calculate the order quantities (when the Generate button on the Suppliers|Orders screen is pressed) is as follows:

Calculate predicted stock P = current stock + qty on order and not yet delivered or cancelled
Let I = ideal quantity, C = critical quantity
If P <= C, then order (I - P)

Note that the above algorithm does not use the Always & Never order check boxes (but this is planned for a future enhancement). Also the algorithm is more complex than the above because it also takes into account the supplier's package size.

 

Regards, Tim G

Syndicate content