, , , ,

FND Messages in Oracle

Calling messages: Following standard procedure can be called to show predefined oracle applications message through forms or concurrent programs.

FND_MESSAGE.CLEAR – Clears the message stack of all the messages.

FND_MESSAGE.DEBUG – Immediately displays the string passed to it as input parameter.

FND_MESSAGE.ERASE – Clears the oracle Forms status line.

FND_MESSAGE.ERROR – Displays an error message in an Oracle Forms modal window or a concurrent program log file. (Example: “Invalid value entered.”). It takes its message from the stack, displays the message, and then clears all the messages from the message stack.

FND_MESSAGE.GET – Retrieves a translated and token–substituted message from the message stack and then clears that message from the message stack. This could be used for getting a translated message for a forms built–in or other function. Assumes you have already called FND_MESSAGE.SET_NAME and, if necessary,
FND_MESSAGE.SET_TOKEN. It returns up to 2000 bytes of message.

FND_MESSAGE.HINT – Displays a message in the Oracle Forms status line. It takes its message from the stack, displays the message, and then clears that message from the message stack. The user may still need to acknowledge the message if another message immediately comes onto the message line.

FND_MESSAGE.QUESTION – Displays a message and up to three buttons in an Oracle Forms modal window. (Example: “Please choose one of the following actions: ”) It takes its message from the stack, and clears that message. After the user selects a button, It returns the number of the button selected.
For each button, you must define or use an existing message in Message Dictionary (under the Oracle Application Object Library application) that contains the text of the button. This routine looks for your button name message in the Oracle Application Object Library messages, so when you define your message, you must associate it with Oracle Application Object Library (the “FND” application) instead of your application.

FND_MESSAGE.RETRIEVE – Retrieves a message from the database server, translates and substitutes tokens, and sets the message on the message stack.

FND_MESSAGE.SET_NAME – Retrieves your message from Message Dictionary and sets it on the message stack. You call it once for each message you use in your client–side PL/SQL procedure. You must call this procedure before you call FND_MESSAGE.SET_TOKEN.

FND_MESSAGE.SET_STRING – Takes an input string and sets it directly on the message stack. The string does not need to be defined in the Messages window. These strings may be hard coded into the form and are not translated like messages defined in Message Dictionary.

FND_MESSAGE.SET_TOKEN – Substitutes a message token with a value you specify. You call FND_MESSAGE.SET_TOKEN once for each token/value pair in a message. The optional translate parameter can be set to TRUE to indicate that the value should be translated before substitution. (The value should be translated if it is, itself, a Message Dictionary message name.)

FND_MESSAGE.SHOW – Displays an informational message in an Oracle Forms modal window or a concurrent program log file. (Example: “To complete this function, please enter the following… ”). It takes its message from the stack, displays the message, and then clears only that message from the message stack.

FND_MESSAGE.WARN – Displays a warning message in an Oracle Forms modal window and allows the user to either accept or cancel the current operation. (Example: “Do you wish to proceed with the current operation?”) FND_MESSAGE.WARN returns TRUE if the user accepts the message (that is, clicks OK), or FALSE if the user cancels. It takes its message from the stack, displays the message, and clears that message from the message stack.
Most frequently used procedures are FND_MESSAGE.SET_NAME and FND_MESSAGE.SET_TOKEN

Example
/*Here FND_MESSAGE.SET_NAME is used to retrieve the message and put it on the message stack. Message name is ‘WANT TO CONTINUE’.*/

FND_MESSAGE.SET_NAME (’FND’, ‘WANT TO CONTINUE);

/*Next FND_MESSAGE.SET_TOKEN is used to replace the token ‘PROCEDURE’ with text ‘Compiling this flexfield’.

FND_MESSAGE.SET_TOKEN (’PROCEDURE’, ’Compiling this flexfield’);

IF FND_MESSAGE.WARN THEN
/* User want to continue */

ELSE
/* User want to cancel*/

END;

/by
, , , ,

What are Business Events in Oracle Apps?

Business event is, as the term means, is simply an event that occured in business. Business is all about people taking decisions in their respective functions on the information available at that time and communicating further down the supply chain as quickly and accurately as they can: Upon order booking warehouse ships the order or based on invoice apporval payables department pays either the vendor or an employee. All these are business events in the flow of work from one department to another so that common objective is achieved at the end of the day.
Business events in workflow are no different from the above scenario. As the human actions that flow from one department to another, workflow also passes the message from source to destination so that information can be processed in the supply chain. Also business events can be raised and read accross applications and systems.
From technical point of view, just like a department which ”performs” the action that should be communicated, there is a source that “raises” the event. This event when raised is “queued” into an “agent”. Then there is an “agent listener” this picks up the queue from this agent processes it or “dequeues” it. While dequeueing, the “agent listener” processes the rule function that is attached as a ”subscription” using the information that contained in the queue message (“parameters”).
Take an example. Say a new employee joined your company. The day she reports to work, you generate an employee record for her. Say,once she is assinged an employee number, you have to communicate to payroll and systems departments (among others). This information either can be an email to both the departments or inter-office memo for each department. This memo will be picked by mail department and delivered. Once this mail is received, payroll and systems divisions use this information like employee number to complete their own work.
So if you extend this example to business events in wokflow, HR Department raises this event (creation of employee). This event will have a message to be delivered (parameters). This message will be picked up by mail department and delivered (Agent). Once delviered, respective departments receive this information (listened to the queue). This message will be delivered to an appropriate person in the department (subscriber) who will process this information (executing code or kicking a workflow off).
Nailing this down to simple diagram in technical terms, this is how it looks.

There are can be two kinds of subscriptions: Deferred or Immediate. Deferred subscriptions are those that are queued and to be processed by listener. The above example is of that kind. HR deferred the work to be processed by Payroll. On the other hand there are chances that the HR and Payroll handled by the same person. In that case there is nothing to be deferred. This is synchronous process and both the functions are performed in the same department (no need of mail delivery) or by the same person.
And subscribers can process the information in two different ways: either kick off a workflow or execute pl/sql package or java class. If the rule function controls this. Also based on the rule function (workflow vs pl/sql of Java function) appropriate queue is used. If it is pl/sql WF_DEFERRED is used and if it is Java then WF_JAVA_DEFERRED is used.

/by
, , , , , ,

What is under one item type in workflow?

While debugging a workflow issue, it is a little cumbersome to connect to the database in workflow builder to check the code behind an activity. Below sql prints all the runnable processes and activities in that process along with the pl/sql functions behind each of them in sql*plus for a given item type. Just pass in the internal name of the item type and you have it all.

 set serveroutput on;
DECLARE
l number;
m number;
l_item_type varchar2(30) :=’OEOL’;
l_std_type varchar2(30) :=’WFSTD’;
   CURSOR c1 (p_item_type IN VARCHAR2)
   IS
      SELECT display_name,NAME
        FROM wf_item_types_tl
       WHERE NAME = p_item_type;

   CURSOR c2 (p_item_type IN VARCHAR2)
   IS
      SELECT *
        FROM wf_runnable_processes_v
       WHERE item_type = p_item_type;
  

   CURSOR c3 (p_item_type IN VARCHAR2, p_process_name IN VARCHAR2,p_std_type in varchar2)
   IS
      SELECT   a.process_name, e.description, b.NAME activity_name,
               d.description act_desc, c.TYPE process_type, b.TYPE activity_type,
               b.FUNCTION, substr(icon_geometry,1,(instr(a.icon_geometry,’,’)-1))
          FROM wf_process_activities a,
               wf_activities b,
               wf_activities c,
               wf_activities_tl d,
               wf_activities_tl e
         WHERE process_item_type=p_item_type
–and activity_item_type in (p_item_type,p_std_type)
           AND process_name = p_process_name
           AND a.activity_name = b.NAME
           AND process_item_type = b.item_type
           AND b.end_date IS NULL
           AND c.end_date IS NULL
           AND a.process_name = c.NAME
           AND c.item_type = a.process_item_type
           AND a.process_version = c.VERSION
           AND b.item_type = d.item_type
           AND b.NAME = d.NAME
           AND c.item_type = e.item_type
           AND c.NAME = e.NAME
           AND b.VERSION = d.VERSION
           AND c.VERSION = e.VERSION
      ORDER BY to_number(substr(a.icon_geometry,1,(instr(a.icon_geometry,’,’)-1)));

CURSOR c4 (p_item_type IN VARCHAR2, p_process_name IN VARCHAR2,p_std_type in varchar2)
   IS
      SELECT   a.process_name, e.description, b.NAME activity_name,
               d.description act_desc, c.TYPE process_type, b.TYPE activity_type,
               b.FUNCTION, substr(icon_geometry,1,(instr(a.icon_geometry,’,’)-1))
          FROM wf_process_activities a,
               wf_activities b,
               wf_activities c,
               wf_activities_tl d,
               wf_activities_tl e
         WHERE process_item_type=p_item_type
–and activity_item_type in (p_item_type,p_std_type)
           AND process_name = p_process_name
           AND a.activity_name = b.NAME
           AND process_item_type = b.item_type
           AND b.end_date IS NULL
           AND c.end_date IS NULL
           AND a.process_name = c.NAME
           AND c.item_type = a.process_item_type
           AND a.process_version = c.VERSION
           AND b.item_type = d.item_type
           AND b.NAME = d.NAME
           AND c.item_type = e.item_type
           AND c.NAME = e.NAME
           AND b.VERSION = d.VERSION
           AND c.VERSION = e.VERSION
      ORDER BY to_number(substr(a.icon_geometry,1,(instr(a.icon_geometry,’,’)-1)));
   assertion_failure   EXCEPTION;

   PROCEDURE pl (
      str         IN   VARCHAR2,
      len         IN   INTEGER := 80,
      expand_in   IN   BOOLEAN := TRUE
   )
   IS
      v_len   PLS_INTEGER     := LEAST (len, 255);
      v_str   VARCHAR2 (2000);
   BEGIN
      IF LENGTH (str) > v_len
      THEN
         v_str := SUBSTR (str, 1, v_len);
         DBMS_OUTPUT.put_line (v_str);
         pl (SUBSTR (str, len + 1), v_len, expand_in);
      ELSE
         v_str := str;
         DBMS_OUTPUT.put_line (v_str);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF expand_in
         THEN
            DBMS_OUTPUT.ENABLE (1000000);
            DBMS_OUTPUT.put_line (v_str);
         ELSE
            RAISE;
         END IF;
   END pl;

BEGIN
 l:=1;
 m:=1;
   FOR i IN c1 (l_item_type)
   LOOP
   pl(‘===========================================================’);
   pl(‘ITEM TYPE : ‘||i.display_name);
   pl(‘===========================================================’);
      FOR j IN c2 (i.name)
      LOOP
      pl(‘———————————————————‘);
      pl(‘MAIN RUNNABLE PROCESS : ‘ ||j.display_name);
      pl(‘———————————————————‘);
      l:=1;
         FOR k IN c3 (l_item_type, j.process_name,l_std_type)
          LOOP
          pl(‘—‘||l||’.’||k.act_desc||’-‘||k.function);
            IF k.process_type = ‘PROCESS’
            THEN
             m:=1;
            FOR j IN c4 (l_item_type, k.activity_name,l_std_type)
            LOOP
              PL(‘—-‘||l||’.’||m||’:’||j.act_desc);
                m:=m+1;
               END LOOP;
            END IF;
             l:=l+1;
         END LOOP;
      END LOOP;
   END LOOP;
END;

/by
, , , , , ,

WIP completion and Asset Tracking

Back in 2009, I needed WIP completion transaction also be part of the Asset Creation process for depreciable items in Asset Tracking. The base was either R12.0.4 or R12.0.6, i don’t remember. But traditionally, for depreciable items,  following transactions are supported to create an asset in FA while the asset is still in Inventory.

  1. Miscellaneous Receipt
  2. Account Alias Receipt
  3. Account Receipt
  4. PO Receipt into Inventory
  5. PO Receipt into Projects
  6. Physical Inventory (Receipts)
  7. Cycle Counting (Receipts)

But there are companies that manuafacture products and they want to track them in FA. They typically get into inventory with WIP completion transaction. As you can see this transaction is not in the list above.
Recently, to my surprise, I found this patch 7489949 (which was released in 2008) that includes WIP completion also into supported transactions for asset creation. To add to my surprise, support had no clue when they were asked for solution at that time. Of course we came a long way.
BTW, it is included in base release of R12.1.1.

/by
, , , ,

R12: Asset Tracking for Depreciable Items

As discussed in the earlier article Asset Tracking is all about two kinds of items: Depreciable and Normal Items. I already discussed normal items with simple inventory transactions earlier. Let us discuss depreciable items in this article. This is very peculiar situation where you will be receiving items into inventory and at the same time depreciating them by creating assets while it is sitting in your inventory.
Sounds odd but this is reality in a number of asset intensive industries. Take construction industry for example. All the equipment and material that is required to construct a plant or a building is typically stored on site. Here we should be able to keep track of inventory from planning perspective and at the same time some of the items while sitting in inventory on site. Asset tracking for depreciable items comes handy here.
The flow is very simple. You receive items against a purchase order and run one program which creates an asset for you in Fixed Assets. From then on the asset starts getting depreciated. But where it gets tricky is in accounting. Since we are receiving this into inventory as well as creating an asset in fixed asset, potentially we are counting the asset value twice. An asset value can be in inventory or assets not both. So how Oracle handles this? Also what happens if we dispose of or sell this asset? Let us examine the accounting entries for each transaction to understand this.
Take a case. Donald Drumps Construction Company is constructing a huge building in the suburbs of New York. A forklift was required to be purchased for the construction needs. This forklift is treated as an asset hence required to be created as asset. Hence a purchase order was raised and sent to the supplier to be delivered at the construction site. It costs $150,000 and the company uses standard costing.
Each construction site can be assigned a different organization or a Subinventory within an organization. A location should be created for this construction site in New York and assigned to this Subinventory. Important attributes in the item master to be enabled are shown here.
When the supplier sends forklift against this PO, someone on site receives into inventory. As soon it is received into inventory, IB gets created, marking the transaction (csi_transactions) ready to be processed for asset creation.
Accounting for PO Receipt transaction into Inventory is as shown here.
 Now as per the flow running the program Create Assets: Interface Inventory Transactions to Assets will interface the asset to the fixed assets taking this information from IB and inventory. Creation of assets also involves in an accounting entry as the asset value is increasing. So the accounting in this transaction is:
As you can see here, inventory account is used to credit, debiting the asset account taken from the asset category assigned to the item in the item master. So the inventory value goes down in balance sheet increasing the asset value from fixed asset. All this is happening at cost of the item. And the asset starts depreciating for the period of the project.
What happens after the building construction is done?
Out of a number of possibilities, two things can happen. This forklift can be moved to another site where construction is going on or simply after the project is successfully completed, this can be sold right from the site to another smaller construction company. If it is moved to another site (considered as Asset Move in Asset Tracking) depending on how the other site is configured in the system (different organization mapped to different asset book or same asset book), treatment in assets is different. But let us take the case where Mr. Drump is tired of this asset and would like to sell it off. Since quantity exists in inventory we can create a sales order and ship it. When you ship accounting is (assuming that he is selling at 60% of the cost as the equipment is already used):
 This is not good. As you can see we credited Inventory account twice. Once when we are creating asset and once shipping this forklift after usage. Also since we have shipped the asset to someone else, Mr. Drump should retire this asset from asset books by retiring the asset.
Asset Tracking handles this well. Inventory sends this shipping transaction message to IB which identifies the transaction to be eligible for asset retirement. Another program, called Interface Move Transactions to Assets is run to send the retirement message to Fixed Assets from IB. Asset retirement ensues.
But what about crediting the inventory account twice? To solve this issue there is another program we need to run to reverse the shipping accounting: Create Reversal GL entries For Inventory FA Items. This program identifies transactions that are eligible (rows in csi_transactions table with gl_interface_status_code flag set to 1) and inserts rows into gl_interface for importing into GL. So when you run this program, accounting is:
You can see that COGS account is not touched as revenue is involved and only Deferred COGS is touched. This might surprise you because, you thought, with the advent of Subledger Accounting, all transactions from sub-ledgers to GL flow through the Subledger Accounting application. Here is one accounting entry flowing into GL without Subledger accounting, making reconciliation harder for inventory.
Moreover this left credit balance in my deferred COGS account as you figured it out!
Points to consider:

  • Don’t be surprised to see inventory account having the source of Assets when reconciling inventory to GL. Also accounting flows from Asset Tracking but with the source of Inventory (reversals).
  • What happens to all those reports that are used to match the inventory value by quantity and inventory value in inventory account? The will not match here because quantity very well exists but the value in the account got credited elsewhere in assets and not in the inventory in of form of an issue.
/by