zOs/SQL/KE5480A

set current sqlId = 'A540769';
declare global temporary table
    tab (tab_text3 char(4) not null, tab_code char(20)  not null,
         tab_type2 integer not null, tab_type1 integer not null)
  ;
--create unique index session.tab
--     on session.tab (tab_code asc, tab_type2 asc, tab_type1 asc,
--                     tab_text3 asc)
--;
insert into session.tab
(
select V843.TAB_TEXT3, V843.TAB_CODE, V843.TAB_TYPE2, V843B.TAB_TYPE1
 from oa1p.VKS843A1V V843, oa1p.VKS843A1V V843B
 where                                                 V843.TAB_ART =
'PRODU3T_ID' AND
                    V843B.TAB_ART = 'APPLICATION_IDENT' AND
V843B.TAB_TEXT3 = V843.TAB_TEXT3
)
;
                                      SELECT DISTINCT V833.PARTNER_KEY,
V831.PARTNER_ID, V858.IDENT_ID, tab.TAB_TEXT3, V858.VALID_TO_DATE,
V859.VALID_TO_DATE, V859.APPLICATION_TYPE
FROM oa1p.VKS831A1V V831,
oa1p.VKS833A1V V833, oa1p.VKS835A1V V835
 , session.tab tab,
   oa1p.VKS858A1V V858, oa1p.VKS859A1V V859
WHERE V833.PARTNER_KEY >= ' ' AND
V833.PARTNER_TYPE = 3
  AND V831.PARTNER_KEY = V833.PARTNER_KEY AND
V835.PARTNER_KEY = V833.PARTNER_KEY AND V835.PARTNER_TYPE = 3  and
V835.CONTRACT_KEY = V833.CONTRACT_KEY
   AND V858.PARTNER_KEY =
V833.PARTNER_KEY AND V859.IDENT_ID = V858.IDENT_ID
             AND tab.TAB_CODE = V835.PRODUCT_ID AND tab.TAB_TYPE2 =
V858.IDENT_TYPE
                                 AND V859.APPLICATION_TYPE =
tab.TAB_TYPE1
AND (  V833.LAST_UPDATE_TIME > current timestamp - 7 days
    or V835.LAST_UPDATE_TIME > current timestamp - 7 days
    OR V858.VALID_TO_DATE BETWEEN  current date  - 7 days
                  and current date + 7 days
    or V859.VALID_TO_DATE BETWEEN  current date - 7 days
                  and current date + 7 days
    OR V859.VALID_FROM_DATE >= current date - 7 days
 )
ORDER BY V833.PARTNER_KEY, V831.PARTNER_ID, V858.IDENT_ID,
tab.TAB_TEXT3, V859.APPLICATION_TYPE, V858.VALID_TO_DATE,
V859.VALID_TO_DATE WITH UR