-- 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;
/*
CODE_ID    SHORTCODE   VALUE          LONG_DESCRIPT  
---------  ----------  -------------  -------------  
Rolle#IPV  IPV         BusinessAp#IP  IPV-Bezüger    
Rolle#VB   VB          BusinessAp#VB  IPV Fachdaten  
*/
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      Unknown                               
Civil stat#L    L           1      Unmarried                             
Civil stat#H    H           2      Married                               
Civil stat#W    W           3      Widowed                               
Civil stat#S    S           4      Divorced                              
Civil stat#G    G           5      Legally  separated                    
Civil stat#GPS  GPS         6      Registered same-sex union             
Civil stat#GGP  GGP         7      Judicially terminated same-sex union  
Civil stat#GTP  GTP         8      Same-sex union terminated by death    
Civil stat#GRP  GRP         9      Judicially separated same-sex union   
*/
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;
/*
CODE_ID         SHORTCODE   VALUE  LONG_DESCRIPT                                   
--------------  ----------  -----  ----------------------------------------------  
LegalAgent#BMV  BMV                Legal adviser with administration of assets     
LegalAgent#BOV  BOV                Legal adviser without administration of assets  
LegalAgent#BRT  BRT                Advisory committee                              
LegalAgent#VRM  VRM                Legal guardian       
*/
-- All PV CodeTypes:
select * from vista.tiscmcodetype where company_shortcode='PV'; -- shortcode = 1034706

-- Bordereau:
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 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;

-- BSV-Countries (IK):
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 shortcode; -- d.long_descript; -- Hong Kong missing in IK... ?

-- Countries in PV:
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... ?

-- CH vs. LI
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!

-- PVCONST:
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;

-- SEDEX message types:
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;

-- MQ: CS Switches:
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;

-- IBAN Details:
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           
(...)
*/

-- Country details:
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='Country') and c.shortcode='CH'
order by c.value;
/*
CODE_ID     SHORTCODE   LONG_DESCRIPT  ATTR_SHORTCODE  ATTR_VALUE  
----------  ----------  -------------  --------------  ----------  
Country#CH  CH          Schweiz        BSVNummer       100         
Country#CH  CH          Schweiz        ISO3166A3       CHE         
Country#CH  CH          Schweiz        IstEULand       false       
Country#CH  CH          Schweiz        IstEFTALan      true        
Country#CH  CH          Schweiz        DatBilAbko      01.06.2002  
Country#CH  CH          Schweiz        KKraftIdx       100         
*/

-- 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; -- message types

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';

-- KEP Configuration:
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           
(...)
*/

-- Warnings for terminated/deleted addresses (IG_PV_DELA_V)
select c.code_id, c.codeno, c.shortcode, c.const_field, c.value, d.short_descript,
attr.code_id, 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 rel on (rel.codeno = c.codeno)
left join vista.tiscmcode attr on (attr.codeno = rel.code1)
where c.code_id like 'Rolle%' and attr.code_id = 'VATTRVALS#addrDelWarn'
order by c.shortcode;
/*
CODE_ID      CODENO    SHORTCODE   CONST_FIELD  VALUE          SHORT_DESCRIPT                  CODE_ID                VALUE  
-----------  --------  ----------  -----------  -------------  ------------------------------  ---------------------  -----  
Rolle#AB     42008469  AB          ROLLE_AB     BusinessAp#BW  AB                              VATTRVALS#addrDelWarn  Y      
Rolle#AG     42008526  AG          ROLLE_AG     BusinessAp#BW  AG                              VATTRVALS#addrDelWarn  Y      
Rolle#BGSA   42008583  BGSA        ROLLE_BGSA   BusinessAp#BW  BGSA                            VATTRVALS#addrDelWarn  Y      
Rolle#IV     42022792  IV          ROLLE_IV     BusinessAp#IV  IV                              VATTRVALS#addrDelWarn  Y      
Rolle#IVTGB  42301481  IVTGB       ROLLE_IVTGB  BusinessAp#IT  IVTGB                           VATTRVALS#addrDelWarn  Y      
Rolle#NE     42008697  NE          ROLLE_NE     BusinessAp#BW  NE                              VATTRVALS#addrDelWarn  Y      
Rolle#SE     42008640  SE          ROLLE_SE     BusinessAp#BW  SE                              VATTRVALS#addrDelWarn  Y      
Rolle#ST     42008754  ST          ROLLE_ST     BusinessAp#BW  ST                              VATTRVALS#addrDelWarn  Y      
Rolle#VB     42008923  VB          ROLLE_VB     BusinessAp#VB  VB                              VATTRVALS#addrDelWarn  Y      
*/

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