, , , , , , , ,

Oracle R12 Allocate Order using APIs

DECLARE
   x_return_status   VARCHAR2 (1);
   x_msg_data        VARCHAR2 (4000);
   x_msg_count       NUMBER;
   l_line_tbl        inv_move_order_pub.trolin_tbl_type;

   PROCEDURE allocate_move_order (
      p_line_tbl        IN       inv_move_order_pub.trolin_tbl_type
    , x_return_status   OUT      VARCHAR2
    , x_msg_data        OUT      VARCHAR2
    , x_msg_count       OUT      NUMBER
   )
   IS
      x_line_tbl        inv_move_order_pub.trolin_tbl_type;
      l_trolin_tbl      inv_move_order_pub.trolin_tbl_type;
      l_mold_tbl        inv_mo_line_detail_util.g_mmtt_tbl_type;
      l_qty_detailed    NUMBER;
      l_qty_delivered   NUMBER;
      l_return_status   VARCHAR2 (1);
      v_msg_index_out   NUMBER;
      l_rsr_type        inv_reservation_global.mtl_reservation_tbl_type;
      i                 INTEGER;
      l_trolin_rec      inv_move_order_pub.trolin_rec_type;
   BEGIN
      x_line_tbl := p_line_tbl;

      IF x_line_tbl.COUNT > 0
      THEN
         FOR j IN x_line_tbl.FIRST .. x_line_tbl.LAST
         LOOP
            DBMS_OUTPUT.put_line (x_line_tbl (j).line_id);

            BEGIN
               inv_ppengine_pvt.create_suggestions (p_api_version              => 1.0
                                                  , p_init_msg_list            => fnd_api.g_false
                                                  , p_commit                   => fnd_api.g_false
                                                  , p_validation_level         => fnd_api.g_valid_level_none
                                                  , x_return_status            => x_return_status
                                                  , x_msg_count                => x_msg_count
                                                  , x_msg_data                 => x_msg_data
                                                  , p_transaction_temp_id      => x_line_tbl (j).line_id
                                                  , p_reservations             => l_rsr_type
                                                  , p_suggest_serial           => fnd_api.g_true
                                                  , p_plan_tasks               => FALSE
                                                  , p_quick_pick_flag          => ‘N’
                                                  , p_organization_id          => 207
                                                   );
               DBMS_OUTPUT.put_line (‘Return Status is :’ || x_return_status);
               DBMS_OUTPUT.put_line (‘Message Count is :’ || x_msg_count);

               IF x_return_status = ‘S’
               THEN
                  BEGIN
                     l_trolin_tbl := x_line_tbl;

                     IF (l_trolin_tbl.COUNT <> 0)
                     THEN
                        i := l_trolin_tbl.FIRST;

                        WHILE i IS NOT NULL
                        LOOP
                           IF (    l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_unexp_error
                               AND l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_error
                              )
                           THEN
                              l_trolin_rec := inv_trolin_util.query_row (l_trolin_tbl (i).line_id);
                              l_trolin_tbl (i) := l_trolin_rec;
                              l_qty_detailed := l_trolin_tbl (i).quantity_detailed;
                              l_qty_delivered := NVL (l_trolin_tbl (i).quantity_delivered, 0);

                              IF NVL (l_qty_detailed, 0) = 0
                              THEN
                                 l_mold_tbl := inv_mo_line_detail_util.query_rows (p_line_id => l_trolin_tbl (i).line_id);

                                 FOR j IN 1 .. l_mold_tbl.COUNT
                                 LOOP
                                    l_mold_tbl (j).transaction_status := 3;
                                    l_mold_tbl (j).transaction_mode := 1;
                                    l_mold_tbl (j).source_line_id := l_trolin_tbl (i).line_id;
                                    inv_mo_line_detail_util.update_row (l_return_status, l_mold_tbl (j));
                                 END LOOP;

                                 SELECT transaction_header_id, transaction_quantity
                                   INTO l_trolin_tbl (i).transaction_header_id, l_trolin_tbl (i).quantity_detailed
                                   FROM mtl_material_transactions_temp
                                  WHERE move_order_line_id = l_trolin_tbl (i).line_id;

                                 l_trolin_tbl (i).last_update_date := SYSDATE;
                                 l_trolin_tbl (i).last_update_login := fnd_global.login_id;

                                 IF l_trolin_tbl (i).last_update_login = -1
                                 THEN
                                    l_trolin_tbl (i).last_update_login := fnd_global.conc_login_id;
                                 END IF;

                                 l_trolin_tbl (i).last_updated_by := fnd_global.user_id;
                                 l_trolin_tbl (i).program_id := fnd_global.conc_program_id;
                                 l_trolin_tbl (i).program_update_date := SYSDATE;
                                 l_trolin_tbl (i).request_id := fnd_global.conc_request_id;
                                 l_trolin_tbl (i).program_application_id := fnd_global.prog_appl_id;
                                 inv_trolin_util.update_row (l_trolin_tbl (i));
                              END IF;
                           END IF;

                           i := l_trolin_tbl.NEXT (i);
                        END LOOP;
                     END IF;
                  END;
               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;
         END LOOP;
      END IF;
   END;
BEGIN
   l_line_tbl (1).line_id := 3929705;
   allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count);
   COMMIT;
END;

/by
, , , , , , , ,

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

How SLA derives all the rules required for Create Accounting?

We all know SLA is rule driven to derive accounts for create accounting process. If you are not using standard (seeded) accounting rules, but have created a new one with a new application accounting method for a specific application, how create accounting program uses these rules to derive correct accounting?
We talked about SLA earlier here. If you follow the earlier article, we are talking about using application accounting method (custom) to derive an account for a specific journal line type (debit or credit). To achieve custom accounting you assign this accounting derivation rule to this journal line type. These journal line types roll into event classes (invoices or deposits) and entities (AR transactions) to form Application Specific Accounting Method. This accounting method is assigned to an application of interest where this desired accounting is expected. This accounting method has to be validated (Validation Program) for this to be used.
This validation process actually creates a database package where all these rules are coded and maintained. To identify the package you can use this simple script which gives you the package name. Parameters to this package have to be sourced from the table XLA_PRODUCT_RULES_B. This tables stores the application accounting definitions that we talked about earlier. For example you want to get the custom application accounting method you have created for Receivables, you can simply use this SQL to get that.
Pass the values from the above SQL to this simple function get that package. If you want to see standard rules, change product rule type code from C to S. If you have more than one product rule code (application accounting method), you should know which one you are looking for. If you open this database package your technical eyes can see what is happening in create accounting program.

select application_id,
product_rule_code,
product_rule_type_code,product_rule_hash_id from xla_product_rules_b
where application_id=222 –Receivables
and product_rule_type_code=’C’;

DECLARE
c_package_name CONSTANT VARCHAR2 (30) := ‘XLA_$id1$_AAD_$id2$_$id3$_PKG’;
l_package_name VARCHAR2 (1000);
FUNCTION getpackagename (
p_application_id IN NUMBER,
p_product_rule_type_code IN VARCHAR2,
p_product_rule_hash_id IN NUMBER
)
RETURN VARCHAR2
IS
l_name VARCHAR2 (30);
l_hashapplication VARCHAR2 (30);
l_hashrulecode VARCHAR2 (30);
l_log_module VARCHAR2 (240);
BEGIN
l_hashapplication := LPAD (SUBSTR (TO_CHAR (ABS (p_application_id)), 1, 5), 5, ’0′);
l_hashrulecode := LPAD (SUBSTR (TO_CHAR (p_product_rule_hash_id), 1, 6), 6, ’0′);
l_name := c_package_name;
l_name := REPLACE (l_name, ‘$id1$’, l_hashapplication);
l_name := REPLACE (l_name, ‘$id2$’, p_product_rule_type_code);
l_name := REPLACE (l_name, ‘$id3$’, l_hashrulecode);
RETURN l_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END getpackagename;
BEGIN
l_package_name :=
getpackagename (p_application_id => 222,–application ID
p_product_rule_type_code => ‘C’, –Rule type
p_product_rule_hash_id => 18 –Hash value
);
DBMS_OUTPUT.put_line (l_package_name);
END;

/by