select *
from (
SELECT
/*+ INDEX (MCI_REISES IX_MCI_REISES_DW_TYPE)*/
c.id cid,
t.id,
t.amount t_amount,
t.lacc_our AS lacc_our,
t.shttype,
t.rel_kvit,
NVL(pkg_codes.f_code_by_rn_id_scheme(c.id, pkg_codes.v_scheme_bic),t.bic) as bic,--c.bicdom,
pkg_codes.f_code_by_rn_id_scheme(c.id, pkg_codes.v_scheme_inn) inn,--c.inn,
null, --c.bicint,
'<-TRNS|MCI-> ' as mci,
--
m.typedoc,
m.laccpol,
m.laccplat,
m.bikpol,
m.bikplat,
m.innpol,
m.innplat,
m.swiftpol,
m.swiftplat,
m.amount m_amount
,t.trnid trnid
FROM
t_transactions t,
customer c,
mci_reises m
WHERE
1 = 1
AND AND t.valuedate = to_date('&DateOfTransactionDDMMYYYY','DD.MM.YYYY')
AND ( t.SEC_PLACEACCOUNT_ID = '&AccountId')
AND t.parent_id IS NULL
--and t.amount > 0
--and t.rel_kvit is null
AND c.id = t.cid
AND t.bal_id = pkg_balance.pBalType_Main --bitand(t.balancemask,3) > 0
-- Общие условия
and trunc(m.datevvoda) = trunc(t.valuedate)
AND m.accpointer = t.SEC_PLACEACCOUNT_ID
AND m.status = 0) jmt
,deal_types_accmask dta
WHERE
1=1
AND (
(typedoc = 1 and m_amount = t_amount)
OR
(typedoc IN (2,22) AND m_amount =-t_amount)
)
AND
(
-- 1 For payment, Id = ... was found a document MCI ID = ... , condition of search: Account(lacc_our) is defined in transaction
(
(typedoc = 1 and laccpol = lacc_our)
OR
(typedoc IN (2,22) and laccplat = lacc_our)
)
-- 2 ForFor payment, Id = ... was found a document MCI ID = ... ,condition of search: BIK or SWIFT is defined
OR (
dta.dealtype = shttype
-- (для релиза 7.2) соответствие с типом платежа
-- dta.dealtype = trnid (расскоментировать для 7.2)
-- select DISTINCT tt.type_name,tt.trnid, dta.accmask from resman.deal_types_accmask dta join resman.transaction_types tt on tt.trnid = dta.dealtype where 1=1 order by dta.accmask
AND
(
(bikplat = NVL(pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_bic),bic) and typedoc = 1)
OR
(bikpol = NVL(pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_bic),bic) and typedoc IN (2,22))
)
AND (
instr(laccpol,dta.accmask) = 1
OR instr(laccplat,dta.accmask) = 1
)
)
-- 3 For payment, Id = ... was found a document MCI ID = ...,condition of search: INN is defined
OR (
dta.dealtype = shttype
-- (для релиза 7.2) соответствие с типом платежа
-- dta.dealtype = trnid (расскоментировать для 7.2)
-- select DISTINCT tt.type_name,tt.trnid, dta.accmask from resman.deal_types_accmask dta join resman.transaction_types tt on tt.trnid = dta.dealtype where 1=1 order by dta.accmask
AND
(
(innplat = pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_inn) and typedoc = 1)
OR
(innpol = pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_inn) and typedoc IN (2,22))
)
AND (
instr(laccpol,dta.accmask) = 1
OR instr(laccplat,dta.accmask) = 1
)
AND shttype IN (
and substr(trnid,1,2) IN ('FX',
'DP'
)
)
-- 4 For payment, Id = ... was found a document MCI ID = ... ,condition of search: INN is defined and laccPol is empty
-- 4 For payment, Id = ... was found a document MCI ID = ...,condition of search: BIK or SWIFT is defined and laccPol is empty
OR (
innplat = pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_inn)
AND nvl(laccpol,'00000000000000000000') <> '00000000000000000000'
)
);
|