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,
deal_types_accmask dta
WHERE
1 = 1
AND AND t.valuedate = to_date('&DateOfTransactions-DD.MM.YYYYDateOfTransactionDDMMYYYY','DD.MM.YYYY')
AND ( t.accpointerSEC_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 (
(m.typedoc = 1 and m._amount = t._amount)
OR
(m.typedoc IN (2,22) AND m._amount =-t._amount)
)
AND m.accpointer = t.accpointer
AND m.status = 0
(
-- 1
AND
( For payment, Id = ... was found a document MCI ID = ... , condition of search: Account(lacc_our) is defined in transaction
(
(m.typedoc = 1 and m.laccpol = t.lacc_our)
OR
(m. OR
(typedoc IN (2,22) and laccplat = t.lacc_our)
)
-- 2 For 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= t.shttype
1 order by dta.accmask
AND
(
(m.bikplat = c.bicdom and m.
(
(bikplat = NVL(pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_bic),bic) and typedoc = 1)
OR
(m.bikpol = c.bicdom and m. 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(m.laccpol,dta.accmask) = 1
OR instr(m.laccplat,dta.accmask) = 1
)
)
-- 3 For payment, Id = ... was found a document MCI ID = ...,condition of search: INN is defined
OR (
)
dta.dealtype = shttype
-- 3
(для релиза 7.2) соответствие с типом платежа
OR (
-- 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 t.shttypedta.accmask
AND
(
(m.innplat = c.inn and m.
(
(innplat = pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_inn) and typedoc = 1)
OR
(m.innpol = c.inn and m. OR
(innpol = pkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_inn) and typedoc IN (2,22))
)
)
AND (
instr(m.laccpol,dta.accmask) = 1
OR instr(m.laccplat,dta.accmask) = 1
)
AND t.shttype and substr(trnid,1,2) IN ('FX', 'DP')
)
'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 )
-- 4= ...,condition of search: BIK or SWIFT is defined and laccPol is empty
OR (
m.innplat = c.innpkg_codes.f_code_by_rn_id_scheme(cid, pkg_codes.v_scheme_inn)
AND nvl(m.laccpol,'00000000000000000000') <> '00000000000000000000'
)
);
/ |