FRM-40831: Truncation Occurred: value too long for field XXXXX

FRM-40831: Truncation Occurred: value too long for field XXXXX

Cause:

1. Form field length and database column length may not be same.
2. Minimum query length Property.
3. Data Length Semantics property and Unicode.

Solution:

1. Check the Form field length and database column length.

2. Your base table field query length (in the Results block) must be long enough to contain the query criteria. If it is not, you get an error that the value is too long for your field. All fields should have a minimum query length of 255.

3. If you have radio groups, list items, or check boxes based on database fields in your Results block, you should only copy those values from the Find window if they are not NULL.

4. If you ever need to adjust the default WHERE clause, remember to set it back when you do a non-query-find query.

How to change the order/arrangement of multi records block?

Normally, multi records are displaying in vertical navigation style. (Like from Left to Right)

How to change the arrangement into horizontal (like Top to bottom)?

Set the BLOCK level properties “RECORD ORIENTATION -> HORIZONTAL”

(example)

How to invoke another form in oracle apps form?

In Oracle Applications, we can use FND_FUNCTION.EXECUTE instead of CALL_FORM / OPEN_FORM

Syntax

FND_FUNCTION.EXECUTE
                    (function_name  IN varchar2,
                     open_flag      IN varchar2 default ’Y’,
                     session_flag   IN varchar2 default ’SESSION’,
                     other_params   IN varchar2 default NULL,
                     activate       IN varchar2 default ’ACTIVATE’,
                     browser_target IN varchar2 default NULL);



Description

Execute the specified form function; only executes functions that have a form attached.

Arguments

 
Example
 

How do use the same LOV for 2 form fields?

We can achieve by passing the return values in global values / parameters and using the values in the code.

in another way ... we can create the diff LOV with the same Record groups

How to create the Hierarchical Tree in oracle forms?

There are 2 ways to populate a hierarchical tree (1. Record Group, 2. Query Text)

Step1 - Create Record group
Step2 - Create hierarchical tree item (Control Block)
                         - WHEN-TREE-NODE-SELECTED
                         - WHEN-TREE-NODE-ACTIVATED
Step3 – Create data block

Step1 - Create Record group

The record Group query should be in the specified structure., which requires 5 Columns.

SELECT STATUS, LEVEL, LABEL, ICON, VALUE FROM TABLE;

STATUS - Indicates the initiate status of the Node (Normally Value is 1).
LEVEL - This is a specific pseudo-column Derived from “CONNECT BY”.
LABEL -This is the visible label of the Node.
ICON - That contains the icon name of the Node (can be NULL).
VALUE -That contains the value of the Node.



Step2 - Create hierarchical tree item (Control Block)



Add the trigger code @ WHEN-NEW-FORM-INSTANCE and WHEN-TREE-NODE-ACTIVATED

DECLARE

   vi_tree      item;

   vn_tree_rg   NUMBER;

BEGIN

   vi_tree      := FIND_ITEM ('TREE_BLK.EMP_TREE');

   vn_tree_rg   := POPULATE_GROUP ('EMP_TREE_RG');

   ftree.SET_TREE_PROPERTY ( vi_tree, ftree.record_group, 'EMP_TREE_RG');

EXCEPTION

   WHEN OTHERS

   THEN

      fnd_message.debug (SQLERRM);

END;

Add the trigger code @ WHEN-TREE-NODE-SELECTED

DECLARE

   vi_htree        item;

   vc_node_value   VARCHAR2 (100);

BEGIN

   vi_htree        := FIND_ITEM ('TREE_BLK.EMP_TREE');

   vc_node_value   := ftree.GET_TREE_NODE_PROPERTY ( vi_htree, :SYSTEM.trigger_node, ftree.node_value);

   SET_BLOCK_PROPERTY ( 'emp', default_where, 'EMPNO = ' || vc_node_value);

   GO_BLOCK ('EMP');

   EXECUTE_QUERY;

EXCEPTION

   WHEN OTHERS

   THEN

      fnd_message.debug (SQLERRM);

END;

Step3 – Create data block





@ Runtime

How to place the stacked canvas in TAB page canvas?

We can’t place the stacked canvas in TAB page canvas. But we can show the stacked canvas in all/required tab pages.

For an example, (Requirement like this ...) we need to show the ITEM DETAILS (Line sequence num, Item number and description) in all TAB pages (Item details, OSP WIP Job, PO Details, Container and notes).

As per requirement you did content and TAB page canvas design; once you are going to create the stacked canvas the developer suite will throw an error …

To avoid this error, go to VIEW >> STACKED VIEWS >> just un-select your TAB page canvas (CTRL +select/unselect). Now you can view the content canvas only.

Do the design/place the stacked canvas and place the items whatever you want.

Once you done the stacked canvas design; goto VIEW >> STACKED VIEWS >> choose the all canvas views and verify the position of items.

How to register the forms in oracle applications?

Steps

1. Download the required fmb(s) and pll(s) from the server.
2. Develop the custom form in forms developer.
3. Move the form into server.
4. Compile the form file.
5. Form registration.
6. Form function registration.
7. Attaching the form function into menu.

Steps in detail

1. Download the TEMPLATE.fmb and APPSTAND.fmb from $AU_TOP/forms/US.
    (You can find all the oracle apps seeded forms in this directory)

2. Download the required pll files from $AU_TOP/resource.
    (You can find all the oracle apps seeded library files in this directory)

3. Copy and save the TEMPLATE.fmb into meaningful custom name.

4. Delete the following objects
             i. Window name (BLOCKNAME)
            ii. Canvas name (BLOCKNAME)
           iii. Data blocks (BLOCKNAME and DETAILBLOCK)

5. Create the windows, canvases and data blocks as per the requirements. (Don’t forget to sub class the properties for the same.) If you are creating the multi record blocks then include a one non-data base item in the same block and sub class the property called “CURRENT_RECORD_INDICATOR”.

6. You must modify the following triggers

          i. PRE-FORM

         ii. WHEN-NEW-FORM-INSTANCE.

7. You do not modify these form-level triggers, but you can write block or item level triggers that override the form-level trigger.
           i. KEY-LISTVAL
          ii. ON-ERROR.

8. You do not modify the following triggers in any way.
               i. STANDARD_ATTACHMENTS
              ii. ZOOM
             iii. FOLDER_ACTION
             iv. KEY-HELP
              v. KEY-EXIT
             vi. KEY-EDIT
            vii. KEY-COMMIT
           viii. WHEN-WINDOW-CLOSED
            ix. CLOSE_WINDOW

9. You must modify the APP_CUSTOM package body as mentioned like a below figure.

10. Move the fmb file into $XXCUSTOM_TOP/forms/US.

11. Compile the fmb file in PuTTY (connect to PuTTY and change the current directory into $XXCUSTOM_TOP/forms/US, execute the below mentioned command with apps password.)

Release 12
frmcmp_batch module= userid=apps/ output_file= module_type=form batch=yes compile_all=special

11i Release
f60gen module= userid=APPS/ output_file= module_type=form batch=yes compile_all=special

12. Register the form in oracle apps.
Navigation -> Application Developer > Application > Form

13. Register the form functions
Navigation -> Application Developer > Application > Function

Description TAB (Function name and user function name)
Properties TAB (Type -> Form)
Form TAB (chose the custom form name)

14. Attach this form function into menu.
Navigation -> Application Developer > Application > Menu
Query the appropriate menu and add the custom function into the same.

15. Verify the form in oracle apps.