zOs/SQL/HUNGER2

//STA0153A 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.#STA0153.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.#STA0153.UNLOAD,
//             MGMTCLAS=COM#A091,
//             DISP=(NEW,CATLG,DELETE)
//**********************************************************************
//*  Report for Esther Rein  N4354, PSENG   STA0152 and STA0153
//*  --------------------------------------------------
//*
//*  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:      --> STA0152
//*      - 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
//*
//*  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:
//*  -------
//*  000000000001      TEST_IDENTIFIKATION   for Reset
//*  083509400102      284476362015080802-P  ???
//*
//**********************************************************************
//SYSTSIN   DD *
 DSN SYSTEM(DBOF)
 RUN PROGRAM(DSNTIAUL) PARM('SQL')
//SYSIN     DD *
     SELECT
            v858.ident_id
           ,CHAR(DECIMAL(COUNT(distinct V841.ACTIVITY_TSTAMP),10,0))
     FROM
            OA1P.VKS831A1V  O831
           ,OA1P.VKS831A1V  U831
           ,OA1P.VKS832A1V  V832
           ,OA1P.VKS833A1V  V833
           ,OA1P.VKS835A1V  V835
           ,OA1P.VKS858A1V  V858
           ,OA1P.VKS859A1V  V859
           ,OA1P.VKS841H1V  V841
     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 V835.arrang_partner_key = O831.partner_key

           and v833.partner_key        = V858.partner_key
           AND V858.ident_id           = V859.ident_id
           AND V859.application_type   = 15

           AND V858.IDENT_ID           = V841.LOG_KEY
           AND V841.APPLICATION_TYPE   = 15
           AND V841.LOG_CODE           = '0000'

           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
           GROUP BY V858.ident_id
      WITH UR;