Script to backorder Move Order

Using API inv_mo_backorder_pvt.backorder to back order move order

DECLARE
  CURSOR c_order_det
  IS
    SELECT dd.delivery_detail_id,
      dd.source_code,
      dd.source_line_id,
      dd.org_id,
      dd.source_header_id,
      dd.source_header_number,
      dd.source_line_number,
      NVL (dl.ship_method_code, dd.ship_method_code) ship_method_code,
      dd.inventory_item_id,
      (SELECT concatenated_segments
      FROM mtl_system_items_kfv
      WHERE inventory_item_id = dd.inventory_item_id
      AND organization_id     = dd.organization_id
      ) ordered_item,
    NVL (wsn.quantity, dd.requested_quantity) requested_quantity,
    NVL (wsn.quantity, dd.shipped_quantity) shipped_quantity,
    dd.requested_quantity_uom,
    dd.src_requested_quantity_uom,
    dd.requested_quantity2,
    dd.shipped_quantity2,
    dd.requested_quantity_uom2,
    dd.src_requested_quantity_uom2,
    dd.ship_set_id,
    dd.revision,
    dd.lot_number,
    NVL (wsn.fm_serial_number, DECODE (dd.oe_interfaced_flag, \'Y\', dd.serial_number, NULL) ) serial_number,
    dd.released_status,
    wl2.meaning,
    dl.delivery_id,
    dl.NAME,
    dl.currency_code,
    dl.status_code delivery_status_code,
    DECODE (NVL (dl.status_code, \'-99\'), \'-99\', NULL, wl1.meaning ) delivery_status_meaning,
    dd.organization_id,
    dl.initial_pickup_date,
    dl.ultimate_dropoff_date,
    NVL (wsn.to_serial_number, dd.to_serial_number) to_serial_number,
    dd.move_order_line_id,
    (SELECT request_number
    FROM mtl_txn_request_headers
    WHERE header_id IN
      (SELECT header_id
      FROM mtl_txn_request_lines
      WHERE line_id = dd.move_order_line_id
      )
    ) Move_order,
    (SELECT Line_number
    FROM mtl_txn_request_lines
    WHERE line_id = dd.move_order_line_id
    ) Move_order_Line
  FROM wsh_lookups wl1,
    wsh_lookups wl2,
    wsh_new_deliveries dl,
    wsh_delivery_assignments_v da,
    wsh_delivery_details dd,
    wsh_locations wlf,
    wsh_locations wlt,
    wsh_serial_numbers wsn
  WHERE dd.delivery_detail_id            = da.delivery_detail_id
  AND da.delivery_id                     = dl.delivery_id(+)
  AND NVL (dl.status_code, \'OP\')         = wl1.lookup_code
  AND wl1.lookup_type                    = \'DELIVERY_STATUS\'
  AND ( ( wl2.lookup_code                = \'K\'
  AND dd.released_status                 = \'S\'
  AND dd.move_order_line_id             IS NULL)
  OR ( wl2.lookup_code                   = dd.released_status
  AND ( (dd.move_order_line_id          IS NOT NULL)
  OR (dd.released_status                <> \'S\'))
  AND (dd.replenishment_status          IS NULL))
  OR ( wl2.lookup_code                   = \'E\'
  AND dd.released_status                IN (\'R\', \'B\')
  AND dd.replenishment_status            = \'R\')
  OR ( wl2.lookup_code                   = \'F\'
  AND dd.released_status                IN (\'R\', \'B\')
  AND dd.replenishment_status            = \'C\'))
  AND wl2.lookup_type                    = \'PICK_STATUS\'
  AND dd.ship_from_location_id           = wlf.wsh_location_id(+)
  AND dd.ship_to_location_id             = wlt.wsh_location_id(+)
  AND dd.delivery_detail_id              = wsn.delivery_detail_id(+)
  AND NVL (dd.line_direction, \'O\')      IN (\'O\', \'IO\')
  AND NVL (dl.delivery_type, \'STANDARD\') = \'STANDARD\'
  AND dd.source_header_number            = \'65136\' --Sales Order Number
  AND dd.released_status                 = \'S\';
  l_return_status VARCHAR2 (100);
  l_msg_count     NUMBER;
  l_msg_data      VARCHAR2 (2000);
  l_msg_index     NUMBER;
  l_user_id       NUMBER;
  l_resp_id       NUMBER;
  l_appl_id       NUMBER;
BEGIN
  SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = \'MFG\';    --User
  
  SELECT responsibility_id,
    application_id
  INTO l_resp_id,
    l_appl_id
  FROM fnd_responsibility_vl
  WHERE responsibility_name = \'Manufacturing and Distribution Manager\'; --Responsibility
  

  fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

  FOR i IN c_order_det
  LOOP
    mo_global.set_policy_context (\'S\', i.org_id);
    mo_global.init (\'ONT\');
    DBMS_OUTPUT.put_line (\'Calling INV_MO_BACKORDER_PVT to Backorder MO\');
    DBMS_OUTPUT.put_line (\'===============================\');
    inv_mo_backorder_pvt.backorder ( p_line_id => i.move_order_line_id, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data );
    DBMS_OUTPUT.put_line (\'Return Status is=> \' || l_return_status);

    -- Check Return Status
    IF l_return_status = fnd_api.g_ret_sts_success THEN
      DBMS_OUTPUT.put_line (\'Successfully BackOrdered the Move Order Line\');
      COMMIT;
    ELSE
      DBMS_OUTPUT.put_line (\'Could not able to Back Order Line Due to Following Reasons\' );
      ROLLBACK;
      FOR j IN 1 .. l_msg_count
      LOOP
        fnd_msg_pub.get (p_msg_index => j, p_encoded => fnd_api.g_false, p_data => l_msg_data, p_msg_index_out => l_msg_index);
        DBMS_OUTPUT.put_line (\'Error Message is=> \' || l_msg_data);
      END LOOP;
    END IF;
  END LOOP;


END;

After back order,Move order line status is changed from \'Pre-Approved\' to \'Closed\',and Requested Quantity is cleared to zero.

Shipping Transaction Status is also changed to \'Backordered\'

转载于:http://blog.itpub.net/26687597/viewspace-1204349/