zOs/SQL/HUNGER1
//STA0152A JOB (SK03,KE50,,SP00000000),
// MSGCLASS=T,
// NOTIFY=&SYSUID,
// TIME=1440
//JOBLIB DD DSN=PCL.U0000.P0.RZ2AKT.PERM.@008.LLB,DISP=SHR
//*MAIN CLASS=SUBP2
//****
//P00 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEL 'KE.U0000.P0.EBVV.#STA0152.UNLOAD' SCR
IF MAXCC < 9 THEN SET MAXCC = 0
//*
//P02 EXEC PGM=IKJEFT1A,
// DYNAMNBR=20
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD DSNAME=KE.U0000.P0.EBVV.#STA0152.UNLOAD,
// MGMTCLAS=COM#A091,
// DISP=(NEW,CATLG,DELETE)
//**********************************************************************
//* Report for Esther Rein N4354, PSENG
//* --------------------------------------------------
//*
//* Output: (last run, but STAT?? unknown)
//* - CifNo Owner
//* - Konzern-Produkt --> 0017
//* - Customer Office Owner and User
//* - Domicile Owner and User
//* - Owner Customer Id Contract id
//* - Segment Owner and User
//* - Client Assets ??
//*
//* Request:
//* Per CCE Owner
//* new Output requested:
//* - UserId
//* - USER_CIF
//* - U_CUST_OFFICE
//* - USER_DOM
//* - USER_SEGM
//* - OWNER_CIF
//* - O_CUST_OFFICE
//* - OWNER_DOM
//* - OWNER_SEGM
//* - CNTRACT_ID
//* - PROCUCT
//* - SERVICE_KEY
//* - SERVICE_TEXT: which services are activated
//* which are used in CCE -> not available in EBVV
//* - LAST_CCE_Login_SEC1
//* - LAST_CCE_LOGON_SEC2
//* - number of logins within 3 months --> STA0153
//* - DN user yes / no --> not necessary, see comments below
//* - last dn Login date --> not necessary, see comments below
//*--------------------------------------------------------------------
//* Are there really customers with Direct Net and Multiuser for
//* same cif? I checked with below query --> that's not standard
//*--------------------------------------------------------------------
//* QMF: COUNTDOPPELTIDENT (of A593397)
//*
//* SELECT V831.PARTNER_ID
//* FROM OA1P.VKS831A1V V831
//* ,OA1P.VKS858A1V C858
//* ,OA1P.VKS858A1V D858
//*
//* WHERE V831.PARTNER_KEY = D858.PARTNER_KEY
//* AND V831.PARTNER_KEY = C858.PARTNER_KEY
//* AND D858.IDENT_TYPE = 1
//* AND C858.IDENT_TYPE = 5
//*
//* GROUP BY V831.PARTNER_ID
//* HAVING COUNT(*) > 1
//*
//* WITH UR;
//*
//*
//* Result: 2 rows
//* --------------
//* 000000000001 TEST_IDENTIFIKATION for Reset
//* 083509400102 284476362015080802-P Testcontract ???
//*
//**********************************************************************
//SYSTSIN DD *
DSN SYSTEM(DBOF)
RUN PROGRAM(DSNTIAUL) PARM('SQL')
//SYSIN DD *
SELECT distinct
v858.ident_id AS UserId
,cast(';' as char(1))
,u831.partner_id AS USER_CIF
,cast(';' as char(1))
,SUBSTR(U100.CD100452,9,5) AS U_CUST_OFFICE
,cast(';' as char(1))
,u831.DOMIZIL AS USER_DOM
,cast(';' as char(1))
,U831.CUSTOMER_SEGMENT AS USER_SEGM
,cast(';' as char(1))
,o831.partner_id AS OWNER_CIF
,cast(';' as char(1))
,SUBSTR(U100.CD100452,9,5) AS O_CUST_OFFICE
,cast(';' as char(1))
,O831.DOMIZIL AS OWNER_DOM
,cast(';' as char(1))
,O831.CUSTOMER_SEGMENT AS OWNER_SEGM
,cast(';' as char(1))
,v832.ext_contract_id AS CNTRACT_ID
,cast(';' as char(1))
,v843.tab_text1 AS PROCUCT
,cast(';' as char(1))
,v835.service_key AS SERVICE
,cast(';' as char(1))
,S843.tab_text1 AS SERVICE_TEXT
,cast(';' as char(1))
,V859.SECID1_LAST_LOGON AS LAST_LOGON_SEC1
,cast(';' as char(1))
,V859.SECID2_LAST_LOGON AS LAST_LOGON_SEC2
,cast(';' as char(1))
,CAST((CASE
WHEN((V859.SECID1_LAST_LOGON> V859.SECID2_LAST_LOGON
AND V859.SECID1_LAST_LOGON^='9999-12-31-23.59.59.999999')
OR V859.SECID2_LAST_LOGON='9999-12-31-23.59.59.999999')
THEN V859.SECID1_LAST_LOGON
ELSE V859.SECID2_LAST_LOGON
END) AS CHAR(26))
FROM
OA1P.VKS831A1V O831
,OA1P.VKS831A1V U831
,OA1P.VKS832A1V V832
,OA1P.VKS833A1V V833
,OA1P.VKS835A1V V835
,OA1P.VKS858A1V V858
,OA1P.VKS859A1V V859
,OA1P.VKS843A1V V843
,OA1P.VKS843A1V S843
,OA1P.TCD100A1 U100
,OA1P.TCD100A1 O100
WHERE
V835.PRODUCT_ID IN (
'0095',
'0125',
'0141',
'0148',
'0149',
'0150',
'0151',
'0183',
'0191',
'0192',
'0193' )
and v835.partner_type = 3
AND V835.CONTRACT_KEY = V832.CONTRACT_KEY
AND V835.CONTRACT_KEY = V833.CONTRACT_KEY
and v833.partner_type = 3
AND V833.partner_key = u831.partner_key
AND U831.partner_ID = U100.CD100001
AND V835.arrang_partner_key = O831.partner_key
AND O831.partner_ID = O100.CD100001
and v833.partner_key = V858.partner_key
AND V858.ident_id = V859.ident_id
AND V859.application_type = 15
AND V843.tab_art = 'PRODUCT_ID'
AND V843.tab_code = V835.PRODUCT_ID
AND S843.tab_art = 'SERVICE_ID'
AND S843.tab_code = V835.SERVICE_KEY
AND S843.TAB_LANGUAGE_CODE = '001'
and current date < o831.valid_to_date
and current date < u831.valid_to_date
and current date < v832.valid_to_date
and current date < v833.valid_to_date
and current date < v858.valid_to_date
and current date < v859.valid_to_date
and current date between v835.valid_from_date and
v835.valid_to_date
ORDER BY u831.partner_id
WITH UR;
//*--------------------------------------------------------------------
//* First run (not all fields available yet) 06.06.2012 CHU A593397
//*--------------------------------------------------------------------
//* SELECT distinct
//* v858.ident_id AS UserId
//* ,cast(';' as char(1))
//* ,u831.partner_id AS USER_CIF
//* ,cast(';' as char(1))
//* ,SUBSTR(U100.CD100452,9,5) AS U_CUST_OFFICE
//* ,cast(';' as char(1))
//* ,u831.DOMIZIL AS USER_DOM
//* ,cast(';' as char(1))
//* ,U831.CUSTOMER_SEGMENT AS USER_SEGM
//* ,cast(';' as char(1))
//* ,o831.partner_id AS OWNER_CIF
//* ,cast(';' as char(1))
//* ,SUBSTR(U100.CD100452,9,5) AS O_CUST_OFFICE
//* ,cast(';' as char(1))
//* ,O831.DOMIZIL AS OWNER_DOM
//* ,cast(';' as char(1))
//* ,O831.CUSTOMER_SEGMENT AS OWNER_SEGM
//* ,cast(';' as char(1))
//* ,v832.ext_contract_id AS CNTRACT_ID
//* ,cast(';' as char(1))
//* ,v843.tab_text1 AS PROCUCT
//* ,cast(';' as char(1))
//* ,v835.service_key AS SERVICE
//* ,cast(';' as char(1))
//* ,S843.tab_text1 AS SERVICE_TEXT
//* ,cast(';' as char(1))
//* ,V859.SECID1_LAST_LOGON AS LAST_LOGON_SEC1
//* ,cast(';' as char(1))
//* ,V859.SECID2_LAST_LOGON AS LAST_LOGON_SEC2
//* ,cast(';' as char(1))
//* ,CAST((CASE
//* WHEN((V859.SECID1_LAST_LOGON> V859.SECID2_LAST_LOGON
//* AND V859.SECID1_LAST_LOGON^='9999-12-31-23.59.59.999999')
//* OR V859.SECID2_LAST_LOGON='9999-12-31-23.59.59.999999')
//* THEN V859.SECID1_LAST_LOGON
//* ELSE V859.SECID2_LAST_LOGON
//* END) AS CHAR(26))
//* FROM
//* OA1P.VKS831A1V O831
//* ,OA1P.VKS831A1V U831
//* ,OA1P.VKS832A1V V832
//* ,OA1P.VKS833A1V V833
//* ,OA1P.VKS835A1V V835
//* ,OA1P.VKS858A1V V858
//* ,OA1P.VKS859A1V V859
//* ,OA1P.VKS843A1V V843
//* ,OA1P.VKS843A1V S843
//* ,OA1P.TCD100A1 U100
//* ,OA1P.TCD100A1 O100
//* WHERE
//* V835.PRODUCT_ID IN (
//* '0095',
//* '0125',
//* '0141',
//* '0148',
//* '0149',
//* '0150',
//* '0151',
//* '0183',
//* '0191',
//* '0192',
//* '0193' )
//* and v835.partner_type = 3
//* AND V835.CONTRACT_KEY = V832.CONTRACT_KEY
//*
//* AND V835.CONTRACT_KEY = V833.CONTRACT_KEY
//* and v833.partner_type = 3
//*
//* AND V833.partner_key = u831.partner_key
//* AND U831.partner_ID = U100.CD100001
//*
//* AND V835.arrang_partner_key = O831.partner_key
//* AND O831.partner_ID = O100.CD100001
//*
//* and v833.partner_key = V858.partner_key
//* AND V858.ident_id = V859.ident_id
//* AND V859.application_type = 15
//*
//* AND V843.tab_art = 'PRODUCT_ID'
//* AND V843.tab_code = V835.PRODUCT_ID
//*
//* AND S843.tab_art = 'SERVICE_ID'
//* AND S843.tab_code = V835.SERVICE_KEY
//* AND S843.TAB_LANGUAGE_CODE = '001'
//*
//* and current date < o831.valid_to_date
//* and current date < u831.valid_to_date
//* and current date < v832.valid_to_date
//* and current date < v833.valid_to_date
//* and current date < v858.valid_to_date
//* and current date < v859.valid_to_date
//* and current date between v835.valid_from_date and
//* v835.valid_to_date
//* ORDER BY u831.partner_id
//* WITH UR;