...
| Code Block |
|---|
| language | xml |
|---|
| theme | Eclipse |
|---|
| title | Reward_Participants_List.xml |
|---|
| collapse | true |
|---|
|
<?xml version="1.0" encoding="UTF-8"?><definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../xsd/reports.xsd">
<name>key_reward_participants</name>
<description>key_a_financial_report_that_displays_all_the_financial_transactions_that_were_posted_within_a_specific_period_of_time.</description>
<dataset/>
<entityTable>REWARDPARTICIPANTS</entityTable>
<entityField>REWPARTID</entityField>
<orderBy>REWPARTSIGNUPDATE ASC</orderBy>
<joins>
INNER JOIN ACCOUNTSRECEIVABLE ON ACCOUNTSRECEIVABLE.ACCRECID = REWARDPARTICIPANTS.ACCRECID
INNER JOIN ACCCLASSIFICATIONS ON ACCCLASSIFICATIONS.ACCCLASSID = ACCOUNTSRECEIVABLE.ACCCLASSID
INNER JOIN CONTACTINFORMATION ON CONTACTINFORMATION.CIID = ACCOUNTSRECEIVABLE.CIID
INNER JOIN CONTACTINFODEFINITIONS ON CONTACTINFODEFINITIONS.CIDEFACTIVE = 1 AND CONTACTINFODEFINITIONS.CIDEFDELETED = 0
LEFT JOIN WALLETS ON WALLETS.ACCRECID = ACCOUNTSRECEIVABLE.ACCRECID AND WALLETS.WALLETDELETED = 0 AND WALLETS.LIFECYCLESTATE = 'EFFECTIVE'
INNER JOIN OUUNITS ON OUUNITS.OUUID = REWARDPARTICIPANTS.REWPARTSIGNUPBYOUUID
LEFT JOIN CURRENCIES ON CURRENCIES.CURRENCYID = REWARDPARTICIPANTS.CURRENCYID
LEFT JOIN CURRENCYRATEPERIODS ON CURRENCYRATEPERIODS.CURRENCYID = CURRENCIES.CURRENCYID AND CURRENCYRATEPERIODS.CURRATEPERIODTODATE IS NULL
LEFT JOIN SYSTEMSETTINGS ON SYSTEMSETTINGS.SYSTEMSETTINGNAME= 'DEFAULT_CURRENCY' AND SYSTEMSETTINGS.SYSTEMSETTINGDELETED=0
LEFT JOIN CURRENCIES DEFAULTCURRENCIES ON DEFAULTCURRENCIES.CURRENCYID = SQL_CLOBTOCHAR(SYSTEMSETTINGS.SYSTEMSETTINGSTRINGVALUE)SQL_CLOBTOCHAR
</joins>
<conditions>
REWARDPARTICIPANTS.REWPARTDELETED = 0
</conditions>
<template>Rewards/Reward_Participants_List</template>
<subReports>
<subReport>
<name>walletSubReport</name>
<folder>Rewards</folder>
</subReport>
<subReport>
<name>participatingSchemesSubReport</name>
<folder>Rewards</folder>
</subReport>
<subReport>
<name>signedOffSchemesSubReport</name>
<folder>Rewards</folder>
</subReport>
<subReport>
<name>accessTokensSubReport</name>
<folder>Rewards</folder>
</subReport>
</subReports>
<criteria>
<criterion>
<criteriatype>0</criteriatype>
<label>key_rewards_participant_schemes</label>
<extralabel>key_rewards_participant_schemes</extralabel>
<entitytablename>REWARDSCHEMES</entitytablename>
<entityfieldname>REWSCHID</entityfieldname>
<existclause>
SELECT 'x'
FROM REWARDPARTICIPATINGSCHEMES
INNER JOIN REWARDSCHEMES ON REWARDSCHEMES.REWSCHID = REWARDPARTICIPATINGSCHEMES.REWSCHID
WHERE REWARDPARTICIPATINGSCHEMES.REWPARTID =REWARDPARTICIPANTS.REWPARTID
AND REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMEDELETED = 0
</existclause>
<isMandatory>0</isMandatory>
<managedbeanname>rewardParticipantsView</managedbeanname>
<method>searchRewardSchemes</method>
<componentType>selectcheckboxmenu</componentType>
<className>com.crm.dataobject.rewards.CRMDORewardScheme</className>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_life_cycle_state</label>
<extralabel>key_life_cycle_state</extralabel>
<entitytablename>REWARDPARTICIPANTS</entitytablename>
<entityfieldname>LIFECYCLESTATE</entityfieldname>
<isMandatory>0</isMandatory>
<managedbeanname>rewardParticipantsView</managedbeanname>
<method>getParticipantLifeCycleStateOptions</method>
<componentType>selectcheckboxmenu</componentType>
<className>com.crm.dataobject.rewards.RewardsParticipantLifeCycleState</className>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_sign_up_date</label>
<extralabel>key_sign_up_date</extralabel>
<entitytablename>REWARDPARTICIPANTS</entitytablename>
<entityfieldname>REWPARTSIGNUPDATE</entityfieldname>
<componentType>calendar</componentType>
<isMandatory>0</isMandatory>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_sign_up_unit</label>
<extralabel>key_sign_up_unit</extralabel>
<entitytablename>OUUNITS</entitytablename>
<entityfieldname>OUUID</entityfieldname>
<componentType>autocomplete</componentType>
<managedbeanname>genericReportView</managedbeanname>
<method>searchUnits</method>
<isMandatory>0</isMandatory>
<className>com.crm.dataobject.networkmanagement.CRMDOUnit</className>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_scheme_sign_up_date</label>
<extralabel>key_scheme_sign_up_date</extralabel>
<entitytablename>REWARDPARTICIPATINGSCHEMES</entitytablename>
<entityfieldname>REWPARTSCHEMESIGNUPDATE</entityfieldname>
<componentType>calendar</componentType>
<existclause>
SELECT 'x'
FROM REWARDPARTICIPATINGSCHEMES
WHERE REWARDPARTICIPATINGSCHEMES.REWPARTID =REWARDPARTICIPANTS.REWPARTID
AND REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMEDELETED = 0
</existclause>
<isMandatory>0</isMandatory>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_scheme_sign_off_date</label>
<extralabel>key_scheme_sign_off_date</extralabel>
<entitytablename>REWARDPARTICIPATINGSCHEMES</entitytablename>
<entityfieldname>REWPARTSCHEMESIGNOFFDATE</entityfieldname>
<componentType>calendar</componentType>
<existclause>
SELECT 'x'
FROM REWARDPARTICIPATINGSCHEMES
WHERE REWARDPARTICIPATINGSCHEMES.REWPARTID =REWARDPARTICIPANTS.REWPARTID
AND REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMEDELETED = 0
</existclause>
<isMandatory>0</isMandatory>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_scheme_sign_up_unit</label>
<extralabel>key_scheme_sign_up_unit</extralabel>
<entitytablename>OUUNITS</entitytablename>
<entityfieldname>OUUID</entityfieldname>
<existclause>
SELECT 'x'
FROM REWARDPARTICIPATINGSCHEMES
INNER JOIN OUUNITS ON OUUNITS.OUUID = REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMESIGNUPBYOUUID
WHERE REWARDPARTICIPATINGSCHEMES.REWPARTID =REWARDPARTICIPANTS.REWPARTID
AND REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMEDELETED = 0
</existclause>
<componentType>autocomplete</componentType>
<managedbeanname>genericReportView</managedbeanname>
<method>searchUnits</method>
<isMandatory>0</isMandatory>
<className>com.crm.dataobject.networkmanagement.CRMDOUnit</className>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_accounts_receivable_classification</label>
<extralabel>key_accounts_receivable_classification</extralabel>
<entitytablename>ACCCLASSIFICATIONS</entitytablename>
<entityfieldname>ACCCLASSID</entityfieldname>
<isMandatory>0</isMandatory>
<managedbeanname>accountsReceivableSummaryView</managedbeanname>
<method>searchClassifications</method>
<componentType>selectcheckboxmenu</componentType>
<className>com.crm.dataobject.accounts.CRMDOAccountClassification</className>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_termination_date</label>
<extralabel>key_termination_date</extralabel>
<entitytablename>REWARDPARTICIPANTS</entitytablename>
<entityfieldname>REWPARTTERMINATIONDATE</entityfieldname>
<componentType>calendar</componentType>
<isMandatory>0</isMandatory>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_rewards_participant_gender</label>
<extralabel>key_rewards_participant_gender</extralabel>
<entitytablename>CONTACTINFORMATION</entitytablename>
<entityfieldname>CIGENDER</entityfieldname>
<isMandatory>0</isMandatory>
<managedbeanname>subscriptionChangeAccountView</managedbeanname>
<method>getGenderOptions</method>
<componentType>selectcheckboxmenu</componentType>
<className>com.crm.dataobject.contactinfo.Gender</className>
</criterion>
<criterion>
<criteriatype>0</criteriatype>
<label>key_rewards_participant_age_range</label>
<extralabel>key_rewards_participant_age_range</extralabel>
<entitytablename>EXTRACT(YEAR FROM CURRENT_DATE) - COALESCE(CONTACTINFORMATION</entitytablename>
<entityfieldname>CIYEAROFBIRTH,EXTRACT(YEAR FROM CURRENT_DATE))</entityfieldname>
<fieldtype>ftXInteger</fieldtype>
<componentType>inputText</componentType>
<multiselect>0</multiselect>
<isMandatory>0</isMandatory>
</criterion>
</criteria>
<columns>
<column>
<fieldname>REWARDPARTICIPANTS.REWPARTID</fieldname>
<alias>PRIMARY_KEY</alias>
<hidden>1</hidden>
</column>
<column>
<fieldname>REWPARTNUM</fieldname>
<width>10</width>
<widthType>PERCENTAGE</widthType>
<header>key_number</header>
</column>
<column>
<fieldname>ACCOUNTSRECEIVABLE.ACCRECNAME</fieldname>
<alias>ACCOUNTSRECEIVABLE</alias>
<width>20</width>
<widthType>PERCENTAGE</widthType>
<header>key_accounts_receivable</header>
</column>
<column>
<fieldname>
CASE WHEN REWARDPARTICIPANTS.LIFECYCLESTATE='TERMINATED' then 'Terminated'
WHEN REWARDPARTICIPANTS.LIFECYCLESTATE='EFFECTIVE' then 'Effective'
END
</fieldname>
<alias>LIFECYCLESTATE</alias>
<width>6</width>
<header>key_life_cycle_state</header>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>ACCCLASSNAME</fieldname>
<width>10</width>
<widthType>PERCENTAGE</widthType>
<header>key_accounts_receivable_classification</header>
</column>
<column>
<fieldname>REWPARTTERMINATIONDATE</fieldname>
<fieldtype>FTXDATE</fieldtype>
<width>10</width>
<widthType>PERCENTAGE</widthType>
<header>key_termination_date</header>
</column>
<column>
<fieldname>
CASE WHEN REWARDPARTICIPANTS.rewpartterminationreason='CUSTOMER_REQUEST' then 'Customer Request'
WHEN REWARDPARTICIPANTS.rewpartterminationreason='FRAUD' then 'Fraud'
WHEN REWARDPARTICIPANTS.rewpartterminationreason='DUPLICATION' then 'Duplication'
END
</fieldname>
<alias>REWPARTTERMINATIONREASON</alias>
<width>10</width>
<widthType>PERCENTAGE</widthType>
<header>key_termination_reason</header>
</column>
<column>
<fieldname>REWPARTSIGNUPDATE</fieldname>
<fieldtype>FTXDATE</fieldtype>
<width>10</width>
<widthType>PERCENTAGE</widthType>
<header>key_sign_up_date</header>
</column>
<column>
<fieldname>OUUNAME</fieldname>
<width>10</width>
<widthType>PERCENTAGE</widthType>
<header>key_sign_up_by_unit</header>
</column>
<column>
<fieldname>
(
select count(1) from rewardparticipatingschemes rps where
rps.rewpartid=REWARDPARTICIPANTS.rewpartid
and
(rewpartschemesignupdate > SQL_FIRST_DAY[[SQL_CURRENT_DATE]]SQL_FIRST_DAY
or
rewpartschemesignupdate = SQL_FIRST_DAY[[SQL_CURRENT_DATE]]SQL_FIRST_DAY)
and
rewpartschemesignupdate < [SQL_CURRENT_DATE]
and rps.rewpartschemedeleted=0 and rewpartschemesignoffdate is null
)
</fieldname>
<alias>THIS_MONTH_SCHEME_SIGN_UPS</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_sign_ups</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>
(
select count(1) from rewardparticipatingschemes rps where
rps.rewpartid=REWARDPARTICIPANTS.rewpartid
and rewpartschemesignoffdate is not null and
(rewpartschemesignoffdate > SQL_FIRST_DAY[[SQL_CURRENT_DATE]]SQL_FIRST_DAY
or
rewpartschemesignoffdate = SQL_FIRST_DAY[[SQL_CURRENT_DATE]]SQL_FIRST_DAY)
and rewpartschemesignoffdate < [SQL_CURRENT_DATE]
and rps.rewpartschemedeleted=0
)
</fieldname>
<alias>THIS_MONTH_SCHEME_SIGN_OFFS</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_sign_offs</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>
(
select 1 from rewardparticipants RP
where LIFECYCLESTATE='EFFECTIVE' and RP.rewpartid =REWARDPARTICIPANTS.rewpartid
)
</fieldname>
<alias>EFFECTIVE_PARTICIPANTS</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_effective_participants</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>
(
select 1 from rewardparticipants RP
where LIFECYCLESTATE='TERMINATED' and RP.rewpartid =REWARDPARTICIPANTS.rewpartid
)
</fieldname>
<alias>TERMINATED_PARTICIPANTS</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_terminated_participants</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>
(
select 1 from rewardparticipants RP where
REWPARTTERMINATIONDATE is not null and
(RP.REWPARTTERMINATIONDATE > SQL_FIRST_DAY[[SQL_CURRENT_DATE]]SQL_FIRST_DAY
or
RP.REWPARTTERMINATIONDATE = SQL_FIRST_DAY[[SQL_CURRENT_DATE]]SQL_FIRST_DAY)
and REWPARTTERMINATIONDATE < [SQL_CURRENT_DATE]
and RP.rewpartid =REWARDPARTICIPANTS.rewpartid
)
</fieldname>
<alias>THIS_MONTH_TERMINATIONS</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_balance</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>
(
select COALESCE(sum(COALESCE(rewawardtrxnamt,0)),0) from rewardawardtransactions where
rewardawardtransactions.rewpartid=REWARDPARTICIPANTS.rewpartid
and rewawardtrxndeleted=0
)
</fieldname>
<alias>THIS_MONTH_AWARDED_AMOUNT</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_balance</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>
(
select COALESCE(sum(COALESCE(rewspendtrxntotalamt,0)),0) from rewardspendtransactions where
rewardspendtransactions.rewpartid=REWARDPARTICIPANTS.rewpartid
and rewspendtrxndeleted=0
)
</fieldname>
<alias>THIS_MONTH_SPEND_AMOUNT</alias>
<width>1</width>
<hidden>1</hidden>
<widthType>PERCENTAGE</widthType>
<header>key_balance</header>
<fieldtype>FTXINTEGER</fieldtype>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
</column>
<column>
<fieldname>(SELECT COUNT(WALLETID) FROM WALLETS WHERE WALLETS.ACCRECID = ACCOUNTSRECEIVABLE.ACCRECID AND WALLETS.WALLETDELETED = 0)</fieldname>
<alias>NUMOFWALLETS</alias>
<hidden>1</hidden>
<fieldtype>FTXINTEGER</fieldtype>
</column>
<column>
<fieldname>(SELECT COUNT(REWPARTSCHEMEID) FROM REWARDPARTICIPATINGSCHEMES WHERE REWARDPARTICIPATINGSCHEMES.REWPARTID = REWARDPARTICIPANTS.REWPARTID AND REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMEDELETED = 0 AND REWPARTSCHEMESIGNOFFDATE IS NULL)</fieldname>
<alias>NUMOFSIGNUPSCHEMES</alias>
<hidden>1</hidden>
<fieldtype>FTXINTEGER</fieldtype>
</column>
<column>
<fieldname>(SELECT COUNT(REWPARTSCHEMEID) FROM REWARDPARTICIPATINGSCHEMES WHERE REWARDPARTICIPATINGSCHEMES.REWPARTID = REWARDPARTICIPANTS.REWPARTID AND REWARDPARTICIPATINGSCHEMES.REWPARTSCHEMEDELETED = 0 AND REWPARTSCHEMESIGNOFFDATE IS NOT NULL)</fieldname>
<alias>NUMOFSIGNOFFSCHEMES</alias>
<hidden>1</hidden>
<fieldtype>FTXINTEGER</fieldtype>
</column>
<column>
<fieldname>(SELECT COUNT(ACCESSTOKENID) FROM ACCESSTOKENS WHERE ACCESSTOKENS.REWPARTID = REWARDPARTICIPANTS.REWPARTID AND ACCESSTOKENS.ACCESSTOKENDELETED = 0 AND ACCESSTOKENS.LIFECYCLESTATE= 'EFFECTIVE')</fieldname>
<alias>NUMOFACCESSTOKNES</alias>
<hidden>1</hidden>
<fieldtype>FTXINTEGER</fieldtype>
</column>
</columns>
<groups>
<group>
<groupid>ACCOUNT_RECEIVABLE_CLASSIFICATION</groupid>
<sortorder>ACCOUNTSRECEIVABLE.ACCCLASSID</sortorder>
<groupbyheader>key_accounts_receivable_classification</groupbyheader>
<header>
<column>
<fieldname>ACCCLASSNAME</fieldname>
<width>25</width>
<widthType>PERCENTAGE</widthType>
<header>key_accounts_receivable_classification:</header>
</column>
</header>
<footer>
<column>
<fieldname>=COUNT({EFFECTIVE_PARTICIPANTS})</fieldname>
<header>key_effective_reward_participants:</header>
<width>25</width>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=COUNT({TERMINATED_PARTICIPANTS})</fieldname>
<header>key_terminated_reward_participants:</header>
<width>25</width>
<widthType>PERCENTAGE</widthType>
</column>
</footer>
</group>
<group>
<groupid>SIGN_UP_UNIT</groupid>
<sortorder>OUUNITS.OUUID</sortorder>
<groupbyheader>key_sign_up_unit</groupbyheader>
<header>
<column>
<fieldname>OUUNAME</fieldname>
<width>25</width>
<widthType>PERCENTAGE</widthType>
<header>key_sign_up_unit:</header>
</column>
</header>
<footer>
<column>
<fieldname>=COUNT({EFFECTIVE_PARTICIPANTS})</fieldname>
<header>key_effective_reward_participants:</header>
<width>25</width>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=COUNT({TERMINATED_PARTICIPANTS})</fieldname>
<header>key_terminated_reward_participants:</header>
<width>25</width>
<widthType>PERCENTAGE</widthType>
</column>
</footer>
</group>
</groups>
<footers>
<column>
<fieldname>=COUNT({REWPARTNUM})</fieldname>
<header>key_number_of_reward_participants:</header>
<width>15</width>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=SUM({THIS_MONTH_SCHEME_SIGN_UPS})</fieldname>
<header>key_scheme_sign_ups_during_this_month:</header>
<width>15</width>
<widthType>PERCENTAGE</widthType>
<fieldtype>FTXINTEGER</fieldtype>
</column>
<column>
<fieldname>=SUM({THIS_MONTH_SCHEME_SIGN_OFFS})</fieldname>
<header>key_scheme_sign_offs_during_this_month:</header>
<width>15</width>
<widthType>PERCENTAGE</widthType>
<fieldtype>FTXINTEGER</fieldtype>
</column>
<column>
<fieldname>=COUNT({EFFECTIVE_PARTICIPANTS})</fieldname>
<header>key_effective_reward_participants:</header>
<width>15</width>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=COUNT({TERMINATED_PARTICIPANTS})</fieldname>
<header>key_terminated_reward_participants:</header>
<width>15</width>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=COUNT({THIS_MONTH_TERMINATIONS})</fieldname>
<header>key_terminations_during_this_month:</header>
<width>15</width>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=SUM({THIS_MONTH_AWARDED_AMOUNT})</fieldname>
<header>key_awarded_amount_during_this_month:</header>
<width>15</width>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
<widthType>PERCENTAGE</widthType>
</column>
<column>
<fieldname>=SUM({THIS_MONTH_SPEND_AMOUNT})</fieldname>
<header>key_spent_amount_during_this_month:</header>
<width>15</width>
<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
<widthType>PERCENTAGE</widthType>
</column>
</footers>
</definition>
|
| Expand |
|---|
| title | Accounts Receivable Balance Reward Participants Report Criteria |
|---|
|
 Image Removed Image Added
|
| Expand |
|---|
| title | Accounts Receivable Balance Reward Participants Report Results |
|---|
|
Image Removed Image Removed Image Added |
For a full list of report page attributes, go to Report Pages Documentation
...