RCV API

  DECLARE
  ln_user_id      NUMBER;
  ln_po_header_id NUMBER := 3319024; --1713998;
  ln_vendor_id    NUMBER;
  lv_segment1     VARCHAR2(20);
  ln_org_id       NUMBER;
  ln_line_num     NUMBER;
  ln_parent_txn_id NUMBER;
  
  CURSOR po_line IS
  
    SELECT pl.item_id,
           pl.po_line_id,
           pl.line_num,
           --pll.quantity,
           pd.quantity_ordered quantity,
           pd.po_distribution_id,
           pl.unit_meas_lookup_code,
           mp.organization_code,
           pll.line_location_id,
           pll.closed_code,
           pll.quantity_received,
--          10  quantity_received,
           pll.cancel_flag,
           pll.shipment_num
           , pl.po_header_id
      FROM po_lines_all          pl,
           po_line_locations_all pll,
           po_distributions_all  pd,
           mtl_parameters        mp
     WHERE  -- quantity_received <pd.quantity_ordered -- 3317071 1713998
      pl.po_header_id = 3319024 --ln_po_header_id
       AND pl.po_line_id = pll.po_line_id and LINE_NUM=1
       AND pd.line_location_id = pll.line_location_id
       AND pd.po_line_id = pl.po_line_id
       AND pll.ship_to_organization_id = mp.organization_id;
       

       
BEGIN
 
ln_user_id := 1187; --144333;

  SELECT po_header_id,
         vendor_id,
         segment1,
         org_id
    INTO ln_po_header_id,
         ln_vendor_id,
         lv_segment1,
         ln_org_id
    FROM po_headers_all
   WHERE segment1 =:PO_NUMBER  --50540008772   55140031173  103134024630
     AND org_id =:P_ORG_ID; --3690  3732  122
    


  INSERT INTO rcv_headers_interface
    (header_interface_id,
     group_id,
     processing_status_code,
     receipt_source_code,
     transaction_type,
     last_update_date,
     last_updated_by,
     last_update_login,
     vendor_id,
     expected_receipt_date,
     validation_flag,
     org_id)
     
     
    SELECT rcv_headers_interface_s.nextval,
           rcv_interface_groups_s.nextval,
           'PENDING',
           'VENDOR',
           'NEW',
           sysdate,
           ln_user_id,
           0,
           ln_vendor_id,
           sysdate,
           'Y',
           ln_org_id
      FROM dual;
     
  FOR cur_po_line IN po_line
  LOOP
    IF cur_po_line.closed_code IN ('APPROVED', 'OPEN')
       AND cur_po_line.quantity_received < cur_po_line.quantity
       AND NVL(cur_po_line.cancel_flag,'N') = 'N'
    THEN
    
--    2763622
--    select * from rcv_transactions_interface where INTERFACE_TRANSACTION_ID=2763622;
    
    
      INSERT INTO rcv_transactions_interface
        (interface_transaction_id,
         group_id,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login,
         transaction_type,
         transaction_date,
         processing_status_code,
         processing_mode_code,
         transaction_status_code,
         po_header_id,
         po_line_id,
         item_id,
         quantity,
         unit_of_measure,
         po_line_location_id,
         po_distribution_id,
         auto_transact_code,
         receipt_source_code,
         to_organization_code,
         source_document_code,
         header_interface_id,
         validation_flag,
         org_id)
        SELECT rcv_transactions_interface_s.nextval,
               rcv_interface_groups_s.currval,
               sysdate,
               ln_user_id,
               sysdate,
               ln_user_id,
               0,
               'RECEIVE',
               SYSDATE,
               'PENDING',
               'BATCH',
               'PENDING',
               ln_po_header_id,
               cur_po_line.po_line_id,
               cur_po_line.item_id,
               cur_po_line.quantity,
               cur_po_line.unit_meas_lookup_code,
               cur_po_line.line_location_id,
               cur_po_line.po_distribution_id,
               'RECEIVE',
               'VENDOR',
               cur_po_line.organization_code,
               'PO',
               rcv_headers_interface_s.currval,
               'Y',
               ln_org_id
          FROM dual;
         
      ln_parent_txn_id := rcv_transactions_interface_s.currval;  
         
      INSERT INTO rcv_transactions_interface
        (
         parent_interface_txn_id,
         interface_transaction_id,
         group_id,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login,
         transaction_type,
         transaction_date,
         processing_status_code,
         processing_mode_code,
         transaction_status_code,
         po_header_id,
         po_line_id,
         item_id,
         quantity,
         unit_of_measure,
         po_line_location_id,
         po_distribution_id,
         auto_transact_code,
         receipt_source_code,
         to_organization_code,
         source_document_code,
         header_interface_id,
         validation_flag,
         org_id)
        SELECT ln_parent_txn_id,
               rcv_transactions_interface_s.nextval,
               rcv_interface_groups_s.currval,            
               sysdate,
               ln_user_id,
               sysdate,
               ln_user_id,
               0,
               'DELIVER',
               SYSDATE,
               'PENDING',
               'BATCH',
               'PENDING',
               ln_po_header_id,
               cur_po_line.po_line_id,
               cur_po_line.item_id,
               cur_po_line.quantity,
               cur_po_line.unit_meas_lookup_code,
               cur_po_line.line_location_id,
               cur_po_line.po_distribution_id,
               NULL,--'RECEIVE',
               'VENDOR',
               cur_po_line.organization_code,
               'PO',
               rcv_headers_interface_s.currval,
               'Y',
               ln_org_id
          FROM dual;         
      dbms_output.put_line('po line: ' || cur_po_line.line_num || ' shipment: ' || cur_po_line.shipment_num ||
                           ' has been inserted sucessfully inserted in the Interface tables.');
    ELSE
      dbms_output.put_line('po line ' || cur_po_line.line_num || ' is either closed, cancelled, received or have some errors');
    END IF;
  END LOOP;
  COMMIT;
END;

Isolated  Item delete form system:

-SET DEFINE OFF;
Insert into BOM_DELETE_ENTITIES
   (DELETE_ENTITY_SEQUENCE_ID, DELETE_GROUP_SEQUENCE_ID, DELETE_ENTITY_TYPE, BILL_SEQUENCE_ID, ROUTING_SEQUENCE_ID, 
    INVENTORY_ITEM_ID, ORGANIZATION_ID, ALTERNATE_DESIGNATOR, ITEM_DESCRIPTION, ITEM_CONCAT_SEGMENTS, 
    DELETE_STATUS_TYPE, DELETE_DATE, PRIOR_PROCESS_FLAG, PRIOR_COMMIT_FLAG, LAST_UPDATE_DATE, 
    LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, 
    PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE)
 Values
   (BOM_DELETE_ENTITIES_S.nextval, 455045 -- group id from BOM_DELETE_GROUPS
   , 1, NULL, NULL
   , 5051658 -- inventory item id
     ,102 -- Org id
     , NULL
     , 'COP PUSH BUTTON BLACK MIRROR 36MM DIA WITH CABLE -1' -- item name
     , '0500031025' -- item code
     , 1, null, 1, 1, TO_DATE('07/Dec/22 6:02:52 PM', 'DD/MON/YY HH:MI:SS AM'), 
    1187, TO_DATE('07/Dec/22 6:02:38 PM', 'DD/MON/YY HH:MI:SS AM'), 1187, null, null, 
    null, null, null);
    
     

OM Order Info of respective ORDER_NUMBER in R12

 SELECT ood.organization_code           org_code,
         ood.organization_name           org_name,
         wdd.DELIVERY_DETAIL_ID          DDID,
         ooh.HEADER_ID,
         ool.LINE_ID,
         ooh.ORDER_NUMBER                SO_No,
         ool.CUST_PO_NUMBER              DO_No,
         ORDERED_ITEM                    item_code,
         wdd.ITEM_DESCRIPTION            item_name,
         ool.ATTRIBUTE3                  OU,
         ool.LINE_NUMBER                 line_no,
         ool.ORDERED_QUANTITY            ord_qty,
         ool.FULFILLED_QUANTITY          full_qty,
         ool.SHIPPED_QUANTITY            ship_qty,
         wdd.REQUESTED_QUANTITY          req_qty,
         ooh.FLOW_STATUS_CODE            ooh_status,
         ool.FLOW_STATUS_CODE            ool_ststus,
         wdd.RELEASED_STATUS             wdd_status,
         DECODE (wdd.released_status,
                 'B', 'Backordered',
                 'C', 'Shipped',
                 'D', 'Cancelled',
                 'N', 'Not Ready for Release',
                 'R', 'Ready to Release',
                 'S', 'Release to Warehouse',
                 'X', 'Not Applicable',
                 'Y', 'Stagged/Pick Confirm',
                 wdd.released_status)    AS line_status
    FROM oe_order_headers_all        ooh,
         oe_order_lines_all          ool,
         wsh_delivery_details        wdd,
         org_organization_definitions ood
   WHERE     1 = 1            --and ooh.ORDER_NUMBER='10864543' --hid-35072684
         --and ool.ORDERED_ITEM='85140'
         AND ooh.ship_from_org_id = ool.ship_from_org_id
         AND ool.ORDER_SOURCE_ID = 9
         AND ooh.HEADER_ID = ool.HEADER_ID
         AND ool.HEADER_ID = wdd.source_header_id
         AND ool.line_ID = wdd.source_line_id
         AND ool.INVENTORY_ITEM_ID = wdd.INVENTORY_ITEM_ID
         AND wdd.RELEASED_STATUS <> 'C'
         AND ool.FLOW_STATUS_CODE = 'CLOSED'
         AND ool.ORDERED_QUANTITY < wdd.REQUESTED_QUANTITY
         AND ool.ship_from_org_id = ood.organization_id
         AND TRUNC (wdd.CREATION_DATE) BETWEEN '01-Aug-2025' AND '20-Nov-2025'
ORDER BY ood.organization_code, ooh.ORDER_NUMBER, wdd.RELEASED_STATUS ASC

 

Move Orders Allocate and Transact APIs in R12

1. Create a package using the below script.

Package Specification:

CREATE OR REPLACE PACKAGE APPS.xxcona_transact_move_orders AUTHID CURRENT_USER

AS

----------------------------------------------------------------------------------------------------

-- File name       : xxcona_transact_move_orders.prc

--

   -----------------------------------------------------------------------------------------------------

   -- WHO columns

   l_user_id          NUMBER        := -1;

   l_resp_id          NUMBER        := -1;

   l_application_id   NUMBER        := -1;

   l_user_name        VARCHAR2 (30) := 'CONACENT';

   l_resp_name        VARCHAR2 (30) := 'INVENTORY';

   PROCEDURE xxcona_alloc_move_order (

      p_move_order_line_id   IN       NUMBER,

      x_return_status        OUT      VARCHAR2,

      x_detailed_qty         OUT      NUMBER

   );

   PROCEDURE xxcona_process_move_order_line (

      p_move_order_line_id      IN       NUMBER,

      p_new_quantity_detailed   IN       NUMBER,

      x_return_status           OUT      VARCHAR2

   );

   PROCEDURE xxcona_transact_mo_line (

      p_header_id   IN   NUMBER,

      p_line_id     IN   NUMBER

   );

   PROCEDURE xxcona_main (

      p_move_order_no     IN   VARCHAR2,

      p_organization_id   IN   NUMBER

   );

END xxcona_transact_move_orders;

/

Package Body:

CREATE OR REPLACE PACKAGE BODY APPS.xxcona_transact_move_orders

AS

   PROCEDURE xxcona_alloc_move_order (

      p_move_order_line_id   IN       NUMBER,

      x_return_status        OUT      VARCHAR2,

      x_detailed_qty         OUT      NUMBER

   )

   IS

      -- Common Declarations

      l_api_version            NUMBER                                  := 1.0;

      l_init_msg_list          VARCHAR2 (2)                 := fnd_api.g_true;

      l_return_values          VARCHAR2 (2)                := fnd_api.g_false;

      l_commit                 VARCHAR2 (2)                := fnd_api.g_false;

      --x_return_status          VARCHAR2 (2);

      x_msg_count              NUMBER                                    := 0;

      x_msg_data               VARCHAR2 (255);

      -- WHO columns

      --l_user_id                NUMBER                                   := -1;

      --l_resp_id                NUMBER                                   := -1;

      --l_application_id         NUMBER                                   := -1;

      l_row_cnt                NUMBER                                    := 1;

       --l_user_name              VARCHAR2 (30)                    := 'CONACENT';

      --l_resp_name              VARCHAR2 (30)                   := 'INVENTORY';

       -- API specific declarations

      l_trohdr_rec             inv_move_order_pub.trohdr_rec_type;

      l_trohdr_val_rec         inv_move_order_pub.trohdr_val_rec_type;

      x_trohdr_rec             inv_move_order_pub.trohdr_rec_type;

      x_trohdr_val_rec         inv_move_order_pub.trohdr_val_rec_type;

      l_validation_flag        VARCHAR2 (2)

                                       := inv_move_order_pub.g_validation_yes;

      l_trolin_tbl             inv_move_order_pub.trolin_tbl_type;

      l_trolin_val_tbl         inv_move_order_pub.trolin_val_tbl_type;

      x_trolin_tbl             inv_move_order_pub.trolin_tbl_type;

      x_trolin_val_tbl         inv_move_order_pub.trolin_val_tbl_type;

      l_line_id                mtl_txn_request_lines.line_id%TYPE

                                                      := p_move_order_line_id;

      l_move_order_type        mtl_txn_request_headers.move_order_type%TYPE

                                                                         := 1;

      --x_detailed_qty           NUMBER                                    := 0;

      x_number_of_rows         NUMBER                                    := 0;

      x_revision               VARCHAR2 (3);

      x_locator_id             NUMBER                                    := 0;

      x_transfer_to_location   NUMBER                                    := 0;

      x_lot_number             VARCHAR2 (30);

      x_expiration_date        DATE;

      x_transaction_temp_id    NUMBER                                    := 0;

   BEGIN

      -- Get the user_id

      /*SELECT user_id

        INTO l_user_id

        FROM fnd_user

       WHERE user_name = l_user_name;

      -- Get the application_id and responsibility_id

      SELECT application_id, responsibility_id

        INTO l_application_id, l_resp_id

        FROM fnd_responsibility

       WHERE responsibility_key = l_resp_name;

      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

      -- MFG / Mfg Mgr / INV

      DBMS_OUTPUT.put_line (   'Initialized applications context: '

                            || l_user_id

                            || ' '

                            || l_resp_id

                            || ' '

                            || l_application_id

                           );*/

      -- Allocate each line of the Move Order

      inv_replenish_detail_pub.line_details_pub

                           (p_line_id                    => l_line_id,

                            x_number_of_rows             => x_number_of_rows,

                            x_detailed_qty               => x_detailed_qty,

                            x_return_status              => x_return_status,

                            x_msg_count                  => x_msg_count,

                            x_msg_data                   => x_msg_data,

                            x_revision                   => x_revision,

                            x_locator_id                 => x_locator_id,

                            x_transfer_to_location       => x_transfer_to_location,

                            x_lot_number                 => x_lot_number,

                            x_expiration_date            => x_expiration_date,

                            x_transaction_temp_id        => x_transaction_temp_id,

                            p_transaction_header_id      => NULL,

                            p_transaction_mode           => NULL,

                            p_move_order_type            => l_move_order_type,

                            p_serial_flag                => fnd_api.g_false,

                            p_plan_tasks                 => FALSE

                                                                 --FND_API.G_FALSE

      ,

                            p_auto_pick_confirm          => FALSE

                                                                 --FND_API.G_FALSE

      ,

                            p_commit                     => TRUE

                           --FND_API.G_FALSE

                           );

      DBMS_OUTPUT.put_line

                 ('==========================================================');

      DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

      IF (x_return_status <> fnd_api.g_ret_sts_success)

      THEN

         DBMS_OUTPUT.put_line ('Error Message :' || x_msg_data);

      END IF;

      IF (x_return_status = fnd_api.g_ret_sts_success)

      THEN

         COMMIT;

         DBMS_OUTPUT.put_line ('Detailed Qty: ' || x_detailed_qty);

         DBMS_OUTPUT.put_line ('Number of rows: ' || x_number_of_rows);

         DBMS_OUTPUT.put_line ('Trx temp ID: ' || x_transaction_temp_id);

      END IF;

      DBMS_OUTPUT.put_line

                 ('==========================================================');

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Exception Occured :');

         DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

         DBMS_OUTPUT.put_line

                   ('=======================================================');

   END xxcona_alloc_move_order;

   PROCEDURE xxcona_process_move_order_line (

      p_move_order_line_id      IN       NUMBER,

      p_new_quantity_detailed   IN       NUMBER,

      x_return_status           OUT      VARCHAR2

   )

   IS

      -- Main parameters -- Move order to update, and new quantity

      l_new_quantity_detailed   NUMBER             := p_new_quantity_detailed;

      l_mo_line_id              NUMBER                := p_move_order_line_id;

      -- User Information

      --l_user_id                 NUMBER                             := -1;

      --l_user_name               VARCHAR2 (20)                   := 'CONACENT';

      --l_resp_id                 NUMBER                             := -1;

      --l_resp_name               VARCHAR2 (40)                  := 'INVENTORY';

      --l_application_id          NUMBER                             := -1;

      l_rowcnt                  NUMBER                             := 1;

      -- Errors

      --x_return_status           VARCHAR2 (10);

      x_msg_count               NUMBER;

      x_msg_data                VARCHAR2 (255);

      x_message_list            error_handler.error_tbl_type;

      -- Move order variables

      l_trolin_tbl              inv_move_order_pub.trolin_tbl_type;

      l_trolin_old_tbl          inv_move_order_pub.trolin_tbl_type;

      x_trolin_tbl              inv_move_order_pub.trolin_tbl_type;

      l_mo_line_rec             inv_move_order_pub.trolin_rec_type;

   BEGIN

   

      l_mo_line_rec := inv_trolin_util.query_row (p_line_id => l_mo_line_id);

      l_trolin_tbl (1) := l_mo_line_rec;

      l_trolin_old_tbl (1) := l_mo_line_rec;

      l_trolin_tbl (1).operation := 'UPDATE';    -- INV_GLOBALS.G_OPR_UPDATE;

      l_trolin_tbl (1).quantity_detailed := l_new_quantity_detailed;

      inv_move_order_pub.process_move_order_line

                                       (p_api_version_number      => 1.0,

                                        p_init_msg_list           => fnd_api.g_true,

                                        p_return_values           => fnd_api.g_false

                                                                                    -- Convert ids to values

      ,

                                        p_commit                  => fnd_api.g_false

                                                                                    --FND_API.G_FALSE

      ,

                                        x_return_status           => x_return_status,

                                        x_msg_count               => x_msg_count,

                                        x_msg_data                => x_msg_data,

                                        p_trolin_tbl              => l_trolin_tbl,

                                        p_trolin_old_tbl          => l_trolin_old_tbl,

                                        x_trolin_tbl              => x_trolin_tbl

                                       );

      DBMS_OUTPUT.put_line ('=========================================');

      DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

      IF (x_return_status <> fnd_api.g_ret_sts_success)

      THEN

         DBMS_OUTPUT.put_line ('x_msg_data :' || x_msg_data);

         DBMS_OUTPUT.put_line ('Error Messages :');

         error_handler.get_message_list (x_message_list => x_message_list);

         FOR i IN 1 .. x_message_list.COUNT

         LOOP

            DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);

         END LOOP;

      ELSE

         COMMIT;

      END IF;

      DBMS_OUTPUT.put_line ('=========================================');

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Exception Occured :');

         DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

         DBMS_OUTPUT.put_line ('========================================');

   END xxcona_process_move_order_line;

   PROCEDURE xxcona_transact_mo_line (

      p_header_id   IN   NUMBER,

      p_line_id     IN   NUMBER

   )

   IS

      -- Common Declarations

      l_api_version        NUMBER                                  := 1.0;

      l_init_msg_list      VARCHAR2 (2)                     := fnd_api.g_true;

      l_commit             VARCHAR2 (2)                    := fnd_api.g_false;

      x_return_status      VARCHAR2 (2);

      x_msg_count          NUMBER                                  := 0;

      x_msg_data           VARCHAR2 (255);

      -- API specific declarations

      l_move_order_type    NUMBER                                  := 1;

      l_transaction_mode   NUMBER                                  := 1;

      l_trolin_tbl         inv_move_order_pub.trolin_tbl_type;

      l_mold_tbl           inv_mo_line_detail_util.g_mmtt_tbl_type;

      x_mmtt_tbl           inv_mo_line_detail_util.g_mmtt_tbl_type;

      x_trolin_tbl         inv_move_order_pub.trolin_tbl_type;

      l_transaction_date   DATE                                    := SYSDATE;

      -- WHO columns

      --l_user_id            NUMBER                                  := -1;

      --l_resp_id            NUMBER                                  := -1;

      --l_application_id     NUMBER                                  := -1;

      l_row_cnt            NUMBER                                  := 1;

   --l_user_name          VARCHAR2 (30)                        := 'CONACENT';

   --l_resp_name          VARCHAR2 (30)                       := 'INVENTORY';

   BEGIN

      -- Get the user_id

      /*SELECT user_id

        INTO l_user_id

        FROM fnd_user

       WHERE user_name = l_user_name;

      -- Get the application_id and responsibility_id

      SELECT application_id, responsibility_id

        INTO l_application_id, l_resp_id

        FROM fnd_responsibility

       WHERE responsibility_key = l_resp_name;

      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

      DBMS_OUTPUT.put_line (   'Initialized applications context: '

                            || l_user_id

                            || ' '

                            || l_resp_id

                            || ' '

                            || l_application_id

                           );*/

      l_trolin_tbl (1).line_id := p_line_id;

      l_trolin_tbl (1).header_id := p_header_id;                     -- Added

      -- call API to create move order header

      DBMS_OUTPUT.put_line

                   ('=======================================================');

      DBMS_OUTPUT.put_line

                   ('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API');

      inv_pick_wave_pick_confirm_pub.pick_confirm

                                   (p_api_version_number      => l_api_version,

                                    p_init_msg_list           => l_init_msg_list,

                                    p_commit                  => l_commit,

                                    x_return_status           => x_return_status,

                                    x_msg_count               => x_msg_count,

                                    x_msg_data                => x_msg_data,

                                    p_move_order_type         => l_move_order_type,

                                    p_transaction_mode        => l_transaction_mode,

                                    p_trolin_tbl              => l_trolin_tbl,

                                    p_mold_tbl                => l_mold_tbl,

                                    x_mmtt_tbl                => x_mmtt_tbl,

                                    x_trolin_tbl              => x_trolin_tbl,

                                    p_transaction_date        => l_transaction_date

                                   );

      DBMS_OUTPUT.put_line

                    ('=======================================================');

      DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

      IF (x_return_status <> fnd_api.g_ret_sts_success)

      THEN

         DBMS_OUTPUT.put_line ('Error Message :' || x_msg_data);

      ELSE

         COMMIT;

      END IF;

      DBMS_OUTPUT.put_line

                    ('=======================================================');

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Exception Occured :');

         DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

         DBMS_OUTPUT.put_line

                   ('=======================================================');

   END xxcona_transact_mo_line;

   PROCEDURE xxcona_main (

      p_move_order_no     IN   VARCHAR2,

      p_organization_id   IN   NUMBER

   )

   IS

      x_return_status   VARCHAR2 (2);

      x_detailed_qty    NUMBER;

      CURSOR move_order_cur

      IS

         SELECT mtrh.request_number, mtrh.organization_id, mtrh.header_id,

                mtrl.line_id, mtrh.move_order_type, mtrl.quantity,

                mtrl.quantity_delivered,

                (mtrl.quantity - mtrl.quantity_delivered) unallocated_qty

           FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl

          WHERE mtrh.organization_id = mtrl.organization_id

            AND mtrh.header_id = mtrl.header_id

            AND mtrh.request_number = p_move_order_no

            AND mtrh.organization_id = p_organization_id;

   BEGIN

      BEGIN

         -- Get the user_id

         SELECT user_id

           INTO l_user_id

           FROM fnd_user

          WHERE user_name = l_user_name;

         -- Get the application_id and responsibility_id

         SELECT application_id, responsibility_id

           INTO l_application_id, l_resp_id

           FROM fnd_responsibility

          WHERE responsibility_key = l_resp_name;

         fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

         DBMS_OUTPUT.put_line (   'Initialized applications context: '

                               || l_user_id

                               || ' '

                               || l_resp_id

                               || ' '

                               || l_application_id

                              );

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line

               ('Exception Occured : Not able to Initialize applications context'

               );

            DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

            DBMS_OUTPUT.put_line

                    ('=======================================================');

      END;

      BEGIN

         FOR cur_rec IN move_order_cur

         LOOP

            xxcona_alloc_move_order (cur_rec.line_id,

                                     x_return_status,

                                     x_detailed_qty

                                    );

            IF     x_return_status = fnd_api.g_ret_sts_success

               AND x_detailed_qty > 0

            THEN

               x_return_status := NULL;                      --- Reset Status

               xxcona_process_move_order_line (cur_rec.line_id,

                                               x_detailed_qty,

                                               x_return_status

                                              );

               IF x_return_status = fnd_api.g_ret_sts_success

               THEN

                  xxcona_transact_mo_line (cur_rec.header_id,

                                           cur_rec.line_id);

               END IF;

            ELSE

               DBMS_OUTPUT.put_line

                  ('Exception Occured : Unable to allocate requested quantity.'

                  );

            END IF;

         END LOOP;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line

               ('Exception Occured : Unable to process the request --- Transact Move Orders'

               );

            DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);

            DBMS_OUTPUT.put_line

                    ('=======================================================');

      END;

   END xxcona_main;

END xxcona_transact_move_orders;

/

2.Syntax to call the API.

BEGIN

   xxcona_transact_move_orders.xxcona_main (p_move_order_no        => 't21',

                                            p_organization_id      => 101

                                           );

END;