tiered camapaign

[Debankita]

Hi Team,

Can some one please help me with a query which will help to fetch the approved items in the previous campaign. I want to place the query in the present campaign and want to make it run for only the items approved in the previous one.

[Manu269]

@Debankita try referring few of these points which has created few reports :

Campaign Report Queries - Saviynt Forums - 50171

Frequently used Campaign Reports based on Campaign... - Saviynt Forums - 28534

How to download Campaign overall summary report fo... - Saviynt Forums - 29693

Solved: UM Campaign Data - Analytics Report - Saviynt Forums - 54339

Solved: Campaign Query - Saviynt Forums - 17654

[rushikeshvartak]

Can you provide more details type of campaign 

[Debankita]

Hi @rushikeshvartak ,

We have a requirement to configure a tiered campaign, where in the first step an user manager campaign will be launched. In the second step, an entitlement owner campaign is being launched with the line actions the line items which were approved in the user manager campaign.  

I need help with the query which will help in fetching those approved line items.

[rushikeshvartak]

Refer https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter15-Campaigns-and-Certificati...

1. Sample Account Entitlements1 Query:

   (ae1.accountkey, ae1.entitlement_valuekey) in (select distinct ae.accountkey,ae.entitlement_valuekey from Account_entitlements1 ae , Certification_account_entitlement1_status cae, Certification_account ca, Certification_entitlement_value cev , Accounts a, Entitlement_values ev, Campaign c, Certification ce where cae.certified!='3' and cae.cert_accountkey = ca.id and ca.accountkey= a.id and a.id = ae.accountkey and cae.cert_entitlement_valuekey = cev.id and cev.entitlement_valuekey = ev.id and ev.id= ae.entitlement_valuekey and c.id = ce.campaignKey and ce.id = cae.certkey and c.campaignName = 'TieredUM')
2.  

[Debankita]

Hi @rushikeshvartak ,

I tried with the above query, but when I am using it, the query is not able to fetch any record. The campaign is going to the completed state immediately after it is being launched.

[rushikeshvartak]

Validate if its contains data in data analyzer

[Debankita]

Hi @rushikeshvartak ,

I tried in data analyzer the query is not fetching any record. Can you please help me in identifying the error in the query why it is not fetching the approved line items.

[Debankita]

Hi Team,

We are able to fetch the line items from the user manager campaign by using the below query 

(ae1.accountkey, ae1.entitlement_valuekey) in (select distinct ae.accountkey,ae.entitlement_valuekey from Account_entitlements1 ae , Certification_account_entitlement1_status cae, Certification_account ca, Certification_entitlement_value cev , Accounts a, Entitlement_values ev, Campaign c, Certification ce where cae.cert_accountkey = ca.id and ca.accountkey= a.id and a.id = ae.accountkey and cae.cert_entitlement_valuekey = cev.id and cev.entitlement_valuekey = ev.id and ev.id= ae.entitlement_valuekey and c.id = ce.campaignKey and ce.id = cae.certkey and c.campaignName = 'Tiered campaign_User_manager_review').

But it is fetching all the line items, we want only approved line items, can someone please help me with the customization. Please suggest what modification needs to be done to achieve this requirement.