Local Variables Syntax

How to use local variables in form personalization:

Steps:

1. Initialize: 

New Personazaltion: 

Actions> Properties > 

Object Type: Local Variables

Target Object: REQUEST_DATE

Property Name: VALUE

Value: =NULL

2. Set Local Variable:

Actions> Properties > 

Object Type: Local Variables

Target Object: REQUEST_DATE

Property Name: VALUE

Value: =TO_CHAR(:LINE.REQUEST_DATE, 'DD-MON-YYYY')

3. Use Local variable in Condition:

${var.REQUEST_DATE.value}

ORA-01861: literal does not match format string

While developing XML Publisher Reports, when parameters are selected as FND_STANDARD_DATE, when used this in Data Template, it is converted as String, so we have to convert it back to date format.

you may use fnd_date.canonical_to_date(:xx_param) 

Form Personalizations Details SQL

References:

http://amaralam.blogspot.com/2016_04_01_archive.html

1)  SELECT FORM_ID, FORM_NAME, USER_FORM_NAME, DESCRIPTION

     FROM FND_FORM_VL
     WHERE FORM_NAME IN (SELECT FORM_NAME FROM FND_FORM_CUSTOM_RULES      GROUP BY FORM_NAME)

2) Query to Get Form Personalization Details ( Oracle Applications )  from Database.

FND_FORM_CUSTOM_RULES - The Rules for the form customization's. A rule must have 1 more more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.
FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule
FND_FORM - stores information about your registered application forms. Each row includes names (the actual SQL*Forms form name, and the Easy Form form title) and a description of the form. Each row also includes a flag that indicates whether this form is included in the Audit Trail audit set. You need one row for each form in each application. Oracle Application


Select Distinct
    A.Id,
    A.Form_Name ,
    A.Enabled,
    C.User_Form_Name,
    D.Application_Name ,
    A.Description,
    Ca.Action_Type,
    Ca.Enabled,
    Ca.Object_Type,
    ca.message_type,
    ca.message_text
from
    FND_FORM_CUSTOM_RULES a,
    FND_FORM b,
    FND_FORM_TL c,
    Fnd_Application_Tl D,
    Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
    And B.Form_Id = C.Form_Id
    And B.Application_Id = D.Application_Id
    And D.Application_Id = 230 --For Order Management
    And C.User_Form_Name Like 'Inventory%'  --All the Forms that Start with Sales
    And A.Enabled ='Y'
    and a.id = ca.rule_id


SELECT
    ffv.form_id          "Form ID",
    ffv.form_name        "Form Name",
    ffv.user_form_name   "User Form Name",
    ffv.description      "Form Description",
    ffcr.sequence        "Sequence",
    ffcr.description     "Personalization Rule Name"
FROM fnd_form_vl             ffv,
       fnd_form_custom_rules   ffcr
WHERE ffv.form_name = ffcr.form_name
ORDER BY ffv.form_name, ffcr.sequence;



SELECT 
    ffcr.SEQUENCE "Seq", ffcr.description "Description",
    DECODE (ffcr.rule_type,
           'F', 'Form',
            'A', 'Function',
            'Other'
           ) "Level",
    ffcr.enabled "Enabled",
    ffcr.trigger_event "Trigger Event",
    ffcr.trigger_object "Trigger Object",
    ffcr.condition "Condition",
    DECODE (ffcr.fire_in_enter_query,
            'Y', 'Both',
            'N', 'Not in Enter-Query Mode',
            'O', 'Only in Enter-Query Mode',
            'Other'
           ) "Processing Mode"
FROM apps.fnd_form_custom_rules ffcr
WHERE ffcr.function_name = 'PO_POXPOEPO'
    AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;

Using FND Messages in Form Personalization

Step-1 ) Create database function:

CREATE OR REPLACE FUNCTION GET_NEW_MESSAGE_TEST(parameter1 IN VARCHAR2, parameter2 IN VARCHAR2)

RETURN VARCHAR2

is

L_CHR_MSG VARCHAR2(1000);

BEGIN

FND_MESSAGE.SET_NAME(<Application name>,<message name>);

FND_MESSAGE.SET_TOKEN(<token 1>,parameter1);

FND_MESSAGE.SET_TOKEN((<token 2>,parameter2);

L_CHR_MSG := FND_MESSAGE.GET;

RETURN L_CHR_MSG;

END;

Step 2)

Got to forms personalization -- Actions

Type - Message

Type = error

Message text :

=(Select Get_new_msg_test (:<block.fieldname1>,:<block.fld2>) from dual)

Apply. close the form and re visit again..it should be done...

Execute a Procedure Using form personalization

Execute a Procedure Using form personalization

1. Personalize the form

2. Create an action of type "BuiltIn"

3. BuiltIn Type for Action should be "Execute a Procedure"

4. Argument should be as below

='declare

   v_field_value VARCHAR2(200) ;

   begin

       plsql_package.procedurenameHere ;

  end'

or alternately

='declare

   v_field_value VARCHAR2(200) ;

   begin

       apassi_prc ('''||${item.PO_CONTROL_RULES.OBJECT_CODE_DISPLAYED_VALUE.value}||''');

   end'

Note the syntax, after =, entire declare begin end is within single quote.

Also, there is no semi colon after "end"

You can pass field values as

'''||${item.BLOCKNAME.FIELDNAME.value}||'''

Required Profile Options for Form Personalization to work

Following profile options should be set to the user making the Form Personalization:

o ‘FND: Diagnostics’ set to ‘Yes’

o ‘Hide Diagnostics menu entry’ set to ‘No’

o ‘Utilities:Diagnostics’ set to ‘Yes’

Order Management : Calling Release Hold API

SET SERVEROUTPUT ON;

DECLARE

   v_msg_data        VARCHAR2 (4000);

   p_header_id       OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE := 40750464;

   p_line_id         OE_ORDER_LINES_ALL.LINE_ID%TYPE := 48876396;

   l_cnt             NUMBER (10) := 0;

   p_user_id         FND_USER.USER_ID%TYPE := 66982;        --127903; --66982;

   p_resp_id         FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE := 52434; --52434; --50609;  --52434;

   p_resp_appl_id    FND_RESPONSIBILITY.APPLICATION_ID%TYPE := 660;

   p_org_id          oe_order_lines_all.ORG_ID%TYPE := 149;

   p_hold_id         OE_HOLD_DEFINITIONS.hold_id%TYPE := 3026; --:= getHoldId;

   p_error_msg       VARCHAR2 (1000);

   p_status          VARCHAR2 (1);

   l_act_status      BOOLEAN := FALSE;

   l_order_tbl       OE_HOLDS_PVT.order_tbl_type;

   l_return_status   VARCHAR2 (100);

   l_msg_data        VARCHAR2 (4000);

   l_msg_count       NUMBER;

   X_DEBUG_FILE      VARCHAR2 (1000);

   l_msg_index_out   NUMBER (10);

BEGIN

   DBMS_OUTPUT.enable (10000);

   /* Setting the org context for the particular session */

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-1' || MO_GLOBAL.get_current_org_id);

   fnd_global.apps_initialize (user_id        => p_user_id,

                               resp_id        => p_resp_id,

                               resp_appl_id   => p_resp_appl_id);

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-2' || MO_GLOBAL.get_current_org_id);

   mo_global.init ('ONT');

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-3' || MO_GLOBAL.get_current_org_id);

   mo_global.set_policy_context ('S', p_org_id);

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-4' || MO_GLOBAL.get_current_org_id);

   --oe_debug_pub.setdebuglevel (5);

   oe_msg_pub.initialize;

   DBMS_OUTPUT.PUT_LINE (

      'MO_GLOBAL.get_current_org_id:-5' || MO_GLOBAL.get_current_org_id);

   l_order_tbl (1).header_id := p_header_id;

   l_order_tbl (1).line_id := p_line_id;

   OE_Holds_PUB.Release_Holds (

      p_api_version           => 1.0,

      p_order_tbl             => l_order_tbl,

      p_hold_id               => p_hold_id,

      p_release_reason_code   => 'VALID_REASON_CODE',

      p_release_comment       => 'Released by anonymous block',

      x_return_status         => l_return_status,

      x_msg_count             => l_msg_count,

      x_msg_data              => l_msg_data);

   -- Check the return status

   IF l_return_status = FND_API.G_RET_STS_SUCCESS

   THEN

      p_status := 'S';

      COMMIT;

      DBMS_OUTPUT.PUT_LINE ('successfully completed');

   ELSE

      DBMS_OUTPUT.PUT_LINE ('failure:' || l_msg_count);

      DBMS_OUTPUT.PUT_LINE ('failure:' || l_return_status);

      IF (fnd_msg_pub.count_msg > 0)

      THEN

         FOR i IN 1 .. fnd_msg_pub.count_msg

         LOOP

            fnd_msg_pub.get (p_msg_index       => i,

                             p_encoded         => 'F',

                             p_data            => L_msg_data,

                             p_msg_index_out   => L_msg_count);

            DBMS_OUTPUT.PUT_LINE ('API ERROR: ' || L_msg_data);

         END LOOP;

         DBMS_OUTPUT.put_line (                             --   x_jtf_note_id

                               L_msg_count || '--' || L_msg_data);

      END IF;

      ROLLBACK;

   END IF;

--DBMS_OUTPUT.PUT_LINE ('err1:' || l_msg_data);

EXCEPTION

   WHEN OTHERS

   THEN

      --p_error_msg:=SUBSTR (SQLERRM, 1, 300);

      DBMS_OUTPUT.PUT_LINE ('err2:' || SQLERRM);

END;