-- VISTA Useful SQL Queries
-- 
-- 
-- CM --------------------------------------------------------------------------------------------
select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id in ('Rolle#IP', 'Rolle#IPV', 'Rolle#VB')
order by c.value;

select p.kpvpartid, a.kpvadrsid
from vista.tpvpartner p
join vista.tpvaddress a on (a.kpvpartid = p.kpvpartid)
where a.validto > current date and a.kpvadrsid is null;

/*
CODE_ID         SHORTCODE   VALUE  LONG_DESCRIPT                                                        
--------------  ----------  -----  -------------------------------------------------------------------  
Civil stat#U    U                  Unbekannt                                                            
Civil stat#L    L           1      Ledig                                                                
Civil stat#H    H           2      Verheiratet                                                          
Civil stat#W    W           3      Verwitwet                                                            
Civil stat#S    S           4      Geschieden                                                           
*/
select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=4)
where c.code_id like 'Civil stat#%'
order by c.value;
/*
CODE_ID         SHORTCODE   VALUE  LONG_DESCRIPT                                                        
--------------  ----------  -----  -------------------------------------------------------------------  
Civil stat#U    U           0      Unbekannt                                                            
Civil stat#L    L           1      Ledig                                                                
Civil stat#H    H           2      Verheiratet                                                          
Civil stat#W    W           3      Verwitwet                                                            
Civil stat#S    S           4      Geschieden                                                           
Civil stat#G    G           5      Gerichtlich getrennt                                                 
Civil stat#GPS  GPS         6      Eingetragene Partnerschaft gleichgeschlechtlicher Partner            
Civil stat#GGP  GGP         7      Gerichtlich aufgelöte Partnerschaft gleichgeschlechtlicher Partner  
Civil stat#GTP  GTP         8      Durch Tod aufgeöste Partnerschaft gleichgeschlechtlicher Partner    
Civil stat#GRP  GRP         9      Richterlich getrennte Partnerschaft gleichgeschlechtlicher Partner   
*/
select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=4)
where c.code_id like 'LegalAgent#%'
order by c.value;

select * from vista.tiscmcodetype where company_shortcode='PV'; -- shortcode = 1034706
select * from vista.tiscmcode where codetypeno=1034706;

select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'BORD%'
order by c.value;
/*
CODE_ID     SHORTCODE   VALUE  LONG_DESCRIPT           
----------  ----------  -----  ----------------------  
BORDTYPE#1  1                  PDF                     
BORDTYPE#2  2                  EXCEL                   
BORDTYPE#3  3                  TXT-TFS                 
BORDTYPE#4  4                  PDF und EXCEL           
BORDTYPE#5  5                  PDF und TXT-TFS         
BORDTYPE#6  6                  EXCEL und TXT-TFS       
BORDTYPE#7  7                  PDF, EXCEL und TXT-TFS  
*/

select * from vista.tpvpartner where cseparation is not null;

select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'Sex%'
order by c.value;
/*
CODE_ID  SHORTCODE   VALUE  LONG_DESCRIPT  
-------  ----------  -----  -------------  
Sex#M    M                  Männlich       
Sex#U    U                  Unbekannt      
Sex#W    W                  Weiblich                                                               
*/
select c.code_id, c.shortcode, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'Separ%'
order by c.value;

select c.code_id, c.shortcode, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'CorresLang%'
order by c.value;

select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'ERFASSTCOD%'
order by c.value;

select c.code_id, c.shortcode, c.value, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'BSVLAND%'
order by d.long_descript; -- Hong Kong missing in IK... ?

select c.code_id, c.shortcode, c.value, d.medium_descript, c.VALID_FROM, c.valid_to from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=3)
where c.code_id like 'Country%'
and c.valid_to < '9999-12-31'
order by d.medium_descript; -- Hong Kong missing in IK... ?

select c.code_id, c.shortcode, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id in ('Country#AN', 'Country#XZ')
order by d.medium_descript; -- AN / XZ

select c.code_id, c.shortcode, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id in ('Country#LI', 'Country#CH')
order by d.medium_descript; -- CH: 100 / LI: 222 - in LI vice-versa!

select c.code_id, c.shortcode, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.value is null
order by d.medium_descript;

select c.code_id, c.shortcode, c.const_field, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=4)
where c.code_id like 'PVCONST%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=2)
where c.code_id like 'PVCONST%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.short_descript, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'ZAMSGTYPE#%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'ForeignPay%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.short_descript, d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'CompoundFu#%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=2)
where c.code_id like 'KEPROLE%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=2)
where c.code_id like 'KEPCONF%'
order by c.shortcode;

select c.code_id, c.shortcode, c.const_field, c.value, d.medium_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id like 'CSSWITCHES%'
order by c.shortcode;

-- CM + Attributes (e.g. IBAN):

select c.code_id, c.shortcode, d.long_descript, attr.shortcode as attr_shortcode, attr.value as attr_value from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
left join vista.tiscmcodexcode x on (x.codeno = c.codeno)
left join vista.tiscmcode attr on (attr.codeno = x.code1)
where c.codetypeno in (select codetypeno from vista.tiscmcodetype where shortcode='IBANDetail') and c.shortcode='DE'
order by c.value;
/*
CODE_ID        SHORTCODE   LONG_DESCRIPT  ATTR_SHORTCODE  ATTR_VALUE  
-------------  ----------  -------------  --------------  ----------  
IBANDetail#EG  EG          Ägypten        AutoLookup      N           
IBANDetail#EG  EG          Ägypten        BCNoLength      0           
IBANDetail#EG  EG          Ägypten        CntrySupp       Y           
IBANDetail#EG  EG          Ägypten        Length          27          
*/
select c.code_id, c.shortcode, d.long_descript, attr.shortcode as attr_shortcode, attr.value as attr_value from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
left join vista.tiscmcodexcode x on (x.codeno = c.codeno)
left join vista.tiscmcode attr on (attr.codeno = x.code1)
where c.codetypeno in (select codetypeno from vista.tiscmcodetype where shortcode='IBANDetail') and attr.shortcode='isSEPA' and attr.value='Y'
order by c.shortcode;
/*
CODE_ID        SHORTCODE   LONG_DESCRIPT          ATTR_SHORTCODE  ATTR_VALUE  
-------------  ----------  ---------------------  --------------  ----------  
IBANDetail#AD  AD          Andorra                isSEPA          Y           
IBANDetail#AT  AT          Östereich              isSEPA          Y           
IBANDetail#BE  BE          Belgien                isSEPA          Y           
IBANDetail#BG  BG          Bulgarien              isSEPA          Y           
IBANDetail#CH  CH          Schweiz                isSEPA          Y           
(...)
*/

-- Messages
select * from vista.tiscmcode where company_shortcode='PV' and code_id like 'MSGTYPE%'; -- codetypeno=1000132 / codeno=1020759
select * from vista.tiscmcodetype_lang where codetypeno=1000132; -- Meldungstypen

select * from vista.tiscmcode_descr where codetypeno=1000132 and codeno=1020759 and language=4;

select code.shortcode, lang.long_descript from vista.tiscmcode code
left join vista.tiscmcode_descr lang on (lang.codetypeno = code.codetypeno and lang.codeno = code.codeno and language = 4)
where code.shortcode='PV00809080';
-- Nur IK, nur Italienisch:
select code.shortcode, lang.long_descript from vista.tiscmcode code
left join vista.tiscmcode_descr lang on (lang.codetypeno = code.codetypeno and lang.codeno = code.codeno and language = 3)
where code_id like 'MSGTYPE#%' and company_shortcode='IK';

-- KEP:
select c.code_id, c.shortcode, d.long_descript, attr.shortcode as attr_shortcode, attr.value as attr_value from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
left join vista.tiscmcodexcode x on (x.codeno = c.codeno)
left join vista.tiscmcode attr on (attr.codeno = x.code1)
where c.codetypeno in (select codetypeno from vista.tiscmcodetype where shortcode='KEPCONF')
order by c.value;
/*
CODE_ID             SHORTCODE   LONG_DESCRIPT           ATTR_SHORTCODE  ATTR_VALUE  
------------------  ----------  ----------------------  --------------  ----------  
KEPCONF#VN          VN                                  KPADshow        Y           
KEPCONF#VN          VN                                  KPGTshow        Y           
KEPCONF#VN          VN                                  KPSPshow        Y           
KEPCONF#VN          VN                                  KPFRshow        Y           
KEPCONF#VN          VN                                  KPTDshow        Y           
KEPCONF#VN          VN                                  AttrFetch       Y           
KEPCONF#NATIONID    NATIONID                            AttrFetch       Y           
KEPCONF#NATIONID    NATIONID                            AttrCompar      Y           
(...)
*/

-- DEFLANG --------------------------------------------------------------------------------------------
-- default language / default language in CM: (NOT: "RECONSTCAN#AKDEFLANG")
select * from vista.tiscmcode where code_id = 'VISTAADMIN#DEFLANG';

-- GILAI / web@AI -------------------------------------------------------------------------------------
select * from vista.aaireplication;
select * from vista.aaipartner; -- ccitizenship
select * from vista.aaiaddress; -- ccountry

-- OS / DOPE ------------------------------------------------------------------------------------------


-- UPI ---------------------------------------------------------------------------------------------------------------------------------------------
select distinct new_nnss from vista.aurupiregister upi where not exists (
    select 1 from vista.tpvpartner p where p.personid = upi.new_nnss
) and not exists (
    select 1 from vista.tikheader h where h.vnr = upi.new_nnss
) and statuscd='0' and actualitycd='1';

select * from vista.aurupiregister upi where statuscd in ('2', '3');
select * from vista.aurupiregister upi where statuscd='3' and actualitycd='1'; -- empty

-- DB2 -------------------------------------------------------------------------------------------------------------------------------------------------
-- Find all tables by table names:
select * from sysibm.systables where name like 'TIK%' and type = 'T'; -- 50 TIK-Tables
select * from sysibm.systables where name like 'TPV%' and type = 'T'; -- 31 TPV-Tables

-- Find all columns by name:
select * from syscat.columns where tabschema='VISTA' and colname like '%DOCREF%';

-- Table IDs:
SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME
FROM SYSCAT.TABLES AS T,
SYSCAT.COLUMNS AS C
WHERE T.TBSPACEID = 303
AND T.TABLEID = 3
AND C.COLNO = 2
AND C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME;
/*
TABSCHEMA  TABNAME     COLNAME    
---------  ----------  ---------  
VISTA      TPVADDRESS  KPVPARTID
*/

-- DB / Federated DB - UPI ---------------------------------------------------------------------------------------------------------------------------
select current server as db, servername, create_time, option, setting
from syscat.serveroptions
where option in ( 'DBNAME' , 'NODE' )
order by option;
/*
DB        SERVERNAME     CREATE_TIME          OPTION  SETTING   
--------  -------------  -------------------  ------  --------  
VIDPDBGR  ZASDBSERVER    2020-10-21 10:19:55  DBNAME  vidpdbza  
VIDPDBGR  GILAIDBSERVER  2020-08-08 12:26:55  DBNAME  vidpdbex  
VIDPDBGR  ZASDBSERVER    2020-10-21 10:19:55  NODE    vidpinza  
VIDPDBGR  GILAIDBSERVER  2020-08-08 12:26:54  NODE    vidpingr  
*/

-- MQ ------------------------------------------------------------------------------------------------------------------------------------------------------
select * from vista.tsfmqoutbound where lastupdated>'2021-06-10 08:00:30' and jmstype='BZ.PV-I003' order by lastupdated desc;
select * from vista.tsfmqinbound where lastupdated>'2021-06-10 08:00:30' and jmstype='BZ.PV-I003' order by lastupdated desc;
select * from vista.tsfmqinbound where lastupdated>'2021-06-10 08:00:30' and jmstype='EA-N001' order by lastupdated desc;
select * from vista.tsfmqevent where lastupdated>'2021-06-10 08:00:30' order by lastupdated desc;
select tsfmqoutboundid, reason, jmscorrelationid,  substr(outboundmsg, (position('<messageID>', outboundmsg, OCTETS) + 11), 36) as messageId
from vista.tsfmqoutbound where reason is not null and lastupdated>'2017-11-09 12:40:30' and jmstype='BZ.PV-I003' order by lastupdated desc;

--- XML-Parsing -----------------------------------------------------------------------------------------------------------------------------------------

-- Replication-XMLtable:
with outboundtable as (
	select out.tsfmqoutboundid, x.triggeringObject, x.triggeringObjectKey, out.reason
	from vista.tsfmqoutbound out,
	XMLtable ('$d/*:PartnerReplicationRequestEnvelopeType/*:body' passing XMLparse(document outboundmsg) as "d"
		columns
			triggeringObject varchar(20) path './*:triggeringObject',
			triggeringObjectKey bigint path './*:triggeringObjectKey'
		) as x
	where out.jmstype='BZ.PV-I003' and out.lastupdated > '2021-05-21'
)
select * from outboundtable where triggeringObjectKey=2498426;
--------------


-- Misc. Queries ---------------------------------------------------------------------------------------------------------------------------------------------------


-- PV.2021.93886 (ÜL PV - Foregin IBANs)
select r.croletype, count(*) as cnt_roles from vista.tpvrolerel rr
left join vista.tpvrole r on (r.kpvroleid = rr.kpvroleid_source)
where rr.crelationtype='RoleRelati#ZE'
group by r.croletype order by 2 desc;
-- ZH:
/*
CROLETYPE  CNT_ROLES  
---------  ---------  
Rolle#VB   1496044    
Rolle#RE   604148     
Rolle#HE   38869      
Rolle#EL   30481      
Rolle#EMP  683        
Rolle#AS   18         
Rolle#IPV  2          
Rolle#IK   1          
Rolle#STA  1          
*/

-- Duplicate IBANs
select iban1.kpvpartid, iban1.iban as iban1_iban, iban1.kpvpaddid as iban1_kpvpaddid, iban2.iban as iban2_iban, iban2.kpvpaddid as iban2_kpvpaddid
from vista.tpvpaymentaddr iban1
left join vista.tpvpaymentaddr iban2 on (iban2.kpvpartid = iban1.kpvpartid);

select iban1.kpvpartid, iban1.iban as iban1_iban, iban1.kpvpaddid as iban1_kpvpaddid, iban2.iban as iban2_iban, iban2.kpvpaddid as iban2_kpvpaddid
from vista.tpvpaymentaddr iban1
left join vista.tpvpaymentaddr iban2 on (iban2.kpvpartid = iban1.kpvpartid and iban2.kpvpaddid <> iban1.kpvpaddid);

select iban1.kpvpartid, iban1.iban as iban1_iban, iban1.kpvpaddid as iban1_kpvpaddid, iban2.iban as iban2_iban, iban2.kpvpaddid as iban2_kpvpaddid
from vista.tpvpaymentaddr iban1
left join vista.tpvpaymentaddr iban2 on (iban2.kpvpartid = iban1.kpvpartid and iban2.kpvpaddid <> iban1.kpvpaddid)
where iban1.iban is not null and iban2.iban is not null;

select iban1.kpvpartid, iban1.iban as iban1_iban, iban1.kpvpaddid as iban1_kpvpaddid, iban2.iban as iban2_iban, iban2.kpvpaddid as iban2_kpvpaddid
from vista.tpvpaymentaddr iban1
left join vista.tpvpaymentaddr iban2 on (iban2.kpvpartid = iban1.kpvpartid and iban2.kpvpaddid <> iban1.kpvpaddid and iban2.iban = iban1.iban)
where iban1.iban is not null and iban2.iban is not null;

--> Cross-check:
select * from vista.tpvpaymentaddr iban where iban.kpvpartid=969046; -- prodZH
--> BR_PV_1000821_19

select iban1.kpvpartid, iban1.iban as iban1_iban, iban1.kpvpaddid as iban1_kpvpaddid, iban2.iban as iban2_iban, iban2.kpvpaddid as iban2_kpvpaddid
from vista.tpvpaymentaddr iban1
left join vista.tpvpaymentaddr iban2 on (iban2.kpvpartid = iban1.kpvpartid and iban2.kpvpaddid <> iban1.kpvpaddid and iban2.iban = iban1.iban)
where iban1.iban is not null and iban2.iban is not null and iban1.validto > current date and iban2.validto > current date;

-- Duplicate WS:
select ws1.kpvpartid, ws1.caddresstype as ws1_type, ws1.kpvadrsid as ws1_kpvadrsid, ws2.caddresstype as ws2_type, ws2.kpvadrsid as ws2_kpvadrsid
from vista.tpvaddress ws1
left join vista.tpvaddress ws2 on (ws2.kpvpartid = ws1.kpvpartid and ws2.kpvadrsid <> ws1.kpvadrsid and ws2.caddresstype = ws1.caddresstype)
where ws1.caddresstype is not null and ws2.caddresstype is not null and ws1.validto > current date and ws2.validto > current date;

select ws1.kpvpartid, ws1.caddresstype as ws1_type, ws1.kpvadrsid as ws1_kpvadrsid, ws2.caddresstype as ws2_type, ws2.kpvadrsid as ws2_kpvadrsid
from vista.tpvaddress ws1
left join vista.tpvaddress ws2 on (ws2.kpvpartid = ws1.kpvpartid and ws2.kpvadrsid <> ws1.kpvadrsid and ws2.caddresstype = ws1.caddresstype)
where ws1.caddresstype='AddressTyp#WS' and ws2.caddresstype='AddressTyp#WS' and ws1.validto > current date and ws2.validto > current date;

--> Cross-check:
select * from vista.tpvaddress ws where ws.kpvpartid=999990020069261; -- AIT101
