Антон

Прежде, чем развивать компетенции в какой-то конкретной технологии СУБД, надо сначала прокачаться в теории БД.
Что такое множества. Операции над множествами. Сущности (поля, кортежи, таблицы, индексы, последовательности, транзакции и т.д.). Связи таблиц (виды и типы связей). Доступ к данным и т.д.
А уже потом выбирать или не выбирать конкретную технологию.

А то бывает приходят люди с опытом PLSQL, вроде и код пишет и стандартные функции знает.
А про левое/правое соединение ни гугу.

Добавлено: про нормальные формы забыл еще написать

Вопрос не понятен от слова совсем.

Сразу скажу.
Несмотря на то, что условия пишутся одинаково, необходимо разделять условия связи двух таблиц и условия фильтрации/отбора. Логически условия отбора фильтруют итоговый массив строк, полученный после связывания всех таблиц. Технически фильтрация может выполняться раньше.
Т.о. условия связи в данной задаче надо оставить под инструкцией ON, а условия фильтрации вынести в инструкцию WHERE.

И да, (совет №1) не забывайте писать алиас таблицы перед именем поля, даже если в другой таблице такого поля нет. Ведь завтра оно может там появиться и код работать не будет. Научитесь (совет №2) придумывать короткие алиасы к таблица. А лучше систему получения коротких алиасов. Это пригодится в будущем, когда количество таблиц в системе и в запросе станет увеличиваться.

Возможно, я неправильно предположил суть вопроса, но должно получиться что-то похожее на

SELECT
	c.id
  , c.pagetitle
  , c.parent
  , tv.id
  , tv.tmplvarid
  , tv.contentid
  , tv.value 
FROM modx_site_content AS c 
RIGHT JOIN modx_site_tmplvar_contentvalues AS tv ON c.id = tv.contentid
WHERE c.parent = 22923
  AND ( tv.tmplvarid, tv.value ) IN ( ( 20, 4 ), ( 20, 6 ), ( 19, 10 ) )
ORDER BY c.id ASC

И последнее. Мне на 99% кажется, что исходя из имен таблиц, здесь должен быть не RIGHT, а LEFT JOIN.

Я вам рекомендую переделать этот код.
В наличие пренебрежение некоторыми нормами разработки.
1.

id in number

СУБД дает вам возможность создавать имена переменных длинной до 32 символов. Так пользуйтесь этим. Придумайте нормальное имя, например, id_certificate.

2.

currentSequence NUMBER(10,0);
BEGIN
currentSequence := 0;

Я верю, что вам платят не за строчки кода. Такое надо объединять в

currentSequence NUMBER(10,0) := 0 ;

3. Поле с именем ID это зло. Когда сущностей в системе станет к 100. Вы будете путаться в этих ID как ребенок в буквах.

4.

currentSequence := 0;
SELECT
max(vr.CERTIFICATE) INTO currentSequence
FROM
VAGON_REGISTR vr
WHERE
vr.ID = id;

На этот код должен был ругнуться WARNING'ом компилятор. Типа, значение присваивается, но не используется и снова присваивается в команде выбора.

5. Команда выхода из блока return должна быть одна. Для ветвления алгоритмов есть If & Case etc.

6. Про commit (или rollback) уже писали выше.

7. И да, Oracle не знает camelcase'а GetCertificateSeq. Лучше делать так Get_Certificate_Seq

Сергей, не понятна постановка задачи.
Будьте добры, то же самое, только другими словами.

Описание задачи недостаточное
Не понятен смысл таблиц. Где Primary? Где справочники и значения? и т.д.