Home » Developer & Programmer » Forms » Trigger Assistance
icon5.gif  Trigger Assistance [message #287537] Wed, 12 December 2007 09:41 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Hello -

I feel that this is a stupid question but I cannot for the life of me find the proper documentation or help anywhere online or elsewhere.

Here is my scenario:

I have a form I wish to deploy into Oracle E-Business Suite Applications 11.5.8.

I have a data block built on a view. I have written a PL/SQL procedure that I want to fire each time the record changes in the data block (eg, when the user scrolls through the records using the up and down arrow keys). I cannot achieve this functionality.

I have been through just about every trigger available and I have run out of ideas. According to the Forms Help, I would imagine the trigger I should use is the WHEN-NEW-RECORD-INSTANCE trigger set at the block level, but when I put custom code into the trigger definition, I am not allowed into QueryMode in the application (F11 does not work).

What am I doing wrong?

Below is the trigger code I wish to execute:
DECLARE
-- Local variables needed

	v_years_of_serv     NUMBER 	 := 0;
	v_intern_flag	    VARCHAR2(3)  := NULL;
	v_sick_accr_rate    VARCHAR2(10) := NULL;
	v_vac_accr_rate     VARCHAR2(10) := NULL;
	v_vac_percent_rate  NUMBER 	 := 0;
	v_sic_percent_rate  NUMBER 	 := 0;
	
	v_personal_dsp	    NUMBER	 := 0;
	v_famsick_dsp	    NUMBER	 := 0;
	v_military_dsp	    NUMBER	 := 0;

BEGIN
	
-- Set Accrual Rates for Employee 
  	 
-- Set the appropriate accrual rates for vacation & sick time
--  First determine if employee is an intern
BEGIN
   
  SELECT
   UPPER(attribute1)
  INTO
   v_intern_flag
  FROM
   fnd_flex_value_sets     fvs,
   fnd_flex_values         ffv
  WHERE
   ffv.flex_value_set_id = fvs.flex_value_set_id
   AND UPPER(fvs.flex_value_set_name) LIKE 'RIDOT%HR%JOB%CODE%'
   AND UPPER(NVL(ffv.attribute1, 'NO')) = 'YES'
   AND ffv.enabled_flag = 'Y'
   AND NVL(end_date_active, '31-DEC-4712') > TRUNC(sysdate)
   AND ffv.flex_value = :RIDOT_ACCRUAL_BAL_HIST.CLASS_CODE
  ;
		
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     v_intern_flag := 'NO';
		
END;	
 
-- INTERNS DO NOT ACCRUE ANY TIME 
  IF v_intern_flag = 'YES' THEN
     v_vac_accr_rate :=  0.0;
     v_sick_accr_rate := 0.0;
 	 
  ELSE

-- Determine accrual rate of sick time - THIS IS BASED SOLELY ON WORKWEEK
		
       IF :RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK = 40 THEN
	  v_sic_percent_rate := .0625;
	  v_sick_accr_rate := LTRIM(TO_CHAR(ROUND(v_sic_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
		 
       ELSIF :RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK IN (20, 35) THEN
	  v_sic_percent_rate := .0577;
	  v_sick_accr_rate := LTRIM(TO_CHAR(ROUND(v_sic_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
       
       END IF;
		
-- Determine accrual rate of vacation time - THIS IS BASED ON WORKWEEK AND YEARS OF STATE SERVICE 
	
        IF :RIDOT_ACCRUAL_BAL_HIST.years_of_service_pp_based BETWEEN 0 AND 5.00 THEN
	   v_vac_percent_rate := .0308;
	   v_vac_accr_rate := LTRIM(TO_CHAR(ROUND(v_vac_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
				ELSIF :RIDOT_ACCRUAL_BAL_HIST.years_of_service_pp_based BETWEEN 5.01 AND 10.00 THEN
	   v_vac_percent_rate := .0500;
	   v_vac_accr_rate := LTRIM(TO_CHAR(ROUND(v_vac_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
				ELSIF :RIDOT_ACCRUAL_BAL_HIST.years_of_service_pp_based BETWEEN 10.01 AND 15.00 THEN
	   v_vac_percent_rate := .0538;
	   v_vac_accr_rate := LTRIM(TO_CHAR(ROUND(v_vac_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
				ELSIF :RIDOT_ACCRUAL_BAL_HIST.years_of_service_pp_based BETWEEN 15.01 AND 20.00 THEN
	   v_vac_percent_rate := .0615;
	   v_vac_accr_rate := LTRIM(TO_CHAR(ROUND(v_vac_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
				ELSIF :RIDOT_ACCRUAL_BAL_HIST.years_of_service_pp_based BETWEEN 20.01 AND 25.00 THEN
	   v_vac_percent_rate := .0654;
	   v_vac_accr_rate := LTRIM(TO_CHAR(ROUND(v_vac_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK* 2), 1), '999999999.9'));

	ELSE
	   v_vac_percent_rate := .0731;
	   v_vac_accr_rate := LTRIM(TO_CHAR(ROUND(v_vac_percent_rate * (:RIDOT_ACCRUAL_BAL_HIST.STD_WORK_WEEK * 2), 1), '999999999.9'));
					
	END IF;

 END IF;	
            
 -- Assign the derived values to the appropriate display items on the canvas
 -- Accrual Rate Display
 :DSP_VACATION_ACCRUAL_RATE := v_vac_accr_rate;
 :DSP_SICK_ACCRUAL_RATE := v_sick_accr_rate;
 	
 -- Union Description Display
 --:DSP_UNION_DESCRIPTION := v_union_desc;

END;


Thank you in advance,
Steve
Re: Trigger Assistance [message #287595 is a reply to message #287537] Wed, 12 December 2007 19:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

I am not allowed into QueryMode in the application (F11 does not work)
What do you mean by not working?
Are you getting any error?
(Or)
If you enter any value, isn't it returning rows when you press Ctrl+F11?

By
Vamsi
Re: Trigger Assistance [message #287600 is a reply to message #287595] Wed, 12 December 2007 20:54 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Vamsi -

No, I am not receiving any error. Typically, the F11 hotkey will enter query mode in the applications. When I insert the code I posted into a WHEN-NEW-RECORD trigger on the Data Block I am attempting to query, the application will not force the form into query mode. My mouse icon becomes an hourglass for a split second and then returns me to the form in normal mode.

edit - In other words, I cannot fetch any records. The datasource is a view.

-
Steve

[Updated on: Wed, 12 December 2007 20:55]

Report message to a moderator

Re: Trigger Assistance [message #287787 is a reply to message #287537] Thu, 13 December 2007 08:19 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Update:

To give this issue some direction, I will start with the following -

1) Do I have the appropriate trigger to achieve the functionality I want? Am I misunderstanding the use of the WHEN-NEW-RECORD-INSTANCE trigger at the block level? Again, I am trying to fire off logic each time the user changes the record in the block.

2) If I do have the appropriate trigger, are there any contingencies to its use? For example, I inserted the code I posted in the initial thread post to an On-Fetch trigger at the block level. The functionality I wanted was achieved, but because On-Fetch always has a record in a buffer, the trigger fired for every other record and not for each record.

3) Why would the trigger code stop the form from being able to enter query mode? Are there any properties I should check at the form and block level that may be restricting the use of the trigger?

Thank you!
Steve
Re: Trigger Assistance [message #287793 is a reply to message #287537] Thu, 13 December 2007 08:35 Go to previous message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
I have solved the issue.

The WHEN-NEW-RECORD-INSTANCE trigger was not the appropriate trigger. I used a POST-QUERY trigger at the block level and the result was successful. I was under the impression that post-query would fire only for the first record retrieved and not for all records. This is not the case. I swear I tried using the POST-QUERY trigger before and was unsuccessful, but I must have been mistaken.

Thank you for your assistance,
Steve
Previous Topic: showing message on several lines
Next Topic: Problem reading DATEs from Excel via OLE2
Goto Forum:
  


Current Time: Sat Dec 10 01:29:46 CST 2016

Total time taken to generate the page: 0.13078 seconds