Oracle Reports Add new parameters

I have a very serious issue here. Hope someone can give me a solution to this. Need it pretty bad.

We have a few reports that we have implemented over the web. Using Apache/Tomcat/Weblogic architecture.

All these days were using Oracle Reports Provided Parameter Forms which is very limited in functionality. For example if we need to put some extra functionality like Showing a Calendar and few other functions of our product.

To achieve that we are creating customized forms using HTMLdynamically  from a table. The table contains the Parameter Name, Type, Table name and Column name to use to construct the where condition. Each parameter, based on the type ( custom types), displays with a LOV or a Calendar and such.

The problem at hand is - how do we allow the user to add new parameters on the fly.

What we thought of doing is -
- to let the user add the new mapping for the parameters in the table
and to pass that as a string to the Report.
- Then to use the lexical parameters to append the where condition to
the main query.

But more issues with this approach is:
- what if the main query contains sub queries in the select and the
where condition is to be applied to the subselect not the main select
- what if the main query contains psedu-views in the select and the
condition needs to applied.

e.g
select a.shipment_id, start_time, end_time, status_type_id from
  ( select shipment_id, start_time, end_time     from shipment
    where shipment_id = :p_shipment_id ) a,   ( select status_type_id, shipment_gid
    from status_type
    where status_id = :p_status ) b
where a.shipment_gid = b.shipment_gid ;

Now the user wants to add a new parameter item_number ( which is in the shipment table not in the status_type table) to the report.

Where do I put the new User added condition. I cannot do

select ...
from
 ( select ...) a,
( select ...) b
where &p_new because item_number is not a valid column name in the main query.

SOMEBODY HELP ME! Cheers
Biz Received on Fri May 31 2002 - 20:43:23 CEST