Form6.0.8, oracle8.1.6

I am relatively new to the forms.
The automatic query refresh is not working for my application, tested on client/server and the
Web.

In order to refreshing, I have to disconnect the form application from the database and
reconnect it to.
I used the SQLPLUS to verify the user does not need reconnect inorder to view the data
updated.

Settings in LOVs

automatic refresh =yes
filter before display = no
automatic display=no
automatic select =no
automatic skip =no
automatic position =no

settings in the record groups

record group fetch size =20
record group type =query
record group query =SELECT distinct(GL_CODE_COMBINATIONS.SEGMENT18) FROM
GL.GL_CODE_COMBINATIONS order by 1

I have created a small form for just test the auto-refresh, and it works by the same settings. The problem for me is I could not find out what else other than the settings of LOVs and the Record Groups .

Hi!
I am using from claue query as a datablock source property.
my query is
select empno ,to_char(name) name from emp
When i try to update any record error come
frm-40654 record has been updated by another user. requery to see change
there is no user except me.
i think the problem is of using To_CHAR function but
I have to use to_char function
is there any other option like procedure or else
plz help
regards

Hi!
I am using to_char function because
name is nvarchar2 column
desc emp
----------
empno number
name nvarchar2(4000)
there is no nvarchar2 datatype property in form. (forms 6)
i am using char datatype for name column in form.
if i do not use to_char function then name column remain empty and only empno is retreived.
regards

Whilst playing with this on an app (6i/9i) at work, we found that

a) its wise to wrap the SQL in parentheses - otherwise updateable blocks have errors in some places

b) enforce primary key had some dramas - can't remember the specifics

Other than that, ran very nicely indeed.

Excellent as usual. I was trying to solve one big problem in my forms.Using this feature i solved it with in an hour.

Thanks again for your answers.

I'm having a problem using a FROM CLAUSE query Query Data Source Type on an Oracle Applications template form.

I've successfully created a form with a single data block having a single item INVENTORY_ITEM_ID, with the Query Data Source Name as:

(SELECT INVENTORY_ITEM_ID FROM mtl_org_assign_v x WHERE 1=1 AND master_organization_id = 86 AND NVL(eam_enabled_flag, 'N') = NVL('N', 'N') AND inventory_item_id = 2)

however, if I add a subquery to the statement:

(SELECT INVENTORY_ITEM_ID FROM mtl_org_assign_v x WHERE 1=1 AND master_organization_id = 86 AND NVL(eam_enabled_flag, 'N') = NVL('N', 'N') AND inventory_item_id = 2 AND EXISTS ( SELECT 'x' FROM org_organization_definitions ood WHERE ood.organization_id = x.organization_id AND ood.inventory_enabled_flag = 'Y' ))

the whole thing blows up!! (i.e. throws a java exception and closes the application) I've tried and run the query directly from SQL*Plus no problem. I've also built a view with it and queried that directly - no problem.

The reason I am using this query is that I am trying to build up to a much more complex query with a UNION and bind variables which doesn't work - I'm trying to isolate the root cause, so I'm doing it one step at a time.

The query I'm finally trying to have in there is:
SELECT DECODE (organization_id, :master_org_id, 1, 2), assigned_flag,organization_id, inventory_item_id, organization_code,
organization_name, primary_unit_of_measure_tl,
cost_of_sales_account, sales_account, expense_account,
encumbrance_account
FROM mtl_org_assign_v x
WHERE 1=1
AND master_organization_id = :master_org_id
AND NVL (eam_enabled_flag, 'N') = NVL (:EAM_ITEM_FLAG, 'N')
AND inventory_item_id = :inventory_item_id
AND EXISTS (
SELECT 'x'
FROM org_organization_definitions ood
WHERE ood.organization_id = x.organization_id
AND ood.inventory_enabled_flag = 'Y'
)
UNION
SELECT DECODE (organization_id, :master_org_id, 1, 2), assigned_flag,organization_id, inventory_item_id, organization_code,
organization_name, primary_unit_of_measure_tl,
cost_of_sales_account, sales_account, expense_account,
encumbrance_account
FROM mtl_org_assign_v x
WHERE 1=1
AND master_organization_id = :master_org_id
AND NVL (eam_enabled_flag, 'N') = NVL (:EAM_ITEM_FLAG, 'N')
AND EXISTS (
SELECT 'x'
FROM org_organization_definitions ood
WHERE ood.organization_id = x.organization_id
AND ood.inventory_enabled_flag = 'Y'
)
AND (inventory_item_id IS NULL
AND organization_id IN (
SELECT
organization_id
FROM mtl_org_assign_v
WHERE inventory_item_id IS NULL
MINUS
SELECT organization_id
FROM mtl_org_assign_v
WHERE inventory_item_id = :inventory_item_id
)
)
ORDER BY
1 ASC
,2 DESC
,3

Any suggestions?