, , , , , , , , ,

R12: Create, Allocate and Transact a Move Order using APIs

Move Orders in Oracle provide good functionality for approval based movement of material with in an inventory organization. Move Order Approvals use a workflow (INV: Move Order Approval) for approvals and hence provide flexibility to extend it for approvals.
Move Orders can be created from different sources. These move orders are identified by Move order type at the header level.

Different types of move orders are:
Move Order Requisitions: These are created from the move order form. Based on the setup at the organization and item level, move orders require approval before they can be allocated and transacted. These requisitions can be created to charge an account or move the material from one subinventory to another.
Replenishment Move Orders: These are auto-created with pre approved status. Depending on the planning type you can replenish the quantity in a subinventory for an item. Three types of replenishment plans (in relation to move orders) are available:

  1. Min-max Replenishment Planning
  2. Kanban Replenishment Planning
  3. Replenishing Count planning

Based on these sources, when appropriate programs are run, move orders are automatically created.
Pick Wave Move Order Requisitions: These move orders are specifically for the sales order picking process. When Pick Releasing program is run move orders are created as preapproved and allocated. Pick Confirm process transacts these pick wave move orders.
Move orders contain headers and lines. All types of approved move orders have to be allocated (basically reserved) before they can be transacted. Depending on the item transaction controls (Subinventory, Locator, Revision, Lot or Serial number) move order creation and allocation gets complex.
The process is simple. Create and approve Move orders, allocate move order lines and transact them.
In this article let us review creating, allocating, and transacting a move order requisition of first type (requisitions) using APIs. These move orders will be created in pre approved status and hence do not use workflow. Move Order Issue transaction type is used in this example. An account is chosen while creating this move order. When successfully transacted, this account gets debited, crediting inventory account of the subinventory. From the user interface, to create Move orders, Move Orders window is used.
The lines are allocated when allocation step is performed. In this example the logic for allocation and transaction is borrowed from pick confirmation code. To allocate and transact same form is used: Transact Move Orders.
If the item is lot and serial number controlled (as in this example), you have to pass the lot number and serial number information into the move order lines table parameter. And also make sure to pass another parameter (p_suggess_serial) as true. This will automatically take care of allocating the lot and serial numbers into the appropriate table as mentioned in the process flow. Also when we allocate the move order from the user interface, the records in the form are created in the mtl_material_transactions_temp table with transaction_status as 2, which allows the user to change the values from user interface. But in order to perform transaction from SQL, you have to change this value to 3 so that the transaction manager can pick these records up. You find that logic in the allocating code.
The tables affected in each process step are mentioned in the process flow. Code for each step as well as complete code to create, allocate and transact can be see here:
Oracle R12 Create Order using APIs
Oracle R12 Allocate Order using APIs
Oracle R12 Transact Order using APIs
Oracle R12 Move Order using APIs

/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
, , ,

SLA functionality

A good part of SLA functionality is deriving correct account based on business rules. This functionality is called defining account derivation rules. The outcome of accounting derivation rule sometimes is an entire account (famously known as code combination) or simply a segment in the code combination. If only a segment is derived then each of the segment in the chart of accounts is weaved together just like the way it is done in workflow account generator. Take for example revenue account. Oracle gives us a default account from the transaction in AR which is generated using Auto Accounting. What we can do is, take that account and plant a specific segment, say, natural account under specific condition. Also the whole account can be replaced with a new one under specific conditions.
The difference between workflow account generator and SLA accounting derivation rules is that, the conditions under which the account or a segment derived reside in code in first one but are visible to the business users in later one. They understand the conditions under which an account is derived because it is configured in the screens and not sitting some cryptic code. Also little room for bugs, so to speak.
Usually we generate the account for either credit or debit line of a journal entry. An accounting entry, at a minimum, has a debit and credit. These are called are journal lines (a debit line and a credit line). An accounting derivation rule is for a specific journal line. Going a little further this journal entry is for a transaction. This transaction event is called event class. These two make up the heart of SLA setups.
Taking the same example of revenue account, revenue account is a journal line. Revenue is one side or one line in an accounting entry. The other usually is a receivables account (in case of AR transaction). These two together make a journal entry. And this journal entry belongs to an event class called Invoice (in case it is invoice).But if you have observed the line definitions under invoice event class you might have noticed way more than these two lines. That is because each of them is used in different ways depending on the nature of transaction.
Implementation: What is in it? What kind of work is involved?
Traditionally we have accounting in each application or a process flow is taken care of by individual specialist in that area. For example an AR specialist used to get the accounting needs implemented as per the requirement and cost management or order management specialist gets the cost accounting right. So a business process owner for accounting has to run around each of these specialists to get the accounting taken care of. Also prior to SLA the functionality provided in Oracle is rigid in some application areas and very flexible in a number of other areas. To cater to these flexible needs a number of tools were also available like Workflow account generators, Post or Pre hooks or some setups to generate accounting based on business rules.
SLA is a game changer from that perspective. We can throw away workflow accounting generator and post or pre-hooks coding. And also one specialist, who understands enterprise accounting and business needs of accounting department, can implement complete SLA solution in all applications and processes. This is new and emerging consulting role.
Implementing SLA involves writing rules to arrive at accurate accounting under specific conditions for a specific transaction event. To achieve what you need, we may need to write some coding in custom sources but in general that is far simpler than workflow. All you need to know if writing simple pl/sql code.
But for a SLA specialist the challenge is in understanding transactions and event class is mapping to these transactions. Sometimes it is self explanatory. An event class like Sales Order issue in Cost Management or Invoice in AR tells you that these are for COGS and Revenue respectively. But not all are so straight forward. But if you have knowledge about the transactions performed in each application areas and some technical background it is pretty easy to get there.

Apart from generating accounts there are a lot of other features in SLA.

/by
, , , ,

Concept behind Subledger Accounting

Before going into any detail, let me take you into accounting world for a brief moment. Fundamentally accounting is based on two methods : Cash Basis or Accrual Basis.
Accrual Basis Accounting
Under the accrual basis accounting, revenues and expenses are recognized as follows:
AR:

  •  Revenue recognition: Revenue is recognized when both of the following conditions are met:
        a. Revenue is earned.
        b. Revenue is realized or realizable.
  •  Revenue is earned when products are delivered or services are provided.
  • Realized means cash is received.
  • Realizable means it is reasonable to expect that cash will be received in the future.

AP:

  • Expense recognition: Expense is recognized in the period in which related revenue is recognized (Matching Principle).

Cash Basis Accounting
Under the cash basis accounting, revenues and expenses are recognized as follows:
AR:

  • Revenue recognition: Revenue is recognized when cash is received.

AP:

  • Expense recognition: Expense is recognized when cash is paid.

 Timing differences in recognizing revenues and expenses:

  1. Accrued Revenue: Revenue is recognized before cash is received.
  2. Accrued Expense: Expense is recognized before cash is paid.
  3. Deferred Revenue: Revenue is recognized after cash is received.
  4. Deferred Expense: Expense is recognized after cash is paid.

                 Options in 11i                            To                                Options in 12
Till 11i the only way we represent this accounting method is by choosing accounting method in Payables Options in AP and System Options in AR. But in R12 you can see in that these options are gone from the system options of AP and AR. That is where subledger accounting comes in.
Part of the global release concept in R12, accounting methods have to be much more flexible and generation of accounting entries should be configurable.
As we know accounting is the end product of transctions and financial statements are end products of accounting. Also there is a need to seperate transaction from accounting. An accounting clerk who creates an invoice has nothing to do what accounting is behind that transaction. It is the duty of the management to decide accounting behind this transaction. 
Subledger Accounting is taking us in that direction.
Purpose of Subledger Accounting
The end product of Subledger Accounting Setups is a Subledger Accounting Method that can be assigned to one or more ledgers in GL. All accounting in different subledger applications is subject to the rules defined in this accounting method.
In 11i, as mentioned earlier, the only way to choose accounting method we chose is AR and AP system options setup (Cash Vs Accrual). We used start in GL setting up the Set of books and then define the organization information like Legal Entity and Operating units and so on. And then define these accounting methods for each operating unit. As you can see operations and accounting are so closely meshed with each other. But in R12 it is not the same. In this release it is now configurable in Subledger Accounting setups taking this away from system options of individual products.
Demystifying subledger accounting setups
Out of the box, Oracle seeds accounting rules for all applications. If you are satisfied with the Oracle’s seeded rules, there is no need to change any setup and you can use those existing rules (Accounting Method for Accrual is Standard Accrual and for Cash is Standard Cash). This screenshot here shows you the difference between the Accrual Basis of accounting and Cash Basis of Accounting. As you can see here, per rules, there is no accounting created when invoice is created under cash basis (no revenue is recognized until cash is received) but accounting is created when cash is realized. Invoice is accounted as soon it is completed under Accrual Method. This is configurable here where as in 11i we did not have a choice!.
If you choose this accounting method, accounting works exactly the way it works in previous releases.
Subledger Accounting as a gatekeeper of Reconciliation
       R11i Transfer to GL                                                                   R12 Transfer to GL
Starting R12 all accounting entries are generated and passed through subledger accounting application instead of directly going to GL. Hence reconciliation is already done between source to Subledger Accounting and Subledger Accounting to GL, reducing huge amount of time spent on reconciliation. Since these entries have to flow through the subledger accounting application, there is a need to map the source application accounting entries to subledger accounting. That is key for the setups.
Mapping a transaction to Subledger Accounting Setup
                                        AR Invoice Accounting
Let us take a simple example. Whenever you create an AR Invoice following accounting takes place. 
                             
                             Invoice Accounting in AR
Taking a step back and thinking through, this transaction is happening in AR for the Invoice Creation event….                                            
                                            Subledger Accounting Setup Model
Now we map the source (AR Invoices) to Subledger Accounting as shown here. So to conclude
Journal Line Types are nothing but accounting line types (Receivable or Revenue).
Event Classes identify a transaction type (Invoice Vs Credit Memo).
These two are assembled using Accounting Derivation Rules and Sources.
All these together make up Application Accounting Definition for Receivables.
Different Application Accounting Definitions together make up a Subledger Accounting Method.
This method can be attached to one or more Ledgers.

/by