, , , , , , , ,

Oracle R12 Create Order using APIs

DECLARE
   PROCEDURE create_move_order
   IS
      l_hdr_rec         inv_move_order_pub.trohdr_rec_type              := inv_move_order_pub.g_miss_trohdr_rec;
      l_line_tbl        inv_move_order_pub.trolin_tbl_type              := inv_move_order_pub.g_miss_trolin_tbl;
      x_return_status   VARCHAR2 (1);
      x_msg_count       NUMBER;
      x_msg_data        VARCHAR2 (4000);
      x_hdr_rec         inv_move_order_pub.trohdr_rec_type              := inv_move_order_pub.g_miss_trohdr_rec;
      x_hdr_val_rec     inv_move_order_pub.trohdr_val_rec_type;
      x_line_tbl        inv_move_order_pub.trolin_tbl_type;
      x_line_val_tbl    inv_move_order_pub.trolin_val_tbl_type;
      v_msg_index_out   NUMBER;
      l_rsr_type        inv_reservation_global.mtl_reservation_tbl_type;
   BEGIN
      l_line_tbl.DELETE;
      x_line_tbl.DELETE;
      mo_global.set_policy_context (‘S’, 204);
      inv_globals.set_org_id (207);
      fnd_global.apps_initialize (1005902, 50583, 401);
      l_hdr_rec.date_required := SYSDATE;
      l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
      l_hdr_rec.organization_id := 207;
      l_hdr_rec.status_date := SYSDATE;
      l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
      l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
      l_hdr_rec.db_flag := fnd_api.g_true;
      l_hdr_rec.operation := inv_globals.g_opr_create;
      l_hdr_rec.description := ‘Test Move Order’;
      l_hdr_rec.to_account_id := 12831;
      l_hdr_rec.from_subinventory_code := ‘FGI’;
      l_line_tbl (1).date_required := SYSDATE;
      l_line_tbl (1).inventory_item_id := 167742;
      l_line_tbl (1).line_id := fnd_api.g_miss_num;
      l_line_tbl (1).line_number := 1;
      l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
      l_line_tbl (1).transaction_type_id := inv_globals.g_type_transfer_order_issue;
      l_line_tbl (1).organization_id := 207;
      l_line_tbl (1).quantity := 1;
      l_line_tbl (1).status_date := SYSDATE;
      l_line_tbl (1).uom_code := ‘Ea’;
      l_line_tbl (1).db_flag := fnd_api.g_true;
      l_line_tbl (1).operation := inv_globals.g_opr_create;
      l_line_tbl (1).from_subinventory_code := ‘FGI’;
      l_line_tbl (1).to_account_id := 12831;
      l_line_tbl (1).lot_number := ‘A01’;                                                         –If the item is lot controlled
      l_line_tbl (1).serial_number_start := ‘A01-0031’;                                 –If the item is serial number controlled
      l_line_tbl (1).serial_number_end := ‘A01-0031’;                                   –If the item is serial number controlled
      inv_move_order_pub.process_move_order (p_api_version_number      => 1.0
                                           , p_init_msg_list           => fnd_api.g_false
                                           , p_return_values           => fnd_api.g_false
                                           , p_commit                  => fnd_api.g_false
                                           , x_return_status           => x_return_status
                                           , x_msg_count               => x_msg_count
                                           , x_msg_data                => x_msg_data
                                           , p_trohdr_rec              => l_hdr_rec
                                           , p_trolin_tbl              => l_line_tbl
                                           , x_trohdr_rec              => x_hdr_rec
                                           , x_trohdr_val_rec          => x_hdr_val_rec
                                           , x_trolin_tbl              => x_line_tbl
                                           , x_trolin_val_tbl          => x_line_val_tbl
                                            );
      DBMS_OUTPUT.put_line (‘Return Status is :’ || x_return_status);
      DBMS_OUTPUT.put_line (‘Message Count is :’ || x_msg_count);
      DBMS_OUTPUT.put_line (‘Move Order Number is :’ || x_hdr_rec.request_number);
      DBMS_OUTPUT.put_line (‘Number of Lines Created are :’ || x_line_tbl.COUNT);

      IF x_return_status = ‘S’
      THEN
         COMMIT;
      ELSE
         ROLLBACK;
      END IF;

      IF x_msg_count > 0
      THEN
         FOR v_index IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => v_index, p_encoded => ‘F’, p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
            x_msg_data := SUBSTR (x_msg_data, 1, 200);
            DBMS_OUTPUT.put_line (x_msg_data);
            DBMS_OUTPUT.put_line (‘============================================================’);
         END LOOP;
      END IF;
   END;
BEGIN
   create_move_order;
END;

/by
, , , , , , , , ,

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