Integration

I finished the Cue Card "Build a Fusion Web Application" which is pretty basic and shows you have to update a parent( ORDER ) table and view the child ( ORDER_ITEM ) rows associated with the parent record. This is all fine but of course in the "real world" you would need to update/add items to an order. Naturally after add/updating the ORDER_ITEMS table you would want the ORDER_TOTAL value in the ORDER table to reflect the change. In other words, if you double the quantity of one of the items, the ORDER_TOTAL value in the ORDER table should increase.

So to expand on the Cue Card, I added another page which allows me to update the values in the ORDER_ITEMS table which works fine - this is just out-of-the-box ADF magic. The task flow appears as follows:.

I tried two ways to update the ORDER_TOTAL in the ORDER table after an add/update to ORDER_ITEM table.

1. Modify the orderview to include a nested select statement which calculates the ORDER_TOTAL dynamically

SELECT Orders.ORDER_ID,

   Orders.ORDER\_DATE,

   Orders.ORDER\_SHIPPED\_DATE,

   Orders.ORDER\_STATUS\_CODE,

  (select sum(unit\_price \* quantity) from order\_items a

    where a.order\_id = Orders.order\_id

   group by order\_id )

AS ORDER_TOTAL,

   Orders.CUSTOMER\_ID,

   Orders.SHIP\_TO\_NAME,

   Orders.SHIP\_TO\_ADDRESS\_ID,

   Orders.SHIP\_TO\_PHONE\_NUMBER,

. . . .

2. Add an update to the ORDER_TOTAL value in the ORDER table with in the task flow after an add/update to ORDER_ITEM table and COMMIT.

update orders set order_total = ???? where order_id = ????

With either solution, I get the same results which is as follows

I update the ORDER_ITEM table in the editOrderItem.jsff page, the task flow performs a COMMIT and returns to the editOrder.jsff page, ORDER_TOTAL amount does not reflect the change. Hitting cancel from editOrder.jsff returns to order.jsff which show the correct ORDER_TOTAL amount. Returning to editOrder.jsff from order.jsff and the correct value for ORDER_TOTAL appears. It seems that the values in the ADF form inside order.jsff are cached and the page never refreshes against any updates to the database. I've tried to make editOrder.jsff refresh by changing the properties from refresh "true" to "false". Also changed CacheResults from "true" to "false". Tried modifying the task flow properties from "no controller action" to "Always Begin New Transcation".

thanks in advance,

Rob