Standard Line Status Flows in OM

Standard Line Status Flows in OM

Oracle Order Management captures the order line status in the Sales Order Pad on the Line Items Main tab in the status field and in the Order Organizer on the Summary and Line tabs. Oracle Shipping Execution displays the delivery line status in the Shipping Transactions Form on the Lines/LPN Main tab in the Line Status field. For a standard flow the statuses are:

Begin by placing the order in Order Management (OM):

Entered (OM): Order is saved but not booked.

Booked (OM): Order is booked.

Scheduled (OM): A user can customize the Workflow to show the Scheduled status which indicates that the order line has been successfully scheduled. When the ship line logic starts, the order line status changes to Awaiting Shipping.

Awaiting Shipping (OM): Order is booked but lines are not yet picked.

Open (OM): This status of a delivery on the Additional Line Information form indicates that none of the delivery lines associated with that delivery have been ship confirmed.

Ready to Release (SE): Order line is booked and passed to Shipping Execution. It is now a delivery line that is eligible for Pick Release.

Submitted for Release (SE): In Release 11i.4, Submitted for Release status changes name to Released to warehouse.

Released to Warehouse (SE): Pick Release has started but not completed. Either no allocations were created or allocations have not been Pick Confirmed.

Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used.

Released (SE): Pick Release has run successfully. The action has translated the delivery lines into move order lines in Inventory. The move order lines have been allocated and pick confirmed. Once a delivery has been created, the delivery and its associated delivery lines are eligible for Ship Confirm. This status is no longer valid starting with Release 11i.4. Released was replaced by the following three additional statuses:

Backordered (SE): The delivery line is pick released but no allocations were created or partial allocations occurred. As an example, if a delivery line has a quantity of 100, and at pick release only 25 are available for allocation, the original delivery line splits to create a new line (quantity of 75) for the unallocated portion with a status of

Backordered. The quantity on the original delivery line changes to 25 to reflect the allocated portion with a status of Staged/Pick Confirmed.
Staged/Pick Confirmed (SE): The delivery line is successfully pick released. It occurs after pick confirm to indicate subinventory transfer from source location to staging location is complete. Lines staged until they are ship confirmed.

Both Backordered and Staged/Pick Confirmed status provide the ability to perform opportunistic cross-docking for warehouse organizations with Oracle Warehouse Management System (WMS) installed.

Shipped (SE): This line status indicates that the delivery associated with the delivery line(s) is ship confirmed.

In Transit (SE): This delivery status indicates that the delivery associated with the line is ship confirmed and the pick up stop is closed.

Confirmed (SE): This delivery status indicates that the delivery line is either shipped or backordered and the trip stops are open.

Navigate back to Order Management and query the order which results in OM pulling updated Pick Release information from Shipping Execution (in R11i.4, these statuses will occur after Staged/Pick Confirmed and Backordered respectively):

Picked (OM): Pick release has completed normally (both allocation and pick confirm). The delivery associated with the delivery line(s) may have also been Ship Confirmed but the Delivery may not be set in transit and the Trip may not be closed.

Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred. The delivery line splits during Ship Confirm and the information passes to Order Management through the Process Order API. The order line then splits to reflect the changes that occurred during the Shipping process. As an example, a customer orders quantity 50. There are 20 on hand in inventory. The delivery line splits into two delivery lines and therefore represents two order lines in Order Management. The order line with quantity 20 has the status of Picked or Shipped depending on whether
or not the delivery line is Ship Confirmed, the Delivery set in transit and the Trip closed. The second order line with a quantity of 30 has a status of Awaiting Shipping.

Shipping Execution pushes status information to Order Management once Ship
Confirm is completed:

Shipped (OM): The delivery associated with the line is Ship Confirmed. The Delivery status is set to in transit. This status appears in the Additional Line Information at the Pick Status field.

Interfaced (SE): If delivery was sourced from Oracle OM: The delivery line is shipped and the OM Interface and Inventory Interface concurrent processes have completed.

If delivery was sourced from an Oracle Application other than OM: The delivery
line is shipped and the Inventory Interface concurrent process has completed.

Awaiting Fulfillment (OM): Not all shippable lines in a fulfillment set or a configuration are fulfilled. This is a synchronization step within the Workflow process.

Fulfilled (OM): All lines in a fulfillment set are fulfilled.
Note: Fulfillment Sets are defined as a group of order lines that get fulfilled together. Items that are not shippable can be in fulfillment sets with shippable items, and then will not be fulfilled (and therefore invoiced) until the shippable items are fulfilled. A line can belong to either a ship set or an arrival set, but can belong to multiple fulfillment sets.

Interfaced to Receivables (OM): Invoice Interface has been launched. Order Management writes information to Receivables tables.
Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

Closed (OM): Closed indicates that the line is closed. It does not necessarily indicate that the line is interfaced to Accounts Receivable (AR) since you must “close line” activity in a no-bill flow.

Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

The following scenario will emulate a Standard customer order from the first customer call to the invoice. The line status will assist the customer service agent on the Shipper's side to answer the questions of the Customer.

Entered Status (OM)
A customer calls and begins placing an order with the customer service representative. The customer is unclear whether or not the order is complete and indicates that he/she will call back to finish placing the order. The customer service representative saves the order to capture the current information but will not book the order because the customer has indicated that the order is not complete. Both the Order Header and the Order Lines associated with the customer call will have the status of Entered once the order is saved. The line on the order exists in the system and can be queried when the customer calls back to complete the order.

Booked Status (OM)
The customer service representative receives a second call the customer and adds two additional lines to the order. The customer indicates that the order is complete so the user Books the order.

Ready to Release Status (SE)
Once the order has been booked, the information passes to Shipping Execution. Order lines appear as delivery lines. Initially, it is a one to one ratio of order line to delivery line. The customer service agent calls the warehouse to ensure that the order that was just booked has appeared in Shipping Execution. The warehouse clerk queries the delivery lines by the order number provided by the customer service representative and indicates that the Line Status is Ready to Release indicating the delivery lines are eligible for Pick Release. The customer service representative has been assured that the booked order lines are visible in the Shipping Transactions form and are ready for the next step, Pick Release.

Staged/Pick Confirmed and Released to Warehouse Statuses (SE)
The warehouse clerk launches Pick Release. Upon querying the delivery lines by order number, the warehouse clerk will see that the Pick Release status is: Staged/Pick Confirmed for those delivery lines that have received allocation and Pick Confirmed successfully and Released to Warehouse for delivery lines that require a manual Pick Confirm or have not been allocated.

Picked and Awaiting Shipping Statuses (OM)
The customer who placed the order calls up and wants to know the status, the customer service representative queries up the order in the Order Organizer and finds that the status of the lines are Picked and Awaiting Shipping. The customer service representative is equipped to report that two of the order lines are processing smoothly as they have been picked from their source location and transferred to the staging location within the warehouse. The customer service representative can also inform the customer that the third order line has been released to the warehouse.

Closed and Picked Status (OM)
The warehouse clerk has just Ship Confirmed the delivery associated with the delivery lines corresponding to the customer’s order. The warehouse clerk used the check boxes on the Ship Confirm form to automatically set the delivery in transit and close the trip. Order Management will be updated through the Process Order API and the order lines that previously had the status of Picked will now show a status of Closed. The customer calls back to check the status of the order, the customer service representative can tell the customer the date(s) that two of the order lines physically shipped from the warehouse. The customer service representative can also inform the customer that the third order line has been Picked, indicating that the next function is to ship the product.

O2C Cycle with Screenshots

NAVIGATION:

Enter Header & Line Details.

Copy the Order Number .

Navigation:

Shipping – Transaction – Enter

Give Order Number and Org Code(M1) & Line Status.( Take blank space)

And Press  'Find Button.'

And Press Find Button.

Here we will see the Exception Details.(Exception check box)

Because Period dates and Scheduling Dates are not matched. That's why this Exception will be raised.

These Exception Details are stored in   "WSH_EXCEPTIONS".

NOW GO TO TOOLS MENU==èResolve Exception Form(Select)

THEN PRESS OK BUTTON

THIS FORM WILL SHOW ONE EXCEPTION MESSAGE.

THEN  GO TO TOOLS MENU=èSELECT RESOVE PURGE EXCEPTION

PRESS 'SUBMIT ' BUTTON.

THEN PRESS 'YES 'BUTTON.

Then press YES.

                               

CLOSE ALL FORMS .

GO TO NAVIGATION

AGAIN OPEN SHIPPING==è TRANSACTIONS

THEN PRESS FIND BUTTON

see the Exception Details in above form .(The Exception check box unchecked).

Go to shipping==èRelease Sales Order=èRelease Sales Order(select).

Enter the Order number .it will display order type (default).

Now 'Seheduled ship dates' and 'Requested Dates'

Will be deleted.

Go to inventory tab

Enter warehouse =è   M1

<![if !vml]><![endif]>

THEN GO TO SHIPPING TAB

IT WILL GENERATE AUTOMATICALLY

Auto Create Delivary 'yes'

Auto Pick Conform  'yes'

Then press concurrent button

Then press 'ok button

go to view menu select Request=èpress find button

Next, we have to Ship the Sales Order.

Navigation Path

Shipping  Transactions

Click on the Form and enter the Sales Order Number 51823 and click on

Find Button

Shipping Transactions Window Opens and the Line Status shows as Stage

/Pick Confirmed for the Sales Order no 56710

Click on Delivery Tab and choose Ship Confirm from Actions LOV and

click Go Button.

This Opens the Confirm Delivery Screen. Click on OK Button. This will

generate a concurrent request. Click OK and view the Concurrent Request

2719527

View the Concurrent Request 2719527. The Request completed Normal. We

can view the Output of the commercial Invoice created in concurrent

Request ID 2719526

Select " Commercial Invoice 

Press view out put.

Next we can go and Check the Status of the Sales Order.

Navigation Path.

Order Management Super User

Order, Returns  Sales Order

Query for the Sales Order Number(56710) and click on Line Items to view the

Status.

Next, we need to run the Workflow Back ground Process to populate the AR

Interface Table.

Navigation Path

Order Management Super User Responsibility.

Report and Requests  Run Requests

Choose the Concurrent Request Workflow Back ground Process

And Enter the Parameters as follows.

Process Deferred set to Yes

Process Timed Out No.

Click on OK and Click Submit Button.This will generate a Concurrent

Request.

How to create your own serial numbers using API?

How to create your own serial numbers using API?

We all know how we can generate serial numbers in the system. It is governed by the the setup at the organization and item level. A the inventory organization parameters level you decide whether we want serial number generation at the organization level or item level or user defined. If you choose User Defined and at in the item master you can choose Pre-Defined as your control, you are saying you want to generate your own serial numbers. What happens here is that the the serial number generation logic is followed by the serial number pre-fix and and number that is entered in the item master. Which means you cannot truly create your own serial numbers with this approach.

In order to achieve this requirement you have to use a very obscure package called user_pkg_serial.generate_serial_number. If you put your code here, then that serial number will be generated. This is kind of a hook. The same goes with the lot number. The package is user_pkg_lot.

Who knew that there are standard database packages with names like this!!

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

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 downloaded as seen here.

Create Move Order Code:

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;

Allocate Move Order:

/* Formatted on 2008/07/08 17:57 (Formatter Plus v4.8.8) */
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;




Transact Move Order:



DECLARE
   x_return_status   VARCHAR2 (1);

   PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2)
   IS
      l_header_id        NUMBER;
      l_program          VARCHAR2 (100);
      l_func             VARCHAR2 (100);
      l_args             VARCHAR2 (100);
      p_timeout          NUMBER;
      l_old_tm_success   BOOLEAN;
      l_rc_field         NUMBER;

      CURSOR c1 (p_header_id IN NUMBER)
      IS
         SELECT transaction_header_id
           FROM mtl_material_transactions_temp
          WHERE transaction_source_id = p_header_id;
   BEGIN
      mo_global.set_policy_context ('S', 204);
      inv_globals.set_org_id (207);
      fnd_global.apps_initialize (1005902, 50583, 401);

      FOR i IN c1 (p_move_order_id)
      LOOP
         l_program := 'INXTPU';
         l_func := l_program;
         l_args := l_program || ' ' || 'TRANS_HEADER_ID=' || TO_CHAR (i.transaction_header_id);
         p_timeout := 500;
         COMMIT;
         l_old_tm_success :=
            inv_pick_wave_pick_confirm_pub.inv_tm_launch (program      => l_program
                                                        , args         => l_args
                                                        , TIMEOUT      => p_timeout
                                                        , rtval        => l_rc_field
                                                         );

         IF l_old_tm_success
         THEN
            x_return_status := 'S';
            DBMS_OUTPUT.put_line ('Result is :' || 'Success');
         ELSE
            x_return_status := 'E';
            DBMS_OUTPUT.put_line ('Result is :' || 'Failed');
         END IF;

         IF x_return_status = 'S'
         THEN
            COMMIT;
         ELSE
            ROLLBACK;
         END IF;
      END LOOP;
   END;
BEGIN
   transact_move_order (2055447, x_return_status);
   COMMIT;
END;

Move Order Composite:

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

   PROCEDURE create_move_order (
      x_hdr_rec         OUT   inv_move_order_pub.trohdr_rec_type
    , x_line_tbl        OUT   inv_move_order_pub.trolin_tbl_type
    , x_return_status   OUT   VARCHAR2
    , x_msg_data        OUT   VARCHAR2
    , x_msg_count       OUT   NUMBER
   )
   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_hdr_val_rec     inv_move_order_pub.trohdr_val_rec_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;
      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-0039';                                 --If the item is serial number controlled
      l_line_tbl (1).serial_number_end := 'A01-0039';                                   --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 ('Move Order ID is :' || x_hdr_rec.header_id);
      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;

   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;

   PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2)
   IS
      l_header_id        NUMBER;
      l_program          VARCHAR2 (100);
      l_func             VARCHAR2 (100);
      l_args             VARCHAR2 (100);
      p_timeout          NUMBER;
      l_old_tm_success   BOOLEAN;
      l_rc_field         NUMBER;

      CURSOR c1 (p_header_id IN NUMBER)
      IS
         SELECT transaction_header_id
           FROM mtl_material_transactions_temp
          WHERE transaction_source_id = p_header_id;
   BEGIN
      FOR i IN c1 (p_move_order_id)
      LOOP
         l_program := 'INXTPU';
         l_func := l_program;
         l_args := l_program || ' ' || 'TRANS_HEADER_ID=' || TO_CHAR (i.transaction_header_id);
         p_timeout := 500;
         COMMIT;
         l_old_tm_success :=
            inv_pick_wave_pick_confirm_pub.inv_tm_launch (program      => l_program
                                                        , args         => l_args
                                                        , TIMEOUT      => p_timeout
                                                        , rtval        => l_rc_field
                                                         );

         IF l_old_tm_success
         THEN
            x_return_status := 'S';
            DBMS_OUTPUT.put_line ('Result is :' || 'Success');
         ELSE
            x_return_status := 'E';
            DBMS_OUTPUT.put_line ('Result is :' || 'Failed');
         END IF;

         IF x_return_status = 'S'
         THEN
            COMMIT;
         ELSE
            ROLLBACK;
         END IF;
      END LOOP;
   END;
BEGIN
   mo_global.set_policy_context ('S', 204);
   inv_globals.set_org_id (207);
   fnd_global.apps_initialize (1005902, 50583, 401);
   DBMS_OUTPUT.put_line ('Creating Move Order');
   create_move_order (l_header_rec, l_line_tbl, x_return_status, x_msg_data, x_msg_count);

   IF x_return_status = 'S'
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Move Order Created');

      IF l_line_tbl.COUNT > 0
      THEN
         x_return_status := NULL;
         x_msg_data := NULL;
         x_msg_count := NULL;
         DBMS_OUTPUT.put_line ('Allocating Move Order');
         allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count);

         IF x_return_status = 'S'
         THEN
            COMMIT;
            DBMS_OUTPUT.put_line ('Move Order Allocated');
            x_return_status := NULL;
            DBMS_OUTPUT.put_line ('Transacting Move Order');
            DBMS_OUTPUT.put_line ('l_header_rec.header_id :' || l_header_rec.header_id);
            transact_move_order (l_header_rec.header_id, x_return_status);

            IF x_return_status = 'S'
            THEN
               COMMIT;
               DBMS_OUTPUT.put_line ('Move Order Transacted');
            END IF;
         END IF;
      END IF;
   END IF;
END;

O2C Cycle Baiscs

                  OM Standard Orders Flow

    Order Entry               1

      

  

   Order booking            2      

 

 

Reserving for Items

   In an Order                3

 

   

 

    Pick Release              4

 

                                      5

    Pick Confirm

 

                                      6

    Ship Confirm

 

                                       7

    Enter Invoice

 

 Close Order Line          8

 

Close Order Header      9    

Normally standard sales order can be split into sub steps. This jointly carried out by some modules like INV, OM, Pricing, Shipping and AR. Lets take each sub steps with data flow conditions. These are based out of the flow which is available in 11.5.10.2!!!!!.

1. Order Entry:   

This is first stage when Order in enter in system. When the order is entered it basically create a record in order headers and Order Lines table.

                            

·         Oe_order_headers_all ( flow_status_code as entered)

·         Oe_oreder_lines_all ( flow_status_code as entered)( Order number is generated.

2. Order Booking:

                          

This is next stage, when Order which is entered in step1 is booked and flow status changed from entered to Booked. At this stage these table get affected.

·         Oe_order_headers_all (flow_status_code as booked, booked_flag updated).

·         Oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated).

·         wsh_new_deliverries ( status_code OP open).

·         Wsh_delivery_details ( released_status ‘R’ ready to release).

Note:

          Same time, Demand Interface program runs in back ground and insert into inventory tables mtl_demand.

   

3. Reservation:

This step is required for doing reservations SHCEDULE ORDER PROGRAM runs in the background and quantities are reserved .Once this program get successfully get completed, the mtl_reservations table get updated.

4. Pick Release :

Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.

Normally pick release SRS program runs in background. Once the program get completed these are the table get affected.

·         Oe_order_lines_all (flow_status_code ‘PICKED’)

·         wsh_delivery_details ( released_status ‘S’ ‘submitted for release’)

·         mtl_txn_request_headers

·         mtl_txn_request_lines ( move order tables , Here request is generated to move item from saleable  to staging sub inventory).

·         Mtl_material_transactions_temp ( link to above tables through move_order_header_id/line_id.  

5. Pick Confirm :

Items are transferred from saleble to staging Sub inventory

·         Mtl_material_transactions

·         Mtl_transaction_accounts

·         Wsh_delivery_details (released_status ‘Y’ ‘Released’).

·         Wsh_delivery_assignments.

6. Ship Confirm :

Here ship confirm Interface program runs in background. Data removed from wsh_new_deliverries.

·          Oe_order_line_all ( flow_status_code ‘Shipped’)

·         wsh_delivery_details ( released_status ‘C’ ‘Shipped’)

·         mtl_transaction_interface

·         mtl_material_transactions ( linked through Transaction source header id)

·         mtl_transaction-accounts

·         Data deleted frommtl_demand,mtl_reservations

·         Item deducted from mtl_onhand_quatnities.

7. Enter invoice :

This is also called Receivable interface, that mean Information moved to accounting area for invoicing details.

·         Invoicing workflow activity transfers shipped item information to Oracle Reveivables.

·         Ra_interface_lines_all (interface table into which the data is transferred fromorder management) T.

·         Then Auto Invoice Program Imports data from this

·         Table which get affected into this stage are receivables base table.

o    Ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines tables and trx_number is invoice number).

o    Ra_customer_trx_lines_all (line_number_1 and line_attribute_6 are linked to header_id ( or order_number) and line_id of the orders).

8. Complete Line :

                                    This is stage order line level table get updated with Flow status and Open flag.

       

·          Oe_order_lines_all (flow_status_code ‘Shipped’, open_flag’N’).

9. Close Order  :

This is last step of Order Processing. In this stage only oe_order_lines_all table get updated.

 These are the table get affected in this step.

·         Oe_order_lines_all (flow_status_code ‘Closed’, open_flag ‘N’).

 These are the typically data flow of a order to cash model for a standard order.

Understand “ Drop Shipment” in Order Management?

Order Management allows you to enter drop-shipment sales orders as well as standard sales orders.

Items means you can receive orders for items that you do not stock or for which you lack sufficient inventory, and have a supplier provide the items directly to your customer.

ORDER CYCLE DROP SHIPMENT

             These are the following activity takes place when you have drop shipment

·         Supplier

                                              

o    Warehouse Item

o    Ship Order

o    Shipment notification

·         Order Entry

o    Enter Customer

o    Enter Order

o    Demand Order (optional)

o    Cancel Order ( Optional)

o    Close Order

·          Purchasing

o    Create and send Purchase Order

o    Enter Shipment notification in system

·         Receivables

o    Create invoice

o    Collection of payments

o    Receipt

       Hence ,drop ship order items ship directly from a supplier to the customer of the order processing company. A purchase requisition then a purchase order, is generated to notify the supplier ships the order , it notifies purchasing to enter this information in the purchasing module.

What are the advantages of Drop Shipment Orders?

 These are the benefits:

     

·         No inventory is required

·         Reduced order fulfillment processing costs

·         Reduced flow times

·         Elimination of losses on non-sellable goods

·         Elimination of packing and shipping costs

·         Reduced inventory space requirements

·         Reduced shipping time to your customer

·         Allows you to offer a variety of products to your customers

How to understand the dataflow for Drop Shipment Orders?

To understand, here are the processes divided in sub process and the underline activity is highlighted here:

  Here are the Details as per Mark.

1.    Order Entry:

           Here the activity is entering process where oe_order_headers_all ( flow_status_coder as entered) oe_order_lines_all. The order is booked as DROP SHIP.

2.    Order Booking:

3.    The Purchase program passes information about eligible drop-ship order lines to Oracle Purcahsing. The interface table which gets populated is po_requistions_interface_all

4.    After Purchasing Release has completed successfully, run Requisition Import I Oracle Purcahsing to generate purchase requisitions for the processed order lines. The Requisition Import program reads the table po_requisitions_interface_all validates your data, dervies or defaults additional information and writes as error message for every validation that fails into the po__interface_errors table. The validated data is then inserted into the requisition base tables.

5.    po_requistion_headers_all, po_requisition_lines_all,po_requistion_distributions_all. Then use Auto create PO functionality to create purchase orders and then perform receipts against these purchase orders.

6.    After the good are successfully received invoices for vendors are created in accounts payables as in normal purchase orders.

7.    Invoices are generated for customers In Accounts receivables.

8.    oe_order_lines_all ( flow_status_code ‘ Shipped’ Open_flag “N” ).

9.    oe_order_lines_all ( flow_status_code ‘Closed’ Open_flag “N” ).

Very common thing, people get confuse why such type of naming convention oracle have used. Here is note for some of them.

·         _ALL : 

                  Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating units to see the data specific to that operating units only

·         _TL :

                 These are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support . For each item in the table without _TL there can be many rows in the _TL table ,but all with different values  in the LANGUAGE column.

·         _B :

              These are the BASE tables. They are very important and the data is stored in the table with all validations. It is supposed that these table will always contain the perfect format data. If anything happens to the BASE table data ,then it is a data corruption issue.

·         _F :

                  These are date Tracked Tables , which occur in HR and Payroll . For these there are TWO date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely .The Date intervals can not overlap. Many think they are Secured date guess someone from Oracle Confirms.

·         _V : Tables are the Views created on base tables.

·         _VL : are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(‘LANG’).

·         _S :  are sequences, used for finding new values for the primary key of a table .

·         _A   : are Audit Shadow Tables.

·         _AVN and _ACN : are Audit Shadow Views ( when data was changed, and with what values)