Solved

Changing Record Color in Oracle Form

Posted on 2008-10-30
18
9,475 Views
Last Modified: 2013-12-12
Hi Experts..I am encountering a problem while trying to Highlight the record with any specific color.
My requirement is When new form is opened, the record that are satisfying the conditions should get highlighted with some color.Its an Tabular form I know I need to use set_item_instance_property and I am using some thing like this:

I wrote the below code in WHEN_NEW_FORM_INSTANCE Trigger.I even tested it to know whether the query is getting executed or not.It is working fine..i am able to display simple messages.

I think i need to write the code in some other trigger, but not sure.Could some one help me out??

EXECUTE_QUERY;

LOOP

--message('statemnt1');

v_lot_cnt := 0;

SELECT count(1) INTO v_lot_cnt 

FROM SMC_IRRS

WHERE lot_number = :WIP_OPERATIONS_V.lot_number

AND department = '656'

AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';

--message('statement2 count:' || v_lot_cnt);

IF v_lot_cnt <> 0 

THEN 

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 

end if;

   IF :SYSTEM.last_record='TRUE' THEN

       EXIT;

   ELSE

       NEXT_RECORD;

    END IF; 

   END LOOP;

Open in new window

0
Comment
Question by:sum_got
  • 7
  • 6
  • 2
18 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848143
Hi!

Did you try to put the code in the POST-QUERY-trigger of the queried block?

So you won't have to go through all the records twice. Having quite the same code in one of my forms in the post-query-trigger worked fine.
0
 

Expert Comment

by:mahmoud_76
ID: 22849298
The most efficient way to do that, is to put that logic in WHEN_NEW_ITEM_INSTANCE trigger on block level, so with every record displayed in the grid the calculation will b done and if condition satisfied then visual attribute changes accordingly.
0
 

Expert Comment

by:mahmoud_76
ID: 22849478
Sorry, i meant WHEN_NEW_RECORD_INSTANCE
0
 

Author Comment

by:sum_got
ID: 22851877
Hi MarusId...I tried to execute the above code in POST_QUERY Trigger at block level (wip_operations_v)
I am getting the below error msg :
40737-Illegal restricted procedure
NEXT_RECORD in POST_QUERY trigger

If i commented the Next_Record Code..How could i go to the next record in the grid (tabular form)

When i keep on pressing ok, i am getting some other err msgs like Unable to Fetch the next record and so on. Could some one help me out??

0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22851916
Hi sum_got,

You don't need the loop in this case (as Forms is going through all the records anyway), only the following part of code:
SELECT count(1) INTO v_lot_cnt 

FROM SMC_IRRS

WHERE lot_number = :WIP_OPERATIONS_V.lot_number

AND department = '656'

AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';

--message('statement2 count:' || v_lot_cnt);

IF v_lot_cnt <> 0 

THEN 

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 

end if;

Open in new window

0
 

Author Comment

by:sum_got
ID: 22851976
Hi mahmoud_76 and All,
I thk you din't get me properly.Let me be more clear.
 When i opened a new form, i am able to populate the data in the tabular block.My requirement is to
highlight the records which are satisfying the conditions i specified.
When i am trying in When_New_Record_Instance, nothing is happening.I am attaching the code for reference.
::Code written in POST_QUERY at Block Level::

Note : I tried the same code in When_New_Record_Instance Trigger
 

DECLARE

  V_ERROR_COUNT NUMBER;

  v_lot_cnt number := 0;

BEGIN

-- Additional Code for Highlighting Jobs 

--EXECUTE_QUERY;

LOOP

--message('statemnt1');

v_lot_cnt := 0;

SELECT count(1) INTO v_lot_cnt 

FROM SMC_IRRS

WHERE lot_number = :WIP_OPERATIONS_V.lot_number

AND department = '656'

AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
 

--message('statement2 count:' || v_lot_cnt);
 

IF v_lot_cnt <> 0 

THEN 

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 

end if;

   IF :SYSTEM.last_record='TRUE' THEN

       EXIT;

   ELSE

       NEXT_RECORD;

   END IF; 

END LOOP; 

Open in new window

0
 

Author Comment

by:sum_got
ID: 22852147
Hi MarkusId,

I tried as you said, but nothing happened.Just the data is populating in the grid as before,  the records that are satisfying the condition are not getting highlighted.Wat might be the reason behind that?
I am really not understanding, i am fresher to the job.Could you pls help?
If you need any more details, i can give you.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:MarkusId
ID: 22852209
Hi,

Have you already tried to put a message-command inside the if-block and tried if it is shown? Please try also with "!=" instead of "<>" as unequal-sign (or just with greater than ">").

If a message inside the if-block shows up and the records haven't changed their colour you could try another visual attribute or change the visual attribute to another colour?
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22852295
Please also try with the Background/font-parameters instead of the visual_attribute (didn't work for me in Forms 6i, but that doesn't necessarily mean that it wouldn't work on another environment)
0
 

Author Comment

by:sum_got
ID: 22852390
Hi,

I tried putting the message-command before the Select Statement and at the End of the Select Statement. I am getting the msg populated.Attached the screen shot and code.
Am i trying in the right way ? How you want me to try?? Could you pls send the code for background color
FYI : I am working on Forms 5

message('statemnt1');

--v_lot_cnt := 0;

SELECT count(1) INTO v_lot_cnt 

FROM SMC_IRRS

WHERE lot_number = :WIP_OPERATIONS_V.lot_number

AND department = '656'

AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
 

message('statement2 count:' || v_lot_cnt);
 

IF v_lot_cnt <> 0 

THEN 

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 

end if;

Open in new window

Snap.doc
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 500 total points
ID: 22852419
In the attached document the v_lot_cnt = 0, so it won't change the record, as it doesn't enter the if-block (because the program gets there only if v_lot_cnt does not equal 0)
0
 

Author Comment

by:sum_got
ID: 22852575
Hi...If i am not wrong wat you are saying is The value for v_lot_cnt is 0 and thats the reason its not entering the IF-Condition.
I think, when the select statment is checking the LOT_NUMBER aganist the condition, the count is getting changed to other than Zero (i checked the query  in DB)
Could i test the code putting the message command inside the IF Condition.  
0
 

Author Comment

by:sum_got
ID: 22852779
Hi MarkusId,
I tried putting the message command in side the IF-Condition and the message is not getting displayed.That means Is it not entering into the IF statement at all.
If that is the Case..How should i change my code.Could you pls modify the code.

message('statemnt1');

--v_lot_cnt := 0;

SELECT count(1) INTO v_lot_cnt 

FROM SMC_IRRS

WHERE lot_number = :WIP_OPERATIONS_V.lot_number

AND department = '656'

AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
 

message('statement2 count:' || v_lot_cnt);
 

IF v_lot_cnt <> 0 

THEN 

     message('statement3 count:' || v_lot_cnt);

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');

     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 

end if;

Open in new window

0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22856337
Hi,

Sorry I couldn't answer earlier - well, you have the select-statement above that isn't fetching any records. So could you please also message-output the :WIP_OPERATIONS_V.lot_number and show select and the output of a database query where you have  replaced :WIP_OPERATIONS_V.lot_number with the value given by the Forms?
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 23129602
I suppose the initial question has turned out not to be the real problem. I'm quite sure the SET_ITEM_INSTANCE-property would have worked if the program had not been prevented
from going there through the if-clause.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now