Home » Applications » Oracle Fusion Apps & E-Business Suite » custom.pll when-validate-record trigger fires twice and resets value (EBS 11.5, Unix)
icon5.gif  custom.pll when-validate-record trigger fires twice and resets value [message #279459] Thu, 08 November 2007 10:28
schibbl
Messages: 2
Registered: November 2007
Junior Member
Hello,

I've got a misbehaviour of the Orders Form 'OEXOEORD' when changing customer dependent data at a DFF. I observe the CUSTOMER.CUSTOMER_NUMBER field for changes and if so the ORDER.ATTRIBUTE6 - in DFF - should be updated and the whole bunch has to be commited. There's the rub. do_key('commit_form') will fire a WHEN-VALIDATE-RECORD trigger. After this trigger is gone thru custom.pll and the new value of ORDER.ATTRIBUTE6 is set to NULL, a second WHEN-VALIDATE-RECORD trigger enters custom.pll with the changes reversed to old value. So out of custom.pll (inside the Form?) there is something which reverses my changes if the new value is null. If I replace the old attr6 value by a non-null value, the second when-validate-record trigger will not occur (which is good). What is the second occurence of when-validate-record for? I ensured the Info-FlexField-Segments have no validation for this field, its just a 10 char. I also tried to set a null value to chr(0) instead but without any effects.
here is the code:
-- real code starts here
form_name varchar2(30) := name_in('system.current_form'); 
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.current_item');
-- constants for some glob var values
LC_TRUE constant varchar2(1) := 'Y';
LC_FALSE constant varchar2(1) := 'N';
LC_PROGRESS constant varchar2(10) := 'INPROGRESS';
LC_INITIAL constant varchar2(7) := 'INITIAL';
-- counter to keep track of custom.pll instance
l_event_cnt number := 0;
-- marker if customer has changed
l_cust_changed boolean := false;
-- customer number
l_cust_nbr number;
-- new att6 value
l_new_att6 varchar2(30) := null;
BEGIN
  /**
   * JUST FOR DEBUGGING!
   * counts the events caught by custom.pll, to identify the
   * instance which is poping up a window
   */
  IF (event_name='WHEN-NEW-FORM-INSTANCE') THEN
    copy('0','GLOBAL.XX_'||form_name||'_EVENT_CNT');
  ELSE
    l_event_cnt := to_number(name_in('GLOBAL.XX_'||form_name||'_EVENT_CNT'));
    copy(to_number(name_in('GLOBAL.XX_'||form_name||'_EVENT_CNT'))+1,'GLOBAL.XX_'||form_name||'_EVENT_CNT');
  END IF;
  /**
   * JUST FOR DEBUGGING!
   * Observer at start for att6 at order
   */
  IF (form_name IN ('OEXOEORD')) THEN
    IF (event_name='WHEN-NEW-FORM-INSTANCE') THEN
      copy(LC_INITIAL,'GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD');
    END IF;
    IF (event_name = 'WHEN-NEW-ITEM-INSTANCE' 
        AND name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD') = LC_INITIAL 
        AND name_in('ORDER.SOLD_TO_ORG_ID') IS NOT NULL
    ) THEN
      copy(name_in('ORDER.ATTRIBUTE6'),'GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD');
    END IF;
    IF (nvl(name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD'),'<NUL>') != LC_INITIAL
        AND nvl(name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD'),'<NUL>') != nvl(name_in('ORDER.ATTRIBUTE6'),'<NUL>')
    ) THEN
      fnd_message.set_string(l_event_cnt||'/ '||block_name||'.'||item_name||' '||event_name||': start, changed att6 from '||replace(nvl(name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD'),'<NUL>'),chr(0),'chr0')||' to '||replace(nvl(name_in('ORDER.ATTRIBUTE6'),'<NUL>'),chr(0),'chr0'));
      fnd_message.show;
      copy(name_in('ORDER.ATTRIBUTE6'),'GLOBAL.XX_'||form_name||'_ATT6_OBS1_OLD');
    END IF;
  END IF;
  /**
   * REAL CODE
   * Observer for customer number
   */
  IF (form_name IN ('OEXOEORD')) THEN
    IF (event_name='WHEN-NEW-FORM-INSTANCE') THEN
      copy(LC_INITIAL,'GLOBAL.XX_'||form_name||'_CUST_OLD');
    END IF;
    IF (event_name = 'WHEN-NEW-ITEM-INSTANCE' 
        AND name_in('GLOBAL.XX_'||form_name||'_CUST_OLD') = LC_INITIAL 
        AND name_in('ORDER.SOLD_TO_ORG_ID') IS NOT NULL
    ) THEN
      copy(name_in('ORDER.CUSTOMER_NUMBER'),'GLOBAL.XX_'||form_name||'_CUST_OLD');
    END IF;
    IF (nvl(name_in('GLOBAL.XX_'||form_name||'_CUST_OLD'),'<NUL>') != LC_INITIAL
        AND nvl(name_in('GLOBAL.XX_'||form_name||'_CUST_OLD'),'<NUL>') != nvl(name_in('ORDER.CUSTOMER_NUMBER'),'<NUL>')
    ) THEN
      l_cust_changed := true;
      copy(name_in('ORDER.CUSTOMER_NUMBER'),'GLOBAL.XX_'||form_name||'_CUST_OLD'); -- this will prevent from running code below in paralell instance of custom.pll
    END IF;
  END IF;

  /**
   * REAL CODE
   * change dependend data of DFF by customer
   */
  IF (l_cust_changed) THEN
    fnd_message.set_string(l_event_cnt||'/ '||block_name||'.'||item_name||' '||event_name||' starting change of att6');
    fnd_message.show;
    l_cust_nbr := name_in('ORDER.CUSTOMER_NUMBER');
    -- clear old att6 field
    copy(NULL,'ORDER.ATTRIBUTE6');
    -- get data and if there is one write it to att6
    BEGIN
      SELECT myfield INTO l_new_att6 FROM sometable WHERE custnbr = l_cust_nbr AND ROWNUM = 1;
      copy(l_new_att6,'ORDER.ATTRIBUTE6');
    EXCEPTION WHEN no_data_found THEN
      null;
    END;
    -- commit the form -> will trigger when-validate-record on paralell instance
    do_key('commit_form');
    fnd_message.set_string(l_event_cnt||'/ '||block_name||'.'||item_name||' '||event_name||' end of change of att6');
    fnd_message.show;
  END IF;

  /**
   * JUST FOR DEBUGGING!
   * Observer at end for att6 at order
   */
  IF (form_name IN ('OEXOEORD')) THEN
    IF (event_name='WHEN-NEW-FORM-INSTANCE') THEN
      copy(LC_INITIAL,'GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD');
    END IF;
    IF (event_name = 'WHEN-NEW-ITEM-INSTANCE' 
        AND name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD') = LC_INITIAL 
        AND name_in('ORDER.SOLD_TO_ORG_ID') IS NOT NULL
    ) THEN
      copy(name_in('ORDER.ATTRIBUTE6'),'GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD');
    END IF;
    IF (nvl(name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD'),'<NUL>') != LC_INITIAL
        AND nvl(name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD'),'<NUL>') != nvl(name_in('ORDER.ATTRIBUTE6'),'<NUL>')
    ) THEN
      fnd_message.set_string(l_event_cnt||'/ '||block_name||'.'||item_name||' '||event_name||': end, changed att6 from '||replace(nvl(name_in('GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD'),'<NUL>'),chr(0),'chr0')||' to '||replace(nvl(name_in('ORDER.ATTRIBUTE6'),'<NUL>'),chr(0),'chr0'));
      fnd_message.show;
      copy(name_in('ORDER.ATTRIBUTE6'),'GLOBAL.XX_'||form_name||'_ATT6_OBS2_OLD');
    END IF;
  END IF;


Sorry for this amount of code but this is a simplified version for cut and paste, so you can evaluate with ease.

This code will popup following messages if customer is changed and new attribute6 value is null.
- 11/ ORDER.CUSTOMER_NAME WHEN-NEW-ITEM-INSTANCE starting of change att6
- 12/ ORDER. WHEN-VALIDATE-RECORD: start, changed att6 from 4 to <NUL>
- 12/ ORDER. WHEN-VALIDATE-RECORD: end, changed att6 from 4 to <NUL>
- 13/ ORDER. WHEN-VALIDATE-RECORD: start, changed att6 from <NUL> to 4 <--- why is this fired and reverses the null
- 13/ ORDER. WHEN-VALIDATE-RECORD: end, changed att6 from <NUL> to 4
- 11/ ORDER.CUSTOMER_NAME WHEN-NEW-ITEM-INSTANCE end of change att6

After the execution of this code, the displayed value in DFF is 4 as well as in Database. I really do not know what else to do to fix this.
Please help.

Many Thanks in advance.

[Updated on: Fri, 09 November 2007 10:21]

Report message to a moderator

Previous Topic: Installation of EBS on linux
Next Topic: Market for oracle crm functional
Goto Forum:
  


Current Time: Sun Dec 04 04:21:52 CST 2016

Total time taken to generate the page: 0.09917 seconds