MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_22: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_22');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: Create XML file. Part 3 of 4.
*/
  tx:=fx||'35';
  if s.pg(sn)='35' then xt_c(tx);
    select xt into x from me where id=g||'a31'; m0; -- a35 is similar
    x:=replace(x, s.BH||'tTRAF10_31', a35);
    x:=replace(x, 'FIM0079', 'FIM0103');
    s.uei(x); p('35a'); end if;
  if s.pg(sn)='35a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('35b'); end if;
  if s.pg(sn)='35b' then s.ugs(tx); p('36'); end if;
  tx:=fx||'36';
  if s.pg(sn)='36' then xt_c(tx);
    select xt into x from me where id=g||'a32'; m0; -- a36 is similar
    x:=replace(x, s.BH||'tTRAF10_32', a36);
    x:=replace(x, 'FIM0080', 'FIM0104');
    x:=replace(x, 'FIM0081', 'FIM0105');
    x:=replace(x, 'FIM0082', 'FIM0106');
    x:=replace(x, 'FIM0083', 'FIM0107');
    x:=replace(x, 'FIM0084', 'FIM0108');
    s.uei(x); p('36a'); end if;
  if s.pg(sn)='36a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('36b'); end if;
  if s.pg(sn)='36b' then s.ugs(tx); p('41'); end if;
  tx:=fx||'41';
  if s.pg(sn)='41' then xt_c(tx);
    select xt into x from me where id=g||'a41'; m0;
    x:=replace(x, s.BH||'tTRAF10_41', a41);
    s.uei(x); p('41a'); end if;
  if s.pg(sn)='41a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('41b'); end if;
  if s.pg(sn)='41b' then s.ugs(tx); p('42'); end if;
  tx:=fx||'42';
  if s.pg(sn)='42' then xt_c(tx);
    select xt into x from me where id=g||'a42'; m0;
    x:=replace(x, s.BH||'tTRAF10_42', a42);
    s.uei(x); p('42a'); end if;
  if s.pg(sn)='42a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('42b'); end if;
  if s.pg(sn)='42b' then s.ugs(tx); p('D0'); end if;
  tx:=fx||'DX';
  if s.pg(sn)='D0' then xt_c(tx);
    select xt into x from me where id=g||'New-1of2'; m0; e:=x;
    select xt into x from me where id=g||'New-2of2';
    x:=replace(x, s.BH||'tfx', fx); e:=e||x;
    e:=replace(e, s.BH||'tTRAF10_11', a11);
    select replace(xt, s.BH||'01'
      , (select xt from me where id=g||'Indx')
      )
    into x from me where id=g||'SwpIn';
    e:=replace(e, s.BH||'01', x); -- SwpIn
    x:=replace(x, 'FIM0073', 'FIM0085');
    x:=replace(x, 'FIM0074', 'FIM0086');
    x:=replace(x, 'FIM0075', 'FIM0087');
    x:=replace(x, 'FIM0076', 'FIM0088');
    x:=replace(x, 'FIM0077', 'FIM0089');
    x:=replace(x, 'FIM0078', 'FIM0090');
    x:=replace(x, 'a31.x', 'a33.x');
    x:=replace(x, 'a32.x', 'a34.x');
    e:=replace(e, s.BH||'02', x); -- SwpOut
    x:=replace(x, 'FIM0085', 'FIM0097');
    x:=replace(x, 'FIM0086', 'FIM0098');
    x:=replace(x, 'FIM0087', 'FIM0099');
    x:=replace(x, 'FIM0088', 'FIM0100');
    x:=replace(x, 'FIM0089', 'FIM0101');
    x:=replace(x, 'FIM0090', 'FIM0102');
    x:=replace(x, 'a33.x', 'a35.x');
    x:=replace(x, 'a34.x', 'a36.x');
    e:=replace(e, s.BH||'03', x); -- Othr
    select xt into x from me where id=g||'Pric';
    e:=replace(e, s.BH||'11', x); -- Pric
    x:=replace(x, 'FIM0049', 'FIM0110');
    x:=replace(x, 'FIM0050', 'FIM0111');
    x:=replace(x, 'FIM0051', 'FIM0112');
    x:=replace(x, 'FIM0052', 'FIM0113');
    x:=replace(x, 'FIM0053', 'FIM0114');
    x:=replace(x, 'FIM0054', 'FIM0115');
    e:=replace(e, s.BH||'12', x); -- StrkPric
    s.uei(e); p('D1'); end if;
  if s.pg(sn)='D1' then
    select xt into x from me where id=g||'Cxl'; m0;
    x:=replace(x, s.BH||'tTRAF10_51', a51);
    s.uei(x); p('D1a'); end if;
  if s.pg(sn)='D1a' then s.ugs(tx); xt_i(tx); p('BD'); end if;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_34: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_34');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_36: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_36');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_41: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_41');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_42: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(replace(replace(
          m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
        , 'TRAF10_21', 'TRAF10_42')
      , ':1:=0; /* see 0e')
    , ' */ -- see 0e');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_21: A mandatory row is missing.
*/
declare
  ec stp.id%type:='__\e.001.ec'; -- error count
begin
  \i
  \s
    \k
  , 'FIL-105 (P9902.TRAF10_11,T0019.'||T0019||')'
    ||' A mandatory row is missing in TRAF10_21.'
  , \aTRAF10_11
  , -1 h_ -- unsaveable - no reference in h.mi_
  from (
      select T0019 from \tTRAF10_11 minus
      select h.T0019 from
        \EX11 h
      , \tTRAF10_21 d
      where h.T0019<=d.T0019
      and d.T0019<h.NEXT_T0019
      and h.R0011=d.R0011
      and h.R0012=d.R0012
    ); s.ps(ec, sql%rowcount+s.pg(ec)); :1:=0;
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_23: A mandatory row is missing.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '01')
    , 'TRAF10_21', 'TRAF10_23');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_35: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_35');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_23: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_23');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_24: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_24');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_31: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_31');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_32: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_32');
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: Create XML file. Part 2 of 4.
*/
  tx:=fx||'23';
  if s.pg(sn)='23' then xt_c(tx);
    select replace(xt, s.BH||'01'
      , (select xt from me where id=g||'Prsn')
      )
    into x from me where id=g||'a21'; m0; -- a23 is similar
    x:=replace(x, s.BH||'tTRAF10_21', a23);
    x:=replace(x, 's.FMT_ISODATE', ''''||s.FMT_ISODATE||'''');
    x:=replace(x, 'FIM0004', 'FIM0021');
    x:=replace(x, 'FIM0005', 'FIM0022');
    x:=replace(x, 'FIM0006', 'FIM0023');
    x:=replace(x, 'FIM0007', 'FIM0024');
    x:=replace(x, 'FIM0008', 'FIM0025');
    x:=replace(x, 'FIM0009', 'FIM0026');
    x:=replace(x, 'FIM0010', 'FIM0027');
    x:=replace(x, 'FIM0011', 'FIM0028');
    x:=replace(x, 'FIM0012', 'FIM0029');
    x:=replace(x, 'FIM0013', 'FIM0030');
    s.uei(x); p('23a'); end if;
  if s.pg(sn)='23a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('23b'); end if;
  if s.pg(sn)='23b' then s.ugs(tx); p('24'); end if;
  tx:=fx||'24';
  if s.pg(sn)='24' then xt_c(tx);
    select replace(xt, s.BH||'01'
      , (select xt from me where id=g||'Prsn')
      )
    into x from me where id=g||'a22'; m0; -- a24 is similar
    x:=replace(x, s.BH||'tTRAF10_22', a24);
    x:=replace(x, 's.FMT_ISODATE', ''''||s.FMT_ISODATE||'''');
    x:=replace(x, 'FIM0006', 'FIM0032');
    x:=replace(x, 'FIM0007', 'FIM0033');
    x:=replace(x, 'FIM0008', 'FIM0034');
    x:=replace(x, 'FIM0009', 'FIM0035');
    x:=replace(x, 'FIM0010', 'FIM0036');
    x:=replace(x, 'FIM0011', 'FIM0037');
    x:=replace(x, 'FIM0014', 'FIM0031');
    x:=replace(x, 'FIM0015', 'FIM0032');
    x:=replace(x, 'FIM0016', 'FIM0033');
    x:=replace(x, 'FIM0017', 'FIM0034');
    x:=replace(x, 'FIM0018', 'FIM0035');
    x:=replace(x, 'FIM0019', 'FIM0036');
    x:=replace(x, 'FIM0020', 'FIM0037');
    s.uei(x); p('24a'); end if;
  if s.pg(sn)='24a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('24b'); end if;
  if s.pg(sn)='24b' then s.ugs(tx); p('31'); end if;
  tx:=fx||'31';
  if s.pg(sn)='31' then xt_c(tx);
    select xt into x from me where id=g||'a31'; m0;
    x:=replace(x, s.BH||'tTRAF10_31', a31);
    s.uei(x); p('31a'); end if;
  if s.pg(sn)='31a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('31b'); end if;
  if s.pg(sn)='31b' then s.ugs(tx); p('32'); end if;
  tx:=fx||'32';
  if s.pg(sn)='32' then xt_c(tx);
    select xt into x from me where id=g||'a32'; m0;
    x:=replace(x, s.BH||'tTRAF10_32', a32);
    s.uei(x); p('32a'); end if;
  if s.pg(sn)='32a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('32b'); end if;
  if s.pg(sn)='32b' then s.ugs(tx); p('33'); end if;
  tx:=fx||'33';
  if s.pg(sn)='33' then xt_c(tx);
    select xt into x from me where id=g||'a31'; m0; -- a33 is similar
    x:=replace(x, s.BH||'tTRAF10_31', a33);
    x:=replace(x, 'FIM0079', 'FIM0091');
    s.uei(x); p('33a'); end if;
  if s.pg(sn)='33a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('33b'); end if;
  if s.pg(sn)='33b' then s.ugs(tx); p('34'); end if;
  tx:=fx||'34';
  if s.pg(sn)='34' then xt_c(tx);
    select xt into x from me where id=g||'a32'; m0; -- a34 is similar
    x:=replace(x, s.BH||'tTRAF10_32', a34);
    x:=replace(x, 'FIM0080', 'FIM0092');
    x:=replace(x, 'FIM0081', 'FIM0093');
    x:=replace(x, 'FIM0082', 'FIM0094');
    x:=replace(x, 'FIM0083', 'FIM0095');
    x:=replace(x, 'FIM0084', 'FIM0096');
    s.uei(x); p('34a'); end if;
  if s.pg(sn)='34a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('34b'); end if;
  if s.pg(sn)='34b' then s.ugs(tx); p('35'); end if;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: Create XML file. Part 1 of 4.
*/
declare
  a11 char(11):='\tTRAF10_11';
  a21 char(11):='\tTRAF10_21';
  a22 char(11):='\tTRAF10_22';
  a23 char(11):='\tTRAF10_23';
  a24 char(11):='\tTRAF10_24';
  a31 char(11):='\tTRAF10_31';
  a32 char(11):='\tTRAF10_32';
  a33 char(11):='\tTRAF10_33';
  a34 char(11):='\tTRAF10_34';
  a35 char(11):='\tTRAF10_35';
  a36 char(11):='\tTRAF10_36';
  a41 char(11):='\tTRAF10_41';
  a42 char(11):='\tTRAF10_42';
  a51 char(11):='\tTRAF10_51';
  d md.id%type;
  e varchar2(32767 byte); -- expression
  fn char(3):='m\j';
  ec stp.id%type:='__\e.001.ec'; -- error count
  f mf%rowtype;
  fc mfc%rowtype;
  fx char(11):='\EX'; -- m.FON_DATXML
  g me.id%type:='m.tab2xml-003-';
  q varchar2(32767 byte); -- query
  sn stp.id%type:='__\e.001.sn'; -- step No
  tx char(13); -- xml table name
  x me.xt%type;
  procedure p(sn_ char) is begin s.ps(sn, sn_); commit; end;
  procedure m0 is begin -- macro No 0
    x:=replace(x, s.BH||'i', replace('\i', '\e', tx));
    x:=replace(x, s.BH||'s', '\s');
    x:=replace(x, s.BH||'tfx', fx); end;
  procedure xt_c(n_ char) is begin -- xml table - create
    if s.uox(n_)=s.GC_NO then s.ued('
create table '||n_||'
(T0019 number('||m.SZ_T0019||') constraint nn_'||n_||'_T0019 not null
,x xmltype)'); else s.utt(n_); end if; end;
  procedure xt_d(n_ char) is begin -- xml table - drop
    if s.uox(n_)=s.GC_YES then s.ued('drop table '||n_); end if; end;
  procedure xt_i(n_ char) is begin -- xml table - index T0019
    if s.uox('IX_'||n_, s.GC_INDEX)=s.GC_NO then s.ued('
create index ix_'||n_||' on '||n_||' (T0019)'); end if; end;
  procedure xt_p(n_ char) is begin  -- xml table - populate by null
    s.uei(replace('\i', '\e', n_)||' (T0019)
\s T0019 from '||a11||'
minus
select T0019 from '||n_); end;
begin
  -- for a11 see 00
  g:='m.tab2xml-016-'; tx:=fx||'21';
  if s.pg(sn)='21' then xt_c(tx);
    select replace(xt, s.BH||'01'
      , (select xt from me where id=g||'Prsn')
      )
    into x from me where id=g||'a21'; m0;
    x:=replace(x, s.BH||'tTRAF10_21', a21);
    x:=replace(x, 's.FMT_ISODATE', ''''||s.FMT_ISODATE||'''');
    s.uei(x); p('21a'); end if;
  if s.pg(sn)='21a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('21b'); end if;
  if s.pg(sn)='21b' then s.ugs(tx); p('22'); end if;
  tx:=fx||'22';
  if s.pg(sn)='22' then xt_c(tx);
    select replace(xt, s.BH||'01'
      , (select xt from me where id=g||'Prsn')
      )
    into x from me where id=g||'a22'; m0;
    x:=replace(x, s.BH||'tTRAF10_22', a22);
    x:=replace(x, 's.FMT_ISODATE', ''''||s.FMT_ISODATE||'''');
    x:=replace(x, 'FIM0006', 'FIM0015');
    x:=replace(x, 'FIM0007', 'FIM0016');
    x:=replace(x, 'FIM0008', 'FIM0017');
    x:=replace(x, 'FIM0009', 'FIM0018');
    x:=replace(x, 'FIM0010', 'FIM0019');
    x:=replace(x, 'FIM0011', 'FIM0020');
    s.uei(x); p('22a'); end if;
  if s.pg(sn)='22a' then s.ugs(tx); xt_i(tx); xt_p(tx); p('22b'); end if;
  if s.pg(sn)='22b' then s.ugs(tx); p('23'); end if;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: Create XML file. Part 4 of 4.
*/
  if s.pg(sn)='BD' then
    g:='m.tab2xml-003-';
    if s.uox(fx)=s.GC_NO then s.ued('create table '||fx||' (b blob)');
    else s.utt(fx); end if;
    select xt into x from me where id=g||'BizData'; tx:=fx; m0;
    q:=replace(x, 'no indent', 'indent size=2');
    g:='m.tab2xml-001-';
    select xt into x from me where id=g||'AppHdr-016';
    e:=replace(x, s.BH||'u', '\u');
    q:=replace(q, s.BH||'h', e);
    g:='m.tab2xml-016-';
    select xt into e from me where id=g||'Document';
    e:=replace(e, s.BH||'tfx', fx);
    q:=replace(q, s.BH||'d', e);
    s.uei(q); p('0'); end if;
  if s.pg(sn)='0' then
    select xt into x from me where id='m.sh-touch';
    x:=replace(x, s.BH||'f', s.fdp||'/'||fx||'.xml');
    s.fxsh(fn, x);
    select xt into x from me where id='m.sh-chgrp';
    x:=replace(x, s.BH||'g', s.pg('s.sh-group'));
    x:=replace(x, s.BH||'f', s.fdp||'/'||fx||'.xml');
    s.fxsh(fn, x); declare b blob;
    begin execute immediate 'select b from '||fx into b;
      s.fcp(b, fx||'.xml'); end; p('1'); end if;
  if s.pg(sn)='1' then
    select xt into x from me where id='m.xml-validate';
    select id into d from md where ii=\D;
    x:=replace(x, s.BH||'C0', d);
    x:=replace(x, s.BH||'C1', ec);
    x:=replace(x, s.BH||'C2', fx||'.xml');
    x:=replace(x, s.BH||'e', '\e');
    x:=replace(x, s.BH||'j', '\j');
    x:=replace(x, s.BH||'k', '\k');
    s.uei(x); p('2'); end if;
  begin
    select distinct 'CNB_DATT'||substr(md.id, -2)||'_'||mpc.ii||'_'
      ||substr(mi.id, -6)||'_'||to_char(mi.t, 'yy')||'.xml.gz'
    into f.n
    from
      mi
    , mo
    , mpc
    , mpk
    , md
    where mi.ii=\u
    and mi.mo_=mo.ii
    and mo.mp_=mpc.mp_
    and mpc.ii=mpk.mpc_
    and trunc(sysdate) between mpk.cr and mpk.ex
    and mpk.mpks_=m.KS_VERIFIED
    and mpk.rd is null
    and mo.md_=md.ii;
  exception when no_data_found then f.n:=null; end;
  if s.pg(sn)='2' then
    if f.n is not null then f.ii:=s.ir('mf.ii'); -- lock
      s.ii(f.ii); s.iw('mf.ii', f.ii); f.mfs_:=m.FS_CREATED;
      insert into mf values f;
      fc.mf_:=f.ii; fc.b:=empty_blob(); insert into mfc values fc; end if;
    p('3'); end if;
  if f.n is not null and f.ii is null then
    select * into f from mf where n=f.n;
    select * into fc from mfc where mf_=f.ii; end if;
  if s.pg(sn)='3' then
    if f.n is not null then declare s blob; t blob; begin
      execute immediate 'select b from '||fx into s;
      select b into t from mfc where mf_=f.ii for update;
      utl_compress.lz_compress(s, t);
      update mf set mfs_=m.FS_LOADED where ii=f.ii; end;
    end if; p('4'); end if;
  xt_d(fx); xt_d(fx||'DX'); xt_d(fx||'11');
  xt_d(fx||'21'); xt_d(fx||'22'); xt_d(fx||'23'); xt_d(fx||'24');
  xt_d(fx||'31'); xt_d(fx||'32'); xt_d(fx||'33'); xt_d(fx||'34');
  xt_d(fx||'35'); xt_d(fx||'36'); xt_d(fx||'41'); xt_d(fx||'42');
  if s.pg(sn)='4' then
    select xt into x from me where id='m.sh-rm';
    x:=replace(x, s.BH||'f', s.fdp||'/'||fx||'*');
    s.fxsh(fn, x); p('5'); end if;
  :1:=s.pg(ec); s.pd(ec); s.pd(sn);
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11+TRAF10_51: Duplicity of T0019.
*/
declare
  ec stp.id%type:='__\e.001.ec'; -- error count
  sn stp.id%type:='__\e.001.sn'; -- step No
  x me.xt%type;
  procedure p(sn_ char) is begin s.ps(sn, sn_); commit; end;
begin
  if s.pg(sn, s.GC_NO) is null then
    if s.uox('\EX11')=s.GC_NO then s.ued('
create table \EX11
(T0019 number('||m.SZ_T0019||') constraint nn_\EX11_T0019 not null
,NEXT_T0019 number('||m.SZ_T0019||')'
||' constraint nn_\EX11_NEXT_T0019 not null
,R0011 varchar2(52 byte) constraint nn_\EX11_R0011 not null
,R0012 varchar2(20 byte) constraint nn_\EX11_R0012 not null
)'); end if; p('11'); end if;
  if s.pg(sn)='11' then
    select xt into x from me where id='m.tab2xml-016-h11';
    x:=replace(x, s.BH||'i', replace('\i', '\e', '\EX11'));
    x:=replace(x, s.BH||'s', '\s');
    x:=replace(x, s.BH||'tTRAF'||'10_11', '\tTRAF10_11'); -- see T20
    x:=replace(x, s.BH||'C0', lpad('9', m.SZ_T0019, '9'));
    s.uei(x); p('11a'); end if;
  if s.pg(sn)='11a' then s.ugs('\EX11'); p('11b'); end if;
  if s.pg(sn)='11b' then
    if s.uox('\EX11_I00', s.GC_INDEX)=s.GC_NO then s.ued('
create index \EX11_I00 on \EX11 (T0019,NEXT_T0019,R0011,R0012)');
    end if; p('21'); end if; -- see 0f
  \i
  \s
    \k
  , 'FIL-105 (P9902.'||P9902||',T0019.'||T0019||') Duplicity of T0019.'
  , P9902_
  , -1 h_ -- unsaveable - no reference in h.mi_
  from (
      select
        'TRAF10_11' P9902
      , T0019
      , \aTRAF10_11 P9902_
      from \tTRAF10_11 union all
      select
        'TRAF10_51' P9902
      , T0019
      , \aTRAF10_51 P9902_
      from \tTRAF10_51
    )
  where T0019 in (
    select T0019 from (
        select T0019 from \tTRAF10_11 union all
        select T0019 from \tTRAF10_51
      )
    group by T0019 having count(*)>1
  ); s.ps(ec, sql%rowcount); :1:=0;
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_21: T0019 out of range of TRAF10_11.
*/
declare
  e number(9);
  ec stp.id%type:='__\e.001.ec'; -- error count
  sn stp.id%type:='__\e.001.sn'; -- step No
begin
  \i
  \s
    \k
  , 'FIL-105 (P9902.TRAF10_21,T0021.'||T0021||',T0019.'||T0019
    ||',R0011.'||R0011||',R0012.'||R0012||')'
    ||' TRAF10_21.T0019 out of range of TRAF10_11.T0019.'
  , \aTRAF10_11
  , -1 h_ -- unsaveable - no reference in h.mi_
  from (
      select
        T0021
      , T0019
      , R0011
      , R0012
      from \tTRAF10_21 minus
      select
        T0021
      , h.T0019
      , h.R0011
      , h.R0012
      from
        \EX11 h
      , \tTRAF10_21 d
      where h.T0019<=d.T0019
      and d.T0019<h.NEXT_T0019
      and h.R0011=d.R0011
      and h.R0012=d.R0012
    ); s.ps(ec, sql%rowcount+s.pg(ec)); :1:=0; /* see 0e
  e:=s.pg(ec);
  if e>0 then
    if s.uox('\EX11')=s.GC_YES then s.ued('drop table \EX11'); end if;
    s.pd(ec); s.pd(sn); end if; :1:=e; */ -- see 0e
end;

MKT20180103 TRAFIM10 000 0001
/*
FIL-105: Kontrola oproti ESMA XML schématu
  CT_MTS: TRAF10_11-TRAF10_33: T0019 out of range of TRAF10_11.
\#include
*/
begin :1:=replace(m.gke('MKT20180103', 'TRAFIM10', '000', '0001', '03')
    , 'TRAF10_21', 'TRAF10_33');
end;

MKT20180103 TRAFIM10 110 0023
/*
CON-023: "If the report is a new transaction, the transaction reference
number shall pertain to a single transaction per the executing entity, i.e.
the same transaction reference number has not been used before or pertains
to a transaction that has been cancelled (the last accepted report with this
transaction reference number was a cancellation report)."
Part 2 of 2. Fragment 1 of 3. The first item of the Set 1.
*/
      select
        h.ii
      , r.R0011
      , r.R0012
      , h.T0019
      , to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE)
      , null
      , 'T' -- new transaction
      from
        rtraf10_11 r
      , rtraf10_11h h
      where r.ii=h.r_
      and h.mi_=\u
      union all
      select
        h.ii
      , r.R0011
      , r.R0012
      , h.T0019
      , null
      , null
      , 'C' -- new cancellation
      from
        rtraf10_51 r
      , rtraf10_51h h
      where r.ii=h.r_
      and h.mi_=\u
      order by 2, 3, 4
    );
  for i in x.first..x.last loop
    case x(i).t
    when 'C' then
      if gz=0 then en:=k_0024; et:=T_0024;
      elsif px.t in ('C', 'c') then
        if en=0 then en:=k_0025; et:=T_0025; end if;
      elsif px.t in ('T', 't') then
        if en=0 then w(x(i), k_ACPT);
          if px.s is null then e(pp).mk_:=k_RMVD;
          else w(px, case px.s when 'ACPT' then k_CNCL else k_RMVD end);
          end if;
        else en:=k_0024; et:=T_0024; end if; end if;
    when 'T' then
      if gz=0 or px.t in ('C', 'c') then en:=0;
        w(x(i), case x(i).FIM0041 when rd then k_RCVD else k_UNKN end);
        pp:=ne;
      elsif px.t in ('T', 't') then en:=k_0023; et:=T_0023; end if;
    else null; end case;
    if en!=0 then w(x(i), en, et); w(x(i), k_RJCT); iec; end if; px:=x(i);
    if i<x.last and (
      x(i).R0011!=x(i+1).R0011 or x(i).R0012!=x(i+1).R0012
    ) then gz:=0; en:=0; else gz:=gz+1; end if;
  end loop; if ne>0 then
    forall i in 1..ne insert into \e values e(i); end if; :1:=ec;
end;\m2 -- end if

MKT20180103 TRAFIM10 110 0023
/*
CON-023: "If the report is a new transaction, the transaction reference
number shall pertain to a single transaction per the executing entity, i.e.
the same transaction reference number has not been used before or pertains
to a transaction that has been cancelled (the last accepted report with this
transaction reference number was a cancellation report)."
Part 1 of 2. Fragment 1 of 3. The first item of the Set 1.
*/
\m0 -- if m_pm.ii=m.PM_DEFEERED then
begin :1:=0; return; end;
\m1 -- else
declare
  T_0023 constant char(114):='Transaction report with the same transaction'
  ||' reference number has already been sent for the firm and not cancelled';
  T_0024 constant char(44):='Transaction for cancellation cannot be found';
  T_0025 constant char(38):='Transaction has already been cancelled';
  type r_x is record (
    h_ rtraf10_11h.ii%type
  , R0011 rtraf10_11.R0011%type
  , R0012 rtraf10_11.R0012%type
  , T0019 rtraf10_11h.T0019%type
  , FIM0041 date -- converted
  , s rtraf10_11f.s%type
  , t char(1) -- type (T|C)|(t|c) = (new)|(old)
  );
  type t_e is table of \e%rowtype index by pls_integer;
  type t_x is table of r_x index by pls_integer;
  e t_e;
  ec pls_integer:=0; -- realy error count
  en mk.ii%type:=0; -- last error number
  et varchar2(200 byte); -- last error text
  gz pls_integer:=0; -- group size
  k_0023 mk.ii%type:=\k0023;
  k_0024 mk.ii%type:=\k0024;
  k_0025 mk.ii%type:=\k0025;
  k_ACPT mk.ii%type;
  k_CNCL mk.ii%type;
  k_RCVD mk.ii%type;
  k_RJCT mk.ii%type;
  k_RMVD mk.ii%type;
  k_UNKN mk.ii%type:=0; -- unknown
  ne pls_integer:=0; -- number of errors
  pp pls_integer:=0; -- previous extract (position in collection e)
  px r_x; -- previous extract
  rd date:=trunc(sysdate); -- reference date
  x t_x; -- extract
  procedure iec is begin ec:=ec+1; end; -- increment error count
  procedure w(p_x r_x, p_k mk.ii%type, p_t char:=' ') is -- write error
  begin ne:=ne+1; e(ne).mk_:=p_k; e(ne).h_:=p_x.h_; e(ne).d:=p_t;
    e(ne).ma_:=case p_x.t when 'C' then \aTRAF10_51 else \aTRAF10_11 end;
  end;
begin
  select ii into k_RCVD from mk where md_=\D
  and id='0010';
  select ii into k_ACPT from mk where md_=\D
  and id='0012';
  select ii into k_CNCL from mk where md_=\D
  and id='0013';
  select ii into k_RJCT from mk where md_=\D
  and id='0014';
  select ii into k_RMVD from mk where md_=\D
  and id='0910';
  \s * bulk collect into x from (
      select
        h_
      , R0011
      , R0012
      , -1 T0019 -- formal min
      , null
      , s
      , case s when 'CNCL' then 'c' else 't' end n
      from (
with q as (
  select /*+ materialize */ * from (
      select
        R0011
      , R0012
      from \tTRAF10_11
      union
      select
        R0011
      , R0012
      from \tTRAF10_51
    )
)
select
  h_
, R0011
, R0012
, s
, row_number() over (partition by R0011, R0012 order by rt desc) as rowno
from (
    select
      h.ii h_
    , r.R0011
    , r.R0012
    , f.s
    , f.rt
    , row_number() over (partition by h.ii order by f.rt desc) as rowno
    from
      rtraf10_11 r
    , rtraf10_11h h
    , rtraf10_11f f
    , q
    where r.ii=h.r_
    and h.ii=f.h_
    and r.R0011=q.R0011
    and r.R0012=q.R0012
  )
where rowno=1
and s not in ('RJCT', 'RMVD')
        )
      where rowno=1
      and s in ('ACPT', 'CNCL', 'PDNG', 'RCVD')
      union all

MKT20180103 TRAFIM10 110 0024
/*
CON-024: "If the report is a cancellation report, a transaction with the
same transaction reference number should have been reported by the executing
entity before."
Fragment 2 of 3.
*/

MKT20180103 TRAFIM10 110 0025
/*
CON-025: "If the report is a cancellation report, this transaction should be
an active transaction (there was no cancellation report following the last
accepted new transaction report for this transaction reference number)."
Fragment 3 of 3.
*/

MKT20180103 TRAFIM10 110 0040
/*
CON-040: "Where field 5 is populated with 'true', this field shall be
populated with a LEI accurately formatted and in the LEI database included
in the GLEIF database maintained by the Central Operating Unit.
1. The status of the LEI shall be 'Issued', 'Pending transfer' or
'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  declare
    e varchar2(32767 byte); -- expression
    skr stp.id%type:='__\e.040.skr'; -- save check result
                                     -- after check No. 025
  begin
    if '\m'=m.PM_CHECKER then
      if s.pg(skr, s.GC_NO) is null then
        s.ugs('\e'); s.ps(skr, '0'); commit; end if;
      if s.pg(skr)='0' then s.ps(skr, '1');
        select xt into e from me
        where id='m.save-check-results-T10-after-CON-025';
        e:=replace(e, s.BH||'D', '\D');
        e:=replace(e, s.BH||'K', '\K');
        e:=replace(e, s.BH||'a', '\aTRAF10_11');
        e:=replace(e, s.BH||'e', '\e');
        e:=replace(e, s.BH||'t', 'rtraf10_11');
        s.uei(e); commit; end if;
      if s.pg(skr)='1' then s.ps(skr, '2');
        select xt into e from me
        where id='m.save-check-results-T10-after-CON-025';
        e:=replace(e, s.BH||'D', '\D');
        e:=replace(e, s.BH||'K', '\K');
        e:=replace(e, s.BH||'a', '\aTRAF10_51');
        e:=replace(e, s.BH||'e', '\e');
        e:=replace(e, s.BH||'t', 'rtraf10_51');
        s.uei(e); commit; end if;
      s.pd(skr);
    end if;
  end;
  \i
  \s
    \k
  , 'The executing entity LEI is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r.R0012
        , to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
          fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0002='true'
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , el
      where q.R0012=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=q.fim0041
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0041
/*
CON-041: "Where field 5 is populated with 'false', this field shall be
populated with a LEI accurately formatted and in the LEI database included
in the GLEIF database maintained by the Central Operating Unit.
1. The status of the LEI shall be 'Issued', 'Pending transfer' 'Pending
archival' or 'Lapsed'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The executing entity LEI is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r.R0012
        , to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
          fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0002='false'
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , el
      where q.R0012=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=q.fim0041
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0060
/*
CON-060: "This field shall be populated with a LEI accurately formatted and
in the LEI database included in the GLEIF database maintained by the Central
Operating Unit.
1. The status of the LEI shall be "Issued", "Lapsed", "Pending transfer" or
"Pending archival"; 2. The InitialRegistrationDate of the LEI shall be equal
or before the submission date; 3. The EntityStatus shall be Active or if the
EntityStatus is Inactive, the LastUpdateDate shall be equal or after the
submission date".
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Submitting entity LEI is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0003
        , to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
          fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , el
      where q.FIM0003=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=q.fim0041
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0070
/*
CON-070: "If LEI is used, this field shall be populated with a LEI
accurately formatted and in the LEI database included in the GLEIF database
maintained by the Central Operating Unit.
1. The status of the LEI shall be 'Issued', 'Lapsed', 'Pending
transfer' or 'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
  RD date:=to_date('2018-07-03', s.FMT_ISODATE); -- reference date for
                                                 -- start of validation
begin
  \i
  \s
    \k
  , 'Buyer LEI '||FIM0004||' is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r21.FIM0004
        , to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_21 r21
        , rtraf10_21h h21
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h21.h_
        and h21.r_=r21.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r21.FIM0004 is not null
      )
      select
        ii
      , FIM0004
      from q
      minus
      select
        q.ii
      , q.FIM0004
      from
        q
      , el
      where q.FIM0004=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
-- \#begin
      and el.i<=case when q.fim0041<RD
          then to_date(s.GC_DT4K, s.FMT_ISODATE) else q.fim0041 end
-- \#end
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0071
/*
CON-071: "If national identifier is used, the first 2 characters of the
national identification code should be an ISO 3166 country code that was
valid at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Buyer national identification code '||FIM0009||' does not include'
    ||' valid country code'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r21.FIM0009
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_21 r21
        , rtraf10_21h h21
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h21.h_
        and h21.r_=r21.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r21.FIM0009 is not null
      )
      select
        ii
      , FIM0009
      from q
      minus
      select
        q.ii
      , q.FIM0009
      from
        q
      , ey
      where substr(q.FIM0009, 1, 2)=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0072
/*
CON-072: "If MIC code is used, it should be a valid ISO 10383 MIC code
indicating a trading venue that was active at the trading date, excluding
special MIC codes 'XXXX' and 'XOFF'."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Buyer MIC '||FIM0005||' is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r21.FIM0005
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_21 r21
        , rtraf10_21h h21
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h21.h_
        and h21.r_=r21.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r21.FIM0005 is not null
        and nvl(r21.FIM0005, 'x') not in ('XOFF', 'XXXX')
      )
      select
        ii
      , FIM0005
      from q
      minus
      select
        q.ii
      , q.FIM0005
      from
        q
      , em
      where q.FIM0005=em.id
      and q.fim0041 between em.vf and em.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0073
/*
CON-073: "If the CONCAT code is used, the birthdate in the CONCAT code
should be the same as the birthdate populated in field 11."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The CONCAT code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_21 r21
  , rtraf10_21h h21
  , \e x
  where r.ii=h.r_
  and h.ii=h21.h_
  and h21.r_=r21.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and r21.FIM0011='CONCAT'
  and nvl(to_char(r21.FIM0008, s.FMT_CMPDATE), '0')
    !=nvl(substr(r21.FIM0009, 3, 8), '1');
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0074
/*
CON-074: "In case the CONCAT code is used, the following characters are only
allowed: Capital Latin letters, Numbers, #. It should be a string of exactly
20 characters where first two characters are letters, the next 8 characters
are numbers and the remaining characters are letters or #, where 11th and
16th character are letters.
In case national identification number or passport number is used, the
following characters are only allowed: capital Latin letters (A-Z), numbers
(0-9) as well as '+' and '-' in case the identifier starts with "FI" and "-"
when the identifier starts with "LV". It can be a string of 3 to 35
characters, where first two characters are letters."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The format of the buyer identification code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_21 r21
  , rtraf10_21h h21
  , \e x
  where r.ii=h.r_
  and h.ii=h21.h_
  and h21.r_=r21.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      r21.FIM0011='CONCAT'
      and nvl(regexp_instr(r21.FIM0009, e.RX_CONCAT), 0)=0
    ) or (
      r21.FIM0010 in ('CCPT', 'NIDN')
      and nvl(regexp_instr(r21.FIM0009, e.RX_NIDNCCPT), 0)=0
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0080
/*
CON-080: "If populated, it should be an ISO 3166 country code that was valid
at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Country code '||FIM0013||' is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r21.FIM0013
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_21 r21
        , rtraf10_21h h21
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h21.h_
        and h21.r_=r21.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r21.FIM0013 is not null
      )
      select
        ii
      , FIM0013
      from q
      minus
      select
        q.ii
      , q.FIM0013
      from
        q
      , ey
      where q.FIM0013=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0120
/*
CON-120: "If LEI is used, this field shall be populated with a LEI
accurately formatted and in the LEI database included in the GLEIF database
maintained by the Central Operating Unit.
1. The status of the LEI shall be 'Issued', 'Lapsed', 'Pending
transfer' or 'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
  RD date:=to_date('2018-07-03', s.FMT_ISODATE); -- reference date for
                                                 -- start of validation
begin
  \i
  \s
    \k
  , 'Buyer decision maker LEI '||FIM0014||' is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r22.FIM0014
        , to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_22 r22
        , rtraf10_22h h22
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h22.h_
        and h22.r_=r22.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r22.FIM0014 is not null
      )
      select
        ii
      , FIM0014
      from q
      minus
      select
        q.ii
      , q.FIM0014
      from
        q
      , el
      where q.FIM0014=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
-- \#begin
      and el.i<=case when q.fim0041<RD
          then to_date(s.GC_DT4K, s.FMT_ISODATE) else q.fim0041 end
-- \#end
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0121
/*
CON-121: "If national identifier is used, the first 2 characters of the
national identification code should be an ISO 3166 country code that was
valid at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Buyer decision maker identification code '||FIM0018||' does not'
    ||' include valid country code'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r22.FIM0018
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_22 r22
        , rtraf10_22h h22
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h22.h_
        and h22.r_=r22.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r22.FIM0018 is not null
      )
      select
        ii
      , FIM0018
      from q
      minus
      select
        ii
      , FIM0018
      from
        q
      , ey
      where substr(q.FIM0018, 1, 2)=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0122
/*
CON-122: "If the CONCAT code is used, the birthdate in the CONCAT code
should be the same as the birthdate populated in field 15."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The CONCAT code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_22 r22
  , rtraf10_22h h22
  , \e x
  where r.ii=h.r_
  and h.ii=h22.h_
  and h22.r_=r22.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and r22.FIM0020='CONCAT'
  and nvl(to_char(r22.FIM0017, s.FMT_CMPDATE), '0')
    !=nvl(substr(r22.FIM0018, 3, 8), '1');
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0123
/*
CON-123: "In case the CONCAT code is used, the following characters are only
allowed: Capital Latin letters, Numbers, #. It should be a string of exactly
20 characters where first two characters are letters, the next 8 characters
are numbers and the remaining characters are letters or #, where 11th and
16th character are letters.
In case national identification number or passport number is used, the
following characters are only allowed: capital Latin letters (A-Z), numbers
(0-9) as well as '+' and '-' in case the identifier starts with "FI" and "-"
when the identifier starts with "LV". It can be a string of 3 to 35
characters, where first two characters are letters."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The format of the buyer decision maker code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_22 r22
  , rtraf10_22h h22
  , \e x
  where r.ii=h.r_
  and h.ii=h22.h_
  and h22.r_=r22.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      r22.FIM0020='CONCAT'
      and nvl(regexp_instr(r22.FIM0018, e.RX_CONCAT), 0)=0
    ) or (
      r22.FIM0019 in ('CCPT', 'NIDN')
      and nvl(regexp_instr(r22.FIM0018, e.RX_NIDNCCPT), 0)=0
    )
  );
:1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0160
/*
CON-160: "If LEI is used, this field shall be populated with a LEI
accurately formatted and in the LEI database included in the GLEIF database
maintained by the Central Operating Unit.
1. The status of the LEI shall be 'Issued', 'Lapsed', 'Pending
transfer' or 'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
  RD date:=to_date('2018-07-03', s.FMT_ISODATE); -- reference date for
                                                 -- start of validation
begin
  \i
  \s
    \k
  , 'Seller LEI '||FIM0021||' is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r23.FIM0021
        , to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_23 r23
        , rtraf10_23h h23
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h23.h_
        and h23.r_=r23.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r23.FIM0021 is not null
      )
      select
        ii
      , FIM0021
      from q
      minus
      select
        q.ii
      , q.FIM0021
      from
        q
      , el
      where q.FIM0021=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=case when q.fim0041<RD
          then to_date(s.GC_DT4K, s.FMT_ISODATE) else q.fim0041 end
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0161
/*
CON-161: "If national identifier is used, the first 2 characters of the
national identification code should be an ISO 3166 country code that was
valid at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Seller national identification code '||FIM0026||' does not'
    ||' include valid country code'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r23.FIM0026
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_23 r23
        , rtraf10_23h h23
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h23.h_
        and h23.r_=r23.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r23.FIM0026 is not null
      )
      select
        ii
      , FIM0026
      from q
      minus
      select
        q.ii
      , q.FIM0026
      from
        q
      , ey
      where substr(q.FIM0026, 1, 2)=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0162
/*
CON-162: "If MIC code is used, it should be a valid ISO 10383 MIC code
indicating a trading venue that was active at the trading date, excluding
special MIC codes 'XXXX' and 'XOFF'."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Seller MIC '||FIM0022||' is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r23.FIM0022
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_23 r23
        , rtraf10_23h h23
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h23.h_
        and h23.r_=r23.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r23.FIM0022 is not null
        and nvl(r23.FIM0022, 'x') not in ('XOFF', 'XXXX')
      )
      select
        ii
      , FIM0022
      from q
      minus
      select
        q.ii
      , q.FIM0022
      from
        q
      , em
      where q.FIM0022=em.id
      and q.fim0041 between em.vf and em.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0163
/*
CON-163: "If the CONCAT code is used, the birthdate in the CONCAT code
should be the same as the birthdate populated in field 20."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The CONCAT code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_23 r23
  , rtraf10_23h h23
  , \e x
  where r.ii=h.r_
  and h.ii=h23.h_
  and h23.r_=r23.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and r23.FIM0028='CONCAT'
  and nvl(to_char(r23.FIM0025, s.FMT_CMPDATE), '0')
    !=nvl(substr(r23.FIM0026, 3, 8), '1');
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0164
/*
CON-164: "In case the CONCAT code is used, the following characters are only
allowed: Capital Latin letters, Numbers, #. It should be a string of exactly
20 characters where first two characters are letters, the next 8 characters
are numbers and the remaining characters are letters or #, where 11th and
16th character are letters.
In case national identification number or passport number is used, the
following characters are only allowed: capital Latin letters (A-Z), numbers
(0-9) as well as '+' and '-' in case the identifier starts with "FI" and "-"
when the identifier starts with "LV". It can be a string of 3 to 35
characters, where first two characters are letters."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The format of the seller identification code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_23 r23
  , rtraf10_23h h23
  , \e x
  where r.ii=h.r_
  and h.ii=h23.h_
  and h23.r_=r23.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      r23.FIM0028='CONCAT'
      and nvl(regexp_instr(r23.FIM0026, e.RX_CONCAT), 0)=0
    ) or (
      r23.FIM0027 in ('CCPT', 'NIDN')
      and nvl(regexp_instr(r23.FIM0026, e.RX_NIDNCCPT), 0)=0
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0170
/*
CON-170: "If populated, it should be an ISO 3166 country code that was valid
at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Country code '||FIM0030||' is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r23.FIM0030
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_23 r23
        , rtraf10_23h h23
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h23.h_
        and h23.r_=r23.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r23.FIM0030 is not null
      )
      select
        ii
      , FIM0030
      from q
      minus
      select
        q.ii
      , q.FIM0030
      from
        q
      , ey
      where q.FIM0030=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0210
/*
CON-210: "If LEI is used, this field shall be populated with a LEI
accurately formatted and in the LEI database included in the GLEIF database
maintained by the Central Operating Unit.
1. The status of the LEI shall be 'Issued', 'Lapsed', 'Pending
transfer' or 'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
  RD date:=to_date('2018-07-03', s.FMT_ISODATE); -- reference date for
                                                 -- start of validation
begin
  \i
  \s
    \k
  , 'Seller decision maker LEI '||FIM0031||' is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r24.FIM0031
        , to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_24 r24
        , rtraf10_24h h24
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h24.h_
        and h24.r_=r24.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r24.FIM0031 is not null
      )
      select
        ii
      , FIM0031
      from q
      minus
      select
        q.ii
      , q.FIM0031
      from
        q
      , el
      where q.FIM0031=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=case when q.fim0041<RD
          then to_date(s.GC_DT4K, s.FMT_ISODATE) else q.fim0041 end
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0211
/*
CON-211: "If national identifier is used, the first 2 characters of the
national identification code should be an ISO 3166 country code that was
valid at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Seller decision maker identification code '||FIM0035
    ||' does not include valid country code'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , r24.FIM0035
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_24 r24
        , rtraf10_24h h24
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h24.h_
        and h24.r_=r24.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and r24.FIM0035 is not null
      )
      select
        ii
      , FIM0035
      from q
      minus
      select
        q.ii
      , q.FIM0035
      from
        q
      , ey
      where substr(q.FIM0035, 1, 2)=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0212
/*
CON-212: "If the CONCAT code is used, the birthdate in the CONCAT code
should be the same as the birthdate populated in field 24."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The CONCAT code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_24 r24
  , rtraf10_24h h24
  , \e x
  where r.ii=h.r_
  and h.ii=h24.h_
  and h24.r_=r24.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and r24.FIM0037='CONCAT'
  and nvl(to_char(r24.FIM0034, s.FMT_CMPDATE), '0')
    !=nvl(substr(r24.FIM0035, 3, 8), '1');
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0213
/*
CON-213: "In case the CONCAT code is used, the following characters are only
allowed: Capital Latin letters, Numbers, #. It should be a string of exactly
20 characters where first two characters are letters, the next 8 characters
are numbers and the remaining characters are letters or #, where 11th and
16th character are letters.
In case national identification number or passport number is used, the
following characters are only allowed: capital Latin letters (A-Z), numbers
(0-9) as well as '+' and '-' in case the identifier starts with "FI" and "-"
when the identifier starts with "LV". It can be a string of 3 to 35
characters, where first two characters are letters."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The format of the seller decision maker code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_24 r24
  , rtraf10_24h h24
  , \e x
  where r.ii=h.r_
  and h.ii=h24.h_
  and h24.r_=r24.ii
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      r24.FIM0037='CONCAT'
      and nvl(regexp_instr(r24.FIM0035, e.RX_CONCAT), 0)=0
    ) or (
      r24.FIM0036 in ('CCPT', 'NIDN')
      and nvl(regexp_instr(r24.FIM0035, e.RX_NIDNCCPT), 0)=0
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0251
/*
CON-251: "When the executing entity is dealing on its own account or matched
principal basis (value 'DEAL' or 'MTCH' populated in field 29) this field
must be populated with 'false'."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Transmission of order indicator is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0042 in ('DEAL', 'MTCH')
  and nvl(FIM0038, s.NA)!='false';
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0260
/*
CON-260: "This field shall be populated with a LEI accurately formatted and
in the LEI database included in the GLEIF database maintained by the Central
Operating Unit.
1. The status of the LEI shall be 'Issued', 'Lapsed', 'Pending
transfer' or 'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Firm transmitting identification code for the buyer LEI is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0039
        , to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0039 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , el
      where q.FIM0039=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=q.fim0041
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0270
/*
CON-270: "This field shall be populated with a LEI accurately formatted and
in the LEI database included in the GLEIF database maintained by the Central
Operating Unit.
1. The status of the LEI shall be 'Issued', 'Lapsed', 'Pending
transfer' or 'Pending archival'.
2. The InitialRegistrationDate of the LEI shall be equal or before the
trading date.
3. The EntityStatus shall be Active or if the EntityStatus is Inactive, the
LastUpdateDate shall be equal or after the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Firm transmitting identification code for the seller LEI is not valid'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0040
        , to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0040 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , el
      where q.FIM0040=el.id
      and el.rs in ('IS', 'LA', 'PA', 'PT')
      and el.i<=q.fim0041
      and (
        el.s='A'
        or (
          el.s='I'
          and el.u>=q.fim0041
        )
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0280
/*
CON-280: "Trading date time must be the same or before the system date
time."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
  rt timestamp; -- reference time
  sn stp.id%type:='__\e.280.sn'; -- step No
begin
  select sys_extract_utc(cast(t as timestamp)) into rt from mi where ii=\u;
  if s.pg(sn, s.GC_NO) is null then s.ps(sn, '0');
    \i
    \s
      \k
    , 'Trading date time is in the future'
    , \aTRAF10_11
    , h.ii
    from
      rtraf10_11 r
    , rtraf10_11h h
    , \e x
    where r.ii=h.r_
    and h.mi_=\u
    and h.ii=x.h_
    and x.mk_=k_UNKN
    and e.is_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
      =s.GC_NO;
    commit; end if; s.pd(sn);
  \i
  \s
    \k
  , 'Trading date time is in the future'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , (
      select h_ from \e where mk_=k_UNKN
      minus select h_ from \e where mk_=\k
    ) x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)>rt;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0281
/*
CON-281: "Trading date cannot be earlier than the agreed date of the first
trading date to be reported under MiFIR or earlier than 5 years before the
current date."
*/
declare
  k_0280 mk.ii%type;
  k_UNKN mk.ii%type:=0; -- unknown
  rt timestamp:=
    greatest(add_months(sys_extract_utc(systimestamp), -5*12)
    , to_timestamp(s.pg('m.modul-start-day'), s.FMT_ISOTIME)); -- reference
                                                               -- time
begin
  select ii into k_0280 from mk where md_=\D
  and id='0280';
  \i
  \s
    \k
  , 'Trade date is too far in the past'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , (
      select h_ from \e where mk_=k_UNKN
      minus select h_ from \e where mk_=k_0280
    ) x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)<rt;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0290
/*
CON-290: "When using 'DEAL' Buyer Identification or Seller Identification or
both of them should be identical with the executing entity identification
code."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'When using ''DEAL'' either Buyer or Seller should be identical with'
    ||' the executing entity identification code'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , rtraf10_21 r21
  , rtraf10_21h h21
  , rtraf10_23 r23
  , rtraf10_23h h23
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=h21.h_
  and h21.r_=r21.ii
  and h.ii=h23.h_
  and h23.r_=r23.ii
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0042='DEAL'
  and nvl(r21.FIM0004, s.NA)!=r.R0012
  and nvl(r23.FIM0021, s.NA)!=r.R0012;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0310
/*
CON-310: "A valid ISO 4217 currency code that was active at the trading date
or is a pre-EURO currency should be populated.
The following special currency codes are not allowed: XAG, XAU, XBA, XBB,
XBC, XBD, XDR, XEU, XFU, XPD, XPT, XXX."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Currency code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q0 as (
        select /*+ materialize */
          h.ii
        , FIM0045
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0045 is not null
      ), q1 as (
        select /*+ materialize */
          h.ii
        , FIM0047
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0047 is not null
      )
      select * from (
select ii from q0
minus
select q0.ii
from
  q0
, er
where q0.FIM0045=er.id
and (
  q0.fim0041 between er.vf and er.vt
  or er.p=s.GC_YES
) and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
      union
      select * from (
select ii from q1
minus
select q1.ii
from
  q1
, er
where q1.FIM0047=er.id
and (
  q1.fim0041 between er.vf and er.vt
  or er.p=s.GC_YES
) and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0340
/*
CON-340: "A valid ISO 4217 currency code that was active at the trading date
should be populated.
The following special currency codes are not allowed: XAG, XAU, XBA, XBB,
XBC, XBD, XDR, XEU, XFU, XPD, XPT, XXX. "
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Currency code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q0 as (
        select /*+ materialize */
          h.ii
        , FIM0050
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0050 is not null
      ), q1 as (
        select /*+ materialize */
          h.ii
        , FIM0056
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0056 is not null
      )
      select * from (
select ii from q0
minus
select q0.ii
from
  q0
, er
where q0.FIM0050=er.id
and q0.fim0041 between er.vf and er.vt
and er.p=s.GC_NO
and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
      union
      select * from (
select ii from q1
minus
select q1.ii
from
  q1
, er
where q1.FIM0056=er.id
and q1.fim0041 between er.vf and er.vt
and er.p=s.GC_NO
and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0360
/*
CON-360: "The reported code should be a valid ISO 10383 MIC code that was
active at the trading date"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Venue MIC is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0058
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0058 is not null
        and FIM0058 not in ('XOFF', 'XXXX')
      )
      select ii from q
      minus
      select q.ii from
        q
      , em
      where q.FIM0058=em.id
      and q.fim0041 between em.vf and em.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0370
/*
CON-370: "This field is mandatory where: Field 36 is an EEA trading venue or
trading platform outside the Union"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Country of branch membership is missing'
  , \aTRAF10_11
  , ii
  from (
      select distinct h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      , em
      , ey
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0058=em.id
      and em.y=ey.id
      and to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
        between ey.vf and ey.vt
      and (
        ey.eea=s.GC_NO
        or (
          ey.eea=s.GC_YES
          and em.c!='S'
        )
      )
      -- and em.id not in ('XOFF', 'XXXX') <-- 20180911 deleted from em
      and FIM0059 is null
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0371
/*
CON-371: "If populated, it should be an ISO 3166 country code that was valid
at the trading date"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Country code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0059
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0059 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , ey
      where q.FIM0059=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0390
/*
CON-390: "A valid ISO 4217 currency code that was active at the trading date
should be populated.
The following special currency codes are not allowed: XAG, XAU, XBA, XBB,
XBC, XBD, XDR, XEU, XFU, XPD, XPT, XXX."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Currency code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0061
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0061 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , er
      where q.FIM0061=er.id
      and q.fim0041 between er.vf and er.vt
      and er.p=s.GC_NO
      and regexp_instr(er.id, e.RX_EXCLCURRS)=0
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0410
/*
CON-410: "The check digit of the ISIN code should be valid according to the
algorithm of ISIN validation, as defined in the ISO 6166:2013 standard,
Annex C Formula for computing the modulus 10 double-add-double" check
digit."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The ISIN code is invalid'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0065||FIM0066 is not null
  and e.is_isin(FIM0065||FIM0066)=s.GC_NO;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0570
/*
CON-570: "Field must be always populated where the entity deals on own
account (i.e. value 'DEAL' populated in field 29) and the firm does not act
as a receiving firm (fields 26 or 27 are not populated)."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Investment decision identifier is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0042='DEAL'
  and FIM0039 is null
  and FIM0040 is null
  and FIM0123 is null
  and FIM0126 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0571
/*
CON-571: "Field should not be populated in reports where the transmitting
firm identification code is not populated for the buyer or seller (fields 26
and 27) and the firm deals on a matched principal capacity (i.e. value
'MTCH' populated in field 29) or an any other capacity (i.e. value 'AOTC'
populated in field 29) unless the decision maker field is populated with the
LEI of the executing entity."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Investment decision identifier should not be populated in reports'
    ||' where the firm deals on a matched principal capacity or an any'
    ||' other capacity unless the decision maker field is populated with'
    ||' the LEI of the executing firm.'
  , \aTRAF10_11
  , ii
  from (
      select h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0042 in ('AOTC', 'MTCH')
      and FIM0039 is null
      and FIM0040 is null
      and (
        FIM0123 is not null
        or FIM0126 is not null
      )
      minus
      select h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , rtraf10_22 r22
      , rtraf10_22h h22
      , \e x
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=h22.h_
      and h22.r_=r22.ii
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and r.R0012=r22.FIM0014
      minus
      select h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , rtraf10_24 r24
      , rtraf10_24h h24
      , \e x
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=h24.h_
      and h24.r_=r24.ii
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and r.R0012=r24.FIM0031
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0572
/*
CON-572: "Investment decision identifier should not be populated in reports
where the firm deals on a matched principal capacity (i.e. value 'MTCH'
populated in field 29) or an any other capacity (i.e. value 'AOTC' populated
in field 29) and the transmitting firm identification code is populated for
the buyer and/or seller unless the decision maker field is populated with
the LEI of the transmitting firm."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , ' Investment decision identifier should not be populated in reports'
    ||' where the firm deals on a matched principal capacity or an any'
    ||' other capacity and the transmitting firm identification code is'
    ||' populated for the buyer and/or seller unless the decision maker'
    ||' field is populated with the LEI of the transmitting firm.'
  , \aTRAF10_11
  , ii
  from (
      select h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0042 in ('AOTC', 'MTCH')
      and (
        FIM0039 is not null
        or FIM0040 is not null
      )
      and (
        FIM0123 is not null
        or FIM0126 is not null
      )
      minus
      select h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , rtraf10_22 r22
      , rtraf10_22h h22
      , \e x
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=h22.h_
      and h22.r_=r22.ii
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0039=r22.FIM0014
      minus
      select h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , rtraf10_24 r24
      , rtraf10_24h h24
      , \e x
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=h24.h_
      and h24.r_=r24.ii
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0040=r24.FIM0031
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0573
/*
CON-573: "If national identifier is used, the first 2 characters of the
national identification code should be an ISO 3166 country code that was
valid at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The national identification code does not include valid country code'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , substr(FIM0123, 1, 2) fim0123
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0123 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , ey
      where q.fim0123=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0574
/*
CON-574: "In case the CONCAT code is used, the following characters are only
allowed: Capital Latin letters, Numbers, #. It should be a string of exactly
20 characters where first two characters are letters, the next 8 characters
are numbers and the remaining characters are letters or #, where 11th and
16th character are letters.
In case national identification number or passport number is used, the
following characters are only allowed: capital Latin letters (A-Z), numbers
(0-9) as well as '+' and '-' in case the identifier starts with "FI" and "-"
when the identifier starts with "LV". It can be a string of 3 to 35
characters, where first two characters are letters."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The format of the executing person code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      FIM0125='CONCAT'
      and nvl(regexp_instr(FIM0123, e.RX_CONCAT), 0)=0
    ) or (
      FIM0124 in ('CCPT', 'NIDN')
      and nvl(regexp_instr(FIM0123, e.RX_NIDNCCPT), 0)=0
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0580
/*
CON-580: "If populated, it should be an ISO 3166 country code that was valid
at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Country code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0122
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0122 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , ey
      where q.FIM0122=ey.id
      and q.fim0041 between ey.vf and ey.vt
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0590
/*
CON-590: "If national identifier is used, the first 2 characters of the
national identification code should be an ISO 3166 country code that was
valid at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The national identification code does not include valid country code'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , substr(FIM0128, 1, 2) fim0128
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0128 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , ey
      where q.fim0128=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0591
/*
CON-591: "In case the CONCAT code is used, the following characters are only
allowed: Capital Latin letters, Numbers, #. It should be a string of exactly
20 characters where first two characters are letters, the next 8 characters
are numbers and the remaining characters are letters or #, where 11th and
16th character are letters.
In case national identification number or passport number is used, the
following characters are only allowed: capital Latin letters (A-Z), numbers
(0-9) as well as '+' and '-' in case the identifier starts with "FI" and "-"
when the identifier starts with "LV". It can be a string of 3 to 35
characters, where first two characters are letters."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The format of the executing person code is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      FIM0130='CONCAT'
      and nvl(regexp_instr(FIM0128, e.RX_CONCAT), 0)=0
    ) or (
      FIM0129 in ('CCPT', 'NIDN')
      and nvl(regexp_instr(FIM0128, e.RX_NIDNCCPT), 0)=0
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 110 0600
/*
CON-600: "If populated, it should be an ISO 3166 country code that was valid
at the trading date."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Country code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0127
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0127 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , ey
      where q.FIM0127=ey.id
      and q.fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 120 0030
/*
CON-030: "This field must not be populated where field 36 is populated with
a non-EEA Trading Venue or 'XXXX' or 'XOFF'. "
The first item of the Set 2.
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  declare
    e varchar2(32767 byte); -- expression
    skr stp.id%type:='__\e.030.skr'; -- save check result
                                     -- after check No. 600
  begin
    if s.pg(skr, s.GC_NO) is null then s.ps(skr, '0');
      select xt into e from me
      where id='m.save-check-results-T10-general';
      e:=replace(e, s.BH||'C0', 'true');
      e:=replace(e, s.BH||'K', '\K');
      e:=replace(e, s.BH||'S', 'RJCT');
      e:=replace(e, s.BH||'a', '\aTRAF10_11');
      e:=replace(e, s.BH||'e', '\e');
      e:=replace(e, s.BH||'t', 'rtraf10_11');
      e:=replace(e, s.BH||'x', null);
      s.uei(e); commit; end if;
    s.pd(skr);
  end;
  \i
  \s
    \k
  , 'Trading venue transaction identification code is inconsistent with'
    ||' the trading venue'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0058
        , to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
          fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0058 is not null
        and FIM0063 is not null
      )
      select ii from q where FIM0058 in ('XXXX', 'XOFF')
      union
      select ii
      from
        q
      , em
      , ey
      where FIM0058=em.id
      and fim0041 between em.vf and em.vt
      and em.y=ey.id
      and fim0041 between ey.vf and ey.vt
      and (
        (
          em.c in ('M', 'O', 'R')
          and ey.eea=s.GC_NO
        ) or em.c='S'
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 120 0610
/*
CON-610: "This field should NOT be populated where:
1. Field 36 is 'XOFF', 'XXXX' or a non-EEA venue"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Waiver indicator is inconsistent with trading venue'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0058
        , to_timestamp(translate(FIM0041, 'TZ', '+'), s.FMT_ISOSTAMPTZ)
          fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_41 r41
        , rtraf10_41h h41
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h41.h_
        and h41.r_=r41.ii
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0058 is not null
        and r41.FIM0133 is not null
      )
      select ii from q where FIM0058 in ('XXXX', 'XOFF')
      union
      select ii
      from
        q
      , em
      , ey
      where FIM0058=em.id
      and em.c in ('M', 'O', 'R', 'S')
      and fim0041 between em.vf and em.vt
      and em.y=ey.id
      and ey.eea=s.GC_NO
      and fim0041 between ey.vf and ey.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 130 0411
/*
CON-411: "The instrument should be present in reference data for the trading
date for the following transactions:
1. The transaction is executed on a trading venue within EEA (MIC code in
field 36 belongs to a trading venue within the EEA);
2. The transaction is an OTC transaction in instrument admitted to trading
(field 36 populated with 'XOFF') and field 47 and 48 are not populated;
3. The transaction is executed on a SI or an organised trading platform
outside EEA and field 47 and 48 are not populated.
In the case of transactions on EEA venues (point 1 above), the instrument
should be valid on the particular trading venue where the transaction was
executed. In the case of the other transactions (point 2 and 3), the
instrument should be valid on any (at least one) EEA trading venue or SI.
If no more than 7 days has passed after the transaction submission (i.e. it
is the 7th day or before) the transaction shall be pending."
The first item of the Set 3.
Part 1 of 2. Fragment 1 of 2.
*/
declare
  T_0411 constant char(29):='Pending instrument validation';
  T_0412 constant char(61):=
    'Instrument is not valid in reference data on transaction date';
  k_UNKN mk.ii%type:=0; -- unknown
  rd date:=trunc(sysdate)-s.pg('m.t10-waiting-time'); -- reference date
begin
  declare
    c char(1); -- dummy
    e varchar2(32767 byte); -- expression
    skr stp.id%type:='__\e.411.skr'; -- save check result
                                     -- after check No. 610
  begin
    if '\m'=m.PM_CHECKER and s.pg(skr, s.GC_NO) is null then s.ps(skr, '0');
      select xt into e from me
      where id='m.save-check-results-T10-general';
      e:=replace(e, s.BH||'C0', 'true');
      e:=replace(e, s.BH||'K', '\K');
      e:=replace(e, s.BH||'S', 'RJCT');
      e:=replace(e, s.BH||'a', '\aTRAF10_11');
      e:=replace(e, s.BH||'e', '\e');
      e:=replace(e, s.BH||'t', 'rtraf10_11');
      e:=replace(e, s.BH||'x', null);
      s.uei(e); commit; end if;
    s.pd(skr);
    begin select 'x' into c from md where ii=\D
      and id='TRAFIM10';
    exception when no_data_found then :1:=0; return; end;
  end;
  \i
  \s
    case when fim0041<rd then \k0412 else \k0411 end
  , case when fim0041<rd then T_0412 else T_0411 end
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) fim0041
        , FIM0058
        , FIM0065
        , FIM0073||FIM0074||FIM0075||FIM0076||FIM0085
          ||FIM0086||FIM0087||FIM0088||FIM0097||FIM0098
          ||FIM0099||FIM0100||r31.FIM0079||r32.FIM0080||r32.FIM0081
          ||r32.FIM0082||r33.FIM0091||r34.FIM0092||r34.FIM0093||r34.FIM0094
          ||r35.FIM0103||r36.FIM0104||r36.FIM0105||r36.FIM0106 x
        from
          rtraf10_11 r
        , rtraf10_11h h
        , rtraf10_31 r31
        , rtraf10_31h h31
        , rtraf10_32 r32
        , rtraf10_32h h32
        , rtraf10_33 r33
        , rtraf10_33h h33
        , rtraf10_34 r34
        , rtraf10_34h h34
        , rtraf10_35 r35
        , rtraf10_35h h35
        , rtraf10_36 r36
        , rtraf10_36h h36
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=h31.h_(+)
        and h31.r_=r31.ii(+)
        and h.ii=h32.h_(+)
        and h32.r_=r32.ii(+)
        and h.ii=h33.h_(+)
        and h33.r_=r33.ii(+)
        and h.ii=h34.h_(+)
        and h34.r_=r34.ii(+)
        and h.ii=h35.h_(+)
        and h35.r_=r35.ii(+)
        and h.ii=h36.h_(+)
        and h36.r_=r36.ii(+)
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0058!='XXXX'
        and FIM0065 is not null
      )

MKT20180103 TRAFIM10 130 0411
/*
CON-411: Part 2 of 2.
*/
      select
        ii
      , fim0041
      from q
      minus
      select
        q0.ii
      , q0.fim0041
      from
        q q0
      , em
      , ey
      , eim
      where q0.FIM0058=em.id
      and q0.fim0041 between em.vf and em.vt
      and em.c in ('M', 'O', 'R')
      and em.y=ey.id
      and q0.fim0041 between ey.vf and ey.vt
      and ey.eea=s.GC_YES
      and q0.FIM0065=eim.id
      and em.id=eim.m
      and q0.fim0041 between eim.vf and eim.vt
      minus
      select
        q0.ii
      , q0.fim0041
      from (
          select q0.*
          from q q0
          where q0.FIM0058='XOFF'
          and q0.x is null
          union all
          select q0.*
          from
            q q0
          , em
          , ey
          where q0.x is null
          and q0.FIM0058=em.id
          and q0.fim0041 between em.vf and em.vt
          and em.y=ey.id
          and q0.fim0041 between ey.vf and ey.vt
          and (
            (
              em.c in ('M', 'O', 'R', 'S')
              and ey.eea=s.GC_YES
            ) or ey.eea=s.GC_NO
          )
        ) q0
      , em
      , ey
      , eim
      where q0.FIM0065=eim.id
      and q0.fim0041 between eim.vf and eim.vt
      and eim.m=em.id
      and q0.fim0041 between em.vf and em.vt
      and em.y=ey.id
      and q0.fim0041 between ey.vf and ey.vt
      and (
        (
          em.c!='S'
          and ey.eea=s.GC_YES
        ) or em.c='S'
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 130 0412
/*
CON-412: "The instrument should be present in reference data for the trading
date for the following transactions:
1. The transaction is executed on a trading venue within EEA (MIC code in
field 36 belongs to a trading venue within the EEA);
2. The transaction is an OTC transaction in instrument admitted to trading
(field 36 populated with 'XOFF') and field 47 and 48 are not populated;
3. The transaction is executed on a SI or an organised trading platform
outside EEA and field 47 and 48 are not populated.
In the case of transactions on EEA venues (point 1 above), the instrument
should be valid on the particular trading venue where the transaction was
executed. In the case of the other transactions (point 2 and 3), the
instrument should be valid on any (at least one) EEA trading venue or SI.
If at least 7 days has passed after the transaction submission (i.e. it is
the 8th day after the submission) the transaction shall be rejected."
Fragment 2 of 2.
*/

MKT20180103 TRAFIM10 140 0331
/*
CON-331: "In case the CFI code is available in the instrument reference data
(transaction in an instruments admitted to trading):
For debt instruments, i.e. where CFI of the instrument in the reference data
is D*****, where the quantity is reported in unit terms, the reported price
shall not be in percentage terms. "
The first item of the Set 4.
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  declare
    e varchar2(32767 byte); -- expression
    skr stp.id%type:='__\e.331.skr'; -- save check result
                                     -- after check No. 412
  begin
    if s.pg(skr, s.GC_NO) is null then s.ps(skr, '0'); -- unique 411/PDNG
      delete \e
      where mk_ in (
          select ii from mk where md_=\D
          and id='0411'
        )
      and h_ in (
          select f.h_
          from
            rtraf10_11h h
          , rtraf10_11f f
          where h.mi_=\u
          and h.ii=f.h_
          and f.s='PDNG'
        ); commit; end if;
    if s.pg(skr)='0' then s.ps(skr, '1');
      select xt into e from me
      where id='m.save-check-results-T10-general';
      e:=replace(e, s.BH||'C0', 'true');
      e:=replace(e, s.BH||'K', '\K');
      e:=replace(e, ''''||s.BH||'S'''
        , 'case when x(i).kid=''0411'' then ''PDNG'' else ''RJCT'' end');
      e:=replace(e, s.BH||'a', '\aTRAF10_11');
      e:=replace(e, s.BH||'e', '\e');
      e:=replace(e, s.BH||'t', 'rtraf10_11');
      e:=replace(e, s.BH||'x', null);
      s.uei(e); commit; end if;
    s.pd(skr);
  end;
  \i
  \s
    \k
  , 'Price notation is incorrect'
  , \aTRAF10_11
  , ii
  from (
      select distinct h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      , eim
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0065=eim.id
      and to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) between eim.vf
        and eim.vt
      and substr(eim.c, 1, 1)='D'
      and FIM0043 is not null
      and FIM0052 is not null
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 140 0351
/*
CON-351: "In case the CFI code is available in the instrument reference data
(transaction in an instruments admitted to trading):
The field is mandatory for debt instruments, i.e. CFI of the instrument in
the reference data is DB**** (Bonds)"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Net amount is missing'
  , \aTRAF10_11
  , ii
  from (
      select distinct h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      , eim
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0065=eim.id
      and to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) between eim.vf
        and eim.vt
      and substr(eim.c, 1, 2)='DB'
      and FIM0057 is null
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 140 0381
/*
CON-381: "In case the CFI code is available in the instrument reference data
(transaction in an instruments admitted to trading):
Field is mandatory where the CFI of the instrument in the reference data is
SC**** (CDS)"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Up-front payment is missing'
  , \aTRAF10_11
  , ii
  from (
      select distinct h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      , eim
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0065=eim.id
      and to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) between eim.vf
        and eim.vt
      and substr(eim.c, 1, 2)='SC'
      and (
        FIM0060 is null
        or FIM0062 is null
      )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 140 0640
/*
CON-640: "Field is mandatory for commodity derivative transaction where the
instrument reported in field 41 is classified in instrument reference data
as commodity derivative (i.e. field 4 in instrument reference data is
populated with 'true')."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Commodity derivative indicator is missing'
  , \aTRAF10_11
  , ii
  from (
      select distinct h.ii
      from
        rtraf10_11 r
      , rtraf10_11h h
      , \e x
      , eim
      where r.ii=h.r_
      and h.mi_=\u
      and h.ii=x.h_
      and x.mk_=k_UNKN
      and FIM0065=eim.id
      and to_date(substr(FIM0041, 1, 10), s.FMT_ISODATE) between eim.vf
        and eim.vt
      and eim.cd=s.GC_YES
      and FIM0136 is null
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 150 0470
/*
CON-470: "The check digit of the ISIN code should be valid according to the
algorithm of ISIN validation, as defined in the ISO 6166:2013 standard,
Annex C Formula for computing the modulus 10 double-add-double check digit."
The first item of the Set 5.
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The ISIN code '||x||' is invalid'
  , \aTRAF10_11
  , ii
  from (
      with h as (
        select /*+ materialize */ h.*
        from
          rtraf10_11h h
        , \e x
        where h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
      )
      select
        ii
      , listagg(x, ', ') within group (order by x) as x
      from (
          select
            h.ii
          , FIM0073 x
          from
            rtraf10_11 r
          , h
          where h.r_=r.ii
          and FIM0073 is not null
          and e.is_isin(FIM0073)=s.GC_NO
          union all
          select
            h.ii
          , FIM0074
          from
            rtraf10_11 r
          , h
          where h.r_=r.ii
          and FIM0074 is not null
          and e.is_isin(FIM0074)=s.GC_NO
          union all
          select
            h.ii
          , FIM0085
          from
            rtraf10_11 r
          , h
          where h.r_=r.ii
          and FIM0085 is not null
          and e.is_isin(FIM0085)=s.GC_NO
          union all
          select
            h.ii
          , FIM0086
          from
            rtraf10_11 r
          , h
          where h.r_=r.ii
          and FIM0086 is not null
          and e.is_isin(FIM0086)=s.GC_NO
          union all
          select
            h.ii
          , FIM0097
          from
            rtraf10_11 r
          , h
          where h.r_=r.ii
          and FIM0097 is not null
          and e.is_isin(FIM0097)=s.GC_NO
          union all
          select
            h.ii
          , FIM0098
          from
            rtraf10_11 r
          , h
          where h.r_=r.ii
          and FIM0098 is not null
          and e.is_isin(FIM0098)=s.GC_NO
          union all
          select
            h.ii
          , r31.FIM0079
          from
            rtraf10_31 r31
          , rtraf10_31h h31
          , h
          where h.ii=h31.h_
          and h31.r_=r31.ii
          and r31.FIM0079 is not null
          and e.is_isin(r31.FIM0079)=s.GC_NO
          union all
          select
            h.ii
          , r32.FIM0080
          from
            rtraf10_32 r32
          , rtraf10_32h h32
          , h
          where h.ii=h32.h_
          and h32.r_=r32.ii
          and r32.FIM0080 is not null
          and e.is_isin(r32.FIM0080)=s.GC_NO
          union all
          select
            h.ii
          , r33.FIM0091
          from
            rtraf10_33 r33
          , rtraf10_33h h33
          , h
          where h.ii=h33.h_
          and h33.r_=r33.ii
          and r33.FIM0091 is not null
          and e.is_isin(r33.FIM0091)=s.GC_NO
          union all
          select
            h.ii
          , r34.FIM0092
          from
            rtraf10_34 r34
          , rtraf10_34h h34
          , h
          where h.ii=h34.h_
          and h34.r_=r34.ii
          and r34.FIM0092 is not null
          and e.is_isin(r34.FIM0092)=s.GC_NO
          union all
          select
            h.ii
          , r35.FIM0103
          from
            rtraf10_35 r35
          , rtraf10_35h h35
          , h
          where h.ii=h35.h_
          and h35.r_=r35.ii
          and r35.FIM0103 is not null
          and e.is_isin(r35.FIM0103)=s.GC_NO
          union all
          select
            h.ii
          , r36.FIM0104
          from
            rtraf10_36 r36
          , rtraf10_36h h36
          , h
          where h.ii=h36.h_
          and h36.r_=r36.ii
          and r36.FIM0104 is not null
          and e.is_isin(r36.FIM0104)=s.GC_NO
        )
      group by ii
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 150 0473
/*
CON-473: "For swap transactions where field 43 is populated with S*****, at
least one swap leg consisting of a single instrument, an index or a basket
should be reported with the direction indication (i.e. the underlying must
be reported using the UndrlygInstrm\Swp\SwpIn or UndrlygInstrm\Swp\SwpOut
XML elements).
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'No underlying reported for swap transaction.'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  , rtraf10_31 r31
  , rtraf10_31h h31
  , rtraf10_32 r32
  , rtraf10_32h h32
  , rtraf10_33 r33
  , rtraf10_33h h33
  , rtraf10_34 r34
  , rtraf10_34h h34
  where substr(FIM0068, 1, 1)='S'
  and FIM0073 is null
  and FIM0074 is null
  and FIM0085 is null
  and FIM0086 is null
  and r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and h.ii=h31.h_(+)
  and h31.r_=r31.ii(+)
  and r31.FIM0079 is null
  and h.ii=h32.h_(+)
  and h32.r_=r32.ii(+)
  and r32.FIM0080 is null
  and h.ii=h33.h_(+)
  and h33.r_=r33.ii(+)
  and r33.FIM0091 is null
  and h.ii=h34.h_(+)
  and h34.r_=r34.ii(+)
  and r34.FIM0092 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 160 0361
/*
CON-361: "If the instrument reported in field 41 is present in reference
data, 'XXXX' cannot be reported in field 36."
The first item of the Set 6.
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'The reported MIC code is incorrect'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0065
        , FIM0066
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0058='XXXX'
      )
      select ii from q where FIM0065 is not null
      union all
      select ii
      from
        q
      , eim
      where q.FIM0066=eim.id
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 160 0471
/*
CON-471: Part 2 of 2.
*/
  if s.pg(sn)='7' then if s.uox('\eT2')=s.GC_NO then s.ued('
create table \eT2 as
\s *
from (
    with q0 as (
      select /*+ materialize */ t.*
      from
        \eT1 t
      , em
      , ey
      where t.FIM0058=em.id
      and em.c in (''M'', ''O'', ''R'', ''S'')
      and t.fim0041 between em.vf and em.vt
      and em.y=ey.id
      and ey.eea=''N'' /* s.GC_NO */
      and t.fim0041 between ey.vf and ey.vt
    ), q1 as (select /*+ materialize */ * from \eT1 where FIM0058=''XOFF'')
    select * from q0 union select * from q1
  )'); end if; p('8'); end if;
  if s.pg(sn)='8' then if s.uox('\eT3')=s.GC_NO then s.ued('
create table \eT3 as
\s
  h_
, fim0041
, x
from (
    select * from \eT2
    minus
    select t.*
    from
      \eT2 t
    , eim
    where t.FIM0066=eim.id
    and t.fim0041 between eim.vf and eim.vt
    union
    select * from \eT1 where FIM0058=''XXXX''
  )'); end if; p('9'); end if;
  if s.pg(sn)='9' then execute immediate '
declare
  T_0471 constant char(42):=''Pending underlying instrument x validation'';
  T_0472 constant char(74):=''Underlying instrument x is not valid in ''
    ||''reference data on transaction date'';
  ec stp.id%type:=''__\e.471.ec''; -- error count
  rd date:=trunc(sysdate)-s.pg(''m.t10-waiting-time''); -- reference date
begin
  \i
  \s
    case when fim0041<rd then \k0472 else \k0471 end
  , replace(case when fim0041<rd then T_0472 else T_0471 end, ''x'', x)
  , \aTRAF10_11
  , h_
  from (
      select
        h_
      , fim0041
      , listagg(x, '', '') within group (order by x) as x
      from (
          select * from \eT3
          minus
          select t.*
          from
            \eT3 t
          , eim
          where t.x=eim.id
          and t.fim0041 between eim.vf and eim.vt
        )
      group by
        h_
      , fim0041
    ); s.ps(ec, sql%rowcount);
end;'; p('a'); end if;
  if s.pg(sn)='a' then
    if s.uox('\eT3')=s.GC_YES then s.ued('drop table \eT3'); end if;
    if s.uox('\eT2')=s.GC_YES then s.ued('drop table \eT2'); end if;
    if s.uox('\eT1')=s.GC_YES then s.ued('drop table \eT1'); end if;
    if s.uox('\eT0')=s.GC_YES then s.ued('drop table \eT0'); end if;
    p('b'); end if;
  if s.pg(sn)='b' then :1:=s.pg(ec); s.pd(ec); s.pd(sn); end if;
end;

MKT20180103 TRAFIM10 160 0471
/*
CON-471:
"The underlying instrument should be present in reference data for the
trading date for the following transactions:
1. The transaction is an OTC transaction:
a) field 36 is populated with 'XXXX'; and b) the underlying instrument is
not an index;
2. The transaction was executed on a non-EEA organised trading platform:
a) field 36 is populated with a non-EEA MIC code or 'XOFF'; and b) the
instrument (field 41) is not populated or populated with an instrument that
is not present in the reference data; and
c) the underlying instrument is not an index;
In the case of baskets, if more than one basket constituent is populated,
all of them should be present in the instrument reference data.
Where the submitting entity populates not only the reportable components but
also non-reportable ones, the report is pended and, after the 7 calendar
days, rejected (unless the non-reportable components become reportable
within that 7 calendar day period).
If no more than 7 days has passed after the transaction submission (i.e. it
is the 7th day or before) the transaction shall be pending."
Part 1 of 2.
*/
declare
  ec stp.id%type:='__\e.471.ec'; -- error count
  k_UNKN mk.ii%type:=0; -- unknown
  sn stp.id%type:='__\e.471.sn'; -- step No
  procedure p(sn_ char) is begin s.ps(sn, sn_); commit; end;
begin
  if s.pg(sn, s.GC_NO) is null then
    declare
      c char(1); -- dummy
      e varchar2(32767 byte); -- expression
      skr stp.id%type:='__\e.471.skr'; -- save check result
                                       -- after check No. 361
    begin
      if s.pg(skr, s.GC_NO) is null then s.ps(skr, '0');
        select xt into e from me
        where id='m.save-check-results-T10-general';
        e:=replace(e, s.BH||'C0', 'true');
        e:=replace(e, s.BH||'K', '\K');
        e:=replace(e, s.BH||'S', 'RJCT');
        e:=replace(e, s.BH||'a', '\aTRAF10_11');
        e:=replace(e, s.BH||'e', '\e');
        e:=replace(e, s.BH||'t', 'rtraf10_11');
        e:=replace(e, s.BH||'x', null);
        s.uei(e); commit; end if;
      s.pd(skr);
      begin select 'x' into c from md where ii=\D
        and id='TRAFIM10';
      exception when no_data_found then :1:=0; return; end;
    end;
    p('0'); end if;
  if s.pg(sn)='0' then if s.uox('\eT0')=s.GC_NO then s.ued('
create table \eT0 as
\s
  h.ii h_
, to_date(substr(FIM0041, 1, 10), '''||s.FMT_ISODATE||''') fim0041
, FIM0058
, FIM0066
, r.ii r_
from
  rtraf10_11 r
, rtraf10_11h h
, \e x
where r.ii=h.r_
and h.mi_=\u
and h.ii=x.h_
and x.mk_=0 /* k_UNKN */'); end if; p('1'); end if;
  if s.pg(sn)='1' then if s.uox('\eT1')=s.GC_NO then s.ued('
create table \eT1 as
\s
  t.h_
, t.fim0041
, t.FIM0058
, t.FIM0066
, r.FIM0073 x
from
  \eT0 t
, rtraf10_11 r
where t.r_=r.ii
and r.FIM0073 is not null'); end if; p('2'); end if;
  if s.pg(sn)='2' then s.ued('
\iT1
\s
  t.h_
, t.fim0041
, t.FIM0058
, t.FIM0066
, r.FIM0085
from
  \eT0 t
, rtraf10_11 r
where t.r_=r.ii
and r.FIM0085 is not null'); p('3'); end if;
  if s.pg(sn)='3' then s.ued('
\iT1
\s
  t.h_
, t.fim0041
, t.FIM0058
, t.FIM0066
, r.FIM0097
from
  \eT0 t
, rtraf10_11 r
where t.r_=r.ii
and r.FIM0097 is not null'); p('4'); end if;
  if s.pg(sn)='4' then s.ued('
\iT1
\s
  t.h_
, t.fim0041
, t.FIM0058
, t.FIM0066
, r31.FIM0079
from
  \eT0 t
, rtraf10_31 r31
, rtraf10_31h h31
where t.h_=h31.h_
and h31.r_=r31.ii
and r31.FIM0079 is not null'); p('5'); end if;
  if s.pg(sn)='5' then s.ued('
\iT1
\s
  t.h_
, t.fim0041
, t.FIM0058
, t.FIM0066
, r33.FIM0091
from
  \eT0 t
, rtraf10_33 r33
, rtraf10_33h h33
where t.h_=h33.h_
and h33.r_=r33.ii
and r33.FIM0091 is not null'); p('6'); end if;
  if s.pg(sn)='6' then s.ued('
\iT1
\s
  t.h_
, t.fim0041
, t.FIM0058
, t.FIM0066
, r35.FIM0103
from
  \eT0 t
, rtraf10_35 r35
, rtraf10_35h h35
where t.h_=h35.h_
and h35.r_=r35.ii
and r35.FIM0103 is not null'); p('7'); end if;

MKT20180103 TRAFIM10 160 0472
/*
CON-472: "
The underlying instrument should be present in reference data for the
trading date for the following transactions:
1. The transaction is an OTC transaction:
a) field 36 is populated with 'XXXX'; and
b) the underlying instrument is not an index;
2. The transaction was executed on a non-EEA organised trading platform:
a) field 36 is populated with a non-EEA MIC code or 'XOFF'; and
b) the instrument (field 41) is not populated or populated with an
instrument that is not present in the reference data; and
c) the underlying instrument is not an index;
In the case of baskets, if more than one basket constituent is populated,
all of them should be present in the instrument reference data.
Where the submitting entity populates not only the reportable components but
also non-reportable ones, the report is pended and, after the 7 calendar
days, rejected (unless the non-reportable components become reportable
within that 7 calendar day period).
If at least 7 days has passed after the transaction submission (i.e. it is
the 8th day after the submission) the transaction shall be rejected."
*/

MKT20180103 TRAFIM10 170 0430
/*
CON-430: "The reported CFI should be an allowed CFI according to the ISO
10962:2015."
The first item of the Set 7.
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  declare
    e varchar2(32767 byte); -- expression
    skr stp.id%type:='__\e.430.skr'; -- save check result
                                     -- after check No. 472
  begin
    if s.pg(skr, s.GC_NO) is null then s.ps(skr, '0'); -- unique 471/PDNG
      delete \h \e
      where mk_ in (
          select ii from mk where md_=\D
          and id='0471'
        )
      and h_ in (
          select h_
          from
            rtraf10_11h h
          , rtraf10_11f f
          where h.mi_=\u
          and h.ii=f.h_
          and f.s='PDNG'
        ); commit; end if;
    if s.pg(skr)='0' then s.ps(skr, '1');
      select xt into e from me
      where id='m.save-check-results-T10-general';
      e:=replace(e, s.BH||'C0', 'true');
      e:=replace(e, s.BH||'K', '\K');
      e:=replace(e, ''''||s.BH||'S'''
        , 'case when x(i).kid=''0471'' then ''PDNG'' else ''RJCT'' end');
      e:=replace(e, s.BH||'a', '\aTRAF10_11');
      e:=replace(e, s.BH||'e', '\e');
      e:=replace(e, s.BH||'t', 'rtraf10_11');
      e:=replace(e, s.BH||'x', null);
      s.uei(e); commit; end if;
    s.pd(skr);
  end;
  \i
  \s
    \k
  , 'Instrument classification identifier is incorrect'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0068
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0068 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , ec
      where q.FIM0068=ec.id
      and q.fim0041 between ec.vf and ec.vt
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 170 0440
/*
CON-440: "A valid ISO 4217 currency code that was active at the trading date
or is a pre-EURO currency should be populated.
The following special currency codes are not allowed: XAG, XAU, XBA, XBB,
XBC, XBD, XDR, XEU, XFU, XPD, XPT, XXX."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Currency code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q as (
        select /*+ materialize */
          h.ii
        , FIM0069
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0069 is not null
      )
      select ii from q
      minus
      select q.ii
      from
        q
      , er
      where q.FIM0069=er.id
      and (
        q.fim0041 between er.vf and er.vt
        or er.p=s.GC_YES
      ) and regexp_instr(er.id, e.RX_EXCLCURRS)=0
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 170 0450
/*
CON-450: "Field can be only populated when field 44 was also populated."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Notional currency 2 was populated but Notional currency 1 is missing.'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0069 is null
  and FIM0121 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 170 0451
/*
CON-451: "A valid ISO 4217 currency code that was active at the trading date
or is a pre-EURO currency should be populated.
The following special currency codes are not allowed: XAG, XAU, XBA, XBB,
XBC, XBD, XDR, XEU, XFU, XPD, XPT, XXX."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Currency code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q0 as (
        select /*+ materialize */
          h.ii
        , FIM0120
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0120 is not null
      ), q1 as (
        select /*+ materialize */
          h.ii
        , FIM0121
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0121 is not null
      )
      select * from (
select ii from q0
minus
select q0.ii
from
  q0
, er
where q0.FIM0120=er.id
and (
  q0.fim0041 between er.vf and er.vt
  or er.p=s.GC_YES
) and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
      union
      select * from (
select ii from q1
minus
select q1.ii
from
  q1
, er
where q1.FIM0121=er.id
and (
  q1.fim0041 between er.vf and er.vt
  or er.p=s.GC_YES
) and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 170 0520
/*
CON-520: "A valid ISO 4217 currency code that was active at the trading date
should be populated.
The following special currency codes are not allowed: XAG, XAU, XBA, XBB,
XBC, XBD, XDR, XEU, XFU, XPD, XPT, XXX."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Currency code is not valid for the trade date'
  , \aTRAF10_11
  , ii
  from (
      with q0 as (
        select /*+ materialize */
          h.ii
        , FIM0111
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0111 is not null
      ), q1 as (
        select /*+ materialize */
          h.ii
        , FIM0117
        , trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
            , s.FMT_ISOSTAMPTZ)) fim0041
        from
          rtraf10_11 r
        , rtraf10_11h h
        , \e x
        where r.ii=h.r_
        and h.mi_=\u
        and h.ii=x.h_
        and x.mk_=k_UNKN
        and FIM0117 is not null
      )
      select * from (
select ii from q0
minus
select q0.ii
from
  q0
, er
where q0.FIM0111=er.id
and (
  q0.fim0041 between er.vf and er.vt
  or er.p=s.GC_YES
) and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
      union
      select * from (
select ii from q1
minus
select q1.ii
from
  q1
, er
where q1.FIM0117=er.id
and (
  q1.fim0041 between er.vf and er.vt
  or er.p=s.GC_YES
) and regexp_instr(er.id, e.RX_EXCLCURRS)!=1
        )
    );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 170 0541
/*
CON-541: "The maturity date should be equal or later than the trading day
(field 28)."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Maturity date is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0070 is not null
  and FIM0070<trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
      , s.FMT_ISOSTAMPTZ));
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 170 0552
/*
CON-552: "The expiry date should be equal or later than the trading day
(field 28)."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Expiry date is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0071 is not null
  and FIM0071<trunc(to_timestamp(translate(FIM0041, 'TZ', '+')
      , s.FMT_ISOSTAMPTZ));
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0330
/*
CON-330: "In case the CFI code is reported in field 43 (transaction in a
financial instrument that is not admitted to trading):
For debt instruments, i.e. CFI reported in field 43 is D*****, where the
quantity is reported in unit terms, the reported price shall not be in
percentage terms. "
The first item of the Set 8.
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  declare
    e varchar2(32767 byte); -- expression
    skr stp.id%type:='__\e.330.skr'; -- save check result
                                     -- after check No. 552
  begin
    if s.pg(skr, s.GC_NO) is null then s.ps(skr, '0');
      select xt into e from me
      where id='m.save-check-results-T10-general';
      e:=replace(e, s.BH||'C0', 'true');
      e:=replace(e, s.BH||'K', '\K');
      e:=replace(e, s.BH||'S', 'RJCT');
      e:=replace(e, s.BH||'a', '\aTRAF10_11');
      e:=replace(e, s.BH||'e', '\e');
      e:=replace(e, s.BH||'t', 'rtraf10_11');
      e:=replace(e, s.BH||'x', ' and mk.id=''0430''');
      s.uei(e); commit; end if;
    s.pd(skr);
  end;
  \i
  \s
    \k
  , 'Price notation is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and substr(FIM0068, 1, 1)='D'
  and FIM0043 is not null
  and FIM0052 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0350
/*
CON-350: "In case the CFI code is reported in field 43 (transaction in a
financial instrument that is not admitted to trading):
The field is mandatory for debt instruments, i.e. CFI reported in field 43
is DB**** (Bonds)"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Net amount is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and substr(FIM0068, 1, 2)='DB'
  and FIM0057 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0380
/*
CON-380: "In case the CFI code is reported in field 43 (transaction in a
derivative instrument that is not admitted to trading):
Field is mandatory where the CFI reported in field 43 is SC**** (CDS)"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Up-front payment is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and substr(FIM0068, 1, 2)='SC'
  and (
    FIM0060 is null
    or FIM0062 is null
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0431
/*
CON-431: "The instrument classification should be consistent with option
style (field 53):
1. If field 53 = EURO, then one of the following CFI codes should be used:
O*E***, H**A**, H**D**, H**G**
2. If field 53 = AMER, then one of the following CFI codes should be used:
O*A***, H**B**, H**E**, H**H**
3. If field 53 = BERM, then one of the following CFI codes should be used:
O*B***, H**C**, H**F**, H**I**"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'CFI code is inconsistent with option exercise style.'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and FIM0068 is not null
  and (
    (
      FIM0118='EURO'
      and regexp_instr(FIM0068, '^(O.E|H..[ADG]|R[FW]...E)')=0
    ) or (
      FIM0118='AMER'
      and regexp_instr(FIM0068, '^(O.A|H..[BEH]|R[FW]...A)')=0
    ) or (
      FIM0118='BERM'
      and regexp_instr(FIM0068, '^(O.B|H..[CFI]|R[FW]...B)')=0
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0441
/*
CON-441: "Field is mandatory where Field 43 is populated with:
1. Other instruments with the following CFI attributes: O*****, S*****,
C*****, R*****, F*****, E*****, D*****, H*****, J*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Notional Currency 1 is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^[OSCRFEDHJ]')=1
  and FIM0069 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0452
/*
CON-452: "This field is mandatory where field 43 is populated with:
1. Swaps with the following CFI attributes : SF****
2. Futures with the following CFI attributes: FFC***"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Notional Currency 2 is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^(SF|FFC)')=1
  and FIM0120 is null
  and FIM0121 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0453
/*
CON-453: "This field should not be populated where field 43 is populated
with:
1. Options with the following CFI attributes: O**S**, O**D**, O**T**, O**N**
2. Futures with the following CFI attributes: FFS***, FFD***, FFN***,
FFV***, FC****
3. Swaps with the following CFI attributes: ST****
4. Complex options with the following CFI attributes: HT****, HE****, HF****
5. Other classes of instruments with the following CFI attributes: R*****,
E*****, C*****, D*****, J*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Notional currency 2 is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068
    , '^(O..[SDTN]|FF[SDNV]|FC|ST|H[TEF]|[RECDJ])')=1
  and (
    FIM0120 is not null
    or FIM0121 is not null
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0480
/*
CON-480: "This field is mandatory where field 43 is populated with
1. Options with the following CFI attributes : O**I**, O**N**,
2. Futures with the following CFI attributes : FFI***, FFN***"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Direct underlying index name is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  , rtraf10_36 r36
  , rtraf10_36h h36
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and h.ii=h36.h_(+)
  and h36.r_=r36.ii(+)
  and regexp_instr(FIM0068, '^(O..[IN]|FF[IN]|RWI)')=1
  and FIM0099 is null
  and FIM0100 is null
  and r36.FIM0105 is null
  and r36.FIM0106 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0481
/*
CON-481: "This field should not be populated where field 43 is populated
with:
1. Debt instruments with the following CFI attributes: DB****, DT****,
DY****
2. Other instruments with the following CFI attributes: E*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Underlying index name is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  , rtraf10_32 r32
  , rtraf10_32h h32
  , rtraf10_34 r34
  , rtraf10_34h h34
  , rtraf10_36 r36
  , rtraf10_36h h36
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and h.ii=h32.h_(+)
  and h32.r_=r32.ii(+)
  and h.ii=h34.h_(+)
  and h34.r_=r34.ii(+)
  and h.ii=h36.h_(+)
  and h36.r_=r36.ii(+)
  and regexp_instr(FIM0068, '^(D[BTY]|E)')=1
  and FIM0075||FIM0076||FIM0087||FIM0088||FIM0099||FIM0100
      ||r32.FIM0081||r32.FIM0082||r34.FIM0093||r34.FIM0094||r36.FIM0105
      ||r36.FIM0106 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0490
/*
CON-490: "This field is mandatory where field 43 is populated with
1. Options with the following CFI attributes : O**N**
2. Futures with the following CFI attributes : FFN***"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Term of the underlying index is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  , rtraf10_36 r36
  , rtraf10_36h h36
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and h.ii=h36.h_(+)
  and h36.r_=r36.ii(+)
  and regexp_instr(FIM0068, '^(O..N|FFN)')=1
  and (
    nvl2(FIM0101, 1, 0)!=nvl2(FIM0102, 1, 0)
    or nvl2(r36.FIM0107, 1, 0)!=nvl2(r36.FIM0108, 1, 0)
    or FIM0101||FIM0102||r36.FIM0107||r36.FIM0108 is null
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0500
/*
CON-500: "Field is mandatory where field 43 is populated with:
1. Options with the following CFI attributes: O***** or H*****
2. Warrants with the following CFI attributes: RW****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Option type is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^(O|H|RW)')=1
  and FIM0109 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0501
/*
CON-501: "This field should not be populated where field 43 is populated
with:
1. Instruments with the following CFI attributes: F*****, S*****, E*****,
C*****, D*****, J*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Option type is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^[FSECDJ]')=1
  and FIM0109 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0502
/*
CON-502: "If CFI code is
1. 'OC****', 'H**A**', 'H**B**', 'RW**C*' or 'H**C**', then option type
should be 'CALL'
2. 'OP****', 'H**D**', 'H**E**', 'RW**P*' or 'H**F**', then option type
should be 'PUTO'
3. 'OM****', 'H**G**', 'H**H**', 'RW**B*' or 'H**I**', then option type
should be 'OTHR'"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Option type does not match instrument classification'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      regexp_instr(FIM0068, '^(OC|H..[ABC]|RW..C)')=1
      and FIM0109!='CALL'
    ) or (
      regexp_instr(FIM0068, '^(OP|H..[DEF]|RW..P)')=1
      and FIM0109!='PUTO'
    ) or (
      regexp_instr(FIM0068, '^(OM|H..[GHI]|RW..B)')=1
      and FIM0109!='OTHR'
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0510
/*
CON-510: "Field is mandatory where field 43 is populated with:
1. Options with the following CFI attributes: O***** or H*****
2. Warrants with the following CFI attributes: RW****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Strike Price is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^(O|H|RW)')=1
  and FIM0110||FIM0110||FIM0112||FIM0113||FIM0114||FIM0115||FIM0116 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0511
/*
CON-511: "This field should not be populated where field 43 is populated
with:
1. Instruments with the following CFI attributes: F*****, S*****, E*****,
C*****, D*****, J*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Strike price is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^[FSECDJ]')=1
  and FIM0110||FIM0112||FIM0113||FIM0114||FIM0115||FIM0116 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0530
/*
CON-530: "Field is mandatory where field 43 is populated with:
1. Options with the following CFI attributes: O***** or H*****
2. Warrants with the following CFI attributes: RW****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Option exercise style is missing.'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^(O|H|RW)')=1
  and FIM0118 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0531
/*
CON-531: "This field should not be populated where field 43 is populated
with:
1. Instruments with the following CFI attributes: F*****, S*****, E*****,
C*****, D*****, J*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Option exercise style is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^[FSECDJ]')=1
  and FIM0118 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0540
/*
CON-540: "This field is mandatory where field 43 is populated with
1. D*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Maturity date is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and substr(FIM0068, 1, 1)='D'
  and FIM0070 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0542
/*
CON-542: "This field should not be populated where field 43 is populated
with:
1. Instruments with the following CFI attributes: R*****, O*****, F*****,
S*****, E*****, C*****, H*****, J*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Maturity date is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^[ROFSECHJ]')=1
  and FIM0070 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0550
/*
CON-550: "Field is mandatory where field 43 is populated with:
1. Options with the following CFI attributes: 'O*****'
2. Futures with the following CFI attributes: F*****
3. Forwards with the following CFI attributes: JC**F*"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Expiry date is missing'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^(O|F|JC..F)')=1
  and FIM0071 is null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0551
/*
CON-551: "This field is should not be populated where field 43 is populated
with:
1. Instruments with the following CFI attributes: E*****, C*****, D*****"
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Expiry date is not applicable for the given instrument'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and regexp_instr(FIM0068, '^[ECD]')=1
  and FIM0071 is not null;
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 180 0560
/*
CON-560: "Where field 43 is populated with CFI one of the following codes:
OC**P*, OP**P*, FF*P**, FC*P**, SR***P, ST***P, SE***P, SC***P, SF***P,
SM***P, HR***P, HT***P, HE***P, HC***P, HF***P, HM***P, IF***P, JE***P,
JF***P, JC***P, JR***P, JT***P, LL***P, this field should be populated with
'PHYS'.
Where field 43 is populated with CFI one of the following codes: OC**C*,
OP**C, FF*C**, FC*C**, SR***C, ST***C, SE***C, SC***C, SM***C, HR***C,
HT***E, HE***C, HC***C, HF***C, HM***C, JE***C, JF***C, JC***C, JR***C,
JT***C, LL***C, this field should be populated with 'CASH'."
*/
declare
  k_UNKN mk.ii%type:=0; -- unknown
begin
  \i
  \s
    \k
  , 'Delivery type is incorrect'
  , \aTRAF10_11
  , h.ii
  from
    rtraf10_11 r
  , rtraf10_11h h
  , \e x
  where r.ii=h.r_
  and h.mi_=\u
  and h.ii=x.h_
  and x.mk_=k_UNKN
  and (
    (
      regexp_instr(FIM0068
      , '^(O[CP]..P|F[CF].P|(S[RTECFM]|H[RTECFM]|IF|J[EFCRT]|LL)...P)')=1
      and FIM0119!='PHYS'
    ) or (
      regexp_instr(FIM0068
      , '^(O[CP]..C|F[CF].P|HT...[CE]|(S[RTECM]|H[RECFM]|J[EFCRT]|LL)...C)'
      )=1
      and FIM0119!='CASH'
    )
  );
  :1:=sql%rowcount;
end;

MKT20180103 TRAFIM10 190 0012
/*
CON-000: Stavová kontrola - ESMA stav "Accepted"
*/
declare
  e varchar2(32767 byte); -- expression
  skr stp.id%type:='__\e.012.skr'; -- save check result
                                   -- after check No. 560
begin
  if s.pg(skr, s.GC_NO) is null then s.ps(skr, '0');
    select xt into e from me
    where id='m.save-check-results-T10-general';
    e:=replace(e, s.BH||'C0', 'true');
    e:=replace(e, s.BH||'K', '\K');
    e:=replace(e, s.BH||'S', 'RJCT');
    e:=replace(e, s.BH||'a', '\aTRAF10_11');
    e:=replace(e, s.BH||'e', '\e');
    e:=replace(e, s.BH||'t', 'rtraf10_11');
    e:=replace(e, s.BH||'x', null);
    s.uei(e); s.ps(skr, '0'); commit; end if;
  if s.pg(skr)='0' then update \e set mk_=\k
    , d='PDNG->ACPT'; s.ps(skr, '1'); commit; end if;
  if s.pg(skr)='1' then
    select xt into e from me
    where id='m.save-check-results-T10-general';
\m0 -- m_pm.ii=m.PM_DEFEERED then
    e:=replace(e, s.BH||'C0', 'true');
\m1 -- else
    e:=replace(e, s.BH||'C0', 'false');
\m2 -- end if
    e:=replace(e, s.BH||'K', '\K');
    e:=replace(e, s.BH||'S', 'ACPT');
    e:=replace(e, s.BH||'a', '\aTRAF10_11');
    e:=replace(e, s.BH||'e', '\e');
    e:=replace(e, s.BH||'t', 'rtraf10_11');
    e:=replace(e, s.BH||'x', null);
    s.uei(e); commit; end if;
  s.pd(skr);
  :1:=0;
end;

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 000 0001
INHERIT

MKT20190101 TRAFIM10 110 0023
INHERIT

MKT20190101 TRAFIM10 110 0023
INHERIT

MKT20190101 TRAFIM10 110 0024
INHERIT

MKT20190101 TRAFIM10 110 0025
INHERIT

MKT20190101 TRAFIM10 110 0040
INHERIT

MKT20190101 TRAFIM10 110 0041
INHERIT

MKT20190101 TRAFIM10 110 0060
INHERIT

MKT20190101 TRAFIM10 110 0070
INHERIT

MKT20190101 TRAFIM10 110 0071
INHERIT

MKT20190101 TRAFIM10 110 0072
INHERIT

MKT20190101 TRAFIM10 110 0073
INHERIT

MKT20190101 TRAFIM10 110 0074
INHERIT

MKT20190101 TRAFIM10 110 0080
INHERIT

MKT20190101 TRAFIM10 110 0120
INHERIT

MKT20190101 TRAFIM10 110 0121
INHERIT

MKT20190101 TRAFIM10 110 0122
INHERIT

MKT20190101 TRAFIM10 110 0123
INHERIT

MKT20190101 TRAFIM10 110 0160
INHERIT

MKT20190101 TRAFIM10 110 0161
INHERIT

MKT20190101 TRAFIM10 110 0162
INHERIT

MKT20190101 TRAFIM10 110 0163
INHERIT

MKT20190101 TRAFIM10 110 0164
INHERIT

MKT20190101 TRAFIM10 110 0170
INHERIT

MKT20190101 TRAFIM10 110 0210
INHERIT

MKT20190101 TRAFIM10 110 0211
INHERIT

MKT20190101 TRAFIM10 110 0212
INHERIT

MKT20190101 TRAFIM10 110 0213
INHERIT

MKT20190101 TRAFIM10 110 0251
INHERIT

MKT20190101 TRAFIM10 110 0260
INHERIT

MKT20190101 TRAFIM10 110 0270
INHERIT

MKT20190101 TRAFIM10 110 0280
INHERIT

MKT20190101 TRAFIM10 110 0281
INHERIT

MKT20190101 TRAFIM10 110 0290
INHERIT

MKT20190101 TRAFIM10 110 0310
INHERIT

MKT20190101 TRAFIM10 110 0340
INHERIT

MKT20190101 TRAFIM10 110 0360
INHERIT

MKT20190101 TRAFIM10 110 0370
INHERIT

MKT20190101 TRAFIM10 110 0371
INHERIT

MKT20190101 TRAFIM10 110 0390
INHERIT

MKT20190101 TRAFIM10 110 0410
INHERIT

MKT20190101 TRAFIM10 110 0570
INHERIT

MKT20190101 TRAFIM10 110 0571
INHERIT

MKT20190101 TRAFIM10 110 0572
INHERIT

MKT20190101 TRAFIM10 110 0573
INHERIT

MKT20190101 TRAFIM10 110 0574
INHERIT

MKT20190101 TRAFIM10 110 0580
INHERIT

MKT20190101 TRAFIM10 110 0590
INHERIT

MKT20190101 TRAFIM10 110 0591
INHERIT

MKT20190101 TRAFIM10 110 0600
INHERIT

MKT20190101 TRAFIM10 120 0030
INHERIT

MKT20190101 TRAFIM10 120 0610
INHERIT

MKT20190101 TRAFIM10 130 0411
INHERIT

MKT20190101 TRAFIM10 130 0411
INHERIT

MKT20190101 TRAFIM10 130 0412
INHERIT

MKT20190101 TRAFIM10 140 0331
INHERIT

MKT20190101 TRAFIM10 140 0351
INHERIT

MKT20190101 TRAFIM10 140 0381
INHERIT

MKT20190101 TRAFIM10 140 0640
INHERIT

MKT20190101 TRAFIM10 150 0470
INHERIT

MKT20190101 TRAFIM10 150 0473
INHERIT

MKT20190101 TRAFIM10 160 0361
INHERIT

MKT20190101 TRAFIM10 160 0471
INHERIT

MKT20190101 TRAFIM10 160 0471
INHERIT

MKT20190101 TRAFIM10 160 0472
INHERIT

MKT20190101 TRAFIM10 170 0430
INHERIT

MKT20190101 TRAFIM10 170 0440
INHERIT

MKT20190101 TRAFIM10 170 0450
INHERIT

MKT20190101 TRAFIM10 170 0451
INHERIT

MKT20190101 TRAFIM10 170 0520
INHERIT

MKT20190101 TRAFIM10 170 0541
INHERIT

MKT20190101 TRAFIM10 170 0552
INHERIT

MKT20190101 TRAFIM10 180 0330
INHERIT

MKT20190101 TRAFIM10 180 0350
INHERIT

MKT20190101 TRAFIM10 180 0380
INHERIT

MKT20190101 TRAFIM10 180 0431
INHERIT

MKT20190101 TRAFIM10 180 0441
INHERIT

MKT20190101 TRAFIM10 180 0452
INHERIT

MKT20190101 TRAFIM10 180 0453
INHERIT

MKT20190101 TRAFIM10 180 0480
INHERIT

MKT20190101 TRAFIM10 180 0481
INHERIT

MKT20190101 TRAFIM10 180 0490
INHERIT

MKT20190101 TRAFIM10 180 0500
INHERIT

MKT20190101 TRAFIM10 180 0501
INHERIT

MKT20190101 TRAFIM10 180 0502
INHERIT

MKT20190101 TRAFIM10 180 0510
INHERIT

MKT20190101 TRAFIM10 180 0511
INHERIT

MKT20190101 TRAFIM10 180 0530
INHERIT

MKT20190101 TRAFIM10 180 0531
INHERIT

MKT20190101 TRAFIM10 180 0540
INHERIT

MKT20190101 TRAFIM10 180 0542
INHERIT

MKT20190101 TRAFIM10 180 0550
INHERIT

MKT20190101 TRAFIM10 180 0551
INHERIT

MKT20190101 TRAFIM10 180 0560
INHERIT

MKT20190101 TRAFIM10 190 0012
INHERIT