Moderation State Process Plugin

ModerationState.php

<?php
namespace Drupal\my_module\Plugin\migrate\process;

use \Drupal\Core\Database\Database;
use Drupal\migrate\MigrateExecutableInterface;
use Drupal\migrate\ProcessPluginBase;
use Drupal\migrate\Row;

/**
 * Custom process plugin to set a proper moderation state based on what's current on the site
 *
 * @MigrateProcessPlugin(
 *   id = "moderation_state"
 * )
 *
 * Example:
 * moderation_state:
    plugin: moderation_state
    source: status
 */
class ModerationState extends ProcessPluginBase {
  /**
   * {@inheritdoc}
   */
  public function transform($value, MigrateExecutableInterface $migrate_executable, Row $row, $destination_property) {
    $publishedStatus = $row->getSourceProperty('status');
    $nid = $row->getSourceProperty('nid');
    $vid = $row->getSourceProperty('vid');
    $state = $this->query($nid, $vid);

    // https://www.drupal.org/project/drupal/issues/3164497
    // If there's no state available use the publish status to set moderation state.
    if ($publishedStatus === '1' && !$state) {
      $state = 'published';
    } elseif ($publishedStatus === '0' && !$state) {
      $state = 'draft';
    }

    return $state;
  }

  public function query($nid, $vid) {
    $return = null;
    Database::setActiveConnection('migrate');
    $migrateDB = \Drupal\Core\Database\Database::getConnection();
    $query = $migrateDB->select('workbench_moderation_node_history', 'w')
      ->fields('w', ['state', 'published', 'is_current'])
      ->condition('w.nid', $nid)
      ->condition('w.vid', $vid)
      ->orderBy('w.stamp', 'DESC')
      ->range(0, 1);
    $data = $query->execute()->fetchAssoc();

    Database::setActiveConnection();

    if (!empty($data)) {
      $return = $data['state'];

      // In case the final state is published but the revision is not the
      // published one, set the state to draft.
      if ($data['state'] == 'published' && $data['published'] == '0') {
        $return = 'draft';
      }
      
    }

    return $return;
  }

}