https://pastein.ru/t/bMM
Загрузка данных
WITH
cliInv AS (
SELECT
r.c_CHECK_OBJ cliRef
,r.c_CHECK_DATE dtCert
,r.c_CHECK_VAL rezCert
,f.c_DESCRIPTION rezCertDescr
,f.c_CODE rezCode
,r.c_IS_LAST rezIsLast
,first_value(r.c_CHECK_DATE) OVER (PARTITION BY r.c_CHECK_OBJ ORDER BY r.c_CHECK_DATE desc) dtCertLast
,first_value(r.c_CHECK_VAL) OVER (PARTITION BY r.c_CHECK_OBJ ORDER BY r.c_CHECK_DATE desc) rezCertLast
,first_value(f.c_CODE) OVER (PARTITION BY r.c_CHECK_OBJ ORDER BY r.c_CHECK_DATE desc) rezCodeLast
,first_value(f.c_DESCRIPTION ) OVER (PARTITION BY r.c_CHECK_OBJ ORDER BY r.c_CHECK_DATE desc) rezCertDescrLast
FROM z#CL_CHECK_RESULT r
JOIN z#P207_TERROR_FLAG f
ON f.id = r.c_CHECK_VAL
WHERE r.c_CHECK_DATE >= to_date('01.04.2026','dd.mm.yyyy')
AND r.c_CHECK_DATE < trunc(sysdate)+1 -- to_date('01.07.2026','dd.mm.yyyy')
AND r.c_CHECK_TYPE =11580572
-- AND c_CHECK_VAL IN (11580578, 11580579)
-- AND r.c_CHECK_OBJ = 949400978116 -- убрать
ORDER BY r.c_CHECK_OBJ, r.c_CHECK_DATE
)
,cliAreal AS (
SELECT DISTINCT
cliRef cliRef
,c.c_FAMILY_CL||' '||c.c_NAME_CL||' '||c.c_SNAME_CL cliName
,clie.c_INN cliInn
,c.c_DOC#NUM||' '||c.c_DOC#SER cliPasp
,c.c_DOC#DATE_END cliPaspEnd
,c.c_DOC#VALID_BEFORE cliPaspValid
FROM cliInv
JOIN z#CL_PRIV c
ON cliInv.cliRef = c.id
JOIN z#CLIENT clie
ON clie.id = c.id
WHERE rezCert IN (11580578, 11580579) -- найдено совпадение со списком недействиетльности
)
,rko AS (
SELECT
rk.c_CLIENT cliRef
,rk.c_LIMITS_PAY cliLimPay
,rk.c_ACCOUNT accId
,acc.c_main_v_id accNum
,ac.c_DATE_OP accDtBeg
,ac.c_DATE_CLOSE accDtEnd
,d.id docId
,d.c_DOCUMENT_NUM docNum
,d.c_ASTR_DATE_PROV docProv
,doc.c_SUM_NT docSumNt
,doc.c_NAZN docNazn
,acc_kt.c_main_v_id accKt
,cla.cliName
,cla.cliInn
,cla.cliPasp
,cla.cliPaspEnd
,cla.cliPaspValid
FROM z#RKO rk
JOIN cliAreal cla
ON cla.cliRef = rk.c_CLIENT
JOIN z#AC_FIN acc -- нужно только те, у кого есть РКО с 40817
ON acc.id = rk.c_ACCOUNT
AND acc.c_main_v_id LIKE '40817%'
JOIN z#ACCOUNT ac
ON ac.id = acc.id
LEFT JOIN z#RECORDS rec
ON rec.collection_id = acc.c_ARC_MOVE
AND nvl(rec.c_DT, 0) = 1 -- 40817 по Дт
AND rec.c_DATE >= to_date('01.04.2026','dd.mm.yyyy')
AND rec.c_DATE < to_date('01.07.2026','dd.mm.yyyy')
LEFT JOIN z#MAIN_DOCUM doc
ON doc.id = rec.c_DOC
AND doc.STATE_ID = 'PROV'
LEFT JOIN z#DOCUMENT d
ON d.id = doc.id
LEFT JOIN z#AC_FIN acc_kt
ON acc_kt.id = rec.c_ACC_CORR
ORDER BY rk.c_CLIENT, d.c_ASTR_DATE_PROV
)
,itog AS (
SELECT distinct
rk.cliRef
,rk.cliName
,rk.cliInn
,rk.cliPasp
,rk.cliPaspEnd
,rk.cliPaspValid
,first_value(cl.dtCert) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY cl.dtCert desc) dtCert
,first_value(cl.rezCert) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY cl.dtCert desc) rezCert
,first_value(cl.rezCertDescr) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY cl.dtCert desc) rezCertDescr
,first_value(cl.rezCode) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY cl.dtCert desc) rezCode
,first_value(cl.rezIsLast) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY cl.dtCert desc) rezIsLast
,rk.accNum
,rk.accDtBeg
,rk.accDtEnd
,first_value(clLim.c_DATE_BEG) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY clLim.c_DATE_BEG desc) limBeg
,first_value(CASE WHEN nvl(clLim.c_VALID_LIMIT,0)=1 -- если ограничение действует в дату закрытия
THEN clLim.c_DATE_END
ELSE clLim.c_DATE_END-1
END) OVER (PARTITION BY rk.cliRef, rk.docId ORDER BY clLim.c_DATE_BEG desc) limEnd
,rk.docId
,rk.docNum
,rk.docProv
,rk.docSumNt
,rk.docNazn
,rk.accKt
,cl.dtCertLast
,cl.rezCertLast
,cl.rezCodeLast
,cl.rezCertDescrLast
,clLim.c_ACCOUNT
,rk.accId
,rk.cliLimPay
FROM rko rk
JOIN cliInv cl
ON cl.cliRef = rk.cliRef
AND cl.dtCert <= rk.docProv
LEFT JOIN z#RKO_LIMIT_PAY clLim
ON clLim.collection_id = rk.cliLimPay
AND clLim.c_KIND_LIMIT = 6257636 -- STOP_DT
-- AND clLim.c_ACCOUNT = rk.accId
AND clLim.c_DATE_BEG <= trunc(rk.docProv)
ORDER BY rk.cliRef, rk.docProv
)
,itogAll AS (
SELECT distinct
i.cliName AS "ФИО"
,i.cliInn AS "ИНН"
,i.cliPasp AS "Серия номер паспорта"
,to_char(i.cliPaspEnd,'dd.mm.yyyy') AS "Дата окончания действия паспорта"
,to_char(i.cliPaspValid,'dd.mm.yyyy') AS "Дата действительности паспорта"
,to_char(i.dtCert, 'dd.mm.yyyy hh24:mi:ss') AS "Дата проверки паспорта"
,i.rezCode AS "Результат проверки"
,i.accNum AS "Номер расчетного счета"
,to_char(i.accDtBeg,'dd.mm.yyyy') AS "Дата открытия счета"
,to_char(i.accDtEnd,'dd.mm.yyyy') AS "Дата закрытия счета"
,to_char(CASE WHEN nvl(i.limEnd, to_date('01.01.2059','dd.mm.yyyy')) < trunc(i.docProv) THEN NULL ELSE i.limBeg END,'dd.mm.yyyy') AS "Дата установки STOP_DT"
,to_char(CASE WHEN nvl(i.limEnd, to_date('01.01.2059','dd.mm.yyyy')) < trunc(i.docProv) THEN NULL ELSE i.limEnd END,'dd.mm.yyyy') AS "Дата снятия STOP_DT"
,to_char(i.docProv, 'dd.mm.yyyy hh24:mi:ss') AS "Дата операции "
--
,to_char(first_value(clLimNext.c_DATE_BEG) OVER (PARTITION BY i.cliRef, i.rezCert ORDER BY clLimNext.c_DATE_BEG asc),'dd.mm.yyyy') AS "Ближайшая к проверке паспорта дата установки STOP_DT"
,to_char(first_value(clLimNext.c_DATE_END) OVER (PARTITION BY i.cliRef, i.rezCert ORDER BY clLimNext.c_DATE_BEG asc),'dd.mm.yyyy') AS "Ближайшая к проверке паспорта дата снятия STOP_DT"
,i.docNum AS "Номер документа"
,i.docSumNt AS "Сумма документа (в нац. покрытии)"
,i.accKt AS "Счет (по кредиту)"
,i.docNazn AS "Назначение платежа"
,to_char(i.dtCertLast, 'dd.mm.yyyy hh24:mi:ss') AS "Дата последней проверки паспорта"
,i.rezCodeLast AS "Результат последней проверки"
FROM itog i
LEFT JOIN z#RKO_LIMIT_PAY clLimNext
ON clLimNext.collection_id = i.cliLimPay
AND clLimNext.c_KIND_LIMIT = 6257636 -- STOP_DT
-- AND clLim.c_ACCOUNT = rk.accId
AND clLimNext.c_DATE_BEG >= trunc(i.dtCert)
WHERE i.rezCert IN (11580578, 11580579)
)
SELECT * FROM itogAll
