Oracle eBS經驗分享

This job is doing backorder all open move order .(ps. it’s not work for WMS move order)

Below is what I do.

For you all reference.

/* Formatted on 2010/01/12 17:44 (Formatter Plus v4.8.8) */
DECLARE
   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;
   x_return_status   VARCHAR2 (100);

   CURSOR cur
   IS
      SELECT   toh.request_number move_order_num, tol.line_number,
               tol.line_id move_order_line_id, tol.inventory_item_id
          FROM mtl_txn_request_headers toh,
               mtl_txn_request_lines tol,
               mtl_material_transactions_temp mmtt
         WHERE toh.header_id = tol.header_id
           AND toh.organization_id = tol.organization_id
           AND tol.line_id = mmtt.move_order_line_id
           AND tol.organization_id = 93
      ORDER BY tol.line_id;
BEGIN
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = 'TINALEE';

   SELECT responsibility_id
     INTO l_resp_id
     FROM fnd_responsibility_tl
    WHERE LANGUAGE = 'US' AND responsibility_name = 'HKG INVENTORY SUPER USER';

   SELECT application_id
     INTO l_appl_id
     FROM fnd_application_tl
    WHERE LANGUAGE = 'US' AND application_name = 'Inventory';

/* Initialize return status*/
   x_return_status := wsh_util_core.g_ret_sts_success;
   -- DBMS_OUTPUT.put_line ('submit');
       /* Call this procedure to initialize applications parameters. To determine
    parameter values, refer to the Application Paramater Initialization section of
    this chapter. */
   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => l_resp_id,
                               resp_appl_id      => l_appl_id
                              );

   --DBMS_OUTPUT.put_line ('Calling INV_MO_BACKORDER_PVT to Backorder MO');
   FOR rec IN cur
   LOOP
      DBMS_OUTPUT.put_line (   rec.move_order_num
                            || '-'
                            || rec.line_number
                            || '==============================='
                           );
      inv_mo_backorder_pvt.backorder (p_line_id            => rec.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;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Program Fail ==' || SQLERRM);
END;