Help needed: Application Owner campaign need to create report with consulted on step2 and no respons

[mbh_it1]

Hello All,

I want to create a report for application owner campaign having 

1) Campaign Name

2) Consultee

Such that the step2 consulted is done but there is no response.

Kindly guide me as I am not aware of which table the actions get logged like consult and no response. we are using EIC v24.4

Thank you

Mahesh

[rushikeshvartak]

SELECT

a.CERT_NAME 'CERTIFICATION NAME',
CASE
WHEN a.status = 0 THEN 'New'
WHEN a.status = 1 THEN 'In Progress'
WHEN a.status = 2 THEN 'Ready to Submit'
WHEN a.status = 3 THEN 'Submitted'
WHEN a.status = 4 THEN 'Expired'
WHEN a.status = 5 THEN 'Discontinued'
WHEN a.status = 6 THEN 'Preview'
WHEN a.status = 7 THEN 'Discontinued'
WHEN a.status = 8 THEN 'Submitted and Task Created'
WHEN a.status = 9 THEN 'Cancelled'
WHEN a.status = 10 THEN 'Submitted'
END 'CERTIFICATION STATUS',
DATE(a.startdate) 'CERTIFICATION LAUNCH DATE',
CASE
WHEN (a.status IN (3 , 2, 8, 10)) THEN DATE(a.updatedate)
END 'CERTIFICATION COMPLETE DATE',
u.username 'CERTIFIER',
concat(u.firstname, ' ', u.lastname) AS 'CERTIFIER FULL NAME',
u.EMAIL AS 'CERTIFIER EMAIL'
from certification a, users u
where u.userkey = a.certifier
and a.type=9;

[mbh_it1]

Hi @rushikeshvartak  ,

sorry looks like I did not explain well, actually I need to make a report for application owner campaign and that should list all consulted line items which has no response.

I made following query but still the consulted usernames coming wrong and the number of data shown is wrong so, you can comment may be , you may have some insight.

SELECT 

    cm.campaign_name AS 'Campaign Name',

    k.CERT_NAME AS 'Certification Name',

    u2.username AS 'Consultee Username',

    u2.FIRSTNAME AS 'Consultee First Name',

    u2.LASTNAME AS 'Consultee Last Name',

    u.USERNAME AS 'Consulted Username',

    u.FIRSTNAME AS 'Consulted User First Name',

    u.LASTNAME AS 'Consulted User Last Name',

    CAS.AUDIT_TRAIL

FROM

    certification_certifier cc,

    certification k,

    certification_user cu,

    users u,

    users u2,

    campaign cm,

    certification_account_entitlement1_status CAS

WHERE

    k.certkey = cc.certkey

        AND cc.userkey = cu.cert_userkey

        AND u.userkey = cu.userkey

        AND k.CAMPAIGNKEY = cm.id

        AND u2.USERKEY = cc.CERTIFIER

        AND k.certkey=207

        and CAS.CERTKEY=k.CERTKEY

        and CAS.AUDIT_TRAIL like 'Consulted%' and CAS.CERTIFIED is null 

UNION 

SELECT 

    cm.campaign_name AS 'Campaign Name',

    k.CERT_NAME AS 'Certification Name',

    u2.username AS 'Consultee Username',

    u2.FIRSTNAME AS 'Consultee First Name',

    u2.LASTNAME AS 'Consultee Last Name',

    u.USERNAME AS 'Consulted Username',

    u.FIRSTNAME AS 'Consulted User First Name',

    u.LASTNAME AS 'Consulted User Last Name',

    CEV.AUDIT_TRAIL

FROM

    certification_certifier cc,

    certification k,

    certification_user cu,

    users u,

    users u2,

    campaign cm,

    cert_ent_values_status CEV

WHERE

    k.certkey = cc.certkey

        AND cc.userkey = cu.cert_userkey

        AND u.userkey = cu.userkey

        AND k.CAMPAIGNKEY = cm.id

        AND u2.USERKEY = cc.CERTIFIER

        AND k.certkey=207

        and CEV.CERTKEY=k.CERTKEY

        and CEV.AUDIT_TRAIL like 'Consulted%' and CEV.CERTIFIED is null ;

Thanks

Mahesh

[rushikeshvartak]

What if multiple consultee ?

[mbh_it1]

Hi @rushikeshvartak 

One is user for which the consult is done, another is the new user who is consultee, who will be new certifier(approver/ rejector of access).

Thanks

Mahesh

[rushikeshvartak]

Can you explain with example