, , , , , , , ,

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

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

Creating Manual Journal Entries from Sub ledger Accounting (SLA)

One of many cool features in SLA is creating manual journal entries. Many times we have a need to pass manual entries to reverse the entries happened a source transaction because the account is wrong or date is wrong or even the value is wrong. All sub ledgers give you a way to reverse the transaction and hence reverse the associated accounting entry. But sometimes you may run out of time and luck and resort to manual entries in GL. Once we are GL to enter manual entries for transactions that pertain to sub ledger, we lose an opportunity to reconcile as we do not have primary references for which we have created this accounting entry for. If we have any reports that are based on SLA tables the reconciliation becomes harder as we cannot link anything that happened in the GL with the SLA. After all, the purpose of SLA is to eliminate this risk of reconciliation with GL.
Also, I guess, Oracle Support uses this route (manual journal entries) to fix any entries that were corrupted in the original transaction.
While entering these manual entries we can capture original transaction attributes using supporting references feature where you can capture attributes like asset_id if it is Assets related entry, transaction_id if it is related to Inventory, invoice_Id for AP and customer_trx_id or cash_receipt_id if it is AR invoice or cash respectively, These give meaning to the manual journal entries.
Process comes in three steps: 
Creating entries, saving them as incomplete entries with in SLA to be posted later and finally completing this so that this will automatically transfer to GL, journal Import and Post them as needed. To create manual entries you need to assign a sub-function SLA: Create Subledger Journal Entry to the menu. It will appear as a button in the view journal entries page.
The same can be achieved with an API XLA_JOURNAL_ENTRIES_PUB_PKG. Also this is immensely useful if you want to create accounting entries related to transactions in legacy or related systems. You can capture the supporting references related to the related system for reconciliation purpose.
The best part of using this API is that, we do not have to separately code for Journal Import and Posting (with complete journals parameter to complete, transfer and post). The value you pass to this parameter takes care of that.
Sources:Oracle Financial Accounting Hub Implementation Guide (Chapter 12 on Manual Sub ledger Journal Entries API) and Oracle Subledger Accounting Implementation Guide (Chapter 6 Sub ledger Journal Entries).
/by