處理物料搬運單API

DECLARE

BEGIN
      -- 向mtl_material_transactions_temp表中插值
      --事務處理表先向mtl_material_transactions_temp表中插值然後再匯入mmt表進行事務處理
      dbms_output.put_line(p_transaction_id);
      inv_ppengine_pvt.create_suggestions(p_api_version         => 1.0,
                                          p_init_msg_list       => fnd_api.g_false,
                                          p_commit              => fnd_api.g_true,
                                          p_validation_level    => fnd_api.g_valid_level_full,
                                          x_return_status       => l_return_status,
                                          x_msg_count           => l_msg_count,
                                          x_msg_data            => l_msg_data,
                                          p_transaction_temp_id => p_transaction_id, --mtl_txn_request_lines.line_id物料搬運單行ID                                       
                                          p_reservations        => l_mtl_reservation,
                                          p_suggest_serial      => 'F',
                                          p_plan_tasks          => NULL);
      
      --獲得temp表頭id資料 
      SELECT mmtt.transaction_header_id,
             mmtt.organization_id,
             mmtt.inventory_item_id,
             mmtt.transaction_uom
        INTO l_transaction_header_id,
             l_organization_id,
             l_inventory_item_id,
             l_transaction_uom
        FROM mtl_material_transactions_temp mmtt
       WHERE mmtt.trx_source_line_id = p_transaction_id;
   
      --獲取Primary Quantity
      SELECT msi.primary_uom_code
        INTO l_primary_uom
        FROM mtl_system_items_b msi
       WHERE inventory_item_id = l_inventory_item_id
         AND organization_id = l_organization_id;
   
      l_primary_quantity := inv_convert.inv_um_convert(l_inventory_item_id,
                                                       6,
                                                       abs(p_quantity),
                                                       l_transaction_uom,
                                                       l_primary_uom,
                                                       NULL,
                                                       NULL);

      --更新事務處理臨時表狀態為1
      /*Indicates if it has to be processed or just saved.
      NULL or 1 is default behavior.
      2 is save only.
      3 is ready to process*/
      UPDATE mtl_material_transactions_temp mmtt
         SET mmtt.transaction_status   = 1,
             mmtt.transaction_quantity = p_quantity,
             mmtt.primary_quantity     = l_primary_quantity,  
             mmtt.transaction_date     = SYSDATE
       WHERE mmtt.trx_source_line_id = p_transaction_id;

   
      --將temp表資料處理至mmt表
      l_return_status := inv_lpn_trx_pub.process_lpn_trx(p_trx_hdr_id         => l_transaction_header_id,
                                                         p_commit             => 'T',
                                                         x_proc_msg           => l_msg_data,
                                                         p_proc_mode          => NULL,
                                                         p_process_trx        => 'T',
                                                         p_atomic             => 'T',
                                                         p_business_flow_code => NULL);
      dbms_output.put_line('搬運單處理請求結果: ' || l_return_status);

         EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Exception : ' || SQLERRM);
    END;

END;