Overblog Folge diesem Blog
Administration Create my blog

Posts mit #forms tag

A card-like javabean

16. Juni 2011 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

In some application i've seen a layout where the data is presented in a card-like style, having a picture to the left, a title on top and some text containing additional information. I liked the layout, so i decided that this should also be possible in forms (of course using a java-bean)

The idea

So what should such a component be capable of

  • it should be used as a standard textitem
  • the layout should be fully configurable
  • it should allow showing images
  • it should allow hyperlinks
  • it should look somehow "modern"

The implementation

And thats my approach. 

  • I created a javabean overwriting the standard VTextField. That allows usage in a multi-record block without having to deal with synchronizing content when scrolling
  • The layout is done by an HTMLEditorkit placed in a JEditorPane.
  • The layout is defined by creating a html-"template", containing placeholders to be replaced with the real data at runtime
  • The value is set by simply assigning it to the Bean-Item
  • WHEN-MOUSE-CLICKED-events are raised when clicking on the item or on a link, with a special GET_CUSTOM_PROPERTY you can get the link clicked in the bean (if any)
  • For handling images i use a adjusted version from Francois Degrelles "HandleImage3"-Bean

The code

Here's a first version of the java code

package forms;
import java.awt.Color;
import java.awt.Graphics;
import java.awt.Graphics2D;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.awt.image.BufferedImage;
import java.awt.image.DataBufferInt;

import java.net.URL;

import java.util.Dictionary;
import java.util.EventListener;
import java.util.Hashtable;
import java.util.StringTokenizer;

import javax.swing.ImageIcon;
import javax.swing.JEditorPane;
import javax.swing.event.HyperlinkEvent;
import javax.swing.event.HyperlinkListener;
import javax.swing.text.html.HTMLEditorKit;

import oracle.forms.properties.ID;
import oracle.forms.ui.VTextField;

import sun.misc.BASE64Decoder;
/**
    This is just sample code, its free to use.
    It is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    It is tested against Forms 10.1.2.0.2, but may stop working with any patch or future version of forms
   
    Sample code for a Javabean to implement a carditem
    For usage-notes see the the package PK_CARDITEM, which is the counterpart for this code
    on the forms-side
*/
public class CardTextfield extends VTextField implements MouseListener, HyperlinkListener
{
  /** Constants for edge-style */
  public static final String STYLE_EDGE="E";
  public static final String STYLE_ROUNDED="R";
  /** ID's for forms-events */
  public static final ID TEMPLATE   =ID.registerProperty("TEMPLATE");
  public static final ID CLICKED    =ID.registerProperty("CLICKED");
  public static final ID LINK       =ID.registerProperty("LINK");
  public static final ID RECT_STYLE =ID.registerProperty("RECT_STYLE");
  public static final ID READIMGBASE=ID.registerProperty("READIMGBASE");
  public static final ID SHADOWCOLOR=ID.registerProperty("SHADOWCOLOR");
  public static final ID HOVERCOLOR =ID.registerProperty("HOVERCOLOR");
  /** Imagecache */
  private static Hashtable C_IMAGECACHE=new Hashtable(); 
  /** Constant for opacity */
  private static float     C_OPACITY= 0.99f;
  /** Stringbuffer for uploading images */
  private StringBuffer m_imageBuffer =new StringBuffer();
  /** Size of shadow in pixels */
  private int          m_shadowSize=10;
  /** Shadow-color */
  private Color        m_shadowColor=Color.black; 
  /** Shadow-Color when mouse-focused */
  private Color        m_shadowFocusColor=Color.red; 
  /** The html-editor-pane */
  private JEditorPane  m_htmlEditor=new JEditorPane();
  /** html-template */
  private String       m_template="";
  /** Flag, if mouse is on image */
  private boolean      m_hasFocus=false;
  /** Last "Link" visited in html*/
  private String       m_event=null;
  /** Initialization-flag */
  private boolean      m_first=true; 
  /** Current edge-style */
  private String       m_rectangleStyle=STYLE_EDGE; 
  /** Gridbag-Constraints for Layout */
  private GridBagConstraints m_constraints = new GridBagConstraints();
  /** Gridbag-Layout */
  private GridBagLayout m_layout = new GridBagLayout();
  /** Image for shadow */
  private BufferedImage m_shadowImage=null;       
  /** Image for shadow when mouse-focused */
  private BufferedImage m_hoverImage=null;       

  /**
   * Constructor, set up the layout
   */
  public CardTextfield()
  {
    this.setLayout(m_layout);
    this.addMouseListener(this);
    try
    {
      m_htmlEditor.setEditorKit(new HTMLEditorKit());
      // Seems not available in JInitiator
      //m_htmlEditor.setFocusable(false);
      m_htmlEditor.addHyperlinkListener(this);
      m_htmlEditor.setEditable(false);
       
      m_constraints.fill = GridBagConstraints.BOTH;  
      m_constraints.weightx = 1;                    
      m_constraints.weighty = 1;                    
      m_constraints.insets = new Insets(8,8,12,12);  
      m_constraints.gridx = 0;                       
      m_constraints.gridy = 0;                       
      m_constraints.gridwidth = 0;                   
      m_constraints.gridheight = 0;                  
      m_layout.setConstraints(m_htmlEditor, m_constraints);
      add(m_htmlEditor);
      Dictionary cache=(Dictionary)m_htmlEditor.getDocument().getProperty("imageCache");
      if (cache==null)
      {
        m_htmlEditor.getDocument().putProperty("imageCache",C_IMAGECACHE);
      }

    } catch (Exception e)
    {
    }
  }

  /**
   * Create the rectangle-image and apply the shadow
   * @return Image
   * @param c Color for shadow
   * @param height height of the image
   * @param width width of the image
   */
  private BufferedImage createShadowRectangle(int width, int height, Color c)
  {
    BufferedImage subject = new BufferedImage(width + m_shadowSize * 2,
                                              height + m_shadowSize * 2,
                                              BufferedImage.TYPE_INT_ARGB);

    Graphics2D g2 = subject.createGraphics();
 
    g2.setPaint(Color.black);
    if (STYLE_EDGE.equals(m_rectangleStyle))
    {
      g2.fillRect(1, 1, width, height);
    } else
    {
      g2.fillRoundRect(1, 1, width, height, 25, 25);
    }
    g2.dispose();

    applyShadow(subject, c);
    return subject;
  }

  /**
   * applys a shadow to the given image, taken from
   * Romain Guy's blog at http://jroller.com/gfx/entry/non_rectangular_shadow
   * @param c color for the shadow
   * @param image imgae to apply a shadow on
   */
  private void applyShadow(BufferedImage image, Color c)
  {
    int dstWidth = image.getWidth();
    int dstHeight = image.getHeight();

    int left = (m_shadowSize - 1) >> 1;
    int right = m_shadowSize - left;
    int xStart = left;
    int xStop = dstWidth - right;
    int yStart = left;
    int yStop = dstHeight - right;

    int shadowRgb = c.getRGB() & 0x00FFFFFF;

    int[] aHistory = new int[m_shadowSize];
    int historyIdx = 0;

    int aSum;

    int[] dataBuffer = ((DataBufferInt) image.getRaster().getDataBuffer()).getData();
    int lastPixelOffset = right * dstWidth;
    float sumDivider = C_OPACITY/m_shadowSize;

    // horizontal pass

    for (int y = 0, bufferOffset = 0; y < dstHeight; y++, bufferOffset = y * dstWidth)
    {
      aSum = 0;
      historyIdx = 0;
      for (int x = 0; x < m_shadowSize; x++, bufferOffset++)
      {
        int a = dataBuffer[bufferOffset] >>> 24;
        aHistory[x] = a;
        aSum += a;
      }

      bufferOffset -= right;

      for (int x = xStart; x < xStop; x++, bufferOffset++)
      {
        int a = (int) (aSum * sumDivider);
        dataBuffer[bufferOffset] = a << 24 | shadowRgb;

        // substract the oldest pixel from the sum
        aSum -= aHistory[historyIdx];

        // get the lastest pixel
        a = dataBuffer[bufferOffset + right] >>> 24;
        aHistory[historyIdx] = a;
        aSum += a;

        if (++historyIdx >= m_shadowSize)
        {
          historyIdx -= m_shadowSize;
        }
      }
    }

    // vertical pass
    for (int x = 0, bufferOffset = 0; x < dstWidth; x++, bufferOffset = x)
    {
      aSum = 0;
      historyIdx = 0;
      for (int y = 0; y < m_shadowSize; y++, bufferOffset += dstWidth)
      {
        int a = dataBuffer[bufferOffset] >>> 24;
        aHistory[y] = a;
        aSum += a;
      }

      bufferOffset -= lastPixelOffset;

      for (int y = yStart; y < yStop; y++, bufferOffset += dstWidth)
      {
        int a = (int) (aSum * sumDivider);
        dataBuffer[bufferOffset] = a << 24 | shadowRgb;

        // substract the oldest pixel from the sum
        aSum -= aHistory[historyIdx];

        // get the lastest pixel
        a = dataBuffer[bufferOffset + lastPixelOffset] >>> 24;
        aHistory[historyIdx] = a;
        aSum += a;

        if (++historyIdx >= m_shadowSize)
        {
          historyIdx -= m_shadowSize;
        }
      }
    }
  }

  /**
   * Paint the component. At first call, duplicate mouse-listeners from
   * the item to the thml-editor
   * @param g Graphics-Context
   */
  public void paint(Graphics g)
  {
   
    if (m_first)
    {
      // Move Mouselisteners forward to html-editor
      // Seems not available in JInitiator changed to generic getListeners
      EventListener[] ml=getListeners(MouseListener.class);
      for (int i=0;i<ml.length;i++)
      {
        m_htmlEditor.addMouseListener((MouseListener)ml[i]);
      }
      m_first=false;
    }
   
    Graphics2D g2=(Graphics2D)g;
    g2.setPaint(getBackground());
    g2.fillRect(0, 0, getBounds().width, getBounds().height);
    if (m_hasFocus)
    {
      if (m_hoverImage==null)
      {
        m_hoverImage=createShadowRectangle(getBounds().width-10, getBounds().height-10, m_shadowFocusColor);
      }
      g2.drawImage(m_hoverImage, 3, 3, null);
    } else
    {
      if (m_shadowImage==null)
      {
        m_shadowImage=createShadowRectangle(getBounds().width-10, getBounds().height-10, m_shadowColor);
      }
      g2.drawImage(m_shadowImage, 3, 3, null);
    }
    g2.setPaint(getForeground());
    if (STYLE_EDGE.equals(m_rectangleStyle))
    {
      g2.fillRect(3, 3, getBounds().width-10, getBounds().height-10);
    } else
    {
      g2.fillRoundRect(3, 3, getBounds().width-10, getBounds().height-10, 25, 25);
    }
     // Apply forground to html-editors background
    m_htmlEditor.setBackground(getForeground());
    // Sub-components
    paintComponents(g);
  }
 
  /**
   * Apply the given values to the template
   * @param text values, concatenated
   */
  public void setValue(String text)
  {
    String result="";
    if (text!=null && !"".equals(text))
    {
      result=m_template;
      StringTokenizer st=new StringTokenizer(text, "|");
      while (st.hasMoreTokens())
      {
        String field=null;
        String value=null;
        if (st.hasMoreTokens())
        {
          field=st.nextToken();
        }
        if (st.hasMoreTokens())
        {
          value=st.nextToken();
        }
        if (field!=null && value!=null)
        {
          int pos=result.indexOf("#"+field+"#");
          while (pos>-1)
          {
            result=result.substring(0, pos)+value+result.substring(pos+field.length()+2);
            pos=result.indexOf("#"+field+"#");
          }
        }
      }
    } else
    {
      result="<html>&nbsp;</html>";
    }
    m_htmlEditor.setText(result);
  }
 
  /**
   * Standard Method, overwritten to make the bean-specific properties from forms
   * @return true
   * @param value
   * @param id   
   */
  public boolean setProperty(ID id, Object value)
  {
    if (id==TEMPLATE)
    {
      m_template=(String)value;
      setValue(getText());
      return true;
    } else if (id==RECT_STYLE)
    {
      // Force shadow to be recalculated
      m_shadowImage=null;
      m_hoverImage=null;
      m_rectangleStyle=(String)value;
      if (STYLE_EDGE.equals(m_rectangleStyle))
      {
        m_constraints.insets = new Insets(2,2,6,6);  
        m_layout.setConstraints(m_htmlEditor, m_constraints);
       
      } else
      {
        m_constraints.insets = new Insets(8,8,12,12);  
        m_layout.setConstraints(m_htmlEditor, m_constraints);
      }

      return true;
    } else if (id==SHADOWCOLOR)
    {
      // R,G,B values, divided by |
      StringTokenizer st=new StringTokenizer((String)value, "|");
      int r=Integer.parseInt(st.nextToken());
      int g=Integer.parseInt(st.nextToken());
      int b=Integer.parseInt(st.nextToken());
      m_shadowColor=new Color(r, g, b);
      m_shadowImage=null;
      return true;
    } else if (id==HOVERCOLOR)
    {
      // R,G,B values, divided by |
      StringTokenizer st=new StringTokenizer((String)value, "|");
      int r=Integer.parseInt(st.nextToken());
      int g=Integer.parseInt(st.nextToken());
      int b=Integer.parseInt(st.nextToken());
      m_shadowFocusColor=new Color(r, g, b);
      m_hoverImage=null;
      return true;
    } else if (id==ID.VALUE)
    {
      super.setProperty(id, "");
      setValue((String)value);
      return true;
    } else if (id == READIMGBASE) 
    {       
      String imageData = value.toString();
      if(!imageData.startsWith("[END_IMAGE]"))
      {
        m_imageBuffer.append(imageData);
      }
      else
      {
        // extract name of icon
        String name=imageData.substring(11);
        BASE64Decoder decoder = new BASE64Decoder();
        try
        {
          byte[] decodedStr = decoder.decodeBuffer(m_imageBuffer.toString());
          ImageIcon ii = new ImageIcon(decodedStr);
          URL u=new URL(name);
          C_IMAGECACHE.put(u, ii.getImage());
        } catch(Exception e)
        {
        }
        finally
        {
          m_imageBuffer =new StringBuffer();
        }
      }
      return true ;
    } else if (id == ID.BACKGROUND)
    {
      super.setProperty(id, value);
      this.repaint();
      return true;
    } else if (id == ID.FOREGROUND)
    {
      super.setProperty(id, value);
      this.repaint();
      return true;
    } else
    {
      /** Delegate */
      return super.setProperty(id, value);
    }
  }

  public void mouseClicked(MouseEvent e) {}
 
  public void mousePressed(MouseEvent e) {}
 
  public void mouseReleased(MouseEvent e) {}
 
  /**
   * Mouse-entered event,  remember flag for colored-shadow
   * @param e
   */
  public void mouseEntered(MouseEvent e)
  {
    m_hasFocus=true;
    this.repaint();
  }
 
  /**
   * Mouse-exited event,  reset flag for colored-shadow
   * @param e
   */
  public void mouseExited(MouseEvent e)
  {
    m_hasFocus=false;
    this.repaint();
  }
 
  /**
   * Hyperlink-Listener, remembers last visited link in the document
   * @param hyperlinkEvent
   */
  public void hyperlinkUpdate(HyperlinkEvent hyperlinkEvent)
  {
    HyperlinkEvent.EventType type = hyperlinkEvent.getEventType();
    URL url = hyperlinkEvent.getURL();
    if (type == HyperlinkEvent.EventType.ENTERED)
    {
      m_event=hyperlinkEvent.getURL().toString();
    } else if (type == HyperlinkEvent.EventType.ACTIVATED)
    {
    } else if (type == HyperlinkEvent.EventType.EXITED)
    {
      m_event=null;
    } 
  }

  /**
   * Standard Method, overwritten to return the bean-specific properties to forms
   * @return value
   * @param id   
   */
  public Object getProperty(ID id)
  {
    if (id==LINK)
    {
      // return the last activated link
      return m_event;
    } else
    {
      return super.getProperty(id);
    }
  } 
}

And here the forms-code

PACKAGE PK_CARDITEM IS
/**
   
    This is just sample code, its free to use.
    It is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    It is tested against Forms 10.1.2.0.2, but may stop working with any patch or future version of forms

    Sample code for a forms-Side Package for the Carditem Java-Bean

    To build up a carditem, follow these steps
   
    -Create a textitem which the appropiate size and set the implementation class to forms.CardItem
    -Create a WHEN-MOUSE-CLICKED-trigger at the item with a call to FK_GET_CLICKED_LINK
    -For some internal reason you have to have another visible item in your block (maybe a dummy-item with size 0x0),
     and you have to have a WHEN-NEW-ITEM-INSTANCE-trigger on your carditem with a NEXTITEM or a PREVIOUSITEM in it.
    -Assign the needed values to the Card-Item in an appropiate trigger, maybe the POST-QUERY-trigger of your block with code like
   
    DECLARE
        lValues PK_CARDITEM.tValueList;
        rValue  PK_CARDITEM.tValue;
    BEGIN
          -- Fill the variables for the html-item
          rValue.vcField:='NAME';
          rValue.vcValue:=:EMP.FIRST_NAME ||' ' || :EMP.LAST_NAME;
          lValues(1):=rValue;
         
          rValue.vcField:='DATE';
          rValue.vcValue:=TO_DATE(:EMP.HIRE_DATE);
          lValues(2):=rValue;

          rValue.vcField:='SALARY';
          rValue.vcValue:=:EMP.SALARY;
          lValues(3):=rValue;

          :EMP.B:=PK_CARDITEM.FK_GET_ITEM_VALUE('EMP.B', lValues);
    END
   
    -make sure the jar is on the archive or achive_jini-tag   
  
   Template
   - The template is built-up using standard-html. Everything which can be rendered by a JEditorPane can be used
     which means, you have to test what works. If you want to put some variable, data-specific content in the html,
     simply put the name of this "field" in the html enclosed by #, e.g. <P><FONT STYLE="font-size: 13pt">#DEPT#</FONT></P></TD>.
     The dept will be replaced at runtime with the value passed via the FK_GET_ITEM_VALUE.
    
   Using images and links you can use images in your
   - You need the Database-package Pkg_Read_Blob_Image from Francois Degrelle's advanced image javabean
   - You can use images and hyperlinks in your html-content
     + Links. You can use any link. If you want to use "virtual links" to call some forms-logic, make sure it starts with http://
     + Images. You can use real urls for images or use PR_READ_IMAGE to load an image to the carditem. You can use any url for the
       image-link, you only have to make sure that the url starts with http://
*/
  -- Type used for value-passing
  TYPE tValue IS RECORD (
    vcField VARCHAR2(30),
    vcValue VARCHAR2(4000)
  );
  -- List-Type used for value-passing
  TYPE tValueList IS TABLE OF tValue INDEX BY BINARY_INTEGER;
 
  /** Initialization-method for a card-item
      i_vcItem           name of the item which represents the carditem
      i_vcTemplate       HTML-template to show the content of the item
      i_vcStyle          Borderstyle, either R for rounded rectangle or E for edged rectangle
  */   
  PROCEDURE PR_INIT(i_vcItem     IN VARCHAR2,
                    i_vcTemplate IN VARCHAR2,
                    i_vcStyle    IN VARCHAR2);
                   
  /** Setter for the template
      i_vcItem           name of the item which represents the carditem
      i_vcTemplate       HTML-template to show the content of the item
  */
  PROCEDURE PR_SET_TEMPLATE(i_vcItem     IN VARCHAR2,
                            i_vcTemplate IN VARCHAR2);
                   
 
  /** Setter for the shadow-Color
      i_vcItem           name of the item which represents the carditem
      i_nRed             Red-part of color
      i_nGreen           Green-part of color
      i_nBlue            Blue-part of color
     
  */
  PROCEDURE PR_SET_SHADOW_COLOR(i_vcItem IN VARCHAR2,
                                i_nRed   IN NUMBER,
                                i_nGreen IN NUMBER,
                                i_nBlue  IN NUMBER);
                               
  /** Setter for the hover-Color, when the mouse moves over the carditem
      i_vcItem           name of the item which represents the carditem
      i_nRed             Red-part of color
      i_nGreen           Green-part of color
      i_nBlue            Blue-part of color
     
  */
  PROCEDURE PR_SET_HOVER_COLOR(i_vcItem IN VARCHAR2,
                               i_nRed   IN NUMBER,
                               i_nGreen IN NUMBER,
        
                               i_nBlue  IN NUMBER);
                              
  /** Computes the value which has to be set to the item
      i_vcItem           name of the item which represents the carditem
      i_lValues          List of Key and Values
     
      RETURN             VARCHAR2-Value which has to be set as the values item
  */
  FUNCTION FK_GET_ITEM_VALUE(i_vcItem  IN VARCHAR2,
                             i_lValues IN tValueList)
  RETURN VARCHAR2;
 
  /** WHEN-MOUSE-CLICKED-trigger for the carditem
      i_vcItem           name of the item which represents the carditem
     
      RETURN             NULL if the item was clicked, name of a href-tag if it was clicked
  */
  FUNCTION FK_GET_CLICKED_LINK(i_vcItem IN VARCHAR2)
  RETURN VARCHAR2;

  /** WHEN-MOUSE-CLICKED-trigger for the carditem
      i_vcItem           name of the item which represents the carditem
      i_vcQueryForBlob   Query which points to the BLOB of the image to be read
      i_vcImageUrl       Name under which the image will be referenced in the carditem
  */
    PROCEDURE PR_READ_IMAGE(i_vcItem         IN VARCHAR2,
                            i_vcQueryForBlob IN VARCHAR2,
                            i_vcImageUrl     IN VARCHAR2);
 
END;

PACKAGE BODY PK_CARDITEM IS

  PROCEDURE PR_INIT(i_vcItem     IN VARCHAR2,
                    i_vcTemplate IN VARCHAR2,
                    i_vcStyle    IN VARCHAR2) IS
  BEGIN
    SET_CUSTOM_ITEM_PROPERTY(i_vcItem, 'TEMPLATE',   i_vcTemplate);
    SET_CUSTOM_ITEM_PROPERTY(i_vcItem, 'RECT_STYLE', i_vcStyle);
  END;   
     
  -- -------------------------------------------------------------------------------     
                   
  PROCEDURE PR_SET_TEMPLATE(i_vcItem     IN VARCHAR2,
                            i_vcTemplate IN VARCHAR2) IS
  BEGIN
    SET_CUSTOM_ITEM_PROPERTY(i_vcItem, 'TEMPLATE',   i_vcTemplate);
  END;                                 
                   
  -- -------------------------------------------------------------------------------     
 
  PROCEDURE PR_SET_SHADOW_COLOR(i_vcItem IN VARCHAR2,
                                i_nRed   IN NUMBER,
                                i_nGreen IN NUMBER,
                                i_nBlue  IN NUMBER) IS
  BEGIN
      SET_CUSTOM_ITEM_PROPERTY(i_vcItem,'SHADOWCOLOR', TO_CHAR(i_nRed)   || '|' ||
                                                       TO_CHAR(i_nGreen) || '|' ||
                                                       TO_CHAR(i_nBlue)  || '|');
  END;                               
  -- -------------------------------------------------------------------------------     

  PROCEDURE PR_SET_HOVER_COLOR(i_vcItem IN VARCHAR2,
                               i_nRed   IN NUMBER,
                               i_nGreen IN NUMBER,
                               i_nBlue  IN NUMBER) IS
  BEGIN
      SET_CUSTOM_ITEM_PROPERTY(i_vcItem,'HOVERCOLOR', TO_CHAR(i_nRed)   || '|' ||
                                                      TO_CHAR(i_nGreen) || '|' ||
                                                      TO_CHAR(i_nBlue)  || '|');
  END;                                                     
                              
  -- -------------------------------------------------------------------------------     

  FUNCTION FK_GET_ITEM_VALUE(i_vcItem  IN VARCHAR2,
                             i_lValues IN tValueList)
  RETURN VARCHAR2 IS
    vcValue VARCHAR2(32000);
  BEGIN
      FOR i IN 1..i_lValues.COUNT LOOP
          vcValue:=vcValue || i_lValues(i).vcField || '|' || REPLACE(i_lValues(i).vcValue, '|', ' ') || '|';
      END LOOP;
      RETURN vcValue;
  END;                               
 
  -- -------------------------------------------------------------------------------     

  FUNCTION FK_GET_CLICKED_LINK(i_vcItem IN VARCHAR2)
  RETURN VARCHAR2 IS
        nTopRecord NUMBER:=GET_BLOCK_PROPERTY(SUBSTR(i_vcItem, 1, INSTR(i_vcItem, '.')-1), TOP_RECORD);
        vcLink     VARCHAR2(2000);
    BEGIN
      IF :SYSTEM.MOUSE_RECORD!=:SYSTEM.CURSOR_RECORD THEN
          GO_RECORD(:SYSTEM.MOUSE_RECORD);
      END IF;
      vcLink:=GET_CUSTOM_PROPERTY(i_vcItem, :SYSTEM.MOUSE_RECORD-nTopRecord+1, 'LINK');
      RETURN vcLink;
    END;

  -- -------------------------------------------------------------------------------     

    PROCEDURE PR_READ_IMAGE(i_vcItem         IN VARCHAR2,
                            i_vcQueryForBlob IN VARCHAR2,
                            i_vcImageUrl     IN VARCHAR2) IS
      LB$Ok      boolean ;
      LC$Image   Varchar2(32767) ;
      LC$Clause  Varchar2(4000) ;
    BEGIN
       
        --
        -- Read an image from the database
        --
        LC$Clause := i_vcQueryForBlob;
        -- Select the Blob column --
        If Pkg_Read_Blob_Image.Select_Blob(LC$Clause) Then
             Loop
               -- Get the image chunks from the database --
               LC$Image := Pkg_Read_Blob_Image.Get_B64_Chunk ;
               If LC$Image Is Not Null Then
                 -- Send the chunks to the Java Bean --
                 Set_Custom_Property( i_vcItem, 1, 'READIMGBASE', LC$Image ) ;
               Else
                    -- End the sending process --
                    Set_Custom_Property( i_vcItem, 1, 'READIMGBASE', '[END_IMAGE]' || i_vcImageUrl) ;
                    Exit ;
               End if ;   
             End loop ;
        End if ; 
    END; 
END;

 

This video shows the usage of the bean with the Oracle demo-tables EMPLOYEES (extended with a column called PHOTO to contain an image for each employee)

A compiled version with a demo-fmb can be found at Francois Degrelle's PJC-site here

Weiterlesen

Pasting Excel-data to a forms-block

8. Mai 2011 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

There was a thread in the Forms-forum these days dealing with Copy&Paste-problems, especially with the question if its possible to copy whole rows into a forms-block. As this is structured
data and. of course, forms doesn't know how do deal with that structured data "automatically". But there is a way to deal with that problem using standard PL/SQL-methods.

Example

Lets say that some manager has entered data about new employees in an excel-sheet. And now the manager wants to transfer that data to the "legay-system"without having to type everything again. So
i use a tabular form based on the EMPLOYEES-table from the demo-schema.

How to do it

When you have a closer look about what gets pasted into the clipboard when you have copied some rows and columns  from excel. The data is pure "textdata", each row is delimited by a CHR(10).
In each row, the different cell-values are separated by a CHR(9). So. if we know the stucture of the excel-data, its quite easy to slice it down to the differents rows and columns and transfer
the data to forms-items.
 

As we have to do some "special work" on pasting, i start the pasting-action using a button.

Here's the code behing the "Paste"-Button

 

DECLARE
  vcBuffer VARCHAR2(32000);
  vcLine   VARCHAR2(32000);
  VARCHAR2(4000);

  FUNCTION FK_SPLIT(io_vcText IN OUT VARCHAR2, i_vcSplitChar IN VARCHAR2)
  RETURN VARCHAR2 IS
    iPos     PLS_INTEGER:=NVL(INSTR(io_vcText, i_vcSplitChar),0);
    vcResult VARCHAR2(4000);
  BEGIN
    IF iPos>0 THEN    
      vcResult:=SUBSTR(io_vcText, 1, iPos-1);     
      io_vcText:=SUBSTR(io_vcText, iPos+1);  
    ELSE     
      vcResult:=io_vcText;     
      io_vcText:=NULL;  
    END IF;   
    RETURN vcResult; 
  END;
 
  BEGIN     
    GO_ITEM('BL_CONTROL.IT_PASTE'); 
    PASTE_REGION; 
    GO_BLOCK('EMPLOYEES'); 
    vcBuffer:=:BL_CONTROL.IT_PASTE; 
    LOOP
      EXIT WHEN vcBuffer IS NULL;   
      -- Next line   
      vcLine:=FK_SPLIT(vcBuffer, CHR(10));   
      IF :SYSTEM.RECORD_STATUS!='NEW' THEN     
        CREATE_RECORD;   
      END IF;   
      :EMPLOYEES.EMPLOYEE_ID:=TO_NUMBER(FK_SPLIT(vcLine, CHR(9)));   
      :EMPLOYEES.FIRST_NAME:=FK_SPLIT(vcLine,CHR(9));   
      :EMPLOYEES.LAST_NAME:=FK_SPLIT(vcLine, CHR(9));   
      :EMPLOYEES.EMAIL:=FK_SPLIT(vcLine, CHR(9));   
      :EMPLOYEES.HIRE_DATE:=TO_DATE(FK_SPLIT(vcLine, CHR(9)), 'DD.MM.YYYY'); 
    END LOOP;
  END;
END;


Of course, this dummy-Item :BL_CONTROL.IT_PASTE on the canvas could be made to have a size of (0,0) in a "real" application.

Weiterlesen

Building a treetable-like form (part 2)

5. August 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

My last post was about "building a treetable-like form", it ended with a functional version which didn't look very sophisticated. Lets change that.

An example

I'll use the same example as in part 1, the EMPLOYEES-table from hr.

The approach

The problem is not to read the hierarchical data, but to build a nice user interface to display the data. The main difference between a tree and a treetable is, that in a treetable you have a "standard" datarow with separate fields for each column, which, if needed, may also be changed in the treetable. If you scroll through the data, the table scrolls with the data. To achieve this, the component to display the tree must be part of the formsblock, so that for each row of the data there is a piece of tree rendered. So the tree-structure has to be splitted into record-based pieces, each of which has informastion about the current node, but also about preceding and following node. But what different types of renderings are possible in a tree?

Each "record" in the hierarchy exists of a number of square blocks, one for each level of the tree-depth the current row is located in. For each of this "squares" there are different possible renderings, depending on the row itself, but also depending on the preceding and following rows. In detail there are 4 possibilities, i colored different in the example.


Red - The rendering in the red square indicates that the current row is the last one at the specific level (the parent of the row has no other childs).
Green - The green square rendering indicates that there is another row at the same level as the current row (the parent of the row has another child).
Yellow/Blue - These rendering doesn't indicate a state for the current row, but for rows at a higher level. For each higher level there is one square, an yellow square means that the preceding row at the specific levels has no following rows at the same level, the blue one indicates that it has.
So, the treelines are build up of 4 different graphical objects, followed by the node-state, icon and label we also had so far.

If we can provide this information to a javabean-item, its quite simple to implement a treelike rendering. We would provide 1-for the red square, 3-for the yellow square, 2 for the green square, 4 for the blue square
In the example, for the different rows, we would have to provide
 

1 Expanded King
32 Collapsed Zlotkey
32 Collapsed Weiss
32 Collapsed Vollman
32 Expanded Russel
342 Collapsed Tuvault
...    


Row 1 is the top-level node, so there is only one level of tree-rendering and this will be 1 for the red-square-style. Row 2 is a row at tree-level 2, so it has two "squares" to be rendered first a 3 for an empty square, followed by a 2 for the green one. This information is provided for every row, so the complete tree gets rendered like a puzzle of square-blocks.

Fetching the data

So, how to get that data in a single query? After some playing around i came up to this query:

SELECT MASTER,
       DETAIL,
       NEXTDETAIL,
       REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(PREV, '#'), 1,LENGTH(SYS_CONNECT_BY_PATH(PREV, '#'))-1),'#', '') ||
       AKT    || '|' ||
       STATUS || '|' ||
       ICON   || '|' ||
       LABEL            NODESTATE,
       EMPLOYEE_ID,
       LAST_NAME,
       FIRST_NAME,
       EMAIL,
       HIRE_DATE
-- -------------------------------------------------------------------------------------
FROM (SELECT MASTER,
             DETAIL,
             NEXTDETAIL,
             CASE WHEN NO_OF_SAME_LEVEL=COUNT_OF_SAME_LEVEL THEN
               '1'
             ELSE
               '2'
             END AKT,
             CASE WHEN DETAIL IN (SELECT COLUMN_VALUE
                                    FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                                 )
             THEN
               'E'
             ELSE
               'C'
             END STATUS,
             CASE WHEN NO_OF_SAME_LEVEL=COUNT_OF_SAME_LEVEL THEN
               '3'
             ELSE
               '4'
             END PREV,
             ICON,
             LABEL,
             EMPLOYEE_ID,
             LAST_NAME,
             FIRST_NAME,
             EMAIL,
             HIRE_DATE
-- -------------------------------------------------------------------------------------
      FROM (SELECT MASTER,
                   DETAIL,
                   LEAD(MASTER,1) OVER (ORDER BY ROWNUM ASC)               NEXTDETAIL,
                   COUNT(*) OVER (PARTITION BY MASTER ORDER BY ROWNUM ASC) NO_OF_SAME_LEVEL,
                   COUNT(*) OVER (PARTITION BY MASTER)                     COUNT_OF_SAME_LEVEL,
                   ICON,
                   LABEL,
                   EMPLOYEE_ID,
                   LAST_NAME,
                   FIRST_NAME,
                   EMAIL,
                   HIRE_DATE
-- -------------------------------------------------------------------------------------
              FROM (SELECT NVL(MANAGER_ID,-1)             MASTER,
                           EMPLOYEE_ID                    DETAIL,
                           'db_user.gif'                  ICON,
                           LAST_NAME || ',' || FIRST_NAME LABEL,
                           EMPLOYEE_ID,
                           LAST_NAME,
                           FIRST_NAME,
                           EMAIL,
                           HIRE_DATE
                      FROM EMPLOYEES
                    START WITH MANAGER_ID IS NULL
                    CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
                    ORDER SIBLINGS BY LAST_NAME
                   )
             WHERE MASTER IN (SELECT COLUMN_VALUE
                                FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                             )
            ORDER BY ROWNUM
           )
       )           
  START WITH MASTER=-1
  CONNECT BY PRIOR DETAIL=MASTER
 ;


 
The query looks quite complicated at first glance, i'll try to explain it a little beginning form the inner query.
The inner query does a hierarchical query on the employees-table and queries the pseudo-columns

 

MASTER ID of the master-record
DETAIL ID of the record
ICON Icon-Name to be rendered in the tree
LABEL additional text next to the icon


in addition to the "real" table-column. These pseudo-columns are used in the outer queries to determine the node-states and "squares" for the tree-rendering-part.
The second query restricts the data to the "expanded" data hold in the pl/sql-table in the db-package and does some analytical functions on the data, which is needed for the determination of the tree-states.

 

NEXTDETAIL is the master of the next record
NO_OF_SAME_LEVEL is the number of rows on the same level so far
COUNT_OF_SAME_LEVEL is the total number of rows on the same level


The third query now takes the data provided and transforms it as required.

The last query does again a hierarchical query over the result and concatenates the needed values for the rendering bean (the SYS_CONNECT_BY_PATH-part).

 

The javabean

The task for the javabean now is quite simple. My simple overrides the standard-textitem, so that the value is always passed correctly to the bean when the record-navigation in forms takes place (if i would took a Bean-Area i had to do theis synchronization by myself). The data the bean gets consists of 3 parts, separated by pipe:

<square-numbers(1,2,3,4)>|<state (C,E)>|<icon>

The bean now renders one of the four squares for each level given in the bean-value, followed by the appropiate node-state-icon (collapsed or expanded), followed by the custom icon. Here the prototype code

package forms.client;
import java.awt.Graphics;
import java.awt.Image;
import java.awt.Rectangle;
import java.awt.Toolkit;
import java.net.URL;
import java.util.StringTokenizer;
import javax.swing.ImageIcon;
import oracle.forms.handler.IHandler;
import oracle.forms.properties.ID;
import oracle.forms.ui.VTextField;

public class TreeStateItem extends VTextField
{
  private ImageIcon m_collapsed=new ImageIcon(this.getClass().getResource("collapsed.png"));
  private ImageIcon m_expanded=new ImageIcon(this.getClass().getResource("expanded.png"));  
  private Image     m_image;
  private String    m_lastImage="";
  private URL       m_codeBase;  
  private String    m_status;
  private String    m_nodestates;
  private String    m_label;
  private String    m_value="";

 
  public void init(IHandler handler)
  {
    // Remember Codebase
    m_codeBase = handler.getCodeBase();
    super.init(handler);
  }
 
  public void paint(Graphics g)
  {
    Rectangle r=this.getBounds();
    g.setColor(getBackground());
    g.fillRect(0, 0, (int)r.getWidth(), (int)r.getHeight());
    g.setColor(getForeground());
    // Draw lines for nodestates
    if (m_nodestates!=null)
    {
      char[] c=m_nodestates.toCharArray();
      for (int i=0;i<c.length;i++)
      {
        switch (c[i])
        {
          case '1' : g.drawLine(i*16+8, 0, i*16+8, (int)(r.getHeight()/2));
                     g.drawLine(i*16+8, (int)(r.getHeight()/2), i*16+15, (int)(r.getHeight()/2));
                     break;
          case '2' : g.drawLine(i*16+8, 0, i*16+8, (int)r.getHeight());
                     g.drawLine(i*16+8, (int)(r.getHeight()/2), i*16+15, (int)(r.getHeight()/2));
                     break;
          case '3' : break;
          case '4' : g.drawLine(i*16+8, 0, i*16+8, (int)r.getHeight());
                     break;
        }
      }
      if ("E".equals(m_status))
      {
        g.drawImage(m_expanded.getImage(), (c.length)*16+1, 3, 16, 16, m_expanded.getImageObserver());
      } else if ("C".equals(m_status))
      {
        g.drawImage(m_collapsed.getImage(), (c.length)*16+1, 3, 16, 16, m_collapsed.getImageObserver());
      }
      if (m_image!=null)
      {
        g.drawImage(m_image, (c.length+1)*16+1, 1, 16, 16, null);
      }
    }
  }
 
  private void loadImage(String imageName)
  {
    // LoadImage, taken from oracle-demo RolloverButton
    URL imageURL = null;
    boolean loadSuccess = false;
    if (!(imageName==null || "".equals(imageName)))
    {
      if (!imageName.equals(m_lastImage))
      {
        //JAR
        imageURL = getClass().getResource("/"+imageName);
        if (imageURL != null)
        {
          try
          {
            m_image = Toolkit.getDefaultToolkit().getImage(imageURL);
            loadSuccess = true;
          }
          catch (Exception ilex)
          {
          }
        }
        //DOCBASE
        if (!loadSuccess)
        {
          try
          {
            if (imageName.toLowerCase().startsWith("http://")||imageName.toLowerCase().startsWith("https://"))
            {
              imageURL = new URL(imageName);
            }
            else
            {
              imageURL = new URL(m_codeBase.getProtocol() + "://" + m_codeBase.getHost() + ":" + m_codeBase.getPort() + imageName);
            }
            try
            {
              m_image = createImage((java.awt.image.ImageProducer) imageURL.getContent());
              loadSuccess = true;
            }
            catch (Exception ilex)
            {
            }
          }
          catch (java.net.MalformedURLException urlex)
          {
          }
        }
        //CODEBASE
        if (!loadSuccess)
        {
          try
          {
            imageURL = new URL(m_codeBase, imageName);
            try
            {
              m_image = createImage((java.awt.image.ImageProducer) imageURL.getContent());
              loadSuccess = true;
            }
            catch (Exception ilex)
            {
            }
          }
          catch (java.net.MalformedURLException urlex)
          {
          }
        }
        // remember last image
        m_lastImage=imageName;
      }
    }
  }
 
  public boolean setProperty(ID property, Object value)
  {
    if (property==ID.VALUE)
    {
      // "Catch" the value, tokenize it and store it in member vars
      m_value=(String)value;
      StringTokenizer st=new StringTokenizer(m_value, "|");
      m_nodestates="";
      m_status="";
      m_label="";
      if (st.hasMoreTokens())
      {
        m_nodestates=st.nextToken();
      }
      if (st.hasMoreTokens())
      {
        m_status=st.nextToken();
      }
      String imagename=null;
      if (st.hasMoreTokens())
      {
        imagename=st.nextToken();
        loadImage(imagename);
      }
      if (st.hasMoreTokens())
      {
        m_label=st.nextToken();
      }
      if (imagename==null)
      {
        m_image=null;
        m_lastImage=null;
      }
      // pass an empty value to the superclass
      return super.setProperty(property, "");
    } else
    {
      return super.setProperty(property, value);
    }
  }

  public Object getProperty(ID property)
  {
    // TODO:  Override this oracle.forms.ui.VTextField method
    if (property==ID.VALUE)
    {
      // return the locally stored value
      return m_value;
    } else
    {
      return super.getProperty(property);
    }
  }
}

 

 

The forms-part

Putting everything together, there is not much logic in the form itself. I created a view based on the formerly discussed query. Next step is to create a tabular block based on that view.
Then, add the triggers as discussed in part 1 of the article. Last, set the implementation class of the item containing the node-state to forms.client.TreeStateItem. And finally, as for every java-bean, wrap the javabean in a jar and put it into the archive or archine_jini-tag in the formsweb.cfg.
 

 

 

 

Weiterlesen

Working with trees

21. März 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

Trees are a nice feature to present hierarchical data. And, since forms 5 they are available as standard components in forms. A lot of forum posts are related to trees and the way they are populated. I want to show some different techniques to populate a tree and a way to interact with "standard" data-blocks.

Sample

Lets build a form with a tree showing the employee-hierarchie and a "standard" datablock, showing all the employees which have the selected employee in the tree as manager.

Building the basic form

First, we build a datablock EMP based on the table employees with a tabular layout. Then create another datablock BL_TREE with property "Single Record" set to true and create a tree-item in it, lets name it IT_TREE. In the layout we place the tree at the left of the tabular layout, so that it looks like the explorer.

Populating the tree - using a fixed query

The easiest way to populate a tree is by defining a query with a specific result-set-structure and just put that query in the property "Data Query" of the tree.

The query has to be defined so that it has the following result-columns

column-name

meaning

NODE_STATE

defines, how the node is initially shown in the tree
-1 = collapsed
 0 = leaf-node
 1 = expanded

NODE_DEPTH

The tree-level the row it at. This column defines the hierarchy of the tree-data

NODE_LABEL

The text to be shown in the tree-node

NODE_ICON

The name of an icon-file which should be rendered for the treenode. The icon must be accessible to forms as any icon used, e.g. on a button

NODE_VALUE

The "value" to be stored for the tree-node

 

 

 

 

 

 

 

 

 

 

Our example-table EMPLOYEES already has a hierarchical structure, each employee has a manager assigned via the MANAGER_ID, where the referenced manager is an employee him (or her)self.
So, our query look like

SELECT -1                              NODE_STATE,
       LEVEL                           NODE_DEPTH,
       LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,
       NULL                            NODE_ICON,
       EMPLOYEE_ID                     NODE_VALUE
  FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
START WITH MANAGER_ID IS NULL
ORDER SIBLINGS BY LAST_NAME

One last thing is to do, we have to populate the tree on startup of the form. So we create a WHEN-NEW-FORM-INSTANCE-trigger with the following code

Ftree.POPULATE_TREE('BL_TREE.IT_TREE');

Populating the tree - using a query which may change at runtime

A quite similar approach is to use a record-group as base-data for the tree. The structure of the data has to be the same. But, with a recordgroup you can change the query at runtime, e.g. when some userinput influences the data to be shown in the tree.

For this approach, we create a recordgroup named RG_TREE with the same query as in the last approach and assign it to the tree using the property "Record Group"

The population could also be done in the WHEN-nEW-FORM-INSTANCE-trigger, but because of the idea of re-populating it under certain condition, we put the logic in a procedure and just call that from the trigger. Here's the procedure

PROCEDURE PR_TREE IS
  rgTree  RECORDGROUP;
  vcQuery VARCHAR2(2000);
  nStatus NUMBER;
  itTree  ITEM := FIND_ITEM ('BLOCK.TREE');
BEGIN
  rgTree := FIND_GROUP ('RG_TREE');
  -- Clear Tree
  Ftree.DELETE_TREE_NODE (itTree, Ftree.ROOT_NODE);
  -- Build up a new Query, if desired
  vcQuery := 'SELECT -1                              NODE_STATE,' ||
             '       LEVEL                           NODE_DEPTH,' ||
             '       LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,' ||
             '       NULL                            NODE_ICON,' ||
             '       EMPLOYEE_ID                     NODE_VALUE' ||
             '  FROM EMPLOYEES' ||
             ' CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID' ||
             ' START WITH MANAGER_ID IS NULL' ||
             ' ORDER SIBLINGS BY LAST_NAME';

  -- Repopulate Record-Group
  nStatus := POPULATE_GROUP_WITH_QUERY (rgTree, vcQuery);
  -- Repopulate Tree
  Ftree.POPULATE_TREE(itTree);
END;

Populating the tree - using PL/SQL and FTree

The third way to populate a tree is to do everything "by hand". All functionality to interact with a tree are included in the standard-package Ftree. So, there is also a function to add a new node to a tree.

The logic is encapsulated in the following package. There's a procedure PR_FILL_TREE to add the top-level-nodes to the tree (The managers which do not have managers themselves), and, for every manager, the procedure PR_QUERY_DETAILS to read the employees of this manager. And, because each of these can also be a manager the procedure recursivela calls itself. Here's the code

PACKAGE PK_TREE IS
 
  PROCEDURE PR_FILL_TREE;
 
END;

PACKAGE BODY PK_TREE IS
 
  itTree ITEM:=FIND_ITEM('BL_TREE.IT_TREE');
 
  FUNCTION FK_ADD_NODE(i_ndMaster IN FTree.NODE,
                       i_vcValue  IN VARCHAR2,
                       i_vcLabel  IN VARCHAR2,
                       i_vcIcon   IN VARCHAR2 DEFAULT NULL,
                       i_nState   IN NUMBER DEFAULT Ftree.EXPANDED_NODE
                      )
  RETURN Ftree.NODE IS
  BEGIN
      RETURN Ftree.Add_Tree_Node(itTree,
                                 i_ndMaster,
                                 Ftree.PARENT_OFFSET,
                                 Ftree.LAST_CHILD,
                                 i_nState,
                                 i_vcLabel,
                                 i_vcIcon,
                                 i_vcValue);
  END;
 
  PROCEDURE PR_QUERY_DETAILS(i_ndMaster   IN Ftree.NODE,
                             i_nManagerId IN NUMBER) IS
    CURSOR crDetails IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID=i_nManagerId
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
      FOR rec IN crDetails LOOP
          nd:=FK_ADD_NODE(i_ndMaster, rec.VALUE , rec.LABEL);
          PR_QUERY_DETAILS(nd, rec.VALUE);
      END LOOP;
  END;
 
 
  PROCEDURE PR_FILL_TREE IS
    CURSOR crTop IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID IS NULL
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
      Ftree.DELETE_TREE_NODE(itTree, FTree.ROOT_NODE);
      FOR rec IN crTop LOOP
          nd:=FK_ADD_NODE(Ftree.ROOT_NODE, rec.VALUE , rec.LABEL);
          PR_QUERY_DETAILS(nd, rec.VALUE);
      END LOOP;
  END;
END;

Now, in the WHEN-NEW-FORM-INSTANCE-trigger we just call

PK_TREE.PR_FILL_TREE;

Large trees - using partial tree population

Whats the advantage of doing all this "handwork" if it can be done in a single query?
Sometimes it's not possible to put all the logic into a single query, so you have todo it manually. Another possible situation is that you have a large tree with lots of nodes and the population would last very long if all the data would be read at startup. In this case, a partial population can increase performance. The idea behind this is, just read the top-level at startup and collapse all these node, and, at the moment the user expands a node, query the next level of data into that node.

We already have a procedure to populate the top-level and also one for populating the "children" under a master-node. All we need to do is have some event when to query required data and a marker, if the details already have been read. We do this by simply putting a minus in front of the value for "unpopulated" children and remove that minus at the moment the details are queried. The event to populate the details is the moment when the user expands a node, so we use the appropiate trigger, the WHEN-TREE-NODE-EXPANDED-trigger.

So, lets modify the code a litte bit.

PACKAGE PK_TREE IS
 
  PROCEDURE PR_FILL_TREE;
 
  PROCEDURE PR_WTNE(i_ndNode IN Ftree.NODE);
 
END;

PACKAGE BODY PK_TREE IS
 
  itTree ITEM:=FIND_ITEM('BL_TREE.IT_TREE');
 
  FUNCTION FK_ADD_NODE(i_ndMaster IN FTree.NODE,
                       i_vcValue  IN VARCHAR2,
                       i_vcLabel  IN VARCHAR2,
                       i_vcIcon   IN VARCHAR2 DEFAULT NULL,
                       i_nState   IN NUMBER DEFAULT Ftree.EXPANDED_NODE
                      )
  RETURN Ftree.NODE IS
  BEGIN
    RETURN Ftree.Add_Tree_Node(itTree,
                               i_ndMaster,
                               Ftree.PARENT_OFFSET,
                               Ftree.LAST_CHILD,
                               i_nState,
                               i_vcLabel,
                               i_vcIcon,
                               i_vcValue);
  END;
 
  PROCEDURE PR_QUERY_DETAILS(i_ndMaster   IN Ftree.NODE,
                             i_nManagerId IN NUMBER) IS
    CURSOR crDetails IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID=i_nManagerId
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
    FOR rec IN crDetails LOOP
      -- add the node with a negative id
      nd:=FK_ADD_NODE(i_ndMaster, -rec.VALUE , rec.LABEL);
      -- don't add details here
    END LOOP;
  END;
 
  PROCEDURE PR_FILL_TREE IS
    CURSOR crTop IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID IS NULL
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
    Ftree.DELETE_TREE_NODE(itTree, FTree.ROOT_NODE);
    FOR rec IN crTop LOOP
      -- add the node with a negative id
      nd:=FK_ADD_NODE(Ftree.ROOT_NODE, -rec.VALUE , rec.LABEL, NULL, FTree.COLLAPSED_NODE);
      -- don't add details here
    END LOOP;
  END;
 
  PROCEDURE PR_WTNE(i_ndNode IN Ftree.NODE) IS
    nValue   NUMBER;
    ndDetail FTree.NODE;
  BEGIN
    -- Get the value of the node to be expanded
    nValue:=FTree.GET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE);
    IF nValue<0 THEN
      -- value is negative, then we have to read the details
      PR_QUERY_DETAILS(i_ndNode, ABS(nValue));
      -- Now set the value of the node to positive, so that at next expansion we won't re-read the details
      FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE, ABS(nValue));
      ndDetail:=FTree.FIND_TREE_NODE(itTree,'',FTREE.FIND_NEXT, FTREE.NODE_LABEL,i_ndNode,i_ndNode); 
      -- if there we're no details, set node as lead,
      IF Ftree.ID_NULL(ndDetail) THEN
        FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_STATE, FTree.EXPANDED_NODE);   
      ELSE
        -- otherwise set node to expanded
        FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_STATE, FTree.EXPANDED_NODE);   
      END IF;
    END IF;
  END;
 
END;

At the tree-item, we have to create an additional WHEN-TREE-NODE-EXPANDED-trigger which calls our new procedure

PK_TREE.PR_WTNE(:SYSTEM.TRIGGER_NODE);

Important:
To have this working, you must set the property "Allow Empty Branches" at the treeitem to "Yes".

Synchronizing the data block

The last requirement is that whenever the user selects an entry in the tree, all employees who have the selected employee in the tree as manager should be queried in the block at the right.

We enhance the PK_TREE for this with a procedure PR_WTNS and create a WHEN-TREE-NODE-SELECTED-trigger at the tree-item which calls that new procedure for this:
 

  PROCEDURE PR_WTNS(i_ndNode IN Ftree.NODE) IS
    nValue NUMBER; 
  BEGIN
    IF :SYSTEM.TRIGGER_NODE_SELECTED='TRUE' THEN
        -- Get the Employee-id, remeber that we made it negative
        nValue:=ABS(FTree.GET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE));
        -- Restrict block
        SET_BLOCK_PROPERTY('EMPLOYEES', ONETIME_WHERE, 'MANAGER_ID=' || TO_CHAR(nValue));
        GO_BLOCK('EMPLOYEES');
        -- execute block
        EXECUTE_QUERY;
    END IF;
  END;



 

Weiterlesen

Populating listitems dynamically

12. März 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

Listitems in forms are quite useful if one has to offer a smaller choice of values to the enduser. Often the values to be displayed have to be taken from another table, so the listitems values have to be read at runtime.

Also, listitems are often used for so called "domains". A domain in general is a list of discrete values, each of which has a meaning in form of a text. To avoid hardcoding the values, in many projects there is a domaintable which stores the allowed values along with the textual meaning.

So, lets have a look at how a listitem can be "populated" using Forms-builtin's.

Example

As example, lets assume we want to have a listitem in a form which is based on the column DEPARTMENT_ID from the HR-table EMPLOYEES, and in the form the item should be displayed as a listitem where the user can choose the department-name from.

Using a query

One way to populate a listitem is to use a record-group in conjunction with a query. The query to populate a list must have to columns of datatype VARCHAR2, where the first one is the Label to be displayed to the user, and the second one is the value stored as itemvalue. Here's the example to populate the department-item, in my example used in the PRE-FORM-trigger:

DECLARE
  rg RECORDGROUP;
  nDummy NUMBER;
BEGIN
  rg:=CREATE_GROUP_FROM_QUERY('RG', 'SELECT DEPARTMENT_NAME LABEL,
        TO_CHAR(DEPARTMENT_ID) VALUE FROM DEPARTMENTS ORDER BY 2');
  nDummy:=POPULATE_GROUP(rg);
  POPULATE_LIST('EMPLOYEES.DEPARTMENT_ID', rg);
END;

Doing it "by hand"

The other way to populate a listitem is to use the built-in's to handle a listitem in detail. There are built-in's to add or delete a row or to clear the entire list-item.
To achieve the same result as in the former example using these built-in's, the following code can be used

DECLARE
  CURSOR crDept IS
    SELECT DEPARTMENT_NAME LABEL,
           TO_CHAR(DEPARTMENT_ID) VALUE
      FROM DEPARTMENTS
    ORDER BY 2;
  it ITEM:=FIND_ITEM('EMPLOYEES.DEPARTMENT_ID');
BEGIN
  CLEAR_LIST(it);
  FOR rec IN crDept LOOP
    ADD_LIST_ELEMENT(it, GET_LIST_ELEMENT_COUNT(it)+1, rec.LABEL, rec.VALUE);
  END LOOP;
END;

Of course, you could also use dynamic sql instead of the "fixed" cursor.

Dealing with default-values

When filling a listitem dynamically you won't be able to define a "hardcoded" deafult-value, you will receive a FRM-30362.

To set a default-value in this case, you can either use an "indirect" initial value with using a global (something like :GLOBAL.G_INITIAL_DEPTNO) or parameter (something like :PARAMETER.P_INITIAL_DEPTNO). In both cases you have to make sure that the initial value in in the list. The other way is the use of a WHEN-CREATE-RECORD-trigger ad there setting the value of the listitem to the desired value, e.g.

:EMPLOYEES.DEPARTMENT_ID:=20;

Encapsulating the logic

Using the first method its quite easy to encapsulate the logic in a generic procedure:

PROCEDURE PR_POPULATE_LIST(i_vcItem IN VARCHAR2, i_vcQuery IN VARCHAR2) IS
  itList  ITEM:=FIND_ITEM(i_vcItem);
  rgQuery RECORDGROUP:=FIND_GROUP('RG_QUERY');
  nDummy  NUMBER;
BEGIN
  -- Check item-existance
  IF ID_NULL(itList) THEN
    -- Error-message "Item does not exist"
    message('Item does not exist', ACKNOWLEDGE);
    RAISE FORM_TRIGGER_FAILURE;
  ELSE
    -- delete possibly existing record-group
    IF NOT ID_NULL(rgQuery) THEN
      DELETE_GROUP(rgQuery);
    END IF;
    -- create the group from the query
    rgQuery:=CREATE_GROUP_FROM_QUERY('RG_QUERY', 'SELECT LABEL, TO_CHAR(VALUE) VALUE FROM (' || i_vcQuery ||')');
    IF NOT FORM_SUCCESS THEN
      RAISE FORM_TRIGGER_FAILURE;
    ELSE
      -- populate the group
      nDummy:=POPULATE_GROUP(rgQuery);
      IF nDummy!=0 THEN
        -- Error-message "Error during group-population"
        message('Error during group-population' || TO_CHAR(nDummy), ACKNOWLEDGE);
        RAISE FORM_TRIGGER_FAILURE;
      ELSE
        -- fianlly, populate the list-item
        POPULATE_LIST(itList, rgQuery);
      END IF;
    END IF;
  END IF;   
END;

The error-handling would have to be adjusted according to the message-handling used in the application.

So finally, the code to fill a list-item is simply a

PR_POPULATE_LIST('EMPLOYEES.DEPARTMENT_ID', 'SELECT DEPARTMENT_NAME LABEL, DEPARTMENT_ID VALUE FROM DEPARTMENTS ORDER BY 2');
Weiterlesen

Do it silently

27. Februar 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

Another often asked question from the forum

How can i supress that message FRM-40401..... when doing some programmatic COMMIT_FORM ? And its not only the COMMIT_FORM, it could also be EXECUTE_QUERY with the message FMR-40350...

The logic behind

Each message or error forms raises has a message-level assigned (something like a "ranking"). And there is a forms-setting what is the "actual" level the forms-session deals with. This is stored in :SYSTEM.MESSAGE_LEVEL. Now, for every message or error to be shown, forms checks the messages message-level against the "actual" level. The message or error is raised only if the messages message-level is equal or bigger as the system-setting. The actual raising of messages and errors occurs by firing the ON-MESSAGE-triger for messages and ON-ERROR for errors.

An example

  • The user clicks on the save-symbol in the smartbar, there are no changes to save.

  • Forms now would show the "FRM-40401 No changes to save"-message, which has a message-level of 0.

  • Forms checks this message-level (0) against the SYSTEM.MESSAGE_LEVEL (which is by default also 0). Because 0>=0, forms fires the ON-MESSAGE-trigger (if existing) with message_type='FRM' and message_code=40401, or it simple displays the message in the message-bar.

How to influence the behaviour - Permanent solution

If the requirement is to supress a message completely, you have to "overwrite" the message or error-handling by implementing the ON-ERROR or ON-MESSAGE-trigger. To stay with the example, lets say we want to supress the FRM-40401 permanently. So we implement our own ON-MESSAGE-trigger:

BEGIN
  IF     MESSAGE_TYPE='FRM'
     AND MESSAGE_CODE=40401 THEN
    NULL;
  ELSE
    MESSAGE(MESSAGE_TYPE || '-' || MESSAGE_CODE || ': ' || MESSAGE_TEXT);
  END IF;
END;

So we just filter the specific message. Running through the example:

  • The user clicks on the save-symbol in the smartbar, there are no changes to save.

  • Forms now would show the "FRM-40401 No changes to save"-message, which has a message-level of 0.

  • Forms checks this message-level (0) against the SYSTEM.MESSAGE_LEVEL (which is by default also 0). Because 0>=0, forms fires the ON-MESSAGE-trigger with message_type='FRM' and message_code=40401

  • Our ON-MESSAGE-trigger just "consumes" the message and nothing is shown to the user.

How to influence the behaviour - Only for specific situations

The other requirement is to supress some messages only in specific situations, e.g. when doing some automatic COMMIT_FORM. Thats the moment to adjust the SYSTEM.MESSAGE_LEVEL up to a level higher than the message(s) to be supressed, do the action and then readjust SYSTEM.MESSAGE_LEVEL again. This would look like

BEGIN
  :SYSTEM.MESSAGE_LEVEL:=5;
  COMMIT_FORM;
  :SYSTEM.MESSAGE_LEVEL:=0;
END;

Running through the example again:

  • The user clicks on the save-symbol in the smartbar, there are no changes to save.

  • Forms now would show the "FRM-40401 No changes to save"-message, which has a message-level of 0.

  • Forms checks this message-level (0) against the SYSTEM.MESSAGE_LEVEL (which is now 5). Because 0>=5 is not true, forms does nothing more.

Encapsulating the logic

As i'm a fan of encapsulation, i created a procedure for the committing logic and have it in my pll:

PROCEDURE PR_SILENT_COMMIT IS
   nMessageLevel NUMBER:=NAME_IN('SYSTEM.MESSAGE_LEVEL');
BEGIN
   COPY('5', 'SYSTEM.MESSAGE_LEVEL');
   COMMIT_FORM;
   COPY(TO_CHAR(nMessageLevel), 'SYSTEM.MESSAGE_LEVEL');
END;

The same also exists for silent execute-query ....

 

 

Weiterlesen

Calling a form and passing a context

20. Februar 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

One common task in a forms-application is call one form form another one and give some context-information to that new form when calling it. In some cases you also want to get a kind of "return-value" when the called form is finished to that you can react on it. There are several solutions for this task i will describe here:

Test-case

For the examples i take a simple application consisting of two forms-modules, both based on the EMPLOYEES-tables form the HR-demo-schema. The first form, i call it OVERVIEW is a tabular form where you can query data from the employees-table, but cannot change anything. Beneath the table there are two buttons, one called "Edit" and one called "New". Both will call the second form i call EDIT_EMP which can either be used to edit an existing record form the EMPLOYEES-table, or to create a new employee. What action should be possible in the second form is given as context from the OVERVIEW-form.
In detail,

  • when pressing the Edit-button i want to give the EMPLOYEE_ID from the selected record to the EDIT_EMP, along with the information to edit that record
  • when pressing the New-button i want to call EDIT_EMP with a blank record which enables the user to enter data for a new employee.

In the examples i will deal only with CALL_FORM (for first). There are some issues with OPEN_FORM which are similar (or equal) but also some which may differ.

Use parameters to pass a context

One approach of passing a context from one OVERVIEW to EDIT_EMP is to use Parameters.
To do that create two parameters in the Object-Navigator in EDIT_EMP:

  • P_EMPLOYEE_ID with datatype Number
  • P_MODE with datatype Char

To give a context from OVERVIEW to EDIT_EMP you now have to create a parameter-list, fill in the appropiate parameters and values and pass the parameters when calling the EDIT_EMP.

Here the example-code for the WHEN-BUTTON-PRESSED-trigger on the Edit-button in OVERVIEW:

DECLARE
  pl PARAMLIST:=GET_PARAMETER_LIST('PL_EDIT_EMP');
BEGIN
  -- Delete list if existing
  IF NOT ID_NULL(pl) THEN
    DESTROY_PARAMETER_LIST(pl);
  END IF;
  -- Create new
  pl:=CREATE_PARAMETER_LIST('PL_EDIT_EMP');
  -- Add parameter for Mode
  ADD_PARAMETER(pl, 'P_MODE', TEXT_PARAMETER, 'EDIT');
  -- Add parameter for EMPLOYEE_ID
  ADD_PARAMETER(pl, 'P_EMPLOYEE_ID', TEXT_PARAMETER, :EMP.EMPLOYEE_ID);
  -- Now call the second form, giving the parameter-list
  CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY,pl);
END;


And now in EDIT_EMP you have to "take over" the parameters and react on them. A good trigger to do so is the WHEN-NEW-FORM-INSTANCE-trigger. Heres the code that will react on the Edit-Mode and query the appropiate record.

IF :PARAMETER.P_MODE='EDIT' THEN
  -- go to the employee-block
  GO_BLOCK('EMP');
  -- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:PARAMETER.P_EMPLOYEE_ID)
  EXECUTE_QUERY;
  -- Restrict createing new records
  SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;


Important here is the WHERE-condition which is set at the EMP-block to restrict the query to the parameter-value.
Thats it for the parameter-approach.

Pro's:

  • You can see in the Module-definition of the called form which parameters it accepts.
  • The values of the parameters are specific to the called "instance" of the form, so if one form is called more than one time, each form can have it's own values

Con's:

  •  Parameters are one-way, means you can pass parameters when from the calling form to the called form, but you cannot return values back to the called form.

Using globals to pass a context

With this approach you make use of the Global-variable-concept in forms to pass a context. Globals are not defined at design-time, but implicitly at runtime when you first assign a value to them or use the Built-In DEFAULT_VALUE.

Lets do the same thing as before using Globals:

Heres the WHEN-BUTTON-PRESSED-trigger on the Edit-Button:

-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);


And also, you take over the context in the WHEN-NEW-FORM-INSTANCE-trigger of EDIT_EMP:

-- Make sure the Gloabls exist
DEFAULT_VALUE(NULL, 'GLOBAL.MODE');
DEFAULT_VALUE(NULL, 'GLOBAL.EMPLOYEE_ID');
IF :GLOBAL.MODE='EDIT' THEN
  -- go to the employee-block
  GO_BLOCK('EMP');
  -- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:GLOBAL.EMPLOYEE_ID)
  EXECUTE_QUERY;
  -- Restrict creating new records
  SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;



Again, the WHERE-condition at the block EMP is important to be set.
So far, the Global-approach is quite similar to the parameter-approach.

Pro's:

  • Globals can be seen by every module in a forms-session, so you can also use them to return a value from the called form back to the calling form

Con's:

  • Globals are not defined declarative so you have to look into the code
  • Globals can be seen by every module in a forms-session, so if one form is called more than one time, the second call will overwrite the globals from the first call.

Returning values from a called form

As returning a value from a clalled dialog is a common requirement, lets enhance the example and return a flag from the EDIT_EMP to indicate if the user has changed the edited employee, so that we can do a requery in that case to reflect the changes in the overview.

First, we implement the WHEN-BUTTON-PRESSED-trigger on the save-button in EDIT_EMP:

IF :SYSTEM.FORM_STATUS='CHANGED' THEN
  -- save changes, if needed
  DO_KEY('COMMIT_FORM');
  -- set "saved"-flag
  :GLOBAL.USER_HAS_SAVED:='TRUE';
  -- exit
  EXIT_FORM;
END IF;


And also the WHEN-BUTTOn-PRESSED-trigger on the cancel-button:

-- reset saved-flag
:GLOBAL.USER_HAS_SAVED:='FALSE';
-- exit without saving
EXIT_FORM(NO_VALIDATE);


Last we have to adjust the Edit-button code to react on the return-value

-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
-- Make sure the global exists
DEFAULT_VALUE('FALSE', 'GLOBAL.USER_HAS_SAVED');
-- Check retur value
IF :GLOBAL.USER_HAS_SAVED='TRUE' THEN
  -- Requery data
  GO_BLOCK('EMP');
  EXECUTE_QUERY;
END IF;

 

Encapsulating the logic

Both approaches have pros and cons and both have in common the same problems when a form is called from more than one other form:

  • The names of the parameters/globals must match on both sides.
  • The passed values must match on both sides
  • When enhancing the called form its sometimes hard to find all calling forms to adjust the call

So i took the following approach to "hide the complexity" and make it quite straight-forward to do the communication:

  1. Create a pll to contain all communication logic.
  2. For each form that can be called, create a package in it
  3. Inside each package, create the following functions/procedures
  • a procedure to call the form, having all the parameters needed to call as IN-parameters, or
  • a function to call the form, having all the parameters needed to call as IN-parameters and returning the result
  • a procedure for retrieving the callers parameters from the package to the called form.
  • a procedure to set the return-value from the called form into the package.
  • All communication with the called form in done exclusively using this package
  • In case there are different modes or variants to call a form, there may also be different "calling"-procedures/functions.

 

This approach has some (in my eyes big) advantages:

  • The way the communication between caller and calling-form is implemented is hidden and doesn't really matter to the caller
  • All usage of Global-names/parameternames is only done in this package, so the chance of having typos in it is quite small
  • Parameters are given with its "real" datatype and can even be checked before calling the form.
  • If the signature to call a form must change the signature of the package has to be changed, and with a recompile it's quite eays to find modules using the changed module, for they will no longer compile


So, lets change the example to match this approach:

First, heres the code for the package:

PACKAGE PK_EDIT_EMP IS

  -- Call the dialog to edit the employee with the given ID
  -- function return TRUE if user changed the Employee, else FALSE
  FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
  RETURN BOOLEAN;

  -- retrieve the callersparameters
  -- o_vcMode will be EDIT for editing NEW for creating a new Employee
  -- o_nEmployeeId will give the Id of the employee to be queried when o_vcMode is EDIT
  PROCEDURE PR_GET_PARAMETERS(o_vcMode      OUT VARCHAR2,
                              o_nEmployeeId OUT NUMBER);

  -- Sets the return-value                            
  PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN);  

END;

PACKAGE BODY PK_EDIT_EMP IS
 
  VCC_GLOBAL_MODE   VARCHAR2(30):='GLOBAL.EDIT_EMP_MODE';
  VCC_GLOBAL_EMPID  VARCHAR2(30):='GLOBAL.EDIT_EMP_EMPID';
  VCC_GLOBAL_RETURN VARCHAR2(30):='GLOBAL.EDIT_EMP_RETURN';
 
  FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
  RETURN BOOLEAN IS
  BEGIN
   -- Set the globals
    COPY('FALSE', VCC_GLOBAL_RETURN);
    COPY('EDIT', VCC_GLOBAL_MODE);
    COPY(TO_CHAR(i_nEmployeeId), VCC_GLOBAL_EMPID);
    -- call the form
    CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
    -- return result
    RETURN (NAME_IN(VCC_GLOBAL_RETURN)='TRUE');
  END;  

  -- ----------------------------------------------------------------
 
  PROCEDURE PR_GET_PARAMETERS(o_vcMode      OUT VARCHAR2,
                              o_nEmployeeId OUT NUMBER) IS
  BEGIN
    -- transfer globals to out-params
    o_vcMode:=NAME_IN(VCC_GLOBAL_MODE);
    o_nEmployeeId:=TO_NUMBER(NAME_IN(VCC_GLOBAL_EMPID));
  END;

  -- ----------------------------------------------------------------

  PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN) IS
  BEGIN
    -- map boolean to Char-result
    IF i_bSaved THEN
      COPY('TRUE', VCC_GLOBAL_RETURN);
    ELSE
      COPY('FALSE', VCC_GLOBAL_RETURN);
    END IF;
  END;

END;


Now the WHEN-BUTTON-PRESSED-trigger in OVERVIEW:

IF PK_EDIT_EMP.FK_EDIT_EMPLOYEE(:EMP.EMPLOYEE_ID) THEN
  -- Requery data
  GO_BLOCK('EMP');
  EXECUTE_QUERY;
END IF;


The WHEN-NEW-FORM-INSTANCE-trigger in EDIT_EMP:

DECLARE
  vcMode      VARCHAR2(4);
  nEmployeeId NUMBER;
BEGIN
  PK_EDIT_EMP.PR_GET_PARAMETERS(vcMode, nEmployeeId);
  -- to avoid using the globals directly in the form, created a item 
  -- in y control-block
  -- and restrict the query  on that
  :BL_CONTROL.IT_EMPLOYEE_ID:=nEmployeeId;
  -- go to the employee-block
  GO_BLOCK('EMP');
  -- execute the query (the block has a 
  -- WHERE-condition using EMPLOYEE_ID=:BL_CONTROL.IT_EMPLOYEE_ID
  EXECUTE_QUERY;
  -- Restrict createing new records
  SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END;



Note that for avoiding to "break" the encapsulation i created a control-block with an item to store the employee_id in.

And last, the two button "Save"

IF :SYSTEM.FORM_STATUS='CHANGED' THEN
  DO_KEY('COMMIT_FORM');
  PK_EDIT_EMP.PR_SET_RETURN(TRUE);
  EXIT_FORM;
END IF;


and "Cancel"

PK_EDIT_EMP.PR_SET_RETURN(FALSE);
EXIT_FORM(NO_VALIDATE);
Weiterlesen