Calling Procedure Forms Personalization

Calling procedure in forms personalization can be done using argument in below format.

='declare v_field_value VARCHAR2(500);begin scheme.procedure('''||${item.tolines_blk.header_id.value}||''',
'''||${item.tolines_blk.line_id.value}||''','''||fnd_global.user_id||''','''||fnd_global.org_id||''');
end'

oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility

OAF Error

oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility. (Could not lookup message because there is no database connection)

Resolution

Check the following properties are set correctly.

Go to Project Settings. Expand Common and then Oracle Applications.

1) Database Connection 

• Check if database setup is created.

2) Runtime conneciton 

• DBC file name. ( this is the connection used when your run it . The database connection is only for the desing time)
• Application short name such AK.
• Responsiblity key
• User name (Apps user name)
• Password (Apps user password).

3) Check if responsibility(from responsibility key used above) is assigned to your apps user   

     name that is used in runtime connection.

Wait for concurrent request using fnd_concurrent.wait_for_request

Most of the times while submitting concurrent request one has to wait for its completion to perform sequence of steps. This can be achieved through fnd_concurrent.wait_for_request in oracle apps. It returns the status of the previously submitted concurrent program upon interval set.

This is a conjunction to the SUBMIT_REQUEST. Follow ARTICLE on submitting concurrent program and use below call upon completed. There are some parameter that has to be declared.

      lb_complete      BOOLEAN;
      lc_phase           VARCHAR2 (100);
      lc_status           VARCHAR2 (100);
      lc_dev_phase   VARCHAR2 (100);
      lc_dev_status   VARCHAR2 (100);
      lc_message      VARCHAR2 (100);

ln_request_id is return variable for concurrent_request_id from above article. If ln_request_id > 0 (means request submitted successfully) then wait_for_request.

   Arguments (input)
     request_id    - Request ID to wait on
     interval         - time b/w checks. Number of seconds to sleep (default 60 seconds)
     max_wait      - Max amount of time to wait (in seconds) for request's completion
  Arguments (output)
                 User version of      phase and status
                 Developer version of phase and status
                 Completion text if any
     phase            - Request phase ( from meaning in fnd_lookups )
     status            - Request status( for display purposes          )
     dev_phase    - Request phase as a constant string so that it can be used for comparisons
     dev_status    - Request status as a constatnt string
     message       - Completion message if request has completed

      IF ln_request_id > 0
      THEN
         lb_complete :=
            fnd_concurrent.wait_for_request (request_id      => ln_request_id
                                                             ,interval            => 2
                                                             ,max_wait        => 60

                                                             -- out arguments
                                                             ,phase              => lc_phase
                                                             ,status              => lc_status
                                                             ,dev_phase      => lc_dev_phase
                                                             ,dev_status      => lc_dev_status
                                                             ,message         => lc_message
                                            );
         COMMIT;

         IF UPPER (lc_dev_phase) IN ('COMPLETE')
         THEN
            dbms_output.put_line('Concurrent request completed successfully');
         END IF;
      END IF;

Avaialble to Reserve and Availble to Transact quantities using inv_quantity_tree_pub.query_quantities API

There is much to mention about this API which makes life easy to get available to Reserve/Transact quantities, since we cannot perform a quantity transaction based on On hand quantity.

Use inv_quantity_tree_pub.query_quantities API to get the exact quantities.

Things to consider.

1. Set the value TRUE for lot /serial controlled only if your item is controlled by those attributes else set to FALSE.
2. Advice to call inv_quantity_tree_grp.clear_quantity_cache to delete all the tree quantities in the memory.
3. Most importantly if you are here because you are not getting right quantity by directly calling this function please follow the note after API.

FUNCTION available_to_reserve (
   p_inv_item_id       NUMBER
  ,p_subinv_code       VARCHAR2
  ,p_locator_id        NUMBER
  ,p_lot_number        VARCHAR2
  ,p_organization_id   NUMBER
)
   RETURN NUMBER
IS
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   l_item_id               NUMBER;
   l_organization_id       NUMBER;
   l_qty_on_hand           NUMBER;
   l_res_qty_on_hand       NUMBER;
   l_avail_to_tnsct        NUMBER;
   l_avail_to_reserve      NUMBER;
   l_qty_reserved          NUMBER;
   l_qty_suggested         NUMBER;
   l_lot_control_code      BOOLEAN;
   l_serial_control_code   BOOLEAN;
BEGIN
   /* Used to clear cache*/
   inv_quantity_tree_grp.clear_quantity_cache;
  
   -- Initialize apps by passing correct values
   fnd_global.apps_initialize (user_id,resp_id,resp_appl_id);
  
   -- Set the variable values
   l_item_id := p_inv_item_id;
   l_organization_id := p_organization_id;
   l_lot_control_code := TRUE;   --Only When Lot number is passed  TRUE else FALSE
   l_serial_control_code := FALSE;   --if item is serial controlled pass  TRUE else FALSE
   -- Call API
   inv_quantity_tree_pub.query_quantities
         (p_api_version_number       => 1.0
         ,p_init_msg_lst             => 'F'
         ,x_return_status            => x_return_status
         ,x_msg_count                => x_msg_count
         ,x_msg_data                 => x_msg_data
         ,p_organization_id          => l_organization_id
         ,p_inventory_item_id        => l_item_id
         ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode   -- or 3
         ,p_is_revision_control      => FALSE
         ,p_is_lot_control           => l_lot_control_code   -- is_lot_control,
         ,p_is_serial_control        => l_serial_control_code
         ,p_revision                 => NULL   -- p_revision,
         ,p_lot_number               => p_lot_number   -- p_lot_number,
         ,p_lot_expiration_date      => SYSDATE
         ,p_subinventory_code        => p_subinv_code   -- p_subinventory,
         ,p_locator_id               => p_locator_id
         ,p_cost_group_id            => NULL
         ,p_onhand_source            => 3   -- Can also pass NULL
         ,x_qoh                      => l_qty_on_hand   -- Quantity on-hand
         ,x_rqoh                     => l_res_qty_on_hand   --reservable quantity on-hand
         ,x_qr                       => l_qty_reserved
         ,x_qs                       => l_qty_suggested
         ,x_att                      => l_avail_to_tnsct   -- available to transact
         ,x_atr                      => l_avail_to_reserve   -- available to reserve
         );
   DBMS_OUTPUT.put_line ('On hand Quantity: ' || l_qty_on_hand);
   DBMS_OUTPUT.put_line ('Avaialble to Reserve : ' || l_avail_to_reserve);
   RETURN NVL (l_avail_to_reserve, 0);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
      RETURN 0;
END;

Note: For some reason i am not able to achieve right results even after passing the correct values and initializing correctly through SQL/Concurrent Prgoram. After hours of debug i found that this API cannot be run directly from SQL/Toad just by declare begin and end. It has to be created as function/procedure. Also function created cannot be called directly . Its advisable to create a main function which in turn calls the quantity API. This was i was able to get right quantity. 

Here is example :

function quantity_call (arguments)
RETURN NUMBER IS
l_avail_qty NUMBER;
BEGIN
---This condition is to clear the quantity with incorrect inputs by hardcoding
         ln_avaialble_qty :=
              avail_to_reserve (1234567890, 'AAAA', 12345000, 'AAA.3.0', organization_id);
---Then pass correct values         
ln_avaialble_qty := avail_to_reserve (arguments);

 ---This condition is to clear the quantity with  incorrect inputs and try again
     IF ln_avaialble_qty = 0
    THEN
      ---This condition is to clear the quantity with incorrect inputs by hardcoding
         ln_avaialble_qty :=
              avail_to_reserve (1234567890, 'AAAA', 12345000, 'AAA.3.0', organization_id);
     ---Then pass correct values  
            ln_avaialble_qty := avail_to_reserve (arguments);
         END IF;

l_avail_qty := availalbe_to_reserve (arguments);
RETURN  l_avail_qty;
END;

Use quantity_call (arguments) where ever needed.

How to enable About this page in oracle apps

Navigate to System Administrator --> Profile --> System

Profile: FND: Diagnostics
Set the site value to Yes




Logout and log back in.

XML Publisher conditional formatting

Conditional formatting in XML publisher can be handled by If and while statements

1. If Statement
• <?if:COLOR='RED'?>Y<?end if?>
2. If-then-Else
• <?if:COLOR='RED' then Y else N end if?>
• Nested If-then-Else
  <?if:COLOR='RED' then Y else if COLOR = 'BLUE' then B else N end if?>

Combining If with <?xdofx:expression?> helps in scripting SQL statements. Refer below.

3. Choose
• <?choose:?>
  <?when:expression?>
  <?otherwise?>
• Example
  <?choose:?>
        <?when:CLOLOR = ‘RED'?>
          'Y'
        <?end when?>
        <?when:COLOR = 'B'?>
          'B'
        <?otherwise?>
          'N'
        <?end otherwise?>
  <?end choose?>

BI Publisher has extended a set of SQL and XSL functions for use in RTF templates. The syntax for these extended functions is

<?xdofx:expression?>  --> for extended SQL functions or

<?xdoxslt:expression?> --> for extended XSL functions

•  <?xdofx:if COLOR ='Y' then substr('ACCEPTED',1,6) else REJECT end if?>

Refer --> Exteneded Functions










Set Org Context in Oracle Apps 11i and R12

R12

set_policy_context

   Sets the application context for the current org and the access
   mode to be used in server side code for validations as well as in
   the Multi-Org security policy function.
   

   Arguments
   p_access_mode    - specifies the operating unit access. 'S' for Single, 'M' for Multiple, 'A' for All.
   p_org_id              - org_id of the operating unit.

 exec mo_global.set_policy_context('S','&org_id');

           OR

           MO_GLOBAL.INIT('APPL_SHORT_CODE');

11i

1.  Set Org Context 11i
   
2. Begin
   fnd_client_info.set_org_context(&org_id);
   End;

Forms Trace / Diagnostics Oracle Apps

Tracing in R12

1. Set the user value for profile option 'ICX: Forms Launcher' to be 'http://hostname.domain:port/forms/frmservlet?record=collect+log=user1.log'. Use without quotes and you can replace "user1" with your own.
2. By default, this will enable tracing for errors only. As an optional step, you can enable different trace options (e.g. user actions, user-exit events, dbsql events, network events) by setting a trace group in file $ORA_CONFIG_HOME/10.1.2/forms/server/ftrace.cfg
3. Login to the Personal Home Page, navigate to the form and perform the steps that you wish to trace.
4. Locate the file in the directory set by variable FORMS_TRACE_DIR or "echo $FORMS_TRACE_DIR" to get existing value. Note: by default, this directory is named forms_.trc, where is the process identifier.

Tracing in 11.5.10

1. Make the user value for profile option 'ICX: Forms Launcher' the same as the site value.
   e.g. http://testserver.oracle.com:8005/dev60cgi/f60cgi to be the value for the user.
2. Append the user value of ICX: Forms Launcher with the Forms parameters for FRD. For example,update the value of ICX: Forms Launcher for the user to be: 'http://testserver.oracle.com:8005/dev60cgi/f60cgi?record=all&log=username_frd.log'
3. Login to the Personal Home Page, navigate to the form and perform the steps that you wish to trace for the user the profile above was set for.
   
4. Locate the file in the directory set by variable $FORMS60_TRACE_PATH. Note: the default directory for $FORMS60_TRACE_PATH is $ORACLE_HOME/forms60/log.