-- MQ ------------------------------------------------------------------------------------------------------------------------------------------------------
select * from vista.tsfmqoutbound where lastupdated>'2022-06-10 08:00:30' and jmstype='BZ.PV-I003' order by lastupdated desc;
select * from vista.tsfmqinbound where lastupdated>'2022-06-10 08:00:30' and jmstype='BZ.PV-I003' order by lastupdated desc;
select * from vista.tsfmqinbound where lastupdated>'2022-06-10 08:00:30' and jmstype='EA-N001' order by lastupdated desc;
select * from vista.tsfmqevent where lastupdated>'2022-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>'2022-06-21 12:40:30' and jmstype='BZ.PV-I003' order by lastupdated desc;
/*
TSFMQOUTBOUNDID  REASON                JMSCORRELATIONID                      MESSAGEID       
---------------  --------------------  ------------------------------------  --------------  
8905329926       MQOUTBREJ#BEANERROR   e09bd1db-37ad-4ba6-a94f-b465ea3a69d6  (null)          
8905294795       MQOUTBREJ#BEANERROR   79778548-9d84-4cf3-8063-527ed630f203  (null)          
8905278152       MQOUTBREJ#BEANERROR   496db4a1-233b-494c-a8aa-15f083988338  (null)          
8905264870       MQOUTINFO#IGNOREREPL  bdbd89cb-fc04-455e-8645-0f684680c322  (null)          
8905264855       MQOUTINFO#IGNOREREPL  670e2aa8-3ab2-4a11-862e-a25730c50f88  (null)          
*/

--- XML-Parsing ---------
-- Replication-XMLtable: Find an entry in the MQ table using a key from the XML:
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 > '2022-06-25'
	fetch first 500 rows only -- optional
)
select * from outboundtable where triggeringObjectKey=8867794082; -- execute inner-sql first to get a specific entry

select tsfmqoutboundid, reason, jmscorrelationid,  
cast ( SUBSTR( 
    OUTBOUNDMSG, LOCATE_IN_STRING(OUTBOUNDMSG, '<messageID>') + 11,
    (LOCATE_IN_STRING(OUTBOUNDMSG, '</messageID>') - LOCATE_IN_STRING(OUTBOUNDMSG, '<messageID>') - 11) -- length
) as varchar(36) ) as x_message_id
,
cast ( SUBSTR( 
    OUTBOUNDMSG, LOCATE_IN_STRING(OUTBOUNDMSG, '<ns2:triggeringObject>') + 22,
    (LOCATE_IN_STRING(OUTBOUNDMSG, '</ns2:triggeringObject>') - LOCATE_IN_STRING(OUTBOUNDMSG, '<ns2:triggeringObject>') - 22) -- length
) as varchar(21) ) as x_triggering_object
,
cast ( SUBSTR( 
    OUTBOUNDMSG, LOCATE_IN_STRING(OUTBOUNDMSG, '<ns2:triggeringObjectKey>') + 25,
    (LOCATE_IN_STRING(OUTBOUNDMSG, '</ns2:triggeringObjectKey>') - LOCATE_IN_STRING(OUTBOUNDMSG, '<ns2:triggeringObjectKey>') - 25) -- length
) as varchar(21) ) as x_triggering_object_key
from vista.tsfmqoutbound where 
reason is not null and OUTBOUNDMSG is not null and OUTBOUNDMSG not like '%<ns3:lzPartnerKey>%' and lastupdated>'2022-06-25 12:40:30' and jmstype='BZ.PV-I003' order by lastupdated desc;
--tsfmqoutboundid = 1372233130;
-- not working anymore: [Code: -138, SQL State: 22011]  The statement was not executed because a numeric argument of a scalar function is out of range.. SQLCODE=-138, SQLSTATE=22011, DRIVER=4.26.14

-- Report:
with q as (
    select distinct o.reason, o.jmscorrelationid,
    cast(i.inboundmsg as varchar(100)) as inboundmsg, -- cast is required to use distinct on a CLOB...
    cast(
        replace(
            substring(i.inboundmsg, locate_in_string (left(i.inboundmsg, length(i.inboundmsg)-1 ), ';', -1))
        , ';', '')
        as varchar(21)
    ) as kpv_mq, -- extracts the logical key of the replicated item, out of a string like: 'PARTNER;U;4583509726;135165453;' --> (4th element)
    o.lastupdated as lastupdated_out from vista.tsfmqoutbound o
    left join vista.tsfmqinbound i on (i.jmscorrelationid = o.jmscorrelationid)
    where o.jmstype='BZ.PV-I003' -- or LZ.PV-N001...
    and o.reason='MQOUTBREJ#BEANERROR' -- or INVOUTXML, IGNOREREPL, ...
    and i.lastupdated >= '2022-04-14' ---- or your due date...
), err as (
    select q.*, 
    case when q.inboundmsg like 'PARTNER%' then q.kpv_mq 
         when q.inboundmsg like 'ADDRESS%' then (select max(kpvpartid) from vista.tpvaddress a where a.kpvadrsid = q.kpv_mq)
         when q.inboundmsg like 'PAYMENTADDR%' then (select kpvpartid from vista.tpvpaymentaddr pa where pa.kpvpaddid = q.kpv_mq fetch first 1 row only)
         when q.inboundmsg like 'ROLE%' then (select max(kpvpartid) from vista.tpvrole r where r.kpvroleid = q.kpv_mq)
         -- when q.inboundmsg like 'FAMILYREL%' then (select max(kpvpartid_source) from vista.tpvfamilyrel fr where fr.tpvfrelid = q.kpv_mq) 
         -- simplification: only source partner -- does not work, as there is no 4th element: FAMILYREL;I;8437250417;;
         else -1 -- could be extended with ADDRESSBASIC, TAXDATA, ...
    end as kpvpartid_mq
    from q
), partner as (
    select err.*,
    p.kpvpartid, p.tpvpartid, p.cpartnertype, p.personid, p.clegaltype, p.clegalform, p.ccitizenship, p.validfrom, p.validto -- any other attributes if required
    from err
    left join vista.tpvpartner p on (p.kpvpartid = cast(err.kpvpartid_mq as bigint))
), overview as (
    select partner.reason, partner.jmscorrelationid, partner.inboundmsg, partner.kpv_mq, partner.kpvpartid, partner.tpvpartid, partner.cpartnertype, partner.personid, 
    partner.clegaltype, partner.clegalform, partner.ccitizenship, partner.validfrom, partner.validto,
    a.kpvadrsid, a.caddresstype, ab.zip, ab.city, ab.ccountry, ab.validfrom as ab_validrom, ab.validto as ab_validto -- any other attributes / joines if required
    from partner 
    left join vista.tpvaddress a on (a.kpvpartid = partner.kpvpartid)
    left join vista.tpvaddressbasic ab on (ab.kpvadbaid = a.kpvadbaid)
    order by partner.kpvpartid desc, partner.tpvpartid desc
)
--select distinct * from err;
select distinct * from overview
--left join vista.tpvrole r on (r.kpvpartid = overview.kpvpartid);
left join vista.tpvrole r on (r.kpvpartid = overview.kpvpartid);


-- BEANERROR (MIG GE):
/*
SPLUNK:
at com.ibm.vista.pv.mq.BZPVI003.V5.PvReplicationMsgBean.copyPartnerAddress(PvReplicationMsgBean.java:2149)

try {
    String swissZipCOde = pvAddressAE
            .getPvAddressMasterAE().getZip()
            .substring(0, 4);
    String swissZipCOdeAddon = pvAddressAE
            .getPvAddressMasterAE().getZip()
            .substring(4, 6); ----------------------- Line 2149
    addressInformationType.setSwissZipCode(Long
            .parseLong(swissZipCOde));
    addressInformationType
            .setSwissZipCodeAddOn(swissZipCOdeAddon);
} catch (NumberFormatException e
*/
select distinct jmscorrelationid from vista.tsfmqoutbound ob where ob.reason='MQOUTBREJ#BEANERROR'; -- 114
select distinct jmscorrelationid from vista.tsfmqoutbound ob where ob.reason='MQOUTBREJ#BEANERROR' and lastupdated > '2021-11-02'; -- 57

select ob.*, ib.* from vista.tsfmqoutbound ob 
left join vista.tsfmqinbound ib on (ib.jmscorrelationid = ob.jmscorrelationid)
where ob.reason='MQOUTBREJ#BEANERROR';


with q as (
    select o.reason, o.jmscorrelationid, i.inboundmsg, cast(
        replace(substr(replace(i.inboundmsg,'PARTNER;U;',''),locate(';',replace(i.inboundmsg,'PARTNER;U;',''))+1),';','')
        as varchar(10) 
    ) as kpvpartid_mq from vista.tsfmqoutbound o
    left join vista.tsfmqinbound i on (i.jmscorrelationid = o.jmscorrelationid)
    where o.jmstype='BZ.PV-I003' and o.reason='MQOUTBREJ#BEANERROR'
    and i.lastupdated >= '2021-11-03'
), err as (
    select q.*, p.kpvpartid, p.cpartnertype, p.personid, p.clegaltype, p.clegalform, p.ccitizenship, p.validfrom, p.validto
    from q
    left join vista.tpvpartner p on (p.kpvpartid = cast(q.kpvpartid_mq as bigint))
    order by p.kpvpartid, p.validfrom
)
select distinct (err.kpvpartid) from err; -- 57

with q as (
    select o.reason, o.jmscorrelationid, i.inboundmsg, cast(
        replace(substr(replace(i.inboundmsg,'PARTNER;U;',''),locate(';',replace(i.inboundmsg,'PARTNER;U;',''))+1),';','')
        as varchar(10)
    ) as kpvpartid_mq, o.lastupdated as lastupdated_out from vista.tsfmqoutbound o
    left join vista.tsfmqinbound i on (i.jmscorrelationid = o.jmscorrelationid)
    where o.jmstype='BZ.PV-I003' and o.reason='MQOUTBREJ#BEANERROR'
    and i.lastupdated >= '2021-11-03' ---- To be modified...
), err as (
    select q.*, p.kpvpartid, p.tpvpartid, p.cpartnertype, p.personid, p.clegaltype, p.clegalform, p.ccitizenship, p.validfrom, p.validto
    from q
    left join vista.tpvpartner p on (p.kpvpartid = cast(q.kpvpartid_mq as bigint))
    order by p.kpvpartid, p.validfrom
), overview as (
    select distinct err.reason, err.jmscorrelationid, err.kpvpartid, err.tpvpartid, err.cpartnertype, err.personid, err.clegaltype, err.clegalform, err.ccitizenship, err.validfrom, err.validto, -- err.lastupdated_out
    a.kpvadrsid, a.caddresstype, ab.zip, ab.city, ab.ccountry, ab.validfrom as ab_validrom, ab.validto as ab_validto
    from err 
    left join vista.tpvaddress a on (a.kpvpartid = err.kpvpartid)
    left join vista.tpvaddressbasic ab on (ab.kpvadbaid = a.kpvadbaid)
    order by err.kpvpartid desc, err.tpvpartid desc
)
select * from overview;
--select distinct jmscorrelationid from overview; --57
--select distinct jmscorrelationid, kpvpartid, personid from overview; --57
--select count(distinct kpvpartid) from overview where length(zip) = 4; -- abgebrochen nach 500 sekunden (vermutlich fulltable scans...)
/*
06ab5928-2231-463a-803a-83cae02e2af0: PvReplicationMsgBean.sortCodeManagementData(PvReplicationMsgBean.java:3742 / PvReplicationMsgBean.copypvPartnerAEtailsToPartnerList(PvReplicationMsgBean.java:1270 / IPvConstants.CONST_ALLOWED_LANG
073bd91f-f98c-401c-a140-6f6f52605d14: ZaUpiRegisterDSPValidator.validateNNSS(ZaUpiRegisterDSPValidator.java:162) / 7560000000003
*/