История страницы

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('&DateOfTransactionDDMMYYYY','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.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.shttype1 order by dta.accmask
             AND
                (
                    (m.bikplat = NVL(pkg_codes.f_code_by_rn_id_scheme(c.idcid, pkg_codes.v_scheme_bic),t.bic) and m.typedoc = 1)
                    OR
                    (m.bikpol = NVL(pkg_codes.f_code_by_rn_id_scheme(c.idcid, pkg_codes.v_scheme_bic),t.bic) and m.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 =  pkg_codes.f_code_by_rn_id_scheme(c.idcid, pkg_codes.v_scheme_inn) and m.typedoc = 1)
                    OR
                    (m.innpol =  pkg_codes.f_code_by_rn_id_scheme(c.idcid, pkg_codes.v_scheme_inn) and m.typedoc IN (2,22))
                )
            AND (
                instr(m.laccpol,dta.accmask) = 1
                OR instr(m.laccplat,dta.accmask) = 1
            )
            AND t.shttypeand 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 = ...,condition of search: )
BIK or SWIFT is defined and laccPol --is 4empty
         OR (
            m.innplat = pkg_codes.f_code_by_rn_id_scheme(c.idcid, pkg_codes.v_scheme_inn)
            AND nvl(m.laccpol,'00000000000000000000') <> '00000000000000000000'
        )
    );
/ 

Для уточнения какое свойство документа или платежа не квитуются необходимо закомментировать условие в запросе. При этом необходимо соблюдать условия для компиляции запроса

select * 
  from vw_protocol 
 where category = 'MCI' 
   and event_time >= td
   -- условия для поиска конкретного документа, по которому произошла квитовка
   and message like '%НомерДокументаМЦИ%' -- номер документа МЦИ
   and 1=1;
/